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.16 -r1.136.2.17 --- openacs-4/packages/acs-kernel/acs-kernel.info 1 Jul 2016 14:04:39 -0000 1.136.2.16 +++ openacs-4/packages/acs-kernel/acs-kernel.info 5 Jul 2016 16:35:23 -0000 1.136.2.17 @@ -9,15 +9,15 @@ f t - + OpenACS Core Team Routines and data models providing the foundation for OpenACS-based Web services. - 2015-10-04 + 2016-07-05 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.2.2 -r1.39.2.3 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 1 Jul 2016 14:04:39 -0000 1.39.2.2 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 5 Jul 2016 16:35:23 -0000 1.39.2.3 @@ -214,7 +214,6 @@ --- added select define_function_args('priv_recurse_subtree','nkey,child_priv'); -- @@ -290,7 +289,6 @@ --- added select define_function_args('acs_privilege__create_privilege','privilege,pretty_name;null,pretty_plural;null'); -- @@ -332,7 +330,6 @@ --- added select define_function_args('acs_privilege__drop_privilege','privilege'); -- @@ -352,7 +349,6 @@ --- added select define_function_args('acs_privilege__add_child','privilege,child_privilege'); -- @@ -375,7 +371,6 @@ --- added select define_function_args('acs_privilege__remove_child','privilege,child_privilege'); -- @@ -459,6 +454,7 @@ and pdm.privilege = p.privilege and pamm.party_id = p.grantee_id; + -- -- Obsolete and deprecated view. -- @@ -494,68 +490,29 @@ --- 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; - - --- procedure revoke_permission - - --- added -select define_function_args('acs_permission__revoke_permission','object_id,grantee_id,privilege'); - +-- Actually, at least all permission functions should be defined this +-- way, keeping the old "__" notation around for backwards +-- compatibility for custom packages. -- --- procedure acs_permission__revoke_permission/3 +-- TODO: handling of schema names in define_function_args -- -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 $$ -DECLARE -BEGIN - delete from acs_permissions - where object_id = revoke_permission__object_id - and grantee_id = revoke_permission__grantee_id - and privilege = revoke_permission__privilege; +CREATE SCHEMA acs_permission; - return 0; -END; -$$ LANGUAGE plpgsql; - -select define_function_args('acs_permission__permission_p','object_id,party_id,privilege'); - -- --- procedure acs_permission__permission_p/3 +-- 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 +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; @@ -565,9 +522,9 @@ RETURN EXISTS (WITH RECURSIVE object_context(object_id, context_id) AS ( - SELECT permission_p__object_id, permission_p__object_id + SELECT p_object_id, p_object_id FROM acs_objects - WHERE object_id = permission_p__object_id + WHERE object_id = p_object_id UNION ALL @@ -580,7 +537,7 @@ ), privilege_ancestors(privilege, child_privilege) AS ( - SELECT permission_p__privilege, permission_p__privilege + SELECT p_privilege, p_privilege UNION ALL @@ -593,23 +550,22 @@ 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 + WHERE pap.member_id = p_party_id ); END; $$ LANGUAGE plpgsql stable; --- for tsearch -- --- procedure acs_permission__permission_p_recursive_array/3 +-- 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. -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 +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; @@ -619,41 +575,191 @@ 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 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 + 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 - + 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 + 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; + WHERE pap.member_id = 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'; +-- + +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'); + + 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; + + +-- +-- 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; + + +-- +-- 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; + + return 0; +END; +$$ LANGUAGE plpgsql; + + + + +--- +--- 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; + + +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 $$ +BEGIN + RETURN QUERY SELECT acs_permission.permission_p_recursive_array(p_objects, p_party_id, p_privilege); +END; +$$ LANGUAGE plpgsql stable; + + +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; +$$ LANGUAGE plpgsql; + + +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 Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d10-5.9.1d11.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d10-5.9.1d11.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.1d10-5.9.1d11.sql 5 Jul 2016 16:35:23 -0000 1.1.2.1 @@ -0,0 +1,280 @@ +-- +-- 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. +-- +-- 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 +-- + +DO $$ +DECLARE + v_found boolean; +BEGIN + SELECT exists(select schema_name FROM information_schema.schemata WHERE schema_name = 'acs_permission') + INTO v_found; + + if v_found IS FALSE then + + CREATE SCHEMA acs_permission; + + end if; +END$$; + +-- +-- procedure acs_permission.permission_p/3 +-- +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 + v_security_context_root := acs__magic_object_id('security_context_root'); + + RETURN EXISTS (WITH RECURSIVE + object_context(object_id, context_id) AS ( + + 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 + + ), 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 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; + + +-- +-- 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. + +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'); + + RETURN QUERY WITH RECURSIVE + object_context(obj_id, context_id, orig_obj_id) AS ( + + 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 ( + + 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 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; + + +-- +-- 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'; +-- + +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'); + + 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; + + + +-- +-- 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; + + +-- +-- 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; + + return 0; +END; +$$ LANGUAGE plpgsql; + + + + +--- +--- 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; + + +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 $$ +BEGIN + RETURN QUERY SELECT acs_permission.permission_p_recursive_array(p_objects, p_party_id, p_privilege); +END; +$$ LANGUAGE plpgsql stable; + + +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; +$$ LANGUAGE plpgsql; + + +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; + + Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d6-5.9.1d7.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d6-5.9.1d7.sql,v diff -u -N -r1.1.2.1 -r1.1.2.2 --- openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d6-5.9.1d7.sql 1 Jul 2016 04:44:17 -0000 1.1.2.1 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d6-5.9.1d7.sql 5 Jul 2016 16:35:23 -0000 1.1.2.2 @@ -1,15 +1,36 @@ - -- --- Use a better name for attribute "tag" in party_approved_member_map --- -alter table party_approved_member_map rename tag to originating_rel_id; - +-- The DO statement is used to allow this script to be run multiple +-- times without raising exceptions -- --- Create an "identity relationship" --- -select acs_object__new(-10, 'relationship') from dual; -insert into acs_rels (rel_id, rel_type, object_id_one, object_id_two) values (-10, 'relationship', 0, 0); +DO $$ +DECLARE + v_found boolean; +BEGIN + -- + -- Was the column already renamed? + -- + SELECT exists( + SELECT column_name + FROM information_schema.columns + WHERE table_name = 'party_approved_member_map' and column_name = 'originating_rel_id' + ) INTO v_found; + + if v_found IS FALSE then + -- + -- Use a better name for attribute "tag" in party_approved_member_map + -- + alter table party_approved_member_map rename tag to originating_rel_id; + + -- + -- Create an "identity relationship" + -- + select acs_object__new(-10, 'relationship') from dual; + insert into acs_rels (rel_id, rel_type, object_id_one, object_id_two) values (-10, 'relationship', 0, 0); + + end if; +END$$; + -- -- Use the new identity relation instead of value "0" -- @@ -27,17 +48,39 @@ except select rel_id from acs_rels); -- --- Add a foreign key +-- Add a foreign key ... +-- ... and let the script run multiple times... -- -alter table party_approved_member_map +ALTER TABLE party_approved_member_map +DROP CONSTRAINT IF EXISTS party_member_rel_id_fk; + +ALTER TABLE party_approved_member_map ADD CONSTRAINT party_member_rel_id_fk foreign key (originating_rel_id) references acs_rels on delete cascade; --- speed up referential integrity -create index party_member_party_idx on party_approved_member_map(party_id); -create index party_member_originating_idx on party_approved_member_map(originating_rel_id); +DO $$ +DECLARE + v_found boolean; +BEGIN + -- + -- Was the index already created? + -- + SELECT exists( + SELECT relname from pg_class + WHERE relname ='party_member_party_idx' + ) into v_found; + + if v_found IS FALSE then + -- + -- speed up referential integrity + -- + create index party_member_party_idx on party_approved_member_map(party_id); + create index party_member_originating_idx on party_approved_member_map(originating_rel_id); + end if; +END$$; + -- -- Redefine the stored procedures/functions referring to the attribute -- "tag". 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 -r1.1.2.2 -r1.1.2.3 --- openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d7-5.9.1d8.sql 1 Jul 2016 08:53:30 -0000 1.1.2.2 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d7-5.9.1d8.sql 5 Jul 2016 16:35:23 -0000 1.1.2.3 @@ -6,6 +6,7 @@ -- -- procedure acs_permission__permission_p/3 -- +DROP FUNCTION IF EXISTS acs_permission__permission_p(integer, integer, varchar); CREATE OR REPLACE FUNCTION acs_permission__permission_p( permission_p__object_id integer, permission_p__party_id integer, @@ -57,7 +58,7 @@ -- -- procedure acs_permission__permission_p_recursive_array/3 -- - +DROP FUNCTION IF EXISTS acs_permission__permission_p_recursive_array(integer[], integer, varchar); CREATE OR REPLACE FUNCTION acs_permission__permission_p_recursive_array( permission_p__objects integer[], permission_p__party_id integer, Index: openacs-4/packages/acs-tcl/acs-tcl.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/acs-tcl.info,v diff -u -N -r1.70.2.16 -r1.70.2.17 --- openacs-4/packages/acs-tcl/acs-tcl.info 22 May 2016 19:20:40 -0000 1.70.2.16 +++ openacs-4/packages/acs-tcl/acs-tcl.info 5 Jul 2016 16:35:22 -0000 1.70.2.17 @@ -9,7 +9,7 @@ f t - + OpenACS The Kernel Tcl API library. 2016-05-15 @@ -18,9 +18,9 @@ GPL version 2 3 - + - + Index: openacs-4/packages/acs-tcl/tcl/acs-kernel-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/acs-kernel-procs-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-tcl/tcl/acs-kernel-procs-oracle.xql 5 Jul 2016 16:35:23 -0000 1.1.2.1 @@ -0,0 +1,13 @@ + + + + oracle8.1.6 + + + + SELECT 1 from DUAL + + + + + Index: openacs-4/packages/acs-tcl/tcl/acs-kernel-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/acs-kernel-procs-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-tcl/tcl/acs-kernel-procs-postgresql.xql 5 Jul 2016 16:35:23 -0000 1.1.2.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + SELECT exists ( + SELECT 1 FROM information_schema.schemata + WHERE schema_name = : schema_name + ) + + + + + Index: openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.tcl,v diff -u -N -r1.10.2.1 -r1.10.2.2 --- openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.tcl 10 Sep 2015 08:21:55 -0000 1.10.2.1 +++ openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.tcl 5 Jul 2016 16:35:23 -0000 1.10.2.2 @@ -10,10 +10,47 @@ @return 1 if a user with admin privileges exists, 0 otherwise. } { - return [db_string admin_exists_p {} -default 0] + ad_acs_require_basic_schemata + + return [db_string admin_exists_p { + select 1 as admin_exists_p + from dual + where exists ( + select 1 + from acs_permissions p, + party_approved_member_map m, + users u, + acs_magic_objects amo + where amo.name = 'security_context_root' + and p.object_id = amo.object_id + and p.grantee_id = m.party_id + and u.user_id = m.member_id + and acs_permission.permission_p(amo.object_id, u.user_id, 'admin') + ) + } -default 0] } +ad_proc -private ad_acs_require_basic_schemata {} { + This is a transitional code to ensure that the SQL schemata + required for botostrapping (i.e. before the upgrade script) are + already installed. + +} { + set schema_name "acs_permission" + if {![db_string has_schema {}]} { + set kernelSqlDir "$::acs::rootdir/packages/acs-kernel/sql/[db_driverkey {}]/upgrade" + set files "upgrade-5.9.1d10-5.9.1d11.sql" + foreach file $files { + set fn $kernelSqlDir/$file + if {[file readable $fn]} { + ns_log notice "bootstrap: upgrading sql file $fn" + db_source_sql_file -callback apm_dummy_callback $fn + } + } + } +} + ad_proc -public ad_acs_admin_node {} { @return The node id of the ACS administration service if it is mounted, 0 otherwise. Index: openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.xql,v diff -u -N -r1.4.24.1 -r1.4.24.2 --- openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.xql 1 Jul 2016 13:32:40 -0000 1.4.24.1 +++ openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.xql 5 Jul 2016 16:35:23 -0000 1.4.24.2 @@ -1,21 +1,6 @@ - - - - select 1 as admin_exists_p - from dual - where exists (select 1 - from acs_object_party_privilege_map m, users u, acs_magic_objects amo - where m.object_id = amo.object_id - and amo.name = 'security_context_root' - and m.party_id = u.user_id - and m.privilege = 'admin') - - - - Index: openacs-4/packages/acs-tcl/tcl/acs-permissions-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/acs-permissions-procs-oracle.xql,v diff -u -N -r1.12 -r1.12.2.1 --- openacs-4/packages/acs-tcl/tcl/acs-permissions-procs-oracle.xql 27 Oct 2014 16:40:05 -0000 1.12 +++ openacs-4/packages/acs-tcl/tcl/acs-permissions-procs-oracle.xql 5 Jul 2016 16:35:23 -0000 1.12.2.1 @@ -47,13 +47,12 @@ - - - begin - :1 := acs_permission.permission_p(:object_id, :party_id, :privilege); - end; - + + + select distinct o.title, p.party_id + from acs_object_party_privilege_map p, acs_objects o + where p.object_id = :object_id and p.privilege = :privilege and o.object_id = p.party_id + - Index: openacs-4/packages/acs-tcl/tcl/acs-permissions-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/acs-permissions-procs-postgresql.xql,v diff -u -N -r1.11 -r1.11.4.1 --- openacs-4/packages/acs-tcl/tcl/acs-permissions-procs-postgresql.xql 2 Apr 2013 11:05:18 -0000 1.11 +++ openacs-4/packages/acs-tcl/tcl/acs-permissions-procs-postgresql.xql 5 Jul 2016 16:35:23 -0000 1.11.4.1 @@ -24,8 +24,7 @@ - select acs_object__name(:object_id) - from dual + select acs_object__name(:object_id) from dual @@ -37,11 +36,12 @@ - - - select acs_permission__permission_p(:object_id, :party_id, :privilege) - + + + select distinct o.title, p.party_id + from acs_permission.parties_with_object_privilege(:object_id, :privilege) p, acs_objects o + where p.party_id = o.object_id + - - + Index: openacs-4/packages/acs-tcl/tcl/acs-permissions-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/acs-permissions-procs.tcl,v diff -u -N -r1.33.2.2 -r1.33.2.3 --- openacs-4/packages/acs-tcl/tcl/acs-permissions-procs.tcl 1 Mar 2016 07:27:42 -0000 1.33.2.2 +++ openacs-4/packages/acs-tcl/tcl/acs-permissions-procs.tcl 5 Jul 2016 16:35:23 -0000 1.33.2.3 @@ -153,7 +153,9 @@ # We have a thread-local cache here global permission__permission_p__cache if { ![info exists permission__permission_p__cache($party_id,$object_id,$privilege)] } { - set permission__permission_p__cache($party_id,$object_id,$privilege) [expr {[db_exec_plsql select_permission_p {}] ? 1 : 0 }] + set permission__permission_p__cache($party_id,$object_id,$privilege) [db_string select_permission_p { + select acs_permission.permission_p(:object_id, :party_id, :privilege) from dual + }] } return $permission__permission_p__cache($party_id,$object_id,$privilege) } @@ -268,7 +270,6 @@ @param creation_user Optionally specify creation_user directly as an optimization. Otherwise a query will be executed. - @param party_id The party to have or not have write permission. @see permission::write_permission_p @@ -279,7 +280,22 @@ } } +ad_proc -public permission::get_parties_with_permission { + {-object_id:required} + {-privilege "admin"} +} { + Return a list of lists of party_id and acs_object.title, + having a given privilege on the given object + @param obect_id + @param privilege + + @see permission::permission_p +} { + return [db_list_of_lists get_parties {}] +} + + # Local variables: # mode: tcl # tcl-indent-level: 4