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 VARCHAR(64)  NOT NULL,
  ts number(37)  NOT NULL,
  nodeid varchar(64),
  scheduled int  NOT NULL,
  transacted int  NOT NULL,
  details blob,
  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 number(19,0) not null, PIID number(19,0), AID number(19,0), CHANNEL varchar2(255 char), REASON varchar2(255 char), DATE_TIME timestamp, LDATA_ID number(19,0), ACTIONS varchar2(255 char), RETRIES number(10,0), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_CORRELATION_PROP (ID number(19,0) not null, NAME varchar2(255 char), NAMESPACE varchar2(255 char), VALUE varchar2(255 char), CORR_SET_ID number(19,0), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_CORRELATION_SET (ID number(19,0) not null, VALUE varchar2(255 char), CORR_SET_NAME varchar2(255 char), SCOPE_ID number(19,0), PIID number(19,0), PROCESS_ID number(19,0), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_CORRELATOR (ID number(19,0) not null, CID varchar2(255 char), PROCESS_ID number(19,0), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_CORRELATOR_MESSAGE_CKEY (ID number(19,0) not null, CKEY varchar2(255 char), CORRELATOR_MESSAGE_ID number(19,0), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_EVENT (ID number(19,0) not null, IID number(19,0), PID number(19,0), TSTAMP timestamp, TYPE varchar2(255 char), DETAIL clob, LDATA_ID number(19,0), SID number(19,0), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_FAULT (ID number(19,0) not null, FAULTNAME varchar2(255 char), LDATA_ID number(19,0), EXPLANATION varchar2(4000 char), LINE_NUM number(10,0), AID number(10,0), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_INSTANCE (ID number(19,0) not null, INSTANTIATING_CORRELATOR number(19,0), FAULT number(19,0), JACOB_STATE number(19,0), PREVIOUS_STATE number(5,0), PROCESS_ID number(19,0), STATE number(5,0), LAST_ACTIVE_DT timestamp, SEQUENCE number(19,0), FAILURE_COUNT number(10,0), FAILURE_DT timestamp, INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_MESSAGE (ID number(19,0) not null, MEX number(19,0), TYPE varchar2(255 char), DATA number(19,0), HEADER number(19,0), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_MESSAGE_EXCHANGE (ID number(19,0) not null, PORT_TYPE varchar2(255 char), CHANNEL_NAME varchar2(255 char), CLIENTKEY varchar2(255 char), LDATA_EPR_ID number(19,0), LDATA_CEPR_ID number(19,0), REQUEST number(19,0), RESPONSE number(19,0), INSERT_DT timestamp, OPERATION varchar2(255 char), STATE varchar2(255 char), PROCESS number(19,0), PIID number(19,0), DIR char(1 char), PLINK_MODELID number(10,0), PATTERN varchar2(255 char), CORR_STATUS varchar2(255 char), FAULT_TYPE varchar2(255 char), FAULT_EXPL varchar2(255 char), CALLEE varchar2(255 char), PARTNERLINK number(19,0), PIPED_ID varchar2(255 char), SUBSCRIBER_COUNT number(10,0), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_MEX_PROPS (MEX number(19,0) not null, VALUE varchar2(255 char), NAME varchar2(255 char) not null, primary key (MEX, NAME));
create table BPEL_PLINK_VAL (ID number(19,0) not null, PARTNER_LINK varchar2(100 char) not null, PARTNERROLE varchar2(100 char), MYROLE_EPR number(19,0), PARTNERROLE_EPR number(19,0), PROCESS number(19,0), SCOPE number(19,0), SVCNAME varchar2(255 char), MYROLE varchar2(100 char), MODELID number(10,0), MYSESSIONID varchar2(255 char), PARTNERSESSIONID varchar2(255 char), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_PROCESS (ID number(19,0) not null, PROCID varchar2(255 char) not null unique, deployer varchar2(255 char), deploydate timestamp, type_name varchar2(255 char), type_ns varchar2(255 char), version number(19,0), ACTIVE_ number(1,0), guid varchar2(255 char), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_SCOPE (ID number(19,0) not null, PIID number(19,0), PARENT_SCOPE_ID number(19,0), STATE varchar2(255 char) not null, NAME varchar2(255 char) not null, MODELID number(10,0), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_SELECTORS (ID number(19,0) not null, PIID number(19,0) not null, SELGRPID varchar2(255 char) not null, IDX number(10,0) not null, CORRELATION_KEY varchar2(255 char) not null, PROC_TYPE varchar2(255 char) not null, ROUTE_POLICY varchar2(255 char), CORRELATOR number(19,0) not null, INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID), unique (CORRELATION_KEY, CORRELATOR));
create table BPEL_UNMATCHED (ID number(19,0) not null, MEX number(19,0), CORRELATION_KEY varchar2(255 char), CORRELATOR number(19,0) not null, INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table BPEL_XML_DATA (ID number(19,0) not null, LDATA_ID number(19,0), NAME varchar2(255 char) not null, SCOPE_ID number(19,0), PIID number(19,0), IS_SIMPLE_TYPE number(1,0), INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table LARGE_DATA (ID number(19,0) not null, BIN_DATA blob, INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));
create table VAR_PROPERTY (ID number(19,0) not null, XML_DATA_ID number(19,0), PROP_VALUE varchar2(255 char), PROP_NAME varchar2(255 char) not null, INSERT_TIME timestamp, MLOCK number(10,0) not null, primary key (ID));

create index IDX_CORRELATOR_CID on BPEL_CORRELATOR (CID) ;
create index IDX_CKEYxx 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 sequence hibernate_sequence;


--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) ;

