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/11/21 06:10:10 UTC
[shardingsphere] branch master updated: Support pg \d and optimize table collect code. (#22305)
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 bd4a918338b Support pg \d and optimize table collect code. (#22305)
bd4a918338b is described below
commit bd4a918338be182879d6d4709bf6c60c46be370b
Author: Chuxin Chen <ch...@qq.com>
AuthorDate: Mon Nov 21 14:09:54 2022 +0800
Support pg \d and optimize table collect code. (#22305)
---
.../ShardingSphereTableDataCollectorUtil.java | 113 +++++++++++++++++++++
.../collector/tables/PgClassTableCollector.java | 68 ++-----------
.../tables/PgNamespaceTableCollector.java | 49 ++-------
.../schema/opengauss/pg_catalog/pg_class.yaml | 8 ++
.../schema/opengauss/pg_catalog/pg_namespace.yaml | 8 ++
5 files changed, 147 insertions(+), 99 deletions(-)
diff --git a/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/data/collector/ShardingSphereTableDataCollectorUtil.java b/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/data/collector/ShardingSphereTableDataCollectorUtil.java
new file mode 100644
index 00000000000..36cf882dc64
--- /dev/null
+++ b/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/data/collector/ShardingSphereTableDataCollectorUtil.java
@@ -0,0 +1,113 @@
+/*
+ * 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.infra.metadata.data.collector;
+
+import org.apache.shardingsphere.infra.metadata.data.ShardingSphereRowData;
+import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereColumn;
+import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereTable;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Types;
+import java.util.Collection;
+import java.util.LinkedList;
+import java.util.List;
+
+/**
+ * Table data collector util.
+ */
+public final class ShardingSphereTableDataCollectorUtil {
+
+ /**
+ * Collect row data.
+ *
+ * @param dataSources data sources
+ * @param sql sql
+ * @param table table
+ * @param selectedColumnNames selected column names
+ * @return ShardingSphere row data
+ * @throws SQLException sql exception
+ */
+ public static Collection<ShardingSphereRowData> collectRowData(final Collection<DataSource> dataSources, final String sql, final ShardingSphereTable table,
+ final Collection<String> selectedColumnNames) throws SQLException {
+ Collection<ShardingSphereRowData> result = new LinkedList<>();
+ for (DataSource each : dataSources) {
+ try (
+ Connection connection = each.getConnection();
+ Statement statement = connection.createStatement();
+ ResultSet resultSet = statement.executeQuery(sql)) {
+ result.addAll(getRows(resultSet, table, selectedColumnNames));
+ }
+ }
+ return result;
+ }
+
+ private static Collection<ShardingSphereRowData> getRows(final ResultSet resultSet, final ShardingSphereTable table, final Collection<String> selectedColumnNames) throws SQLException {
+ Collection<ShardingSphereRowData> result = new LinkedList<>();
+ while (resultSet.next()) {
+ result.add(new ShardingSphereRowData(getRow(table, resultSet, selectedColumnNames)));
+ }
+ return result;
+ }
+
+ private static List<Object> getRow(final ShardingSphereTable table, final ResultSet resultSet, final Collection<String> selectedColumnNames) throws SQLException {
+ List<Object> result = new LinkedList<>();
+ for (ShardingSphereColumn each : table.getColumns().values()) {
+ if (selectedColumnNames.contains(each.getName())) {
+ result.add(convertIfNecessary(resultSet.getObject(each.getName()), each.getDataType()));
+ } else {
+ result.add(mockValue(each.getDataType()));
+ }
+ }
+ return result;
+ }
+
+ private static Object mockValue(final int dataType) {
+ switch (dataType) {
+ case Types.BIGINT:
+ return 0L;
+ case Types.VARCHAR:
+ case Types.CHAR:
+ case Types.OTHER:
+ case Types.ARRAY:
+ return "";
+ case Types.INTEGER:
+ case Types.SMALLINT:
+ return 0;
+ case Types.REAL:
+ return Float.valueOf("0");
+ case Types.BIT:
+ return false;
+ default:
+ return null;
+ }
+ }
+
+ private static Object convertIfNecessary(final Object data, final int dataType) {
+ if (Types.ARRAY == dataType) {
+ return null == data ? null : data.toString();
+ }
+ if (Types.BIGINT == dataType) {
+ return null == data ? null : Long.valueOf(data.toString());
+ }
+ return data;
+ }
+}
diff --git a/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/data/collector/tables/PgClassTableCollector.java b/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/data/collector/tables/PgClassTableCollector.java
index bb0f39053ef..63dedc39d79 100644
--- a/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/data/collector/tables/PgClassTableCollector.java
+++ b/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/data/collector/tables/PgClassTableCollector.java
@@ -20,18 +20,13 @@ package org.apache.shardingsphere.infra.metadata.data.collector.tables;
import org.apache.shardingsphere.infra.metadata.data.ShardingSphereRowData;
import org.apache.shardingsphere.infra.metadata.data.ShardingSphereTableData;
import org.apache.shardingsphere.infra.metadata.data.collector.ShardingSphereDataCollector;
+import org.apache.shardingsphere.infra.metadata.data.collector.ShardingSphereTableDataCollectorUtil;
import org.apache.shardingsphere.infra.metadata.database.ShardingSphereDatabase;
-import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereColumn;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereTable;
import org.apache.shardingsphere.infra.rule.ShardingSphereRule;
import org.apache.shardingsphere.infra.rule.identifier.type.DataNodeContainedRule;
-import javax.sql.DataSource;
-import java.sql.Connection;
-import java.sql.ResultSet;
import java.sql.SQLException;
-import java.sql.Statement;
-import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
@@ -48,7 +43,7 @@ public final class PgClassTableCollector implements ShardingSphereDataCollector
private static final String PG_CLASS = "pg_class";
- private static final String COLUMN_NAMES = "relname, relnamespace, relkind";
+ private static final String COLUMN_NAMES = "relname, relnamespace, relkind, reloptions";
private static final String SELECT_SQL = "SELECT " + COLUMN_NAMES + " FROM pg_catalog.pg_class WHERE relkind IN ('r','v','m','S','L','f','e','o','') "
+ "AND relname NOT LIKE 'matviewmap\\_%' AND relname NOT LIKE 'mlog\\_%' AND pg_catalog.pg_table_is_visible(oid);";
@@ -56,70 +51,21 @@ public final class PgClassTableCollector implements ShardingSphereDataCollector
@Override
public Optional<ShardingSphereTableData> collect(final String databaseName, final ShardingSphereTable table,
final Map<String, ShardingSphereDatabase> shardingSphereDatabases) throws SQLException {
- List<ShardingSphereRowData> rows = new LinkedList<>();
- for (DataSource each : shardingSphereDatabases.get(databaseName).getResourceMetaData().getDataSources().values()) {
- try (
- Connection connection = each.getConnection();
- Statement statement = connection.createStatement();
- ResultSet resultSet = statement.executeQuery(SELECT_SQL)) {
- rows.addAll(getRows(resultSet, table));
- }
- }
- List<ShardingSphereRowData> rowData = decorateTableName(rows, table, shardingSphereDatabases.get(databaseName).getRuleMetaData().getRules());
+ Collection<ShardingSphereRowData> rows = ShardingSphereTableDataCollectorUtil.collectRowData(shardingSphereDatabases.get(databaseName).getResourceMetaData().getDataSources().values(),
+ SELECT_SQL, table, Arrays.stream(COLUMN_NAMES.split(",")).map(String::trim).collect(Collectors.toList()));
+ Collection<ShardingSphereRowData> rowData = decorateTableName(rows, table, shardingSphereDatabases.get(databaseName).getRuleMetaData().getRules());
ShardingSphereTableData result = new ShardingSphereTableData(PG_CLASS, new ArrayList<>(table.getColumns().values()));
result.getRows().addAll(rowData.stream().distinct().collect(Collectors.toList()));
return Optional.of(result);
}
- private List<ShardingSphereRowData> getRows(final ResultSet resultSet, final ShardingSphereTable table) throws SQLException {
- List<ShardingSphereRowData> result = new LinkedList<>();
- List<String> selectedColumnNames = Arrays.stream(COLUMN_NAMES.split(",")).map(String::trim).collect(Collectors.toList());
- while (resultSet.next()) {
- result.add(new ShardingSphereRowData(getRow(table, resultSet, selectedColumnNames)));
- }
- return result;
- }
-
- private List<Object> getRow(final ShardingSphereTable table, final ResultSet resultSet, final List<String> selectedColumnNames) throws SQLException {
- List<Object> result = new LinkedList<>();
- for (ShardingSphereColumn each : table.getColumns().values()) {
- if (selectedColumnNames.contains(each.getName())) {
- result.add(resultSet.getObject(each.getName()));
- } else {
- result.add(mockValue(each.getDataType()));
- }
- }
- return result;
- }
-
- private Object mockValue(final int dataType) {
- switch (dataType) {
- case Types.BIGINT:
- return 0L;
- case Types.VARCHAR:
- case Types.CHAR:
- case Types.OTHER:
- case Types.ARRAY:
- return "";
- case Types.INTEGER:
- case Types.SMALLINT:
- return 0;
- case Types.REAL:
- return Float.valueOf("0");
- case Types.BIT:
- return false;
- default:
- return null;
- }
- }
-
- private List<ShardingSphereRowData> decorateTableName(final List<ShardingSphereRowData> rows, final ShardingSphereTable table, final Collection<ShardingSphereRule> rules) {
+ private Collection<ShardingSphereRowData> decorateTableName(final Collection<ShardingSphereRowData> rows, final ShardingSphereTable table, final Collection<ShardingSphereRule> rules) {
Optional<DataNodeContainedRule> dataNodeContainedRule = rules.stream().filter(rule -> rule instanceof DataNodeContainedRule).map(rule -> (DataNodeContainedRule) rule).findFirst();
if (!dataNodeContainedRule.isPresent()) {
return rows;
}
int tableNameIndex = table.getColumnNames().indexOf("relname");
- List<ShardingSphereRowData> result = new LinkedList<>();
+ Collection<ShardingSphereRowData> result = new LinkedList<>();
for (ShardingSphereRowData each : rows) {
String tableName = (String) each.getRows().get(tableNameIndex);
Optional<String> logicTableName = dataNodeContainedRule.get().findLogicTableByActualTable(tableName);
diff --git a/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/data/collector/tables/PgNamespaceTableCollector.java b/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/data/collector/tables/PgNamespaceTableCollector.java
index ead5956a951..60cad23fb21 100644
--- a/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/data/collector/tables/PgNamespaceTableCollector.java
+++ b/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/data/collector/tables/PgNamespaceTableCollector.java
@@ -20,22 +20,17 @@ package org.apache.shardingsphere.infra.metadata.data.collector.tables;
import org.apache.shardingsphere.infra.metadata.data.ShardingSphereRowData;
import org.apache.shardingsphere.infra.metadata.data.ShardingSphereTableData;
import org.apache.shardingsphere.infra.metadata.data.collector.ShardingSphereDataCollector;
+import org.apache.shardingsphere.infra.metadata.data.collector.ShardingSphereTableDataCollectorUtil;
import org.apache.shardingsphere.infra.metadata.database.ShardingSphereDatabase;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereTable;
-import javax.sql.DataSource;
-import java.sql.Connection;
-import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
import java.sql.SQLException;
-import java.sql.Statement;
-import java.sql.Types;
import java.util.ArrayList;
-import java.util.LinkedHashSet;
-import java.util.List;
+import java.util.Arrays;
+import java.util.Collection;
import java.util.Map;
import java.util.Optional;
-import java.util.Set;
+import java.util.stream.Collectors;
/**
* Table pg_namespace data collector.
@@ -44,42 +39,20 @@ public final class PgNamespaceTableCollector implements ShardingSphereDataCollec
private static final String PG_NAMESPACE = "pg_namespace";
+ private static final String COLUMN_NAMES = "oid, nspname, nspowner, nspacl";
+
+ private static final String SELECT_SQL = "SELECT " + COLUMN_NAMES + " FROM pg_catalog.pg_namespace";
+
@Override
public Optional<ShardingSphereTableData> collect(final String databaseName, final ShardingSphereTable table,
final Map<String, ShardingSphereDatabase> shardingSphereDatabases) throws SQLException {
- Set<ShardingSphereRowData> rows = new LinkedHashSet<>();
- for (DataSource each : shardingSphereDatabases.get(databaseName).getResourceMetaData().getDataSources().values()) {
- try (
- Connection connection = each.getConnection();
- Statement statement = connection.createStatement();
- ResultSet resultSet = statement.executeQuery("SELECT oid, nspname, nspowner, nspacl FROM pg_catalog.pg_namespace")) {
- ResultSetMetaData metaData = resultSet.getMetaData();
- while (resultSet.next()) {
- List<Object> row = new ArrayList<>(metaData.getColumnCount());
- for (int i = 1; i <= metaData.getColumnCount(); i++) {
- row.add(convertIfNecessary(resultSet.getObject(i), metaData.getColumnType(i)));
- }
- ShardingSphereRowData rowData = new ShardingSphereRowData(row);
- rows.add(rowData);
- }
- }
- }
+ Collection<ShardingSphereRowData> rows = ShardingSphereTableDataCollectorUtil.collectRowData(shardingSphereDatabases.get(databaseName).getResourceMetaData().getDataSources().values(),
+ SELECT_SQL, table, Arrays.stream(COLUMN_NAMES.split(",")).map(String::trim).collect(Collectors.toList()));
ShardingSphereTableData result = new ShardingSphereTableData(PG_NAMESPACE, new ArrayList<>(table.getColumns().values()));
- result.getRows().addAll(rows);
+ result.getRows().addAll(rows.stream().distinct().collect(Collectors.toList()));
return Optional.of(result);
}
- // TODO extract to util
- private Object convertIfNecessary(final Object data, final int dataType) {
- if (Types.ARRAY == dataType) {
- return null == data ? null : data.toString();
- }
- if (Types.BIGINT == dataType) {
- return null == data ? null : Long.valueOf(data.toString());
- }
- return data;
- }
-
@Override
public String getType() {
return PG_NAMESPACE;
diff --git a/infra/common/src/main/resources/schema/opengauss/pg_catalog/pg_class.yaml b/infra/common/src/main/resources/schema/opengauss/pg_catalog/pg_class.yaml
index 0d59b0b1f54..e1d096c1673 100644
--- a/infra/common/src/main/resources/schema/opengauss/pg_catalog/pg_class.yaml
+++ b/infra/common/src/main/resources/schema/opengauss/pg_catalog/pg_class.yaml
@@ -17,6 +17,14 @@
name: pg_class
columns:
+ oid:
+ caseSensitive: true
+ dataType: -5
+ generated: false
+ name: oid
+ primaryKey: false
+ unsigned: false
+ visible: true
relname:
caseSensitive: true
dataType: 12
diff --git a/infra/common/src/main/resources/schema/opengauss/pg_catalog/pg_namespace.yaml b/infra/common/src/main/resources/schema/opengauss/pg_catalog/pg_namespace.yaml
index dca6fe0dfec..9643b1fb66a 100644
--- a/infra/common/src/main/resources/schema/opengauss/pg_catalog/pg_namespace.yaml
+++ b/infra/common/src/main/resources/schema/opengauss/pg_catalog/pg_namespace.yaml
@@ -17,6 +17,14 @@
name: pg_namespace
columns:
+ oid:
+ caseSensitive: true
+ dataType: -5
+ generated: false
+ name: oid
+ primaryKey: true
+ unsigned: false
+ visible: true
nspname:
caseSensitive: true
dataType: 12