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 b57efb878f9 Fix case sensitive issue when loading schema meta data
with H2 database (#24789)
b57efb878f9 is described below
commit b57efb878f93723d78d811b71d8825b6c4af08e6
Author: Zheng Feng <[email protected]>
AuthorDate: Fri Mar 24 08:22:50 2023 +0800
Fix case sensitive issue when loading schema meta data with H2 database
(#24789)
---
.../loader/metadata/dialect/H2SchemaMetaDataLoader.java | 16 ++++++++--------
.../metadata/dialect/H2SchemaMetaDataLoaderTest.java | 8 ++++----
2 files changed, 12 insertions(+), 12 deletions(-)
diff --git
a/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/metadata/dialect/H2SchemaMetaDataLoader.java
b/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/metadata/dialect/H2SchemaMetaDataLoader.java
index 05714df9cd5..c511d66cf29 100644
---
a/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/metadata/dialect/H2SchemaMetaDataLoader.java
+++
b/infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/metadata/dialect/H2SchemaMetaDataLoader.java
@@ -51,20 +51,20 @@ public final class H2SchemaMetaDataLoader implements
DialectSchemaMetaDataLoader
private static final String TABLE_META_DATA_SQL = TABLE_META_DATA_NO_ORDER
+ ORDER_BY_ORDINAL_POSITION;
- private static final String TABLE_META_DATA_SQL_IN_TABLES =
TABLE_META_DATA_NO_ORDER + " AND TABLE_NAME IN (%s)" +
ORDER_BY_ORDINAL_POSITION;
+ private static final String TABLE_META_DATA_SQL_IN_TABLES =
TABLE_META_DATA_NO_ORDER + " AND UPPER(TABLE_NAME) IN (%s)" +
ORDER_BY_ORDINAL_POSITION;
private static final String INDEX_META_DATA_SQL = "SELECT TABLE_CATALOG,
TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES"
- + " WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND TABLE_NAME IN
(%s)";
+ + " 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_META_DATA_SQL_IN_TABLES =
PRIMARY_KEY_META_DATA_SQL + " AND TABLE_NAME IN (%s)";
+ 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 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_IN_TABLES =
GENERATED_INFO_SQL + " AND C.TABLE_NAME IN (%s)";
+ private static final String GENERATED_INFO_SQL_IN_TABLES =
GENERATED_INFO_SQL + " AND UPPER(C.TABLE_NAME) IN (%s)";
@Override
public Collection<SchemaMetaData> load(final DataSource dataSource, final
Collection<String> tables, final String defaultSchemaName) throws SQLException {
@@ -115,7 +115,7 @@ public final class H2SchemaMetaDataLoader implements
DialectSchemaMetaDataLoader
private String getTableMetaDataSQL(final Collection<String> tables) {
return tables.isEmpty() ? TABLE_META_DATA_SQL
- : String.format(TABLE_META_DATA_SQL_IN_TABLES,
tables.stream().map(each -> String.format("'%s'",
each)).collect(Collectors.joining(",")));
+ : String.format(TABLE_META_DATA_SQL_IN_TABLES,
tables.stream().map(each -> String.format("'%s'",
each).toUpperCase()).collect(Collectors.joining(",")));
}
private Map<String, Collection<IndexMetaData>> loadIndexMetaData(final
Connection connection, final Collection<String> tableNames) throws SQLException
{
@@ -138,12 +138,12 @@ public final class H2SchemaMetaDataLoader implements
DialectSchemaMetaDataLoader
}
private String getIndexMetaDataSQL(final Collection<String> tableNames) {
- return String.format(INDEX_META_DATA_SQL, tableNames.stream().map(each
-> String.format("'%s'", each)).collect(Collectors.joining(",")));
+ 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) {
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)).collect(Collectors.joining(",")));
+ : String.format(PRIMARY_KEY_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
{
@@ -164,7 +164,7 @@ public final class H2SchemaMetaDataLoader implements
DialectSchemaMetaDataLoader
private String getGeneratedInfoSQL(final Collection<String> tables) {
return tables.isEmpty() ? GENERATED_INFO_SQL
- : String.format(GENERATED_INFO_SQL_IN_TABLES,
tables.stream().map(each -> String.format("'%s'",
each)).collect(Collectors.joining(",")));
+ : String.format(GENERATED_INFO_SQL_IN_TABLES,
tables.stream().map(each -> String.format("'%s'",
each).toUpperCase()).collect(Collectors.joining(",")));
}
private Map<String, Map<String, Boolean>> loadTableGenerated(final
Connection connection, final Collection<String> tableNames) throws SQLException
{
diff --git
a/infra/common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/metadata/dialect/H2SchemaMetaDataLoaderTest.java
b/infra/common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/metadata/dialect/H2SchemaMetaDataLoaderTest.java
index 20fc2474df9..bf7d855b3b8 100644
---
a/infra/common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/metadata/dialect/H2SchemaMetaDataLoaderTest.java
+++
b/infra/common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/metadata/dialect/H2SchemaMetaDataLoaderTest.java
@@ -54,7 +54,7 @@ public final class H2SchemaMetaDataLoaderTest {
.executeQuery()).thenReturn(resultSet);
ResultSet indexResultSet = mockIndexMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(
- "SELECT TABLE_CATALOG, TABLE_NAME, INDEX_NAME FROM
INFORMATION_SCHEMA.INDEXES WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND
TABLE_NAME IN ('tbl')")
+ "SELECT TABLE_CATALOG, TABLE_NAME, INDEX_NAME FROM
INFORMATION_SCHEMA.INDEXES WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND
UPPER(TABLE_NAME) IN ('TBL')")
.executeQuery())
.thenReturn(indexResultSet);
ResultSet primaryKeys = mockPrimaryKeysMetaDataResultSet();
@@ -74,15 +74,15 @@ public final class H2SchemaMetaDataLoaderTest {
ResultSet resultSet = mockTableMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(
"SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
ORDINAL_POSITION, COALESCE(IS_VISIBLE, FALSE) IS_VISIBLE FROM
INFORMATION_SCHEMA.COLUMNS"
- + " WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND
TABLE_NAME IN ('tbl') ORDER BY ORDINAL_POSITION")
+ + " WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND
UPPER(TABLE_NAME) IN ('TBL') ORDER BY ORDINAL_POSITION")
.executeQuery()).thenReturn(resultSet);
ResultSet indexResultSet = mockIndexMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(
- "SELECT TABLE_CATALOG, TABLE_NAME, INDEX_NAME FROM
INFORMATION_SCHEMA.INDEXES WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND
TABLE_NAME IN ('tbl')")
+ "SELECT TABLE_CATALOG, TABLE_NAME, INDEX_NAME FROM
INFORMATION_SCHEMA.INDEXES WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND
UPPER(TABLE_NAME) IN ('TBL')")
.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 TABLE_NAME IN ('tbl')")
+ "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')")
.executeQuery())
.thenReturn(primaryKeys);
ResultSet generatedInfo = mockGeneratedInfoResultSet();
