This is an automated email from the ASF dual-hosted git repository.
wuchunfu pushed a commit to branch dev
in repository https://gitbox.apache.org/repos/asf/seatunnel.git
The following commit(s) were added to refs/heads/dev by this push:
new 312ee866fb [Improve][Jdbc] Optimize index name conflicts when create
table for postgresql (#7875)
312ee866fb is described below
commit 312ee866fb1612e7a98e68c592da321e56175db4
Author: hailin0 <[email protected]>
AuthorDate: Mon Oct 21 09:54:35 2024 +0800
[Improve][Jdbc] Optimize index name conflicts when create table for
postgresql (#7875)
---
.../psql/PostgresCreateTableSqlBuilder.java | 22 ++++++++--------------
.../psql/PostgresCreateTableSqlBuilderTest.java | 16 +++++++++-------
2 files changed, 17 insertions(+), 21 deletions(-)
diff --git
a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/catalog/psql/PostgresCreateTableSqlBuilder.java
b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/catalog/psql/PostgresCreateTableSqlBuilder.java
index f7b98c1bb1..1fbfd7c095 100644
---
a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/catalog/psql/PostgresCreateTableSqlBuilder.java
+++
b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/catalog/psql/PostgresCreateTableSqlBuilder.java
@@ -30,11 +30,14 @@ import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import lombok.Getter;
+import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
+import java.util.UUID;
import java.util.stream.Collectors;
+@Slf4j
public class PostgresCreateTableSqlBuilder {
private List<Column> columns;
private PrimaryKey primaryKey;
@@ -161,10 +164,7 @@ public class PostgresCreateTableSqlBuilder {
}
private String buildUniqueKeySql(ConstraintKey constraintKey) {
- String constraintName = constraintKey.getConstraintName();
- if (constraintName.length() > 25) {
- constraintName = constraintName.substring(0, 25);
- }
+ String constraintName = UUID.randomUUID().toString().replace("-", "");
String indexColumns =
constraintKey.getColumnNames().stream()
.map(
@@ -175,16 +175,12 @@ public class PostgresCreateTableSqlBuilder {
constraintKeyColumn.getColumnName(),
fieldIde)))
.collect(Collectors.joining(", "));
- return "CONSTRAINT " + constraintName + " UNIQUE (" + indexColumns +
")";
+ return "CONSTRAINT \"" + constraintName + "\" UNIQUE (" + indexColumns
+ ")";
}
private String buildIndexKeySql(TablePath tablePath, ConstraintKey
constraintKey) {
- // We add table name to index name to avoid name conflict in PG
- // Since index name in PG should unique in the schema
- String constraintName = tablePath.getTableName() + "_" +
constraintKey.getConstraintName();
- if (constraintName.length() > 25) {
- constraintName = constraintName.substring(0, 25);
- }
+ // If the index name is omitted, PostgreSQL will choose an appropriate
name based on table
+ // name and indexed columns.
String indexColumns =
constraintKey.getColumnNames().stream()
.map(
@@ -196,9 +192,7 @@ public class PostgresCreateTableSqlBuilder {
fieldIde)))
.collect(Collectors.joining(", "));
- return "CREATE INDEX "
- + constraintName
- + " ON "
+ return "CREATE INDEX ON "
+ tablePath.getSchemaAndTableName("\"")
+ "("
+ indexColumns
diff --git
a/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/catalog/psql/PostgresCreateTableSqlBuilderTest.java
b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/catalog/psql/PostgresCreateTableSqlBuilderTest.java
index 37049eced3..bc204a913a 100644
---
a/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/catalog/psql/PostgresCreateTableSqlBuilderTest.java
+++
b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/catalog/psql/PostgresCreateTableSqlBuilderTest.java
@@ -35,6 +35,7 @@ import com.google.common.collect.Lists;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
+import java.util.regex.Pattern;
class PostgresCreateTableSqlBuilderTest {
@@ -49,17 +50,18 @@ class PostgresCreateTableSqlBuilderTest {
String createTableSql =
postgresCreateTableSqlBuilder.build(
catalogTable.getTableId().toTablePath());
- Assertions.assertEquals(
- "CREATE TABLE \"test\" (\n"
+ String pattern =
+ "CREATE TABLE \"test\" \\(\n"
+ "\"id\" int4 NOT NULL PRIMARY
KEY,\n"
+ "\"name\" text NOT NULL,\n"
+ "\"age\" int4 NOT NULL,\n"
- + "\tCONSTRAINT unique_name UNIQUE
(\"name\")\n"
- + ");",
- createTableSql);
+ + "\tCONSTRAINT \"([a-zA-Z0-9]+)\"
UNIQUE \\(\"name\"\\)\n"
+ + "\\);";
+ Assertions.assertTrue(
+
Pattern.compile(pattern).matcher(createTableSql).find());
+
Assertions.assertEquals(
- Lists.newArrayList(
- "CREATE INDEX test_index_age ON
\"test\"(\"age\");"),
+ Lists.newArrayList("CREATE INDEX ON
\"test\"(\"age\");"),
postgresCreateTableSqlBuilder.getCreateIndexSqls());
// skip index
