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.4 -r1.5 --- openacs-4/packages/acs-kernel/sql/oracle/acs-permissions-create.sql 21 Jun 2002 21:59:31 -0000 1.4 +++ openacs-4/packages/acs-kernel/sql/oracle/acs-permissions-create.sql 17 Mar 2003 21:58:22 -0000 1.5 @@ -11,7 +11,7 @@ -- -- @creation-date 2000-08-13 -- --- @cvs-id $Id$ +-- @cvs-id acs-permissions-create.sql,v 1.10.2.2 2001/01/12 22:59:20 oumi Exp -- @@ -37,24 +37,225 @@ --' create table acs_privileges ( - privilege varchar2(100) not null constraint acs_privileges_pk + privilege varchar(100) not null constraint acs_privileges_pk primary key, - pretty_name varchar2(100), - pretty_plural varchar2(100) + pretty_name varchar(100), + pretty_plural varchar(100) ); create table acs_privilege_hierarchy ( - privilege not null constraint acs_priv_hier_priv_fk + privilege varchar(100) not null + constraint acs_priv_hier_priv_fk references acs_privileges (privilege), - child_privilege not null constraint acs_priv_hier_child_priv_fk + child_privilege varchar(100) 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), @@ -93,356 +294,228 @@ -- were granted moderate on a user. --' ---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 +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 insert into acs_privileges (privilege, pretty_name, pretty_plural) values - (create_privilege.privilege, - create_privilege.pretty_name, - create_privilege.pretty_plural); - end; + (create_privilege__privilege, + create_privilege__pretty_name, + create_privilege__pretty_plural); + + return 0; +end;' language 'plpgsql'; - procedure drop_privilege ( - privilege in acs_privileges.privilege%TYPE - ) - is - begin +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 delete from acs_privileges - where privilege = drop_privilege.privilege; - end; + where privilege = drop_privilege__privilege; - procedure add_child ( - privilege in acs_privileges.privilege%TYPE, - child_privilege in acs_privileges.privilege%TYPE - ) - is - begin + 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 insert into acs_privilege_hierarchy (privilege, child_privilege) values - (add_child.privilege, add_child.child_privilege); - end; + (add_child__privilege, add_child__child_privilege); - procedure remove_child ( - privilege in acs_privileges.privilege%TYPE, - child_privilege in acs_privileges.privilege%TYPE - ) - is - begin + 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 delete from acs_privilege_hierarchy - where privilege = remove_child.privilege - and child_privilege = remove_child.child_privilege; - end; + where privilege = remove_child__privilege + and child_privilege = remove_child__child_privilege; + return 0; +end;' language 'plpgsql'; -end; -/ -show errors - ------------------------------------ -- OPERATIONAL LEVEL: PERMISSIONS -- ------------------------------------ create table acs_permissions ( - object_id not null + object_id integer not null constraint acs_permissions_on_what_id_fk references acs_objects (object_id), - grantee_id not null + grantee_id integer not null constraint acs_permissions_grantee_id_fk references parties (party_id), - privilege not null constraint acs_permissions_priv_fk + privilege varchar(100) 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); -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; +-- 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_permissions_all +-- 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 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 or replace view acs_object_grantee_priv_map +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; --- The last two unions make sure that the_public gets expaned to all --- users plus 0 (the default user_id) we should probably figure out a --- better way to handle this eventually since this view is getting --- pretty freaking hairy. I'd love to be able to move this stuff into --- a Java middle tier. +-- New fast version of acs_object_party_privilege_map -create or replace view acs_object_party_privilege_map -as select ogpm.object_id, gmm.member_id as party_id, ogpm.privilege - from acs_object_grantee_priv_map ogpm, group_approved_member_map gmm - where ogpm.grantee_id = gmm.group_id - union - select ogpm.object_id, rsmm.member_id as party_id, ogpm.privilege - from acs_object_grantee_priv_map ogpm, rel_seg_approved_member_map rsmm - where ogpm.grantee_id = rsmm.segment_id - union - select object_id, grantee_id as party_id, privilege - from acs_object_grantee_priv_map - union - select object_id, u.user_id as party_id, privilege - from acs_object_grantee_priv_map m, users u - where m.grantee_id = -1 - union - select object_id, 0 as party_id, privilege - from acs_object_grantee_priv_map - where grantee_id = -1; +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; ----------------------------------------------------- --- ALTERNATE VIEW: ALL_OBJECT_PARTY_PRIVILEGE_MAP -- ----------------------------------------------------- +create view all_object_party_privilege_map as +select * from acs_object_party_privilege_map; --- This view is a helper for all_object_party_privilege_map -create or replace view acs_grantee_party_map as - select -1 as grantee_id, 0 as party_id from dual - union all - select -1 as grantee_id, user_id as party_id - from users - union all - select party_id as grantee_id, party_id - from parties - union all - select segment_id as grantee_id, member_id - from rel_seg_approved_member_map - union all - select group_id as grantee_id, member_id as party_id - from group_approved_member_map; --- This view is like acs_object_party_privilege_map, but does not --- necessarily return distinct rows. It may be *much* faster to join --- against this view instead of acs_object_party_privilege_map, and is --- usually not much slower. The tradeoff for the performance boost is --- increased complexity in your usage of the view. Example usage that I've --- found works well is: --- --- select DISTINCT --- my_table.* --- from my_table, --- (select object_id --- from all_object_party_privilege_map --- where party_id = :user_id and privilege = :privilege) oppm --- where oppm.object_id = my_table.my_id; --- +-- 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 all_object_party_privilege_map as -select /*+ ORDERED */ - op.object_id, - pdm.descendant as privilege, - gpm.party_id as party_id - from acs_object_paths op, - acs_permissions p, - acs_privilege_descendant_map pdm, - acs_grantee_party_map gpm - where op.ancestor_id = p.object_id - and pdm.privilege = p.privilege - and gpm.grantee_id = p.grantee_id; +-- (OpenACS - DanW) +create table acs_permissions_lock ( + lck integer +); ---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; +create function acs_permissions_lock_tr () returns opaque as ' +begin + raise EXCEPTION ''FOR LOCKING ONLY, NO DML STATEMENTS ALLOWED''; + return null; +end;' language 'plpgsql'; -create or replace package acs_permission -as +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(); - 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_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; - procedure revoke_permission ( - object_id acs_permissions.object_id%TYPE, - grantee_id acs_permissions.grantee_id%TYPE, - privilege acs_permissions.privilege%TYPE - ); + 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; - function permission_p ( - object_id acs_objects.object_id%TYPE, - party_id parties.party_id%TYPE, - privilege acs_privileges.privilege%TYPE - ) return char; + if not exists_p then -end acs_permission; -/ -show errors + insert into acs_permissions + (object_id, grantee_id, privilege) + values + (grant_permission__object_id, grant_permission__grantee_id, + 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 + end if; + + -- exception + -- when dup_val_on_index then + -- return; + + 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; - end revoke_permission; + where object_id = revoke_permission__object_id + and grantee_id = revoke_permission__grantee_id + and privilege = revoke_permission__privilege; - 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 - -- - -- direct permissions - select decode(count(*),0,'f','t') into exists_p - from dual where exists ( - select 'x' - from acs_object_grantee_priv_map - where object_id = permission_p.object_id - and grantee_id = permission_p.party_id - and privilege = permission_p.privilege); - if exists_p = 't' then - return 't'; - end if; - -- - -- public-like permissions - select decode(count(*),0,'f','t') into exists_p - from dual where exists ( - select 'x' - from acs_object_grantee_priv_map - where object_id = permission_p.object_id - and 0 = permission_p.party_id - and privilege = permission_p.privilege - and grantee_id = -1); - if exists_p = 't' then - return 't'; - end if; - -- - -- public permissions - select decode(count(*),0,'f','t') into exists_p - from dual where exists ( - select 'x' - from acs_object_grantee_priv_map m, users u - where object_id = permission_p.object_id - and u.user_id = permission_p.party_id - and privilege = permission_p.privilege - and m.grantee_id = -1); - if exists_p = 't' then - return 't'; - end if; - -- - -- group permmissions - select decode(count(*),0,'f','t') into exists_p - from dual where exists ( - select 'x' - from acs_object_grantee_priv_map ogpm, - group_approved_member_map gmm - where object_id = permission_p.object_id - and gmm.member_id = permission_p.party_id - and privilege = permission_p.privilege - and ogpm.grantee_id = gmm.group_id); - if exists_p = 't' then - return 't'; - end if; - -- - -- relational segment approved group - select decode(count(*),0,'f','t') into exists_p - from dual where exists ( - select 'x' - from acs_object_grantee_priv_map ogpm, - rel_seg_approved_member_map rsmm - where object_id = permission_p.object_id - and rsmm.member_id = permission_p.party_id - and privilege = permission_p.privilege - and ogpm.grantee_id = rsmm.segment_id); - if exists_p = 't' then - return 't'; - end if; - return exists_p; - end; - -- -end acs_permission; -/ -show errors + 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'; Index: openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql 18 Feb 2003 20:53:45 -0000 1.4 +++ openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql 17 Mar 2003 21:58:22 -0000 1.5 @@ -3,145 +3,174 @@ -- -- @author rhs@mit.edu -- @creation-date 2000-08-22 --- @cvs-id $Id$ +-- @cvs-id groups-body-create.sql,v 1.1.4.1 2001/01/12 22:58:33 mbryzek Exp -- -------------- -- TRIGGERS -- -------------- -create or replace trigger membership_rels_up_tr -before update on membership_rels -for each row -begin - - if :new.member_state = :old.member_state then - return; - end if; +-- The insert trigger was dummied up in groups-create.sql, so we just need +-- to replace the trigger function, not create the trigger. - for map in (select group_id, element_id, rel_type - from group_element_index - where rel_id = :new.rel_id) - loop - if :new.member_state = 'approved' then - party_approved_member.add(map.group_id, map.element_id, map.rel_type); - else - party_approved_member.remove(map.group_id, map.element_id, map.rel_type); - end if; - end loop; +-- However, PG 7.3 introduces a new type "trigger" for the return type +-- needed for functions called by triggers. "create function" transmorgifies +-- the return type "opaque" to "trigger" so PG 7.2 dumps can be restored into +-- PG 7.3. But "create or replace" doesn't do it. We can't use "trigger" +-- because we currently are still supporting PG 7.2. Isn't life a pleasure? -end; -/ -show errors +-- I'm leaving the triggers we aren't overriding as "create or replace" because +-- this will be the right thing to do if the PG folks fix this problem or when +-- we drop support of PG 7.2 and no longer need to declare these as type "opaque" -create or replace trigger membership_rels_in_tr -after insert on membership_rels -for each row +drop trigger membership_rels_in_tr on membership_rels; +drop function membership_rels_in_tr (); + +create or replace function membership_rels_in_tr () returns opaque as ' declare v_object_id_one acs_rels.object_id_one%TYPE; v_object_id_two acs_rels.object_id_two%TYPE; v_rel_type acs_rels.rel_type%TYPE; - v_error varchar2(4000); + v_error text; + map record; begin -- First check if added this relation violated any relational constraints - v_error := rel_constraint.violation(:new.rel_id); + v_error := rel_constraint__violation(new.rel_id); if v_error is not null then - raise_application_error(-20000,v_error); + raise EXCEPTION ''-20000: %'', v_error; end if; select object_id_one, object_id_two, rel_type into v_object_id_one, v_object_id_two, v_rel_type from acs_rels - where rel_id = :new.rel_id; + where rel_id = new.rel_id; - -- Insert a row for me in the group_member_index. + -- Insert a row for me in the group_element_index. insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) values - (v_object_id_one, v_object_id_two, :new.rel_id, v_object_id_one, - v_rel_type, 'membership_rel'); + (v_object_id_one, v_object_id_two, new.rel_id, v_object_id_one, + v_rel_type, ''membership_rel''); - if :new.member_state = 'approved' then - party_approved_member.add(v_object_id_one, v_object_id_two, v_rel_type); + if new.member_state = ''approved'' then + perform party_approved_member__add(v_object_id_one, v_object_id_two, new.rel_id, v_rel_type); end if; -- For all groups of which I am a component, insert a - -- row in the group_member_index. - for map in (select distinct group_id + -- row in the group_element_index. + for map in select distinct group_id from group_component_map - where component_id = v_object_id_one) loop + where component_id = v_object_id_one + loop + insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) values - (map.group_id, v_object_id_two, :new.rel_id, v_object_id_one, - v_rel_type, 'membership_rel'); + (map.group_id, v_object_id_two, new.rel_id, v_object_id_one, + v_rel_type, ''membership_rel''); - if :new.member_state = 'approved' then - party_approved_member.add(map.group_id, v_object_id_two, v_rel_type); + if new.member_state = ''approved'' then + perform party_approved_member__add(map.group_id, v_object_id_two, new.rel_id, v_rel_type); end if; end loop; -end; -/ -show errors -create or replace trigger membership_rels_del_tr -before delete on membership_rels -for each row -declare - v_error varchar2(4000); + return new; + +end;' language 'plpgsql'; + +create trigger membership_rels_in_tr after insert on membership_rels +for each row execute procedure membership_rels_in_tr (); + +create or replace function membership_rels_up_tr () returns opaque as ' +declare + map record; begin + + if new.member_state = old.member_state then + return new; + end if; + + for map in select group_id, element_id, rel_type + from group_element_index + where rel_id = new.rel_id + loop + if new.member_state = ''approved'' then + perform party_approved_member__add(map.group_id, map.element_id, new.rel_id, map.rel_type); + else + perform party_approved_member__remove(map.group_id, map.element_id, new.rel_id, map.rel_type); + end if; + end loop; + + return new; + +end;' language 'plpgsql'; + +create trigger membership_rels_up_tr before update on membership_rels +for each row execute procedure membership_rels_up_tr (); + +create or replace function membership_rels_del_tr () returns opaque as ' +declare + v_error text; + map record; +begin -- First check if removing this relation would violate any relational constraints - v_error := rel_constraint.violation_if_removed(:old.rel_id); + v_error := rel_constraint__violation_if_removed(old.rel_id); if v_error is not null then - raise_application_error(-20000,v_error); + raise EXCEPTION ''-20000: %'', v_error; end if; - for map in (select group_id, element_id, rel_type - from group_element_index - where rel_id = :old.rel_id) + for map in select group_id, element_id, rel_type + from group_element_index + where rel_id = old.rel_id loop - party_approved_member.remove(map.group_id, map.element_id, map.rel_type); + perform party_approved_member__remove(map.group_id, map.element_id, old.rel_id, map.rel_type); end loop; delete from group_element_index - where rel_id = :old.rel_id; + where rel_id = old.rel_id; -end; -/ -show errors; + return old; -create or replace trigger composition_rels_in_tr -after insert on composition_rels -for each row +end;' language 'plpgsql'; + +create trigger membership_rels_del_tr before delete on membership_rels +for each row execute procedure membership_rels_del_tr (); + +drop trigger composition_rels_in_tr on composition_rels; +drop function composition_rels_in_tr (); + +create or replace function composition_rels_in_tr () returns opaque as ' declare v_object_id_one acs_rels.object_id_one%TYPE; v_object_id_two acs_rels.object_id_two%TYPE; v_rel_type acs_rels.rel_type%TYPE; - v_error varchar2(4000); + v_error text; + map record; begin -- First check if added this relation violated any relational constraints - v_error := rel_constraint.violation(:new.rel_id); + v_error := rel_constraint__violation(new.rel_id); + if v_error is not null then - raise_application_error(-20000,v_error); + raise EXCEPTION ''-20000: %'', v_error; end if; select object_id_one, object_id_two, rel_type into v_object_id_one, v_object_id_two, v_rel_type from acs_rels - where rel_id = :new.rel_id; + where rel_id = new.rel_id; -- Insert a row for me in group_element_index insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) values - (v_object_id_one, v_object_id_two, :new.rel_id, v_object_id_one, - v_rel_type, 'composition_rel'); + (v_object_id_one, v_object_id_two, new.rel_id, v_object_id_one, + v_rel_type, ''composition_rel''); -- Make my elements be elements of my new composite group insert into group_element_index @@ -160,17 +189,18 @@ -- For all direct or indirect containers of my new composite group, -- add me and add my elements - for map in (select distinct group_id + for map in select distinct group_id from group_component_map - where component_id = v_object_id_one) loop + where component_id = v_object_id_one + LOOP -- Add a row for me insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) values - (map.group_id, v_object_id_two, :new.rel_id, v_object_id_one, - v_rel_type, 'composition_rel'); + (map.group_id, v_object_id_two, new.rel_id, v_object_id_one, + v_rel_type, ''composition_rel''); -- Add rows for my elements insert into group_element_index @@ -188,39 +218,42 @@ and rel_id = m.rel_id); end loop; -end; -/ -show errors + return new; +end;' language 'plpgsql'; + +create trigger composition_rels_in_tr after insert on composition_rels +for each row execute procedure composition_rels_in_tr (); + -- -- TO DO: See if this can be optimized now that the member and component -- mapping tables have been combined -- -create or replace trigger composition_rels_del_tr -before delete on composition_rels -for each row +create or replace function composition_rels_del_tr () returns opaque as ' declare v_object_id_one acs_rels.object_id_one%TYPE; v_object_id_two acs_rels.object_id_two%TYPE; - n_rows integer; - v_error varchar2(4000); + n_rows integer; + v_error text; + map record; begin -- First check if removing this relation would violate any relational constraints - v_error := rel_constraint.violation_if_removed(:old.rel_id); + v_error := rel_constraint__violation_if_removed(old.rel_id); if v_error is not null then - raise_application_error(-20000,v_error); + raise EXCEPTION ''-20000: %'', v_error; end if; select object_id_one, object_id_two into v_object_id_one, v_object_id_two from acs_rels - where rel_id = :old.rel_id; + where rel_id = old.rel_id; - for map in (select * + for map in select * from group_component_map - where rel_id = :old.rel_id) loop + where rel_id = old.rel_id + LOOP delete from group_element_index - where rel_id = :old.rel_id; + where rel_id = old.rel_id; select count(*) into n_rows from group_component_map @@ -231,13 +264,13 @@ delete from group_element_index where group_id = map.group_id and container_id = map.component_id - and ancestor_rel_type = 'membership_rel'; + and ancestor_rel_type = ''membership_rel''; end if; end loop; - for map in (select * + for map in select * from group_component_map where group_id in (select group_id from group_component_map @@ -251,7 +284,8 @@ union select v_object_id_two from dual) - and group_contains_p(group_id, component_id, rel_id) = 'f') loop + and group_contains_p(group_id, component_id, rel_id) = ''f'' + LOOP delete from group_element_index where group_id = map.group_id @@ -267,41 +301,48 @@ delete from group_element_index where group_id = map.group_id and container_id = map.component_id - and ancestor_rel_type = 'membership_rel'; + and ancestor_rel_type = ''membership_rel''; end if; end loop; -end; -/ -show errors + return old; +end;' language 'plpgsql'; + +create trigger composition_rels_del_tr before delete on composition_rels +for each row execute procedure composition_rels_del_tr (); + +-- show errors + + -------------------- -- PACKAGE BODIES -- -------------------- -create or replace package body composition_rel -as +-- create or replace package body composition_rel +-- function new +select define_function_args('composition_rel__new','rel_id,rel_type;composition_rel,object_id_one,object_id_two,creation_user,creation_ip'); - function new ( - rel_id in composition_rels.rel_id%TYPE default null, - rel_type in acs_rels.rel_type%TYPE default 'composition_rel', - object_id_one in acs_rels.object_id_one%TYPE, - object_id_two in acs_rels.object_id_two%TYPE, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null - ) return composition_rels.rel_id%TYPE - is - v_rel_id integer; - begin - v_rel_id := acs_rel.new ( - rel_id => rel_id, - rel_type => rel_type, - object_id_one => object_id_one, - object_id_two => object_id_two, - context_id => object_id_one, - creation_user => creation_user, - creation_ip => creation_ip +create or replace function composition_rel__new (integer,varchar,integer,integer,integer,varchar) +returns integer as ' +declare + new__rel_id alias for $1; -- default null + rel_type alias for $2; -- default ''composition_rel'' + object_id_one alias for $3; + object_id_two alias for $4; + creation_user alias for $5; -- default null + creation_ip alias for $6; -- default null + v_rel_id integer; +begin + v_rel_id := acs_rel__new ( + new__rel_id, + rel_type, + object_id_one, + object_id_two, + object_id_one, + creation_user, + creation_ip ); insert into composition_rels @@ -310,509 +351,613 @@ (v_rel_id); return v_rel_id; - end; + +end;' language 'plpgsql'; - procedure delete ( - rel_id in composition_rels.rel_id%TYPE - ) - is - begin - acs_rel.delete(rel_id); - end; +-- function new +create or replace function composition_rel__new (integer,integer) +returns integer as ' +declare + object_id_one alias for $1; + object_id_two alias for $2; +begin + return composition_rel__new(null, + ''composition_rel'', + object_id_one, + object_id_two, + null, + null); +end;' language 'plpgsql'; - function check_path_exists_p ( - component_id in groups.group_id%TYPE, - container_id in groups.group_id%TYPE - ) return char - is - begin +-- procedure delete +create or replace function composition_rel__delete (integer) +returns integer as ' +declare + rel_id alias for $1; +begin + PERFORM acs_rel__delete(rel_id); + + return 0; +end;' language 'plpgsql'; + + +-- function check_path_exists_p +create or replace function composition_rel__check_path_exists_p (integer,integer) +returns boolean as ' +declare + component_id alias for $1; + container_id alias for $2; + row record; +begin if component_id = container_id then - return 't'; + return ''t''; end if; - for row in (select r.object_id_one as parent_id + for row in select r.object_id_one as parent_id from acs_rels r, composition_rels c where r.rel_id = c.rel_id - and r.object_id_two = component_id) loop - if check_path_exists_p(row.parent_id, container_id) = 't' then - return 't'; + and r.object_id_two = component_id + LOOP + if composition_rel__check_path_exists_p(row.parent_id, container_id) = ''t'' then + return ''t''; end if; end loop; - return 'f'; - end; + return ''f''; + +end;' language 'plpgsql'; - function check_index ( - component_id in groups.group_id%TYPE, - container_id in groups.group_id%TYPE - ) return char - is - result char(1); - n_rows integer; - begin - result := 't'; +-- function check_index +create or replace function composition_rel__check_index (integer,integer) +returns boolean as ' +declare + check_index__component_id alias for $1; + check_index__container_id alias for $2; + result boolean; + n_rows integer; + dc record; + r1 record; + r2 record; +begin + result := ''t''; + -- Loop through all the direct containers (DC) of COMPONENT_ID -- that are also contained by CONTAINER_ID and verify that the -- GROUP_COMPONENT_INDEX contains the (GROUP_ID, DC.REL_ID, -- CONTAINER_ID) triple. - for dc in (select r.rel_id, r.object_id_one as container_id + for dc in select r.rel_id, r.object_id_one as container_id from acs_rels r, composition_rels c where r.rel_id = c.rel_id - and r.object_id_two = component_id) loop + and r.object_id_two = check_index__component_id + LOOP - if check_path_exists_p(dc.container_id, - check_index.container_id) = 't' then - select decode(count(*),0,0,1) into n_rows + if composition_rel__check_path_exists_p(dc.container_id, + check_index__container_id) = ''t'' then + select case when count(*) = 0 then 0 else 1 end into n_rows from group_component_index - where group_id = check_index.container_id - and component_id = check_index.component_id + where group_id = check_index__container_id + and component_id = check_index__component_id and rel_id = dc.rel_id; if n_rows = 0 then - result := 'f'; - acs_log.error('composition_rel.check_representation', - 'Row missing from group_component_index for (' || - 'group_id = ' || container_id || ', ' || - 'component_id = ' || component_id || ', ' || - 'rel_id = ' || dc.rel_id || ')'); + result := ''f''; + PERFORM acs_log__error(''composition_rel.check_representation'', + ''Row missing from group_component_index for ('' || + ''group_id = '' || check_index__container_id || '', '' || + ''component_id = '' || check_index__component_id || '', '' || + ''rel_id = '' || dc.rel_id || '')''); end if; end if; end loop; -- Loop through all the containers of CONTAINER_ID. - for r1 in (select r.object_id_one as container_id + for r1 in select r.object_id_one as container_id from acs_rels r, composition_rels c where r.rel_id = c.rel_id - and r.object_id_two = check_index.container_id + and r.object_id_two = check_index__container_id union - select check_index.container_id - from dual) loop + select check_index__container_id as container_id + from dual + LOOP -- Loop through all the components of COMPONENT_ID and make a -- recursive call. - for r2 in (select r.object_id_two as component_id + for r2 in select r.object_id_two as component_id from acs_rels r, composition_rels c where r.rel_id = c.rel_id - and r.object_id_one = check_index.component_id + and r.object_id_one = check_index__component_id union - select check_index.component_id - from dual) loop - if (r1.container_id != check_index.container_id or - r2.component_id != check_index.component_id) and - check_index(r2.component_id, r1.container_id) = 'f' then - result := 'f'; + select check_index__component_id as component_id + from dual + LOOP + if (r1.container_id != check_index__container_id or + r2.component_id != check_index__component_id) and + composition_rel__check_index(r2.component_id, r1.container_id) = ''f'' then + result := ''f''; end if; end loop; end loop; return result; - end; + +end;' language 'plpgsql'; - function check_representation ( - rel_id in composition_rels.rel_id%TYPE - ) return char - is - container_id groups.group_id%TYPE; - component_id groups.group_id%TYPE; - result char(1); - begin - result := 't'; - if acs_object.check_representation(rel_id) = 'f' then - result := 'f'; +-- function check_representation +create or replace function composition_rel__check_representation (integer) +returns boolean as ' +declare + check_representation__rel_id alias for $1; + container_id groups.group_id%TYPE; + component_id groups.group_id%TYPE; + result boolean; + row record; +begin + result := ''t''; + + if acs_object__check_representation(check_representation__rel_id) = ''f'' then + result := ''f''; end if; select object_id_one, object_id_two into container_id, component_id from acs_rels - where rel_id = check_representation.rel_id; + where rel_id = check_representation__rel_id; - -- First let's check that the index has all the rows it should. - if check_index(component_id, container_id) = 'f' then - result := 'f'; + -- First let us check that the index has all the rows it should. + if composition_rel__check_index(component_id, container_id) = ''f'' then + result := ''f''; end if; - -- Now let's check that the index doesn't have any extraneous rows + -- Now let us check that the index doesn''t have any extraneous rows -- relating to this relation. - for row in (select * + for row in select * from group_component_index - where rel_id = check_representation.rel_id) loop - if check_path_exists_p(row.component_id, row.group_id) = 'f' then - result := 'f'; - acs_log.error('composition_rel.check_representation', - 'Extraneous row in group_component_index: ' || - 'group_id = ' || row.group_id || ', ' || - 'component_id = ' || row.component_id || ', ' || - 'rel_id = ' || row.rel_id || ', ' || - 'container_id = ' || row.container_id || '.'); + where rel_id = check_representation__rel_id + LOOP + if composition_rel__check_path_exists_p(row.component_id, row.group_id) = ''f'' then + result := ''f''; + PERFORM acs_log__error(''composition_rel.check_representation'', + ''Extraneous row in group_component_index: '' || + ''group_id = '' || row.group_id || '', '' || + ''component_id = '' || row.component_id || '', '' || + ''rel_id = '' || row.rel_id || '', '' || + ''container_id = '' || row.container_id || ''.''); end if; end loop; return result; - end; + +end;' language 'plpgsql'; -end composition_rel; -/ -show errors +-- show errors +-- create or replace package body membership_rel +-- function new +select define_function_args('membership_rel__new','rel_id,rel_type;membership_rel,object_id_one,object_id_two,member_state;approved,creation_user,creation_ip'); -create or replace package body membership_rel -as - - function new ( - rel_id in membership_rels.rel_id%TYPE default null, - rel_type in acs_rels.rel_type%TYPE default 'membership_rel', - object_id_one in acs_rels.object_id_one%TYPE, - object_id_two in acs_rels.object_id_two%TYPE, - member_state in membership_rels.member_state%TYPE default 'approved', - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null - ) return membership_rels.rel_id%TYPE - is - v_rel_id integer; - begin - v_rel_id := acs_rel.new ( - rel_id => rel_id, - rel_type => rel_type, - object_id_one => object_id_one, - object_id_two => object_id_two, - context_id => object_id_one, - creation_user => creation_user, - creation_ip => creation_ip +create or replace function membership_rel__new (integer,varchar,integer,integer,varchar,integer,varchar) +returns integer as ' +declare + new__rel_id alias for $1; -- default null + rel_type alias for $2; -- default ''membership_rel'' + object_id_one alias for $3; + object_id_two alias for $4; + new__member_state alias for $5; -- default ''approved'' + creation_user alias for $6; -- default null + creation_ip alias for $7; -- default null + v_rel_id integer; +begin + v_rel_id := acs_rel__new ( + new__rel_id, + rel_type, + object_id_one, + object_id_two, + object_id_one, + creation_user, + creation_ip ); insert into membership_rels (rel_id, member_state) values - (v_rel_id, new.member_state); + (v_rel_id, new__member_state); return v_rel_id; - end; + +end;' language 'plpgsql'; - procedure ban ( - rel_id in membership_rels.rel_id%TYPE - ) - is - begin +-- function new +create or replace function membership_rel__new (integer,integer) +returns integer as ' +declare + object_id_one alias for $1; + object_id_two alias for $2; +begin + return membership_rel__new(null, + ''membership_rel'', + object_id_one, + object_id_two, + ''approved'', + null, + null); +end;' language 'plpgsql'; + +-- procedure ban +create or replace function membership_rel__ban (integer) +returns integer as ' +declare + ban__rel_id alias for $1; +begin update membership_rels - set member_state = 'banned' - where rel_id = ban.rel_id; - end; + set member_state = ''banned'' + where rel_id = ban__rel_id; - procedure approve ( - rel_id in membership_rels.rel_id%TYPE - ) - is - begin + return 0; +end;' language 'plpgsql'; + + +-- procedure approve +create or replace function membership_rel__approve (integer) +returns integer as ' +declare + approve__rel_id alias for $1; +begin update membership_rels - set member_state = 'approved' - where rel_id = approve.rel_id; - end; + set member_state = ''approved'' + where rel_id = approve__rel_id; - procedure reject ( - rel_id in membership_rels.rel_id%TYPE - ) - is - begin + return 0; +end;' language 'plpgsql'; + + +-- procedure reject +create or replace function membership_rel__reject (integer) +returns integer as ' +declare + reject__rel_id alias for $1; +begin update membership_rels - set member_state = 'rejected' - where rel_id = reject.rel_id; - end; + set member_state = ''rejected'' + where rel_id = reject__rel_id; - procedure unapprove ( - rel_id in membership_rels.rel_id%TYPE - ) - is - begin + return 0; +end;' language 'plpgsql'; + + +-- procedure unapprove +create or replace function membership_rel__unapprove (integer) +returns integer as ' +declare + unapprove__rel_id alias for $1; +begin update membership_rels - set member_state = 'needs approval' - where rel_id = unapprove.rel_id; - end; + set member_state = ''needs approval'' + where rel_id = unapprove__rel_id; - procedure deleted ( - rel_id in membership_rels.rel_id%TYPE - ) - is - begin + return 0; +end;' language 'plpgsql'; + + +-- procedure deleted +create or replace function membership_rel__deleted (integer) +returns integer as ' +declare + deleted__rel_id alias for $1; +begin update membership_rels - set member_state = 'deleted' - where rel_id = deleted.rel_id; - end; + set member_state = ''deleted'' + where rel_id = deleted__rel_id; - procedure delete ( - rel_id in membership_rels.rel_id%TYPE - ) - is - begin - acs_rel.delete(rel_id); - end; + return 0; +end;' language 'plpgsql'; - function check_index ( - group_id in groups.group_id%TYPE, - member_id in parties.party_id%TYPE, - container_id in groups.group_id%TYPE - ) return char - is - result char(1); - n_rows integer; - begin +-- procedure delete +create or replace function membership_rel__delete (integer) +returns integer as ' +declare + rel_id alias for $1; +begin + PERFORM acs_rel__delete(rel_id); + + return 0; +end;' language 'plpgsql'; + + +-- function check_index +create or replace function membership_rel__check_index (integer,integer,integer) +returns boolean as ' +declare + check_index__group_id alias for $1; + check_index__member_id alias for $2; + check_index__container_id alias for $3; + result boolean; + n_rows integer; + row record; +begin + select count(*) into n_rows - from group_member_index - where group_id = check_index.group_id - and member_id = check_index.member_id - and container_id = check_index.container_id; + from group_element_index + where group_id = check_index__group_id + and member_id = check_index__member_id + and container_id = check_index__container_id; if n_rows = 0 then - result := 'f'; - acs_log.error('membership_rel.check_representation', - 'Row missing from group_member_index: ' || - 'group_id = ' || group_id || ', ' || - 'member_id = ' || member_id || ', ' || - 'container_id = ' || container_id || '.'); + result := ''f''; + PERFORM acs_log__error(''membership_rel.check_representation'', + ''Row missing from group_element_index: '' || + ''group_id = '' || check_index__group_id || '', '' || + ''member_id = '' || check_index__member_id || '', '' || + ''container_id = '' || check_index__container_id || ''.''); end if; - for row in (select r.object_id_one as container_id + for row in select r.object_id_one as container_id from acs_rels r, composition_rels c where r.rel_id = c.rel_id - and r.object_id_two = group_id) loop - if check_index(row.container_id, member_id, container_id) = 'f' then - result := 'f'; + and r.object_id_two = check_index__group_id + LOOP + if membership_rel__check_index(row.container_id, check_index__member_id, check_index__container_id) = ''f'' then + result := ''f''; end if; end loop; return result; - end; + +end;' language 'plpgsql'; - function check_representation ( - rel_id in membership_rels.rel_id%TYPE - ) return char - is - group_id groups.group_id%TYPE; - member_id parties.party_id%TYPE; - result char(1); - begin - result := 't'; - if acs_object.check_representation(rel_id) = 'f' then - result := 'f'; +-- function check_representation +create or replace function membership_rel__check_representation (integer) +returns boolean as ' +declare + check_representation__rel_id alias for $1; + group_id groups.group_id%TYPE; + member_id parties.party_id%TYPE; + result boolean; + row record; +begin + result := ''t''; + + if acs_object__check_representation(check_representation__rel_id) = ''f'' then + result := ''f''; end if; select r.object_id_one, r.object_id_two into group_id, member_id from acs_rels r, membership_rels m where r.rel_id = m.rel_id - and m.rel_id = check_representation.rel_id; + and m.rel_id = check_representation__rel_id; - if check_index(group_id, member_id, group_id) = 'f' then - result := 'f'; + if membership_rel__check_index(group_id, member_id, group_id) = ''f'' then + result := ''f''; end if; - for row in (select * + for row in select * from group_member_index - where rel_id = check_representation.rel_id) loop - if composition_rel.check_path_exists_p(row.container_id, - row.group_id) = 'f' then - result := 'f'; - acs_log.error('membership_rel.check_representation', - 'Extra row in group_member_index: ' || - 'group_id = ' || row.group_id || ', ' || - 'member_id = ' || row.member_id || ', ' || - 'container_id = ' || row.container_id || '.'); + where rel_id = check_representation__rel_id + LOOP + if composition_rel__check_path_exists_p(row.container_id, + row.group_id) = ''f'' then + result := ''f''; + PERFORM acs_log__error(''membership_rel.check_representation'', + ''Extra row in group_member_index: '' || + ''group_id = '' || row.group_id || '', '' || + ''member_id = '' || row.member_id || '', '' || + ''container_id = '' || row.container_id || ''.''); end if; end loop; return result; - end; + +end;' language 'plpgsql'; -end membership_rel; -/ -show errors + +-- create or replace package body acs_group +-- function new +select define_function_args('acs_group__new','group_id,object_type;group,creation_date;now(),creation_user,creation_ip,email,url,group_name,join_policy,context_id'); - - -create or replace package body acs_group -is - function new ( - group_id in groups.group_id%TYPE default null, - object_type in acs_objects.object_type%TYPE - default 'group', - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - email in parties.email%TYPE default null, - url in parties.url%TYPE default null, - group_name in groups.group_name%TYPE, - join_policy in groups.join_policy%TYPE default null, - context_id in acs_objects.context_id%TYPE default null - ) - return groups.group_id%TYPE - is - v_group_id groups.group_id%TYPE; - v_group_type_exists_p integer; - v_join_policy groups.join_policy%TYPE; - begin +create or replace function acs_group__new (integer,varchar,timestamptz,integer,varchar,varchar,varchar,varchar,varchar,integer) +returns integer as ' +declare + new__group_id alias for $1; -- default null + new__object_type alias for $2; -- default ''group'' + new__creation_date alias for $3; -- default now() + new__creation_user alias for $4; -- default null + new__creation_ip alias for $5; -- default null + new__email alias for $6; -- default null + new__url alias for $7; -- default null + new__group_name alias for $8; + new__join_policy alias for $9; -- default null + new__context_id alias for $10; -- default null + v_group_id groups.group_id%TYPE; + v_group_type_exists_p integer; + v_join_policy groups.join_policy%TYPE; +begin v_group_id := - party.new(group_id, object_type, creation_date, creation_user, - creation_ip, email, url, context_id); + party__new(new__group_id, new__object_type, new__creation_date, + new__creation_user, new__creation_ip, new__email, + new__url, new__context_id); - v_join_policy := join_policy; + v_join_policy := new__join_policy; - -- if join policy wasn't specified, select the default based on group type - if v_join_policy is null then + -- if join policy was not specified, select the default based on group type + if v_join_policy is null or v_join_policy = '''' then select count(*) into v_group_type_exists_p from group_types - where group_type = object_type; + where group_type = new__object_type; if v_group_type_exists_p = 1 then select default_join_policy into v_join_policy from group_types - where group_type = object_type; + where group_type = new__object_type; else - v_join_policy := 'open'; + v_join_policy := ''open''; end if; end if; insert into groups (group_id, group_name, join_policy) values - (v_group_id, group_name, v_join_policy); + (v_group_id, new__group_name, v_join_policy); - -- setup the permissible relationship types for this group + + -- DRB: we have to call nextval() directly because the select may + -- return more than one row. The sequence hack will only compute + -- one nextval value causing the insert to fail ("may" in PG, which + -- is actually broken. It should ALWAYS return exactly one value for + -- the view. In PG it may or may not depending on the optimizer''s + -- mood. PG group seems uninterested in acknowledging the fact that + -- this is a bug) + insert into group_rels (group_rel_id, group_id, rel_type) - select acs_object_id_seq.nextval, v_group_id, g.rel_type + select nextval(''t_acs_object_id_seq''), v_group_id, g.rel_type from group_type_rels g - where g.group_type = new.object_type; + where g.group_type = new__object_type; return v_group_id; - end new; + +end;' language 'plpgsql'; +-- function new +create or replace function acs_group__new (varchar) returns integer as ' +declare + gname alias for $1; +begin + return acs_group__new(null, + ''group'', + now(), + null, + null, + null, + null, + gname, + null, + null); +end;' language 'plpgsql'; - procedure delete ( - group_id in groups.group_id%TYPE - ) - is - begin +-- procedure delete +create or replace function acs_group__delete (integer) +returns integer as ' +declare + delete__group_id alias for $1; + row record; +begin -- Delete all segments defined for this group - for row in (select segment_id + for row in select segment_id from rel_segments - where group_id = acs_group.delete.group_id) loop - - rel_segment.delete(row.segment_id); - + where group_id = delete__group_id + LOOP + PERFORM rel_segment__delete(row.segment_id); end loop; -- Delete all the relations of any type to this group - for row in (select r.rel_id, t.package_name + for row in select r.rel_id, t.package_name from acs_rels r, acs_object_types t where r.rel_type = t.object_type - and (r.object_id_one = acs_group.delete.group_id - or r.object_id_two = acs_group.delete.group_id)) loop - execute immediate 'begin ' || row.package_name || '.delete(' || row.rel_id || '); end;'; + and (r.object_id_one = delete__group_id + or r.object_id_two = delete__group_id) + LOOP + execute ''select '' || row.package_name || ''__delete('' || row.rel_id || '')''; end loop; - party.delete(group_id); - end delete; + PERFORM party__delete(delete__group_id); - function name ( - group_id in groups.group_id%TYPE - ) - return varchar2 - is - group_name varchar2(200); - begin + return 0; +end;' language 'plpgsql'; + + +-- function name +create or replace function acs_group__name (integer) +returns varchar as ' +declare + name__group_id alias for $1; + name__group_name varchar(200); +begin select group_name - into group_name + into name__group_name from groups - where group_id = name.group_id; + where group_id = name__group_id; - return group_name; - end name; + return name__group_name; + +end;' language 'plpgsql'; - function member_p ( - party_id in parties.party_id%TYPE, - group_id in groups.group_id%TYPE, - cascade_membership char - ) - return char - is - m_result integer; - begin - - if cascade_membership = 't' then - select count(*) - into m_result +create or replace function acs_group__member_p (integer, integer, boolean) +returns boolean as ' +declare + p_party_id alias for $1; + p_group_id alias for $2; + p_cascade_membership alias for $3; +begin + if p_cascade_membership then + return count(*) > 0 from group_member_map - where group_id = member_p.group_id and - member_id = member_p.party_id; - - if m_result > 0 then - return 't'; - end if; + where group_id = p_group_id and + member_id = p_party_id; else - select count(*) - into m_result + return count(*) > 0 from acs_rels rels, all_object_party_privilege_map perm where perm.object_id = rels.rel_id - and perm.privilege = 'read' - and rels.rel_type = 'membership_rel' - and rels.object_id_one = member_p.group_id - and rels.object_id_two = member_p.party_id; - - if m_result > 0 then - return 't'; - end if; + and perm.privilege = ''read'' + and rels.rel_type = ''membership_rel'' + and rels.object_id_one = p_group_id + and rels.object_id_two = p_party_id; end if; +end;' language 'plpgsql'; - return 'f'; - end member_p; - function check_representation ( - group_id in groups.group_id%TYPE - ) return char - is - result char(1); - begin - result := 't'; - acs_log.notice('acs_group.check_representation', - 'Running check_representation on group ' || group_id); +-- function check_representation +create or replace function acs_group__check_representation (integer) +returns boolean as ' +declare + group_id alias for $1; + res boolean; + comp record; + memb record; +begin + res := ''t''; + PERFORM acs_log__notice(''acs_group.check_representation'', + ''Running check_representation on group '' || group_id); - if acs_object.check_representation(group_id) = 'f' then - result := 'f'; + if acs_object__check_representation(group_id) = ''f'' then + res := ''f''; end if; - for c in (select c.rel_id + for comp in select c.rel_id from acs_rels r, composition_rels c where r.rel_id = c.rel_id - and r.object_id_one = group_id) loop - if composition_rel.check_representation(c.rel_id) = 'f' then - result := 'f'; + and r.object_id_one = group_id + LOOP + if composition_rel__check_representation(comp.rel_id) = ''f'' then + res := ''f''; end if; end loop; - for m in (select m.rel_id + for memb in select m.rel_id from acs_rels r, membership_rels m where r.rel_id = m.rel_id - and r.object_id_one = group_id) loop - if membership_rel.check_representation(m.rel_id) = 'f' then - result := 'f'; + and r.object_id_one = group_id + LOOP + if membership_rel__check_representation(memb.rel_id) = ''f'' then + res := ''f''; end if; end loop; - acs_log.notice('acs_group.check_representation', - 'Done running check_representation on group ' || group_id); - return result; - end; + PERFORM acs_log__notice(''acs_group.check_representation'', + ''Done running check_representation on group '' || group_id); -end acs_group; -/ -show errors + return res; + +end;' language 'plpgsql'; + + + +-- show errors Index: openacs-4/packages/acs-kernel/sql/oracle/rel-segments-body-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/rel-segments-body-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-kernel/sql/oracle/rel-segments-body-create.sql 16 Sep 2002 19:15:57 -0000 1.2 +++ openacs-4/packages/acs-kernel/sql/oracle/rel-segments-body-create.sql 17 Mar 2003 21:58:22 -0000 1.3 @@ -3,7 +3,7 @@ -- -- @author Oumi Mehrotra oumi@arsdigita.com -- @creation-date 2000-11-22 --- @cvs-id $Id$ +-- @cvs-id rel-segments-body-create.sql,v 1.1.4.1 2001/01/12 22:58:33 mbryzek Exp -- Copyright (C) 1999-2000 ArsDigita Corporation -- This is free software distributed under the terms of the GNU Public @@ -14,146 +14,168 @@ -- PACKAGE BODY -- ------------------ -create or replace package body rel_segment -is - function new ( - segment_id in rel_segments.segment_id%TYPE default null, - object_type in acs_objects.object_type%TYPE - default 'rel_segment', - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - email in parties.email%TYPE default null, - url in parties.url%TYPE default null, - segment_name in rel_segments.segment_name%TYPE, - group_id in rel_segments.group_id%TYPE, - rel_type in rel_segments.rel_type%TYPE, - context_id in acs_objects.context_id%TYPE default null - ) return rel_segments.segment_id%TYPE - is - v_segment_id rel_segments.segment_id%TYPE; - begin +-- rel_segment__new -- full version +create function rel_segment__new (integer,varchar,timestamptz,integer,varchar,varchar,varchar,varchar,integer,varchar,integer) +returns integer as ' +declare + new__segment_id alias for $1; -- default null + object_type alias for $2; -- default ''rel_segment'' + creation_date alias for $3; -- default now() + creation_user alias for $4; -- default null + creation_ip alias for $5; -- default null + email alias for $6; -- default null + url alias for $7; -- default null + new__segment_name alias for $8; + new__group_id alias for $9; + new__rel_type alias for $10; + context_id alias for $11; -- default null + v_segment_id rel_segments.segment_id%TYPE; +begin v_segment_id := - party.new(segment_id, object_type, creation_date, creation_user, + party__new(new__segment_id, object_type, creation_date, creation_user, creation_ip, email, url, context_id); insert into rel_segments (segment_id, segment_name, group_id, rel_type) values - (v_segment_id, new.segment_name, new.group_id, new.rel_type); + (v_segment_id, new__segment_name, new__group_id, new__rel_type); return v_segment_id; - end new; + +end;' language 'plpgsql'; - procedure delete ( - segment_id in rel_segments.segment_id%TYPE - ) - is - begin +-- rel_segment__new -- overloaded version for specifying only non-default values +create function rel_segment__new (varchar,integer,varchar) +returns integer as ' +declare + new__segment_name alias for $1; + new__group_id alias for $2; + new__rel_type alias for $3; + v_segment_id rel_segments.segment_id%TYPE; +begin + v_segment_id := rel_segment__new(null, ''rel_segment'', now(), null, null, null, null, new__segment_name, new__group_id, new__rel_type, null); + + return v_segment_id; + +end;' language 'plpgsql'; + + +-- procedure delete +create function rel_segment__delete (integer) +returns integer as ' +declare + delete__segment_id alias for $1; + row record; +begin + -- remove all constraints on this segment - for row in (select constraint_id + for row in select constraint_id from rel_constraints - where rel_segment = rel_segment.delete.segment_id) loop + where rel_segment = delete__segment_id + LOOP - rel_constraint.delete(row.constraint_id); + PERFORM rel_constraint__delete(row.constraint_id); end loop; - party.delete(segment_id); + PERFORM party__delete(delete__segment_id); - end delete; + return 0; +end;' language 'plpgsql'; - -- EXPERIMENTAL / UNSTABLE -- use at your own risk - -- - function get ( - group_id in rel_segments.group_id%TYPE, - rel_type in rel_segments.rel_type%TYPE - ) return rel_segments.segment_id%TYPE - is - v_segment_id rel_segments.segment_id%TYPE; - begin + +-- function get +create function rel_segment__get (integer,varchar) +returns integer as ' +declare + get__group_id alias for $1; + get__rel_type alias for $2; + v_segment_id rel_segments.segment_id%TYPE; +begin select min(segment_id) into v_segment_id from rel_segments - where group_id = get.group_id - and rel_type = get.rel_type; + where group_id = get__group_id + and rel_type = get__rel_type; return v_segment_id; - end get; + +end;' language 'plpgsql'; +create function rel_segment__get_or_new(integer,varchar) returns integer as ' +declare + gid alias for $1; + typ alias for $2; +begin + return rel_segment__get_or_new(gid,typ,null); +end;' language 'plpgsql'; - -- EXPERIMENTAL / UNSTABLE -- use at your own risk - -- - -- This function simplifies the use of segments a little by letting - -- you not have to worry about creating and initializing segments. - -- If the segment you're interested in exists, this function - -- returns its segment_id. - -- If the segment you're interested in doesn't exist, this function - -- does a pretty minimal amount of initialization for the segment - -- and returns a new segment_id. - function get_or_new ( - group_id in rel_segments.group_id%TYPE, - rel_type in rel_segments.rel_type%TYPE, - segment_name in rel_segments.segment_name%TYPE - default null - ) return rel_segments.segment_id%TYPE - is - v_segment_id rel_segments.segment_id%TYPE; - v_segment_name rel_segments.segment_name%TYPE; - begin +-- function get_or_new +create function rel_segment__get_or_new (integer,varchar,varchar) +returns integer as ' +declare + get_or_new__group_id alias for $1; + get_or_new__rel_type alias for $2; + segment_name alias for $3; -- default null + v_segment_id rel_segments.segment_id%TYPE; + v_segment_name rel_segments.segment_name%TYPE; +begin - v_segment_id := get(group_id, rel_type); + v_segment_id := rel_segment__get(get_or_new__group_id,get_or_new__rel_type); if v_segment_id is null then if segment_name is not null then v_segment_name := segment_name; else - select groups.group_name || ' - ' || acs_object_types.pretty_name || - ' segment' + select groups.group_name || '' - '' || acs_object_types.pretty_name || + '' segment'' into v_segment_name from groups, acs_object_types - where groups.group_id = get_or_new.group_id - and acs_object_types.object_type = get_or_new.rel_type; + where groups.group_id = get_or_new__group_id + and acs_object_types.object_type = get_or_new__rel_type; end if; - v_segment_id := rel_segment.new ( - object_type => 'rel_segment', - creation_user => null, - creation_ip => null, - email => null, - url => null, - segment_name => v_segment_name, - group_id => get_or_new.group_id, - rel_type => get_or_new.rel_type, - context_id => get_or_new.group_id + v_segment_id := rel_segment__new ( + null, + ''rel_segment'', + now(), + null, + null, + null, + null, + v_segment_name, + get_or_new__group_id, + get_or_new__rel_type, + get_or_new__group_id ); end if; return v_segment_id; - end get_or_new; + +end;' language 'plpgsql'; - function name ( - segment_id in rel_segments.segment_id%TYPE - ) - return rel_segments.segment_name%TYPE - is - segment_name varchar(200); - begin + +-- function name +create function rel_segment__name (integer) +returns varchar as ' +declare + name__segment_id alias for $1; + name__segment_name varchar(200); +begin select segment_name - into segment_name + into name__segment_name from rel_segments - where segment_id = name.segment_id; + where segment_id = name__segment_id; - return segment_name; - end name; + return name__segment_name; + +end;' language 'plpgsql'; -end rel_segment; -/ -show errors + +-- show errors + Index: openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql 13 Mar 2003 20:22:57 -0000 1.3 +++ openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql 17 Mar 2003 21:58:22 -0000 1.4 @@ -3,7 +3,7 @@ -- -- @author Oumi Mehrotra oumi@arsdigita.com -- @creation-date 2000-11-22 --- @cvs-id $Id$ +-- @cvs-id rel-segments-create.sql,v 1.1.4.3 2001/01/16 18:54:05 oumi Exp -- Copyright (C) 1999-2000 ArsDigita Corporation -- This is free software distributed under the terms of the GNU Public @@ -15,45 +15,54 @@ -- change in the future, particularly the functions marked "EXPERIMENTAL". -- +create function inline_0 () +returns integer as ' begin -- -- Relational Segment: a dynamically derived set of parties, defined -- in terms of a particular type of membership or -- composition to a particular group. -- - acs_object_type.create_type ( - supertype => 'party', - object_type => 'rel_segment', - pretty_name => 'Relational Party Segment', - pretty_plural => 'Relational Party Segments', - table_name => 'rel_segments', - id_column => 'segment_id', - package_name => 'rel_segment', - type_extension_table => 'rel_segment', - name_method => 'rel_segment.name' - ); + PERFORM acs_object_type__create_type ( + ''rel_segment'', + ''Relational Party Segment'', + ''Relational Party Segments'', + ''party'', + ''rel_segments'', + ''segment_id'', + ''rel_segment'', + ''f'', + ''rel_segment'', + ''rel_segment.name'' + ); -end; -/ -show errors + return 0; +end;' language 'plpgsql'; +select inline_0 (); +drop function inline_0 (); + + +-- show errors + + -- Note that we do not use on delete cascade on the group_id or -- rel_type column because rel_segments are acs_objects. On delete -- cascade only deletes the corresponding row in this table, not all -- the rows up the type hierarchy. Thus, rel segments must be deleted -- using rel_segment.delete before dropping a relationship type. create table rel_segments ( - segment_id not null + segment_id integer not null constraint rel_segments_segment_id_fk references parties (party_id) constraint rel_segments_pk primary key, - segment_name varchar2(230) not null, - group_id not null + segment_name varchar(230) not null, + group_id integer not null constraint rel_segments_group_id_fk references groups (group_id), - rel_type not null + rel_type varchar(100) not null constraint rel_segments_rel_type_fk references acs_rel_types (rel_type), constraint rel_segments_grp_rel_type_uq unique(group_id, rel_type) @@ -83,352 +92,264 @@ -- create pl/sql package rel_segment -create or replace package rel_segment -is - function new ( - --/** Creates a new relational segment - -- - -- @author Oumi Mehrotra (oumi@arsdigita.com) - -- @creation-date 12/2000 - -- - --*/ - segment_id in rel_segments.segment_id%TYPE default null, - object_type in acs_objects.object_type%TYPE - default 'rel_segment', - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - email in parties.email%TYPE default null, - url in parties.url%TYPE default null, - segment_name in rel_segments.segment_name%TYPE, - group_id in rel_segments.group_id%TYPE, - rel_type in rel_segments.rel_type%TYPE, - context_id in acs_objects.context_id%TYPE default null - ) return rel_segments.segment_id%TYPE; - - procedure delete ( - --/** Deletes a relational segment - -- - -- @author Oumi Mehrotra (oumi@arsdigita.com) - -- @creation-date 12/2000 - -- - --*/ - segment_id in rel_segments.segment_id%TYPE - ); - - function name ( - segment_id in rel_segments.segment_id%TYPE - ) return rel_segments.segment_name%TYPE; - - function get ( - --/** EXPERIMENTAL / UNSTABLE -- use at your own risk - -- Get the id of a segment given a group_id and rel_type. - -- This depends on the uniqueness of group_id,rel_type. We - -- might remove the unique constraint in the future, in which - -- case we would also probably remove this function. - -- - -- @author Oumi Mehrotra (oumi@arsdigita.com) - -- @creation-date 12/2000 - -- - --*/ - - group_id in rel_segments.group_id%TYPE, - rel_type in rel_segments.rel_type%TYPE - ) return rel_segments.segment_id%TYPE; - - function get_or_new ( - --/** EXPERIMENTAL / UNSTABLE -- use at your own risk - -- - -- This function simplifies the use of segments a little by letting - -- you not have to worry about creating and initializing segments. - -- If the segment you're interested in exists, this function - -- returns its segment_id. - -- If the segment you're interested in doesn't exist, this function - -- does a pretty minimal amount of initialization for the segment - -- and returns a new segment_id. - -- - -- @author Oumi Mehrotra (oumi@arsdigita.com) - -- @creation-date 12/2000 - -- - --*/ - group_id in rel_segments.group_id%TYPE, - rel_type in rel_segments.rel_type%TYPE, - segment_name in rel_segments.segment_name%TYPE - default null - ) return rel_segments.segment_id%TYPE; - -end rel_segment; -/ -show errors - - ----------- -- Views -- ----------- -create or replace view rel_segment_party_map +create view rel_segment_party_map as select rs.segment_id, gem.element_id as party_id, gem.rel_id, gem.rel_type, gem.group_id, gem.container_id, gem.ancestor_rel_type - from rel_segments rs, - group_element_map gem + from rel_segments rs, group_element_map gem, acs_object_types o1, acs_object_types o2 where gem.group_id = rs.group_id - and rs.rel_type in (select object_type - from acs_object_types - start with object_type = gem.rel_type - connect by prior supertype = object_type); + and o1.object_type = gem.rel_type + and o2.object_type = rs.rel_type + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey); - -create or replace view rel_segment_distinct_party_map +create view rel_segment_distinct_party_map as select distinct segment_id, party_id, ancestor_rel_type from rel_segment_party_map; -create or replace view rel_segment_member_map +create view rel_segment_member_map as select segment_id, party_id as member_id, rel_id, rel_type, group_id, container_id from rel_segment_party_map where ancestor_rel_type = 'membership_rel'; -create or replace view rel_seg_approved_member_map -as select /*+ ordered */ - rs.segment_id, gem.element_id as member_id, gem.rel_id, gem.rel_type, - gem.group_id, gem.container_id - from membership_rels mr, group_element_map gem, rel_segments rs +create view rel_seg_approved_member_map +as select rs.segment_id, gem.element_id as member_id, gem.rel_id, + gem.rel_type, gem.group_id, gem.container_id + from membership_rels mr, group_element_map gem, rel_segments rs, + acs_object_types o1, acs_object_types o2 where rs.group_id = gem.group_id - and rs.rel_type in (select object_type - from acs_object_types - start with object_type = gem.rel_type - connect by prior supertype = object_type) + and rs.rel_type = o2.object_type + and o1.object_type = gem.rel_type + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey) and mr.rel_id = gem.rel_id and mr.member_state = 'approved'; -create or replace view rel_seg_distinct_member_map +create view rel_seg_distinct_member_map as select distinct segment_id, member_id from rel_seg_approved_member_map; --- The party_approved_member_map table maps all parties to all their --- members. It's here rather in a logical place for historical reasons. - --- The count column is needed because composition_rels lead to a lot of --- redundant data in the group element map (i.e. you can belong to the --- registered users group an infinite number of times, strange concept) - --- (it is "cnt" rather than "count" because Oracle confuses it with the --- "count()" aggregate in some contexts) - -- Though for permission checking we only really need to map parties to -- member users, the old view included identity entries for all parties -- in the system. It doesn't cost all that much to maintain the extra -- rows so we will, just in case some overly clever programmer out there -- depends on it. +-- This represents a large amount of redundant data which is separately +-- stored in the group_element_index table. We might want to clean this +-- up in the future but time constraints on 4.6.1 require I keep this +-- relatively simple. Implementing a real "subgroup_rel" would help a +-- lot by in itself reducing the number of redundant rows in the two +-- tables. + +-- DRB: Unfortunately visibility semantics in PostgreSQL are very different +-- than in Oracle. This makes it impossible to remove the duplicate +-- rows by maintaining a count column as I've done in the Oracle version +-- without requiring application code to issue explicit "lock table in +-- exclusive mode" statements. This would kill abstraction and be very +-- error prone. The PL/pgSQL procs can issue the locks but unfortunately +-- statements within such procs don't generate a new snapshot when executed +-- but rather work within the context of the caller. This means locks within +-- a PL/pgSQL are too late to be of use. Such code works perfectly in Oracle. + +-- Maybe people who buy Oracle aren't as dumb as you thought! + create table party_approved_member_map ( party_id integer + constraint party_member_party_nn + not null constraint party_member_party_fk references parties, member_id integer + constraint party_member_member_nn + not null constraint party_member_member_fk references parties, - cnt integer, + tag integer + constraint party_member_tag_nn + not null, constraint party_approved_member_map_pk - primary key (party_id, member_id) + primary key (party_id, member_id, tag) ); -- Need this to speed referential integrity create index party_member_member_idx on party_approved_member_map(member_id); --- Triggers to maintain party_approved_member_map when parties are create or replaced or --- destroyed. +-- Helper functions to maintain the materialized party_approved_member_map. -create or replace trigger parties_in_tr after insert on parties -for each row +create or replace function party_approved_member__add_one(integer, integer, integer) returns integer as ' +declare + p_party_id alias for $1; + p_member_id alias for $2; + p_rel_id alias for $3; begin + insert into party_approved_member_map - (party_id, member_id, cnt) + (party_id, member_id, tag) values - (:new.party_id, :new.party_id, 1); -end parties_in_tr; -/ -show errors; + (p_party_id, p_member_id, p_rel_id); -create or replace trigger parties_del_tr before delete on parties -for each row -begin - delete from party_approved_member_map - where party_id = :old.party_id - and member_id = :old.party_id; -end parties_del_tr; -/ -show errors; + return 1; --- Triggers to maintain party_approved_member_map when relational segments are --- create or replaced or destroyed. We only remove the (segment_id, member_id) rows as --- removing the relational segment itself does not remove members from the --- group with that rel_type. This was intentional on the part of the aD folks --- who added relational segments to ACS 4.2. +end;' language 'plpgsql'; -create or replace trigger rel_segments_in_tr before insert on rel_segments -for each row +create or replace function party_approved_member__add(integer, integer, integer, varchar) returns integer as ' +declare + p_party_id alias for $1; + p_member_id alias for $2; + p_rel_id alias for $3; + p_rel_type alias for $4; + v_segments record; begin - insert into party_approved_member_map - (party_id, member_id, cnt) - select :new.segment_id, element_id, 1 - from group_element_index - where group_id = :new.group_id - and rel_type = :new.rel_type; -end rel_segments_in_tr; -/ -show errors; -create or replace trigger rel_segments_del_tr before delete on rel_segments -for each row + perform party_approved_member__add_one(p_party_id, p_member_id, p_rel_id); + + -- if the relation type is mapped to relational segments unmap them too + + for v_segments in select segment_id + from rel_segments s, acs_object_types o1, acs_object_types o2 + where + o1.object_type = p_rel_type + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey) + and s.rel_type = o2.object_type + and s.group_id = p_party_id + loop + perform party_approved_member__add_one(v_segments.segment_id, p_member_id, p_rel_id); + end loop; + + return 1; + +end;' language 'plpgsql'; + +create or replace function party_approved_member__remove_one(integer, integer, integer) returns integer as ' +declare + p_party_id alias for $1; + p_member_id alias for $2; + p_rel_id alias for $3; begin + delete from party_approved_member_map - where party_id = :old.segment_id - and member_id in (select element_id - from group_element_index - where group_id = :old.group_id - and rel_type = :old.rel_type); -end parties_del_tr; -/ -show errors; + where party_id = p_party_id + and member_id = p_member_id + and tag = p_rel_id; --- DRB: Helper functions to maintain the materialized party_approved_member_map. The counting crap --- has to do with the fact that composition rels create duplicate rows in groups. + return 1; -create or replace package party_approved_member is +end;' language 'plpgsql'; - procedure add_one( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE - ); - procedure add( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE, - p_rel_type in acs_rels.rel_type%TYPE - ); +create or replace function party_approved_member__remove(integer, integer, integer, varchar) returns integer as ' +declare + p_party_id alias for $1; + p_member_id alias for $2; + p_rel_id alias for $3; + p_rel_type alias for $4; + v_segments record; +begin - procedure remove_one ( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE - ); + perform party_approved_member__remove_one(p_party_id, p_member_id, p_rel_id); - procedure remove ( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE, - p_rel_type in acs_rels.rel_type%TYPE - ); + -- if the relation type is mapped to relational segments unmap them too -end party_approved_member; -/ -show errors; + for v_segments in select segment_id + from rel_segments s, acs_object_types o1, acs_object_types o2 + where + o1.object_type = p_rel_type + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey) + and s.rel_type = o2.object_type + and s.group_id = p_party_id + loop + perform party_approved_member__remove_one(v_segments.segment_id, p_member_id, p_rel_id); + end loop; -create or replace package body party_approved_member is + return 1; - procedure add_one( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE - ) - is - begin +end;' language 'plpgsql'; - insert into party_approved_member_map - (party_id, member_id, cnt) - values - (p_party_id, p_member_id, 1); - exception when dup_val_on_index then - update party_approved_member_map - set cnt = cnt + 1 - where party_id = p_party_id - and member_id = p_member_id; +-- Triggers to maintain party_approved_member_map when parties are created or +-- destroyed. These don't call the above helper functions because we're just +-- creating the identity row for the party. - end add_one; +create or replace function parties_in_tr () returns opaque as ' +begin - procedure add( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE, - p_rel_type in acs_rels.rel_type%TYPE - ) - is - begin + insert into party_approved_member_map + (party_id, member_id, tag) + values + (new.party_id, new.party_id, 0); - add_one(p_party_id, p_member_id); + return new; - -- if the relation type is mapped to a relational segment map that too +end;' language 'plpgsql'; - for v_segments in (select segment_id - from rel_segments - where group_id = p_party_id - and rel_type in (select object_type - from acs_object_types - start with object_type = p_rel_type - connect by prior supertype = object_type)) - loop - add_one(v_segments.segment_id, p_member_id); - end loop; +create trigger parties_in_tr after insert on parties +for each row execute procedure parties_in_tr (); - end add; +create or replace function parties_del_tr () returns opaque as ' +begin - procedure remove_one ( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE - ) - is - begin + delete from party_approved_member_map + where party_id = old.party_id + and member_id = old.party_id; - update party_approved_member_map - set cnt = cnt - 1 - where party_id = p_party_id - and member_id = p_member_id; + return old; - delete from party_approved_member_map - where party_id = p_party_id - and member_id = p_member_id - and cnt = 0; +end;' language 'plpgsql'; - end remove_one; +create trigger parties_del_tr before delete on parties +for each row execute procedure parties_del_tr (); - procedure remove ( - p_party_id in parties.party_id%TYPE, - p_member_id in parties.party_id%TYPE, - p_rel_type in acs_rels.rel_type%TYPE - ) - is - begin +-- Triggers to maintain party_approved_member_map when relational segments are +-- created or destroyed. We only remove the (segment_id, member_id) rows as +-- removing the relational segment itself does not remove members from the +-- group with that rel_type. This was intentional on the part of the aD folks +-- who added relational segments to ACS 4.2. - remove_one(p_party_id, p_member_id); +create or replace function rel_segments_in_tr () returns opaque as ' +begin - -- if the relation type is mapped to a relational segment unmap that too + insert into party_approved_member_map + (party_id, member_id, tag) + select new.segment_id, element_id, rel_id + from group_element_index + where group_id = new.group_id + and rel_type = new.rel_type; - for v_segments in (select segment_id - from rel_segments - where group_id = p_party_id - and rel_type in (select object_type - from acs_object_types - start with object_type = p_rel_type - connect by prior supertype = object_type)) - loop - remove_one(v_segments.segment_id, p_member_id); - end loop; + return new; - end remove; +end;' language 'plpgsql'; -end party_approved_member; -/ -show errors; +create trigger rel_segments_in_tr before insert on rel_segments +for each row execute procedure rel_segments_in_tr (); +create or replace function rel_segments_del_tr () returns opaque as ' +begin + + delete from party_approved_member_map + where party_id = old.segment_id + and member_id in (select element_id + from group_element_index + where group_id = old.group_id + and rel_type = old.rel_type); + + return old; + +end;' language 'plpgsql'; + +create trigger rel_segments_del_tr before delete on rel_segments +for each row execute procedure rel_segments_del_tr (); + -- View: rel_segment_group_rel_type_map -- -- Result Set: the set of triples (:segment_id, :group_id, :rel_type) such that -- -- IF a party were to be in :group_id -- through a relation of type :rel_type, -- THEN the party would necessarily be in segment :segemnt_id. --- --- -create or replace view rel_segment_group_rel_type_map as + +create view rel_segment_group_rel_type_map as select s.segment_id, gcm.component_id as group_id, acs_rel_types.rel_type as rel_type @@ -438,9 +359,10 @@ UNION ALL select group_id, group_id as component_id from groups) gcm, - acs_rel_types + acs_rel_types, + acs_object_types o1, acs_object_types o2 where s.group_id = gcm.group_id - and s.rel_type in (select object_type from acs_object_types - start with object_type = acs_rel_types.rel_type - connect by prior supertype = object_type); - + and s.rel_type = o2.object_type + and o1.object_type = acs_rel_types.rel_type + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey); + 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.12 -r1.13 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 23 Feb 2003 16:23:55 -0000 1.12 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 17 Mar 2003 21:58:36 -0000 1.13 @@ -20,7 +20,7 @@ objects like the site-wide organization, and the all users party. '; -create function acs__add_user (integer,varchar,timestamp with time zone,integer,varchar,varchar,varchar,varchar,varchar,char,char,varchar,varchar,varchar,boolean,varchar) +create function acs__add_user (integer,varchar,timestamptz,integer,varchar,varchar,varchar,varchar,varchar,char,char,varchar,varchar,varchar,boolean,varchar) returns integer as ' declare user_id alias for $1; -- default null @@ -73,8 +73,6 @@ end;' language 'plpgsql'; - --- procedure remove_user create function acs__remove_user (integer) returns integer as ' declare @@ -86,8 +84,6 @@ return 0; end;' language 'plpgsql'; - --- function magic_object_id create function acs__magic_object_id (varchar) returns integer as ' declare @@ -145,7 +141,7 @@ begin root_id := acs_object__new ( - 0, + -4, ''acs_object'', now(), null, @@ -156,7 +152,7 @@ insert into acs_magic_objects (name, object_id) values - (''security_context_root'', 0); + (''security_context_root'', -4); return root_id; @@ -193,44 +189,85 @@ end; +-- Now create our special groups and users. We can not create the +-- relationships between these entities yet. This is done in acs-install.sql --- show errors - create function inline_2 () returns integer as ' declare v_object_id integer; begin - insert into acs_objects - (object_id, object_type) - values - (-1, ''party''); + -- Make an "Unregistered Visitor" as object 0, which corresponds + -- with the user_id assigned throughout the toolkit Tcl code - insert into parties - (party_id) - values - (-1); + insert into acs_objects + (object_id, object_type) + values + (0, ''person''); - insert into acs_magic_objects - (name, object_id) - values - (''the_public'', -1); + insert into parties + (party_id) + values + (0); + insert into persons + (person_id, first_names, last_name) + values + (0, ''Unregistered'', ''Visitor''); + + insert into acs_magic_objects + (name, object_id) + values + (''unregistered_visitor'', 0); + + v_object_id := acs_group__new ( + -1, + ''group'', + now(), + null, + null, + null, + null, + ''The Public'', + null, + null + ); + + insert into acs_magic_objects + (name, object_id) + values + (''the_public'', -1); + + -- Add our only user, the Unregistered Visitor, to The Public + -- group. + + perform membership_rel__new ( + null, + ''membership_rel'', + acs__magic_object_id(''the_public''), + acs__magic_object_id(''unregistered_visitor''), + ''approved'', + null, + null); + return 0; + end;' language 'plpgsql'; select inline_2 (); drop function inline_2 (); - create function inline_3 () returns integer as ' declare group_id integer; begin + -- We will create the registered users group with type group for the moment + -- because the application_group package has not yet been created. + group_id := acs_group__new ( -2, ''group'', @@ -244,19 +281,29 @@ null ); - insert into acs_magic_objects - (name, object_id) - values - (''registered_users'', -2); + insert into acs_magic_objects + (name, object_id) + values + (''registered_users'', -2); + -- Now declare "The Public" to be composed of itself and the "Registered + -- Users" group + + perform composition_rel__new ( + null, + ''composition_rel'', + acs__magic_object_id(''the_public''), + acs__magic_object_id(''registered_users''), + null, + null); + return 0; end;' language 'plpgsql'; select inline_3 (); drop function inline_3 (); - select acs_object__new ( -3, 'acs_object', @@ -266,9 +313,7 @@ null ); - insert into acs_magic_objects +insert into acs_magic_objects (name, object_id) - values +values ('default_context', -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 -r1.24 -r1.25 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 23 Feb 2003 16:23:55 -0000 1.24 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 17 Mar 2003 21:58:36 -0000 1.25 @@ -56,7 +56,19 @@ 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 -- @@ -72,173 +84,178 @@ ); +-- 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); --- DanW: eliminated hierarchy index in favor of using descendant map +-- 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. -create function acs_priv_hier_ins_tr() returns opaque as ' +-- 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 - v_rec record; - v_id integer; - v_lvl integer; - v_cont boolean; + new_value integer; + new_key varbit default null; + v_rec record; + deleted_p boolean; begin - -- first insert the new child relation if it does not exist already - insert into acs_privilege_descendant_map - select new.privilege, new.child_privilege as descendant - where not exists (select 1 - from acs_privilege_descendant_map - where privilege = new.privilege - and descendant = new.child_privilege); + -- if more than one node was deleted the second trigger call + -- will error out. This check avoids that problem. - -- insert self-reference for privilege - insert into acs_privilege_descendant_map - select new.privilege, new.privilege as descendant - where not exists (select 1 - from acs_privilege_descendant_map - where privilege = new.privilege - and descendant = new.privilege); + 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 - -- insert self-reference for descendant - insert into acs_privilege_descendant_map - select new.child_privilege as privilege, new.child_privilege as descendant - where not exists (select 1 - from acs_privilege_descendant_map - where privilege = new.child_privilege - and descendant = new.child_privilege); + return new; - -- now look for existing children to add to - for v_rec in select privilege, descendant - from acs_privilege_descendant_map - where descendant = new.privilege - LOOP - insert into acs_privilege_descendant_map - select v_rec.privilege, new.child_privilege as descendant - where not exists (select 1 - from acs_privilege_descendant_map - where privilege = v_rec.privilege - and descendant = new.child_privilege); - end LOOP; + end if; + end if; - -- now look for existing parents to add to - for v_rec in select privilege, descendant - from acs_privilege_descendant_map - where privilege = new.child_privilege + -- 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 - insert into acs_privilege_descendant_map - select new.privilege, v_rec.descendant - where not exists (select 1 - from acs_privilege_descendant_map - where privilege = new.privilege - and descendant = v_rec.descendant); - end LOOP; + -- top level node, so find the next key at this level. - return new; + select max(tree_leaf_key_to_int(tree_sortkey)) into new_value + from acs_privilege_hierarchy_index + where tree_level(tree_sortkey) = 1; -end;' language 'plpgsql'; + -- 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)); -create trigger acs_priv_hier_ins_tr after insert -on acs_privilege_hierarchy for each row -execute procedure acs_priv_hier_ins_tr (); + -- now recurse down from this node + PERFORM priv_recurse_subtree(tree_next_key(null, new_value), v_rec.child_privilege); + end LOOP; -create function recurse_del_priv_hier(varchar,varchar) -returns varchar as ' -declare - parent alias for $1; - child alias for $2; - v_rec record; -begin - -- now look for more children of this child - for v_rec in select privilege, - child_privilege - from acs_privilege_hierarchy - where privilege = child - LOOP - -- insert the children - insert into acs_privilege_descendant_map - select parent as privilege, v_rec.child_privilege as descendant - where not exists (select 1 - from acs_privilege_descendant_map - where privilege = parent - and descendant = v_rec.child_privilege); + -- materialize the map view to speed up queries + -- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003 + delete from acs_privilege_descendant_map; - -- and recurse down ad-nauseum - PERFORM recurse_del_priv_hier(parent,v_rec.child_privilege); - end loop; + insert into acs_privilege_descendant_map (privilege, descendant) + select privilege, descendant from acs_privilege_descendant_map_view; - return null; + return new; + end;' language 'plpgsql'; -create function acs_priv_hier_del_tr() returns opaque as ' +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 - v_rec record; - v_id integer; + nkey alias for $1; + child_priv alias for $2; + new_value integer; + v_rec record; + new_key varbit; begin - -- rebuild from scratch - delete from acs_privilege_descendant_map; - -- loop through the top-level of privileges - for v_rec in select privilege, - child_privilege - from acs_privilege_hierarchy + -- 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 - -- insert the top level privileges if they do not already exist - insert into acs_privilege_descendant_map - select v_rec.privilege, v_rec.child_privilege as descendant - where not exists (select 1 - from acs_privilege_descendant_map - where privilege = v_rec.privilege - and descendant = v_rec.child_privilege); - -- now recurse down to the next level - PERFORM recurse_del_priv_hier(v_rec.privilege,v_rec.child_privilege); - end LOOP; + -- calculate the next key for this level and parent - -- provide self-mapping - for v_rec in select privilege, privilege as child_privilege - from acs_privilege_descendant_map - - LOOP + 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; - insert into acs_privilege_descendant_map - select v_rec.privilege, v_rec.child_privilege as descendant - where not exists (select 1 - from acs_privilege_descendant_map - where privilege = v_rec.privilege - and descendant = v_rec.child_privilege); - end loop; + new_key := tree_next_key(nkey, new_value); - -- provide self-mapping - for v_rec in select descendant as privilege, descendant as child_privilege - from acs_privilege_descendant_map - - LOOP + -- insert the new child node. - insert into acs_privilege_descendant_map - select v_rec.privilege, v_rec.child_privilege as descendant - where not exists (select 1 - from acs_privilege_descendant_map - where privilege = v_rec.privilege - and descendant = v_rec.child_privilege); - end loop; + 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 - return new; + PERFORM priv_recurse_subtree(new_key, v_rec.child_privilege); -end;' language 'plpgsql'; + end LOOP; -create trigger acs_priv_hier_del_tr after delete -on acs_privilege_hierarchy for each row -execute procedure acs_priv_hier_del_tr (); + -- 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), @@ -363,6 +380,24 @@ create index acs_permissions_grantee_idx on acs_permissions (grantee_id); 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 + +-- 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 as select op.object_id, p.grantee_id, p.privilege from acs_object_paths op, acs_permissions p @@ -373,81 +408,18 @@ from acs_permissions_all a, acs_privilege_descendant_map m where a.privilege = m.privilege; --- The last two unions make sure that the_public gets expaned to all --- users plus 0 (the default user_id) we should probably figure out a --- better way to handle this eventually since this view is getting --- pretty freaking hairy. I'd love to be able to move this stuff into --- a Java middle tier. +-- New fast version of acs_object_party_privilege_map -create view acs_object_party_privilege_map -as select ogpm.object_id, gmm.member_id as party_id, ogpm.privilege - from acs_object_grantee_priv_map ogpm, group_approved_member_map gmm - where ogpm.grantee_id = gmm.group_id - union - select ogpm.object_id, rsmm.member_id as party_id, ogpm.privilege - from acs_object_grantee_priv_map ogpm, rel_seg_approved_member_map rsmm - where ogpm.grantee_id = rsmm.segment_id - union - select object_id, grantee_id as party_id, privilege - from acs_object_grantee_priv_map - union - select object_id, u.user_id as party_id, privilege - from acs_object_grantee_priv_map m, users u - where m.grantee_id = -1 - union - select object_id, 0 as party_id, privilege - from acs_object_grantee_priv_map - where grantee_id = -1; +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; ----------------------------------------------------- --- ALTERNATE VIEW: ALL_OBJECT_PARTY_PRIVILEGE_MAP -- ----------------------------------------------------- - --- This view is a helper for all_object_party_privilege_map -create view acs_grantee_party_map as - select -1 as grantee_id, 0 as party_id from dual - union all - select -1 as grantee_id, user_id as party_id - from users - union all - select party_id as grantee_id, party_id - from parties - union all - select segment_id as grantee_id, member_id - from rel_seg_approved_member_map - union all - select group_id as grantee_id, member_id as party_id - from group_approved_member_map; - --- This view is like acs_object_party_privilege_map, but does not --- necessarily return distinct rows. It may be *much* faster to join --- against this view instead of acs_object_party_privilege_map, and is --- usually not much slower. The tradeoff for the performance boost is --- increased complexity in your usage of the view. Example usage that I've --- found works well is: --- --- select DISTINCT --- my_table.* --- from my_table, --- (select object_id --- from all_object_party_privilege_map --- where party_id = :user_id and privilege = :privilege) oppm --- where oppm.object_id = my_table.my_id; --- - --- DRB: This view does seem to be quite fast in Postgres as well as Oracle. - create view all_object_party_privilege_map as -select op.object_id, - pdm.descendant as privilege, - gpm.party_id as party_id - from acs_object_paths op, - acs_permissions p, - acs_privilege_descendant_map pdm, - acs_grantee_party_map gpm - where op.ancestor_id = p.object_id - and pdm.privilege = p.privilege - and gpm.grantee_id = p.grantee_id; +select * from acs_object_party_privilege_map; -- This table acts as a mutex for inserts/deletes from acs_permissions. @@ -527,124 +499,23 @@ return 0; end;' language 'plpgsql'; --- Speedy version of permission_p from Matthew Avalos --- Further improved to a minor degree by Don Baccus +-- Really speedy version of permission_p written by Don Baccus -create function acs_permission__permission_p (integer,integer,varchar) +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 - -- - -- Check public-like permissions - if (0 = permission_p__party_id or - exists (select 1 from users where user_id = permission_p__party_id)) and - exists (select 1 - from acs_object_grantee_priv_map - where object_id = permission_p__object_id - and privilege = permission_p__privilege - and grantee_id = -1) - -- - then - return ''t''; - end if; - -- - -- Check direct permissions - if exists ( - select 1 - from acs_object_grantee_priv_map - where object_id = permission_p__object_id - and grantee_id = permission_p__party_id - and privilege = permission_p__privilege) - then - return ''t''; - end if; - -- - -- Check group permmissions - if exists ( - select 1 - from acs_object_grantee_priv_map ogpm, - group_approved_member_map gmm - where object_id = permission_p__object_id - and gmm.member_id = permission_p__party_id - and privilege = permission_p__privilege - and ogpm.grantee_id = gmm.group_id) - then - return ''t''; - end if; - -- - -- relational segment approved group - if exists ( - select 1 - from acs_object_grantee_priv_map ogpm, - rel_seg_approved_member_map rsmm - where object_id = permission_p__object_id - and rsmm.member_id = permission_p__party_id - and privilege = permission_p__privilege - and ogpm.grantee_id = rsmm.segment_id) - then - return ''t''; - end if; - return ''f''; + 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'; - --- Returns true if at least one user exists with the given permission. Used --- to avoid some queries on acs_object_party_privilege_map. - -create function acs_permission__user_with_perm_exists_p (integer,varchar) -returns boolean as ' -declare - permission_p__object_id alias for $1; - permission_p__privilege alias for $2; -begin - -- - -- Check public-like permissions - if exists (select 1 - from acs_object_grantee_priv_map - where object_id = permission_p__object_id - and privilege = permission_p__privilege - and grantee_id = -1) - -- - then - return ''t''; - end if; - -- - -- Check direct user permissions - if exists ( - select 1 - from acs_object_grantee_priv_map, users - where object_id = permission_p__object_id - and grantee_id = user_id - and privilege = permission_p__privilege) - then - return ''t''; - end if; - -- - -- Check group permmissions - if exists ( - select 1 - from acs_object_grantee_priv_map ogpm, - group_approved_member_map gmm - where object_id = permission_p__object_id - and privilege = permission_p__privilege - and ogpm.grantee_id = gmm.group_id) - then - return ''t''; - end if; - -- - -- relational segment approved group - if exists ( - select 1 - from acs_object_grantee_priv_map ogpm, - rel_seg_approved_member_map rsmm - where object_id = permission_p__object_id - and privilege = permission_p__privilege - and ogpm.grantee_id = rsmm.segment_id) - then - return ''t''; - end if; - return ''f''; -end;' language 'plpgsql'; Index: openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql,v diff -u -r1.21 -r1.22 --- openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 13 Mar 2003 20:23:24 -0000 1.21 +++ openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 17 Mar 2003 21:58:36 -0000 1.22 @@ -82,7 +82,7 @@ end;' language 'plpgsql'; -create trigger membership_rels_in_tr after insert on membership_rels +create trigger membership_rels_in_tr after insert on membership_rels for each row execute procedure membership_rels_in_tr (); create or replace function membership_rels_up_tr () returns opaque as ' @@ -220,8 +220,11 @@ return new; -end;' language 'plpgsql'; +end;' language 'plpgsql'; +create trigger composition_rels_in_tr after insert on composition_rels +for each row execute procedure composition_rels_in_tr (); + -- -- TO DO: See if this can be optimized now that the member and component -- mapping tables have been combined Index: openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-body-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-body-create.sql,v diff -u -r1.9 -r1.10 --- openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-body-create.sql 21 Dec 2002 22:31:16 -0000 1.9 +++ openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-body-create.sql 17 Mar 2003 21:58:36 -0000 1.10 @@ -15,7 +15,7 @@ ------------------ -- rel_segment__new -- full version -create function rel_segment__new (integer,varchar,timestamp with time zone,integer,varchar,varchar,varchar,varchar,integer,varchar,integer) +create function rel_segment__new (integer,varchar,timestamptz,integer,varchar,varchar,varchar,varchar,integer,varchar,integer) returns integer as ' declare new__segment_id alias for $1; -- default null