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.39 -r1.40
--- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql	27 Jul 2015 12:51:21 -0000	1.39
+++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql	7 Aug 2017 23:47:56 -0000	1.40
@@ -1,7 +1,7 @@
 --
 -- acs-kernel/sql/acs-permissions-create.sql
 --
--- The ACS core permissioning system. The knowledge level of system
+-- The ACS core permission system. The knowledge level of system
 -- allows you to define a hierarchichal system of privilages.  The
 -- operational level allows you to grant to any party a privilege on
 -- any object.
@@ -19,33 +19,33 @@
 ---------------------------------------------
 
 create table acs_privileges (
-	privilege	varchar(100) not null constraint acs_privileges_privilege_pk
-			primary key,
-	pretty_name	varchar(100),
-	pretty_plural	varchar(100)
+    privilege	    varchar(100) not null constraint acs_privileges_privilege_pk
+    		    primary key,
+    pretty_name	    varchar(100),
+    pretty_plural   varchar(100)
 );
 
 create table acs_privilege_hierarchy (
-	privilege	varchar(100) not null 
-                        constraint acs_priv_hier_priv_fk
-			references acs_privileges (privilege),
-        child_privilege	varchar(100) not null 
-                        constraint acs_priv_hier_child_priv_fk
-			references acs_privileges (privilege),
-	constraint acs_privilege_hierarchy_pk
-	primary key (privilege, child_privilege)
+    privilege	    varchar(100) not null 
+                    constraint acs_priv_hier_priv_fk
+    		    references acs_privileges (privilege),
+    child_privilege varchar(100) not null 
+                    constraint acs_priv_hier_child_priv_fk
+    		    references acs_privileges (privilege),
+    constraint acs_privilege_hierarchy_pk
+    primary key (privilege, child_privilege)
 );
 
 create index acs_priv_hier_child_priv_idx on acs_privilege_hierarchy (child_privilege);
 
 create table acs_privilege_hierarchy_index (
-	privilege       varchar(100) not null 
-                        constraint acs_priv_hier_priv_fk
-			references acs_privileges (privilege),
-        child_privilege varchar(100) not null 
-                        constraint acs_priv_hier_child_priv_fk
-			references acs_privileges (privilege),
-        tree_sortkey    varbit
+    privilege       varchar(100) not null 
+                    constraint acs_priv_hier_priv_fk
+    		    references acs_privileges (privilege),
+    child_privilege varchar(100) not null 
+                    constraint acs_priv_hier_child_priv_fk
+    		    references acs_privileges (privilege),
+    tree_sortkey    varbit
 );
 
 create index priv_hier_sortkey_idx on 
@@ -57,13 +57,12 @@
 -- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003
 
 create table acs_privilege_descendant_map (
-	privilege       varchar(100) not null 
-                        constraint acs_priv_hier_priv_fk
-			references acs_privileges (privilege),
-        descendant      varchar(100) not null 
-                        constraint acs_priv_hier_child_priv_fk
-			references acs_privileges (privilege)
-
+    privilege       varchar(100) not null 
+                    constraint acs_priv_hier_priv_fk
+    		    references acs_privileges (privilege),
+    descendant      varchar(100) not null 
+                    constraint acs_priv_hier_child_priv_fk
+    		    references acs_privileges (privilege)
 );
 
 -- DRB: Empirical testing showed that even with just 61 entries in the new table
@@ -215,7 +214,6 @@
 
 
 
--- added
 select define_function_args('priv_recurse_subtree','nkey,child_priv');
 
 --
@@ -291,7 +289,6 @@
 
 
 
--- added
 select define_function_args('acs_privilege__create_privilege','privilege,pretty_name;null,pretty_plural;null');
 
 --
@@ -333,7 +330,6 @@
 
 
 
--- added
 select define_function_args('acs_privilege__drop_privilege','privilege');
 
 --
@@ -353,7 +349,6 @@
 
 
 
