You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/08/28 07:44:12 UTC
[GitHub] [shardingsphere] lhyundeadsoul opened a new pull request, #20616: add constraint info in PostgreSQLSchemaMetaDataLoader
lhyundeadsoul opened a new pull request, #20616:
URL: https://github.com/apache/shardingsphere/pull/20616
fix #16086
Changes proposed in this pull request:
- add constraint info in PostgreSQLSchemaMetaDataLoader
-
-
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on a diff in pull request #20616: add constraint info in PostgreSQLSchemaMetaDataLoader
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on code in PR #20616:
URL: https://github.com/apache/shardingsphere/pull/20616#discussion_r963213382
##########
shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/PostgreSQLSchemaMetaDataLoader.java:
##########
@@ -107,6 +116,29 @@ private Collection<ViewMetaData> loadViewMetaData(final DataSource dataSource, f
return result;
}
+ private Map<String, Multimap<String, ConstraintMetaData>> loadConstraintMetaDataMap(final DataSource dataSource,
+ final Collection<String> schemaNames) throws SQLException {
+ Map<String, Multimap<String, ConstraintMetaData>> result = new LinkedHashMap<>();
+ try (Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(getConstraintKeyMetaDataSQL(schemaNames))) {
+ try (ResultSet resultSet = preparedStatement.executeQuery()) {
+ while (resultSet.next()) {
+ String schemaName = resultSet.getString("table_schema");
+ Multimap<String, ConstraintMetaData> constraintMetaData = result.computeIfAbsent(schemaName, key -> LinkedHashMultimap.create());
+
Review Comment:
Please remove useless blank line.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lhyundeadsoul commented on a diff in pull request #20616: add constraint info in PostgreSQLSchemaMetaDataLoader
Posted by GitBox <gi...@apache.org>.
lhyundeadsoul commented on code in PR #20616:
URL: https://github.com/apache/shardingsphere/pull/20616#discussion_r957938184
##########
shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/PostgreSQLSchemaMetaDataLoader.java:
##########
@@ -45,48 +46,81 @@
* Schema meta data loader for PostgreSQL.
*/
public final class PostgreSQLSchemaMetaDataLoader implements DialectSchemaMetaDataLoader {
-
+
private static final String BASIC_TABLE_META_DATA_SQL = "SELECT table_name, column_name, ordinal_position, data_type, udt_name, column_default, table_schema"
+ " FROM information_schema.columns WHERE table_schema IN (%s)";
-
+
private static final String TABLE_META_DATA_SQL_WITHOUT_TABLES = BASIC_TABLE_META_DATA_SQL + " ORDER BY ordinal_position";
-
+
private static final String TABLE_META_DATA_SQL_WITH_TABLES = BASIC_TABLE_META_DATA_SQL + " AND table_name IN (%s) ORDER BY ordinal_position";
-
+
+ private static final String FOREIGN_KEY_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 "
+ + "WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema IN (%s)";
+
private static final String PRIMARY_KEY_META_DATA_SQL = "SELECT tc.table_name, kc.column_name, kc.table_schema FROM information_schema.table_constraints tc"
+ " JOIN information_schema.key_column_usage kc ON kc.table_schema = tc.table_schema AND kc.table_name = tc.table_name AND kc.constraint_name = tc.constraint_name"
+ " WHERE tc.constraint_type = 'PRIMARY KEY' AND kc.ordinal_position IS NOT NULL AND kc.table_schema IN (%s)";
-
+
private static final String BASIC_INDEX_META_DATA_SQL = "SELECT tablename, indexname, schemaname FROM pg_indexes WHERE schemaname 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_FILTED_ROLE_TABLE_GRANTS_SQL = LOAD_ALL_ROLE_TABLE_GRANTS_SQL + " WHERE table_name IN (%s)";
-
+
@Override
public Collection<SchemaMetaData> load(final DataSource dataSource, final Collection<String> tables, final String defaultSchemaName) throws SQLException {
Collection<String> schemaNames = loadSchemaNames(dataSource, DatabaseTypeFactory.getInstance(getType()));
Map<String, Multimap<String, IndexMetaData>> schemaIndexMetaDataMap = loadIndexMetaDataMap(dataSource, schemaNames);
Map<String, Multimap<String, ColumnMetaData>> schemaColumnMetaDataMap = loadColumnMetaDataMap(dataSource, tables, schemaNames);
+ Map<String, Multimap<String, ConstraintMetaData>> schemaConstraintMetaDataMap = loadConstraintMetaDataMap(dataSource, schemaNames);
Collection<SchemaMetaData> result = new LinkedList<>();
- for (String each : schemaNames) {
- Multimap<String, IndexMetaData> tableIndexMetaDataMap = schemaIndexMetaDataMap.getOrDefault(each, LinkedHashMultimap.create());
- Multimap<String, ColumnMetaData> tableColumnMetaDataMap = schemaColumnMetaDataMap.getOrDefault(each, LinkedHashMultimap.create());
- result.add(new SchemaMetaData(each, createTableMetaDataList(tableIndexMetaDataMap, tableColumnMetaDataMap)));
+ for (String schemaName : schemaNames) {
Review Comment:
Is `schemaName` more explicit than `each` to reveal the object is here?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu merged pull request #20616: add constraint info in PostgreSQLSchemaMetaDataLoader
Posted by GitBox <gi...@apache.org>.
strongduanmu merged PR #20616:
URL: https://github.com/apache/shardingsphere/pull/20616
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on pull request #20616: add constraint info in PostgreSQLSchemaMetaDataLoader
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on PR #20616:
URL: https://github.com/apache/shardingsphere/pull/20616#issuecomment-1236214012
Hi @lhyundeadsoul, can you fix ci error?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] codecov-commenter commented on pull request #20616: add constraint info in PostgreSQLSchemaMetaDataLoader
Posted by GitBox <gi...@apache.org>.
codecov-commenter commented on PR #20616:
URL: https://github.com/apache/shardingsphere/pull/20616#issuecomment-1229407168
# [Codecov](https://codecov.io/gh/apache/shardingsphere/pull/20616?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation) Report
> Merging [#20616](https://codecov.io/gh/apache/shardingsphere/pull/20616?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation) (6e30a52) into [master](https://codecov.io/gh/apache/shardingsphere/commit/1d3b0d15d485f94fb8cfaf253c8255b3f53349ba?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation) (1d3b0d1) will **increase** coverage by `0.00%`.
> The diff coverage is `92.30%`.
```diff
@@ Coverage Diff @@
## master #20616 +/- ##
=========================================
Coverage 61.06% 61.07%
Complexity 2414 2414
=========================================
Files 3952 3952
Lines 54879 54897 +18
Branches 9319 9322 +3
=========================================
+ Hits 33514 33527 +13
- Misses 18518 18521 +3
- Partials 2847 2849 +2
```
| [Impacted Files](https://codecov.io/gh/apache/shardingsphere/pull/20616?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation) | Coverage Δ | |
|---|---|---|
| [...loader/dialect/PostgreSQLSchemaMetaDataLoader.java](https://codecov.io/gh/apache/shardingsphere/pull/20616/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation#diff-c2hhcmRpbmdzcGhlcmUtaW5mcmEvc2hhcmRpbmdzcGhlcmUtaW5mcmEtY29tbW9uL3NyYy9tYWluL2phdmEvb3JnL2FwYWNoZS9zaGFyZGluZ3NwaGVyZS9pbmZyYS9tZXRhZGF0YS9kYXRhYmFzZS9zY2hlbWEvbG9hZGVyL2RpYWxlY3QvUG9zdGdyZVNRTFNjaGVtYU1ldGFEYXRhTG9hZGVyLmphdmE=) | `87.37% <92.30%> (+0.31%)` | :arrow_up: |
| [...handler/distsql/ral/hint/enums/HintSourceType.java](https://codecov.io/gh/apache/shardingsphere/pull/20616/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation#diff-c2hhcmRpbmdzcGhlcmUtcHJveHkvc2hhcmRpbmdzcGhlcmUtcHJveHktYmFja2VuZC9zcmMvbWFpbi9qYXZhL29yZy9hcGFjaGUvc2hhcmRpbmdzcGhlcmUvcHJveHkvYmFja2VuZC9oYW5kbGVyL2Rpc3RzcWwvcmFsL2hpbnQvZW51bXMvSGludFNvdXJjZVR5cGUuamF2YQ==) | `0.00% <0.00%> (-42.86%)` | :arrow_down: |
:mega: We’re building smart automated test selection to slash your CI/CD build times. [Learn more](https://about.codecov.io/iterative-testing/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on a diff in pull request #20616: add constraint info in PostgreSQLSchemaMetaDataLoader
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on code in PR #20616:
URL: https://github.com/apache/shardingsphere/pull/20616#discussion_r959695938
##########
shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/PostgreSQLSchemaMetaDataLoader.java:
##########
@@ -45,48 +46,81 @@
* Schema meta data loader for PostgreSQL.
*/
public final class PostgreSQLSchemaMetaDataLoader implements DialectSchemaMetaDataLoader {
-
+
private static final String BASIC_TABLE_META_DATA_SQL = "SELECT table_name, column_name, ordinal_position, data_type, udt_name, column_default, table_schema"
+ " FROM information_schema.columns WHERE table_schema IN (%s)";
-
+
private static final String TABLE_META_DATA_SQL_WITHOUT_TABLES = BASIC_TABLE_META_DATA_SQL + " ORDER BY ordinal_position";
-
+
private static final String TABLE_META_DATA_SQL_WITH_TABLES = BASIC_TABLE_META_DATA_SQL + " AND table_name IN (%s) ORDER BY ordinal_position";
-
+
+ private static final String FOREIGN_KEY_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 "
+ + "WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema IN (%s)";
+
private static final String PRIMARY_KEY_META_DATA_SQL = "SELECT tc.table_name, kc.column_name, kc.table_schema FROM information_schema.table_constraints tc"
+ " JOIN information_schema.key_column_usage kc ON kc.table_schema = tc.table_schema AND kc.table_name = tc.table_name AND kc.constraint_name = tc.constraint_name"
+ " WHERE tc.constraint_type = 'PRIMARY KEY' AND kc.ordinal_position IS NOT NULL AND kc.table_schema IN (%s)";
-
+
private static final String BASIC_INDEX_META_DATA_SQL = "SELECT tablename, indexname, schemaname FROM pg_indexes WHERE schemaname 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_FILTED_ROLE_TABLE_GRANTS_SQL = LOAD_ALL_ROLE_TABLE_GRANTS_SQL + " WHERE table_name IN (%s)";
-
+
@Override
public Collection<SchemaMetaData> load(final DataSource dataSource, final Collection<String> tables, final String defaultSchemaName) throws SQLException {
Collection<String> schemaNames = loadSchemaNames(dataSource, DatabaseTypeFactory.getInstance(getType()));
Map<String, Multimap<String, IndexMetaData>> schemaIndexMetaDataMap = loadIndexMetaDataMap(dataSource, schemaNames);
Map<String, Multimap<String, ColumnMetaData>> schemaColumnMetaDataMap = loadColumnMetaDataMap(dataSource, tables, schemaNames);
+ Map<String, Multimap<String, ConstraintMetaData>> schemaConstraintMetaDataMap = loadConstraintMetaDataMap(dataSource, schemaNames);
Collection<SchemaMetaData> result = new LinkedList<>();
- for (String each : schemaNames) {
- Multimap<String, IndexMetaData> tableIndexMetaDataMap = schemaIndexMetaDataMap.getOrDefault(each, LinkedHashMultimap.create());
- Multimap<String, ColumnMetaData> tableColumnMetaDataMap = schemaColumnMetaDataMap.getOrDefault(each, LinkedHashMultimap.create());
- result.add(new SchemaMetaData(each, createTableMetaDataList(tableIndexMetaDataMap, tableColumnMetaDataMap)));
+ for (String schemaName : schemaNames) {
Review Comment:
> Is `schemaName` more explicit than `each` to reveal the object is here?
You can read code conduct first.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on a diff in pull request #20616: add constraint info in PostgreSQLSchemaMetaDataLoader
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on code in PR #20616:
URL: https://github.com/apache/shardingsphere/pull/20616#discussion_r957931030
##########
shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/PostgreSQLSchemaMetaDataLoader.java:
##########
@@ -45,48 +46,81 @@
* Schema meta data loader for PostgreSQL.
*/
public final class PostgreSQLSchemaMetaDataLoader implements DialectSchemaMetaDataLoader {
-
+
private static final String BASIC_TABLE_META_DATA_SQL = "SELECT table_name, column_name, ordinal_position, data_type, udt_name, column_default, table_schema"
+ " FROM information_schema.columns WHERE table_schema IN (%s)";
-
Review Comment:
Please keep the same indent with previous line.
##########
shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/PostgreSQLSchemaMetaDataLoader.java:
##########
@@ -45,48 +46,81 @@
* Schema meta data loader for PostgreSQL.
*/
public final class PostgreSQLSchemaMetaDataLoader implements DialectSchemaMetaDataLoader {
-
+
private static final String BASIC_TABLE_META_DATA_SQL = "SELECT table_name, column_name, ordinal_position, data_type, udt_name, column_default, table_schema"
+ " FROM information_schema.columns WHERE table_schema IN (%s)";
-
+
private static final String TABLE_META_DATA_SQL_WITHOUT_TABLES = BASIC_TABLE_META_DATA_SQL + " ORDER BY ordinal_position";
-
+
private static final String TABLE_META_DATA_SQL_WITH_TABLES = BASIC_TABLE_META_DATA_SQL + " AND table_name IN (%s) ORDER BY ordinal_position";
-
+
+ private static final String FOREIGN_KEY_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 "
+ + "WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema IN (%s)";
+
private static final String PRIMARY_KEY_META_DATA_SQL = "SELECT tc.table_name, kc.column_name, kc.table_schema FROM information_schema.table_constraints tc"
+ " JOIN information_schema.key_column_usage kc ON kc.table_schema = tc.table_schema AND kc.table_name = tc.table_name AND kc.constraint_name = tc.constraint_name"
+ " WHERE tc.constraint_type = 'PRIMARY KEY' AND kc.ordinal_position IS NOT NULL AND kc.table_schema IN (%s)";
-
+
private static final String BASIC_INDEX_META_DATA_SQL = "SELECT tablename, indexname, schemaname FROM pg_indexes WHERE schemaname 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_FILTED_ROLE_TABLE_GRANTS_SQL = LOAD_ALL_ROLE_TABLE_GRANTS_SQL + " WHERE table_name IN (%s)";
-
+
@Override
public Collection<SchemaMetaData> load(final DataSource dataSource, final Collection<String> tables, final String defaultSchemaName) throws SQLException {
Collection<String> schemaNames = loadSchemaNames(dataSource, DatabaseTypeFactory.getInstance(getType()));
Map<String, Multimap<String, IndexMetaData>> schemaIndexMetaDataMap = loadIndexMetaDataMap(dataSource, schemaNames);
Map<String, Multimap<String, ColumnMetaData>> schemaColumnMetaDataMap = loadColumnMetaDataMap(dataSource, tables, schemaNames);
+ Map<String, Multimap<String, ConstraintMetaData>> schemaConstraintMetaDataMap = loadConstraintMetaDataMap(dataSource, schemaNames);
Collection<SchemaMetaData> result = new LinkedList<>();
- for (String each : schemaNames) {
- Multimap<String, IndexMetaData> tableIndexMetaDataMap = schemaIndexMetaDataMap.getOrDefault(each, LinkedHashMultimap.create());
- Multimap<String, ColumnMetaData> tableColumnMetaDataMap = schemaColumnMetaDataMap.getOrDefault(each, LinkedHashMultimap.create());
- result.add(new SchemaMetaData(each, createTableMetaDataList(tableIndexMetaDataMap, tableColumnMetaDataMap)));
+ for (String schemaName : schemaNames) {
Review Comment:
Please rename schemaName to each.
##########
shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/PostgreSQLSchemaMetaDataLoader.java:
##########
@@ -45,48 +46,81 @@
* Schema meta data loader for PostgreSQL.
*/
public final class PostgreSQLSchemaMetaDataLoader implements DialectSchemaMetaDataLoader {
-
+
private static final String BASIC_TABLE_META_DATA_SQL = "SELECT table_name, column_name, ordinal_position, data_type, udt_name, column_default, table_schema"
+ " FROM information_schema.columns WHERE table_schema IN (%s)";
-
+
private static final String TABLE_META_DATA_SQL_WITHOUT_TABLES = BASIC_TABLE_META_DATA_SQL + " ORDER BY ordinal_position";
-
+
private static final String TABLE_META_DATA_SQL_WITH_TABLES = BASIC_TABLE_META_DATA_SQL + " AND table_name IN (%s) ORDER BY ordinal_position";
-
+
+ private static final String FOREIGN_KEY_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 "
+ + "WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema IN (%s)";
+
private static final String PRIMARY_KEY_META_DATA_SQL = "SELECT tc.table_name, kc.column_name, kc.table_schema FROM information_schema.table_constraints tc"
+ " JOIN information_schema.key_column_usage kc ON kc.table_schema = tc.table_schema AND kc.table_name = tc.table_name AND kc.constraint_name = tc.constraint_name"
+ " WHERE tc.constraint_type = 'PRIMARY KEY' AND kc.ordinal_position IS NOT NULL AND kc.table_schema IN (%s)";
-
+
private static final String BASIC_INDEX_META_DATA_SQL = "SELECT tablename, indexname, schemaname FROM pg_indexes WHERE schemaname 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_FILTED_ROLE_TABLE_GRANTS_SQL = LOAD_ALL_ROLE_TABLE_GRANTS_SQL + " WHERE table_name IN (%s)";
-
+
@Override
public Collection<SchemaMetaData> load(final DataSource dataSource, final Collection<String> tables, final String defaultSchemaName) throws SQLException {
Collection<String> schemaNames = loadSchemaNames(dataSource, DatabaseTypeFactory.getInstance(getType()));
Map<String, Multimap<String, IndexMetaData>> schemaIndexMetaDataMap = loadIndexMetaDataMap(dataSource, schemaNames);
Map<String, Multimap<String, ColumnMetaData>> schemaColumnMetaDataMap = loadColumnMetaDataMap(dataSource, tables, schemaNames);
+ Map<String, Multimap<String, ConstraintMetaData>> schemaConstraintMetaDataMap = loadConstraintMetaDataMap(dataSource, schemaNames);
Collection<SchemaMetaData> result = new LinkedList<>();
- for (String each : schemaNames) {
- Multimap<String, IndexMetaData> tableIndexMetaDataMap = schemaIndexMetaDataMap.getOrDefault(each, LinkedHashMultimap.create());
- Multimap<String, ColumnMetaData> tableColumnMetaDataMap = schemaColumnMetaDataMap.getOrDefault(each, LinkedHashMultimap.create());
- result.add(new SchemaMetaData(each, createTableMetaDataList(tableIndexMetaDataMap, tableColumnMetaDataMap)));
+ for (String schemaName : schemaNames) {
+ Multimap<String, IndexMetaData> tableIndexMetaDataMap = schemaIndexMetaDataMap.getOrDefault(schemaName, LinkedHashMultimap.create());
+ Multimap<String, ColumnMetaData> tableColumnMetaDataMap = schemaColumnMetaDataMap.getOrDefault(schemaName, LinkedHashMultimap.create());
+ Multimap<String, ConstraintMetaData> tableConstraintMetaDataMap = schemaConstraintMetaDataMap.getOrDefault(schemaName, LinkedHashMultimap.create());
+ result.add(new SchemaMetaData(schemaName, createTableMetaDataList(tableIndexMetaDataMap, tableColumnMetaDataMap, tableConstraintMetaDataMap)));
}
return result;
}
-
- private Collection<TableMetaData> createTableMetaDataList(final Multimap<String, IndexMetaData> tableIndexMetaDataMap, final Multimap<String, ColumnMetaData> tableColumnMetaDataMap) {
+
+ private Collection<TableMetaData> createTableMetaDataList(final Multimap<String, IndexMetaData> tableIndexMetaDataMap,
+ final Multimap<String, ColumnMetaData> tableColumnMetaDataMap,
+ final Multimap<String, ConstraintMetaData> tableConstraintMetaDataMap) {
Collection<TableMetaData> result = new LinkedList<>();
- for (String each : tableColumnMetaDataMap.keySet()) {
- Collection<ColumnMetaData> columnMetaDataList = tableColumnMetaDataMap.get(each);
- Collection<IndexMetaData> indexMetaDataList = tableIndexMetaDataMap.get(each);
- result.add(new TableMetaData(each, columnMetaDataList, indexMetaDataList, Collections.emptyList()));
+ for (String columnName : tableColumnMetaDataMap.keySet()) {
+ Collection<ColumnMetaData> columnMetaDataList = tableColumnMetaDataMap.get(columnName);
+ Collection<IndexMetaData> indexMetaDataList = tableIndexMetaDataMap.get(columnName);
+ Collection<ConstraintMetaData> constraintMetaDataList = tableConstraintMetaDataMap.get(columnName);
+ result.add(new TableMetaData(columnName, columnMetaDataList, indexMetaDataList, constraintMetaDataList));
}
return result;
}
-
+
+ private Map<String, Multimap<String, ConstraintMetaData>> loadConstraintMetaDataMap(final DataSource dataSource,
+ final Collection<String> schemaNames) throws SQLException {
+ Map<String, Multimap<String, ConstraintMetaData>> result = new LinkedHashMap<>();
+ try (Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(getConstraintKeyMetaDataSQL(schemaNames))) {
+ try (ResultSet resultSet = preparedStatement.executeQuery()) {
+ while (resultSet.next()) {
+ String schemaName = resultSet.getString("table_schema");
+ Multimap<String, ConstraintMetaData> constraintMetaData = result.computeIfAbsent(schemaName, key -> LinkedHashMultimap.create());
+
Review Comment:
Please remove this useless blank line.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lhyundeadsoul commented on a diff in pull request #20616: add constraint info in PostgreSQLSchemaMetaDataLoader
Posted by GitBox <gi...@apache.org>.
lhyundeadsoul commented on code in PR #20616:
URL: https://github.com/apache/shardingsphere/pull/20616#discussion_r957938184
##########
shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/PostgreSQLSchemaMetaDataLoader.java:
##########
@@ -45,48 +46,81 @@
* Schema meta data loader for PostgreSQL.
*/
public final class PostgreSQLSchemaMetaDataLoader implements DialectSchemaMetaDataLoader {
-
+
private static final String BASIC_TABLE_META_DATA_SQL = "SELECT table_name, column_name, ordinal_position, data_type, udt_name, column_default, table_schema"
+ " FROM information_schema.columns WHERE table_schema IN (%s)";
-
+
private static final String TABLE_META_DATA_SQL_WITHOUT_TABLES = BASIC_TABLE_META_DATA_SQL + " ORDER BY ordinal_position";
-
+
private static final String TABLE_META_DATA_SQL_WITH_TABLES = BASIC_TABLE_META_DATA_SQL + " AND table_name IN (%s) ORDER BY ordinal_position";
-
+
+ private static final String FOREIGN_KEY_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 "
+ + "WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema IN (%s)";
+
private static final String PRIMARY_KEY_META_DATA_SQL = "SELECT tc.table_name, kc.column_name, kc.table_schema FROM information_schema.table_constraints tc"
+ " JOIN information_schema.key_column_usage kc ON kc.table_schema = tc.table_schema AND kc.table_name = tc.table_name AND kc.constraint_name = tc.constraint_name"
+ " WHERE tc.constraint_type = 'PRIMARY KEY' AND kc.ordinal_position IS NOT NULL AND kc.table_schema IN (%s)";
-
+
private static final String BASIC_INDEX_META_DATA_SQL = "SELECT tablename, indexname, schemaname FROM pg_indexes WHERE schemaname 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_FILTED_ROLE_TABLE_GRANTS_SQL = LOAD_ALL_ROLE_TABLE_GRANTS_SQL + " WHERE table_name IN (%s)";
-
+
@Override
public Collection<SchemaMetaData> load(final DataSource dataSource, final Collection<String> tables, final String defaultSchemaName) throws SQLException {
Collection<String> schemaNames = loadSchemaNames(dataSource, DatabaseTypeFactory.getInstance(getType()));
Map<String, Multimap<String, IndexMetaData>> schemaIndexMetaDataMap = loadIndexMetaDataMap(dataSource, schemaNames);
Map<String, Multimap<String, ColumnMetaData>> schemaColumnMetaDataMap = loadColumnMetaDataMap(dataSource, tables, schemaNames);
+ Map<String, Multimap<String, ConstraintMetaData>> schemaConstraintMetaDataMap = loadConstraintMetaDataMap(dataSource, schemaNames);
Collection<SchemaMetaData> result = new LinkedList<>();
- for (String each : schemaNames) {
- Multimap<String, IndexMetaData> tableIndexMetaDataMap = schemaIndexMetaDataMap.getOrDefault(each, LinkedHashMultimap.create());
- Multimap<String, ColumnMetaData> tableColumnMetaDataMap = schemaColumnMetaDataMap.getOrDefault(each, LinkedHashMultimap.create());
- result.add(new SchemaMetaData(each, createTableMetaDataList(tableIndexMetaDataMap, tableColumnMetaDataMap)));
+ for (String schemaName : schemaNames) {
Review Comment:
Is `schemaName` more explicit than `each` to reveal the object here?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lhyundeadsoul commented on pull request #20616: add constraint info in PostgreSQLSchemaMetaDataLoader
Posted by GitBox <gi...@apache.org>.
lhyundeadsoul commented on PR #20616:
URL: https://github.com/apache/shardingsphere/pull/20616#issuecomment-1237595283
> Hi @lhyundeadsoul, can you fix ci error?
sorry for the late reply. It has been fixed. Thx.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org