This is an automated email from the ASF dual-hosted git repository.
tuichenchuxin 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 867feef3b39 support save unique key in metadata for postgres (#28063)
867feef3b39 is described below
commit 867feef3b39b72c48cfde5365ce550a64890e726
Author: Zonglei Dong <[email protected]>
AuthorDate: Mon Aug 14 09:34:22 2023 +0800
support save unique key in metadata for postgres (#28063)
---
.../data/loader/PostgreSQLMetaDataLoader.java | 25 ++++++++++++++++++++++
.../data/loader/PostgreSQLMetaDataLoaderTest.java | 22 ++++++++++++++++++-
2 files changed, 46 insertions(+), 1 deletion(-)
diff --git
a/infra/database/type/postgresql/src/main/java/org/apache/shardingsphere/infra/database/postgresql/metadata/data/loader/PostgreSQLMetaDataLoader.java
b/infra/database/type/postgresql/src/main/java/org/apache/shardingsphere/infra/database/postgresql/metadata/data/loader/PostgreSQLMetaDataLoader.java
index 8dc69b40f12..dc52dcadb8b 100644
---
a/infra/database/type/postgresql/src/main/java/org/apache/shardingsphere/infra/database/postgresql/metadata/data/loader/PostgreSQLMetaDataLoader.java
+++
b/infra/database/type/postgresql/src/main/java/org/apache/shardingsphere/infra/database/postgresql/metadata/data/loader/PostgreSQLMetaDataLoader.java
@@ -40,6 +40,7 @@ import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.Map;
+import java.util.Optional;
import java.util.Set;
import java.util.stream.Collectors;
@@ -66,6 +67,10 @@ public final class PostgreSQLMetaDataLoader implements
DialectMetaDataLoader {
private static final String BASIC_INDEX_META_DATA_SQL = "SELECT tablename,
indexname, schemaname FROM pg_indexes WHERE schemaname IN (%s)";
+ private static final String ADVANCE_INDEX_META_DATA_SQL = "SELECT
idx.relname as index_name, insp.nspname as index_schema, tbl.relname as
table_name, pgi.indisunique as is_unique"
+ + " FROM pg_index pgi JOIN pg_class idx ON idx.oid =
pgi.indexrelid JOIN pg_namespace insp ON insp.oid = idx.relnamespace JOIN
pg_class tbl ON tbl.oid = pgi.indrelid"
+ + " JOIN pg_namespace tnsp ON tnsp.oid = tbl.relnamespace WHERE
tnsp.nspname IN (%s)";
+
private static final String LOAD_ALL_ROLE_TABLE_GRANTS_SQL = "SELECT
table_name FROM information_schema.role_table_grants";
private static final String LOAD_FILTERED_ROLE_TABLE_GRANTS_SQL =
LOAD_ALL_ROLE_TABLE_GRANTS_SQL + " WHERE table_name IN (%s)";
@@ -99,6 +104,22 @@ public final class PostgreSQLMetaDataLoader implements
DialectMetaDataLoader {
indexMetaDataMap.put(tableName, new IndexMetaData(indexName));
}
}
+ try (PreparedStatement preparedStatement =
connection.prepareStatement(getAdvanceIndexMetaDataSQL(schemaNames)); ResultSet
resultSet = preparedStatement.executeQuery()) {
+ while (resultSet.next()) {
+ String schemaName = resultSet.getString("index_schema");
+ String tableName = resultSet.getString("table_name");
+ String indexName = resultSet.getString("index_name");
+ boolean isUnique = resultSet.getBoolean("is_unique");
+ Collection<IndexMetaData> indexMetaDatas =
result.getOrDefault(schemaName, LinkedHashMultimap.create()).get(tableName);
+ if (null == indexMetaDatas || indexMetaDatas.isEmpty()) {
+ continue;
+ }
+ Optional<IndexMetaData> indexMetaData =
indexMetaDatas.stream().filter(each ->
each.getName().equals(indexName)).findFirst();
+ if (indexMetaData.isPresent()) {
+ indexMetaData.get().setUnique(isUnique);
+ }
+ }
+ }
return result;
}
@@ -106,6 +127,10 @@ public final class PostgreSQLMetaDataLoader implements
DialectMetaDataLoader {
return String.format(BASIC_INDEX_META_DATA_SQL,
schemaNames.stream().map(each -> String.format("'%s'",
each)).collect(Collectors.joining(",")));
}
+ private String getAdvanceIndexMetaDataSQL(final Collection<String>
schemaNames) {
+ return String.format(ADVANCE_INDEX_META_DATA_SQL,
schemaNames.stream().map(each -> String.format("'%s'",
each)).collect(Collectors.joining(",")));
+ }
+
private Map<String, Multimap<String, ColumnMetaData>>
loadColumnMetaDataMap(final Connection connection, final Collection<String>
tables,
final Collection<String> schemaNames) throws SQLException {
Map<String, Multimap<String, ColumnMetaData>> result = new
LinkedHashMap<>();
diff --git
a/infra/database/type/postgresql/src/test/java/org/apache/shardingsphere/infra/database/postgresql/metadata/data/loader/PostgreSQLMetaDataLoaderTest.java
b/infra/database/type/postgresql/src/test/java/org/apache/shardingsphere/infra/database/postgresql/metadata/data/loader/PostgreSQLMetaDataLoaderTest.java
index 68c9e125da7..297d518022f 100644
---
a/infra/database/type/postgresql/src/test/java/org/apache/shardingsphere/infra/database/postgresql/metadata/data/loader/PostgreSQLMetaDataLoaderTest.java
+++
b/infra/database/type/postgresql/src/test/java/org/apache/shardingsphere/infra/database/postgresql/metadata/data/loader/PostgreSQLMetaDataLoaderTest.java
@@ -60,6 +60,10 @@ class PostgreSQLMetaDataLoaderTest {
private static final String BASIC_INDEX_META_DATA_SQL = "SELECT tablename,
indexname, schemaname FROM pg_indexes WHERE schemaname IN ('public')";
+ private static final String ADVANCE_INDEX_META_DATA_SQL = "SELECT
idx.relname as index_name, insp.nspname as index_schema, tbl.relname as
table_name, pgi.indisunique as is_unique"
+ + " FROM pg_index pgi JOIN pg_class idx ON idx.oid =
pgi.indexrelid JOIN pg_namespace insp ON insp.oid = idx.relnamespace JOIN
pg_class tbl ON tbl.oid = pgi.indrelid"
+ + " JOIN pg_namespace tnsp ON tnsp.oid = tbl.relnamespace WHERE
tnsp.nspname IN ('public')";
+
private static final String BASIC_CONSTRAINT_META_DATA_SQL = "SELECT
tc.table_schema,tc.table_name,tc.constraint_name,pgo.relname refer_table_name
FROM information_schema.table_constraints tc "
+ "JOIN pg_constraint pgc ON tc.constraint_name = pgc.conname AND
contype='f' "
+ "JOIN pg_class pgo ON pgc.confrelid = pgo.oid "
@@ -78,6 +82,8 @@ class PostgreSQLMetaDataLoaderTest {
when(dataSource.getConnection().prepareStatement(PRIMARY_KEY_META_DATA_SQL).executeQuery()).thenReturn(primaryKeyResultSet);
ResultSet indexResultSet = mockIndexMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(BASIC_INDEX_META_DATA_SQL).executeQuery()).thenReturn(indexResultSet);
+ ResultSet advanceIndexResultSet = mockAdvanceIndexMetaDataResultSet();
+
when(dataSource.getConnection().prepareStatement(ADVANCE_INDEX_META_DATA_SQL).executeQuery()).thenReturn(advanceIndexResultSet);
ResultSet constraintResultSet = mockConstraintMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(BASIC_CONSTRAINT_META_DATA_SQL).executeQuery()).thenReturn(constraintResultSet);
ResultSet roleTableGrantsResultSet = mockRoleTableGrantsResultSet();
@@ -103,6 +109,8 @@ class PostgreSQLMetaDataLoaderTest {
when(dataSource.getConnection().prepareStatement(PRIMARY_KEY_META_DATA_SQL).executeQuery()).thenReturn(primaryKeyResultSet);
ResultSet indexResultSet = mockIndexMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(BASIC_INDEX_META_DATA_SQL).executeQuery()).thenReturn(indexResultSet);
+ ResultSet advanceIndexResultSet = mockAdvanceIndexMetaDataResultSet();
+
when(dataSource.getConnection().prepareStatement(ADVANCE_INDEX_META_DATA_SQL).executeQuery()).thenReturn(advanceIndexResultSet);
ResultSet constraintResultSet = mockConstraintMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(BASIC_CONSTRAINT_META_DATA_SQL).executeQuery()).thenReturn(constraintResultSet);
ResultSet roleTableGrantsResultSet = mockRoleTableGrantsResultSet();
@@ -164,6 +172,16 @@ class PostgreSQLMetaDataLoaderTest {
return result;
}
+ private ResultSet mockAdvanceIndexMetaDataResultSet() throws SQLException {
+ ResultSet result = mock(ResultSet.class);
+ when(result.next()).thenReturn(true, false);
+ when(result.getString("table_name")).thenReturn("tbl");
+ when(result.getString("index_name")).thenReturn("id");
+ when(result.getString("index_schema")).thenReturn("public");
+ when(result.getBoolean("is_unique")).thenReturn(true);
+ return result;
+ }
+
private ResultSet mockConstraintMetaDataResultSet() throws SQLException {
ResultSet result = mock(ResultSet.class);
when(result.next()).thenReturn(true, false);
@@ -189,7 +207,9 @@ class PostgreSQLMetaDataLoaderTest {
assertThat(columnsIterator.next(), is(new ColumnMetaData("name",
Types.VARCHAR, false, false, true, true, false, true)));
assertThat(actualTableMetaData.getIndexes().size(), is(1));
Iterator<IndexMetaData> indexesIterator =
actualTableMetaData.getIndexes().iterator();
- assertThat(indexesIterator.next(), is(new IndexMetaData("id")));
+ IndexMetaData indexMetaData = new IndexMetaData("id");
+ indexMetaData.setUnique(true);
+ assertThat(indexesIterator.next(), is(indexMetaData));
assertThat(actualTableMetaData.getConstraints().size(), is(1));
Iterator<ConstraintMetaData> constrainsIterator =
actualTableMetaData.getConstraints().iterator();
assertThat(constrainsIterator.next(), is(new
ConstraintMetaData("tbl_con", "refer_tbl")));
