hsqldb.sql

一个很好实用的工作流OSWORKFLOW开发例子.有着非常优秀的灵活性.

DROP TABLE IF EXISTS QRTZ_JOB_LISTENERS;
DROP TABLE IF EXISTS QRTZ_TRIGGER_LISTENERS;
DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS;
DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE;
DROP TABLE IF EXISTS QRTZ_LOCKS;
DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_JOB_DETAILS;
DROP TABLE IF EXISTS QRTZ_CALENDARS;
DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE;

DROP TABLE IF EXISTS OS_PROPERTYENTRY;
DROP TABLE IF EXISTS OS_CURRENTSTEP_PREV;
DROP TABLE IF EXISTS OS_HISTORYSTEP_PREV;
DROP TABLE IF EXISTS OS_CURRENTSTEP;
DROP TABLE IF EXISTS OS_HISTORYSTEP;
DROP TABLE IF EXISTS OS_WFENTRY;

DROP SEQUENCE SEQ_OS_WFENTRY IF EXISTS;
DROP SEQUENCE SEQ_OS_CURRENTSTEPS IF EXISTS;

CREATE SEQUENCE SEQ_OS_WFENTRY START WITH 10 INCREMENT BY 2;
CREATE SEQUENCE SEQ_OS_CURRENTSTEPS START WITH 10 INCREMENT BY 2;

CREATE TABLE OS_WFENTRY
(
    ID int,
    NAME varchar(128),
    STATE smallint,
    primary key (ID)
);

CREATE TABLE OS_CURRENTSTEP
(
    ID int,
    ENTRY_ID int,
    STEP_ID smallint,
    ACTION_ID smallint,
    OWNER varchar(20),
    START_DATE TIMESTAMP ,
    FINISH_DATE TIMESTAMP ,
    DUE_DATE TIMESTAMP ,
    STATUS varchar(20),
    CALLER varchar(20),
    primary key (ID),
    foreign key (ENTRY_ID) references OS_WFENTRY(ID)
);

CREATE TABLE OS_HISTORYSTEP
(
    ID int,
    ENTRY_ID int,
    STEP_ID smallint,
    ACTION_ID smallint,
    OWNER varchar(20),
    START_DATE TIMESTAMP ,
    FINISH_DATE TIMESTAMP ,
    DUE_DATE TIMESTAMP ,
    STATUS varchar(20),
    CALLER varchar(20),
    primary key (ID),
    foreign key (ENTRY_ID) references OS_WFENTRY(ID)
);

CREATE TABLE OS_CURRENTSTEP_PREV
(
    ID int,
    PREVIOUS_ID int,
    primary key (ID, PREVIOUS_ID),
    foreign key (ID) references OS_CURRENTSTEP(ID),
    foreign key (PREVIOUS_ID) references OS_HISTORYSTEP(ID)
);

CREATE TABLE OS_HISTORYSTEP_PREV
(
    ID int,
    PREVIOUS_ID int,
    primary key (ID, PREVIOUS_ID),
    foreign key (ID) references OS_HISTORYSTEP(ID),
    foreign key (PREVIOUS_ID) references OS_HISTORYSTEP(ID)
);

CREATE TABLE OS_PROPERTYENTRY
(
  GLOBAL_KEY varchar(255),
  ITEM_KEY varchar(255),
  ITEM_TYPE smallint,
  STRING_VALUE varchar(255),
  DATE_VALUE TIMESTAMP ,
  DATA_VALUE varbinary(2000),
  FLOAT_VALUE float,
  NUMBER_VALUE numeric,
  primary key (GLOBAL_KEY, ITEM_KEY)
);

CREATE TABLE qrtz_job_details 
( 
JOB_NAME VARCHAR(80) NOT NULL, 
JOB_GROUP VARCHAR(80) NOT NULL, 
DESCRIPTION VARCHAR(120) NULL, 
JOB_CLASS_NAME VARCHAR(128) NOT NULL, 
IS_DURABLE VARCHAR(1) NOT NULL, 
IS_VOLATILE VARCHAR(1) NOT NULL, 
IS_STATEFUL VARCHAR(1) NOT NULL, 
REQUESTS_RECOVERY VARCHAR(1) NOT NULL, 
JOB_DATA BINARY NULL, 
PRIMARY KEY (JOB_NAME,JOB_GROUP) 
); 

CREATE TABLE qrtz_job_listeners 
( 
JOB_NAME VARCHAR(80) NOT NULL, 
JOB_GROUP VARCHAR(80) NOT NULL, 
JOB_LISTENER VARCHAR(80) NOT NULL, 
PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER), 
FOREIGN KEY (JOB_NAME,JOB_GROUP) 
REFERENCES QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP) 
); 

