1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
USE CMSO;
-- -----------------------------------------------------
-- UPDATE THE PRIMARY KEYS
-- -----------------------------------------------------
ALTER TABLE SCHEDULES DROP PRIMARY KEY, CHANGE id id INT, ADD PRIMARY KEY (uuid);
ALTER TABLE DOMAIN_DATA DROP PRIMARY KEY, CHANGE id id INT, ADD PRIMARY KEY (uuid);
ALTER TABLE APPROVAL_TYPES DROP PRIMARY KEY, CHANGE id id INT, ADD PRIMARY KEY (uuid);
ALTER TABLE SCHEDULE_APPROVALS DROP PRIMARY KEY, CHANGE id id INT, ADD PRIMARY KEY (uuid);
ALTER TABLE CHANGE_MANAGEMENT_GROUPS DROP PRIMARY KEY, CHANGE id id INT, ADD PRIMARY KEY (uuid);
ALTER TABLE CHANGE_MANAGEMENT_SCHEDULES DROP PRIMARY KEY, CHANGE id id INT, ADD PRIMARY KEY (uuid);
ALTER TABLE CHANGE_MANAGEMENT_CHANGE_WINDOWS DROP PRIMARY KEY, CHANGE id id INT, ADD PRIMARY KEY (uuid);
-- -----------------------------------------------------
-- Table CHANGE_MANAGEMENT_DATA (DO this after we update the PK)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS ELEMENT_DATA (
uuid BINARY(16) NOT NULL,
change_management_schedules_uuid BINARY(16) NOT NULL,
name VARCHAR(128) NULL,
value LONGTEXT NULL,
CONSTRAINT PK_ELEMENT_DATA PRIMARY KEY (uuid),
CONSTRAINT FK_ELEMENT_DATA_CHANGE_MANAGEMENT_SCHEDULES FOREIGN KEY (change_management_schedules_uuid)
REFERENCES CHANGE_MANAGEMENT_SCHEDULES (uuid)
ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB;
CREATE INDEX FK_ELEMENT_DATA_CHANGE_MANAGEMENT_SCHEDULES_IDX ON ELEMENT_DATA (change_management_schedules_uuid ASC);
-- -----------------------------------------------------
-- RE_CREATE INDEXES WITH UPDATED KEYES
-- -----------------------------------------------------
ALTER TABLE DOMAIN_DATA ADD
CONSTRAINT FK_DOMAIN_DATA_SCHEDULES FOREIGN KEY (schedules_uuid)
REFERENCES SCHEDULES (uuid)
ON DELETE NO ACTION ON UPDATE NO ACTION
;
CREATE INDEX FK_DOMAIN_DATA_SCHEDULES_IDX ON DOMAIN_DATA (schedules_uuid ASC);
-- -----------------------------------------------------
ALTER TABLE SCHEDULE_APPROVALS ADD
CONSTRAINT FK_SCHEDULE_APPROVALS_SCHEDULES FOREIGN KEY (schedules_uuid)
REFERENCES SCHEDULES (uuid)
ON DELETE NO ACTION ON UPDATE NO ACTION
;
ALTER TABLE SCHEDULE_APPROVALS ADD
CONSTRAINT FK_SCHEDULE_APPROVALS_APPROVAL_TYPES FOREIGN KEY (approval_types_uuid)
REFERENCES APPROVAL_TYPES (uuid)
ON DELETE NO ACTION ON UPDATE NO ACTION
;
CREATE INDEX FK_SCHEDULE_APPROVALS_SCHEDULES_IDX ON SCHEDULE_APPROVALS (schedules_uuid ASC);
CREATE INDEX FK_APPROVAL_TYPES_IDX ON SCHEDULE_APPROVALS (approval_types_uuid ASC);
-- -----------------------------------------------------
ALTER TABLE CHANGE_MANAGEMENT_GROUPS ADD
CONSTRAINT FK_CHANGE_MANAGEMENT_GROUPS_SCHEDULES FOREIGN KEY (schedules_uuid)
REFERENCES SCHEDULES (uuid)
ON DELETE NO ACTION ON UPDATE NO ACTION
;
CREATE INDEX FK_SCHEDULES_IDX ON CHANGE_MANAGEMENT_GROUPS (schedules_uuid ASC);
CREATE UNIQUE INDEX CHANGE_MANAGEMENT_GROUP_ID_UNIQUE ON CHANGE_MANAGEMENT_GROUPS (schedules_uuid ASC, group_id ASC);
-- -----------------------------------------------------
ALTER TABLE CHANGE_MANAGEMENT_SCHEDULES ADD
CONSTRAINT FK_CHANGE_MANAGEMENT_SCHEDULES_CHANGE_MANAGEMENT_GROUP FOREIGN KEY (change_management_group_uuid)
REFERENCES CHANGE_MANAGEMENT_GROUPS (uuid)
ON DELETE NO ACTION ON UPDATE NO ACTION
;
CREATE INDEX FK_CHANGE_MANAGEMENT_GROUP_CHANGE_MANAGEMENT_SCHEDULES_IDX ON CHANGE_MANAGEMENT_SCHEDULES (change_management_group_uuid ASC);
CREATE UNIQUE INDEX CHANGE_MANAGEMENT_SCHEDULES_VNF_NAME_UNIQUE ON CHANGE_MANAGEMENT_SCHEDULES (change_management_group_uuid ASC, vnf_name ASC);
-- -----------------------------------------------------
ALTER TABLE CHANGE_MANAGEMENT_CHANGE_WINDOWS ADD
CONSTRAINT FK_CHANGE_WINDOWS_CHANGE_MANAGEMENT_GROUPS1 FOREIGN KEY (change_management_group_uuid)
REFERENCES CHANGE_MANAGEMENT_GROUPS (uuid)
ON DELETE NO ACTION ON UPDATE NO ACTION
;
CREATE INDEX FK_CHANGE_WINDOWS_CHANGE_MANAGEMENT_GROUPS1_IDX on CHANGE_MANAGEMENT_CHANGE_WINDOWS (change_management_group_uuid ASC);
|
