mysql_swis.sql

来自「日常的办公系统 应用工作流框架等增加员工的基本信息、培训信息、奖罚信息、薪资信息」· SQL 代码 · 共 122 行

-- $Id: mysql_swis.sql,v 1.1 2004/09/08 18:49:47 jlawrenc Exp $

-- 
-- creating the tables necessary for the 'swis' in mysql
--

drop database if exists swis;
create database if not exists swis;

-- SWIS means 'Sql WorkItem Store'

use swis;

-- workitem_id bigint primary key not null,
create table workitem
(
    workitem_id char(13) primary key not null,
    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
    msg_err          varchar(255)

);

create table flow_expression_id
(
    workitem_id bigint 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,
    wfd_url varchar(255) not null,
    wfd_name varchar(255) not null,
    wfd_revision varchar(255) not null,
    wf_instance_id bigint not null,
    expression_name varchar(255) not null,
    expression_id int not null,

    primary key (workitem_id, stack_index)
);

create table attribute
(
    workitem_id bigint not null,
    id bigint not null,
    parent_id bigint,
	-- 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)
);

--
-- history of the workitem
--

create table history_item
(
    workitem_id char(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 char(13) not null,
    userid varchar(32),
    doc_revision int,

    primary key (workitem_id, id)
);

--
-- filter
--

create table filter
(
    workitem_id bigint 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 table filter_entry
(
    workitem_id bigint not null,
    regex varchar(255) not null,
    permissions char(3) not null,
    attributeType varchar(255),

    primary key (workitem_id, regex)
);


--
-- the action table
--

create table action
(
    workitem_id char(13) not null,
    action varchar(255) not null,
    arg   varchar(255),
    msg_err   varchar(255),

    primary key (workitem_id)
);