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.2 -r1.3 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 15 Mar 2001 01:37:48 -0000 1.2 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 16 Mar 2001 02:50:06 -0000 1.3 @@ -56,6 +56,238 @@ 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 varchar(4000) +); + +create index priv_hier_sortkey_idx on +acs_privilege_hierarchy_index (tree_sortkey); + + +-- This big ugly 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 +-- paths in the tree. As such, tree queries cannot be represented by the +-- usual tree query methods used for openacs. + +-- FIXME: simplify this if possible. There's got to be a better way. +-- also need to create a delete trigger. + +-- 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 finding 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 like (h2.tree_sortkey || '%') +-- and h2.tree_sortkey < h1.tree_sortkey; + + +create function acs_priv_hier_insert_tr () returns opaque as ' +declare + v_parent_sk varchar; + max_key varchar; + new_key varchar; + pkey varchar; + clr_keys_p boolean; + child_exists_p boolean; + parent_exists_p boolean; + child_has_other_parents_p boolean; + v_rec record; + v_rec2 record; +begin + -- find out if the parent or the child of thie relation exists + + select count(*) > 0 into child_exists_p + from acs_privilege_hierarchy_index + where privilege = new.child_privilege; + + select count(*) > 0 into parent_exists_p + from acs_privilege_hierarchy_index + where child_privilege = new.privilege; + + -- simple case - just insert the new privilege and child privilege + + if NOT child_exists_p and NOT parent_exists_p then + + -- find the next key on the first level and insert the privilege + + select max(tree_sortkey) into max_key + from acs_privilege_hierarchy_index + where tree_level(tree_sortkey) = 1; + + new_key := ''/'' || tree_next_key(max_key); + + insert into acs_privilege_hierarchy_index + (privilege, child_privilege, tree_sortkey) + values + (new.privilege, new.child_privilege, new_key); + + -- and enter the child privilege on its own row with the child + -- privilege the same as the parent privilege. + + new_key := new_key || ''/00''; + + insert into acs_privilege_hierarchy_index + (privilege, child_privilege, tree_sortkey) + values + (new.child_privilege, new.child_privilege, new_key); + + else if child_exists_p and NOT parent_exists_p then + + -- child exists, so first insert the parent privilege + + select max(tree_sortkey) into max_key + from acs_privilege_hierarchy_index + where tree_level(tree_sortkey) = 1; + + new_key := ''/'' || tree_next_key(max_key); + pkey := new_key; + + insert into acs_privilege_hierarchy_index + (privilege, child_privilege, tree_sortkey) + values + (new.privilege, new.child_privilege, new_key); + + + -- before inserting the child privilege, check to see if + -- the child privilege is a child for another privilege already + -- in the table. + + select count(*) > 0 into child_has_other_parents_p + from acs_privilege_hierarchy_index + where child_privilege = new.child_privilege + and NOT ((privilege, child_privilege) = + (new.privilege, new.child_privilege)); + if NOT child_has_other_parents_p then + + -- loop over all of the child privileges + + for v_rec in select privilege, child_privilege, tree_sortkey + from acs_privilege_hierarchy_index + where privilege = new.child_privilege + LOOP + -- loop over all of the child privileges of the child + -- privilege. + + clr_keys_p := ''t''; + for v_rec2 in select privilege, child_privilege, tree_sortkey + from acs_privilege_hierarchy_index + where tree_sortkey + like v_rec.tree_sortkey || ''%'' + order by tree_sortkey + LOOP + + -- clear all the childs children sortkeys so that + -- new sortkeys can be calculated. + + if clr_keys_p then + update acs_privilege_hierarchy_index + set tree_sortkey = null + where tree_sortkey + like v_rec.tree_sortkey || ''%''; + clr_keys_p := ''f''; + end if; + + -- find the parent key of this privilege + + select coalesce(max(tree_sortkey),'''') into v_parent_sk + from acs_privilege_hierarchy_index + where child_privilege = v_rec2.privilege + and child_privilege <> privilege; + + -- and find the next key for this level + + select max(tree_sortkey) into max_key + from acs_privilege_hierarchy_index + where privilege = v_rec2.privilege + and child_privilege <> privilege; + new_key := v_parent_sk || ''/'' || tree_next_key(max_key); + -- now update the key with new value + + update acs_privilege_hierarchy_index + set tree_sortkey = new_key + where privilege = v_rec2.privilege + and child_privilege = v_rec2.child_privilege + and tree_sortkey = null; + end LOOP; + end LOOP; + else + + -- this child privilege is a child of an existing privilege. + -- so get the first existing child and its children + + for v_rec in select privilege, child_privilege, tree_sortkey + from acs_privilege_hierarchy_index + where child_privilege = new.child_privilege + LOOP + + -- now copy the subtree and insert under the newly inserted + -- privilege. + + for v_rec2 in select privilege, child_privilege, tree_sortkey + from acs_privilege_hierarchy_index + where tree_sortkey + like v_rec.tree_sortkey || ''/%'' + order by tree_sortkey + LOOP + + -- calc the parent key of this privilege + + select coalesce(max(tree_sortkey),'''') into v_parent_sk + from acs_privilege_hierarchy_index + where child_privilege = v_rec2.privilege + and child_privilege <> privilege; + + -- now find the next key for this level + + select max(tree_sortkey) into max_key + from acs_privilege_hierarchy_index + where privilege = v_rec2.privilege + and child_privilege <> privilege + and tree_sortkey like pkey || ''/%''; + new_key := v_parent_sk || ''/'' || tree_next_key(max_key); + + -- insert (copy) the privilege + + insert into acs_privilege_hierarchy_index + (privilege, child_privilege, tree_sortkey) + values + (v_rec2.privilege, v_rec2.child_privilege, new_key); + end LOOP; + exit; + end LOOP; + end if; + end if; end if; + + return new; + +end;' language 'plpgsql'; + +create trigger acs_priv_hier_insert_tr before insert +on acs_privilege_hierarchy for each row +execute procedure acs_priv_hier_insert_tr (); + --create table acs_privilege_method_rules ( -- privilege not null constraint acs_priv_method_rules_priv_fk -- references acs_privileges (privilege), @@ -221,14 +453,25 @@ create index acs_permissions_grantee_idx on acs_permissions (grantee_id); create index acs_permissions_privilege_idx on acs_permissions (privilege); +-- create 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; + create 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; + where p2.privilege in (select distinct h2.child_privilege + from + acs_privilege_hierarchy_index h1, + acs_privilege_hierarchy_index h2 + where + h1.privilege = p1.privilege + and h2.tree_sortkey like h1.tree_sortkey || '%'); create view acs_permissions_all as select op.object_id, p.grantee_id, p.privilege