Two database schema issues

1. The operation_status table is missing the service_name column,
   which was added in the amsterdam release
2. Table camundabpmn.ACT_HI_VARINST has no column STATE_

Change-Id: Ifadfce23a78b9404b677c1a9597003b3c8b9187d
Issue-ID: SO-560
Signed-off-by: Rob Daugherty <rd472p@att.com>

3 files changed, 34 insertions, 18 deletions

diff --git a/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/bulkload-files/default/create_mso_db-default.sql b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/bulkload-files/default/create_mso_db-default.sql
index dd4325b..c24d541 100644
--- a/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/bulkload-files/default/create_mso_db-default.sql
+++ b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/bulkload-files/default/create_mso_db-default.sql
@@ -1,4 +1,4 @@
-SOURCE ../../camunda/mariadb_engine_7.7.3-ee.sql
+SOURCE ../../camunda/mariadb_engine_7.8.0-ee.sql
 
 --
 -- Create an admin user automatically for the cockpit
diff --git a/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/camunda/mariadb_engine_7.7.3-ee.sql b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/camunda/mariadb_engine_7.8.0-ee.sql
index b9b8dd6..ba57092 100644
--- a/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/camunda/mariadb_engine_7.7.3-ee.sql
+++ b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/camunda/mariadb_engine_7.8.0-ee.sql
@@ -4,14 +4,11 @@ CREATE DATABASE `camundabpmn`;
 
 USE `camundabpmn`;
 
-# DROP USER IF EXISTS 'camunda';
 delete from mysql.user where User='camunda';
 CREATE USER 'camunda';
 GRANT ALL on camundabpmn.* to 'camunda' identified by 'camunda123' with GRANT OPTION;
 FLUSH PRIVILEGES;
 
