Tuesday, July 27, 2010

Custom Workflow Architecture: Examining Workflow Data


From the previous post, we should already have the following database tables created and populated with the data shown:

Database Tables


FROM Workflow;

1 Vacation Request Workflow A workflow to allow employees to request vacation time.

FROM WorkflowStep;

1 1 Workflow Instance Instantiated This step marks the point where a workflow instance was created.
2 1 Request Creation/Submission A vacation request is created and submitted for approval at this step.
3 1 Manager Review/Approval The vacation request is reviewed and approved/rejected by a manager at this step.
4 1 Workflow Completed The vacation request was finalized (Either approved or rejected).

FROM WorkflowTrans;

1 1 1 2 Instantiate Workflow The initial transition that fires off for every new workflow.
2 1 2 3 Submit For Manager Approval Submit the request for approval.
3 1 3 4 Approved by Manager Approve Request.


Now lets start extracting some more meaningful data. The above queries give us raw dumps of all our tables, so we can at least see all our workflows, steps and transitions. But it does not give us the details our users and managers will want to see. So here are some common questions and solutions for querying data from our workflow data repository.

I need to see a list of all steps ordered by their logical flow within the workflow, starting with the step "Workflow Instance Instantiated" and ending with whatever the final workflow step happens to be, for this workflow instance. How can I do that?

The trick is to know what your starting transition is. Our transitions map the workflow from one step to another, so if we know what the first step is, we can follow our transitions to the end point. Writing the SQL to walk workflow transitions is trickier than it sounds. Its really a parent/child relationship. In my examples here, I use the built in oracle functions for traversing hierarchical relationships - START WITH and CONNECT BY.

In these examples, for simplicity, I'm hardcoding the starting step ID into my queries. In a more complete example, I would want to store the starting step ID somewhere in the database as part of the workflow scheme. For example, we could track the start step ID as part of the workflow record by creating a new column: Workflow.StartStepID.

SELECT w.Name AS WorkflowName,
ws.Name AS StepName,
wt.Name AS TransName,
FROM Workflow w
INNER JOIN WorkflowStep ws ON ws.WorkflowID = w.ID
INNER JOIN WorkflowTrans wt ON wt.FromStepID = ws.ID AND wt.WorkflowID = w.ID
WHERE w.ID = 1
START WITH wt.FromStepID = 1
CONNECT BY PRIOR wt.ToStepID = wt.FromStepID

Vacation Request Workflow Workflow Instance Instantiated Instantiate Workflow 1
Vacation Request Workflow Request Creation/Submission Submit For Manager Approval 2
Vacation Request Workflow Manager Review/Approval Approved by Manager 3

I use the START BY statement to tell oracle what the starting point is for traversing the workflow I've designed. The table that represents the workflow hierarchy is really only the WorkflowTrans table, where the FromStepID and ToStepId represent our parent/child keys. So, we tell oracle to walk the transition hierarchy, and we use oracle's built in LEVEL keyword to sort our results correctly. This gives us a tabular representation of how our workflow is linked by transitions.

No comments: