Procedure: nornix_site.get_document_sub

Code

Subroutine for fetching one document

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_document_sub`(IN vPath VARCHAR(127))
    READS SQL DATA
    COMMENT 'Subroutine for fetching one document'
BEGIN
 DECLARE number, current_lft, current_rgt, current_level, auth_level INTEGER;
 DECLARE done INT DEFAULT 0;
 DECLARE path_part VARCHAR(127);
 DECLARE title VARCHAR(80);
 DECLARE curs CURSOR FOR SELECT `part` FROM nornix_site.splitstring ORDER BY `pk` ASC;
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 SET @tempdocpath=vPath;
 DROP TEMPORARY TABLE IF EXISTS nornix_site.tempdoc;
 CREATE TEMPORARY TABLE nornix_site.tempdoc (
  `No` int(10) unsigned NOT NULL,
  `Level` int(10) unsigned NOT NULL,
  `AuthLevel` int(10) unsigned NOT NULL,
  `PageTitle` varchar(80) NOT NULL,
  PRIMARY KEY  (`Level`)
 ) ENGINE=MEMORY;
 SELECT No, AuthLevel, PageTitle, Lft, Rgt, Level
 INTO number, auth_level, title, current_lft, current_rgt, current_level
  FROM document
  WHERE Lft=1;
 IF NOT done THEN
  INSERT INTO nornix_site.tempdoc VALUES (number, current_level, auth_level, title);
 END IF;
 CALL split_string(vPath, "/");
 OPEN curs;
 REPEAT
  FETCH curs INTO path_part;
  IF NOT done THEN
    SELECT No, AuthLevel, PageTitle, Lft, Rgt, Level
     INTO number, auth_level, title, current_lft, current_rgt, current_level
     FROM document
     WHERE Lft>current_lft AND Rgt<current_rgt
     AND Level=current_level+1
     AND PathPart=path_part;
    IF NOT done THEN
     INSERT INTO nornix_site.tempdoc VALUES (number, current_level, auth_level, title);
    END IF;
  END IF;
 UNTIL done END REPEAT;
 CLOSE curs;
END