Function: nornix_site.get_docs_list

Code

Get a list of documents based on authentication level

CREATE DEFINER=`root`@`localhost` FUNCTION `get_docs_list`(vAuthLevel INTEGER) RETURNS int(11)
    READS SQL DATA
    COMMENT 'Get a list of documents based on authentication level'
BEGIN
 DECLARE done, old_level INTEGER DEFAULT 0;
 DECLARE base_url VARCHAR(127) DEFAULT "";
 DECLARE path_part, old_path_part VARCHAR(45) DEFAULT "";
 DECLARE id, current_lft, current_rgt, current_level, find_lft, auth_level INTEGER;
 DECLARE menu_name, page_title VARCHAR(80);
 DECLARE curs CURSOR FOR
  SELECT `No`, PathPart, AuthLevel, MenuName, PageTitle, Lft, Rgt, `Level`
  FROM document
  ORDER BY Lft;
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 DROP TEMPORARY TABLE IF EXISTS nornix_site.tempdoclist;
 CREATE TEMPORARY TABLE IF NOT EXISTS nornix_site.tempdoclist (
  `No`  int(10) unsigned NOT NULL,
  `Level` int(10) unsigned NOT NULL,
  `Path` varchar(127) NOT NULL,
  `AuthLevel` int(10) unsigned NOT NULL,
  `MenuName` varchar(80) NOT NULL,
  `PageTitle` varchar(80) NOT NULL
 ) ENGINE=MEMORY;
 DROP TEMPORARY TABLE IF EXISTS nornix_site.baseurl;
 CREATE TEMPORARY TABLE IF NOT EXISTS nornix_site.baseurl (
  `Level` int(10) unsigned PRIMARY KEY,
  `Path` varchar(127) NOT NULL
 ) ENGINE=MEMORY;
/* loop all documents, skip those with too high authentication level */
 OPEN curs;
 main_loop: LOOP
  FETCH curs INTO id, path_part, auth_level, menu_name, page_title, current_lft, current_rgt,
current_level;
  IF done THEN
   LEAVE main_loop;
  END IF;
  IF auth_level > vAuthLevel THEN
   /* jump to after this branch */
   SET find_lft = current_rgt;
   REPEAT
    FETCH curs INTO id, path_part, auth_level, menu_name, page_title, current_lft, current_rgt,
current_level;
   UNTIL done OR current_lft > find_lft END REPEAT;
   IF done THEN
    LEAVE main_loop;
   END IF;
  END IF;
  /* add to result set */
  IF current_level > old_level THEN
    IF current_level = 1 THEN
     SET base_url = '';
    ELSE
     SET base_url = CONCAT_WS("/", base_url, old_path_part);
    END IF;
    SET old_level = current_level;
    REPLACE INTO nornix_site.baseurl VALUES(current_level, base_url);
  ELSEIF current_level < old_level THEN
    SELECT `Path` INTO base_url FROM nornix_site.baseurl WHERE `Level` = current_level;
    SET old_level = current_level;
  END IF;
  INSERT INTO nornix_site.tempdoclist VALUES (id, current_level, CONCAT_WS("/", base_url,
path_part), auth_level, menu_name, page_title);
  SET old_path_part = path_part;
 END LOOP;
 CLOSE curs;
 RETURN 1;
END