Index: openacs-4/packages/acs-kernel/sql/postgresql/site-nodes-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/site-nodes-create.sql,v diff -u -N -r1.30.2.2 -r1.30.2.3 --- openacs-4/packages/acs-kernel/sql/postgresql/site-nodes-create.sql 14 Mar 2020 19:01:42 -0000 1.30.2.2 +++ openacs-4/packages/acs-kernel/sql/postgresql/site-nodes-create.sql 14 Mar 2020 19:03:08 -0000 1.30.2.3 @@ -14,7 +14,7 @@ ) RETURNS integer AS $$ DECLARE - dummy integer; + dummy integer; BEGIN PERFORM acs_object_type__create_type ( 'site_node', @@ -41,33 +41,33 @@ -- This table allows urls to be mapped to a node_ids. create table site_nodes ( - node_id integer constraint site_nodes_node_id_fk - references acs_objects (object_id) - constraint site_nodes_node_id_pk - primary key, - parent_id integer constraint site_nodes_parent_id_fk - references site_nodes (node_id), - name text - constraint site_nodes_name_ck - check (name not like '%/%'), - constraint site_nodes_un - unique (parent_id, name), - -- Is it legal to create a child node? - directory_p boolean not null, - -- Should urls that are logical children of this node be - -- mapped to this node? - pattern_p boolean default false not null, - object_id integer constraint site_nodes_object_id_fk - references acs_objects (object_id) + node_id integer constraint site_nodes_node_id_fk + references acs_objects (object_id) + constraint site_nodes_node_id_pk + primary key, + parent_id integer constraint site_nodes_parent_id_fk + references site_nodes (node_id), + name text + constraint site_nodes_name_ck + check (name not like '%/%'), + constraint site_nodes_un + unique (parent_id, name), + -- Is it legal to create a child node? + directory_p boolean not null, + -- Should urls that are logical children of this node be + -- mapped to this node? + pattern_p boolean default false not null, + object_id integer constraint site_nodes_object_id_fk + references acs_objects (object_id) ); create index site_nodes_object_id_idx on site_nodes (object_id); create index site_nodes_parent_object_node_id_idx on site_nodes(parent_id, object_id, node_id); create index site_nodes_parent_id_idx on site_nodes(parent_id); --- +-- -- procedure site_node__new/8 --- +-- select define_function_args('site_node__new','node_id;null,parent_id;null,name,object_id;null,directory_p,pattern_p;f,creation_user;null,creation_ip;null'); @@ -93,7 +93,7 @@ where node_id = new__parent_id; if v_directory_p = 'f' then - raise EXCEPTION '-20000: Node % is not a directory', new__parent_id; + raise EXCEPTION '-20000: Node % is not a directory', new__parent_id; end if; end if; @@ -116,7 +116,7 @@ new__directory_p, new__pattern_p); return v_node_id; - + END; $$ LANGUAGE plpgsql; @@ -140,7 +140,7 @@ PERFORM acs_object__delete(delete__node_id); - return 0; + return 0; END; $$ LANGUAGE plpgsql; @@ -163,7 +163,7 @@ BEGIN if find_pattern__node_id is null then -- raise no_data_found; - raise exception 'NO DATA FOUND'; + raise exception 'NO DATA FOUND'; end if; select pattern_p, parent_id into v_pattern_p, v_parent_id @@ -175,7 +175,7 @@ else return site_node__find_pattern(v_parent_id); end if; - + END; $$ LANGUAGE plpgsql; @@ -192,12 +192,12 @@ DECLARE v_pos integer; v_first site_nodes.name%TYPE; - v_rest text; - v_node_id integer; + v_rest text; + v_node_id integer; v_pattern_p site_nodes.pattern_p%TYPE; - v_url text; + v_url text; v_directory_p site_nodes.directory_p%TYPE; - v_trailing_slash_p boolean; + v_trailing_slash_p boolean; BEGIN v_url := p_url; @@ -211,7 +211,7 @@ end if; -- - -- Split the URL on the first "/" into v_first and v_rest. + -- Split the URL on the first "/" into v_first and v_rest. -- select position('/' in v_url) into v_pos; @@ -229,7 +229,7 @@ v_rest := substr(v_url, v_pos + 1); end if; - if p_parent_id is not null then + if p_parent_id is not null then select node_id, directory_p into v_node_id, v_directory_p from site_nodes where parent_id = p_parent_id @@ -240,16 +240,16 @@ -- select node_id, directory_p into v_node_id, v_directory_p from site_nodes - where parent_id is null + where parent_id is null and name = v_first; end if; - if NOT FOUND then - return site_node__find_pattern(p_parent_id); + if NOT FOUND then + return site_node__find_pattern(p_parent_id); end if; -- - -- v_first was found. + -- v_first was found. -- if v_rest is null then -- @@ -258,9 +258,9 @@ -- find_pattern(). Otherwise, return the found node_id -- if v_trailing_slash_p is true and v_directory_p is false then - return site_node__find_pattern(p_parent_id); + return site_node__find_pattern(p_parent_id); else - return v_node_id; + return v_node_id; end if; else -- @@ -285,63 +285,62 @@ raise notice 'starting site-nodes doing the recursive part -- vguerra'; IF cmp_pg_version('8.4') >= 0 THEN - -- recursive site_nodes recursive - START + -- recursive site_nodes recursive - START - CREATE OR REPLACE FUNCTION site_node__url( - url__node_id integer - ) RETURNS varchar AS $$ + CREATE OR REPLACE FUNCTION site_node__url( + url__node_id integer + ) RETURNS varchar AS $$ - WITH RECURSIVE site_nodes_path(parent_id, path, directory_p, node_id) as ( - - select parent_id, ARRAY[name || case when directory_p then '/' else ' ' end]::text[] as path, directory_p, node_id - from site_nodes where node_id = url__node_id - - UNION ALL - - select sn.parent_id, sn.name::text || snr.path , sn.directory_p, snr.parent_id - from site_nodes sn join site_nodes_path snr on sn.node_id = snr.parent_id - where snr.parent_id is not null + WITH RECURSIVE site_nodes_path(parent_id, path, directory_p, node_id) as ( - ) select array_to_string(path,'/') from site_nodes_path where parent_id is null + select parent_id, ARRAY[name || case when directory_p then '/' else ' ' end]::text[] as path, directory_p, node_id + from site_nodes where node_id = url__node_id - $$ LANGUAGE sql strict stable; + UNION ALL - -- recursive site_nodes END - + select sn.parent_id, sn.name::text || snr.path , sn.directory_p, snr.parent_id + from site_nodes sn join site_nodes_path snr on sn.node_id = snr.parent_id + where snr.parent_id is not null + + ) select array_to_string(path,'/') from site_nodes_path where parent_id is null + + $$ LANGUAGE sql strict stable; + + -- recursive site_nodes END + ELSE - CREATE OR REPLACE FUNCTION site_node__url( - url__node_id integer - ) RETURNS varchar AS $$ - DECLARE - v_parent_id site_nodes.node_id%TYPE; - v_name site_nodes.name%TYPE; - v_directory_p site_nodes.directory_p%TYPE; - BEGIN - if url__node_id is null then - return ''; - end if; + CREATE OR REPLACE FUNCTION site_node__url( + url__node_id integer + ) RETURNS varchar AS $$ + DECLARE + v_parent_id site_nodes.node_id%TYPE; + v_name site_nodes.name%TYPE; + v_directory_p site_nodes.directory_p%TYPE; + BEGIN + if url__node_id is null then + return ''; + end if; - select parent_id, name, directory_p into - v_parent_id, v_name, v_directory_p - from site_nodes - where node_id = url__node_id; + select parent_id, name, directory_p into + v_parent_id, v_name, v_directory_p + from site_nodes + where node_id = url__node_id; - if v_directory_p = 't' then - return site_node__url(v_parent_id) || v_name || '/'; - else - return site_node__url(v_parent_id) || v_name; - end if; - - END; - $$ LANGUAGE plpgsql; + if v_directory_p = 't' then + return site_node__url(v_parent_id) || v_name || '/'; + else + return site_node__url(v_parent_id) || v_name; + end if; + END; + $$ LANGUAGE plpgsql; + END IF; - + return null; -END; +END; $inline_0$ LANGUAGE plpgsql; select inline_0(); drop function inline_0(); -