diff options
Diffstat (limited to 'cmso-database/src')
5 files changed, 282 insertions, 0 deletions
diff --git a/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-add-uuid.sql b/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-add-uuid.sql new file mode 100644 index 0000000..b11ea0a --- /dev/null +++ b/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-add-uuid.sql @@ -0,0 +1,93 @@ +USE CMSO; + +-- Schedules Add UUID column +ALTER TABLE SCHEDULES ADD COLUMN uuid BINARY(16) NOT NULL FIRST; +-- Add foreign keys +ALTER TABLE DOMAIN_DATA ADD COLUMN schedules_uuid BINARY(16) NOT NULL; +ALTER TABLE SCHEDULE_APPROVALS ADD COLUMN schedules_uuid BINARY(16) NOT NULL; +ALTER TABLE CHANGE_MANAGEMENT_GROUPS ADD COLUMN schedules_uuid BINARY(16) NOT NULL; +ALTER TABLE CHANGE_MANAGEMENT_CHANGE_WINDOWS ADD COLUMN schedules_uuid BINARY(16); + + + +-- CHANGE_MANAGEMENT_GROUPS Add UUID column +ALTER TABLE CHANGE_MANAGEMENT_GROUPS ADD COLUMN uuid BINARY(16) NOT NULL FIRST; +-- Foreign keys +ALTER TABLE CHANGE_MANAGEMENT_SCHEDULES ADD COLUMN change_management_group_uuid BINARY(16) NOT NULL; +ALTER TABLE CHANGE_MANAGEMENT_CHANGE_WINDOWS ADD COLUMN change_management_group_uuid BINARY(16); + + +-- CHANGE_MANAGEMENT_SCHEDULES Add UUID column +ALTER TABLE CHANGE_MANAGEMENT_SCHEDULES ADD COLUMN uuid BINARY(16) NOT NULL FIRST; +-- Foreign keys + + +-- DOMAIN_DATA Add UUID column +ALTER TABLE DOMAIN_DATA ADD COLUMN uuid BINARY(16) NOT NULL FIRST; +-- Foreign keys + + +-- SCHEDULE_APPROVALS Add UUID column +ALTER TABLE SCHEDULE_APPROVALS ADD COLUMN uuid BINARY(16) NOT NULL FIRST; +-- Foreign keys + + +-- CHANGE_MANAGEMENT_CHANGE_WINDOWS Add UUID column +-- Note that change window will be related to either a schedule or a group. +ALTER TABLE CHANGE_MANAGEMENT_CHANGE_WINDOWS ADD COLUMN uuid BINARY(16) NOT NULL FIRST; +-- Foreign keys + + +-- APPROVAL_TYPES Add UUID column +ALTER TABLE APPROVAL_TYPES ADD COLUMN uuid BINARY(16) NOT NULL FIRST; +-- Foreign keys +ALTER TABLE SCHEDULE_APPROVALS ADD COLUMN approval_types_uuid BINARY(16) NOT NULL; + + +-- SCHEDULE_EVENTS not used +DROP TABLE IF EXISTS SCHEDULE_EVENTS; + +SET SQL_SAFE_UPDATES = 0; + +-- ---------------------------------------------------- +-- Populate UUID in all existing uuid fields +-- ---------------------------------------------------- +UPDATE SCHEDULES SET uuid = unhex(replace(uuid(), '-', '')) where uuid is null; +UPDATE CHANGE_MANAGEMENT_GROUPS SET uuid = unhex(replace(uuid(), '-', '')) where uuid is null; +UPDATE CHANGE_MANAGEMENT_SCHEDULES SET uuid = unhex(replace(uuid(), '-', '')) where uuid is null; +UPDATE DOMAIN_DATA SET uuid = unhex(replace(uuid(), '-', '')) where uuid is null; +UPDATE SCHEDULE_APPROVALS SET uuid = unhex(replace(uuid(), '-', '')) where uuid is null; +UPDATE CHANGE_MANAGEMENT_CHANGE_WINDOWS SET uuid = unhex(replace(uuid(), '-', '')) where uuid is null; +UPDATE APPROVAL_TYPES SET uuid = unhex(replace(uuid(), '-', '')); + +-- ---------------------------------------------------- +-- SCHEDULES Update all of the foreign key columns +-- ---------------------------------------------------- +update DOMAIN_DATA set schedules_uuid = + (select distinct s.uuid from SCHEDULES s where schedules_id = s.id); +update SCHEDULE_APPROVALS set schedules_uuid = + (select distinct s.uuid from SCHEDULES s where schedules_id = s.id); +update CHANGE_MANAGEMENT_GROUPS set schedules_uuid = + (select distinct s.uuid from SCHEDULES s where schedules_id = s.id); + +-- ---------------------------------------------------- +-- CHANGE_MANAGEMENT_GROUPS Update all of the foreign key columns +-- ---------------------------------------------------- +update CHANGE_MANAGEMENT_SCHEDULES set change_management_group_uuid = + (select distinct s.uuid from CHANGE_MANAGEMENT_GROUPS s where change_management_groups_id = s.id); +update CHANGE_MANAGEMENT_CHANGE_WINDOWS set change_management_group_uuid = + (select distinct s.uuid from CHANGE_MANAGEMENT_GROUPS s where change_management_groups_id = s.id); + +-- ---------------------------------------------------- +-- APPROVAL_TYPES Update all of the foreign key columns +-- ---------------------------------------------------- +update SCHEDULE_APPROVALS set approval_types_uuid = + (select distinct s.uuid from APPROVAL_TYPES s where approval_type_id = s.id); + +-- ---------------------------------------------------- +-- APPROVAL_TYPES Update all of the foreign key columns +-- ---------------------------------------------------- +update SCHEDULE_APPROVALS set approval_types_uuid = + (select distinct s.uuid from SCHEDULE_APPROVALS t, APPROVAL_TYPES s where t.approval_type_id = s.id); + +SET SQL_SAFE_UPDATES = 1; diff --git a/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-drop-ids.sql b/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-drop-ids.sql new file mode 100644 index 0000000..5522f64 --- /dev/null +++ b/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-drop-ids.sql @@ -0,0 +1,18 @@ +USE CMSO; + +-- -------------------------------------------------------------- +-- DROP ALL OF THE INTENGER IDs +-- -------------------------------------------------------------- +ALTER TABLE SCHEDULES DROP COLUMN id; +ALTER TABLE DOMAIN_DATA DROP COLUMN id; +ALTER TABLE DOMAIN_DATA DROP COLUMN schedules_id; +ALTER TABLE APPROVAL_TYPES DROP COLUMN id; +ALTER TABLE SCHEDULE_APPROVALS DROP COLUMN id; +ALTER TABLE SCHEDULE_APPROVALS DROP COLUMN schedules_id; +ALTER TABLE SCHEDULE_APPROVALS DROP COLUMN approval_type_id; +ALTER TABLE CHANGE_MANAGEMENT_GROUPS DROP COLUMN id; +ALTER TABLE CHANGE_MANAGEMENT_GROUPS DROP COLUMN schedules_id; +ALTER TABLE CHANGE_MANAGEMENT_SCHEDULES DROP COLUMN id; +ALTER TABLE CHANGE_MANAGEMENT_SCHEDULES DROP COLUMN change_management_groups_id; +ALTER TABLE CHANGE_MANAGEMENT_CHANGE_WINDOWS DROP COLUMN id; +ALTER TABLE CHANGE_MANAGEMENT_CHANGE_WINDOWS DROP COLUMN change_management_groups_id; diff --git a/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-drop-indices.sql b/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-drop-indices.sql new file mode 100644 index 0000000..ec74432 --- /dev/null +++ b/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-drop-indices.sql @@ -0,0 +1,28 @@ +USE CMSO; + +-- ----------------------------------------------------- +-- DROP THE FOREIGN KEYS THAT USE INT IDs +-- ----------------------------------------------------- +ALTER TABLE DOMAIN_DATA DROP FOREIGN KEY FK_DOMAIN_DATA_SCHEDULES; +ALTER TABLE SCHEDULE_APPROVALS DROP FOREIGN KEY FK_SCHEDULE_APPROVALS_SCHEDULES; +ALTER TABLE CHANGE_MANAGEMENT_GROUPS DROP FOREIGN KEY FK_CHANGE_MANAGEMENT_GROUPS_SCHEDULES; +ALTER TABLE CHANGE_MANAGEMENT_SCHEDULES DROP FOREIGN KEY FK_CHANGE_MANAGEMENT_SCHEDULES_CHANGE_MANAGEMENT_GROUP; +ALTER TABLE CHANGE_MANAGEMENT_CHANGE_WINDOWS DROP FOREIGN KEY FK_CHANGE_WINDOWS_CHANGE_MANAGEMENT_GROUPS1; +ALTER TABLE SCHEDULE_APPROVALS DROP FOREIGN KEY FK_SCHEDULE_APPROVALS_APPROVAL_TYPES; + +-- ----------------------------------------------------- +-- DROP THE INDEXES THAT USE INT IDs +-- ----------------------------------------------------- +DROP INDEX ID_UNIQUE ON DOMAIN_DATA; -- THIS iS NOT NECESSARY PK +DROP INDEX CHANGE_MANAGEMENT_GROUP_ID_UNIQUE ON CHANGE_MANAGEMENT_GROUPS; +DROP INDEX CHANGE_MANAGEMENT_SCHEDULES_VNF_NAME_UNIQUE ON CHANGE_MANAGEMENT_SCHEDULES; +DROP INDEX FK_DOMAIN_DATA_SCHEDULES_IDX ON DOMAIN_DATA; +DROP INDEX FK_SCHEDULE_APPROVALS_SCHEDULES_IDX ON SCHEDULE_APPROVALS; +DROP INDEX FK_APPROVAL_TYPES_IDX ON SCHEDULE_APPROVALS; +DROP INDEX FK_SCHEDULES_IDX ON CHANGE_MANAGEMENT_GROUPS; +DROP INDEX FK_CHANGE_MANAGEMENT_GROUP_CHANGE_MANAGEMENT_SCHEDULES_IDX ON CHANGE_MANAGEMENT_SCHEDULES; +DROP INDEX FK_CHANGE_WINDOWS_CHANGE_MANAGEMENT_GROUPS1_IDX on CHANGE_MANAGEMENT_CHANGE_WINDOWS; + + + + 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); + diff --git a/cmso-database/src/main/resources/optimizer-dbchanges/onap-cmso-v1-schema.sql b/cmso-database/src/main/resources/optimizer-dbchanges/onap-cmso-v1-schema.sql new file mode 100644 index 0000000..d6e225b --- /dev/null +++ b/cmso-database/src/main/resources/optimizer-dbchanges/onap-cmso-v1-schema.sql @@ -0,0 +1,51 @@ +CREATE SCHEMA IF NOT EXISTS `optimizer` DEFAULT CHARACTER SET utf8 ; + +CREATE TABLE IF NOT EXISTS `optimizer`.`request` ( + uuid BINARY(16) NOT NULL, + request LONGTEXT NOT NULL, + created_time BIGINT(20) NULL DEFAULT NULL, + request_start BIGINT(20) NULL DEFAULT NULL, + request_end BIGINT(20) NULL DEFAULT NULL, + status VARCHAR(45) NULL DEFAULT NULL, + PRIMARY KEY (`uuid`)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = utf8; + +CREATE TABLE IF NOT EXISTS `optimizer`.`response` ( + uuid BINARY(16) NOT NULL, + repsonse LONGTEXT NULL DEFAULT NULL, + delivered_time BIGINT(20) NULL DEFAULT NULL, + PRIMARY KEY (`uuid`)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = utf8; + + +CREATE TABLE IF NOT EXISTS `optimizer`.`topology` ( + uuid BINARY(16) NOT NULL, + topology LONGTEXT NULL DEFAULT NULL, + topology_start BIGINT(20) NULL DEFAULT NULL, + topology_end BIGINT(20) NULL DEFAULT NULL, + topology_retries INT NULL DEFAULT NULL, + PRIMARY KEY (`uuid`)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = utf8; + +CREATE TABLE IF NOT EXISTS `optimizer`.`tickets` ( + uuid BINARY(16) NOT NULL, + tickets LONGTEXT NULL DEFAULT NULL, + tickets_start BIGINT(20) NULL DEFAULT NULL, + tickets_end BIGINT(20) NULL DEFAULT NULL, + tickets_retries INT NULL DEFAULT NULL, + PRIMARY KEY (`uuid`)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = utf8; + +CREATE TABLE IF NOT EXISTS `optimizer`.`optimizer` ( + uuid BINARY(16) NOT NULL, + optimize_response LONGTEXT NULL DEFAULT NULL, + optimize_start BIGINT(20) NULL DEFAULT NULL, + optimize_end BIGINT(20) NULL DEFAULT NULL, + optimize_retries INT NULL DEFAULT NULL, + PRIMARY KEY (`uuid`)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = utf8; |