[MSO-8] Additional fixes for the second rebase

DB fixes + BPMN flows and groovy fixes + Fix issue with CloudConfig file
not reloaded properly when it's wrong (JSON error or model hierarchy
mistake) at MSO startup

Change-Id: I2853030b78499e2a761706b643ea210955e72de3
Signed-off-by: Determe, Sebastien (sd378r) <sd378r@intl.att.com>

[MSO-8] Restore files removed in patch set 2

Those groovy files must be there

Change-Id: I9a47ac3d9c8fc06774a1b8f518491b1b0b00af04
Signed-off-by: Determe, Sebastien (sd378r) <sd378r@intl.att.com>

2 files changed, 295 insertions, 297 deletions

diff --git a/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/drop_mariadb_engine_7.5.6.sql b/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/drop_mariadb_engine_7.5.6.sql
index 10e9930720..6ebc6e221e 100644
--- a/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/drop_mariadb_engine_7.5.6.sql
+++ b/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/drop_mariadb_engine_7.5.6.sql
@@ -1,76 +1,75 @@
--- Fix for https://itrack.web.att.com/browse/AJSCCMDA-90 --
-use camundabpmn;
-/* 
-Drop a archive tables
-*/
-/*-- TMP_ARCHIVING_PROCINST */
-DROP TABLE IF EXISTS TMP_ARCHIVING_PROCINST;
-
-/*-- TMP_ARCHIVING_BYTEARRAY */
-DROP TABLE IF EXISTS TMP_ARCHIVING_BYTEARRAY;
-
-/*-- TMP LOG TABLE */
-DROP TABLE IF EXISTS TMPLOGTABLE;
-
-/* -- Camunda Hi Tables --*/
-DROP TABLE IF EXISTS Camunda_Hi_Tables;
-
-/* drop own extentions columns:
-alter table  ARCHIVE_ACT_HI_PROCINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
-alter table  ARCHIVE_ACT_HI_ACTINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
-alter table  ARCHIVE_ACT_HI_TASKINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
-alter table  ARCHIVE_ACT_HI_VARINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
-alter table  ARCHIVE_ACT_HI_DETAIL DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
-alter table  ARCHIVE_ACT_HI_COMMENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
-alter table  ARCHIVE_ACT_HI_ATTACHMENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
-alter table  ARCHIVE_ACT_HI_OP_LOG DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
-alter table  ARCHIVE_ACT_HI_INCIDENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
-*/
-
-/*--#1 */
-DROP TABLE IF EXISTS ARCHIVE_ACT_HI_PROCINST;
-/*--#2 */
-DROP TABLE IF EXISTS ARCHIVE_ACT_HI_ACTINST;
-/*--#3 */
-DROP TABLE IF EXISTS ARCHIVE_ACT_HI_TASKINST;
-/*--#4 */
-DROP TABLE IF EXISTS ARCHIVE_ACT_HI_VARINST;
-/*--#5 */
-DROP TABLE IF EXISTS ARCHIVE_ACT_HI_DETAIL;
-/*--#6 */
-DROP TABLE IF EXISTS ARCHIVE_ACT_HI_COMMENT;
-/*--#7 */
-DROP TABLE IF EXISTS ARCHIVE_ACT_HI_ATTACHMENT;
-/*--#8 */
-DROP TABLE IF EXISTS ARCHIVE_ACT_HI_OP_LOG;
-/*--#9 */
-DROP TABLE IF EXISTS ARCHIVE_ACT_HI_INCIDENT;
-/*--#10 */
-DROP TABLE IF EXISTS ARCHIVE_ACT_GE_BYTEARRAY;
-
-/* drop PL SQL procedures: */
-DROP PROCEDURE IF EXISTS ARCHIVE_CAMUNDA_HISTORY;
-DROP PROCEDURE IF EXISTS ROLLB_ARCHIVE_CAMUNDA_HISTORY;
- 
-/*-- Sequence */
--- as sequence drop doesn't work automatically in MariaDB, use this procedure to drop sequence
- DROP PROCEDURE IF EXISTS DropSequence;
- 
-  DELIMITER //
- 
-  CREATE PROCEDURE DropSequence (vname VARCHAR(30))
-  BEGIN
-     -- Drop the sequence
-     DELETE FROM _sequences WHERE name = vname;  
-  END
-  //
-  DELIMITER ;
-
--- use the above procedure to drop sequence 
-CALL DropSequence('STAT_EXECUTION_SEQ');
-
-/*-- To Drop the MariaDB specific user defined procedures and functions */
-DROP FUNCTION IF EXISTS NextVal;
-DROP PROCEDURE IF EXISTS CreateSequence;
-DROP PROCEDURE IF EXISTS DropSequence;
+use camundabpmn;

