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.4 -r1.5 --- openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 14 Feb 2003 02:47:13 -0000 1.4 +++ openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 17 Feb 2003 15:32:53 -0000 1.5 @@ -92,10 +92,102 @@ -- 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 @@ -115,6 +207,21 @@ 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 @@ -130,111 +237,71 @@ as select distinct segment_id, member_id from rel_seg_approved_member_map; --- party_approved_member_map can be used to expand any party into its members. + +-- party_member_map can be used to expand any party into its members. -- Every party is considered to be a member of itself. --- DRB: This is here rather where parties are created for historical reasons --- (in other words this is where the old view was created in older versions) +-- By the way, aren't the party_member_map and party_approved_member_map +-- views equivalent?? (TO DO: RESOLVE THIS QUESTION) --- The count column is needed because composition_rels and relational segment --- rel_types derived from membership_rel lead to a lot of redundant data in the --- group element map (i.e. you can belong to the registered users group an --- infinite number of times, strange concept) +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; -create table party_approved_member_map ( - party_id integer - constraint party_member_party_fk - references parties, - member_id integer - constraint party_member_member_fk - references parties, - count integer, - constraint party_member_map_pk - primary key (party_id, member_id) -); +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; --- Need this to speed referential integrity -create index party_member_member_idx on party_approved_member_map(member_id); +-- party_element_map tells us all the parties that "belong to" a party, +-- whether through somet type of membership, composition, or identity. --- Triggers to maintain party_approved_member_map when parties are created or --- destroyed. +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; -create or replace function parties_in_tr () returns opaque as ' -begin - insert into party_approved_member_map - (party_id, member_id, count) - values - (new.party_id, new.party_id, 1); - return new; - - -end;' language 'plpgsql'; - -create trigger parties_in_tr before 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, count) - select new.segment_id, element_id, 1 - 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 parties_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,