Fix schema mismatch

Updated MySQL-Catalog-schema.sql to match the Hibernate class
definitions.  Restored the scripts that add default values for
CREATION_TIMESTAMP columns.

Issue: SO-180
Change-Id: Ie76059f4c2497060a4fba8744e974b29d33b16f1
Signed-off-by: Rob Daugherty <rd472p@att.com>

4 files changed, 34 insertions, 4 deletions

diff --git a/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/bulkload-files/default/create_mso_db-default.sql b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/bulkload-files/default/create_mso_db-default.sql
index 7df7e29..ad1646f 100644
--- a/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/bulkload-files/default/create_mso_db-default.sql
+++ b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/bulkload-files/default/create_mso_db-default.sql
@@ -16,6 +16,7 @@ CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mso_requests` /*!40100 DEFAULT CHARACT
 USE `mso_requests`;
 
 SOURCE ../../main-schemas/MySQL-Requests-schema.sql
+SOURCE ../../sub-sql-files/site_status_updated_timestamp.sql
 
 --
 -- Current Database: `mso_catalog`
@@ -28,6 +29,7 @@ CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mso_catalog` /*!40100 DEFAULT CHARACTE
 USE `mso_catalog`;
 
 SOURCE ../../main-schemas/MySQL-Catalog-schema.sql
+SOURCE ../../sub-sql-files/catalog_timestamp_mso_db.sql
 
 LOCK TABLES `NETWORK_RECIPE` WRITE;
 /*!40000 ALTER TABLE `NETWORK_RECIPE` DISABLE KEYS */;
diff --git a/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/main-schemas/MySQL-Catalog-schema.sql b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/main-schemas/MySQL-Catalog-schema.sql
index b825361..7503fa6 100644
--- a/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/main-schemas/MySQL-Catalog-schema.sql
+++ b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/main-schemas/MySQL-Catalog-schema.sql
@@ -29,7 +29,7 @@
 
     alter table VNF_RES_CUSTOM_TO_VF_MODULE_CUSTOM 
         drop 
-        foreign key FK_6tdyg2ib5eguh4k2qgofh4fj7;
+        foreign key FK_4p0consmgggrkf4qj1onx8g5j;
 
     drop table if exists ALLOTTED_RESOURCE;
 
@@ -409,7 +409,6 @@
         VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID varchar(200) not null,
         VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID varchar(200) not null,
         CREATION_TIMESTAMP datetime not null,
-        VNF_RESOURCE_MODEL_UUID varchar(200) not null,
         primary key (VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID, VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID)
     );
 
@@ -467,6 +466,6 @@
         references VNF_RESOURCE (MODEL_UUID);
 
     alter table VNF_RES_CUSTOM_TO_VF_MODULE_CUSTOM 
-        add constraint FK_6tdyg2ib5eguh4k2qgofh4fj7 
-        foreign key (VNF_RESOURCE_MODEL_UUID) 
+        add constraint FK_4p0consmgggrkf4qj1onx8g5j 
+        foreign key (VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID) 
         references VNF_RESOURCE_CUSTOMIZATION (MODEL_CUSTOMIZATION_UUID);
diff --git a/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/sub-sql-files/catalog_timestamp_mso_db.sql b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/sub-sql-files/catalog_timestamp_mso_db.sql
new file mode 100644
index 0000000..cf2ce7f
--- /dev/null
+++ b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/sub-sql-files/catalog_timestamp_mso_db.sql
@@ -0,0 +1,28 @@
+USE `mso_catalog`;
+
+# Add DEFAULT value for CREATION_TIMESTAMP columns
+
+ALTER TABLE ALLOTTED_RESOURCE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE ALLOTTED_RESOURCE_CUSTOMIZATION MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE HEAT_ENVIRONMENT MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE HEAT_FILES MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE HEAT_TEMPLATE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE MODEL MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE MODEL_RECIPE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE NETWORK_RECIPE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE NETWORK_RESOURCE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE NETWORK_RESOURCE_CUSTOMIZATION MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE SERVICE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE SERVICE_RECIPE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE SERVICE_TO_ALLOTTED_RESOURCES MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE SERVICE_TO_NETWORKS MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE SERVICE_TO_RESOURCE_CUSTOMIZATIONS MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE TOSCA_CSAR MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE VF_MODULE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE VF_MODULE_CUSTOMIZATION MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE VNF_COMPONENTS MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE VNF_COMPONENTS_RECIPE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE VNF_RECIPE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE VNF_RESOURCE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE VNF_RESOURCE_CUSTOMIZATION MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
+ALTER TABLE VNF_RES_CUSTOM_TO_VF_MODULE_CUSTOM MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
diff --git a/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/sub-sql-files/site_status_updated_timestamp.sql b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/sub-sql-files/site_status_updated_timestamp.sql
new file mode 100644
index 0000000..3b2de4c
--- /dev/null
+++ b/volumes/mariadb/docker-entrypoint-initdb.d/db-sql-scripts/sub-sql-files/site_status_updated_timestamp.sql
@@ -0,0 +1 @@
+ALTER TABLE SITE_STATUS MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;