+/* 

+Drop a archive tables

+*/

+/*-- TMP_ARCHIVING_PROCINST */

+DROP TABLE IF EXISTS TMP_ARCHIVING_PROCINST;

+

+/*-- TMP_ARCHIVING_BYTEARRAY */

+DROP TABLE IF EXISTS TMP_ARCHIVING_BYTEARRAY;

+

+/*-- TMP LOG TABLE */

+DROP TABLE IF EXISTS TMPLOGTABLE;

+

+/* -- Camunda Hi Tables --*/

+DROP TABLE IF EXISTS Camunda_Hi_Tables;

+

+/* drop own extentions columns:

+alter table  ARCHIVE_ACT_HI_PROCINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);

+alter table  ARCHIVE_ACT_HI_ACTINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);

+alter table  ARCHIVE_ACT_HI_TASKINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);

+alter table  ARCHIVE_ACT_HI_VARINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);

+alter table  ARCHIVE_ACT_HI_DETAIL DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);

+alter table  ARCHIVE_ACT_HI_COMMENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);

+alter table  ARCHIVE_ACT_HI_ATTACHMENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);

+alter table  ARCHIVE_ACT_HI_OP_LOG DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);

+alter table  ARCHIVE_ACT_HI_INCIDENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);

+*/

+

+/*--#1 */

+DROP TABLE IF EXISTS ARCHIVE_ACT_HI_PROCINST;

+/*--#2 */

+DROP TABLE IF EXISTS ARCHIVE_ACT_HI_ACTINST;

+/*--#3 */

+DROP TABLE IF EXISTS ARCHIVE_ACT_HI_TASKINST;

+/*--#4 */

+DROP TABLE IF EXISTS ARCHIVE_ACT_HI_VARINST;

+/*--#5 */

+DROP TABLE IF EXISTS ARCHIVE_ACT_HI_DETAIL;

+/*--#6 */

+DROP TABLE IF EXISTS ARCHIVE_ACT_HI_COMMENT;

+/*--#7 */

+DROP TABLE IF EXISTS ARCHIVE_ACT_HI_ATTACHMENT;

+/*--#8 */

+DROP TABLE IF EXISTS ARCHIVE_ACT_HI_OP_LOG;

+/*--#9 */

+DROP TABLE IF EXISTS ARCHIVE_ACT_HI_INCIDENT;

+/*--#10 */

+DROP TABLE IF EXISTS ARCHIVE_ACT_GE_BYTEARRAY;

+

+/* drop PL SQL procedures: */

+DROP PROCEDURE IF EXISTS ARCHIVE_CAMUNDA_HISTORY;

+DROP PROCEDURE IF EXISTS ROLLB_ARCHIVE_CAMUNDA_HISTORY;

+ 

+/*-- Sequence */

+-- as sequence drop doesn't work automatically in MariaDB, use this procedure to drop sequence

+ DROP PROCEDURE IF EXISTS DropSequence;

+ 

+  DELIMITER //

+ 

+  CREATE PROCEDURE DropSequence (vname VARCHAR(30))

+  BEGIN

+     -- Drop the sequence

+     DELETE FROM _sequences WHERE name = vname;  

+  END

+  //

+  DELIMITER ;

+

+-- use the above procedure to drop sequence 

+CALL DropSequence('STAT_EXECUTION_SEQ');

+

+/*-- To Drop the MariaDB specific user defined procedures and functions */

+DROP FUNCTION IF EXISTS NextVal;

+DROP PROCEDURE IF EXISTS CreateSequence;

+DROP PROCEDURE IF EXISTS DropSequence;

 DROP TABLE IF EXISTS _sequences;
