salix/db/routines/vn/procedures/balanceNestTree_addChild.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`balanceNestTree_addChild`(
	vSelf INT,
	vName VARCHAR(45)
)
BEGIN
/**
 * Agrega un nuevo nodo hijo a un nodo existente dentro de la estructura
 * de árbol de vn.balanceNestTree.
 *
 * @param vSelf Identificador del nodo
 * @param vName Nombre del nuevo nodo hijo
 */
	DECLARE vTable VARCHAR(45) DEFAULT util.quoteIdentifier('balanceNestTree');
	DECLARE vLeft INT;

	CREATE OR REPLACE TEMPORARY TABLE tAux
		SELECT 0 lft;

	EXECUTE IMMEDIATE CONCAT(
		'UPDATE tAux 
			SET lft = (SELECT lft 
				FROM ', vTable, 
				' WHERE id = ?)')
		USING vSelf;
	
	SELECT lft INTO vLeft FROM tAux;

	EXECUTE IMMEDIATE CONCAT(
		'UPDATE ', vTable, ' 
			SET rgt = rgt + 2 
			WHERE rgt > ? 
			ORDER BY rgt DESC')
		USING vLeft;
	
	EXECUTE IMMEDIATE CONCAT(
		'UPDATE ', vTable, ' 
			SET lft = lft + 2
			WHERE lft > ? 
			ORDER BY lft DESC')
		USING vLeft;
	
	EXECUTE IMMEDIATE CONCAT(
		'INSERT INTO ', vTable, ' (name, lft, rgt)
			VALUES(?, ? + 1, ? + 2)')
		USING vName,
			vLeft,
			vLeft;

	DROP TEMPORARY TABLE tAux;
END$$
DELIMITER ;