1 -- Fix for https://itrack.web.att.com/browse/AJSCCMDA-90 --
4 1. Add some Camunda Indexes to history schema part (for Archiving)
6 create INDEX IF NOT EXISTS IDX_ACT_HI_TASKINST_PIID ON ACT_HI_TASKINST (PROC_INST_ID_);
7 create INDEX IF NOT EXISTS IDX_ACT_HI_COMMENT_PIID ON ACT_HI_COMMENT (PROC_INST_ID_);
8 create INDEX IF NOT EXISTS IDX_ACT_HI_ATTACHMENT_PIID ON ACT_HI_ATTACHMENT (PROC_INST_ID_);
9 create INDEX IF NOT EXISTS IDX_ACT_HI_OP_LOG_PIID ON ACT_HI_OP_LOG (PROC_INST_ID_);
10 create INDEX IF NOT EXISTS IDX_ACT_HI_INCIDENT_PIID ON ACT_HI_INCIDENT (PROC_INST_ID_);
11 create INDEX IF NOT EXISTS IDX_ACT_HI_ACTINST_PIID ON ACT_HI_ACTINST(PROC_INST_ID_);
15 2. Create Archiving Tables in current schema
18 /*-- TMP_ARCHIVING_PROCINST */
19 CREATE TABLE TMP_ARCHIVING_PROCINST
20 ( PROC_INST_ID_ varchar(64) not null,
23 CREATE INDEX AI_TMP_ARCH_PROCINST_PI_ID ON TMP_ARCHIVING_PROCINST(PROC_INST_ID_);
25 /*-- TMP_ARCHIVING_BYTEARRAY */
26 CREATE TABLE TMP_ARCHIVING_BYTEARRAY
27 ( BYTEARRAY_ID_ varchar(64) not null,
28 PROC_INST_ID_ varchar(64)
30 CREATE INDEX AI_TMP_ARCH_BYTEARRAY_BAID ON TMP_ARCHIVING_BYTEARRAY(BYTEARRAY_ID_);
33 /*--#1 ARCHIVE_ACT_HI_PROCINST; */
34 create TABLE ARCHIVE_ACT_HI_PROCINST
35 AS ( select * from ACT_HI_PROCINST where 1=0);
37 create index AI_HI_PROCINST_END_TIME on ARCHIVE_ACT_HI_PROCINST(END_TIME_);
38 ALTER TABLE ARCHIVE_ACT_HI_PROCINST ADD CONSTRAINT ARCHIVE_ACT_HI_PROCINST_UQ UNIQUE ( PROC_INST_ID_);
40 /*--#2 ARCHIVE_ACT_HI_ACTINST; */
41 create TABLE ARCHIVE_ACT_HI_ACTINST
42 AS ( select * from ACT_HI_ACTINST where 1=0);
44 create index AI_HI_ACTINST_PROC_INST_ID on ARCHIVE_ACT_HI_ACTINST(PROC_INST_ID_);
45 create index AI_HI_ACTINST_END_TIME on ARCHIVE_ACT_HI_ACTINST(END_TIME_);
47 /*--#3 ARCHIVE_ACT_HI_TASKINST; */
48 create TABLE ARCHIVE_ACT_HI_TASKINST
49 AS ( select * from ACT_HI_TASKINST where 1=0);
51 create index AI_HI_TASKINST_PROC_INST_ID on ARCHIVE_ACT_HI_TASKINST(PROC_INST_ID_);
52 create index AI_HI_TASKINST_END_TIME on ARCHIVE_ACT_HI_TASKINST(END_TIME_);
54 /*--#4 ARCHIVE_ACT_HI_VARINST; */
55 create TABLE ARCHIVE_ACT_HI_VARINST
56 AS ( select * from ACT_HI_VARINST where 1=0);
58 create index AI_HI_VARINST_PROC_INST_ID on ARCHIVE_ACT_HI_VARINST(PROC_INST_ID_);
60 /*--#5 ARCHIVE_ACT_HI_DETAIL; */
61 create TABLE ARCHIVE_ACT_HI_DETAIL
62 AS ( select * from ACT_HI_DETAIL where 1=0);
64 create index AI_HI_DETAIL_PROC_INST_ID on ARCHIVE_ACT_HI_DETAIL(PROC_INST_ID_);
65 create index AI_HI_DETAIL_TIME on ARCHIVE_ACT_HI_DETAIL(TIME_);
67 /*--#6 ARCHIVE_ACT_HI_COMMENT; */
68 create TABLE ARCHIVE_ACT_HI_COMMENT
69 AS ( select * from ACT_HI_COMMENT where 1=0);
71 create index AI_HI_COMMENT_PROC_INST_ID on ARCHIVE_ACT_HI_COMMENT(PROC_INST_ID_);
72 create index AI_HI_COMMENT_TIME on ARCHIVE_ACT_HI_COMMENT(TIME_);
74 /*--#7 ARCHIVE_ACT_HI_ATTACHMENT; */
75 create TABLE ARCHIVE_ACT_HI_ATTACHMENT
76 AS ( select * from ACT_HI_ATTACHMENT where 1=0);
78 create index AI_HI_ATTACHMENT_PROC_INST_ID on ARCHIVE_ACT_HI_ATTACHMENT(PROC_INST_ID_);
80 /*--#8 ARCHIVE_ACT_HI_OP_LOG; */
81 create TABLE ARCHIVE_ACT_HI_OP_LOG
82 AS ( select * from ACT_HI_OP_LOG where 1=0);
84 create index AI_HI_OP_LOG_PROC_INST_ID on ARCHIVE_ACT_HI_OP_LOG(PROC_INST_ID_);
85 create index AI_HI_OP_LOG_TIMESTAMP on ARCHIVE_ACT_HI_OP_LOG(TIMESTAMP_);
87 /*--#9 ARCHIVE_ACT_HI_INCIDENT; */
88 create TABLE ARCHIVE_ACT_HI_INCIDENT
89 AS ( select * from ACT_HI_INCIDENT where 1=0);
91 create index AI_HI_INCIDENT_PROC_INST_ID on ARCHIVE_ACT_HI_INCIDENT(PROC_INST_ID_);
93 /*--#10 ARCHIVE_ACT_GE_BYTEARRAY; */
94 create TABLE ARCHIVE_ACT_GE_BYTEARRAY
95 AS ( select * from ACT_GE_BYTEARRAY where 1=0);
97 create index AI_GE_BYTEARRAY_ID_ on ARCHIVE_ACT_GE_BYTEARRAY(ID_);
99 /* -----------------------------------------------------------------------------
100 Extend a ARCHIVE: Table by two attributes: STAT_EXECUTION_ID, STAT_EXECUTION_TS
105 alter table ARCHIVE_%TableName%
106 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
107 CREATE INDEX AI_%TableName%_EXE_ID ON ARCHIVE_%TableName%(STAT_EXECUTION_ID);
111 /*--#1 ACT_HI_PROCINST */
112 alter table ARCHIVE_ACT_HI_PROCINST
113 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
114 CREATE INDEX AI_ACT_HI_PROCINST_EXE_ID ON ARCHIVE_ACT_HI_PROCINST(STAT_EXECUTION_ID);
116 /*--#2 ACT_HI_ACTINST */
117 alter table ARCHIVE_ACT_HI_ACTINST
118 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
119 CREATE INDEX AI_ACT_HI_ACTINST_EXE_ID ON ARCHIVE_ACT_HI_ACTINST(STAT_EXECUTION_ID);
121 /*--#3 ACT_HI_TASKINST */
122 alter table ARCHIVE_ACT_HI_TASKINST
123 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
124 CREATE INDEX AI_ACT_HI_TASKINST_EXE_ID ON ARCHIVE_ACT_HI_TASKINST(STAT_EXECUTION_ID);
126 /*--#4 ACT_HI_VARINST */
127 alter table ARCHIVE_ACT_HI_VARINST
128 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
129 CREATE INDEX AI_ACT_HI_VARINST_EXE_ID ON ARCHIVE_ACT_HI_VARINST(STAT_EXECUTION_ID);
131 /*--#5 ACT_HI_DETAIL */
132 alter table ARCHIVE_ACT_HI_DETAIL
133 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
134 CREATE INDEX AI_ACT_HI_DETAIL_EXE_ID ON ARCHIVE_ACT_HI_DETAIL(STAT_EXECUTION_ID);
136 /*--#6 ACT_HI_COMMENT */
137 alter table ARCHIVE_ACT_HI_COMMENT
138 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
139 CREATE INDEX AI_ACT_HI_COMMENT_EXE_ID ON ARCHIVE_ACT_HI_COMMENT(STAT_EXECUTION_ID);
141 /*--#7 ACT_HI_ATTACHMENT */
142 alter table ARCHIVE_ACT_HI_ATTACHMENT
143 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
144 CREATE INDEX AI_ACT_HI_ATTACHMENT_EXE_ID ON ARCHIVE_ACT_HI_ATTACHMENT(STAT_EXECUTION_ID);
146 /*--#8 ACT_HI_OP_LOG */
147 alter table ARCHIVE_ACT_HI_OP_LOG
148 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
149 CREATE INDEX AI_ACT_HI_OP_LOG_EXE_ID ON ARCHIVE_ACT_HI_OP_LOG(STAT_EXECUTION_ID);
151 /*--#9 ACT_HI_INCIDENT */
152 alter table ARCHIVE_ACT_HI_INCIDENT
153 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
154 CREATE INDEX AI_ACT_HI_INCIDENT_EXE_ID ON ARCHIVE_ACT_HI_INCIDENT(STAT_EXECUTION_ID);
156 /*--#10 ACT_GE_BYTEARRAY */
157 alter table ARCHIVE_ACT_GE_BYTEARRAY
158 add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP );
159 CREATE INDEX AI_ACT_GE_BYTEARRAY_EXE_ID ON ARCHIVE_ACT_GE_BYTEARRAY(STAT_EXECUTION_ID);
162 /* -- Next Val as a user defined function needed only in MariaDB--*/
163 DROP FUNCTION IF EXISTS NextVal;
165 CREATE FUNCTION NextVal (vname VARCHAR(30))
168 -- Retrieve and update in single statement
173 RETURN (SELECT next FROM _sequences LIMIT 1);
178 /* -- History tables for use in archive procedure, there is no array type in MariaDB --*/
179 Create Table Camunda_Hi_Tables (id_ MEDIUMINT NOT NULL AUTO_INCREMENT,
180 TableName_ varchar(80) NOT NULL,
183 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_PROCINST');
184 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_ACTINST');
185 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_TASKINST');
186 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_VARINST');
187 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_DETAIL');
188 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_COMMENT');
189 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_ATTACHMENT');
190 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_OP_LOG');
191 Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_INCIDENT');
194 CREATE TABLE TMPLOGTABLE (LogMessage Varchar(700));
197 /* -- Below user defined functions and procedures needed only in MariaDB, they are in-built in Oracle --*/
198 /*-- Create a sequence SP */
199 DROP PROCEDURE IF EXISTS CreateSequence;
201 CREATE PROCEDURE CreateSequence (name VARCHAR(30), start INT, inc INT)
203 -- Create a table to store sequences
204 CREATE TABLE IF NOT EXISTS _sequences
206 name VARCHAR(70) NOT NULL UNIQUE,
211 -- Add the new sequence
212 INSERT INTO _sequences VALUES (name, start, inc);
217 /*--------------------------------------------------------------------------------------------------
219 -------------------------------------------------------------------------------------------------- */
221 /* Create STAT_EXECUTION_SEQ: each Archive Entry has a same Execution ID during one Archiving Run */
222 CALL CreateSequence('STAT_EXECUTION_SEQ', 1, 1);