This is an automated email from the ASF dual-hosted git repository. machristie pushed a commit to branch AIRAVATA-3697 in repository https://gitbox.apache.org/repos/asf/airavata.git
commit 6fcefb2c476fd73cc5070d378f0730713f5ac65f Author: Marcus Christie <[email protected]> AuthorDate: Mon Jul 3 15:45:10 2023 -0400 AIRAVATA-3697 Switching to column level utf8 character set --- .../init/05-replica-catalog-migrations.sql | 32 ++++++++------------- .../src/main/resources/replicacatalog-mysql.sql | 22 +++++++-------- .../DeltaScripts/replicaCatalog_schema_delta.sql | 33 +++++++++------------- 3 files changed, 36 insertions(+), 51 deletions(-) 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 index db5e878fb9..1c3b8d1c3f 100644 --- 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 @@ -1,35 +1,27 @@ 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; + DATA_PRODUCT +MODIFY + PRODUCT_NAME text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ALTER TABLE - DATA_REPLICA_METADATA CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + DATA_PRODUCT +MODIFY + PRODUCT_DESCRIPTION varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ALTER TABLE - CONFIGURATION CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; - -SET - FOREIGN_KEY_CHECKS = 1; + DATA_REPLICA_LOCATION +MODIFY + REPLICA_NAME text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ALTER TABLE DATA_REPLICA_LOCATION MODIFY - REPLICA_NAME text; + REPLICA_DESCRIPTION varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ALTER TABLE - DATA_PRODUCT + DATA_REPLICA_LOCATION MODIFY - PRODUCT_NAME text; + FILE_PATH varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; 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 7c8a0ee11d..99731e0a9c 100644 --- a/modules/registry/registry-core/src/main/resources/replicacatalog-mysql.sql +++ b/modules/registry/registry-core/src/main/resources/replicacatalog-mysql.sql @@ -21,8 +21,8 @@ CREATE TABLE DATA_PRODUCT ( PRODUCT_URI VARCHAR (255), GATEWAY_ID VARCHAR (255), - PRODUCT_NAME TEXT, - PRODUCT_DESCRIPTION VARCHAR (255), + PRODUCT_NAME TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, + PRODUCT_DESCRIPTION VARCHAR (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, OWNER_NAME VARCHAR (255), PARENT_PRODUCT_URI VARCHAR (255), PRODUCT_SIZE INT, @@ -31,15 +31,15 @@ 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 = utf8 COLLATE = utf8_bin; +) ENGINE = InnoDB DEFAULT CHARSET = latin1; CREATE TABLE DATA_REPLICA_LOCATION ( REPLICA_ID VARCHAR (255), PRODUCT_URI VARCHAR (255) NOT NULL, - REPLICA_NAME TEXT, - REPLICA_DESCRIPTION VARCHAR (255), + REPLICA_NAME TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, + REPLICA_DESCRIPTION VARCHAR (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, STORAGE_RESOURCE_ID VARCHAR (255), - FILE_PATH VARCHAR (1024), + FILE_PATH VARCHAR (1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, CREATION_TIME TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00', LAST_MODIFIED_TIME TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, VALID_UNTIL_TIME TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00', @@ -47,7 +47,7 @@ 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 = utf8 COLLATE = utf8_bin; +) ENGINE = InnoDB DEFAULT CHARSET = latin1; CREATE TABLE DATA_PRODUCT_METADATA ( PRODUCT_URI VARCHAR(255) NOT NULL, @@ -55,7 +55,7 @@ CREATE TABLE DATA_PRODUCT_METADATA ( 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 = utf8 COLLATE = utf8_bin; +) ENGINE = InnoDB DEFAULT CHARSET = latin1; CREATE TABLE DATA_REPLICA_METADATA ( REPLICA_ID VARCHAR(255) NOT NULL, @@ -63,15 +63,15 @@ CREATE TABLE DATA_REPLICA_METADATA ( 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 = utf8 COLLATE = utf8_bin; +) ENGINE = InnoDB DEFAULT CHARSET = latin1; CREATE TABLE CONFIGURATION ( CONFIG_KEY VARCHAR(255), CONFIG_VAL VARCHAR(255), PRIMARY KEY(CONFIG_KEY, CONFIG_VAL) -) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin; +) ENGINE = InnoDB DEFAULT CHARSET = latin1; INSERT INTO CONFIGURATION (CONFIG_KEY, CONFIG_VAL) VALUES -('data_catalog_version', '0.16'); + ('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 3b4645208f..8a23bf06c0 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 @@ -19,35 +19,28 @@ -- 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; - +-- AIRAVATA-3697: Support file names that have UTF8 characters and that are long 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; + DATA_PRODUCT +MODIFY + PRODUCT_NAME text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ALTER TABLE - DATA_REPLICA_METADATA CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + DATA_PRODUCT +MODIFY + PRODUCT_DESCRIPTION varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ALTER TABLE - CONFIGURATION CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; - -SET - FOREIGN_KEY_CHECKS = 1; + DATA_REPLICA_LOCATION +MODIFY + REPLICA_NAME text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ALTER TABLE DATA_REPLICA_LOCATION MODIFY - REPLICA_NAME text; + REPLICA_DESCRIPTION varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ALTER TABLE - DATA_PRODUCT + DATA_REPLICA_LOCATION MODIFY - PRODUCT_NAME text; + FILE_PATH varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
