bpmn/MSOCommonBPMN/src/test/resources/__files/CamundaDBScripts/Archive/ROLLB_ARCHIVE_CAMUNDA_HISTORY-StoreProcedure.sql

   1 /*
   2 Camunda Version: 7.5.4-ee; MariaDB tested
   3 Date: 11.30.2016
   4 Balaji Mudipalli, AJSC Camunda Team
   5
   6 DOC.:
   7 --------------------------------------------------------------------------------------
   8 Create  ROLLB_ARCHIVE_CAMUNDA_HISTORY StoreProcedure for ROLLBACK (RESTORE)
   9 of archived Camunda history tables.
  10 */
  11
  12 /* uncomment below statement and run for your db, e.g. : use camundabpmn;
  13  */
  14 -- use <db_name>;
  15 use camundabpmn;
  16
  17 DROP PROCEDURE IF EXISTS ROLLB_ARCHIVE_CAMUNDA_HISTORY;
  18
  19 DELIMITER //
  20 CREATE PROCEDURE ROLLB_ARCHIVE_CAMUNDA_HISTORY(IN IN_executionId_from INT,
  21                                                             IN IN_executionId_til INT,
  22                                                             IN IN_maxProcessInstances INT)
  23 MODIFIES SQL DATA
  24
  25 BEGIN
  26  DECLARE P_hiTableCount INT;
  27  DECLARE P_piProcessed DOUBLE;
  28  DECLARE P_baProcessed DOUBLE;
  29  DECLARE P_query VARCHAR(600);
  30  DECLARE P_startDate DATETIME;
  31  DECLARE P_executionDuration DOUBLE;
  32  DECLARE P_result NVARCHAR(400);
  33 DECLARE not_found INT DEFAULT 0;
  34       DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1;
  35
  36
  37     /* START TRANSACTION */
  38     SET P_startDate = sysdate();
  39         DELETE FROM TMPLOGTABLE;
  40
  41         INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]:  START EXECUTION: ' , ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') ,
  42                            '; PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') ,
  43                            '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') ,
  44                            '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, ''));
  45
  46     /* 1. Truncate TMP_ARCHIVING_PROCINST */
  47
  48     DELETE FROM TMP_ARCHIVING_PROCINST;
  49     DELETE FROM TMP_ARCHIVING_BYTEARRAY;
  50
  51     /* 2. Fill TMP_ARCHIVING_PROCINST with candidates: */
  52     IF IN_executionId_til = -1 THEN /* IN_executionId_from only */
  53         SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID = ' , IFNULL(IN_executionId_from, ''));
  54
  55     ELSEIF IN_executionId_til = 0 THEN /* all from IN_executionId_from */
  56         SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID >= ' , IFNULL(IN_executionId_from, ''));
  57
  58     ELSE /* between IN_executionId_from AND IN_executionId_til */
  59         SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID between ', IFNULL(IN_executionId_from, '') , ' AND ' , IFNULL(IN_executionId_til, ''));
  60     END IF;
  61
  62     IF IN_maxProcessInstances = 0 THEN /* all */
  63
  64         SET @P_query1 = CONCAT('INSERT INTO TMP_ARCHIVING_PROCINST ', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
  65                    '   SELECT PROC_INST_ID_, END_TIME_ FROM ARCHIVE_ACT_HI_PROCINST ', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
  66                    '   ',  Ifnull(P_query, ''));
  67
  68     ELSE /* limit: IN_maxProcessInstances */
  69         SET @P_query1 = CONCAT('INSERT INTO TMP_ARCHIVING_PROCINST ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
  70                    ' (PROC_INST_ID_, END_TIME_ ) ( ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
  71                    '   SELECT PROC_INST_ID_, END_TIME_ FROM ARCHIVE_ACT_HI_PROCINST ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
  72                    '   ',  Ifnull(P_query, '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
  73                    ') LIMIT ', IFNULL(IN_maxProcessInstances, ''));
  74     END IF;
  75
  76     INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /fill temp table with PI candidates/ ' , Ifnull(P_query, ''));
  77
  78         PREPARE stmt1 FROM @P_query1;
  79         EXECUTE stmt1;
  80         DEALLOCATE PREPARE stmt1;
  81
  82     INSERT INTO TMPLOGTABLE SELECT CONCAT('.... rows inserted into TMP_ARCHIVING_PROCINST: ' , IFNULL((ROW_COUNT()), ''));
  83
  84     /* 3. Fill TMP_ARCHIVING_BYTEARRAYS with candidates:  */
  85     INSERT INTO TMP_ARCHIVING_BYTEARRAY
  86         SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_VARINST archvar
  87         where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)
  88         AND archvar.BYTEARRAY_ID_ is not null;
  89
  90     INSERT INTO TMP_ARCHIVING_BYTEARRAY
  91         SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_DETAIL archvar
  92         where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)
  93         AND archvar.BYTEARRAY_ID_ is not null;
  94
  95     select count(*) INTO P_baProcessed FROM TMP_ARCHIVING_BYTEARRAY;
  96     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), ''));
  97
  98
  99     /* 4. Check PI's im TEMP ready for ROLLBACK */
 100     select count(*) INTO P_piProcessed FROM TMP_ARCHIVING_PROCINST;
 101
 102     IF P_piProcessed = 0 THEN /* no candidates found */
 103         INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: NO ProcessInstance-Candidates for archive-Rollback found! ');
 104         INSERT INTO TMPLOGTABLE SELECT CONCAT('Try TA-ROLLBACK ...');
 105         ROLLBACK;  /*-- TMP_ARCHIVING_PROCINST un-Delete */
 106         INSERT INTO TMPLOGTABLE SELECT CONCAT('TA-ROLLBACK DONE! ...' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''));
 107
 108         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), ''),
 109                     ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 110                     ' Used PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') ,
 111                            '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') ,
 112                            '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, ''));
 113
 114         INSERT INTO TMPLOGTABLE SELECT CONCAT(P_result);
 115
 116     ELSE
 117         INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: ', IFNULL(P_piProcessed, '') ,' ProcessInstance candidates for Rollback found!');
 118
 119         /* LOOP over tables */
 120         SELECT COUNT(*)+1 INTO P_hiTableCount FROM camunda_hi_tables;
 121
 122                         INSERT INTO TMPLOGTABLE SELECT CONCAT('P_hiTableCount value is ',P_hiTableCount);
 123
 124                         SET @i = 1;
 125
 126                         WHILE @i < P_hiTableCount
 127                                 DO
 128                                         SELECT TableName_ INTO @P_tableName FROM camunda_hi_tables WHERE id_ = @i;
 129
 130                                         INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: #######  Start restore from:  ARCHIVE_' , IFNULL(@P_tableName, '') ,'  ...');
 131
 132                                         SET @P_tableFields = CONCAT('');
 133
 134                                         select GROUP_CONCAT(column_name order by ordinal_position)
 135                                                 INTO @P_tableFields
 136                                                 from information_schema.columns
 137                                                 where table_schema = (select DATABASE()) AND TABLE_NAME = @P_tableName;
 138
 139                                         /* INSERT */
 140                                         SET @P_query2 = CONCAT('INSERT INTO ', IFNULL(@P_tableName, '') ,
 141                                                            ' SELECT ' , @P_tableFields,
 142                                                            '  FROM ARCHIVE_' , IFNULL(@P_tableName, '') ,
 143                                                            '  WHERE PROC_INST_ID_ in ( SELECT tmp.PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST tmp)');
 144
 145                                         INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /copy back to history table/ ' , Ifnull(@P_query2, ''));
 146
 147                                                 PREPARE stmt2 FROM @P_query2;
 148                                                 EXECUTE stmt2;
 149                                                 DEALLOCATE PREPARE stmt2;
 150
 151                                         INSERT INTO TMPLOGTABLE SELECT Concat('.... rows inserted: ' , IFNULL((ROW_COUNT()), ''));
 152
 153                                         /* DELETE */
 154                                         INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]:         Delete in Archive: ARCHIVE_' , IFNULL(@P_tableName, '') ,'  ...');
 155                                         /* SET @P_query3 = CONCAT(' DELETE FROM ARCHIVE_' , IFNULL(@P_tableName, '') , ' WHERE PROC_INST_ID_ in (select PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)'); */
 156                                         SET @P_query3 = CONCAT('DELETE ARCH FROM ARCHIVE_' , IFNULL(@P_tableName, '') ,
 157                                                                         ' ARCH INNER JOIN TMP_ARCHIVING_PROCINST TMP ON ARCH.PROC_INST_ID_ = TMP.PROC_INST_ID_');
 158                                         INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): ' , Ifnull(@P_query3, ''));
 159                                                 PREPARE stmt3 FROM @P_query3;
 160                                                 EXECUTE stmt3;
 161                                                 DEALLOCATE PREPARE stmt3;
 162
 163                                         INSERT INTO TMPLOGTABLE SELECT Concat('.... rows deleted: ' , IFNULL((ROW_COUNT()), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''));
 164
 165                                 SET @i = @i+1;
 166                         END WHILE;
 167
 168         /* INSERT */
 169         SET @P_tableFields2 = CONCAT(''); /* reset, becouse had some problems with double columns  */
 170         /* fetch table column names into P_tableFields : */
 171         select GROUP_CONCAT(COLUMN_NAME order by ordinal_position)
 172                         INTO @P_tableFields2
 173             from information_schema.columns
 174             where table_schema = (select DATABASE()) AND TABLE_NAME = 'ACT_GE_BYTEARRAY';
 175
 176         SET @P_query4 = CONCAT('INSERT INTO ACT_GE_BYTEARRAY ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 177                    ' SELECT ' , IFNULL(@P_tableFields2, '') ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 178                    '  FROM ARCHIVE_ACT_GE_BYTEARRAY' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 179                    '  WHERE ID_ in ( SELECT tmp.BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY tmp)');
 180         INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /copy back to history table/ ' , Ifnull(@P_query4, ''));
 181                 PREPARE stmt4 FROM @P_query4;
 182                 EXECUTE stmt4;
 183                 DEALLOCATE PREPARE stmt4;
 184         INSERT INTO TMPLOGTABLE SELECT Concat('.... rows inserted: ' , IFNULL((ROW_COUNT()), ''));
 185
 186         /* DELETE */
 187         /* DELETE FROM ARCHIVE_ACT_GE_BYTEARRAY WHERE ID_ in (select BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY); */
 188                 DELETE AAGB FROM ARCHIVE_ACT_GE_BYTEARRAY AAGB INNER JOIN TMP_ARCHIVING_BYTEARRAY TMP_B ON AAGB.ID_ = TMP_B.BYTEARRAY_ID_;
 189         INSERT INTO TMPLOGTABLE SELECT Concat('.... rows deleted: ' , IFNULL((ROW_COUNT()), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''));
 190
 191         /* COMMIT TRANSACTION */
 192         INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: PIs processed: ' , IFNULL(P_piProcessed, '')) ;
 193         COMMIT;
 194         INSERT INTO TMPLOGTABLE SELECT CONCAT('TA-COMMIT DONE!' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''));
 195
 196         SET P_executionDuration = DATEDIFF(sysdate(), P_startDate);
 197
 198         SET P_result = CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]:  EXECUTED (commited) successfully! ' , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 199                 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), '') ,
 200                 ' PIs processed: ' , IFNULL(P_piProcessed, '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''),
 201                 ' Used PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') ,
 202                            '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') ,
 203                            '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, ''));
 204
 205         INSERT INTO TMPLOGTABLE SELECT CONCAT(P_result);
 206     END IF;
 207  END;
 208 //
 209
 210 DELIMITER ;