Index: openacs-4/packages/acs-kernel/acs-kernel.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/acs-kernel.info,v diff -u -N -r1.17.2.3 -r1.17.2.4 --- openacs-4/packages/acs-kernel/acs-kernel.info 17 Dec 2002 10:22:50 -0000 1.17.2.3 +++ openacs-4/packages/acs-kernel/acs-kernel.info 11 Feb 2003 17:03:29 -0000 1.17.2.4 @@ -153,45 +153,45 @@ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 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 -N -r1.6.4.2 -r1.6.4.3 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 21 Jan 2003 13:46:20 -0000 1.6.4.2 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 11 Feb 2003 17:04:24 -0000 1.6.4.3 @@ -20,46 +20,6 @@ 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 @@ -113,8 +73,6 @@ end;' language 'plpgsql'; - --- procedure remove_user create function acs__remove_user (integer) returns integer as ' declare @@ -126,8 +84,6 @@ return 0; end;' language 'plpgsql'; - --- function magic_object_id create function acs__magic_object_id (varchar) returns integer as ' declare @@ -143,10 +99,6 @@ end;' language 'plpgsql' with(isstrict,iscachable); - - --- show errors - -- ****************************************************************** -- * Community Core API -- ****************************************************************** @@ -189,7 +141,7 @@ begin root_id := acs_object__new ( - 0, + -4, ''acs_object'', now(), null, @@ -200,7 +152,7 @@ insert into acs_magic_objects (name, object_id) values - (''security_context_root'', 0); + (''security_context_root'', -4); return root_id; @@ -230,64 +182,92 @@ -- 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 - 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'', @@ -301,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', @@ -323,10 +313,7 @@ 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 -N -r1.9.4.1 -r1.9.4.2 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql 21 Jan 2003 17:34:57 -0000 1.9.4.1 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql 11 Feb 2003 17:04:24 -0000 1.9.4.2 @@ -48,6 +48,29 @@ ); + 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 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 ( @@ -116,7 +139,6 @@ null ); - cr_id := apm_service__new ( null, ''ACS Content Repository'', @@ -176,23 +198,6 @@ PERFORM apm_package__enable (api_doc_id); - insert into inline_data (id,name) values (api_doc_id, ''api_doc_id''); - - return null; - -end;' language 'plpgsql'; - - - -- Set default permissions for ACS API Browser so - -- that only users logged in can view it -create function inline_1 () returns integer as ' -declare - api_doc_id integer; -begin - - select id into api_doc_id - from inline_data where name = ''api_doc_id''; - PERFORM acs_permission__grant_permission ( api_doc_id, acs__magic_object_id (''registered_users''), @@ -210,26 +215,15 @@ null ); - return null; + update acs_objects + set security_inherit_p = ''f'' + where object_id = api_doc_id; + return null; + end;' language 'plpgsql'; -create table inline_data ( - id integer, - name varchar -); - select inline_0 (); -select id from inline_data where name = 'api_doc_id'; -update acs_objects - set security_inherit_p = 'f' - where object_id = (select id from inline_data where name = 'api_doc_id'); - -select inline_1 (); - drop function inline_0 (); -drop function inline_1 (); -drop table inline_data; --- show errors 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 -N -r1.35.2.1 -r1.35.2.2 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 21 Jan 2003 13:46:20 -0000 1.35.2.1 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 11 Feb 2003 17:04:24 -0000 1.35.2.2 @@ -381,7 +381,9 @@ from acs_object_context_index where object_id != ancestor_id; -create function acs_objects_context_id_in_tr () returns opaque as ' +create or replace 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) @@ -396,13 +398,15 @@ n_generations + 1 as n_generations from acs_object_context_index where object_id = new.context_id; - 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; + 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; return new; @@ -411,11 +415,10 @@ create trigger acs_objects_context_id_in_tr after insert on acs_objects for each row execute procedure acs_objects_context_id_in_tr (); --- show errors - -create function acs_objects_context_id_up_tr () returns opaque as ' +create or replace 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 @@ -455,19 +458,22 @@ from acs_object_context_index where object_id = new.context_id; end loop; - else if new.object_id != 0 then + else + security_context_root = acs__magic_object_id(''security_context_root''); + if new.object_id != security_context_root then -- We need to make sure that new.OBJECT_ID and all of its - -- children have 0 as an ancestor. + -- children have security_context_root 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, 0, 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, security_context_root, 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 -N -r1.19 -r1.19.4.1 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 8 Dec 2001 01:17:59 -0000 1.19 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 11 Feb 2003 17:04:24 -0000 1.19.4.1 @@ -69,7 +69,26 @@ 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 @@ -110,13 +129,14 @@ -- This would be better, since the same query could be used for both oracle -- and postgresql. -create function acs_priv_hier_ins_del_tr () returns opaque as ' +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. @@ -166,6 +186,13 @@ 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'; @@ -353,7 +380,12 @@ create index acs_permissions_grantee_idx on acs_permissions (grantee_id); create index acs_permissions_privilege_idx on acs_permissions (privilege); -create view acs_privilege_descendant_map +-- Added table to materialize view that previously used +-- acs_privilege_descendant_map name +-- +-- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003 + +create view acs_privilege_descendant_map_view as select p1.privilege, p2.privilege as descendant from acs_privileges p1, acs_privileges p2 where exists (select h2.child_privilege @@ -376,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. @@ -530,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 -N -r1.16.2.1 -r1.16.2.2 --- openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 21 Jan 2003 13:46:20 -0000 1.16.2.1 +++ openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 11 Feb 2003 17:04:24 -0000 1.16.2.2 @@ -9,11 +9,154 @@ -------------- -- TRIGGERS -- -------------- --- 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 ' + +-- 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 ' 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; @@ -32,7 +175,7 @@ from acs_rels 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) @@ -41,11 +184,11 @@ v_rel_type, ''membership_rel''); -- For all groups of which I am a component, insert a - -- row in the group_member_index. + -- 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 + loop insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) @@ -58,19 +201,63 @@ 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 --- show errors + if new.member_state = old.member_state then + return new; + end if; --- 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 ' + 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 ' 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 @@ -145,38 +332,11 @@ 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 function composition_rels_del_tr () returns opaque as ' +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; @@ -453,12 +613,12 @@ from acs_rels where rel_id = check_representation__rel_id; - -- First let''s check that the index has all the rows it should. + -- 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 * from group_component_index @@ -629,15 +789,15 @@ begin select count(*) into n_rows - from group_member_index + 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''; PERFORM acs_log__error(''membership_rel.check_representation'', - ''Row missing from group_member_index: '' || + ''Row missing from group_element_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 -N -r1.11 -r1.11.2.1 --- openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql 16 Sep 2002 21:52:42 -0000 1.11 +++ openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql 11 Feb 2003 17:04:24 -0000 1.11.2.1 @@ -386,7 +386,7 @@ create function composition_rels_in_tr () returns opaque as ' declare begin - raise EXCEPTION ''-20000: Insert to membership rels not yet supported''; + raise EXCEPTION ''-20000: Insert to composition 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 -N -r1.3 -r1.3.4.1 --- openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 1 Dec 2001 17:55:16 -0000 1.3 +++ openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 11 Feb 2003 17:04:24 -0000 1.3.4.1 @@ -92,102 +92,10 @@ -- 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 @@ -207,21 +115,6 @@ 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 @@ -237,71 +130,111 @@ as select distinct segment_id, member_id from rel_seg_approved_member_map; - --- party_member_map can be used to expand any party into its members. +-- party_approved_member_map can be used to expand any party into its members. -- Every party is considered to be a member of itself. --- By the way, aren't the party_member_map and party_approved_member_map --- views equivalent?? (TO DO: RESOLVE THIS QUESTION) +-- 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) -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; +-- 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_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; +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) +); --- party_element_map tells us all the parties that "belong to" a party, --- whether through somet type of membership, composition, or identity. +-- Need this to speed referential integrity +create index party_member_member_idx on party_approved_member_map(member_id); -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; +-- Triggers to maintain party_approved_member_map when parties are created or +-- destroyed. +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/upgrade/upgrade-4.6-4.6.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/Attic/upgrade-4.6-4.6.1.sql,v diff -u -N -r1.1.2.1 -r1.1.2.2 --- openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql 17 Dec 2002 10:23:20 -0000 1.1.2.1 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql 11 Feb 2003 17:05:21 -0000 1.1.2.2 @@ -23,3 +23,744 @@ return magic_object_id__object_id; end;' language 'plpgsql' with(isstrict,iscachable); + +----------------------------------------------------------------------------------------- + +-- DRB: Change security context to object -4 + +create or replace 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) + values + (new.object_id, new.object_id, 0); + + if new.context_id is not null and new.security_inherit_p = ''t'' then + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + select + new.object_id as object_id, ancestor_id, + 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; + + return new; + +end;' language 'plpgsql'; + +create or replace 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 + new.security_inherit_p = old.security_inherit_p then + return new; + end if; + + -- Remove my old ancestors from my descendants. + delete from acs_object_context_index + where object_id in (select object_id + from acs_object_contexts + where ancestor_id = old.object_id) + and ancestor_id in (select ancestor_id + from acs_object_contexts + where object_id = old.object_id); + + -- Kill all my old ancestors. + delete from acs_object_context_index + where object_id = old.object_id; + + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + values + (new.object_id, new.object_id, 0); + + if new.context_id is not null and new.security_inherit_p = ''t'' then + -- Now insert my new ancestors for my descendants. + 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) + select + pair.object_id, ancestor_id, + n_generations + pair.n_generations + 1 as n_generations + 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 + -- We need to make sure that new.OBJECT_ID and all of its + -- children have security_context_root 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; + + return new; + +end;' language 'plpgsql'; + +-- DRB: This is the function that actually changes security_context_root +-- to -4 rather than 0 + +drop trigger acs_objects_context_id_in_tr on acs_objects; +drop trigger acs_objects_context_id_up_tr on acs_objects; + +delete from acs_magic_objects +where name = 'security_context_root'; + +select acs_object__new ( + -4, + 'acs_object', + now(), + null, + null, + null + ); + +insert into acs_magic_objects + (name, object_id) +values + ('security_context_root', -4); + +update acs_object_context_index +set ancestor_id = -4 +where ancestor_id = 0; + +update acs_object_context_index +set object_id = -4 +where object_id = 0; + +update acs_permissions +set object_id = -4 +where object_id = 0; + +-- Content Repository sets parent_id to security_context_root +-- for content modules + +update cr_items +set parent_id = -4 +where parent_id = 0; + +select acs_object__delete(0); + +create trigger acs_objects_context_id_in_tr after insert on acs_objects +for each row execute procedure acs_objects_context_id_in_tr (); + +create trigger acs_objects_context_id_up_tr after update on acs_objects +for each row execute procedure acs_objects_context_id_up_tr (); + +------------------------------------------------------------------------- + +-- DRB: We now will turn the magic -1 party into a group that contains +-- all registered users and a new unregistered visitor. This will allow +-- us to do all permission checking on a materialized version of the +-- party_member_map. + +-- Make our new "Unregistered Visitor" be object 0, which corresponds +-- with the user_id assigned throughout the toolkit Tcl code + +insert into acs_objects + (object_id, object_type) +values + (0, 'person'); + +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); + +-- Now transform the old special -1 party into a legitimate group with +-- one user, our Unregistered Visitor + +update acs_objects +set object_type = 'group' +where object_id = -1; + +insert into groups + (group_id, group_name, join_policy) +values + (-1, 'The Public', 'closed'); + +-- Add our only user, the Unregistered Visitor + +select membership_rel__new ( + null, + 'membership_rel', + acs__magic_object_id('the_public'), + 0, + 'approved', + null, + null); + +-- Now declare "The Public" to be composed of itself and the "Registered +-- Users" group + +select composition_rel__new ( + null, + 'composition_rel', + acs__magic_object_id('the_public'), + acs__magic_object_id('registered_users'), + null, + null); + +------------------------------------------------------------------------------- + +-- DRB: Replace the old party_emmber_map and party_approved_member_map views +-- (they were both the same and very slow) with a table containing the same +-- information. This can be used to greatly speed permissions checking. + +drop view party_member_map; +drop view party_approved_member_map; + +-- 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) + +-- 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. + +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_approved_member_map_pk + primary key (party_id, member_id) +); + +-- Need this to speed referential integrity +create index party_member_member_idx on party_approved_member_map(member_id); + +-- Every person is a member of itself + +insert into party_approved_member_map + (party_id, member_id, count) +select party_id, party_id, 1 +from parties; + +-- Every party is a member if it is an approved member of +-- some sort of membership_rel + +insert into party_approved_member_map + (party_id, member_id, count) +select group_id, member_id, count(*) +from group_approved_member_map +group by group_id, member_id; + +-- Every party is a member if it is an approved member of +-- some sort of relation segment + +insert into party_approved_member_map + (party_id, member_id, count) +select segment_id, member_id, count(*) +from rel_seg_approved_member_map +group by segment_id, member_id; + +analyze party_approved_member_map; + +-- Triggers to maintain party_approved_member_map when parties are created or +-- destroyed. + +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 (); + + +-- 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 ' +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; + v_error text; + map record; +begin + + -- First check if added this relation violated any relational constraints + v_error := rel_constraint__violation(new.rel_id); + if v_error is not null then + 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; + + -- 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''); + + -- For all groups of which I am a component, insert a + -- 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 + 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''); + end loop; + + return new; + +end;' language 'plpgsql'; + +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 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 ' +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'; + +------------------------------------------------------------------------------------ + +-- DRB: upgrade to Dan Wickstrom's version of acs-permissions which materializes the +-- acs_privilege_descendant_map view. + +drop view acs_privilege_descendant_map; +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); + +insert into acs_privilege_descendant_map +select privilege, descendant from acs_privilege_descendant_map_view; + +drop 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; + +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 view acs_privilege_descendant_map_view +as select p1.privilege, p2.privilege as descendant + from acs_privileges p1, acs_privileges p2 + where exists (select h2.child_privilege + from + acs_privilege_hierarchy_index h1, + acs_privilege_hierarchy_index h2 + where + h1.privilege = p1.privilege + and h2.privilege = p2.privilege + and h2.tree_sortkey between h1.tree_sortkey and tree_right(h1.tree_sortkey)) or + p1.privilege = p2.privilege; + +insert into acs_privilege_descendant_map (privilege, descendant) +select privilege, descendant from acs_privilege_descendant_map_view; + +-- New fast version of acs_object_party_privilege_map + +drop view acs_object_party_privilege_map; +create view acs_object_party_privilege_map as +select c.object_id, pdm.descendant as privilege, pamm.member_id as party_id +from acs_object_context_index c, acs_permissions p, acs_privilege_descendant_map pdm, + party_approved_member_map pamm +where c.ancestor_id = p.object_id + and pdm.privilege = p.privilege + and pamm.party_id = p.grantee_id; + +drop view all_object_party_privilege_map; +create view all_object_party_privilege_map as +select * from acs_object_party_privilege_map; + +-- 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'; + +-- No longer needed with fast acs_object_party_privilege_map +drop function acs_permission__user_with_perm_exists_p (integer,varchar); + + Index: openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.tcl,v diff -u -N -r1.3 -r1.3.4.1 --- openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.tcl 21 Mar 2001 00:26:19 -0000 1.3 +++ openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.tcl 11 Feb 2003 17:06:36 -0000 1.3.4.1 @@ -10,15 +10,7 @@ @return 1 if a user with admin privileges exists, 0 otherwise. } { - return [db_string admin_exists_p { - select 1 as admin_exists_p - from dual - where exists (select 1 - from acs_object_party_privilege_map m, users u - where m.object_id = 0 - and m.party_id = u.user_id - and m.privilege = 'admin') - } -default 0] + return [db_string admin_exists_p {} -default 0] } Index: openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.xql,v diff -u -N -r1.2 -r1.2.4.1 --- openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.xql 28 Nov 2001 18:39:39 -0000 1.2 +++ openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.xql 11 Feb 2003 17:06:36 -0000 1.2.4.1 @@ -7,10 +7,11 @@ select 1 as admin_exists_p from dual where exists (select 1 - from all_object_party_privilege_map m, users u - where m.object_id = 0 - and m.party_id = u.user_id - and m.privilege = 'admin') + from all_object_party_privilege_map m, users u, acs_magic_objects amo + where m.object_id = amo.object_id + and amo.name = 'security_context_root' + and m.party_id = u.user_id + and m.privilege = 'admin') Index: openacs-4/packages/cms/sql/postgresql/cms-content-methods.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-content-methods.sql,v diff -u -N -r1.4 -r1.4.4.1 --- openacs-4/packages/cms/sql/postgresql/cms-content-methods.sql 8 Jun 2001 01:44:53 -0000 1.4 +++ openacs-4/packages/cms/sql/postgresql/cms-content-methods.sql 11 Feb 2003 17:07:33 -0000 1.4.4.1 @@ -125,7 +125,7 @@ -- create or replace package body content_method as -- function get_method -create function content_method__get_method (varchar) +create or replace function content_method__get_method (varchar) returns varchar as ' declare p_content_type alias for $1; @@ -173,7 +173,7 @@ -- function is_mapped -create function content_method__is_mapped (varchar,varchar) +create or replace function content_method__is_mapped (varchar,varchar) returns boolean as ' declare p_content_type alias for $1; @@ -193,7 +193,7 @@ -- procedure add_method -create function content_method__add_method (varchar,varchar,boolean) +create or replace function content_method__add_method (varchar,varchar,boolean) returns integer as ' declare p_content_type alias for $1; @@ -234,7 +234,7 @@ -- procedure add_all_methods -create function content_method__add_all_methods (varchar) +create or replace function content_method__add_all_methods (varchar) returns integer as ' declare p_content_type alias for $1; @@ -262,7 +262,7 @@ -- procedure set_default_method -create function content_method__set_default_method (varchar,varchar) +create or replace function content_method__set_default_method (varchar,varchar) returns integer as ' declare p_content_type alias for $1; @@ -284,7 +284,7 @@ -- procedure unset_default_method -create function content_method__unset_default_method (varchar) +create or replace function content_method__unset_default_method (varchar) returns integer as ' declare p_content_type alias for $1; @@ -299,7 +299,7 @@ -- procedure remove_method -create function content_method__remove_method (varchar,varchar) +create or replace function content_method__remove_method (varchar,varchar) returns integer as ' declare p_content_type alias for $1; Index: openacs-4/packages/cms/sql/postgresql/cms-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-create.sql,v diff -u -N -r1.5.4.2 -r1.5.4.3 --- openacs-4/packages/cms/sql/postgresql/cms-create.sql 31 Jan 2003 20:26:13 -0000 1.5.4.2 +++ openacs-4/packages/cms/sql/postgresql/cms-create.sql 11 Feb 2003 17:07:33 -0000 1.5.4.3 @@ -14,7 +14,7 @@ \i cms-update.sql -create function inline_0 () +create or replace function inline_0 () returns integer as ' declare attr_id acs_attributes.attribute_id%TYPE; @@ -149,7 +149,7 @@ -- create or replace package body content_module -create function content_module__new (varchar,varchar,varchar,integer,integer) +create or replace function content_module__new (varchar,varchar,varchar,integer,integer) returns integer as ' declare p_name alias for $1; @@ -172,14 +172,14 @@ ); end;' language 'plpgsql'; -create function content_module__new (varchar,varchar,integer,integer,integer) +create or replace function content_module__new (varchar,varchar,integer,integer,integer) returns integer as ' begin return content_module__new ($1, $2, cast ($3 as varchar), $4, $5); end;' language 'plpgsql'; -- function new -create function content_module__new (varchar,varchar,varchar,integer,integer,integer,timestamp with time zone,integer,varchar,varchar) +create or replace function content_module__new (varchar,varchar,varchar,integer,integer,integer,timestamp with time zone,integer,varchar,varchar) returns integer as ' declare p_name alias for $1; @@ -223,7 +223,7 @@ end;' language 'plpgsql'; -create function content_module__get_label (integer) returns varchar as ' +create or replace function content_module__get_label (integer) returns varchar as ' declare p_module_id alias for $1; v_name cm_modules.name%TYPE; @@ -241,7 +241,7 @@ end;' language 'plpgsql'; -- Insert the default modules -create function inline_1 () returns integer as ' +create or replace function inline_1 () returns integer as ' declare v_id integer; v_module_id integer; @@ -271,7 +271,7 @@ -- Get the alphabetical ordering of a string, based on the first -- character. Treat all non-alphabetical characters as before ''a'' -create function letter_placement (varchar) returns integer as ' +create or replace function letter_placement (varchar) returns integer as ' declare p_word alias for $1; v_letter varchar(1); Index: openacs-4/packages/cms/sql/postgresql/cms-fix.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-fix.sql,v diff -u -N -r1.4 -r1.4.4.1 --- openacs-4/packages/cms/sql/postgresql/cms-fix.sql 22 May 2001 22:46:13 -0000 1.4 +++ openacs-4/packages/cms/sql/postgresql/cms-fix.sql 11 Feb 2003 17:07:33 -0000 1.4.4.1 @@ -13,7 +13,7 @@ -- content_module inherit from content_item -- this way it is possible to grant permissions on content modules -create function inline_0 () +create or replace function inline_0 () returns integer as ' declare v_user_id users.user_id%TYPE; Index: openacs-4/packages/cms/sql/postgresql/cms-forms.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-forms.sql,v diff -u -N -r1.4.4.2 -r1.4.4.3 --- openacs-4/packages/cms/sql/postgresql/cms-forms.sql 6 Feb 2003 15:56:31 -0000 1.4.4.2 +++ openacs-4/packages/cms/sql/postgresql/cms-forms.sql 11 Feb 2003 17:07:33 -0000 1.4.4.3 @@ -120,99 +120,8 @@ order by object_type, sort_order; --- create or replace package cm_form_widget --- is --- --- procedure set_attribute_order ( --- --/** Update the sort_order column of acs_attributes. --- -- @author Karl Goldstein --- -- @param content_type The name of the content type --- -- @param attribute_name The name of the attribute --- -- @param sort_order The sort order. --- --*/ --- content_type in acs_attributes.object_type%TYPE, --- attribute_name in acs_attributes.attribute_name%TYPE, --- sort_order in acs_attributes.sort_order%TYPE --- ); --- --- procedure register_attribute_widget ( --- --/** Register a form widget to a content type attribute. The form widget --- -- uses the default values if none are set. If there is already a widget --- -- registered to the attribute, the new widget replaces the old widget, --- -- and all parameters are set to their default values. --- -- @author Karl Goldstein, Stanislav Freidin --- -- @param content_type The name of the content type --- -- @param attribute_name The name of the attribute --- -- @param widget The name of the form widget to use in metadata --- -- forms --- -- @param is_required Whether this form widget requires a value, --- -- defaults to 'f' --- -- @see /ats/form-procs.tcl/element_create, --- -- {cm_form_widget.set_attribute_param_value}, --- -- {cm_form_widget.unregister_attribute_widget} --- --*/ --- content_type in acs_attributes.object_type%TYPE, --- attribute_name in acs_attributes.attribute_name%TYPE, --- widget in cm_form_widgets.widget%TYPE, --- is_required in cm_attribute_widgets.is_required%TYPE default 'f' --- ); --- --- procedure unregister_attribute_widget ( --- --/** Unregister a form widget from a content type attribute. --- -- The attribute will no longer show up on the dynamic revision --- -- upload form.

