1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
|
-- Fix for https://itrack.web.att.com/browse/AJSCCMDA-90 --
use camundabpmn;
/*
1. 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_ MEDIUMINT NOT NULL AUTO_INCREMENT,
TableName_ varchar(80) NOT NULL,
PRIMARY KEY (id_));
Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_PROCINST');
Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_ACTINST');
Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_TASKINST');
Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_VARINST');
Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_DETAIL');
Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_COMMENT');
Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_ATTACHMENT');
Insert Into Camunda_Hi_Tables(TableName_) Values ('ACT_HI_OP_LOG');
Insert Into Camunda_Hi_Tables(TableName_) Values ('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);
|