CREATE TABLE qrtz_triggers 
( 
TRIGGER_NAME VARCHAR(80) NOT NULL, 
TRIGGER_GROUP VARCHAR(80) NOT NULL, 
JOB_NAME VARCHAR(80) NOT NULL, 
JOB_GROUP VARCHAR(80) NOT NULL, 
IS_VOLATILE VARCHAR(1) NOT NULL, 
DESCRIPTION VARCHAR(120) NULL, 
NEXT_FIRE_TIME NUMERIC(13) NULL, 
PREV_FIRE_TIME NUMERIC(13) NULL, 
TRIGGER_STATE VARCHAR(16) NOT NULL, 
TRIGGER_TYPE VARCHAR(8) NOT NULL, 
START_TIME NUMERIC(13) NOT NULL, 
END_TIME NUMERIC(13) NULL, 
CALENDAR_NAME VARCHAR(80) NULL, 
MISFIRE_INSTR NUMERIC(2) NULL, 
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), 
FOREIGN KEY (JOB_NAME,JOB_GROUP) 
REFERENCES QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP) 
); 

CREATE TABLE qrtz_simple_triggers 
( 
TRIGGER_NAME VARCHAR(80) NOT NULL, 
TRIGGER_GROUP VARCHAR(80) NOT NULL, 
REPEAT_COUNT NUMERIC(7) NOT NULL, 
REPEAT_INTERVAL NUMERIC(12) NOT NULL, 
TIMES_TRIGGERED NUMERIC(7) NOT NULL, 
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), 
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) 
REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP) 
); 

CREATE TABLE qrtz_cron_triggers 
( 
TRIGGER_NAME VARCHAR(80) NOT NULL, 
TRIGGER_GROUP VARCHAR(80) NOT NULL, 
CRON_EXPRESSION VARCHAR(80) NOT NULL, 
TIME_ZONE_ID VARCHAR(80), 
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), 
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) 
REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP) 
); 

CREATE TABLE qrtz_blob_triggers 
( 
TRIGGER_NAME VARCHAR(80) NOT NULL, 
TRIGGER_GROUP VARCHAR(80) NOT NULL, 
BLOB_DATA BINARY NULL, 
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), 
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) 
REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP) 
); 

CREATE TABLE qrtz_trigger_listeners 
( 
TRIGGER_NAME VARCHAR(80) NOT NULL, 
TRIGGER_GROUP VARCHAR(80) NOT NULL, 
TRIGGER_LISTENER VARCHAR(80) NOT NULL, 
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_LISTENER), 
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) 
REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP) 
); 

CREATE TABLE qrtz_calendars 
( 
CALENDAR_NAME VARCHAR(80) NOT NULL, 
CALENDAR BINARY NOT NULL, 
PRIMARY KEY (CALENDAR_NAME) 
); 

CREATE TABLE qrtz_paused_trigger_grps 
( 
TRIGGER_GROUP VARCHAR(80) NOT NULL, 
PRIMARY KEY (TRIGGER_GROUP) 
); 

CREATE TABLE qrtz_fired_triggers 
( 
ENTRY_ID VARCHAR(95) NOT NULL, 
TRIGGER_NAME VARCHAR(80) NOT NULL, 
TRIGGER_GROUP VARCHAR(80) NOT NULL, 
IS_VOLATILE VARCHAR(1) NOT NULL, 
INSTANCE_NAME VARCHAR(80) NOT NULL, 
FIRED_TIME NUMERIC(13) NOT NULL, 
STATE VARCHAR(16) NOT NULL, 
JOB_NAME VARCHAR(80) NULL, 
JOB_GROUP VARCHAR(80) NULL, 
IS_STATEFUL VARCHAR(1) NULL, 
REQUESTS_RECOVERY VARCHAR(1) NULL, 
PRIMARY KEY (ENTRY_ID) 
); 

CREATE TABLE qrtz_scheduler_state 
( 
INSTANCE_NAME VARCHAR(80) NOT NULL, 
LAST_CHECKIN_TIME NUMERIC(13) NOT NULL, 
CHECKIN_INTERVAL NUMERIC(13) NOT NULL, 
RECOVERER VARCHAR(80) NULL, 
PRIMARY KEY (INSTANCE_NAME) 
); 

CREATE TABLE qrtz_locks 
( 
LOCK_NAME VARCHAR(40) NOT NULL, 
PRIMARY KEY (LOCK_NAME) 
); 

INSERT INTO qrtz_locks values('TRIGGER_ACCESS'); 
INSERT INTO qrtz_locks values('JOB_ACCESS'); 
INSERT INTO qrtz_locks values('CALENDAR_ACCESS'); 
INSERT INTO qrtz_locks values('STATE_ACCESS'); 
INSERT INTO qrtz_locks values('MISFIRE_ACCESS');