--- added
 select define_function_args('acs_privilege__add_child','privilege,child_privilege');
 
 --
@@ -376,7 +371,6 @@
 
 
 
--- added
 select define_function_args('acs_privilege__remove_child','privilege,child_privilege');
 
 --
@@ -402,20 +396,20 @@
 ------------------------------------
 
 create table acs_permissions (
-	object_id		integer not null
-				constraint acs_permissions_object_id_fk
-				references acs_objects (object_id)
-                                on delete cascade,
-	grantee_id		integer not null
-				constraint acs_permissions_grantee_id_fk
-				references parties (party_id)
-                                on delete cascade,
-	privilege		varchar(100) not null 
-                                constraint acs_permissions_privilege_fk
-				references acs_privileges (privilege)
-                                on delete cascade,
-	constraint acs_permissions_pk
-	primary key (object_id, grantee_id, privilege)
+    object_id		integer not null
+    			constraint acs_permissions_object_id_fk
+    			references acs_objects (object_id)
+                        on delete cascade,
+    grantee_id		integer not null
+    			constraint acs_permissions_grantee_id_fk
+    			references parties (party_id)
+                        on delete cascade,
+    privilege		varchar(100) not null 
+                        constraint acs_permissions_privilege_fk
+    			references acs_privileges (privilege)
+                        on delete cascade,
+    constraint acs_permissions_pk
+    primary key (object_id, grantee_id, privilege)
 );
 
 create index acs_permissions_grantee_idx on acs_permissions (grantee_id);
@@ -460,13 +454,17 @@
   and pdm.privilege = p.privilege
   and pamm.party_id = p.grantee_id;
 
+
+--
+-- Obsolete and deprecated view.
+--
 create view all_object_party_privilege_map as
 select * from acs_object_party_privilege_map;
 
 
 -- This table acts as a mutex for inserts/deletes from acs_permissions.
 -- This is used since postgresql's exception handing mechanism is non-
--- existant.  A dup insert on acs_permissions will roll-back the 
+-- existent.  A dup insert on acs_permissions will roll-back the 
 -- transaction and give an error, which is not what we want.  Using a 
 -- separate table for locking allows us exclusive access for 
 -- inserts/deletes, but does not block readers.  That way we don't 
@@ -492,200 +490,314 @@
 
 
 
--- added
-select define_function_args('acs_permission__grant_permission','object_id,grantee_id,privilege');
 
 --
--- procedure acs_permission__grant_permission/3
+-- Create an SQL schema to allow the same dot notation as in
+-- Oracle. The advantage of this notation is that the function can be
+-- called identically for PostgreSQL and Oracle, so much duplicated
+-- code can be removed.
 --
-CREATE OR REPLACE FUNCTION acs_permission__grant_permission(
-   grant_permission__object_id integer,
-   grant_permission__grantee_id integer,
-   grant_permission__privilege varchar
-) RETURNS integer AS $$
-DECLARE
-BEGIN
-    insert into acs_permissions
-      (object_id, grantee_id, privilege)
-      values
-      (grant_permission__object_id, grant_permission__grantee_id, 
-      grant_permission__privilege);
-    
-    return 0;
-EXCEPTION 
-    when unique_violation then
-      return 0;
-END;
-$$ LANGUAGE plpgsql;
+-- Actually, at least all permission functions should be defined this
+-- way, keeping the old "__" notation around for backwards
+-- compatibility for custom packages.
+--
+-- TODO: handling of schema names in define_function_args
+--
+CREATE SCHEMA acs_permission;
 
 
--- procedure revoke_permission
-
-
--- added
-select define_function_args('acs_permission__revoke_permission','object_id,grantee_id,privilege');
-
 --
--- procedure acs_permission__revoke_permission/3
+-- procedure acs_permission.permission_p/3
 --
