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);
+ 