If no widget is registered to the attribute, --- -- the procedure does nothing. --- -- @author Karl Goldstein, Stanislav Freidin --- -- @param content_type The name of the content type --- -- @param attribute_name The name of the attribute for which to --- -- unregister the widget --- -- @see {cm_form_widget.register_attribute_widget} --- --*/ --- content_type in acs_attributes.object_type%TYPE, --- attribute_name in acs_attributes.attribute_name%TYPE --- ); --- --- procedure set_attribute_param_value ( --- --/** Sets custom values for the param tag of a form widget that is --- -- registered to a content type attribute. Unless this procedure is --- -- called, the default form widget param values are used.

--- -- If the parameter already has a value associated with it, the old --- -- value is overwritten. --- -- @author Karl Goldstein, Stanislav Freidin --- -- @param content_type The name of the content type --- -- @param attribute_name The name of the attribute --- -- @param param The name of the form widget parameter. --- -- Can be an ATS 'element create' flag or an --- -- HTML form widget tag --- -- @param param_type The type of value the param tag expects. --- -- Can be 'onevalue','onelist', or 'multilist', --- -- defaults to 'onevalue' --- -- @param param_source How the param value is to be acquired, either --- -- 'literal', 'eval', or 'query', defaults to --- -- 'literal' --- -- @param value The value(s) or means or obtaining the value(s) --- -- for the param tag --- -- @see /ats/form-procs.tcl/element_create, --- -- {cm_form_widget.register_attribute_widget} --- --*/ --- content_type in acs_attributes.object_type%TYPE, --- attribute_name in acs_attributes.attribute_name%TYPE, --- param in cm_form_widget_params.param%TYPE, --- value in cm_attribute_widget_params.value%TYPE, --- param_type in cm_attribute_widget_params.param_type%TYPE --- default 'onevalue', --- param_source in cm_attribute_widget_params.param_source%TYPE --- default 'literal' --- ); --- --- end cm_form_widget; --- show errors - - --- create or replace package body cm_form_widget --- procedure register_attribute_widget -create function cm_form_widget__register_attribute_widget (varchar,varchar,varchar,boolean) +create or replace function cm_form_widget__register_attribute_widget (varchar,varchar,varchar,boolean) returns integer as ' declare p_content_type alias for $1; @@ -272,7 +181,7 @@ -- procedure set_attribute_order -create function cm_form_widget__set_attribute_order (varchar,varchar,integer) +create or replace function cm_form_widget__set_attribute_order (varchar,varchar,integer) returns integer as ' declare p_content_type alias for $1; @@ -295,7 +204,7 @@ -- procedure unregister_attribute_widget -create function cm_form_widget__unregister_attribute_widget (varchar,varchar) +create or replace function cm_form_widget__unregister_attribute_widget (varchar,varchar) returns integer as ' declare p_content_type alias for $1; @@ -337,7 +246,7 @@ -- procedure set_attribute_param_value -create function cm_form_widget__set_attribute_param_value (varchar,varchar,varchar,varchar,varchar,varchar) +create or replace function cm_form_widget__set_attribute_param_value (varchar,varchar,varchar,varchar,varchar,varchar) returns integer as ' declare p_content_type alias for $1; @@ -405,13 +314,10 @@ return 0; end;' language 'plpgsql'; -create function cm_form_widget__set_attribute_param_value (varchar,varchar,varchar,integer,varchar,varchar) +create or replace function cm_form_widget__set_attribute_param_value (varchar,varchar,varchar,integer,varchar,varchar) returns integer as ' begin return cm_form_widget__set_attribute_param_value($1, $2, $3, cast ($4 as varchar), $5, $6); end;' language 'plpgsql'; --- show errors - - \i cms-widgets.sql Index: openacs-4/packages/cms/sql/postgresql/cms-permissions.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-permissions.sql,v diff -u -N -r1.9 -r1.9.2.1 --- openacs-4/packages/cms/sql/postgresql/cms-permissions.sql 20 Sep 2002 03:54:26 -0000 1.9 +++ openacs-4/packages/cms/sql/postgresql/cms-permissions.sql 11 Feb 2003 17:07:33 -0000 1.9.2.1 @@ -1,7 +1,7 @@ -- This file will eventually replace content-perms.sql -- Implements the CMS permission -create function inline_0 () +create or replace function inline_0 () returns integer as ' declare v_perms boolean default ''f''; @@ -220,7 +220,7 @@ -- create or replace package body cms_permission -- procedure update_permissions -create function cms_permission__update_permissions (integer,varchar) +create or replace function cms_permission__update_permissions (integer,varchar) returns integer as ' declare p_item_id alias for $1; @@ -311,7 +311,7 @@ -- function has_grant_authority -create function cms_permission__has_grant_authority (integer,integer,varchar) +create or replace function cms_permission__has_grant_authority (integer,integer,varchar) returns boolean as ' declare p_item_id alias for $1; @@ -335,7 +335,7 @@ -- function has_revoke_authority -create function cms_permission__has_revoke_authority (integer,integer,varchar,integer) +create or replace function cms_permission__has_revoke_authority (integer,integer,varchar,integer) returns boolean as ' declare p_item_id alias for $1; @@ -371,7 +371,7 @@ ); insert into v_items (value) values ('{0}'); -create function v_items_tr () returns opaque as ' +create or replace function v_items_tr () returns opaque as ' begin raise EXCEPTION ''Only updates are allowed on this table''; return null; @@ -386,7 +386,7 @@ ); insert into v_perms (value) values ('{''}'); -create function v_perms_tr () returns opaque as ' +create or replace function v_perms_tr () returns opaque as ' begin raise EXCEPTION ''Only updates are allowed on this table''; return null; @@ -398,7 +398,7 @@ -- procedure grant_permission -- FIXME: need to fix problem with defined types -create function cms_permission__grant_permission (integer,integer,varchar,integer,varchar) +create or replace function cms_permission__grant_permission (integer,integer,varchar,integer,varchar) returns integer as ' declare p_item_id alias for $1; @@ -487,7 +487,7 @@ -- procedure revoke_permission -create function cms_permission__revoke_permission (integer,integer,varchar,integer,varchar) +create or replace function cms_permission__revoke_permission (integer,integer,varchar,integer,varchar) returns integer as ' declare p_item_id alias for $1; @@ -583,7 +583,7 @@ -- function permission_p -create function cms_permission__permission_p (integer,integer,varchar) +create or replace function cms_permission__permission_p (integer,integer,varchar) returns boolean as ' declare p_item_id alias for $1; @@ -667,7 +667,7 @@ end;' language 'plpgsql'; -create function cms_permission__cm_admin_exists() returns boolean as ' +create or replace function cms_permission__cm_admin_exists() returns boolean as ' declare v_exists boolean; begin @@ -692,7 +692,7 @@ -- A trigger to automatically grant item creators the cm_write and cm_perm -- permissions -create function cr_items_permission_tr () returns opaque as ' +create or replace function cr_items_permission_tr () returns opaque as ' declare v_user_id parties.party_id%TYPE; begin @@ -808,7 +808,7 @@ -- create or replace package body content_permission -- procedure inherit_permissions -create function content_permission__inherit_permissions (integer,integer,integer) +create or replace function content_permission__inherit_permissions (integer,integer,integer) returns integer as ' declare p_parent_object_id alias for $1; @@ -821,7 +821,7 @@ -- function has_grant_authority -create function content_permission__has_grant_authority (integer,integer,varchar) +create or replace function content_permission__has_grant_authority (integer,integer,varchar) returns boolean as ' declare p_object_id alias for $1; @@ -836,7 +836,7 @@ -- procedure grant_permission_h -create function content_permission__grant_permission_h (integer,integer,varchar) +create or replace function content_permission__grant_permission_h (integer,integer,varchar) returns integer as ' declare p_object_id alias for $1; @@ -848,7 +848,7 @@ -- procedure grant_permission -create function content_permission__grant_permission (integer,integer,varchar,integer,varchar,varchar) +create or replace function content_permission__grant_permission (integer,integer,varchar,integer,varchar,varchar) returns integer as ' declare p_object_id alias for $1; @@ -867,7 +867,7 @@ -- function has_revoke_authority -create function content_permission__has_revoke_authority (integer,integer,varchar,integer) +create or replace function content_permission__has_revoke_authority (integer,integer,varchar,integer) returns boolean as ' declare p_object_id alias for $1; @@ -883,7 +883,7 @@ -- procedure revoke_permission_h -create function content_permission__revoke_permission_h (integer,integer,varchar) +create or replace function content_permission__revoke_permission_h (integer,integer,varchar) returns integer as ' declare p_object_id alias for $1; @@ -895,7 +895,7 @@ -- procedure revoke_permission -create function content_permission__revoke_permission (integer,integer,varchar,integer,varchar,varchar) +create or replace function content_permission__revoke_permission (integer,integer,varchar,integer,varchar,varchar) returns integer as ' declare p_object_id alias for $1; @@ -914,7 +914,7 @@ -- function permission_p -create function content_permission__permission_p (integer,integer,varchar) +create or replace function content_permission__permission_p (integer,integer,varchar) returns boolean as ' declare p_object_id alias for $1; Index: openacs-4/packages/cms/sql/postgresql/cms-publishing-wf.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-publishing-wf.sql,v diff -u -N -r1.8.2.1 -r1.8.2.2 --- openacs-4/packages/cms/sql/postgresql/cms-publishing-wf.sql 8 Oct 2002 17:03:20 -0000 1.8.2.1 +++ openacs-4/packages/cms/sql/postgresql/cms-publishing-wf.sql 11 Feb 2003 17:07:33 -0000 1.8.2.2 @@ -6,7 +6,7 @@ references wf_cases ); -create function inline_0 () +create or replace function inline_0 () returns integer as ' declare v_workflow_key varchar(100); @@ -244,7 +244,7 @@ -- create or replace package body publishing_wf as -- function is_next -create function publishing_wf__is_next (integer,varchar,varchar,varchar,varchar,varchar) +create or replace function publishing_wf__is_next (integer,varchar,varchar,varchar,varchar,varchar) returns char as ' declare p_case_id alias for $1; @@ -268,7 +268,7 @@ end;' language 'plpgsql'; -create function inline_2 () +create or replace function inline_2 () returns integer as ' declare v_attribute_id acs_attributes.attribute_id%TYPE; Index: openacs-4/packages/cms/sql/postgresql/cms-update.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-update.sql,v diff -u -N -r1.3 -r1.3.4.1 --- openacs-4/packages/cms/sql/postgresql/cms-update.sql 22 May 2001 22:46:13 -0000 1.3 +++ openacs-4/packages/cms/sql/postgresql/cms-update.sql 11 Feb 2003 17:07:33 -0000 1.3.4.1 @@ -1,6 +1,6 @@ -- Modify permissions to include the cm_relate permission -create function inline_0 () +create or replace function inline_0 () returns integer as ' declare v_exists integer; @@ -33,7 +33,7 @@ -- This parent_id column was not included in the cr_keywords table -- for RC 0. Ensure this column is there. -create function inline_1 () +create or replace function inline_1 () returns integer as ' begin @@ -63,7 +63,7 @@ -- show errors -- Drop the broken trigger, if any -create function inline_2 () +create or replace function inline_2 () returns integer as ' begin -- FIXME: DCW - can''t locate where this trigger is created. Need a table Index: openacs-4/packages/cms/sql/postgresql/cms-widgets.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-widgets.sql,v diff -u -N -r1.4 -r1.4.4.1 --- openacs-4/packages/cms/sql/postgresql/cms-widgets.sql 8 Jun 2001 01:44:53 -0000 1.4 +++ openacs-4/packages/cms/sql/postgresql/cms-widgets.sql 11 Feb 2003 17:07:33 -0000 1.4.4.1 @@ -3,7 +3,7 @@ /* insert form widgets and params */ -create function inline_0 () +create or replace function inline_0 () returns integer as ' begin @@ -94,7 +94,7 @@ -- show errors -create function inline_1 () +create or replace function inline_1 () returns integer as ' begin @@ -123,7 +123,7 @@ -create function inline_2 () +create or replace function inline_2 () returns integer as ' begin @@ -188,7 +188,7 @@ /* Register attribute widgets for content_revision and image */ -create function inline_3 () +create or replace function inline_3 () returns integer as ' begin -- register form widgetes for content revision attributes @@ -302,7 +302,7 @@ -create function inline_4 () +create or replace function inline_4 () returns integer as ' begin Index: openacs-4/packages/cms/sql/postgresql/cms-workflow.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-workflow.sql,v diff -u -N -r1.11.4.1 -r1.11.4.2 --- openacs-4/packages/cms/sql/postgresql/cms-workflow.sql 21 Jan 2003 13:48:57 -0000 1.11.4.1 +++ openacs-4/packages/cms/sql/postgresql/cms-workflow.sql 11 Feb 2003 17:07:33 -0000 1.11.4.2 @@ -340,7 +340,7 @@ -- create or replace package body content_workflow -- function is_overdue -create function content_workflow__is_overdue (integer) +create or replace function content_workflow__is_overdue (integer) returns boolean as ' declare p_task_id alias for $1; @@ -368,7 +368,7 @@ -- function is_overdue -create function content_workflow__is_overdue (integer,varchar) +create or replace function content_workflow__is_overdue (integer,varchar) returns boolean as ' declare p_case_id alias for $1; @@ -393,7 +393,7 @@ -- function get_holding_user_name -create function content_workflow__get_holding_user_name (integer) +create or replace function content_workflow__get_holding_user_name (integer) returns varchar as ' declare p_task_id alias for $1; @@ -417,7 +417,7 @@ -create function content_workflow__get_first_place() returns varchar as ' +create or replace function content_workflow__get_first_place() returns varchar as ' declare v_first_place wf_places.place_key%TYPE; begin @@ -441,7 +441,7 @@ end;' language 'plpgsql'; -- function get_this_place -create function content_workflow__get_this_place (varchar) +create or replace function content_workflow__get_this_place (varchar) returns varchar as ' declare p_transition_key alias for $1; @@ -470,7 +470,7 @@ -- function get_next_place -create function content_workflow__get_next_place (varchar) +create or replace function content_workflow__get_next_place (varchar) returns varchar as ' declare p_transition_key alias for $1; @@ -505,7 +505,7 @@ -- function get_previous_place -create function content_workflow__get_previous_place (varchar) +create or replace function content_workflow__get_previous_place (varchar) returns varchar as ' declare p_transition_key alias for $1; @@ -540,7 +540,7 @@ -- procedure checkout -create function content_workflow__checkout (integer,timestamp with time zone,integer,varchar,varchar) +create or replace function content_workflow__checkout (integer,timestamp with time zone,integer,varchar,varchar) returns integer as ' declare p_task_id alias for $1; @@ -634,7 +634,7 @@ -- procedure checkin -create function content_workflow__checkin (integer,integer,varchar,varchar) +create or replace function content_workflow__checkin (integer,integer,varchar,varchar) returns integer as ' declare p_task_id alias for $1; @@ -694,7 +694,7 @@ -- procedure approve -create function content_workflow__approve (integer,integer,varchar,varchar) +create or replace function content_workflow__approve (integer,integer,varchar,varchar) returns integer as ' declare p_task_id alias for $1; @@ -770,7 +770,7 @@ -- procedure reject -create function content_workflow__reject (integer,integer,varchar,varchar,varchar) +create or replace function content_workflow__reject (integer,integer,varchar,varchar,varchar) returns integer as ' declare p_task_id alias for $1; @@ -877,7 +877,7 @@ -- procedure notify_of_checkout -create function content_workflow__notify_of_checkout (integer,integer,integer,varchar) +create or replace function content_workflow__notify_of_checkout (integer,integer,integer,varchar) returns integer as ' declare p_task_id alias for $1; @@ -937,7 +937,7 @@ -- function can_reject -create function content_workflow__can_reject (integer,integer) +create or replace function content_workflow__can_reject (integer,integer) returns boolean as ' declare p_task_id alias for $1; @@ -965,7 +965,7 @@ -- function can_approve -create function content_workflow__can_approve (integer,integer) +create or replace function content_workflow__can_approve (integer,integer) returns boolean as ' declare p_task_id alias for $1; @@ -989,7 +989,7 @@ -- function can_start -create function content_workflow__can_start (integer,integer) +create or replace function content_workflow__can_start (integer,integer) returns boolean as ' declare p_task_id alias for $1; @@ -1013,7 +1013,7 @@ -- function approve_string -create function content_workflow__approve_string (integer,integer) +create or replace function content_workflow__approve_string (integer,integer) returns varchar as ' declare p_task_id alias for $1; @@ -1049,7 +1049,7 @@ -- function count_finished_tasks -create function content_workflow__count_finished_tasks (integer) +create or replace function content_workflow__count_finished_tasks (integer) returns integer as ' declare p_case_id alias for $1; @@ -1086,7 +1086,7 @@ -- function count_unfinished_tasks -create function content_workflow__count_unfinished_tasks (integer) +create or replace function content_workflow__count_unfinished_tasks (integer) returns integer as ' declare p_case_id alias for $1; @@ -1114,7 +1114,7 @@ -- function is_active -create function content_workflow__is_active (integer,varchar) +create or replace function content_workflow__is_active (integer,varchar) returns boolean as ' declare p_case_id alias for $1; @@ -1136,7 +1136,7 @@ -- function is_finished -create function content_workflow__is_finished (integer,varchar) +create or replace function content_workflow__is_finished (integer,varchar) returns boolean as ' declare p_case_id alias for $1; @@ -1186,7 +1186,7 @@ -- function is_checked_out -create function content_workflow__is_checked_out (integer,varchar) +create or replace function content_workflow__is_checked_out (integer,varchar) returns boolean as ' declare p_case_id alias for $1; @@ -1209,7 +1209,7 @@ -- function is_checked_out -create function content_workflow__is_checked_out (integer,varchar,integer) +create or replace function content_workflow__is_checked_out (integer,varchar,integer) returns boolean as ' declare p_case_id alias for $1; @@ -1235,7 +1235,7 @@ -- function get_status -create function content_workflow__get_status (integer,varchar) +create or replace function content_workflow__get_status (integer,varchar) returns varchar as ' declare p_case_id alias for $1; @@ -1283,7 +1283,7 @@ -- function can_touch -create function content_workflow__can_touch (integer,integer) +create or replace function content_workflow__can_touch (integer,integer) returns boolean as ' declare p_item_id alias for $1; @@ -1342,7 +1342,7 @@ -- function unfinished_workflow_exists -create function content_workflow__unfinished_workflow_exists (integer) +create or replace function content_workflow__unfinished_workflow_exists (integer) returns boolean as ' declare p_item_id alias for $1; Index: openacs-4/packages/cms/sql/postgresql/rel-test.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/rel-test.sql,v diff -u -N -r1.2 -r1.2.4.1 --- openacs-4/packages/cms/sql/postgresql/rel-test.sql 22 May 2001 04:44:57 -0000 1.2 +++ openacs-4/packages/cms/sql/postgresql/rel-test.sql 11 Feb 2003 17:07:33 -0000 1.2.4.1 @@ -18,7 +18,7 @@ check (direction in ('in', 'out')) ); -create function inline_0 () returns integer as ' +create or replace function inline_0 () returns integer as ' declare attr_id integer; begin @@ -95,7 +95,7 @@ weight_b integer not null ); -create function inline_1 () returns integer as ' +create or replace function inline_1 () returns integer as ' declare attr_id integer; begin Index: openacs-4/packages/cms/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/cms/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql 11 Feb 2003 17:08:19 -0000 1.1.2.1 @@ -0,0 +1,146 @@ +-- DRB: Needed because workflow views were dependent on the party_approved_member_map +-- view (which is now a table) and because PG doesn't support CREATE OR REPLACE VIEW. + +-- function permission_p +create or replace function cms_permission__permission_p (integer,integer,varchar) +returns boolean as ' +declare + p_item_id alias for $1; + p_holder_id alias for $2; + p_privilege alias for $3; + v_workflow_count integer; + v_task_count integer; +begin + + -- Check permission the old-fashioned way first + if acs_permission__permission_p ( + p_item_id, p_holder_id, p_privilege + ) = ''f'' + then + return ''f''; + end if; + + -- Special case for workflow + + if p_privilege = ''cm_relate'' or + p_privilege = ''cm_write'' or + p_privilege = ''cm_new'' + then + + -- Check if the publishing workflow exists, and if it + -- is the only workflow that exists + select + count(case_id) into v_workflow_count + from + wf_cases + where + object_id = p_item_id; + + -- If there are multiple workflows / no workflows, do nothing + -- special + if v_workflow_count <> 1 then + return ''t''; + end if; + + -- Even if there is a workflow, the user can touch the item if he + -- has cm_item_workflow + if acs_permission__permission_p ( + p_item_id, p_holder_id, ''cm_item_workflow'' + ) = ''t'' + then + return ''t''; + end if; + + -- Check if the user holds the current task + if v_workflow_count = 0 then + return ''f''; + end if; + + select + count(task_id) into v_task_count + from + wf_user_tasks t, wf_cases c + where + t.case_id = c.case_id + and + c.workflow_key = ''publishing_wf'' + and + c.state = ''active'' + and + c.object_id = p_item_id + and + ( t.state = ''enabled'' + or + ( t.state = ''started'' and t.holding_user = p_holder_id )) + and + t.user_id = p_holder_id; + + -- is the user assigned a current task on this item + if v_task_count = 0 then + return ''f''; + end if; + + end if; + + return ''t''; + +end;' language 'plpgsql'; + +-- function can_touch +create or replace function content_workflow__can_touch (integer,integer) +returns boolean as ' +declare + p_item_id alias for $1; + p_user_id alias for $2; + v_workflow_count integer; + v_task_count integer; +begin + + -- cm_admin has highest precedence + if content_permission__permission_p( + p_item_id, p_user_id, ''cm_item_workflow'' ) = ''t'' then + return ''t''; + end if; + + select + count(case_id) into v_workflow_count + from + wf_cases + where + object_id = p_item_id; + + -- workflow must exist + if v_workflow_count = 0 then + return ''f''; + end if; + + select + count(task_id) into v_task_count + from + wf_user_tasks t, wf_cases c + where + t.case_id = c.case_id + and + c.workflow_key = ''publishing_wf'' + and + c.state = ''active'' + and + c.object_id = p_item_id + and + ( t.state = ''enabled'' + or + ( t.state = ''started'' and t.holding_user = p_user_id )) + and + t.user_id = p_user_id; + + + -- is the user assigned a current task on this item + if v_task_count = 0 then + return ''f''; + else + return ''t''; + end if; + + +end;' language 'plpgsql'; + Index: openacs-4/packages/file-storage/tcl/file-storage-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/tcl/file-storage-procs-postgresql.xql,v diff -u -N -r1.29.2.1 -r1.29.2.2 --- openacs-4/packages/file-storage/tcl/file-storage-procs-postgresql.xql 21 Jan 2003 13:50:16 -0000 1.29.2.1 +++ openacs-4/packages/file-storage/tcl/file-storage-procs-postgresql.xql 11 Feb 2003 17:09:46 -0000 1.29.2.2 @@ -45,31 +45,29 @@ --- we use $ n_past-days instead of :n_past_days because the pgdriver --- bind variable emulation puts single-quotes around the n_past_days --- integer. Postgresql tries to turn '-1' into a date datatype --- so now()-'-1' fails but now()- interval '-1' works fine. + select fs_objects.object_id, + fs_objects.name, + fs_objects.live_revision, + fs_objects.type, + to_char(fs_objects.last_modified, 'Month DD YYYY HH24:MI') as last_modified, + fs_objects.content_size, + fs_objects.url, + fs_objects.key, + fs_objects.sort_key, + fs_objects.file_upload_name, + case when fs_objects.last_modified >= (now() - interval '$n_past_days days') then 1 else 0 end as new_p, + acs_permission__permission_p(fs_objects.object_id, :user_id, 'admin') as admin_p, + acs_permission__permission_p(fs_objects.object_id, :user_id, 'delete') as delete_p, + acs_permission__permission_p(fs_objects.object_id, :user_id, 'write') as write_p + from fs_objects + where fs_objects.parent_id = :folder_id + and exists (select 1 + from acs_object_party_privilege_map m + where m.object_id = fs_objects.object_id + and m.party_id = :user_id + and m.privilege = 'read') + order by fs_objects.sort_key, fs_objects.name - select fc.* - from (select fs_objects.object_id, - fs_objects.name, - fs_objects.live_revision, - fs_objects.type, - to_char(fs_objects.last_modified, 'Month DD YYYY HH24:MI') as last_modified, - fs_objects.content_size, - fs_objects.url, - fs_objects.key, - fs_objects.sort_key, - fs_objects.file_upload_name, - case when fs_objects.last_modified >= (now() - interval '$n_past_days days') then 1 else 0 end as new_p, - acs_permission__permission_p(fs_objects.object_id, :user_id, 'admin') as admin_p, - acs_permission__permission_p(fs_objects.object_id, :user_id, 'delete') as delete_p, - acs_permission__permission_p(fs_objects.object_id, :user_id, 'write') as write_p - from fs_objects - where fs_objects.parent_id = :folder_id) fc - where 't' = (select acs_permission__permission_p(fc.object_id, :user_id, 'read') from dual) - order by fc.sort_key, - fc.name Index: openacs-4/packages/file-storage/www/folder-chunk-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/www/folder-chunk-postgresql.xql,v diff -u -N -r1.1.2.2 -r1.1.2.3 --- openacs-4/packages/file-storage/www/folder-chunk-postgresql.xql 4 Feb 2003 12:15:31 -0000 1.1.2.2 +++ openacs-4/packages/file-storage/www/folder-chunk-postgresql.xql 11 Feb 2003 17:11:30 -0000 1.1.2.3 @@ -20,4 +20,4 @@ order by sort_key, name - \ No newline at end of file +