You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2022/07/13 02:14:00 UTC
[shardingsphere] branch master updated: Fix get create table sql with ";" (#19034)
This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new d6d203cb4c6 Fix get create table sql with ";" (#19034)
d6d203cb4c6 is described below
commit d6d203cb4c69696e78ffef3c652c1bca79fc531d
Author: Chuxin Chen <ch...@qq.com>
AuthorDate: Wed Jul 13 10:13:54 2022 +0800
Fix get create table sql with ";" (#19034)
* Fix get create table sql with ";"
* Fix parse column with national
* Fix parse column with national
---
.../spi/ddlgenerator/CreateTableSQLGenerator.java | 7 ++++---
.../fixture/CreateTableSQLGeneratorFixture.java | 6 ++++--
.../metadata/generator/PipelineDDLGenerator.java | 7 ++++---
.../ddlgenerator/MySQLCreateTableSQLGenerator.java | 6 ++++--
.../OpenGaussCreateTableSQLGenerator.java | 9 +++++++--
.../PostgreSQLCreateTableSQLGenerator.java | 8 ++++++--
.../cases/entity/DDLGeneratorOutputEntity.java | 5 +++--
.../cases/postgresql/PostgreSQLDDLGeneratorIT.java | 22 +++++++++++++++-------
.../ddlgenerator/postgresql/ddlgenerator.xml | 12 ++++++------
9 files changed, 53 insertions(+), 29 deletions(-)
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-api/src/main/java/org/apache/shardingsphere/data/pipeline/spi/ddlgenerator/CreateTableSQLGenerator.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-api/src/main/java/org/apache/shardingsphere/data/pipeline/spi/ddlgenerator/CreateTableSQLGenerator.java
index db622b19789..e123b69edb1 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-api/src/main/java/org/apache/shardingsphere/data/pipeline/spi/ddlgenerator/CreateTableSQLGenerator.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-api/src/main/java/org/apache/shardingsphere/data/pipeline/spi/ddlgenerator/CreateTableSQLGenerator.java
@@ -22,6 +22,7 @@ import org.apache.shardingsphere.spi.type.typed.TypedSPI;
import javax.sql.DataSource;
import java.sql.SQLException;
+import java.util.Collection;
/**
* Create table SQL generator.
@@ -30,13 +31,13 @@ import java.sql.SQLException;
public interface CreateTableSQLGenerator extends TypedSPI {
/**
- * Generate create table SQL.
+ * Generate create table SQLs.
*
* @param tableName table name
* @param schemaName schema name
* @param dataSource dataSource
- * @return generated SQL
+ * @return generated SQLs
* @throws SQLException SQL exception
*/
- String generate(String tableName, String schemaName, DataSource dataSource) throws SQLException;
+ Collection<String> generate(String tableName, String schemaName, DataSource dataSource) throws SQLException;
}
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-api/src/test/java/org/apache/shardingsphere/data/pipeline/spi/fixture/CreateTableSQLGeneratorFixture.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-api/src/test/java/org/apache/shardingsphere/data/pipeline/spi/fixture/CreateTableSQLGeneratorFixture.java
index 6e5373831e1..d3ae097c776 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-api/src/test/java/org/apache/shardingsphere/data/pipeline/spi/fixture/CreateTableSQLGeneratorFixture.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-api/src/test/java/org/apache/shardingsphere/data/pipeline/spi/fixture/CreateTableSQLGeneratorFixture.java
@@ -20,12 +20,14 @@ package org.apache.shardingsphere.data.pipeline.spi.fixture;
import org.apache.shardingsphere.data.pipeline.spi.ddlgenerator.CreateTableSQLGenerator;
import javax.sql.DataSource;
+import java.util.Collection;
+import java.util.Collections;
public final class CreateTableSQLGeneratorFixture implements CreateTableSQLGenerator {
@Override
- public String generate(final String tableName, final String schemaName, final DataSource dataSource) {
- return "";
+ public Collection<String> generate(final String tableName, final String schemaName, final DataSource dataSource) {
+ return Collections.singletonList("");
}
@Override
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/metadata/generator/PipelineDDLGenerator.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/metadata/generator/PipelineDDLGenerator.java
index db063da7194..df41a01e1a7 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/metadata/generator/PipelineDDLGenerator.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/metadata/generator/PipelineDDLGenerator.java
@@ -48,6 +48,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.Tab
import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
import java.sql.SQLException;
+import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Map;
@@ -83,9 +84,9 @@ public final class PipelineDDLGenerator {
ShardingSphereDatabase database = contextManager.getMetaDataContexts().getMetaData().getDatabases().get(databaseName);
log.info("generateLogicDDLSQL, databaseType={}, databaseName={}, schemaName={}, tableName={}, dataSourceNames={}",
databaseType.getType(), databaseName, schemaName, tableName, database.getResource().getDataSources().keySet());
- String sql = generateActualDDLSQL(databaseType, schemaName, tableName, database);
+ Collection<String> multiSQL = generateActualDDLSQL(databaseType, schemaName, tableName, database);
StringBuilder result = new StringBuilder();
- for (String each : sql.split(DELIMITER)) {
+ for (String each : multiSQL) {
Optional<String> logicSQL = decorate(databaseType, databaseName, schemaName, database, each);
logicSQL.ifPresent(ddlSQL -> result.append(ddlSQL).append(DELIMITER).append(System.lineSeparator()));
}
@@ -128,7 +129,7 @@ public final class PipelineDDLGenerator {
return Optional.of(result);
}
- private String generateActualDDLSQL(final DatabaseType databaseType, final String schemaName, final String tableName, final ShardingSphereDatabase database) throws SQLException {
+ private Collection<String> generateActualDDLSQL(final DatabaseType databaseType, final String schemaName, final String tableName, final ShardingSphereDatabase database) throws SQLException {
DataNodes dataNodes = new DataNodes(database.getRuleMetaData().getRules());
Optional<DataNode> filteredDataNode = dataNodes.getDataNodes(tableName).stream()
.filter(each -> database.getResource().getDataSources().containsKey(each.getDataSourceName().contains(".") ? each.getDataSourceName().split("\\.")[0] : each.getDataSourceName()))
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-mysql/src/main/java/org/apache/shardingsphere/data/pipeline/mysql/ddlgenerator/MySQLCreateTableSQLGenerator.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-mysql/src/main/java/org/apache/shardingsphere/data/pipeline/mysql/ddlgenerator/MySQLCreateTableSQLGenerator.java
index 1e4c721f0b0..1315d590d1f 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-mysql/src/main/java/org/apache/shardingsphere/data/pipeline/mysql/ddlgenerator/MySQLCreateTableSQLGenerator.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-mysql/src/main/java/org/apache/shardingsphere/data/pipeline/mysql/ddlgenerator/MySQLCreateTableSQLGenerator.java
@@ -24,6 +24,8 @@ import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
+import java.util.Collection;
+import java.util.Collections;
/**
* Create table SQL generator for MySQL.
@@ -35,12 +37,12 @@ public final class MySQLCreateTableSQLGenerator implements CreateTableSQLGenerat
private static final String COLUMN_LABEL = "create table";
@Override
- public String generate(final String tableName, final String schemaName, final DataSource dataSource) throws SQLException {
+ public Collection<String> generate(final String tableName, final String schemaName, final DataSource dataSource) throws SQLException {
try (
Statement statement = dataSource.getConnection().createStatement();
ResultSet resultSet = statement.executeQuery(String.format(SHOW_CREATE_SQL, tableName))) {
if (resultSet.next()) {
- return resultSet.getString(COLUMN_LABEL);
+ return Collections.singletonList(resultSet.getString(COLUMN_LABEL));
}
}
throw new ShardingSphereException("Failed to get ddl sql for table %s", tableName);
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-opengauss/src/main/java/org/apache/shardingsphere/data/pipeline/opengauss/ddlgenerator/OpenGaussCreateTableSQLGenerator.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-opengauss/src/main/java/org/apache/shardingsphere/data/pipeline/opengauss/ddlgenerator/OpenGaussCreateTableSQLGenerator.java
index 6a07a5c58aa..ace7935152c 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-opengauss/src/main/java/org/apache/shardingsphere/data/pipeline/opengauss/ddlgenerator/OpenGaussCreateTableSQLGenerator.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-opengauss/src/main/java/org/apache/shardingsphere/data/pipeline/opengauss/ddlgenerator/OpenGaussCreateTableSQLGenerator.java
@@ -24,6 +24,8 @@ import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
+import java.util.Arrays;
+import java.util.Collection;
/**
* Create table SQL generator for openGauss.
@@ -34,13 +36,16 @@ public final class OpenGaussCreateTableSQLGenerator implements CreateTableSQLGen
private static final String COLUMN_LABEL = "pg_get_tabledef";
+ private static final String DELIMITER = ";";
+
@Override
- public String generate(final String tableName, final String schemaName, final DataSource dataSource) throws SQLException {
+ public Collection<String> generate(final String tableName, final String schemaName, final DataSource dataSource) throws SQLException {
try (
Statement statement = dataSource.getConnection().createStatement();
ResultSet resultSet = statement.executeQuery(String.format(SELECT_TABLE_DEF_SQL, schemaName, tableName))) {
if (resultSet.next()) {
- return resultSet.getString(COLUMN_LABEL);
+ // TODO use ";" to split is not always correct
+ return Arrays.asList(resultSet.getString(COLUMN_LABEL).split(DELIMITER));
}
}
throw new ShardingSphereException("Failed to get ddl sql for table %s", tableName);
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgreSQLCreateTableSQLGenerator.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgreSQLCreateTableSQLGen [...]
index 462b88bfa3f..f663405412a 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgreSQLCreateTableSQLGenerator.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgreSQLCreateTableSQLGenerator.java
@@ -23,6 +23,7 @@ import org.apache.shardingsphere.data.pipeline.postgresql.util.FreemarkerManager
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
+import java.util.Arrays;
import java.util.Collection;
import java.util.Map;
@@ -31,16 +32,19 @@ import java.util.Map;
*/
public final class PostgreSQLCreateTableSQLGenerator implements CreateTableSQLGenerator {
+ private static final String DELIMITER = ";";
+
// TODO support partitions etc.
@Override
- public String generate(final String tableName, final String schemaName, final DataSource dataSource) throws SQLException {
+ public Collection<String> generate(final String tableName, final String schemaName, final DataSource dataSource) throws SQLException {
try (Connection connection = dataSource.getConnection()) {
int majorVersion = connection.getMetaData().getDatabaseMajorVersion();
int minorVersion = connection.getMetaData().getDatabaseMinorVersion();
Map<String, Object> materials = loadMaterials(tableName, schemaName, connection, majorVersion, minorVersion);
String tableSQL = generateCreateTableSQL(majorVersion, minorVersion, materials);
String indexSQL = generateCreateIndexSQL(connection, majorVersion, minorVersion, materials);
- return tableSQL + System.lineSeparator() + indexSQL;
+ // TODO use ";" to split is not always correct
+ return Arrays.asList((tableSQL + System.lineSeparator() + indexSQL).trim().split(DELIMITER));
}
}
diff --git a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-scaling/src/test/java/org/apache/shardingsphere/integration/data/pipeline/cases/entity/DDLGeneratorOutputEntity.java b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-scaling/src/test/java/org/apache/shardingsphere/integration/data/pipeline/cases/entity/DDLGeneratorOutputEntity.java
index 09be6d6ad86..73f85129fc5 100644
--- a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-scaling/src/test/java/org/apache/shardingsphere/integration/data/pipeline/cases/entity/DDLGeneratorOutputEntity.java
+++ b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-scaling/src/test/java/org/apache/shardingsphere/integration/data/pipeline/cases/entity/DDLGeneratorOutputEntity.java
@@ -24,6 +24,7 @@ import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
import javax.xml.bind.annotation.XmlAttribute;
import javax.xml.bind.annotation.XmlElement;
+import java.util.Collection;
/**
* DDL generator output entity for JAXB.
@@ -36,6 +37,6 @@ public final class DDLGeneratorOutputEntity {
@XmlAttribute
private String version;
- @XmlElement
- private String sql;
+ @XmlElement(required = true, name = "sql")
+ private Collection<String> multiSQL;
}
diff --git a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-scaling/src/test/java/org/apache/shardingsphere/integration/data/pipeline/cases/postgresql/PostgreSQLDDLGeneratorIT.java b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-scaling/src/test/java/org/apache/shardingsphere/integration/data/pipeline/cases/postgresql/PostgreSQLDDLGeneratorIT.java
index 4718a8d3179..53b03c7bf14 100644
--- a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-scaling/src/test/java/org/apache/shardingsphere/integration/data/pipeline/cases/postgresql/PostgreSQLDDLGeneratorIT.java
+++ b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-scaling/src/test/java/org/apache/shardingsphere/integration/data/pipeline/cases/postgresql/PostgreSQLDDLGeneratorIT.java
@@ -43,6 +43,7 @@ import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
+import java.util.Iterator;
import java.util.LinkedList;
import java.util.Objects;
import java.util.regex.Pattern;
@@ -100,21 +101,21 @@ public final class PostgreSQLDDLGeneratorIT {
int majorVersion = connection.getMetaData().getDatabaseMajorVersion();
for (DDLGeneratorAssertionEntity each : rootEntity.getAssertions()) {
statement.execute(each.getInput().getSql());
- String sql = CreateTableSQLGeneratorFactory.findInstance(parameterized.getDatabaseType()).orElseThrow(() -> new ShardingSphereException("Failed to get dialect ddl sql generator"))
- .generate(each.getInput().getTable(), DEFAULT_SCHEMA, dataSource);
- assertThat(REPLACE_LINE_SPACE.matcher(sql).replaceAll(""), is(REPLACE_LINE_SPACE.matcher(getVersionOutput(each.getOutputs(), majorVersion)).replaceAll("")));
+ Collection<String> multiSQL = CreateTableSQLGeneratorFactory.findInstance(parameterized.getDatabaseType())
+ .orElseThrow(() -> new ShardingSphereException("Failed to get dialect ddl sql generator")).generate(each.getInput().getTable(), DEFAULT_SCHEMA, dataSource);
+ assertIsCorrect(multiSQL, getVersionOutput(each.getOutputs(), majorVersion));
}
}
}
- private String getVersionOutput(final Collection<DDLGeneratorOutputEntity> outputs, final int majorVersion) {
- String result = "";
+ private Collection<String> getVersionOutput(final Collection<DDLGeneratorOutputEntity> outputs, final int majorVersion) {
+ Collection<String> result = new LinkedList<>();
for (DDLGeneratorOutputEntity each : outputs) {
if ("default".equals(each.getVersion())) {
- result = each.getSql();
+ result = each.getMultiSQL();
}
if (String.valueOf(majorVersion).equals(each.getVersion())) {
- return each.getSql();
+ return each.getMultiSQL();
}
}
return result;
@@ -131,6 +132,13 @@ public final class PostgreSQLDDLGeneratorIT {
return result;
}
+ private void assertIsCorrect(final Collection<String> actualSQL, final Collection<String> expectedSQL) {
+ Iterator<String> expected = expectedSQL.iterator();
+ for (String each : actualSQL) {
+ assertThat(REPLACE_LINE_SPACE.matcher(each).replaceAll(""), is(REPLACE_LINE_SPACE.matcher(expected.next()).replaceAll("")));
+ }
+ }
+
@After
public void stopContainer() {
databaseContainer.stop();
diff --git a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-scaling/src/test/resources/env/scenario/ddlgenerator/postgresql/ddlgenerator.xml b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-scaling/src/test/resources/env/scenario/ddlgenerator/postgresql/ddlgenerator.xml
index a70bb9f8ca6..4bf1eda5603 100644
--- a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-scaling/src/test/resources/env/scenario/ddlgenerator/postgresql/ddlgenerator.xml
+++ b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-scaling/src/test/resources/env/scenario/ddlgenerator/postgresql/ddlgenerator.xml
@@ -32,7 +32,7 @@
CONSTRAINT t_order_pkey PRIMARY KEY (
order_id)
)
- TABLESPACE pg_default;
+ TABLESPACE pg_default
</sql>
</output>
<output version="10">
@@ -44,7 +44,7 @@
status character varying(45) COLLATE pg_catalog."default",
CONSTRAINT t_order_pkey PRIMARY KEY (order_id))
WITH (OIDS = FALSE)
- TABLESPACE pg_default;
+ TABLESPACE pg_default
</sql>
</output>
<output version="11">
@@ -56,7 +56,7 @@
status character varying(45) COLLATE pg_catalog."default",
CONSTRAINT t_order_pkey PRIMARY KEY (order_id))
WITH (OIDS = FALSE)
- TABLESPACE pg_default;
+ TABLESPACE pg_default
</sql>
</output>
</ddl-generator-assertion>
@@ -79,7 +79,7 @@
CONSTRAINT t_order_item_pkey PRIMARY KEY (
item_id)
)
- TABLESPACE pg_default;
+ TABLESPACE pg_default
</sql>
</output>
<output version="10">
@@ -94,7 +94,7 @@
CONSTRAINT t_order_item_pkey PRIMARY KEY (
item_id))
WITH (OIDS = FALSE)
- TABLESPACE pg_default;
+ TABLESPACE pg_default
</sql>
</output>
<output version="11">
@@ -109,7 +109,7 @@
CONSTRAINT t_order_item_pkey PRIMARY KEY (
item_id))
WITH (OIDS = FALSE)
- TABLESPACE pg_default;
+ TABLESPACE pg_default
</sql>
</output>
</ddl-generator-assertion>