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 2019/01/31 15:32:37 UTC
[airavata] branch
airavata-2938-change-db-initialization-in-registry-server-to-use-registry-refactoring-code
updated: AIRAVATA-2938 Migration to bring DB into sync with DB init script
This is an automated email from the ASF dual-hosted git repository.
machristie pushed a commit to branch airavata-2938-change-db-initialization-in-registry-server-to-use-registry-refactoring-code
in repository https://gitbox.apache.org/repos/asf/airavata.git
The following commit(s) were added to refs/heads/airavata-2938-change-db-initialization-in-registry-server-to-use-registry-refactoring-code by this push:
new 9dc7878 AIRAVATA-2938 Migration to bring DB into sync with DB init script
9dc7878 is described below
commit 9dc7878921b156a34269246cbe3673989de335b0
Author: Marcus Christie <ma...@apache.org>
AuthorDate: Thu Jan 31 10:32:18 2019 -0500
AIRAVATA-2938 Migration to bring DB into sync with DB init script
credential_store and profile_service databases
---
.../user/entities/NSFDemographicsEntity.java | 8 +-
.../commons/user/entities/UserProfileEntity.java | 6 +-
.../main/resources/user-profile-catalog-derby.sql | 84 ++++---
.../main/resources/user-profile-catalog-mysql.sql | 104 +++++----
.../workspacecatalog/NSFDemographicsEntity.java | 94 --------
.../workspacecatalog/UserProfileEntity.java | 247 ---------------------
.../src/main/resources/appcatalog-derby.sql | 2 +-
.../src/main/resources/appcatalog-mysql.sql | 2 +-
.../next/DeltaScripts/appCatalog_schema_delta.sql | 14 ++
.../DeltaScripts/credential_store_schema_delta.sql | 30 +++
.../DeltaScripts/profile_service_schema_delta.sql | 23 ++
11 files changed, 198 insertions(+), 416 deletions(-)
diff --git a/airavata-services/profile-service/profile-service-commons/src/main/java/org/apache/airavata/service/profile/commons/user/entities/NSFDemographicsEntity.java b/airavata-services/profile-service/profile-service-commons/src/main/java/org/apache/airavata/service/profile/commons/user/entities/NSFDemographicsEntity.java
index 80d0656..f177785 100644
--- a/airavata-services/profile-service/profile-service-commons/src/main/java/org/apache/airavata/service/profile/commons/user/entities/NSFDemographicsEntity.java
+++ b/airavata-services/profile-service/profile-service-commons/src/main/java/org/apache/airavata/service/profile/commons/user/entities/NSFDemographicsEntity.java
@@ -54,7 +54,7 @@ public class NSFDemographicsEntity {
@ElementCollection
@CollectionTable(name="NSF_DEMOGRAPHIC_ETHNICITY", joinColumns = @JoinColumn(name="AIRAVATA_INTERNAL_USER_ID"))
- @Column(name = "ETHNICITIES")
+ @Column(name = "ETHNICITY")
public List<String> getEthnicities() {
return ethnicities;
}
@@ -65,7 +65,7 @@ public class NSFDemographicsEntity {
@ElementCollection
@CollectionTable(name="NSF_DEMOGRAPHIC_RACE", joinColumns = @JoinColumn(name="AIRAVATA_INTERNAL_USER_ID"))
- @Column(name = "RACES")
+ @Column(name = "RACE")
public List<String> getRaces() {
return races;
}
@@ -76,7 +76,7 @@ public class NSFDemographicsEntity {
@ElementCollection
@CollectionTable(name="NSF_DEMOGRAPHIC_DISABILITY", joinColumns = @JoinColumn(name="AIRAVATA_INTERNAL_USER_ID"))
- @Column(name = "DISABILITIES")
+ @Column(name = "DISABILITY")
public List<String> getDisabilities() {
return disabilities;
}
@@ -105,4 +105,4 @@ public class NSFDemographicsEntity {
", disabilities=" + disabilities +
'}';
}
-}
\ No newline at end of file
+}
diff --git a/airavata-services/profile-service/profile-service-commons/src/main/java/org/apache/airavata/service/profile/commons/user/entities/UserProfileEntity.java b/airavata-services/profile-service/profile-service-commons/src/main/java/org/apache/airavata/service/profile/commons/user/entities/UserProfileEntity.java
index dddcf42..79e9dbd 100644
--- a/airavata-services/profile-service/profile-service-commons/src/main/java/org/apache/airavata/service/profile/commons/user/entities/UserProfileEntity.java
+++ b/airavata-services/profile-service/profile-service-commons/src/main/java/org/apache/airavata/service/profile/commons/user/entities/UserProfileEntity.java
@@ -94,7 +94,7 @@ public class UserProfileEntity {
@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name="USER_PROFILE_EMAIL", joinColumns = @JoinColumn(name="AIRAVATA_INTERNAL_USER_ID"))
- @Column(name = "EMAILS")
+ @Column(name = "EMAIL")
public List<String> getEmails() {
return emails;
}
@@ -159,7 +159,7 @@ public class UserProfileEntity {
@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name="USER_PROFILE_PHONE", joinColumns = @JoinColumn(name="AIRAVATA_INTERNAL_USER_ID"))
- @Column(name = "PHONES")
+ @Column(name = "PHONE")
public List<String> getPhones() {
return phones;
}
@@ -342,4 +342,4 @@ public class UserProfileEntity {
", nsfDemographics=" + nsfDemographics +
'}';
}
-}
\ No newline at end of file
+}
diff --git a/airavata-services/profile-service/profile-user-core/src/main/resources/user-profile-catalog-derby.sql b/airavata-services/profile-service/profile-user-core/src/main/resources/user-profile-catalog-derby.sql
index c93c1bc..5437f67 100644
--- a/airavata-services/profile-service/profile-user-core/src/main/resources/user-profile-catalog-derby.sql
+++ b/airavata-services/profile-service/profile-user-core/src/main/resources/user-profile-catalog-derby.sql
@@ -19,84 +19,112 @@
*
*/
+CREATE TABLE IF NOT EXISTS GATEWAY (
+ AIRAVATA_INTERNAL_GATEWAY_ID varchar(255) NOT NULL,
+ DECLINED_REASON varchar(255) DEFAULT NULL,
+ GATEWAY_DOMAIN varchar(255) DEFAULT NULL,
+ EMAIL_ADDRESS varchar(255) DEFAULT NULL,
+ GATEWAY_ACRONYM varchar(255) DEFAULT NULL,
+ GATEWAY_ADMIN_EMAIL varchar(255) DEFAULT NULL,
+ GATEWAY_ADMIN_FIRST_NAME varchar(255) DEFAULT NULL,
+ GATEWAY_ADMIN_LAST_NAME varchar(255) DEFAULT NULL,
+ GATEWAY_APPROVAL_STATUS varchar(255) DEFAULT NULL,
+ GATEWAY_ID varchar(255) DEFAULT NULL,
+ GATEWAY_NAME varchar(255) DEFAULT NULL,
+ GATEWAY_PUBLIC_ABSTRACT varchar(255) DEFAULT NULL,
+ GATEWAY_URL varchar(255) DEFAULT NULL,
+ IDENTITY_SERVER_PASSWORD_TOKEN varchar(255) DEFAULT NULL,
+ IDENTITY_SERVER_USERNAME varchar(255) DEFAULT NULL,
+ OAUTH_CLIENT_ID varchar(255) DEFAULT NULL,
+ OAUTH_CLIENT_SECRET varchar(255) DEFAULT NULL,
+ REQUEST_CREATION_TIME bigint(20) DEFAULT NULL,
+ REQUESTER_USERNAME varchar(255) DEFAULT NULL,
+ GATEWAY_REVIEW_PROPOSAL_DESCRIPTION varchar(255) DEFAULT NULL,
+ PRIMARY KEY (AIRAVATA_INTERNAL_GATEWAY_ID)
+);
+
CREATE TABLE USER_PROFILE (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- USER_ID VARCHAR (255),
- GATEWAY_ID VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ USER_ID VARCHAR (255) NOT NULL,
+ GATEWAY_ID VARCHAR (255) NOT NULL,
USER_MODEL_VERSION VARCHAR (255),
- USER_NAME VARCHAR (255),
+ FIRST_NAME VARCHAR (255),
+ LAST_NAME VARCHAR (255),
+ MIDDLE_NAME VARCHAR (255),
+ NAME_PREFIX VARCHAR (255),
+ NAME_PREFIX VARCHAR (255),
ORCID_ID VARCHAR (255),
COUNTRY VARCHAR (255),
HOME_ORGANIZATION VARCHAR (255),
ORIGINATION_AFFILIATION VARCHAR (255),
- CREATION_TIME BIGINT,
- LAST_ACCESS_TIME BIGINT,
- VALID_UNTIL BIGINT,
+ CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ LAST_ACCESS_TIME TIMESTAMP,
+ VALID_UNTIL TIMESTAMP,
STATE VARCHAR (255),
COMMENTS CLOB,
- GPG_KEY VARCHAR (8192),
+ GPG_KEY CLOB,
TIME_ZONE VARCHAR (255),
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID)
);
CREATE TABLE USER_PROFILE_EMAIL (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- EMAIL VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ EMAIL VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, EMAIL),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
);
CREATE TABLE USER_PROFILE_PHONE (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- PHONE VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ PHONE VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, PHONE ),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
);
CREATE TABLE USER_PROFILE_NATIONALITY (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- NATIONALITY VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ NATIONALITY VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, NATIONALITY ),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
);
CREATE TABLE USER_PROFILE_LABELED_URI (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- LABELED_URI VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ LABELED_URI VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, LABELED_URI ),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
);
CREATE TABLE NSF_DEMOGRAPHIC (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- GENDER VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ GENDER VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
);
CREATE TABLE NSF_DEMOGRAPHIC_ETHNICITY (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- ETHNICITY VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ ETHNICITY VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, ETHNICITY ),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES NSF_DEMOGRAPHIC(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
);
CREATE TABLE NSF_DEMOGRAPHIC_RACE (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- RACE VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ RACE VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, RACE ),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES NSF_DEMOGRAPHIC(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
);
CREATE TABLE NSF_DEMOGRAPHIC_DISABILITY (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- DISABILITY VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ DISABILITY VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, DISABILITY ),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES NSF_DEMOGRAPHIC(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
);
CREATE TABLE CUSTOMIZED_DASHBOARD (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
ENABLED_EXPERIMENT_ID VARCHAR (255),
ENABLED_NAME VARCHAR (255),
ENABLED_DESCRIPTION VARCHAR (255),
@@ -121,14 +149,14 @@ CREATE TABLE CUSTOMIZED_DASHBOARD (
ENABLED_OUTPUTS VARCHAR (255),
ENABLED_STORAGE_DIR VARCHAR (255),
ENABLED_ERRORS VARCHAR (255),
- PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID)
+ PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
);
CREATE TABLE CONFIGURATION
(
- CONFIG_KEY VARCHAR(255),
- CONFIG_VAL VARCHAR(255),
+ CONFIG_KEY VARCHAR(255) NOT NULL,
+ CONFIG_VAL VARCHAR(255) NOT NULL,
PRIMARY KEY(CONFIG_KEY, CONFIG_VAL)
);
diff --git a/airavata-services/profile-service/profile-user-core/src/main/resources/user-profile-catalog-mysql.sql b/airavata-services/profile-service/profile-user-core/src/main/resources/user-profile-catalog-mysql.sql
index 6fe1bac..14f1d24 100644
--- a/airavata-services/profile-service/profile-user-core/src/main/resources/user-profile-catalog-mysql.sql
+++ b/airavata-services/profile-service/profile-user-core/src/main/resources/user-profile-catalog-mysql.sql
@@ -19,84 +19,112 @@
*
*/
+CREATE TABLE IF NOT EXISTS GATEWAY (
+ AIRAVATA_INTERNAL_GATEWAY_ID varchar(255) NOT NULL,
+ DECLINED_REASON varchar(255) DEFAULT NULL,
+ GATEWAY_DOMAIN varchar(255) DEFAULT NULL,
+ EMAIL_ADDRESS varchar(255) DEFAULT NULL,
+ GATEWAY_ACRONYM varchar(255) DEFAULT NULL,
+ GATEWAY_ADMIN_EMAIL varchar(255) DEFAULT NULL,
+ GATEWAY_ADMIN_FIRST_NAME varchar(255) DEFAULT NULL,
+ GATEWAY_ADMIN_LAST_NAME varchar(255) DEFAULT NULL,
+ GATEWAY_APPROVAL_STATUS varchar(255) DEFAULT NULL,
+ GATEWAY_ID varchar(255) DEFAULT NULL,
+ GATEWAY_NAME varchar(255) DEFAULT NULL,
+ GATEWAY_PUBLIC_ABSTRACT varchar(255) DEFAULT NULL,
+ GATEWAY_URL varchar(255) DEFAULT NULL,
+ IDENTITY_SERVER_PASSWORD_TOKEN varchar(255) DEFAULT NULL,
+ IDENTITY_SERVER_USERNAME varchar(255) DEFAULT NULL,
+ OAUTH_CLIENT_ID varchar(255) DEFAULT NULL,
+ OAUTH_CLIENT_SECRET varchar(255) DEFAULT NULL,
+ REQUEST_CREATION_TIME bigint(20) DEFAULT NULL,
+ REQUESTER_USERNAME varchar(255) DEFAULT NULL,
+ GATEWAY_REVIEW_PROPOSAL_DESCRIPTION varchar(255) DEFAULT NULL,
+ PRIMARY KEY (AIRAVATA_INTERNAL_GATEWAY_ID)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
CREATE TABLE IF NOT EXISTS USER_PROFILE (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- USER_ID VARCHAR (255),
- GATEWAY_ID VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ USER_ID VARCHAR (255) NOT NULL,
+ GATEWAY_ID VARCHAR (255) NOT NULL,
USER_MODEL_VERSION VARCHAR (255),
- USER_NAME VARCHAR (255),
+ FIRST_NAME VARCHAR (255),
+ LAST_NAME VARCHAR (255),
+ MIDDLE_NAME VARCHAR (255),
+ NAME_PREFIX VARCHAR (255),
+ NAME_PREFIX VARCHAR (255),
ORCID_ID VARCHAR (255),
COUNTRY VARCHAR (255),
HOME_ORGANIZATION VARCHAR (255),
ORIGINATION_AFFILIATION VARCHAR (255),
- CREATION_TIME BIGINT,
- LAST_ACCESS_TIME BIGINT,
- VALID_UNTIL BIGINT,
+ CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ LAST_ACCESS_TIME TIMESTAMP,
+ VALID_UNTIL TIMESTAMP,
STATE VARCHAR (255),
COMMENTS TEXT,
- GPG_KEY VARCHAR (8192),
+ GPG_KEY TEXT,
TIME_ZONE VARCHAR (255),
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID)
-);
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS USER_PROFILE_EMAIL (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- EMAIL VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ EMAIL VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, EMAIL),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
-);
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS USER_PROFILE_PHONE (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- PHONE VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ PHONE VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, PHONE ),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
-);
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS USER_PROFILE_NATIONALITY (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- NATIONALITY VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ NATIONALITY VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, NATIONALITY ),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
-);
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS USER_PROFILE_LABELED_URI (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- LABELED_URI VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ LABELED_URI VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, LABELED_URI ),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
-);
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS NSF_DEMOGRAPHIC (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- GENDER VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ GENDER VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
-);
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS NSF_DEMOGRAPHIC_ETHNICITY (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- ETHNICITY VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ ETHNICITY VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, ETHNICITY ),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES NSF_DEMOGRAPHIC(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
-);
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS NSF_DEMOGRAPHIC_RACE (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- RACE VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ RACE VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, RACE ),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES NSF_DEMOGRAPHIC(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
-);
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS NSF_DEMOGRAPHIC_DISABILITY (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
- DISABILITY VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
+ DISABILITY VARCHAR (255) NOT NULL,
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, DISABILITY ),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES NSF_DEMOGRAPHIC(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
-);
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS CUSTOMIZED_DASHBOARD (
- AIRAVATA_INTERNAL_USER_ID VARCHAR (255),
+ AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
ENABLED_EXPERIMENT_ID VARCHAR (255),
ENABLED_NAME VARCHAR (255),
ENABLED_DESCRIPTION VARCHAR (255),
@@ -123,13 +151,13 @@ CREATE TABLE IF NOT EXISTS CUSTOMIZED_DASHBOARD (
ENABLED_ERRORS VARCHAR (255),
PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID),
FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
-);
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE CONFIGURATION
(
- CONFIG_KEY VARCHAR(255),
- CONFIG_VAL VARCHAR(255),
+ CONFIG_KEY VARCHAR(255) NOT NULL,
+ CONFIG_VAL VARCHAR(255) NOT NULL,
PRIMARY KEY(CONFIG_KEY, CONFIG_VAL)
-);
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO CONFIGURATION (CONFIG_KEY, CONFIG_VAL) VALUES('user_profile_catalog_version', '0.17');
diff --git a/modules/registry-refactoring/src/main/java/org/apache/airavata/registry/core/entities/workspacecatalog/NSFDemographicsEntity.java b/modules/registry-refactoring/src/main/java/org/apache/airavata/registry/core/entities/workspacecatalog/NSFDemographicsEntity.java
deleted file mode 100644
index 0bcbafa..0000000
--- a/modules/registry-refactoring/src/main/java/org/apache/airavata/registry/core/entities/workspacecatalog/NSFDemographicsEntity.java
+++ /dev/null
@@ -1,94 +0,0 @@
-/*
- *
- * 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.
- *
-*/
-package org.apache.airavata.registry.core.entities.workspacecatalog;
-
-import javax.persistence.*;
-import java.util.List;
-
-@Entity
-@Table(name = "WORKSPACE_NSF_DEMOGRAPHIC")
-public class NSFDemographicsEntity {
- private String airavataInternalUserId;
- private String gender;
- private List<String> ethnicities;
- private List<String> races;
- private List<String> disabilities;
- private UserProfileEntity userProfile;
-
- @Id
- @Column(name = "AIRAVATA_INTERNAL_USER_ID")
- public String getAiravataInternalUserId() {
- return airavataInternalUserId;
- }
-
- public void setAiravataInternalUserId(String userId) {
- this.airavataInternalUserId = userId;
- }
-
- @Column(name = "GENDER")
- public String getGender() {
- return gender;
- }
-
- public void setGender(String gender) {
- this.gender = gender;
- }
-
- @ElementCollection
- @CollectionTable(name="NSF_DEMOGRAPHIC_ETHNICITY", joinColumns = @JoinColumn(name="AIRAVATA_INTERNAL_USER_ID"))
- public List<String> getEthnicities() {
- return ethnicities;
- }
-
- public void setEthnicities(List<String> ethnicities) {
- this.ethnicities = ethnicities;
- }
-
- @ElementCollection
- @CollectionTable(name="NSF_DEMOGRAPHIC_RACE", joinColumns = @JoinColumn(name="AIRAVATA_INTERNAL_USER_ID"))
- public List<String> getRaces() {
- return races;
- }
-
- public void setRaces(List<String> races) {
- this.races = races;
- }
-
- @ElementCollection
- @CollectionTable(name="NSF_DEMOGRAPHIC_DISABILITY", joinColumns = @JoinColumn(name="AIRAVATA_INTERNAL_USER_ID"))
- public List<String> getDisabilities() {
- return disabilities;
- }
-
- public void setDisabilities(List<String> disabilities) {
- this.disabilities = disabilities;
- }
-
- @OneToOne(targetEntity = UserProfileEntity.class, cascade = CascadeType.ALL)
- @PrimaryKeyJoinColumn(name = "AIRAVATA_INTERNAL_USER_ID", referencedColumnName = "AIRAVATA_INTERNAL_USER_ID")
- public UserProfileEntity getUserProfile() {
- return userProfile;
- }
-
- public void setUserProfile(UserProfileEntity userProfile) {
- this.userProfile = userProfile;
- }
-}
\ No newline at end of file
diff --git a/modules/registry-refactoring/src/main/java/org/apache/airavata/registry/core/entities/workspacecatalog/UserProfileEntity.java b/modules/registry-refactoring/src/main/java/org/apache/airavata/registry/core/entities/workspacecatalog/UserProfileEntity.java
deleted file mode 100644
index 7dd51ed..0000000
--- a/modules/registry-refactoring/src/main/java/org/apache/airavata/registry/core/entities/workspacecatalog/UserProfileEntity.java
+++ /dev/null
@@ -1,247 +0,0 @@
-/*
- *
- * 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.
- *
-*/
-package org.apache.airavata.registry.core.entities.workspacecatalog;
-
-import javax.persistence.*;
-import java.util.List;
-
-@Entity
-@Table(name="WORKSPACE_USER_PROFILE")
-public class UserProfileEntity {
- private String airavataInternalUserId;
- private String userId;
- private String gatewayId;
- private String userModelVersion;
- private String userName;
- private String orcidId;
- private String country;
- private String homeOrganization;
- private String orginationAffiliation;
- private long creationTime;
- private long lastAccessTime;
- private long validUntil;
- private String state;
- private String comments;
- private List<String> labeledURI;
- private String gpgKey;
- private String timeZone;
-
- private List<String> nationality;
- private List<String> emails;
- private List<String> phones;
- private NSFDemographicsEntity nsfDemographics;
-
- @Id
- @Column(name = "AIRAVATA_INTERNAL_USER_ID")
- public String getAiravataInternalUserId() {
- return airavataInternalUserId;
- }
-
- public void setAiravataInternalUserId(String id) {
- this.airavataInternalUserId = id;
- }
-
- @Column(name = "USER_ID")
- public String getUserId() {
- return userId;
- }
-
- public void setUserId(String userId) {
- this.userId = userId;
- }
-
- @Column(name = "GATEWAY_ID")
- public String getGatewayId() {
- return gatewayId;
- }
-
- public void setGatewayId(String gatewayId) {
- this.gatewayId = gatewayId;
- }
-
- @Column(name = "USER_MODEL_VERSION")
- public String getUserModelVersion() {
- return userModelVersion;
- }
-
- public void setUserModelVersion(String userModelVersion) {
- this.userModelVersion = userModelVersion;
- }
-
- @ElementCollection
- @CollectionTable(name="USER_PROFILE_EMAIL", joinColumns = @JoinColumn(name="AIRAVATA_INTERNAL_USER_ID"))
- public List<String> getEmails() {
- return emails;
- }
-
- public void setEmails(List<String> emails) {
- this.emails = emails;
- }
-
- @Column(name = "USER_NAME")
- public String getUserName() {
- return userName;
- }
-
- public void setUserName(String userName) {
- this.userName = userName;
- }
-
- @Column(name = "ORCID_ID")
- public String getOrcidId() {
- return orcidId;
- }
-
- public void setOrcidId(String orcidId) {
- this.orcidId = orcidId;
- }
-
- @ElementCollection
- @CollectionTable(name="USER_PROFILE_PHONE", joinColumns = @JoinColumn(name="AIRAVATA_INTERNAL_USER_ID"))
- public List<String> getPhones() {
- return phones;
- }
-
- public void setPhones(List<String> phones) {
- this.phones = phones;
- }
-
- @Column(name = "COUNTRY")
- public String getCountry() {
- return country;
- }
-
- public void setCountry(String country) {
- this.country = country;
- }
-
- @ElementCollection
- @CollectionTable(name="USER_PROFILE_NATIONALITY", joinColumns = @JoinColumn(name="AIRAVATA_INTERNAL_USER_ID"))
- public List<String> getNationality() {
- return nationality;
- }
-
- public void setNationality(List<String> nationality) {
- this.nationality = nationality;
- }
-
- @Column(name = "HOME_ORGANIZATION")
- public String getHomeOrganization() {
- return homeOrganization;
- }
-
- public void setHomeOrganization(String homeOrganization) {
- this.homeOrganization = homeOrganization;
- }
-
- @Column(name = "ORIGINATION_AFFILIATION")
- public String getOrginationAffiliation() {
- return orginationAffiliation;
- }
-
- public void setOrginationAffiliation(String orginationAffiliation) {
- this.orginationAffiliation = orginationAffiliation;
- }
-
- @Column(name="CREATION_TIME")
- public long getCreationTime() {
- return creationTime;
- }
-
- public void setCreationTime(long creationTime) {
- this.creationTime = creationTime;
- }
-
- @Column(name = "LAST_ACCESS_TIME")
- public long getLastAccessTime() {
- return lastAccessTime;
- }
-
- public void setLastAccessTime(long lastAccessTime) {
- this.lastAccessTime = lastAccessTime;
- }
-
- @Column(name = "VALID_UNTIL")
- public long getValidUntil() {
- return validUntil;
- }
-
- public void setValidUntil(long validUntil) {
- this.validUntil = validUntil;
- }
-
- @Column(name = "STATE")
- public String getState() {
- return state;
- }
-
- public void setState(String state) {
- this.state = state;
- }
-
- @Lob
- @Column(name = "COMMENTS")
- public String getComments() {
- return comments;
- }
-
- public void setComments(String comments) {
- this.comments = comments;
- }
-
- @ElementCollection
- @CollectionTable(name="USER_PROFILE_LABELED_URI", joinColumns = @JoinColumn(name="AIRAVATA_INTERNAL_USER_ID"))
- public List<String> getLabeledURI() {
- return labeledURI;
- }
-
- public void setLabeledURI(List<String> labeledURI) {
- this.labeledURI = labeledURI;
- }
-
- @Lob
- @Column(name = "GPG_KEY")
- public String getGpgKey() {
- return gpgKey;
- }
-
- public void setGpgKey(String gpgKey) {
- this.gpgKey = gpgKey;
- }
-
- @Column(name = "TIME_ZONE")
- public String getTimeZone() {
- return timeZone;
- }
-
- public void setTimeZone(String timeZone) {
- this.timeZone = timeZone;
- }
-
- @OneToOne(targetEntity = NSFDemographicsEntity.class, cascade = CascadeType.ALL, mappedBy = "userProfile")
- public NSFDemographicsEntity getNsfDemographics() {
- return nsfDemographics;
- }
-
- public void setNsfDemographics(NSFDemographicsEntity nsfDemographics) {
- this.nsfDemographics = nsfDemographics;
- }
-}
\ No newline at end of file
diff --git a/modules/registry-refactoring/src/main/resources/appcatalog-derby.sql b/modules/registry-refactoring/src/main/resources/appcatalog-derby.sql
index c2d0d97..da7bb1b 100644
--- a/modules/registry-refactoring/src/main/resources/appcatalog-derby.sql
+++ b/modules/registry-refactoring/src/main/resources/appcatalog-derby.sql
@@ -508,7 +508,7 @@ CREATE TABLE LOCAL_DATA_MOVEMENT
CREATE TABLE STORAGE_PREFERENCE
(
GATEWAY_ID VARCHAR(255) NOT NULL,
- STORAGE_RESOURCE_ID VARCHAR(255),
+ STORAGE_RESOURCE_ID VARCHAR(255) NOT NULL,
LOGIN_USERNAME VARCHAR(255),
FS_ROOT_LOCATION VARCHAR(255),
RESOURCE_CS_TOKEN VARCHAR(255),
diff --git a/modules/registry-refactoring/src/main/resources/appcatalog-mysql.sql b/modules/registry-refactoring/src/main/resources/appcatalog-mysql.sql
index 29822c8..fa68ada 100644
--- a/modules/registry-refactoring/src/main/resources/appcatalog-mysql.sql
+++ b/modules/registry-refactoring/src/main/resources/appcatalog-mysql.sql
@@ -501,7 +501,7 @@ CREATE TABLE LOCAL_DATA_MOVEMENT
CREATE TABLE STORAGE_PREFERENCE
(
GATEWAY_ID VARCHAR(255) NOT NULL,
- STORAGE_RESOURCE_ID VARCHAR(255),
+ STORAGE_RESOURCE_ID VARCHAR(255) NOT NULL,
LOGIN_USERNAME VARCHAR(255),
FS_ROOT_LOCATION VARCHAR(255),
RESOURCE_CS_TOKEN VARCHAR(255),
diff --git a/modules/registry/release-migration-scripts/next/DeltaScripts/appCatalog_schema_delta.sql b/modules/registry/release-migration-scripts/next/DeltaScripts/appCatalog_schema_delta.sql
index c446fad..43e8957 100644
--- a/modules/registry/release-migration-scripts/next/DeltaScripts/appCatalog_schema_delta.sql
+++ b/modules/registry/release-migration-scripts/next/DeltaScripts/appCatalog_schema_delta.sql
@@ -124,6 +124,8 @@ ALTER TABLE `PREJOB_COMMAND` ENGINE = InnoDB;
ALTER TABLE `SCP_DATA_MOVEMENT` ENGINE = InnoDB;
ALTER TABLE `BATCH_QUEUE` ENGINE = InnoDB;
+-- Some of these column changes involve FK columns so need to temporarily turn off FK checks
+set FOREIGN_KEY_CHECKS=0;
ALTER TABLE `GROUP_COMPUTE_RESOURCE_PREFERENCE` CHANGE `RESERVATION_END_TIME` `RESERVATION_END_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE `GROUP_COMPUTE_RESOURCE_PREFERENCE` CHANGE `PREFERED_JOB_SUB_PROTOCOL` `PREFERED_JOB_SUB_PROTOCOL` varchar(255) DEFAULT NULL;
ALTER TABLE `GROUP_COMPUTE_RESOURCE_PREFERENCE` CHANGE `PREFERED_DATA_MOVE_PROTOCOL` `PREFERED_DATA_MOVE_PROTOCOL` varchar(255) DEFAULT NULL;
@@ -182,6 +184,7 @@ ALTER TABLE `COMPUTE_RESOURCE_PREFERENCE` CHANGE `RESERVATION_END_TIME` `RESERVA
ALTER TABLE `APPLICATION_DEPLOYMENT` CHANGE `DEFAULT_WALLTIME` `DEFAULT_WALLTIME` int(11) DEFAULT NULL;
ALTER TABLE `APPLICATION_DEPLOYMENT` CHANGE `DEFAULT_CPU_COUNT` `DEFAULT_CPU_COUNT` int(11) DEFAULT NULL;
ALTER TABLE `APPLICATION_DEPLOYMENT` CHANGE `DEFAULT_NODE_COUNT` `DEFAULT_NODE_COUNT` int(11) DEFAULT NULL;
+set FOREIGN_KEY_CHECKS=1;
ALTER TABLE `GATEWAY_PROFILE` DROP IF EXISTS `DATA_STORAGE_HOST`;
@@ -251,4 +254,15 @@ ALTER TABLE `POSTJOB_COMMAND` ADD CONSTRAINT `POSTJOB_COMMAND_ibfk_1` FOREIGN KE
ALTER TABLE `PREJOB_COMMAND` ADD CONSTRAINT `PREJOB_COMMAND_ibfk_1` FOREIGN KEY IF NOT EXISTS (`APPDEPLOYMENT_ID`) REFERENCES `APPLICATION_DEPLOYMENT` (`DEPLOYMENT_ID`) ON DELETE CASCADE;
ALTER TABLE `SSH_ACCOUNT_PROVISIONER_CONFIG` ADD CONSTRAINT `SSH_ACCOUNT_PROVISIONER_CONFIG_ibfk_1` FOREIGN KEY IF NOT EXISTS (`GATEWAY_ID`, `RESOURCE_ID`) REFERENCES `COMPUTE_RESOURCE_PREFERENCE` (`GATEWAY_ID`, `RESOURCE_ID`) ON DELETE CASCADE;
ALTER TABLE `SSH_JOB_SUBMISSION` ADD CONSTRAINT `SSH_JOB_SUBMISSION_ibfk_1` FOREIGN KEY IF NOT EXISTS (`RESOURCE_JOB_MANAGER_ID`) REFERENCES `RESOURCE_JOB_MANAGER` (`RESOURCE_JOB_MANAGER_ID`);
+-- Note: to deal with STORAGE_INTERFACEs that reference non-existent STORAGE_RESOURCEs, use the following to clean up orphans
+-- MariaDB [app_catalog]> create table temp1 as select STORAGE_RESOURCE_ID from STORAGE_INTERFACE si where not exists (select 1 from STORAGE_RESOURCE sr where sr.STORAGE_RESOURCE_ID = si.STORAGE_RESOURCE_ID);
+-- MariaDB [app_catalog]> delete from STORAGE_INTERFACE where STORAGE_RESOURCE_ID in (select STORAGE_RESOURCE_ID from temp1);
ALTER TABLE `STORAGE_INTERFACE` ADD CONSTRAINT `STORAGE_INTERFACE_ibfk_1` FOREIGN KEY IF NOT EXISTS (`STORAGE_RESOURCE_ID`) REFERENCES `STORAGE_RESOURCE` (`STORAGE_RESOURCE_ID`) ON DELETE CASCADE;
+
+-- Older tables that no longer exist, or don't exist with those names
+DROP TABLE IF EXISTS `DATA_STORAGE_INTERFACE`;
+DROP TABLE IF EXISTS `GATEWAY_CLIENT_CREDENTIAL`;
+
+-- misnamed column
+ALTER TABLE `PARSER_CONNECTOR_INPUT` DROP IF EXISTS `PARENT_OUTPUT_ID`;
+ALTER TABLE `PARSING_TEMPLATE_INPUT` DROP IF EXISTS `PARSER_INPUT_ID`;
diff --git a/modules/registry/release-migration-scripts/next/DeltaScripts/credential_store_schema_delta.sql b/modules/registry/release-migration-scripts/next/DeltaScripts/credential_store_schema_delta.sql
new file mode 100644
index 0000000..cf53a4e
--- /dev/null
+++ b/modules/registry/release-migration-scripts/next/DeltaScripts/credential_store_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.
+--
+
+-- AIRAVATA-2938: bring database schema into sync with registry-refactoring DB init scripts
+set FOREIGN_KEYS=0;
+ALTER TABLE `CREDENTIALS` CHANGE `TIME_PERSISTED` `TIME_PERSISTED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
+ALTER TABLE `CREDENTIALS` CHANGE `DESCRIPTION` `DESCRIPTION` varchar(500) DEFAULT NULL;
+ALTER TABLE `CREDENTIALS` CHANGE `TOKEN_ID` `TOKEN_ID` varchar(100) NOT NULL;
+ALTER TABLE `COMMUNITY_USER` CHANGE `TOKEN_ID` `TOKEN_ID` varchar(100) NOT NULL;
+ALTER TABLE `COMMUNITY_USER` CHANGE `GATEWAY_ID` `GATEWAY_ID` varchar(100) NOT NULL;
+ALTER TABLE `COMMUNITY_USER` CHANGE `COMMUNITY_USER_NAME` `COMMUNITY_USER_NAME` varchar(100) NOT NULL;
+ALTER TABLE `CREDENTIALS` CHANGE `GATEWAY_ID` `GATEWAY_ID` varchar(100) NOT NULL;
+set FOREIGN_KEYS=1;
diff --git a/modules/registry/release-migration-scripts/next/DeltaScripts/profile_service_schema_delta.sql b/modules/registry/release-migration-scripts/next/DeltaScripts/profile_service_schema_delta.sql
index 9e63f29..9ccaeac 100644
--- a/modules/registry/release-migration-scripts/next/DeltaScripts/profile_service_schema_delta.sql
+++ b/modules/registry/release-migration-scripts/next/DeltaScripts/profile_service_schema_delta.sql
@@ -1 +1,24 @@
alter database profile_service character set = 'latin1';
+
+-- AIRAVATA-2938: bring database schema into sync with registry-refactoring DB init scripts
+set FOREIGN_KEY_CHECKS=0;
+ALTER TABLE `NSF_DEMOGRAPHIC_RACE` CHANGE COLUMN IF EXISTS RACES RACE varchar(255) NOT NULL;
+ALTER TABLE `NSF_DEMOGRAPHIC_DISABILITY` CHANGE COLUMN IF EXISTS DISABILITIES DISABILITY varchar(255) NOT NULL;
+ALTER TABLE `USER_PROFILE_PHONE` CHANGE COLUMN IF EXISTS PHONES PHONE varchar(255) NOT NULL;
+ALTER TABLE `USER_PROFILE_EMAIL` CHANGE COLUMN IF EXISTS EMAILS EMAIL varchar(255) NOT NULL;
+ALTER TABLE `NSF_DEMOGRAPHIC_ETHNICITY` CHANGE COLUMN IF EXISTS ETHNICITIES ETHNICITY varchar(255) NOT NULL;
+ALTER TABLE `NSF_DEMOGRAPHIC_DISABILITY` ADD PRIMARY KEY IF NOT EXISTS (`AIRAVATA_INTERNAL_USER_ID`,`DISABILITY`);
+ALTER TABLE `NSF_DEMOGRAPHIC_DISABILITY` DROP INDEX IF EXISTS `AIRAVATA_INTERNAL_USER_ID`;
+ALTER TABLE `NSF_DEMOGRAPHIC_ETHNICITY` ADD PRIMARY KEY IF NOT EXISTS (`AIRAVATA_INTERNAL_USER_ID`,`ETHNICITY`);
+ALTER TABLE `NSF_DEMOGRAPHIC_ETHNICITY` DROP INDEX IF EXISTS `AIRAVATA_INTERNAL_USER_ID`;
+ALTER TABLE `NSF_DEMOGRAPHIC_RACE` ADD PRIMARY KEY IF NOT EXISTS (`AIRAVATA_INTERNAL_USER_ID`,`RACE`);
+ALTER TABLE `NSF_DEMOGRAPHIC_RACE` DROP INDEX IF EXISTS `AIRAVATA_INTERNAL_USER_ID`;
+ALTER TABLE `USER_PROFILE_EMAIL` ADD PRIMARY KEY IF NOT EXISTS (`AIRAVATA_INTERNAL_USER_ID`,`EMAIL`);
+ALTER TABLE `USER_PROFILE_EMAIL` DROP INDEX IF EXISTS `AIRAVATA_INTERNAL_USER_ID`;
+ALTER TABLE `USER_PROFILE_LABELED_URI` ADD PRIMARY KEY IF NOT EXISTS (`AIRAVATA_INTERNAL_USER_ID`,`LABELED_URI`);
+ALTER TABLE `USER_PROFILE_LABELED_URI` DROP INDEX IF EXISTS `AIRAVATA_INTERNAL_USER_ID`;
+ALTER TABLE `USER_PROFILE_NATIONALITY` ADD PRIMARY KEY IF NOT EXISTS (`AIRAVATA_INTERNAL_USER_ID`,`NATIONALITY`);
+ALTER TABLE `USER_PROFILE_NATIONALITY` DROP INDEX IF EXISTS `AIRAVATA_INTERNAL_USER_ID`;
+ALTER TABLE `USER_PROFILE_PHONE` ADD PRIMARY KEY IF NOT EXISTS (`AIRAVATA_INTERNAL_USER_ID`,`PHONE`);
+ALTER TABLE `USER_PROFILE_PHONE` DROP INDEX IF EXISTS `AIRAVATA_INTERNAL_USER_ID`;
+set FOREIGN_KEY_CHECKS=1;