Externalizing Workflow Data

By default, LogicNets stores workflow data in the local SQLite database of your project or installation. However, you can also store that same data to external databases by configuring the workflow data location in the project settings tab of your application. You enter the database connection string and point it to an ODBC data source.

Supported Databases and Database Schemas

The LogicNets platform supports the following external databases: Oracle, MS SQL Server, and MySQL. The following are the different database schemas for the workflow tables:

LogicNets Workflow Tables - Oracle

CREATE TABLE GENERIC_TASK
("ID" NUMBER,
"USER_ID" VARCHAR2(255),
"STATUS" VARCHAR2(255),
"TASK" VARCHAR2(255),
"ROLE" VARCHAR2(255),
"DISPLAY" VARCHAR2(25),
"LOCKED" VARCHAR2(25),
"USER_GROUP" VARCHAR2(255),
"TRACK" VARCHAR2(25),
"TASK_NET" VARCHAR2(255),
"SES" VARCHAR2(255),
"PLANNED_END_STAMP" NUMBER,
"STAGE" NUMBER,
"PLANNED_START_STAMP" NUMBER,
"ACTUAL_START_STAMP" NUMBER,
"ACTUAL_END_STAMP" NUMBER,
"WF_NODE_STATUS" VARCHAR2(255),
"PRIORITY" VARCHAR2(255),
"OPTIONS" CLOB,
"TYPE" VARCHAR2(255),
"SUBJECT_LINE" VARCHAR2(4000),
"CREATED" NUMBER,
"WF_NODE_ID" NUMBER,
"SELECTED_OPTION" VARCHAR2(255),
"PREVIOUS_SELECTED_OPTION" VARCHAR2(255),
"WF_NET" VARCHAR2(255),
"EXPIRATION_DATE" NUMBER,
"LAST_UPDATED" NUMBER,
"OBJECT_ID" NUMBER,
"OBJECT_NAME" VARCHAR2(255),
"CREATED_BY" VARCHAR2(4000),
"CMT" VARCHAR2(4000),
"BUDDY_USER_ID" VARCHAR2(255),
"PARAMETERS" CLOB,
"LOAD_NET" VARCHAR2(255),
"SAVE_NET" VARCHAR2(255),
"WORKFLOW_INSTANCE" NUMBER,
"OBJECT_TYPE" VARCHAR2(255),
"WORKFLOW_ROOT" NUMBER,
"ASSIGNMENTID" VARCHAR2(255),
"ACTIONS" CLOB,
"PRE_PROCESS_NET" VARCHAR2(4000),
"POST_PROCESS_NET" VARCHAR2(4000),
PRIMARY KEY (ID)
)

CREATE TABLE GENERIC_TASK_STORAGE
("ID" NUMBER,
"TYPE" VARCHAR2(4000),
"DATA" CLOB,
"WORKFLOW" VARCHAR2(4000),
"WORKFLOW_ID" VARCHAR2(4000),
PRIMARY KEY (ID)
)

CREATE TABLE GENERIC_TASK_TAGS
("ID" NUMBER,
"TASK_ID" NUMBER,
"VALUE" VARCHAR2(255),
"TYPE" VARCHAR2(255),
PRIMARY KEY (ID)
)

CREATE TABLE GENERIC_WORKFLOW
("ID" NUMBER,
"STATE" VARCHAR2(4000),
"PARENT" NUMBER,
"CREATED_USER" VARCHAR2(4000),
"CREATED_DATE" NUMBER,
"FINISHED_DATE" NUMBER,
"ORIGIN" VARCHAR2(4000),
PRIMARY KEY (ID)
)

CREATE TABLE GENERIC_CHECKER_WORKFLOW
("ID" NUMBER,
"CHECKER_ID" NUMBER,
"WORKFLOW_ID" NUMBER,
PRIMARY KEY (ID)
)

LogicNets Workflow Tables - Microsoft SQL Server

CREATE TABLE generic_task
([id] BIGINT,
[user_id] NVARCHAR(MAX),
[status] NVARCHAR(MAX),
[task] NVARCHAR(MAX),
[role] NVARCHAR(MAX),
[display] NVARCHAR(MAX),
[locked] NVARCHAR(MAX),
[user_group] NVARCHAR(MAX),
[track] NVARCHAR(MAX),
[task_net] NVARCHAR(MAX),
[SES] NVARCHAR(MAX),
[planned_end_stamp] FLOAT,
[stage] FLOAT,
[planned_start_stamp] FLOAT,
[actual_start_stamp] FLOAT,
[actual_end_stamp] FLOAT,
[wf_node_status] NVARCHAR(MAX),
[priority] NVARCHAR(MAX),
[options] NVARCHAR(MAX),
[type] NVARCHAR(MAX),
[subject_line] NVARCHAR(MAX),
[created] FLOAT,
[wf_node_id] FLOAT,
[selected_option] NVARCHAR(MAX),
[previous_selected_option] NVARCHAR(MAX),
[wf_net] NVARCHAR(MAX),
[expiration_date] FLOAT,
[last_updated] FLOAT,
[object_id] FLOAT,
[object_name] NVARCHAR(MAX),
[created_by] NVARCHAR(MAX),
[CMT] NVARCHAR(MAX),
[buddy_user_id] NVARCHAR(MAX),
[parameters] NVARCHAR(MAX),
[load_net] NVARCHAR(MAX),
[save_net] NVARCHAR(MAX),
[workflow_instance] FLOAT,
[object_type] NVARCHAR(MAX),
[workflow_root] FLOAT,
[assignmentId] NVARCHAR(MAX),
[actions] NVARCHAR(MAX),
[pre_process_net] NVARCHAR(MAX),
[post_process_net] NVARCHAR(MAX),
PRIMARY KEY (id)
)

