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.9 -r1.10 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 14 Feb 2003 02:47:13 -0000 1.9 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 17 Feb 2003 15:32:53 -0000 1.10 @@ -20,6 +20,46 @@ objects like the site-wide organization, and the all users party. '; +-- create or replace package acs +-- as +-- +-- function add_user ( +-- user_id in users.user_id%TYPE default null, +-- object_type in acs_objects.object_type%TYPE +-- default 'user', +-- 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, +-- url in parties.url%TYPE default null, +-- first_names in persons.first_names%TYPE, +-- last_name in persons.last_name%TYPE, +-- password in users.password%TYPE, +-- salt in users.salt%TYPE, +-- password_question in users.password_question%TYPE default null, +-- password_answer in users.password_answer%TYPE default null, +-- screen_name in users.screen_name%TYPE default null, +-- email_verified_p in users.email_verified_p%TYPE default 't', +-- member_state in membership_rels.member_state%TYPE default 'approved' +-- ) +-- return users.user_id%TYPE; +-- +-- procedure remove_user ( +-- user_id in users.user_id%TYPE +-- ); +-- +-- function magic_object_id ( +-- name in acs_magic_objects.name%TYPE +-- ) return acs_objects.object_id%TYPE; +-- +-- end acs; + +-- show errors + +-- create or replace package body acs +-- function add_user create function acs__add_user (integer,varchar,timestamp with time zone,integer,varchar,varchar,varchar,varchar,varchar,char,char,varchar,varchar,varchar,boolean,varchar) returns integer as ' declare @@ -73,6 +113,8 @@ end;' language 'plpgsql'; + +-- procedure remove_user create function acs__remove_user (integer) returns integer as ' declare @@ -84,6 +126,8 @@ return 0; end;' language 'plpgsql'; + +-- function magic_object_id create function acs__magic_object_id (varchar) returns integer as ' declare @@ -99,6 +143,10 @@ end;' language 'plpgsql' with(isstrict,iscachable); + + +-- show errors + -- ****************************************************************** -- * Community Core API -- ****************************************************************** @@ -141,7 +189,7 @@ begin root_id := acs_object__new ( - -4, + 0, ''acs_object'', now(), null, @@ -152,7 +200,7 @@ insert into acs_magic_objects (name, object_id) values - (''security_context_root'', -4); + (''security_context_root'', 0); return root_id; @@ -182,92 +230,64 @@ -- Administrators can read, write, create, and delete. -- --------------------------------------------------------- + -- temporarily drop this trigger to avoid a data-change violation + -- on acs_privilege_hierarchy_index while updating the child privileges. + + drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy; + select acs_privilege__add_child('admin', 'read'); select acs_privilege__add_child('admin', 'write'); select acs_privilege__add_child('admin', 'create'); + + -- re-enable the trigger before the last insert to force the + -- acs_privilege_hierarchy_index table to be updated. + + create trigger acs_priv_hier_ins_del_tr after insert or delete + on acs_privilege_hierarchy for each row + execute procedure acs_priv_hier_ins_del_tr (); + select acs_privilege__add_child('admin', 'delete'); end; --- 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 - -- Make an "Unregistered Visitor" as object 0, which corresponds - -- with the user_id assigned throughout the toolkit Tcl code + insert into acs_objects + (object_id, object_type) + values + (-1, ''party''); - insert into acs_objects - (object_id, object_type) - values - (0, ''person''); + insert into parties + (party_id) + values + (-1); - insert into parties - (party_id) - values - (0); + insert into acs_magic_objects + (name, object_id) + values + (''the_public'', -1); - 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'', @@ -281,29 +301,19 @@ 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', @@ -313,7 +323,10 @@ null ); -insert into acs_magic_objects + insert into acs_magic_objects (name, object_id) -values + values ('default_context', -3); + + +-- show errors Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql,v diff -u -r1.18 -r1.19 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql 16 Feb 2003 00:02:23 -0000 1.18 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql 17 Feb 2003 15:32:53 -0000 1.19 @@ -26,31 +26,9 @@ acs__magic_object_id(''default_context'') ); - insert into application_groups - (group_id, package_id) - values - (-2, main_site_id); - update acs_objects - set object_type = ''application_group'' - where object_id = -2; + PERFORM apm_package__enable (main_site_id); - perform rel_segment__new( - null, - ''rel_segment'', - now(), - null, - null, - null, - null, - ''Main Site Members'', - -2, - ''membership_rel'', - null - ); - - perform apm_package__enable (main_site_id); - node_id := site_node__new ( null, null, @@ -62,7 +40,7 @@ null ); - perform acs_permission__grant_permission ( + PERFORM acs_permission__grant_permission ( main_site_id, acs__magic_object_id(''the_public''), ''read'' Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql,v diff -u -r1.37 -r1.38 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 14 Feb 2003 02:47:13 -0000 1.37 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 17 Feb 2003 15:32:53 -0000 1.38 @@ -382,8 +382,6 @@ where object_id != ancestor_id; create function acs_objects_context_id_in_tr () returns opaque as ' -declare - security_context_root integer; begin insert into acs_object_context_index (object_id, ancestor_id, n_generations) @@ -398,15 +396,13 @@ n_generations + 1 as n_generations from acs_object_context_index where object_id = new.context_id; - else - security_context_root = acs__magic_object_id(''security_context_root''); - if new.object_id != security_context_root then - insert into acs_object_context_index - (object_id, ancestor_id, n_generations) - values - (new.object_id, security_context_root, 1); - end if; - end if; + else if new.object_id != 0 then + -- 0 is the id of the security context root object + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + values + (new.object_id, 0, 1); + end if; end if; return new; @@ -420,7 +416,6 @@ create function acs_objects_context_id_up_tr () returns opaque as ' declare pair record; - security_context_root integer; begin if new.object_id = old.object_id and new.context_id = old.context_id and @@ -460,22 +455,19 @@ from acs_object_context_index where object_id = new.context_id; end loop; - else - security_context_root = acs__magic_object_id(''security_context_root''); - if new.object_id != security_context_root then + else if new.object_id != 0 then -- We need to make sure that new.OBJECT_ID and all of its - -- children have security_context_root as an ancestor. + -- children have 0 as an ancestor. for pair in select * from acs_object_context_index where ancestor_id = new.object_id - LOOP - insert into acs_object_context_index - (object_id, ancestor_id, n_generations) - values - (pair.object_id, security_context_root, pair.n_generations + 1); - end loop; - end if; - end if; + LOOP + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + values + (pair.object_id, 0, pair.n_generations + 1); + end loop; + end if; end if; return new; 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.21 -r1.22 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 14 Feb 2003 02:47:13 -0000 1.21 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 17 Feb 2003 15:32:53 -0000 1.22 @@ -84,11 +84,6 @@ ); --- 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 @@ -129,14 +124,13 @@ -- 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 ' +create 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. @@ -408,18 +402,81 @@ from acs_permissions_all a, acs_privilege_descendant_map m where a.privilege = m.privilege; --- New fast version of acs_object_party_privilege_map +-- 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. -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; +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; +---------------------------------------------------- +-- 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 * from acs_object_party_privilege_map; +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; -- This table acts as a mutex for inserts/deletes from acs_permissions. @@ -499,23 +556,124 @@ return 0; end;' language 'plpgsql'; --- Really speedy version of permission_p written by Don Baccus +-- Speedy version of permission_p from Matthew Avalos +-- Further improved to a minor degree by Don Baccus -create or replace function acs_permission__permission_p (integer,integer,varchar) +create 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); + -- + -- 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''; 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.18 -r1.19 --- openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 14 Feb 2003 02:47:13 -0000 1.18 +++ openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 17 Feb 2003 15:32:53 -0000 1.19 @@ -9,154 +9,11 @@ -------------- -- TRIGGERS -- -------------- - --- DRB: Helper functions to maintain the materialized party_approved_member_map. The counting crap --- has to do with the way composition_rels work, which is not how any sane person would care --- for them to work. Fixing the groups and relational segments model will be a nice future project. --- For now I will just settle for making permission checking fast ... - -create or replace function insert_into_party_map(integer, integer, varchar) returns integer as ' +-- a dummy trigger was defined in groups-create.sql +drop trigger membership_rels_in_tr on membership_rels; +drop function membership_rels_in_tr (); +create function membership_rels_in_tr () returns opaque as ' declare - p_party_id alias for $1; - p_member_id alias for $2; - p_rel_type alias for $3; - v_segment_id rel_segments.segment_id%TYPE; - v_count integer; -begin - - insert into party_approved_member_map - (party_id, member_id, count) - select p_party_id, p_member_id, 1 - where not exists (select 1 - from party_approved_member_map - where party_id = p_party_id - and member_id = p_member_id); - - get diagnostics v_count = row_count; - - if v_count = 0 then - update party_approved_member_map - set count = count + 1 - where party_id = p_party_id - and member_id = p_member_id; - end if; - - -- if the relation type is mapped to a relational segment map that too - - select into v_segment_id segment_id - from rel_segments s - where s.rel_type = p_rel_type - and s.group_id = p_party_id; - - if found then - insert into party_approved_member_map - (party_id, member_id, count) - select v_segment_id, p_member_id, 1 - where not exists (select 1 - from party_approved_member_map - where party_id = v_segment_id - and member_id = p_member_id); - - get diagnostics v_count = row_count; - - if v_count = 0 then - update party_approved_member_map - set count = count + 1 - where party_id = v_segment_id - and member_id = p_member_id; - end if; - - end if; - - return 1; - -end;' language 'plpgsql'; - -create or replace function delete_from_party_map(integer, integer, varchar) returns integer as ' -declare - p_party_id alias for $1; - p_member_id alias for $2; - p_rel_type alias for $3; - v_segment_id rel_segments.segment_id%TYPE; - v_count integer; -begin - - delete from party_approved_member_map - where party_id = p_party_id - and member_id = p_member_id - and count = 1; - - get diagnostics v_count = row_count; - - if v_count = 0 then - update party_approved_member_map - set count = count - 1 - where party_id = p_party_id - and member_id = p_member_id; - end if; - - -- if the relation type is mapped to a relational segment unmap that too - - select into v_segment_id segment_id - from rel_segments s - where s.rel_type = p_rel_type - and s.group_id = p_party_id; - - if found then - - delete from party_approved_member_map - where party_id = v_segment_id - and member_id = p_member_id - and count = 1; - - get diagnostics v_count = row_count; - - if v_count = 0 then - update party_approved_member_map - set count = count - 1 - where party_id = v_segment_id - and member_id = p_member_id; - end if; - - end if; - - return 1; - -end;' language 'plpgsql'; - -create or replace function group_element_index_in_tr () returns opaque as ' -declare - v_member_state membership_rels.member_state%TYPE; -begin - - select into v_member_state m.member_state - from membership_rels m - where m.rel_id = new.rel_id; - - -- Only membership_rels are tracked in the party_approved_member_map - - if v_member_state = ''approved'' then - perform insert_into_party_map(new.group_id, new.element_id, new.rel_type); - end if; - - return new; - -end;' language 'plpgsql'; - -create trigger group_element_index_in_tr before insert on group_element_index -for each row execute procedure group_element_index_in_tr (); - -create or replace function group_element_index_del_tr () returns opaque as ' -begin - perform delete_from_party_map(old.group_id, old.element_id, old.rel_type); - return old; -end;' language 'plpgsql'; - -create trigger group_element_index_del_tr after delete on group_element_index -for each row execute procedure group_element_index_del_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; @@ -175,7 +32,7 @@ from acs_rels where rel_id = new.rel_id; - -- Insert a row for me in the group_element_index. + -- Insert a row for me in the group_member_index. insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) @@ -184,11 +41,11 @@ v_rel_type, ''membership_rel''); -- For all groups of which I am a component, insert a - -- row in the group_element_index. + -- row in the group_member_index. for map in select distinct group_id from group_component_map where component_id = v_object_id_one - loop + LOOP insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) @@ -201,63 +58,19 @@ end;' language 'plpgsql'; -create or replace function membership_rels_up_tr () returns opaque as ' -declare - map record; -begin +create trigger membership_rels_in_tr after insert on membership_rels +for each row execute procedure membership_rels_in_tr (); - if new.member_state = old.member_state then - return new; - end if; +-- show errors - 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 insert_into_party_map(map.group_id, map.element_id, map.rel_type); - else - perform delete_from_party_map(map.group_id, map.element_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 ' +-- a dummy trigger was defined in groups-create.sql +drop trigger composition_rels_in_tr on composition_rels; +drop function composition_rels_in_tr(); +create function composition_rels_in_tr () returns opaque as ' declare - v_error text; 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; - map record; - v_count integer; -begin - -- First check if removing this relation would violate any relational constraints - v_error := rel_constraint__violation_if_removed(old.rel_id); - if v_error is not null then - raise EXCEPTION ''-20000: %'', v_error; - end if; - - delete from group_element_index - where rel_id = old.rel_id; - - return old; - -end;' language 'plpgsql'; - -create trigger membership_rels_del_tr before delete on membership_rels -for each row execute procedure membership_rels_del_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 text; map record; begin @@ -332,11 +145,38 @@ end;' language 'plpgsql'; +create trigger composition_rels_in_tr after insert on composition_rels +for each row execute procedure composition_rels_in_tr (); + +-- show errors + +create function membership_rels_del_tr () returns opaque as ' +declare + v_error text; +begin + -- First check if removing this relation would violate any relational constraints + v_error := rel_constraint__violation_if_removed(old.rel_id); + if v_error is not null then + raise EXCEPTION ''-20000: %'', v_error; + end if; + + delete from group_element_index + where rel_id = old.rel_id; + + return old; + +end;' language 'plpgsql'; + +create trigger membership_rels_del_tr before delete on membership_rels +for each row execute procedure membership_rels_del_tr (); + +-- show errors + -- -- TO DO: See if this can be optimized now that the member and component -- mapping tables have been combined -- -create or replace function composition_rels_del_tr () returns opaque as ' +create 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; @@ -613,12 +453,12 @@ from acs_rels where rel_id = check_representation__rel_id; - -- First let us check that the index has all the rows it should. + -- First let''s 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 us check that the index doesn''t have any extraneous rows + -- Now let''s check that the index doesn''t have any extraneous rows -- relating to this relation. for row in select * from group_component_index @@ -789,15 +629,15 @@ begin select count(*) into n_rows - from group_element_index + 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; if n_rows = 0 then result := ''f''; PERFORM acs_log__error(''membership_rel.check_representation'', - ''Row missing from group_element_index: '' || + ''Row missing from group_member_index: '' || ''group_id = '' || check_index__group_id || '', '' || ''member_id = '' || check_index__member_id || '', '' || ''container_id = '' || check_index__container_id || ''.''); Index: openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql,v diff -u -r1.13 -r1.14 --- openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql 14 Feb 2003 02:47:13 -0000 1.13 +++ openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql 17 Feb 2003 15:32:53 -0000 1.14 @@ -386,7 +386,7 @@ create function composition_rels_in_tr () returns opaque as ' declare begin - raise EXCEPTION ''-20000: Insert to composition rels not yet supported''; + raise EXCEPTION ''-20000: Insert to membership rels not yet supported''; return new; Index: openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 14 Feb 2003 02:47:13 -0000 1.4 +++ openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 17 Feb 2003 15:32:53 -0000 1.5 @@ -92,10 +92,102 @@ -- 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 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 +-- 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); + 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 @@ -115,6 +207,21 @@ from rel_segment_party_map where ancestor_rel_type = 'membership_rel'; + +-- Need to find out what this optimizer hint does? DCW, 2001-03-13. +-- create 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 +-- 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 mr.rel_id = gem.rel_id and mr.member_state = 'approved'; + + 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 @@ -130,111 +237,71 @@ as select distinct segment_id, member_id from rel_seg_approved_member_map; --- party_approved_member_map can be used to expand any party into its members. + +-- party_member_map can be used to expand any party into its members. -- Every party is considered to be a member of itself. --- DRB: This is here rather where parties are created for historical reasons --- (in other words this is where the old view was created in older versions) +-- By the way, aren't the party_member_map and party_approved_member_map +-- views equivalent?? (TO DO: RESOLVE THIS QUESTION) --- The count column is needed because composition_rels and relational segment --- rel_types derived from membership_rel 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) +create view party_member_map +as select segment_id as party_id, member_id + from rel_seg_distinct_member_map + union + select group_id as party_id, member_id + from group_distinct_member_map + union + select party_id, party_id as member_id + from parties; -create table party_approved_member_map ( - party_id integer - constraint party_member_party_fk - references parties, - member_id integer - constraint party_member_member_fk - references parties, - count integer, - constraint party_member_map_pk - primary key (party_id, member_id) -); +create view party_approved_member_map +as select distinct segment_id as party_id, member_id + from rel_seg_approved_member_map + union + select distinct group_id as party_id, member_id + from group_approved_member_map + union + select party_id, party_id as member_id + from parties; --- Need this to speed referential integrity -create index party_member_member_idx on party_approved_member_map(member_id); +-- party_element_map tells us all the parties that "belong to" a party, +-- whether through somet type of membership, composition, or identity. --- Triggers to maintain party_approved_member_map when parties are created or --- destroyed. +create view party_element_map +as select distinct group_id as party_id, element_id + from group_element_map + union + select distinct segment_id as party_id, party_id as element_id + from rel_segment_party_map + union + select party_id, party_id as element_id + from parties; -create or replace function parties_in_tr () returns opaque as ' -begin - insert into party_approved_member_map - (party_id, member_id, count) - values - (new.party_id, new.party_id, 1); - return new; - - -end;' language 'plpgsql'; - -create trigger parties_in_tr before insert on parties -for each row execute procedure parties_in_tr (); - -create or replace function parties_del_tr () returns opaque as ' -begin - - delete from party_approved_member_map - where party_id = old.party_id - and member_id = old.party_id; - - return old; - -end;' language 'plpgsql'; - -create trigger parties_del_tr before delete on parties -for each row execute procedure parties_del_tr (); - --- 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. - -create or replace function rel_segments_in_tr () returns opaque as ' -begin - - insert into party_approved_member_map - (party_id, member_id, count) - select new.segment_id, element_id, 1 - from group_element_index - where group_id = new.group_id - and rel_type = new.rel_type; - - return new; - -end;' language 'plpgsql'; - -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 parties_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 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 +-- from rel_segments s, +-- (select group_id, component_id +-- from group_component_map +-- UNION ALL +-- select group_id, group_id as component_id +-- from groups) gcm, +-- acs_rel_types +-- 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); create view rel_segment_group_rel_type_map as select s.segment_id, Index: openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-drop.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-drop.sql 14 Feb 2003 02:47:13 -0000 1.3 +++ openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-drop.sql 17 Feb 2003 15:32:53 -0000 1.4 @@ -19,8 +19,9 @@ end;' language 'plpgsql'; select inline_0 (); +drop view party_element_map; drop view party_approved_member_map; -drop table party_member_map; +drop view party_member_map; drop view rel_seg_distinct_member_map; drop view rel_seg_approved_member_map; drop view rel_segment_member_map;