packages/root-pack-extras/config-resources/mysql/db-sql-scripts/camunda/archive_mariadb_camunda_tables.sql

   1 -- Fix for https://itrack.web.att.com/browse/AJSCCMDA-90 --
   2 use camundabpmn;
   3
   4 /* uncomment below statement and run for your db, e.g. : use camundabpmn;
   5  */
   6 -- use <db_name>;
   7
   8 /*
   9 Drop a archive tables
  10 */
  11 /*-- TMP_ARCHIVING_PROCINST */
  12 DROP TABLE IF EXISTS TMP_ARCHIVING_PROCINST;
  13
  14 /*-- TMP_ARCHIVING_BYTEARRAY */
  15 DROP TABLE IF EXISTS TMP_ARCHIVING_BYTEARRAY;
  16
  17 /*-- TMP LOG TABLE */
  18 DROP TABLE IF EXISTS TMPLOGTABLE;
  19
  20 /* -- Camunda Hi Tables --*/
  21 DROP TABLE IF EXISTS Camunda_Hi_Tables;
  22
  23 /* drop own extentions columns:
  24 alter table  ARCHIVE_ACT_HI_PROCINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
  25 alter table  ARCHIVE_ACT_HI_ACTINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
  26 alter table  ARCHIVE_ACT_HI_TASKINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
  27 alter table  ARCHIVE_ACT_HI_VARINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
  28 alter table  ARCHIVE_ACT_HI_DETAIL DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
  29 alter table  ARCHIVE_ACT_HI_COMMENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
  30 alter table  ARCHIVE_ACT_HI_ATTACHMENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
  31 alter table  ARCHIVE_ACT_HI_OP_LOG DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
  32 alter table  ARCHIVE_ACT_HI_INCIDENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS);
  33 */
  34
  35 /*--#1 */
  36 DROP TABLE IF EXISTS ARCHIVE_ACT_HI_PROCINST;
  37 /*--#2 */
  38 DROP TABLE IF EXISTS ARCHIVE_ACT_HI_ACTINST;
  39 /*--#3 */
  40 DROP TABLE IF EXISTS ARCHIVE_ACT_HI_TASKINST;
  41 /*--#4 */
  42 DROP TABLE IF EXISTS ARCHIVE_ACT_HI_VARINST;
  43 /*--#5 */
  44 DROP TABLE IF EXISTS ARCHIVE_ACT_HI_DETAIL;
  45 /*--#6 */
  46 DROP TABLE IF EXISTS ARCHIVE_ACT_HI_COMMENT;
  47 /*--#7 */
  48 DROP TABLE IF EXISTS ARCHIVE_ACT_HI_ATTACHMENT;
  49 /*--#8 */
  50 DROP TABLE IF EXISTS ARCHIVE_ACT_HI_OP_LOG;
  51 /*--#9 */
  52 DROP TABLE IF EXISTS ARCHIVE_ACT_HI_INCIDENT;
  53 /*--#10 */
  54 DROP TABLE IF EXISTS ARCHIVE_ACT_GE_BYTEARRAY;
  55
  56 /* drop PL SQL procedures: */
  57 DROP PROCEDURE IF EXISTS ARCHIVE_CAMUNDA_HISTORY;
  58 DROP PROCEDURE IF EXISTS ROLLB_ARCHIVE_CAMUNDA_HISTORY;
  59
  60 /*-- Sequence */
  61 -- as sequence drop doesn't work automatically in MariaDB, use this procedure to drop sequence
  62  DROP PROCEDURE IF EXISTS DropSequence;
  63
  64 /*-- To Drop the MariaDB specific user defined procedures and functions */
  65 DROP FUNCTION IF EXISTS NextVal;
  66 DROP PROCEDURE IF EXISTS CreateSequence;
  67 DROP PROCEDURE IF EXISTS DropSequence;
  68 DROP TABLE IF EXISTS _sequences;
  69
  70 /* -- If only the sequence: STAT_EXECUTION_SEQ needs to be removed, uncomment and use below statements --*/
  71 /*
  72   DELIMITER //
  73   CREATE PROCEDURE DropSequence (vname VARCHAR(30))
  74   BEGIN
  75      -- Drop the sequence
  76      DELETE FROM _sequences WHERE name = vname;
  77   END
  78   //
  79   DELIMITER ;
  80
  81 -- use the above procedure to drop sequence
  82 CALL DropSequence('STAT_EXECUTION_SEQ');
  83 */
  84
  85
  86
  87
  88
  89 /*
  90  1. Create starts - Add some Camunda Indexes to history schema part (for Archiving)
  91 */
  92 create INDEX IF NOT EXISTS IDX_ACT_HI_TASKINST_PIID ON ACT_HI_TASKINST (PROC_INST_ID_);
  93 create INDEX IF NOT EXISTS IDX_ACT_HI_COMMENT_PIID ON ACT_HI_COMMENT (PROC_INST_ID_);
  94 create INDEX IF NOT EXISTS IDX_ACT_HI_ATTACHMENT_PIID ON ACT_HI_ATTACHMENT (PROC_INST_ID_);
  95 create INDEX IF NOT EXISTS IDX_ACT_HI_OP_LOG_PIID ON ACT_HI_OP_LOG (PROC_INST_ID_);
  96 create INDEX IF NOT EXISTS IDX_ACT_HI_INCIDENT_PIID ON ACT_HI_INCIDENT (PROC_INST_ID_);
  97 create INDEX IF NOT EXISTS IDX_ACT_HI_ACTINST_PIID ON ACT_HI_ACTINST(PROC_INST_ID_);
  98
  99
 100 /*
 101  2.  Create Archiving Tables in current schema
 102 */
 103
 104 /*-- TMP_ARCHIVING_PROCINST */
 105 CREATE TABLE TMP_ARCHIVING_PROCINST
 106 ( PROC_INST_ID_ varchar(64) not null,
 107   END_TIME_ datetime(3)
 108 );
 109 CREATE INDEX AI_TMP_ARCH_PROCINST_PI_ID ON TMP_ARCHIVING_PROCINST(PROC_INST_ID_);
 110
 111 /*-- TMP_ARCHIVING_BYTEARRAY */
 112 CREATE TABLE TMP_ARCHIVING_BYTEARRAY
 113 ( BYTEARRAY_ID_ varchar(64) not null,
 114   PROC_INST_ID_ varchar(64)
 115 );
 116 CREATE INDEX AI_TMP_ARCH_BYTEARRAY_BAID ON TMP_ARCHIVING_BYTEARRAY(BYTEARRAY_ID_);
 117
 118
 119 /*--#1 ARCHIVE_ACT_HI_PROCINST; */
 120 create TABLE ARCHIVE_ACT_HI_PROCINST
 121 AS ( select * from ACT_HI_PROCINST where 1=0);
 122
 123 create index AI_HI_PROCINST_END_TIME on ARCHIVE_ACT_HI_PROCINST(END_TIME_);
 124 ALTER TABLE ARCHIVE_ACT_HI_PROCINST ADD CONSTRAINT  ARCHIVE_ACT_HI_PROCINST_UQ UNIQUE ( PROC_INST_ID_);
 125
 126 /*--#2   ARCHIVE_ACT_HI_ACTINST; */
 127 create TABLE ARCHIVE_ACT_HI_ACTINST
 128 AS ( select * from ACT_HI_ACTINST where 1=0);
 129
 130 create index AI_HI_ACTINST_PROC_INST_ID on ARCHIVE_ACT_HI_ACTINST(PROC_INST_ID_);
 131 create index AI_HI_ACTINST_END_TIME on ARCHIVE_ACT_HI_ACTINST(END_TIME_);
 132
 133 /*--#3  ARCHIVE_ACT_HI_TASKINST; */
 134 create TABLE ARCHIVE_ACT_HI_TASKINST
 135 AS ( select * from ACT_HI_TASKINST where 1=0);
 136
 137 create index AI_HI_TASKINST_PROC_INST_ID on ARCHIVE_ACT_HI_TASKINST(PROC_INST_ID_);
 138 create index AI_HI_TASKINST_END_TIME on ARCHIVE_ACT_HI_TASKINST(END_TIME_);
 139
 140 /*--#4 ARCHIVE_ACT_HI_VARINST; */
 141 create TABLE ARCHIVE_ACT_HI_VARINST
 142 AS ( select * from ACT_HI_VARINST where 1=0);
 143
 144 create index AI_HI_VARINST_PROC_INST_ID on ARCHIVE_ACT_HI_VARINST(PROC_INST_ID_);
 145
 146 /*--#5 ARCHIVE_ACT_HI_DETAIL; */
 147 create TABLE ARCHIVE_ACT_HI_DETAIL
 148 AS ( select * from ACT_HI_DETAIL where 1=0);
 149
 150 create index AI_HI_DETAIL_PROC_INST_ID on ARCHIVE_ACT_HI_DETAIL(PROC_INST_ID_);
 151 create index AI_HI_DETAIL_TIME on ARCHIVE_ACT_HI_DETAIL(TIME_);
 152
 153 /*--#6 ARCHIVE_ACT_HI_COMMENT; */
 154 create TABLE ARCHIVE_ACT_HI_COMMENT
 155 AS ( select * from ACT_HI_COMMENT where 1=0);
 156
 157 create index AI_HI_COMMENT_PROC_INST_ID on ARCHIVE_ACT_HI_COMMENT(PROC_INST_ID_);
 158 create index AI_HI_COMMENT_TIME on ARCHIVE_ACT_HI_COMMENT(TIME_);
 159
 160 /*--#7 ARCHIVE_ACT_HI_ATTACHMENT; */
 161 create TABLE ARCHIVE_ACT_HI_ATTACHMENT
 162 AS ( select * from ACT_HI_ATTACHMENT where 1=0);
 163
 164 create index AI_HI_ATTACHMENT_PROC_INST_ID on ARCHIVE_ACT_HI_ATTACHMENT(PROC_INST_ID_);
 165
 166 /*--#8 ARCHIVE_ACT_HI_OP_LOG; */
 167 create TABLE ARCHIVE_ACT_HI_OP_LOG
 168 AS ( select * from ACT_HI_OP_LOG where 1=0);
 169
 170 create index AI_HI_OP_LOG_PROC_INST_ID on ARCHIVE_ACT_HI_OP_LOG(PROC_INST_ID_);
 171 create index AI_HI_OP_LOG_TIMESTAMP on ARCHIVE_ACT_HI_OP_LOG(TIMESTAMP_);
 172
 173 /*--#9 ARCHIVE_ACT_HI_INCIDENT; */
 174 create TABLE ARCHIVE_ACT_HI_INCIDENT
 175 AS ( select * from ACT_HI_INCIDENT where 1=0);
 176
 177 create index AI_HI_INCIDENT_PROC_INST_ID on ARCHIVE_ACT_HI_INCIDENT(PROC_INST_ID_);
 178
 179 /*--#10 ARCHIVE_ACT_GE_BYTEARRAY; */
 180 create TABLE ARCHIVE_ACT_GE_BYTEARRAY
 181 AS ( select * from ACT_GE_BYTEARRAY where 1=0);
 182
 183 create index AI_GE_BYTEARRAY_ID_ on ARCHIVE_ACT_GE_BYTEARRAY(ID_);
 184
 185 /* -----------------------------------------------------------------------------
 186 Extend a ARCHIVE: Table by two attributes: STAT_EXECUTION_ID, STAT_EXECUTION_TS
 187 */
 188
 189 /*
 190 --TEMPLATE:
 191 alter table ARCHIVE_%TableName%
 192     add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
 193 CREATE INDEX AI_%TableName%_EXE_ID ON ARCHIVE_%TableName%(STAT_EXECUTION_ID);
 194 */
 195
 196
 197 /*--#1 ACT_HI_PROCINST */
 198 alter table ARCHIVE_ACT_HI_PROCINST
 199     add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
 200 CREATE INDEX AI_ACT_HI_PROCINST_EXE_ID ON ARCHIVE_ACT_HI_PROCINST(STAT_EXECUTION_ID);
 201
 202 /*--#2 ACT_HI_ACTINST */
 203 alter table ARCHIVE_ACT_HI_ACTINST
 204     add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
 205 CREATE INDEX AI_ACT_HI_ACTINST_EXE_ID ON ARCHIVE_ACT_HI_ACTINST(STAT_EXECUTION_ID);
 206
 207 /*--#3 ACT_HI_TASKINST */
 208 alter table ARCHIVE_ACT_HI_TASKINST
 209     add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
 210 CREATE INDEX AI_ACT_HI_TASKINST_EXE_ID ON ARCHIVE_ACT_HI_TASKINST(STAT_EXECUTION_ID);
 211
 212 /*--#4 ACT_HI_VARINST */
 213 alter table ARCHIVE_ACT_HI_VARINST
 214     add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
 215 CREATE INDEX AI_ACT_HI_VARINST_EXE_ID ON ARCHIVE_ACT_HI_VARINST(STAT_EXECUTION_ID);
 216
 217 /*--#5 ACT_HI_DETAIL */
 218 alter table ARCHIVE_ACT_HI_DETAIL
 219     add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
 220 CREATE INDEX AI_ACT_HI_DETAIL_EXE_ID ON ARCHIVE_ACT_HI_DETAIL(STAT_EXECUTION_ID);
 221
 222 /*--#6 ACT_HI_COMMENT */
 223 alter table ARCHIVE_ACT_HI_COMMENT
 224     add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
 225 CREATE INDEX AI_ACT_HI_COMMENT_EXE_ID ON ARCHIVE_ACT_HI_COMMENT(STAT_EXECUTION_ID);
 226
 227 /*--#7 ACT_HI_ATTACHMENT */
 228 alter table ARCHIVE_ACT_HI_ATTACHMENT
 229     add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
 230 CREATE INDEX AI_ACT_HI_ATTACHMENT_EXE_ID ON ARCHIVE_ACT_HI_ATTACHMENT(STAT_EXECUTION_ID);
 231
 232 /*--#8 ACT_HI_OP_LOG */
 233 alter table ARCHIVE_ACT_HI_OP_LOG
 234     add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
 235 CREATE INDEX AI_ACT_HI_OP_LOG_EXE_ID ON ARCHIVE_ACT_HI_OP_LOG(STAT_EXECUTION_ID);
 236
 237 /*--#9 ACT_HI_INCIDENT */
 238 alter table ARCHIVE_ACT_HI_INCIDENT
 239     add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
 240 CREATE INDEX AI_ACT_HI_INCIDENT_EXE_ID ON ARCHIVE_ACT_HI_INCIDENT(STAT_EXECUTION_ID);
 241
 242 /*--#10 ACT_GE_BYTEARRAY */
 243 alter table ARCHIVE_ACT_GE_BYTEARRAY
 244     add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
 245 CREATE INDEX AI_ACT_GE_BYTEARRAY_EXE_ID ON ARCHIVE_ACT_GE_BYTEARRAY(STAT_EXECUTION_ID);
 246
 247
 248 /* -- Next Val as a user defined function needed only in MariaDB--*/
 249 DROP FUNCTION IF EXISTS NextVal;
 250   DELIMITER //
 251   CREATE FUNCTION NextVal (vname VARCHAR(30))
 252     RETURNS INT
 253   BEGIN
 254      -- Retrieve and update in single statement
 255      UPDATE _sequences
 256        SET next = next + 1
 257        WHERE name = vname;
 258
 259      RETURN (SELECT next FROM _sequences LIMIT 1);
 260   END
 261   //
 262   DELIMITER ;
 263
 264 /* -- History tables for use in archive procedure, there is no array type in MariaDB --*/
 265
 266 Create Table Camunda_Hi_Tables (id_ INT NOT NULL,
 267 TableName_ varchar(80) NOT NULL);
 268
 269 Insert Into Camunda_Hi_Tables Values (1,'ACT_HI_PROCINST');
 270 Insert Into Camunda_Hi_Tables Values (2,'ACT_HI_ACTINST');
 271 Insert Into Camunda_Hi_Tables Values (3,'ACT_HI_TASKINST');
 272 Insert Into Camunda_Hi_Tables Values (4,'ACT_HI_VARINST');
 273 Insert Into Camunda_Hi_Tables Values (5,'ACT_HI_DETAIL');
 274 Insert Into Camunda_Hi_Tables Values (6,'ACT_HI_COMMENT');
 275 Insert Into Camunda_Hi_Tables Values (7,'ACT_HI_ATTACHMENT');
 276 Insert Into Camunda_Hi_Tables Values (8,'ACT_HI_OP_LOG');
 277 Insert Into Camunda_Hi_Tables Values (9,'ACT_HI_INCIDENT');
 278
 279 /*-- log table --*/
 280 CREATE TABLE TMPLOGTABLE (LogMessage Varchar(700));
 281
 282
 283 /* -- Below user defined functions and procedures needed only in MariaDB, they are in-built in Oracle --*/
 284 /*-- Create a sequence SP */
 285 DROP PROCEDURE IF EXISTS CreateSequence;
 286   DELIMITER //
 287   CREATE PROCEDURE CreateSequence (name VARCHAR(30), start INT, inc INT)
 288   BEGIN
 289      -- Create a table to store sequences
 290      CREATE TABLE IF NOT EXISTS _sequences
 291      (
 292          name VARCHAR(70) NOT NULL UNIQUE,
 293          next INT NOT NULL,
 294          inc INT NOT NULL
 295      );
 296
 297      -- Add the new sequence
 298      INSERT INTO _sequences VALUES (name, start, inc);
 299   END
 300   //
 301   DELIMITER ;
 302
 303 /*--------------------------------------------------------------------------------------------------
 304  Add Meta to Archive
 305  -------------------------------------------------------------------------------------------------- */
 306
 307 /* Create STAT_EXECUTION_SEQ: each Archive Entry has a same Execution ID during one Archiving Run */
 308 CALL CreateSequence('STAT_EXECUTION_SEQ', 1, 1);
 309
 310
 311
 312 /*
 313 ARCHIVE_CAMUNDA_HISTORY-Default Store Procedure starts
 314 Camunda Version: 7.5.4-ee; MariaDB tested
 315 Date: 11.30.2016
 316 Balaji Mudipalli, AJSC Camunda Team
 317
 318 DOC.:
 319 --------------------------------------------------------------------------------------
 320 Create  ARCHIVE_CAMUNDA_HISTORY StoreProcedure -function for archiving of history camunda tables.
 321 */
 322
 323 /* uncomment below statement and run for your db, e.g. : use camundabpmn;
 324  */
 325
 326 DROP PROCEDURE IF EXISTS ARCHIVE_CAMUNDA_HISTORY;
 327
 328 DELIMITER //
 329
 330 CREATE PROCEDURE ARCHIVE_CAMUNDA_HISTORY(IN IN_periodInDays INT, IN IN_maxProcessInstances INT)
 331 MODIFIES SQL DATA
 332
 333 BEGIN
 334  DECLARE P_hiTableCount INT;
 335  DECLARE P_executionId BIGINT;
 336  DECLARE P_piProcessed DOUBLE;
 337  DECLARE P_baProcessed DOUBLE;
 338  DECLARE P_startDate DATE;
 339  DECLARE P_executionDuration double;
 340
 341  DECLARE not_found INT DEFAULT 0;
 342     DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1;
 343
 344     /* START TRANSACTION */
 345     set P_startDate = sysdate();
 346     set P_executionId = NextVal('STAT_EXECUTION_SEQ');
 347
 348    INSERT INTO TMPLOGTABLE SELECT CONCAT('P_executionId value is ',P_executionId);
 349
 350    DELETE FROM TMP_ARCHIVING_PROCINST;
 351    DELETE FROM TMP_ARCHIVING_BYTEARRAY;
 352    -- temp table --
 353         DELETE FROM TMPLOGTABLE;
 354
 355
 356         /* 1. Set Default Value for Max Pi's  */
 357         IF IN_maxProcessInstances = 0 THEN SET IN_maxProcessInstances = 1000; END IF;
 358         IF IN_maxProcessInstances > 1000 THEN SET IN_maxProcessInstances = 1000; END IF;
 359
 360         INSERT INTO TMPLOGTABLE SELECT CONCAT('IN_maxProcessInstances value is: ',IN_maxProcessInstances);
 361
 362     /* 2. Fill TMP_ARCHIVING_PROCINST with candidates: */
 363     IF IN_maxProcessInstances = 0 THEN /* all */
 364         INSERT INTO TMP_ARCHIVING_PROCINST
 365               SELECT hi.PROC_INST_ID_, hi.END_TIME_
 366                 FROM ACT_HI_PROCINST hi
 367                                WHERE hi.END_TIME_ IS NOT NULL
 368                                      AND hi.END_TIME_ <= ( DATE_SUB(SYSDATE(), INTERVAL IN_periodInDays DAY));
 369
 370                 ELSE /* limit: IN_maxProcessInstances */
 371                         INSERT INTO TMP_ARCHIVING_PROCINST
 372                                 (PROC_INST_ID_, END_TIME_) (
 373                                   SELECT hi2.PROC_INST_ID_, hi2.END_TIME_
 374                                         FROM ACT_HI_PROCINST hi2
 375                                                                    WHERE hi2.END_TIME_ IS NOT NULL
 376                                                                                  AND hi2.END_TIME_ <= ( DATE_SUB(SYSDATE(), INTERVAL IN_periodInDays DAY))
 377                                 ) LIMIT IN_maxProcessInstances;
 378     END IF;
 379
 380     /* 3. Check PI's im TEMP if any found, ready for ACHIVING */
 381     select count(*) INTO P_piProcessed FROM TMP_ARCHIVING_PROCINST;
 382
 383     IF P_piProcessed = 0 THEN
 384         INSERT INTO TMPLOGTABLE SELECT CONCAT('P_piProcessed value is: ',P_piProcessed);
 385         ROLLBACK;
 386                 /* 4. Move data from history to archive (insert to archive and delete in history) */
 387                 ELSE
 388                         SELECT COUNT(*)+1 INTO P_hiTableCount FROM camunda_hi_tables;
 389                                 INSERT INTO TMPLOGTABLE SELECT CONCAT('P_hiTableCount value is ',P_hiTableCount);
 390                         SET @i = 1;
 391                         WHILE @i < P_hiTableCount
 392                                 DO
 393                                         SELECT TableName_ INTO @P_tableName FROM camunda_hi_tables WHERE id_ = @i;
 394                                         INSERT INTO TMPLOGTABLE SELECT CONCAT('P_tableName: ', @P_tableName);
 395
 396                                    Set @P_archiveTableName = Concat('ARCHIVE_',IFNULL(@P_tableName, ''));
 397                                    INSERT INTO TMPLOGTABLE SELECT CONCAT('@P_archiveTableName: ', @P_archiveTableName);
 398
 399                                    SET @query1 = CONCAT('INSERT INTO ', @P_archiveTableName ,
 400                                                 ' SELECT hi3.*, ',P_executionId, ', NOW() FROM ', @P_tableName,' hi3
 401                                                 WHERE hi3.PROC_INST_ID_ in ( SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)');
 402                                         INSERT INTO TMPLOGTABLE SELECT CONCAT('@query1: ', @query1);
 403
 404                                         PREPARE stmt1 FROM @query1;
 405                                         EXECUTE stmt1;
 406                                         DEALLOCATE PREPARE stmt1;
 407
 408                                         SET @query2 = CONCAT ('DELETE ACT FROM ',@P_tableName,' ACT INNER JOIN TMP_ARCHIVING_PROCINST TMP ON ACT.PROC_INST_ID_ = TMP.PROC_INST_ID_');
 409                                         INSERT INTO TMPLOGTABLE SELECT CONCAT('@query2: ', @query2);
 410
 411                                         PREPARE stmt2 FROM @query2;
 412                                         EXECUTE stmt2;
 413                                         DEALLOCATE PREPARE stmt2;
 414
 415                         SET @i = @i+1;
 416                 END WHILE;
 417                         /* select bytearray_ids */
 418                         INSERT INTO TMP_ARCHIVING_BYTEARRAY
 419                                 SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_VARINST archvar
 420                                 where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)
 421                                 AND archvar.BYTEARRAY_ID_ is not null;
 422
 423                         INSERT INTO TMP_ARCHIVING_BYTEARRAY
 424                                 SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_DETAIL archvar
 425                                 where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)
 426                                 AND archvar.BYTEARRAY_ID_ is not null;
 427
 428                         /* 5. Check Bytearrays im TEMP if any found, ready for ACHIVING */
 429                         select count(*) INTO P_baProcessed FROM TMP_ARCHIVING_BYTEARRAY;
 430
 431                         /* INSERT */
 432                         INSERT INTO ARCHIVE_ACT_GE_BYTEARRAY
 433                                 SELECT hi4.*, P_executionId, NOW() FROM ACT_GE_BYTEARRAY hi4
 434                                 WHERE hi4.ID_ in ( SELECT BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY);
 435
 436                         /* DELETE */
 437                         DELETE FROM ACT_GE_BYTEARRAY WHERE ID_ in (select BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY);
 438
 439                         /* COMMIT TRANSACTION */
 440                         COMMIT;
 441                         INSERT INTO TMPLOGTABLE SELECT CONCAT('P_executionId is ', P_executionId);
 442
 443                 set P_executionDuration = DATEDIFF(sysdate(), P_startDate);
 444
 445                 INSERT INTO TMPLOGTABLE SELECT CONCAT('SP success and P_executionDuration is ', ifnull((round(P_executionDuration*24*60*60, 1)), ''), ' sec.');
 446     END IF;
 447
 448  END;
 449 //
 450
 451 DELIMITER ;
 452
 453
 454
 455 /*
 456 ROLLB_ARCHIVE_CAMUNDA_HISTORY-StoreProcedure starts
 457 Camunda Version: 7.5.4-ee; MariaDB tested
 458 Date: 11.30.2016
 459 Balaji Mudipalli, AJSC Camunda Team
 460
 461 DOC.:
 462 --------------------------------------------------------------------------------------
 463 Create  ROLLB_ARCHIVE_CAMUNDA_HISTORY StoreProcedure for ROLLBACK (RESTORE)
 464 of archived Camunda history tables.
 465 */
 466
 467 /* uncomment below statement and run for your db, e.g. : use camundabpmn;
 468  */
 469 -- use <db_name>;
 470
 471
 472 DROP PROCEDURE IF EXISTS ROLLB_ARCHIVE_CAMUNDA_HISTORY;
 473
 474 DELIMITER //
 475 CREATE PROCEDURE ROLLB_ARCHIVE_CAMUNDA_HISTORY(IN IN_executionId_from INT,
 476                                                             IN IN_executionId_til INT,
 477                                                             IN IN_maxProcessInstances INT)
 478 MODIFIES SQL DATA
 479
 480 BEGIN
 481  DECLARE P_hiTableCount INT;
 482  DECLARE P_piProcessed DOUBLE;
 483  DECLARE P_baProcessed DOUBLE;
 484  DECLARE P_query VARCHAR(600);
 485  DECLARE P_startDate DATETIME;
 486  DECLARE P_executionDuration DOUBLE;
 487  DECLARE P_result NVARCHAR(400);
 488 DECLARE not_found INT DEFAULT 0;
 489       DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1;
 490
 491
 492     /* START TRANSACTION */
 493     SET P_startDate = sysdate();
 494         DELETE FROM TMPLOGTABLE;
 495
 496         INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]:  START EXECUTION: ' , ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') ,
 497                            '; PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') ,
 498                            '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') ,
 499                            '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, ''));
 500
 501     /* 1. Truncate TMP_ARCHIVING_PROCINST */
 502
 503     DELETE FROM TMP_ARCHIVING_PROCINST;
 504     DELETE FROM TMP_ARCHIVING_BYTEARRAY;
 505
 506     /* 2. Fill TMP_ARCHIVING_PROCINST with candidates: */
 507     IF IN_executionId_til = -1 THEN /* IN_executionId_from only */
 508         SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID = ' , IFNULL(IN_executionId_from, ''));
 509
 510     ELSEIF IN_executionId_til = 0 THEN /* all from IN_executionId_from */
 511         SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID >= ' , IFNULL(IN_executionId_from, ''));
 512
 513     ELSE /* between IN_executionId_from AND IN_executionId_til */
 514         SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID between ', IFNULL(IN_executionId_from, '') , ' AND ' , IFNULL(IN_executionId_til, ''));
 515     END IF;
 516
 517     IF IN_maxProcessInstances = 0 THEN /* all */
 518
 519         SET @P_query1 = CONCAT('INSERT INTO TMP_ARCHIVING_PROCINST ', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 520                    '   SELECT PROC_INST_ID_, END_TIME_ FROM ARCHIVE_ACT_HI_PROCINST ', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 521                    '   ',  Ifnull(P_query, ''));
 522
 523     ELSE /* limit: IN_maxProcessInstances */
 524         SET @P_query1 = CONCAT('INSERT INTO TMP_ARCHIVING_PROCINST ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 525                    ' (PROC_INST_ID_, END_TIME_ ) ( ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 526                    '   SELECT PROC_INST_ID_, END_TIME_ FROM ARCHIVE_ACT_HI_PROCINST ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 527                    '   ',  Ifnull(P_query, '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 528                    ') LIMIT ', IFNULL(IN_maxProcessInstances, ''));
 529     END IF;
 530
 531     INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /fill temp table with PI candidates/ ' , Ifnull(P_query, ''));
 532
 533         PREPARE stmt1 FROM @P_query1;
 534         EXECUTE stmt1;
 535         DEALLOCATE PREPARE stmt1;
 536
 537     INSERT INTO TMPLOGTABLE SELECT CONCAT('.... rows inserted into TMP_ARCHIVING_PROCINST: ' , IFNULL((ROW_COUNT()), ''));
 538
 539     /* 3. Fill TMP_ARCHIVING_BYTEARRAYS with candidates:  */
 540     INSERT INTO TMP_ARCHIVING_BYTEARRAY
 541         SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_VARINST archvar
 542         where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)
 543         AND archvar.BYTEARRAY_ID_ is not null;
 544
 545     INSERT INTO TMP_ARCHIVING_BYTEARRAY
 546         SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_DETAIL archvar
 547         where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)
 548         AND archvar.BYTEARRAY_ID_ is not null;
 549
 550     select count(*) INTO P_baProcessed FROM TMP_ARCHIVING_BYTEARRAY;
 551     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), ''));
 552
 553
 554     /* 4. Check PI's im TEMP ready for ROLLBACK */
 555     select count(*) INTO P_piProcessed FROM TMP_ARCHIVING_PROCINST;
 556
 557     IF P_piProcessed = 0 THEN /* no candidates found */
 558         INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: NO ProcessInstance-Candidates for archive-Rollback found! ');
 559         INSERT INTO TMPLOGTABLE SELECT CONCAT('Try TA-ROLLBACK ...');
 560         ROLLBACK;  /*-- TMP_ARCHIVING_PROCINST un-Delete */
 561         INSERT INTO TMPLOGTABLE SELECT CONCAT('TA-ROLLBACK DONE! ...' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''));
 562
 563         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), ''),
 564                     ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 565                     ' Used PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') ,
 566                            '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') ,
 567                            '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, ''));
 568
 569         INSERT INTO TMPLOGTABLE SELECT CONCAT(P_result);
 570
 571     ELSE
 572         INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: ', IFNULL(P_piProcessed, '') ,' ProcessInstance candidates for Rollback found!');
 573
 574         /* LOOP over tables */
 575         SELECT COUNT(*)+1 INTO P_hiTableCount FROM camunda_hi_tables;
 576
 577                         INSERT INTO TMPLOGTABLE SELECT CONCAT('P_hiTableCount value is ',P_hiTableCount);
 578
 579                         SET @i = 1;
 580
 581                         WHILE @i < P_hiTableCount
 582                                 DO
 583                                         SELECT TableName_ INTO @P_tableName FROM camunda_hi_tables WHERE id_ = @i;
 584
 585                                         INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: #######  Start restore from:  ARCHIVE_' , IFNULL(@P_tableName, '') ,'  ...');
 586
 587                                         SET @P_tableFields = CONCAT('');
 588
 589                                         select GROUP_CONCAT(column_name order by ordinal_position)
 590                                                 INTO @P_tableFields
 591                                                 from information_schema.columns
 592                                                 where table_schema = (select DATABASE()) AND TABLE_NAME = @P_tableName;
 593
 594                                         /* INSERT */
 595                                         SET @P_query2 = CONCAT('INSERT INTO ', IFNULL(@P_tableName, '') ,
 596                                                            ' SELECT ' , @P_tableFields,
 597                                                            '  FROM ARCHIVE_' , IFNULL(@P_tableName, '') ,
 598                                                            '  WHERE PROC_INST_ID_ in ( SELECT tmp.PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST tmp)');
 599
 600                                         INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /copy back to history table/ ' , Ifnull(@P_query2, ''));
 601
 602                                                 PREPARE stmt2 FROM @P_query2;
 603                                                 EXECUTE stmt2;
 604                                                 DEALLOCATE PREPARE stmt2;
 605
 606                                         INSERT INTO TMPLOGTABLE SELECT Concat('.... rows inserted: ' , IFNULL((ROW_COUNT()), ''));
 607
 608                                         /* DELETE */
 609                                         INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]:         Delete in Archive: ARCHIVE_' , IFNULL(@P_tableName, '') ,'  ...');
 610                                         /* SET @P_query3 = CONCAT(' DELETE FROM ARCHIVE_' , IFNULL(@P_tableName, '') , ' WHERE PROC_INST_ID_ in (select PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)'); */
 611                                         SET @P_query3 = CONCAT('DELETE ARCH FROM ARCHIVE_' , IFNULL(@P_tableName, '') ,
 612                                                                         ' ARCH INNER JOIN TMP_ARCHIVING_PROCINST TMP ON ARCH.PROC_INST_ID_ = TMP.PROC_INST_ID_');
 613                                         INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): ' , Ifnull(@P_query3, ''));
 614                                                 PREPARE stmt3 FROM @P_query3;
 615                                                 EXECUTE stmt3;
 616                                                 DEALLOCATE PREPARE stmt3;
 617
 618                                         INSERT INTO TMPLOGTABLE SELECT Concat('.... rows deleted: ' , IFNULL((ROW_COUNT()), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''));
 619
 620                                 SET @i = @i+1;
 621                         END WHILE;
 622
 623         /* INSERT */
 624         SET @P_tableFields2 = CONCAT(''); /* reset, becouse had some problems with double columns  */
 625         /* fetch table column names into P_tableFields : */
 626         select GROUP_CONCAT(COLUMN_NAME order by ordinal_position)
 627                         INTO @P_tableFields2
 628             from information_schema.columns
 629             where table_schema = (select DATABASE()) AND TABLE_NAME = 'ACT_GE_BYTEARRAY';
 630
 631         SET @P_query4 = CONCAT('INSERT INTO ACT_GE_BYTEARRAY ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 632                    ' SELECT ' , IFNULL(@P_tableFields2, '') ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 633                    '  FROM ARCHIVE_ACT_GE_BYTEARRAY' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 634                    '  WHERE ID_ in ( SELECT tmp.BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY tmp)');
 635         INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /copy back to history table/ ' , Ifnull(@P_query4, ''));
 636                 PREPARE stmt4 FROM @P_query4;
 637                 EXECUTE stmt4;
 638                 DEALLOCATE PREPARE stmt4;
 639         INSERT INTO TMPLOGTABLE SELECT Concat('.... rows inserted: ' , IFNULL((ROW_COUNT()), ''));
 640
 641         /* DELETE */
 642         /* DELETE FROM ARCHIVE_ACT_GE_BYTEARRAY WHERE ID_ in (select BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY); */
 643                 DELETE AAGB FROM ARCHIVE_ACT_GE_BYTEARRAY AAGB INNER JOIN TMP_ARCHIVING_BYTEARRAY TMP_B ON AAGB.ID_ = TMP_B.BYTEARRAY_ID_;
 644         INSERT INTO TMPLOGTABLE SELECT Concat('.... rows deleted: ' , IFNULL((ROW_COUNT()), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''));
 645
 646         /* COMMIT TRANSACTION */
 647         INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: PIs processed: ' , IFNULL(P_piProcessed, '')) ;
 648         COMMIT;
 649         INSERT INTO TMPLOGTABLE SELECT CONCAT('TA-COMMIT DONE!' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''));
 650
 651         SET P_executionDuration = DATEDIFF(sysdate(), P_startDate);
 652
 653         SET P_result = CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]:  EXECUTED (commited) successfully! ' , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 654                 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), '') ,
 655                 ' PIs processed: ' , IFNULL(P_piProcessed, '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 656                 ' Used PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') ,
 657                            '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') ,
 658                            '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, ''));
 659
 660         INSERT INTO TMPLOGTABLE SELECT CONCAT(P_result);
 661     END IF;
 662  END;
 663 //
 664
 665 DELIMITER ;