[airavata] branch develop updated: AIRAVATA-2872 DB schema migration for adding NOT NULL

[ma...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

machristie pushed a commit to branch develop
in repository https://gitbox.apache.org/repos/asf/airavata.git


The following commit(s) were added to refs/heads/develop by this push:
     new 423a736  AIRAVATA-2872 DB schema migration for adding NOT NULL
423a736 is described below

commit 423a7365e58143f8d666e5dc85d7e67f510b055f
Author: Marcus Christie <ma...@apache.org>
AuthorDate: Tue Nov 6 12:34:09 2018 -0500

    AIRAVATA-2872 DB schema migration for adding NOT NULL
---
 .../next/DeltaScripts/appCatalog_schema_delta.sql  | 33 +++++++++++++++++++++-
 .../experimentCatalog_schema_delta.sql             | 27 ++++++++++++++++++
 ...a_delta.sql => replicaCatalog_schema_delta.sql} | 20 ++++---------
 3 files changed, 64 insertions(+), 16 deletions(-)

diff --git a/modules/registry/release-migration-scripts/next/DeltaScripts/appCatalog_schema_delta.sql b/modules/registry/release-migration-scripts/next/DeltaScripts/appCatalog_schema_delta.sql
index 272ed9b..d711c65 100644
--- a/modules/registry/release-migration-scripts/next/DeltaScripts/appCatalog_schema_delta.sql
+++ b/modules/registry/release-migration-scripts/next/DeltaScripts/appCatalog_schema_delta.sql
@@ -37,4 +37,35 @@ alter table APPLICATION_INTERFACE modify column HAS_OPTIONAL_FILE_INPUTS tinyint
 alter table APPLICATION_DEPLOYMENT modify column EDITABLE_BY_USER tinyint(1);
 alter table BATCH_QUEUE modify column IS_DEFAULT_QUEUE tinyint(1);
 alter table COMPUTE_RESOURCE modify column GATEWAY_USAGE_REPORTING tinyint(1);
