Outer Stored Procedure for Building Nested Set From Scratch

nest_rebuild.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS `nest_generic_rebuild` $$
CREATE PROCEDURE `nest_generic_rebuild` ()
MODIFIES SQL DATA
BEGIN
DECLARE counter INT DEFAULT 0;
DECLARE nest_depth INT DEFAULT 1;
DECLARE my_left_id INT;
DECLARE my_nest_depth INT;
DECLARE no_more_records INT DEFAULT FALSE;
DECLARE sub_id INT;
DECLARE top_tier CURSOR FOR SELECT nl.id 
   FROM nest_rebuild nl
   LEFT JOIN nest_rebuild nr ON nr.id = nl.super_id
  WHERE nl.super_id = nl.id  /* I am my own superior */
     OR nl.super_id IS NULL  /* I have no superior */
     OR nl.super_id = 0      /* I have even zero is better than me */
     OR nr.id IS NULL;       /* I work for Santa or something */

DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SET no_more_records = TRUE; END;

UPDATE nest_rebuild SET left_id = NULL, right_id = NULL, nest_depth = NULL;

SELECT 'hierarchy has been nulled out' AS debug;

PREPARE update_lrd FROM 'UPDATE nest_rebuild SET left_id = ?, right_id = ?, nest_depth = ? WHERE id = ?';

SELECT 'update statement is prepared' AS debug;

OPEN top_tier;

SELECT 'cursor is open' AS debug;

froot_loop: LOOP
    FETCH NEXT FROM top_tier INTO sub_id;
    IF no_more_records = TRUE THEN
        LEAVE froot_loop;
    END IF;

    SELECT 'top_tier',counter,nest_depth,sub_id;
    CALL nest_generic_recurse(sub_id, nest_depth, counter);

END LOOP;

DEALLOCATE PREPARE update_lrd;
END $$

DELIMITER ;