Blackboard Database Schema

A database design is created that enables full auditing similar to the database design implemented in date tracking systems for oracle databases [2]. The next few paragraphs discuss the database design structure in more detail to accommodate full data auditing.

Table A.1: The schema for the table wewe_workflows.

Table “wewe_workflows”

field type(size) default/options/indexes/constraints workflow_id serial auto incrementing, primary key, not null

title varchar(250) not null

short_description varchar(500) not null description text

timestamp timestamp currentdatetime + timezone created_by varchar(50) not null default “wewe” workflow_status integer default

Blackboard

HashMap<String [workflow ID], Workflow> Workflows = new ConcurrentHashMap(); StagingZone stageZone = new StagingZone();

Workflow

HashMap<String [GUID], WorkflowProcessInstance> WorkflowProcessInstances = new ConcurrentHashMap(); Integer WorkflowID; String Name; String Description; Integer createdBy; TimeStamp timestamp;

Staging Zone

HashMap<String [GUID], WorkflowProcessInstance> WorkflowProcessInstances = new ConcurrentHashMap();

When a workflow designer creates a new workflow, Table A.1 is used to save its metadata. The workflow ID is used as a foreign key for all other tables. All information under the umbrella of this workflow (WF process instances, specialists, forms information etc) will be somehow linked to this workflow ID. Table A.2 records all the workflow process instance objects.

Table A.2: The schema for the table wewe_process_instances.

Table “wewe_process_instances”

field type(size) default/options/indexes/constraints

guid guid primary key, not null

workflow_id integer foreign key (wewe_workflows:workflow_id) timestamp timestamp currentdatetime + timezone

process_status integer not null

When a new workflow process instance object is created on the blackboard, the object itself inserts a record into Table A.2. Once the record is inserted, the object will retrieve its GUID from this newly inserted record. This table also records the status of each workflow process instance as an integer value. Table A.3 lists the meanings of each integer value.

Table A.3: A list of all workflow process instance statuses and their descriptions.

Status Number Meaning Description

1 Active WF process instance is running tasks and activities (Specialist is currently interacting)

2 Idle WF process instance is running but no tasks and activities are currently being executed (No specialists interaction)

3 Completed WF process instance has been completed 4 Paused WF process instance has been paused by user

5 Inactive (new) WF process instance is in the process of being setup on the blackboard

6 Inactive WF process instance has been cancelled by user 7 Halted due to error WF process instance has been paused due to an error 8 Cancelled WF process instance has been cancelled.

The status of a workflow process instance can change depending on the circumstances. The workflow instance object immediately updates its status in Table A.2 when a change in status occurs.

WF process constants are created either when a WF process instance is spawned or when specialists add constants into a WF process instance. When a constant is added to a WF process instance object, it immediately saves the details of that constant to Table A.4.

Table A.4: The schema for the table wewe_wf_process_constants.

Table “wewe_wf_process_constants”

field type(size) default/options/indexes/constraints const_id serial auto increment, primary key, not null

workflow_id integer foreign key (wewe_workflows:workflow_id) guid guid foreign key (wewe_process_instances:guid) constant_name varchar(100) not null constraint

constant_value text not null constraint constant_type varchar(100) not null constraint

Once a constant has been created in a workflow process instance object, it cannot be changed or can be removed and will be perennial until the completion of the workflow process or when a workflow process instance is removed from the blackboard. So no auditing of constants is required. Only a simple constraint is added that restricts more than one constant of the same name to be created within a workflow process instance.

WF process variables can be added, altered and removed by specialists by extension WF tasks and activities at will and at anytime within the lifetime of a workflow process instance, and by doing so, advance the WF process’s path within the boundaries of the workflow. All changes to variables must be recorded for auditing purposes. Two tables with their schema described in Tables A.5 and A.6 are used track changes to variables.

Table A.5: The schema for the table wewe_wf_process_iterations.

Table “wewe_wf_process_iterations”

field type(size) default/options/indexes/constraints iteration_id serial auto increment, Primary Key, Not Null specialist varchar(100) NOT NULL constraint

timestamp timestamp currentdatetime + timezone

workflow_id int Foreign Key (wewe_workflows:workflow_id) guid guid Foreign Key (wewe_process_instances:guid)

Table A.6: The schema for the table wewe_wf_process_variables.

Table “wewe_wf_process_variables”

field type(size) default/options/indexes/constraints var_id serial auto increment, primary key, not null

workflow_id integer foreign key (wewe_workflows:workflow_id) guid guid foreign key (wewe_process_instances:guid)

iteration_id integer foreign key (wewe_wf_process_iterations:iteration_id) var_name varchar(100) not null constraint

var_value text no constraints or defaults var_type varchar(100) not null constraint

timestamp timestamp currentdatetime + timezone modified_by varchar(100) not null constraint

is_active small integer not null constraint

Table A.5 records all the interactions between specialists and the workflow process instance object whenever a variable is created, altered or removed. Each specialist interaction can be termed as a workflow process iteration since a specialist interacting with the WF process object generally means that the WF process instance has advanced (actions are being performed) within the variables of the workflow definition. Every time a variable is created, altered or removed, the workflow process instance inserts a record in Table A.6 with a reference of the iteration (from Table A.5) of the workflow process instance. New iteration and variable records are added when a variable is altered. If a variable is removed from a workflow process instance, the ‘is_active’ field is updated to false instead of removing records pertaining to that variable.

In the event of a system reload, WEWE is able to use the data stored into these set of tables to completely recreate the blackboard and revive all uncompleted workflows at their point of stoppage. All workflow process iterations are stored in the logs component of the workflow instance. This can be used by administrators who want to rewind or fast forward workflow processes to any workflow state.