activiti.informix.create.case.engine.sql

--
-- Copyright © 2012 - 2018 camunda services GmbH and various authors (info@camunda.com)
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--

-- create case definition table --

create table ACT_RE_CASE_DEF (
  ID_ varchar(64) not null,
  REV_ integer,
  CATEGORY_ varchar(255),
  NAME_ varchar(255),
  KEY_ varchar(255) not null,
  VERSION_ integer not null,
  DEPLOYMENT_ID_ varchar(64),
  RESOURCE_NAME_ lvarchar(4000),
  DGRM_RESOURCE_NAME_ lvarchar(4000),
  TENANT_ID_ varchar(64),
  HISTORY_TTL_ integer,
  primary key (ID_)
);

-- create case execution table --

create table ACT_RU_CASE_EXECUTION (
  ID_ varchar(64) not null,
  REV_ integer,
  CASE_INST_ID_ varchar(64),
  SUPER_CASE_EXEC_ varchar(64),
  SUPER_EXEC_ varchar(64),
  BUSINESS_KEY_ varchar(255),
  PARENT_ID_ varchar(64),
  CASE_DEF_ID_ varchar(64),
  ACT_ID_ varchar(255),
  PREV_STATE_ integer,
  CURRENT_STATE_ integer,
  REQUIRED_ boolean,
  TENANT_ID_ varchar(64),
  primary key (ID_)
);

-- create case sentry part table --

create table ACT_RU_CASE_SENTRY_PART (
  ID_ varchar(64) not null,
  REV_ integer,
  CASE_INST_ID_ varchar(64),
  CASE_EXEC_ID_ varchar(64),
  SENTRY_ID_ varchar(255),
  TYPE_ varchar(255),
  SOURCE_CASE_EXEC_ID_ varchar(64),
  STANDARD_EVENT_ varchar(255),
  SOURCE_ varchar(255),
  VARIABLE_EVENT_ varchar(255),
  VARIABLE_NAME_ varchar(255),
  SATISFIED_ boolean,
  TENANT_ID_ varchar(64),
  primary key (ID_)
);

-- create index on business key --
create index ACT_IDX_CASE_EXEC_BUSKEY on ACT_RU_CASE_EXECUTION(BUSINESS_KEY_);

-- https://app.camunda.com/jira/browse/CAM-9165
create index ACT_IDX_CASE_EXE_CASE_INST on ACT_RU_CASE_EXECUTION(CASE_INST_ID_);

-- create foreign key constraints on ACT_RU_CASE_EXECUTION --
alter table ACT_RU_CASE_EXECUTION
  add constraint foreign key (CASE_INST_ID_)
  references ACT_RU_CASE_EXECUTION(ID_)
  constraint ACT_FK_CASE_EXE_CASE_INST;

alter table ACT_RU_CASE_EXECUTION
  add constraint foreign key (PARENT_ID_)
  references ACT_RU_CASE_EXECUTION(ID_)
  constraint ACT_FK_CASE_EXE_PARENT;

alter table ACT_RU_CASE_EXECUTION
  add constraint foreign key (CASE_DEF_ID_)
  references ACT_RE_CASE_DEF(ID_)
  constraint ACT_FK_CASE_EXE_CASE_DEF;

alter table ACT_RU_VARIABLE
  add constraint foreign key (CASE_EXECUTION_ID_)
  references ACT_RU_CASE_EXECUTION(ID_)
  constraint ACT_FK_VAR_CASE_EXE;

alter table ACT_RU_VARIABLE
  add constraint foreign key (CASE_INST_ID_)
  references ACT_RU_CASE_EXECUTION(ID_)
  constraint ACT_FK_VAR_CASE_INST;

alter table ACT_RU_TASK
  add constraint foreign key (CASE_EXECUTION_ID_)
  references ACT_RU_CASE_EXECUTION(ID_)
  constraint ACT_FK_TASK_CASE_EXE;

alter table ACT_RU_TASK
  add constraint foreign key (CASE_DEF_ID_)
  references ACT_RE_CASE_DEF(ID_)
  constraint ACT_FK_TASK_CASE_DEF;

alter table ACT_RU_CASE_SENTRY_PART
  add constraint foreign key (CASE_INST_ID_)
  references ACT_RU_CASE_EXECUTION(ID_)
  constraint ACT_FK_CASE_SENTRY_CASE_INST;

alter table ACT_RU_CASE_SENTRY_PART
  add constraint foreign key (CASE_EXEC_ID_)
  references ACT_RU_CASE_EXECUTION(ID_)
  constraint ACT_FK_CASE_SENTRY_CASE_EXEC;

-- indexes for concurrency problems - https://app.camunda.com/jira/browse/CAM-1646 --
-- create index ACT_IDX_CASE_EXEC_CASE on ACT_RU_CASE_EXECUTION(CASE_DEF_ID_);
-- create index ACT_IDX_CASE_EXEC_PARENT on ACT_RU_CASE_EXECUTION(PARENT_ID_);
-- create index ACT_IDX_VARIABLE_CASE_EXEC on ACT_RU_VARIABLE(CASE_EXECUTION_ID_);
-- create index ACT_IDX_VARIABLE_CASE_INST on ACT_RU_VARIABLE(CASE_INST_ID_);
-- create index ACT_IDX_TASK_CASE_EXEC on ACT_RU_TASK(CASE_EXECUTION_ID_);
-- create index ACT_IDX_TASK_CASE_DEF_ID on ACT_RU_TASK(CASE_DEF_ID_);

-- add indexes for ACT_RU_CASE_SENTRY_PART --
-- create index ACT_IDX_CASE_SENTRY_CASE_INST on ACT_RU_CASE_SENTRY_PART(CASE_INST_ID_);
-- create index ACT_IDX_CASE_SENTRY_CASE_EXEC on ACT_RU_CASE_SENTRY_PART(CASE_EXEC_ID_);

create index ACT_IDX_CASE_DEF_TENANT_ID on ACT_RE_CASE_DEF(TENANT_ID_);
create index ACT_IDX_CASE_EXEC_TENANT_ID on ACT_RU_CASE_EXECUTION(TENANT_ID_);