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.42 -r1.42.2.1 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 1 Nov 2018 08:38:00 -0000 1.42 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 8 Oct 2022 20:07:17 -0000 1.42.2.1 @@ -18,14 +18,21 @@ -- KNOWLEDGE LEVEL: PRIVILEGES AND ACTIONS -- --------------------------------------------- -create table acs_privileges ( +CREATE TABLE acs_privileges ( 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 ( +COMMENT ON TABLE acs_privileges is ' + Privileges share a global namespace. This is to avoid a + situation where granting the foo privilege on one type of object can + have an entirely different meaning than granting the foo privilege on + another type of object. +'; + +CREATE TABLE acs_privilege_hierarchy ( privilege varchar(100) not null constraint acs_priv_hier_priv_fk references acs_privileges (privilege), @@ -36,47 +43,28 @@ primary key (privilege, child_privilege) ); -create index acs_priv_hier_child_priv_idx on acs_privilege_hierarchy (child_privilege); +comment on table acs_privilege_hierarchy is ' + The acs_privilege_hierarchy gives us an easy way to say: The foo + privilege is a superset of the bar 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 acs_priv_hier_child_priv_idx ON acs_privilege_hierarchy (child_privilege); -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 OR REPLACE VIEW acs_privilege_descendant_map AS +WITH RECURSIVE privilege_desc(parent, child) AS ( + SELECT child_privilege as parent, child_privilege as child FROM acs_privilege_hierarchy +UNION ALL + SELECT privilege as parent, privilege as child FROM + (SELECT privilege FROM acs_privilege_hierarchy + EXCEPT + SELECT child_privilege FROM acs_privilege_hierarchy) identity +UNION ALL + SELECT h.privilege as parent, pd.child + FROM acs_privilege_hierarchy h, privilege_desc pd + WHERE pd.parent = h.child_privilege +) SELECT privilege_desc.parent, privilege_desc.child FROM privilege_desc; -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); - --- Gustaf (Jan 2009): interesting enough, the index above is never --- used on openacs.org and can be most likely dropped. The index below --- (together with acs_obj_ctx_idx_object_id_idx) makes real-world --- applications more than a factor of 10 faster (openacs/download and --- openacs/download/one-revision?revision_id=2089636) -create index acs_priv_desc_map_privilege_idx on acs_privilege_descendant_map (privilege); - -- 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 @@ -119,177 +107,6 @@ -- and PostgreSQL. - --- --- procedure acs_priv_hier_ins_del_tr/0 --- -CREATE OR REPLACE FUNCTION acs_priv_hier_ins_del_tr( - -) RETURNS trigger 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 OR REPLACE FUNCTION acs_priv_del_tr () RETURNS trigger AS $$ -BEGIN - - delete from acs_privilege_descendant_map - where privilege = old.privilege; - - return old; - -END; -$$ LANGUAGE plpgsql; - -create trigger acs_priv_del_tr before delete -on acs_privileges for each row -execute procedure acs_priv_del_tr (); - - - -select define_function_args('priv_recurse_subtree','nkey,child_priv'); - --- --- procedure priv_recurse_subtree/2 --- -CREATE OR REPLACE FUNCTION priv_recurse_subtree( - nkey varbit, - child_priv varchar -) RETURNS integer AS $$ -DECLARE - 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; - -comment on table acs_privileges is ' - Privileges share a global namespace. This is to avoid a - situation where granting the foo privilege on one type of object can - have an entirely different meaning than granting the foo privilege on - another type of object. -'; - -comment on table acs_privilege_hierarchy is ' - The acs_privilege_hierarchy gives us an easy way to say: The foo - privilege is a superset of the bar privilege. -'; - - - select define_function_args('acs_privilege__create_privilege','privilege,pretty_name;null,pretty_plural;null'); -- @@ -417,50 +234,25 @@ create index acs_permissions_privilege_idx on acs_permissions (privilege); create index acs_permissions_object_id_idx on acs_permissions(object_id); --- Added table to materialize view that previously used --- acs_privilege_descendant_map name -- --- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003 +-- The following view was replaced by acs_permission.permissions_all() +-- in Jul 6, 2016 +-- +-- create 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; --- 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_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; -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 -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 -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 - -create view acs_object_party_privilege_map as -select c.object_id, pdm.descendant as privilege, pamm.member_id as party_id -from acs_object_context_index c, acs_permissions p, acs_privilege_descendant_map pdm, - party_approved_member_map pamm -where c.ancestor_id = p.object_id - and pdm.privilege = p.privilege - and pamm.party_id = p.grantee_id; - - -- -- Obsolete and deprecated view. -- -create view all_object_party_privilege_map as -select * from acs_object_party_privilege_map; +-- 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.