-CREATE OR REPLACE FUNCTION acs_permission__revoke_permission(
-   revoke_permission__object_id integer,
-   revoke_permission__grantee_id integer,
-   revoke_permission__privilege varchar
-) RETURNS integer AS $$
+CREATE OR REPLACE FUNCTION acs_permission.permission_p(
+       p_object_id integer,
+       p_party_id  integer,
+       p_privilege varchar
+) RETURNS boolean AS $$
 DECLARE
+    v_security_context_root   integer;
 BEGIN
-    delete from acs_permissions
-    where object_id = revoke_permission__object_id
-    and grantee_id = revoke_permission__grantee_id
-    and privilege = revoke_permission__privilege;
+    v_security_context_root := acs__magic_object_id('security_context_root');
 
-    return 0; 
-END;
-$$ LANGUAGE plpgsql;
+    RETURN EXISTS (WITH RECURSIVE
+        object_context(object_id, context_id) AS (
 
--- Really speedy version of permission_p written by Don Baccus
+            SELECT p_object_id, p_object_id 
+            FROM acs_objects 
+            WHERE object_id = p_object_id
 
+            UNION ALL
 
+            SELECT ao.object_id,
+                   CASE WHEN (ao.security_inherit_p = 'f' OR ao.context_id IS NULL) 
+                   THEN v_security_context_root ELSE ao.context_id END
+            FROM object_context oc, acs_objects ao
+            WHERE ao.object_id = oc.context_id
+            AND ao.object_id != v_security_context_root
 
--- added
-select define_function_args('acs_permission__permission_p','object_id,party_id,privilege');
+        ), privilege_ancestors(privilege, child_privilege) AS (
 
+            SELECT p_privilege, p_privilege 
+   
+            UNION ALL
 
-CREATE FUNCTION inline_0()
-RETURNS integer AS $inline_0$
-BEGIN
+            SELECT aph.privilege, aph.child_privilege
+            FROM privilege_ancestors pa
+            JOIN acs_privilege_hierarchy aph ON aph.child_privilege = pa.privilege
 
-    raise notice 'starting permissions recursive -- vguerra';
+        )
+        SELECT 1 FROM acs_permissions p
+        JOIN  party_approved_member_map pap ON pap.party_id  =  p.grantee_id
+        JOIN  privilege_ancestors pa        ON  pa.privilege =  p.privilege
+        JOIN  object_context oc             ON  p.object_id  =  oc.context_id      
+        WHERE pap.member_id = p_party_id
+    );
+END;
+$$ LANGUAGE plpgsql stable;
 
-    IF cmp_pg_version('8.4') >= 0 THEN
-        -- recursive site_nodes recursive - START
 
