invalid command name "ns_adp_include"
while executing
"ns_adp_include header.adp "
invoked from within chunk: 2 of adp: /home/dvr/web/dvr/cmd/workflow.adp
The first question to ask when designing a workflow is: How restrictive do we want to be? If a news story typically goes through 4 steps -- writing, editing, layout, and approval -- does every story need to go through these four step? Do these steps need to be completed in a specified order?
The least-restrictive workflow is the one described earlier, which is practically no workflow at all: the revision's state is used to send information to others working on the item, and users are allowed to move between any two states (except for the 'approved' state, which is reserved for editors). When a writer finishes a story, he puts it in the 'written' state and the editor knows it is his turn to look at it. If the designer has completed marking up a story (leaving it in the 'formatted' state) and the writer wants to add a few more lines, he can check it out, putting it in the 'writing' state, make his changes, then check it back in, putting it in the 'written' state so the editor knows to review the changes.
There are a few ways we can add more structure:
A "flexible" workflow is one where the steps can be completed in any order, and it's possible to move between any two states.
Here are a few flexible workflow configurations that might make sense for a publishing organization, from least restrictive to most restrictive:
The first thing to notice is that the user interface is the same regardless of which design we choose. If the system determines you're allowed to check out a story, you'll get the "check out" button at the top of the item's page. Before the story is checked out to you, you're forced to choose a state from a pull-down menu.
The data model will be different for each of these designs. The design challenge for workflow is the same as permissions -- creating a consistent interface that will look the same regardless of the data model sitting beneath it. And the solution is the same: create a collection of views and query only against those views, never against the underlying tables. To change from one workflow design to another, create the new tables and redefine the views.
For permissions, we needed the system to answer two questions: "Is user X allowed to do Y on item Z", and "Is user X allowed to do Y in department Z". For workflow, we have two questions:
The first question will be answered by the view cm_v_items_users_can_checkout, which contains two columns: user_id and item_id. A user will be able to check out the item if a row exists with that user_id and item_id:
select count(*) from cm_v_items_users_can_checkout where user_id = :user_id and item_id = :item_id
The second question will be answered by the view cm_v_allowed_rev_state_usr_map, which contains every allowed combination of revision_id, user_id, and state_id. If the user is checking in a story and we need to generate the selectlist with the states available to this user, the query is just:
select state_id ... from cm_v_allowed_rev_state_usr_map where user_id = :user_id and revision_id = :revision_id
If the system needs to verify this user is allowed to choose a state, we do a count:
select count(*) from cm_v_allowed_rev_state_usr_map where user_id = :user_id and revision_id = :revision_id and state_key = :state_key
If the count is greater than zero, the user is allowed.
Implementing design #1
Three tables are needed:
-- The list of all states
create table cm_workflow_states (
state_key varchar(20) primary key,
state_name varchar(100) not null,
editor_state_p number(1) default 0 not null
check(editor_state_p in (1, 0)),
action_state_p number(1) default 0 not null
check(action_state_p in (1, 0))
);
-- which states are possible for each content type
create table cm_workflow_state_type_map (
state_key varchar(20) references cm_workflow_states,
type_key varchar(20) references cm_types,
primary key(state_key, type_key),
sort_key integer not null,
-- be sure the sort_key is unique amoung states for that
-- content type
unique(type_key, sort_key)
);
-- Which users are staff members, and which of those are editors
create table cm_department_members (
user_id integer references users,
department_id integer references cm_departments,
primary key(user_id, department_id),
editor_p number(1) default 0 not null
);
To simplify cm_v_allowed_rev_state_usr_map, we create a helper view that returns all possible revision/state_key combinations:
create or replace view cm_v_possible_revision_states as
select
r.revision_id,
i.department_id,
t.state_key,
t.sort_key
from cm_revisions r, cm_items i, cm_workflow_state_type_map t
where r.item_id = i.item_id
and i.type_key = t.type_key;
Then cm_v_allowed_rev_state_usr_map is:
create or replace view cm_v_allowed_rev_state_usr_map as
select
unique r.revision_id,
s.state_key,
m.user_id,
r.sort_key
from cm_v_possible_revision_states r, cm_workflow_states s,
cm_v_department_members m
where r.state_key = s.state_key
and r.department_id = m.department_id
and (m.editor_p = 1 or s.editor_state_p = 0);
An item can be checked out by anyone in the item's department, so cm_v_items_users_can_checkout is:
create or replace view cm_v_items_users_can_checkout as
select
unique i.item_id,
m.user_id
from cm_v_department_members m, cm_items i
where m.department_id = i.department_id;
Implementing design #2
For #2, the list of possible states is determined by the person's position on staff.
First we need a place to store the list of roles and which users fill which roles:
create table cm_staff_roles ( role_key varchar(20) primary key, role_name varchar(100) not null ); create table cm_department_role_user_map ( department_id varchar(20) references cm_departments, role_key varchar(20) references cm_staff_roles, user_id integer references users, primary key(department_id, role_key, user_id) );
cm_department_role_user_map replaces cm_department_members in design #1.
We still use cm_workflow_states but drop editor_state_p in favor of a many-to-many mapping table:
create table cm_workflow_states (
state_key varchar(20) primary key,
state_name varchar(100) not null,
action_state_p number(1) default 0 not null
check(action_state_p in (1, 0))
);
create table cm_workflow_state_role_map (
role_key varchar(20) references cm_staff_roles,
state_key varchar(20) references cm_states,
primary key(role_key, state_key)
);
After these changes, cm_v_allowed_rev_state_usr_map becomes:
create or replace view cm_v_allowed_rev_state_usr_map as
select
unique r.revision_id,
r.state_id,
t.user_id,
r.sort_key
from cm_v_possible_revision_states r,
cm_workflow_state_role_map t,
cm_department_role_user_map f
where r.state_id = t.state_id
and t.role_key = f.role_key;
and r.department_id = t.department_id
And cm_v_items_users_can_checkout becomes:
create or replace view cm_v_items_users_can_checkout
select
unique i.item_id,
d.user_id
from cm_department_role_user_map d, cm_items r
where d.department_id = r.department_id;
Implementing design #3
#3 is the same as #2 except staff members can only check out an item if the story's last revision is in one of their allowed states. Relative to #2, the only change is in cm_v_items_users_can_checkout:
create or replace view cm_v_items_users_can_checkout
select
c.item_id,
m.user_id
from cm_vlast_content c, cm_v_allowed_rev_state_usr_map m
where c.revision_id = m.revision_id
and c.state_id = m.state_id;
Implementing design #4
#4 is similar to #3 except editors and designers are allowed to use all states, while other positions need to have states granted to them. The simplest solution is to create a view that looks exactly like cm_department_role_user_map, but grants all states to editors and designers:
create or replace view cm_v_department_role_user_map as
select
role_key,
state_key
from cm_department_role_user_map
UNION
select
r.role_key,
s.state_key
from cm_workflow_states s, cm_staff_roles r
where r.role_key in ('editor, 'designer');
Then cm_v_department_role_user_map can be dropped in as a replacement for cm_department_role_user_map:
create or replace view cm_v_allowed_rev_state_usr_map as
select
unique r.revision_id,
r.state_id,
t.user_id,
r.sort_key
from cm_v_possible_revision_states r,
cm_workflow_state_role_map t,
cm_v_department_role_user_map f
where r.state_id = t.state_id
and t.role_key = f.role_key;
and r.department_id = t.department_id
Implementing design #5
For #5, states are granted directly to the user instead of the role. For example, Bob can use 'created', 'writing', and 'written' in one department, and all states in another, and no states in a third.
We drop cm_department_roles, cm_department_role_user_map, and cm_workflow_state_role_map and replace them all with one map between users, states, and departments:
create table cm_department_state_user_map ( user_id integer references users, department_id varchar(20) references cm_departments, state_id varchar(20) references cm_workflow_states, primary key(user_id, department_id, state_id) );
Then cm_v_allowed_rev_state_usr_map becomes:
create or replace view cm_v_allowed_rev_state_usr_map as
select
unique r.revision_id,
r.state_key,
f.user_id,
r.sort_key
from cm_v_possible_revision_states r,
cm_department_state_user_map m
where r.state_key = m.state_key
and r.department_id = m.department_id
To simplify workflow, two procedures are built on top of these views: cm_verify_state_allowed, which throws an error if a user tries to set a state they're not allowed to use, and cm_verify_checkout_allowed which throws an exception if the user isn't allowed to check the item they're trying to check out:
create or replace procedure cm_verify_state_allowed (
--
-- Throw an exception if this user isn't allowed to use this state
--
p_revision_id in integer,
p_user_id in integer,
p_state_key in cm_workflow_states.state_key%type
)
as
v_allowed_p integer;
begin
select count(*) into v_allowed_p
from cm_v_allowed_rev_state_usr_map
where revision_id = p_revision_id
and user_id= p_user_id
and state_key = p_state_key;
if v_allowed_p = 0 then
cm_throw_exception('You''re not allowed to use this state');
end if;
end cm_verify_state_allowed ;
create or replace procedure cm_verify_checkout_allowed (
--
-- Throw an exception if this user isn't allowed to check out
-- this item
--
p_item_id in integer,
p_user_id in integer
)
as
v_allowed_p integer;
begin
select count(*) into v_allowed_p
from cm_v_items_users_can_checkout
where item_id = p_item_id
and user_id = p_user_id;
if v_allowed_p = 0 then
cm_throw_exception('You''re not allowed to checkout this item');
end if;
end;
cm_item_checkout shows both in action:
create or replace function cm_item_checkout (
--
-- Checks out this item to this user and create a new revision
-- with the requested state. The new revision_id is returned.
--
-- If the content is checked out by someone else, or this user
-- isn't allowed to check out the content, throw an exception.
--
p_item_id in integer,
p_user_id in integer,
p_state_key in cm_workflow_states.state_key%type
) return integer
as
v_type_key cm_types.type_key%TYPE;
-- The new revision created
v_revision_id integer;
begin
cm_verify_checkout_allowed (
p_item_id => p_item_id,
p_user_id => p_user_id
);
insert into cm_items_checked_out (
item_id, user_id, checked_out_date)
select p_item_id, p_user_id, sysdate from dual
where not exists (select 1
from cm_items_checked_out
where item_id = p_item_id);
if sql%rowcount = 0 then
-- There's already a row in cm_items_checked_out, which means
-- it's already checked out by someone else
cm_throw_exception('That content is already checked out by someone else');
else
v_revision_id := cm_revision_create (
p_item_id => p_item_id,
p_user_id => p_user_id,
p_state_key => p_state_key
);
-- Once the new revision is created, verify that the state
-- was allowed. If not, this will throw an exception and
-- roll back the new revision.
cm_verify_state_allowed (
p_revision_id => v_revision_id,
p_user_id => p_user_id,
p_state_key => p_state_key
);
end if;
return v_revision_id;
end cm_item_checkout;
A "rigid workflow" is one that requires an item to go through a set of steps in a defined order. You can move to the next or back a step, but you can't jump steps.
In practice, the difference is greater than this. In a flexible workflow, states are used to communicate to others what's happening to the item right now -- that's why we have both 'writing' and 'written'. In a rigid workflow, states are more like checkoff points -- the writer checks off the story as being written, the editor checks off the story as being edited, and the designer says the story has been marked up. If the editor receives the story, either after it's been written or marked up, and isn't happy with the result, he backs it up a step, essentially saying "No, this isn't done being written", or "No, this html doesn't look good enough." The writer or editor makes changes and pushes it forward again.
This change in behavior means the user interface is different. We don't ask for state information when a user checks out or checks in a revision. Since you only want to promote an item to the next step once you're done making changes, the option to move a revision to the next step should be available only when the item isn't checked out. If a writer creates a story, then finishes the writing and checks it in, his interface will show:
| Current state is assigned | Change: [ forward to written ] |
When the writer moves the story forward to the 'written', the editor will see:
| Current state is written | Change: [ forward to edited | back to assigned ] |
Since states are now functioning like check-off points, we drop the "action states" -- 'writing', 'editing', etc. Every step must be reached, but since there won't always be the need for an editor to make changes, and we don't, for example, want to force a revision through the 'editing' state unless editing is actually taking place. The writer leaves the story in the 'written' state, and the editor moves it to 'edited' once all his changes have been made.
Even though a rigid workflow functions very differently, the abstractions used above still work here. The only difference is that the list of possible states is based on the current state; the staff member has the option of moving it back a step, forward a step, or leaving it where it is. To make any of data models above work with a rigid workflow, only cm_v_possible_revision_states needs to be modified:
-- A helper view to make the next view more understandable
-- For a given state and give type, return (1) the current
-- state, (2) the next state, and (3) the previous state. sort_key
-- is offset so that the previous state has a sort_key of -1,
-- the current state has a sort_key of 0, and the next state
-- has a sort_key of 1.
create or replace view cm_v_possible_state_trans as
select
t1.type_id,
t1.state_id,
t2.state_id as next_state_id,
t2.sort_key - t1.sort_key as next_sort_key
from cm_workflow_state_type_map t1, cm_workflow_state_type_map t2
where t1.type_id = t2.type_id
and t2.sort_key in (t1.sort_key, t1.sort_key - 1, t1.sort_key + 1);
create or replace view cm_v_possible_revision_states as
select
r.revision_id,
i.department_id,
s.next_state_id as state_id,
s.next_sort_key as sort_key
from cm_revisions r, cm_items i, cm_v_possible_state_trans s
where r.item_id = i.item_id
and i.type_id = s.type_id
and r.state_id = s.state_id;
invalid command name "ns_adp_include"
while executing
"ns_adp_include footer.adp "
invoked from within chunk: 12 of adp: /home/dvr/web/dvr/cmd/workflow.adp