Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
el:developers:stored_procedures [2022/10/31 15:55] – created admin | el:developers:stored_procedures [2022/10/31 16:10] (current) – admin | ||
---|---|---|---|
Line 2: | Line 2: | ||
- | Για να γίνει μεταφορά της πλατφόρμας από έναν υπολογιστή σε άλλο χρειάζεται να γίνει backup / restore η βάση δεδομένων της πλατφόρμας. Επειδή όμως το backup της βάσης δεν έχει τα stored procedures, για να δημιουργηθούν στη νέα πλατφόρμα | + | Για να γίνει μεταφορά της πλατφόρμας από έναν υπολογιστή σε άλλο χρειάζεται να γίνει backup / restore η βάση δεδομένων της πλατφόρμας. Επειδή όμως το backup της βάσης δεν έχει τα stored procedures, για να δημιουργηθούν στη νέα πλατφόρμα |
+ | |||
+ | < | ||
+ | DROP PROCEDURE IF EXISTS add_node; | ||
+ | DROP PROCEDURE IF EXISTS update_node; | ||
+ | DROP PROCEDURE IF EXISTS delete_node; | ||
+ | DROP PROCEDURE IF EXISTS shift_right; | ||
+ | DROP PROCEDURE IF EXISTS shift_left; | ||
+ | DROP PROCEDURE IF EXISTS shift_end; | ||
+ | DROP PROCEDURE IF EXISTS get_maxrgt; | ||
+ | DROP PROCEDURE IF EXISTS get_parent; | ||
+ | DROP PROCEDURE IF EXISTS delete_nodes; | ||
+ | DROP PROCEDURE IF EXISTS move_nodes; | ||
+ | DELIMITER // | ||
+ | CREATE PROCEDURE `add_node` (IN name TEXT CHARSET utf8, IN description TEXT CHARSET utf8, IN parentlft INT(11), | ||
+ | IN p_code VARCHAR(20) CHARSET utf8, IN p_allow_course BOOLEAN, | ||
+ | IN p_allow_user BOOLEAN, IN p_order_priority INT(11), IN p_visible TINYINT(4)) | ||
+ | LANGUAGE SQL | ||
+ | BEGIN | ||
+ | DECLARE lft, rgt INT(11); | ||
+ | SET lft = parentlft + 1; | ||
+ | SET rgt = parentlft + 2; | ||
+ | CALL shift_right(parentlft, | ||
+ | INSERT INTO `hierarchy` (name, description, | ||
+ | END;// | ||
+ | CREATE PROCEDURE `update_node` (IN p_id INT(11), IN p_name TEXT CHARSET utf8, IN p_description TEXT CHARSET utf8, | ||
+ | IN nodelft INT(11), IN p_lft INT(11), IN p_rgt INT(11), IN parentlft INT(11), | ||
+ | IN p_code VARCHAR(20) CHARSET utf8, IN p_allow_course BOOLEAN, IN p_allow_user BOOLEAN, | ||
+ | IN p_order_priority INT(11), IN p_visible TINYINT(4)) | ||
+ | LANGUAGE SQL | ||
+ | BEGIN | ||
+ | UPDATE `hierarchy` SET name = p_name, description = p_description, | ||
+ | code = p_code, allow_course = p_allow_course, | ||
+ | order_priority = p_order_priority, | ||
+ | IF nodelft <> parentlft THEN | ||
+ | CALL move_nodes(nodelft, | ||
+ | END IF; | ||
+ | END;// | ||
+ | CREATE PROCEDURE `delete_node` (IN p_id INT(11)) | ||
+ | LANGUAGE SQL | ||
+ | BEGIN | ||
+ | DECLARE p_lft, p_rgt INT(11); | ||
+ | SELECT lft, rgt INTO p_lft, p_rgt FROM `hierarchy` WHERE id = p_id; | ||
+ | DELETE FROM `hierarchy` WHERE id = p_id; | ||
+ | CALL delete_nodes(p_lft, | ||
+ | END;// | ||
+ | CREATE PROCEDURE `shift_right` (IN node INT(11), IN shift INT(11), IN maxrgt INT(11)) | ||
+ | LANGUAGE SQL | ||
+ | BEGIN | ||
+ | IF maxrgt > 0 THEN | ||
+ | UPDATE `hierarchy` SET rgt = rgt + shift WHERE rgt > node AND rgt <= maxrgt; | ||
+ | ELSE | ||
+ | UPDATE `hierarchy` SET rgt = rgt + shift WHERE rgt > node; | ||
+ | END IF; | ||
+ | IF maxrgt > 0 THEN | ||
+ | UPDATE `hierarchy` SET lft = lft + shift WHERE lft > node AND lft <= maxrgt; | ||
+ | ELSE | ||
+ | UPDATE `hierarchy` SET lft = lft + shift WHERE lft > node; | ||
+ | END IF; | ||
+ | END;// | ||
+ | CREATE PROCEDURE `shift_left` (IN node INT(11), IN shift INT(11), IN maxrgt INT(11)) | ||
+ | LANGUAGE SQL | ||
+ | BEGIN | ||
+ | IF maxrgt > 0 THEN | ||
+ | UPDATE `hierarchy` SET rgt = rgt - shift WHERE rgt > node AND rgt <= maxrgt; | ||
+ | ELSE | ||
+ | UPDATE `hierarchy` SET rgt = rgt - shift WHERE rgt > node; | ||
+ | END IF; | ||
+ | IF maxrgt > 0 THEN | ||
+ | UPDATE `hierarchy` SET lft = lft - shift WHERE lft > node AND lft <= maxrgt; | ||
+ | ELSE | ||
+ | UPDATE `hierarchy` SET lft = lft - shift WHERE lft > node; | ||
+ | END IF; | ||
+ | END;// | ||
+ | CREATE PROCEDURE `shift_end` (IN p_lft INT(11), IN p_rgt INT(11), IN maxrgt INT(11)) | ||
+ | LANGUAGE SQL | ||
+ | BEGIN | ||
+ | UPDATE `hierarchy` | ||
+ | SET lft = (lft - (p_lft - 1)) + maxrgt, | ||
+ | rgt = (rgt - (p_lft - 1)) + maxrgt WHERE lft BETWEEN p_lft AND p_rgt; | ||
+ | END;// | ||
+ | CREATE PROCEDURE `get_maxrgt` (OUT maxrgt INT(11)) | ||
+ | LANGUAGE SQL | ||
+ | BEGIN | ||
+ | SELECT rgt INTO maxrgt FROM `hierarchy` ORDER BY rgt DESC LIMIT 1; | ||
+ | END;// | ||
+ | CREATE PROCEDURE `get_parent` (IN p_lft INT(11), IN p_rgt INT(11)) | ||
+ | LANGUAGE SQL | ||
+ | BEGIN | ||
+ | SELECT * FROM `hierarchy` WHERE lft < p_lft AND rgt > p_rgt ORDER BY lft DESC LIMIT 1; | ||
+ | END;// | ||
+ | CREATE PROCEDURE `delete_nodes` (IN p_lft INT(11), IN p_rgt INT(11)) | ||
+ | LANGUAGE SQL | ||
+ | BEGIN | ||
+ | DECLARE node_width INT(11); | ||
+ | SET node_width = p_rgt - p_lft + 1; | ||
+ | DELETE FROM `hierarchy` WHERE lft BETWEEN p_lft AND p_rgt; | ||
+ | UPDATE `hierarchy` SET rgt = rgt - node_width WHERE rgt > p_rgt; | ||
+ | UPDATE `hierarchy` SET lft = lft - node_width WHERE lft > p_lft; | ||
+ | END;// | ||
+ | CREATE PROCEDURE `move_nodes` (INOUT nodelft INT(11), IN p_lft INT(11), IN p_rgt INT(11)) | ||
+ | LANGUAGE SQL | ||
+ | BEGIN | ||
+ | DECLARE node_width, maxrgt INT(11); | ||
+ | SET node_width = p_rgt - p_lft + 1; | ||
+ | CALL get_maxrgt(maxrgt); | ||
+ | CALL shift_end(p_lft, | ||
+ | IF nodelft = 0 THEN | ||
+ | CALL shift_left(p_rgt, | ||
+ | ELSE | ||
+ | CALL shift_left(p_rgt, | ||
+ | IF p_lft < nodelft THEN | ||
+ | SET nodelft = nodelft - node_width; | ||
+ | END IF; | ||
+ | CALL shift_right(nodelft, | ||
+ | UPDATE `hierarchy` SET rgt = (rgt - maxrgt) + nodelft WHERE rgt > maxrgt; | ||
+ | UPDATE `hierarchy` SET lft = (lft - maxrgt) + nodelft WHERE lft > maxrgt; | ||
+ | END IF; | ||
+ | END;// | ||
+ | DELIMITER ; | ||
+ | </ |