Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql,v
diff -u -r1.22 -r1.23
--- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql	17 Feb 2003 15:32:53 -0000	1.22
+++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql	23 Feb 2003 05:58:25 -0000	1.23
@@ -84,172 +84,173 @@
 
 );
 
--- This trigger is used to create a pseudo-tree hierarchy that
--- can be used to emulate tree queries on the acs_privilege_hierarchy table.
--- The acs_privilege_hierarchy table maintains the permissions structure, but 
--- it has a complication in that the same privileges can exist in more than one
--- path in the tree.  As such, tree queries cannot be represented by the 
--- usual tree query methods used for openacs.  
 
--- DCW, 2001-03-15.
 
--- usage: queries directly on acs_privilege_hierarchy don't seem to occur
---        in many places.  Rather it seems that acs_privilege_hierarchy is
---        used to build the view: acs_privilege_descendant_map.  I did however
---        find one tree query in content-perms.sql that looks like the 
---        following:
+-- DanW: eliminated hierarchy index in favor of using descendant map
 
--- select privilege, child_privilege from acs_privilege_hierarchy
---           connect by prior privilege = child_privilege
---           start with child_privilege = 'cm_perm'
 
--- This query is used to find all of the ancestor permissions of 'cm_perm'. 
--- The equivalent query for the postgresql tree-query model would be:
-
--- select  h2.privilege 
---   from acs_privilege_hierarchy_index h1, 
---        acs_privilege_hierarchy_index h2
---  where h1.child_privilege = 'cm_perm'
---    and h1.tree_sortkey between h2.tree_sortkey and tree_right(h2.tree_sortkey)
---    and h2.tree_sortkey <> h1.tree_sortkey;
-
--- Also since acs_privilege_descendant_map is simply a path enumeration of
--- acs_privilege_hierarchy, we should be able to replace the above connect-by
--- with: 
-
--- select privilege 
--- from acs_privilege_descendant_map 
--- where descendant = 'cm_perm'
-
--- This would be better, since the same query could be used for both oracle
--- and postgresql.
-
-create function acs_priv_hier_ins_del_tr () returns opaque as '
+create or replace function acs_priv_hier_ins_tr() returns opaque as '
 declare
-        new_value       integer;
-        new_key         varbit default null;
-        v_rec           record;
-        deleted_p       boolean;
+        v_rec record;
+        v_id  integer;
+        v_lvl integer;
+        v_cont boolean;
 begin
-        -- if more than one node was deleted the second trigger call
-        -- will error out.  This check avoids that problem.
 
-        if TG_OP = ''DELETE'' then 
-            select count(*) = 0 into deleted_p
-              from acs_privilege_hierarchy_index 
-             where old.privilege = privilege
-               and old.child_privilege = child_privilege;     
-       
-            if deleted_p then
+        -- first insert the new child relation if it does not exist already
+        insert into acs_privilege_descendant_map
+        select new.privilege, new.child_privilege as descendant
+         where not exists (select 1 
+                             from acs_privilege_descendant_map
+                            where privilege = new.privilege
+                              and descendant = new.child_privilege);
 
-                return new;
+        -- insert self-reference for privilege
+        insert into acs_privilege_descendant_map
+        select new.privilege, new.privilege as descendant
+         where not exists (select 1 
+                             from acs_privilege_descendant_map
+                            where privilege = new.privilege
+                              and descendant = new.privilege);
 
-            end if;
-        end if;
+        -- insert self-reference for descendant
+        insert into acs_privilege_descendant_map
+        select new.child_privilege as privilege, new.child_privilege as descendant
+         where not exists (select 1 
+                             from acs_privilege_descendant_map
+                            where privilege = new.child_privilege
+                              and descendant = new.child_privilege);
 
-        -- recalculate the table from scratch.
+        -- now look for existing children to add to
+        for v_rec in select privilege, descendant
+                       from acs_privilege_descendant_map
+                      where descendant = new.privilege
+        LOOP
+                insert into acs_privilege_descendant_map
+                select v_rec.privilege, new.child_privilege as descendant
+                 where not exists (select 1
+                                     from acs_privilege_descendant_map
+                                    where privilege =  v_rec.privilege
+                                      and descendant = new.child_privilege);
+        end LOOP;
 
