This is an automated email from the ASF dual-hosted git repository.
terrymanu 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 a8ebfa6434a Fix H2 primary key and identity metadata loading (#39787)
a8ebfa6434a is described below
commit a8ebfa6434ae9e5b0607c91a0df5388ceb5f86e6
Author: Liang Zhang <[email protected]>
AuthorDate: Thu Sep 10 13:09:20 2026 +0800
Fix H2 primary key and identity metadata loading (#39787)
* Raise anonymous inner class length limit
* Set variable declaration usage distance to 7
* Refactor H2MetaDataLoader
* Fix H2 primary key and identity metadata loading
---
.../h2/metadata/data/loader/H2MetaDataLoader.java | 32 ++++++++++------------
.../metadata/data/loader/H2MetaDataLoaderTest.java | 23 +++++++++-------
2 files changed, 28 insertions(+), 27 deletions(-)
diff --git
a/database/connector/dialect/h2/src/main/java/org/apache/shardingsphere/database/connector/h2/metadata/data/loader/H2MetaDataLoader.java
b/database/connector/dialect/h2/src/main/java/org/apache/shardingsphere/database/connector/h2/metadata/data/loader/H2MetaDataLoader.java
index 289f4f01989..61ea93b436d 100644
---
a/database/connector/dialect/h2/src/main/java/org/apache/shardingsphere/database/connector/h2/metadata/data/loader/H2MetaDataLoader.java
+++
b/database/connector/dialect/h2/src/main/java/org/apache/shardingsphere/database/connector/h2/metadata/data/loader/H2MetaDataLoader.java
@@ -56,25 +56,23 @@ public final class H2MetaDataLoader implements
DialectMetaDataLoader {
private static final String INDEX_META_DATA_SQL = "SELECT TABLE_CATALOG,
TABLE_NAME, INDEX_NAME, INDEX_TYPE_NAME FROM INFORMATION_SCHEMA.INDEXES"
+ " WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND UPPER(TABLE_NAME)
IN (%s)";
- private static final String PRIMARY_KEY_META_DATA_SQL = "SELECT
TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_CATALOG=?
AND TABLE_SCHEMA=?"
- + " AND INDEX_TYPE_NAME = 'PRIMARY KEY'";
+ private static final String PRIMARY_KEY_COLUMN_META_DATA_SQL = "SELECT
KCU.TABLE_NAME, KCU.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU"
+ + " USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
WHERE KCU.TABLE_CATALOG=? AND KCU.TABLE_SCHEMA=? AND
TC.CONSTRAINT_TYPE='PRIMARY KEY'";
- private static final String PRIMARY_KEY_META_DATA_SQL_IN_TABLES =
PRIMARY_KEY_META_DATA_SQL + " AND UPPER(TABLE_NAME) IN (%s)";
+ private static final String PRIMARY_KEY_COLUMN_META_DATA_SQL_IN_TABLES =
PRIMARY_KEY_COLUMN_META_DATA_SQL + " AND UPPER(KCU.TABLE_NAME) IN (%s)";
- private static final String GENERATED_INFO_SQL = "SELECT C.TABLE_NAME
TABLE_NAME, C.COLUMN_NAME COLUMN_NAME, COALESCE(I.IS_GENERATED, FALSE)
IS_GENERATED FROM INFORMATION_SCHEMA.COLUMNS C"
- + " RIGHT JOIN INFORMATION_SCHEMA.INDEXES I ON
C.TABLE_NAME=I.TABLE_NAME WHERE C.TABLE_CATALOG=? AND C.TABLE_SCHEMA=?";
+ private static final String GENERATED_INFO_SQL = "SELECT TABLE_NAME,
COLUMN_NAME, IS_IDENTITY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG=?
AND TABLE_SCHEMA=?";
- private static final String GENERATED_INFO_SQL_IN_TABLES =
GENERATED_INFO_SQL + " AND UPPER(C.TABLE_NAME) IN (%s)";
+ private static final String GENERATED_INFO_SQL_IN_TABLES =
GENERATED_INFO_SQL + " AND UPPER(TABLE_NAME) IN (%s)";
- private static final String VIEW_META_DATA_SQL = "SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES"
- + " WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND TABLE_TYPE='VIEW'
AND UPPER(TABLE_NAME) IN (%s)";
+ private static final String VIEW_META_DATA_SQL = "SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND
TABLE_TYPE='VIEW' AND UPPER(TABLE_NAME) IN (%s)";
@Override
public Collection<SchemaMetaData> load(final MetaDataLoaderMaterial
material) throws SQLException {
Collection<TableMetaData> tableMetaDataList = new LinkedList<>();
try (Connection connection = material.getDataSource().getConnection())
{
Collection<String> tables = material.getActualTableNames();
- Map<String, Collection<ColumnMetaData>> columnMetaDataMap =
loadColumnMetaDataMap(connection, tables, loadTablePrimaryKeys(connection,
tables), loadTableGenerated(connection, tables));
+ Map<String, Collection<ColumnMetaData>> columnMetaDataMap =
loadColumnMetaDataMap(connection, tables,
loadTablePrimaryKeyColumns(connection, tables), loadTableGenerated(connection,
tables));
Collection<String> viewNames = columnMetaDataMap.isEmpty() ?
Collections.emptySet() : loadViewNames(connection, columnMetaDataMap.keySet());
Map<String, Collection<IndexMetaData>> indexMetaDataMap =
columnMetaDataMap.isEmpty() ? Collections.emptyMap() :
loadIndexMetaData(connection, columnMetaDataMap.keySet());
for (Entry<String, Collection<ColumnMetaData>> entry :
columnMetaDataMap.entrySet()) {
@@ -169,22 +167,22 @@ public final class H2MetaDataLoader implements
DialectMetaDataLoader {
return String.format(INDEX_META_DATA_SQL, tableNames.stream().map(each
-> String.format("'%s'", each).toUpperCase()).collect(Collectors.joining(",")));
}
- private String getPrimaryKeyMetaDataSQL(final Collection<String> tables) {
+ private String getPrimaryKeyColumnMetaDataSQL(final Collection<String>
tables) {
return tables.isEmpty()
- ? PRIMARY_KEY_META_DATA_SQL
- : String.format(PRIMARY_KEY_META_DATA_SQL_IN_TABLES,
tables.stream().map(each -> String.format("'%s'",
each).toUpperCase()).collect(Collectors.joining(",")));
+ ? PRIMARY_KEY_COLUMN_META_DATA_SQL
+ : String.format(PRIMARY_KEY_COLUMN_META_DATA_SQL_IN_TABLES,
tables.stream().map(each -> String.format("'%s'",
each).toUpperCase()).collect(Collectors.joining(",")));
}
- private Map<String, Collection<String>> loadTablePrimaryKeys(final
Connection connection, final Collection<String> tableNames) throws SQLException
{
+ private Map<String, Collection<String>> loadTablePrimaryKeyColumns(final
Connection connection, final Collection<String> tableNames) throws SQLException
{
Map<String, Collection<String>> result = new HashMap<>();
- try (PreparedStatement preparedStatement =
connection.prepareStatement(getPrimaryKeyMetaDataSQL(tableNames))) {
+ try (PreparedStatement preparedStatement =
connection.prepareStatement(getPrimaryKeyColumnMetaDataSQL(tableNames))) {
preparedStatement.setString(1, connection.getCatalog());
preparedStatement.setString(2, "PUBLIC");
try (ResultSet resultSet = preparedStatement.executeQuery()) {
while (resultSet.next()) {
- String indexName = resultSet.getString("INDEX_NAME");
+ String columnName = resultSet.getString("COLUMN_NAME");
String tableName = resultSet.getString("TABLE_NAME");
- result.computeIfAbsent(tableName, key -> new
LinkedList<>()).add(indexName);
+ result.computeIfAbsent(tableName, key -> new
LinkedList<>()).add(columnName);
}
}
}
@@ -206,7 +204,7 @@ public final class H2MetaDataLoader implements
DialectMetaDataLoader {
while (resultSet.next()) {
String columnName = resultSet.getString("COLUMN_NAME");
String tableName = resultSet.getString("TABLE_NAME");
- boolean generated = resultSet.getBoolean("IS_GENERATED");
+ boolean generated =
"YES".equals(resultSet.getString("IS_IDENTITY"));
result.computeIfAbsent(tableName, key -> new
HashMap<>()).put(columnName, generated);
}
}
diff --git
a/database/connector/dialect/h2/src/test/java/org/apache/shardingsphere/database/connector/h2/metadata/data/loader/H2MetaDataLoaderTest.java
b/database/connector/dialect/h2/src/test/java/org/apache/shardingsphere/database/connector/h2/metadata/data/loader/H2MetaDataLoaderTest.java
index 427b3f47380..fa932b99564 100644
---
a/database/connector/dialect/h2/src/test/java/org/apache/shardingsphere/database/connector/h2/metadata/data/loader/H2MetaDataLoaderTest.java
+++
b/database/connector/dialect/h2/src/test/java/org/apache/shardingsphere/database/connector/h2/metadata/data/loader/H2MetaDataLoaderTest.java
@@ -68,11 +68,13 @@ class H2MetaDataLoaderTest {
.executeQuery()).thenReturn(indexResultSet);
ResultSet primaryKeys = mockPrimaryKeysMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(
- "SELECT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES
WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND INDEX_TYPE_NAME = 'PRIMARY
KEY'").executeQuery()).thenReturn(primaryKeys);
+ "SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU"
+ + " USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA,
CONSTRAINT_NAME)"
+ + " WHERE KCU.TABLE_CATALOG=? AND KCU.TABLE_SCHEMA=?
AND TC.CONSTRAINT_TYPE='PRIMARY KEY'")
+ .executeQuery()).thenReturn(primaryKeys);
ResultSet generatedInfo = mockGeneratedInfoResultSet();
when(dataSource.getConnection().prepareStatement(
- "SELECT C.TABLE_NAME TABLE_NAME, C.COLUMN_NAME COLUMN_NAME,
COALESCE(I.IS_GENERATED, FALSE) IS_GENERATED FROM INFORMATION_SCHEMA.COLUMNS C
RIGHT JOIN"
- + " INFORMATION_SCHEMA.INDEXES I ON
C.TABLE_NAME=I.TABLE_NAME WHERE C.TABLE_CATALOG=? AND C.TABLE_SCHEMA=?")
+ "SELECT TABLE_NAME, COLUMN_NAME, IS_IDENTITY FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=?")
.executeQuery()).thenReturn(generatedInfo);
DataTypeRegistry.load(dataSource, "H2");
assertTableMetaDataMap(getDialectTableMetaDataLoader().load(new
MetaDataLoaderMaterial(Collections.emptyList(), "foo_ds", dataSource,
databaseType, "sharding_db")));
@@ -93,12 +95,13 @@ class H2MetaDataLoaderTest {
.executeQuery()).thenReturn(indexResultSet);
ResultSet primaryKeys = mockPrimaryKeysMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(
- "SELECT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES
WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND INDEX_TYPE_NAME = 'PRIMARY KEY'
AND UPPER(TABLE_NAME) IN ('TBL')")
+ "SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU"
+ + " USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA,
CONSTRAINT_NAME)"
+ + " WHERE KCU.TABLE_CATALOG=? AND KCU.TABLE_SCHEMA=?
AND TC.CONSTRAINT_TYPE='PRIMARY KEY' AND UPPER(KCU.TABLE_NAME) IN ('TBL')")
.executeQuery()).thenReturn(primaryKeys);
ResultSet generatedInfo = mockGeneratedInfoResultSet();
when(dataSource.getConnection().prepareStatement(
- "SELECT C.TABLE_NAME TABLE_NAME, C.COLUMN_NAME COLUMN_NAME,
COALESCE(I.IS_GENERATED, FALSE) IS_GENERATED FROM INFORMATION_SCHEMA.COLUMNS C"
- + " RIGHT JOIN INFORMATION_SCHEMA.INDEXES I ON
C.TABLE_NAME=I.TABLE_NAME WHERE C.TABLE_CATALOG=? AND C.TABLE_SCHEMA=? AND
C.TABLE_NAME IN ('tbl')")
+ "SELECT TABLE_NAME, COLUMN_NAME, IS_IDENTITY FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND
UPPER(TABLE_NAME) IN ('TBL')")
.executeQuery()).thenReturn(generatedInfo);
DataTypeRegistry.load(dataSource, "H2");
assertTableMetaDataMap(getDialectTableMetaDataLoader().load(new
MetaDataLoaderMaterial(Collections.singletonList("tbl"), "foo_ds", dataSource,
databaseType, "sharding_db")));
@@ -180,7 +183,7 @@ class H2MetaDataLoaderTest {
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("COLUMN_NAME")).thenReturn("id");
return result;
}
@@ -188,8 +191,8 @@ class H2MetaDataLoaderTest {
ResultSet result = mock(ResultSet.class);
when(result.next()).thenReturn(true, true, false);
when(result.getString("TABLE_NAME")).thenReturn("tbl");
- when(result.getString("COLUMN_NAME")).thenReturn("id");
- when(result.getBoolean("IS_GENERATED")).thenReturn(false);
+ when(result.getString("COLUMN_NAME")).thenReturn("id", "name");
+ when(result.getString("IS_IDENTITY")).thenReturn("YES", "NO");
return result;
}
@@ -213,7 +216,7 @@ class H2MetaDataLoaderTest {
TableMetaData actualTableMetaData =
schemaMetaDataList.iterator().next().getTables().iterator().next();
assertThat(actualTableMetaData.getColumns().size(), is(2));
Iterator<ColumnMetaData> columnsIterator =
actualTableMetaData.getColumns().iterator();
- assertColumnMetaData(columnsIterator.next(), new ColumnMetaData("id",
Types.INTEGER, true, false, false, true, false, false));
+ assertColumnMetaData(columnsIterator.next(), new ColumnMetaData("id",
Types.INTEGER, true, true, false, true, false, false));
assertColumnMetaData(columnsIterator.next(), new
ColumnMetaData("name", Types.VARCHAR, false, false, false, false, false, true));
assertThat(actualTableMetaData.getIndexes().size(), is(1));
Iterator<IndexMetaData> indexesIterator =
actualTableMetaData.getIndexes().iterator();