CREATE TABLE generic_task_storage
([id] BIGINT,
[type] NVARCHAR(MAX),
[data] NVARCHAR(MAX),
[workflow] NVARCHAR(MAX),
[workflow_id] NVARCHAR(MAX),
PRIMARY KEY (id)
)

CREATE TABLE generic_task_tags
([id] BIGINT,
[task_id] FLOAT,
[value] NVARCHAR(MAX),
[type] NVARCHAR(MAX),
PRIMARY KEY (id)
)

CREATE TABLE generic_workflow
([id] BIGINT,
[state] NVARCHAR(MAX),
[parent] FLOAT,
[created_user] NVARCHAR(MAX),
[created_date] FLOAT,
[finished_date] FLOAT,
[origin] NVARCHAR(MAX),
PRIMARY KEY (id)
)

CREATE TABLE generic_checker_workflow
([id] BIGINT,
[checker_id] FLOAT,
[workflow_id] FLOAT,
PRIMARY KEY (id)
)

LogicNets Workflow Tables - MySQL

CREATE TABLE generic_task
(`id` BIGINT,
`user_id` varchar(255),
`status` varchar(255),
`task` varchar(255),
`role` varchar(255),
`display` varchar(25),
`locked` varchar(25),
`user_group` varchar(255),
`track` varchar(25),
`task_net` varchar(255),
`SES` varchar(255),
`planned_end_stamp` FLOAT,
`stage` FLOAT,
`planned_start_stamp` FLOAT,
`actual_start_stamp` FLOAT,
`actual_end_stamp` FLOAT,
`wf_node_status` varchar(255),
`priority` varchar(255),
`options` varchar(4000),
`type` varchar(255),
`subject_line` varchar(4000),
`created` FLOAT,
`wf_node_id` FLOAT,
`selected_option` varchar(255),
`previous_selected_option` varchar(255),
`wf_net` varchar(255),
`expiration_date` FLOAT,
`last_updated` FLOAT,
`object_id` FLOAT,
`object_name` varchar(255),
`created_by` varchar(4000),
`CMT` varchar(4000),
`buddy_user_id` varchar(255),
`parameters` varchar(4000),
`load_net` varchar(255),
`save_net` varchar(255),
`workflow_instance` FLOAT,
`object_type` varchar(255),
`workflow_root` FLOAT,
`assignmentId` varchar(255),
`actions` varchar(4000),
`pre_process_net` varchar(4000),
`post_process_net` varchar(4000),
PRIMARY KEY (`id`)
);

CREATE TABLE generic_task_storage
(`id` BIGINT,
`type` varchar(4000),
`data` varchar(4000),
`workflow` varchar(4000),
`workflow_id` varchar(4000),
PRIMARY KEY (`id`)
);

CREATE TABLE generic_task_tags
(`id` BIGINT,
`task_id` FLOAT,
`value` varchar(255),
`type` varchar(255),
PRIMARY KEY (`id`)
);

CREATE TABLE generic_workflow
(`id` BIGINT,
`state` varchar(4000),
`parent` FLOAT,
`created_user` varchar(4000),
`created_date` FLOAT,
`finished_date` FLOAT,
`origin` varchar(4000),
PRIMARY KEY (`id`)
);

CREATE TABLE generic_checker_workflow
(`id` BIGINT,
`checker_id` FLOAT,
`workflow_id` FLOAT,
PRIMARY KEY (`id`)
)

Overriding Table Names

If your workflow tables have different names than those above, you must override the workflow settings. You can do this by changing the workflow settings in the settings.cfg file of your logicnets installation or by changing it inside your application using the set data object value part. In the example below, we set the name of the generic_task_table to TASK_TABLE.

_settings = {
    workflowlib_settings = {
        generic_task_storage_table = "",
        generic_task_table = "",
        generic_task_tags_table = "",
        generic_workflow_table = "",
        generic_checker_workflow_table = ""
    }
}