-USE `camundabpmn`;
-
 create table ACT_GE_PROPERTY (
     NAME_ varchar(64),
     VALUE_ varchar(300),
@@ -34,6 +31,9 @@ values ('deployment.lock', '0', 1);
 insert into ACT_GE_PROPERTY
 values ('history.cleanup.job.lock', '0', 1);
 
+insert into ACT_GE_PROPERTY
+values ('startup.lock', '0', 1);
+
 create table ACT_GE_BYTEARRAY (
     ID_ varchar(64),
     REV_ integer,
@@ -186,8 +186,8 @@ create table ACT_RU_VARIABLE (
     BYTEARRAY_ID_ varchar(64),
     DOUBLE_ double,
     LONG_ bigint,
-    TEXT_ LONGBLOB NULL,
-    TEXT2_ LONGBLOB NULL,
+    TEXT_ LONGBLOB,
+    TEXT2_ LONGBLOB,
     VAR_SCOPE_ varchar(64) not null,
     SEQUENCE_COUNTER_ bigint,
     IS_CONCURRENT_LOCAL_ TINYINT,
@@ -234,7 +234,7 @@ create table ACT_RU_AUTHORIZATION (
   GROUP_ID_ varchar(255),
   USER_ID_ varchar(255),
   RESOURCE_TYPE_ integer not null,
-  RESOURCE_ID_ varchar(64),
+  RESOURCE_ID_ varchar(255),
   PERMS_ integer,
   primary key (ID_)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
@@ -625,6 +625,7 @@ create table ACT_RE_DECISION_DEF (
     DEC_REQ_KEY_ varchar(255),
     TENANT_ID_ varchar(64),
     HISTORY_TTL_ integer,
+    VERSION_TAG_ varchar(64),
     primary key (ID_)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
 
@@ -742,9 +743,10 @@ create table ACT_HI_VARINST (
     BYTEARRAY_ID_ varchar(64),
     DOUBLE_ double,
     LONG_ bigint,
-    TEXT_ LONGBLOB NULL,
-    TEXT2_ LONGBLOB NULL,
+    TEXT_ LONGBLOB,
+    TEXT2_ LONGBLOB,
     TENANT_ID_ varchar(64),
+    STATE_ varchar(20),
     primary key (ID_)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
 
@@ -769,8 +771,8 @@ create table ACT_HI_DETAIL (
     BYTEARRAY_ID_ varchar(64),
     DOUBLE_ double,
     LONG_ bigint,
-    TEXT_ LONGBLOB NULL,
-    TEXT2_ LONGBLOB NULL,
+    TEXT_ LONGBLOB,
+    TEXT2_ LONGBLOB,
     SEQUENCE_COUNTER_ bigint,
     TENANT_ID_ varchar(64),
     OPERATION_ID_ varchar(64),
@@ -942,6 +944,8 @@ create index ACT_IDX_HI_ACT_INST_PROC_DEF_KEY on ACT_HI_ACTINST(PROC_DEF_KEY_);
 
 create index ACT_IDX_HI_TASK_INST_TENANT_ID on ACT_HI_TASKINST(TENANT_ID_);
 create index ACT_IDX_HI_TASK_INST_PROC_DEF_KEY on ACT_HI_TASKINST(PROC_DEF_KEY_);
+create index ACT_IDX_HI_TASKINST_PROCINST on ACT_HI_TASKINST(PROC_INST_ID_);
+create index ACT_IDX_HI_TASKINSTID_PROCINST on ACT_HI_TASKINST(ID_,PROC_INST_ID_);
 
 create index ACT_IDX_HI_DETAIL_PROC_INST on ACT_HI_DETAIL(PROC_INST_ID_);
 create index ACT_IDX_HI_DETAIL_ACT_INST on ACT_HI_DETAIL(ACT_INST_ID_);
@@ -952,34 +956,47 @@ create index ACT_IDX_HI_DETAIL_NAME on ACT_HI_DETAIL(NAME_);
 create index ACT_IDX_HI_DETAIL_TASK_ID on ACT_HI_DETAIL(TASK_ID_);
 create index ACT_IDX_HI_DETAIL_TENANT_ID on ACT_HI_DETAIL(TENANT_ID_);
 create index ACT_IDX_HI_DETAIL_PROC_DEF_KEY on ACT_HI_DETAIL(PROC_DEF_KEY_);
+create index ACT_IDX_HI_DETAIL_BYTEAR on ACT_HI_DETAIL(BYTEARRAY_ID_);
 
 create index ACT_IDX_HI_IDENT_LNK_USER on ACT_HI_IDENTITYLINK(USER_ID_);
 create index ACT_IDX_HI_IDENT_LNK_GROUP on ACT_HI_IDENTITYLINK(GROUP_ID_);
 create index ACT_IDX_HI_IDENT_LNK_TENANT_ID on ACT_HI_IDENTITYLINK(TENANT_ID_);
 create index ACT_IDX_HI_IDENT_LNK_PROC_DEF_KEY on ACT_HI_IDENTITYLINK(PROC_DEF_KEY_);
+create index ACT_IDX_HI_IDENT_LINK_TASK on ACT_HI_IDENTITYLINK(TASK_ID_);
 
 create index ACT_IDX_HI_PROCVAR_PROC_INST on ACT_HI_VARINST(PROC_INST_ID_);
 create index ACT_IDX_HI_PROCVAR_NAME_TYPE on ACT_HI_VARINST(NAME_, VAR_TYPE_);
 create index ACT_IDX_HI_CASEVAR_CASE_INST on ACT_HI_VARINST(CASE_INST_ID_);
 create index ACT_IDX_HI_VAR_INST_TENANT_ID on ACT_HI_VARINST(TENANT_ID_);
 create index ACT_IDX_HI_VAR_INST_PROC_DEF_KEY on ACT_HI_VARINST(PROC_DEF_KEY_);
+create index ACT_IDX_HI_VARINST_BYTEAR on ACT_HI_VARINST(BYTEARRAY_ID_);
 
 create index ACT_IDX_HI_INCIDENT_TENANT_ID on ACT_HI_INCIDENT(TENANT_ID_);
 create index ACT_IDX_HI_INCIDENT_PROC_DEF_KEY on ACT_HI_INCIDENT(PROC_DEF_KEY_);
+create index ACT_IDX_HI_INCIDENT_PROCINST on ACT_HI_INCIDENT(PROC_INST_ID_);
 
 create index ACT_IDX_HI_JOB_LOG_PROCINST on ACT_HI_JOB_LOG(PROCESS_INSTANCE_ID_);
 create index ACT_IDX_HI_JOB_LOG_PROCDEF on ACT_HI_JOB_LOG(PROCESS_DEF_ID_);
 create index ACT_IDX_HI_JOB_LOG_TENANT_ID on ACT_HI_JOB_LOG(TENANT_ID_);
 create index ACT_IDX_HI_JOB_LOG_JOB_DEF_ID on ACT_HI_JOB_LOG(JOB_DEF_ID_);
 create index ACT_IDX_HI_JOB_LOG_PROC_DEF_KEY on ACT_HI_JOB_LOG(PROCESS_DEF_KEY_);
+create index ACT_IDX_HI_JOB_LOG_EX_STACK on ACT_HI_JOB_LOG(JOB_EXCEPTION_STACK_ID_);
 
 create index ACT_HI_EXT_TASK_LOG_PROCINST on ACT_HI_EXT_TASK_LOG(PROC_INST_ID_);
 create index ACT_HI_EXT_TASK_LOG_PROCDEF on ACT_HI_EXT_TASK_LOG(PROC_DEF_ID_);
 create index ACT_HI_EXT_TASK_LOG_PROC_DEF_KEY on ACT_HI_EXT_TASK_LOG(PROC_DEF_KEY_);
 create index ACT_HI_EXT_TASK_LOG_TENANT_ID on ACT_HI_EXT_TASK_LOG(TENANT_ID_);
+create index ACT_IDX_HI_EXTTASKLOG_ERRORDET on ACT_HI_EXT_TASK_LOG(ERROR_DETAILS_ID_);
 
 create index ACT_IDX_HI_OP_LOG_PROCINST on ACT_HI_OP_LOG(PROC_INST_ID_);
 create index ACT_IDX_HI_OP_LOG_PROCDEF on ACT_HI_OP_LOG(PROC_DEF_ID_);
+
+create index ACT_IDX_HI_COMMENT_TASK on ACT_HI_COMMENT(TASK_ID_);
+create index ACT_IDX_HI_COMMENT_PROCINST on ACT_HI_COMMENT(PROC_INST_ID_);
+
+create index ACT_IDX_HI_ATTACHMENT_CONTENT on ACT_HI_ATTACHMENT(CONTENT_ID_);
+create index ACT_IDX_HI_ATTACHMENT_PROCINST on ACT_HI_ATTACHMENT(PROC_INST_ID_);
+create index ACT_IDX_HI_ATTACHMENT_TASK on ACT_HI_ATTACHMENT(TASK_ID_);
 create table ACT_HI_CASEINST (
     ID_ varchar(64) not null,
     CASE_INST_ID_ varchar(64) not null,
@@ -1059,8 +1076,8 @@ create table ACT_HI_DEC_IN (
     BYTEARRAY_ID_ varchar(64),
     DOUBLE_ double,
     LONG_ bigint,
-    TEXT_ LONGBLOB NULL,
-    TEXT2_ LONGBLOB NULL,
+    TEXT_ LONGBLOB,
+    TEXT2_ LONGBLOB,
     TENANT_ID_ varchar(64),
     primary key (ID_)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
@@ -1078,8 +1095,8 @@ create table ACT_HI_DEC_OUT (
     BYTEARRAY_ID_ varchar(64),
     DOUBLE_ double,
     LONG_ bigint,
-    TEXT_ LONGBLOB NULL,
-    TEXT2_ LONGBLOB NULL,
+    TEXT_ LONGBLOB,
+    TEXT2_ LONGBLOB,
     TENANT_ID_ varchar(64),
     primary key (ID_)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
@@ -1104,8 +1121,7 @@ create index ACT_IDX_HI_DEC_IN_CLAUSE on ACT_HI_DEC_IN(DEC_INST_ID_, CLAUSE_ID_)
 create index ACT_IDX_HI_DEC_OUT_INST on ACT_HI_DEC_OUT(DEC_INST_ID_);
 create index ACT_IDX_HI_DEC_OUT_RULE on ACT_HI_DEC_OUT(RULE_ORDER_, CLAUSE_ID_);
 
-
--- mariadb identity:
+-- mariadb_identity_7.8.0-ee
 
 create table ACT_ID_GROUP (
     ID_ varchar(64),
@@ -1192,4 +1208,3 @@ alter table ACT_ID_TENANT_MEMBER
     add constraint ACT_FK_TENANT_MEMB_GROUP
     foreign key (GROUP_ID_)
     references ACT_ID_GROUP (ID_);
-
diff --git a/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/main-schemas/MySQL-Requests-schema.sql b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/main-schemas/MySQL-Requests-schema.sql
index 947f464..4a7d189 100644
--- a/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/main-schemas/MySQL-Requests-schema.sql
+++ b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/main-schemas/MySQL-Requests-schema.sql
@@ -93,6 +93,7 @@
     create table OPERATION_STATUS (
         SERVICE_ID varchar(255) not null,
         OPERATION_ID varchar(256) not null,
+        SERVICE_NAME varchar(256),
         OPERATION_TYPE varchar(256),
         USER_ID varchar(256),
         RESULT varchar(256),