\ No newline at end of file
diff --git a/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/mariadb_engine_7.5_patch_7.5.6.sql b/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/mariadb_engine_7.5_patch_7.5.6.sql
index 8c7faa0792..c5b69c7a34 100644
--- a/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/mariadb_engine_7.5_patch_7.5.6.sql
+++ b/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/mariadb_engine_7.5_patch_7.5.6.sql
@@ -1,222 +1,221 @@
--- Fix for https://itrack.web.att.com/browse/AJSCCMDA-90 --
-use camundabpmn;
-/*
- 1.  Add some Camunda Indexes to history schema part (for Archiving)
-*/
-create INDEX IF NOT EXISTS IDX_ACT_HI_TASKINST_PIID ON ACT_HI_TASKINST (PROC_INST_ID_); 
-create INDEX IF NOT EXISTS IDX_ACT_HI_COMMENT_PIID ON ACT_HI_COMMENT (PROC_INST_ID_);
-create INDEX IF NOT EXISTS IDX_ACT_HI_ATTACHMENT_PIID ON ACT_HI_ATTACHMENT (PROC_INST_ID_);
-create INDEX IF NOT EXISTS IDX_ACT_HI_OP_LOG_PIID ON ACT_HI_OP_LOG (PROC_INST_ID_);
-create INDEX IF NOT EXISTS IDX_ACT_HI_INCIDENT_PIID ON ACT_HI_INCIDENT (PROC_INST_ID_);
-create INDEX IF NOT EXISTS IDX_ACT_HI_ACTINST_PIID ON ACT_HI_ACTINST(PROC_INST_ID_); 
-
-
-/*
- 2.  Create Archiving Tables in current schema 
-*/
-
-/*-- TMP_ARCHIVING_PROCINST */
-CREATE TABLE TMP_ARCHIVING_PROCINST
-( PROC_INST_ID_ varchar(64) not null,
-  END_TIME_ datetime(3)
-);
-CREATE INDEX AI_TMP_ARCH_PROCINST_PI_ID ON TMP_ARCHIVING_PROCINST(PROC_INST_ID_);
-
-/*-- TMP_ARCHIVING_BYTEARRAY */
-CREATE TABLE TMP_ARCHIVING_BYTEARRAY
-( BYTEARRAY_ID_ varchar(64) not null,
-  PROC_INST_ID_ varchar(64)
-);
-CREATE INDEX AI_TMP_ARCH_BYTEARRAY_BAID ON TMP_ARCHIVING_BYTEARRAY(BYTEARRAY_ID_);
-
-
-/*--#1 ARCHIVE_ACT_HI_PROCINST; */
-create TABLE ARCHIVE_ACT_HI_PROCINST
-AS ( select * from ACT_HI_PROCINST where 1=0);
-
-create index AI_HI_PROCINST_END_TIME on ARCHIVE_ACT_HI_PROCINST(END_TIME_);
-ALTER TABLE ARCHIVE_ACT_HI_PROCINST ADD CONSTRAINT  ARCHIVE_ACT_HI_PROCINST_UQ UNIQUE ( PROC_INST_ID_);
-
-/*--#2   ARCHIVE_ACT_HI_ACTINST; */
-create TABLE ARCHIVE_ACT_HI_ACTINST
-AS ( select * from ACT_HI_ACTINST where 1=0);
-
-create index AI_HI_ACTINST_PROC_INST_ID on ARCHIVE_ACT_HI_ACTINST(PROC_INST_ID_);
-create index AI_HI_ACTINST_END_TIME on ARCHIVE_ACT_HI_ACTINST(END_TIME_);
-
-/*--#3  ARCHIVE_ACT_HI_TASKINST; */
-create TABLE ARCHIVE_ACT_HI_TASKINST
-AS ( select * from ACT_HI_TASKINST where 1=0);
-
-create index AI_HI_TASKINST_PROC_INST_ID on ARCHIVE_ACT_HI_TASKINST(PROC_INST_ID_);
-create index AI_HI_TASKINST_END_TIME on ARCHIVE_ACT_HI_TASKINST(END_TIME_);
-
-/*--#4 ARCHIVE_ACT_HI_VARINST; */
-create TABLE ARCHIVE_ACT_HI_VARINST
-AS ( select * from ACT_HI_VARINST where 1=0);
-
-create index AI_HI_VARINST_PROC_INST_ID on ARCHIVE_ACT_HI_VARINST(PROC_INST_ID_);
-
-/*--#5 ARCHIVE_ACT_HI_DETAIL; */
-create TABLE ARCHIVE_ACT_HI_DETAIL
-AS ( select * from ACT_HI_DETAIL where 1=0);
-
-create index AI_HI_DETAIL_PROC_INST_ID on ARCHIVE_ACT_HI_DETAIL(PROC_INST_ID_);
-create index AI_HI_DETAIL_TIME on ARCHIVE_ACT_HI_DETAIL(TIME_);
-
-/*--#6 ARCHIVE_ACT_HI_COMMENT; */
-create TABLE ARCHIVE_ACT_HI_COMMENT
-AS ( select * from ACT_HI_COMMENT where 1=0);
-
-create index AI_HI_COMMENT_PROC_INST_ID on ARCHIVE_ACT_HI_COMMENT(PROC_INST_ID_);
-create index AI_HI_COMMENT_TIME on ARCHIVE_ACT_HI_COMMENT(TIME_);
-
-/*--#7 ARCHIVE_ACT_HI_ATTACHMENT; */
-create TABLE ARCHIVE_ACT_HI_ATTACHMENT
-AS ( select * from ACT_HI_ATTACHMENT where 1=0);
-
-create index AI_HI_ATTACHMENT_PROC_INST_ID on ARCHIVE_ACT_HI_ATTACHMENT(PROC_INST_ID_);
-
-/*--#8 ARCHIVE_ACT_HI_OP_LOG; */
-create TABLE ARCHIVE_ACT_HI_OP_LOG
-AS ( select * from ACT_HI_OP_LOG where 1=0);
-
-create index AI_HI_OP_LOG_PROC_INST_ID on ARCHIVE_ACT_HI_OP_LOG(PROC_INST_ID_);
-create index AI_HI_OP_LOG_TIMESTAMP on ARCHIVE_ACT_HI_OP_LOG(TIMESTAMP_);
-
-/*--#9 ARCHIVE_ACT_HI_INCIDENT; */
-create TABLE ARCHIVE_ACT_HI_INCIDENT
-AS ( select * from ACT_HI_INCIDENT where 1=0);
-
-create index AI_HI_INCIDENT_PROC_INST_ID on ARCHIVE_ACT_HI_INCIDENT(PROC_INST_ID_);
-
-/*--#10 ARCHIVE_ACT_GE_BYTEARRAY; */
-create TABLE ARCHIVE_ACT_GE_BYTEARRAY
-AS ( select * from ACT_GE_BYTEARRAY where 1=0);
-
-create index AI_GE_BYTEARRAY_ID_ on ARCHIVE_ACT_GE_BYTEARRAY(ID_);
-
-/* -----------------------------------------------------------------------------
-Extend a ARCHIVE: Table by two attributes: STAT_EXECUTION_ID, STAT_EXECUTION_TS 
-*/
- 
-/*
---TEMPLATE:
-alter table ARCHIVE_%TableName%
-    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
-CREATE INDEX AI_%TableName%_EXE_ID ON ARCHIVE_%TableName%(STAT_EXECUTION_ID);
-*/
-
-
-/*--#1 ACT_HI_PROCINST */
-alter table ARCHIVE_ACT_HI_PROCINST
-    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
-CREATE INDEX AI_ACT_HI_PROCINST_EXE_ID ON ARCHIVE_ACT_HI_PROCINST(STAT_EXECUTION_ID);
-
-/*--#2 ACT_HI_ACTINST */
-alter table ARCHIVE_ACT_HI_ACTINST
-    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
-CREATE INDEX AI_ACT_HI_ACTINST_EXE_ID ON ARCHIVE_ACT_HI_ACTINST(STAT_EXECUTION_ID);
-
-/*--#3 ACT_HI_TASKINST */
-alter table ARCHIVE_ACT_HI_TASKINST
-    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
-CREATE INDEX AI_ACT_HI_TASKINST_EXE_ID ON ARCHIVE_ACT_HI_TASKINST(STAT_EXECUTION_ID);
-
-/*--#4 ACT_HI_VARINST */
-alter table ARCHIVE_ACT_HI_VARINST
-    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
-CREATE INDEX AI_ACT_HI_VARINST_EXE_ID ON ARCHIVE_ACT_HI_VARINST(STAT_EXECUTION_ID);
-
-/*--#5 ACT_HI_DETAIL */
-alter table ARCHIVE_ACT_HI_DETAIL
-    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
-CREATE INDEX AI_ACT_HI_DETAIL_EXE_ID ON ARCHIVE_ACT_HI_DETAIL(STAT_EXECUTION_ID);
-
-/*--#6 ACT_HI_COMMENT */
-alter table ARCHIVE_ACT_HI_COMMENT
-    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
-CREATE INDEX AI_ACT_HI_COMMENT_EXE_ID ON ARCHIVE_ACT_HI_COMMENT(STAT_EXECUTION_ID);
-
-/*--#7 ACT_HI_ATTACHMENT */
-alter table ARCHIVE_ACT_HI_ATTACHMENT
-    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
-CREATE INDEX AI_ACT_HI_ATTACHMENT_EXE_ID ON ARCHIVE_ACT_HI_ATTACHMENT(STAT_EXECUTION_ID);
-
-/*--#8 ACT_HI_OP_LOG */
-alter table ARCHIVE_ACT_HI_OP_LOG
-    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
-CREATE INDEX AI_ACT_HI_OP_LOG_EXE_ID ON ARCHIVE_ACT_HI_OP_LOG(STAT_EXECUTION_ID);
-
-/*--#9 ACT_HI_INCIDENT */
-alter table ARCHIVE_ACT_HI_INCIDENT
-    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
-CREATE INDEX AI_ACT_HI_INCIDENT_EXE_ID ON ARCHIVE_ACT_HI_INCIDENT(STAT_EXECUTION_ID);
-
-/*--#10 ACT_GE_BYTEARRAY */
-alter table ARCHIVE_ACT_GE_BYTEARRAY
-    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
-CREATE INDEX AI_ACT_GE_BYTEARRAY_EXE_ID ON ARCHIVE_ACT_GE_BYTEARRAY(STAT_EXECUTION_ID);
-
-
-/* -- Next Val as a user defined function needed only in MariaDB--*/
-DROP FUNCTION IF EXISTS NextVal;
-  DELIMITER //
-  CREATE FUNCTION NextVal (vname VARCHAR(30))
-    RETURNS INT
-  BEGIN
-     -- Retrieve and update in single statement
-     UPDATE _sequences
-       SET next = next + 1
-       WHERE name = vname;
- 
-     RETURN (SELECT next FROM _sequences LIMIT 1);
-  END
-  //
-  DELIMITER ;
-  
-/* -- History tables for use in archive procedure, there is no array type in MariaDB --*/
-Create Table Camunda_Hi_Tables (id_ MEDIUMINT NOT NULL AUTO_INCREMENT,
-     TableName_ varchar(80) NOT NULL,
-     PRIMARY KEY (id_));
-   
-Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_PROCINST');
-Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_ACTINST');
-Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_TASKINST');
-Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_VARINST');
-Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_DETAIL');
-Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_COMMENT');
-Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_ATTACHMENT');
-Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_OP_LOG');
-Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_INCIDENT');
-
-/*-- log table --*/
-CREATE TABLE TMPLOGTABLE (LogMessage Varchar(700));
-
-
-/* -- Below user defined functions and procedures needed only in MariaDB, they are in-built in Oracle --*/
-/*-- Create a sequence SP */
-DROP PROCEDURE IF EXISTS CreateSequence;
-  DELIMITER //
-  CREATE PROCEDURE CreateSequence (name VARCHAR(30), start INT, inc INT)
-  BEGIN
-     -- Create a table to store sequences
-     CREATE TABLE IF NOT EXISTS _sequences
-     (
-         name VARCHAR(70) NOT NULL UNIQUE,
-         next INT NOT NULL,
-         inc INT NOT NULL
-     );
- 
-     -- Add the new sequence
-     INSERT INTO _sequences VALUES (name, start, inc);  
-  END
-  //
-  DELIMITER ;
-
-/*--------------------------------------------------------------------------------------------------
- Add Meta to Archive
- -------------------------------------------------------------------------------------------------- */
-
-/* Create STAT_EXECUTION_SEQ: each Archive Entry has a same Execution ID during one Archiving Run */
-CALL CreateSequence('STAT_EXECUTION_SEQ', 1, 1);
+use camundabpmn;

