modules/registry/release-migration-scripts/0.18-0.19/DeltaScripts/experimentCatalog_schema_delta.sql

--
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--

use experiment_catalog;

-- AIRAVATA-2768
alter table EXPERIMENT_INPUT modify METADATA VARCHAR(4096);

-- AIRAVATA-2820
alter table TASK drop column IF EXISTS 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 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;

-- AIRAVATA-2953
alter table PROCESS_INPUT modify column METADATA VARCHAR(4096);

-- AIRAVATA-2938: bring database schema into sync with registry-refactoring DB init scripts
DROP TABLE IF EXISTS `COMMUNITY_USER`;
DROP TABLE IF EXISTS `CREDENTIALS`;

ALTER TABLE `EXPERIMENT_INPUT` CHANGE `IS_READ_ONLY` `IS_READ_ONLY` tinyint(1) DEFAULT NULL;
ALTER TABLE `GATEWAY` CHANGE `REQUEST_CREATION_TIME` `REQUEST_CREATION_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `NOTIFICATION` CHANGE `CREATION_DATE` `CREATION_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE `NOTIFICATION` CHANGE `EXPIRATION_DATE` `EXPIRATION_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE `NOTIFICATION` CHANGE `PUBLISHED_DATE` `PUBLISHED_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `PROCESS_INPUT` CHANGE `IS_READ_ONLY` `IS_READ_ONLY` tinyint(1) DEFAULT NULL;
ALTER TABLE `EXPERIMENT` DROP IF EXISTS `ARCHIVE`;
ALTER TABLE `GATEWAY` DROP IF EXISTS `GATEWAY_DOMAIN`;
ALTER TABLE `PROCESS` DROP IF EXISTS `ARCHIVE`;

set FOREIGN_KEY_CHECKS=0;
ALTER TABLE `GATEWAY_WORKER` DROP FOREIGN KEY IF EXISTS `GATEWAY_WORKER_ibfk_2`;
ALTER TABLE `GATEWAY_WORKER` DROP INDEX IF EXISTS `USER_NAME`;
ALTER TABLE `PROJECT_USER` DROP FOREIGN KEY IF EXISTS `PROJECT_USER_ibfk_2`;
ALTER TABLE `PROJECT_USER` DROP INDEX IF EXISTS `USER_NAME`;
ALTER TABLE `PROJECT` DROP FOREIGN KEY IF EXISTS `PROJECT_ibfk_2`;
ALTER TABLE `PROJECT` DROP INDEX IF EXISTS `USER_NAME`;
ALTER TABLE `USERS` ADD CONSTRAINT `USERS_ibfk_1` FOREIGN KEY IF NOT EXISTS (`GATEWAY_ID`) REFERENCES `GATEWAY` (`GATEWAY_ID`) ON DELETE CASCADE;
ALTER TABLE `USERS` ADD PRIMARY KEY IF NOT EXISTS (`GATEWAY_ID`,`USER_NAME`);
ALTER TABLE `USERS` DROP INDEX IF EXISTS `USER_NAME`;
set FOREIGN_KEY_CHECKS=1;

-- AIRAVATA-2978: Add missing columns to PROCESS_RESOURCE_SCHEDULE
ALTER TABLE PROCESS_RESOURCE_SCHEDULE ADD COLUMN IF NOT EXISTS STATIC_WORKING_DIR varchar(255);
ALTER TABLE PROCESS_RESOURCE_SCHEDULE ADD COLUMN IF NOT EXISTS OVERRIDE_ALLOCATION_PROJECT_NUMBER varchar(255);
ALTER TABLE PROCESS_RESOURCE_SCHEDULE ADD COLUMN IF NOT EXISTS OVERRIDE_LOGIN_USER_NAME varchar(255);
ALTER TABLE PROCESS_RESOURCE_SCHEDULE ADD COLUMN IF NOT EXISTS OVERRIDE_SCRATCH_LOCATION varchar(255);

-- AIRAVATA-3029: Add METADATA to EXPERIMENT_OUTPUT and PROCESS_OUTPUT
ALTER TABLE `EXPERIMENT_OUTPUT` ADD COLUMN IF NOT EXISTS METADATA VARCHAR(4096);
ALTER TABLE `PROCESS_OUTPUT` ADD COLUMN IF NOT EXISTS METADATA VARCHAR(4096);

ALTER TABLE `PROCESS` ADD COLUMN IF NOT EXISTS `GROUP_RESOURCE_PROFILE_ID` varchar(255) DEFAULT NULL;
ALTER TABLE `USER_CONFIGURATION_DATA` ADD IF NOT EXISTS `GROUP_RESOURCE_PROFILE_ID` varchar(255) DEFAULT NULL;

-- Copy OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET from profile_service.GATEWAY
update GATEWAY g
inner join profile_service.GATEWAY pg
on pg.GATEWAY_ID = g.GATEWAY_ID
set g.OAUTH_CLIENT_ID = pg.OAUTH_CLIENT_ID,
    g.OAUTH_CLIENT_SECRET = pg.OAUTH_CLIENT_SECRET
where
    g.OAUTH_CLIENT_ID is null
    or g.OAUTH_CLIENT_SECRET is null;

-- AIRAVATA-3303 Slashes in experiment id
set FOREIGN_KEY_CHECKS=0;

update EXPERIMENT set EXPERIMENT_ID = REPLACE(EXPERIMENT_ID, "/", "_") where EXPERIMENT_ID like '%/%';
update EXPERIMENT_INPUT set EXPERIMENT_ID = REPLACE(EXPERIMENT_ID, "/", "_") where EXPERIMENT_ID like '%/%';
update EXPERIMENT_OUTPUT set EXPERIMENT_ID = REPLACE(EXPERIMENT_ID, "/", "_") where EXPERIMENT_ID like '%/%';
update EXPERIMENT_STATUS set EXPERIMENT_ID = REPLACE(EXPERIMENT_ID, "/", "_") where EXPERIMENT_ID like '%/%';
update EXPERIMENT_ERROR set EXPERIMENT_ID = REPLACE(EXPERIMENT_ID, "/", "_") where EXPERIMENT_ID like '%/%';
update USER_CONFIGURATION_DATA set EXPERIMENT_ID = REPLACE(EXPERIMENT_ID, "/", "_") where EXPERIMENT_ID like '%/%';
update PROCESS set EXPERIMENT_ID = REPLACE(EXPERIMENT_ID, "/", "_") where EXPERIMENT_ID like '%/%';

set FOREIGN_KEY_CHECKS=1;

-- AIRAVATA-3268: add overrideFilename to EXPERIMENT_INPUT, PROCESS_INPUT
ALTER TABLE EXPERIMENT_INPUT ADD COLUMN IF NOT EXISTS OVERRIDE_FILENAME VARCHAR(255);
ALTER TABLE PROCESS_INPUT ADD COLUMN IF NOT EXISTS OVERRIDE_FILENAME VARCHAR(255);