create table ODE_SCHEMA_VERSION(VERSION integer);
insert into ODE_SCHEMA_VERSION values (5);
-- Apache ODE - SimpleScheduler Database Schema
-- 
-- Apache Derby scripts by Maciej Szefler.
-- 
-- 

CREATE TABLE ode_job (
  jobid CHAR(64)  NOT NULL DEFAULT '',
  ts BIGINT  NOT NULL DEFAULT 0,
  nodeid char(64),
  scheduled int  NOT NULL DEFAULT 0,
  transacted int  NOT NULL DEFAULT 0,
  details image null,
  PRIMARY KEY(jobid));

CREATE INDEX IDX_ODE_JOB_TS ON ode_job(ts);
CREATE INDEX IDX_ODE_JOB_NODEID ON ode_job(nodeid);


create table BPEL_ACTIVITY_RECOVERY (ID numeric(19,0) identity not null, PIID numeric(19,0) null, AID numeric(19,0) null, CHANNEL varchar(255) null, REASON varchar(255) null, DATE_TIME datetime null, LDATA_ID numeric(19,0) null, ACTIONS varchar(255) null, RETRIES int null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_CORRELATION_PROP (ID numeric(19,0) identity not null, NAME varchar(255) null, NAMESPACE varchar(255) null, VALUE varchar(255) null, CORR_SET_ID numeric(19,0) null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_CORRELATION_SET (ID numeric(19,0) identity not null, VALUE varchar(255) null, CORR_SET_NAME varchar(255) null, SCOPE_ID numeric(19,0) null, PIID numeric(19,0) null, PROCESS_ID numeric(19,0) null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_CORRELATOR (ID numeric(19,0) identity not null, CID varchar(255) null, PROCESS_ID numeric(19,0) null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_CORRELATOR_MESSAGE_CKEY (ID numeric(19,0) identity not null, CKEY varchar(255) null, CORRELATOR_MESSAGE_ID numeric(19,0) null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_EVENT (ID numeric(19,0) identity not null, IID numeric(19,0) null, PID numeric(19,0) null, TSTAMP datetime null, TYPE varchar(255) null, DETAIL text null, LDATA_ID numeric(19,0) null, SID numeric(19,0) null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_FAULT (ID numeric(19,0) identity not null, FAULTNAME varchar(255) null, LDATA_ID numeric(19,0) null, EXPLANATION varchar(4000) null, LINE_NUM int null, AID int null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_INSTANCE (ID numeric(19,0) identity not null, INSTANTIATING_CORRELATOR numeric(19,0) null, FAULT numeric(19,0) null, JACOB_STATE numeric(19,0) null, PREVIOUS_STATE smallint null, PROCESS_ID numeric(19,0) null, STATE smallint null, LAST_ACTIVE_DT datetime null, SEQUENCE numeric(19,0) null, FAILURE_COUNT int null, FAILURE_DT datetime null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_MESSAGE (ID numeric(19,0) identity not null, MEX numeric(19,0) null, TYPE varchar(255) null, DATA numeric(19,0) null, HEADER numeric(19,0) null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_MESSAGE_EXCHANGE (ID numeric(19,0) identity not null, PORT_TYPE varchar(255) null, CHANNEL_NAME varchar(255) null, CLIENTKEY varchar(255) null, LDATA_EPR_ID numeric(19,0) null, LDATA_CEPR_ID numeric(19,0) null, REQUEST numeric(19,0) null, RESPONSE numeric(19,0) null, INSERT_DT datetime null, OPERATION varchar(255) null, STATE varchar(255) null, PROCESS numeric(19,0) null, PIID numeric(19,0) null, DIR char(1) null, PLINK_MODELID int null, PATTERN varchar(255) null, CORR_STATUS varchar(255) null, FAULT_TYPE varchar(255) null, FAULT_EXPL varchar(255) null, CALLEE varchar(255) null, PARTNERLINK numeric(19,0) null, PIPED_ID varchar(255) null, SUBSCRIBER_COUNT int null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_MEX_PROPS (MEX numeric(19,0) not null, VALUE varchar(8000) null, NAME varchar(255) not null, primary key (MEX, NAME));
create table BPEL_PLINK_VAL (ID numeric(19,0) identity not null, PARTNER_LINK varchar(100) not null, PARTNERROLE varchar(100) null, MYROLE_EPR numeric(19,0) null, PARTNERROLE_EPR numeric(19,0) null, PROCESS numeric(19,0) null, SCOPE numeric(19,0) null, SVCNAME varchar(255) null, MYROLE varchar(100) null, MODELID int null, MYSESSIONID varchar(255) null, PARTNERSESSIONID varchar(255) null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_PROCESS (ID numeric(19,0) identity not null, PROCID varchar(255) not null unique, deployer varchar(255) null, deploydate datetime null, type_name varchar(255) null, type_ns varchar(255) null, version numeric(19,0) null, ACTIVE_ tinyint null, guid varchar(255) null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_SCOPE (ID numeric(19,0) identity not null, PIID numeric(19,0) null, PARENT_SCOPE_ID numeric(19,0) null, STATE varchar(255) not null, NAME varchar(255) not null, MODELID int null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_SELECTORS (ID numeric(19,0) identity not null, PIID numeric(19,0) not null, SELGRPID varchar(255) not null, IDX int not null, CORRELATION_KEY varchar(255) not null, PROC_TYPE varchar(255) not null, ROUTE_POLICY varchar(255) null, CORRELATOR numeric(19,0) not null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID), unique (CORRELATION_KEY, CORRELATOR));
create table BPEL_UNMATCHED (ID numeric(19,0) identity not null, MEX numeric(19,0) null, CORRELATION_KEY varchar(255) null, CORRELATOR numeric(19,0) not null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table BPEL_XML_DATA (ID numeric(19,0) identity not null, LDATA_ID numeric(19,0) null, NAME varchar(255) not null, SCOPE_ID numeric(19,0) null, PIID numeric(19,0) null, IS_SIMPLE_TYPE tinyint null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table LARGE_DATA (ID numeric(19,0) identity not null, BIN_DATA image null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create table VAR_PROPERTY (ID numeric(19,0) identity not null, XML_DATA_ID numeric(19,0) null, PROP_VALUE varchar(255) null, PROP_NAME varchar(255) not null, INSERT_TIME datetime null, MLOCK int not null, primary key (ID));
create index IDX_CORRELATOR_CID on BPEL_CORRELATOR (CID);
create index IDX_BPEL_CORRELATOR_MESSAGE_CKEY on BPEL_CORRELATOR_MESSAGE_CKEY (CKEY);
create index IDX_SELECTOR_CORRELATOR on BPEL_SELECTORS (CORRELATOR);
create index IDX_SELECTOR_CKEY on BPEL_SELECTORS (CORRELATION_KEY);
create index IDX_SELECTOR_SELGRPID on BPEL_SELECTORS (SELGRPID);
create index IDX_UNMATCHED_CKEY on BPEL_UNMATCHED (CORRELATION_KEY);
create index IDX_UNMATCHED_CORRELATOR on BPEL_UNMATCHED (CORRELATOR);

-- create indexes.

create index IDX_XMLDATA_IID on BPEL_XML_DATA (PIID) ;
create index IDX_XMLDATA_SID on BPEL_XML_DATA (SCOPE_ID)  ;
create index IDX_XMLDATA_NAME on BPEL_XML_DATA (NAME)  ;
create index IDX_XMLDATA_NAME_SID on BPEL_XML_DATA (NAME, SCOPE_ID)  ;

create index IDX_EVENT_IID on BPEL_EVENT (IID)  ;
create index IDX_EVENT_PID on BPEL_EVENT (PID)  ;


create index IDX_CORR_SET_NAME on BPEL_CORRELATION_SET (CORR_SET_NAME)  ;
create index IDX_CORR_SET_SCOPE_ID on BPEL_CORRELATION_SET (SCOPE_ID)  ;
create index IDX_BPEL_INSTANCE_PROCESS_ID on BPEL_INSTANCE (PROCESS_ID)  ;
create index IDX_BPEL_INSTANCE_STATE on BPEL_INSTANCE (STATE)  ;
create index IDX_BPEL_PROCESS_TYPE_NAME on BPEL_PROCESS (type_name)  ;
create index IDX_BPEL_PROCESS_TYPE_NS on BPEL_PROCESS (type_ns)  ;
create index IDX_BPEL_CORRELATOR_PROCESS_ID on BPEL_CORRELATOR (PROCESS_ID)  ;
create index IDX_UNMATCHED_CORRELATOR_CKEY on BPEL_UNMATCHED (CORRELATOR,CORRELATION_KEY)  ;
create index IDX_RECOVERY_LDATAID on BPEL_ACTIVITY_RECOVERY (LDATA_ID)  ;
create index IDX_EVENT_LDATAID on BPEL_EVENT (LDATA_ID)  ;
create index IDX_FAULT_LDATAID on BPEL_FAULT (LDATA_ID)  ;
create index IDX_MESSAGE_LDATAID on BPEL_MESSAGE (DATA)  ;
create index IDX_INSTANCE_LDATAID on BPEL_INSTANCE (JACOB_STATE)  ;
create index IDX_MEXCEPR_LDATAID on BPEL_MESSAGE_EXCHANGE (LDATA_CEPR_ID)  ;
create index IDX_MEXEPR_LDATAID on BPEL_MESSAGE_EXCHANGE (LDATA_EPR_ID)  ;
create index IDX_PLINK_VAL_PROCESS_IDX on BPEL_PLINK_VAL (PROCESS)  ;
create index IDX_PLINK_VAL_SCOPE on BPEL_PLINK_VAL (SCOPE)  ;
create index IDX_PLINKMYR_LDATAID on BPEL_PLINK_VAL (MYROLE_EPR)  ;
create index IDX_PLINKPR_LDATAID on BPEL_PLINK_VAL (PARTNERROLE_EPR)  ;
create index IDX_PLINK_VAL_MODELID on BPEL_PLINK_VAL (MODELID)  ;
create index IDX_SELECTOR_INSTANCE on BPEL_SELECTORS (PIID)  ;
create index IDX_VARPROP_XMLDATA on VAR_PROPERTY (XML_DATA_ID)  ;
create index IDX_VARPROP_NAME on VAR_PROPERTY (PROP_NAME)  ;
create index IDX_VARPROP_VALUE on VAR_PROPERTY (PROP_VALUE)  ;
create index IDX_UNMATCHED_MEX on BPEL_UNMATCHED (MEX)  ;

--Indexes for instance deletion
create index IDX_MESSAGE_MEX on BPEL_MESSAGE(MEX) ;
create index IDX_MESSAGE_EXCHANGE_PIID on BPEL_MESSAGE_EXCHANGE(PIID) ;
create index IDX_SCOPE_PIID on BPEL_SCOPE(PIID) ;