+/*

+ 1.  Add some Camunda Indexes to history schema part (for Archiving)

+*/

+create INDEX IF NOT EXISTS IDX_ACT_HI_TASKINST_PIID ON ACT_HI_TASKINST (PROC_INST_ID_); 

+create INDEX IF NOT EXISTS IDX_ACT_HI_COMMENT_PIID ON ACT_HI_COMMENT (PROC_INST_ID_);

+create INDEX IF NOT EXISTS IDX_ACT_HI_ATTACHMENT_PIID ON ACT_HI_ATTACHMENT (PROC_INST_ID_);

+create INDEX IF NOT EXISTS IDX_ACT_HI_OP_LOG_PIID ON ACT_HI_OP_LOG (PROC_INST_ID_);

+create INDEX IF NOT EXISTS IDX_ACT_HI_INCIDENT_PIID ON ACT_HI_INCIDENT (PROC_INST_ID_);

+create INDEX IF NOT EXISTS IDX_ACT_HI_ACTINST_PIID ON ACT_HI_ACTINST(PROC_INST_ID_); 

+

+

+/*

+ 2.  Create Archiving Tables in current schema 

+*/

+

+/*-- TMP_ARCHIVING_PROCINST */

+CREATE TABLE TMP_ARCHIVING_PROCINST

