summaryrefslogtreecommitdiffstats
path: root/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-restore-indices.sql
diff options
context:
space:
mode:
Diffstat (limited to 'cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-restore-indices.sql')
-rw-r--r--cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-restore-indices.sql92
1 files changed, 92 insertions, 0 deletions
diff --git a/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-restore-indices.sql b/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-restore-indices.sql
new file mode 100644
index 0000000..0cb224a
--- /dev/null
+++ b/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-restore-indices.sql
@@ -0,0 +1,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);
+