-alter table USER_COMPUTE_RESOURCE_PREFERENCE modify column VALIDATED tinyint(1) default 0;
\ No newline at end of file
+alter table USER_COMPUTE_RESOURCE_PREFERENCE modify column VALIDATED tinyint(1) default 0;
+
+-- AIRAVATA-2872: NOT NULL foreign key columsn
+set FOREIGN_KEY_CHECKS=0;
+alter table HOST_ALIAS modify column RESOURCE_ID VARCHAR(255) NOT NULL;
+alter table HOST_IPADDRESS modify column RESOURCE_ID VARCHAR(255) NOT NULL;
+alter table GSISSH_EXPORT modify column SUBMISSION_ID VARCHAR(255) NOT NULL;
+alter table GSISSH_PREJOBCOMMAND modify column SUBMISSION_ID VARCHAR(255) NOT NULL;
+alter table GSISSH_POSTJOBCOMMAND modify column SUBMISSION_ID VARCHAR(255) NOT NULL;
+alter table GLOBUS_GK_ENDPOINT modify column SUBMISSION_ID VARCHAR(255) NOT NULL;
+alter table APPLICATION_DEPLOYMENT modify column APP_MODULE_ID VARCHAR(255) NOT NULL;
+alter table APPLICATION_DEPLOYMENT modify column COMPUTE_HOSTID VARCHAR(255) NOT NULL;
+alter table PREJOB_COMMAND modify column APPDEPLOYMENT_ID VARCHAR(255) NOT NULL;
+alter table POSTJOB_COMMAND modify column APPDEPLOYMENT_ID VARCHAR(255) NOT NULL;
+alter table LIBRARY_PREPAND_PATH modify column DEPLOYMENT_ID VARCHAR(255) NOT NULL;
+alter table LIBRARY_APEND_PATH modify column DEPLOYMENT_ID VARCHAR(255) NOT NULL;
+alter table APP_ENVIRONMENT modify column DEPLOYMENT_ID VARCHAR(255) NOT NULL;
+alter table APP_MODULE_MAPPING modify column INTERFACE_ID VARCHAR(255) NOT NULL;
+alter table APP_MODULE_MAPPING modify column MODULE_ID VARCHAR(255) NOT NULL;
+alter table APPLICATION_INPUT modify column INTERFACE_ID VARCHAR(255) NOT NULL;
+alter table APPLICATION_OUTPUT modify column INTERFACE_ID VARCHAR(255) NOT NULL;
+alter table COMPUTE_RESOURCE_PREFERENCE modify column GATEWAY_ID VARCHAR(255) NOT NULL;
+alter table COMPUTE_RESOURCE_PREFERENCE modify column RESOURCE_ID VARCHAR(255) NOT NULL;
+alter table SSH_ACCOUNT_PROVISIONER_CONFIG modify column GATEWAY_ID VARCHAR(255) NOT NULL;
+alter table SSH_ACCOUNT_PROVISIONER_CONFIG modify column RESOURCE_ID VARCHAR(255) NOT NULL;
+alter table DATA_STORAGE_PREFERENCE modify column GATEWAY_ID VARCHAR(255) NOT NULL;
+alter table WORKFLOW_INPUT modify column WF_TEMPLATE_ID VARCHAR(255) NOT NULL;
+alter table WORKFLOW_OUTPUT modify column WF_TEMPLATE_ID VARCHAR(255) NOT NULL;
+set FOREIGN_KEY_CHECKS=1;
+
+-- AIRAVATA-2872: add missing column
+alter table APP_ENVIRONMENT add column ENV_ORDER INTEGER;
diff --git a/modules/registry/release-migration-scripts/next/DeltaScripts/experimentCatalog_schema_delta.sql b/modules/registry/release-migration-scripts/next/DeltaScripts/experimentCatalog_schema_delta.sql
index 6b649e7..7f083bb 100644
--- a/modules/registry/release-migration-scripts/next/DeltaScripts/experimentCatalog_schema_delta.sql
+++ b/modules/registry/release-migration-scripts/next/DeltaScripts/experimentCatalog_schema_delta.sql
@@ -33,3 +33,30 @@ alter table USER_CONFIGURATION_DATA modify column IS_USE_USER_CR_PREF tinyint(1)
 alter table PROCESS_STATUS modify column TIME_OF_STATE_CHANGE TIMESTAMP(6) DEFAULT NOW(6) ON UPDATE NOW(6);
 alter table TASK_STATUS modify column TIME_OF_STATE_CHANGE TIMESTAMP(6) DEFAULT NOW(6) ON UPDATE NOW(6);
 alter table JOB_STATUS modify column TIME_OF_STATE_CHANGE TIMESTAMP(6) DEFAULT NOW(6) ON UPDATE NOW(6);
