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
+