Database Structure

Workflow Studio saves and loads data into a database. It is database-based. You must create the needed tables and fields in your database in order to use Workflow Studio.

Workflow Studio distribution includes some SQL scripts for easy creation of tables in the database. The scripts are in the dbscripts folder. The scripts provide so far are:

  • wsSQLServer.sql: for Microsoft SQL Server databases;

  • wsOracle.sql: for Oracle databases;

  • wsFirebird.sql: for Firebird/Interbase databases.

As an alternative, you can also manually create the tables and fields in your database, just use the same underlying database structure.

Underlying Database Structure

Below is the structure for the tables used by Workflow Studio.

The field types are described for Microsoft SQL Server, but you can just translate it for the database server you want.

For example, Image field is a blob field, while Text is a blob or memo field.

All Datetime fields must contain both date and time parts (not only date).

Table wsattachment

Field Data type Options
id Int Primary key, Required
workkey Int
createdon Datetime
filecontent Image
objecttype Int

Table wstaskinstance

Field Data type Options
id Int Primary key, Required
task Text
createdon Datetime
userid VarChar(50)
comments Text
name VarChar(50)
subject VarChar(50)
description Text
workflowinstancekey Int
workflowdefinitionkey Int
completed VarChar(1)
modifiedon Datetime
modifieduserid VarChar(50)
taskversion Int Default 0

Table wsworkflowdefinition

Field Data type Options
id Int Primary key, Required
workflow Text
name VarChar(255)

Table wsworkflowinstance

Field Data type Options
id Int Primary key, Required
workflow Text
workflowdefinitionkey Int
createdon Datetime
modifiedon Datetime
finishedon Datetime
nextruntime Datetime
instanceversion Int Default 0

Table wstasklog

Field Data type Options
taskinstancekey Int Primary key, Required
eventdate Datetime Primary key, Required
operation VarChar(1) Primary key, Required
userid VarChar(50)
info VarChar(100)
info2 VarChar(100)

Table wsconfig

Field Data type Options
id Int Primary key, Required
dbversion Int

Upgrading database from previous versions

Some Workflow versions require updates in the database structure. Last versions to require it were 2.4 and 1.5. If you have the database structure created for Workflow Studio in versions prior to any of those versions, some changes in structure are required to keep the component working properly.

Workflow Studio distribution includes some SQL scripts for easy updating the workflow tables in database, located in in dbscripts folder. The scripts provided so far are:

  • To update from versions lower then 1.5:

    • wsSQLServerUpdate.sql: for Microsoft SQL Server databases;

    • wsOracleUpdate.sql: for Oracle databases;

    • wsFirebirdUpdate.sql: for Firebird/Interbase databases.

  • To update from versions lower then 2.4:

    • wsSQLServerUpdate2_4.sql: for Microsoft SQL Server databases;

    • wsOracleUpdate2_4.sql: for Oracle databases;

    • wsFirebirdUpdate2_4.sql: for Firebird/Interbase databases.

Following are listed the database changes required to upgrade Workflow Studio version.

The field types are described for Microsoft SQL Server, but you can just translate it for the database server you want.

From previous versions to version 2.4

  1. New field in table wsworkflowinstance: instanceversion Int Default 0.

  2. New field in table wstaskinstance: taskversion Int Default 0.

  3. New table wsconfig with new record with field values id = 1 and dbversion = 1.

From previous versions to version 1.5

  1. New field in table wsworkflowinstance: nextruntime Datetime.