· 6 years ago · Jun 14, 2019, 02:14 PM
1DROP DATABASE IF EXISTS `de-local`;
2
3CREATE DATABASE `de-local`;
4
5USE `de-local`;
6
7-- username and password details for DEV, UAT and STG. Substitue the values before applying the scripts
8-- DEV --> username - dcsuser, password - Mcddcsdev01acceptancepolicies
9-- UAT --> username - dcsuseruat, password - Mcddcsuat01
10-- STG --> username - dcsuser, password - Mcddcsstg01
11-- DROP USER '{username}'@'%';
12
13-- FLUSH PRIVILEGES;
14
15-- CREATE USER '{username}'@'%' IDENTIFIED BY '{password}';
16
17-- password to be provided in the GRANT query
18-- DEV --> password - *103AC72883E754EDF7BB4E8F4904FE08FBDA47C9
19-- UAT --> password - *AEC769E6A6C8BF5A2C8849D774302597BB4B08FA
20-- STG --> password - *FD6A9DB2F6EDD2CA0111837C808E9A5641754CB4
21-- GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO '{username}'@'%' IDENTIFIED BY PASSWORD '{password}' WITH GRANT OPTION;
22
23CREATE TABLE `baseProfile` (
24 `dcsId` varchar(20) NOT NULL,
25 `username` varchar(254) DEFAULT NULL,
26 `firstName` varchar(128) DEFAULT NULL,
27 `middleName` varchar(60) DEFAULT NULL,
28 `lastName` varchar(128) DEFAULT NULL,
29 `shortName` varchar(45) DEFAULT NULL,
30 `displayName` varchar(45) DEFAULT NULL,
31 `suffix` varchar(45) DEFAULT NULL,
32 `prefix` varchar(45) DEFAULT NULL,
33 `languageCode` varchar(2) DEFAULT NULL,
34 `marketCode` varchar(2) DEFAULT NULL,
35 `ethnicity` varchar(10) DEFAULT NULL,
36 `activeInd` varchar(2) DEFAULT NULL,
37 `gender` varchar(1) DEFAULT NULL,
38 `maritalStatus` varchar(1) DEFAULT NULL,
39 `birthMonth` varchar(2) DEFAULT NULL,
40 `birthYear` varchar(4) DEFAULT NULL,
41 `birthDay` varchar(2) DEFAULT NULL,
42 `ageRange` varchar(10) DEFAULT NULL,
43 `isFrozen` boolean DEFAULT FALSE,
44 `frozenStatusChangedAt` datetime NULL DEFAULT NULL,
45 `createdBy` varchar(45) DEFAULT NULL,
46 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
47 `lastModifiedBy` varchar(45) DEFAULT NULL,
48 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
49 PRIMARY KEY (`dcsId`),
50 KEY `dcsId` (`dcsId`)
51) ENGINE=InnoDB DEFAULT CHARSET=utf8;
52
53
54/* DCS-788 GDPR Freeze start */
55-- ALTER TABLE `baseProfile` DROP `isFrozen`, DROP `frozenStatusChangedAt`;
56-- ALTER TABLE `baseProfile` ADD COLUMN(
57-- `isFrozen` boolean DEFAULT FALSE,
58-- `frozenStatusChangedAt` datetime DEFAULT NULL
59-- );
60/* DCS-788 GDPR Freeze end */
61
62CREATE TABLE `acceptancePolicies` (
63 `dcsId` varchar(20) NOT NULL,
64 `sourceId` varchar(50) DEFAULT NULL,
65 `type` varchar(4) NOT NULL,
66 `name` varchar(100) NOT NULL,
67 `version` varchar(10) DEFAULT NULL,
68 `channelId` varchar(1) DEFAULT NULL,
69 `deviceId` varchar(200) DEFAULT NULL,
70 `acceptanceInd` varchar(1) DEFAULT 'N',
71 `acceptanceDate` datetime NULL DEFAULT NULL,
72 `createdBy` varchar(45) DEFAULT NULL,
73 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
74 `lastModifiedBy` varchar(45) DEFAULT NULL,
75 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
76 PRIMARY KEY (`dcsId`,`name`),
77 CONSTRAINT `acceptancePolicies_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
78) ENGINE=InnoDB DEFAULT CHARSET=utf8;
79
80CREATE TABLE `accessPolicy` (
81 `dcsId` varchar(20) NOT NULL,
82 `sourceId` varchar(50) DEFAULT NULL,
83 `type` varchar(4) NOT NULL,
84 `name` varchar(100) NOT NULL,
85 `version` varchar(10) DEFAULT NULL,
86 `channelId` varchar(1) DEFAULT NULL,
87 `deviceId` varchar(200) DEFAULT NULL,
88 `acceptanceInd` varchar(1) DEFAULT 'N',
89 `acceptanceDate` datetime NULL DEFAULT NULL,
90 `createdBy` varchar(45) DEFAULT NULL,
91 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
92 `lastModifiedBy` varchar(45) DEFAULT NULL,
93 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
94 PRIMARY KEY (`dcsId`,`name`),
95 CONSTRAINT `accessPolicy_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
96) ENGINE=InnoDB DEFAULT CHARSET=utf8;
97
98CREATE TABLE `address` (
99 `dcsId` varchar(20) NOT NULL,
100 `activeInd` varchar(1) DEFAULT 'N',
101 `primaryInd` varchar(1) DEFAULT 'N',
102 `allowPromotions` varchar(1) DEFAULT 'N',
103 `addressType` varchar(45) NOT NULL,
104 `addressLocale` varchar(10) NOT NULL,
105 `area` varchar(100) DEFAULT NULL,
106 `building` varchar(100) DEFAULT NULL,
107 `city` varchar(45) DEFAULT NULL,
108 `company` varchar(100) DEFAULT NULL,
109 `department` varchar(100) DEFAULT NULL,
110 `district` varchar(100) DEFAULT NULL,
111 `garden` varchar(100) DEFAULT NULL,
112 `state` varchar(100) DEFAULT NULL,
113 `addressLine1` varchar(100) DEFAULT NULL,
114 `addressLine2` varchar(100) DEFAULT NULL,
115 `addressLine3` varchar(100) DEFAULT NULL,
116 `addressLine4` varchar(100) DEFAULT NULL,
117 `streetType` varchar(100) DEFAULT NULL,
118 `suburb` varchar(100) DEFAULT NULL,
119 `zipCode` varchar(32) DEFAULT NULL,
120 `block` varchar(50) DEFAULT NULL,
121 `level` varchar(50) DEFAULT NULL,
122 `unit` varchar(50) DEFAULT NULL,
123 `houseNumber` varchar(50) DEFAULT NULL,
124 `addressPreferenceTypeID` varchar(10) DEFAULT NULL,
125 `streetLonNumber` varchar(10) DEFAULT NULL,
126 `remark` varchar(100) DEFAULT NULL,
127 `latitude` varchar(10) DEFAULT NULL,
128 `longitude` varchar(10) DEFAULT NULL,
129 `isRedZone` varchar(1) DEFAULT 'N',
130 `isAmberZone` varchar(1) DEFAULT 'N',
131 `landmark` varchar(100) DEFAULT NULL,
132 `county` varchar(100) DEFAULT NULL,
133 `shortZipCode` varchar(10) DEFAULT NULL,
134 `country` varchar(2) DEFAULT NULL,
135 `createdBy` varchar(45) DEFAULT NULL,
136 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
137 `lastModifiedBy` varchar(45) DEFAULT NULL,
138 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
139 PRIMARY KEY (`dcsId`,`addressType`,`addressLocale`),
140 CONSTRAINT `address_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
141) ENGINE=InnoDB DEFAULT CHARSET=utf8;
142
143CREATE TABLE `agentRemark` (
144 `dcsId` varchar(20) NOT NULL,
145 `agentName` varchar(100) DEFAULT NULL,
146 `remark` varchar(200) DEFAULT NULL,
147 `createdBy` varchar(45) DEFAULT NULL,
148 `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
149 `lastModifiedBy` varchar(45) DEFAULT NULL,
150 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
151 PRIMARY KEY (`dcsId`,`createdAt`),
152 CONSTRAINT `agentRemark_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
153) ENGINE=InnoDB DEFAULT CHARSET=utf8;
154
155CREATE TABLE `audit` (
156 `dcsId` varchar(20) NOT NULL,
157 `registrationChannel` varchar(50) DEFAULT NULL,
158 `activatedAt` datetime NULL DEFAULT NULL,
159 `verifiedAt` datetime NULL DEFAULT NULL,
160 `deActivatedAt` datetime NULL DEFAULT NULL,
161 `deletedAt` datetime NULL DEFAULT NULL,
162 `socialProvider` varchar(50) DEFAULT NULL,
163 `accountDeactivatedReason` varchar(200) DEFAULT NULL,
164 `lastPasswordChangeAt` datetime NULL DEFAULT NULL,
165 `lastAuthenticatedAt` datetime NULL DEFAULT NULL,
166 `lastLoggedInAt` datetime NULL DEFAULT NULL,
167 `createdBy` varchar(45) DEFAULT NULL,
168 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
169 `lastModifiedBy` varchar(45) DEFAULT NULL,
170 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
171 `updatePrimaryMobileInd` varchar(1) DEFAULT '0',
172 `updatePrimaryEmailInd` varchar(1) DEFAULT '0',
173 PRIMARY KEY (`dcsId`),
174 CONSTRAINT `audit_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
175) ENGINE=InnoDB DEFAULT CHARSET=utf8;
176
177CREATE TABLE `consolidated` (
178 `dcsId` varchar(20) NOT NULL,
179 `emailLoginUsername` varchar(254) DEFAULT NULL,
180 `consolidatedData` longtext DEFAULT NULL,
181 `phoneLoginUsername` varchar(20) DEFAULT NULL,
182 `socialIdentifier` varchar(100) DEFAULT NULL,
183 `socialProvider` varchar(50) DEFAULT NULL,
184 PRIMARY KEY (`dcsId`),
185 KEY `fk_dcsId` (`dcsId`),
186 KEY `emailLoginUsername` (`emailLoginUsername`),
187 KEY `phoneLoginUsername` (`phoneLoginUsername`),
188 KEY `socialIdentifier` (`socialIdentifier`),
189 UNIQUE KEY `emailLoginUsername_unique` (`emailLoginUsername`),
190 UNIQUE KEY `phoneLoginUsername_unique` (`phoneLoginUsername`),
191 CONSTRAINT `consolidated_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
192) ENGINE=InnoDB DEFAULT CHARSET=utf8;
193
194CREATE TABLE `dcsIdGenerator` (
195 `dcsId` bigint(20) NOT NULL AUTO_INCREMENT,
196 PRIMARY KEY (`dcsId`),
197 KEY `dcsId` (`dcsId`)
198) ENGINE=InnoDB AUTO_INCREMENT=11840000000000001 DEFAULT CHARSET=utf8;
199
200CREATE TABLE `devices` (
201 `dcsId` varchar(20) NOT NULL,
202 `deviceId` varchar(250) DEFAULT NULL,
203 `deviceIdType` varchar(20) DEFAULT NULL,
204 `token` varchar(250) DEFAULT NULL,
205 `personalName` varchar(60) DEFAULT NULL,
206 `brand` varchar(60) DEFAULT NULL,
207 `manufacturer` varchar(60) DEFAULT NULL,
208 `model` varchar(60) DEFAULT NULL,
209 `language` varchar(200) DEFAULT NULL,
210 `timezone` varchar(250) DEFAULT NULL,
211 `os` varchar(20) DEFAULT NULL,
212 `osVersion` varchar(20) DEFAULT NULL,
213 `sourceId` varchar(20) DEFAULT NULL,
214 `isActive` varchar(1) DEFAULT 'N',
215 `createdBy` varchar(45) DEFAULT NULL,
216 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
217 `lastModifiedBy` varchar(45) DEFAULT NULL,
218 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
219 PRIMARY KEY (`dcsId`),
220 CONSTRAINT `devices_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
221) ENGINE=InnoDB DEFAULT CHARSET=utf8;
222
223CREATE TABLE `email` (
224 `dcsId` varchar(20) NOT NULL,
225 `activeInd` varchar(1) DEFAULT 'N',
226 `type` varchar(10) NOT NULL,
227 `primaryInd` varchar(1) DEFAULT 'N',
228 `verifiedInd` varchar(1) DEFAULT 'N',
229 `verifiedDate` datetime NULL DEFAULT NULL,
230 `emailAddress` varchar(254) NOT NULL,
231 `createdBy` varchar(45) DEFAULT NULL,
232 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
233 `lastModifiedBy` varchar(45) DEFAULT NULL,
234 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
235 PRIMARY KEY (`dcsId`,`emailAddress`,`primaryInd`),
236 UNIQUE KEY `email_address_unique` (`emailAddress`),
237 KEY `fk_dcsId` (`dcsId`),
238 KEY `emailAddress` (`emailAddress`),
239 CONSTRAINT `email_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
240) ENGINE=InnoDB DEFAULT CHARSET=utf8;
241
242CREATE TABLE `eventExceptionController` (
243 `eventname` varchar(100) NOT NULL,
244 `lastactiveat` varchar(45) DEFAULT NULL,
245 `status` varchar(45) DEFAULT NULL,
246 `stopStatus` varchar(45) DEFAULT NULL,
247 PRIMARY KEY (`eventname`)
248) ENGINE=InnoDB DEFAULT CHARSET=utf8;
249
250CREATE TABLE `eventExceptionFramework` (
251 `transactionid` varchar(50) NOT NULL,
252 `apiuid` varchar(45) DEFAULT NULL,
253 `appname` varchar(45) DEFAULT NULL,
254 `contenttype` varchar(45) DEFAULT NULL,
255 `createddatetime` varchar(45) DEFAULT NULL,
256 `eventdetails` longtext,
257 `exchangename` varchar(45) DEFAULT NULL,
258 `marketid` varchar(45) DEFAULT NULL,
259 `processedind` varchar(2) DEFAULT NULL,
260 `republisheddatetime` varchar(45) DEFAULT NULL,
261 `sourceapp` varchar(45) DEFAULT NULL,
262 `transactiontype` varchar(45) DEFAULT NULL,
263 PRIMARY KEY (`transactionid`)
264) ENGINE=InnoDB DEFAULT CHARSET=utf8;
265
266CREATE TABLE `externalId` (
267 `dcsId` varchar(20) NOT NULL,
268 `appId` varchar(50) NOT NULL,
269 `appName` varchar(100) NOT NULL,
270 `activeInd` varchar(1) DEFAULT 'N',
271 `createdBy` varchar(45) DEFAULT NULL,
272 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
273 `lastModifiedBy` varchar(45) DEFAULT NULL,
274 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
275 PRIMARY KEY (`dcsId`,`appId`,`appName`),
276 UNIQUE KEY `externalId_unique` (`appName`,`appId`,`dcsId`),
277 CONSTRAINT `externalId_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
278) ENGINE=InnoDB DEFAULT CHARSET=utf8;
279
280CREATE TABLE `favorites` (
281 `dcsId` varchar(20) NOT NULL,
282 `favoriteId` varchar(4) NOT NULL,
283 `sourceId` varchar(10) DEFAULT NULL,
284 `type` varchar(20) NOT NULL,
285 `details` longtext DEFAULT NULL,
286 `createdBy` varchar(45) DEFAULT NULL,
287 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
288 `lastModifiedBy` varchar(45) DEFAULT NULL,
289 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
290 PRIMARY KEY (`dcsId`,`favoriteId`),
291 CONSTRAINT `favorites_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
292) ENGINE=InnoDB DEFAULT CHARSET=utf8;
293
294CREATE TABLE `feedback` (
295 `dcsId` varchar(20) NOT NULL,
296 `feedbackRating` varchar(10) DEFAULT NULL,
297 `feedBackType` varchar(20) DEFAULT NULL,
298 `userComment` varchar(500) DEFAULT NULL,
299 `createdBy` varchar(45) DEFAULT NULL,
300 `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
301 `lastModifiedBy` varchar(45) DEFAULT NULL,
302 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
303 `feedbackId` varchar(4) DEFAULT NULL,
304 PRIMARY KEY (`dcsId`,`createdAt`),
305 CONSTRAINT `feedback_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
306) ENGINE=InnoDB DEFAULT CHARSET=utf8;
307
308CREATE TABLE `phone` (
309 `dcsId` varchar(20) NOT NULL,
310 `activeInd` varchar(1) DEFAULT 'N',
311 `type` varchar(10) NOT NULL,
312 `primaryInd` varchar(1) DEFAULT 'N',
313 `verifiedInd` varchar(1) DEFAULT 'N',
314 `verifiedDate` datetime NULL DEFAULT NULL,
315 `number` varchar(128) NOT NULL,
316 `createdBy` varchar(45) DEFAULT NULL,
317 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
318 `lastModifiedBy` varchar(45) DEFAULT NULL,
319 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
320 PRIMARY KEY (`dcsId`,`number`,`primaryInd`),
321 KEY `number` (`number`),
322 CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
323) ENGINE=InnoDB DEFAULT CHARSET=utf8;
324
325CREATE TABLE `preferences` (
326 `dcsId` varchar(20) NOT NULL,
327 `preferenceId` varchar(4) NOT NULL,
328 `sourceId` varchar(10) DEFAULT NULL,
329 `preferenceDesc` varchar(100) DEFAULT NULL,
330 `type` varchar(50) DEFAULT NULL,
331 `isActive` varchar(1) DEFAULT 'N',
332 `details` longtext,
333 `createdBy` varchar(45) DEFAULT NULL,
334 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
335 `lastModifiedBy` varchar(45) DEFAULT NULL,
336 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
337 PRIMARY KEY (`dcsId`,`preferenceId`),
338 CONSTRAINT `preferences_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
339) ENGINE=InnoDB DEFAULT CHARSET=utf8;
340
341CREATE TABLE `security` (
342 `dcsId` varchar(20) NOT NULL,
343 `phoneVerificationCode` varchar(10) DEFAULT NULL,
344 `emailVerificationCode` varchar(20) DEFAULT NULL,
345 `pVerificationCodeCreatedAt` datetime NULL DEFAULT NULL,
346 `eVerificationCodeCreatedAt` datetime NULL DEFAULT NULL,
347 `resetPasswordCode` varchar(20) DEFAULT NULL,
348 `resetPasswordCreatedAt` datetime NULL DEFAULT NULL,
349 `verifiedPwdInd` varchar(1) DEFAULT 'N',
350 `ageVerified` varchar(1) DEFAULT 'N',
351 `createdBy` varchar(45) DEFAULT NULL,
352 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
353 `lastModifiedBy` varchar(45) DEFAULT NULL,
354 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
355 PRIMARY KEY (`dcsId`),
356 CONSTRAINT `security_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
357) ENGINE=InnoDB DEFAULT CHARSET=utf8;
358
359CREATE TABLE `subscriptions` (
360 `dcsId` varchar(20) NOT NULL,
361 `subscriptionId` varchar(4) NOT NULL,
362 `sourceId` varchar(10) DEFAULT NULL,
363 `subscriptionDesc` varchar(50) DEFAULT NULL,
364 `channelId` varchar(1) DEFAULT NULL,
365 `deviceId` varchar(200) DEFAULT NULL,
366 `optInStatus` varchar(1) DEFAULT 'N',
367 `optInDate` datetime NULL DEFAULT NULL,
368 `optOutDate` datetime NULL DEFAULT NULL,
369 `optOutReason` varchar(100) DEFAULT NULL,
370 `createdBy` varchar(45) DEFAULT NULL,
371 `createdAt` datetime NULL DEFAULT CURRENT_TIMESTAMP,
372 `lastModifiedBy` varchar(45) DEFAULT NULL,
373 `lastModifiedAt` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
374 `legacyId` varchar(4) DEFAULT NULL,
375 `legacyType` varchar(10) DEFAULT NULL,
376 PRIMARY KEY (`dcsId`,`subscriptionId`),
377 CONSTRAINT `subscriptions_ibfk_1` FOREIGN KEY (`dcsId`) REFERENCES `baseProfile` (`dcsId`) ON UPDATE CASCADE
378) ENGINE=InnoDB DEFAULT CHARSET=utf8;
379
380-- Script to create audit tables for acceptancepolicy, accesspolicy, baseprofile and consolidated
381CREATE TABLE `acceptancePolicyAuditTrail` (
382 `auditState` varchar(20) DEFAULT NULL,
383 `auditDmlAction` varchar(20) DEFAULT NULL,
384 `logentrydttm` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
385 `dcsId` varchar(20) NOT NULL,
386 `name` varchar(100) NOT NULL,
387 `channelId` varchar(1) DEFAULT NULL,
388 `deviceId` varchar(20) DEFAULT NULL,
389 `acceptanceInd` varchar(2) DEFAULT NULL,
390 `acceptanceDate` datetime NULL DEFAULT NULL,
391 `createdBy` varchar(45) DEFAULT NULL,
392 `createdAt` datetime NULL DEFAULT NULL,
393 `lastModifiedBy` varchar(45) DEFAULT NULL,
394 `lastModifiedAt` datetime NULL DEFAULT NULL
395) ENGINE=InnoDB DEFAULT CHARSET=utf8;
396
397CREATE TABLE `accessPolicyAuditTrail` (
398 `auditState` varchar(20) DEFAULT NULL,
399 `auditDmlAction` varchar(20) DEFAULT NULL,
400 `logentrydttm` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
401 `dcsId` varchar(20) NOT NULL,
402 `name` varchar(100) NOT NULL,
403 `channelId` varchar(1) DEFAULT NULL,
404 `deviceId` varchar(20) DEFAULT NULL,
405 `acceptanceInd` varchar(2) DEFAULT NULL,
406 `acceptanceDate` datetime NULL DEFAULT NULL,
407 `createdBy` varchar(45) DEFAULT NULL,
408 `createdAt` datetime NULL DEFAULT NULL,
409 `lastModifiedBy` varchar(45) DEFAULT NULL,
410 `lastModifiedAt` datetime NULL DEFAULT NULL
411) ENGINE=InnoDB DEFAULT CHARSET=utf8;
412
413CREATE TABLE `deleteProfileAuditTrail` (
414 `auditState` varchar(20) DEFAULT NULL,
415 `auditDmlAction` varchar(20) DEFAULT NULL,
416 `logentrydttm` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
417 `dcsId` varchar(20) NOT NULL,
418 `emailAddress` varchar(254) NULL,
419 `phone` varchar(20) NULL,
420 `channelId` varchar(1) DEFAULT NULL,
421 `deviceToken` varchar(20) DEFAULT NULL,
422 `deleteInd` varchar(2) DEFAULT NULL,
423 `deleteDate` datetime NULL DEFAULT CURRENT_TIMESTAMP,
424 `createdBy` varchar(45) DEFAULT NULL,
425 `createdAt` datetime NULL DEFAULT NULL,
426 `lastModifiedBy` varchar(45) DEFAULT NULL,
427 `lastModifiedAt` datetime NULL DEFAULT NULL
428) ENGINE=InnoDB DEFAULT CHARSET=utf8;
429
430CREATE TABLE `consolidated_temp` (
431 `dcsId` varchar(20) NOT NULL,
432 `emailLoginUsername` varchar(254) DEFAULT NULL,
433 `phoneLoginUsername` varchar(20) DEFAULT NULL,
434 PRIMARY KEY (`dcsId`)
435) ENGINE=InnoDB DEFAULT CHARSET=utf8;
436
437CREATE INDEX IDX_PREFERENCES ON preferences(dcsId);
438CREATE INDEX IDX_SUBSCRIPTIONS ON subscriptions(dcsId);
439CREATE INDEX IDX_ADDRESS_DCSID ON address (dcsId) ;
440CREATE INDEX IDX_01_ACCESSPOLICY_DCSID ON accessPolicy(dcsId);
441CREATE INDEX IDX_01_ACCEPTANCE_DCSID ON acceptancePolicies(dcsId);
442CREATE INDEX IDX_01_AGENTREMARK_DCSID ON agentRemark(dcsId);
443CREATE INDEX IDX_01_FEEDBACK_DCSID ON feedback(dcsId);
444CREATE INDEX IDX_01_FAVORITES_DCSID ON favorites(dcsId);
445CREATE INDEX IDX_EMAIL_01_DCSID ON email(dcsId);
446
447DROP INDEX externalId_unique ON externalId;
448CREATE UNIQUE INDEX externalId_unique ON externalId(`appName`,`appId`);
449CREATE INDEX IDX_EXTERNALID_01_DCSID ON externalId(`dcsId`);
450
451
452INSERT INTO `eventExceptionController`
453(`eventname`,
454`lastactiveat`,
455`status`,
456`stopStatus`)
457VALUES
458('queueExcpDelete',
459'16:28:00',
460'N',
461'N');
462
463INSERT INTO `eventExceptionController`
464(`eventname`,
465`lastactiveat`,
466`status`,
467`stopStatus`)
468VALUES
469('queueExcpUpdate',
470'16:28:00',
471'N',
472'N');
473
474COMMIT;
475
476USE `de-local`;
477
478-- script to create triggers on tables acceptancepolicies, accesspolicy, baseprofile and consolidated and Stored Procedure for dcsIdGeneration
479
480DELIMITER $$
481-- create trigger for insert operation on acceptancepolicies
482DROP TRIGGER IF EXISTS `acceptancePolicies_After_Insert`$$
483
484CREATE TRIGGER `acceptancePolicies_After_Insert` AFTER INSERT ON `acceptancePolicies` FOR EACH ROW
485BEGIN
486 -- insert the new incoming values into the audit table
487 INSERT INTO acceptancePolicyAuditTrail(auditState,auditDmlAction,dcsId,name,channelId,deviceId,acceptanceInd,acceptanceDate,
488 createdBy,createdAt,lastModifiedBy,lastModifiedAt)
489 VALUES ('NEW','INSERT',new.`dcsId`,new.`name`,new.`channelId`,new.`deviceId`,new.`acceptanceInd`,new.`acceptanceDate`,
490 new.`createdBy`,new.`createdAt`,new.`lastModifiedBy`,new.`lastModifiedAt`);
491
492END$$
493
494-- create trigger for update operation on acceptancepolicies
495DROP TRIGGER IF EXISTS `acceptancePolicies_After_Update`$$
496
497CREATE TRIGGER `acceptancePolicies_After_Update` AFTER UPDATE ON `acceptancePolicies` FOR EACH ROW
498BEGIN
499 -- insert the new incoming values into the audit table
500 INSERT INTO acceptancePolicyAuditTrail(auditState,auditDmlAction,dcsId,name,channelId,deviceId,acceptanceInd,acceptanceDate,
501 createdBy,createdAt,lastModifiedBy,lastModifiedAt)
502 VALUES ('NEW','UPDATE',new.`dcsId`,new.`name`,new.`channelId`,new.`deviceId`,new.`acceptanceInd`,new.`acceptanceDate`,
503 new.`createdBy`,new.`createdAt`,new.`lastModifiedBy`,new.`lastModifiedAt`);
504
505END$$
506
507-- create trigger for delete operation on acceptancepolicies
508DROP TRIGGER IF EXISTS `acceptancePolicies_After_Delete`$$
509
510CREATE TRIGGER `acceptancePolicies_After_Delete` AFTER DELETE ON `acceptancePolicies` FOR EACH ROW
511BEGIN
512 -- insert old existing values into the audit table
513 INSERT INTO acceptancePolicyAuditTrail(auditState,auditDmlAction,dcsId,name,channelId,deviceId,acceptanceInd,acceptanceDate,
514 createdBy,createdAt,lastModifiedBy,lastModifiedAt)
515 VALUES ('OLD','DELETE',old.`dcsId`,old.`name`,old.`channelId`,old.`deviceId`,old.`acceptanceInd`,old.`acceptanceDate`,
516 old.`createdBy`,old.`createdAt`,old.`lastModifiedBy`,old.`lastModifiedAt` );
517
518END$$
519
520-- create trigger for insert operation on accesspolicy
521DROP TRIGGER IF EXISTS `accessPolicy_After_Insert`$$
522
523CREATE TRIGGER `accessPolicy_After_Insert` AFTER INSERT ON `accessPolicy` FOR EACH ROW
524BEGIN
525 -- insert the new incoming values into the audit table
526 INSERT INTO accessPolicyAuditTrail(auditState,auditDmlAction,dcsId,name,channelId,deviceId,acceptanceInd,acceptanceDate,
527 createdBy,createdAt,lastModifiedBy,lastModifiedAt)
528 VALUES ('NEW','INSERT',new.`dcsId`,new.`name`,new.`channelId`,new.`deviceId`,new.`acceptanceInd`,new.`acceptanceDate`,
529 new.`createdBy`,new.`createdAt`,new.`lastModifiedBy`,new.`lastModifiedAt`);
530
531END$$
532
533-- create trigger for update operation on accesspolicy
534DROP TRIGGER IF EXISTS `accessPolicy_After_Update`$$
535
536CREATE TRIGGER `accessPolicy_After_Update` AFTER UPDATE ON `accessPolicy` FOR EACH ROW
537BEGIN
538 -- insert the new incoming values into the audit table
539 INSERT INTO accessPolicyAuditTrail(auditState,auditDmlAction,dcsId,name,channelId,deviceId,acceptanceInd,acceptanceDate,
540 createdBy,createdAt,lastModifiedBy,lastModifiedAt)
541 VALUES ('NEW','UPDATE',new.`dcsId`,new.`name`,new.`channelId`,new.`deviceId`,new.`acceptanceInd`,new.`acceptanceDate`,
542 new.`createdBy`,new.`createdAt`,new.`lastModifiedBy`,new.`lastModifiedAt`);
543
544END$$
545
546-- create trigger for delete operation on accesspolicy
547DROP TRIGGER IF EXISTS `accessPolicy_After_Delete`$$
548
549CREATE TRIGGER `accessPolicy_After_Delete` AFTER DELETE ON `accessPolicy` FOR EACH ROW
550BEGIN
551 -- insert old existing values into the audit table
552 INSERT INTO accessPolicyAuditTrail(auditState,auditDmlAction,dcsId,name,channelId,deviceId,acceptanceInd,acceptanceDate,
553 createdBy,createdAt,lastModifiedBy,lastModifiedAt)
554 VALUES ('OLD','DELETE', old.`dcsId`,old.`name`,old.`channelId`,old.`deviceId`,old.`acceptanceInd`,old.`acceptanceDate`,
555 old.`createdBy`,old.`createdAt`,old.`lastModifiedBy`,old.`lastModifiedAt`);
556
557END$$
558
559-- create trigger for updating deleteInd to 'S' on soft delete of profile
560DROP TRIGGER IF EXISTS `deleteProfile_After_Update`$$
561
562CREATE TRIGGER `deleteProfile_After_Update` AFTER UPDATE ON `baseProfile` FOR EACH ROW
563BEGIN
564
565 DECLARE emailAddress varchar(254);
566 DECLARE phoneNumber varchar(20);
567 SELECT emailLoginUsername INTO emailAddress FROM consolidated WHERE dcsId = new.`dcsId`;
568 SELECT phoneLoginUsername INTO phoneNumber FROM consolidated WHERE dcsId = new.`dcsId`;
569 -- check if activeInd value is 'S' and then insert data into the table
570 IF new.`activeInd` = 'S' THEN
571 INSERT INTO deleteProfileAuditTrail(auditState,auditDmlAction,dcsId,emailAddress,phone,channelId,deviceToken,deleteInd,createdBy,createdAt,lastModifiedBy,lastModifiedAt)
572 VALUES ('OLD','UPDATE',new.`dcsId`,emailAddress,phoneNumber,null,null,'S',new.`createdBy`,new.`createdAt`,new.`lastModifiedBy`,new.`lastModifiedAt`);
573 END IF;
574
575END$$
576
577-- create trigger to store emailAddress and phoneNumber on hard delete of profile in consolidated_temp table to use in audit table
578DROP TRIGGER IF EXISTS `getProfile_Before_Delete`$$
579
580CREATE TRIGGER `getProfile_Before_Delete` BEFORE DELETE ON `consolidated` FOR EACH ROW
581BEGIN
582
583 INSERT INTO consolidated_temp (SELECT dcsId, emailLoginUsername, phoneLoginUsername FROM consolidated WHERE dcsId = old.`dcsId`);
584
585END$$
586
587-- create trigger for updating deleteInd to 'H' on hard delete of profile
588DROP TRIGGER IF EXISTS `deleteProfile_After_Delete`$$
589
590CREATE TRIGGER `deleteProfile_After_Delete` AFTER DELETE ON `baseProfile` FOR EACH ROW
591BEGIN
592
593 DECLARE emailAddress varchar(254);
594 DECLARE phoneNumber varchar(20);
595 SELECT emailLoginUsername INTO emailAddress FROM consolidated_temp WHERE dcsId = old.`dcsId`;
596 SELECT phoneLoginUsername INTO phoneNumber FROM consolidated_temp WHERE dcsId = old.`dcsId`;
597 -- insert old existing values into the audit table
598 INSERT INTO deleteProfileAuditTrail(auditState,auditDmlAction,dcsId,emailAddress,phone,channelId,deviceToken,deleteInd,createdBy,createdAt,lastModifiedBy,lastModifiedAt)
599 VALUES ('OLD','DELETE',old.`dcsId`,emailAddress,phoneNumber,null,null,'H',old.`createdBy`,old.`createdAt`,old.`lastModifiedBy`,old.`lastModifiedAt`);
600 -- deleting record from consolidated_temp table to keep clear of junk data
601 DELETE FROM consolidated_temp WHERE dcsId = old.`dcsId`;
602
603END$$
604
605-- create procedure for dcsId generation
606DROP PROCEDURE IF EXISTS dcsIdGenerator$$
607USE `de-local`$$
608
609CREATE PROCEDURE dcsIdGenerator (OUT dcsId bigint)
610BEGIN
611
612INSERT INTO `dcsIdGenerator`(`dcsId`) VALUES (NULL);
613
614SELECT LAST_INSERT_ID() INTO dcsId;
615
616END$$
617
618DELIMITER ;
619
620COMMIT;