Index: openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql,v diff -u -r1.36.2.3 -r1.36.2.4 --- openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 14 Mar 2020 19:01:42 -0000 1.36.2.3 +++ openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 14 Mar 2020 19:03:08 -0000 1.36.2.4 @@ -76,16 +76,16 @@ where component_id = v_object_id_one loop - insert into group_element_index - (group_id, element_id, rel_id, container_id, - rel_type, ancestor_rel_type) - values - (map.group_id, v_object_id_two, new.rel_id, v_object_id_one, - v_rel_type, 'membership_rel'); + insert into group_element_index + (group_id, element_id, rel_id, container_id, + rel_type, ancestor_rel_type) + values + (map.group_id, v_object_id_two, new.rel_id, v_object_id_one, + v_rel_type, 'membership_rel'); - if new.member_state = 'approved' then - perform party_approved_member__add(map.group_id, v_object_id_two, new.rel_id, v_rel_type); - end if; + if new.member_state = 'approved' then + perform party_approved_member__add(map.group_id, v_object_id_two, new.rel_id, v_rel_type); + end if; end loop; end if; @@ -114,8 +114,8 @@ end if; for map in select group_id, element_id, rel_type - from group_element_index - where rel_id = new.rel_id + from group_element_index + where rel_id = new.rel_id loop if new.member_state = 'approved' then perform party_approved_member__add(map.group_id, map.element_id, new.rel_id, map.rel_type); @@ -151,8 +151,8 @@ end if; for map in select group_id, element_id, rel_type - from group_element_index - where rel_id = old.rel_id + from group_element_index + where rel_id = old.rel_id loop perform party_approved_member__remove(map.group_id, map.element_id, old.rel_id, map.rel_type); end loop; @@ -350,20 +350,20 @@ for map in select * - from group_component_map + from group_component_map where group_id in (select group_id - from group_component_map - where component_id = v_object_id_one + from group_component_map + where component_id = v_object_id_one union select v_object_id_one from dual) - and component_id in (select component_id - from group_component_map - where group_id = v_object_id_two + and component_id in (select component_id + from group_component_map + where group_id = v_object_id_two union select v_object_id_two from dual) - and group_contains_p(group_id, component_id, rel_id) = 'f' + and group_contains_p(group_id, component_id, rel_id) = 'f' LOOP delete from group_element_index @@ -459,12 +459,12 @@ -- DECLARE BEGIN - return composition_rel__new(null, - 'composition_rel', - object_id_one, - object_id_two, - null, - null); + return composition_rel__new(null, + 'composition_rel', + object_id_one, + object_id_two, + null, + null); END; $$ LANGUAGE plpgsql; @@ -510,12 +510,12 @@ end if; for row in select r.object_id_one as parent_id - from acs_rels r, composition_rels c - where r.rel_id = c.rel_id - and r.object_id_two = component_id + from acs_rels r, composition_rels c + where r.rel_id = c.rel_id + and r.object_id_two = component_id LOOP if composition_rel__check_path_exists_p(row.parent_id, container_id) = 't' then - return 't'; + return 't'; end if; end loop; @@ -552,56 +552,56 @@ -- GROUP_COMPONENT_INDEX contains the (GROUP_ID, DC.REL_ID, -- CONTAINER_ID) triple. for dc in select r.rel_id, r.object_id_one as container_id - from acs_rels r, composition_rels c - where r.rel_id = c.rel_id - and r.object_id_two = check_index__component_id + from acs_rels r, composition_rels c + where r.rel_id = c.rel_id + and r.object_id_two = check_index__component_id LOOP if composition_rel__check_path_exists_p(dc.container_id, - check_index__container_id) = 't' then - select case when count(*) = 0 then 0 else 1 end into n_rows - from group_component_index - where group_id = check_index__container_id - and component_id = check_index__component_id - and rel_id = dc.rel_id; + check_index__container_id) = 't' then + select case when count(*) = 0 then 0 else 1 end into n_rows + from group_component_index + where group_id = check_index__container_id + and component_id = check_index__component_id + and rel_id = dc.rel_id; - if n_rows = 0 then - result := 'f'; - PERFORM acs_log__error('composition_rel.check_representation', - 'Row missing from group_component_index for (' || - 'group_id = ' || check_index__container_id || ', ' || - 'component_id = ' || check_index__component_id || ', ' || - 'rel_id = ' || dc.rel_id || ')'); - end if; + if n_rows = 0 then + result := 'f'; + PERFORM acs_log__error('composition_rel.check_representation', + 'Row missing from group_component_index for (' || + 'group_id = ' || check_index__container_id || ', ' || + 'component_id = ' || check_index__component_id || ', ' || + 'rel_id = ' || dc.rel_id || ')'); + end if; end if; end loop; -- Loop through all the containers of CONTAINER_ID. for r1 in select r.object_id_one as container_id - from acs_rels r, composition_rels c - where r.rel_id = c.rel_id - and r.object_id_two = check_index__container_id - union - select check_index__container_id as container_id - from dual + from acs_rels r, composition_rels c + where r.rel_id = c.rel_id + and r.object_id_two = check_index__container_id + union + select check_index__container_id as container_id + from dual LOOP -- Loop through all the components of COMPONENT_ID and make a -- recursive call. for r2 in select r.object_id_two as component_id - from acs_rels r, composition_rels c - where r.rel_id = c.rel_id - and r.object_id_one = check_index__component_id - union - select check_index__component_id as component_id - from dual + from acs_rels r, composition_rels c + where r.rel_id = c.rel_id + and r.object_id_one = check_index__component_id + union + select check_index__component_id as component_id + from dual LOOP - if (r1.container_id != check_index__container_id or - r2.component_id != check_index__component_id) and - composition_rel__check_index(r2.component_id, r1.container_id) = 'f' then - result := 'f'; - end if; + if (r1.container_id != check_index__container_id or + r2.component_id != check_index__component_id) and + composition_rel__check_index(r2.component_id, r1.container_id) = 'f' then + result := 'f'; + end if; end loop; end loop; @@ -648,17 +648,17 @@ -- Now let us check that the index doesn't have any extraneous rows -- relating to this relation. for row in select * - from group_component_index - where rel_id = check_representation__rel_id + from group_component_index + where rel_id = check_representation__rel_id LOOP if composition_rel__check_path_exists_p(row.component_id, row.group_id) = 'f' then - result := 'f'; - PERFORM acs_log__error('composition_rel.check_representation', - 'Extraneous row in group_component_index: ' || - 'group_id = ' || row.group_id || ', ' || - 'component_id = ' || row.component_id || ', ' || - 'rel_id = ' || row.rel_id || ', ' || - 'container_id = ' || row.container_id || '.'); + result := 'f'; + PERFORM acs_log__error('composition_rel.check_representation', + 'Extraneous row in group_component_index: ' || + 'group_id = ' || row.group_id || ', ' || + 'component_id = ' || row.component_id || ', ' || + 'rel_id = ' || row.rel_id || ', ' || + 'container_id = ' || row.container_id || '.'); end if; end loop; @@ -732,13 +732,13 @@ -- DECLARE BEGIN - return membership_rel__new(null, - 'membership_rel', - object_id_one, - object_id_two, - 'approved', - null, - null); + return membership_rel__new(null, + 'membership_rel', + object_id_one, + object_id_two, + 'approved', + null, + null); END; $$ LANGUAGE plpgsql; @@ -928,19 +928,19 @@ if n_rows = 0 then result := 'f'; PERFORM acs_log__error('membership_rel.check_representation', - 'Row missing from group_element_index: ' || - 'group_id = ' || check_index__group_id || ', ' || - 'member_id = ' || check_index__member_id || ', ' || - 'container_id = ' || check_index__container_id || '.'); + 'Row missing from group_element_index: ' || + 'group_id = ' || check_index__group_id || ', ' || + 'member_id = ' || check_index__member_id || ', ' || + 'container_id = ' || check_index__container_id || '.'); end if; for row in select r.object_id_one as container_id - from acs_rels r, composition_rels c - where r.rel_id = c.rel_id - and r.object_id_two = check_index__group_id + from acs_rels r, composition_rels c + where r.rel_id = c.rel_id + and r.object_id_two = check_index__group_id LOOP if membership_rel__check_index(row.container_id, check_index__member_id, check_index__container_id) = 'f' then - result := 'f'; + result := 'f'; end if; end loop; @@ -985,17 +985,17 @@ end if; for row in select * - from group_member_index - where rel_id = check_representation__rel_id + from group_member_index + where rel_id = check_representation__rel_id LOOP if composition_rel__check_path_exists_p(row.container_id, - row.group_id) = 'f' then - result := 'f'; - PERFORM acs_log__error('membership_rel.check_representation', - 'Extra row in group_member_index: ' || - 'group_id = ' || row.group_id || ', ' || - 'member_id = ' || row.member_id || ', ' || - 'container_id = ' || row.container_id || '.'); + row.group_id) = 'f' then + result := 'f'; + PERFORM acs_log__error('membership_rel.check_representation', + 'Extra row in group_member_index: ' || + 'group_id = ' || row.group_id || ', ' || + 'member_id = ' || row.member_id || ', ' || + 'container_id = ' || row.container_id || '.'); end if; end loop; @@ -1038,8 +1038,8 @@ BEGIN v_group_id := party__new(new__group_id, new__object_type, new__creation_date, - new__creation_user, new__creation_ip, new__email, - new__url, new__context_id); + new__creation_user, new__creation_ip, new__email, + new__url, new__context_id); v_join_policy := new__join_policy; @@ -1050,11 +1050,11 @@ where group_type = new__object_type; if v_group_type_exists_p = 1 then - select default_join_policy into v_join_policy - from group_types - where group_type = new__object_type; + select default_join_policy into v_join_policy + from group_types + where group_type = new__object_type; else - v_join_policy := 'open'; + v_join_policy := 'open'; end if; end if; @@ -1084,12 +1084,12 @@ ( select distinct g.rel_type from group_type_rels g, ( select parent.object_type as parent_type - from acs_object_types child, acs_object_types parent - where child.object_type <> parent.object_type - and child.tree_sortkey between parent.tree_sortkey - and tree_right(parent.tree_sortkey) - and child.object_type = new__object_type - order by parent.tree_sortkey desc) types + from acs_object_types child, acs_object_types parent + where child.object_type <> parent.object_type + and child.tree_sortkey between parent.tree_sortkey + and tree_right(parent.tree_sortkey) + and child.object_type = new__object_type + order by parent.tree_sortkey desc) types where g.group_type = types.parent_type and not exists ( select 1 from group_rels @@ -1116,16 +1116,16 @@ -- DECLARE BEGIN - return acs_group__new(null, - 'group', - now(), - null, - null, - null, - null, - gname, - null, - null); + return acs_group__new(null, + 'group', + now(), + null, + null, + null, + null, + gname, + null, + null); END; $$ LANGUAGE plpgsql; @@ -1147,18 +1147,18 @@ -- Delete all the relations of any type to this group for row in select r.rel_id, t.package_name - from acs_rels r, acs_object_types t - where r.rel_type = t.object_type - and (r.object_id_one = delete__group_id - or r.object_id_two = delete__group_id) + from acs_rels r, acs_object_types t + where r.rel_type = t.object_type + and (r.object_id_one = delete__group_id + or r.object_id_two = delete__group_id) LOOP execute 'select ' || row.package_name || '__delete(' || row.rel_id || ')'; end loop; -- Delete all segments defined for this group for row in select segment_id - from rel_segments - where group_id = delete__group_id + from rel_segments + where group_id = delete__group_id LOOP PERFORM rel_segment__delete(row.segment_id); end loop; @@ -1215,17 +1215,17 @@ EXISTS ( select 1 from group_member_map where group_id = p_group_id - and member_id = p_party_id + and member_id = p_party_id ) - ELSE + ELSE -- -- Only direct memberships -- EXISTS ( select 1 from acs_rels rels where rels.rel_type = 'membership_rel' - and rels.object_id_one = p_group_id - and rels.object_id_two = p_party_id + and rels.object_id_one = p_group_id + and rels.object_id_two = p_party_id ) END; @@ -1251,40 +1251,40 @@ memb record; BEGIN if group_id is null then - --maybe we should just return 'f' instead? + --maybe we should just return 'f' instead? raise exception 'acs_group__check_representation called with null group_id'; end if; res := 't'; PERFORM acs_log__notice('acs_group.check_representation', - 'Running check_representation on group ' || group_id); + 'Running check_representation on group ' || group_id); if acs_object__check_representation(group_id) = 'f' then res := 'f'; end if; for comp in select c.rel_id - from acs_rels r, composition_rels c - where r.rel_id = c.rel_id - and r.object_id_one = group_id + from acs_rels r, composition_rels c + where r.rel_id = c.rel_id + and r.object_id_one = group_id LOOP if composition_rel__check_representation(comp.rel_id) = 'f' then res := 'f'; end if; end loop; for memb in select m.rel_id - from acs_rels r, membership_rels m - where r.rel_id = m.rel_id - and r.object_id_one = group_id + from acs_rels r, membership_rels m + where r.rel_id = m.rel_id + and r.object_id_one = group_id LOOP if membership_rel__check_representation(memb.rel_id) = 'f' then res := 'f'; end if; end loop; PERFORM acs_log__notice('acs_group.check_representation', - 'Done running check_representation on group ' || group_id); + 'Done running check_representation on group ' || group_id); return res; @@ -1346,13 +1346,13 @@ DECLARE BEGIN return membership_rel__new( - null, -- rel_id - 'admin_rel', -- rel_type - object_id_one, -- object_id_one - object_id_two, -- object_id_two - 'approved', -- member_state - null, -- creation_user - null -- creation_ip + null, -- rel_id + 'admin_rel', -- rel_type + object_id_one, -- object_id_one + object_id_two, -- object_id_two + 'approved', -- member_state + null, -- creation_user + null -- creation_ip ); END; $$ LANGUAGE plpgsql; 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 -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(); -