+
+-- AIRAVATA-2872: NOT NULL foreign key columsn
+set FOREIGN_KEY_CHECKS=0;
+alter table USERS modify column GATEWAY_ID VARCHAR(255) NOT NULL;
+alter table GATEWAY_WORKER modify column GATEWAY_ID VARCHAR(255) NOT NULL;
+alter table PROJECT modify column GATEWAY_ID VARCHAR(255) NOT NULL;
+alter table PROJECT_USER modify column PROJECT_ID VARCHAR(255) NOT NULL;
+alter table PROJECT_USER modify column USER_NAME VARCHAR(255) NOT NULL;
+alter table EXPERIMENT modify column PROJECT_ID VARCHAR(255) NOT NULL;
+alter table EXPERIMENT_INPUT modify column EXPERIMENT_ID VARCHAR(255) NOT NULL;
+alter table EXPERIMENT_OUTPUT modify column EXPERIMENT_ID VARCHAR(255) NOT NULL;
+alter table EXPERIMENT_STATUS modify column EXPERIMENT_ID VARCHAR(255) NOT NULL;
+alter table EXPERIMENT_ERROR modify column EXPERIMENT_ID VARCHAR(255) NOT NULL;
+alter table USER_CONFIGURATION_DATA modify column EXPERIMENT_ID VARCHAR(255) NOT NULL;
+alter table PROCESS modify column EXPERIMENT_ID VARCHAR(255) NOT NULL;
+alter table PROCESS_INPUT modify column PROCESS_ID VARCHAR(255) NOT NULL;
+alter table PROCESS_OUTPUT modify column PROCESS_ID VARCHAR(255) NOT NULL;
+alter table PROCESS_STATUS modify column PROCESS_ID VARCHAR(255) NOT NULL;
+alter table PROCESS_ERROR modify column PROCESS_ID VARCHAR(255) NOT NULL;
+alter table PROCESS_RESOURCE_SCHEDULE modify column PROCESS_ID VARCHAR(255) NOT NULL;
+alter table TASK modify column PARENT_PROCESS_ID VARCHAR(255) NOT NULL;
+alter table TASK_STATUS modify column TASK_ID VARCHAR(255) NOT NULL;
+alter table TASK_ERROR modify column TASK_ID VARCHAR(255) NOT NULL;
+alter table JOB modify column TASK_ID VARCHAR(255) NOT NULL;
+alter table JOB_STATUS modify column TASK_ID VARCHAR(255) NOT NULL;
+alter table JOB_STATUS modify column JOB_ID VARCHAR(255) NOT NULL;
+set FOREIGN_KEY_CHECKS=1;
diff --git a/modules/registry/release-migration-scripts/next/DeltaScripts/experimentCatalog_schema_delta.sql b/modules/registry/release-migration-scripts/next/DeltaScripts/replicaCatalog_schema_delta.sql
similarity index 51%
copy from modules/registry/release-migration-scripts/next/DeltaScripts/experimentCatalog_schema_delta.sql
copy to modules/registry/release-migration-scripts/next/DeltaScripts/replicaCatalog_schema_delta.sql
index 6b649e7..0be8971 100644
--- a/modules/registry/release-migration-scripts/next/DeltaScripts/experimentCatalog_schema_delta.sql
+++ b/modules/registry/release-migration-scripts/next/DeltaScripts/replicaCatalog_schema_delta.sql
@@ -18,18 +18,8 @@
 -- under the License.
 --
 
--- AIRAVATA-2768
-alter table EXPERIMENT_INPUT modify METADATA VARCHAR(4096);
-
--- AIRAVATA-2820
-alter table TASK drop column TASK_INTERNAL_STORE;
-
--- AIRAVATA-2827: OpenJPA 2.4.3 upgrade, convert BIT -> TINYINT(1)
-alter table PROCESS modify column USE_USER_CR_PREF tinyint(1);
-alter table QUEUE_STATUS modify column QUEUE_UP tinyint(1);
-alter table USER_CONFIGURATION_DATA modify column IS_USE_USER_CR_PREF tinyint(1);
-
--- AIRAVATA-2899
-alter table PROCESS_STATUS modify column TIME_OF_STATE_CHANGE TIMESTAMP(6) DEFAULT NOW(6) ON UPDATE NOW(6);
-alter table TASK_STATUS modify column TIME_OF_STATE_CHANGE TIMESTAMP(6) DEFAULT NOW(6) ON UPDATE NOW(6);
-alter table JOB_STATUS modify column TIME_OF_STATE_CHANGE TIMESTAMP(6) DEFAULT NOW(6) ON UPDATE NOW(6);
+-- AIRAVATA-2872: NOT NULL foreign key columsn
+set FOREIGN_KEY_CHECKS=0;
+alter table DATA_PRODUCT_METADATA modify column PRODUCT_URI VARCHAR(255) NOT NULL;
+alter table DATA_REPLICA_METADATA modify column REPLICA_ID VARCHAR(255) NOT NULL;
+set FOREIGN_KEY_CHECKS=1;