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, 
