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.20 -r1.21
--- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql	30 Jan 2003 16:28:57 -0000	1.20
+++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql	14 Feb 2003 02:47:13 -0000	1.21
@@ -84,6 +84,11 @@
 
 );
 
+-- DRB: Empirical testing showed that even with just 61 entries in the new table
+-- this index sped things up by roughly 15%
+
+create index acs_priv_desc_map_idx on acs_privilege_descendant_map(descendant);
+
 -- 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 
@@ -124,13 +129,14 @@
 -- 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_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.
 
@@ -402,81 +408,18 @@
    from acs_permissions_all a, acs_privilege_descendant_map m
    where a.privilege = m.privilege;
 
--- The last two unions make sure that the_public gets expaned to all
--- users plus 0 (the default user_id) we should probably figure out a
--- better way to handle this eventually since this view is getting
--- pretty freaking hairy. I'd love to be able to move this stuff into
--- a Java middle tier.
+-- New fast version of acs_object_party_privilege_map
 
-create view acs_object_party_privilege_map
-as select ogpm.object_id, gmm.member_id as party_id, ogpm.privilege
-   from acs_object_grantee_priv_map ogpm, group_approved_member_map gmm
-   where ogpm.grantee_id = gmm.group_id
-   union
-   select ogpm.object_id, rsmm.member_id as party_id, ogpm.privilege
-   from acs_object_grantee_priv_map ogpm, rel_seg_approved_member_map rsmm
-   where ogpm.grantee_id = rsmm.segment_id
-   union
-   select object_id, grantee_id as party_id, privilege
-   from acs_object_grantee_priv_map
-   union
-   select object_id, u.user_id as party_id, privilege
-   from acs_object_grantee_priv_map m, users u
-   where m.grantee_id = -1
-   union
-   select object_id, 0 as party_id, privilege
-   from acs_object_grantee_priv_map
-   where grantee_id = -1;
+create view acs_object_party_privilege_map as
+select c.object_id, pdm.descendant as privilege, pamm.member_id as party_id
+from acs_object_context_index c, acs_permissions p, acs_privilege_descendant_map pdm,
+  party_approved_member_map pamm
+where c.ancestor_id = p.object_id
+  and pdm.privilege = p.privilege
+  and pamm.party_id = p.grantee_id;
 
-----------------------------------------------------
--- ALTERNATE VIEW: ALL_OBJECT_PARTY_PRIVILEGE_MAP --
-----------------------------------------------------
-
--- This view is a helper for all_object_party_privilege_map
-create view acs_grantee_party_map as
-   select -1 as grantee_id, 0 as party_id from dual
-   union all
-   select -1 as grantee_id, user_id as party_id
-   from users
-   union all
-   select party_id as grantee_id, party_id
-   from parties
-   union all
-   select segment_id as grantee_id, member_id
-   from rel_seg_approved_member_map
-   union all
-   select group_id as grantee_id, member_id as party_id
-   from group_approved_member_map;
-
--- This view is like acs_object_party_privilege_map, but does not 
--- necessarily return distinct rows.  It may be *much* faster to join
--- against this view instead of acs_object_party_privilege_map, and is
--- usually not much slower.  The tradeoff for the performance boost is
--- increased complexity in your usage of the view.  Example usage that I've
--- found works well is:
---
---    select DISTINCT 
---           my_table.*
---    from my_table,
---         (select object_id
---          from all_object_party_privilege_map 
---          where party_id = :user_id and privilege = :privilege) oppm
---    where oppm.object_id = my_table.my_id;
---
-
--- DRB: This view does seem to be quite fast in Postgres as well as Oracle.
-
 create view all_object_party_privilege_map as
-select         op.object_id,
-               pdm.descendant as privilege,
-               gpm.party_id as party_id
-        from acs_object_paths op, 
-             acs_permissions p, 
-             acs_privilege_descendant_map pdm,
-             acs_grantee_party_map gpm
-        where op.ancestor_id = p.object_id 
-          and pdm.privilege = p.privilege
-          and gpm.grantee_id = p.grantee_id;
+select * from acs_object_party_privilege_map;
 
 
 -- This table acts as a mutex for inserts/deletes from acs_permissions.
