diff options
Diffstat (limited to 'adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V2.0.0__Modify_Resource_Relationships.sql')
-rw-r--r-- | adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V2.0.0__Modify_Resource_Relationships.sql | 247 |
1 files changed, 247 insertions, 0 deletions
diff --git a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V2.0.0__Modify_Resource_Relationships.sql b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V2.0.0__Modify_Resource_Relationships.sql new file mode 100644 index 0000000000..b141784e8d --- /dev/null +++ b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V2.0.0__Modify_Resource_Relationships.sql @@ -0,0 +1,247 @@ + +USE catalogdb; + +DELETE from service where model_uuid in ( SELECT SERVICE_MODEL_UUID +FROM service_to_resource_customizations +GROUP BY +SERVICE_MODEL_UUID, resource_model_customization_uuid +HAVING COUNT(*) > 1); + + +CREATE TABLE IF NOT EXISTS external_service_to_internal_model_mapping ( +id INT(11) NOT NULL, +SERVICE_NAME VARCHAR(200) NOT NULL, +PRODUCT_FLAVOR VARCHAR(200) NULL, +SUBSCRIPTION_SERVICE_TYPE VARCHAR(200) NOT NULL, +SERVICE_MODEL_UUID VARCHAR(200) NOT NULL, +PRIMARY KEY (id), +UNIQUE INDEX UK_external_service_to_internal_model_mapping +(SERVICE_NAME ASC, PRODUCT_FLAVOR ASC, SERVICE_MODEL_UUID ASC)); + +CREATE TABLE IF NOT EXISTS `collection_resource` ( + MODEL_UUID varchar(200) NOT NULL, + MODEL_NAME varchar(200) NOT NULL, + MODEL_INVARIANT_UUID varchar(200) NOT NULL, + MODEL_VERSION varchar(20) NOT NULL, + TOSCA_NODE_TYPE varchar(200) NOT NULL, + DESCRIPTION varchar(200), + CREATION_TIMESTAMP datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`MODEL_UUID`) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `collection_resource_customization` ( + MODEL_CUSTOMIZATION_UUID varchar(200) NOT NULL, + MODEL_INSTANCE_NAME varchar(200) NOT NULL, + ROLE varchar(200) NOT NULL, + PRIMARY_TYPE varchar(200) NOT NULL, + FUNCTION varchar(200) NOT NULL, + SUBINTERFACE_NETWORK_QUANTITY INT, + COLLECTION_RESOURCE_TYPE varchar(200) NOT NULL, + CREATION_TIMESTAMP datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + CR_MODEL_UUID varchar(200) NOT NULL, + PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `instance_group` ( + MODEL_UUID varchar(200) NOT NULL, + MODEL_NAME varchar(200) NOT NULL, + MODEL_INVARIANT_UUID varchar(200) NOT NULL, + MODEL_VERSION varchar(20) NOT NULL, + TOSCA_NODE_TYPE varchar(200) DEFAULT NULL, + ROLE varchar(200) NOT NULL, + PRIMARY_TYPE varchar(200) NOT NULL, + FUNCTION varchar(200) NOT NULL, + DESCRIPTION varchar(200), + CREATION_TIMESTAMP datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + CR_MODEL_UUID varchar(200) NOT NULL, + INSTANCE_GROUP_TYPE varchar(200) NOT NULL, + PRIMARY KEY (`MODEL_UUID`) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; + + CREATE TABLE IF NOT EXISTS `catalogdb`.`configuration` + ( `MODEL_UUID` VARCHAR(200) NOT NULL, + `MODEL_INVARIANT_UUID` VARCHAR(200) NOT NULL, + `MODEL_VERSION` VARCHAR(20) NOT NULL, + `MODEL_NAME` VARCHAR(200) NOT NULL, + `TOSCA_NODE_TYPE` VARCHAR(200) NOT NULL, + `DESCRIPTION` VARCHAR(1200) NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`MODEL_UUID`)) + ENGINE = InnoDB AUTO_INCREMENT = 20654 + DEFAULT CHARACTER SET = latin1; + + CREATE TABLE IF NOT EXISTS `catalogdb`.`service_proxy` ( + `MODEL_UUID` VARCHAR(200) NOT NULL, + `MODEL_INVARIANT_UUID` VARCHAR(200) NOT NULL, + `MODEL_VERSION` VARCHAR(20) NOT NULL, + `MODEL_NAME` VARCHAR(200) NOT NULL, + `DESCRIPTION` VARCHAR(1200) NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`MODEL_UUID`)) + ENGINE = InnoDB AUTO_INCREMENT = 20654 + DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `service_proxy_customization` ( +`MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, +`MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL, +`TOSCA_NODE_TYPE` VARCHAR(200) NOT NULL, +`SOURCE_SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL, +`CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, +`SERVICE_PROXY_MODEL_UUID` VARCHAR(200) NOT NULL, +PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`), +INDEX `fk_service_proxy_customization__service_proxy1_idx` (`SERVICE_PROXY_MODEL_UUID` ASC), +INDEX `fk_service_proxy_customization__service1_idx` (`SOURCE_SERVICE_MODEL_UUID` ASC), +CONSTRAINT`fk_spr_customization__service_proxy_resource1` +FOREIGN KEY (`SERVICE_PROXY_MODEL_UUID`) REFERENCES `catalogdb`.`service_proxy` (`MODEL_UUID`) +ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `fk_service_proxy_resource_customization__service1` +FOREIGN KEY (`SOURCE_SERVICE_MODEL_UUID`) REFERENCES `catalogdb`.`service` +(`MODEL_UUID`) ON DELETE CASCADE ON UPDATE CASCADE) +ENGINE = InnoDB +AUTO_INCREMENT = 20654 +DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `catalogdb`.`configuration_customization` ( +`MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, +`MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL, +`CONFIGURATION_TYPE` VARCHAR(200) NULL, +`CONFIGURATION_ROLE` VARCHAR(200) NULL, +`CONFIGURATION_FUNCTION` VARCHAR(200) NULL, +`CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, +`CONFIGURATION_MODEL_UUID` VARCHAR(200) NOT NULL, +`SERVICE_PROXY_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NULL, +`CONFIGURATION_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NULL, +PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`), +INDEX `fk_configuration_customization__configuration_idx` (`CONFIGURATION_MODEL_UUID` ASC), +INDEX `fk_configuration_customization__service_proxy_customization_idx` +(`SERVICE_PROXY_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID` ASC), +INDEX `fk_configuration_customization__configuration_customization_idx` +(`CONFIGURATION_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID` ASC), +CONSTRAINT `fk_configuration_resource_customization__configuration_resour1` +FOREIGN KEY (`CONFIGURATION_MODEL_UUID`) REFERENCES `catalogdb`.`configuration` (`MODEL_UUID`) +ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `fk_configuration_customization__service_proxy_customization1` FOREIGN +KEY (`SERVICE_PROXY_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID`) REFERENCES +`catalogdb`.`service_proxy_customization` (`MODEL_CUSTOMIZATION_UUID`) +ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT +`fk_configuration_customization__configuration_customization1` FOREIGN +KEY (`CONFIGURATION_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID`) REFERENCES +`catalogdb`.`configuration_customization` (`MODEL_CUSTOMIZATION_UUID`) +ON DELETE CASCADE ON UPDATE CASCADE) +ENGINE = InnoDB +AUTO_INCREMENT =20654 +DEFAULT CHARACTER SET = latin1; + + +CREATE TABLE `service_proxy_customization_to_service` ( + `SERVICE_MODEL_UUID` varchar(200) NOT NULL, + `RESOURCE_MODEL_CUSTOMIZATION_UUID` varchar(200) NOT NULL, + PRIMARY KEY (`SERVICE_MODEL_UUID`,`RESOURCE_MODEL_CUSTOMIZATION_UUID`) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; + + +CREATE TABLE `configuration_customization_to_service` ( + `SERVICE_MODEL_UUID` varchar(200) NOT NULL, + `RESOURCE_MODEL_CUSTOMIZATION_UUID` varchar(200) NOT NULL, + PRIMARY KEY (`SERVICE_MODEL_UUID`,`RESOURCE_MODEL_CUSTOMIZATION_UUID`) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; + + +CREATE TABLE IF NOT EXISTS `collection_resource_customization_to_service` ( + `SERVICE_MODEL_UUID` varchar(200) NOT NULL, + `RESOURCE_MODEL_CUSTOMIZATION_UUID` varchar(200) NOT NULL, + PRIMARY KEY (`SERVICE_MODEL_UUID`,`RESOURCE_MODEL_CUSTOMIZATION_UUID`) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; + + +CREATE TABLE `network_resource_customization_to_service` ( + `SERVICE_MODEL_UUID` varchar(200) NOT NULL, + `RESOURCE_MODEL_CUSTOMIZATION_UUID` varchar(200) NOT NULL, + PRIMARY KEY (`SERVICE_MODEL_UUID`,`RESOURCE_MODEL_CUSTOMIZATION_UUID`) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE `vnf_resource_customization_to_service` ( + `SERVICE_MODEL_UUID` varchar(200) NOT NULL, + `RESOURCE_MODEL_CUSTOMIZATION_UUID` varchar(200) NOT NULL, + PRIMARY KEY (`SERVICE_MODEL_UUID`,`RESOURCE_MODEL_CUSTOMIZATION_UUID`) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE `allotted_resource_customization_to_service` ( + `SERVICE_MODEL_UUID` varchar(200) NOT NULL, + `RESOURCE_MODEL_CUSTOMIZATION_UUID` varchar(200) NOT NULL, + PRIMARY KEY (`SERVICE_MODEL_UUID`,`RESOURCE_MODEL_CUSTOMIZATION_UUID`) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; + + + + +CREATE TABLE IF NOT EXISTS `collection_network_resource_customization` ( +`MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, +`MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL, +`NETWORK_TECHNOLOGY` VARCHAR(45) NULL, +`NETWORK_TYPE` VARCHAR(45) NULL, +`NETWORK_ROLE` VARCHAR(200) NULL, +`NETWORK_SCOPE` VARCHAR(45) NULL, +`CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, +`NETWORK_RESOURCE_MODEL_UUID` VARCHAR(200) NOT NULL, `INSTANCE_GROUP_MODEL_UUID` VARCHAR(200) NULL, +`CRC_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, PRIMARY KEY +(`MODEL_CUSTOMIZATION_UUID`, `CRC_MODEL_CUSTOMIZATION_UUID`), +INDEX `fk_collection_net_resource_customization__network_resource1_idx` +(`NETWORK_RESOURCE_MODEL_UUID` ASC), INDEX +`fk_collection_net_resource_customization__instance_group1_idx` +(`INSTANCE_GROUP_MODEL_UUID` ASC), INDEX +`fk_col_net_res_customization__collection_res_customization_idx` +(`CRC_MODEL_CUSTOMIZATION_UUID` ASC), CONSTRAINT +`fk_collection_net_resource_customization__network_resource10` FOREIGN +KEY (`NETWORK_RESOURCE_MODEL_UUID`) REFERENCES +`catalogdb`.`network_resource` (`MODEL_UUID`) ON DELETE CASCADE ON +UPDATE CASCADE, CONSTRAINT +`fk_collection_net_resource_customization__instance_group10` FOREIGN KEY +(`INSTANCE_GROUP_MODEL_UUID`) REFERENCES `instance_group` +(`MODEL_UUID`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT +`fk_collection_network_resource_customization__collection_reso1` FOREIGN +KEY (`CRC_MODEL_CUSTOMIZATION_UUID`) REFERENCES +`collection_resource_customization` +(`MODEL_CUSTOMIZATION_UUID`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE += InnoDB DEFAULT CHARACTER SET = latin1; + + +CREATE TABLE IF NOT EXISTS `northbound_request_ref_lookup` ( +`id` INT(11) NOT NULL AUTO_INCREMENT, +`REQUEST_SCOPE` VARCHAR(200) NOT NULL, +`MACRO_ACTION` VARCHAR(200) NOT NULL, +`ACTION` VARCHAR(200) NOT NULL, +`IS_ALACARTE` TINYINT(1) NOT NULL DEFAULT 0, +`MIN_API_VERSION` DOUBLE NOT NULL, +`MAX_API_VERSION` DOUBLE NULL, +PRIMARY KEY (`id`), +UNIQUE INDEX `UK_northbound_request_ref_lookup` (`MIN_API_VERSION` ASC, `REQUEST_SCOPE` ASC, `ACTION` ASC, `IS_ALACARTE` ASC, `MACRO_ACTION` ASC)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `orchestration_flow_reference` ( +`id` INT(11) NOT NULL AUTO_INCREMENT, +`COMPOSITE_ACTION` VARCHAR(200) NOT NULL, +`SEQ_NO` INT(11) NOT NULL, +`FLOW_NAME` VARCHAR(200) NOT NULL, +`FLOW_VERSION` DOUBLE NOT NULL, +`NB_REQ_REF_LOOKUP_ID` INT(11) NOT NULL, +PRIMARY KEY (`id`), +INDEX `fk_orchestration_flow_reference__northbound_req_ref_look_idx` (`NB_REQ_REF_LOOKUP_ID` ASC), +UNIQUE INDEX `UK_orchestration_flow_reference` (`COMPOSITE_ACTION` ASC, `FLOW_NAME` ASC, `SEQ_NO` ASC, `NB_REQ_REF_LOOKUP_ID` ASC), +CONSTRAINT `fk_orchestration_flow_reference__northbound_request_ref_look1` +FOREIGN KEY (`NB_REQ_REF_LOOKUP_ID`) REFERENCES `northbound_request_ref_lookup` (`id`) +ON DELETE CASCADE ON UPDATE CASCADE) +ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `rainy_day_handler_macro` ( +`id` INT(11) NOT NULL AUTO_INCREMENT, +`FLOW_NAME` VARCHAR(200) NOT NULL, +`SERVICE_TYPE` VARCHAR(200) NOT NULL, +`VNF_TYPE` VARCHAR(200) NOT NULL, +`ERROR_CODE` VARCHAR(200) NOT NULL, +`WORK_STEP` VARCHAR(200) NOT NULL, +`POLICY` VARCHAR(200) NOT NULL, +PRIMARY KEY (`id`)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; |