-        --
-        -- procedure acs_permission__permission_p/3
-        --
-        CREATE OR REPLACE FUNCTION acs_permission__permission_p(
-           permission_p__object_id integer,
-           permission_p__party_id integer,
-           permission_p__privilege varchar
-        ) RETURNS boolean AS $$
-        DECLARE
-            exists_p                          boolean;
-        BEGIN
-            return exists (With RECURSIVE object_context(object_id, context_id) AS (
+--
+-- procedure acs_permission.permission_p_recursive_array/3
+--
+--      Return for a an array of objects a set of objects where the
+--      specified user has the specified rights.
 
-                    select permission_p__object_id, permission_p__object_id 
-                    from acs_objects 
-                    where object_id = permission_p__object_id
+CREATE OR REPLACE FUNCTION  acs_permission.permission_p_recursive_array(
+       p_objects   integer[],
+       p_party_id  integer, 
+       p_privilege varchar
+) RETURNS table (object_id integer, orig_object_id integer) AS $$
+DECLARE
+    v_security_context_root  integer;
+BEGIN
+    v_security_context_root := acs__magic_object_id('security_context_root');
 
-                    union all
+    RETURN QUERY WITH RECURSIVE
+       object_context(obj_id, context_id, orig_obj_id) AS (
 
-                    select ao.object_id,
-                    case when (ao.security_inherit_p = 'f' or ao.context_id is null) 
-                    then acs__magic_object_id('security_context_root') else ao.context_id end
-                    from object_context oc, acs_objects ao
-                    where ao.object_id = oc.context_id
-                    and ao.object_id != acs__magic_object_id('security_context_root')
+           SELECT unnest(p_objects), unnest(p_objects), unnest(p_objects)
+           UNION ALL
+           SELECT
+              ao.object_id,
+              CASE WHEN (ao.security_inherit_p = 'f' OR ao.context_id IS NULL) 
+              THEN v_security_context_root ELSE ao.context_id END, 
+              oc.orig_obj_id
+           FROM  object_context oc, acs_objects ao
+           WHERE ao.object_id = oc.context_id
+           AND   ao.object_id != v_security_context_root
 
-                ), privilege_ancestors(privilege, child_privilege) AS (
+       ), privilege_ancestors(privilege, child_privilege) AS (
 
-                    select permission_p__privilege, permission_p__privilege 
-                   
-                    union all
+           SELECT p_privilege, p_privilege
+           UNION ALL
+           SELECT aph.privilege, aph.child_privilege
+           FROM   privilege_ancestors pa
+           JOIN   acs_privilege_hierarchy aph ON aph.child_privilege = pa.privilege
 
-                    select aph.privilege, aph.child_privilege
-                    from privilege_ancestors pa join acs_privilege_hierarchy aph
-                    on aph.child_privilege = pa.privilege
+       )
+       SELECT p.object_id, oc.orig_obj_id
+       FROM  acs_permissions p
+       JOIN  party_approved_member_map pap ON pap.party_id =  p.grantee_id
+       JOIN  privilege_ancestors pa        ON pa.privilege =  p.privilege
+       JOIN  object_context oc             ON p.object_id  =  oc.context_id
+       WHERE pap.member_id = p_party_id;
+END; 
+$$ LANGUAGE plpgsql stable;
 
-                ) select
-                  1
-                  from
-                  acs_permissions p
-                  join  party_approved_member_map pap on pap.party_id   =  p.grantee_id
-                  join  privilege_ancestors pa  on  pa.privilege  =  p.privilege
-                  join  object_context oc on  p.object_id =  oc.context_id      
-                  where pap.member_id = permission_p__party_id
-                );
-        END;
-        $$ LANGUAGE plpgsql stable;
 
+--
+-- procedure acs_permission.parties_with_object_privilege/2
+--
+--     Find all party_ids which have a given privilege on a given
+--     object. The function is equivalent to an SQL query on the
+--     deprecated acs_object_party_privilege_map such as e.g.:
+--
+--   select p.party_id
+--   from acs_object_party_privilege_map p
+--   where p.object_id = :object_id
+--   and p.privilege = 'admin';
+--
 
-        -- for tsearch
+CREATE OR REPLACE FUNCTION acs_permission.parties_with_object_privilege(
+       p_object_id integer, 
+       p_privilege varchar
+) RETURNS table (party_id integer) AS $$
+DECLARE
+    v_security_context_root  integer;
+BEGIN
+    v_security_context_root := acs__magic_object_id('security_context_root');
 
-        perform define_function_args('acs_permission__permission_p_recursive_array','a_objects,a_party_id,a_privilege');
+    RETURN QUERY
+    WITH RECURSIVE
+       object_context(obj_id, context_id, orig_obj_id) AS (
+           SELECT p_object_id, p_object_id, p_object_id
+           UNION ALL
+           SELECT
+              ao.object_id,
+              CASE WHEN (ao.security_inherit_p = 'f' OR ao.context_id IS NULL) 
+              THEN v_security_context_root ELSE ao.context_id END, 
+              oc.orig_obj_id
+           FROM  object_context oc, acs_objects ao
+           WHERE ao.object_id = oc.context_id
+           AND   ao.object_id != v_security_context_root
+           
+       ), privilege_ancestors(privilege, child_privilege) AS (
+           SELECT p_privilege, p_privilege
+           UNION ALL
+           SELECT aph.privilege, aph.child_privilege
+           FROM privilege_ancestors pa
+           JOIN acs_privilege_hierarchy aph ON aph.child_privilege = pa.privilege
+       )
+       SELECT pap.member_id
+       FROM  acs_permissions p
+       JOIN  party_approved_member_map pap ON pap.party_id =  p.grantee_id
+       JOIN  privilege_ancestors pa        ON pa.privilege =  p.privilege
+       JOIN  object_context oc             ON p.object_id  =  oc.context_id;
+END; 
+$$ LANGUAGE plpgsql stable;
 
