This is an automated email from the ASF dual-hosted git repository. jshao pushed a commit to branch branch-0.6 in repository https://gitbox.apache.org/repos/asf/gravitino.git
The following commit(s) were added to refs/heads/branch-0.6 by this push: new a7b136af1 [#3817] fix(catalog-postgresql): Fix bugs when creating a schema or table using upper-case name. (#4376) a7b136af1 is described below commit a7b136af1f7324c8057a1e43f33be420c87ef0a8 Author: github-actions[bot] <41898282+github-actions[bot]@users.noreply.github.com> AuthorDate: Mon Aug 5 22:45:53 2024 +0800 [#3817] fix(catalog-postgresql): Fix bugs when creating a schema or table using upper-case name. (#4376) ### What changes were proposed in this pull request? Double quote table name or schema name to support upper case name. ### Why are the changes needed? It's a bug needs to be fixed. Fix: #3817 ### Does this PR introduce _any_ user-facing change? N/A. ### How was this patch tested? Add IT. Co-authored-by: Qi Yu <y...@datastrato.com> --- .../operation/PostgreSqlSchemaOperations.java | 12 ++++++-- .../operation/PostgreSqlTableOperations.java | 6 ++++ .../integration/test/CatalogPostgreSqlIT.java | 34 ++++++++++++++++++++++ 3 files changed, 50 insertions(+), 2 deletions(-) diff --git a/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/operation/PostgreSqlSchemaOperations.java b/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/operation/PostgreSqlSchemaOperations.java index ebddbadaa..04850055a 100644 --- a/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/operation/PostgreSqlSchemaOperations.java +++ b/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/operation/PostgreSqlSchemaOperations.java @@ -18,6 +18,8 @@ */ package org.apache.gravitino.catalog.postgresql.operation; +import static org.apache.gravitino.catalog.postgresql.operation.PostgreSqlTableOperations.PG_QUOTE; + import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; @@ -117,11 +119,13 @@ public class PostgreSqlSchemaOperations extends JdbcDatabaseOperations { "PostgreSQL does not support properties on database create."); } - StringBuilder sqlBuilder = new StringBuilder("CREATE SCHEMA " + schema + ";"); + StringBuilder sqlBuilder = new StringBuilder("CREATE SCHEMA \"" + schema + "\";"); if (StringUtils.isNotEmpty(comment)) { sqlBuilder .append("COMMENT ON SCHEMA ") + .append(PG_QUOTE) .append(schema) + .append(PG_QUOTE) .append(" IS '") .append(comment) .append("'"); @@ -167,7 +171,11 @@ public class PostgreSqlSchemaOperations extends JdbcDatabaseOperations { } private String getShowSchemaCommentSql(String schema) { - return String.format("SELECT obj_description('%s'::regnamespace) as comment", schema); + return String.format( + "SELECT obj_description(n.oid, 'pg_namespace') AS comment\n" + + "FROM pg_catalog.pg_namespace n\n" + + "WHERE n.nspname = '%s';\n", + schema); } private String getSchemaComment(String schema, Connection connection) throws SQLException { diff --git a/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/operation/PostgreSqlTableOperations.java b/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/operation/PostgreSqlTableOperations.java index fde21ce48..639544105 100644 --- a/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/operation/PostgreSqlTableOperations.java +++ b/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/operation/PostgreSqlTableOperations.java @@ -159,7 +159,9 @@ public class PostgreSqlTableOperations extends JdbcTableOperations { sqlBuilder .append(NEW_LINE) .append(TABLE_COMMENT) + .append(PG_QUOTE) .append(tableName) + .append(PG_QUOTE) .append(IS) .append(comment) .append("';"); @@ -171,9 +173,13 @@ public class PostgreSqlTableOperations extends JdbcTableOperations { sqlBuilder .append(NEW_LINE) .append(COLUMN_COMMENT) + .append(PG_QUOTE) .append(tableName) + .append(PG_QUOTE) .append(".") + .append(PG_QUOTE) .append(jdbcColumn.name()) + .append(PG_QUOTE) .append(IS) .append(jdbcColumn.comment()) .append("';")); diff --git a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/integration/test/CatalogPostgreSqlIT.java b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/integration/test/CatalogPostgreSqlIT.java index 1d2b3685a..947538750 100644 --- a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/integration/test/CatalogPostgreSqlIT.java +++ b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/integration/test/CatalogPostgreSqlIT.java @@ -292,6 +292,40 @@ public class CatalogPostgreSqlIT extends AbstractIT { Assertions.assertTrue(column.isPresent()); } + @Test + void testCreateUpperCaseSchemaAndTable() { + // Create table from Gravitino API + Column[] columns = columnsWithSpecialNames(); + + String tableN = GravitinoITUtils.genRandomName("postgresql_it_table").toUpperCase(); + String schemaN = GravitinoITUtils.genRandomName("postgresql_it_schema").toUpperCase(); + + // Create a schema with upper case name + catalog.asSchemas().createSchema(schemaN, schema_comment, Collections.EMPTY_MAP); + NameIdentifier tableIdentifier = NameIdentifier.of(schemaN, tableN); + Distribution distribution = Distributions.NONE; + + SortOrder[] sortOrders = new SortOrder[0]; + Transform[] partitioning = Transforms.EMPTY_TRANSFORM; + + Map<String, String> properties = createProperties(); + TableCatalog tableCatalog = catalog.asTableCatalog(); + // Create a table with upper case name + tableCatalog.createTable( + tableIdentifier, + columns, + table_comment, + properties, + partitioning, + distribution, + sortOrders); + + Table t = tableCatalog.loadTable(tableIdentifier); + Optional<Column> column = + Arrays.stream(t.columns()).filter(c -> c.name().equals("binary")).findFirst(); + Assertions.assertTrue(column.isPresent()); + } + private Map<String, String> createProperties() { Map<String, String> properties = Maps.newHashMap(); return properties;