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.1 -r1.2
--- openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql	20 Mar 2001 22:51:55 -0000	1.1
+++ openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql	18 Feb 2003 20:53:45 -0000	1.2
@@ -206,46 +206,217 @@
 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.
 
--- party_member_map can be used to expand any party into its members.  
--- Every party is considered to be a member of itself.
+-- 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)
 
--- By the way, aren't the party_member_map and party_approved_member_map 
--- views equivalent??  (TO DO: RESOLVE THIS QUESTION)
+-- (it is "cnt" rather than "count" because Oracle confuses it with the
+-- "count()" aggregate in some contexts)
 
-create or replace 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;
+-- 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.
 
-create or replace 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_fk
+                    references parties,
+    member_id       integer
+                    constraint party_member_member_fk
+                    references parties,
+    cnt             integer,
+    constraint party_approved_member_map_pk
+    primary key (party_id, member_id)
+);
 
--- 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 or replace 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;
+-- Triggers to maintain party_approved_member_map when parties are create or replaced or
+-- destroyed.
 
+create or replace trigger parties_in_tr after insert on parties
+for each row 
+begin
+  insert into party_approved_member_map
+    (party_id, member_id, cnt)
+  values
+    (:new.party_id, :new.party_id, 1);
+end parties_in_tr;
+/
+show errors;
+
+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;
+
+-- 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.
+
+create or replace trigger rel_segments_in_tr before insert on rel_segments
+for each row
+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
+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;
+
+-- 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.
+
+create or replace package party_approved_member is
+
+  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
+  );
+
+  procedure remove_one (
+    p_party_id in parties.party_id%TYPE,
+    p_member_id in parties.party_id%TYPE
+  );
+
+  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
+  );
+
+end party_approved_member;
+/
+show errors;
+
+create or replace package body party_approved_member is
+
+  procedure add_one(
+    p_party_id in parties.party_id%TYPE,
+    p_member_id in parties.party_id%TYPE
+  )
+  is
+  begin
+
+    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;
+
+  end add_one;
+
+  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
+    v_segment_id rel_segments.segment_id%TYPE;
+  begin
+
+    add_one(p_party_id, p_member_id);
+
+    -- if the relation type is mapped to a relational segment map that too
+
+    select segment_id into v_segment_id
+    from rel_segments s
+    where s.rel_type = p_rel_type
+      and s.group_id = p_party_id;
+
+    exception when no_data_found then return;
+
+    add_one(v_segment_id, p_member_id);
+
+  end add;
+
+  procedure remove_one (
+    p_party_id in parties.party_id%TYPE,
+    p_member_id in parties.party_id%TYPE
+  )
+  is
+  begin
+
+    update party_approved_member_map
+    set cnt = cnt - 1
+    where party_id = p_party_id
+      and member_id = p_member_id;
+
+    delete from party_approved_member_map
+    where party_id = p_party_id
+      and member_id = p_member_id
+      and cnt = 0;
+
+  end remove_one;
+
+  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
+    v_segment_id rel_segments.segment_id%TYPE;
+  begin
+
+    remove_one(p_party_id, p_member_id);
+
+    -- if the relation type is mapped to a relational segment unmap that too
+
+    select segment_id into v_segment_id
+    from rel_segments s
+    where s.rel_type = p_rel_type
+      and s.group_id = p_party_id;
+
+    exception when no_data_found then return;
+
+    remove_one(v_segment_id, p_member_id);
+
+  end remove;
+
+end party_approved_member;
+/
+show errors;
+
 -- View: rel_segment_group_rel_type_map
 --
 -- Result Set: the set of triples (:segment_id, :group_id, :rel_type) such that
