Index: openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql 13 Mar 2003 20:22:57 -0000 1.3 +++ openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql 17 Mar 2003 21:58:22 -0000 1.4 @@ -3,7 +3,7 @@ -- -- @author Oumi Mehrotra oumi@arsdigita.com -- @creation-date 2000-11-22 --- @cvs-id $Id$ +-- @cvs-id rel-segments-create.sql,v 1.1.4.3 2001/01/16 18:54:05 oumi Exp -- Copyright (C) 1999-2000 ArsDigita Corporation -- This is free software distributed under the terms of the GNU Public @@ -15,45 +15,54 @@ -- change in the future, particularly the functions marked "EXPERIMENTAL". -- +create function inline_0 () +returns integer as ' begin -- -- Relational Segment: a dynamically derived set of parties, defined -- in terms of a particular type of membership or -- composition to a particular group. -- - acs_object_type.create_type ( - supertype => 'party', - object_type => 'rel_segment', - pretty_name => 'Relational Party Segment', - pretty_plural => 'Relational Party Segments', - table_name => 'rel_segments', - id_column => 'segment_id', - package_name => 'rel_segment', - type_extension_table => 'rel_segment', - name_method => 'rel_segment.name' - ); + PERFORM acs_object_type__create_type ( + ''rel_segment'', + ''Relational Party Segment'', + ''Relational Party Segments'', + ''party'', + ''rel_segments'', + ''segment_id'', + ''rel_segment'', + ''f'', + ''rel_segment'', + ''rel_segment.name'' + ); -end; -/ -show errors + return 0; +end;' language 'plpgsql'; +select inline_0 (); +drop function inline_0 (); + + +-- show errors + + -- Note that we do not use on delete cascade on the group_id or -- rel_type column because rel_segments are acs_objects. On delete -- cascade only deletes the corresponding row in this table, not all -- the rows up the type hierarchy. Thus, rel segments must be deleted -- using rel_segment.delete before dropping a relationship type. create table rel_segments ( - segment_id not null + segment_id integer not null constraint rel_segments_segment_id_fk references parties (party_id) constraint rel_segments_pk primary key, - segment_name varchar2(230) not null, - group_id not null + segment_name varchar(230) not null, + group_id integer not null constraint rel_segments_group_id_fk references groups (group_id), - rel_type not null + rel_type varchar(100) not null constraint rel_segments_rel_type_fk references acs_rel_types (rel_type), constraint rel_segments_grp_rel_type_uq unique(group_id, rel_type) @@ -83,352 +92,264 @@ -- 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 or replace view rel_segment_party_map +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 + from rel_segments rs, group_element_map gem, acs_object_types o1, acs_object_types o2 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); + and o1.object_type = gem.rel_type + and o2.object_type = rs.rel_type + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey); - -create or replace view rel_segment_distinct_party_map +create view rel_segment_distinct_party_map as select distinct segment_id, party_id, ancestor_rel_type from rel_segment_party_map; -create or replace view rel_segment_member_map +create view rel_segment_member_map as select segment_id, party_id as member_id, rel_id, rel_type, group_id, container_id from rel_segment_party_map where ancestor_rel_type = 'membership_rel'; -create or replace 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 +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 + from membership_rels mr, group_element_map gem, rel_segments rs, + acs_object_types o1, acs_object_types o2 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 rs.rel_type = o2.object_type + and o1.object_type = gem.rel_type + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey) and mr.rel_id = gem.rel_id and mr.member_state = 'approved'; -create or replace view rel_seg_distinct_member_map +create view rel_seg_distinct_member_map as select distinct segment_id, member_id from rel_seg_approved_member_map; --- The party_approved_member_map table maps all parties to all their --- members. It's here rather in a logical place for historical reasons. - --- The count column is needed because composition_rels 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) - --- (it is "cnt" rather than "count" because Oracle confuses it with the --- "count()" aggregate in some contexts) - -- 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. +-- 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. + +-- 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. + +-- Maybe people who buy Oracle aren't as dumb as you thought! + 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, - cnt integer, + tag integer + constraint party_member_tag_nn + not null, constraint party_approved_member_map_pk - primary key (party_id, member_id) + primary key (party_id, member_id, tag) ); -- Need this to speed referential integrity create index party_member_member_idx on party_approved_member_map(member_id); --- Triggers to maintain party_approved_member_map when parties are create or replaced or --- destroyed. +-- Helper functions to maintain the materialized party_approved_member_map. -create or replace trigger parties_in_tr after insert on parties -for each row +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, cnt) + (party_id, member_id, tag) values - (:new.party_id, :new.party_id, 1); -end parties_in_tr; -/ -show errors; + (p_party_id, p_member_id, p_rel_id); -create or replace trigger parties_del_tr before delete on parties -for each row -begin - delete from party_approved_member_map - where party_id = :old.party_id - and member_id = :old.party_id; -end parties_del_tr; -/ -show errors; + return 1; --- Triggers to maintain party_approved_member_map when relational segments are --- create or replaced 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. +end;' language 'plpgsql'; -create or replace trigger rel_segments_in_tr before insert on rel_segments -for each row +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_segments record; begin - insert into party_approved_member_map - (party_id, member_id, cnt) - select :new.segment_id, element_id, 1 - from group_element_index - where group_id = :new.group_id - and rel_type = :new.rel_type; -end rel_segments_in_tr; -/ -show errors; -create or replace trigger rel_segments_del_tr before delete on rel_segments -for each row + 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 + + for v_segments in select segment_id + from rel_segments s, acs_object_types o1, acs_object_types o2 + where + o1.object_type = p_rel_type + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey) + and s.rel_type = o2.object_type + and s.group_id = p_party_id + loop + perform party_approved_member__add_one(v_segments.segment_id, p_member_id, p_rel_id); + end loop; + + 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 = :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); -end parties_del_tr; -/ -show errors; + where party_id = p_party_id + and member_id = p_member_id + and tag = p_rel_id; --- DRB: Helper functions to maintain the materialized party_approved_member_map. The counting crap --- has to do with the fact that composition rels create duplicate rows in groups. + return 1; -create or replace package party_approved_member is +end;' language 'plpgsql'; - procedure add_one( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE - ); - procedure add( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE, - p_rel_type in acs_rels.rel_type%TYPE - ); +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_segments record; +begin - procedure remove_one ( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE - ); + perform party_approved_member__remove_one(p_party_id, p_member_id, p_rel_id); - procedure remove ( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE, - p_rel_type in acs_rels.rel_type%TYPE - ); + -- if the relation type is mapped to relational segments unmap them too -end party_approved_member; -/ -show errors; + for v_segments in select segment_id + from rel_segments s, acs_object_types o1, acs_object_types o2 + where + o1.object_type = p_rel_type + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey) + and s.rel_type = o2.object_type + and s.group_id = p_party_id + loop + perform party_approved_member__remove_one(v_segments.segment_id, p_member_id, p_rel_id); + end loop; -create or replace package body party_approved_member is + return 1; - procedure add_one( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE - ) - is - begin +end;' language 'plpgsql'; - insert into party_approved_member_map - (party_id, member_id, cnt) - values - (p_party_id, p_member_id, 1); - exception when dup_val_on_index then - update party_approved_member_map - set cnt = cnt + 1 - where party_id = p_party_id - and member_id = p_member_id; +-- 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. - end add_one; +create or replace function parties_in_tr () returns opaque as ' +begin - procedure add( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE, - p_rel_type in acs_rels.rel_type%TYPE - ) - is - begin + insert into party_approved_member_map + (party_id, member_id, tag) + values + (new.party_id, new.party_id, 0); - add_one(p_party_id, p_member_id); + return new; - -- if the relation type is mapped to a relational segment map that too +end;' language 'plpgsql'; - for v_segments in (select segment_id - from rel_segments - where group_id = p_party_id - and rel_type in (select object_type - from acs_object_types - start with object_type = p_rel_type - connect by prior supertype = object_type)) - loop - add_one(v_segments.segment_id, p_member_id); - end loop; +create trigger parties_in_tr after insert on parties +for each row execute procedure parties_in_tr (); - end add; +create or replace function parties_del_tr () returns opaque as ' +begin - procedure remove_one ( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE - ) - is - begin + delete from party_approved_member_map + where party_id = old.party_id + and member_id = old.party_id; - update party_approved_member_map - set cnt = cnt - 1 - where party_id = p_party_id - and member_id = p_member_id; + return old; - delete from party_approved_member_map - where party_id = p_party_id - and member_id = p_member_id - and cnt = 0; +end;' language 'plpgsql'; - end remove_one; +create trigger parties_del_tr before delete on parties +for each row execute procedure parties_del_tr (); - procedure remove ( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE, - p_rel_type in acs_rels.rel_type%TYPE - ) - is - begin +-- 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. - remove_one(p_party_id, p_member_id); +create or replace function rel_segments_in_tr () returns opaque as ' +begin - -- if the relation type is mapped to a relational segment unmap that too + 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; - for v_segments in (select segment_id - from rel_segments - where group_id = p_party_id - and rel_type in (select object_type - from acs_object_types - start with object_type = p_rel_type - connect by prior supertype = object_type)) - loop - remove_one(v_segments.segment_id, p_member_id); - end loop; + return new; - end remove; +end;' language 'plpgsql'; -end party_approved_member; -/ -show errors; +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 or replace view rel_segment_group_rel_type_map as + +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 @@ -438,9 +359,10 @@ UNION ALL select group_id, group_id as component_id from groups) gcm, - acs_rel_types + acs_rel_types, + acs_object_types o1, acs_object_types o2 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); - + and s.rel_type = o2.object_type + and o1.object_type = acs_rel_types.rel_type + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey); +