-        CREATE OR REPLACE FUNCTION  acs_permission__permission_p_recursive_array(
-            permission_p__objects integer[],
-            permission_p__party_id integer, 
-            permission_p__privilege varchar
-        ) RETURNS table (object_id integer, orig_object_id integer) as $$
-        BEGIN
-            return query With RECURSIVE object_context(obj_id, context_id, orig_obj_id) AS (
+--
+-- procedure acs_permission.permissions_all/1
+--
+--    Return the permissions for an object from the object context
+--    hierarchy. The call
+--
+--         select * from acs_permission.permissions_all(:object_id)
+--
+--    is compatible with the old/Oracle call
+--
+--         select * from acs_permission_all where where object_id = :object_id
+--
+--
+CREATE OR REPLACE FUNCTION acs_permission.permissions_all(
+       p_object_id integer
+) RETURNS table (object_id integer, grantee_id integer, privilege varchar) AS $$
+DECLARE
+    v_security_context_root  integer;
+BEGIN
+    v_security_context_root := acs__magic_object_id('security_context_root');
 
-                    select unnest(permission_p__objects), unnest(permission_p__objects), unnest(permission_p__objects)
+    RETURN QUERY
+    WITH RECURSIVE object_context(obj_id, context_id, orig_obj_id) AS (
+           SELECT p_object_id, p_object_id, p_object_id
+           UNION ALL
+           SELECT
+              ao.object_id,
+              CASE WHEN (ao.security_inherit_p = 'f' OR ao.context_id IS NULL) 
+              THEN v_security_context_root ELSE ao.context_id END, 
+              oc.orig_obj_id
+           FROM  object_context oc, acs_objects ao
+           WHERE ao.object_id = oc.context_id
+           AND   ao.object_id != v_security_context_root
+    )
+    select p_object_id, p.grantee_id, p.privilege
+    from object_context oc, acs_permissions p where p.object_id = oc.context_id;
+END;
+$$ LANGUAGE plpgsql stable;
 
-                    union all
+--
+-- procedure acs_permission.grant_permission/3
+--
+CREATE OR REPLACE FUNCTION acs_permission.grant_permission(
+   p_object_id integer,
+   p_grantee_id integer,
+   p_privilege varchar
+) RETURNS integer AS $$
+DECLARE
+BEGIN
+    insert into acs_permissions
+      (object_id, grantee_id, privilege)
+    values
+      (p_object_id, p_grantee_id, p_privilege);
+    
+    return 0;
+EXCEPTION 
+    when unique_violation then
+      return 0;
+END;
+$$ LANGUAGE plpgsql;
 
-                    select ao.object_id,
-                    case when (ao.security_inherit_p = 'f' or ao.context_id is null) 
-                    then acs__magic_object_id('security_context_root') else ao.context_id END, 
-                    oc.orig_obj_id
-                    from object_context oc, acs_objects ao
-                    where ao.object_id = oc.context_id
-                    and ao.object_id != acs__magic_object_id('security_context_root')
 
-                ), privilege_ancestors(privilege, child_privilege) AS (
+--
+-- procedure acs_permission.revoke_permission/3
+--
+CREATE OR REPLACE FUNCTION acs_permission.revoke_permission(
+   p_object_id integer,
+   p_grantee_id integer,
+   p_privilege varchar
+) RETURNS integer AS $$
+DECLARE
+BEGIN
+    delete from acs_permissions
+    where object_id = p_object_id
+    and grantee_id = p_grantee_id
+    and privilege = p_privilege;
 
-                   select permission_p__privilege, permission_p__privilege
+    return 0; 
+END;
+$$ LANGUAGE plpgsql;
 
-                   union all
 
-                   select aph.privilege, aph.child_privilege
-                   from privilege_ancestors pa join acs_privilege_hierarchy aph
-                   on aph.child_privilege = pa.privilege
 
-                ) select
-                  p.object_id, oc.orig_obj_id
-                  from
-                  acs_permissions p
-                  join  party_approved_member_map pap on pap.party_id   =  p.grantee_id
-                  join  privilege_ancestors pa  on  pa.privilege  =  p.privilege
-                  join  object_context oc on  p.object_id =  oc.context_id
-                  where pap.member_id = permission_p__party_id
-              ;
-        END; 
-        $$ LANGUAGE plpgsql stable;
 
