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.19.4.1 -r1.19.4.2
--- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql	11 Feb 2003 17:04:24 -0000	1.19.4.1
+++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql	11 Mar 2003 02:31:49 -0000	1.19.4.2
@@ -385,18 +385,18 @@
 --
 -- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003
 
+-- DRB: I switched this to UNION form because the old view was incredibly
+-- slow and caused installation of packages to take exponentially increasing
+-- time.   No code should be querying against this view other than the
+-- trigger that recreates the denormalized map anyway ...
+
 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;
+as select distinct h1.privilege, h2.child_privilege as descendant
+   from acs_privilege_hierarchy_index h1, acs_privilege_hierarchy_index h2
+   where h2.tree_sortkey between h1.tree_sortkey and tree_right(h1.tree_sortkey)
+   union
+   select privilege, privilege
+   from acs_privileges;
 
 create view acs_permissions_all
 as select op.object_id, p.grantee_id, p.privilege
Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql,v
diff -u -r1.1.2.1 -r1.1.2.2
--- openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql	5 Mar 2003 14:38:50 -0000	1.1.2.1
+++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql	11 Mar 2003 02:32:18 -0000	1.1.2.2
@@ -206,3 +206,90 @@
 	return v_version_id;
    
 end;' language 'plpgsql';
+
+-- DRB: Fix the incredibly slow execution of acs_privilege__add_child()
+
+drop view acs_privilege_descendant_map_view;
+create view acs_privilege_descendant_map_view
+as select distinct h1.privilege, h2.child_privilege as descendant
+   from acs_privilege_hierarchy_index h1, acs_privilege_hierarchy_index h2
+   where h2.tree_sortkey between h1.tree_sortkey and tree_right(h1.tree_sortkey)
+   union
+   select privilege, privilege
+   from acs_privileges;
+
+drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy;
+drop function acs_priv_hier_ins_del_tr ();
+create or replace function acs_priv_hier_ins_del_tr () returns opaque as '
+declare
+        new_value       integer;
+        new_key         varbit default null;
+        v_rec           record;
+        deleted_p       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
+
+                return new;
+
+            end if;
+        end if;
+
+        -- recalculate the table from scratch.
+
+        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)
+                                           
+        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;
+
+             -- insert the new node
+
+            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
+
+            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;
+
+        insert into acs_privilege_descendant_map (privilege, descendant) 
+        select privilege, descendant from acs_privilege_descendant_map_view;
+
+        return new;
+
+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 ();
+
+