.devcontainer/database_scripts/init/06-cloud-execution-support-migration.sql

USE app_catalog;

-- Add RESOURCE_TYPE column to base table
ALTER TABLE `GROUP_COMPUTE_RESOURCE_PREFERENCE`
    ADD COLUMN `RESOURCE_TYPE` VARCHAR(255) NOT NULL DEFAULT 'SLURM';

-- Make sure all future inserts require an explicit RESOURCE_TYPE
ALTER TABLE `GROUP_COMPUTE_RESOURCE_PREFERENCE`
    ALTER COLUMN `RESOURCE_TYPE` DROP DEFAULT;

-- Create the new Slurm-specific table
CREATE TABLE `SLURM_GROUP_COMPUTE_RESOURCE_PREFERENCE`
(
    `RESOURCE_ID`                             VARCHAR(255) NOT NULL,
    `GROUP_RESOURCE_PROFILE_ID`               VARCHAR(255) NOT NULL,
    `PREFERED_BATCH_QUEUE`                    VARCHAR(255)          DEFAULT NULL,
    `ALLOCATION_PROJECT_NUMBER`               VARCHAR(255)          DEFAULT NULL,
    `USAGE_REPORTING_GATEWAY_ID`              VARCHAR(255)          DEFAULT NULL,
    `QUALITY_OF_SERVICE`                      VARCHAR(255)          DEFAULT NULL,
    `RESERVATION`                             VARCHAR(255)          DEFAULT NULL,
    `RESERVATION_START_TIME`                  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `RESERVATION_END_TIME`                    TIMESTAMP    NOT NULL DEFAULT '0000-00-00 00:00:00',
    `SSH_ACCOUNT_PROVISIONER`                 VARCHAR(255)          DEFAULT NULL,
    `SSH_ACCOUNT_PROVISIONER_ADDITIONAL_INFO` TEXT                  DEFAULT NULL,
    PRIMARY KEY (`RESOURCE_ID`, `GROUP_RESOURCE_PROFILE_ID`),
    CONSTRAINT `FK_SLURM_PREF_TO_BASE` FOREIGN KEY (`RESOURCE_ID`, `GROUP_RESOURCE_PROFILE_ID`)
        REFERENCES `GROUP_COMPUTE_RESOURCE_PREFERENCE` (`RESOURCE_ID`, `GROUP_RESOURCE_PROFILE_ID`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Migrate Slurm-specific field data from the base table into the new Slurm table
INSERT INTO `SLURM_GROUP_COMPUTE_RESOURCE_PREFERENCE` (
    `RESOURCE_ID`,
    `GROUP_RESOURCE_PROFILE_ID`,
    `PREFERED_BATCH_QUEUE`,
    `ALLOCATION_PROJECT_NUMBER`,
    `USAGE_REPORTING_GATEWAY_ID`,
    `QUALITY_OF_SERVICE`,
    `RESERVATION`,
    `RESERVATION_START_TIME`,
    `RESERVATION_END_TIME`,
    `SSH_ACCOUNT_PROVISIONER`,
    `SSH_ACCOUNT_PROVISIONER_ADDITIONAL_INFO`
)
SELECT
    `RESOURCE_ID`,
    `GROUP_RESOURCE_PROFILE_ID`,
    `PREFERED_BATCH_QUEUE`,
    `ALLOCATION_PROJECT_NUMBER`,
    `USAGE_REPORTING_GATEWAY_ID`,
    `QUALITY_OF_SERVICE`,
    `RESERVATION`,
    `RESERVATION_START_TIME`,
    `RESERVATION_END_TIME`,
    `SSH_ACCOUNT_PROVISIONER`,
    `SSH_ACCOUNT_PROVISIONER_ADDITIONAL_INFO`
FROM `GROUP_COMPUTE_RESOURCE_PREFERENCE`;

-- Drop the Slurm-specific columns from the base table
ALTER TABLE `GROUP_COMPUTE_RESOURCE_PREFERENCE`
DROP COLUMN `PREFERED_BATCH_QUEUE`,
  DROP COLUMN `ALLOCATION_PROJECT_NUMBER`,
  DROP COLUMN `USAGE_REPORTING_GATEWAY_ID`,
  DROP COLUMN `QUALITY_OF_SERVICE`,
  DROP COLUMN `RESERVATION`,
  DROP COLUMN `RESERVATION_START_TIME`,
  DROP COLUMN `RESERVATION_END_TIME`,
  DROP COLUMN `SSH_ACCOUNT_PROVISIONER`,
  DROP COLUMN `SSH_ACCOUNT_PROVISIONER_ADDITIONAL_INFO`;

-- Create the AWS-specific group compute preference table
CREATE TABLE `AWS_GROUP_COMPUTE_RESOURCE_PREFERENCE`
(
    `RESOURCE_ID`                 VARCHAR(255) NOT NULL,
    `GROUP_RESOURCE_PROFILE_ID`   VARCHAR(255) NOT NULL,
    `AWS_REGION`                  VARCHAR(255) NOT NULL,
    `PREFERRED_AMI_ID`            VARCHAR(255) NOT NULL,
    `PREFERRED_INSTANCE_TYPE`     VARCHAR(255) NOT NULL,

    PRIMARY KEY (`RESOURCE_ID`, `GROUP_RESOURCE_PROFILE_ID`),
    CONSTRAINT `FK_AWS_PREF_TO_BASE` FOREIGN KEY (`RESOURCE_ID`, `GROUP_RESOURCE_PROFILE_ID`)
        REFERENCES `GROUP_COMPUTE_RESOURCE_PREFERENCE` (`RESOURCE_ID`, `GROUP_RESOURCE_PROFILE_ID`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;