-        -- recursive site_nodes END
+---
+--- Functions for backwards compatibility
+---
+select define_function_args('acs_permission__permission_p','object_id,party_id,privilege');
+DROP FUNCTION IF EXISTS acs_permission__permission_p(integer, integer, varchar);
+CREATE OR REPLACE FUNCTION acs_permission__permission_p(
+       p_object_id integer,
+       p_party_id  integer,
+       p_privilege varchar
+) RETURNS boolean AS $$
+BEGIN
+  RETURN acs_permission.permission_p(p_object_id, p_party_id, p_privilege);
+END; 
+$$ LANGUAGE plpgsql stable;
 
-    ELSE
 
-        CREATE OR REPLACE FUNCTION acs_permission__permission_p(
-           permission_p__object_id integer,
-           permission_p__party_id integer,
-           permission_p__privilege varchar
-        ) RETURNS boolean AS $$
-        DECLARE
-            exists_p                          boolean;
-        BEGIN
-          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 stable;
+select define_function_args('acs_permission__permission_p_recursive_array','objects,party_id,privilege');
+DROP FUNCTION IF EXISTS acs_permission__permission_p_recursive_array(integer[], integer, varchar);
+CREATE OR REPLACE FUNCTION acs_permission__permission_p_recursive_array(
+       p_objects   integer[],
+       p_party_id  integer, 
+       p_privilege varchar
+) RETURNS table (object_id integer, orig_object_id integer) AS $$
+  SELECT acs_permission.permission_p_recursive_array($1, $2, $3);
+$$ LANGUAGE sql stable;
 
-    END IF;
-    raise notice 'ending permissions recursive -- vguerra';
 
-    return null;
+select define_function_args('acs_permission__grant_permission','object_id,grantee_id,privilege');
+DROP FUNCTION IF EXISTS acs_permission__grant_permission(integer, integer, varchar);
+CREATE OR REPLACE FUNCTION acs_permission__grant_permission(
+   p_object_id integer,
+   p_grantee_id integer,
+   p_privilege varchar
+) RETURNS integer AS $$
+DECLARE
+BEGIN
+  RETURN acs_permission.grant_permission(p_object_id, p_grantee_id, p_privilege);
 END; 
-$inline_0$ LANGUAGE plpgsql;
+$$ LANGUAGE plpgsql;
 
-select inline_0();
-drop function inline_0();
+
+select define_function_args('acs_permission__revoke_permission','object_id,grantee_id,privilege');
+DROP FUNCTION IF EXISTS acs_permission__revoke_permission(integer, integer, varchar);
+CREATE OR REPLACE FUNCTION acs_permission__revoke_permission(
+   p_object_id integer,
+   p_grantee_id integer,
+   p_privilege varchar
+) RETURNS integer AS $$
+DECLARE
+BEGIN
+    RETURN acs_permission.revoke_permission(p_object_id, p_grantee_id, p_privilege);
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+--
+-- Local variables:
+--   mode: sql
+--   indent-tabs-mode: nil
+-- End:
