This is an automated email from the ASF dual-hosted git repository. machristie pushed a commit to branch develop in repository https://gitbox.apache.org/repos/asf/airavata.git
commit 33300327fc98d34ba5a62dbe75aea2678120a065 Author: Marcus Christie <[email protected]> AuthorDate: Thu Apr 27 16:22:34 2023 -0400 AIRAVATA-3697 Db migrations and changing PRODUCT_NAME, REPLICA_NAME to clobs --- modules/ide-integration/README.md | 2 +- .../init/05-replica-catalog-migrations.sql | 35 ++++++++++++++++++++++ .../entities/replicacatalog/DataProductEntity.java | 1 + .../replicacatalog/DataReplicaLocationEntity.java | 1 + .../src/main/resources/replicacatalog-mysql.sql | 35 ++++++++++------------ .../DeltaScripts/replicaCatalog_schema_delta.sql | 34 ++++++++++++++++++++- 6 files changed, 87 insertions(+), 21 deletions(-) diff --git a/modules/ide-integration/README.md b/modules/ide-integration/README.md index e661c4d83c..6ac08fa260 100644 --- a/modules/ide-integration/README.md +++ b/modules/ide-integration/README.md @@ -60,7 +60,7 @@ Using this module, you can setup a full Airavata installation inside Intelij IDE * Apply any database migrations. Go to src/main/containers directory and run ``` - cat ./database_scripts/init/*-migrations.sql | docker exec -i resources_db_1 mysql -p123456 + cat ./database_scripts/init/*-migrations.sql | docker exec -i containers-db-1 mysql -p123456 ``` * Wait until all the services come up. This will initialize all utilities required to start Airavata server diff --git a/modules/ide-integration/src/main/containers/database_scripts/init/05-replica-catalog-migrations.sql b/modules/ide-integration/src/main/containers/database_scripts/init/05-replica-catalog-migrations.sql new file mode 100644 index 0000000000..db5e878fb9 --- /dev/null +++ b/modules/ide-integration/src/main/containers/database_scripts/init/05-replica-catalog-migrations.sql @@ -0,0 +1,35 @@ +USE replica_catalog; + +-- AIRAVATA-3697: Support file names that have UTF8 characters and that are long +SET + FOREIGN_KEY_CHECKS = 0; + +ALTER DATABASE replica_catalog CHARACTER SET = utf8 COLLATE = utf8_bin; + +ALTER TABLE + DATA_PRODUCT CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +ALTER TABLE + DATA_REPLICA_LOCATION CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +ALTER TABLE + DATA_PRODUCT_METADATA CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +ALTER TABLE + DATA_REPLICA_METADATA CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +ALTER TABLE + CONFIGURATION CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +SET + FOREIGN_KEY_CHECKS = 1; + +ALTER TABLE + DATA_REPLICA_LOCATION +MODIFY + REPLICA_NAME text; + +ALTER TABLE + DATA_PRODUCT +MODIFY + PRODUCT_NAME text; diff --git a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/replicacatalog/DataProductEntity.java b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/replicacatalog/DataProductEntity.java index ba10d59620..e5c2eeac0f 100644 --- a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/replicacatalog/DataProductEntity.java +++ b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/replicacatalog/DataProductEntity.java @@ -43,6 +43,7 @@ public class DataProductEntity implements Serializable { @Column(name = "GATEWAY_ID") private String gatewayId; + @Lob @Column(name = "PRODUCT_NAME") private String productName; diff --git a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/replicacatalog/DataReplicaLocationEntity.java b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/replicacatalog/DataReplicaLocationEntity.java index ca0360b306..d943163eab 100644 --- a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/replicacatalog/DataReplicaLocationEntity.java +++ b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/entities/replicacatalog/DataReplicaLocationEntity.java @@ -43,6 +43,7 @@ public class DataReplicaLocationEntity implements Serializable { @Column(name = "PRODUCT_URI") private String productUri; + @Lob @Column(name = "REPLICA_NAME") private String replicaName; diff --git a/modules/registry/registry-core/src/main/resources/replicacatalog-mysql.sql b/modules/registry/registry-core/src/main/resources/replicacatalog-mysql.sql index c3890702ca..7c8a0ee11d 100644 --- a/modules/registry/registry-core/src/main/resources/replicacatalog-mysql.sql +++ b/modules/registry/registry-core/src/main/resources/replicacatalog-mysql.sql @@ -18,12 +18,10 @@ * under the License. * */ - -CREATE TABLE DATA_PRODUCT -( +CREATE TABLE DATA_PRODUCT ( PRODUCT_URI VARCHAR (255), GATEWAY_ID VARCHAR (255), - PRODUCT_NAME VARCHAR (255), + PRODUCT_NAME TEXT, PRODUCT_DESCRIPTION VARCHAR (255), OWNER_NAME VARCHAR (255), PARENT_PRODUCT_URI VARCHAR (255), @@ -33,13 +31,12 @@ CREATE TABLE DATA_PRODUCT PRODUCT_TYPE VARCHAR(10), PRIMARY KEY (PRODUCT_URI), FOREIGN KEY (PARENT_PRODUCT_URI) REFERENCES DATA_PRODUCT (PRODUCT_URI) ON DELETE CASCADE -)ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin; -CREATE TABLE DATA_REPLICA_LOCATION -( +CREATE TABLE DATA_REPLICA_LOCATION ( REPLICA_ID VARCHAR (255), PRODUCT_URI VARCHAR (255) NOT NULL, - REPLICA_NAME VARCHAR (255), + REPLICA_NAME TEXT, REPLICA_DESCRIPTION VARCHAR (255), STORAGE_RESOURCE_ID VARCHAR (255), FILE_PATH VARCHAR (1024), @@ -50,31 +47,31 @@ CREATE TABLE DATA_REPLICA_LOCATION REPLICA_PERSISTENT_TYPE VARCHAR(10), PRIMARY KEY (REPLICA_ID), FOREIGN KEY (PRODUCT_URI) REFERENCES DATA_PRODUCT(PRODUCT_URI) ON DELETE CASCADE -)ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin; -CREATE TABLE DATA_PRODUCT_METADATA -( +CREATE TABLE DATA_PRODUCT_METADATA ( PRODUCT_URI VARCHAR(255) NOT NULL, METADATA_KEY VARCHAR(255), METADATA_VALUE VARCHAR(255), PRIMARY KEY(PRODUCT_URI, METADATA_KEY), FOREIGN KEY (PRODUCT_URI) REFERENCES DATA_PRODUCT(PRODUCT_URI) ON DELETE CASCADE -)ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin; -CREATE TABLE DATA_REPLICA_METADATA -( +CREATE TABLE DATA_REPLICA_METADATA ( REPLICA_ID VARCHAR(255) NOT NULL, METADATA_KEY VARCHAR(255), METADATA_VALUE VARCHAR(255), PRIMARY KEY(REPLICA_ID, METADATA_KEY), FOREIGN KEY (REPLICA_ID) REFERENCES DATA_REPLICA_LOCATION(REPLICA_ID) ON DELETE CASCADE -)ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin; -CREATE TABLE CONFIGURATION -( +CREATE TABLE CONFIGURATION ( CONFIG_KEY VARCHAR(255), CONFIG_VAL VARCHAR(255), PRIMARY KEY(CONFIG_KEY, CONFIG_VAL) -)ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin; -INSERT INTO CONFIGURATION (CONFIG_KEY, CONFIG_VAL) VALUES('data_catalog_version', '0.16'); +INSERT INTO + CONFIGURATION (CONFIG_KEY, CONFIG_VAL) +VALUES +('data_catalog_version', '0.16'); diff --git a/modules/registry/release-migration-scripts/next/DeltaScripts/replicaCatalog_schema_delta.sql b/modules/registry/release-migration-scripts/next/DeltaScripts/replicaCatalog_schema_delta.sql index 942ff54c85..3b4645208f 100644 --- a/modules/registry/release-migration-scripts/next/DeltaScripts/replicaCatalog_schema_delta.sql +++ b/modules/registry/release-migration-scripts/next/DeltaScripts/replicaCatalog_schema_delta.sql @@ -17,5 +17,37 @@ -- specific language governing permissions and limitations -- under the License. -- +USE replica_catalog; -use replica_catalog; +SET + FOREIGN_KEY_CHECKS = 0; + +ALTER DATABASE replica_catalog CHARACTER SET = utf8 COLLATE = utf8_bin; + +ALTER TABLE + DATA_PRODUCT CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +ALTER TABLE + DATA_REPLICA_LOCATION CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +ALTER TABLE + DATA_PRODUCT_METADATA CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +ALTER TABLE + DATA_REPLICA_METADATA CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +ALTER TABLE + CONFIGURATION CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +SET + FOREIGN_KEY_CHECKS = 1; + +ALTER TABLE + DATA_REPLICA_LOCATION +MODIFY + REPLICA_NAME text; + +ALTER TABLE + DATA_PRODUCT +MODIFY + PRODUCT_NAME text;
