You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airavata.apache.org by ma...@apache.org on 2017/06/28 15:25:09 UTC
airavata git commit: AIRAVATA-2453 0.17->0.18 DB migration scripts
Repository: airavata
Updated Branches:
refs/heads/develop 0479066b2 -> 2b92f6586
AIRAVATA-2453 0.17->0.18 DB migration scripts
AIRAVATA-2438 AIRAVATA-2440
Project: http://git-wip-us.apache.org/repos/asf/airavata/repo
Commit: http://git-wip-us.apache.org/repos/asf/airavata/commit/2b92f658
Tree: http://git-wip-us.apache.org/repos/asf/airavata/tree/2b92f658
Diff: http://git-wip-us.apache.org/repos/asf/airavata/diff/2b92f658
Branch: refs/heads/develop
Commit: 2b92f65863a0244f3107f0111a26b58a152f6fac
Parents: 0479066
Author: Marcus Christie <ma...@apache.org>
Authored: Wed Jun 28 11:24:08 2017 -0400
Committer: Marcus Christie <ma...@apache.org>
Committed: Wed Jun 28 11:24:08 2017 -0400
----------------------------------------------------------------------
.../DeltaScripts/appCatalog_schema_delta.sql | 30 ++++++++++++++++++++
.../sharingCatalog_schema_delta.sql | 10 +++++++
...aCatalog_lowercase_user_data_dir_example.sql | 18 ++++++++++++
.../credentialStore_keycloak_migration.sql | 2 ++
.../expCatalog_keycloak_migration.sql | 11 +++++++
.../profileService_keycloak_migration.sql | 8 ++++++
.../sharingCatalog_keycloak_migration.sql | 8 ++++++
.../0.17-0.18/migration-notes.md | 17 +++++++++++
.../main/resources/sharing-registry-mysql.sql | 8 +++---
9 files changed, 108 insertions(+), 4 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/airavata/blob/2b92f658/modules/registry/release-migration-scripts/0.17-0.18/DeltaScripts/appCatalog_schema_delta.sql
----------------------------------------------------------------------
diff --git a/modules/registry/release-migration-scripts/0.17-0.18/DeltaScripts/appCatalog_schema_delta.sql b/modules/registry/release-migration-scripts/0.17-0.18/DeltaScripts/appCatalog_schema_delta.sql
new file mode 100644
index 0000000..d06f232
--- /dev/null
+++ b/modules/registry/release-migration-scripts/0.17-0.18/DeltaScripts/appCatalog_schema_delta.sql
@@ -0,0 +1,30 @@
+--
+--
+-- 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.
+--
+
+ALTER TABLE `COMPUTE_RESOURCE` ADD `DEFAULT_NODE_COUNT` int(11) NOT NULL DEFAULT '0';
+ALTER TABLE `COMPUTE_RESOURCE` ADD `DEFAULT_CPU_COUNT` int(11) NOT NULL DEFAULT '0';
+ALTER TABLE `COMPUTE_RESOURCE` ADD `DEFAULT_WALLTIME` int(11) NOT NULL DEFAULT '0';
+ALTER TABLE `COMPUTE_RESOURCE` ADD `CPUS_PER_NODE` int(11) NOT NULL DEFAULT '0';
+ALTER TABLE `BATCH_QUEUE` ADD `QUEUE_SPECIFIC_MACROS` varchar(255) NOT NULL;
+ALTER TABLE `APPLICATION_DEPLOYMENT` ADD `DEFAULT_WALLTIME` int(11) NOT NULL DEFAULT '0';
+ALTER TABLE `BATCH_QUEUE` ADD `DEFAULT_WALLTIME` int(11) NOT NULL DEFAULT '0';
+ALTER TABLE `APPLICATION_DEPLOYMENT` CHANGE `DEFAULT_NODE_COUNT` `DEFAULT_NODE_COUNT` int(11) DEFAULT '0';
+ALTER TABLE `APPLICATION_DEPLOYMENT` CHANGE `DEFAULT_CPU_COUNT` `DEFAULT_CPU_COUNT` int(11) NOT NULL DEFAULT '0';
+
http://git-wip-us.apache.org/repos/asf/airavata/blob/2b92f658/modules/registry/release-migration-scripts/0.17-0.18/DeltaScripts/sharingCatalog_schema_delta.sql
----------------------------------------------------------------------
diff --git a/modules/registry/release-migration-scripts/0.17-0.18/DeltaScripts/sharingCatalog_schema_delta.sql b/modules/registry/release-migration-scripts/0.17-0.18/DeltaScripts/sharingCatalog_schema_delta.sql
new file mode 100644
index 0000000..213713a
--- /dev/null
+++ b/modules/registry/release-migration-scripts/0.17-0.18/DeltaScripts/sharingCatalog_schema_delta.sql
@@ -0,0 +1,10 @@
+
+ALTER TABLE `ENTITY` CHANGE `SHARED_COUNT` `SHARED_COUNT` BIGINT DEFAULT 0;
+ALTER TABLE `SHARING` DROP FOREIGN KEY IF EXISTS `SHARING_ibfk_1`;
+ALTER TABLE `SHARING` DROP FOREIGN KEY IF EXISTS `SHARING_ibfk_2`;
+ALTER TABLE `SHARING` DROP FOREIGN KEY IF EXISTS `SHARING_ibfk_3`;
+ALTER TABLE `SHARING` DROP FOREIGN KEY IF EXISTS `SHARING_ibfk_4`;
+ALTER TABLE `SHARING` ADD CONSTRAINT `SHARING_PERMISSION_TYPE_ID_DOMAIN_ID_FK` FOREIGN KEY (PERMISSION_TYPE_ID, DOMAIN_ID) REFERENCES PERMISSION_TYPE(PERMISSION_TYPE_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION;
+ALTER TABLE `SHARING` ADD CONSTRAINT `SHARING_ENTITY_ID_DOMAIN_ID_FK` FOREIGN KEY (ENTITY_ID, DOMAIN_ID) REFERENCES ENTITY(ENTITY_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION;
+ALTER TABLE `SHARING` ADD CONSTRAINT `SHARING_INHERITED_PARENT_ID_DOMAIN_ID_FK` FOREIGN KEY (INHERITED_PARENT_ID, DOMAIN_ID) REFERENCES ENTITY(ENTITY_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION;
+ALTER TABLE `SHARING` ADD CONSTRAINT `SHARING_GROUP_ID_DOMAIN_ID_FK` FOREIGN KEY (GROUP_ID, DOMAIN_ID) REFERENCES USER_GROUP(GROUP_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION;
http://git-wip-us.apache.org/repos/asf/airavata/blob/2b92f658/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration-replica-catalog/replicaCatalog_lowercase_user_data_dir_example.sql
----------------------------------------------------------------------
diff --git a/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration-replica-catalog/replicaCatalog_lowercase_user_data_dir_example.sql b/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration-replica-catalog/replicaCatalog_lowercase_user_data_dir_example.sql
new file mode 100644
index 0000000..2f550b2
--- /dev/null
+++ b/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration-replica-catalog/replicaCatalog_lowercase_user_data_dir_example.sql
@@ -0,0 +1,18 @@
+-- user_data_dir is the path to the gateway's data storage directory
+set @user_data_dir = '/var/www/user_data/';
+set @storage_id = '149.165.156.11_b5f26430-14d5-4372-8a7e-39b125aa640b';
+update DATA_REPLICA_LOCATION
+inner join (
+ select
+ REPLICA_ID,
+ FILE_PATH,
+ SUBSTR(FILE_PATH,
+ LOCATE(@user_data_dir, FILE_PATH) + LENGTH(@user_data_dir),
+ LOCATE('/', FILE_PATH, LOCATE(@user_data_dir, FILE_PATH) + LENGTH(@user_data_dir))
+ - (LOCATE(@user_data_dir, FILE_PATH) + LENGTH(@user_data_dir))
+ ) USERNAME
+ from DATA_REPLICA_LOCATION where STORAGE_RESOURCE_ID = @storage_id
+ and FILE_PATH like concat('%', @user_data_dir, '%')
+) a
+on a.REPLICA_ID = DATA_REPLICA_LOCATION.REPLICA_ID
+set DATA_REPLICA_LOCATION.FILE_PATH = REPLACE(DATA_REPLICA_LOCATION.FILE_PATH, concat(@user_data_dir, a.USERNAME), concat(@user_data_dir, LOWER(a.USERNAME)));
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/airavata/blob/2b92f658/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/credentialStore_keycloak_migration.sql
----------------------------------------------------------------------
diff --git a/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/credentialStore_keycloak_migration.sql b/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/credentialStore_keycloak_migration.sql
new file mode 100644
index 0000000..ba63339
--- /dev/null
+++ b/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/credentialStore_keycloak_migration.sql
@@ -0,0 +1,2 @@
+-- credential store
+update CREDENTIALS set PORTAL_USER_ID = lower(PORTAL_USER_ID);
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/airavata/blob/2b92f658/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/expCatalog_keycloak_migration.sql
----------------------------------------------------------------------
diff --git a/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/expCatalog_keycloak_migration.sql b/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/expCatalog_keycloak_migration.sql
new file mode 100644
index 0000000..ac29a00
--- /dev/null
+++ b/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/expCatalog_keycloak_migration.sql
@@ -0,0 +1,11 @@
+-- experiment catalog
+--- disable foreign key checks
+SET FOREIGN_KEY_CHECKS=0;
+update GATEWAY set REQUESTER_USERNAME = lower(REQUESTER_USERNAME);
+update USERS set AIRAVATA_INTERNAL_USER_ID = lower(AIRAVATA_INTERNAL_USER_ID), USER_NAME = lower(USER_NAME);
+update GATEWAY_WORKER set USER_NAME = lower(USER_NAME);
+update PROJECT set USER_NAME = lower(USER_NAME);
+update PROJECT_USER set USER_NAME = lower(USER_NAME);
+update EXPERIMENT set USER_NAME = lower(USER_NAME);
+update PROCESS set USERNAME = lower(USERNAME);
+SET FOREIGN_KEY_CHECKS=1;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/airavata/blob/2b92f658/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/profileService_keycloak_migration.sql
----------------------------------------------------------------------
diff --git a/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/profileService_keycloak_migration.sql b/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/profileService_keycloak_migration.sql
new file mode 100644
index 0000000..ab87f0d
--- /dev/null
+++ b/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/profileService_keycloak_migration.sql
@@ -0,0 +1,8 @@
+-- profile service
+--- disable foreign key checks in MySQL/MariaDB
+SET FOREIGN_KEY_CHECKS=0;
+update GATEWAY set REQUESTER_USERNAME = lower(REQUESTER_USERNAME);
+update USER_PROFILE set AIRAVATA_INTERNAL_USER_ID = lower(AIRAVATA_INTERNAL_USER_ID), USER_ID = lower(USER_ID);
+update USER_PROFILE_EMAIL set AIRAVATA_INTERNAL_USER_ID = lower(AIRAVATA_INTERNAL_USER_ID);
+update USER_PROFILE_PHONE set AIRAVATA_INTERNAL_USER_ID = lower(AIRAVATA_INTERNAL_USER_ID);
+SET FOREIGN_KEY_CHECKS=1;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/airavata/blob/2b92f658/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/sharingCatalog_keycloak_migration.sql
----------------------------------------------------------------------
diff --git a/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/sharingCatalog_keycloak_migration.sql b/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/sharingCatalog_keycloak_migration.sql
new file mode 100644
index 0000000..42ea49c
--- /dev/null
+++ b/modules/registry/release-migration-scripts/0.17-0.18/keycloak-migration/sharingCatalog_keycloak_migration.sql
@@ -0,0 +1,8 @@
+-- sharing catalog
+--- disable foreign key checks in MySQL/MariaDB
+SET FOREIGN_KEY_CHECKS=0;
+update SHARING_USER set USER_ID = lower(USER_ID), USER_NAME = lower(USER_NAME);
+update USER_GROUP set OWNER_ID = lower(OWNER_ID);
+update ENTITY set OWNER_ID = lower(OWNER_ID);
+update SHARING set GROUP_ID = lower(GROUP_ID);
+SET FOREIGN_KEY_CHECKS=1;
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/airavata/blob/2b92f658/modules/registry/release-migration-scripts/0.17-0.18/migration-notes.md
----------------------------------------------------------------------
diff --git a/modules/registry/release-migration-scripts/0.17-0.18/migration-notes.md b/modules/registry/release-migration-scripts/0.17-0.18/migration-notes.md
new file mode 100644
index 0000000..4ebdfbc
--- /dev/null
+++ b/modules/registry/release-migration-scripts/0.17-0.18/migration-notes.md
@@ -0,0 +1,17 @@
+## 0.17 - 0.18 Migration Notes
+
+### Known Issues:
+
+* None
+
+### Migration Steps:
+
+* Execute the SQL scripts present in DeltaScripts folder on top of Airavata 0.17 Release Database
+* There is no migration script for the new profile_service database
+* Run the WSO2 IS -> Keycloak+Profile Service migration script. See MigrationManager.java in the user-profile-migration module.
+* Keycloak only allows lowercase usernames. So all usernames in the database must be lowercased.
+ Run the scripts in the keycloak-migration folder to lowercase usernames.
+* For each gateway in gateway data storage, run the replicaCatalog_lowercase_user_data_dir_example.sql
+ script in the keycloak-migration-replica-catalog directory. **NOTE**: you'll need to tweak the variables
+ at the start of the script for a particular deployment.
+
http://git-wip-us.apache.org/repos/asf/airavata/blob/2b92f658/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-mysql.sql
----------------------------------------------------------------------
diff --git a/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-mysql.sql b/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-mysql.sql
index ed4112c..09830a0 100644
--- a/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-mysql.sql
+++ b/modules/sharing-registry/sharing-registry-server/src/main/resources/sharing-registry-mysql.sql
@@ -123,10 +123,10 @@ CREATE TABLE SHARING (
CREATED_TIME BIGINT NOT NULL,
UPDATED_TIME BIGINT NOT NULL,
PRIMARY KEY (PERMISSION_TYPE_ID, ENTITY_ID, GROUP_ID, DOMAIN_ID, INHERITED_PARENT_ID),
- FOREIGN KEY (PERMISSION_TYPE_ID, DOMAIN_ID) REFERENCES PERMISSION_TYPE(PERMISSION_TYPE_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION,
- FOREIGN KEY (ENTITY_ID, DOMAIN_ID) REFERENCES ENTITY(ENTITY_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION,
- FOREIGN KEY (INHERITED_PARENT_ID, DOMAIN_ID) REFERENCES ENTITY(ENTITY_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION,
- FOREIGN KEY (GROUP_ID, DOMAIN_ID) REFERENCES USER_GROUP(GROUP_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION
+ CONSTRAINT `SHARING_PERMISSION_TYPE_ID_DOMAIN_ID_FK` FOREIGN KEY (PERMISSION_TYPE_ID, DOMAIN_ID) REFERENCES PERMISSION_TYPE(PERMISSION_TYPE_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION,
+ CONSTRAINT `SHARING_ENTITY_ID_DOMAIN_ID_FK` FOREIGN KEY (ENTITY_ID, DOMAIN_ID) REFERENCES ENTITY(ENTITY_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION,
+ CONSTRAINT `SHARING_INHERITED_PARENT_ID_DOMAIN_ID_FK` FOREIGN KEY (INHERITED_PARENT_ID, DOMAIN_ID) REFERENCES ENTITY(ENTITY_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION,
+ CONSTRAINT `SHARING_GROUP_ID_DOMAIN_ID_FK` FOREIGN KEY (GROUP_ID, DOMAIN_ID) REFERENCES USER_GROUP(GROUP_ID, DOMAIN_ID) ON DELETE CASCADE ON UPDATE NO ACTION
)ENGINE=InnoDB DEFAULT CHARACTER SET=latin1;
CREATE TABLE CONFIGURATION