iJwEv2jj

1CREATE TABLE IF NOT EXISTS REG_CLUSTER_LOCK (
2             REG_LOCK_NAME VARCHAR (20),
3             REG_LOCK_STATUS VARCHAR (20),
4             REG_LOCKED_TIME TIMESTAMP,
5             REG_TENANT_ID INTEGER DEFAULT 0,
6             PRIMARY KEY (REG_LOCK_NAME)
7)ENGINE INNODB;
8
9CREATE TABLE IF NOT EXISTS REG_LOG (
10             REG_LOG_ID INTEGER AUTO_INCREMENT,
11             REG_PATH VARCHAR (750),
12             REG_USER_ID VARCHAR (31) NOT NULL,
13             REG_LOGGED_TIME TIMESTAMP NOT NULL,
14             REG_ACTION INTEGER NOT NULL,
15             REG_ACTION_DATA VARCHAR (500),
16             REG_TENANT_ID INTEGER DEFAULT 0,
17             PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID)
18)ENGINE INNODB;
19
20CREATE INDEX REG_LOG_IND_BY_REGLOG USING HASH ON REG_LOG(REG_LOGGED_TIME, REG_TENANT_ID);
21
22-- The REG_PATH_VALUE should be less than 767 bytes, and hence was fixed at 750.
23-- See CARBON-5917.
24
25CREATE TABLE IF NOT EXISTS REG_PATH(
26             REG_PATH_ID INTEGER NOT NULL AUTO_INCREMENT,
27             REG_PATH_VALUE VARCHAR(750) NOT NULL,
28             REG_PATH_PARENT_ID INTEGER,
29             REG_TENANT_ID INTEGER DEFAULT 0,
30             CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID)
31)ENGINE INNODB;
32
33CREATE INDEX REG_PATH_IND_BY_PATH_VALUE USING HASH ON REG_PATH(REG_PATH_VALUE, REG_TENANT_ID);
34CREATE INDEX REG_PATH_IND_BY_PATH_PARENT_ID USING HASH ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID);
35
36CREATE TABLE IF NOT EXISTS REG_CONTENT (
37             REG_CONTENT_ID INTEGER NOT NULL AUTO_INCREMENT,
38             REG_CONTENT_DATA LONGBLOB,
39             REG_TENANT_ID INTEGER DEFAULT 0,
40             CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
41)ENGINE INNODB;
42
43CREATE TABLE IF NOT EXISTS REG_CONTENT_HISTORY (
44             REG_CONTENT_ID INTEGER NOT NULL,
45             REG_CONTENT_DATA LONGBLOB,
46             REG_DELETED   SMALLINT,
47             REG_TENANT_ID INTEGER DEFAULT 0,
48             CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
49)ENGINE INNODB;
50
51CREATE TABLE IF NOT EXISTS REG_RESOURCE (
52            REG_PATH_ID         INTEGER NOT NULL,
53            REG_NAME            VARCHAR(256),
54            REG_VERSION         INTEGER NOT NULL AUTO_INCREMENT,
55            REG_MEDIA_TYPE      VARCHAR(500),
56            REG_CREATOR         VARCHAR(31) NOT NULL,
57            REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
58            REG_LAST_UPDATOR    VARCHAR(31),
59            REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
60            REG_DESCRIPTION     VARCHAR(1000),
61            REG_CONTENT_ID      INTEGER,
62            REG_TENANT_ID INTEGER DEFAULT 0,
63            REG_UUID VARCHAR(100) NOT NULL,
64            CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
65)ENGINE INNODB;
66
67ALTER TABLE REG_RESOURCE ADD CONSTRAINT REG_RESOURCE_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
68ALTER TABLE REG_RESOURCE ADD CONSTRAINT REG_RESOURCE_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT (REG_CONTENT_ID, REG_TENANT_ID);
69CREATE INDEX REG_RESOURCE_IND_BY_NAME USING HASH ON REG_RESOURCE(REG_NAME, REG_TENANT_ID);
70CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
71CREATE INDEX REG_RESOURCE_IND_BY_UUID USING HASH ON REG_RESOURCE(REG_UUID);
72CREATE INDEX REG_RESOURCE_IND_BY_TENAN USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_UUID);
73CREATE INDEX REG_RESOURCE_IND_BY_TYPE USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_MEDIA_TYPE);
74
75CREATE TABLE IF NOT EXISTS REG_RESOURCE_HISTORY (
76            REG_PATH_ID         INTEGER NOT NULL,
77            REG_NAME            VARCHAR(256),
78            REG_VERSION         INTEGER NOT NULL,
79            REG_MEDIA_TYPE      VARCHAR(500),
80            REG_CREATOR         VARCHAR(31) NOT NULL,
81            REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
82            REG_LAST_UPDATOR    VARCHAR(31),
83            REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
84            REG_DESCRIPTION     VARCHAR(1000),
85            REG_CONTENT_ID      INTEGER,
86            REG_DELETED         SMALLINT,
87            REG_TENANT_ID INTEGER DEFAULT 0,
88            REG_UUID VARCHAR(100) NOT NULL,
89            CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
90)ENGINE INNODB;
91
92ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_PATHID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
93ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY (REG_CONTENT_ID, REG_TENANT_ID);
94CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME USING HASH ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID);
95CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
96
97CREATE TABLE IF NOT EXISTS REG_COMMENT (
98            REG_ID        INTEGER NOT NULL AUTO_INCREMENT,
99            REG_COMMENT_TEXT      VARCHAR(500) NOT NULL,
100            REG_USER_ID           VARCHAR(31) NOT NULL,
101            REG_COMMENTED_TIME    TIMESTAMP NOT NULL,
102            REG_TENANT_ID INTEGER DEFAULT 0,
103            CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID)
104)ENGINE INNODB;
105
106CREATE TABLE IF NOT EXISTS REG_RESOURCE_COMMENT (
107            REG_COMMENT_ID          INTEGER NOT NULL,
108            REG_VERSION             INTEGER,
109            REG_PATH_ID             INTEGER,
110            REG_RESOURCE_NAME       VARCHAR(256),
111            REG_TENANT_ID INTEGER DEFAULT 0
112)ENGINE INNODB;
113
114ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
115ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID FOREIGN KEY (REG_COMMENT_ID, REG_TENANT_ID) REFERENCES REG_COMMENT (REG_ID, REG_TENANT_ID);
116CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
117CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION USING HASH ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID);
118
119CREATE TABLE IF NOT EXISTS REG_RATING (
120            REG_ID     INTEGER NOT NULL AUTO_INCREMENT,
121            REG_RATING        INTEGER NOT NULL,
122            REG_USER_ID       VARCHAR(31) NOT NULL,
123            REG_RATED_TIME    TIMESTAMP NOT NULL,
124            REG_TENANT_ID INTEGER DEFAULT 0,
125            CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID)
126)ENGINE INNODB;
127
128CREATE TABLE IF NOT EXISTS REG_RESOURCE_RATING (
129            REG_RATING_ID           INTEGER NOT NULL,
130            REG_VERSION             INTEGER,
131            REG_PATH_ID             INTEGER,
132            REG_RESOURCE_NAME       VARCHAR(256),
133            REG_TENANT_ID INTEGER DEFAULT 0
134)ENGINE INNODB;
135
136ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
137ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_RATING_ID FOREIGN KEY (REG_RATING_ID, REG_TENANT_ID) REFERENCES REG_RATING (REG_ID, REG_TENANT_ID);
138CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
139CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION USING HASH ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID);
140
141
142CREATE TABLE IF NOT EXISTS REG_TAG (
143            REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
144            REG_TAG_NAME       VARCHAR(500) NOT NULL,
145            REG_USER_ID        VARCHAR(31) NOT NULL,
146            REG_TAGGED_TIME    TIMESTAMP NOT NULL,
147            REG_TENANT_ID INTEGER DEFAULT 0,
148            CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID)
149)ENGINE INNODB;
150
151CREATE TABLE IF NOT EXISTS REG_RESOURCE_TAG (
152            REG_TAG_ID              INTEGER NOT NULL,
153            REG_VERSION             INTEGER,
154            REG_PATH_ID             INTEGER,
155            REG_RESOURCE_NAME       VARCHAR(256),
156            REG_TENANT_ID INTEGER DEFAULT 0
157)ENGINE INNODB;
158
159ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
160ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID);
161CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
162CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION USING HASH ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID);
163
164CREATE TABLE IF NOT EXISTS REG_PROPERTY (
165            REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
166            REG_NAME       VARCHAR(100) NOT NULL,
167            REG_VALUE        VARCHAR(1000),
168            REG_TENANT_ID INTEGER DEFAULT 0,
169            CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID)
170)ENGINE INNODB;
171
172CREATE TABLE IF NOT EXISTS REG_RESOURCE_PROPERTY (
173            REG_PROPERTY_ID         INTEGER NOT NULL,
174            REG_VERSION             INTEGER,
175            REG_PATH_ID             INTEGER,
176            REG_RESOURCE_NAME       VARCHAR(256),
177            REG_TENANT_ID INTEGER DEFAULT 0
178)ENGINE INNODB;
179
180ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
181ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID);
182CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
183CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION USING HASH ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID);
184
185-- CREATE TABLE IF NOT EXISTS REG_ASSOCIATIONS (
186-- SRC_PATH_ID     INTEGER,
187-- SRC_RESOURCE_NAME    VARCHAR(256),
188-- SRC_VERSION     INTEGER,
189-- TGT_PATH_ID     INTEGER,
190-- TGT_RESOURCE_NAME    VARCHAR(256),
191-- TGT_VERSION     INTEGER
192-- )ENGINE INNODB;
193--
194-- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_SRC_PATH_ID FOREIGN KEY (SRC_PATH_ID) REFERENCES REG_PATH (PATH_ID);
195-- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_TGT_PATH_ID FOREIGN KEY (TGT_PATH_ID) REFERENCES REG_PATH (PATH_ID);
196-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_VERSION ON REG_ASSOCIATIONS(SRC_VERSION);
197-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_VERSION ON REG_ASSOCIATIONS(TGT_VERSION);
198-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_RESOURCE_NAME ON REG_ASSOCIATIONS(SRC_RESOURCE_NAME);
199-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_RESOURCE_NAME ON REG_ASSOCIATIONS(TGT_RESOURCE_NAME);
200
201
202
203CREATE TABLE IF NOT EXISTS REG_ASSOCIATION (
204            REG_ASSOCIATION_ID INTEGER AUTO_INCREMENT,
205            REG_SOURCEPATH VARCHAR (750) NOT NULL,
206            REG_TARGETPATH VARCHAR (750) NOT NULL,
207            REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL,
208            REG_TENANT_ID INTEGER DEFAULT 0,
209            PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID)
210)ENGINE INNODB;
211
212CREATE TABLE IF NOT EXISTS REG_SNAPSHOT (
213            REG_SNAPSHOT_ID     INTEGER NOT NULL AUTO_INCREMENT,
214            REG_PATH_ID            INTEGER NOT NULL,
215            REG_RESOURCE_NAME      VARCHAR(255),
216            REG_RESOURCE_VIDS     LONGBLOB NOT NULL,
217            REG_TENANT_ID INTEGER DEFAULT 0,
218            CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID)
219)ENGINE INNODB;
220
221CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
222
223ALTER TABLE REG_SNAPSHOT ADD CONSTRAINT REG_SNAPSHOT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
224
225
226-- ################################
227-- USER MANAGER TABLES
228-- ################################
229
230CREATE TABLE UM_TENANT (
231			UM_ID INTEGER NOT NULL AUTO_INCREMENT,
232	        UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
233            UM_EMAIL VARCHAR(255),
234            UM_ACTIVE BOOLEAN DEFAULT FALSE,
235	        UM_CREATED_DATE TIMESTAMP NOT NULL,
236	        UM_USER_CONFIG LONGBLOB,
237			PRIMARY KEY (UM_ID),
238			UNIQUE(UM_DOMAIN_NAME)
239)ENGINE INNODB;
240
241CREATE TABLE UM_DOMAIN(
242            UM_DOMAIN_ID INTEGER NOT NULL AUTO_INCREMENT,
243            UM_DOMAIN_NAME VARCHAR(255),
244            UM_TENANT_ID INTEGER DEFAULT 0,
245            PRIMARY KEY (UM_DOMAIN_ID, UM_TENANT_ID)
246)ENGINE INNODB;
247
248CREATE UNIQUE INDEX INDEX_UM_TENANT_UM_DOMAIN_NAME
249                    ON UM_TENANT (UM_DOMAIN_NAME);
250
251CREATE TABLE UM_USER (
252             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
253             UM_USER_NAME VARCHAR(255) NOT NULL,
254             UM_USER_PASSWORD VARCHAR(255) NOT NULL,
255             UM_SALT_VALUE VARCHAR(31),
256             UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
257             UM_CHANGED_TIME TIMESTAMP NOT NULL,
258             UM_TENANT_ID INTEGER DEFAULT 0,
259             PRIMARY KEY (UM_ID, UM_TENANT_ID),
260             UNIQUE(UM_USER_NAME, UM_TENANT_ID)
261)ENGINE INNODB;
262
263CREATE TABLE UM_SYSTEM_USER (
264             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
265             UM_USER_NAME VARCHAR(255) NOT NULL,
266             UM_USER_PASSWORD VARCHAR(255) NOT NULL,
267             UM_SALT_VALUE VARCHAR(31),
268             UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
269             UM_CHANGED_TIME TIMESTAMP NOT NULL,
270             UM_TENANT_ID INTEGER DEFAULT 0,
271             PRIMARY KEY (UM_ID, UM_TENANT_ID),
272             UNIQUE(UM_USER_NAME, UM_TENANT_ID)
273)ENGINE INNODB;
274
275CREATE TABLE UM_ROLE (
276             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
277             UM_ROLE_NAME VARCHAR(255) NOT NULL,
278             UM_TENANT_ID INTEGER DEFAULT 0,
279		UM_SHARED_ROLE BOOLEAN DEFAULT FALSE,
280             PRIMARY KEY (UM_ID, UM_TENANT_ID),
281             UNIQUE(UM_ROLE_NAME, UM_TENANT_ID)
282)ENGINE INNODB;
283
284
285CREATE TABLE UM_MODULE(
286	UM_ID INTEGER  NOT NULL AUTO_INCREMENT,
287	UM_MODULE_NAME VARCHAR(100),
288	UNIQUE(UM_MODULE_NAME),
289	PRIMARY KEY(UM_ID)
290)ENGINE INNODB;
291
292CREATE TABLE UM_MODULE_ACTIONS(
293	UM_ACTION VARCHAR(255) NOT NULL,
294	UM_MODULE_ID INTEGER NOT NULL,
295	PRIMARY KEY(UM_ACTION, UM_MODULE_ID),
296	FOREIGN KEY (UM_MODULE_ID) REFERENCES UM_MODULE(UM_ID) ON DELETE CASCADE
297)ENGINE INNODB;
298
299CREATE TABLE UM_PERMISSION (
300             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
301             UM_RESOURCE_ID VARCHAR(255) NOT NULL,
302             UM_ACTION VARCHAR(255) NOT NULL,
303             UM_TENANT_ID INTEGER DEFAULT 0,
304		UM_MODULE_ID INTEGER DEFAULT 0,
305			       UNIQUE(UM_RESOURCE_ID,UM_ACTION, UM_TENANT_ID),
306             PRIMARY KEY (UM_ID, UM_TENANT_ID)
307)ENGINE INNODB;
308
309CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID);
310
311CREATE TABLE UM_ROLE_PERMISSION (
312             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
313             UM_PERMISSION_ID INTEGER NOT NULL,
314             UM_ROLE_NAME VARCHAR(255) NOT NULL,
315             UM_IS_ALLOWED SMALLINT NOT NULL,
316             UM_TENANT_ID INTEGER DEFAULT 0,
317	     UM_DOMAIN_ID INTEGER,
318             UNIQUE (UM_PERMISSION_ID, UM_ROLE_NAME, UM_TENANT_ID, UM_DOMAIN_ID),
319	     FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
320	     FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
321             PRIMARY KEY (UM_ID, UM_TENANT_ID)
322)ENGINE INNODB;
323
324-- REMOVED UNIQUE (UM_PERMISSION_ID, UM_ROLE_ID)
325CREATE TABLE UM_USER_PERMISSION (
326             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
327             UM_PERMISSION_ID INTEGER NOT NULL,
328             UM_USER_NAME VARCHAR(255) NOT NULL,
329             UM_IS_ALLOWED SMALLINT NOT NULL,
330             UM_TENANT_ID INTEGER DEFAULT 0,
331             FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
332             PRIMARY KEY (UM_ID, UM_TENANT_ID)
333)ENGINE INNODB;
334
335-- REMOVED UNIQUE (UM_PERMISSION_ID, UM_USER_ID)
336CREATE TABLE UM_USER_ROLE (
337             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
338             UM_ROLE_ID INTEGER NOT NULL,
339             UM_USER_ID INTEGER NOT NULL,
340             UM_TENANT_ID INTEGER DEFAULT 0,
341             UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID),
342             FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID, UM_TENANT_ID),
343             FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
344             PRIMARY KEY (UM_ID, UM_TENANT_ID)
345)ENGINE INNODB;
346
347CREATE TABLE UM_SHARED_USER_ROLE(
348    UM_ROLE_ID INTEGER NOT NULL,
349    UM_USER_ID INTEGER NOT NULL,
350    UM_USER_TENANT_ID INTEGER NOT NULL,
351    UM_ROLE_TENANT_ID INTEGER NOT NULL,
352    UNIQUE(UM_USER_ID,UM_ROLE_ID,UM_USER_TENANT_ID, UM_ROLE_TENANT_ID),
353    FOREIGN KEY(UM_ROLE_ID,UM_ROLE_TENANT_ID) REFERENCES UM_ROLE(UM_ID,UM_TENANT_ID) ON DELETE CASCADE,
354    FOREIGN KEY(UM_USER_ID,UM_USER_TENANT_ID) REFERENCES UM_USER(UM_ID,UM_TENANT_ID) ON DELETE CASCADE
355)ENGINE INNODB;
356
357CREATE TABLE UM_ACCOUNT_MAPPING(
358	UM_ID INTEGER NOT NULL AUTO_INCREMENT,
359	UM_USER_NAME VARCHAR(255) NOT NULL,
360	UM_TENANT_ID INTEGER NOT NULL,
361	UM_USER_STORE_DOMAIN VARCHAR(100),
362	UM_ACC_LINK_ID INTEGER NOT NULL,
363	UNIQUE(UM_USER_NAME, UM_TENANT_ID, UM_USER_STORE_DOMAIN, UM_ACC_LINK_ID),
364	FOREIGN KEY (UM_TENANT_ID) REFERENCES UM_TENANT(UM_ID) ON DELETE CASCADE,
365	PRIMARY KEY (UM_ID)
366)ENGINE INNODB;
367
368
369CREATE TABLE UM_USER_ATTRIBUTE (
370            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
371            UM_ATTR_NAME VARCHAR(255) NOT NULL,
372            UM_ATTR_VALUE VARCHAR(1024),
373            UM_PROFILE_ID VARCHAR(255),
374            UM_USER_ID INTEGER,
375            UM_TENANT_ID INTEGER DEFAULT 0,
376            FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
377            PRIMARY KEY (UM_ID, UM_TENANT_ID)
378)ENGINE INNODB;
379
380CREATE INDEX UM_USER_ID_INDEX ON UM_USER_ATTRIBUTE(UM_USER_ID);
381
382CREATE TABLE UM_DIALECT(
383            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
384            UM_DIALECT_URI VARCHAR(255) NOT NULL,
385            UM_TENANT_ID INTEGER DEFAULT 0,
386            UNIQUE(UM_DIALECT_URI, UM_TENANT_ID),
387            PRIMARY KEY (UM_ID, UM_TENANT_ID)
388)ENGINE INNODB;
389
390CREATE TABLE UM_CLAIM(
391            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
392            UM_DIALECT_ID INTEGER NOT NULL,
393            UM_CLAIM_URI VARCHAR(255) NOT NULL,
394            UM_DISPLAY_TAG VARCHAR(255),
395            UM_DESCRIPTION VARCHAR(255),
396            UM_MAPPED_ATTRIBUTE_DOMAIN VARCHAR(255),
397            UM_MAPPED_ATTRIBUTE VARCHAR(255),
398            UM_REG_EX VARCHAR(255),
399            UM_SUPPORTED SMALLINT,
400            UM_REQUIRED SMALLINT,
401            UM_DISPLAY_ORDER INTEGER,
402	    UM_CHECKED_ATTRIBUTE SMALLINT,
403            UM_READ_ONLY SMALLINT,
404            UM_TENANT_ID INTEGER DEFAULT 0,
405            UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID,UM_MAPPED_ATTRIBUTE_DOMAIN),
406            FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
407            PRIMARY KEY (UM_ID, UM_TENANT_ID)
408)ENGINE INNODB;
409
410
411CREATE TABLE UM_PROFILE_CONFIG(
412            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
413            UM_DIALECT_ID INTEGER NOT NULL,
414            UM_PROFILE_NAME VARCHAR(255),
415            UM_TENANT_ID INTEGER DEFAULT 0,
416            FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
417            PRIMARY KEY (UM_ID, UM_TENANT_ID)
418)ENGINE INNODB;
419
420CREATE TABLE IF NOT EXISTS UM_CLAIM_BEHAVIOR(
421    UM_ID INTEGER NOT NULL AUTO_INCREMENT,
422    UM_PROFILE_ID INTEGER,
423    UM_CLAIM_ID INTEGER,
424    UM_BEHAVIOUR SMALLINT,
425    UM_TENANT_ID INTEGER DEFAULT 0,
426    FOREIGN KEY(UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG(UM_ID,UM_TENANT_ID),
427    FOREIGN KEY(UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM(UM_ID,UM_TENANT_ID),
428    PRIMARY KEY(UM_ID, UM_TENANT_ID)
429)ENGINE INNODB;
430
431CREATE TABLE UM_HYBRID_ROLE(
432            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
433            UM_ROLE_NAME VARCHAR(255),
434            UM_TENANT_ID INTEGER DEFAULT 0,
435            PRIMARY KEY (UM_ID, UM_TENANT_ID)
436)ENGINE INNODB;
437
438CREATE TABLE UM_HYBRID_USER_ROLE(
439            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
440            UM_USER_NAME VARCHAR(255),
441            UM_ROLE_ID INTEGER NOT NULL,
442            UM_TENANT_ID INTEGER DEFAULT 0,
443	    UM_DOMAIN_ID INTEGER,
444            UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID, UM_DOMAIN_ID),
445            FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
446	    FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
447            PRIMARY KEY (UM_ID, UM_TENANT_ID)
448)ENGINE INNODB;
449
450CREATE TABLE UM_SYSTEM_ROLE(
451            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
452            UM_ROLE_NAME VARCHAR(255),
453            UM_TENANT_ID INTEGER DEFAULT 0,
454            PRIMARY KEY (UM_ID, UM_TENANT_ID)
455)ENGINE INNODB;
456
457CREATE INDEX SYSTEM_ROLE_IND_BY_RN_TI ON UM_SYSTEM_ROLE(UM_ROLE_NAME, UM_TENANT_ID);
458
459CREATE TABLE UM_SYSTEM_USER_ROLE(
460            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
461            UM_USER_NAME VARCHAR(255),
462            UM_ROLE_ID INTEGER NOT NULL,
463            UM_TENANT_ID INTEGER DEFAULT 0,
464            UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID),
465            FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_SYSTEM_ROLE(UM_ID, UM_TENANT_ID),
466            PRIMARY KEY (UM_ID, UM_TENANT_ID)
467)ENGINE INNODB;
468
469
470CREATE TABLE UM_HYBRID_REMEMBER_ME(
471            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
472			UM_USER_NAME VARCHAR(255) NOT NULL,
473			UM_COOKIE_VALUE VARCHAR(1024),
474			UM_CREATED_TIME TIMESTAMP,
475            UM_TENANT_ID INTEGER DEFAULT 0,
476			PRIMARY KEY (UM_ID, UM_TENANT_ID)
477)ENGINE INNODB;
478
479
480
481CREATE TABLE CM_PII_CATEGORY (
482  ID           INTEGER AUTO_INCREMENT,
483  NAME         VARCHAR(255) NOT NULL,
484  DESCRIPTION  VARCHAR(1023),
485  DISPLAY_NAME VARCHAR(255),
486  IS_SENSITIVE INTEGER      NOT NULL,
487  TENANT_ID    INTEGER DEFAULT '-1234',
488  UNIQUE KEY (NAME, TENANT_ID),
489  PRIMARY KEY (ID)
490);
491
492CREATE TABLE CM_RECEIPT (
493  CONSENT_RECEIPT_ID  VARCHAR(255) NOT NULL,
494  VERSION             VARCHAR(255) NOT NULL,
495  JURISDICTION        VARCHAR(255) NOT NULL,
496  CONSENT_TIMESTAMP   TIMESTAMP    NOT NULL,
497  COLLECTION_METHOD   VARCHAR(255) NOT NULL,
498  LANGUAGE            VARCHAR(255) NOT NULL,
499  PII_PRINCIPAL_ID    VARCHAR(255) NOT NULL,
500  PRINCIPAL_TENANT_ID INTEGER DEFAULT '-1234',
501  POLICY_URL          VARCHAR(255) NOT NULL,
502  STATE               VARCHAR(255) NOT NULL,
503  PII_CONTROLLER      VARCHAR(2048) NOT NULL,
504  PRIMARY KEY (CONSENT_RECEIPT_ID)
505);
506
507CREATE TABLE CM_PURPOSE (
508  ID            INTEGER AUTO_INCREMENT,
509  NAME          VARCHAR(255) NOT NULL,
510  DESCRIPTION   VARCHAR(1023),
511  PURPOSE_GROUP VARCHAR(255) NOT NULL,
512  GROUP_TYPE    VARCHAR(255) NOT NULL,
513  TENANT_ID     INTEGER DEFAULT '-1234',
514  UNIQUE KEY (NAME, TENANT_ID, PURPOSE_GROUP, GROUP_TYPE),
515  PRIMARY KEY (ID)
516);
517
518CREATE TABLE CM_PURPOSE_CATEGORY (
519  ID          INTEGER AUTO_INCREMENT,
520  NAME        VARCHAR(255) NOT NULL,
521  DESCRIPTION VARCHAR(1023),
522  TENANT_ID   INTEGER DEFAULT '-1234',
523  UNIQUE KEY (NAME, TENANT_ID),
524  PRIMARY KEY (ID)
525);
526
527CREATE TABLE CM_RECEIPT_SP_ASSOC (
528  ID                 INTEGER AUTO_INCREMENT,
529  CONSENT_RECEIPT_ID VARCHAR(255) NOT NULL,
530  SP_NAME            VARCHAR(255) NOT NULL,
531  SP_DISPLAY_NAME    VARCHAR(255),
532  SP_DESCRIPTION     VARCHAR(255),
533  SP_TENANT_ID       INTEGER DEFAULT '-1234',
534  UNIQUE KEY (CONSENT_RECEIPT_ID, SP_NAME, SP_TENANT_ID),
535  PRIMARY KEY (ID)
536);
537
538CREATE TABLE CM_SP_PURPOSE_ASSOC (
539  ID                     INTEGER AUTO_INCREMENT,
540  RECEIPT_SP_ASSOC       INTEGER      NOT NULL,
541  PURPOSE_ID             INTEGER      NOT NULL,
542  CONSENT_TYPE           VARCHAR(255) NOT NULL,
543  IS_PRIMARY_PURPOSE     INTEGER      NOT NULL,
544  TERMINATION            VARCHAR(255) NOT NULL,
545  THIRD_PARTY_DISCLOSURE INTEGER      NOT NULL,
546  THIRD_PARTY_NAME       VARCHAR(255),
547  UNIQUE KEY (RECEIPT_SP_ASSOC, PURPOSE_ID),
548  PRIMARY KEY (ID)
549);
550
551CREATE TABLE CM_SP_PURPOSE_PURPOSE_CAT_ASSC (
552  SP_PURPOSE_ASSOC_ID INTEGER NOT NULL,
553  PURPOSE_CATEGORY_ID INTEGER NOT NULL,
554  UNIQUE KEY (SP_PURPOSE_ASSOC_ID, PURPOSE_CATEGORY_ID)
555);
556
557CREATE TABLE CM_PURPOSE_PII_CAT_ASSOC (
558  PURPOSE_ID         INTEGER NOT NULL,
559  CM_PII_CATEGORY_ID INTEGER NOT NULL,
560  IS_MANDATORY       INTEGER NOT NULL,
561  UNIQUE KEY (PURPOSE_ID, CM_PII_CATEGORY_ID)
562);
563
564CREATE TABLE CM_SP_PURPOSE_PII_CAT_ASSOC (
565  SP_PURPOSE_ASSOC_ID INTEGER NOT NULL,
566  PII_CATEGORY_ID     INTEGER NOT NULL,
567  VALIDITY            VARCHAR(1023),
568  UNIQUE KEY (SP_PURPOSE_ASSOC_ID, PII_CATEGORY_ID)
569);
570
571CREATE TABLE CM_CONSENT_RECEIPT_PROPERTY (
572  CONSENT_RECEIPT_ID VARCHAR(255)  NOT NULL,
573  NAME               VARCHAR(255)  NOT NULL,
574  VALUE              VARCHAR(1023) NOT NULL,
575  UNIQUE KEY (CONSENT_RECEIPT_ID, NAME)
576);
577
578ALTER TABLE CM_RECEIPT_SP_ASSOC
579  ADD CONSTRAINT CM_RECEIPT_SP_ASSOC_fk0 FOREIGN KEY (CONSENT_RECEIPT_ID) REFERENCES CM_RECEIPT (CONSENT_RECEIPT_ID);
580
581ALTER TABLE CM_SP_PURPOSE_ASSOC
582  ADD CONSTRAINT CM_SP_PURPOSE_ASSOC_fk0 FOREIGN KEY (RECEIPT_SP_ASSOC) REFERENCES CM_RECEIPT_SP_ASSOC (ID);
583
584ALTER TABLE CM_SP_PURPOSE_ASSOC
585  ADD CONSTRAINT CM_SP_PURPOSE_ASSOC_fk1 FOREIGN KEY (PURPOSE_ID) REFERENCES CM_PURPOSE (ID);
586
587ALTER TABLE CM_SP_PURPOSE_PURPOSE_CAT_ASSC
588  ADD CONSTRAINT CM_SP_P_P_CAT_ASSOC_fk0 FOREIGN KEY (SP_PURPOSE_ASSOC_ID) REFERENCES CM_SP_PURPOSE_ASSOC (ID);
589
590ALTER TABLE CM_SP_PURPOSE_PURPOSE_CAT_ASSC
591  ADD CONSTRAINT CM_SP_P_P_CAT_ASSOC_fk1 FOREIGN KEY (PURPOSE_CATEGORY_ID) REFERENCES CM_PURPOSE_CATEGORY (ID);
592
593ALTER TABLE CM_SP_PURPOSE_PII_CAT_ASSOC
594  ADD CONSTRAINT CM_SP_P_PII_CAT_ASSOC_fk0 FOREIGN KEY (SP_PURPOSE_ASSOC_ID) REFERENCES CM_SP_PURPOSE_ASSOC (ID);
595
596ALTER TABLE CM_SP_PURPOSE_PII_CAT_ASSOC
597  ADD CONSTRAINT CM_SP_P_PII_CAT_ASSOC_fk1 FOREIGN KEY (PII_CATEGORY_ID) REFERENCES CM_PII_CATEGORY (ID);
598
599ALTER TABLE CM_CONSENT_RECEIPT_PROPERTY
600  ADD CONSTRAINT CM_CONSENT_RECEIPT_PRT_fk0 FOREIGN KEY (CONSENT_RECEIPT_ID) REFERENCES CM_RECEIPT (CONSENT_RECEIPT_ID);
601
602INSERT INTO CM_PURPOSE (NAME, DESCRIPTION, PURPOSE_GROUP, GROUP_TYPE, TENANT_ID) VALUES ('DEFAULT', 'For core functionalities of the product', 'DEFAULT', 'SP', '-1234');
603
604INSERT INTO CM_PURPOSE_CATEGORY (NAME, DESCRIPTION, TENANT_ID) VALUES ('DEFAULT','For core functionalities of the product', '-1234');
605
606
607
608CREATE TABLE IF NOT EXISTS IDN_BASE_TABLE (
609            PRODUCT_NAME VARCHAR(20),
610            PRIMARY KEY (PRODUCT_NAME)
611)ENGINE INNODB;
612
613INSERT INTO IDN_BASE_TABLE values ('WSO2 Identity Server');
614
615CREATE TABLE IF NOT EXISTS IDN_OAUTH_CONSUMER_APPS (
616            ID INTEGER NOT NULL AUTO_INCREMENT,
617            CONSUMER_KEY VARCHAR(255),
618            CONSUMER_SECRET VARCHAR(2048),
619            USERNAME VARCHAR(255),
620            TENANT_ID INTEGER DEFAULT 0,
621            USER_DOMAIN VARCHAR(50),
622            APP_NAME VARCHAR(255),
623            OAUTH_VERSION VARCHAR(128),
624            CALLBACK_URL VARCHAR(1024),
625            GRANT_TYPES VARCHAR (1024),
626            PKCE_MANDATORY CHAR(1) DEFAULT '0',
627            PKCE_SUPPORT_PLAIN CHAR(1) DEFAULT '0',
628            APP_STATE VARCHAR (25) DEFAULT 'ACTIVE',
629            USER_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
630            APP_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
631            REFRESH_TOKEN_EXPIRE_TIME BIGINT DEFAULT 84600,
632            ID_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
633            CONSTRAINT CONSUMER_KEY_CONSTRAINT UNIQUE (CONSUMER_KEY),
634            PRIMARY KEY (ID)
635)ENGINE INNODB;
636
637CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE_VALIDATORS (
638	APP_ID INTEGER NOT NULL,
639	SCOPE_VALIDATOR VARCHAR (128) NOT NULL,
640	PRIMARY KEY (APP_ID,SCOPE_VALIDATOR),
641	FOREIGN KEY (APP_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
642)ENGINE INNODB;
643
644CREATE TABLE IF NOT EXISTS IDN_OAUTH1A_REQUEST_TOKEN (
645            REQUEST_TOKEN VARCHAR(255),
646            REQUEST_TOKEN_SECRET VARCHAR(512),
647            CONSUMER_KEY_ID INTEGER,
648            CALLBACK_URL VARCHAR(1024),
649            SCOPE VARCHAR(2048),
650            AUTHORIZED VARCHAR(128),
651            OAUTH_VERIFIER VARCHAR(512),
652            AUTHZ_USER VARCHAR(512),
653            TENANT_ID INTEGER DEFAULT -1,
654            PRIMARY KEY (REQUEST_TOKEN),
655            FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
656)ENGINE INNODB;
657
658CREATE TABLE IF NOT EXISTS IDN_OAUTH1A_ACCESS_TOKEN (
659            ACCESS_TOKEN VARCHAR(255),
660            ACCESS_TOKEN_SECRET VARCHAR(512),
661            CONSUMER_KEY_ID INTEGER,
662            SCOPE VARCHAR(2048),
663            AUTHZ_USER VARCHAR(512),
664            TENANT_ID INTEGER DEFAULT -1,
665            PRIMARY KEY (ACCESS_TOKEN),
666            FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
667)ENGINE INNODB;
668
669CREATE TABLE IF NOT EXISTS IDN_OAUTH2_ACCESS_TOKEN (
670            TOKEN_ID VARCHAR (255),
671            ACCESS_TOKEN VARCHAR(2048),
672            REFRESH_TOKEN VARCHAR(2048),
673            CONSUMER_KEY_ID INTEGER,
674            AUTHZ_USER VARCHAR (100),
675            TENANT_ID INTEGER,
676            USER_DOMAIN VARCHAR(50),
677            USER_TYPE VARCHAR (25),
678            GRANT_TYPE VARCHAR (50),
679            TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
680            REFRESH_TOKEN_TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
681            VALIDITY_PERIOD BIGINT,
682            REFRESH_TOKEN_VALIDITY_PERIOD BIGINT,
683            TOKEN_SCOPE_HASH VARCHAR(32),
684            TOKEN_STATE VARCHAR(25) DEFAULT 'ACTIVE',
685            TOKEN_STATE_ID VARCHAR (128) DEFAULT 'NONE',
686            SUBJECT_IDENTIFIER VARCHAR(255),
687            ACCESS_TOKEN_HASH VARCHAR(512),
688            REFRESH_TOKEN_HASH VARCHAR(512),
689            PRIMARY KEY (TOKEN_ID),
690            FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE,
691            CONSTRAINT CON_APP_KEY UNIQUE (CONSUMER_KEY_ID,AUTHZ_USER,TENANT_ID,USER_DOMAIN,USER_TYPE,TOKEN_SCOPE_HASH,
692                                           TOKEN_STATE,TOKEN_STATE_ID)
693)ENGINE INNODB;
694
695CREATE INDEX IDX_AT_CK_AU ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_STATE, USER_TYPE);
696
697CREATE INDEX IDX_TC ON IDN_OAUTH2_ACCESS_TOKEN(TIME_CREATED);
698
699CREATE INDEX IDX_ATH ON IDN_OAUTH2_ACCESS_TOKEN(ACCESS_TOKEN_HASH);
700
701CREATE INDEX IDX_AT_TI_UD ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, TENANT_ID, TOKEN_STATE, USER_DOMAIN);
702
703CREATE TABLE IF NOT EXISTS IDN_OAUTH2_ACCESS_TOKEN_AUDIT (
704            TOKEN_ID VARCHAR (255),
705            ACCESS_TOKEN VARCHAR(2048),
706            REFRESH_TOKEN VARCHAR(2048),
707            CONSUMER_KEY_ID INTEGER,
708            AUTHZ_USER VARCHAR (100),
709            TENANT_ID INTEGER,
710            USER_DOMAIN VARCHAR(50),
711            USER_TYPE VARCHAR (25),
712            GRANT_TYPE VARCHAR (50),
713            TIME_CREATED TIMESTAMP NULL,
714            REFRESH_TOKEN_TIME_CREATED TIMESTAMP NULL,
715            VALIDITY_PERIOD BIGINT,
716            REFRESH_TOKEN_VALIDITY_PERIOD BIGINT,
717            TOKEN_SCOPE_HASH VARCHAR(32),
718            TOKEN_STATE VARCHAR(25),
719            TOKEN_STATE_ID VARCHAR (128) ,
720            SUBJECT_IDENTIFIER VARCHAR(255),
721            ACCESS_TOKEN_HASH VARCHAR(512),
722            REFRESH_TOKEN_HASH VARCHAR(512),
723            INVALIDATED_TIME TIMESTAMP NULL
724);
725
726CREATE TABLE IF NOT EXISTS IDN_OAUTH2_AUTHORIZATION_CODE (
727            CODE_ID VARCHAR (255),
728            AUTHORIZATION_CODE VARCHAR(2048),
729            CONSUMER_KEY_ID INTEGER,
730            CALLBACK_URL VARCHAR(1024),
731            SCOPE VARCHAR(2048),
732            AUTHZ_USER VARCHAR (100),
733            TENANT_ID INTEGER,
734            USER_DOMAIN VARCHAR(50),
735            TIME_CREATED TIMESTAMP,
736            VALIDITY_PERIOD BIGINT,
737            STATE VARCHAR (25) DEFAULT 'ACTIVE',
738            TOKEN_ID VARCHAR(255),
739            SUBJECT_IDENTIFIER VARCHAR(255),
740            PKCE_CODE_CHALLENGE VARCHAR(255),
741            PKCE_CODE_CHALLENGE_METHOD VARCHAR(128),
742            AUTHORIZATION_CODE_HASH VARCHAR(512),
743            PRIMARY KEY (CODE_ID),
744            FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
745)ENGINE INNODB;
746
747CREATE INDEX IDX_AUTHORIZATION_CODE_HASH ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHORIZATION_CODE_HASH,CONSUMER_KEY_ID);
748
749CREATE INDEX IDX_AUTHORIZATION_CODE_AU_TI ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHZ_USER,TENANT_ID, USER_DOMAIN, STATE);
750
751
752CREATE TABLE IF NOT EXISTS IDN_OAUTH2_ACCESS_TOKEN_SCOPE (
753            TOKEN_ID VARCHAR (255),
754            TOKEN_SCOPE VARCHAR (60),
755            TENANT_ID INTEGER DEFAULT -1,
756            PRIMARY KEY (TOKEN_ID, TOKEN_SCOPE),
757            FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE
758)ENGINE INNODB;
759
760CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE (
761            SCOPE_ID INTEGER NOT NULL AUTO_INCREMENT,
762            NAME VARCHAR(255) NOT NULL,
763            DISPLAY_NAME VARCHAR(255) NOT NULL,
764            DESCRIPTION VARCHAR(512),
765            TENANT_ID INTEGER NOT NULL DEFAULT -1,
766            PRIMARY KEY (SCOPE_ID)
767)ENGINE INNODB;
768
769CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE_BINDING (
770            SCOPE_ID INTEGER NOT NULL,
771            SCOPE_BINDING VARCHAR(255),
772            FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE(SCOPE_ID) ON DELETE CASCADE
773)ENGINE INNODB;
774
775CREATE TABLE IF NOT EXISTS IDN_OAUTH2_RESOURCE_SCOPE (
776            RESOURCE_PATH VARCHAR(255) NOT NULL,
777            SCOPE_ID INTEGER NOT NULL,
778            TENANT_ID INTEGER DEFAULT -1,
779            PRIMARY KEY (RESOURCE_PATH),
780            FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE (SCOPE_ID) ON DELETE CASCADE
781)ENGINE INNODB;
782
783CREATE TABLE IF NOT EXISTS IDN_SCIM_GROUP (
784            ID INTEGER AUTO_INCREMENT,
785            TENANT_ID INTEGER NOT NULL,
786            ROLE_NAME VARCHAR(255) NOT NULL,
787            ATTR_NAME VARCHAR(1024) NOT NULL,
788            ATTR_VALUE VARCHAR(1024),
789            PRIMARY KEY (ID)
790)ENGINE INNODB;
791
792CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME);
793
794
795CREATE TABLE IF NOT EXISTS IDN_OPENID_REMEMBER_ME (
796            USER_NAME VARCHAR(255) NOT NULL,
797            TENANT_ID INTEGER DEFAULT 0,
798            COOKIE_VALUE VARCHAR(1024),
799            CREATED_TIME TIMESTAMP,
800            PRIMARY KEY (USER_NAME, TENANT_ID)
801)ENGINE INNODB;
802
803CREATE TABLE IF NOT EXISTS IDN_OPENID_USER_RPS (
804            USER_NAME VARCHAR(255) NOT NULL,
805            TENANT_ID INTEGER DEFAULT 0,
806            RP_URL VARCHAR(255) NOT NULL,
807            TRUSTED_ALWAYS VARCHAR(128) DEFAULT 'FALSE',
808            LAST_VISIT DATE NOT NULL,
809            VISIT_COUNT INTEGER DEFAULT 0,
810            DEFAULT_PROFILE_NAME VARCHAR(255) DEFAULT 'DEFAULT',
811            PRIMARY KEY (USER_NAME, TENANT_ID, RP_URL)
812)ENGINE INNODB;
813
814CREATE TABLE IF NOT EXISTS IDN_OPENID_ASSOCIATIONS (
815            HANDLE VARCHAR(255) NOT NULL,
816            ASSOC_TYPE VARCHAR(255) NOT NULL,
817            EXPIRE_IN TIMESTAMP NOT NULL,
818            MAC_KEY VARCHAR(255) NOT NULL,
819            ASSOC_STORE VARCHAR(128) DEFAULT 'SHARED',
820            TENANT_ID INTEGER DEFAULT -1,
821            PRIMARY KEY (HANDLE)
822)ENGINE INNODB;
823
824CREATE TABLE IF NOT EXISTS IDN_STS_STORE (
825            ID INTEGER AUTO_INCREMENT,
826            TOKEN_ID VARCHAR(255) NOT NULL,
827            TOKEN_CONTENT BLOB(1024) NOT NULL,
828            CREATE_DATE TIMESTAMP NOT NULL,
829            EXPIRE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
830            STATE INTEGER DEFAULT 0,
831            PRIMARY KEY (ID)
832)ENGINE INNODB;
833
834CREATE TABLE IF NOT EXISTS IDN_IDENTITY_USER_DATA (
835            TENANT_ID INTEGER DEFAULT -1234,
836            USER_NAME VARCHAR(255) NOT NULL,
837            DATA_KEY VARCHAR(255) NOT NULL,
838            DATA_VALUE VARCHAR(2048),
839            PRIMARY KEY (TENANT_ID, USER_NAME, DATA_KEY)
840)ENGINE INNODB;
841
842CREATE TABLE IF NOT EXISTS IDN_IDENTITY_META_DATA (
843            USER_NAME VARCHAR(255) NOT NULL,
844            TENANT_ID INTEGER DEFAULT -1234,
845            METADATA_TYPE VARCHAR(255) NOT NULL,
846            METADATA VARCHAR(255) NOT NULL,
847            VALID VARCHAR(255) NOT NULL,
848            PRIMARY KEY (TENANT_ID, USER_NAME, METADATA_TYPE,METADATA)
849)ENGINE INNODB;
850
851CREATE TABLE IF NOT EXISTS IDN_THRIFT_SESSION (
852            SESSION_ID VARCHAR(255) NOT NULL,
853            USER_NAME VARCHAR(255) NOT NULL,
854            CREATED_TIME VARCHAR(255) NOT NULL,
855            LAST_MODIFIED_TIME VARCHAR(255) NOT NULL,
856            TENANT_ID INTEGER DEFAULT -1,
857            PRIMARY KEY (SESSION_ID)
858)ENGINE INNODB;
859
860CREATE TABLE IF NOT EXISTS IDN_AUTH_SESSION_STORE (
861            SESSION_ID VARCHAR (100) NOT NULL,
862            SESSION_TYPE VARCHAR(100) NOT NULL,
863            OPERATION VARCHAR(10) NOT NULL,
864            SESSION_OBJECT BLOB,
865            TIME_CREATED BIGINT,
866            TENANT_ID INTEGER DEFAULT -1,
867            EXPIRY_TIME BIGINT,
868            PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
869)ENGINE INNODB;
870
871CREATE INDEX IDX_IDN_AUTH_SESSION_TIME ON IDN_AUTH_SESSION_STORE (TIME_CREATED);
872
873
874CREATE TABLE IF NOT EXISTS IDN_AUTH_TEMP_SESSION_STORE (
875            SESSION_ID VARCHAR (100) NOT NULL,
876            SESSION_TYPE VARCHAR(100) NOT NULL,
877            OPERATION VARCHAR(10) NOT NULL,
878            SESSION_OBJECT BLOB,
879            TIME_CREATED BIGINT,
880            TENANT_ID INTEGER DEFAULT -1,
881            EXPIRY_TIME BIGINT,
882            PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
883)ENGINE INNODB;
884
885CREATE INDEX IDX_IDN_AUTH_TMP_SESSION_TIME ON IDN_AUTH_TEMP_SESSION_STORE (TIME_CREATED);
886
887
888CREATE TABLE IF NOT EXISTS SP_APP (
889        ID INTEGER NOT NULL AUTO_INCREMENT,
890        TENANT_ID INTEGER NOT NULL,
891	    	APP_NAME VARCHAR (255) NOT NULL ,
892	    	USER_STORE VARCHAR (255) NOT NULL,
893        USERNAME VARCHAR (255) NOT NULL ,
894        DESCRIPTION VARCHAR (1024),
895	    	ROLE_CLAIM VARCHAR (512),
896        AUTH_TYPE VARCHAR (255) NOT NULL,
897	    	PROVISIONING_USERSTORE_DOMAIN VARCHAR (512),
898	    	IS_LOCAL_CLAIM_DIALECT CHAR(1) DEFAULT '1',
899	    	IS_SEND_LOCAL_SUBJECT_ID CHAR(1) DEFAULT '0',
900	    	IS_SEND_AUTH_LIST_OF_IDPS CHAR(1) DEFAULT '0',
901        IS_USE_TENANT_DOMAIN_SUBJECT CHAR(1) DEFAULT '1',
902        IS_USE_USER_DOMAIN_SUBJECT CHAR(1) DEFAULT '1',
903        ENABLE_AUTHORIZATION CHAR(1) DEFAULT '0',
904	    	SUBJECT_CLAIM_URI VARCHAR (512),
905	    	IS_SAAS_APP CHAR(1) DEFAULT '0',
906	    	IS_DUMB_MODE CHAR(1) DEFAULT '0',
907        PRIMARY KEY (ID)
908)ENGINE INNODB;
909
910ALTER TABLE SP_APP ADD CONSTRAINT APPLICATION_NAME_CONSTRAINT UNIQUE(APP_NAME, TENANT_ID);
911
912CREATE TABLE IF NOT EXISTS SP_METADATA (
913            ID INTEGER AUTO_INCREMENT,
914            SP_ID INTEGER,
915            NAME VARCHAR(255) NOT NULL,
916            VALUE VARCHAR(255) NOT NULL,
917            DISPLAY_NAME VARCHAR(255),
918            TENANT_ID INTEGER DEFAULT -1,
919            PRIMARY KEY (ID),
920            CONSTRAINT SP_METADATA_CONSTRAINT UNIQUE (SP_ID, NAME),
921            FOREIGN KEY (SP_ID) REFERENCES SP_APP(ID) ON DELETE CASCADE
922)ENGINE INNODB;
923
924CREATE TABLE IF NOT EXISTS SP_INBOUND_AUTH (
925            ID INTEGER NOT NULL AUTO_INCREMENT,
926            TENANT_ID INTEGER NOT NULL,
927            INBOUND_AUTH_KEY VARCHAR (255),
928            INBOUND_AUTH_TYPE VARCHAR (255) NOT NULL,
929            INBOUND_CONFIG_TYPE VARCHAR (255) NOT NULL,
930            PROP_NAME VARCHAR (255),
931            PROP_VALUE VARCHAR (1024) ,
932            APP_ID INTEGER NOT NULL,
933            PRIMARY KEY (ID)
934)ENGINE INNODB;
935
936ALTER TABLE SP_INBOUND_AUTH ADD CONSTRAINT APPLICATION_ID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
937
938CREATE TABLE IF NOT EXISTS SP_AUTH_STEP (
939            ID INTEGER NOT NULL AUTO_INCREMENT,
940            TENANT_ID INTEGER NOT NULL,
941            STEP_ORDER INTEGER DEFAULT 1,
942            APP_ID INTEGER NOT NULL ,
943            IS_SUBJECT_STEP CHAR(1) DEFAULT '0',
944            IS_ATTRIBUTE_STEP CHAR(1) DEFAULT '0',
945            PRIMARY KEY (ID)
946)ENGINE INNODB;
947
948ALTER TABLE SP_AUTH_STEP ADD CONSTRAINT APPLICATION_ID_CONSTRAINT_STEP FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
949
950CREATE TABLE IF NOT EXISTS SP_FEDERATED_IDP (
951            ID INTEGER NOT NULL,
952            TENANT_ID INTEGER NOT NULL,
953            AUTHENTICATOR_ID INTEGER NOT NULL,
954            PRIMARY KEY (ID, AUTHENTICATOR_ID)
955)ENGINE INNODB;
956
957ALTER TABLE SP_FEDERATED_IDP ADD CONSTRAINT STEP_ID_CONSTRAINT FOREIGN KEY (ID) REFERENCES SP_AUTH_STEP (ID) ON DELETE CASCADE;
958
959CREATE TABLE IF NOT EXISTS SP_CLAIM_DIALECT (
960	   	ID INTEGER NOT NULL AUTO_INCREMENT,
961	   	TENANT_ID INTEGER NOT NULL,
962	   	SP_DIALECT VARCHAR (512) NOT NULL,
963	   	APP_ID INTEGER NOT NULL,
964	   	PRIMARY KEY (ID));
965
966ALTER TABLE SP_CLAIM_DIALECT ADD CONSTRAINT DIALECTID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
967
968CREATE TABLE IF NOT EXISTS SP_CLAIM_MAPPING (
969            ID INTEGER NOT NULL AUTO_INCREMENT,
970            TENANT_ID INTEGER NOT NULL,
971            IDP_CLAIM VARCHAR (512) NOT NULL ,
972            SP_CLAIM VARCHAR (512) NOT NULL ,
973            APP_ID INTEGER NOT NULL,
974            IS_REQUESTED VARCHAR(128) DEFAULT '0',
975	    IS_MANDATORY VARCHAR(128) DEFAULT '0',
976            DEFAULT_VALUE VARCHAR(255),
977            PRIMARY KEY (ID)
978)ENGINE INNODB;
979
980ALTER TABLE SP_CLAIM_MAPPING ADD CONSTRAINT CLAIMID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
981
982CREATE TABLE IF NOT EXISTS SP_ROLE_MAPPING (
983            ID INTEGER NOT NULL AUTO_INCREMENT,
984            TENANT_ID INTEGER NOT NULL,
985            IDP_ROLE VARCHAR (255) NOT NULL ,
986            SP_ROLE VARCHAR (255) NOT NULL ,
987            APP_ID INTEGER NOT NULL,
988            PRIMARY KEY (ID)
989)ENGINE INNODB;
990
991ALTER TABLE SP_ROLE_MAPPING ADD CONSTRAINT ROLEID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
992
993CREATE TABLE IF NOT EXISTS SP_REQ_PATH_AUTHENTICATOR (
994            ID INTEGER NOT NULL AUTO_INCREMENT,
995            TENANT_ID INTEGER NOT NULL,
996            AUTHENTICATOR_NAME VARCHAR (255) NOT NULL ,
997            APP_ID INTEGER NOT NULL,
998            PRIMARY KEY (ID)
999)ENGINE INNODB;
1000
1001ALTER TABLE SP_REQ_PATH_AUTHENTICATOR ADD CONSTRAINT REQ_AUTH_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
1002
1003CREATE TABLE IF NOT EXISTS SP_PROVISIONING_CONNECTOR (
1004            ID INTEGER NOT NULL AUTO_INCREMENT,
1005            TENANT_ID INTEGER NOT NULL,
1006            IDP_NAME VARCHAR (255) NOT NULL ,
1007            CONNECTOR_NAME VARCHAR (255) NOT NULL ,
1008            APP_ID INTEGER NOT NULL,
1009            IS_JIT_ENABLED CHAR(1) NOT NULL DEFAULT '0',
1010            BLOCKING CHAR(1) NOT NULL DEFAULT '0',
1011            RULE_ENABLED CHAR(1) NOT NULL DEFAULT '0',
1012            PRIMARY KEY (ID)
1013)ENGINE INNODB;
1014
1015ALTER TABLE SP_PROVISIONING_CONNECTOR ADD CONSTRAINT PRO_CONNECTOR_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
1016
1017CREATE TABLE SP_AUTH_SCRIPT (
1018  ID         INTEGER AUTO_INCREMENT NOT NULL,
1019  TENANT_ID  INTEGER                NOT NULL,
1020  APP_ID     INTEGER                NOT NULL,
1021  TYPE       VARCHAR(255)           NOT NULL,
1022  CONTENT    BLOB    DEFAULT NULL,
1023  IS_ENABLED CHAR(1) NOT NULL DEFAULT '0',
1024  PRIMARY KEY (ID));
1025
1026CREATE TABLE IF NOT EXISTS SP_TEMPLATE (
1027  ID         INTEGER AUTO_INCREMENT NOT NULL,
1028  TENANT_ID  INTEGER                NOT NULL,
1029  NAME VARCHAR(255) NOT NULL,
1030  DESCRIPTION VARCHAR(1023),
1031  CONTENT BLOB DEFAULT NULL,
1032  PRIMARY KEY (ID),
1033  CONSTRAINT SP_TEMPLATE_CONSTRAINT UNIQUE (TENANT_ID, NAME));
1034CREATE INDEX IDX_SP_TEMPLATE ON SP_TEMPLATE (TENANT_ID, NAME);
1035
1036CREATE TABLE IF NOT EXISTS IDN_AUTH_WAIT_STATUS (
1037  ID              INTEGER AUTO_INCREMENT NOT NULL,
1038  TENANT_ID       INTEGER                NOT NULL,
1039  LONG_WAIT_KEY   VARCHAR(255)           NOT NULL,
1040  WAIT_STATUS     CHAR(1) NOT NULL DEFAULT '1',
1041  TIME_CREATED    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
1042  EXPIRE_TIME     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
1043  PRIMARY KEY (ID),
1044  CONSTRAINT IDN_AUTH_WAIT_STATUS_KEY UNIQUE (LONG_WAIT_KEY));
1045
1046CREATE TABLE IF NOT EXISTS IDP (
1047			ID INTEGER AUTO_INCREMENT,
1048			TENANT_ID INTEGER,
1049			NAME VARCHAR(254) NOT NULL,
1050			IS_ENABLED CHAR(1) NOT NULL DEFAULT '1',
1051			IS_PRIMARY CHAR(1) NOT NULL DEFAULT '0',
1052			HOME_REALM_ID VARCHAR(254),
1053			IMAGE MEDIUMBLOB,
1054			CERTIFICATE BLOB,
1055			ALIAS VARCHAR(254),
1056			INBOUND_PROV_ENABLED CHAR (1) NOT NULL DEFAULT '0',
1057			INBOUND_PROV_USER_STORE_ID VARCHAR(254),
1058 			USER_CLAIM_URI VARCHAR(254),
1059 			ROLE_CLAIM_URI VARCHAR(254),
1060  			DESCRIPTION VARCHAR (1024),
1061 			DEFAULT_AUTHENTICATOR_NAME VARCHAR(254),
1062 			DEFAULT_PRO_CONNECTOR_NAME VARCHAR(254),
1063 			PROVISIONING_ROLE VARCHAR(128),
1064 			IS_FEDERATION_HUB CHAR(1) NOT NULL DEFAULT '0',
1065 			IS_LOCAL_CLAIM_DIALECT CHAR(1) NOT NULL DEFAULT '0',
1066            DISPLAY_NAME VARCHAR(255),
1067			PRIMARY KEY (ID),
1068			UNIQUE (TENANT_ID, NAME)
1069)ENGINE INNODB;
1070
1071CREATE TABLE IF NOT EXISTS IDP_ROLE (
1072			ID INTEGER AUTO_INCREMENT,
1073			IDP_ID INTEGER,
1074			TENANT_ID INTEGER,
1075			ROLE VARCHAR(254),
1076			PRIMARY KEY (ID),
1077			UNIQUE (IDP_ID, ROLE),
1078			FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
1079)ENGINE INNODB;
1080
1081CREATE TABLE IF NOT EXISTS IDP_ROLE_MAPPING (
1082			ID INTEGER AUTO_INCREMENT,
1083			IDP_ROLE_ID INTEGER,
1084			TENANT_ID INTEGER,
1085			USER_STORE_ID VARCHAR (253),
1086			LOCAL_ROLE VARCHAR(253),
1087			PRIMARY KEY (ID),
1088			UNIQUE (IDP_ROLE_ID, TENANT_ID, USER_STORE_ID, LOCAL_ROLE),
1089			FOREIGN KEY (IDP_ROLE_ID) REFERENCES IDP_ROLE(ID) ON DELETE CASCADE
1090)ENGINE INNODB;
1091
1092CREATE TABLE IF NOT EXISTS IDP_CLAIM (
1093			ID INTEGER AUTO_INCREMENT,
1094			IDP_ID INTEGER,
1095			TENANT_ID INTEGER,
1096			CLAIM VARCHAR(254),
1097			PRIMARY KEY (ID),
1098			UNIQUE (IDP_ID, CLAIM),
1099			FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
1100)ENGINE INNODB;
1101
1102CREATE TABLE IF NOT EXISTS IDP_CLAIM_MAPPING (
1103            ID INTEGER AUTO_INCREMENT,
1104            IDP_CLAIM_ID INTEGER,
1105            TENANT_ID INTEGER,
1106            LOCAL_CLAIM VARCHAR(253),
1107            DEFAULT_VALUE VARCHAR(255),
1108            IS_REQUESTED VARCHAR(128) DEFAULT '0',
1109            PRIMARY KEY (ID),
1110            UNIQUE (IDP_CLAIM_ID, TENANT_ID, LOCAL_CLAIM),
1111            FOREIGN KEY (IDP_CLAIM_ID) REFERENCES IDP_CLAIM(ID) ON DELETE CASCADE
1112)ENGINE INNODB;
1113
1114CREATE TABLE IF NOT EXISTS IDP_AUTHENTICATOR (
1115            ID INTEGER AUTO_INCREMENT,
1116            TENANT_ID INTEGER,
1117            IDP_ID INTEGER,
1118            NAME VARCHAR(255) NOT NULL,
1119            IS_ENABLED CHAR (1) DEFAULT '1',
1120            DISPLAY_NAME VARCHAR(255),
1121            PRIMARY KEY (ID),
1122            UNIQUE (TENANT_ID, IDP_ID, NAME),
1123            FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
1124)ENGINE INNODB;
1125
1126CREATE TABLE IF NOT EXISTS IDP_METADATA (
1127            ID INTEGER AUTO_INCREMENT,
1128            IDP_ID INTEGER,
1129            NAME VARCHAR(255) NOT NULL,
1130            VALUE VARCHAR(255) NOT NULL,
1131            DISPLAY_NAME VARCHAR(255),
1132            TENANT_ID INTEGER DEFAULT -1,
1133            PRIMARY KEY (ID),
1134            CONSTRAINT IDP_METADATA_CONSTRAINT UNIQUE (IDP_ID, NAME),
1135            FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
1136)ENGINE INNODB;
1137
1138CREATE TABLE IF NOT EXISTS IDP_AUTHENTICATOR_PROPERTY (
1139            ID INTEGER AUTO_INCREMENT,
1140            TENANT_ID INTEGER,
1141            AUTHENTICATOR_ID INTEGER,
1142            PROPERTY_KEY VARCHAR(255) NOT NULL,
1143            PROPERTY_VALUE VARCHAR(2047),
1144            IS_SECRET CHAR (1) DEFAULT '0',
1145            PRIMARY KEY (ID),
1146            UNIQUE (TENANT_ID, AUTHENTICATOR_ID, PROPERTY_KEY),
1147            FOREIGN KEY (AUTHENTICATOR_ID) REFERENCES IDP_AUTHENTICATOR(ID) ON DELETE CASCADE
1148)ENGINE INNODB;
1149
1150CREATE TABLE IF NOT EXISTS IDP_PROVISIONING_CONFIG (
1151            ID INTEGER AUTO_INCREMENT,
1152            TENANT_ID INTEGER,
1153            IDP_ID INTEGER,
1154            PROVISIONING_CONNECTOR_TYPE VARCHAR(255) NOT NULL,
1155            IS_ENABLED CHAR (1) DEFAULT '0',
1156            IS_BLOCKING CHAR (1) DEFAULT '0',
1157            IS_RULES_ENABLED CHAR (1) DEFAULT '0',
1158            PRIMARY KEY (ID),
1159            UNIQUE (TENANT_ID, IDP_ID, PROVISIONING_CONNECTOR_TYPE),
1160            FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
1161)ENGINE INNODB;
1162
1163CREATE TABLE IF NOT EXISTS IDP_PROV_CONFIG_PROPERTY (
1164            ID INTEGER AUTO_INCREMENT,
1165            TENANT_ID INTEGER,
1166            PROVISIONING_CONFIG_ID INTEGER,
1167            PROPERTY_KEY VARCHAR(255) NOT NULL,
1168            PROPERTY_VALUE VARCHAR(2048),
1169            PROPERTY_BLOB_VALUE BLOB,
1170            PROPERTY_TYPE CHAR(32) NOT NULL,
1171            IS_SECRET CHAR (1) DEFAULT '0',
1172            PRIMARY KEY (ID),
1173            UNIQUE (TENANT_ID, PROVISIONING_CONFIG_ID, PROPERTY_KEY),
1174            FOREIGN KEY (PROVISIONING_CONFIG_ID) REFERENCES IDP_PROVISIONING_CONFIG(ID) ON DELETE CASCADE
1175)ENGINE INNODB;
1176
1177CREATE TABLE IF NOT EXISTS IDP_PROVISIONING_ENTITY (
1178            ID INTEGER AUTO_INCREMENT,
1179            PROVISIONING_CONFIG_ID INTEGER,
1180            ENTITY_TYPE VARCHAR(255) NOT NULL,
1181            ENTITY_LOCAL_USERSTORE VARCHAR(255) NOT NULL,
1182            ENTITY_NAME VARCHAR(255) NOT NULL,
1183            ENTITY_VALUE VARCHAR(255),
1184            TENANT_ID INTEGER,
1185            ENTITY_LOCAL_ID VARCHAR(255),
1186            PRIMARY KEY (ID),
1187            UNIQUE (ENTITY_TYPE, TENANT_ID, ENTITY_LOCAL_USERSTORE, ENTITY_NAME, PROVISIONING_CONFIG_ID),
1188            UNIQUE (PROVISIONING_CONFIG_ID, ENTITY_TYPE, ENTITY_VALUE),
1189            FOREIGN KEY (PROVISIONING_CONFIG_ID) REFERENCES IDP_PROVISIONING_CONFIG(ID) ON DELETE CASCADE
1190)ENGINE INNODB;
1191
1192CREATE TABLE IF NOT EXISTS IDP_LOCAL_CLAIM (
1193            ID INTEGER AUTO_INCREMENT,
1194            TENANT_ID INTEGER,
1195            IDP_ID INTEGER,
1196            CLAIM_URI VARCHAR(255) NOT NULL,
1197            DEFAULT_VALUE VARCHAR(255),
1198            IS_REQUESTED VARCHAR(128) DEFAULT '0',
1199            PRIMARY KEY (ID),
1200            UNIQUE (TENANT_ID, IDP_ID, CLAIM_URI),
1201            FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
1202)ENGINE INNODB;
1203
1204CREATE TABLE IF NOT EXISTS IDN_ASSOCIATED_ID (
1205            ID INTEGER AUTO_INCREMENT,
1206            IDP_USER_ID VARCHAR(255) NOT NULL,
1207            TENANT_ID INTEGER DEFAULT -1234,
1208            IDP_ID INTEGER NOT NULL,
1209            DOMAIN_NAME VARCHAR(255) NOT NULL,
1210            USER_NAME VARCHAR(255) NOT NULL,
1211            PRIMARY KEY (ID),
1212            UNIQUE(IDP_USER_ID, TENANT_ID, IDP_ID),
1213            FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
1214)ENGINE INNODB;
1215
1216CREATE TABLE IF NOT EXISTS IDN_USER_ACCOUNT_ASSOCIATION (
1217            ASSOCIATION_KEY VARCHAR(255) NOT NULL,
1218            TENANT_ID INTEGER,
1219            DOMAIN_NAME VARCHAR(255) NOT NULL,
1220            USER_NAME VARCHAR(255) NOT NULL,
1221            PRIMARY KEY (TENANT_ID, DOMAIN_NAME, USER_NAME)
1222)ENGINE INNODB;
1223
1224CREATE TABLE IF NOT EXISTS FIDO_DEVICE_STORE (
1225            TENANT_ID INTEGER,
1226            DOMAIN_NAME VARCHAR(255) NOT NULL,
1227            USER_NAME VARCHAR(45) NOT NULL,
1228            TIME_REGISTERED TIMESTAMP,
1229            KEY_HANDLE VARCHAR(200) NOT NULL,
1230            DEVICE_DATA VARCHAR(2048) NOT NULL,
1231            PRIMARY KEY (TENANT_ID, DOMAIN_NAME, USER_NAME, KEY_HANDLE)
1232        )ENGINE INNODB;
1233              
1234CREATE TABLE IF NOT EXISTS WF_REQUEST (
1235    UUID VARCHAR (45),
1236    CREATED_BY VARCHAR (255),
1237    TENANT_ID INTEGER DEFAULT -1,
1238    OPERATION_TYPE VARCHAR (50),
1239    CREATED_AT TIMESTAMP,
1240    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
1241    STATUS VARCHAR (30),
1242    REQUEST BLOB,
1243    PRIMARY KEY (UUID)
1244)ENGINE INNODB;
1245
1246CREATE TABLE IF NOT EXISTS WF_BPS_PROFILE (
1247    PROFILE_NAME VARCHAR(45),
1248    HOST_URL_MANAGER VARCHAR(255),
1249    HOST_URL_WORKER VARCHAR(255),
1250    USERNAME VARCHAR(45),
1251    PASSWORD VARCHAR(1023),
1252    CALLBACK_HOST VARCHAR (45),
1253    CALLBACK_USERNAME VARCHAR (45),
1254    CALLBACK_PASSWORD VARCHAR (255),
1255    TENANT_ID INTEGER DEFAULT -1,
1256    PRIMARY KEY (PROFILE_NAME, TENANT_ID)
1257)ENGINE INNODB;
1258
1259CREATE TABLE IF NOT EXISTS WF_WORKFLOW(
1260    ID VARCHAR (45),
1261    WF_NAME VARCHAR (45),
1262    DESCRIPTION VARCHAR (255),
1263    TEMPLATE_ID VARCHAR (45),
1264    IMPL_ID VARCHAR (45),
1265    TENANT_ID INTEGER DEFAULT -1,
1266    PRIMARY KEY (ID)
1267)ENGINE INNODB;
1268
1269CREATE TABLE IF NOT EXISTS WF_WORKFLOW_ASSOCIATION(
1270    ID INTEGER NOT NULL AUTO_INCREMENT,
1271    ASSOC_NAME VARCHAR (45),
1272    EVENT_ID VARCHAR(45),
1273    ASSOC_CONDITION VARCHAR (2000),
1274    WORKFLOW_ID VARCHAR (45),
1275    IS_ENABLED CHAR (1) DEFAULT '1',
1276    TENANT_ID INTEGER DEFAULT -1,
1277    PRIMARY KEY(ID),
1278    FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE
1279)ENGINE INNODB;
1280
1281CREATE TABLE IF NOT EXISTS WF_WORKFLOW_CONFIG_PARAM(
1282    WORKFLOW_ID VARCHAR (45),
1283    PARAM_NAME VARCHAR (45),
1284    PARAM_VALUE VARCHAR (1000),
1285    PARAM_QNAME VARCHAR (45),
1286    PARAM_HOLDER VARCHAR (45),
1287    TENANT_ID INTEGER DEFAULT -1,
1288    PRIMARY KEY (WORKFLOW_ID, PARAM_NAME, PARAM_QNAME, PARAM_HOLDER),
1289    FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE
1290)ENGINE INNODB;
1291
1292CREATE TABLE IF NOT EXISTS WF_REQUEST_ENTITY_RELATIONSHIP(
1293  REQUEST_ID VARCHAR (45),
1294  ENTITY_NAME VARCHAR (255),
1295  ENTITY_TYPE VARCHAR (50),
1296  TENANT_ID INTEGER DEFAULT -1,
1297  PRIMARY KEY(REQUEST_ID, ENTITY_NAME, ENTITY_TYPE, TENANT_ID),
1298  FOREIGN KEY (REQUEST_ID) REFERENCES WF_REQUEST(UUID)ON DELETE CASCADE
1299)ENGINE INNODB;
1300
1301CREATE TABLE IF NOT EXISTS WF_WORKFLOW_REQUEST_RELATION(
1302  RELATIONSHIP_ID VARCHAR (45),
1303  WORKFLOW_ID VARCHAR (45),
1304  REQUEST_ID VARCHAR (45),
1305  UPDATED_AT TIMESTAMP,
1306  STATUS VARCHAR (30),
1307  TENANT_ID INTEGER DEFAULT -1,
1308  PRIMARY KEY (RELATIONSHIP_ID),
1309  FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE,
1310  FOREIGN KEY (REQUEST_ID) REFERENCES WF_REQUEST(UUID)ON DELETE CASCADE
1311)ENGINE INNODB;
1312
1313CREATE TABLE IF NOT EXISTS IDN_RECOVERY_DATA (
1314  USER_NAME VARCHAR(255) NOT NULL,
1315  USER_DOMAIN VARCHAR(127) NOT NULL,
1316  TENANT_ID INTEGER DEFAULT -1,
1317  CODE VARCHAR(255) NOT NULL,
1318  SCENARIO VARCHAR(255) NOT NULL,
1319  STEP VARCHAR(127) NOT NULL,
1320  TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
1321  REMAINING_SETS VARCHAR(2500) DEFAULT NULL,
1322  PRIMARY KEY(USER_NAME, USER_DOMAIN, TENANT_ID, SCENARIO,STEP),
1323  UNIQUE(CODE)
1324)ENGINE INNODB;
1325
1326CREATE TABLE IF NOT EXISTS IDN_PASSWORD_HISTORY_DATA (
1327  ID INTEGER NOT NULL AUTO_INCREMENT,
1328  USER_NAME   VARCHAR(255) NOT NULL,
1329  USER_DOMAIN VARCHAR(127) NOT NULL,
1330  TENANT_ID   INTEGER DEFAULT -1,
1331  SALT_VALUE  VARCHAR(255),
1332  HASH        VARCHAR(255) NOT NULL,
1333  TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
1334  PRIMARY KEY(ID),
1335  UNIQUE (USER_NAME,USER_DOMAIN,TENANT_ID,SALT_VALUE,HASH)
1336)ENGINE INNODB;
1337
1338CREATE TABLE IF NOT EXISTS IDN_CLAIM_DIALECT (
1339  ID INTEGER NOT NULL AUTO_INCREMENT,
1340  DIALECT_URI VARCHAR (255) NOT NULL,
1341  TENANT_ID INTEGER NOT NULL,
1342  PRIMARY KEY (ID),
1343  CONSTRAINT DIALECT_URI_CONSTRAINT UNIQUE (DIALECT_URI, TENANT_ID)
1344)ENGINE INNODB;
1345
1346CREATE TABLE IF NOT EXISTS IDN_CLAIM (
1347  ID INTEGER NOT NULL AUTO_INCREMENT,
1348  DIALECT_ID INTEGER,
1349  CLAIM_URI VARCHAR (255) NOT NULL,
1350  TENANT_ID INTEGER NOT NULL,
1351  PRIMARY KEY (ID),
1352  FOREIGN KEY (DIALECT_ID) REFERENCES IDN_CLAIM_DIALECT(ID) ON DELETE CASCADE,
1353  CONSTRAINT CLAIM_URI_CONSTRAINT UNIQUE (DIALECT_ID, CLAIM_URI, TENANT_ID)
1354)ENGINE INNODB;
1355
1356CREATE TABLE IF NOT EXISTS IDN_CLAIM_MAPPED_ATTRIBUTE (
1357  ID INTEGER NOT NULL AUTO_INCREMENT,
1358  LOCAL_CLAIM_ID INTEGER,
1359  USER_STORE_DOMAIN_NAME VARCHAR (255) NOT NULL,
1360  ATTRIBUTE_NAME VARCHAR (255) NOT NULL,
1361  TENANT_ID INTEGER NOT NULL,
1362  PRIMARY KEY (ID),
1363  FOREIGN KEY (LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
1364  CONSTRAINT USER_STORE_DOMAIN_CONSTRAINT UNIQUE (LOCAL_CLAIM_ID, USER_STORE_DOMAIN_NAME, TENANT_ID)
1365)ENGINE INNODB;
1366
1367CREATE TABLE IF NOT EXISTS IDN_CLAIM_PROPERTY (
1368  ID INTEGER NOT NULL AUTO_INCREMENT,
1369  LOCAL_CLAIM_ID INTEGER,
1370  PROPERTY_NAME VARCHAR (255) NOT NULL,
1371  PROPERTY_VALUE VARCHAR (255) NOT NULL,
1372  TENANT_ID INTEGER NOT NULL,
1373  PRIMARY KEY (ID),
1374  FOREIGN KEY (LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
1375  CONSTRAINT PROPERTY_NAME_CONSTRAINT UNIQUE (LOCAL_CLAIM_ID, PROPERTY_NAME, TENANT_ID)
1376)ENGINE INNODB;
1377
1378CREATE TABLE IF NOT EXISTS IDN_CLAIM_MAPPING (
1379  ID INTEGER NOT NULL AUTO_INCREMENT,
1380  EXT_CLAIM_ID INTEGER NOT NULL,
1381  MAPPED_LOCAL_CLAIM_ID INTEGER NOT NULL,
1382  TENANT_ID INTEGER NOT NULL,
1383  PRIMARY KEY (ID),
1384  FOREIGN KEY (EXT_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
1385  FOREIGN KEY (MAPPED_LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
1386  CONSTRAINT EXT_TO_LOC_MAPPING_CONSTRN UNIQUE (EXT_CLAIM_ID, TENANT_ID)
1387)ENGINE INNODB;
1388
1389CREATE TABLE IF NOT EXISTS  IDN_SAML2_ASSERTION_STORE (
1390  ID INTEGER NOT NULL AUTO_INCREMENT,
1391  SAML2_ID  VARCHAR(255) ,
1392  SAML2_ISSUER  VARCHAR(255) ,
1393  SAML2_SUBJECT  VARCHAR(255) ,
1394  SAML2_SESSION_INDEX  VARCHAR(255) ,
1395  SAML2_AUTHN_CONTEXT_CLASS_REF  VARCHAR(255) ,
1396  SAML2_ASSERTION  VARCHAR(4096) ,
1397  PRIMARY KEY (ID)
1398)ENGINE INNODB;
1399
1400CREATE TABLE IDN_SAML2_ARTIFACT_STORE (
1401  ID INT(11) NOT NULL AUTO_INCREMENT,
1402  SOURCE_ID VARCHAR(255) NOT NULL,
1403  MESSAGE_HANDLER VARCHAR(255) NOT NULL,
1404  AUTHN_REQ_DTO BLOB NOT NULL,
1405  SESSION_ID VARCHAR(255) NOT NULL,
1406  EXP_TIMESTAMP TIMESTAMP NOT NULL,
1407  INIT_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
1408  ASSERTION_ID VARCHAR(255),
1409  PRIMARY KEY (`ID`)
1410)ENGINE INNODB;
1411
1412CREATE TABLE IF NOT EXISTS IDN_OIDC_JTI (
1413  JWT_ID VARCHAR(255) NOT NULL,
1414  EXP_TIME TIMESTAMP NOT NULL ,
1415  TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
1416  PRIMARY KEY (JWT_ID)
1417)ENGINE INNODB;
1418
1419CREATE TABLE IF NOT EXISTS  IDN_OIDC_PROPERTY (
1420  ID INTEGER NOT NULL AUTO_INCREMENT,
1421  TENANT_ID  INTEGER,
1422  CONSUMER_KEY  VARCHAR(255) ,
1423  PROPERTY_KEY  VARCHAR(255) NOT NULL,
1424  PROPERTY_VALUE  VARCHAR(2047) ,
1425  PRIMARY KEY (ID),
1426  FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY) ON DELETE CASCADE
1427)ENGINE INNODB;
1428
1429CREATE TABLE IF NOT EXISTS IDN_OIDC_REQ_OBJECT_REFERENCE (
1430  ID INTEGER NOT NULL AUTO_INCREMENT,
1431  CONSUMER_KEY_ID INTEGER ,
1432  CODE_ID VARCHAR(255) ,
1433  TOKEN_ID VARCHAR(255) ,
1434  SESSION_DATA_KEY VARCHAR(255),
1435  PRIMARY KEY (ID),
1436  FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE,
1437  FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE,
1438  FOREIGN KEY (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE(CODE_ID) ON DELETE CASCADE
1439)ENGINE INNODB;
1440
1441CREATE TABLE IF NOT EXISTS IDN_OIDC_REQ_OBJECT_CLAIMS (
1442  ID INTEGER NOT NULL AUTO_INCREMENT,
1443  REQ_OBJECT_ID INTEGER,
1444  CLAIM_ATTRIBUTE VARCHAR(255) ,
1445  ESSENTIAL CHAR(1) NOT NULL DEFAULT '0' ,
1446  VALUE VARCHAR(255) ,
1447  IS_USERINFO CHAR(1) NOT NULL DEFAULT '0',
1448  PRIMARY KEY (ID),
1449  FOREIGN KEY (REQ_OBJECT_ID) REFERENCES IDN_OIDC_REQ_OBJECT_REFERENCE (ID) ON DELETE CASCADE
1450)ENGINE INNODB;
1451
1452CREATE TABLE IF NOT EXISTS IDN_OIDC_REQ_OBJ_CLAIM_VALUES (
1453  ID INTEGER NOT NULL AUTO_INCREMENT,
1454  REQ_OBJECT_CLAIMS_ID INTEGER ,
1455  CLAIM_VALUES VARCHAR(255) ,
1456  PRIMARY KEY (ID),
1457  FOREIGN KEY (REQ_OBJECT_CLAIMS_ID) REFERENCES  IDN_OIDC_REQ_OBJECT_CLAIMS(ID) ON DELETE CASCADE
1458)ENGINE INNODB;
1459
1460CREATE TABLE IF NOT EXISTS IDN_CERTIFICATE (
1461             ID INTEGER NOT NULL AUTO_INCREMENT,
1462             NAME VARCHAR(100),
1463             CERTIFICATE_IN_PEM BLOB,
1464             TENANT_ID INTEGER DEFAULT 0,
1465             PRIMARY KEY(ID),
1466             CONSTRAINT CERTIFICATE_UNIQUE_KEY UNIQUE (NAME, TENANT_ID)
1467)ENGINE INNODB;
1468
1469CREATE TABLE IF NOT EXISTS IDN_OIDC_SCOPE (
1470            ID INTEGER NOT NULL AUTO_INCREMENT,
1471            NAME VARCHAR(255) NOT NULL,
1472            TENANT_ID INTEGER DEFAULT -1,
1473            PRIMARY KEY (ID)
1474)ENGINE INNODB;
1475
1476CREATE TABLE IF NOT EXISTS IDN_OIDC_SCOPE_CLAIM_MAPPING (
1477            ID INTEGER NOT NULL AUTO_INCREMENT,
1478            SCOPE_ID INTEGER,
1479            EXTERNAL_CLAIM_ID INTEGER,
1480            PRIMARY KEY (ID),
1481            FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OIDC_SCOPE(ID) ON DELETE CASCADE,
1482            FOREIGN KEY (EXTERNAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE
1483)ENGINE INNODB;
1484
1485CREATE INDEX IDX_AT_SI_ECI ON IDN_OIDC_SCOPE_CLAIM_MAPPING(SCOPE_ID, EXTERNAL_CLAIM_ID);
1486
1487create table ODE_SCHEMA_VERSION (VERSION integer);
1488insert into ODE_SCHEMA_VERSION values (6);
1489-- Apache ODE - SimpleScheduler Database Schema
1490--
1491-- MySQL scripts by Maciej Szefler.
1492--
1493--
1494DROP TABLE IF EXISTS ODE_JOB;
1495
1496CREATE TABLE ODE_JOB (
1497  jobid CHAR(64)  NOT NULL DEFAULT '',
1498  ts BIGINT  NOT NULL DEFAULT 0,
1499  nodeid char(64)  NULL,
1500  scheduled int  NOT NULL DEFAULT 0,
1501  transacted int  NOT NULL DEFAULT 0,
1502
1503  instanceId BIGINT,
1504  mexId varchar(255),
1505  processId varchar(255),
1506  type varchar(255),
1507  channel varchar(255),
1508  correlatorId varchar(255),
1509  correlationKeySet varchar(255),
1510  retryCount int,
1511  inMem int,
1512  detailsExt blob(4096),
1513
1514  PRIMARY KEY(jobid),
1515  INDEX IDX_ODE_JOB_TS(ts),
1516  INDEX IDX_ODE_JOB_NODEID(nodeid)
1517)
1518ENGINE=innodb;
1519
1520COMMIT;
1521
1522CREATE TABLE TASK_ATTACHMENT (ATTACHMENT_ID BIGINT NOT NULL, MESSAGE_EXCHANGE_ID VARCHAR(255), PRIMARY KEY (ATTACHMENT_ID)) ENGINE=innodb;
1523CREATE TABLE ODE_ACTIVITY_RECOVERY (ID BIGINT NOT NULL, ACTIONS VARCHAR(255), ACTIVITY_ID BIGINT, CHANNEL VARCHAR(255), DATE_TIME DATETIME, DETAILS TEXT, INSTANCE_ID BIGINT, REASON VARCHAR(255), RETRIES INTEGER, PRIMARY KEY (ID)) ENGINE=innodb;
1524CREATE TABLE ODE_CORRELATION_SET (CORRELATION_SET_ID BIGINT NOT NULL, CORRELATION_KEY VARCHAR(255), NAME VARCHAR(255), SCOPE_ID BIGINT, PRIMARY KEY (CORRELATION_SET_ID)) ENGINE=innodb;
1525CREATE TABLE ODE_CORRELATOR (CORRELATOR_ID BIGINT NOT NULL, CORRELATOR_KEY VARCHAR(255), PROC_ID BIGINT, PRIMARY KEY (CORRELATOR_ID)) ENGINE=innodb;
1526CREATE TABLE ODE_CORSET_PROP (ID BIGINT NOT NULL, CORRSET_ID BIGINT, PROP_KEY VARCHAR(255), PROP_VALUE VARCHAR(255), PRIMARY KEY (ID)) ENGINE=innodb;
1527CREATE TABLE ODE_EVENT (EVENT_ID BIGINT NOT NULL, DETAIL VARCHAR(255), DATA BLOB, SCOPE_ID BIGINT, TSTAMP DATETIME, TYPE VARCHAR(255), INSTANCE_ID BIGINT, PROCESS_ID BIGINT, PRIMARY KEY (EVENT_ID)) ENGINE=innodb;
1528CREATE TABLE ODE_FAULT (FAULT_ID BIGINT NOT NULL, ACTIVITY_ID INTEGER, DATA TEXT, MESSAGE VARCHAR(4000), LINE_NUMBER INTEGER, NAME VARCHAR(255), PRIMARY KEY (FAULT_ID)) ENGINE=innodb;
1529CREATE TABLE ODE_MESSAGE (MESSAGE_ID BIGINT NOT NULL, DATA TEXT, HEADER TEXT, TYPE VARCHAR(255), MESSAGE_EXCHANGE_ID VARCHAR(255), PRIMARY KEY (MESSAGE_ID)) ENGINE=innodb;
1530CREATE TABLE ODE_MESSAGE_EXCHANGE (MESSAGE_EXCHANGE_ID VARCHAR(255) NOT NULL, CALLEE VARCHAR(255), CHANNEL VARCHAR(255), CORRELATION_ID VARCHAR(255), CORRELATION_KEYS VARCHAR(255), CORRELATION_STATUS VARCHAR(255), CREATE_TIME DATETIME, DIRECTION INTEGER, EPR TEXT, FAULT VARCHAR(255), FAULT_EXPLANATION VARCHAR(255), OPERATION VARCHAR(255), PARTNER_LINK_MODEL_ID INTEGER, PATTERN VARCHAR(255), PIPED_ID VARCHAR(255), PORT_TYPE VARCHAR(255), PROPAGATE_TRANS BIT, STATUS VARCHAR(255), SUBSCRIBER_COUNT INTEGER, CORR_ID BIGINT, PARTNER_LINK_ID BIGINT, PROCESS_ID BIGINT, PROCESS_INSTANCE_ID BIGINT, REQUEST_MESSAGE_ID BIGINT, RESPONSE_MESSAGE_ID BIGINT, PRIMARY KEY (MESSAGE_EXCHANGE_ID)) ENGINE=innodb;
1531CREATE TABLE ODE_MESSAGE_ROUTE (MESSAGE_ROUTE_ID BIGINT NOT NULL, CORRELATION_KEY VARCHAR(255), GROUP_ID VARCHAR(255), ROUTE_INDEX INTEGER, PROCESS_INSTANCE_ID INTEGER, ROUTE_POLICY VARCHAR(16), CORR_ID BIGINT, PRIMARY KEY (MESSAGE_ROUTE_ID)) ENGINE=innodb;
1532CREATE TABLE ODE_MEX_PROP (ID BIGINT NOT NULL, MEX_ID VARCHAR(255), PROP_KEY VARCHAR(255), PROP_VALUE VARCHAR(2000), PRIMARY KEY (ID)) ENGINE=innodb;
1533CREATE TABLE ODE_PARTNER_LINK (PARTNER_LINK_ID BIGINT NOT NULL, MY_EPR TEXT, MY_ROLE_NAME VARCHAR(255), MY_ROLE_SERVICE_NAME VARCHAR(255), MY_SESSION_ID VARCHAR(255), PARTNER_EPR TEXT, PARTNER_LINK_MODEL_ID INTEGER, PARTNER_LINK_NAME VARCHAR(255), PARTNER_ROLE_NAME VARCHAR(255), PARTNER_SESSION_ID VARCHAR(255), SCOPE_ID BIGINT, PRIMARY KEY (PARTNER_LINK_ID)) ENGINE=innodb;
1534CREATE TABLE ODE_PROCESS (ID BIGINT NOT NULL, GUID VARCHAR(255), PROCESS_ID VARCHAR(255), PROCESS_TYPE VARCHAR(255), VERSION BIGINT, PRIMARY KEY (ID)) ENGINE=innodb;
1535CREATE TABLE ODE_PROCESS_INSTANCE (ID BIGINT NOT NULL, DATE_CREATED DATETIME, EXECUTION_STATE BLOB, FAULT_ID BIGINT, LAST_ACTIVE_TIME DATETIME, LAST_RECOVERY_DATE DATETIME, PREVIOUS_STATE SMALLINT, SEQUENCE BIGINT, INSTANCE_STATE SMALLINT, INSTANTIATING_CORRELATOR_ID BIGINT, PROCESS_ID BIGINT, ROOT_SCOPE_ID BIGINT, PRIMARY KEY (ID)) ENGINE=innodb;
1536CREATE TABLE ODE_SCOPE (SCOPE_ID BIGINT NOT NULL, MODEL_ID INTEGER, SCOPE_NAME VARCHAR(255), SCOPE_STATE VARCHAR(255), PROCESS_INSTANCE_ID BIGINT, PARENT_SCOPE_ID BIGINT, PRIMARY KEY (SCOPE_ID)) ENGINE=innodb;
1537CREATE TABLE ODE_XML_DATA (XML_DATA_ID BIGINT NOT NULL, DATA TEXT, IS_SIMPLE_TYPE BIT, NAME VARCHAR(255), SCOPE_ID BIGINT, PRIMARY KEY (XML_DATA_ID)) ENGINE=innodb;
1538CREATE TABLE ODE_XML_DATA_PROP (ID BIGINT NOT NULL, XML_DATA_ID BIGINT, PROP_KEY VARCHAR(255), PROP_VALUE VARCHAR(255), PRIMARY KEY (ID)) ENGINE=innodb;
1539CREATE TABLE OPENJPA_SEQUENCE_TABLE (ID TINYINT NOT NULL, SEQUENCE_VALUE BIGINT, PRIMARY KEY (ID)) ENGINE=innodb;
1540CREATE TABLE STORE_DU (NAME VARCHAR(255) NOT NULL, DEPLOYDT DATETIME, DEPLOYER VARCHAR(255), DIR VARCHAR(255), PRIMARY KEY (NAME)) ENGINE=innodb;
1541CREATE TABLE STORE_PROCESS (PID VARCHAR(255) NOT NULL, STATE VARCHAR(255), TYPE VARCHAR(255), VERSION BIGINT, DU VARCHAR(255), PRIMARY KEY (PID)) ENGINE=innodb;
1542CREATE TABLE STORE_PROCESS_PROP (id BIGINT NOT NULL, PROP_KEY VARCHAR(255), PROP_VAL VARCHAR(255), PRIMARY KEY (id)) ENGINE=innodb;
1543CREATE TABLE STORE_PROC_TO_PROP (PROCESSCONFDAOIMPL_PID VARCHAR(255), ELEMENT_ID BIGINT) ENGINE=innodb;
1544CREATE TABLE STORE_VERSIONS (id BIGINT NOT NULL, VERSION BIGINT, PRIMARY KEY (id)) ENGINE=innodb;
1545CREATE INDEX I_D_TASK_ATTACMENT ON TASK_ATTACHMENT (MESSAGE_EXCHANGE_ID);
1546CREATE INDEX I_D_CTVRY_INSTANCE ON ODE_ACTIVITY_RECOVERY (INSTANCE_ID);
1547CREATE INDEX I_D_CR_ST_SCOPE ON ODE_CORRELATION_SET (SCOPE_ID);
1548CREATE INDEX I_D_CRLTR_PROCESS ON ODE_CORRELATOR (PROC_ID);
1549CREATE INDEX I_D_CRPRP_CORRSET ON ODE_CORSET_PROP (CORRSET_ID);
1550CREATE INDEX I_OD_VENT_INSTANCE ON ODE_EVENT (INSTANCE_ID);
1551CREATE INDEX I_OD_VENT_PROCESS ON ODE_EVENT (PROCESS_ID);
1552CREATE INDEX I_OD_MSSG_MESSAGEEXCHANGE ON ODE_MESSAGE (MESSAGE_EXCHANGE_ID);
1553CREATE INDEX I_D_MSHNG_CORRELATOR ON ODE_MESSAGE_EXCHANGE (CORR_ID);
1554CREATE INDEX I_D_MSHNG_PARTNERLINK ON ODE_MESSAGE_EXCHANGE (PARTNER_LINK_ID);
1555CREATE INDEX I_D_MSHNG_PROCESS ON ODE_MESSAGE_EXCHANGE (PROCESS_ID);
1556CREATE INDEX I_D_MSHNG_PROCESSINST ON ODE_MESSAGE_EXCHANGE (PROCESS_INSTANCE_ID);
1557CREATE INDEX I_D_MSHNG_REQUEST ON ODE_MESSAGE_EXCHANGE (REQUEST_MESSAGE_ID);
1558CREATE INDEX I_D_MSHNG_RESPONSE ON ODE_MESSAGE_EXCHANGE (RESPONSE_MESSAGE_ID);
1559CREATE INDEX I_D_MS_RT_CORRELATOR ON ODE_MESSAGE_ROUTE (CORR_ID);
1560CREATE INDEX I_D_MS_RT_PROCESSINST ON ODE_MESSAGE_ROUTE (PROCESS_INSTANCE_ID);
1561CREATE INDEX I_D_MXPRP_MEX ON ODE_MEX_PROP (MEX_ID);
1562CREATE INDEX I_D_PRLNK_SCOPE ON ODE_PARTNER_LINK (SCOPE_ID);
1563CREATE INDEX I_D_PRTNC_FAULT ON ODE_PROCESS_INSTANCE (FAULT_ID);
1564CREATE INDEX I_D_PRTNC_INSTANTIATINGCORRELATOR ON ODE_PROCESS_INSTANCE (INSTANTIATING_CORRELATOR_ID);
1565CREATE INDEX I_D_PRTNC_PROCESS ON ODE_PROCESS_INSTANCE (PROCESS_ID);
1566CREATE INDEX I_D_PRTNC_ROOTSCOPE ON ODE_PROCESS_INSTANCE (ROOT_SCOPE_ID);
1567CREATE INDEX I_OD_SCOP_PARENTSCOPE ON ODE_SCOPE (PARENT_SCOPE_ID);
1568CREATE INDEX I_OD_SCOP_PROCESSINSTANCE ON ODE_SCOPE (PROCESS_INSTANCE_ID);
1569CREATE INDEX I_D_XM_DT_SCOPE ON ODE_XML_DATA (SCOPE_ID);
1570CREATE INDEX I_D_XMPRP_XMLDATA ON ODE_XML_DATA_PROP (XML_DATA_ID);
1571CREATE INDEX I_STR_CSS_DU ON STORE_PROCESS (DU);
1572CREATE INDEX I_STR_PRP_ELEMENT ON STORE_PROC_TO_PROP (ELEMENT_ID);
1573CREATE INDEX I_STR_PRP_PROCESSCONFDAOIMPL_PID ON STORE_PROC_TO_PROP (PROCESSCONFDAOIMPL_PID);
1574
1575
1576
1577--
1578-- Human Task Related SQL Scripts
1579--
1580
1581
1582CREATE TABLE HT_DEADLINE (id BIGINT NOT NULL, DEADLINE_DATE DATETIME NOT NULL, DEADLINE_NAME VARCHAR(255) NOT NULL, STATUS_TOBE_ACHIEVED VARCHAR(255) NOT NULL, TASK_ID BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
1583CREATE TABLE HT_DEPLOYMENT_UNIT (id BIGINT NOT NULL, CHECKSUM VARCHAR(255) NOT NULL, DEPLOYED_ON DATETIME, DEPLOY_DIR VARCHAR(255) NOT NULL, NAME VARCHAR(255) NOT NULL, PACKAGE_NAME VARCHAR(255) NOT NULL, STATUS VARCHAR(255) NOT NULL, TENANT_ID BIGINT NOT NULL, VERSION BIGINT NOT NULL, PRIMARY KEY (id)) ENGINE = innodb;
1584CREATE TABLE HT_EVENT (id BIGINT NOT NULL, EVENT_DETAILS VARCHAR(255), NEW_STATE VARCHAR(255), OLD_STATE VARCHAR(255), EVENT_TIMESTAMP DATETIME NOT NULL, EVENT_TYPE VARCHAR(255) NOT NULL, EVENT_USER VARCHAR(255) NOT NULL, TASK_ID BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
1585CREATE TABLE HT_GENERIC_HUMAN_ROLE (GHR_ID BIGINT NOT NULL, GHR_TYPE VARCHAR(255), TASK_ID BIGINT, PRIMARY KEY (GHR_ID)) ENGINE = innodb;
1586CREATE TABLE HT_HUMANROLE_ORGENTITY (HUMANROLE_ID BIGINT, ORGENTITY_ID BIGINT) ENGINE = innodb;
1587CREATE TABLE HT_JOB (id BIGINT NOT NULL, JOB_DETAILS VARCHAR(4000), JOB_NAME VARCHAR(255), NODEID VARCHAR(255), SCHEDULED VARCHAR(1) NOT NULL, TASKID BIGINT NOT NULL, JOB_TIME BIGINT NOT NULL, TRANSACTED VARCHAR(1) NOT NULL, JOB_TYPE VARCHAR(255) NOT NULL, PRIMARY KEY (id)) ENGINE = innodb;
1588CREATE TABLE HT_MESSAGE (MESSAGE_ID BIGINT NOT NULL, MESSAGE_DATA LONGTEXT, MESSAGE_HEADER LONGTEXT, MESSAGE_TYPE VARCHAR(255), MESSAGE_NAME VARCHAR(512), TASK_ID BIGINT, PRIMARY KEY (MESSAGE_ID)) ENGINE = innodb;
1589CREATE TABLE HT_ORG_ENTITY (ORG_ENTITY_ID BIGINT NOT NULL, ORG_ENTITY_NAME VARCHAR(255), ORG_ENTITY_TYPE VARCHAR(255), PRIMARY KEY (ORG_ENTITY_ID)) ENGINE = innodb;
1590CREATE TABLE HT_PRESENTATION_ELEMENT (id BIGINT NOT NULL, PE_CONTENT VARCHAR(2000), XML_LANG VARCHAR(255), PE_TYPE VARCHAR(31), CONTENT_TYPE VARCHAR(255), TASK_ID BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
1591CREATE TABLE HT_PRESENTATION_PARAM (id BIGINT NOT NULL, PARAM_NAME VARCHAR(255), PARAM_TYPE VARCHAR(255), PARAM_VALUE VARCHAR(2000), TASK_ID BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
1592CREATE TABLE HT_TASK (id BIGINT NOT NULL, ACTIVATION_TIME DATETIME, COMPLETE_BY_TIME DATETIME, CREATED_ON DATETIME, ESCALATED VARCHAR(1), EXPIRATION_TIME DATETIME, TASK_NAME VARCHAR(255) NOT NULL, PACKAGE_NAME VARCHAR(255) NOT NULL, PRIORITY INTEGER NOT NULL, SKIPABLE VARCHAR(1), START_BY_TIME DATETIME, STATUS VARCHAR(255) NOT NULL, STATUS_BEFORE_SUSPENSION VARCHAR(255), TASK_DEF_NAME VARCHAR(255) NOT NULL, TASK_VERSION BIGINT NOT NULL, TENANT_ID INTEGER NOT NULL, TASK_TYPE VARCHAR(255) NOT NULL, UPDATED_ON DATETIME, FAILURE_MESSAGE BIGINT, INPUT_MESSAGE BIGINT, OUTPUT_MESSAGE BIGINT, PARENTTASK_ID BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
1593CREATE TABLE HT_TASK_ATTACHMENT (id BIGINT NOT NULL, ACCESS_TYPE VARCHAR(255), ATTACHED_AT DATETIME, CONTENT_TYPE VARCHAR(255), ATTACHMENT_NAME VARCHAR(255), ATTACHMENT_VALUE VARCHAR(255), TASK_ID BIGINT, ATTACHED_BY BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
1594CREATE TABLE HT_TASK_COMMENT (id BIGINT NOT NULL, COMMENT_TEXT VARCHAR(4000), COMMENTED_BY VARCHAR(100), COMMENTED_ON DATETIME, MODIFIED_BY VARCHAR(100), MODIFIED_ON DATETIME, TASK_ID BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
1595CREATE TABLE HT_VERSIONS (id BIGINT NOT NULL, TASK_VERSION BIGINT NOT NULL, PRIMARY KEY (id)) ENGINE = innodb;
1596CREATE INDEX I_HT_DDLN_TASK ON HT_DEADLINE (TASK_ID);
1597CREATE INDEX I_HT_VENT_TASK ON HT_EVENT (TASK_ID);
1598CREATE INDEX I_HT_G_RL_TASK ON HT_GENERIC_HUMAN_ROLE (TASK_ID);
1599CREATE INDEX I_HT_HTTY_ELEMENT ON HT_HUMANROLE_ORGENTITY (ORGENTITY_ID);
1600CREATE INDEX I_HT_HTTY_HUMANROLE_ID ON HT_HUMANROLE_ORGENTITY (HUMANROLE_ID);
1601CREATE INDEX I_HT_MSSG_TASK ON HT_MESSAGE (TASK_ID);
1602CREATE INDEX I_HT_PMNT_DTYPE ON HT_PRESENTATION_ELEMENT (PE_TYPE);
1603CREATE INDEX I_HT_PMNT_TASK ON HT_PRESENTATION_ELEMENT (TASK_ID);
1604CREATE INDEX I_HT_PPRM_TASK ON HT_PRESENTATION_PARAM (TASK_ID);
1605CREATE INDEX I_HT_TASK_FAILUREMESSAGE ON HT_TASK (FAILURE_MESSAGE);
1606CREATE INDEX I_HT_TASK_INPUTMESSAGE ON HT_TASK (INPUT_MESSAGE);
1607CREATE INDEX I_HT_TASK_OUTPUTMESSAGE ON HT_TASK (OUTPUT_MESSAGE);
1608CREATE INDEX I_HT_TASK_PARENTTASK ON HT_TASK (PARENTTASK_ID);
1609CREATE INDEX I_HT_TMNT_ATTACHEDBY ON HT_TASK_ATTACHMENT (ATTACHED_BY);
1610CREATE INDEX I_HT_TMNT_TASK ON HT_TASK_ATTACHMENT (TASK_ID);
1611CREATE INDEX I_HT_TMNT_TASK1 ON HT_TASK_COMMENT (TASK_ID);
1612
1613--
1614-- Attachment Management Related SQL Scripts
1615--
1616CREATE TABLE ATTACHMENT (
1617	id BIGINT NOT NULL AUTO_INCREMENT,
1618	CREATED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
1619	ATTACHMENT_NAME VARCHAR(255) NOT NULL,
1620	CREATED_BY VARCHAR(255) NOT NULL,
1621	CONTENT_TYPE VARCHAR(255) NOT NULL,
1622	ATTACHMENT_URL VARCHAR(2048) NOT NULL,
1623	ATTACHMENT_CONTENT BLOB,
1624	PRIMARY KEY (id));
1625
1626-- CREATE INDEX I_ATTACHMENT_URL ON ATTACHMENT (ATTACHMENT_URL);
1627
1628
1629--
1630-- B4P Related SQL Scripts
1631--
1632CREATE TABLE HT_COORDINATION_DATA (MESSAGE_ID VARCHAR(255) NOT NULL, PROCESS_INSTANCE_ID VARCHAR(255), PROTOCOL_HANDlER_URL VARCHAR(255) NOT NULL, TASK_ID VARCHAR(255), PRIMARY KEY (MESSAGE_ID)) ENGINE = innodb;
1633
1634
1635
1636
1637
1638
1639
1640CREATE TABLE IF NOT EXISTS METRIC_GAUGE (
1641    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
1642    SOURCE VARCHAR(255) NOT NULL,
1643    TIMESTAMP BIGINT NOT NULL,
1644    NAME VARCHAR(255) NOT NULL,
1645    VALUE VARCHAR(100) NOT NULL
1646);
1647
1648CREATE TABLE IF NOT EXISTS METRIC_COUNTER (
1649    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
1650    SOURCE VARCHAR(255) NOT NULL,
1651    TIMESTAMP BIGINT NOT NULL,
1652    NAME VARCHAR(255) NOT NULL,
1653    COUNT BIGINT NOT NULL
1654);
1655
1656CREATE TABLE IF NOT EXISTS METRIC_METER (
1657    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
1658    SOURCE VARCHAR(255) NOT NULL,
1659    TIMESTAMP BIGINT NOT NULL,
1660    NAME VARCHAR(255) NOT NULL,
1661    COUNT BIGINT NOT NULL,
1662    MEAN_RATE DECIMAL NOT NULL,
1663    M1_RATE DECIMAL NOT NULL,
1664    M5_RATE DECIMAL NOT NULL,
1665    M15_RATE DECIMAL NOT NULL,
1666    RATE_UNIT VARCHAR(50) NOT NULL
1667);
1668
1669CREATE TABLE IF NOT EXISTS METRIC_HISTOGRAM (
1670    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
1671    SOURCE VARCHAR(255) NOT NULL,
1672    TIMESTAMP BIGINT NOT NULL,
1673    NAME VARCHAR(255) NOT NULL,
1674    COUNT BIGINT NOT NULL,
1675    MAX DECIMAL NOT NULL,
1676    MEAN DECIMAL NOT NULL,
1677    MIN DECIMAL NOT NULL,
1678    STDDEV DECIMAL NOT NULL,
1679    P50 DECIMAL NOT NULL,
1680    P75 DECIMAL NOT NULL,
1681    P95 DECIMAL NOT NULL,
1682    P98 DECIMAL NOT NULL,
1683    P99 DECIMAL NOT NULL,
1684    P999 DECIMAL NOT NULL
1685);
1686
1687CREATE TABLE IF NOT EXISTS METRIC_TIMER (
1688    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
1689    SOURCE VARCHAR(255) NOT NULL,
1690    TIMESTAMP BIGINT NOT NULL,
1691    NAME VARCHAR(255) NOT NULL,
1692    COUNT BIGINT NOT NULL,
1693    MAX DECIMAL NOT NULL,
1694    MEAN DECIMAL NOT NULL,
1695    MIN DECIMAL NOT NULL,
1696    STDDEV DECIMAL NOT NULL,
1697    P50 DECIMAL NOT NULL,
1698    P75 DECIMAL NOT NULL,
1699    P95 DECIMAL NOT NULL,
1700    P98 DECIMAL NOT NULL,
1701    P99 DECIMAL NOT NULL,
1702    P999 DECIMAL NOT NULL,
1703    MEAN_RATE DECIMAL NOT NULL,
1704    M1_RATE DECIMAL NOT NULL,
1705    M5_RATE DECIMAL NOT NULL,
1706    M15_RATE DECIMAL NOT NULL,
1707    RATE_UNIT VARCHAR(50) NOT NULL,
1708    DURATION_UNIT VARCHAR(50) NOT NULL
1709);
1710
1711CREATE INDEX IDX_TIMESTAMP_GAUGE ON METRIC_GAUGE (TIMESTAMP);
1712CREATE INDEX IDX_TIMESTAMP_COUNTER ON METRIC_COUNTER (TIMESTAMP);
1713CREATE INDEX IDX_TIMESTAMP_METER ON METRIC_METER (TIMESTAMP);
1714CREATE INDEX IDX_TIMESTAMP_HISTOGRAM ON METRIC_HISTOGRAM (TIMESTAMP);
1715CREATE INDEX IDX_TIMESTAMP_TIMER ON METRIC_TIMER (TIMESTAMP);