DTD identifier | Deterministic | Data access | Mode |
---|---|---|---|
int(11) | NO | READS SQL DATA |
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