1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
| CREATE TABLE Tree
(node CHAR(10) NOT NULL,
parent CHAR(10));
CREATE TABLE Stack
(stack_top INTEGER NOT NULL,
node CHAR(10) NOT NULL,
lft INTEGER,
rgt INTEGER);
DELIMITER |
CREATE PROCEDURE AdjToNested()
LANGUAGE SQL
DETERMINISTIC
BEGIN ATOMIC
DECLARE lft_rgt INTEGER;
DECLARE max_lft_rgt INTEGER;
DECLARE current_top INTEGER;
SET lft_rgt = 2;
SET max_lft_rgt = 2 * (SELECT COUNT(*) FROM Tree);
SET current_top = 1;
DELETE FROM Stack;
INSERT INTO Stack
SELECT 1, node, 1, max_lft_rgt
FROM Tree
WHERE parent IS NULL;
DELETE FROM Tree WHERE parent IS NULL;
WHILE lft_rgt <= max_lft_rgt - 1
DO IF EXISTS (SELECT *
FROM Stack AS S1, Tree AS T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top)
THEN BEGIN
INSERT INTO Stack
SELECT (current_top + 1), MIN(T1.node), lft_rgt, NULL
FROM Stack AS S1, Tree AS T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
DELETE FROM Tree
WHERE node = (SELECT node
FROM Stack
WHERE stack_top = current_top + 1);
SET lft_rgt = lft_rgt + 1;
SET current_top = current_top + 1;
END;
ELSE BEGIN
UPDATE Stack
SET rgt = lft_rgt,
stack_top = - stack_top
WHERE stack_top = current_top;
SET lft_rgt = lft_rgt + 1;
SET current_top = current_top - 1;
END;
END IF;
END WHILE;
IF EXISTS (SELECT * FROM Tree)
THEN « error handling for orphans in original tree »
END IF;
END|
DELIMITER; |
Partager