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.5 -r1.6 --- openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 17 Feb 2003 15:32:53 -0000 1.5 +++ openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 18 Feb 2003 20:54:32 -0000 1.6 @@ -92,102 +92,10 @@ -- create pl/sql package rel_segment --- create or replace package rel_segment --- is --- function new ( --- --/** Creates a new relational segment --- -- --- -- @author Oumi Mehrotra (oumi@arsdigita.com) --- -- @creation-date 12/2000 --- -- --- --*/ --- segment_id in rel_segments.segment_id%TYPE default null, --- object_type in acs_objects.object_type%TYPE --- default 'rel_segment', --- creation_date in acs_objects.creation_date%TYPE --- default sysdate, --- creation_user in acs_objects.creation_user%TYPE --- default null, --- creation_ip in acs_objects.creation_ip%TYPE default null, --- email in parties.email%TYPE default null, --- url in parties.url%TYPE default null, --- segment_name in rel_segments.segment_name%TYPE, --- group_id in rel_segments.group_id%TYPE, --- rel_type in rel_segments.rel_type%TYPE, --- context_id in acs_objects.context_id%TYPE default null --- ) return rel_segments.segment_id%TYPE; --- --- procedure delete ( --- --/** Deletes a relational segment --- -- --- -- @author Oumi Mehrotra (oumi@arsdigita.com) --- -- @creation-date 12/2000 --- -- --- --*/ --- segment_id in rel_segments.segment_id%TYPE --- ); --- --- function name ( --- segment_id in rel_segments.segment_id%TYPE --- ) return rel_segments.segment_name%TYPE; --- --- function get ( --- --/** EXPERIMENTAL / UNSTABLE -- use at your own risk --- -- Get the id of a segment given a group_id and rel_type. --- -- This depends on the uniqueness of group_id,rel_type. We --- -- might remove the unique constraint in the future, in which --- -- case we would also probably remove this function. --- -- --- -- @author Oumi Mehrotra (oumi@arsdigita.com) --- -- @creation-date 12/2000 --- -- --- --*/ --- --- group_id in rel_segments.group_id%TYPE, --- rel_type in rel_segments.rel_type%TYPE --- ) return rel_segments.segment_id%TYPE; --- --- function get_or_new ( --- --/** EXPERIMENTAL / UNSTABLE -- use at your own risk --- -- --- -- This function simplifies the use of segments a little by letting --- -- you not have to worry about creating and initializing segments. --- -- If the segment you're interested in exists, this function --- -- returns its segment_id. --- -- If the segment you're interested in doesn't exist, this function --- -- does a pretty minimal amount of initialization for the segment --- -- and returns a new segment_id. --- -- --- -- @author Oumi Mehrotra (oumi@arsdigita.com) --- -- @creation-date 12/2000 --- -- --- --*/ --- group_id in rel_segments.group_id%TYPE, --- rel_type in rel_segments.rel_type%TYPE, --- segment_name in rel_segments.segment_name%TYPE --- default null --- ) return rel_segments.segment_id%TYPE; --- --- end rel_segment; - --- show errors - - ----------- -- Views -- ----------- --- create view rel_segment_party_map --- as select rs.segment_id, gem.element_id as party_id, gem.rel_id, gem.rel_type, --- gem.group_id, gem.container_id, gem.ancestor_rel_type --- from rel_segments rs, --- group_element_map gem --- where gem.group_id = rs.group_id --- and rs.rel_type in (select object_type --- from acs_object_types --- start with object_type = gem.rel_type --- connect by prior supertype = object_type); - create view rel_segment_party_map as select rs.segment_id, gem.element_id as party_id, gem.rel_id, gem.rel_type, gem.group_id, gem.container_id, gem.ancestor_rel_type @@ -207,21 +115,6 @@ from rel_segment_party_map where ancestor_rel_type = 'membership_rel'; - --- Need to find out what this optimizer hint does? DCW, 2001-03-13. --- create view rel_seg_approved_member_map --- as select /*+ ordered */ --- rs.segment_id, gem.element_id as member_id, gem.rel_id, gem.rel_type, --- gem.group_id, gem.container_id --- from membership_rels mr, group_element_map gem, rel_segments rs --- where rs.group_id = gem.group_id --- and rs.rel_type in (select object_type --- from acs_object_types --- start with object_type = gem.rel_type --- connect by prior supertype = object_type) --- and mr.rel_id = gem.rel_id and mr.member_state = 'approved'; - - create view rel_seg_approved_member_map as select rs.segment_id, gem.element_id as member_id, gem.rel_id, gem.rel_type, gem.group_id, gem.container_id @@ -237,71 +130,218 @@ as select distinct segment_id, member_id from rel_seg_approved_member_map; +-- Though for permission checking we only really need to map parties to +-- member users, the old view included identity entries for all parties +-- in the system. It doesn't cost all that much to maintain the extra +-- rows so we will, just in case some overly clever programmer out there +-- depends on it. --- party_member_map can be used to expand any party into its members. --- Every party is considered to be a member of itself. +-- This represents a large amount of redundant data which is separately +-- stored in the group_element_index table. We might want to clean this +-- up in the future but time constraints on 4.6.1 require I keep this +-- relatively simple. Implementing a real "subgroup_rel" would help a +-- lot by in itself reducing the number of redundant rows in the two +-- tables. --- By the way, aren't the party_member_map and party_approved_member_map --- views equivalent?? (TO DO: RESOLVE THIS QUESTION) +-- DRB: Unfortunately visibility semantics in PostgreSQL are very different +-- than in Oracle. This makes it impossible to remove the duplicate +-- rows by maintaining a count column as I've done in the Oracle version +-- without requiring application code to issue explicit "lock table in +-- exclusive mode" statements. This would kill abstraction and be very +-- error prone. The PL/pgSQL procs can issue the locks but unfortunately +-- statements within such procs don't generate a new snapshot when executed +-- but rather work within the context of the caller. This means locks within +-- a PL/pgSQL are too late to be of use. Such code works perfectly in Oracle. -create view party_member_map -as select segment_id as party_id, member_id - from rel_seg_distinct_member_map - union - select group_id as party_id, member_id - from group_distinct_member_map - union - select party_id, party_id as member_id - from parties; +-- Maybe people who buy Oracle aren't as dumb as you thought! -create view party_approved_member_map -as select distinct segment_id as party_id, member_id - from rel_seg_approved_member_map - union - select distinct group_id as party_id, member_id - from group_approved_member_map - union - select party_id, party_id as member_id - from parties; +create table party_approved_member_map ( + party_id integer + constraint party_member_party_nn + not null + constraint party_member_party_fk + references parties, + member_id integer + constraint party_member_member_nn + not null + constraint party_member_member_fk + references parties, + tag integer + constraint party_member_tag_nn + not null, + constraint party_approved_member_map_pk + primary key (party_id, member_id, tag) +); --- party_element_map tells us all the parties that "belong to" a party, --- whether through somet type of membership, composition, or identity. +-- Need this to speed referential integrity +create index party_member_member_idx on party_approved_member_map(member_id); -create view party_element_map -as select distinct group_id as party_id, element_id - from group_element_map - union - select distinct segment_id as party_id, party_id as element_id - from rel_segment_party_map - union - select party_id, party_id as element_id - from parties; +-- Helper functions to maintain the materialized party_approved_member_map. +create or replace function party_approved_member__add_one(integer, integer, integer) returns integer as ' +declare + p_party_id alias for $1; + p_member_id alias for $2; + p_rel_id alias for $3; +begin + insert into party_approved_member_map + (party_id, member_id, tag) + values + (p_party_id, p_member_id, p_rel_id); + + return 1; + +end;' language 'plpgsql'; + +create or replace function party_approved_member__add(integer, integer, integer, varchar) returns integer as ' +declare + p_party_id alias for $1; + p_member_id alias for $2; + p_rel_id alias for $3; + p_rel_type alias for $4; + v_segment_id rel_segments.segment_id%TYPE; +begin + + perform party_approved_member__add_one(p_party_id, p_member_id, p_rel_id); + + select into v_segment_id segment_id + from rel_segments s + where s.rel_type = p_rel_type + and s.group_id = p_party_id; + + if found then + perform party_approved_member__add_one(v_segment_id, p_member_id, p_rel_id); + end if; + + return 1; + +end;' language 'plpgsql'; + +create or replace function party_approved_member__remove_one(integer, integer, integer) returns integer as ' +declare + p_party_id alias for $1; + p_member_id alias for $2; + p_rel_id alias for $3; +begin + + delete from party_approved_member_map + where party_id = p_party_id + and member_id = p_member_id + and tag = p_rel_id; + + return 1; + +end;' language 'plpgsql'; + + +create or replace function party_approved_member__remove(integer, integer, integer, varchar) returns integer as ' +declare + p_party_id alias for $1; + p_member_id alias for $2; + p_rel_id alias for $3; + p_rel_type alias for $4; + v_segment_id rel_segments.segment_id%TYPE; +begin + + perform party_approved_member__remove_one(p_party_id, p_member_id, p_rel_id); + + -- if the relation type is mapped to a relational segment unmap that too + + select into v_segment_id segment_id + from rel_segments s + where s.rel_type = p_rel_type + and s.group_id = p_party_id; + + if found then + perform party_approved_member__remove_one(v_segment_id, p_member_id, p_rel_id); + end if; + + return 1; + +end;' language 'plpgsql'; + + +-- Triggers to maintain party_approved_member_map when parties are created or +-- destroyed. These don't call the above helper functions because we're just +-- creating the identity row for the party. + +create or replace function parties_in_tr () returns opaque as ' +begin + + insert into party_approved_member_map + (party_id, member_id, tag) + values + (new.party_id, new.party_id, 0); + + return new; + +end;' language 'plpgsql'; + +create trigger parties_in_tr after insert on parties +for each row execute procedure parties_in_tr (); + +create or replace function parties_del_tr () returns opaque as ' +begin + + delete from party_approved_member_map + where party_id = old.party_id + and member_id = old.party_id; + + return old; + +end;' language 'plpgsql'; + +create trigger parties_del_tr before delete on parties +for each row execute procedure parties_del_tr (); + +-- Triggers to maintain party_approved_member_map when relational segments are +-- created or destroyed. We only remove the (segment_id, member_id) rows as +-- removing the relational segment itself does not remove members from the +-- group with that rel_type. This was intentional on the part of the aD folks +-- who added relational segments to ACS 4.2. + +create or replace function rel_segments_in_tr () returns opaque as ' +begin + + insert into party_approved_member_map + (party_id, member_id, tag) + select new.segment_id, element_id, rel_id + from group_element_index + where group_id = new.group_id + and rel_type = new.rel_type; + + return new; + +end;' language 'plpgsql'; + +create trigger rel_segments_in_tr before insert on rel_segments +for each row execute procedure rel_segments_in_tr (); + +create or replace function rel_segments_del_tr () returns opaque as ' +begin + + delete from party_approved_member_map + where party_id = old.segment_id + and member_id in (select element_id + from group_element_index + where group_id = old.group_id + and rel_type = old.rel_type); + + return old; + +end;' language 'plpgsql'; + +create trigger rel_segments_del_tr before delete on rel_segments +for each row execute procedure rel_segments_del_tr (); + -- View: rel_segment_group_rel_type_map -- -- Result Set: the set of triples (:segment_id, :group_id, :rel_type) such that -- -- IF a party were to be in :group_id -- through a relation of type :rel_type, -- THEN the party would necessarily be in segment :segemnt_id. --- --- --- create view rel_segment_group_rel_type_map as --- select s.segment_id, --- gcm.component_id as group_id, --- acs_rel_types.rel_type as rel_type --- from rel_segments s, --- (select group_id, component_id --- from group_component_map --- UNION ALL --- select group_id, group_id as component_id --- from groups) gcm, --- acs_rel_types --- where s.group_id = gcm.group_id --- and s.rel_type in (select object_type from acs_object_types --- start with object_type = acs_rel_types.rel_type --- connect by prior supertype = object_type); create view rel_segment_group_rel_type_map as select s.segment_id,