Procedure: nornix_site.delete_document_tree

Code

Delete subtree of documents

CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_document_tree`(IN vDocumentNo INTEGER)
    MODIFIES SQL DATA
    COMMENT 'Delete subtree of documents'
BEGIN /* TODO: does not remove the root document of the tree!!+ does not remove contents, maybe
corrupts tree */
 DECLARE vDocLft, vDocRgt, vGapSize INTEGER;
 SELECT Lft, Rgt INTO vDocLft, vDocRgt
 FROM document
 WHERE `No` = vDocumentNo;
 DELETE FROM document
 WHERE Lft BETWEEN vDocLft AND vDocRgt;
 SET vGapSize = vDocRgt - vDocLft + 1;
 UPDATE document
  SET Lft = Lft - vGapSize
  WHERE Lft > vDocLft
  ORDER BY Lft ASC;
 UPDATE document
  SET Rgt = Rgt - vGapSize
  WHERE Rgt > vDocLft
  ORDER BY Rgt ASC;
 SET @tempdocpath='';
END