Index: openacs-4/packages/acs-kernel/sql/oracle/acs-permissions-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/acs-permissions-create.sql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/acs-kernel/sql/oracle/acs-permissions-create.sql 17 Mar 2003 21:58:22 -0000 1.5 +++ openacs-4/packages/acs-kernel/sql/oracle/acs-permissions-create.sql 21 Mar 2003 14:57:44 -0000 1.6 @@ -11,7 +11,7 @@ -- -- @creation-date 2000-08-13 -- --- @cvs-id acs-permissions-create.sql,v 1.10.2.2 2001/01/12 22:59:20 oumi Exp +-- @cvs-id $Id$ -- @@ -37,225 +37,24 @@ --' create table acs_privileges ( - privilege varchar(100) not null constraint acs_privileges_pk + privilege varchar2(100) not null constraint acs_privileges_pk primary key, - pretty_name varchar(100), - pretty_plural varchar(100) + pretty_name varchar2(100), + pretty_plural varchar2(100) ); create table acs_privilege_hierarchy ( - privilege varchar(100) not null - constraint acs_priv_hier_priv_fk + privilege 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 + child_privilege not null constraint acs_priv_hier_child_priv_fk references acs_privileges (privilege), constraint acs_privilege_hierarchy_pk primary key (privilege, child_privilege) ); +-- create bitmap index acs_priv_hier_child_priv_idx on acs_privilege_hierarchy (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 -); - -create index priv_hier_sortkey_idx on -acs_privilege_hierarchy_index (tree_sortkey); - --- Added table to materialize view that previously used --- acs_privilege_descendant_map name --- --- 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) - -); - --- 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 --- it has a complication in that the same privileges can exist in more than one --- path in the tree. As such, tree queries cannot be represented by the --- usual tree query methods used for openacs. - --- DCW, 2001-03-15. - --- usage: queries directly on acs_privilege_hierarchy don't seem to occur --- in many places. Rather it seems that acs_privilege_hierarchy is --- used to build the view: acs_privilege_descendant_map. I did however --- find one tree query in content-perms.sql that looks like the --- following: - --- select privilege, child_privilege from acs_privilege_hierarchy --- connect by prior privilege = child_privilege --- start with child_privilege = 'cm_perm' - --- This query is used to find all of the ancestor permissions of 'cm_perm'. --- The equivalent query for the postgresql tree-query model would be: - --- select h2.privilege --- from acs_privilege_hierarchy_index h1, --- acs_privilege_hierarchy_index h2 --- where h1.child_privilege = 'cm_perm' --- and h1.tree_sortkey between h2.tree_sortkey and tree_right(h2.tree_sortkey) --- and h2.tree_sortkey <> h1.tree_sortkey; - --- Also since acs_privilege_descendant_map is simply a path enumeration of --- acs_privilege_hierarchy, we should be able to replace the above connect-by --- with: - --- select privilege --- from acs_privilege_descendant_map --- where descendant = 'cm_perm' - --- This would be better, since the same query could be used for both oracle --- and postgresql. - -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 (); - -create function priv_recurse_subtree(varbit, varchar) -returns integer as ' -declare - nkey alias for $1; - child_priv alias for $2; - new_value integer; - v_rec record; - new_key varbit; -begin - - -- now iterate over all of the children of the - -- previous node. - - for v_rec in select privilege, child_privilege - from acs_privilege_hierarchy - where privilege = child_priv - - LOOP - - -- calculate the next key for this level and parent - - select max(tree_leaf_key_to_int(tree_sortkey)) into new_value - from acs_privilege_hierarchy_index - where tree_sortkey between nkey and tree_right(nkey) - and tree_level(tree_sortkey) = tree_level(nkey) + 1; - - new_key := tree_next_key(nkey, new_value); - - -- insert the new child node. - - insert into acs_privilege_hierarchy_index - (privilege, child_privilege, tree_sortkey) - values - (v_rec.privilege, v_rec.child_privilege, new_key); - - -- keep recursing down until no more children are found - - PERFORM priv_recurse_subtree(new_key, v_rec.child_privilege); - - end LOOP; - - -- no children found, so insert the child node as its own separate - -- node. - - if NOT FOUND then - insert into acs_privilege_hierarchy_index - (privilege, child_privilege, tree_sortkey) - values - (child_priv, child_priv, tree_next_key(nkey, null)); - end if; - - return null; - -end;' language 'plpgsql'; - --create table acs_privilege_method_rules ( -- privilege not null constraint acs_priv_method_rules_priv_fk -- references acs_privileges (privilege), @@ -294,228 +93,245 @@ -- were granted moderate on a user. --' -create function acs_privilege__create_privilege (varchar,varchar,varchar) -returns integer as ' -declare - create_privilege__privilege alias for $1; - create_privilege__pretty_name alias for $2; -- default null - create_privilege__pretty_plural alias for $3; -- default null -begin +--create or replace view acs_privilege_method_map +--as select r1.privilege, pmr.object_type, pmr.method +-- from acs_privileges r1, acs_privileges r2, acs_privilege_method_rules pmr +-- where r2.privilege in (select distinct rh.child_privilege +-- from acs_privilege_hierarchy rh +-- start with privilege = r1.privilege +-- connect by prior child_privilege = privilege +-- union +-- select r1.privilege +-- from dual) +-- and r2.privilege = pmr.privilege; + +create or replace package acs_privilege +as + + procedure create_privilege ( + privilege in acs_privileges.privilege%TYPE, + pretty_name in acs_privileges.pretty_name%TYPE default null, + pretty_plural in acs_privileges.pretty_plural%TYPE default null + ); + + procedure drop_privilege ( + privilege in acs_privileges.privilege%TYPE + ); + + procedure add_child ( + privilege in acs_privileges.privilege%TYPE, + child_privilege in acs_privileges.privilege%TYPE + ); + + procedure remove_child ( + privilege in acs_privileges.privilege%TYPE, + child_privilege in acs_privileges.privilege%TYPE + ); + +end; +/ +show errors + +create or replace package body acs_privilege +as + + procedure create_privilege ( + privilege in acs_privileges.privilege%TYPE, + pretty_name in acs_privileges.pretty_name%TYPE default null, + pretty_plural in acs_privileges.pretty_plural%TYPE default null + ) + is + begin insert into acs_privileges (privilege, pretty_name, pretty_plural) values - (create_privilege__privilege, - create_privilege__pretty_name, - create_privilege__pretty_plural); - - return 0; -end;' language 'plpgsql'; + (create_privilege.privilege, + create_privilege.pretty_name, + create_privilege.pretty_plural); + end; -create function acs_privilege__create_privilege (varchar) -returns integer as ' -declare - create_privilege__privilege alias for $1; -begin - return acs_privilege__create_privilege(create_privilege__privilege, null, null); -end;' language 'plpgsql'; - - -create function acs_privilege__drop_privilege (varchar) -returns integer as ' -declare - drop_privilege__privilege alias for $1; -begin + procedure drop_privilege ( + privilege in acs_privileges.privilege%TYPE + ) + is + begin delete from acs_privileges - where privilege = drop_privilege__privilege; + where privilege = drop_privilege.privilege; + end; - return 0; -end;' language 'plpgsql'; - -create function acs_privilege__add_child (varchar,varchar) -returns integer as ' -declare - add_child__privilege alias for $1; - add_child__child_privilege alias for $2; -begin + procedure add_child ( + privilege in acs_privileges.privilege%TYPE, + child_privilege in acs_privileges.privilege%TYPE + ) + is + begin insert into acs_privilege_hierarchy (privilege, child_privilege) values - (add_child__privilege, add_child__child_privilege); + (add_child.privilege, add_child.child_privilege); + end; - return 0; -end;' language 'plpgsql'; - -create function acs_privilege__remove_child (varchar,varchar) -returns integer as ' -declare - remove_child__privilege alias for $1; - remove_child__child_privilege alias for $2; -begin + procedure remove_child ( + privilege in acs_privileges.privilege%TYPE, + child_privilege in acs_privileges.privilege%TYPE + ) + is + begin delete from acs_privilege_hierarchy - where privilege = remove_child__privilege - and child_privilege = remove_child__child_privilege; + where privilege = remove_child.privilege + and child_privilege = remove_child.child_privilege; + end; - return 0; -end;' language 'plpgsql'; +end; +/ +show errors + ------------------------------------ -- OPERATIONAL LEVEL: PERMISSIONS -- ------------------------------------ create table acs_permissions ( - object_id integer not null + object_id not null constraint acs_permissions_on_what_id_fk references acs_objects (object_id), - grantee_id integer not null + grantee_id not null constraint acs_permissions_grantee_id_fk references parties (party_id), - privilege varchar(100) not null - constraint acs_permissions_priv_fk + privilege not null constraint acs_permissions_priv_fk references acs_privileges (privilege), constraint acs_permissions_pk primary key (object_id, grantee_id, privilege) ); create index acs_permissions_grantee_idx on acs_permissions (grantee_id); +-- create bitmap index acs_permissions_privilege_idx on acs_permissions (privilege); create index acs_permissions_privilege_idx on acs_permissions (privilege); --- Added table to materialize view that previously used --- acs_privilege_descendant_map name --- --- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003 +create or replace view acs_privilege_descendant_map +as select p1.privilege, p2.privilege as descendant + from acs_privileges p1, acs_privileges p2 + where p2.privilege in (select child_privilege + from acs_privilege_hierarchy + start with privilege = p1.privilege + connect by prior child_privilege = privilege) + or p2.privilege = p1.privilege; --- 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 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 +create or replace view acs_permissions_all as select op.object_id, p.grantee_id, p.privilege from acs_object_paths op, acs_permissions p where op.ancestor_id = p.object_id; -create view acs_object_grantee_priv_map +create or replace view acs_object_grantee_priv_map as select a.object_id, a.grantee_id, m.descendant as privilege from acs_permissions_all a, acs_privilege_descendant_map m where a.privilege = m.privilege; --- New fast version of acs_object_party_privilege_map +-- Fast new acs_object_party_privilege_map based on the denormalized +-- party_approved_member_map. You may now use this map without fear. -create view acs_object_party_privilege_map as +create or replace 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; -create view all_object_party_privilege_map as +-- Kept to avoid breaking existing code, should eventually go away. + +create or replace 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 --- 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 --- slow down permissions-checking which is known to have performance --- problems already. +--create or replace view acs_object_party_method_map +--as select opp.object_id, opp.party_id, pm.object_type, pm.method +-- from acs_object_party_privilege_map opp, acs_privilege_method_map pm +-- where opp.privilege = pm.privilege; --- (OpenACS - DanW) +create or replace package acs_permission +as -create table acs_permissions_lock ( - lck integer -); + procedure grant_permission ( + object_id acs_permissions.object_id%TYPE, + grantee_id acs_permissions.grantee_id%TYPE, + privilege acs_permissions.privilege%TYPE + ); -create function acs_permissions_lock_tr () returns opaque as ' -begin - raise EXCEPTION ''FOR LOCKING ONLY, NO DML STATEMENTS ALLOWED''; - return null; -end;' language 'plpgsql'; + procedure revoke_permission ( + object_id acs_permissions.object_id%TYPE, + grantee_id acs_permissions.grantee_id%TYPE, + privilege acs_permissions.privilege%TYPE + ); -create trigger acs_permissions_lock_tr -before insert or update or delete on acs_permissions_lock -for each row execute procedure acs_permissions_lock_tr(); + function permission_p ( + object_id acs_objects.object_id%TYPE, + party_id parties.party_id%TYPE, + privilege acs_privileges.privilege%TYPE + ) return char; -create function acs_permission__grant_permission (integer, integer, varchar) -returns integer as ' -declare - grant_permission__object_id alias for $1; - grant_permission__grantee_id alias for $2; - grant_permission__privilege alias for $3; - exists_p boolean; -begin - lock table acs_permissions_lock; +end acs_permission; +/ +show errors - select count(*) > 0 into exists_p - from acs_permissions - where object_id = grant_permission__object_id - and grantee_id = grant_permission__grantee_id - and privilege = grant_permission__privilege; +create or replace package body acs_permission +as + procedure grant_permission ( + object_id acs_permissions.object_id%TYPE, + grantee_id acs_permissions.grantee_id%TYPE, + privilege acs_permissions.privilege%TYPE + ) + as + begin + insert into acs_permissions + (object_id, grantee_id, privilege) + values + (object_id, grantee_id, privilege); + exception + when dup_val_on_index then + return; + end grant_permission; + -- + procedure revoke_permission ( + object_id acs_permissions.object_id%TYPE, + grantee_id acs_permissions.grantee_id%TYPE, + privilege acs_permissions.privilege%TYPE + ) + as + begin + delete from acs_permissions + where object_id = revoke_permission.object_id + and grantee_id = revoke_permission.grantee_id + and privilege = revoke_permission.privilege; + end revoke_permission; - if not exists_p then + function permission_p ( + object_id acs_objects.object_id%TYPE, + party_id parties.party_id%TYPE, + privilege acs_privileges.privilege%TYPE + ) return char + as + exists_p char(1); + begin - insert into acs_permissions - (object_id, grantee_id, privilege) - values - (grant_permission__object_id, grant_permission__grantee_id, - grant_permission__privilege); + select decode(count(*),0,'f','t') into exists_p + from dual where 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 if; + return exists_p; - -- exception - -- when dup_val_on_index then - -- return; + end permission_p; + -- +end acs_permission; +/ +show errors - return 0; -end;' language 'plpgsql'; - - --- procedure revoke_permission -create function acs_permission__revoke_permission (integer, integer, varchar) -returns integer as ' -declare - revoke_permission__object_id alias for $1; - revoke_permission__grantee_id alias for $2; - revoke_permission__privilege alias for $3; -begin - lock table acs_permissions_lock; - - delete from acs_permissions - where object_id = revoke_permission__object_id - and grantee_id = revoke_permission__grantee_id - and privilege = revoke_permission__privilege; - - return 0; -end;' language 'plpgsql'; - --- Really speedy version of permission_p written by Don Baccus - -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 - 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';