-        delete from acs_privilege_hierarchy_index;
-
-        -- first find the top nodes of the tree
-
-        for v_rec in select privilege, child_privilege
-                       from acs_privilege_hierarchy
-                      where privilege 
-                            NOT in (select distinct child_privilege
-                                      from acs_privilege_hierarchy)
-                                           
+        -- now look for existing parents to add to
+        for v_rec in select privilege, descendant
+                       from acs_privilege_descendant_map
+                      where privilege = new.child_privilege
         LOOP
+                insert into acs_privilege_descendant_map
+                select new.privilege, v_rec.descendant
+                 where not exists (select 1
+                                     from acs_privilege_descendant_map
+                                    where privilege =  new.privilege
+                                      and descendant = v_rec.descendant);
+        end LOOP;
 
-            -- top level node, so find the next key at this level.
 
-            select max(tree_leaf_key_to_int(tree_sortkey)) into new_value 
-              from acs_privilege_hierarchy_index
-             where tree_level(tree_sortkey) = 1;
+        return new;
 
-             -- insert the new node
+end;' language 'plpgsql';
 
-            insert into acs_privilege_hierarchy_index 
-                        (privilege, child_privilege, tree_sortkey)
-                        values
-                        (v_rec.privilege, v_rec.child_privilege, tree_next_key(null, new_value));
 
-            -- now recurse down from this node
+create trigger acs_priv_hier_ins_tr after insert
+on acs_privilege_hierarchy for each row
+execute procedure acs_priv_hier_ins_tr ();
 
-            PERFORM priv_recurse_subtree(tree_next_key(null, new_value), v_rec.child_privilege);
 
-        end LOOP;
 
-        -- materialize the map view to speed up queries
-        -- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003
-        delete from acs_privilege_descendant_map;
+create or replace function recurse_del_priv_hier(varchar,varchar) 
+returns varchar as '
+declare
+        parent  alias for $1;
+        child   alias for $2;
+        v_rec   record;
+begin
+        -- now look for more children of this child
+        for v_rec in  select privilege, 
+                             child_privilege 
+                        from acs_privilege_hierarchy
+                       where privilege = child
+        LOOP
+            -- insert the children
+            insert into acs_privilege_descendant_map
+            select parent as privilege, v_rec.child_privilege as descendant
+             where not exists (select 1 
+                                 from acs_privilege_descendant_map 
+                                where privilege = parent 
+                                  and descendant = v_rec.child_privilege);
 
-        insert into acs_privilege_descendant_map (privilege, descendant) 
-        select privilege, descendant from acs_privilege_descendant_map_view;
+            -- and recurse down ad-nauseum
+            PERFORM recurse_del_priv_hier(parent,v_rec.child_privilege); 
+        end loop;
 
-        return new;
-
+        return null;
 end;' language 'plpgsql';
 
-create trigger acs_priv_hier_ins_del_tr after insert or delete
-on acs_privilege_hierarchy for each row
-execute procedure acs_priv_hier_ins_del_tr ();
-
-create function priv_recurse_subtree(varbit, varchar) 
-returns integer as '
+create or replace function acs_priv_hier_del_tr() returns opaque as '
 declare
-        nkey            alias for $1;
-        child_priv      alias for $2;
-        new_value       integer;
-        v_rec           record;
-        new_key         varbit;
+        v_rec record;
+        v_id  integer;
 begin
+        -- rebuild from scratch
+        delete from acs_privilege_descendant_map;
 
-        -- now iterate over all of the children of the 
-        -- previous node.
-        
-        for v_rec in select privilege, child_privilege
-                       from acs_privilege_hierarchy
-                      where privilege = child_priv
-
+        -- loop through the top-level of privileges
+        for v_rec in  select privilege, 
+                             child_privilege 
+                        from acs_privilege_hierarchy
         LOOP
