You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2022/05/02 07:43:56 UTC
[shardingsphere] branch master updated: Support getting create table SQL in multi schema for PostgreSQL and openGauss (#17264)
This is an automated email from the ASF dual-hosted git repository.
panjuan 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 fa689c316b9 Support getting create table SQL in multi schema for PostgreSQL and openGauss (#17264)
fa689c316b9 is described below
commit fa689c316b9f8c4a38a697a5646f8131db641617
Author: Hongsheng Zhong <sa...@126.com>
AuthorDate: Mon May 2 15:43:40 2022 +0800
Support getting create table SQL in multi schema for PostgreSQL and openGauss (#17264)
* Add TableNameSchemaNameMapping
* Refactor PrepareTargetTablesParameter
* PostgreSQLDataSourcePreparer support multi schema
* OpenGaussDataSourcePreparer support multi schema
---
.../core/api/impl/AbstractPipelineJobAPIImpl.java | 1 +
.../core/metadata/TableNameSchemaNameMapping.java | 62 ++++++++++++++++++++++
.../datasource/PrepareTargetTablesParameter.java | 20 ++++---
.../rulealtered/RuleAlteredJobContext.java | 21 ++++++++
.../rulealtered/RuleAlteredJobPreparer.java | 9 ++--
.../datasource/OpenGaussDataSourcePreparer.java | 25 +++------
.../datasource/PostgreSQLDataSourcePreparer.java | 42 +++++++++------
7 files changed, 133 insertions(+), 47 deletions(-)
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/api/impl/AbstractPipelineJobAPIImpl.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/api/impl/AbstractPipelineJobAPIImpl.java
index 549c3b7d259..406037d25a3 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/api/impl/AbstractPipelineJobAPIImpl.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/api/impl/AbstractPipelineJobAPIImpl.java
@@ -53,6 +53,7 @@ public abstract class AbstractPipelineJobAPIImpl implements PipelineJobAPI {
JobConfigurationPOJO jobConfigPOJO = getElasticJobConfigPOJO(jobId);
jobConfigPOJO.setDisabled(true);
jobConfigPOJO.getProps().setProperty("stop_time", LocalDateTime.now().format(DATE_TIME_FORMATTER));
+ // TODO updateJobConfiguration might doesn't work
PipelineAPIFactory.getJobConfigurationAPI().updateJobConfiguration(jobConfigPOJO);
}
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/metadata/TableNameSchemaNameMapping.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/metadata/TableNameSchemaNameMapping.java
new file mode 100644
index 00000000000..3ae8cab7c05
--- /dev/null
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/metadata/TableNameSchemaNameMapping.java
@@ -0,0 +1,62 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.data.pipeline.core.metadata;
+
+import org.apache.shardingsphere.data.pipeline.api.metadata.LogicTableName;
+import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
+
+import java.util.HashMap;
+import java.util.Map;
+
+/**
+ * Table name and schema name mapping.
+ */
+public final class TableNameSchemaNameMapping {
+
+ private final Map<LogicTableName, String> mapping;
+
+ public TableNameSchemaNameMapping(final ShardingSphereMetaData metaData) {
+ Map<LogicTableName, String> mapping = new HashMap<>();
+ metaData.getSchemas().forEach((schemaName, schema) -> {
+ for (String each : schema.getAllTableNames()) {
+ mapping.put(new LogicTableName(each), schemaName);
+ }
+ });
+ this.mapping = mapping;
+ }
+
+ /**
+ * Get schema name.
+ *
+ * @param logicTableName logic table name
+ * @return schema name
+ */
+ public String getSchemaName(final String logicTableName) {
+ return mapping.get(new LogicTableName(logicTableName));
+ }
+
+ /**
+ * Get schema name.
+ *
+ * @param logicTableName logic table name
+ * @return schema name
+ */
+ public String getSchemaName(final LogicTableName logicTableName) {
+ return mapping.get(logicTableName);
+ }
+}
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/prepare/datasource/PrepareTargetTablesParameter.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/prepare/datasource/PrepareTargetTablesParameter.java
index 41233157c1a..57d019a0590 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/prepare/datasource/PrepareTargetTablesParameter.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/core/prepare/datasource/PrepareTargetTablesParameter.java
@@ -19,24 +19,30 @@ package org.apache.shardingsphere.data.pipeline.core.prepare.datasource;
import lombok.Getter;
import lombok.NonNull;
-import lombok.RequiredArgsConstructor;
import org.apache.shardingsphere.data.pipeline.api.config.rulealtered.RuleAlteredJobConfiguration;
import org.apache.shardingsphere.data.pipeline.api.datanode.JobDataNodeLine;
import org.apache.shardingsphere.data.pipeline.core.datasource.PipelineDataSourceManager;
+import org.apache.shardingsphere.data.pipeline.core.metadata.TableNameSchemaNameMapping;
/**
* Prepare target tables parameter.
*/
-@RequiredArgsConstructor
@Getter
public final class PrepareTargetTablesParameter {
- @NonNull
- private final JobDataNodeLine tablesFirstDataNodes;
-
- @NonNull
private final RuleAlteredJobConfiguration jobConfig;
- @NonNull
+ private final JobDataNodeLine tablesFirstDataNodes;
+
private final PipelineDataSourceManager dataSourceManager;
+
+ private final TableNameSchemaNameMapping tableNameSchemaNameMapping;
+
+ public PrepareTargetTablesParameter(@NonNull final RuleAlteredJobConfiguration jobConfig, @NonNull final PipelineDataSourceManager dataSourceManager,
+ final TableNameSchemaNameMapping tableNameSchemaNameMapping) {
+ this.jobConfig = jobConfig;
+ tablesFirstDataNodes = JobDataNodeLine.unmarshal(jobConfig.getTablesFirstDataNodes());
+ this.dataSourceManager = dataSourceManager;
+ this.tableNameSchemaNameMapping = tableNameSchemaNameMapping;
+ }
}
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobContext.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobContext.java
index aa916d6a04a..2f1e7faf1c4 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobContext.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobContext.java
@@ -28,10 +28,13 @@ import org.apache.shardingsphere.data.pipeline.api.config.rulealtered.TaskConfig
import org.apache.shardingsphere.data.pipeline.api.datasource.PipelineDataSourceWrapper;
import org.apache.shardingsphere.data.pipeline.api.job.JobStatus;
import org.apache.shardingsphere.data.pipeline.api.job.progress.JobProgress;
+import org.apache.shardingsphere.data.pipeline.core.context.PipelineContext;
import org.apache.shardingsphere.data.pipeline.core.datasource.PipelineDataSourceManager;
+import org.apache.shardingsphere.data.pipeline.core.metadata.TableNameSchemaNameMapping;
import org.apache.shardingsphere.data.pipeline.core.metadata.loader.PipelineTableMetaDataLoader;
import org.apache.shardingsphere.data.pipeline.core.task.IncrementalTask;
import org.apache.shardingsphere.data.pipeline.core.task.InventoryTask;
+import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
import java.util.Collection;
import java.util.LinkedList;
@@ -72,6 +75,14 @@ public final class RuleAlteredJobContext {
}
};
+ private final LazyInitializer<TableNameSchemaNameMapping> schemaNameMappingLazyInitializer = new LazyInitializer<TableNameSchemaNameMapping>() {
+ @Override
+ protected TableNameSchemaNameMapping initialize() {
+ ShardingSphereMetaData metaData = PipelineContext.getContextManager().getMetaDataContexts().getMetaData(jobConfig.getDatabaseName());
+ return new TableNameSchemaNameMapping(metaData);
+ }
+ };
+
private final LazyInitializer<PipelineTableMetaDataLoader> sourceMetaDataLoaderLazyInitializer = new LazyInitializer<PipelineTableMetaDataLoader>() {
@Override
protected PipelineTableMetaDataLoader initialize() throws ConcurrentException {
@@ -110,6 +121,16 @@ public final class RuleAlteredJobContext {
return sourceMetaDataLoaderLazyInitializer.get();
}
+ /**
+ * Get table name and schema name mapping.
+ *
+ * @return mapping
+ */
+ @SneakyThrows(ConcurrentException.class)
+ public TableNameSchemaNameMapping getTableNameSchemaNameMapping() {
+ return schemaNameMappingLazyInitializer.get();
+ }
+
/**
* Release resources.
*/
diff --git a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobPreparer.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobPreparer.java
index 8b54ec70bc2..b1832860489 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobPreparer.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-core/src/main/java/org/apache/shardingsphere/data/pipeline/scenario/rulealtered/RuleAlteredJobPreparer.java
@@ -20,7 +20,6 @@ package org.apache.shardingsphere.data.pipeline.scenario.rulealtered;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.data.pipeline.api.config.rulealtered.RuleAlteredJobConfiguration;
import org.apache.shardingsphere.data.pipeline.api.config.rulealtered.TaskConfiguration;
-import org.apache.shardingsphere.data.pipeline.api.datanode.JobDataNodeLine;
import org.apache.shardingsphere.data.pipeline.api.datasource.PipelineDataSourceWrapper;
import org.apache.shardingsphere.data.pipeline.api.datasource.config.PipelineDataSourceConfigurationFactory;
import org.apache.shardingsphere.data.pipeline.api.datasource.config.impl.ShardingSpherePipelineDataSourceConfiguration;
@@ -115,7 +114,7 @@ public final class RuleAlteredJobPreparer {
}
private void prepareAndCheckTarget(final RuleAlteredJobContext jobContext) {
- prepareTarget(jobContext.getJobConfig(), jobContext.getDataSourceManager());
+ prepareTarget(jobContext);
JobProgress initProgress = jobContext.getInitProgress();
if (null == initProgress || initProgress.getStatus() == JobStatus.PREPARING_FAILURE) {
PipelineDataSourceWrapper targetDataSource = jobContext.getDataSourceManager().getDataSource(jobContext.getTaskConfig().getImporterConfig().getDataSourceConfig());
@@ -123,14 +122,14 @@ public final class RuleAlteredJobPreparer {
}
}
- private void prepareTarget(final RuleAlteredJobConfiguration jobConfig, final PipelineDataSourceManager dataSourceManager) {
+ private void prepareTarget(final RuleAlteredJobContext jobContext) {
+ RuleAlteredJobConfiguration jobConfig = jobContext.getJobConfig();
Optional<DataSourcePreparer> dataSourcePreparer = EnvironmentCheckerFactory.getDataSourcePreparer(jobConfig.getTargetDatabaseType());
if (!dataSourcePreparer.isPresent()) {
log.info("dataSourcePreparer null, ignore prepare target");
return;
}
- JobDataNodeLine tablesFirstDataNodes = JobDataNodeLine.unmarshal(jobConfig.getTablesFirstDataNodes());
- PrepareTargetTablesParameter prepareTargetTablesParameter = new PrepareTargetTablesParameter(tablesFirstDataNodes, jobConfig, dataSourceManager);
+ PrepareTargetTablesParameter prepareTargetTablesParameter = new PrepareTargetTablesParameter(jobConfig, jobContext.getDataSourceManager(), jobContext.getTableNameSchemaNameMapping());
dataSourcePreparer.get().prepareTargetTables(prepareTargetTablesParameter);
}
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/prepare/datasource/OpenGaussDataSourcePreparer.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-opengauss/src/main/java/org/apache/shardingsphere/data/pipeline/opengauss/prepare/datasource/OpenGaussDataSourcePreparer.java
index 19552851150..b0f90beaca7 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-opengauss/src/main/java/org/apache/shardingsphere/data/pipeline/opengauss/prepare/datasource/OpenGaussDataSourcePreparer.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-opengauss/src/main/java/org/apache/shardingsphere/data/pipeline/opengauss/prepare/datasource/OpenGaussDataSourcePreparer.java
@@ -31,7 +31,6 @@ import org.apache.shardingsphere.data.pipeline.core.prepare.datasource.PrepareTa
import org.apache.shardingsphere.infra.datanode.DataNode;
import java.sql.Connection;
-import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
@@ -84,10 +83,10 @@ public final class OpenGaussDataSourcePreparer extends AbstractDataSourcePrepare
// Keep dataSource to reuse
PipelineDataSourceWrapper dataSource = dataSourceManager.getDataSource(sourceDataSourceConfig.getActualDataSourceConfig(dataNode.getDataSourceName()));
try (Connection sourceConnection = dataSource.getConnection()) {
+ String schemaName = parameter.getTableNameSchemaNameMapping().getSchemaName(each.getLogicTableName());
String actualTableName = dataNode.getTableName();
- int oid = queryTableOid(sourceConnection, actualTableName);
- String tableDefinition = queryTableDefinition(sourceConnection, oid);
- log.info("getActualTableDefinitions, dataNode={}, tableDefinition={}", dataNode, tableDefinition);
+ String tableDefinition = queryTableDefinition(sourceConnection, schemaName, actualTableName);
+ log.info("getActualTableDefinitions, schemaName={}, dataNode={}, tableDefinition={}", schemaName, dataNode, tableDefinition);
String logicTableName = each.getLogicTableName();
result.add(new ActualTableDefinition(logicTableName, actualTableName, tableDefinition));
}
@@ -96,21 +95,9 @@ public final class OpenGaussDataSourcePreparer extends AbstractDataSourcePrepare
return result;
}
- private int queryTableOid(final Connection sourceConnection, final String actualTableName) throws SQLException {
- String sql = "SELECT oid FROM pg_class WHERE relname = ?";
- try (PreparedStatement statement = sourceConnection.prepareStatement(sql)) {
- statement.setString(1, actualTableName);
- try (ResultSet resultSet = statement.executeQuery()) {
- if (!resultSet.next()) {
- throw new PipelineJobPrepareFailedException("select oid has no result, sql: " + sql + ", actualTableName: " + actualTableName);
- }
- return resultSet.getInt(1);
- }
- }
- }
-
- private String queryTableDefinition(final Connection sourceConnection, final int oid) throws SQLException {
- String sql = String.format("SELECT * FROM pg_get_tabledef(%d)", oid);
+ private String queryTableDefinition(final Connection sourceConnection, final String schemaName, final String actualTableName) throws SQLException {
+ String sql = String.format("SELECT * FROM pg_get_tabledef('%s.%s'::regclass::oid)", schemaName, actualTableName);
+ log.info("queryTableDefinition, sql={}", sql);
try (Statement statement = sourceConnection.createStatement(); ResultSet resultSet = statement.executeQuery(sql)) {
if (!resultSet.next()) {
throw new PipelineJobPrepareFailedException("table definition has no result, sql: " + sql);
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/prepare/datasource/PostgreSQLDataSourcePreparer.java b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/prepare/datasource/PostgreSQLDataSource [...]
index 638cb4e3c00..2d316002fe6 100644
--- a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/prepare/datasource/PostgreSQLDataSourcePreparer.java
+++ b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/prepare/datasource/PostgreSQLDataSourcePreparer.java
@@ -55,20 +55,20 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
private static final String FETCH_CREATE_TABLE_TEMPLATE = "SELECT 'CREATE TABLE IF NOT EXISTS '||'%s'||'('||STRING_AGG"
+ "(column_list.column_expr, ',')||',PRIMARY KEY({PK_placeholder}))' FROM (SELECT *,column_name||' '||data_type||COALESCE('('"
+ " || character_maximum_length || ')', '')||CASE WHEN is_nullable = 'YES' then '' ELSE ' NOT NULL' END AS column_expr "
- + "FROM information_schema.columns WHERE table_name = '%s' AND table_schema='public' ORDER BY ordinal_position) column_list";
+ + "FROM information_schema.columns WHERE table_name = '%s' AND table_schema='%s' ORDER BY ordinal_position) column_list";
private static final String FETCH_NORMAL_INDEXES_TEMPLATE = "SELECT schemaname,indexname,indexdef FROM pg_indexes WHERE tablename = "
- + "'%s' AND indexname != '%s' AND schemaname='public'";
+ + "'%s' AND indexname != '%s' AND schemaname='%s'";
private static final String DROP_INDEX_TEMPLATE = "DROP INDEX IF EXISTS %s.%s";
private static final String FETCH_PRIMARY_KEY_TEMPLATE = "SELECT constraint_name,column_name FROM information_schema"
- + ".key_column_usage WHERE table_name = '%s' AND table_schema='public'";
+ + ".key_column_usage WHERE table_name = '%s' AND table_schema='%s'";
private static final String COMMENT_TEMPLATE = "COMMENT ON %s %s IS '%s'";
private static final String FETCH_TABLE_COLUMN_TEMPLATE = "SELECT ordinal_position,column_name FROM information_schema.columns WHERE "
- + "table_name = '%s' AND table_schema='public' ORDER BY ordinal_position";
+ + "table_name = '%s' AND table_schema='%s' ORDER BY ordinal_position";
private static final String FETCH_COMMENT_TEMPLATE = "SELECT objsubid,description FROM pg_catalog.pg_description WHERE objoid = "
+ "'%s'::regclass";
@@ -130,16 +130,19 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
// TODO to remove if config is fix the problem
final Map<String, Object> configMap =
sourceDataSourceConfig.getRootConfig().getDataSources().get(dataNode.getDataSourceName());
+ // TODO one of jdbcUrl or url might not exist, refactor to getActualDataSourceConfig
configMap.put("jdbcUrl", configMap.get("url"));
PipelineDataSourceWrapper dataSource =
dataSourceManager.getDataSource(sourceDataSourceConfig.getActualDataSourceConfig(dataNode.getDataSourceName()));
try (Connection sourceConnection = dataSource.getConnection()) {
String actualTableName = dataNode.getTableName();
+ String schemaName = parameter.getTableNameSchemaNameMapping().getSchemaName(each.getLogicTableName());
StringJoiner joiner = new StringJoiner(";");
- Pair<String, List<String>> primaryKeyPair = queryTablePrimaryKey(sourceConnection, actualTableName);
- joiner.add(queryCreateTableSql(sourceConnection, actualTableName, primaryKeyPair.getRight()));
- queryCreateIndexes(sourceConnection, actualTableName, primaryKeyPair.getLeft()).forEach(joiner::add);
- queryCommentOnList(sourceConnection, actualTableName).forEach(joiner::add);
+ Pair<String, List<String>> primaryKeyPair = queryTablePrimaryKey(sourceConnection, schemaName, actualTableName);
+ joiner.add(queryCreateTableSql(sourceConnection, schemaName, actualTableName, primaryKeyPair.getRight()));
+ queryCreateIndexes(sourceConnection, schemaName, actualTableName, primaryKeyPair.getLeft()).forEach(joiner::add);
+ // TODO support query comment for multi schema
+ //queryCommentOnList(sourceConnection, schemaName, actualTableName).forEach(joiner::add);
String tableDefinition = joiner.toString();
result.add(new ActualTableDefinition(each.getLogicTableName(), actualTableName, tableDefinition));
}
@@ -148,12 +151,14 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
return result;
}
- private Pair<String, List<String>> queryTablePrimaryKey(final Connection sourceConnection, final String actualTableName) throws SQLException {
+ private Pair<String, List<String>> queryTablePrimaryKey(final Connection sourceConnection, final String schemaName, final String actualTableName) throws SQLException {
+ String sql = String.format(FETCH_PRIMARY_KEY_TEMPLATE, actualTableName, schemaName);
+ log.info("queryTablePrimaryKey, sql={}", sql);
String primaryKeyName = null;
List<String> primaryKeyColumns = new LinkedList<>();
try (
Statement statement = sourceConnection.createStatement();
- ResultSet resultSet = statement.executeQuery(String.format(FETCH_PRIMARY_KEY_TEMPLATE, actualTableName))) {
+ ResultSet resultSet = statement.executeQuery(sql)) {
while (resultSet.next()) {
primaryKeyName = primaryKeyName == null ? resultSet.getString(1) : primaryKeyName;
primaryKeyColumns.add(resultSet.getString(2));
@@ -165,8 +170,9 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
return Pair.of(primaryKeyName, primaryKeyColumns);
}
- private String queryCreateTableSql(final Connection sourceConnection, final String actualTableName, final List<String> pkColumns) throws SQLException {
- final String sql = String.format(FETCH_CREATE_TABLE_TEMPLATE, actualTableName, actualTableName);
+ private String queryCreateTableSql(final Connection sourceConnection, final String schemaName, final String actualTableName, final List<String> pkColumns) throws SQLException {
+ final String sql = String.format(FETCH_CREATE_TABLE_TEMPLATE, actualTableName, actualTableName, schemaName);
+ log.info("queryCreateTableSql, sql={}", sql);
try (Statement statement = sourceConnection.createStatement(); ResultSet resultSet = statement.executeQuery(sql)) {
if (!resultSet.next()) {
throw new PipelineJobPrepareFailedException("table struct has no result, sql: " + sql);
@@ -175,11 +181,13 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
}
}
- private List<String> queryCreateIndexes(final Connection sourceConnection, final String actualTableName, final String pkName) throws SQLException {
+ private List<String> queryCreateIndexes(final Connection sourceConnection, final String schemaName, final String actualTableName, final String pkName) throws SQLException {
List<String> result = new LinkedList<>();
+ String sql = String.format(FETCH_NORMAL_INDEXES_TEMPLATE, actualTableName, pkName, schemaName);
+ log.info("queryCreateIndexes, sql={}", sql);
try (
Statement statement = sourceConnection.createStatement();
- ResultSet resultSet = statement.executeQuery(String.format(FETCH_NORMAL_INDEXES_TEMPLATE, actualTableName, pkName))) {
+ ResultSet resultSet = statement.executeQuery(sql)) {
while (resultSet.next()) {
// TODO add drop index first, make sure the index is not exist
result.add(String.format(DROP_INDEX_TEMPLATE, resultSet.getString(1), resultSet.getString(2)));
@@ -189,8 +197,9 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
return result;
}
- private List<String> queryCommentOnList(final Connection sourceConnection, final String actualTableName) throws SQLException {
+ private List<String> queryCommentOnList(final Connection sourceConnection, final String schemaName, final String actualTableName) throws SQLException {
final String fetchCommentSql = String.format(FETCH_COMMENT_TEMPLATE, actualTableName);
+ log.info("queryCommentOnList, fetchCommentSql={}", fetchCommentSql);
List<String> result = new LinkedList<>();
Map<Integer, String> commentMap = Maps.newHashMap();
try (
@@ -204,7 +213,8 @@ public final class PostgreSQLDataSourcePreparer extends AbstractDataSourcePrepar
result.add(String.format(COMMENT_TEMPLATE, "TABLE", actualTableName, tableComment));
}
}
- final String fetchColumnSql = String.format(FETCH_TABLE_COLUMN_TEMPLATE, actualTableName);
+ final String fetchColumnSql = String.format(FETCH_TABLE_COLUMN_TEMPLATE, actualTableName, schemaName);
+ log.info("queryCommentOnList, fetchColumnSql={}", fetchColumnSql);
try (
Statement statement = sourceConnection.createStatement();
ResultSet columnsResult = statement.executeQuery(fetchColumnSql)) {