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; ALTER TABLE CHANGE_MANAGEMENT_SCHEDULES ADD COLUMN request LONGTEXT NULL DEFAULT NULL;