fix catalogdb cvfnc customization

Hibernate entities were all wrong and needed
a complete rewrite.
The scope kept growing and ended up touching a
lot more of the code than originally planned.
These changes make the db interactions much easier
and more straightforward from java.

The flyway script order had to change to make this
migration possible.

Change-Id: I6ce363a385661300eddb11923ac6a80538edaddc
Issue-ID: SO-1710
Signed-off-by: Benjamin, Max (mb388a) <mb388a@us.att.com>

7 files changed, 165 insertions, 46 deletions

diff --git a/adapters/mso-catalog-db-adapter/src/main/java/org/onap/so/adapters/catalogdb/CatalogDBApplication.java b/adapters/mso-catalog-db-adapter/src/main/java/org/onap/so/adapters/catalogdb/CatalogDBApplication.java
index 93e396bb94..374e597116 100644
--- a/adapters/mso-catalog-db-adapter/src/main/java/org/onap/so/adapters/catalogdb/CatalogDBApplication.java
+++ b/adapters/mso-catalog-db-adapter/src/main/java/org/onap/so/adapters/catalogdb/CatalogDBApplication.java
@@ -25,7 +25,7 @@ import org.springframework.boot.autoconfigure.SpringBootApplication;
 import org.springframework.boot.autoconfigure.domain.EntityScan;
 import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
 
