This is an automated email from the ASF dual-hosted git repository. machristie pushed a commit to branch AIRAVATA-3326-improved-database-migration-script-creation in repository https://gitbox.apache.org/repos/asf/airavata.git
commit a090e3eb00327798f72f1e16fa9e67cc84cb6ed6 Author: Marcus Christie <[email protected]> AuthorDate: Tue Jun 9 10:56:03 2020 -0400 AIRAVATA-3326 ide-integration using mariadb with migrations --- modules/ide-integration/README.md | 6 ++++++ .../database_scripts/init/02-sharing-registry-migrations.sql | 5 ++--- .../resources/database_scripts/init/03-appcatalog-migrations.sql | 6 +++--- .../resources/database_scripts/init/04-expcatalog-migrations.sql | 4 ++-- modules/ide-integration/src/main/resources/docker-compose.yml | 4 ++-- .../src/main/resources/migrations-util/mysql/docker-compose.yml | 2 +- 6 files changed, 16 insertions(+), 11 deletions(-) diff --git a/modules/ide-integration/README.md b/modules/ide-integration/README.md index c905f13..cb81d2e 100644 --- a/modules/ide-integration/README.md +++ b/modules/ide-integration/README.md @@ -57,6 +57,12 @@ Using this module, you can setup a full Airavata installation inside Intelij IDE docker-compose up ``` +* Apply any database migrations. Go to src/main/resources directory and run + + ``` + cat ./database_scripts/init/*-migrations.sql | docker exec -i resources_db_1 mysql -p123456 + ``` + * Wait until all the services come up. This will initialize all utilities required to start Airavata server ### Starting API Server diff --git a/modules/ide-integration/src/main/resources/database_scripts/init/02-sharing-registry-migrations.sql b/modules/ide-integration/src/main/resources/database_scripts/init/02-sharing-registry-migrations.sql index 31ff1d4..9b5733b 100644 --- a/modules/ide-integration/src/main/resources/database_scripts/init/02-sharing-registry-migrations.sql +++ b/modules/ide-integration/src/main/resources/database_scripts/init/02-sharing-registry-migrations.sql @@ -1,5 +1,4 @@ use sharing_catalog; --- FIXME: IF NOT EXISTS syntax only works with MariaDB -ALTER TABLE DOMAIN ADD COLUMN INITIAL_USER_GROUP_ID varchar(255); -ALTER TABLE DOMAIN ADD CONSTRAINT `DOMAIN_INITIAL_USER_GROUP_ID_FK` FOREIGN KEY (INITIAL_USER_GROUP_ID, DOMAIN_ID) REFERENCES USER_GROUP(GROUP_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION; +ALTER TABLE DOMAIN ADD COLUMN IF NOT EXISTS INITIAL_USER_GROUP_ID varchar(255); +ALTER TABLE DOMAIN ADD CONSTRAINT `DOMAIN_INITIAL_USER_GROUP_ID_FK` FOREIGN KEY IF NOT EXISTS (INITIAL_USER_GROUP_ID, DOMAIN_ID) REFERENCES USER_GROUP(GROUP_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION; diff --git a/modules/ide-integration/src/main/resources/database_scripts/init/03-appcatalog-migrations.sql b/modules/ide-integration/src/main/resources/database_scripts/init/03-appcatalog-migrations.sql index cf6f9c7..a509fb2 100644 --- a/modules/ide-integration/src/main/resources/database_scripts/init/03-appcatalog-migrations.sql +++ b/modules/ide-integration/src/main/resources/database_scripts/init/03-appcatalog-migrations.sql @@ -1,11 +1,11 @@ use app_catalog; -ALTER TABLE APPLICATION_INPUT ADD COLUMN OVERRIDE_FILENAME VARCHAR(255); +ALTER TABLE APPLICATION_INPUT ADD COLUMN IF NOT EXISTS OVERRIDE_FILENAME VARCHAR(255); -- AIRAVATA-3126 CREATE TABLE IF NOT EXISTS COMPUTE_RESOURCE_RESERVATION -- ComputeResourceReservationEntity (RESERVATION_ID VARCHAR(255) NOT NULL, END_TIME TIMESTAMP NOT NULL, RESERVATION_NAME VARCHAR(255) NOT NULL, START_TIME TIMESTAMP NOT NULL, RESOURCE_ID VARCHAR(255) NOT NULL, GROUP_RESOURCE_PROFILE_ID VARCHAR(255) NOT NULL, PRIMARY KEY (RESERVATION_ID) )ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS COMPUTE_RESOURCE_RESERVATION_QUEUE (RESERVATION_ID VARCHAR(255), QUEUE_NAME VARCHAR(255) NOT NULL )ENGINE=InnoDB DEFAULT CHARSET=latin1; -CREATE INDEX I_CMPTN_Q_RESERVATION_ID ON COMPUTE_RESOURCE_RESERVATION_QUEUE (RESERVATION_ID); -ALTER TABLE COMPUTE_RESOURCE_RESERVATION ADD CONSTRAINT FK_COMPUTE_RESOURCE_RESERVATION FOREIGN KEY (RESOURCE_ID, GROUP_RESOURCE_PROFILE_ID) REFERENCES GROUP_COMPUTE_RESOURCE_PREFERENCE (RESOURCE_ID, GROUP_RESOURCE_PROFILE_ID) ON DELETE CASCADE; +CREATE INDEX IF NOT EXISTS I_CMPTN_Q_RESERVATION_ID ON COMPUTE_RESOURCE_RESERVATION_QUEUE (RESERVATION_ID); +ALTER TABLE COMPUTE_RESOURCE_RESERVATION ADD CONSTRAINT FK_COMPUTE_RESOURCE_RESERVATION FOREIGN KEY IF NOT EXISTS (RESOURCE_ID, GROUP_RESOURCE_PROFILE_ID) REFERENCES GROUP_COMPUTE_RESOURCE_PREFERENCE (RESOURCE_ID, GROUP_RESOURCE_PROFILE_ID) ON DELETE CASCADE; diff --git a/modules/ide-integration/src/main/resources/database_scripts/init/04-expcatalog-migrations.sql b/modules/ide-integration/src/main/resources/database_scripts/init/04-expcatalog-migrations.sql index 067e449..38ba76d 100644 --- a/modules/ide-integration/src/main/resources/database_scripts/init/04-expcatalog-migrations.sql +++ b/modules/ide-integration/src/main/resources/database_scripts/init/04-expcatalog-migrations.sql @@ -1,4 +1,4 @@ use experiment_catalog; -ALTER TABLE EXPERIMENT_INPUT ADD COLUMN OVERRIDE_FILENAME VARCHAR(255); -ALTER TABLE PROCESS_INPUT ADD COLUMN OVERRIDE_FILENAME VARCHAR(255); +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); diff --git a/modules/ide-integration/src/main/resources/docker-compose.yml b/modules/ide-integration/src/main/resources/docker-compose.yml index d2cac0a..3798117 100644 --- a/modules/ide-integration/src/main/resources/docker-compose.yml +++ b/modules/ide-integration/src/main/resources/docker-compose.yml @@ -14,7 +14,7 @@ services: - ./keycloak/standalone.xml:/opt/jboss/keycloak/standalone/configuration/standalone.xml command: ["-b", "0.0.0.0", "-Dkeycloak.migration.action=import", "-Dkeycloak.migration.provider=singleFile", "-Dkeycloak.migration.file=/opt/keycloak/Default-export.json", "-Dkeycloak.migration.strategy=OVERWRITE_EXISTING"] db: - image: mysql:5.7.21 + image: mariadb:10.4.13 environment: - MYSQL_ROOT_PASSWORD=123456 - MYSQL_USER=airavata @@ -51,4 +51,4 @@ services: volumes: - /tmp:/tmp ports: - - "22222:22" \ No newline at end of file + - "22222:22" diff --git a/modules/registry/registry-core/src/main/resources/migrations-util/mysql/docker-compose.yml b/modules/registry/registry-core/src/main/resources/migrations-util/mysql/docker-compose.yml index c73cf2a..c1478ed 100644 --- a/modules/registry/registry-core/src/main/resources/migrations-util/mysql/docker-compose.yml +++ b/modules/registry/registry-core/src/main/resources/migrations-util/mysql/docker-compose.yml @@ -1,7 +1,7 @@ version: '3' services: db: - image: mariadb:10 + image: mariadb:10.4.13 environment: - MYSQL_ROOT_PASSWORD=123456 - MYSQL_USER=airavata
