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


--create indexes

create index IDX_XMLDATA_IID on BPEL_XML_DATA (PIID) ;
create index IDX_XMLDATA_LDATA_ID on BPEL_XML_DATA (LDATA_ID) ;
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)  ;
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);
create index IDX_LARGE_DATA_ID on LARGE_DATA(ID);
create index IDX_BPEL_MEX_PROPS_MEX on BPEL_MEX_PROPS (MEX) ;
