.devcontainer/database_scripts/init/04-expcatalog-migrations.sql

use experiment_catalog;

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);

CREATE TABLE IF NOT EXISTS GATEWAY_USAGE_REPORTING_COMMAND (
  GATEWAY_ID VARCHAR(255) NOT NULL,
  COMPUTE_RESOURCE_ID VARCHAR(255) NOT NULL,
  COMMAND LONGTEXT NOT NULL,
  PRIMARY KEY (GATEWAY_ID, COMPUTE_RESOURCE_ID)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- AIRAVATA-3369: Convert USER_FRIENDLY_DESCRIPTION from VARCHAR to TEXT (CLOB)
alter table EXPERIMENT_INPUT modify column USER_FRIENDLY_DESCRIPTION TEXT;
alter table PROCESS_INPUT modify column USER_FRIENDLY_DESCRIPTION TEXT;

CREATE TABLE IF NOT EXISTS COMPUTE_RESOURCE_SCHEDULING (
       EXPERIMENT_ID varchar(255) NOT NULL,
       RESOURCE_HOST_ID varchar(255) NOT NULL,
       TOTAL_CPU_COUNT INT,
       NODE_COUNT INT,
       NUMBER_OF_THREADS INT,
       QUEUE_NAME varchar(255) NOT NULL,
       WALL_TIME_LIMIT INT,
       TOTAL_PHYSICAL_MEMORY INT,
       STATIC_WORKING_DIR varchar(255),
       OVERRIDE_LOGIN_USER_NAME varchar(255),
       OVERRIDE_SCRATCH_LOCATION varchar(255),
       OVERRIDE_ALLOCATION_PROJECT_NUMBER varchar(255),
       PARALLEL_GROUP_COUNT INT,
       PRIMARY KEY (EXPERIMENT_ID,RESOURCE_HOST_ID,QUEUE_NAME),
       FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Rename storage resource ID to input storage resource ID
ALTER TABLE USER_CONFIGURATION_DATA CHANGE COLUMN STORAGE_RESOURCE_ID INPUT_STORAGE_RESOURCE_ID VARCHAR(255) DEFAULT NULL;
ALTER TABLE PROCESS CHANGE COLUMN STORAGE_RESOURCE_ID INPUT_STORAGE_RESOURCE_ID VARCHAR(255) DEFAULT NULL;

-- Add output storage resource ID columns
ALTER TABLE USER_CONFIGURATION_DATA ADD COLUMN IF NOT EXISTS OUTPUT_STORAGE_RESOURCE_ID VARCHAR(255) DEFAULT NULL;
ALTER TABLE PROCESS ADD COLUMN IF NOT EXISTS OUTPUT_STORAGE_RESOURCE_ID VARCHAR(255) DEFAULT NULL;

-- Update OUTPUT_STORAGE_RESOURCE_ID with INPUT_STORAGE_RESOURCE_ID when missing
UPDATE USER_CONFIGURATION_DATA
SET OUTPUT_STORAGE_RESOURCE_ID = INPUT_STORAGE_RESOURCE_ID
WHERE (OUTPUT_STORAGE_RESOURCE_ID IS NULL OR OUTPUT_STORAGE_RESOURCE_ID = '')
  AND INPUT_STORAGE_RESOURCE_ID IS NOT NULL
  AND INPUT_STORAGE_RESOURCE_ID != '';

UPDATE PROCESS
SET OUTPUT_STORAGE_RESOURCE_ID = INPUT_STORAGE_RESOURCE_ID
WHERE (OUTPUT_STORAGE_RESOURCE_ID IS NULL OR OUTPUT_STORAGE_RESOURCE_ID = '')
  AND INPUT_STORAGE_RESOURCE_ID IS NOT NULL
  AND INPUT_STORAGE_RESOURCE_ID != '';