+( PROC_INST_ID_ varchar(64) not null,

+  END_TIME_ datetime(3)

+);

+CREATE INDEX AI_TMP_ARCH_PROCINST_PI_ID ON TMP_ARCHIVING_PROCINST(PROC_INST_ID_);

+

+/*-- TMP_ARCHIVING_BYTEARRAY */

+CREATE TABLE TMP_ARCHIVING_BYTEARRAY

+( BYTEARRAY_ID_ varchar(64) not null,

+  PROC_INST_ID_ varchar(64)

+);

+CREATE INDEX AI_TMP_ARCH_BYTEARRAY_BAID ON TMP_ARCHIVING_BYTEARRAY(BYTEARRAY_ID_);

+

+

+/*--#1 ARCHIVE_ACT_HI_PROCINST; */

+create TABLE ARCHIVE_ACT_HI_PROCINST

+AS ( select * from ACT_HI_PROCINST where 1=0);

+

+create index AI_HI_PROCINST_END_TIME on ARCHIVE_ACT_HI_PROCINST(END_TIME_);

+ALTER TABLE ARCHIVE_ACT_HI_PROCINST ADD CONSTRAINT  ARCHIVE_ACT_HI_PROCINST_UQ UNIQUE ( PROC_INST_ID_);

+

+/*--#2   ARCHIVE_ACT_HI_ACTINST; */

