Project

General

Profile

Stored Procedures ยป hierarchy.sql

Yannis Exidaridis, 23/09/2019 11:34

 
1
DROP PROCEDURE IF EXISTS add_node;
2
DROP PROCEDURE IF EXISTS update_node;
3
DROP PROCEDURE IF EXISTS delete_node;
4
DROP PROCEDURE IF EXISTS shift_right;
5
DROP PROCEDURE IF EXISTS shift_left;
6
DROP PROCEDURE IF EXISTS shift_end;
7
DROP PROCEDURE IF EXISTS get_maxrgt;
8
DROP PROCEDURE IF EXISTS get_parent;
9
DROP PROCEDURE IF EXISTS delete_nodes;
10
DROP PROCEDURE IF EXISTS move_nodes;
11
DELIMITER //
12
CREATE PROCEDURE `add_node` (IN name TEXT CHARSET utf8, IN description TEXT CHARSET utf8, IN parentlft INT(11),
13
            IN p_code VARCHAR(20) CHARSET utf8, IN p_allow_course BOOLEAN,
14
            IN p_allow_user BOOLEAN, IN p_order_priority INT(11), IN p_visible TINYINT(4))
15
        LANGUAGE SQL
16
        BEGIN
17
            DECLARE lft, rgt INT(11);
18
            SET lft = parentlft + 1;
19
            SET rgt = parentlft + 2;
20
            CALL shift_right(parentlft, 2, 0);
21
            INSERT INTO `hierarchy` (name, description, lft, rgt, code, allow_course, allow_user, order_priority, visible) VALUES (name, description, lft, rgt, p_code, p_allow_course, p_allow_user, p_order_priority, p_visible);
22
        END;//
23
CREATE PROCEDURE `update_node` (IN p_id INT(11), IN p_name TEXT CHARSET utf8, IN p_description TEXT CHARSET utf8,
24
            IN nodelft INT(11), IN p_lft INT(11), IN p_rgt INT(11), IN parentlft INT(11),
25
            IN p_code VARCHAR(20) CHARSET utf8, IN p_allow_course BOOLEAN, IN p_allow_user BOOLEAN,
26
            IN p_order_priority INT(11), IN p_visible TINYINT(4))
27
        LANGUAGE SQL
28
        BEGIN
29
            UPDATE `hierarchy` SET name = p_name, description = p_description, lft = p_lft, rgt = p_rgt,
30
                code = p_code, allow_course = p_allow_course, allow_user = p_allow_user,
31
                order_priority = p_order_priority, visible = p_visible WHERE id = p_id;
32
            IF nodelft <> parentlft THEN
33
                CALL move_nodes(nodelft, p_lft, p_rgt);
34
            END IF;
35
        END;//
36
CREATE PROCEDURE `delete_node` (IN p_id INT(11))
37
        LANGUAGE SQL
38
        BEGIN
39
            DECLARE p_lft, p_rgt INT(11);
40
            SELECT lft, rgt INTO p_lft, p_rgt FROM `hierarchy` WHERE id = p_id;
41
            DELETE FROM `hierarchy` WHERE id = p_id;
42
            CALL delete_nodes(p_lft, p_rgt);
43
        END;//
44
CREATE PROCEDURE `shift_right` (IN node INT(11), IN shift INT(11), IN maxrgt INT(11))
45
        LANGUAGE SQL
46
        BEGIN
47
            IF maxrgt > 0 THEN
48
                UPDATE `hierarchy` SET rgt = rgt + shift WHERE rgt > node AND rgt <= maxrgt;
49
            ELSE
50
                UPDATE `hierarchy` SET rgt = rgt + shift WHERE rgt > node;
51
            END IF;
52
            IF maxrgt > 0 THEN
53
                UPDATE `hierarchy` SET lft = lft + shift WHERE lft > node AND lft <= maxrgt;
54
            ELSE
55
                UPDATE `hierarchy` SET lft = lft + shift WHERE lft > node;
56
            END IF;
57
        END;//
58
CREATE PROCEDURE `shift_left` (IN node INT(11), IN shift INT(11), IN maxrgt INT(11))
59
        LANGUAGE SQL
60
        BEGIN
61
            IF maxrgt > 0 THEN
62
                UPDATE `hierarchy` SET rgt = rgt - shift WHERE rgt > node AND rgt <= maxrgt;
63
            ELSE
64
                UPDATE `hierarchy` SET rgt = rgt - shift WHERE rgt > node;
65
            END IF;
66
            IF maxrgt > 0 THEN
67
                UPDATE `hierarchy` SET lft = lft - shift WHERE lft > node AND lft <= maxrgt;
68
            ELSE
69
                UPDATE `hierarchy` SET lft = lft - shift WHERE lft > node;
70
            END IF;
71
        END;//
72
CREATE PROCEDURE `shift_end` (IN p_lft INT(11), IN p_rgt INT(11), IN maxrgt INT(11))
73
        LANGUAGE SQL
74
        BEGIN
75
            UPDATE `hierarchy`
76
            SET lft = (lft - (p_lft - 1)) + maxrgt,
77
                rgt = (rgt - (p_lft - 1)) + maxrgt WHERE lft BETWEEN p_lft AND p_rgt;
78
        END;//
79
CREATE PROCEDURE `get_maxrgt` (OUT maxrgt INT(11))
80
        LANGUAGE SQL
81
        BEGIN
82
            SELECT rgt INTO maxrgt FROM `hierarchy` ORDER BY rgt DESC LIMIT 1;
83
        END;//
84
CREATE PROCEDURE `get_parent` (IN p_lft INT(11), IN p_rgt INT(11))
85
        LANGUAGE SQL
86
        BEGIN
87
            SELECT * FROM `hierarchy` WHERE lft < p_lft AND rgt > p_rgt ORDER BY lft DESC LIMIT 1;
88
        END;//
89
CREATE PROCEDURE `delete_nodes` (IN p_lft INT(11), IN p_rgt INT(11))
90
        LANGUAGE SQL
91
        BEGIN
92
            DECLARE node_width INT(11);
93
            SET node_width = p_rgt - p_lft + 1;
94
            DELETE FROM `hierarchy` WHERE lft BETWEEN p_lft AND p_rgt;
95
            UPDATE `hierarchy` SET rgt = rgt - node_width WHERE rgt > p_rgt;
96
            UPDATE `hierarchy` SET lft = lft - node_width WHERE lft > p_lft;
97
        END;//
98
CREATE PROCEDURE `move_nodes` (INOUT nodelft INT(11), IN p_lft INT(11), IN p_rgt INT(11))
99
        LANGUAGE SQL
100
        BEGIN
101
            DECLARE node_width, maxrgt INT(11);
102
            SET node_width = p_rgt - p_lft + 1;
103
            CALL get_maxrgt(maxrgt);
104
            CALL shift_end(p_lft, p_rgt, maxrgt);
105
            IF nodelft = 0 THEN
106
                CALL shift_left(p_rgt, node_width, 0);
107
            ELSE
108
                CALL shift_left(p_rgt, node_width, maxrgt);
109
                IF p_lft < nodelft THEN
110
                    SET nodelft = nodelft - node_width;
111
                END IF;
112
                CALL shift_right(nodelft, node_width, maxrgt);
113
                UPDATE `hierarchy` SET rgt = (rgt - maxrgt) + nodelft WHERE rgt > maxrgt;
114
                UPDATE `hierarchy` SET lft = (lft - maxrgt) + nodelft WHERE lft > maxrgt;
115
            END IF;
116
        END;//
117
DELIMITER ;
    (1-1/1)