+                -- insert the top level privileges if they do not already exist
+                insert into acs_privilege_descendant_map
+                select v_rec.privilege, v_rec.child_privilege as descendant
+                 where not exists (select 1 
+                                     from acs_privilege_descendant_map 
+                                    where privilege = v_rec.privilege 
+                                      and descendant = v_rec.child_privilege);
 
-            -- calculate the next key for this level and parent
+                -- now recurse down to the next level                      
+                PERFORM recurse_del_priv_hier(v_rec.privilege,v_rec.child_privilege);
+        end LOOP;
 
-            select max(tree_leaf_key_to_int(tree_sortkey)) into new_value
-              from acs_privilege_hierarchy_index
-             where tree_sortkey between nkey and tree_right(nkey)
-               and tree_level(tree_sortkey) = tree_level(nkey) + 1;
+        -- provide self-mapping
+        for v_rec in select privilege, privilege as child_privilege 
+                       from acs_privilege_descendant_map
+                      
+        LOOP
 
-            new_key := tree_next_key(nkey, new_value);
+                insert into acs_privilege_descendant_map
+                select v_rec.privilege, v_rec.child_privilege as descendant
+                 where not exists (select 1 
+                                     from acs_privilege_descendant_map 
+                                    where privilege = v_rec.privilege 
+                                      and descendant = v_rec.child_privilege);
+        end loop;
 
-            -- insert the new child node.
+        -- provide self-mapping
+        for v_rec in select descendant as privilege, descendant as child_privilege 
+                       from acs_privilege_descendant_map
+                      
+        LOOP
 
-            insert into acs_privilege_hierarchy_index
-                        (privilege, child_privilege, tree_sortkey)
-                        values
-                        (v_rec.privilege, v_rec.child_privilege, new_key);
+                insert into acs_privilege_descendant_map
+                select v_rec.privilege, v_rec.child_privilege as descendant
+                 where not exists (select 1 
+                                     from acs_privilege_descendant_map 
+                                    where privilege = v_rec.privilege 
+                                      and descendant = v_rec.child_privilege);
+        end loop;
 
-            -- keep recursing down until no more children are found
 
-            PERFORM priv_recurse_subtree(new_key, v_rec.child_privilege);
+        return new;
 
-        end LOOP;
+end;' language 'plpgsql';
 
-        -- no children found, so insert the child node as its own separate 
-        -- node.
+create trigger acs_priv_hier_del_tr after delete
+on acs_privilege_hierarchy for each row
+execute procedure acs_priv_hier_del_tr ();
 
-        if NOT FOUND then
-           insert into acs_privilege_hierarchy_index
-                       (privilege, child_privilege, tree_sortkey)
-                       values 
-                       (child_priv, child_priv, tree_next_key(nkey, null));
-        end if;
 
-        return null;
 
-end;' language 'plpgsql';
-
 --create table acs_privilege_method_rules (
 --	privilege	not null constraint acs_priv_method_rules_priv_fk
 --			references acs_privileges (privilege),
@@ -374,24 +375,6 @@
 create index acs_permissions_grantee_idx on acs_permissions (grantee_id);
 create index acs_permissions_privilege_idx on acs_permissions (privilege);
 
--- Added table to materialize view that previously used 
--- acs_privilege_descendant_map name
---
--- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003
-
-create view acs_privilege_descendant_map_view
-as select p1.privilege, p2.privilege as descendant
-   from acs_privileges p1, acs_privileges p2
-   where exists (select h2.child_privilege
-                   from
-                     acs_privilege_hierarchy_index h1,
-                     acs_privilege_hierarchy_index h2
-                   where
-                     h1.privilege = p1.privilege
-                     and h2.privilege = p2.privilege
-                     and h2.tree_sortkey between h1.tree_sortkey and tree_right(h1.tree_sortkey)) or
-     p1.privilege = p2.privilege;
-
 create view acs_permissions_all
 as select op.object_id, p.grantee_id, p.privilege
    from acs_object_paths op, acs_permissions p
