adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.1__ServiceProxyTableConsolidation.sql

   1 use catalogdb;
   2
   3 /* Consolidate Service_Proxy_Customization and Service_Proxy tables into a new table also called Service_Proxy_Customization */
   4
   5 ALTER TABLE  service_proxy_customization  DROP FOREIGN KEY fk_service_proxy_resource_customization__service1;
   6
   7 ALTER TABLE  service_proxy_customization  DROP FOREIGN KEY fk_spr_customization__service_proxy_resource1;
   8
   9 ALTER TABLE configuration_customization DROP FOREIGN KEY fk_configuration_customization__service_proxy_customization1;
  10
  11 CREATE TABLE IF NOT EXISTS `service_proxy_customization_temp` (
  12   `MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL,
  13   `MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL,
  14   `MODEL_UUID` VARCHAR(200) NOT NULL,
  15   `MODEL_INVARIANT_UUID` VARCHAR(200) NOT NULL,
  16   `MODEL_VERSION` VARCHAR(20) NOT NULL,
  17   `MODEL_NAME` VARCHAR(200) NOT NULL,
  18   `TOSCA_NODE_TYPE` VARCHAR(200) NOT NULL,
  19   `DESCRIPTION` VARCHAR(1200) NULL,
  20   `SOURCE_SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL,
  21   `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  22   PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`),
  23   INDEX `fk_service_proxy_customization__service1_idx` (`SOURCE_SERVICE_MODEL_UUID` ASC),
  24   UNIQUE INDEX `UK_service_proxy_customization` (`MODEL_CUSTOMIZATION_UUID` ASC),
  25   INDEX `fk_service_proxy_customization__serv_prox_to_serv` (`MODEL_CUSTOMIZATION_UUID` ASC),
  26   CONSTRAINT `fk_service_proxy_resource_customization__service1`
  27     FOREIGN KEY (`SOURCE_SERVICE_MODEL_UUID`)
  28     REFERENCES `catalogdb`.`service` (`MODEL_UUID`)
  29     ON DELETE CASCADE
  30     ON UPDATE CASCADE)
  31 ENGINE = InnoDB
  32 AUTO_INCREMENT = 20654
  33 DEFAULT CHARACTER SET = latin1;
  34
  35 INSERT INTO catalogdb.service_proxy_customization_temp (model_customization_uuid,model_instance_name,model_uuid,model_invariant_uuid,model_version,model_name,tosca_node_type,description,source_service_model_uuid)
  36 SELECT T1.model_customization_uuid, T1.model_instance_name,T2.model_uuid, T2.model_invariant_uuid, T2.model_version, T2.model_name, T1.tosca_node_type, T2.description, T1.source_service_model_uuid
  37   FROM catalogdb.service_proxy_customization T1
  38   JOIN catalogdb.service_proxy T2 ON T1.service_proxy_model_uuid = T2.model_uuid;
  39
  40 DROP TABLE service_proxy_customization;
  41
  42 DROP TABLE service_proxy;
  43
  44 RENAME TABLE service_proxy_customization_temp TO service_proxy_customization;