+create TABLE ARCHIVE_ACT_HI_ACTINST

+AS ( select * from ACT_HI_ACTINST where 1=0);

+

+create index AI_HI_ACTINST_PROC_INST_ID on ARCHIVE_ACT_HI_ACTINST(PROC_INST_ID_);

+create index AI_HI_ACTINST_END_TIME on ARCHIVE_ACT_HI_ACTINST(END_TIME_);

+

+/*--#3  ARCHIVE_ACT_HI_TASKINST; */

+create TABLE ARCHIVE_ACT_HI_TASKINST

+AS ( select * from ACT_HI_TASKINST where 1=0);

+

+create index AI_HI_TASKINST_PROC_INST_ID on ARCHIVE_ACT_HI_TASKINST(PROC_INST_ID_);

+create index AI_HI_TASKINST_END_TIME on ARCHIVE_ACT_HI_TASKINST(END_TIME_);

+

+/*--#4 ARCHIVE_ACT_HI_VARINST; */

+create TABLE ARCHIVE_ACT_HI_VARINST

+AS ( select * from ACT_HI_VARINST where 1=0);

+

+create index AI_HI_VARINST_PROC_INST_ID on ARCHIVE_ACT_HI_VARINST(PROC_INST_ID_);

+

+/*--#5 ARCHIVE_ACT_HI_DETAIL; */

+create TABLE ARCHIVE_ACT_HI_DETAIL

