3 1. Add some Camunda Indexes to history schema part (for Archiving)
5 create INDEX IF NOT EXISTS IDX_ACT_HI_TASKINST_PIID ON ACT_HI_TASKINST (PROC_INST_ID_);
6 create INDEX IF NOT EXISTS IDX_ACT_HI_COMMENT_PIID ON ACT_HI_COMMENT (PROC_INST_ID_);
7 create INDEX IF NOT EXISTS IDX_ACT_HI_ATTACHMENT_PIID ON ACT_HI_ATTACHMENT (PROC_INST_ID_);
8 create INDEX IF NOT EXISTS IDX_ACT_HI_OP_LOG_PIID ON ACT_HI_OP_LOG (PROC_INST_ID_);
9 create INDEX IF NOT EXISTS IDX_ACT_HI_INCIDENT_PIID ON ACT_HI_INCIDENT (PROC_INST_ID_);
10 create INDEX IF NOT EXISTS IDX_ACT_HI_ACTINST_PIID ON ACT_HI_ACTINST(PROC_INST_ID_);
14 2. Create Archiving Tables in current schema
17 /*-- TMP_ARCHIVING_PROCINST */
18 CREATE TABLE TMP_ARCHIVING_PROCINST
19 ( PROC_INST_ID_ varchar(64) not null,
22 CREATE INDEX AI_TMP_ARCH_PROCINST_PI_ID ON TMP_ARCHIVING_PROCINST(PROC_INST_ID_);
24 /*-- TMP_ARCHIVING_BYTEARRAY */
25 CREATE TABLE TMP_ARCHIVING_BYTEARRAY
26 ( BYTEARRAY_ID_ varchar(64) not null,
27 PROC_INST_ID_ varchar(64)
29 CREATE INDEX AI_TMP_ARCH_BYTEARRAY_BAID ON TMP_ARCHIVING_BYTEARRAY(BYTEARRAY_ID_);
32 /*--#1 ARCHIVE_ACT_HI_PROCINST; */
33 create TABLE ARCHIVE_ACT_HI_PROCINST
34 AS ( select * from ACT_HI_PROCINST where 1=0);
36 create index AI_HI_PROCINST_END_TIME on ARCHIVE_ACT_HI_PROCINST(END_TIME_);
37 ALTER TABLE ARCHIVE_ACT_HI_PROCINST ADD CONSTRAINT ARCHIVE_ACT_HI_PROCINST_UQ UNIQUE ( PROC_INST_ID_);
39 /*--#2 ARCHIVE_ACT_HI_ACTINST; */
40 create TABLE ARCHIVE_ACT_HI_ACTINST
41 AS ( select * from ACT_HI_ACTINST where 1=0);
43 create index AI_HI_ACTINST_PROC_INST_ID on ARCHIVE_ACT_HI_ACTINST(PROC_INST_ID_);
44 create index AI_HI_ACTINST_END_TIME on ARCHIVE_ACT_HI_ACTINST(END_TIME_);
46 /*--#3 ARCHIVE_ACT_HI_TASKINST; */
47 create TABLE ARCHIVE_ACT_HI_TASKINST
48 AS ( select * from ACT_HI_TASKINST where 1=0);
50 create index AI_HI_TASKINST_PROC_INST_ID on ARCHIVE_ACT_HI_TASKINST(PROC_INST_ID_);
51 create index AI_HI_TASKINST_END_TIME on ARCHIVE_ACT_HI_TASKINST(END_TIME_);
53 /*--#4 ARCHIVE_ACT_HI_VARINST; */
54 create TABLE ARCHIVE_ACT_HI_VARINST
55 AS ( select * from ACT_HI_VARINST where 1=0);
57 create index AI_HI_VARINST_PROC_INST_ID on ARCHIVE_ACT_HI_VARINST(PROC_INST_ID_);
59 /*--#5 ARCHIVE_ACT_HI_DETAIL; */
60 create TABLE ARCHIVE_ACT_HI_DETAIL
61 AS ( select * from ACT_HI_DETAIL where 1=0);
63 create index AI_HI_DETAIL_PROC_INST_ID on ARCHIVE_ACT_HI_DETAIL(PROC_INST_ID_);
64 create index AI_HI_DETAIL_TIME on ARCHIVE_ACT_HI_DETAIL(TIME_);
66 /*--#6 ARCHIVE_ACT_HI_COMMENT; */
67 create TABLE ARCHIVE_ACT_HI_COMMENT
68 AS ( select * from ACT_HI_COMMENT where 1=0);
70 create index AI_HI_COMMENT_PROC_INST_ID on ARCHIVE_ACT_HI_COMMENT(PROC_INST_ID_);
71 create index AI_HI_COMMENT_TIME on ARCHIVE_ACT_HI_COMMENT(TIME_);
73 /*--#7 ARCHIVE_ACT_HI_ATTACHMENT; */
74 create TABLE ARCHIVE_ACT_HI_ATTACHMENT
75 AS ( select * from ACT_HI_ATTACHMENT where 1=0);
77 create index AI_HI_ATTACHMENT_PROC_INST_ID on ARCHIVE_ACT_HI_ATTACHMENT(PROC_INST_ID_);
79 /*--#8 ARCHIVE_ACT_HI_OP_LOG; */
80 create TABLE ARCHIVE_ACT_HI_OP_LOG
81 AS ( select * from ACT_HI_OP_LOG where 1=0);
83 create index AI_HI_OP_LOG_PROC_INST_ID on ARCHIVE_ACT_HI_OP_LOG(PROC_INST_ID_);
84 create index AI_HI_OP_LOG_TIMESTAMP on ARCHIVE_ACT_HI_OP_LOG(TIMESTAMP_);
86 /*--#9 ARCHIVE_ACT_HI_INCIDENT; */
87 create TABLE ARCHIVE_ACT_HI_INCIDENT
88 AS ( select * from ACT_HI_INCIDENT where 1=0);
90 create index AI_HI_INCIDENT_PROC_INST_ID on ARCHIVE_ACT_HI_INCIDENT(PROC_INST_ID_);
92 /*--#10 ARCHIVE_ACT_GE_BYTEARRAY; */
93 create TABLE ARCHIVE_ACT_GE_BYTEARRAY
94 AS ( select * from ACT_GE_BYTEARRAY where 1=0);
96 create index AI_GE_BYTEARRAY_ID_ on ARCHIVE_ACT_GE_BYTEARRAY(ID_);
98 /* -----------------------------------------------------------------------------
99 Extend a ARCHIVE: Table by two attributes: STAT_EXECUTION_ID, STAT_EXECUTION_TS
104 alter table ARCHIVE_%TableName%
105 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
106 CREATE INDEX AI_%TableName%_EXE_ID ON ARCHIVE_%TableName%(STAT_EXECUTION_ID);
110 /*--#1 ACT_HI_PROCINST */
111 alter table ARCHIVE_ACT_HI_PROCINST
112 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
113 CREATE INDEX AI_ACT_HI_PROCINST_EXE_ID ON ARCHIVE_ACT_HI_PROCINST(STAT_EXECUTION_ID);
115 /*--#2 ACT_HI_ACTINST */
116 alter table ARCHIVE_ACT_HI_ACTINST
117 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
118 CREATE INDEX AI_ACT_HI_ACTINST_EXE_ID ON ARCHIVE_ACT_HI_ACTINST(STAT_EXECUTION_ID);
120 /*--#3 ACT_HI_TASKINST */
121 alter table ARCHIVE_ACT_HI_TASKINST
122 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
123 CREATE INDEX AI_ACT_HI_TASKINST_EXE_ID ON ARCHIVE_ACT_HI_TASKINST(STAT_EXECUTION_ID);
125 /*--#4 ACT_HI_VARINST */
126 alter table ARCHIVE_ACT_HI_VARINST
127 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
128 CREATE INDEX AI_ACT_HI_VARINST_EXE_ID ON ARCHIVE_ACT_HI_VARINST(STAT_EXECUTION_ID);
130 /*--#5 ACT_HI_DETAIL */
131 alter table ARCHIVE_ACT_HI_DETAIL
132 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
133 CREATE INDEX AI_ACT_HI_DETAIL_EXE_ID ON ARCHIVE_ACT_HI_DETAIL(STAT_EXECUTION_ID);
135 /*--#6 ACT_HI_COMMENT */
136 alter table ARCHIVE_ACT_HI_COMMENT
137 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
138 CREATE INDEX AI_ACT_HI_COMMENT_EXE_ID ON ARCHIVE_ACT_HI_COMMENT(STAT_EXECUTION_ID);
140 /*--#7 ACT_HI_ATTACHMENT */
141 alter table ARCHIVE_ACT_HI_ATTACHMENT
142 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
143 CREATE INDEX AI_ACT_HI_ATTACHMENT_EXE_ID ON ARCHIVE_ACT_HI_ATTACHMENT(STAT_EXECUTION_ID);
145 /*--#8 ACT_HI_OP_LOG */
146 alter table ARCHIVE_ACT_HI_OP_LOG
147 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
148 CREATE INDEX AI_ACT_HI_OP_LOG_EXE_ID ON ARCHIVE_ACT_HI_OP_LOG(STAT_EXECUTION_ID);
150 /*--#9 ACT_HI_INCIDENT */
151 alter table ARCHIVE_ACT_HI_INCIDENT
152 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
153 CREATE INDEX AI_ACT_HI_INCIDENT_EXE_ID ON ARCHIVE_ACT_HI_INCIDENT(STAT_EXECUTION_ID);
155 /*--#10 ACT_GE_BYTEARRAY */
156 alter table ARCHIVE_ACT_GE_BYTEARRAY
157 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
158 CREATE INDEX AI_ACT_GE_BYTEARRAY_EXE_ID ON ARCHIVE_ACT_GE_BYTEARRAY(STAT_EXECUTION_ID);
161 /* -- Next Val as a user defined function needed only in MariaDB--*/
162 DROP FUNCTION IF EXISTS NextVal;
164 CREATE FUNCTION NextVal (vname VARCHAR(30))
167 -- Retrieve and update in single statement
172 RETURN (SELECT next FROM _sequences LIMIT 1);
177 /* -- History tables for use in archive procedure, there is no array type in MariaDB --*/
178 Create Table Camunda_Hi_Tables (id_ MEDIUMINT NOT NULL AUTO_INCREMENT,
179 TableName_ varchar(80) NOT NULL,
182 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_PROCINST');
183 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_ACTINST');
184 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_TASKINST');
185 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_VARINST');
186 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_DETAIL');
187 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_COMMENT');
188 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_ATTACHMENT');
189 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_OP_LOG');
190 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_INCIDENT');
193 CREATE TABLE TMPLOGTABLE (LogMessage Varchar(700));
196 /* -- Below user defined functions and procedures needed only in MariaDB, they are in-built in Oracle --*/
197 /*-- Create a sequence SP */
198 DROP PROCEDURE IF EXISTS CreateSequence;
200 CREATE PROCEDURE CreateSequence (name VARCHAR(30), start INT, inc INT)
202 -- Create a table to store sequences
203 CREATE TABLE IF NOT EXISTS _sequences
205 name VARCHAR(70) NOT NULL UNIQUE,
210 -- Add the new sequence
211 INSERT INTO _sequences VALUES (name, start, inc);
216 /*--------------------------------------------------------------------------------------------------
218 -------------------------------------------------------------------------------------------------- */
220 /* Create STAT_EXECUTION_SEQ: each Archive Entry has a same Execution ID during one Archiving Run */
221 CALL CreateSequence('STAT_EXECUTION_SEQ', 1, 1);