Custom Workflow Architecture: Examining Workflow Data

!!ROUGH DRAFT!!

This post expands on a previous post: Custom Workflow Architecture: Workflow Steps and Transitions

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

Database Tables

Records

SELECT *
FROM Workflow;

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

SELECT *
FROM WorkflowStep;

ID	WORKFLOWID	NAME	DESCRIPTION
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).

SELECT *
FROM WorkflowTrans;

ID	WORKFLOWID	FROMSTEPID	TOSTEPID	NAME	DESCRIPTION
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.

Queries

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,
LEVEL
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
ORDER BY LEVEL;

WORKFLOWNAME	STEPNAME	TRANSNAME	LEVEL
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.