diff options
Diffstat (limited to 'bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive')
4 files changed, 648 insertions, 0 deletions
diff --git a/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/ARCHIVE_CAMUNDA_HISTORY-Default Store Procedure.sql b/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/ARCHIVE_CAMUNDA_HISTORY-Default Store Procedure.sql new file mode 100644 index 0000000000..67c139ed31 --- /dev/null +++ b/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/ARCHIVE_CAMUNDA_HISTORY-Default Store Procedure.sql @@ -0,0 +1,140 @@ +/* +Camunda Version: 7.5.4-ee; MariaDB tested +Date: 11.30.2016 +Balaji Mudipalli, AJSC Camunda Team + +DOC.: +-------------------------------------------------------------------------------------- +Create ARCHIVE_CAMUNDA_HISTORY StoreProcedure -function for archiving of history camunda tables. +*/ + +/* uncomment below statement and run for your db, e.g. : use camundabpmn; + */ +-- use <db_name>; +use camundabpmn; +DROP PROCEDURE IF EXISTS ARCHIVE_CAMUNDA_HISTORY; + +DELIMITER // + +CREATE PROCEDURE ARCHIVE_CAMUNDA_HISTORY(IN IN_periodInDays INT, IN IN_maxProcessInstances INT) +MODIFIES SQL DATA + +BEGIN + DECLARE P_hiTableCount INT; + DECLARE P_executionId BIGINT; + DECLARE P_piProcessed DOUBLE; + DECLARE P_baProcessed DOUBLE; + DECLARE P_startDate DATE; + DECLARE P_executionDuration double; + + DECLARE not_found INT DEFAULT 0; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1; + + /* START TRANSACTION */ + set P_startDate = sysdate(); + set P_executionId = NextVal('STAT_EXECUTION_SEQ'); + + INSERT INTO TMPLOGTABLE SELECT CONCAT('P_executionId value is ',P_executionId); + + DELETE FROM TMP_ARCHIVING_PROCINST; + DELETE FROM TMP_ARCHIVING_BYTEARRAY; + -- temp table -- + DELETE FROM TMPLOGTABLE; + + + /* 1. Set Default Value for Max Pi's */ + IF IN_maxProcessInstances = 0 THEN SET IN_maxProcessInstances = 1000; END IF; + IF IN_maxProcessInstances > 1000 THEN SET IN_maxProcessInstances = 1000; END IF; + + INSERT INTO TMPLOGTABLE SELECT CONCAT('IN_maxProcessInstances value is: ',IN_maxProcessInstances); + + /* 2. Fill TMP_ARCHIVING_PROCINST with candidates: */ + IF IN_maxProcessInstances = 0 THEN /* all */ + INSERT INTO TMP_ARCHIVING_PROCINST + SELECT hi.PROC_INST_ID_, hi.END_TIME_ + FROM ACT_HI_PROCINST hi + WHERE hi.END_TIME_ IS NOT NULL + AND hi.END_TIME_ <= ( DATE_SUB(SYSDATE(), INTERVAL IN_periodInDays DAY)); + + ELSE /* limit: IN_maxProcessInstances */ + INSERT INTO TMP_ARCHIVING_PROCINST + (PROC_INST_ID_, END_TIME_) ( + SELECT hi2.PROC_INST_ID_, hi2.END_TIME_ + FROM ACT_HI_PROCINST hi2 + WHERE hi2.END_TIME_ IS NOT NULL + AND hi2.END_TIME_ <= ( DATE_SUB(SYSDATE(), INTERVAL IN_periodInDays DAY)) + ) LIMIT IN_maxProcessInstances; + END IF; + + /* 3. Check PI's im TEMP if any found, ready for ACHIVING */ + select count(*) INTO P_piProcessed FROM TMP_ARCHIVING_PROCINST; + + IF P_piProcessed = 0 THEN + INSERT INTO TMPLOGTABLE SELECT CONCAT('P_piProcessed value is: ',P_piProcessed); + ROLLBACK; + /* 4. Move data from history to archive (insert to archive and delete in history) */ + ELSE + SELECT COUNT(*)+1 INTO P_hiTableCount FROM camunda_hi_tables; + INSERT INTO TMPLOGTABLE SELECT CONCAT('P_hiTableCount value is ',P_hiTableCount); + SET @i = 1; + WHILE @i < P_hiTableCount + DO + SELECT TableName_ INTO @P_tableName FROM camunda_hi_tables WHERE id_ = @i; + INSERT INTO TMPLOGTABLE SELECT CONCAT('P_tableName: ', @P_tableName); + + Set @P_archiveTableName = Concat('ARCHIVE_',IFNULL(@P_tableName, '')); + INSERT INTO TMPLOGTABLE SELECT CONCAT('@P_archiveTableName: ', @P_archiveTableName); + + SET @query1 = CONCAT('INSERT INTO ', @P_archiveTableName , + ' SELECT hi3.*, ',P_executionId, ', NOW() FROM ', @P_tableName,' hi3 + WHERE hi3.PROC_INST_ID_ in ( SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)'); + INSERT INTO TMPLOGTABLE SELECT CONCAT('@query1: ', @query1); + + PREPARE stmt1 FROM @query1; + EXECUTE stmt1; + DEALLOCATE PREPARE stmt1; + + SET @query2 = CONCAT ('DELETE ACT FROM ',@P_tableName,' ACT INNER JOIN TMP_ARCHIVING_PROCINST TMP ON ACT.PROC_INST_ID_ = TMP.PROC_INST_ID_'); + INSERT INTO TMPLOGTABLE SELECT CONCAT('@query2: ', @query2); + + PREPARE stmt2 FROM @query2; + EXECUTE stmt2; + DEALLOCATE PREPARE stmt2; + + SET @i = @i+1; + END WHILE; + /* select bytearray_ids */ + INSERT INTO TMP_ARCHIVING_BYTEARRAY + SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_VARINST archvar + where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST) + AND archvar.BYTEARRAY_ID_ is not null; + + INSERT INTO TMP_ARCHIVING_BYTEARRAY + SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_DETAIL archvar + where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST) + AND archvar.BYTEARRAY_ID_ is not null; + + /* 5. Check Bytearrays im TEMP if any found, ready for ACHIVING */ + select count(*) INTO P_baProcessed FROM TMP_ARCHIVING_BYTEARRAY; + + /* INSERT */ + INSERT INTO ARCHIVE_ACT_GE_BYTEARRAY + SELECT hi4.*, P_executionId, NOW() FROM ACT_GE_BYTEARRAY hi4 + WHERE hi4.ID_ in ( SELECT BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY); + + /* DELETE */ + DELETE FROM ACT_GE_BYTEARRAY WHERE ID_ in (select BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY); + + /* COMMIT TRANSACTION */ + COMMIT; + INSERT INTO TMPLOGTABLE SELECT CONCAT('P_executionId is ', P_executionId); + + set P_executionDuration = DATEDIFF(sysdate(), P_startDate); + + INSERT INTO TMPLOGTABLE SELECT CONCAT('SP success and P_executionDuration is ', ifnull((round(P_executionDuration*24*60*60, 1)), ''), ' sec.'); + END IF; + + END; +// + +DELIMITER ;
\ No newline at end of file diff --git a/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/ROLLB_ARCHIVE_CAMUNDA_HISTORY-StoreProcedure.sql b/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/ROLLB_ARCHIVE_CAMUNDA_HISTORY-StoreProcedure.sql new file mode 100644 index 0000000000..f3ba294dba --- /dev/null +++ b/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/ROLLB_ARCHIVE_CAMUNDA_HISTORY-StoreProcedure.sql @@ -0,0 +1,210 @@ +/* +Camunda Version: 7.5.4-ee; MariaDB tested +Date: 11.30.2016 +Balaji Mudipalli, AJSC Camunda Team + +DOC.: +-------------------------------------------------------------------------------------- +Create ROLLB_ARCHIVE_CAMUNDA_HISTORY StoreProcedure for ROLLBACK (RESTORE) +of archived Camunda history tables. +*/ + +/* uncomment below statement and run for your db, e.g. : use camundabpmn; + */ +-- use <db_name>; +use camundabpmn; + +DROP PROCEDURE IF EXISTS ROLLB_ARCHIVE_CAMUNDA_HISTORY; + +DELIMITER // +CREATE PROCEDURE ROLLB_ARCHIVE_CAMUNDA_HISTORY(IN IN_executionId_from INT, + IN IN_executionId_til INT, + IN IN_maxProcessInstances INT) +MODIFIES SQL DATA + +BEGIN + DECLARE P_hiTableCount INT; + DECLARE P_piProcessed DOUBLE; + DECLARE P_baProcessed DOUBLE; + DECLARE P_query VARCHAR(600); + DECLARE P_startDate DATETIME; + DECLARE P_executionDuration DOUBLE; + DECLARE P_result NVARCHAR(400); +DECLARE not_found INT DEFAULT 0; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1; + + + /* START TRANSACTION */ + SET P_startDate = sysdate(); + DELETE FROM TMPLOGTABLE; + + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: START EXECUTION: ' , ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') , + '; PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') , + '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') , + '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, '')); + + /* 1. Truncate TMP_ARCHIVING_PROCINST */ + + DELETE FROM TMP_ARCHIVING_PROCINST; + DELETE FROM TMP_ARCHIVING_BYTEARRAY; + + /* 2. Fill TMP_ARCHIVING_PROCINST with candidates: */ + IF IN_executionId_til = -1 THEN /* IN_executionId_from only */ + SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID = ' , IFNULL(IN_executionId_from, '')); + + ELSEIF IN_executionId_til = 0 THEN /* all from IN_executionId_from */ + SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID >= ' , IFNULL(IN_executionId_from, '')); + + ELSE /* between IN_executionId_from AND IN_executionId_til */ + SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID between ', IFNULL(IN_executionId_from, '') , ' AND ' , IFNULL(IN_executionId_til, '')); + END IF; + + IF IN_maxProcessInstances = 0 THEN /* all */ + + SET @P_query1 = CONCAT('INSERT INTO TMP_ARCHIVING_PROCINST ', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' SELECT PROC_INST_ID_, END_TIME_ FROM ARCHIVE_ACT_HI_PROCINST ', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' ', Ifnull(P_query, '')); + + ELSE /* limit: IN_maxProcessInstances */ + SET @P_query1 = CONCAT('INSERT INTO TMP_ARCHIVING_PROCINST ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' (PROC_INST_ID_, END_TIME_ ) ( ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' SELECT PROC_INST_ID_, END_TIME_ FROM ARCHIVE_ACT_HI_PROCINST ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' ', Ifnull(P_query, '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ') LIMIT ', IFNULL(IN_maxProcessInstances, '')); + END IF; + + INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /fill temp table with PI candidates/ ' , Ifnull(P_query, '')); + + PREPARE stmt1 FROM @P_query1; + EXECUTE stmt1; + DEALLOCATE PREPARE stmt1; + + INSERT INTO TMPLOGTABLE SELECT CONCAT('.... rows inserted into TMP_ARCHIVING_PROCINST: ' , IFNULL((ROW_COUNT()), '')); + + /* 3. Fill TMP_ARCHIVING_BYTEARRAYS with candidates: */ + INSERT INTO TMP_ARCHIVING_BYTEARRAY + SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_VARINST archvar + where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST) + AND archvar.BYTEARRAY_ID_ is not null; + + INSERT INTO TMP_ARCHIVING_BYTEARRAY + SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_DETAIL archvar + where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST) + AND archvar.BYTEARRAY_ID_ is not null; + + select count(*) INTO P_baProcessed FROM TMP_ARCHIVING_BYTEARRAY; + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: ', IFNULL(P_baProcessed, '') ,' ByteArray candidates for rollback found!' , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); + + + /* 4. Check PI's im TEMP ready for ROLLBACK */ + select count(*) INTO P_piProcessed FROM TMP_ARCHIVING_PROCINST; + + IF P_piProcessed = 0 THEN /* no candidates found */ + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: NO ProcessInstance-Candidates for archive-Rollback found! '); + INSERT INTO TMPLOGTABLE SELECT CONCAT('Try TA-ROLLBACK ...'); + ROLLBACK; /*-- TMP_ARCHIVING_PROCINST un-Delete */ + INSERT INTO TMPLOGTABLE SELECT CONCAT('TA-ROLLBACK DONE! ...' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); + + SET P_result = CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: NO ProcessInstance candidates for archive-Rollback found!', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' Used PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') , + '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') , + '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, '')); + + INSERT INTO TMPLOGTABLE SELECT CONCAT(P_result); + + ELSE + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: ', IFNULL(P_piProcessed, '') ,' ProcessInstance candidates for Rollback found!'); + + /* LOOP over tables */ + SELECT COUNT(*)+1 INTO P_hiTableCount FROM camunda_hi_tables; + + INSERT INTO TMPLOGTABLE SELECT CONCAT('P_hiTableCount value is ',P_hiTableCount); + + SET @i = 1; + + WHILE @i < P_hiTableCount + DO + SELECT TableName_ INTO @P_tableName FROM camunda_hi_tables WHERE id_ = @i; + + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: ####### Start restore from: ARCHIVE_' , IFNULL(@P_tableName, '') ,' ...'); + + SET @P_tableFields = CONCAT(''); + + select GROUP_CONCAT(column_name order by ordinal_position) + INTO @P_tableFields + from information_schema.columns + where table_schema = (select DATABASE()) AND TABLE_NAME = @P_tableName; + + /* INSERT */ + SET @P_query2 = CONCAT('INSERT INTO ', IFNULL(@P_tableName, '') , + ' SELECT ' , @P_tableFields, + ' FROM ARCHIVE_' , IFNULL(@P_tableName, '') , + ' WHERE PROC_INST_ID_ in ( SELECT tmp.PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST tmp)'); + + INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /copy back to history table/ ' , Ifnull(@P_query2, '')); + + PREPARE stmt2 FROM @P_query2; + EXECUTE stmt2; + DEALLOCATE PREPARE stmt2; + + INSERT INTO TMPLOGTABLE SELECT Concat('.... rows inserted: ' , IFNULL((ROW_COUNT()), '')); + + /* DELETE */ + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: Delete in Archive: ARCHIVE_' , IFNULL(@P_tableName, '') ,' ...'); + /* SET @P_query3 = CONCAT(' DELETE FROM ARCHIVE_' , IFNULL(@P_tableName, '') , ' WHERE PROC_INST_ID_ in (select PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)'); */ + SET @P_query3 = CONCAT('DELETE ARCH FROM ARCHIVE_' , IFNULL(@P_tableName, '') , + ' ARCH INNER JOIN TMP_ARCHIVING_PROCINST TMP ON ARCH.PROC_INST_ID_ = TMP.PROC_INST_ID_'); + INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): ' , Ifnull(@P_query3, '')); + PREPARE stmt3 FROM @P_query3; + EXECUTE stmt3; + DEALLOCATE PREPARE stmt3; + + INSERT INTO TMPLOGTABLE SELECT Concat('.... rows deleted: ' , IFNULL((ROW_COUNT()), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); + + SET @i = @i+1; + END WHILE; + + /* INSERT */ + SET @P_tableFields2 = CONCAT(''); /* reset, becouse had some problems with double columns */ + /* fetch table column names into P_tableFields : */ + select GROUP_CONCAT(COLUMN_NAME order by ordinal_position) + INTO @P_tableFields2 + from information_schema.columns + where table_schema = (select DATABASE()) AND TABLE_NAME = 'ACT_GE_BYTEARRAY'; + + SET @P_query4 = CONCAT('INSERT INTO ACT_GE_BYTEARRAY ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' SELECT ' , IFNULL(@P_tableFields2, '') ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' FROM ARCHIVE_ACT_GE_BYTEARRAY' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' WHERE ID_ in ( SELECT tmp.BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY tmp)'); + INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /copy back to history table/ ' , Ifnull(@P_query4, '')); + PREPARE stmt4 FROM @P_query4; + EXECUTE stmt4; + DEALLOCATE PREPARE stmt4; + INSERT INTO TMPLOGTABLE SELECT Concat('.... rows inserted: ' , IFNULL((ROW_COUNT()), '')); + + /* DELETE */ + /* DELETE FROM ARCHIVE_ACT_GE_BYTEARRAY WHERE ID_ in (select BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY); */ + DELETE AAGB FROM ARCHIVE_ACT_GE_BYTEARRAY AAGB INNER JOIN TMP_ARCHIVING_BYTEARRAY TMP_B ON AAGB.ID_ = TMP_B.BYTEARRAY_ID_; + INSERT INTO TMPLOGTABLE SELECT Concat('.... rows deleted: ' , IFNULL((ROW_COUNT()), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); + + /* COMMIT TRANSACTION */ + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: PIs processed: ' , IFNULL(P_piProcessed, '')) ; + COMMIT; + INSERT INTO TMPLOGTABLE SELECT CONCAT('TA-COMMIT DONE!' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); + + SET P_executionDuration = DATEDIFF(sysdate(), P_startDate); + + SET P_result = CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: EXECUTED (commited) successfully! ' , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') , '; Duration: ' , ifnull((round(P_executionDuration*24*60*60, 1)), ''), ' sec.' , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '') , + ' PIs processed: ' , IFNULL(P_piProcessed, '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' Used PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') , + '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') , + '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, '')); + + INSERT INTO TMPLOGTABLE SELECT CONCAT(P_result); + END IF; + END; +// + +DELIMITER ; 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 new file mode 100644 index 0000000000..10e9930720 --- /dev/null +++ b/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/drop_mariadb_engine_7.5.6.sql @@ -0,0 +1,76 @@ +-- 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; +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 new file mode 100644 index 0000000000..8c7faa0792 --- /dev/null +++ b/bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/mariadb_engine_7.5_patch_7.5.6.sql @@ -0,0 +1,222 @@ +-- 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); |