postgres_swis.sql

一个工作流设计及定义的系统,可以直接与数据库结合进行系统工作流程的定义及应用.

-- $Id: postgres_swis.sql,v 1.2 2005/06/25 08:31:43 jmettraux Exp $

-- 
-- creating the tables necessary for the 'swis' in postgresql 
--
-- This SQL script was written by Marc 'whychung' Chung 2005/03/09
--

-- SWIS means 'Sql WorkItem Store'


create table owfe_workitem
(
    workitem_id varchar(13) not null primary key,
    participant_name varchar(255) not null,

    dispatch_time varchar(100) not null,
    last_modified varchar(100) not null
	-- dates in one of the ISO formats
);

create index owfe_workitem_workitem_id_index on owfe_workitem(workitem_id);

create table owfe_flow_expression_id
(
    workitem_id varchar(13) not null,
    stack_index int not null,
	-- when this value is set to 0, it means that this is 
	-- the last expression id
	-- when > 0 it means the expression id belongs to the flowstack

    engine_id varchar(255) not null,
    initial_engine_id varchar(255) not null,
    wfd_url varchar(255) not null,
    wfd_name varchar(255) not null,
    wfd_revision varchar(255) not null,
    wf_instance_id varchar(13) not null,
    expression_name varchar(255) not null,
    expression_id int not null,

    primary key (workitem_id, stack_index)
);

create index owfe_flow_expression_id_workitem_id_index on owfe_flow_expression_id(workitem_id);

create table owfe_attribute
(
    workitem_id varchar(13) not null,
    id varchar(13) not null,
    parent_id varchar(13),
	-- the id of the parent attribute
    a_type varchar(100),
	-- 'smap', 'list', 'map', 'string', 'boolean', 'integer', 
	-- 'long'  or 'double'
	-- or NULL if it's just an entry
    a_value text,
    
    primary key (workitem_id, id)
);

create index owfe_attribute_id_index on owfe_attribute(id);

--
-- history of the workitem
--

create table owfe_history_item
(
    workitem_id varchar(13) not null,
    id int not null,
    hi_date varchar(100) not null,
    author varchar(255) not null,
    host varchar(255) not null,
    hi_text text not null,
    wfd_name varchar(255) not null,
    wfd_revision varchar(255) not null,
    wf_instance_id varchar(13) not null,

    primary key (workitem_id, id)
);

create index owfe_history_item_id_index on owfe_history_item(id);

--
-- filter
--

create table owfe_filter
(
    workitem_id varchar(13) not null,
    name varchar(255) not null,
    f_type char(1) not null,
    add_allowed char(1) not null,
    remove_allowed char(1) not null,

    primary key (workitem_id)
	-- there is 1! filter per workitem
);

create index owfe_filter_workitem_id_index on owfe_filter(workitem_id);

create table owfe_filter_entry
(
    workitem_id varchar(13) not null,
    regex varchar(255) not null,
    permissions char(3) not null,
    attributeType varchar(255),

    primary key (workitem_id, regex)
);

create index owfe_filter_entry_workitem_id on owfe_filter_entry(workitem_id);


--
-- the action table
--

create table owfe_action
(
    workitem_id varchar(13) not null,
    action varchar(255) not null,

    primary key (workitem_id)
);

create index owfe_action_workitem_id on owfe_action(workitem_id);