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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
use catalogdb;
-- Drop Foreign Keys so we can migrate data
ALTER TABLE vnf_res_custom_to_vf_module_custom DROP FOREIGN KEY IF EXISTS fk_vnf_res_custom_to_vf_module_custom__vf_module_customization1;
ALTER TABLE vnf_res_custom_to_vf_module_custom DROP FOREIGN KEY IF EXISTS fk_vnf_res_custom_to_vf_module_custom__vnf_resource_customiza1;
ALTER TABLE cvnfc_customization DROP FOREIGN KEY IF EXISTS fk_cvnfc_customization__vf_module_customization1;
ALTER TABLE vnf_vfmodule_cvnfc_configuration_customization DROP FOREIGN KEY IF EXISTS fk_vnf_configuration_cvnfc_customization__vf_module_customiza1;
ALTER TABLE cvnfc_customization DROP FOREIGN KEY IF EXISTS fk_cvnfc_customization__vnf_resource_customization1;
ALTER TABLE vnf_resource_customization_to_service DROP FOREIGN KEY IF EXISTS vnf_resource_customization_to_service_ibfk_2;
ALTER TABLE vnf_vfmodule_cvnfc_configuration_customization DROP FOREIGN KEY IF EXISTS fk_vfmodule_cvnfc_configuration_customization__vnf_resource_c1;
ALTER TABLE vnfc_instance_group_customization DROP FOREIGN KEY IF EXISTS fk_vnfc_instance_group_customization__vnf_resource_customizat1;
DROP INDEX UK_cvnfc_customization on cvnfc_customization;
DELETE FROM vf_module_customization WHERE vf_module_model_uuid NOT IN (SELECT model_uuid FROM vf_module);
ALTER TABLE vnf_resource_customization
CHANGE COLUMN MULTI_STAGE_DESIGN MULTI_STAGE_DESIGN VARCHAR(20) NULL DEFAULT NULL AFTER NF_NAMING_CODE;
-- Generate Primary keys for tables
ALTER TABLE vnf_resource_customization DROP PRIMARY KEY, ADD ID INT(13) NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE vf_module_customization DROP PRIMARY KEY, ADD ID INT(13) NOT NULL AUTO_INCREMENT PRIMARY KEY;
-- Add columns for Joins between tables
ALTER TABLE vnf_resource_customization ADD SERVICE_MODEL_UUID VARCHAR(200) DEFAULT NULL;
ALTER TABLE vf_module_customization ADD VNF_RESOURCE_CUSTOMIZATION_ID INT(13) DEFAULT NULL;
ALTER TABLE vnfc_instance_group_customization ADD VNF_RESOURCE_CUSTOMIZATION_ID INT(13) DEFAULT NULL;
ALTER TABLE cvnfc_customization ADD VF_MODULE_CUSTOMIZATION_ID INT(13) DEFAULT NULL;
-- Migrate linkage between VNF/Services, re-add foreign key
INSERT INTO vnf_resource_customization (SELECT vrc.MODEL_CUSTOMIZATION_UUID,vrc.MODEL_INSTANCE_NAME, vrc.MIN_INSTANCES,vrc.MAX_INSTANCES,vrc.AVAILABILITY_ZONE_MAX_COUNT,vrc.NF_TYPE,vrc.NF_ROLE,vrc.NF_FUNCTION,
vrc.NF_NAMING_CODE,vrc.MULTI_STAGE_DESIGN,vrc.CREATION_TIMESTAMP, vrc.VNF_RESOURCE_MODEL_UUID,vrc.INSTANCE_GROUP_MODEL_UUID,NULL,crcs.SERVICE_MODEL_UUID from vnf_resource_customization vrc, vnf_resource_customization_to_service crcs, service srv WHERE crcs.RESOURCE_MODEL_CUSTOMIZATION_UUID = vrc.MODEL_CUSTOMIZATION_UUID AND srv.MODEL_UUID=crcs.SERVICE_MODEL_UUID);
DELETE FROM vnf_resource_customization where SERVICE_MODEL_UUID IS NULL;
ALTER TABLE vnf_resource_customization CHANGE COLUMN SERVICE_MODEL_UUID SERVICE_MODEL_UUID VARCHAR(200) NOT NULL;
ALTER TABLE vnf_resource_customization
ADD CONSTRAINT fk_vnf_resource_customization_to_service FOREIGN KEY (SERVICE_MODEL_UUID)
REFERENCES service(MODEL_UUID)
ON DELETE CASCADE
ON UPDATE CASCADE;
-- Migrate linkage between VNF/VNFCInstance Groups
ALTER TABLE vnfc_instance_group_customization DROP PRIMARY KEY;
INSERT INTO vnfc_instance_group_customization
(SELECT vnfcgroup.VNF_RESOURCE_CUSTOMIZATION_MODEL_UUID, vnfcgroup.INSTANCE_GROUP_MODEL_UUID, vnfcgroup.FUNCTION, vnfcgroup.DESCRIPTION, vnfcgroup.CREATION_TIMESTAMP, vnfrc.ID
FROM vnfc_instance_group_customization vnfcgroup, vnf_resource_customization vnfrc
WHERE vnfrc.MODEL_CUSTOMIZATION_UUID = vnfcgroup.VNF_RESOURCE_CUSTOMIZATION_MODEL_UUID);
DELETE FROM vnfc_instance_group_customization where VNF_RESOURCE_CUSTOMIZATION_ID IS NULL;
ALTER TABLE vnfc_instance_group_customization ADD ID INT(13) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE vnfc_instance_group_customization
ADD INDEX fk_vnfc_instance_group_customization__vnf_res_custom1_idx (VNF_RESOURCE_CUSTOMIZATION_ID ASC),
ADD CONSTRAINT fk_vnfc_instance_group_customization_vnf_customization FOREIGN KEY (VNF_RESOURCE_CUSTOMIZATION_ID)
REFERENCES vnf_resource_customization(ID)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE vnfc_instance_group_customization DROP VNF_RESOURCE_CUSTOMIZATION_MODEL_UUID;
-- Migrate linkage between VNF/VFMODULEs, re-add foreign key
INSERT INTO vf_module_customization
(SELECT vmc.MODEL_CUSTOMIZATION_UUID, vmc.LABEL, vmc.INITIAL_COUNT, vmc.MIN_INSTANCES, vmc.MAX_INSTANCES, vmc.AVAILABILITY_ZONE_COUNT, vmc.HEAT_ENVIRONMENT_ARTIFACT_UUID, vmc.VOL_ENVIRONMENT_ARTIFACT_UUID, vmc.CREATION_TIMESTAMP, vmc.VF_MODULE_MODEL_UUID, NULL,vnfrc.ID
FROM vf_module_customization vmc, vnf_res_custom_to_vf_module_custom vnfcvf, vnf_resource_customization vnfrc
WHERE vnfcvf.VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID = vmc.MODEL_CUSTOMIZATION_UUID AND vnfrc.MODEL_CUSTOMIZATION_UUID=vnfcvf.VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID);
DELETE FROM vf_module_customization where VNF_RESOURCE_CUSTOMIZATION_ID IS NULL;
-- Migrate Linkage between CVNFC_Customization and VF_Modules
CREATE VIEW vf_module_vnf AS
SELECT vmc.ID as vf_module_id, vnfrc.MODEL_CUSTOMIZATION_UUID as VNF_MODEL_CUST_UUID, vmc.MODEL_CUSTOMIZATION_UUID
FROM vf_module_customization vmc
JOIN vnf_res_custom_to_vf_module_custom vnfcvf ON vmc.MODEL_CUSTOMIZATION_UUID = vnfcvf.VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID
JOIN vnf_resource_customization vnfrc ON vnfrc.MODEL_CUSTOMIZATION_UUID=vnfcvf.VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID;
UPDATE cvnfc_customization as cvnfcc
JOIN vf_module_vnf AS vfmvnf
ON vfmvnf.model_customization_uuid = cvnfcc.VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID
AND cvnfcc.VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID=vfmvnf.VNF_MODEL_CUST_UUID
SET cvnfcc.VF_MODULE_CUSTOMIZATION_ID = vfmvnf.vf_module_id;
DROP VIEW vf_module_vnf;
ALTER TABLE cvnfc_customization
ADD CONSTRAINT fk_cvnfc_customization_to_vf_module_resource_customization FOREIGN KEY (VF_MODULE_CUSTOMIZATION_ID)
REFERENCES vf_module_customization(ID)
ON DELETE CASCADE
ON UPDATE CASCADE;
-- rename/clean up crazy table
ALTER TABLE vnf_vfmodule_cvnfc_configuration_customization DROP VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID;
ALTER TABLE vnf_vfmodule_cvnfc_configuration_customization DROP VF_MODULE_MODEL_CUSTOMIZATION_UUID;
RENAME TABLE vnf_vfmodule_cvnfc_configuration_customization TO cvnfc_configuration_customization;
-- DROP Tables/columns no longer used
ALTER TABLE cvnfc_customization DROP VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID;
ALTER TABLE cvnfc_customization DROP VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID;
ALTER TABLE vnf_resource_customization DROP instance_group_model_uuid;
-- Remove orphaned records, that hav ebeen migrated
DROP TABLE vnf_res_custom_to_vf_module_custom;
DROP TABLE vnf_resource_customization_to_service;
ALTER TABLE vnf_resource_customization
ADD UNIQUE INDEX UK_vnf_resource_customization (MODEL_CUSTOMIZATION_UUID ASC, SERVICE_MODEL_UUID ASC);
-- Additional Clean Up
ALTER TABLE vnf_resource_customization
CHANGE COLUMN `ID` `ID` INT(13) NOT NULL AUTO_INCREMENT FIRST,
ADD INDEX `vnf_resource_customization_mod_cust_uuid_idx` (`MODEL_CUSTOMIZATION_UUID` ASC);
ALTER TABLE service_proxy_customization
DROP INDEX fk_service_proxy_customization__serv_prox_to_serv,
DROP INDEX UK_service_proxy_customization;
ALTER TABLE vf_module_customization
ADD CONSTRAINT fk_vf_module_customization_to_vnf_resource_customization FOREIGN KEY (VNF_RESOURCE_CUSTOMIZATION_ID)
REFERENCES vnf_resource_customization(ID)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE collection_resource
CHANGE COLUMN DESCRIPTION DESCRIPTION VARCHAR(1200) NULL DEFAULT NULL;
ALTER TABLE building_block_detail
CHANGE COLUMN BUILDING_BLOCK_NAME BUILDING_BLOCK_NAME VARCHAR(200) NOT NULL;
ALTER TABLE vnfc_instance_group_customization CHANGE COLUMN VNF_RESOURCE_CUSTOMIZATION_ID VNF_RESOURCE_CUSTOMIZATION_ID INT(13) NOT NULL;
-- clean up before making FK null
DELETE from cvnfc_customization where vf_module_customization_id is NULL;
ALTER TABLE cvnfc_customization CHANGE COLUMN VF_MODULE_CUSTOMIZATION_ID VF_MODULE_CUSTOMIZATION_ID INT(13) NOT NULL;
ALTER TABLE vf_module_customization CHANGE COLUMN VNF_RESOURCE_CUSTOMIZATION_ID VNF_RESOURCE_CUSTOMIZATION_ID INT(13) NOT NULL;
ALTER TABLE cvnfc_configuration_customization CHANGE COLUMN CVNFC_CUSTOMIZATION_ID CVNFC_CUSTOMIZATION_ID INT(11) NOT NULL;
ALTER TABLE cvnfc_configuration_customization
ADD CONSTRAINT fk_cvnfc_config_custom__cvnfc_cust1 FOREIGN KEY (CVNFC_CUSTOMIZATION_ID)
REFERENCES cvnfc_customization(ID)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE vf_module_customization
CHANGE COLUMN ID ID INT(13) NOT NULL AUTO_INCREMENT FIRST,
ADD INDEX vf_module_customization_model_cust_uuid_idx (MODEL_CUSTOMIZATION_UUID ASC);
|
