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;