Function: nornix_site.get_docs_list_part

Information
DTD identifierDeterministicData accessMode
int(11)NOREADS SQL DATA

Code

Get a filtered list of documents based on authentication level

CREATE DEFINER=`root`@`localhost` FUNCTION `get_docs_list_part`(vAuthLevel INTEGER, vRootDocNo
INTEGER, vLevels INTEGER) RETURNS int(11)
    READS SQL DATA
    COMMENT 'Get a filtered 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 current_lft, current_rgt, current_level, limit_lft, limit_rgt, limit_level, find_lft,
auth_level, id INTEGER;
 DECLARE menu_name, page_title VARCHAR(80);
 DECLARE curs CURSOR FOR
  SELECT `No`, PathPart, AuthLevel, MenuName, PageTitle, Lft, Rgt, `Level`
  FROM document
  WHERE Lft > limit_lft AND Rgt < limit_rgt AND `Level` < limit_level
  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;
/* prepare limits and base url */
 SELECT `No`, PathPart, AuthLevel, MenuName, PageTitle, Lft, Rgt, `Level`,
 Lft, Rgt, `Level` + vLevels + 1, `Level` + 1
 INTO id, path_part, auth_level, menu_name, page_title, current_lft, current_rgt, current_level,
 limit_lft, limit_rgt, limit_level, old_level
 FROM document WHERE `No`= vRootDocNo;
 SET base_url = find_doc_path(vRootDocNo);
 IF (base_url = '/') THEN
   SET base_url = '';
 END IF;
 REPLACE INTO nornix_site.baseurl VALUES(old_level, base_url);
 INSERT INTO nornix_site.tempdoclist
 VALUES (id, current_level, CONCAT_WS("/", base_url, path_part), auth_level, menu_name,
page_title);
/* 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