Index: openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql,v diff -u -r1.3.4.3 -r1.3.4.4 --- openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql 16 Feb 2003 02:15:36 -0000 1.3.4.3 +++ openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql 24 Mar 2003 20:30:57 -0000 1.3.4.4 @@ -143,6 +143,18 @@ (v_object_id_one, v_object_id_two, :new.rel_id, v_object_id_one, v_rel_type, 'composition_rel'); + for members in (select distinct member_id, rel_type + from group_approved_member_map m + where group_id = v_object_id_two + and not exists (select 1 + from group_element_map + where group_id = v_object_id_one + and element_id = m.member_id + and rel_id = m.rel_id)) + loop + party_approved_member.add(v_object_id_one, members.member_id, members.rel_type); + end loop; + -- Make my elements be elements of my new composite group insert into group_element_index (group_id, element_id, rel_id, container_id, @@ -173,6 +185,19 @@ v_rel_type, 'composition_rel'); -- Add rows for my elements + + for members in (select distinct member_id, rel_type + from group_approved_member_map m + where group_id = v_object_id_two + and not exists (select 1 + from group_element_map + where group_id = map.group_id + and element_id = m.member_id + and rel_id = m.rel_id)) + loop + party_approved_member.add(map.group_id, members.member_id, members.rel_type); + end loop; + insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) @@ -228,6 +253,15 @@ and component_id = map.component_id; if n_rows = 0 then + + for members in (select member_id, rel_type + from group_approved_member_map + where group_id = map.group_id + and container_id = map.component_id) + loop + party_approved_member.remove(map.group_id, members.member_id, members.rel_type); + end loop; + delete from group_element_index where group_id = map.group_id and container_id = map.component_id @@ -236,7 +270,6 @@ end loop; - for map in (select * from group_component_map where group_id in (select group_id @@ -264,18 +297,27 @@ and component_id = map.component_id; if n_rows = 0 then + + for members in (select member_id, rel_type + from group_approved_member_map + where group_id = map.group_id + and container_id = map.component_id) + loop + party_approved_member.remove(map.group_id, members.member_id, members.rel_type); + end loop; + delete from group_element_index where group_id = map.group_id and container_id = map.component_id and ancestor_rel_type = 'membership_rel'; + end if; end loop; end; / show errors - -------------------- -- PACKAGE BODIES -- -------------------- Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6.1-4.6.2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6.1-4.6.2.sql,v diff -u -r1.1.2.1 -r1.1.2.2 --- openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6.1-4.6.2.sql 5 Mar 2003 14:38:00 -0000 1.1.2.1 +++ openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6.1-4.6.2.sql 24 Mar 2003 20:31:42 -0000 1.1.2.2 @@ -674,3 +674,214 @@ end apm_package_version; / show errors + +------------------------------------------------------------------------------------------------ +-- DRB: composition_rel triggers for permission expansion failed to find its way into 4.6.1. +-- I'm not providing code to correct party_approved_member_map because this trigger's only +-- important when a composition_rel is added to groups which already have members, something +-- existing code doesn't do. + +create or replace trigger composition_rels_in_tr +after insert on composition_rels +for each row +declare + v_object_id_one acs_rels.object_id_one%TYPE; + v_object_id_two acs_rels.object_id_two%TYPE; + v_rel_type acs_rels.rel_type%TYPE; + v_error varchar2(4000); +begin + + -- First check if added this relation violated any relational constraints + v_error := rel_constraint.violation(:new.rel_id); + if v_error is not null then + raise_application_error(-20000,v_error); + end if; + + select object_id_one, object_id_two, rel_type + into v_object_id_one, v_object_id_two, v_rel_type + from acs_rels + where rel_id = :new.rel_id; + + -- Insert a row for me in group_element_index + insert into group_element_index + (group_id, element_id, rel_id, container_id, + rel_type, ancestor_rel_type) + values + (v_object_id_one, v_object_id_two, :new.rel_id, v_object_id_one, + v_rel_type, 'composition_rel'); + + for members in (select distinct member_id, rel_type + from group_approved_member_map m + where group_id = v_object_id_two + and not exists (select 1 + from group_element_map + where group_id = v_object_id_one + and element_id = m.member_id + and rel_id = m.rel_id)) + loop + party_approved_member.add(v_object_id_one, members.member_id, members.rel_type); + end loop; + + -- Make my elements be elements of my new composite group + insert into group_element_index + (group_id, element_id, rel_id, container_id, + rel_type, ancestor_rel_type) + select distinct + v_object_id_one, element_id, rel_id, container_id, + rel_type, ancestor_rel_type + from group_element_map m + where group_id = v_object_id_two + and not exists (select 1 + from group_element_map + where group_id = v_object_id_one + and element_id = m.element_id + and rel_id = m.rel_id); + + -- For all direct or indirect containers of my new composite group, + -- add me and add my elements + for map in (select distinct group_id + from group_component_map + where component_id = v_object_id_one) loop + + -- Add a row for me + 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, 'composition_rel'); + + -- Add rows for my elements + + for members in (select distinct member_id, rel_type + from group_approved_member_map m + where group_id = v_object_id_two + and not exists (select 1 + from group_element_map + where group_id = map.group_id + and element_id = m.member_id + and rel_id = m.rel_id)) + loop + party_approved_member.add(map.group_id, members.member_id, members.rel_type); + end loop; + + insert into group_element_index + (group_id, element_id, rel_id, container_id, + rel_type, ancestor_rel_type) + select distinct + map.group_id, element_id, rel_id, container_id, + rel_type, ancestor_rel_type + from group_element_map m + where group_id = v_object_id_two + and not exists (select 1 + from group_element_map + where group_id = map.group_id + and element_id = m.element_id + and rel_id = m.rel_id); + end loop; + +end; +/ +show errors + +-- +-- TO DO: See if this can be optimized now that the member and component +-- mapping tables have been combined +-- +create or replace trigger composition_rels_del_tr +before delete on composition_rels +for each row +declare + v_object_id_one acs_rels.object_id_one%TYPE; + v_object_id_two acs_rels.object_id_two%TYPE; + n_rows integer; + v_error varchar2(4000); +begin + -- First check if removing this relation would violate any relational constraints + v_error := rel_constraint.violation_if_removed(:old.rel_id); + if v_error is not null then + raise_application_error(-20000,v_error); + end if; + + select object_id_one, object_id_two into v_object_id_one, v_object_id_two + from acs_rels + where rel_id = :old.rel_id; + + for map in (select * + from group_component_map + where rel_id = :old.rel_id) loop + + delete from group_element_index + where rel_id = :old.rel_id; + + select count(*) into n_rows + from group_component_map + where group_id = map.group_id + and component_id = map.component_id; + + if n_rows = 0 then + + for members in (select member_id, rel_type + from group_approved_member_map + where group_id = map.group_id + and container_id = map.component_id) + loop + party_approved_member.remove(map.group_id, members.member_id, members.rel_type); + end loop; + + delete from group_element_index + where group_id = map.group_id + and container_id = map.component_id + and ancestor_rel_type = 'membership_rel'; + end if; + + end loop; + + for map in (select * + from group_component_map + where group_id in (select group_id + 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 + union + select v_object_id_two + from dual) + and group_contains_p(group_id, component_id, rel_id) = 'f') loop + + delete from group_element_index + where group_id = map.group_id + and element_id = map.component_id + and rel_id = map.rel_id; + + select count(*) into n_rows + from group_component_map + where group_id = map.group_id + and component_id = map.component_id; + + if n_rows = 0 then + + for members in (select member_id, rel_type + from group_approved_member_map + where group_id = map.group_id + and container_id = map.component_id) + loop + party_approved_member.remove(map.group_id, members.member_id, members.rel_type); + end loop; + + delete from group_element_index + where group_id = map.group_id + and container_id = map.component_id + and ancestor_rel_type = 'membership_rel'; + + end if; + + end loop; +end; +/ +show errors + 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.16.2.7 -r1.16.2.8 --- openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 12 Mar 2003 02:31:42 -0000 1.16.2.7 +++ openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 24 Mar 2003 20:32:10 -0000 1.16.2.8 @@ -172,6 +172,17 @@ (v_object_id_one, v_object_id_two, new.rel_id, v_object_id_one, v_rel_type, ''composition_rel''); + -- Add to the denormalized party_approved_member_map + + perform party_approved_member__add(v_object_id_one, member_id, rel_id, rel_type) + from group_approved_member_map m + where group_id = v_object_id_two + and not exists (select 1 + from group_element_map + where group_id = v_object_id_one + and element_id = m.member_id + and rel_id = m.rel_id); + -- Make my elements be elements of my new composite group insert into group_element_index (group_id, element_id, rel_id, container_id, @@ -195,14 +206,27 @@ LOOP -- Add a row for me + 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, ''composition_rel''); + -- Add to party_approved_member_map + + perform party_approved_member__add(map.group_id, member_id, rel_id, rel_type) + from group_approved_member_map m + where group_id = v_object_id_two + and not exists (select 1 + from group_element_map + where group_id = map.group_id + and element_id = m.member_id + and rel_id = m.rel_id); + -- Add rows for my elements + insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) @@ -229,6 +253,7 @@ -- TO DO: See if this can be optimized now that the member and component -- mapping tables have been combined -- + create or replace function composition_rels_del_tr () returns opaque as ' declare v_object_id_one acs_rels.object_id_one%TYPE; @@ -261,6 +286,12 @@ and component_id = map.component_id; if n_rows = 0 then + + perform party_approved_member__remove(map.group_id, member_id, rel_id, rel_type) + from group_approved_member_map + where group_id = map.group_id + and container_id = map.component_id; + delete from group_element_index where group_id = map.group_id and container_id = map.component_id @@ -298,11 +329,17 @@ and component_id = map.component_id; if n_rows = 0 then + end if; + + perform party_approved_member__remove(map.group_id, member_id, rel_id, rel_type) + from group_approved_member_map + where group_id = map.group_id + and container_id = map.component_id; + delete from group_element_index where group_id = map.group_id and container_id = map.component_id and ancestor_rel_type = ''membership_rel''; - end if; end loop; @@ -313,9 +350,6 @@ create trigger composition_rels_del_tr before delete on composition_rels for each row execute procedure composition_rels_del_tr (); --- show errors - - -------------------- -- PACKAGE BODIES -- -------------------- Index: openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql,v diff -u -r1.3.4.4 -r1.3.4.5 --- openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 26 Feb 2003 02:23:24 -0000 1.3.4.4 +++ openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 24 Mar 2003 20:32:10 -0000 1.3.4.5 @@ -205,7 +205,7 @@ perform party_approved_member__add_one(p_party_id, p_member_id, p_rel_id); - -- if the relation type is mapped to relational segments unmap them too + -- if the relation type is mapped to relational segments map them too for v_segments in select segment_id from rel_segments s, acs_object_types o1, acs_object_types o2 Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql,v diff -u -r1.1.2.4 -r1.1.2.5 --- openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql 23 Mar 2003 02:03:34 -0000 1.1.2.4 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql 24 Mar 2003 20:32:36 -0000 1.1.2.5 @@ -317,4 +317,221 @@ on acs_privilege_hierarchy for each row execute procedure acs_priv_hier_ins_del_tr (); +------------------------------------------------------------------------------------------------ +-- DRB: composition_rel triggers for permission expansion failed to find its way into 4.6.1. +-- I'm not providing code to correct party_approved_member_map because this trigger's only +-- important when a composition_rel is added to groups which already have members, something +-- existing code doesn't do. +drop trigger composition_rels_in_tr on composition_rels; +drop function composition_rels_in_tr (); + +create or replace function composition_rels_in_tr () returns opaque as ' +declare + v_object_id_one acs_rels.object_id_one%TYPE; + v_object_id_two acs_rels.object_id_two%TYPE; + v_rel_type acs_rels.rel_type%TYPE; + v_error text; + map record; +begin + + -- First check if added this relation violated any relational constraints + v_error := rel_constraint__violation(new.rel_id); + + if v_error is not null then + raise EXCEPTION ''-20000: %'', v_error; + end if; + + select object_id_one, object_id_two, rel_type + into v_object_id_one, v_object_id_two, v_rel_type + from acs_rels + where rel_id = new.rel_id; + + -- Insert a row for me in group_element_index + insert into group_element_index + (group_id, element_id, rel_id, container_id, + rel_type, ancestor_rel_type) + values + (v_object_id_one, v_object_id_two, new.rel_id, v_object_id_one, + v_rel_type, ''composition_rel''); + + -- Add to the denormalized party_approved_member_map + + perform party_approved_member__add(v_object_id_one, member_id, rel_id, rel_type) + from group_approved_member_map m + where group_id = v_object_id_two + and not exists (select 1 + from group_element_map + where group_id = v_object_id_one + and element_id = m.member_id + and rel_id = m.rel_id); + + -- Make my elements be elements of my new composite group + insert into group_element_index + (group_id, element_id, rel_id, container_id, + rel_type, ancestor_rel_type) + select distinct + v_object_id_one, element_id, rel_id, container_id, + rel_type, ancestor_rel_type + from group_element_map m + where group_id = v_object_id_two + and not exists (select 1 + from group_element_map + where group_id = v_object_id_one + and element_id = m.element_id + and rel_id = m.rel_id); + + -- For all direct or indirect containers of my new composite group, + -- add me and add my elements + for map in select distinct group_id + from group_component_map + where component_id = v_object_id_one + LOOP + + -- Add a row for me + + 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, ''composition_rel''); + + -- Add to party_approved_member_map + + perform party_approved_member__add(map.group_id, member_id, rel_id, rel_type) + from group_approved_member_map m + where group_id = v_object_id_two + and not exists (select 1 + from group_element_map + where group_id = map.group_id + and element_id = m.member_id + and rel_id = m.rel_id); + + -- Add rows for my elements + + insert into group_element_index + (group_id, element_id, rel_id, container_id, + rel_type, ancestor_rel_type) + select distinct + map.group_id, element_id, rel_id, container_id, + rel_type, ancestor_rel_type + from group_element_map m + where group_id = v_object_id_two + and not exists (select 1 + from group_element_map + where group_id = map.group_id + and element_id = m.element_id + and rel_id = m.rel_id); + end loop; + + return new; + +end;' language 'plpgsql'; + +create trigger composition_rels_in_tr after insert on composition_rels +for each row execute procedure composition_rels_in_tr (); + +-- +-- TO DO: See if this can be optimized now that the member and component +-- mapping tables have been combined +-- +drop trigger composition_rels_del_tr on composition_rels; +drop function composition_rels_del_tr(); + +create or replace function composition_rels_del_tr () returns opaque as ' +declare + v_object_id_one acs_rels.object_id_one%TYPE; + v_object_id_two acs_rels.object_id_two%TYPE; + n_rows integer; + v_error text; + map record; +begin + -- First check if removing this relation would violate any relational constraints + v_error := rel_constraint__violation_if_removed(old.rel_id); + if v_error is not null then + raise EXCEPTION ''-20000: %'', v_error; + end if; + + select object_id_one, object_id_two into v_object_id_one, v_object_id_two + from acs_rels + where rel_id = old.rel_id; + + for map in select * + from group_component_map + where rel_id = old.rel_id + LOOP + + delete from group_element_index + where rel_id = old.rel_id; + + select count(*) into n_rows + from group_component_map + where group_id = map.group_id + and component_id = map.component_id; + + if n_rows = 0 then + + perform party_approved_member__remove(map.group_id, member_id, rel_id, rel_type) + from group_approved_member_map + where group_id = map.group_id + and container_id = map.component_id; + + delete from group_element_index + where group_id = map.group_id + and container_id = map.component_id + and ancestor_rel_type = ''membership_rel''; + end if; + + end loop; + + + for map in select * + from group_component_map + where group_id in (select group_id + 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 + union + select v_object_id_two + from dual) + and group_contains_p(group_id, component_id, rel_id) = ''f'' + LOOP + + delete from group_element_index + where group_id = map.group_id + and element_id = map.component_id + and rel_id = map.rel_id; + + select count(*) into n_rows + from group_component_map + where group_id = map.group_id + and component_id = map.component_id; + + if n_rows = 0 then + end if; + + perform party_approved_member__remove(map.group_id, member_id, rel_id, rel_type) + from group_approved_member_map + where group_id = map.group_id + and container_id = map.component_id; + + delete from group_element_index + where group_id = map.group_id + and container_id = map.component_id + and ancestor_rel_type = ''membership_rel''; + + end loop; + + return old; + +end;' language 'plpgsql'; + +create trigger composition_rels_del_tr before delete on composition_rels +for each row execute procedure composition_rels_del_tr (); +