AVvXCj70

1CREATE PROCEDURE `tree_create`() MODIFIES SQL DATA
2BEGIN
3
4  DECLARE currentId, currentParentId CHAR(36);
5  DECLARE currentLeft                INT;
6  DECLARE startId                    INT DEFAULT 1;
7
8  SET max_heap_table_size = 1024 * 1024 * 512;
9
10  START TRANSACTION;
11    DROP TABLE IF EXISTS `tmp_tree`;
12
13    CREATE TABLE `tmp_tree` (
14      `id`     bigint(36) NOT NULL DEFAULT '0',
15      `parent` char(36)   DEFAULT NULL,
16      `lft`    int(11) unsigned DEFAULT NULL,
17      `rgt`    int(11) unsigned DEFAULT NULL,
18      PRIMARY KEY      (`id`),
19      INDEX USING HASH (`parent`),
20      INDEX USING HASH (`lft`),
21      INDEX USING HASH (`rgt`)
22    ) ENGINE = MEMORY
23    SELECT
24      `id`,
25      `parent`,
26      `lft`,
27      `rgt`
28    FROM
29      `tree`;
30
31    UPDATE `tmp_tree` SET `lft`  = NULL, `rgt` = NULL;
32
33    # Establishing starting numbers for all root elements.
34    WHILE EXISTS (SELECT * FROM `tmp_tree` WHERE `parent` = 0 AND `lft` IS NULL AND `rgt` IS NULL LIMIT 1) DO
35
36        UPDATE
37          `tmp_tree`
38        SET
39          `lft` = startId,
40          `rgt` = startId + 1
41        WHERE
42          `parent` = 0
43          AND `lft` IS NULL
44          AND `rgt` IS NULL
45        ORDER BY
46          `id` ASC
47        LIMIT 1;
48
49        SET startId = startId + 2;
50
51    END WHILE;
52
53    # Switching the indexes for the lft/rgt columns to B-Trees to speed up the next section, which uses range queries.
54    DROP INDEX `lft`  ON `tmp_tree`;
55    DROP INDEX `rgt` ON `tmp_tree`;
56    CREATE INDEX `lft`  USING BTREE ON `tmp_tree` (`lft`);
57    CREATE INDEX `rgt` USING BTREE ON `tmp_tree` (`rgt`);
58
59    # Numbering all child elements
60    WHILE EXISTS (SELECT * FROM `tmp_tree` WHERE `lft` IS NULL LIMIT 1) DO
61
62        # Picking an unprocessed element which has a processed parent.
63        SELECT
64          `tmp_tree`.`id` INTO currentId
65        FROM
66          `tmp_tree`
67        INNER JOIN `tmp_tree` AS `parents` ON `tmp_tree`.`parent` = `parents`.`id`
68        WHERE
69          `tmp_tree`.`lft` IS NULL
70          AND `parents`.`lft` IS NOT NULL
71        ORDER BY
72          `tmp_tree`.`id` DESC
73        LIMIT 1;
74
75        # Finding the element's parent.
76        SELECT
77          `parent` INTO currentParentId
78        FROM
79          `tmp_tree`
80        WHERE
81          `id` = currentId;
82
83        # Finding the parent's lft value.
84        SELECT
85          `lft` INTO currentLeft
86        FROM
87          `tmp_tree`
88        WHERE
89          `id` = currentParentId;
90
91        # Shifting all elements to the right of the current element 2 to the right.
92        UPDATE
93          `tmp_tree`
94        SET
95          `rgt` = `rgt` + 2
96        WHERE
97          `rgt` > currentLeft;
98
99        UPDATE
100          `tmp_tree`
101        SET
102          `lft` = `lft` + 2
103        WHERE
104          `lft` > currentLeft;
105
106        # Setting lft and rgt values for current element.
107        UPDATE
108          `tmp_tree`
109        SET
110          `lft` = currentLeft + 1,
111          `rgt` = currentLeft + 2
112        WHERE
113          `id` = currentId;
114
115    END WHILE;
116
117    # Writing calculated values back to physical table.
118    UPDATE
119      `tree`, `tmp_tree`
120    SET
121      `tree`.`lft` = `tmp_tree`.`lft`,
122      `tree`.`rgt` = `tmp_tree`.`rgt`
123    WHERE
124      `tree`.`id` = `tmp_tree`.`id`;
125
126    COMMIT;
127
128    DROP TABLE `tmp_tree`;
129
130END