- -- In addition, checks if the Publishing workflow has been assigned to - -- the item. If it has, then the user must be assigned to the current - -- workflow task in order to utilize his cm_relate, cm_write or cm_new - -- permission. - -- @author Stanislav Freidin - -- @param item_id The object whose permissions are to be checked - -- @param holder_id The person whose permissions are to be examined - -- @param privilege The privilege to be checked - -- @return 't' if the user has the specified permission on the item, - -- 'f' otherwise - -- @see {cms_permission.grant_permission}, {cms_permission.revoke_permission}, - -- {acs_permission.permission_p} - --*/ - item_id in cr_items.item_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) return varchar2; +-- create or replace package cms_permission +-- is +-- procedure update_permissions ( +-- --/** Make the child item inherit all of the permissions of the parent +-- -- item. Typically, this function is called whenever permissions on +-- -- an item are changed for the first time. +-- -- @author Stanislav Freidin +-- -- @param item_id The item_id +-- -- @param is_recursive If 'f', update child items as well, otherwise +-- -- update only the item itself (note: this is the opposite of +-- -- is_recursive in grant_permission and revoke_permission) +-- -- @see {cms_permission.grant_permission}, {cms_permission.copy_permissions} +-- --*/ +-- item_id in cr_items.item_id%TYPE, +-- is_recursive in varchar2 default 't' +-- ); +-- +-- function has_grant_authority ( +-- --/** Determine if the user may grant a certain permission to another +-- -- user. The permission may only be granted if the user has +-- -- the permission himself and posesses the cm_perm access, or if the +-- -- user posesses the cm_perm_admin access. +-- -- @author Stanislav Freidin +-- -- @param item_id The item whose permissions are to be changed +-- -- @param holder_id The person who is attempting to grant the permissions +-- -- @param privilege The privilege to be granted +-- -- @return 't' if the donation is possible, 'f' otherwise +-- -- @see {cms_permission.grant_permission}, +-- -- {cms_permission.is_has_revoke_authority}, +-- -- {acs_permission.grant_permission} +-- --*/ +-- item_id in cr_items.item_id%TYPE, +-- holder_id in parties.party_id%TYPE, +-- privilege in acs_privileges.privilege%TYPE +-- ) return varchar2; +-- +-- procedure grant_permission ( +-- --/** Grant the specified privilege to another user. If the donation is +-- -- not possible, the procedure does nothing. +-- -- @author Stanislav Freidin +-- -- @param item_id The item whose permissions are to be changed +-- -- @param holder_id The person who is attempting to grant the permissions +-- -- @param privilege The privilege to be granted +-- -- @param recepient_id The person who will gain the privilege +-- -- @param is_recursive If 't', applies the donation recursively to +-- -- all child items of the item (equivalent to UNIX's chmod -r). +-- -- If 'f', only affects the item itself. +-- -- @see {cms_permission.has_grant_authority}, +-- -- {cms_permission.revoke_permission}, +-- -- {acs_permission.grant_permission} +-- --*/ +-- item_id in cr_items.item_id%TYPE, +-- holder_id in parties.party_id%TYPE, +-- privilege in acs_privileges.privilege%TYPE, +-- recepient_id in parties.party_id%TYPE, +-- is_recursive in varchar2 default 'f' +-- ); +-- +-- function has_revoke_authority ( +-- --/** Determine if the user may take a certain permission away from another +-- -- user. The permission may only be revoked if the user has +-- -- the permission himself and posesses the cm_perm access, while the +-- -- other user does not, or if the user posesses the cm_perm_admin access. +-- -- @author Stanislav Freidin +-- -- @param item_id The item whose permissions are to be changed +-- -- @param holder_id The person who is attempting to revoke the permissions +-- -- @param privilege The privilege to be revoked +-- -- @param revokee_id The user from whom the privilege is to be taken away +-- -- @return 't' if it is possible to revoke the privilege, 'f' otherwise +-- -- @see {cms_permission.has_grant_authority}, +-- -- {cms_permission.revoke_permission}, +-- -- {acs_permission.revoke_permission} +-- --*/ +-- item_id in cr_items.item_id%TYPE, +-- holder_id in parties.party_id%TYPE, +-- privilege in acs_privileges.privilege%TYPE, +-- revokee_id in parties.party_id%TYPE +-- ) return varchar2; +-- +-- procedure revoke_permission ( +-- --/** Take the specified privilege away from another user. If the operation is +-- -- not possible, the procedure does nothing. +-- -- @author Stanislav Freidin +-- -- @param item_id The item whose permissions are to be changed +-- -- @param holder_id The person who is attempting to revoke the permissions +-- -- @param privilege The privilege to be revoked +-- -- @param recepient_id The person who will lose the privilege +-- -- @param is_recursive If 't', applies the operation recursively to +-- -- all child items of the item (equivalent to UNIX's chmod -r). +-- -- If 'f', only affects the iten itself. +-- -- @see {cms_permission.grant_permission}, +-- -- {cms_permission.has_revoke_authority}, +-- -- {acs_permission.revoke_permission} +-- --*/ +-- item_id in cr_items.item_id%TYPE, +-- holder_id in parties.party_id%TYPE, +-- privilege in acs_privileges.privilege%TYPE, +-- revokee_id in parties.party_id%TYPE, +-- is_recursive in varchar2 default 'f' +-- ); +-- +-- function permission_p ( +-- --/** Determine if the user has the specified permission on the specified +-- -- object. Does NOT check objects recursively: that is, if the user has +-- -- the permission on the parent object, he does not automatically gain +-- -- the permission on all the child objects.
+-- -- In addition, checks if the Publishing workflow has been assigned to +-- -- the item. If it has, then the user must be assigned to the current +-- -- workflow task in order to utilize his cm_relate, cm_write or cm_new +-- -- permission. +-- -- @author Stanislav Freidin +-- -- @param item_id The object whose permissions are to be checked +-- -- @param holder_id The person whose permissions are to be examined +-- -- @param privilege The privilege to be checked +-- -- @return 't' if the user has the specified permission on the item, +-- -- 'f' otherwise +-- -- @see {cms_permission.grant_permission}, {cms_permission.revoke_permission}, +-- -- {acs_permission.permission_p} +-- --*/ +-- item_id in cr_items.item_id%TYPE, +-- holder_id in parties.party_id%TYPE, +-- privilege in acs_privileges.privilege%TYPE +-- ) return varchar2; +-- +-- function cm_admin_exists +-- --/** Determine if there exists a user who has administrative +-- -- privileges on the entire content repository. +-- -- @author Stanislav Freidin +-- -- @return 't' if an administrator exists, 'f' otherwise +-- -- @see {cms_permission.grant_permission} +-- --*/ +-- return varchar2; +-- +-- end cms_permission; - function cm_admin_exists - --/** Determine if there exists a user who has administrative - -- privileges on the entire content repository. - -- @author Stanislav Freidin - -- @return 't' if an administrator exists, 'f' otherwise - -- @see {cms_permission.grant_permission} - --*/ - return varchar2; +-- show errors -end cms_permission; -/ -show errors +-- FIXME: several routines in this file use custom types that need to be +-- fixed. -create or replace package body cms_permission -is - - procedure update_permissions ( - item_id in cr_items.item_id%TYPE, - is_recursive in varchar2 default 'f' - ) - is - v_grantee_id parties.party_id%TYPE; - v_privilege acs_privileges.privilege%TYPE; - v_inherit_p varchar2(1); - v_context_id acs_objects.context_id%TYPE; - - cursor c_child_cur is - select item_id from cr_items - where parent_id = update_permissions.item_id; - begin +-- create or replace package body cms_permission +-- procedure update_permissions +create function cms_permission__update_permissions (integer,varchar) +returns integer as ' +declare + p_item_id alias for $1; + p_is_recursive alias for $2; -- default ''f'' + v_grantee_id parties.party_id%TYPE; + v_privilege acs_privileges.privilege%TYPE; + v_inherit_p varchar(1); + v_context_id acs_objects.context_id%TYPE; + c_perm_cur record; +begin -- If there is no inheritance, nothing to do select security_inherit_p, context_id into v_inherit_p, v_context_id from acs_objects - where object_id = update_permissions.item_id; + where object_id = p_item_id; - if v_inherit_p = 'f' or v_context_id is null then - return; + if v_inherit_p = ''f'' or v_context_id is null then + return null; end if; -- Remove inheritance on the item - update acs_objects set security_inherit_p = 'f' - where object_id = update_permissions.item_id; + update acs_objects set security_inherit_p = ''f'' + where object_id = p_item_id; -- If not recursive, turn off inheritance for children of -- this item - if is_recursive = 'f' then + if p_is_recursive = ''f'' then update acs_objects set - security_inherit_p = 'f' + security_inherit_p = ''f'' where object_id in ( select item_id from cr_items - where parent_id = update_permissions.item_id + where parent_id = p_item_id ) and - security_inherit_p = 't'; + security_inherit_p = ''t''; end if; -- Get permissions assigned to the parent(s), copy them into child - declare - cursor c_perm_cur is + for c_perm_cur in select p.grantee_id, p.privilege from acs_permissions p, - (select object_id from acs_objects - connect by prior context_id = object_id - and security_inherit_p = 't' - start with object_id = v_context_id) o + (select o2.object_id + from (select * + from acs_objects + where object_id = v_context_id) o1, + acs_objects o2, + (select case when max(ob2.tree_sortkey) is null + then ''/'' + else max(ob2.tree_sortkey) + end as tree_sortkey + from (select * + from acs_objects + where object_id = v_context_id) ob1, + acs_objects ob2 + where ob2.tree_sortkey <= ob1.tree_sortkey + and ob1.tree_sortkey like (ob2.tree_sortkey || ''%'') + and ob2.inherit_p = ''f'') o3 + where o2.tree_sortkey <= o1.tree_sortkey + and o1.tree_sortkey like (o2.tree_sortkey || ''%'') + and o2.tree_sortkey > o3.tree_sortkey + order by o2.tree_sortkey desc) o where - p.object_id = o.object_id; - begin - open c_perm_cur; - loop - fetch c_perm_cur into v_grantee_id, v_privilege; - exit when c_perm_cur%NOTFOUND; - if acs_permission.permission_p ( - item_id, v_grantee_id, v_privilege - ) = 'f' + p.object_id = o.object_id + LOOP + v_grantee_id := c_perm_cur.grantee_id; + v_privilege := c_perm_cur.privilege; + if acs_permission__permission_p ( + p_item_id, v_grantee_id, v_privilege + ) = ''f'' then - acs_permission.grant_permission ( - item_id, v_grantee_id, v_privilege + PERFORM acs_permission__grant_permission ( + p_item_id, v_grantee_id, v_privilege ); end if; - end loop; - close c_perm_cur; - end; - - end update_permissions; + end loop; + + return 0; +end;' language 'plpgsql'; - function has_grant_authority ( - item_id in cr_items.item_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) return varchar2 - is - begin + +-- function has_grant_authority +create function cms_permission__has_grant_authority (integer,integer,varchar) +returns boolean as ' +declare + p_item_id alias for $1; + p_holder_id alias for $2; + p_privilege alias for $3; +begin -- Can donate permission only if you already have it and you have cm_perm, -- OR you have cm_perm_admin - if acs_permission.permission_p (item_id, holder_id, 'cm_perm_admin')= 't' + if acs_permission__permission_p (p_item_id, p_holder_id, ''cm_perm_admin'') = ''t'' or ( - acs_permission.permission_p (item_id, holder_id, 'cm_perm') = 't' and - acs_permission.permission_p (item_id, holder_id, privilege) = 't' + acs_permission__permission_p (p_item_id, p_holder_id, ''cm_perm'') = ''t'' and + acs_permission__permission_p (p_item_id, p_holder_id, privilege) = ''t'' ) then - return 't'; + return ''t''; else - return 'f'; + return ''f''; end if; - end has_grant_authority; + +end;' language 'plpgsql'; - function has_revoke_authority ( - item_id in cr_items.item_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE, - revokee_id in parties.party_id%TYPE - ) return varchar2 - is - cursor c_perm_cur is - select - 't' + +-- function has_revoke_authority +create function cms_permission__has_revoke_authority (integer,integer,varchar,integer) +returns boolean as ' +declare + p_item_id alias for $1; + p_holder_id alias for $2; + p_privilege alias for $3; + p_revokee_id alias for $4; +begin + return + count(h2.*) > 0 from - acs_privilege_hierarchy + acs_privilege_hierarchy_index h1, + acs_privilege_hierarchy_index h2 where - acs_permission.permission_p( - has_revoke_authority.item_id, - has_revoke_authority.holder_id, - child_privilege - ) = 't' + acs_permission__permission_p( + p_item_id, + p_holder_id, + h2.child_privilege + ) = ''t'' and - acs_permission.permission_p( - has_revoke_authority.item_id, - has_revoke_authority.revokee_id, - privilege - ) = 'f' - connect by - prior privilege = child_privilege - start with - child_privilege = 'cm_perm'; + acs_permission__permission_p( + p_item_id, + p_revokee_id, + h2.privilege + ) = ''f'' + and h1.child_privilege = ''cm_perm'' + and h1.tree_sortkey like (h2.tree_sortkey || ''%'') + and h2.tree_sortkey < h1.tree_sortkey + limit 1; + +end;' language 'plpgsql'; - v_ret varchar2(1); - begin - open c_perm_cur; - fetch c_perm_cur into v_ret; - if c_perm_cur%NOTFOUND then - v_ret := 'f'; - end if; - return v_ret; - end has_revoke_authority; - procedure grant_permission ( - item_id in cr_items.item_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE, - recepient_id in parties.party_id%TYPE, - is_recursive in varchar2 default 'f' - ) - is - cursor c_item_cur is +-- procedure grant_permission +-- FIXME: need to fix problem with defined types + +create function cms_permission__grant_permission (integer,integer,varchar,integer,varchar) +returns integer as ' +declare + p_item_id alias for $1; + p_holder_id alias for $2; + p_privilege alias for $3; + p_recepient_id alias for $4; + p_is_recursive alias for $5; + v_item_id cr_items.item_id%TYPE; + v_items item_array_type; + v_idx integer; + v_count integer; + v_perms perm_array_type; + v_perm acs_privileges.privilege%TYPE; + v_perm_idx integer; + v_perm_count integer; + c_item_cur record; + c_perm_cur record; +begin + + PERFORM cms_permissions__update_permissions(p_item_id, p_is_recursive); + + -- Select all child items + v_count := 0; + + for c_item_cur in select item_id from (select item_id from cr_items - connect by parent_id = prior item_id - start with item_id = grant_permission.item_id) i + where tree_sortkey like (select tree_sortkey || ''%'' + from cr_items + where item_id = p_item_id) + order by tree_sortkey) i where - has_grant_authority ( - i.item_id, grant_permission.holder_id, grant_permission.privilege - ) = 't' + cms_permission__has_grant_authority ( + i.item_id, p_holder_id, p_privilege + ) = ''t'' and - acs_permission.permission_p ( - i.item_id, grant_permission.recepient_id, grant_permission.privilege - ) = 'f'; - - v_item_id cr_items.item_id%TYPE; - - type item_array_type is table of cr_items.item_id%TYPE - index by binary_integer; - v_items item_array_type; - v_idx integer; - v_count integer; - - cursor c_perm_cur is - select descendant from acs_privilege_descendant_map - where privilege = grant_permission.privilege - and descendant <> grant_permission.privilege; - - type perm_array_type is table of acs_privileges.privilege%TYPE - index by binary_integer; - - v_perms perm_array_type; - v_perm acs_privileges.privilege%TYPE; - v_perm_idx integer; - v_perm_count integer; - begin - - update_permissions(item_id, is_recursive); - - -- Select all child items - open c_item_cur; - v_count := 0; - loop - fetch c_item_cur into v_item_id; - exit when c_item_cur%NOTFOUND; + acs_permission__permission_p ( + i.item_id, p_recepient_id, p_privilege + ) = ''f'' + LOOP + v_item_id := c_item_cur.item_id; v_count := v_count + 1; v_items(v_count) := v_item_id; - exit when is_recursive = 'f'; + exit when p_is_recursive = ''f''; end loop; - close c_item_cur; if v_count < 1 then - return; + return null; end if; -- Grant parent permission for v_idx in 1..v_count loop - acs_permission.grant_permission ( - v_items(v_idx), recepient_id, privilege + PERFORM acs_permission__grant_permission ( + v_items(v_idx), p_recepient_id, p_privilege ); end loop; -- Select the child permissions v_perm_count := 0; - open c_perm_cur; + for c_perm_cur in + select descendant from acs_privilege_descendant_map + where privilege = p_privilege + and descendant <> p_privilege loop - fetch c_perm_cur into v_perm; - exit when c_perm_cur%NOTFOUND; + v_perm := c_perm_cur.descendant; v_perm_count := v_perm_count + 1; v_perms(v_perm_count) := v_perm; end loop; - close c_perm_cur; -- Revoke child permissions for v_idx in 1..v_count loop for v_perm_idx in 1..v_perm_count loop - acs_permission.revoke_permission ( - v_items(v_idx), recepient_id, v_perms(v_perm_idx) + PERFORM acs_permission__revoke_permission ( + v_items(v_idx), p_recepient_id, v_perms(v_perm_idx) ); end loop; end loop; - end grant_permission; - + return 0; +end;' language 'plpgsql'; - procedure revoke_permission ( - item_id in cr_items.item_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE, - revokee_id in parties.party_id%TYPE, - is_recursive in varchar2 default 'f' - ) - is - - cursor c_item_cur is - select item_id from cr_items - connect by parent_id = prior item_id - start with item_id = revoke_permission.item_id - where - has_revoke_authority ( - item_id, - cms_permission.revoke_permission.holder_id, - cms_permission.revoke_permission.privilege, - cms_permission.revoke_permission.revokee_id - ) = 't' - and - acs_permission.permission_p ( - item_id, - cms_permission.revoke_permission.revokee_id, - cms_permission.revoke_permission.privilege - ) = 't'; - cursor c_perm_cur is - select +-- procedure revoke_permission +create function cms_permission__revoke_permission (integer,integer,varchar,integer,varchar) +returns integer as ' +declare + p_item_id alias for $1; + p_holder_id alias for $2; + p_privilege alias for $3; + p_revokee_id alias for $4; + p_is_recursive alias for $5; + 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_perm acs_privileges.privilege%TYPE; + v_perm_idx integer; + v_perm_count integer; + c_perm_cur record; + c_item_cur record; +begin + + PERFORM update_permissions(p_item_id, p_is_recursive); + + -- Select the child permissions + v_perm_count := 0; + for c_perm_cur in + select child_privilege from acs_privilege_hierarchy where - privilege = revoke_permission.privilege + privilege = p_privilege and - child_privilege <> revoke_permission.privilege; - - type item_array_type is table of cr_items.item_id%TYPE - index by binary_integer; - v_items item_array_type; - v_item_id cr_items.item_id%TYPE; - v_idx integer; - v_count integer; - - type perm_array_type is table of acs_privileges.privilege%TYPE - index by binary_integer; - - v_perms perm_array_type; - v_perm acs_privileges.privilege%TYPE; - v_perm_idx integer; - v_perm_count integer; - begin - - update_permissions(item_id, is_recursive); - - -- Select the child permissions - v_perm_count := 0; - open c_perm_cur; - loop - fetch c_perm_cur into v_perm; - exit when c_perm_cur%NOTFOUND; + child_privilege <> p_privilege + LOOP + v_perm := c_perm_cur.child_privilege; v_perm_count := v_perm_count + 1; v_perms(v_perm_count) := v_perm; - end loop; - close c_perm_cur; + end LOOP; -- Select child items v_count := 0; - open c_item_cur; - loop - fetch c_item_cur into v_item_id; - exit when c_item_cur%NOTFOUND; + for c_item_cur in + select item_id from cr_items + where tree_sortkey like (select tree_sortkey || ''%'' + from cr_items + where item_id = p_item_id) + where + cms_permission__has_revoke_authority ( + item_id, + p_holder_id, + p_privilege, + p_revokee_id + ) = ''t'' + and + acs_permission__permission_p ( + item_id, + p_revokee_id, + p_privilege + ) = ''t'' + LOOP + v_item_id := c_item_cur.item_id; v_count := v_count + 1; v_items(v_count) := v_item_id; - exit when is_recursive = 'f'; + exit when p_is_recursive = ''f''; end loop; - close c_item_cur; if v_count < 1 then return; @@ -505,46 +507,49 @@ -- Grant child permissions for v_idx in 1..v_count loop for v_perm_idx in 1..v_perm_count loop - acs_permission.grant_permission ( - v_items(v_idx), revokee_id, v_perms(v_perm_idx) + PERFORM acs_permission__grant_permission ( + v_items(v_idx), p_revokee_id, v_perms(v_perm_idx) ); end loop; end loop; -- Revoke the parent permission for v_idx in 1..v_count loop - acs_permission.revoke_permission ( + acs_permission__revoke_permission ( v_items(v_idx), - revoke_permission.revokee_id, - revoke_permission.privilege + p_revokee_id, + p_privilege ); end loop; - end revoke_permission; + return 0; +end;' language 'plpgsql'; - function permission_p ( - item_id in cr_items.item_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) return varchar2 - is - v_workflow_count integer; - v_task_count integer; - begin + +-- function permission_p +create function cms_permission__permission_p (integer,integer,varchar) +returns varchar 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 ( - item_id, holder_id, privilege - ) = 'f' + if acs_permission__permission_p ( + p_item_id, p_holder_id, p_privilege + ) = ''f'' then - return 'f'; + return ''f''; end if; -- Special case for workflow - if privilege = 'cm_relate' or - privilege = 'cm_write' or - privilege = 'cm_new' + 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 @@ -554,26 +559,26 @@ from wf_cases where - object_id = permission_p.item_id; + object_id = p_item_id; -- If there are multiple workflows / no workflows, do nothing -- special if v_workflow_count <> 1 then - return 't'; + 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 ( - item_id, holder_id, 'cm_item_workflow' - ) = 't' + if acs_permission__permission_p ( + p_item_id, p_holder_id, ''cm_item_workflow'' + ) = ''t'' then - return 't'; + return ''t''; end if; -- Check if the user holds the current task if v_workflow_count = 0 then - return 'f'; + return ''f''; end if; select @@ -583,270 +588,292 @@ where t.case_id = c.case_id and - c.workflow_key = 'publishing_wf' + c.workflow_key = ''publishing_wf'' and - c.state = 'active' + c.state = ''active'' and - c.object_id = permission_p.item_id + c.object_id = p_item_id and - ( t.state = 'enabled' + ( t.state = ''enabled'' or - ( t.state = 'started' and t.holding_user = permission_p.holder_id )) + ( t.state = ''started'' and t.holding_user = p_holder_id )) and - t.user_id = permission_p.holder_id; + t.user_id = p_holder_id; -- is the user assigned a current task on this item if v_task_count = 0 then - return 'f'; + return ''f''; end if; end if; - return 't'; - - end permission_p; + return ''t''; + +end;' language 'plpgsql'; - -- Determine if the CMS admin exists - function cm_admin_exists - return varchar2 - is - v_exists varchar2(1); - begin +create function cms_permission__cm_admin_exists() returns boolean as ' +declare + v_exists boolean; +begin select 't' into v_exists from dual where exists ( select 1 from acs_permissions where privilege in ('cm_admin', 'cm_root') ); - return v_exists; + if NOT FOUND then + return ''f''; + else + return ''t''; + end if; - exception when no_data_found then - return 'f'; - end cm_admin_exists; +end;' language 'plpgsql'; -end cms_permission; -/ -show errors +-- show errors + -- A trigger to automatically grant item creators the cm_write and cm_perm -- permissions -create or replace trigger cr_items_permission_tr -after insert on cr_items for each row +create function cr_items_permission_tr () returns opaque as ' declare v_user_id parties.party_id%TYPE; begin select creation_user into v_user_id from acs_objects - where object_id = :new.item_id; + where object_id = new.item_id; + -- FIXME: check to see if this is correct. + + if NOT FOUND then + return null; + end if; + if v_user_id is not null then - if acs_permission.permission_p ( - :new.item_id, v_user_id, 'cm_write' - ) = 'f' + if acs_permission__permission_p ( + new.item_id, v_user_id, ''cm_write'' + ) = ''f'' then - acs_permission.grant_permission ( - :new.item_id, v_user_id, 'cm_write' + acs_permission__grant_permission ( + new.item_id, v_user_id, ''cm_write'' ); end if; - if acs_permission.permission_p ( - :new.item_id, v_user_id, 'cm_perm' - ) = 'f' + if acs_permission__permission_p ( + new.item_id, v_user_id, ''cm_perm'' + ) = ''f'' then - acs_permission.grant_permission ( - :new.item_id, v_user_id, 'cm_perm' + acs_permission__grant_permission ( + new.item_id, v_user_id, ''cm_perm'' ); end if; end if; -exception when no_data_found then null; +-- exception when no_data_found then null; -end cr_items_permission_tr; -/ -show errors + return new; +end;' language 'plpgsql'; + +create trigger cr_items_permission_tr after insert on cr_items +for each row execute procedure cr_items_permission_tr (); + +-- show errors -- A simple wrapper for acs-content-repository procs -create or replace package content_permission -is +-- create or replace package content_permission +-- is +-- +-- procedure inherit_permissions ( +-- parent_object_id in acs_objects.object_id%TYPE, +-- child_object_id in acs_objects.object_id%TYPE, +-- child_creator_id in parties.party_id%TYPE default null +-- ); +-- +-- function has_grant_authority ( +-- object_id in acs_objects.object_id%TYPE, +-- holder_id in parties.party_id%TYPE, +-- privilege in acs_privileges.privilege%TYPE +-- ) return varchar2; +-- +-- procedure grant_permission_h ( +-- object_id in acs_objects.object_id%TYPE, +-- grantee_id in parties.party_id%TYPE, +-- privilege in acs_privileges.privilege%TYPE +-- ); +-- +-- procedure grant_permission ( +-- object_id in acs_objects.object_id%TYPE, +-- holder_id in parties.party_id%TYPE, +-- privilege in acs_privileges.privilege%TYPE, +-- recepient_id in parties.party_id%TYPE, +-- is_recursive in varchar2 default 'f', +-- object_type in acs_objects.object_type%TYPE default 'content_item' +-- ); +-- +-- function has_revoke_authority ( +-- object_id in acs_objects.object_id%TYPE, +-- holder_id in parties.party_id%TYPE, +-- privilege in acs_privileges.privilege%TYPE, +-- revokee_id in parties.party_id%TYPE +-- ) return varchar2; +-- +-- procedure revoke_permission_h ( +-- object_id in acs_objects.object_id%TYPE, +-- revokee_id in parties.party_id%TYPE, +-- privilege in acs_privileges.privilege%TYPE +-- ); +-- +-- procedure revoke_permission ( +-- object_id in acs_objects.object_id%TYPE, +-- holder_id in parties.party_id%TYPE, +-- privilege in acs_privileges.privilege%TYPE, +-- revokee_id in parties.party_id%TYPE, +-- is_recursive in varchar2 default 'f', +-- object_type in acs_objects.object_type%TYPE default 'content_item' +-- ); +-- +-- function permission_p ( +-- object_id in acs_objects.object_id%TYPE, +-- holder_id in parties.party_id%TYPE, +-- privilege in acs_privileges.privilege%TYPE +-- ) return varchar2; +-- +-- function cm_admin_exists +-- return varchar2; +-- +-- end content_permission; - procedure inherit_permissions ( - parent_object_id in acs_objects.object_id%TYPE, - child_object_id in acs_objects.object_id%TYPE, - child_creator_id in parties.party_id%TYPE default null - ); +-- show errors - function has_grant_authority ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) return varchar2; - - procedure grant_permission_h ( - object_id in acs_objects.object_id%TYPE, - grantee_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ); - procedure grant_permission ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE, - recepient_id in parties.party_id%TYPE, - is_recursive in varchar2 default 'f', - object_type in acs_objects.object_type%TYPE default 'content_item' - ); +-- create or replace package body content_permission +-- procedure inherit_permissions +create function content_permission__inherit_permissions (integer,integer,integer) +returns integer as ' +declare + p_parent_object_id alias for $1; + p_child_object_id alias for $2; + p_child_creator_id alias for $3; +begin + PERFORM cms_permission__update_permissions(p_child_object_id); + return 0; +end;' language 'plpgsql'; - function has_revoke_authority ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE, - revokee_id in parties.party_id%TYPE - ) return varchar2; - procedure revoke_permission_h ( - object_id in acs_objects.object_id%TYPE, - revokee_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ); +-- function has_grant_authority +create function content_permission__has_grant_authority (integer,integer,varchar) +returns boolean as ' +declare + p_object_id alias for $1; + p_holder_id alias for $2; + p_privilege alias for $3; +begin + return cms_permission__has_grant_authority ( + p_object_id, p_holder_id, p_privilege + ); + +end;' language 'plpgsql'; - procedure revoke_permission ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE, - revokee_id in parties.party_id%TYPE, - is_recursive in varchar2 default 'f', - object_type in acs_objects.object_type%TYPE default 'content_item' - ); - function permission_p ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) return varchar2; +-- procedure grant_permission_h +create function content_permission__grant_permission_h (integer,integer,varchar) +returns integer as ' +declare + p_object_id alias for $1; + p_grantee_id alias for $2; + p_privilege alias for $3; +begin + return 0; +end;' language 'plpgsql'; - function cm_admin_exists - return varchar2; -end content_permission; -/ -show errors +-- procedure grant_permission +create function content_permission__grant_permission (integer,integer,varchar,integer,varchar,varchar) +returns integer as ' +declare + p_object_id alias for $1; + p_holder_id alias for $2; + p_privilege alias for $3; + p_recepient_id alias for $4; + p_is_recursive alias for $5; + p_object_type alias for $6; +begin + PERFORM cms_permission__grant_permission ( + p_object_id, p_holder_id, p_privilege, p_recepient_id, p_is_recursive + ); + return 0; +end;' language 'plpgsql'; -create or replace package body content_permission -is - procedure inherit_permissions ( - parent_object_id in acs_objects.object_id%TYPE, - child_object_id in acs_objects.object_id%TYPE, - child_creator_id in parties.party_id%TYPE default null - ) - is - begin - cms_permission.update_permissions(child_object_id); - end inherit_permissions; - - function has_grant_authority ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) return varchar2 - is - begin - return cms_permission.has_grant_authority ( - object_id, holder_id, privilege +-- function has_revoke_authority +create function content_permission__has_revoke_authority (integer,integer,varchar,integer) +returns varchar as ' +declare + p_object_id alias for $1; + p_holder_id alias for $2; + p_privilege alias for $3; + p_revokee_id alias for $4; +begin + return cms_permission__has_revoke_authority ( + p_object_id, p_holder_id, p_privilege, p_revokee_id ); - end has_grant_authority; - procedure grant_permission_h ( - object_id in acs_objects.object_id%TYPE, - grantee_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) - is - begin - return; - end; +end;' language 'plpgsql'; - procedure grant_permission ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE, - recepient_id in parties.party_id%TYPE, - is_recursive in varchar2 default 'f', - object_type in acs_objects.object_type%TYPE default 'content_item' - ) - is - begin - cms_permission.grant_permission ( - object_id, holder_id, privilege, recepient_id, is_recursive - ); - end grant_permission; - function has_revoke_authority ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE, - revokee_id in parties.party_id%TYPE - ) return varchar2 - is - begin - return cms_permission.has_revoke_authority ( - object_id, holder_id, privilege, revokee_id - ); - end has_revoke_authority; +-- procedure revoke_permission_h +create function content_permission__revoke_permission_h (integer,integer,varchar) +returns integer as ' +declare + p_object_id alias for $1; + p_revokee_id alias for $2; + p_privilege alias for $3; +begin + return 0; +end;' language 'plpgsql'; - procedure revoke_permission_h ( - object_id in acs_objects.object_id%TYPE, - revokee_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) - is - begin - return; - end revoke_permission_h; - procedure revoke_permission ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE, - revokee_id in parties.party_id%TYPE, - is_recursive in varchar2 default 'f', - object_type in acs_objects.object_type%TYPE default 'content_item' - ) - is - begin - cms_permission.revoke_permission ( - object_id, holder_id, privilege, revokee_id, is_recursive +-- procedure revoke_permission +create function content_permission__revoke_permission (integer,integer,varchar,integer,varchar,varchar) +returns integer as ' +declare + p_object_id alias for $1; + p_holder_id alias for $2; + p_privilege alias for $3; + p_revokee_id alias for $4; + p_is_recursive alias for $5; + p_object_type alias for $6; +begin + PERFORM cms_permission__revoke_permission ( + p_object_id, p_holder_id, p_privilege, p_revokee_id, p_is_recursive ); - end revoke_permission; - function permission_p ( - object_id in acs_objects.object_id%TYPE, - holder_id in parties.party_id%TYPE, - privilege in acs_privileges.privilege%TYPE - ) return varchar2 - is - begin - return cms_permission.permission_p ( - object_id, holder_id, privilege + return 0; +end;' language 'plpgsql'; + + +-- function permission_p +create function content_permission__permission_p (integer,integer,varchar) +returns varchar as ' +declare + p_object_id alias for $1; + p_holder_id alias for $2; + p_privilege alias for $3; +begin + return cms_permission__permission_p ( + p_object_id, p_holder_id, p_privilege ); - end permission_p; + +end;' language 'plpgsql'; - function cm_admin_exists - return varchar2 - is - begin - return cms_permission.cm_admin_exists; - end cm_admin_exists; -end content_permission; -/ -show errors + +-- show errors 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 -r1.1 -r1.2 --- openacs-4/packages/cms/sql/postgresql/cms-publishing-wf.sql 19 May 2001 01:20:10 -0000 1.1 +++ openacs-4/packages/cms/sql/postgresql/cms-publishing-wf.sql 21 May 2001 23:13:59 -0000 1.2 @@ -6,70 +6,77 @@ references wf_cases ); +create function inline_0 () +returns integer as ' declare v_workflow_key varchar(100); begin - v_workflow_key := workflow.create_workflow ( - workflow_key => 'publishing', - pretty_name => 'Simple Publishing Workflow', - pretty_plural => 'Simple Publishing Workflows', - description => 'A simple linear workflow for authoring, - editing and scheduling content items.', - table_name => 'cr_workflows'); + v_workflow_key := workflow__create_workflow( + ''publishing'', + ''Simple Publishing Workflow'', + ''Simple Publishing Workflows'', + ''A simple linear workflow for authoring, + editing and scheduling content items.'', + ''cr_workflows'', + ''case_id'' + ); -end; -/ -show errors + return 0; +end;' language 'plpgsql'; -create or replace package publishing_wf as +select inline_0 (); - -- simply check the 'next_place' attribute and return true if - -- it matches the submitted place_key +drop function inline_0 (); - function is_next ( - case_id in number, - workflow_key in varchar, - transition_key in varchar, - place_key in varchar, - direction in varchar, - custom_arg in varchar - ) return char; + +-- show errors -end publishing_wf; -/ -show errors +-- create or replace package publishing_wf as +-- +-- -- simply check the 'next_place' attribute and return true if +-- -- it matches the submitted place_key +-- +-- function is_next ( +-- case_id in number, +-- workflow_key in varchar, +-- transition_key in varchar, +-- place_key in varchar, +-- direction in varchar, +-- custom_arg in varchar +-- ) return char; +-- +-- end publishing_wf; -create or replace package body publishing_wf as +-- show errors - function is_next ( - case_id in number, - workflow_key in varchar, - transition_key in varchar, - place_key in varchar, - direction in varchar, - custom_arg in varchar - ) return char is +-- create or replace package body publishing_wf as +-- function is_next +create function publishing_wf__is_next (integer,varchar,varchar,varchar,varchar,varchar) +returns char as ' +declare + p_case_id alias for $1; + p_workflow_key alias for $2; + p_transition_key alias for $3; + p_place_key alias for $4; + p_direction alias for $5; + p_custom_arg alias for $6; + v_next_place varchar(100); + v_result boolean; +begin - v_next_place varchar(100); - v_result char(1) := 'f'; + v_next_place := workflow_case__get_attribute_value(case_id,''next_place''); - begin - - v_next_place := workflow_case.get_attribute_value(case_id, 'next_place'); - - if v_next_place = place_key then - v_result := 't'; + if v_next_place = p_place_key then + v_result := ''t''; end if; return v_result; + +end;' language 'plpgsql'; - end is_next; - -end publishing_wf; -/ -show errors +-- show errors insert into wf_places ( place_key, workflow_key, place_name, sort_order @@ -95,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. @@ -119,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. @@ -210,34 +217,49 @@ ); +create function inline_1 () +returns integer as ' declare v_attribute_id acs_attributes.attribute_id%TYPE; begin - v_attribute_id := workflow.create_attribute( - workflow_key => 'publishing_wf', - attribute_name => 'next_place', - datatype => 'string', - wf_datatype => 'none', - pretty_name => 'Next Place', - default_value => 'start' + v_attribute_id := workflow__create_attribute( + ''publishing_wf'', + ''next_place'', + ''string'', + ''Next Place'', + null, + null, + null, + ''start'', + 1, + 1, + null, + ''generic'', + ''none'' ); insert into wf_transition_attribute_map (workflow_key, transition_key, attribute_id, sort_order) values - ('publishing_wf', 'authoring', v_attribute_id, 1); + (''publishing_wf'', ''authoring'', v_attribute_id, 1); insert into wf_transition_attribute_map (workflow_key, transition_key, attribute_id, sort_order) values - ('publishing_wf', 'editing', v_attribute_id, 1); + (''publishing_wf'', ''editing'', v_attribute_id, 1); insert into wf_transition_attribute_map (workflow_key, transition_key, attribute_id, sort_order) values - ('publishing_wf', 'approval', v_attribute_id, 1); + (''publishing_wf'', ''approval'', v_attribute_id, 1); -end; -/ -show errors; + return 0; +end;' language 'plpgsql'; +select inline_1 (); + +drop function inline_1 (); + + +-- show errors + 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 -r1.1 -r1.2 --- openacs-4/packages/cms/sql/postgresql/cms-update.sql 19 May 2001 01:20:10 -0000 1.1 +++ openacs-4/packages/cms/sql/postgresql/cms-update.sql 21 May 2001 23:13:59 -0000 1.2 @@ -1,60 +1,85 @@ -- Modify permissions to include the cm_relate permission +create function inline_0 () +returns integer as ' declare v_exists integer; begin select count(*) into v_exists from acs_privileges - where privilege = 'cm_admin'; + where privilege = ''cm_admin''; if v_exists > 0 then select count(*) into v_exists from acs_privileges - where privilege = 'cm_relate'; + where privilege = ''cm_relate''; if v_exists < 1 then - acs_privilege.create_privilege('cm_relate', 'Relate Items', 'Relate Items'); - acs_privilege.add_child('cm_admin', 'cm_relate'); update acs_privilege_hierarchy - set privilege = 'cm_relate' - where privilege = 'cm_admin' - and child_privilege = 'cm_write'; + set privilege = ''cm_relate'' + where privilege = ''cm_admin'' + and child_privilege = ''cm_write''; end if; end if; -end; -/ -show errors + return 0; +end;' language 'plpgsql'; + +select inline_0 (); + +drop function inline_0 (); + + +-- show errors + -- This parent_id column was not included in the cr_keywords table -- for RC 0. Ensure this column is there. +create function inline_1 () +returns integer as ' begin - if not column_exists('cr_keywords', 'parent_id') then + if not column_exists(''cr_keywords'', ''parent_id'') then - dbms_output.put_line('Adding PARENT_ID column to CR_KEYWORDS' || - ' and updating the parent id from the context id'); + raise notice ''Adding PARENT_ID column to CR_KEYWORDS and updating the parent id from the context id''; - execute immediate 'alter table cr_keywords add + execute ''alter table cr_keywords add parent_id integer constraint cr_keywords_hier - references cr_keywords'; + references cr_keywords''; - execute immediate 'update cr_keywords set parent_id = ( + execute ''update cr_keywords set parent_id = ( select context_id from acs_objects - where object_id = keyword_id)'; + where object_id = keyword_id)''; end if; -end; -/ -show errors + return 0; +end;' language 'plpgsql'; +select inline_1 (); + +drop function inline_1 (); + + +-- show errors + -- Drop the broken trigger, if any +create function inline_2 () +returns integer as ' begin - execute immediate 'drop trigger cr_item_permission_tr'; -exception when others then null; -end; -/ -show errors + -- FIXME: DCW - can't locate where this trigger is created. Need a table + -- name in order to drop it in pg. -exec content_type.register_mime_type ('content_template', 'text/html'); -exec content_type.register_mime_type ('content_template', 'text/plain'); + -- execute ''drop trigger cr_item_permission_tr''; + + return 0; +end;' language 'plpgsql'; + +select inline_2 (); + +drop function inline_2 (); + + +-- show errors + +select content_type__register_mime_type ('content_template', 'text/html'); +select content_type__register_mime_type ('content_template', 'text/plain'); 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 -r1.1 -r1.2 --- openacs-4/packages/cms/sql/postgresql/cms-widgets.sql 19 May 2001 01:20:10 -0000 1.1 +++ openacs-4/packages/cms/sql/postgresql/cms-widgets.sql 21 May 2001 23:13:59 -0000 1.2 @@ -3,157 +3,177 @@ /* insert form widgets and params */ +create function inline_0 () +returns integer as ' begin -- insert the standard form widgets - insert into cm_form_widgets (widget) values ('text'); - insert into cm_form_widgets (widget) values ('textarea'); - insert into cm_form_widgets (widget) values ('radio'); - insert into cm_form_widgets (widget) values ('checkbox'); - insert into cm_form_widgets (widget) values ('select'); - insert into cm_form_widgets (widget) values ('multiselect'); - insert into cm_form_widgets (widget) values ('date'); + insert into cm_form_widgets (widget) values (''text''); + insert into cm_form_widgets (widget) values (''textarea''); + insert into cm_form_widgets (widget) values (''radio''); + insert into cm_form_widgets (widget) values (''checkbox''); + insert into cm_form_widgets (widget) values (''select''); + insert into cm_form_widgets (widget) values (''multiselect''); + insert into cm_form_widgets (widget) values (''date''); -- insert the standard form widget params and ATS form element params insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (10, 'text', 'size', 'f', 't', '30'); + (10, ''text'', ''size'', ''f'', ''t'', ''30''); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (20, 'textarea', 'rows', 'f', 't', '6'); + (20, ''textarea'', ''rows'', ''f'', ''t'', ''6''); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (30, 'textarea', 'cols', 'f', 't', '60'); + (30, ''textarea'', ''cols'', ''f'', ''t'', ''60''); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (31, 'textarea', 'wrap', 'f', 't', 'physical'); + (31, ''textarea'', ''wrap'', ''f'', ''t'', ''physical''); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (40, 'radio', 'options', 't', 'f', null); + (40, ''radio'', ''options'', ''t'', ''f'', null); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (50, 'checkbox', 'options', 't', 'f', null); + (50, ''checkbox'', ''options'', ''t'', ''f'', null); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (60, 'select', 'options', 't', 'f', '{ -- {} }'); + (60, ''select'', ''options'', ''t'', ''f'', ''{ -- {} }''); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (61, 'select', 'values', 'f', 'f', '{}'); + (61, ''select'', ''values'', ''f'', ''f'', ''{}''); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (70, 'select', 'size', 'f', 't', null); + (70, ''select'', ''size'', ''f'', ''t'', null); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (80, 'multiselect', 'options', 't', 'f', null); + (80, ''multiselect'', ''options'', ''t'', ''f'', null); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (90, 'multiselect', 'size', 'f', 't', null); + (90, ''multiselect'', ''size'', ''f'', ''t'', null); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (100, 'date', 'format', 'f', 'f', 'DD/MONTH/YYYY'); + (100, ''date'', ''format'', ''f'', ''f'', ''DD/MONTH/YYYY''); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (110, 'date', 'year_interval', 'f', 'f', '2000 2005 1'); + (110, ''date'', ''year_interval'', ''f'', ''f'', ''2000 2005 1''); -end; -/ -show errors + return 0; +end;' language 'plpgsql'; +select inline_0 (); + +drop function inline_0 (); + + +-- show errors + +create function inline_1 () +returns integer as ' begin /* search widget and params */ - dbms_output.put_line('Inserting search widget metadata...'); + raise notice ''Inserting search widget metadata...''; - insert into cm_form_widgets (widget) values ('search'); + insert into cm_form_widgets (widget) values (''search''); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (120, 'search', 'search_query', 't', 'f', null); + (120, ''search'', ''search_query'', ''t'', ''f'', null); -end; -/ -show errors + return 0; +end;' language 'plpgsql'; +select inline_1 (); +drop function inline_1 (); +-- show errors + + + + +create function inline_2 () +returns integer as ' begin /* new widget params 11-31-00 */ - dbms_output.put_line('Inserting new widget metadata...'); + raise notice ''Inserting new widget metadata...''; insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (11, 'text', 'maxlength', 'f', 't', null); + (11, ''text'', ''maxlength'', ''f'', ''t'', null); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (12, 'text', 'validate', 'f', 'f', null); + (12, ''text'', ''validate'', ''f'', ''f'', null); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (32, 'textarea', 'validate', 'f', 'f', null); + (32, ''textarea'', ''validate'', ''f'', ''f'', null); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (41, 'radio', 'values', 'f', 'f', null); + (41, ''radio'', ''values'', ''f'', ''f'', null); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (51, 'checkbox', 'values', 'f', 'f', null); + (51, ''checkbox'', ''values'', ''f'', ''f'', null); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (91, 'multiselect', 'values', 'f', 'f', null); + (91, ''multiselect'', ''values'', ''f'', ''f'', null); insert into cm_form_widget_params (param_id, widget, param, is_required, is_html, default_value) values - (121, 'search', 'result_datatype', 'f', 'f', 'search'); + (121, ''search'', ''result_datatype'', ''f'', ''f'', ''search''); -end; -/ -show errors + return 0; +end;' language 'plpgsql'; +select inline_2 (); +drop function inline_2 (); +-- show errors @@ -162,134 +182,156 @@ -/* Register attribute widgets for content_revision and image */ + + + +* Register attribute widgets for content_revision and image */ + +create function inline_3 () +returns integer as ' begin -- register form widgetes for content revision attributes - cm_form_widget.register_attribute_widget( - content_type => 'content_revision', - attribute_name => 'title', - widget => 'text', - is_required => 't' + PERFORM cm_form_widget__register_attribute_widget( + ''content_revision'', + ''title'', + ''text'', + ''t'' ); - cm_form_widget.register_attribute_widget( - content_type => 'content_revision', - attribute_name => 'description', - widget => 'textarea' + PERFORM cm_form_widget__register_attribute_widget( + ''content_revision'', + ''description'', + ''textarea'', + ''f'' ); - cm_form_widget.set_attribute_param_value( - content_type => 'content_revision', - attribute_name => 'description', - param => 'cols', - param_type => 'onevalue', - param_source => 'literal', - value => 40 + PERFORM cm_form_widget__set_attribute_param_value( + ''content_revision'', + ''description'', + ''cols'', + 40, + ''onevalue'', + ''literal'' ); - cm_form_widget.register_attribute_widget( - content_type => 'content_revision', - attribute_name => 'mime_type', - widget => 'select', - is_required => 't' + PERFORM cm_form_widget__register_attribute_widget( + ''content_revision'', + ''mime_type'', + ''select'', + ''t'' ); - cm_form_widget.set_attribute_param_value( - content_type => 'content_revision', - attribute_name => 'mime_type', - param => 'options', - param_type => 'multilist', - param_source => 'query', - value => 'select - label, map.mime_type as value - from - cr_mime_types types, - cr_content_mime_type_map map - where - types.mime_type = map.mime_type - and - content_type = :content_type - order by - label' + PERFORM cm_form_widget__set_attribute_param_value( + ''content_revision'', + ''mime_type'', + ''options'', + ''select + label, map.mime_type as value + from + cr_mime_types types, + cr_content_mime_type_map map + where + types.mime_type = map.mime_type + and + content_type = :content_type + order by + label'', + ''multilist'', + ''query'' ); - cm_form_widget.set_attribute_param_value( - content_type => 'content_revision', - attribute_name => 'mime_type', - param => 'values', - param_type => 'onevalue', - param_source => 'query', - value => 'select - mime_type - from - cr_revisions - where - revision_id = content_item.get_latest_revision(:item_id)' + PERFORM cm_form_widget__set_attribute_param_value( + ''content_revision'', + ''mime_type'', + ''values'', + ''select + mime_type + from + cr_revisions + where + revision_id = content_item.get_latest_revision(:item_id)'', + ''onevalue'', + ''query'' ); -- register for widgets for image attributes - cm_form_widget.register_attribute_widget( - content_type => 'image', - attribute_name => 'width', - widget => 'text' + PERFORM cm_form_widget__register_attribute_widget( + ''image'', + ''width'', + ''text'', + ''f'' ); - cm_form_widget.register_attribute_widget( - content_type => 'image', - attribute_name => 'height', - widget => 'text' + PERFORM cm_form_widget__register_attribute_widget( + ''image'', + ''height'', + ''text'', + ''f'' ); - cm_form_widget.set_attribute_param_value( - content_type => 'image', - attribute_name => 'width', - param => 'size', - param_type => 'onevalue', - param_source => 'literal', - value => 5 + PERFORM cm_form_widget__set_attribute_param_value( + ''image'', + ''width'', + ''size'', + 5, + ''onevalue'', + ''literal'' ); - cm_form_widget.set_attribute_param_value( - content_type => 'image', - attribute_name => 'height', - param => 'size', - param_type => 'onevalue', - param_source => 'literal', - value => 5 + PERFORM cm_form_widget__set_attribute_param_value( + ''image'', + ''height'', + ''size'', + 5, + ''onevalue'', + ''literal'' ); -end; -/ -show errors + return 0; +end;' language 'plpgsql'; +select inline_3 (); +drop function inline_3 (); + +-- show errors + + + +create function inline_4 () +returns integer as ' begin /* new widget params 11-31-00 */ - dbms_output.put_line('Inserting new widget attributes...'); - cm_form_widget.set_attribute_param_value( - content_type => 'content_revision', - attribute_name => 'title', - param => 'maxlength', - param_type => 'onevalue', - param_source => 'literal', - value => 1000 + PERFORM cm_form_widget__set_attribute_param_value( + ''content_revision'', + ''title'', + ''maxlength'', + 1000, + ''onevalue'', + ''literal'' ); - cm_form_widget.set_attribute_param_value ( - content_type => 'content_revision', - attribute_name => 'description', - param => 'validate', - param_type => 'onevalue', - param_source => 'literal', - value => 'description_4k_max { cm_widget::validate_description $value } { Description length cannot exceed 4000 bytes. }' + PERFORM cm_form_widget__set_attribute_param_value ( + ''content_revision'', + ''description'', + ''validate'', + ''description_4k_max { cm_widget::validate_description $value } { Description length cannot exceed 4000 bytes. }'', + ''onevalue'', + ''literal'' ); -end; -/ -show errors + return 0; +end;' language 'plpgsql'; + +select inline_4 (); + +drop function inline_4 (); + + +-- show errors 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 -r1.1 -r1.2 --- openacs-4/packages/cms/sql/postgresql/cms-workflow.sql 19 May 2001 01:20:10 -0000 1.1 +++ openacs-4/packages/cms/sql/postgresql/cms-workflow.sql 21 May 2001 23:13:59 -0000 1.2 @@ -6,352 +6,350 @@ -create or replace package content_workflow -as +-- create or replace package content_workflow +-- as +-- +-- function is_overdue ( +-- --/** Determines if the workflow task is overdue +-- -- @author Michael Pih +-- -- @param task_id The task id +-- -- @return 't' if the deadline > sysdate, 'f' otherwise +-- --*/ +-- v_task_id in wf_tasks.task_id%TYPE +-- ) return char; +-- +-- +-- function is_overdue ( +-- --/** Determines if the workflow transition (task) is overdue +-- -- @author Michael Pih +-- -- @param case_id The case id +-- -- @param transition_key The transition key +-- -- @return 't' if the deadline > sysdate, 'f' otherwise +-- --*/ +-- case_id in wf_cases.case_id%TYPE, +-- transition_key in wf_transitions.transition_key%TYPE +-- ) return char; +-- +-- +-- function get_holding_user_name( +-- --/** Gets the name of the user who is currently holding this task +-- -- @author Michael Pih +-- -- @param task_id The task id +-- -- @return name of the user who holds the task, otherwise NULL +-- --*/ +-- v_task_id in wf_tasks.task_id%TYPE +-- ) return varchar2; +-- +-- +-- function get_first_place +-- --/** Gets the first place in the workflow (determined by sort order) +-- -- @author Michael Pih +-- -- @return the first place in the workflow +-- --*/ +-- return wf_places.place_key%TYPE; +-- +-- +-- function get_this_place( +-- --/** Gets the current place in the workflow given the current transition +-- -- @author Michael Pih +-- -- @param transition_key The transition +-- -- @return the current place in the workflow +-- --*/ +-- transition_key in wf_transitions.transition_key%TYPE +-- ) return wf_places.place_key%TYPE; +-- +-- +-- function get_next_place( +-- --/** Gets the next place given a transition key (determined by sort order). +-- -- Throws an error if there is no next place. +-- -- @author Michael Pih +-- -- @param transition_key The transition +-- -- @return the next place in the workflow +-- --*/ +-- transition_key in wf_transitions.transition_key%TYPE +-- ) return wf_places.place_key%TYPE; +-- +-- +-- function get_previous_place( +-- --/** Gets the previous place given a transition key +-- -- (determined by sort order). +-- -- Throws an error if there is no previous place. +-- -- @author Michael Pih +-- -- @param transition_key The transition +-- -- @return the previous place in the workflow +-- --*/ +-- transition_key in wf_transitions.transition_key%TYPE +-- ) return wf_places.place_key%TYPE; +-- +-- +-- procedure checkout ( +-- --/** Checks out a task +-- -- @author Michael Pih +-- -- @param task_id The task_id +-- -- @param hold_timeout How long the user expects to hold this task +-- -- @param user_id The user checking out the task +-- -- @param ip_address The user's IP address (for auditing) +-- -- @param msg Comments concerning checkout +-- --*/ +-- task_id in wf_tasks.task_id%TYPE, +-- hold_timeout in wf_tasks.hold_timeout%TYPE default null, +-- user_id in acs_objects.creation_user%TYPE, +-- ip_address in acs_objects.creation_ip%TYPE, +-- msg in varchar +-- ); +-- +-- +-- procedure checkin ( +-- --/** Checks in a task that the user is holding. Throws an error +-- -- if the task is not checked out already or if the task is checked +-- -- out by another user. +-- -- @author Michael Pih +-- -- @param task_id The task_id +-- -- @param user_id The user checking in the task +-- -- @param ip_address The IP address of the user +-- -- @param msg Comment associated with checking the task in +-- --*/ +-- task_id in wf_tasks.task_id%TYPE, +-- user_id in acs_objects.creation_user%TYPE, +-- ip_address in acs_objects.creation_ip%TYPE, +-- msg in varchar +-- ); +-- +-- +-- procedure approve( +-- --/** Finish a task +-- -- @author Michael Pih +-- -- @param task_id The task_id +-- -- @param user_id The user finishing the task +-- -- @param ip_address The user's IP address (for auditing) +-- -- @param msg Comments concerning finishing the task +-- --*/ +-- task_id in wf_tasks.task_id%TYPE, +-- user_id in acs_objects.creation_user%TYPE, +-- ip_address in acs_objects.creation_ip%TYPE, +-- msg in varchar +-- ); +-- +-- +-- procedure reject( +-- --/** Finish a task +-- -- @author Michael Pih +-- -- @param task_id The task_id +-- -- @param user_id The user finishing the task +-- -- @param ip_address The user's IP address (for auditing) +-- -- @param transition_key The transition the user wants to fall back to +-- -- @param msg Comments concerning finishing the task +-- --*/ +-- task_id in wf_tasks.task_id%TYPE, +-- user_id in acs_objects.creation_user%TYPE, +-- ip_address in acs_objects.creation_ip%TYPE, +-- transition_key in wf_transitions.transition_key%TYPE, +-- msg in varchar +-- ); +-- +-- +-- procedure notify_of_checkout( +-- --/** Helper procedure. +-- -- Fires notifications after stealing the lock on a task +-- -- @author Michael Pih +-- -- @param task_id The task_id +-- -- @param holding_user_old The user finishing the task +-- -- @param holding_user_new The user's IP address (for auditing) +-- -- @param msg Comments concerning stealing the task +-- --*/ +-- task_id in wf_tasks.task_id%TYPE, +-- holding_user_old in wf_tasks.holding_user%TYPE, +-- holding_user_new in wf_tasks.holding_user%TYPE, +-- msg in varchar +-- ); +-- +-- +-- function can_reject( +-- --/** Returns 't' if a task is rejectable by the user. +-- -- Otherwise returns 'f' +-- -- @author Michael Pih +-- -- @param task_id The task_id +-- -- @param user_id The user_id +-- -- @return 't' if the task can be rejected, 'f' otherwise +-- --*/ +-- task_id in wf_tasks.task_id%TYPE, +-- user_id in wf_tasks.holding_user%TYPE +-- ) return char; +-- +-- +-- +-- function can_approve( +-- --/** Returns 't' if a task is approvable by the user. +-- -- Otherwise returns 'f' +-- -- @author Michael Pih +-- -- @param task_id The task_id +-- -- @param user_id The user_id +-- -- @return 't' if the task can be approved, 'f' otherwise +-- --*/ +-- task_id in wf_tasks.task_id%TYPE, +-- user_id in wf_tasks.holding_user%TYPE +-- ) return char; +-- +-- +-- function can_start( +-- --/** Returns 't' if a task can be checked out by the user. +-- -- Otherwise returns 'f' +-- -- @author Michael Pih +-- -- @param task_id The task_id +-- -- @param user_id The user_id +-- -- @return 't' if the task can be started, 'f' otherwise +-- --*/ +-- task_id in wf_tasks.task_id%TYPE, +-- user_id in wf_tasks.holding_user%TYPE +-- ) return char; +-- +-- +-- +-- function approve_string( +-- --/** If the task is approvable, returns 'Approve' or if it is the first +-- -- task, then 'Finish'. Otherwise returns null. +-- -- @author Michael Pih +-- -- @param task_id The task_id +-- -- @param user_id The user_id +-- -- @return 'Approve','Finish' or null +-- --*/ +-- task_id in wf_tasks.task_id%TYPE, +-- user_id in wf_tasks.holding_user%TYPE +-- ) return varchar2; +-- +-- +-- +-- function count_finished_tasks( +-- --/** Gets the number of finished tasks for a given case. +-- -- @author Michael Pih +-- -- @param case_id The case_id +-- -- @return the number of finished tasks for a given case +-- --*/ +-- case_id in wf_cases.case_id%TYPE +-- ) return integer; +-- +-- +-- function count_unfinished_tasks( +-- --/** Gets the number of unfinished tasks for a given case +-- -- @author Michael Pih +-- -- @param case_id The case_id +-- -- @return the number of unfinished tasks for a given case +-- --*/ +-- case_id in wf_cases.case_id%TYPE +-- ) return integer; +-- +-- +-- function is_active ( +-- --/** Determines whether a case transition is active +-- -- @author Michael Pih +-- -- @param case_id The case_id +-- -- @param transition_key The transition +-- -- @return 't' id that case transition is active, 'f' otherwise +-- --*/ +-- case_id in wf_cases.case_id%TYPE, +-- transition_key in wf_transitions.transition_key%TYPE +-- ) return char; +-- +-- +-- function is_finished ( +-- --/** Determines whether a case transition is finished +-- -- @author Michael Pih +-- -- @param case_id The case_id +-- -- @param transition_key The transition +-- -- @return 't' id that case transition is finished, 'f' otherwise +-- --*/ +-- case_id in wf_cases.case_id%TYPE, +-- transition_key in wf_transitions.transition_key%TYPE +-- ) return char; +-- +-- +-- function is_checked_out ( +-- --/** Determines whether a case transition is checked out +-- -- @author Michael Pih +-- -- @param case_id The case_id +-- -- @param transition_key The transition +-- -- @return 't' id that case transition is checked out, 'f' otherwise +-- --*/ +-- case_id in wf_cases.case_id%TYPE, +-- transition_key in wf_transitions.transition_key%TYPE +-- ) return char; +-- +-- +-- function is_checked_out ( +-- --/** Determines whether a case transition is checked out by a certain user +-- -- @author Michael Pih +-- -- @param case_id The case_id +-- -- @param transition_key The transition +-- -- @param user_id The user +-- -- @return 't' id that case transition is checked out by the specified +-- -- user, 'f' otherwise +-- --*/ +-- case_id in wf_cases.case_id%TYPE, +-- transition_key in wf_transitions.transition_key%TYPE, +-- user_id in wf_tasks.holding_user%TYPE +-- ) return char; +-- +-- +-- function get_status( +-- --/** Gets the status of the task. +-- -- @author Michael Pih +-- -- @param case_id The case_id +-- -- @param transition_key The transition +-- -- @return HTML-formatted status of the task, null otherwise +-- --*/ +-- case_id in wf_cases.case_id%TYPE, +-- transition_key in wf_transitions.transition_key%TYPE +-- ) return varchar2; +-- +-- +-- function can_touch ( +-- --/** Returns 't' if a user has permission to touch an item +-- -- @author Michael Pih +-- -- @param item_id The item +-- -- @param user_id The user +-- -- @return 't' if a user has permission to touch an item, 'f' otherwise +-- -- An item is touchable if: +-- -- 1) the user has admin privileges on the +-- -- 2) a workflow exists, current task assigned to user, and +-- -- the task is not checked out +-- --*/ +-- item_id in cr_items.item_id%TYPE, +-- user_id in users.user_id%TYPE +-- ) return char; +-- +-- +-- +-- function unfinished_workflow_exists ( +-- --/** Returns 't' if an unfinished (not cancelled) workflow exists +-- -- otherwise returns 'f' +-- -- @author Michael Pih +-- -- @param item_id The item +-- -- @return 't' if a workflow case exists and is not in the 'finished' or +-- -- 'canceled' state +-- --*/ +-- item_id in cr_items.item_id%TYPE +-- ) return char; +-- +-- +-- end content_workflow; - function is_overdue ( - --/** Determines if the workflow task is overdue - -- @author Michael Pih - -- @param task_id The task id - -- @return 't' if the deadline > sysdate, 'f' otherwise - --*/ - v_task_id in wf_tasks.task_id%TYPE - ) return char; +-- show errors - function is_overdue ( - --/** Determines if the workflow transition (task) is overdue - -- @author Michael Pih - -- @param case_id The case id - -- @param transition_key The transition key - -- @return 't' if the deadline > sysdate, 'f' otherwise - --*/ - case_id in wf_cases.case_id%TYPE, - transition_key in wf_transitions.transition_key%TYPE - ) return char; - function get_holding_user_name( - --/** Gets the name of the user who is currently holding this task - -- @author Michael Pih - -- @param task_id The task id - -- @return name of the user who holds the task, otherwise NULL - --*/ - v_task_id in wf_tasks.task_id%TYPE - ) return varchar2; +-- create or replace package body content_workflow +-- function is_overdue +create function content_workflow__is_overdue (integer) +returns boolean as ' +declare + p_v_task_id alias for $1; +begin - function get_first_place - --/** Gets the first place in the workflow (determined by sort order) - -- @author Michael Pih - -- @return the first place in the workflow - --*/ - return wf_places.place_key%TYPE; - - - function get_this_place( - --/** Gets the current place in the workflow given the current transition - -- @author Michael Pih - -- @param transition_key The transition - -- @return the current place in the workflow - --*/ - transition_key in wf_transitions.transition_key%TYPE - ) return wf_places.place_key%TYPE; - - - function get_next_place( - --/** Gets the next place given a transition key (determined by sort order). - -- Throws an error if there is no next place. - -- @author Michael Pih - -- @param transition_key The transition - -- @return the next place in the workflow - --*/ - transition_key in wf_transitions.transition_key%TYPE - ) return wf_places.place_key%TYPE; - - - function get_previous_place( - --/** Gets the previous place given a transition key - -- (determined by sort order). - -- Throws an error if there is no previous place. - -- @author Michael Pih - -- @param transition_key The transition - -- @return the previous place in the workflow - --*/ - transition_key in wf_transitions.transition_key%TYPE - ) return wf_places.place_key%TYPE; - - - procedure checkout ( - --/** Checks out a task - -- @author Michael Pih - -- @param task_id The task_id - -- @param hold_timeout How long the user expects to hold this task - -- @param user_id The user checking out the task - -- @param ip_address The user's IP address (for auditing) - -- @param msg Comments concerning checkout - --*/ - task_id in wf_tasks.task_id%TYPE, - hold_timeout in wf_tasks.hold_timeout%TYPE default null, - user_id in acs_objects.creation_user%TYPE, - ip_address in acs_objects.creation_ip%TYPE, - msg in varchar - ); - - - procedure checkin ( - --/** Checks in a task that the user is holding. Throws an error - -- if the task is not checked out already or if the task is checked - -- out by another user. - -- @author Michael Pih - -- @param task_id The task_id - -- @param user_id The user checking in the task - -- @param ip_address The IP address of the user - -- @param msg Comment associated with checking the task in - --*/ - task_id in wf_tasks.task_id%TYPE, - user_id in acs_objects.creation_user%TYPE, - ip_address in acs_objects.creation_ip%TYPE, - msg in varchar - ); - - - procedure approve( - --/** Finish a task - -- @author Michael Pih - -- @param task_id The task_id - -- @param user_id The user finishing the task - -- @param ip_address The user's IP address (for auditing) - -- @param msg Comments concerning finishing the task - --*/ - task_id in wf_tasks.task_id%TYPE, - user_id in acs_objects.creation_user%TYPE, - ip_address in acs_objects.creation_ip%TYPE, - msg in varchar - ); - - - procedure reject( - --/** Finish a task - -- @author Michael Pih - -- @param task_id The task_id - -- @param user_id The user finishing the task - -- @param ip_address The user's IP address (for auditing) - -- @param transition_key The transition the user wants to fall back to - -- @param msg Comments concerning finishing the task - --*/ - task_id in wf_tasks.task_id%TYPE, - user_id in acs_objects.creation_user%TYPE, - ip_address in acs_objects.creation_ip%TYPE, - transition_key in wf_transitions.transition_key%TYPE, - msg in varchar - ); - - - procedure notify_of_checkout( - --/** Helper procedure. - -- Fires notifications after stealing the lock on a task - -- @author Michael Pih - -- @param task_id The task_id - -- @param holding_user_old The user finishing the task - -- @param holding_user_new The user's IP address (for auditing) - -- @param msg Comments concerning stealing the task - --*/ - task_id in wf_tasks.task_id%TYPE, - holding_user_old in wf_tasks.holding_user%TYPE, - holding_user_new in wf_tasks.holding_user%TYPE, - msg in varchar - ); - - - function can_reject( - --/** Returns 't' if a task is rejectable by the user. - -- Otherwise returns 'f' - -- @author Michael Pih - -- @param task_id The task_id - -- @param user_id The user_id - -- @return 't' if the task can be rejected, 'f' otherwise - --*/ - task_id in wf_tasks.task_id%TYPE, - user_id in wf_tasks.holding_user%TYPE - ) return char; - - - - function can_approve( - --/** Returns 't' if a task is approvable by the user. - -- Otherwise returns 'f' - -- @author Michael Pih - -- @param task_id The task_id - -- @param user_id The user_id - -- @return 't' if the task can be approved, 'f' otherwise - --*/ - task_id in wf_tasks.task_id%TYPE, - user_id in wf_tasks.holding_user%TYPE - ) return char; - - - function can_start( - --/** Returns 't' if a task can be checked out by the user. - -- Otherwise returns 'f' - -- @author Michael Pih - -- @param task_id The task_id - -- @param user_id The user_id - -- @return 't' if the task can be started, 'f' otherwise - --*/ - task_id in wf_tasks.task_id%TYPE, - user_id in wf_tasks.holding_user%TYPE - ) return char; - - - - function approve_string( - --/** If the task is approvable, returns 'Approve' or if it is the first - -- task, then 'Finish'. Otherwise returns null. - -- @author Michael Pih - -- @param task_id The task_id - -- @param user_id The user_id - -- @return 'Approve','Finish' or null - --*/ - task_id in wf_tasks.task_id%TYPE, - user_id in wf_tasks.holding_user%TYPE - ) return varchar2; - - - - function count_finished_tasks( - --/** Gets the number of finished tasks for a given case. - -- @author Michael Pih - -- @param case_id The case_id - -- @return the number of finished tasks for a given case - --*/ - case_id in wf_cases.case_id%TYPE - ) return integer; - - - function count_unfinished_tasks( - --/** Gets the number of unfinished tasks for a given case - -- @author Michael Pih - -- @param case_id The case_id - -- @return the number of unfinished tasks for a given case - --*/ - case_id in wf_cases.case_id%TYPE - ) return integer; - - - function is_active ( - --/** Determines whether a case transition is active - -- @author Michael Pih - -- @param case_id The case_id - -- @param transition_key The transition - -- @return 't' id that case transition is active, 'f' otherwise - --*/ - case_id in wf_cases.case_id%TYPE, - transition_key in wf_transitions.transition_key%TYPE - ) return char; - - - function is_finished ( - --/** Determines whether a case transition is finished - -- @author Michael Pih - -- @param case_id The case_id - -- @param transition_key The transition - -- @return 't' id that case transition is finished, 'f' otherwise - --*/ - case_id in wf_cases.case_id%TYPE, - transition_key in wf_transitions.transition_key%TYPE - ) return char; - - - function is_checked_out ( - --/** Determines whether a case transition is checked out - -- @author Michael Pih - -- @param case_id The case_id - -- @param transition_key The transition - -- @return 't' id that case transition is checked out, 'f' otherwise - --*/ - case_id in wf_cases.case_id%TYPE, - transition_key in wf_transitions.transition_key%TYPE - ) return char; - - - function is_checked_out ( - --/** Determines whether a case transition is checked out by a certain user - -- @author Michael Pih - -- @param case_id The case_id - -- @param transition_key The transition - -- @param user_id The user - -- @return 't' id that case transition is checked out by the specified - -- user, 'f' otherwise - --*/ - case_id in wf_cases.case_id%TYPE, - transition_key in wf_transitions.transition_key%TYPE, - user_id in wf_tasks.holding_user%TYPE - ) return char; - - - function get_status( - --/** Gets the status of the task. - -- @author Michael Pih - -- @param case_id The case_id - -- @param transition_key The transition - -- @return HTML-formatted status of the task, null otherwise - --*/ - case_id in wf_cases.case_id%TYPE, - transition_key in wf_transitions.transition_key%TYPE - ) return varchar2; - - - function can_touch ( - --/** Returns 't' if a user has permission to touch an item - -- @author Michael Pih - -- @param item_id The item - -- @param user_id The user - -- @return 't' if a user has permission to touch an item, 'f' otherwise - -- An item is touchable if: - -- 1) the user has admin privileges on the - -- 2) a workflow exists, current task assigned to user, and - -- the task is not checked out - --*/ - item_id in cr_items.item_id%TYPE, - user_id in users.user_id%TYPE - ) return char; - - - - function unfinished_workflow_exists ( - --/** Returns 't' if an unfinished (not cancelled) workflow exists - -- otherwise returns 'f' - -- @author Michael Pih - -- @param item_id The item - -- @return 't' if a workflow case exists and is not in the 'finished' or - -- 'canceled' state - --*/ - item_id in cr_items.item_id%TYPE - ) return char; - - -end content_workflow; -/ -show errors - - - - - -create or replace package body content_workflow -as - - function is_overdue( - v_task_id in wf_tasks.task_id%TYPE - ) return char - is - v_overdue_p char; - begin - select - 't' - into - v_overdue_p + -- FIXME: is dead.deadline supposed to be a date-only (e.g. no time) + return + count(*) > 0 from wf_tasks t, wf_case_deadlines dead where @@ -365,85 +363,73 @@ and dead.deadline is not null and - dead.deadline < trunc(sysdate); + dead.deadline < date_trunc(''day'',now()); + +end;' language 'plpgsql'; - return v_overdue_p; - exception - when NO_DATA_FOUND then - return 'f'; - end is_overdue; - - - function is_overdue( - case_id in wf_cases.case_id%TYPE, - transition_key in wf_transitions.transition_key%TYPE - ) return char - is - v_overdue_p char; - begin - select - 't' into v_overdue_p +-- function is_overdue +create function content_workflow__is_overdue (integer,varchar) +returns boolean as ' +declare + p_case_id alias for $1; + p_transition_key alias for $2; +begin + return + count(*) > 0 from wf_case_deadlines dead where - case_id = is_overdue.case_id + case_id = p_case_id and - transition_key = is_overdue.transition_key + transition_key = p_transition_key and deadline is not null and - deadline < trunc(sysdate) + deadline < date_trunc(''day'',now()) and - content_workflow.is_finished(is_overdue.case_id, - is_overdue.transition_key) = 'f'; + content_workflow__is_finished(p_case_id, + p_transition_key) = ''f''; + +end;' language 'plpgsql'; - return v_overdue_p; - exception - when NO_DATA_FOUND then - return 'f'; - end is_overdue; +-- function get_holding_user_name +create function content_workflow__get_holding_user_name (integer) +returns varchar as ' +declare + p_v_task_id alias for $1; + v_name varchar(100); +begin - - function get_holding_user_name( - v_task_id in wf_tasks.task_id%TYPE - ) return varchar2 - is - v_name varchar2(100); - begin - select - first_names || ' ' || last_name + first_names || '' '' || last_name into v_name from persons p, wf_tasks t where t.holding_user = p.person_id and - t.task_id = v_task_id; + t.task_id = p_v_task_id; return v_name; - exception - when NO_DATA_FOUND then - return null; - end get_holding_user_name; + +end;' language 'plpgsql'; - function get_first_place - return wf_places.place_key%TYPE - is +create get_first_place() returns varchar as ' +declare v_first_place wf_places.place_key%TYPE; - begin +begin select place_key into v_first_place from wf_places w where - workflow_key = 'publishing_wf' + workflow_key = ''publishing_wf'' and sort_order = (select min(sort_order) @@ -454,50 +440,49 @@ return v_first_place; - exception when no_data_found then - return null; - end get_first_place; +end;' language 'plpgsql'; +-- function get_this_place +create function content_workflow__get_this_place (varchar) +returns varchar as ' +declare + p_transition_key alias for $1; + v_this_place wf_places.place_key%TYPE; +begin - - function get_this_place( - transition_key in wf_transitions.transition_key%TYPE - ) return wf_places.place_key%TYPE - is - v_this_place wf_places.place_key%TYPE; - begin - select place_key into v_this_place from wf_arcs where - transition_key = get_this_place.transition_key + transition_key = p_transition_key and - workflow_key = 'publishing_wf' + workflow_key = ''publishing_wf'' and - direction = 'in'; + direction = ''in''; + + if NOT FOUND then + raise EXCEPTION ''-20000, ''Bad transition key %'', p_transition_key; + end if; + return v_this_place; - - exception - when no_data_found then - raise_application_error(-20000, 'Bad transition key ' || - get_this_place.transition_key - ); - end get_this_place; + +end;' language 'plpgsql'; +-- function get_next_place +create function content_workflow__get_next_place (varchar) +returns varchar as ' +declare + p_transition_key alias for $1; + v_next_place wf_places.place_key%TYPE; +begin - function get_next_place( - transition_key in wf_transitions.transition_key%TYPE - ) return wf_places.place_key%TYPE - is - v_next_place wf_places.place_key%TYPE; - - cursor c_places_cur is - select + select there.place_key + into + v_next_place from wf_places here, wf_places there where @@ -511,35 +496,28 @@ there.sort_order > here.sort_order order by there.sort_order; - begin - open c_places_cur; - fetch c_places_cur into v_next_place; - if c_places_cur%NOTFOUND then - close c_places_cur; - raise_application_error (-20000, - 'content_workflow.get_next_place - No next place - Dead End' - ); + if NOT FOUND then + raise EXCEPTION ''-20000: content_workflow.get_next_place - No next place - Dead End''; end if; - close c_places_cur; - + return v_next_place; - exception - when NO_DATA_FOUND then - return null; - end get_next_place; + +end;' language 'plpgsql'; +-- function get_previous_place +create function content_workflow__get_previous_place (varchar) +returns varchar as ' +declare + p_transition_key alias for $1; + v_previous_place wf_places.place_key%TYPE; +begin - function get_previous_place( - transition_key in wf_transitions.transition_key%TYPE - ) return wf_places.place_key%TYPE - is - v_previous_place wf_places.place_key%TYPE; - - cursor c_places_cur is select there.place_key + into + v_previous_place from wf_places here, wf_places there where @@ -553,40 +531,31 @@ there.sort_order < here.sort_order order by there.sort_order desc; - begin - open c_places_cur; - fetch c_places_cur into v_previous_place; - if c_places_cur%NOTFOUND then - close c_places_cur; - raise_application_error (-20000, - 'content_workflow.get_previous_place - No previous place - Dead End' - ); + if NOT FOUND then + raise EXCEPTION ''-20000: content_workflow.get_previous_place - No previous place - Dead End''; end if; - close c_places_cur; return v_previous_place; - exception - when NO_DATA_FOUND then - return null; - end get_previous_place; + +end;' language 'plpgsql'; - - procedure checkout ( - task_id in wf_tasks.task_id%TYPE, - hold_timeout in wf_tasks.hold_timeout%TYPE, - user_id in acs_objects.creation_user%TYPE, - ip_address in acs_objects.creation_ip%TYPE, - msg in varchar - ) - is - v_task_state wf_tasks.state%TYPE; - v_holding_user wf_tasks.holding_user%TYPE; - v_journal_id number; - v_transition_key wf_transitions.transition_key%TYPE; - v_this_place wf_places.place_key%TYPE; - begin +-- procedure checkout +create function content_workflow__checkout (integer,timestamp,integer,varchar,<=>) +returns integer as ' +declare + p_task_id alias for $1; + p_hold_timeout alias for $2; + p_user_id alias for $3; + p_ip_address alias for $4; + p_msg alias for $5; + v_task_state wf_tasks.state%TYPE; + v_holding_user wf_tasks.holding_user%TYPE; + v_journal_id number; + v_transition_key wf_transitions.transition_key%TYPE; + v_this_place wf_places.place_key%TYPE; +begin -- find out who is holding the task right now select @@ -596,92 +565,91 @@ from wf_tasks where - task_id = checkout.task_id; + task_id = p_task_id; -- someone else has already holds this task -- we need to check in the task as the other person before -- this user can check it out - if v_task_state = 'started' and v_holding_user is not null - and v_holding_user ^= checkout.user_id then + if v_task_state = ''started'' and v_holding_user is not null + and v_holding_user != p_user_id then -- need to manually update the state otherwise a new task is created update wf_tasks - set state = 'enabled', + set state = ''enabled'', holding_user = null, hold_timeout = null - where task_id = checkout.task_id; + where task_id = p_task_id; - v_task_state := 'enabled'; + v_task_state := ''enabled''; end if; -- actually check out the item - -- (start the task but don't change 'next_place') - if v_task_state = 'enabled' then + -- (start the task but do not change next_place) + if v_task_state = ''enabled'' then - v_journal_id := workflow_case.begin_task_action( - task_id => checkout.task_id, - action => 'start', - action_ip => checkout.ip_address, - user_id => checkout.user_id, - msg => checkout.msg + v_journal_id := workflow_case__begin_task_action( + p_task_id, + ''start'', + p_ip_address, + p_user_id, + p_msg ); - v_this_place := content_workflow.get_this_place( v_transition_key ); + v_this_place := content_workflow__get_this_place( v_transition_key ); - workflow_case.set_attribute_value( - journal_id => v_journal_id, - attribute_name => 'next_place', - value => v_transition_key + workflow_case__set_attribute_value( + v_journal_id, + ''next_place'', + v_transition_key ); - workflow_case.end_task_action( - task_id => checkout.task_id, - action => 'start', - journal_id => v_journal_id + workflow_case__end_task_action( + v_journal_id, + ''start'', + p_task_id ); -- change the holding user and hold timeout update wf_tasks - set hold_timeout = checkout.hold_timeout, - holding_user = checkout.user_id - where task_id = checkout.task_id; + set hold_timeout = p_hold_timeout, + holding_user = p_user_id + where task_id = p_task_id; if v_holding_user is not null and - v_holding_user ^= checkout.user_id then + v_holding_user ^= p_user_id then -- send a notification - content_workflow.notify_of_checkout( - task_id => checkout.task_id, - holding_user_old => v_holding_user, - holding_user_new => checkout.user_id, - msg => checkout.msg + PERFORM content_workflow__notify_of_checkout( + p_task_id, + v_holding_user, + p_user_id, + p_msg ); end if; else - raise_application_error(-20000, - 'Cannot check out this task because it''s in an invalid state ' - || v_task_state - ); + raise EXCEPTION ''-20000: Cannot check out this task because it''''s in an invalid state %'', v_task_state end if; - end checkout; + return 0; +end;' language 'plpgsql'; +-- procedure checkin +create function content_workflow__checkin (integer,integer,varchar,varchar) +returns integer as ' +declare + p_task_id alias for $1; + p_user_id alias for $2; + p_ip_address alias for $3; + p_msg alias for $4; + v_task_state wf_tasks.state%TYPE; + v_holding_user wf_tasks.holding_user%TYPE; + v_journal_id integer; + v_this_place wf_places.place_key%TYPE; + v_transition_key wf_transitions.transition_key%TYPE; +begin - procedure checkin ( - task_id in wf_tasks.task_id%TYPE, - user_id in acs_objects.creation_user%TYPE, - ip_address in acs_objects.creation_ip%TYPE, - msg in varchar - ) is - v_task_state wf_tasks.state%TYPE; - v_holding_user wf_tasks.holding_user%TYPE; - v_journal_id number; - v_this_place wf_places.place_key%TYPE; - v_transition_key wf_transitions.transition_key%TYPE; - begin - -- find out who is holding the task right now select state, holding_user, transition_key @@ -690,63 +658,58 @@ from wf_tasks where - task_id = checkin.task_id; + task_id = p_task_id; - if v_task_state = 'started' and v_holding_user = checkin.user_id then + if v_task_state = ''started'' and v_holding_user = p_user_id then - - v_journal_id := workflow_case.begin_task_action( - task_id => checkin.task_id, - action => 'finish', - user_id => checkin.user_id, - action_ip => checkin.ip_address, - msg => checkin.msg + v_journal_id := workflow_case__begin_task_action( + p_task_id, + ''finish'', + p_ip_address, + p_user_id, + p_msg ); - v_this_place := content_workflow.get_this_place( v_transition_key ); + v_this_place := content_workflow__get_this_place( v_transition_key ); - workflow_case.set_attribute_value( - journal_id => v_journal_id, - attribute_name => 'next_place', - value => v_this_place + PERFORM workflow_case__set_attribute_value( + v_journal_id, + ''next_place'', + v_this_place ); - workflow_case.end_task_action( - journal_id => v_journal_id, - action => 'finish', - task_id => checkin.task_id + PERFORM workflow_case__end_task_action( + v_journal_id, + ''finish'', + p_task_id ); - elsif v_task_state ^= 'started' then - raise_application_error( -20000, - 'Cannot chack in this task because it''s in an invalid state ' - || v_task_state - ); + else if v_task_state != ''started'' then + raise EXCEPTION '' -20000: Cannot chack in this task because it''''s in an invalid state %'', v_task_state; else - raise_application_error( -20000, - 'Cannot check in this task because user_id ' || user_id || - ' is not the holding user' - ); - end if; + raise EXCEPTION '' -20000: Cannot check in this task because user_id % is not the holding user'', user_id; + end if; end if; - end checkin; + return 0; +end;' language 'plpgsql'; +-- procedure approve +create function content_workflow__approve (integer,integer,varchar,varchar) +returns integer as ' +declare + p_task_id alias for $1; + p_user_id alias for $2; + p_ip_address alias for $3; + p_msg alias for $4; + v_task_state wf_tasks.state%TYPE; + v_holding_user wf_tasks.holding_user%TYPE; + v_journal_id integer; + v_transition_key wf_transitions.transition_key%TYPE; + v_next_place wf_places.place_key%TYPE; +begin - procedure approve( - task_id in wf_tasks.task_id%TYPE, - user_id in acs_objects.creation_user%TYPE, - ip_address in acs_objects.creation_ip%TYPE, - msg in varchar - ) is - v_task_state wf_tasks.state%TYPE; - v_holding_user wf_tasks.holding_user%TYPE; - v_journal_id number; - v_transition_key wf_transitions.transition_key%TYPE; - v_next_place wf_places.place_key%TYPE; - begin - -- find out who is holding the task right now select state, holding_user, transition_key @@ -755,78 +718,76 @@ from wf_tasks where - task_id = approve.task_id; + task_id = p_task_id; - if v_task_state = 'started' and v_holding_user ^= approve.user_id then + if v_task_state = ''started'' and v_holding_user != p_user_id then - raise_application_error( -20000, - 'content_workflow.approve - Could not approve task because this task - is checked out by someone else ' || v_holding_user - ); + raise EXCEPTION '' -20000:content_workflow.approve - Could not approve task because this task is checked out by someone else %'', v_holding_user; - elsif v_task_state ^= 'started' and v_task_state ^= 'enabled' then - raise_application_error( -20000, - 'content_workflow.approve - Could not approve task because this task - is in an invalid state ' || v_task_state - ); + else if v_task_state != ''started'' and v_task_state != ''enabled'' then + raise EXCEPTION '' -20000: content_workflow.approve - Could not approve task because this task is in an invalid state %'', v_task_state; -- user is allowed to finish the task else -- we need to checkout the task first - if v_task_state = 'enabled' then - content_workflow.checkout( - task_id => approve.task_id, - hold_timeout => null, - user_id => approve.user_id, - ip_address => approve.ip_address, - msg => approve.msg + if v_task_state = ''enabled'' then + PERFORM content_workflow__checkout( + p_task_id, + null, + p_user_id, + p_ip_address, + p_msg ); - end if; + end if; end if; - v_journal_id := workflow_case.begin_task_action( - task_id => approve.task_id, - action => 'finish', - action_ip => approve.ip_address, - user_id => approve.user_id, - msg => approve.msg + v_journal_id := workflow_case__begin_task_action( + p_task_id, + ''finish'', + p_ip_address, + p_user_id, + p_msg ); - v_next_place := content_workflow.get_next_place( - transition_key => v_transition_key + v_next_place := content_workflow__get_next_place( + v_transition_key ); - workflow_case.set_attribute_value( - journal_id => v_journal_id, - attribute_name => 'next_place', - value => v_next_place + PERFORM workflow_case__set_attribute_value( + v_journal_id, + ''next_place'', + v_next_place ); - workflow_case.end_task_action( - task_id => approve.task_id, - action => 'finish', - journal_id => v_journal_id + PERFORM workflow_case__end_task_action( + v_journal_id, + ''finish'', + p_task_id ); end if; - end approve; + return 0; +end;' language 'plpgsql'; - procedure reject( - task_id in wf_tasks.task_id%TYPE, - user_id in acs_objects.creation_user%TYPE, - ip_address in acs_objects.creation_ip%TYPE, - transition_key in wf_transitions.transition_key%TYPE, - msg in varchar - ) is - v_task_state wf_tasks.state%TYPE; - v_holding_user wf_tasks.holding_user%TYPE; - v_transition_key wf_transitions.transition_key%TYPE; - v_journal_id number; - v_sanity_check integer; - v_previous_place wf_places.place_key%TYPE; - begin +-- procedure reject +create function content_workflow__reject (integer,integer,varchar,varchar,<=>) +returns integer as ' +declare + p_task_id alias for $1; + p_user_id alias for $2; + p_ip_address alias for $3; + p_transition_key alias for $4; + p_msg alias for $5; + v_task_state wf_tasks.state%TYPE; + v_holding_user wf_tasks.holding_user%TYPE; + v_transition_key wf_transitions.transition_key%TYPE; + v_journal_id integer; + v_sanity_check integer; + v_previous_place wf_places.place_key%TYPE; +begin + -- find out who is holding the task right now select state, holding_user, transition_key @@ -835,7 +796,7 @@ from wf_tasks where - task_id = reject.task_id; + task_id = p_task_id; -- do a quick sanity check -- make sure the desired transition is accessible from this transition @@ -844,121 +805,113 @@ from wf_arcs out, wf_arcs dest where - out.workflow_key = 'publishing_wf' + out.workflow_key = ''publishing_wf'' and out.workflow_key = dest.workflow_key and - out.direction = 'out' + out.direction = ''out'' and - dest.direction = 'in' + dest.direction = ''in'' and out.transition_key = v_transition_key and - dest.transition_key = reject.transition_key + dest.transition_key = p_transition_key and - reject.transition_key ^= v_transition_key + p_transition_key != v_transition_key and -- make sure the arcs are connected out.place_key = dest.place_key; if v_sanity_check = 0 then - raise_application_error( -20000, - 'content_workflow.reject - Sanity check failed - invalid transition: ' - || reject.transition_key - ); + raise EXCEPTION '' -20000: content_workflow.reject - Sanity check failed - invalid transition: %'', p_transition_key; end if; - if v_task_state = 'started' and v_holding_user ^= reject.user_id then - raise_application_error( -20000, - 'content_workflow.reject - Could not reject task because this task - is checked out by someone else ' || v_holding_user - ); - elsif v_task_state ^= 'started' and v_task_state ^= 'enabled' then - raise_application_error( -20000, - 'content_workflow.approve - Could not reject task because this task - is in an invalid state ' || v_task_state - ); + if v_task_state = ''started'' and v_holding_user != p_user_id then + raise EXCEPTION '' -20000: content_workflow.reject - Could not reject task because this task is checked out by someone else %'', v_holding_user; + else if v_task_state != ''started'' and v_task_state != ''enabled'' then + raise EXCEPTION '' -20000: content_workflow.approve - Could not reject task because this task is in an invalid state %'', v_task_state; else -- we need to start this task first - if v_task_state = 'enabled' then - content_workflow.checkout( - task_id => reject.task_id, - hold_timeout => null, - user_id => reject.user_id, - ip_address => reject.ip_address, - msg => reject.msg + if v_task_state = ''enabled'' then + PERFORM content_workflow__checkout( + p_task_id, + null, + p_user_id, + p_ip_address, + p_msg ); end if; -- ok to reject this task - v_journal_id := workflow_case.begin_task_action( - task_id => reject.task_id, - action => 'finish', - action_ip => reject.ip_address, - user_id => reject.user_id, - msg => reject.msg + v_journal_id := workflow_case__begin_task_action( + p_task_id, + ''finish'', + p_ip_address, + p_user_id, + p_msg ); - v_previous_place := content_workflow.get_this_place( - transition_key => reject.transition_key + v_previous_place := content_workflow__get_this_place( + p_transition_key ); - workflow_case.set_attribute_value( - journal_id => v_journal_id, - attribute_name => 'next_place', - value => v_previous_place + workflow_case__set_attribute_value( + v_journal_id, + ''next_place'', + v_previous_place ); - workflow_case.end_task_action( - task_id => reject.task_id, - action => 'finish', - journal_id => v_journal_id + workflow_case__end_task_action( + v_journal_id, + ''finish'', + p_task_id ); - end if; - end reject; + end if; end if; + return 0; +end;' language 'plpgsql'; +-- procedure notify_of_checkout +create function content_workflow__notify_of_checkout (integer,integer,integer,varchar) +returns integer as ' +declare + p_task_id alias for $1; + p_holding_user_old alias for $2; + p_holding_user_new alias for $3; + p_msg alias for $4; + v_hold_user_old varchar(100); + v_hold_user_new varchar(100); + v_transition_name wf_transitions.transition_name%TYPE; + v_request_id nt_requests.request_id%TYPE; + v_item_name varchar(100); +begin - - procedure notify_of_checkout ( - task_id in wf_tasks.task_id%TYPE, - holding_user_old in wf_tasks.holding_user%TYPE, - holding_user_new in wf_tasks.holding_user%TYPE, - msg in varchar - ) is - v_hold_user_old varchar(100); - v_hold_user_new varchar(100); - v_transition_name wf_transitions.transition_name%TYPE; - v_request_id nt_requests.request_id%TYPE; - v_item_name varchar(100); - begin - -- get the robbed users name select - first_names || ' ' || last_name into v_hold_user_old + first_names || '' '' || last_name into v_hold_user_old from persons where - person_id = notify_of_checkout.holding_user_old; + person_id = p_holding_user_old; -- get the lock stealers name select - first_names || ' ' || last_name into v_hold_user_new + first_names || '' '' || last_name into v_hold_user_new from persons where - person_id = notify_of_checkout.holding_user_new; + person_id = p_holding_user_new; -- get the item name and transition name select - transition_name, content_item.get_title( c.object_id ) + transition_name, content_item__get_title( c.object_id, ''f'' ) into v_transition_name, v_item_name from @@ -968,258 +921,234 @@ and t.case_id = c.case_id and - t.task_id = notify_of_checkout.task_id; + t.task_id = p_task_id; + -- FIXME: not ported yet. +/* -- send out the request v_request_id := nt.post_request ( - party_from => notify_of_checkout.holding_user_new, - party_to => notify_of_checkout.holding_user_old, - expand_group => 'f', - subject => v_hold_user_new || ' stole the lock for ' || - v_transition_name || ' of ' || v_item_name, - message => 'Dear ' || v_hold_user_old || ',\n' || - notify_of_checkout.msg + party_from => p_holding_user_new, + party_to => p_holding_user_old, + expand_group => ''f'', + subject => v_hold_user_new || '' stole the lock for '' || + v_transition_name || '' of '' || v_item_name, + message => ''Dear '' || v_hold_user_old || '',\n'' || + p_msg ); +*/ + return 0; +end;' language 'plpgsql'; - end notify_of_checkout; +-- function can_reject +create function content_workflow__can_reject (integer,integer) +returns boolean as ' +declare + p_task_id alias for $1; + p_user_id alias for $2; + v_transition_key wf_transitions.transition_key%TYPE; +begin - function can_reject( - task_id in wf_tasks.task_id%TYPE, - user_id in wf_tasks.holding_user%TYPE - ) return char - is - v_transition_key wf_transitions.transition_key%TYPE; - v_can_reject char(1); - begin - - select - 't' into v_can_reject + return + count(*) > 0 from wf_tasks where - task_id = can_reject.task_id + task_id = p_task_id and - workflow_key = 'publishing_wf' + workflow_key = ''publishing_wf'' and - (state = 'enabled' - or (state = 'started' - and holding_user = can_reject.user_id)) + (state = ''enabled'' + or (state = ''started'' + and holding_user = p_user_id)) and - content_workflow.get_this_place(transition_key) ^= - content_workflow.get_first_place; + content_workflow__get_this_place(transition_key) != + content_workflow__get_first_place(); + +end;' language 'plpgsql'; - return v_can_reject; - exception - when NO_DATA_FOUND then - return 'f'; - end can_reject; +-- function can_approve +create function content_workflow__can_approve (integer,integer) +returns boolean +declare + p_task_id alias for $1; + p_user_id alias for $2; +begin - - function can_approve( - task_id in wf_tasks.task_id%TYPE, - user_id in wf_tasks.holding_user%TYPE - ) return char - is - v_can_approve char(1); - begin - - select - 't' into v_can_approve + return + count(*) > 0 from wf_tasks where - (state = 'enabled' - or (state = 'started' - and holding_user = can_approve.user_id)) + (state = ''enabled'' + or (state = ''started'' + and holding_user = p_user_id)) and - task_id = can_approve.task_id + task_id = p_task_id and - workflow_key = 'publishing_wf'; + workflow_key = ''publishing_wf''; - return v_can_approve; - exception - when NO_DATA_FOUND then - return 'f'; +end;' language 'plpgsql'; - end can_approve; +-- function can_start +create function content_workflow__can_start (integer,integer) +returns char as ' +declare + p_task_id alias for $1; + p_user_id alias for $2; +begin - function can_start( - task_id in wf_tasks.task_id%TYPE, - user_id in wf_tasks.holding_user%TYPE - ) return char - is - v_can_start char(1); - begin - - select - 't' into v_can_start + return + count(*) > 0 from wf_tasks where - (state = 'enabled' - or (state = 'started' - and holding_user ^= can_start.user_id)) + (state = ''enabled'' + or (state = ''started'' + and holding_user != p_user_id)) and - task_id = can_start.task_id + task_id = p_task_id and - workflow_key = 'publishing_wf'; + workflow_key = ''publishing_wf''; + +end;' language 'plpgsql'; - return v_can_start; - exception - when NO_DATA_FOUND then - return 'f'; - end can_start; +-- function approve_string +create function content_workflow__approve_string (integer,integer) +returns varchar as ' +declare + p_task_id alias for $1; + p_user_id alias for $2; + v_transition_key wf_transitions.transition_key%TYPE; + v_approve_string varchar(10); +begin - - - function approve_string( - task_id in wf_tasks.task_id%TYPE, - user_id in wf_tasks.holding_user%TYPE - ) return varchar2 - is - v_transition_key wf_transitions.transition_key%TYPE; - v_approve_string varchar(10); - begin - - if content_workflow.can_approve( - approve_string.task_id, approve_string.user_id ) = 't' then + if content_workflow__can_approve( + p_task_id, p_user_id ) = ''t'' then select transition_key into v_transition_key from wf_tasks where - task_id = approve_string.task_id; + task_id = p_task_id; - if content_workflow.get_this_place( v_transition_key ) = - content_workflow.get_first_place then - v_approve_string := 'Finish'; + if content_workflow__get_this_place( v_transition_key ) = + content_workflow__get_first_place() then + v_approve_string := ''Finish''; else - v_approve_string := 'Approve'; + v_approve_string := ''Approve''; end if; else v_approve_string := null; end if; return v_approve_string; - exception - when NO_DATA_FOUND then - return null; + +end;' language 'plpgsql'; - end approve_string; +-- function count_finished_tasks +create function content_workflow__count_finished_tasks (integer) +returns integer as ' +declare + p_case_id alias for $1; +begin - function count_finished_tasks( - case_id in wf_cases.case_id%TYPE - ) return integer - is - v_already_finished_tasks integer; - begin - - select - count(before.place_key) into v_already_finished_tasks + return + count(before.place_key) from ( select p.sort_order from wf_tasks t, wf_places p where - t.workflow_key = 'publishing_wf' + t.workflow_key = ''publishing_wf'' and t.workflow_key = p.workflow_key and - p.place_key = content_workflow.get_this_place( t.transition_key ) + p.place_key = content_workflow__get_this_place( t.transition_key ) and -- active task - t.state in ('enabled', 'started') + t.state in (''enabled'', ''started'') and - t.case_id = count_finished_tasks.case_id + t.case_id = p_case_id ) here, wf_places before where - before.workflow_key = 'publishing_wf' + before.workflow_key = ''publishing_wf'' and -- earlier transitions (tasks that have already been completed) before.sort_order < here.sort_order; - return v_already_finished_tasks; - exception - when NO_DATA_FOUND then - return 0; - end count_finished_tasks; +end;' language 'plpgsql'; +-- function count_unfinished_tasks +create function content_workflow__count_unfinished_tasks (integer) +returns integer as ' +declare + p_case_id alias for $1; + v_unfinished_tasks integer; + v_already_finished_tasks integer; + v_all_tasks integer; +begin - function count_unfinished_tasks( - case_id in wf_cases.case_id%TYPE - ) return integer - is - v_unfinished_tasks integer; - v_already_finished_tasks integer; - v_all_tasks integer; - begin - select count(transition_key) into v_all_tasks from wf_transitions where - workflow_key = 'publishing_wf'; + workflow_key = ''publishing_wf''; - v_already_finished_tasks := content_workflow.count_finished_tasks( - case_id => count_unfinished_tasks.case_id + v_already_finished_tasks := content_workflow__count_finished_tasks( + p_case_id ); v_unfinished_tasks := v_all_tasks - v_already_finished_tasks; + return coalesce(v_unfinished_tasks,0); + +end;' language 'plpgsql'; - return v_unfinished_tasks; - exception - when NO_DATA_FOUND then - return 0; - end count_unfinished_tasks; +-- function is_active +create function content_workflow__is_active (integer,varchar) +returns char as ' +declare + p_case_id alias for $1; + p_transition_key alias for $2; +begin - function is_active ( - case_id in wf_cases.case_id%TYPE, - transition_key in wf_transitions.transition_key%TYPE - ) return char - is - v_unfinished_count integer; - begin - - select - count(task_id) into v_unfinished_count + return + count(task_id) > 0 from wf_tasks where - transition_key = is_active.transition_key + transition_key = p_transition_key and - case_id = is_active.case_id + case_id = p_case_id and - state in ('started','enabled'); + state in (''started'',''enabled''); + +end;' language 'plpgsql'; - if v_unfinished_count > 0 then - return 't'; - else - return 'f'; - end if; - end is_active; +-- function is_finished +create function content_workflow__is_finished (integer,varchar) +returns char as ' +declare + p_case_id alias for $1; + p_transition_key alias for $2; + v_finished_task record; +begin - function is_finished ( - case_id in wf_cases.case_id%TYPE, - transition_key in wf_transitions.transition_key%TYPE - ) return char - is - - cursor c_already_finished_tasks is + for v_finished_task in select trans.transition_key from @@ -1232,172 +1161,157 @@ here.workflow_key = trans.workflow_key and -- the task belongs to this case - t.case_id = is_finished.case_id + t.case_id = p_.case_id and -- the task is active 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) + here.place_key = content_workflow__get_this_place(t.transition_key) and -- there is the place we're checking if it's finished - there.place_key = content_workflow.get_this_place( + there.place_key = content_workflow__get_this_place( trans.transition_key) and -- there needs to be done before here -- (sort order determines task order) - there.sort_order < here.sort_order; - - begin - - for v_finished_task in c_already_finished_tasks loop + there.sort_order < here.sort_order + LOOP -- check if this task has already been finished - if is_finished.transition_key = v_finished_task.transition_key then - return 't'; + if p_transition_key = v_finished_task.transition_key then + return ''t''; end if; end loop; - return 'f'; - end is_finished; + return ''f''; + +end;' language 'plpgsql'; - function is_checked_out ( - case_id in wf_cases.case_id%TYPE, - transition_key in wf_transitions.transition_key%TYPE - ) return char - is - v_checkout_count integer; - begin - select - count(*) into v_checkout_count +-- function is_checked_out +create function content_workflow__is_checked_out (integer,varchar) +returns char as ' +declare + p_case_id alias for $1; + p_transition_key alias for $2; +begin + return + count(*) > 0 from wf_tasks t where - workflow_key = 'publishing_wf' + workflow_key = ''publishing_wf'' and - case_id = is_checked_out.case_id + case_id = p_case_id and - transition_key = is_checked_out.transition_key + transition_key = p_transition_key and - state = 'started'; + state = ''started''; - if v_checkout_count > 0 then - return 't'; - else - return 'f'; - end if; +end;' language 'plpgsql'; - end is_checked_out; - - function is_checked_out ( - case_id in wf_cases.case_id%TYPE, - transition_key in wf_transitions.transition_key%TYPE, - user_id in wf_tasks.holding_user%TYPE - ) return char - is - v_checkout_count integer; - begin +-- function is_checked_out +create function content_workflow__is_checked_out (integer,varchar,integer) +returns char as ' +declare + p_case_id alias for $1; + p_transition_key alias for $2; + p_user_id alias for $3; +begin select - count(task_id) into v_checkout_count + count(task_id) > 0 from wf_tasks t where - workflow_key = 'publishing_wf' + workflow_key = ''publishing_wf'' and - case_id = is_checked_out.case_id + case_id = p_case_id and - transition_key = is_checked_out.transition_key + transition_key = p_transition_key and - state = 'started' + state = ''started'' and - holding_user = is_checked_out.user_id; + holding_user = p_user_id; + +end;' language 'plpgsql'; - if v_checkout_count > 0 then - return 't'; - else - return 'f'; - end if; - end is_checked_out; +-- function get_status +create function content_workflow__get_status (integer,varchar) +returns varchar as ' +declare + p_case_id alias for $1; + p_transition_key alias for $2; + v_status varchar(1000); + v_state wf_tasks.state%TYPE; + v_holding_user wf_tasks.holding_user%TYPE; + v_hold_timeout wf_tasks.hold_timeout%TYPE; + v_enabled_timestamp wf_tasks.enabled_date%TYPE; + v_started_timestamp wf_tasks.started_date%TYPE; +begin - - function get_status( - case_id in wf_cases.case_id%TYPE, - transition_key in wf_transitions.transition_key%TYPE - ) return varchar2 - is - v_status varchar(1000); - v_state wf_tasks.state%TYPE; - v_holding_user wf_tasks.holding_user%TYPE; - v_hold_timeout wf_tasks.hold_timeout%TYPE; - v_enabled_date wf_tasks.enabled_date%TYPE; - v_started_date wf_tasks.started_date%TYPE; - begin - select state, holding_user, hold_timeout, enabled_date, started_date into v_state, v_holding_user, v_hold_timeout, v_enabled_date, v_started_date from wf_tasks where - transition_key = get_status.transition_key + transition_key = p_transition_key and - case_id = get_status.case_id + case_id = p_case_id and - state in ('enabled','started'); + state in (''enabled'',''started''); - v_status := '
| Activated on ' || - to_char(v_enabled_date,'Mon. DD, YYYY HH24:MI:SS') || - ' |
| Activated on '' || + to_char(v_enabled_date,''Mon. DD, YYYY HH24:MI:SS'') || + '' |
| Checked Out by ' || person.name(v_holding_user) || - ' on ' || to_char(v_started_date,'Mon. DD, YYYY HH24:MI:SS') || - ' until ' || - to_char(v_hold_timeout,'Mon. DD, YYYY') || ' |
| Checked Out by '' || person__name(v_holding_user) || + '' on '' || to_char(v_started_date,''Mon. DD, YYYY HH24:MI:SS'') || + '' until '' || + to_char(v_hold_timeout,''Mon. DD, YYYY'') || '' |