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 2023/07/03 19:45:29 UTC
[airavata] 01/01: AIRAVATA-3697 Switching to column level utf8 character set
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 <ma...@apache.org>
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;