Index: openacs-4/packages/acs-kernel/acs-kernel.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/acs-kernel.info,v
diff -u -N -r1.136.2.13 -r1.136.2.14
--- openacs-4/packages/acs-kernel/acs-kernel.info 1 Jul 2016 04:44:18 -0000 1.136.2.13
+++ openacs-4/packages/acs-kernel/acs-kernel.info 1 Jul 2016 04:57:03 -0000 1.136.2.14
@@ -9,15 +9,15 @@
f
t
-
+
OpenACS Core Team
Routines and data models providing the foundation for OpenACS-based Web services.
2015-10-04
OpenACS
The OpenACS kernel contains the core datamodel create and drop scripts for such things as objects, groups, partiies and the supporting PL/SQL and PL/pgSQL procedures.
3
-
+
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 -N -r1.39 -r1.39.2.1
--- 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 1 Jul 2016 04:57:03 -0000 1.39.2.1
@@ -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
@@ -402,20 +401,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);
@@ -507,7 +506,7 @@
BEGIN
insert into acs_permissions
(object_id, grantee_id, privilege)
- values
+ values
(grant_permission__object_id, grant_permission__grantee_id,
grant_permission__privilege);
@@ -544,148 +543,115 @@
END;
$$ LANGUAGE plpgsql;
--- Really speedy version of permission_p written by Don Baccus
-
-
--- added
select define_function_args('acs_permission__permission_p','object_id,party_id,privilege');
-
-CREATE FUNCTION inline_0()
-RETURNS integer AS $inline_0$
+--
+-- 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
+ v_security_context_root integer;
BEGIN
+ v_security_context_root := acs__magic_object_id('security_context_root');
- raise notice 'starting permissions recursive -- vguerra';
+ RETURN EXISTS (WITH RECURSIVE
+ object_context(object_id, context_id) AS (
- IF cmp_pg_version('8.4') >= 0 THEN
- -- recursive site_nodes recursive - START
+ SELECT permission_p__object_id, permission_p__object_id
+ FROM acs_objects
+ WHERE object_id = permission_p__object_id
- --
- -- 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 (
+ UNION ALL
- select permission_p__object_id, permission_p__object_id
- from acs_objects
- where object_id = permission_p__object_id
+ 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
- union all
+ ), privilege_ancestors(privilege, child_privilege) 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 permission_p__privilege, permission_p__privilege
+
+ UNION ALL
- ), privilege_ancestors(privilege, child_privilege) AS (
+ SELECT aph.privilege, aph.child_privilege
+ FROM privilege_ancestors pa
+ JOIN acs_privilege_hierarchy aph ON aph.child_privilege = pa.privilege
- select permission_p__privilege, permission_p__privilege
-
- union all
+ )
+ 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;
- select aph.privilege, aph.child_privilege
- from privilege_ancestors pa join acs_privilege_hierarchy aph
- on aph.child_privilege = pa.privilege
- ) 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;
+-- for tsearch
+--
+-- procedure acs_permission__permission_p_recursive_array/3
+--
+perform define_function_args('acs_permission__permission_p_recursive_array','a_objects,a_party_id,a_privilege');
- -- for tsearch
+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 $$
+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 (
- 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 (
+ SELECT unnest(permission_p__objects), unnest(permission_p__objects), unnest(permission_p__objects)
- select unnest(permission_p__objects), unnest(permission_p__objects), unnest(permission_p__objects)
+ UNION ALL
- 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 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 (
- ), privilege_ancestors(privilege, child_privilege) AS (
+ SELECT permission_p__privilege, permission_p__privilege
- select permission_p__privilege, permission_p__privilege
+ UNION ALL
- 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 = permission_p__party_id
- ;
- END;
- $$ LANGUAGE plpgsql stable;
-
- -- recursive site_nodes END
-
- 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;
-
- END IF;
- raise notice 'ending permissions recursive -- vguerra';
-
- return null;
+ )
+ 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;
-$inline_0$ LANGUAGE plpgsql;
+$$ LANGUAGE plpgsql stable;
-select inline_0();
-drop function inline_0();
+
+--
+-- Local variables:
+-- mode: sql
+-- indent-tabs-mode: nil
+-- End:
Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d7-5.9.1d8.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d7-5.9.1d8.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d7-5.9.1d8.sql 1 Jul 2016 04:57:03 -0000 1.1.2.1
@@ -0,0 +1,107 @@
+--
+-- Slightly faster (around 10%) versions for the base permission query functions.
+-- In the new versions the lookup of acs__magic_object_id was moved out of the loop.
+--
+
+--
+-- 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
+ v_security_context_root integer;
+BEGIN
+ v_security_context_root := acs__magic_object_id('security_context_root');
+
+ RETURN EXISTS (WITH RECURSIVE
+ object_context(object_id, context_id) AS (
+
+ SELECT permission_p__object_id, permission_p__object_id
+ FROM acs_objects
+ WHERE object_id = permission_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
+
+ ), privilege_ancestors(privilege, child_privilege) AS (
+
+ SELECT permission_p__privilege, permission_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 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;
+
+
+-- for tsearch
+--
+-- procedure acs_permission__permission_p_recursive_array/3
+--
+
+perform define_function_args('acs_permission__permission_p_recursive_array','a_objects,a_party_id,a_privilege');
+
+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 $$
+DECLARE
+ v_security_context_root integer;
+BEGIN
+ v_security_context_root := acs__magic_object_id('security_context_root');
+
+ RETURN QUERY WITH RECURSIVE
+ object_context(obj_id, context_id, orig_obj_id) AS (
+
+ SELECT unnest(permission_p__objects), unnest(permission_p__objects), unnest(permission_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 (
+
+ SELECT permission_p__privilege, permission_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 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;