Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-util.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-util.sql,v diff -u -N -r1.5 -r1.6 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-util.sql 1 Apr 2001 05:57:53 -0000 1.5 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-util.sql 22 May 2001 22:46:13 -0000 1.6 @@ -31,7 +31,7 @@ return count(*) > 0 from pg_class c, pg_attribute a - where c.relname = = lower(column_exists__table_name) + where c.relname = lower(column_exists__table_name) and c.oid = a.attrelid and a.attname = lower(column_exists__column_name); 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.4 -r1.5 --- openacs-4/packages/cms/sql/postgresql/cms-create.sql 22 May 2001 04:06:54 -0000 1.4 +++ openacs-4/packages/cms/sql/postgresql/cms-create.sql 22 May 2001 22:46:13 -0000 1.5 @@ -149,21 +149,22 @@ -- create or replace package body content_module -create function content_module__new (varchar,varchar,varchar,integer,integer,integer,timestamp,integer,varchar,varchar) +create function content_module__new (varchar,varchar,varchar,integer,integer) returns integer as ' declare p_name alias for $1; p_key alias for $2; p_root_key alias for $3; p_sort_key alias for $4; + p_parent_id alias for $5; -- default null begin return content_module__new(p_name, p_key, p_root_key, p_sort_key, + p_parent_id, null, - null, now(), null, null, @@ -187,7 +188,7 @@ p_object_type alias for $10; -- ''content_module'' v_module_id integer; begin - module_id := content_item__new( + v_module_id := content_item__new( p_name, p_parent_id, p_object_id, @@ -209,9 +210,9 @@ insert into cm_modules (module_id, key, name, root_key, sort_key) values - (v_module_id, key, p_name, p_root_key, p_sort_key); + (v_module_id, p_key, p_name, p_root_key, p_sort_key); - return module_id; + return v_module_id; end;' language 'plpgsql'; @@ -242,7 +243,7 @@ v_id := content_module__new(''My Tasks'', ''workspace'', NULL, 1,0); v_id := content_module__new(''Site Map'', ''sitemap'', - content_item__get_root_folder(), 2,0); + content_item__get_root_folder(null), 2,0); v_id := content_module__new(''Templates'', ''templates'', content_template__get_root_folder(), 3,0); v_id := content_module__new(''Content Types'', ''types'', 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.3 -r1.4 --- openacs-4/packages/cms/sql/postgresql/cms-fix.sql 21 May 2001 23:13:59 -0000 1.3 +++ openacs-4/packages/cms/sql/postgresql/cms-fix.sql 22 May 2001 22:46:13 -0000 1.4 @@ -94,19 +94,19 @@ ''f'' ); - v_id := content_module__new('My Tasks', 'workspace', NULL, 1,0); - v_id := content_module__new('Site Map', 'sitemap', - content_item.get_root_folder, 2,0); - v_id := content_module__new('Templates', 'templates', - content_template.get_root_folder, 3,0); - v_id := content_module__new('Content Types', 'types', - 'content_revision', 4,0); + v_id := content_module__new(''My Tasks'', ''workspace'', NULL, 1,0); + v_id := content_module__new(''Site Map'', ''sitemap'', + content_item__get_root_folder(), 2,0); + v_id := content_module__new(''Templates'', ''templates'', + content_template__get_root_folder(), 3,0); + v_id := content_module__new(''Content Types'', ''types'', + ''content_revision'', 4,0); v_module_id := v_id; - v_id := content_module__new('Search', 'search', null, 5,0); - v_id := content_module__new('Subject Keywords', 'categories', 0, 6,0); - v_id := content_module__new('Users', 'users', null, 7,0); - v_id := content_module__new('Workflows', 'workflow', null, 8,0); + v_id := content_module__new(''Search'', ''search'', null, 5,0); + v_id := content_module__new(''Subject Keywords'', ''categories'', 0, 6,0); + v_id := content_module__new(''Users'', ''users'', null, 7,0); + v_id := content_module__new(''Workflows'', ''workflow'', null, 8,0); -- upgrade hack, grant users with sitemap privs permission on types module for v_sitemap_perms in @@ -115,7 +115,7 @@ from acs_permissions where - object_id = content_item__get_root_folder(); + object_id = content_item__get_root_folder() LOOP PERFORM acs_permission__grant_permission( v_module_id, v_sitemap_perms.grantee_id, v_sitemap_perms.privilege ); 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.3 -r1.4 --- openacs-4/packages/cms/sql/postgresql/cms-forms.sql 22 May 2001 04:03:04 -0000 1.3 +++ openacs-4/packages/cms/sql/postgresql/cms-forms.sql 22 May 2001 22:46:13 -0000 1.4 @@ -83,17 +83,17 @@ from acs_attributes at, (select - widgets.attribute_id, widgets.is_required widget_is_required, + widgets.attribute_id, widgets.is_required as widget_is_required, widgets.widget, params.param_id, params.param_type, params.param_source, - nvl(params.value,params.default_value) value, + coalesce(params.value,params.default_value) as value, params.param, params.param_is_required, params.is_html, params.default_value from cm_attribute_widgets widgets LEFT OUTER JOIN (select awp.attribute_id, awp.param_id, awp.param_type, awp.param_source, awp.value, - fwp.param, fwp.is_required param_is_required, + fwp.param, fwp.is_required as param_is_required, fwp.is_html, fwp.default_value from cm_form_widget_params fwp, cm_attribute_widget_params awp @@ -103,7 +103,7 @@ select aw.attribute_id, fwp.param_id, 'onevalue' as param_type, 'literal' as param_source, - default_value as value, fwp.param, fwp.is_required param_is_required, + default_value as value, fwp.param, fwp.is_required as param_is_required, fwp.is_html, fwp.default_value from cm_form_widget_params fwp, cm_attribute_widgets aw @@ -364,8 +364,8 @@ aw.attribute_id = a.attribute_id; if NOT FOUND then - raise EXCEPTION ''-20000: No widget is registered for attribute %''.% in cm_form_widget.set_attribute_param_value'', p_content_type, p_attribute_name; - end; + raise EXCEPTION ''-20000: No widget is registered for attribute %.% in cm_form_widget.set_attribute_param_value'', p_content_type, p_attribute_name; + end if; -- Get the param id select param_id into v_param_id from cm_form_widget_params 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.4 -r1.5 --- openacs-4/packages/cms/sql/postgresql/cms-permissions.sql 22 May 2001 04:26:49 -0000 1.4 +++ openacs-4/packages/cms/sql/postgresql/cms-permissions.sql 22 May 2001 22:46:13 -0000 1.5 @@ -4,7 +4,7 @@ create function inline_0 () returns integer as ' declare - v_perms varchar2(1) := ''f''; + v_perms boolean default ''f''; begin select ''t'' into v_perms from dual @@ -17,44 +17,64 @@ if v_perms <> ''t'' then + -- Dummy root privilege - PERFORM acs_privilege__create_privilege('cm_root', 'Root', 'Root'); + PERFORM acs_privilege__create_privilege(''cm_root'', ''Root'', ''Root''); -- He can do everything - PERFORM acs_privilege__create_privilege('cm_admin', 'Administrator', 'Administrators'); - PERFORM acs_privilege__create_privilege('cm_relate', 'Relate Items', 'Relate Items'); - PERFORM acs_privilege__create_privilege('cm_write', 'Write', 'Write'); - PERFORM acs_privilege__create_privilege('cm_new', 'Create New Item', 'Create New Item'); - PERFORM acs_privilege__create_privilege('cm_examine', 'Admin-level Read', 'Admin-level Read'); - PERFORM acs_privilege__create_privilege('cm_read', 'User-level Read', 'User-level Read'); - PERFORM acs_privilege__create_privilege('cm_item_workflow', 'Modify Workflow', 'Modify Workflow'); - PERFORM acs_privilege__create_privilege('cm_perm_admin', 'Modify Any Permissions', 'Modify Any Permissions'); + PERFORM acs_privilege__create_privilege(''cm_admin'', ''Administrator'', ''Administrators''); + PERFORM acs_privilege__create_privilege(''cm_relate'', ''Relate Items'', ''Relate Items''); + PERFORM acs_privilege__create_privilege(''cm_write'', ''Write'', ''Write''); + PERFORM acs_privilege__create_privilege(''cm_new'', ''Create New Item'', ''Create New Item''); + PERFORM acs_privilege__create_privilege(''cm_examine'', ''Admin-level Read'', ''Admin-level Read''); + PERFORM acs_privilege__create_privilege(''cm_read'', ''User-level Read'', ''User-level Read''); + PERFORM acs_privilege__create_privilege(''cm_item_workflow'', ''Modify Workflow'', ''Modify Workflow''); + PERFORM acs_privilege__create_privilege(''cm_perm_admin'', ''Modify Any Permissions'', ''Modify Any Permissions''); - PERFORM acs_privilege__create_privilege('cm_perm', 'Donate Permissions', 'Donate Permissions'); + PERFORM acs_privilege__create_privilege(''cm_perm'', ''Donate Permissions'', ''Donate Permissions''); - PERFORM acs_privilege__add_child('cm_root', 'cm_admin'); -- Do anything - PERFORM acs_privilege__add_child('cm_admin', 'cm_relate'); -- Related/Child items - PERFORM acs_privilege__add_child('cm_relate', 'cm_write'); -- Modify the item - PERFORM acs_privilege__add_child('cm_write', 'cm_new'); -- Create subitems - PERFORM acs_privilege__add_child('cm_new', 'cm_examine'); -- View in admin mode - PERFORM acs_privilege__add_child('cm_examine', 'cm_read'); -- View in user mode - PERFORM acs_privilege__add_child('cm_admin', 'cm_item_workflow'); -- Change item workflow + PERFORM acs_privilege__add_child(''cm_root'', ''cm_admin''); -- Do anything + PERFORM acs_privilege__add_child(''cm_admin'', ''cm_relate''); -- Related/Child items + PERFORM acs_privilege__add_child(''cm_relate'', ''cm_write''); -- Modify the item + PERFORM acs_privilege__add_child(''cm_write'', ''cm_new''); -- Create subitems + PERFORM acs_privilege__add_child(''cm_new'', ''cm_examine''); -- View in admin mode + PERFORM acs_privilege__add_child(''cm_examine'', ''cm_read''); -- View in user mode + PERFORM acs_privilege__add_child(''cm_admin'', ''cm_item_workflow''); -- Change item workflow - PERFORM acs_privilege__add_child('cm_admin', 'cm_perm_admin'); -- Modify any permissions - PERFORM acs_privilege__add_child('cm_perm_admin', 'cm_perm'); -- Modify any permissions on an item + PERFORM acs_privilege__add_child(''cm_admin'', ''cm_perm_admin''); -- Modify any permissions + PERFORM acs_privilege__add_child(''cm_perm_admin'', ''cm_perm''); -- Modify any permissions on an item + + -- Proper inheritance - PERFORM acs_privilege__add_child('admin', 'cm_root'); + -- PERFORM acs_privilege__add_child(''admin'', ''cm_root''); end if; return 0; end;' language 'plpgsql'; +-- 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 inline_0 (); +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 (); + drop function inline_0 (); +select acs_privilege__add_child('admin', 'cm_root') +from dual +where not exists (select 1 + from acs_privilege_hierarchy + where privilege = 'admin' + and child_privilege = 'cm_root') +limit 1; + -- show errors -- create or replace package cms_permission @@ -344,7 +364,35 @@ end;' language 'plpgsql'; +create table v_items ( + value integer[] +); +insert into v_items (value) values ('{0}'); +create function v_items_tr () returns opaque as ' +begin + raise EXCEPTION ''Only updates are allowed on this table''; + return null; +end;' language 'plpgsql'; + +create trigger v_items_tr before insert or delete on v_items +for each row execute procedure v_items_tr(); + + +create table v_perms ( + value varchar(100)[] +); +insert into v_perms (value) values ('{''}'); + +create function v_perms_tr () returns opaque as ' +begin + raise EXCEPTION ''Only updates are allowed on this table''; + return null; +end;' language 'plpgsql'; + +create trigger v_perms_tr before insert or delete on v_perms +for each row execute procedure v_perms_tr(); + -- procedure grant_permission -- FIXME: need to fix problem with defined types @@ -357,10 +405,10 @@ p_recepient_id alias for $4; p_is_recursive alias for $5; -- default ''f'' v_item_id cr_items.item_id%TYPE; - v_items item_array_type; + -- v_items item_array_type; v_idx integer; v_count integer; - v_perms perm_array_type; + -- v_perms perm_array_type; v_perm acs_privileges.privilege%TYPE; v_perm_idx integer; v_perm_count integer; @@ -393,7 +441,8 @@ LOOP v_item_id := c_item_cur.item_id; v_count := v_count + 1; - v_items(v_count) := v_item_id; + -- v_items(v_count) := v_item_id; + update v_items set value[v_count] = v_item_id; exit when p_is_recursive = ''f''; end loop; @@ -404,7 +453,8 @@ -- Grant parent permission for v_idx in 1..v_count loop PERFORM acs_permission__grant_permission ( - v_items(v_idx), p_recepient_id, p_privilege + -- v_items(v_idx), p_recepient_id, p_privilege + v_items.value[v_idx], p_recepient_id, p_privilege ); end loop; @@ -417,14 +467,16 @@ loop v_perm := c_perm_cur.descendant; v_perm_count := v_perm_count + 1; - v_perms(v_perm_count) := v_perm; + -- v_perms(v_perm_count) := v_perm; + update v_perms set value[v_perm_count] = v_perm; end loop; -- Revoke child permissions for v_idx in 1..v_count loop for v_perm_idx in 1..v_perm_count loop PERFORM acs_permission__revoke_permission ( - v_items(v_idx), p_recepient_id, v_perms(v_perm_idx) + -- v_items(v_idx), p_recepient_id, v_perms(v_perm_idx) + v_items.value[v_idx], p_recepient_id, v_perms.value[v_perm_idx] ); end loop; end loop; @@ -442,11 +494,11 @@ p_privilege alias for $3; p_revokee_id alias for $4; p_is_recursive alias for $5; -- default ''f'' - v_items item_array_type; + -- v_items item_array_type; v_item_id cr_items.item_id%TYPE; v_idx integer; v_count integer; - v_perms perm_array_type; + -- v_perms perm_array_type; v_perm acs_privileges.privilege%TYPE; v_perm_idx integer; v_perm_count integer; @@ -470,7 +522,8 @@ LOOP v_perm := c_perm_cur.child_privilege; v_perm_count := v_perm_count + 1; - v_perms(v_perm_count) := v_perm; + -- v_perms(v_perm_count) := v_perm; + update v_perms set value[v_perm_count] = v_perm; end LOOP; -- Select child items @@ -496,7 +549,8 @@ LOOP v_item_id := c_item_cur.item_id; v_count := v_count + 1; - v_items(v_count) := v_item_id; + -- v_items(v_count) := v_item_id; + update v_items set value[v_count] = v_item_id; exit when p_is_recursive = ''f''; end loop; @@ -508,15 +562,17 @@ for v_idx in 1..v_count loop for v_perm_idx in 1..v_perm_count loop PERFORM acs_permission__grant_permission ( - v_items(v_idx), p_revokee_id, v_perms(v_perm_idx) + -- v_items(v_idx), p_revokee_id, v_perms(v_perm_idx) + v_items.value[v_idx], p_revokee_id, v_perms.value[v_perm_idx] ); end loop; end loop; -- Revoke the parent permission for v_idx in 1..v_count loop PERFORM acs_permission__revoke_permission ( - v_items(v_idx), + -- v_items(v_idx), + v_items.value[v_idx], p_revokee_id, p_privilege ); @@ -616,10 +672,10 @@ v_exists boolean; begin - select 't' into v_exists from dual + select ''t'' into v_exists from dual where exists ( select 1 from acs_permissions - where privilege in ('cm_admin', 'cm_root') + where privilege in (''cm_admin'', ''cm_root'') ); if NOT FOUND then 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.2 -r1.3 --- openacs-4/packages/cms/sql/postgresql/cms-publishing-wf.sql 21 May 2001 23:13:59 -0000 1.2 +++ openacs-4/packages/cms/sql/postgresql/cms-publishing-wf.sql 22 May 2001 22:46:13 -0000 1.3 @@ -102,7 +102,7 @@ 'end', 'publishing_wf', 'Approved', 4 ); -* +/* * The next step is to define the valid transitions from one place in the * workflow to another. Transitions are where actions occur, either on the * part of users or machines. @@ -126,7 +126,7 @@ 'approval', 'Approval', 'publishing_wf', 3, 'user' ); -* +/* * The next step is connect transitions to places. This is analogous * to adding arrows or arcs to the workflow diagram, pointing from places * to transitions and from transitions to other places. 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.2 -r1.3 --- openacs-4/packages/cms/sql/postgresql/cms-update.sql 21 May 2001 23:13:59 -0000 1.2 +++ openacs-4/packages/cms/sql/postgresql/cms-update.sql 22 May 2001 22:46:13 -0000 1.3 @@ -66,7 +66,7 @@ create function inline_2 () returns integer as ' begin - -- FIXME: DCW - can't locate where this trigger is created. Need a table + -- FIXME: DCW - can''t locate where this trigger is created. Need a table -- name in order to drop it in pg. -- execute ''drop trigger cr_item_permission_tr''; 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.2 -r1.3 --- openacs-4/packages/cms/sql/postgresql/cms-widgets.sql 21 May 2001 23:13:59 -0000 1.2 +++ openacs-4/packages/cms/sql/postgresql/cms-widgets.sql 22 May 2001 22:46:13 -0000 1.3 @@ -186,7 +186,7 @@ -* Register attribute widgets for content_revision and image */ +/* Register attribute widgets for content_revision and image */ create function inline_3 () returns integer as ' 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.4 -r1.5 --- openacs-4/packages/cms/sql/postgresql/cms-workflow.sql 22 May 2001 04:26:49 -0000 1.4 +++ openacs-4/packages/cms/sql/postgresql/cms-workflow.sql 22 May 2001 22:46:13 -0000 1.5 @@ -418,7 +418,7 @@ -create get_first_place() returns varchar as ' +create function get_first_place() returns varchar as ' declare v_first_place wf_places.place_key%TYPE; begin @@ -485,7 +485,7 @@ from wf_places here, wf_places there where - here.workflow_key = 'publishing_wf' + here.workflow_key = ''publishing_wf'' and here.workflow_key = there.workflow_key and @@ -520,7 +520,7 @@ from wf_places here, wf_places there where - here.workflow_key = 'publishing_wf' + here.workflow_key = ''publishing_wf'' and here.workflow_key = there.workflow_key and @@ -969,7 +969,7 @@ -- function can_approve create function content_workflow__can_approve (integer,integer) -returns boolean +returns boolean as ' declare p_task_id alias for $1; p_user_id alias for $2; @@ -1153,7 +1153,7 @@ from wf_transitions trans, wf_places here, wf_places there, wf_tasks t where - trans.workflow_key = 'publishing_wf' + trans.workflow_key = ''publishing_wf'' and here.workflow_key = there.workflow_key and @@ -1163,12 +1163,12 @@ t.case_id = p_case_id and -- the task is active - t.state in ('enabled','started') + t.state in (''enabled'',''started'') and -- here is the place the case is currently at here.place_key = content_workflow__get_this_place(t.transition_key) and - -- there is the place we're checking if it's finished + -- there is the place we are checking if it is finished there.place_key = content_workflow__get_this_place( trans.transition_key) and