@@ -556,124 +499,23 @@
     return 0; 
 end;' language 'plpgsql';
 
--- Speedy version of permission_p from Matthew Avalos
--- Further improved to a minor degree by Don Baccus
+-- Really speedy version of permission_p written by Don Baccus
 
-create function acs_permission__permission_p (integer,integer,varchar)
+create or replace function acs_permission__permission_p (integer,integer,varchar)
 returns boolean as '
 declare
     permission_p__object_id           alias for $1;
     permission_p__party_id            alias for $2;
     permission_p__privilege           alias for $3;
     exists_p                          boolean;
 begin
-    --
-    -- Check public-like permissions
-    if (0 = permission_p__party_id or
-        exists (select 1 from users where user_id = permission_p__party_id)) and
-        exists (select 1
-                from acs_object_grantee_priv_map
-                where object_id = permission_p__object_id
-                  and privilege = permission_p__privilege
-                  and grantee_id = -1)
-    --
-    then
-       return ''t'';
-    end if;
-    --
-    -- Check direct permissions
-    if exists (
-        select 1
-          from acs_object_grantee_priv_map
-         where object_id = permission_p__object_id
-           and grantee_id = permission_p__party_id
-           and privilege = permission_p__privilege)
-    then
-        return ''t'';
-    end if;
-    --
-    -- Check group permmissions
-    if exists (
-          select 1
-          from acs_object_grantee_priv_map ogpm,
-               group_approved_member_map gmm
-         where object_id = permission_p__object_id
-           and gmm.member_id = permission_p__party_id
-           and privilege = permission_p__privilege
-           and ogpm.grantee_id = gmm.group_id)
-    then
-        return ''t'';
-    end if;
-    --
-    -- relational segment approved group
-    if exists (
-        select 1
-          from acs_object_grantee_priv_map ogpm,
-               rel_seg_approved_member_map rsmm
-         where object_id = permission_p__object_id
-           and rsmm.member_id = permission_p__party_id
-           and privilege = permission_p__privilege
-           and ogpm.grantee_id = rsmm.segment_id)
-    then
-        return ''t'';
-    end if;
-    return ''f'';
+  return exists (select 1
+                 from acs_permissions p, party_approved_member_map m,
+                   acs_object_context_index c, acs_privilege_descendant_map h
+                 where p.object_id = c.ancestor_id
+                   and h.descendant = permission_p__privilege
+                   and c.object_id = permission_p__object_id
+                   and m.member_id = permission_p__party_id
+                   and p.privilege = h.privilege
+                   and p.grantee_id = m.party_id);
 end;' language 'plpgsql';
-
--- Returns true if at least one user exists with the given permission.  Used
--- to avoid some queries on acs_object_party_privilege_map.
-
-create function acs_permission__user_with_perm_exists_p (integer,varchar)
-returns boolean as '
-declare
-    permission_p__object_id           alias for $1;
-    permission_p__privilege           alias for $2;
-begin
-    --
-    -- Check public-like permissions
-    if exists (select 1
-               from acs_object_grantee_priv_map
-                where object_id = permission_p__object_id
-                  and privilege = permission_p__privilege
-                  and grantee_id = -1)
-    --
-    then
-       return ''t'';
-    end if;
-    --
-    -- Check direct user permissions
-    if exists (
-        select 1
-          from acs_object_grantee_priv_map, users
-         where object_id = permission_p__object_id
-           and grantee_id = user_id
-           and privilege = permission_p__privilege)
-    then
-        return ''t'';
-    end if;
-    --
-    -- Check group permmissions
-    if exists (
-          select 1
-          from acs_object_grantee_priv_map ogpm,
-               group_approved_member_map gmm
-         where object_id = permission_p__object_id
-           and privilege = permission_p__privilege
-           and ogpm.grantee_id = gmm.group_id)
-    then
-        return ''t'';
-    end if;
-    --
-    -- relational segment approved group
-    if exists (
-        select 1
-          from acs_object_grantee_priv_map ogpm,
-               rel_seg_approved_member_map rsmm
-         where object_id = permission_p__object_id
-           and privilege = permission_p__privilege
-           and ogpm.grantee_id = rsmm.segment_id)
-    then
-        return ''t'';
-    end if;
-    return ''f'';
-end;' language 'plpgsql';
