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
|
USE catalogdb;
/* Drop existing foreign key */
ALTER TABLE `catalogdb`.`configuration_customization`
DROP FOREIGN KEY IF EXISTS `fk_configuration_customization__configuration_customization1`;
ALTER TABLE `catalogdb`.`configuration_customization`
DROP FOREIGN KEY IF EXISTS `fk_configuration_resource_customization__configuration_resour1`;
/* Drop existing index */
ALTER TABLE `catalogdb`.`configuration_customization`
DROP INDEX IF EXISTS `fk_configuration_customization__configuration_customization_idx` ;
/* Create a new table */
CREATE TABLE `tmp_configuration_customization` (
`ID` INT(11) NOT NULL AUTO_INCREMENT,
`MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL,
`MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL,
`CONFIGURATION_TYPE` VARCHAR(200) DEFAULT NULL,
`CONFIGURATION_ROLE` VARCHAR(200) DEFAULT NULL,
`CONFIGURATION_FUNCTION` VARCHAR(200) DEFAULT NULL,
`CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`CONFIGURATION_MODEL_UUID` VARCHAR(200) NOT NULL,
`SERVICE_PROXY_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) DEFAULT NULL,
`CONFIGURATION_CUSTOMIZATION_MODEL_CUSTOMIZATION_ID` int(11) DEFAULT NULL,
`SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL,
PRIMARY KEY (`ID`) ,
KEY `fk_configuration_customization__configuration_idx` (`CONFIGURATION_MODEL_UUID`),
KEY `fk_configuration_customization__service_idx` (`SERVICE_MODEL_UUID`),
UNIQUE KEY `uk_configuration_customization` (`MODEL_CUSTOMIZATION_UUID` ASC, `SERVICE_MODEL_UUID` ASC),
CONSTRAINT `fk_configuration_customization__configuration1` FOREIGN KEY (`CONFIGURATION_MODEL_UUID`)
REFERENCES `configuration` (`MODEL_UUID`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_configuration_customization__service1` FOREIGN KEY (`SERVICE_MODEL_UUID`)
REFERENCES `service` (`MODEL_UUID`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=LATIN1;
/* Migrate the existing data */
INSERT INTO tmp_configuration_customization
(`model_customization_uuid` ,
`model_instance_name`,
`configuration_type` ,
`configuration_role` ,
`configuration_function` ,
`creation_timestamp` ,
`configuration_model_uuid` ,
`service_proxy_customization_model_customization_uuid` ,
`service_model_uuid`)
SELECT `config`.`model_customization_uuid`,
`config`.`model_instance_name`,
`config`.`configuration_type`,
`config`.`configuration_role`,
`config`.`configuration_function`,
`config`.`creation_timestamp`,
`config`.`configuration_model_uuid`,
`config`.`service_proxy_customization_model_customization_uuid`,
`svc`.`model_uuid` service_model_uuid FROM
configuration_customization config,
service svc,
configuration_customization_to_service config_svc
WHERE
config_svc.service_model_uuid = svc.model_uuid
AND config_svc.resource_model_customization_uuid = config.model_customization_uuid;
/* Drop the old tables */
DROP TABLE `catalogdb`.`configuration_customization`;
DROP TABLE `catalogdb`.`configuration_customization_to_service`;
/* Rename the table */
RENAME TABLE tmp_configuration_customization TO configuration_customization;
|
