DELIMITER ;;
CREATE DEFINER=`webdev`@`localhost` PROCEDURE `rebuild_tree`()
BEGIN

DECLARE done INT DEFAULT FALSE;
DECLARE _pageID INT;
DECLARE _parentID INT;

DECLARE curs CURSOR FOR SELECT pageID, parentID FROM Page ORDER BY path ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

DELETE FROM PageClosure WHERE 1;

OPEN curs;

REPEAT
FETCH curs INTO _pageID, _parentID;

IF ! done THEN
INSERT INTO PageClosure (ancestor, descendant)
SELECT ancestor, _pageID
FROM PageClosure
WHERE
descendant = _parentID
UNION ALL
SELECT
_pageID,
_pageID;
END IF;
UNTIL done END REPEAT;

CLOSE curs;

UPDATE PageClosure t
JOIN (SELECT c.descendant, COUNT(c.ancestor) -1 AS `depth` FROM PageClosure c GROUP BY c.descendant ) AS d ON (d.descendant = t.descendant)
SET t.`depth` = d.`depth`;
END;;
DELIMITER ;