Function: nornix_site.add_new_document_as_last_child

Code

Add new document as last child, returns id

CREATE DEFINER=`root`@`localhost` FUNCTION `add_new_document_as_last_child`(vParentNo INTEGER,
 vPathPart VARCHAR(45), vMenuName VARCHAR(80), vPageTitle VARCHAR(80),
 vAuthLevel INTEGER) RETURNS int(11)
    MODIFIES SQL DATA
    COMMENT 'Add new document as last child, returns id'
BEGIN
 DECLARE vParentRgt, vLevel, vMaxRgt INTEGER;
 SELECT Rgt INTO vMaxRgt FROM document WHERE Lft=1;
 SELECT Rgt, `Level` INTO vParentRgt, vLevel FROM document WHERE `No`=vParentNo;
 IF vParentRgt = vMaxRgt THEN
  UPDATE document
   SET Rgt = Rgt + 2
   WHERE Lft = 1;
 ELSE
  UPDATE document
   SET Lft = IF (Lft > vParentRgt, Lft + 2, Lft),
      Rgt = Rgt + 2
   WHERE Rgt >= vParentRgt ORDER BY Rgt DESC;
 END IF;
 INSERT INTO document (`Level`, AuthLevel, MenuName, PageTitle, PathPart, Lft, Rgt)
 VALUES ((vLevel + 1), vAuthLevel, vMenuName, vPageTitle, vPathPart, vParentRgt, (vParentRgt +
1));
 RETURN LAST_INSERT_ID();
END