+AS ( select * from ACT_HI_DETAIL where 1=0);

+

+create index AI_HI_DETAIL_PROC_INST_ID on ARCHIVE_ACT_HI_DETAIL(PROC_INST_ID_);

+create index AI_HI_DETAIL_TIME on ARCHIVE_ACT_HI_DETAIL(TIME_);

+

+/*--#6 ARCHIVE_ACT_HI_COMMENT; */

+create TABLE ARCHIVE_ACT_HI_COMMENT

+AS ( select * from ACT_HI_COMMENT where 1=0);

+

+create index AI_HI_COMMENT_PROC_INST_ID on ARCHIVE_ACT_HI_COMMENT(PROC_INST_ID_);

+create index AI_HI_COMMENT_TIME on ARCHIVE_ACT_HI_COMMENT(TIME_);

+

+/*--#7 ARCHIVE_ACT_HI_ATTACHMENT; */

+create TABLE ARCHIVE_ACT_HI_ATTACHMENT

+AS ( select * from ACT_HI_ATTACHMENT where 1=0);

+

+create index AI_HI_ATTACHMENT_PROC_INST_ID on ARCHIVE_ACT_HI_ATTACHMENT(PROC_INST_ID_);

+

+/*--#8 ARCHIVE_ACT_HI_OP_LOG; */

+create TABLE ARCHIVE_ACT_HI_OP_LOG

+AS ( select * from ACT_HI_OP_LOG where 1=0);

+

+create index AI_HI_OP_LOG_PROC_INST_ID on ARCHIVE_ACT_HI_OP_LOG(PROC_INST_ID_);

+create index AI_HI_OP_LOG_TIMESTAMP on ARCHIVE_ACT_HI_OP_LOG(TIMESTAMP_);

+

+/*--#9 ARCHIVE_ACT_HI_INCIDENT; */

+create TABLE ARCHIVE_ACT_HI_INCIDENT

+AS ( select * from ACT_HI_INCIDENT where 1=0);

+

+create index AI_HI_INCIDENT_PROC_INST_ID on ARCHIVE_ACT_HI_INCIDENT(PROC_INST_ID_);

+

+/*--#10 ARCHIVE_ACT_GE_BYTEARRAY; */

+create TABLE ARCHIVE_ACT_GE_BYTEARRAY

+AS ( select * from ACT_GE_BYTEARRAY where 1=0);

+

+create index AI_GE_BYTEARRAY_ID_ on ARCHIVE_ACT_GE_BYTEARRAY(ID_);

+

+/* -----------------------------------------------------------------------------

+Extend a ARCHIVE: Table by two attributes: STAT_EXECUTION_ID, STAT_EXECUTION_TS 

+*/

+ 

+/*

+--TEMPLATE:

+alter table ARCHIVE_%TableName%

+    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );

+CREATE INDEX AI_%TableName%_EXE_ID ON ARCHIVE_%TableName%(STAT_EXECUTION_ID);

+*/

+

+

+/*--#1 ACT_HI_PROCINST */

+alter table ARCHIVE_ACT_HI_PROCINST

+    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );

+CREATE INDEX AI_ACT_HI_PROCINST_EXE_ID ON ARCHIVE_ACT_HI_PROCINST(STAT_EXECUTION_ID);

+

+/*--#2 ACT_HI_ACTINST */

+alter table ARCHIVE_ACT_HI_ACTINST

+    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );

+CREATE INDEX AI_ACT_HI_ACTINST_EXE_ID ON ARCHIVE_ACT_HI_ACTINST(STAT_EXECUTION_ID);

+

+/*--#3 ACT_HI_TASKINST */

+alter table ARCHIVE_ACT_HI_TASKINST

+    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );

+CREATE INDEX AI_ACT_HI_TASKINST_EXE_ID ON ARCHIVE_ACT_HI_TASKINST(STAT_EXECUTION_ID);

+

+/*--#4 ACT_HI_VARINST */

+alter table ARCHIVE_ACT_HI_VARINST

+    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );

+CREATE INDEX AI_ACT_HI_VARINST_EXE_ID ON ARCHIVE_ACT_HI_VARINST(STAT_EXECUTION_ID);

+

+/*--#5 ACT_HI_DETAIL */

