summaryrefslogtreecommitdiffstats
path: root/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1.1-add-uuid.sql
blob: b11ea0a66b3f75fc71a91c5d50f9a39a9483d595 (plain)
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;