-@SpringBootApplication(scanBasePackages = {"org.onap.so.adapters.catalogdb", "org.onap.so.db.catalog.client","org.onap.so.logging.jaxrs.filter","org.onap.so.logging.spring.interceptor"})
+@SpringBootApplication(scanBasePackages = {"org.onap.so.adapters.catalogdb", "org.onap.so.db.catalog.client","org.onap.so.logging.jaxrs.filter","org.onap.so.logging.spring.interceptor","org.onap.so.client","org.onap.so.configuration"})
 @EnableJpaRepositories("org.onap.so.db.catalog.data.repository")
 @EntityScan("org.onap.so.db.catalog.beans")
 public class CatalogDBApplication {
diff --git a/adapters/mso-catalog-db-adapter/src/main/java/org/onap/so/adapters/catalogdb/rest/CatalogDbAdapterRest.java b/adapters/mso-catalog-db-adapter/src/main/java/org/onap/so/adapters/catalogdb/rest/CatalogDbAdapterRest.java
index dbb168bd15..5009dce020 100644
--- a/adapters/mso-catalog-db-adapter/src/main/java/org/onap/so/adapters/catalogdb/rest/CatalogDbAdapterRest.java
+++ b/adapters/mso-catalog-db-adapter/src/main/java/org/onap/so/adapters/catalogdb/rest/CatalogDbAdapterRest.java
@@ -22,36 +22,20 @@
 
 package org.onap.so.adapters.catalogdb.rest;
 
-/*
-Create an initial query to retrieve a VNF Resource definition (including a list of possible module types)
-within the context of a given service. Input is a vnf resource model customization ID (new field for 1702),
-or a composite key (from 1610) of service name, service version, vnf instance name
 
-Returns a structure (JSON?) containing VNF RESOURCE attributes, plus a list of VF Module structures.
 
-Query a NETWORK_RESOURCE from the MSO Catalog, based on a networkModelCustomizationUUID (new for 1702),
-a network type (unique type identifier in 1610), or based on network role within a service.
-
-Create Adapter framework for access to Catalog DB, including connection management,
-login/password access, transaction logic, etc. This can be modeled after the Request DB Adapter
-
-Update the MSO Catalog DB schema to include the new fields defined in this user story.
-
-Note that the resourceModelCustomizationUUID (or vfModuleModelCustomizationUUID) will be unique keys (indexes)
-on the VNF_RESOURCE and VF_MODULE tables respectively.
-The previously constructed "vnf-type" and "vf-module-type" field may continue to be populated,
-but should no longer be needed and can deprecate in future release.
-
-For migration, a new randomly generated UUID field may be generated for the *ModelCustomizationUUID" fields
-until such time that the model is redistributed from ASDC.
-
-All other fields Check with Mike Z for appropriate value for the vfModuleLabel.
-We might be able to derive it's value from the current vnf-type (using the "middle" piece that identifies the module type).
-
-min and initial counts can be 0. max can be null to indicate no maximum.
+import java.util.ArrayList;
+import java.util.List;
 
-Once the network-level distribution artifacts are defined, similar updates can be made to the NETWORK_RESOURCE table.
- */
+import javax.ws.rs.GET;
+import javax.ws.rs.Path;
+import javax.ws.rs.PathParam;
+import javax.ws.rs.Produces;
+import javax.ws.rs.QueryParam;
+import javax.ws.rs.core.GenericEntity;
+import javax.ws.rs.core.HttpHeaders;
+import javax.ws.rs.core.MediaType;
+import javax.ws.rs.core.Response;
 
 import org.apache.http.HttpStatus;
 import org.onap.so.adapters.catalogdb.catalogrest.CatalogQuery;
@@ -73,7 +57,6 @@ import org.onap.so.db.catalog.beans.Service;
 import org.onap.so.db.catalog.beans.ToscaCsar;
 import org.onap.so.db.catalog.beans.VfModule;
 import org.onap.so.db.catalog.beans.VfModuleCustomization;
-import org.onap.so.db.catalog.beans.VnfRecipe;
 import org.onap.so.db.catalog.beans.VnfResource;
 import org.onap.so.db.catalog.beans.VnfResourceCustomization;
 import org.onap.so.db.catalog.data.repository.AllottedResourceCustomizationRepository;
@@ -95,23 +78,7 @@ import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.stereotype.Component;
 import org.springframework.transaction.annotation.Transactional;
 
-import javax.ws.rs.GET;
-import javax.ws.rs.Path;
-import javax.ws.rs.PathParam;
-import javax.ws.rs.Produces;
-import javax.ws.rs.QueryParam;
-import javax.ws.rs.core.GenericEntity;
-import javax.ws.rs.core.HttpHeaders;
-import javax.ws.rs.core.MediaType;
-import javax.ws.rs.core.Response;
-import java.util.ArrayList;
-import java.util.List;
 
-/**
- * This class services calls to the REST interface for VF Modules (http://host:port/ecomp/mso/catalog/v1)
- * Both XML and JSON can be produced/consumed.  Set Accept: and Content-Type: headers appropriately.  XML is the default.
- * Requests respond synchronously only
- */
 @Path("/{version: v[0-9]+}")
 @Component
 public class CatalogDbAdapterRest {
@@ -160,7 +127,6 @@ public class CatalogDbAdapterRest {
     public Response respond(String version, int respStatus, boolean isArray, CatalogQuery qryResp) {
         return Response
                 .status(respStatus)
-                //.entity(new GenericEntity<QueryServiceVnfs>(qryResp) {})
                 .entity(qryResp.toJsonString(version, isArray))
                 .header(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_JSON)
                 .build();
diff --git a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.6.1__Use_ID_To_Identify_Tables.sql b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.6.1__Use_ID_To_Identify_Tables.sql
new file mode 100644
index 0000000000..4474d265db
--- /dev/null
+++ b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.6.1__Use_ID_To_Identify_Tables.sql
@@ -0,0 +1,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);
diff --git a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V4.22__AddResourceInput.sql b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.6.2__AddResourceInput.sql
index 10d8789328..10d8789328 100644
--- a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V4.22__AddResourceInput.sql
+++ b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.6.2__AddResourceInput.sql
diff --git a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V4.23__ChangeResourceInputLength.sql b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.6.3__ChangeResourceInputLength.sql
index dfb5075674..dfb5075674 100644
--- a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V4.23__ChangeResourceInputLength.sql
+++ b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.6.3__ChangeResourceInputLength.sql
diff --git a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V4.24__UpdateHeatRelatedAttribute.sql b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.6.4__UpdateHeatRelatedAttribute.sql
index b1aefaf56c..b1aefaf56c 100644
--- a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V4.24__UpdateHeatRelatedAttribute.sql
+++ b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.6.4__UpdateHeatRelatedAttribute.sql
diff --git a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.3__AddBluePrintNameVersion.sql b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.6.5__AddBluePrintNameVersion.sql
index 97397df290..97397df290 100644
--- a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.3__AddBluePrintNameVersion.sql
+++ b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.6.5__AddBluePrintNameVersion.sql