adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7__Use_ID_Configuration_Customization.sql

   1 USE catalogdb;
   2
   3 /* Drop existing foreign key */
   4 ALTER TABLE `catalogdb`.`configuration_customization`
   5 DROP FOREIGN KEY IF EXISTS `fk_configuration_customization__configuration_customization1`;
   6
   7 ALTER TABLE `catalogdb`.`configuration_customization`
   8 DROP FOREIGN KEY IF EXISTS `fk_configuration_resource_customization__configuration_resour1`;
   9 /* Drop existing index */
  10 ALTER TABLE `catalogdb`.`configuration_customization`
  11 DROP INDEX IF EXISTS `fk_configuration_customization__configuration_customization_idx` ;
  12
  13 /* Create a new table */
  14 CREATE TABLE `tmp_configuration_customization` (
  15     `ID` INT(11) NOT NULL AUTO_INCREMENT,
  16     `MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL,
  17     `MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL,
  18     `CONFIGURATION_TYPE` VARCHAR(200) DEFAULT NULL,
  19     `CONFIGURATION_ROLE` VARCHAR(200) DEFAULT NULL,
  20     `CONFIGURATION_FUNCTION` VARCHAR(200) DEFAULT NULL,
  21     `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  22     `CONFIGURATION_MODEL_UUID` VARCHAR(200) NOT NULL,
  23     `SERVICE_PROXY_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) DEFAULT NULL,
  24         `CONFIGURATION_CUSTOMIZATION_MODEL_CUSTOMIZATION_ID` int(11) DEFAULT NULL,
  25     `SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL,
  26     PRIMARY KEY (`ID`) ,
  27     KEY `fk_configuration_customization__configuration_idx` (`CONFIGURATION_MODEL_UUID`),
  28     KEY `fk_configuration_customization__service_idx` (`SERVICE_MODEL_UUID`),
  29         UNIQUE KEY `uk_configuration_customization`  (`MODEL_CUSTOMIZATION_UUID` ASC, `SERVICE_MODEL_UUID` ASC),
  30         CONSTRAINT `fk_configuration_customization__configuration1` FOREIGN KEY (`CONFIGURATION_MODEL_UUID`)
  31         REFERENCES `configuration` (`MODEL_UUID`)
  32         ON DELETE CASCADE ON UPDATE CASCADE,
  33     CONSTRAINT `fk_configuration_customization__service1` FOREIGN KEY (`SERVICE_MODEL_UUID`)
  34         REFERENCES `service` (`MODEL_UUID`)
  35         ON DELETE CASCADE ON UPDATE CASCADE
  36
  37 )  ENGINE=INNODB DEFAULT CHARSET=LATIN1;
  38
  39 /* Migrate the existing data */
  40 INSERT INTO tmp_configuration_customization
  41 (`model_customization_uuid` ,
  42                   `model_instance_name`,
  43                   `configuration_type` ,
  44                   `configuration_role` ,
  45                   `configuration_function` ,
  46                   `creation_timestamp` ,
  47                   `configuration_model_uuid` ,
  48                   `service_proxy_customization_model_customization_uuid` ,
  49                   `service_model_uuid`)
  50 SELECT `config`.`model_customization_uuid`,
  51     `config`.`model_instance_name`,
  52     `config`.`configuration_type`,
  53     `config`.`configuration_role`,
  54     `config`.`configuration_function`,
  55     `config`.`creation_timestamp`,
  56     `config`.`configuration_model_uuid`,
  57     `config`.`service_proxy_customization_model_customization_uuid`,
  58     `svc`.`model_uuid` service_model_uuid FROM
  59     configuration_customization config,
  60     service svc,
  61     configuration_customization_to_service config_svc
  62 WHERE
  63     config_svc.service_model_uuid = svc.model_uuid
  64         AND config_svc.resource_model_customization_uuid = config.model_customization_uuid;
  65
  66 /* Drop the old tables */
  67
  68 DROP TABLE `catalogdb`.`configuration_customization`;
  69
  70 DROP TABLE `catalogdb`.`configuration_customization_to_service`;
  71
  72 /* Rename the table */
  73 RENAME TABLE tmp_configuration_customization TO configuration_customization;
  74
  75