aboutsummaryrefslogtreecommitdiffstats
path: root/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-add-uuid.sql
diff options
context:
space:
mode:
Diffstat (limited to 'cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-add-uuid.sql')
-rw-r--r--cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-add-uuid.sql93
1 files changed, 93 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;