1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
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;
|
