Rebuild Nested Set Tree

rebuild_nested_set_tree.sql

DROP FUNCTION IF EXISTS rebuild_nested_set_tree//
CREATE FUNCTION rebuild_nested_set_tree()
RETURNS INT DETERMINISTIC MODIFIES SQL DATA
BEGIN
    UPDATE hs_downlines t SET F_LEFT = NULL, F_RIGHT = NULL;
    
    SET @i := 0;
    UPDATE hs_downlines t SET F_LEFT = (@i := @i + 1), F_RIGHT = (@i := @i + 1)
    WHERE t.F_SPONSOR_ID IS NULL;

    forever: LOOP
        SET @parent_id := NULL;
        SELECT t.F_USER_ID, t.F_RIGHT FROM hs_downlines t, hs_downlines tc
        WHERE t.F_USER_ID = tc.F_SPONSOR_ID AND tc.F_LEFT IS NULL AND t.F_RIGHT IS NOT NULL
        ORDER BY t.F_RIGHT LIMIT 1 INTO @parent_id, @parent_right;

        IF @parent_id IS NULL THEN LEAVE forever; END IF;

        SET @current_left := @parent_right;

        SELECT @current_left + COUNT(*) * 2 FROM hs_downlines
        WHERE  	F_SPONSOR_ID 	 = @parent_id INTO @parent_right;

        SET @current_length := @parent_right - @current_left;

        UPDATE hs_downlines t SET F_RIGHT = F_RIGHT + @current_length
        WHERE F_RIGHT >= @current_left ORDER BY F_RIGHT;

        UPDATE hs_downlines t SET F_LEFT = F_LEFT + @current_length
        WHERE F_LEFT > @current_left ORDER BY F_LEFT;

        SET @i := (@current_left - 1);
        UPDATE hs_downlines t SET F_LEFT = (@i := @i + 1), F_RIGHT = (@i := @i + 1)
        WHERE  	F_SPONSOR_ID 	 = @parent_id ORDER BY F_ID;
    END LOOP;

    RETURN (SELECT MAX(F_RIGHT) FROM hs_downlines t);
END//