+alter table ARCHIVE_ACT_HI_DETAIL

+    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );

+CREATE INDEX AI_ACT_HI_DETAIL_EXE_ID ON ARCHIVE_ACT_HI_DETAIL(STAT_EXECUTION_ID);

+

+/*--#6 ACT_HI_COMMENT */

+alter table ARCHIVE_ACT_HI_COMMENT

+    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );

+CREATE INDEX AI_ACT_HI_COMMENT_EXE_ID ON ARCHIVE_ACT_HI_COMMENT(STAT_EXECUTION_ID);

+

+/*--#7 ACT_HI_ATTACHMENT */

+alter table ARCHIVE_ACT_HI_ATTACHMENT

+    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );

+CREATE INDEX AI_ACT_HI_ATTACHMENT_EXE_ID ON ARCHIVE_ACT_HI_ATTACHMENT(STAT_EXECUTION_ID);

+

+/*--#8 ACT_HI_OP_LOG */

+alter table ARCHIVE_ACT_HI_OP_LOG

+    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );

+CREATE INDEX AI_ACT_HI_OP_LOG_EXE_ID ON ARCHIVE_ACT_HI_OP_LOG(STAT_EXECUTION_ID);

+

+/*--#9 ACT_HI_INCIDENT */

+alter table ARCHIVE_ACT_HI_INCIDENT

+    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );

+CREATE INDEX AI_ACT_HI_INCIDENT_EXE_ID ON ARCHIVE_ACT_HI_INCIDENT(STAT_EXECUTION_ID);

+

+/*--#10 ACT_GE_BYTEARRAY */

+alter table ARCHIVE_ACT_GE_BYTEARRAY

+    add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );

+CREATE INDEX AI_ACT_GE_BYTEARRAY_EXE_ID ON ARCHIVE_ACT_GE_BYTEARRAY(STAT_EXECUTION_ID);

+

+

+/* -- Next Val as a user defined function needed only in MariaDB--*/

+DROP FUNCTION IF EXISTS NextVal;

+  DELIMITER //

+  CREATE FUNCTION NextVal (vname VARCHAR(30))

+    RETURNS INT

+  BEGIN

+     -- Retrieve and update in single statement

+     UPDATE _sequences

+       SET next = next + 1

+       WHERE name = vname;

+ 

+     RETURN (SELECT next FROM _sequences LIMIT 1);

+  END

+  //

+  DELIMITER ;

+  

+/* -- History tables for use in archive procedure, there is no array type in MariaDB --*/

+Create Table Camunda_Hi_Tables (id_ MEDIUMINT NOT NULL AUTO_INCREMENT,

+     TableName_ varchar(80) NOT NULL,

+     PRIMARY KEY (id_));

+   

+Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_PROCINST');

+Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_ACTINST');

+Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_TASKINST');

+Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_VARINST');

+Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_DETAIL');

+Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_COMMENT');

+Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_ATTACHMENT');

+Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_OP_LOG');

+Insert Into Camunda_Hi_Tables(TableName_)  Values ('ACT_HI_INCIDENT');

+

+/*-- log table --*/

+CREATE TABLE TMPLOGTABLE (LogMessage Varchar(700));

+

+

+/* -- Below user defined functions and procedures needed only in MariaDB, they are in-built in Oracle --*/

+/*-- Create a sequence SP */

+DROP PROCEDURE IF EXISTS CreateSequence;

+  DELIMITER //

+  CREATE PROCEDURE CreateSequence (name VARCHAR(30), start INT, inc INT)

+  BEGIN

+     -- Create a table to store sequences

+     CREATE TABLE IF NOT EXISTS _sequences

+     (

+         name VARCHAR(70) NOT NULL UNIQUE,

+         next INT NOT NULL,

+         inc INT NOT NULL

+     );

+ 

+     -- Add the new sequence

+     INSERT INTO _sequences VALUES (name, start, inc);  

+  END

+  //

+  DELIMITER ;

+

+/*--------------------------------------------------------------------------------------------------

+ Add Meta to Archive

+ -------------------------------------------------------------------------------------------------- */

+

+/* Create STAT_EXECUTION_SEQ: each Archive Entry has a same Execution ID during one Archiving Run */

+CALL CreateSequence('STAT_EXECUTION_SEQ', 1, 1);