Buenos días a todos!
Haciendo un pequeño foro, escribí unos procedimientos para administrar los registros de un sistema de arbol anidado (nested set tree). Los dejo abajo como aporte.
Ahora bien, mi idea es modificar esos procedimientos para que sirvan en distintas tablas, pasándoles como variable el nombre de las mismas. El único modo que encontré es el que describen en http://www.forosdelweb.com/f86/ayuda-con-procedimiento-almacenado-parametrizado-753431/
¿Les parece que es una buena idea utilizar un mismo procedimiento para varias tablas?
¿Es posible hacerlo? y si es posible, ¿Cómo es la forma más adecuada de hacerlo?
Desde ya muchas gracias!!!!!
Código SQL:
Ver originalDROP PROCEDURE IF EXISTS insertForum;
DELIMITER GO
CREATE PROCEDURE insertForum(
IN parent SMALLINT,
IN fmType TINYINT(1),
IN fmName VARCHAR(128),
IN fmComments VARCHAR(255),
IN fmStatus TINYINT(1))
BEGIN
DECLARE parentleft, parentright, s SMALLINT DEFAULT 0;
SELECT left_id, right_id INTO parentleft, parentright FROM forums WHERE id = parent;
IF FOUND_ROWS() = 1 THEN
BEGIN
UPDATE forums SET right_id = right_id + 2 WHERE right_id > parentleft;
UPDATE forums SET left_id = left_id + 2 WHERE left_id > parentleft;
INSERT INTO forums (pf_id, left_id, right_id, forum_type, forum_name, comments, STATUS)
VALUES (parent, parentleft + 1, parentleft + 2, fmType, fmName, fmComments, fmStatus);
END;
ELSE
BEGIN
SELECT IF(MAX(right_id),MAX(right_id),0) INTO s FROM forums;
INSERT INTO forums (pf_id, left_id, right_id, forum_type, forum_name, comments, STATUS)
VALUES (parent, s+1, s+2, fmType, fmName, fmComments, fmStatus);
END;
END IF;
BEGIN
SELECT LAST_INSERT_ID() FROM forums;
END;
END;
GO
DELIMITER ;
Código SQL:
Ver originalDROP PROCEDURE IF EXISTS deleteForums;
DELIMITER GO
CREATE PROCEDURE deleteForums( IN node SMALLINT )
BEGIN
DECLARE thisleft, thisright, thiswidth SMALLINT DEFAULT 0;
SELECT left_id, right_id, ( right_id - left_id + 1 ) INTO thisleft, thisright, thiswidth FROM forums WHERE id = node;
DELETE FROM forums WHERE left_id BETWEEN thisleft AND thisright;
UPDATE forums SET left_id = left_id - thiswidth WHERE left_id > thisleft;
UPDATE forums SET right_id = right_id - thiswidth WHERE right_id > thisright;
END;
GO
DELIMITER ;
Código SQL:
Ver originalDROP PROCEDURE IF EXISTS upDownForum;
DELIMITER GO
CREATE PROCEDURE upDownForum( IN node SMALLINT, IN op VARCHAR(4) )
BEGIN
DECLARE leftnode, thisleft, thisright, thiswidth, thisjump, behind_left, behind_right SMALLINT DEFAULT 0;
SELECT left_id, right_id, ( right_id - left_id + 1 ) INTO thisleft, thisright, thiswidth FROM forums WHERE id = node;
SELECT left_id, right_id, IF( op = 'UP', (left_id - thisleft), ( right_id + 1 - thisleft ) ) INTO behind_left, behind_right, thisjump FROM forums WHERE IF( op = 'UP', (right_id = thisleft - 1), (left_id = thisright + 1));
IF FOUND_ROWS() = 1 THEN
IF thisjump > 0 THEN
BEGIN
UPDATE forums SET left_id = left_id + thiswidth WHERE left_id > behind_right;
UPDATE forums SET right_id = right_id + thiswidth WHERE right_id > behind_right;
UPDATE forums SET left_id = left_id + thisjump, right_id = right_id + thisjump WHERE left_id BETWEEN thisleft AND thisright;
UPDATE forums SET left_id = left_id - thiswidth WHERE left_id > thisright;
UPDATE forums SET right_id = right_id - thiswidth WHERE right_id > thisright;
END;
ELSE
BEGIN
UPDATE forums SET left_id = left_id + thiswidth WHERE left_id >= behind_left;
UPDATE forums SET right_id = right_id + thiswidth WHERE right_id >= behind_left;
UPDATE forums SET left_id = left_id + thisjump - thiswidth, right_id = right_id + thisjump - thiswidth WHERE left_id BETWEEN thisleft + thiswidth AND thisright + thiswidth;
UPDATE forums SET left_id = left_id - thiswidth WHERE left_id > thisright;
UPDATE forums SET right_id = right_id - thiswidth WHERE right_id > thisright;
END;
END IF;
END IF;
END;
GO
DELIMITER ;
Nota: el de insertar registro habría que mejorarlo, ya que inserta sólo al comienzo