From 5a6a6de6f1a26a1897e4917a0df613e25a24eb70 Mon Sep 17 00:00:00 2001 From: "Benjamin, Max (mb388a)" Date: Mon, 30 Jul 2018 15:56:09 -0400 Subject: Containerization feature of SO Change-Id: I95381232eeefcd247a66a5cec370a8ce1c288e18 Issue-ID: SO-670 Signed-off-by: Benjamin, Max (mb388a) --- .../camunda/archive_mariadb_camunda_tables.sql | 1330 ++++++++++---------- 1 file changed, 665 insertions(+), 665 deletions(-) (limited to 'packages/root-pack-extras/config-resources/mysql/db-sql-scripts/camunda') diff --git a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/camunda/archive_mariadb_camunda_tables.sql b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/camunda/archive_mariadb_camunda_tables.sql index fe5ec22520..43a87916ce 100644 --- a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/camunda/archive_mariadb_camunda_tables.sql +++ b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/camunda/archive_mariadb_camunda_tables.sql @@ -1,665 +1,665 @@ --- Fix for https://itrack.web.att.com/browse/AJSCCMDA-90 -- -use camundabpmn; - -/* uncomment below statement and run for your db, e.g. : use camundabpmn; - */ --- use ; - -/* -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; - -/*-- 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; - -/* -- If only the sequence: STAT_EXECUTION_SEQ needs to be removed, uncomment and use below statements --*/ -/* - 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'); -*/ - - - - - -/* - 1. Create starts - 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_ INT NOT NULL, -TableName_ varchar(80) NOT NULL); - -Insert Into Camunda_Hi_Tables Values (1,'ACT_HI_PROCINST'); -Insert Into Camunda_Hi_Tables Values (2,'ACT_HI_ACTINST'); -Insert Into Camunda_Hi_Tables Values (3,'ACT_HI_TASKINST'); -Insert Into Camunda_Hi_Tables Values (4,'ACT_HI_VARINST'); -Insert Into Camunda_Hi_Tables Values (5,'ACT_HI_DETAIL'); -Insert Into Camunda_Hi_Tables Values (6,'ACT_HI_COMMENT'); -Insert Into Camunda_Hi_Tables Values (7,'ACT_HI_ATTACHMENT'); -Insert Into Camunda_Hi_Tables Values (8,'ACT_HI_OP_LOG'); -Insert Into Camunda_Hi_Tables Values (9,'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); - - - -/* -ARCHIVE_CAMUNDA_HISTORY-Default Store Procedure starts -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; - */ - -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 ; - - - -/* -ROLLB_ARCHIVE_CAMUNDA_HISTORY-StoreProcedure starts -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 ; - - -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 ; +-- Fix for https://itrack.web.att.com/browse/AJSCCMDA-90 -- +use camundabpmn; + +/* uncomment below statement and run for your db, e.g. : use camundabpmn; + */ +-- use ; + +/* +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; + +/*-- 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; + +/* -- If only the sequence: STAT_EXECUTION_SEQ needs to be removed, uncomment and use below statements --*/ +/* + 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'); +*/ + + + + + +/* + 1. Create starts - 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_ INT NOT NULL, +TableName_ varchar(80) NOT NULL); + +Insert Into Camunda_Hi_Tables Values (1,'ACT_HI_PROCINST'); +Insert Into Camunda_Hi_Tables Values (2,'ACT_HI_ACTINST'); +Insert Into Camunda_Hi_Tables Values (3,'ACT_HI_TASKINST'); +Insert Into Camunda_Hi_Tables Values (4,'ACT_HI_VARINST'); +Insert Into Camunda_Hi_Tables Values (5,'ACT_HI_DETAIL'); +Insert Into Camunda_Hi_Tables Values (6,'ACT_HI_COMMENT'); +Insert Into Camunda_Hi_Tables Values (7,'ACT_HI_ATTACHMENT'); +Insert Into Camunda_Hi_Tables Values (8,'ACT_HI_OP_LOG'); +Insert Into Camunda_Hi_Tables Values (9,'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); + + + +/* +ARCHIVE_CAMUNDA_HISTORY-Default Store Procedure starts +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; + */ + +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 ; + + + +/* +ROLLB_ARCHIVE_CAMUNDA_HISTORY-StoreProcedure starts +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 ; + + +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 ; -- cgit