Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 14 Mar 2001 04:39:10 -0000 1.1 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 17 Mar 2001 16:00:40 -0000 1.2 @@ -214,36 +214,43 @@ -- show errors -create function inline_1 () -returns integer as ' -begin +begin; + -------------------------------------------------------------- -- Some privilege that will be fundamental to all objects. -- -------------------------------------------------------------- - PERFORM acs_privilege__create_privilege(''read'', null, null); - PERFORM acs_privilege__create_privilege(''write'', null, null); - PERFORM acs_privilege__create_privilege(''create'', null, null); - PERFORM acs_privilege__create_privilege(''delete'', null, null); - PERFORM acs_privilege__create_privilege(''admin'', null, null); + select acs_privilege__create_privilege('read', null, null); + select acs_privilege__create_privilege('write', null, null); + select acs_privilege__create_privilege('create', null, null); + select acs_privilege__create_privilege('delete', null, null); + select acs_privilege__create_privilege('admin', null, null); --------------------------------------------------------- -- Administrators can read, write, create, and delete. -- --------------------------------------------------------- - PERFORM acs_privilege__add_child(''admin'', ''read''); - PERFORM acs_privilege__add_child(''admin'', ''write''); - PERFORM acs_privilege__add_child(''admin'', ''create''); - PERFORM acs_privilege__add_child(''admin'', ''delete''); + -- temporarily drop this trigger to avoid a data-change violation + -- on acs_privilege_hierarchy_index while updating the child privileges. - return 0; -end;' language 'plpgsql'; + drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy; -select inline_1 (); + select acs_privilege__add_child('admin', 'read'); + select acs_privilege__add_child('admin', 'write'); + select acs_privilege__add_child('admin', 'create'); -drop function inline_1 (); + -- re-enable the trigger before the last insert to force the + -- acs_privilege_hierarchy_index table to be updated. + 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 (); + select acs_privilege__add_child('admin', 'delete'); + +end; + + -- show errors create function inline_2 () 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.6 -r1.7 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 17 Mar 2001 01:19:53 -0000 1.6 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 17 Mar 2001 16:00:40 -0000 1.7 @@ -89,8 +89,8 @@ -- 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: +-- 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, @@ -99,69 +99,20 @@ -- and h1.tree_sortkey like (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: -create function priv_recurse_subtree(varchar, varchar, varchar) -returns integer as ' -declare - nkey alias for $1; - priv alias for $2; - child_priv alias for $3; - new_key varchar; - v_rec record; -begin +-- select privilege +-- from acs_privilege_descendant_map +-- where descendant = 'cm_perm' - -- 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 +-- This would be better, since the same query could be used for both oracle +-- and postgresql. - LOOP - - -- calculate the next key for this level and parent - - select tree_next_key(max(tree_sortkey)) into new_key - from acs_privilege_hierarchy_index - where tree_sortkey like nkey || ''/%'' - and tree_sortkey not like nkey || ''/%/%''; - - new_key := nkey || ''/'' || new_key; - - -- 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.privilege, - 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, nkey || ''/00''); - end if; - - return null; - -end;' language 'plpgsql'; - -drop function acs_priv_hier_ins_del_tr (); create function acs_priv_hier_ins_del_tr () returns opaque as ' declare new_key varchar; - child_exists_p boolean; - parent_exists_p boolean; deleted_p boolean; v_rec record; begin @@ -223,6 +174,62 @@ on acs_privilege_hierarchy for each row execute procedure acs_priv_hier_ins_del_tr (); +create function priv_recurse_subtree(varchar, varchar, varchar) +returns integer as ' +declare + nkey alias for $1; + priv alias for $2; + child_priv alias for $3; + new_key varchar; + v_rec record; +begin + + -- now iterate over all of the children of the parent 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 tree_next_key(max(tree_sortkey)) into new_key + from acs_privilege_hierarchy_index + where tree_sortkey like nkey || ''/%'' + and tree_sortkey not like nkey || ''/%/%''; + + new_key := nkey || ''/'' || new_key; + + -- 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.privilege, + 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, nkey || ''/00''); + 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), @@ -332,7 +339,6 @@ return 0; end;' language 'plpgsql'; - -- procedure add_child create function acs_privilege__add_child (varchar,varchar) returns integer as ' @@ -348,7 +354,6 @@ return 0; end;' language 'plpgsql'; - -- procedure remove_child create function acs_privilege__remove_child (varchar,varchar) returns integer as '