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 ebf00fc95d0 Fix Hive and Presto metadata loading (#39835)
ebf00fc95d0 is described below
commit ebf00fc95d0e2cc6dee810a31196a9b626856a9c
Author: Liang Zhang <[email protected]>
AuthorDate: Sun Sep 13 22:14:49 2026 +0800
Fix Hive and Presto metadata loading (#39835)
* Enforce code handoff review output
* Fix Hive and Presto metadata loading
---
.../data/loader/type/ColumnMetaDataLoader.java | 3 +-
.../metadata/data/loader/HiveMetaDataLoader.java | 46 ++++++++++--
.../data/loader/HiveMetaDataLoaderTest.java | 76 +++++++++++++-------
.../metadata/data/loader/PrestoMetaDataLoader.java | 50 +++++++++++--
.../data/loader/PrestoMetaDataLoaderTest.java | 82 ++++++++++++++--------
5 files changed, 191 insertions(+), 66 deletions(-)
diff --git
a/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/data/loader/type/ColumnMetaDataLoader.java
b/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/data/loader/type/ColumnMetaDataLoader.java
index b54b004482e..7f97a2d8464 100644
---
a/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/data/loader/type/ColumnMetaDataLoader.java
+++
b/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/data/loader/type/ColumnMetaDataLoader.java
@@ -74,7 +74,8 @@ public final class ColumnMetaDataLoader {
String tableName = resultSet.getString(TABLE_NAME);
if (Objects.equals(tableNamePattern, tableName)) {
String columnName = resultSet.getString(COLUMN_NAME);
- columnTypes.add(resultSet.getInt(DATA_TYPE));
+ int columnType = resultSet.getInt(DATA_TYPE);
+ columnTypes.add(columnType);
primaryKeyFlags.add(primaryKeys.contains(columnName));
nullableFlags.add("YES".equals(resultSet.getString(IS_NULLABLE)));
columnNames.add(columnName);
diff --git
a/database/connector/dialect/hive/src/main/java/org/apache/shardingsphere/database/connector/hive/metadata/data/loader/HiveMetaDataLoader.java
b/database/connector/dialect/hive/src/main/java/org/apache/shardingsphere/database/connector/hive/metadata/data/loader/HiveMetaDataLoader.java
index faccb2ef238..68798ea87ab 100644
---
a/database/connector/dialect/hive/src/main/java/org/apache/shardingsphere/database/connector/hive/metadata/data/loader/HiveMetaDataLoader.java
+++
b/database/connector/dialect/hive/src/main/java/org/apache/shardingsphere/database/connector/hive/metadata/data/loader/HiveMetaDataLoader.java
@@ -24,6 +24,7 @@ import
org.apache.shardingsphere.database.connector.core.metadata.data.model.Col
import
org.apache.shardingsphere.database.connector.core.metadata.data.model.SchemaMetaData;
import
org.apache.shardingsphere.database.connector.core.metadata.data.model.TableMetaData;
import
org.apache.shardingsphere.database.connector.core.metadata.database.datatype.DataTypeRegistry;
+import
org.apache.shardingsphere.database.connector.core.metadata.database.enums.TableType;
import java.sql.Connection;
import java.sql.PreparedStatement;
@@ -35,6 +36,7 @@ import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedList;
+import java.util.Locale;
import java.util.Map;
import java.util.Map.Entry;
import java.util.stream.Collectors;
@@ -47,14 +49,20 @@ import java.util.stream.Collectors;
*/
public final class HiveMetaDataLoader implements DialectMetaDataLoader {
+ 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> tableMetaData = new LinkedList<>();
if (loadInformationSchemaFlag(material)) {
try (Connection connection =
material.getDataSource().getConnection()) {
- Map<String, Collection<ColumnMetaData>> columnMetaDataMap =
loadColumnMetaDataMap(connection, material.getActualTableNames());
+ String schemaName = connection.getSchema();
+ Map<String, Collection<ColumnMetaData>> columnMetaDataMap =
loadColumnMetaDataMap(connection, schemaName, material.getActualTableNames());
+ Collection<String> viewNames = columnMetaDataMap.isEmpty() ?
Collections.emptySet() : loadViewNames(connection, schemaName,
columnMetaDataMap.keySet());
for (Entry<String, Collection<ColumnMetaData>> entry :
columnMetaDataMap.entrySet()) {
- tableMetaData.add(new TableMetaData(entry.getKey(),
entry.getValue(), Collections.emptyList(), Collections.emptyList()));
+ tableMetaData.add(new TableMetaData(
+ entry.getKey(), entry.getValue(),
Collections.emptyList(), Collections.emptyList(),
viewNames.contains(entry.getKey()) ? TableType.VIEW : TableType.TABLE));
}
}
return Collections.singleton(new
SchemaMetaData(material.getDefaultSchemaName(), tableMetaData));
@@ -76,10 +84,11 @@ public final class HiveMetaDataLoader implements
DialectMetaDataLoader {
}
@SuppressWarnings("CollectionWithoutInitialCapacity")
- private Map<String, Collection<ColumnMetaData>>
loadColumnMetaDataMap(final Connection connection, final Collection<String>
tables) throws SQLException {
+ private Map<String, Collection<ColumnMetaData>>
loadColumnMetaDataMap(final Connection connection, final String schemaName,
final Collection<String> tables) throws SQLException {
Map<String, Collection<ColumnMetaData>> result = new HashMap<>();
try (PreparedStatement preparedStatement =
connection.prepareStatement(getTableMetaDataSQL(tables))) {
preparedStatement.setString(1, "default");
+ preparedStatement.setString(2, schemaName);
try (ResultSet resultSet = preparedStatement.executeQuery()) {
while (resultSet.next()) {
String tableName = resultSet.getString("TABLE_NAME");
@@ -96,18 +105,43 @@ public final class HiveMetaDataLoader implements
DialectMetaDataLoader {
private String getTableMetaDataSQL(final Collection<String> tables) {
if (tables.isEmpty()) {
- return "SELECT
TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG=? ORDER BY
ORDINAL_POSITION";
+ return "SELECT
TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS"
+ + " WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? ORDER BY
ORDINAL_POSITION";
}
String tableNames = tables.stream().map(each -> String.format("'%s'",
each).toUpperCase()).collect(Collectors.joining(","));
return String.format("SELECT
TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS"
- + " WHERE TABLE_CATALOG=? AND UPPER(TABLE_NAME) IN (%s) ORDER
BY ORDINAL_POSITION", tableNames);
+ + " WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND
UPPER(TABLE_NAME) IN (%s) ORDER BY ORDINAL_POSITION", tableNames);
}
private ColumnMetaData loadColumnMetaData(final ResultSet resultSet)
throws SQLException {
String columnName = resultSet.getString("COLUMN_NAME");
String dataType = resultSet.getString("DATA_TYPE");
boolean isNullable = "YES".equals(resultSet.getString("IS_NULLABLE"));
- return new ColumnMetaData(columnName,
DataTypeRegistry.getDataType(getDatabaseType(), dataType).orElse(Types.OTHER),
Boolean.FALSE, Boolean.FALSE, false, Boolean.TRUE, false, isNullable);
+ return new ColumnMetaData(columnName,
DataTypeRegistry.getDataType(getDatabaseType(), dataType).orElse(Types.OTHER),
Boolean.FALSE, Boolean.FALSE,
+ isStringDataType(dataType), Boolean.TRUE, false, isNullable);
+ }
+
+ private boolean isStringDataType(final String dataType) {
+ String normalizedDataType = dataType.toLowerCase(Locale.ENGLISH);
+ return "string".equals(normalizedDataType) ||
normalizedDataType.startsWith("varchar") ||
normalizedDataType.startsWith("char");
+ }
+
+ private Collection<String> loadViewNames(final Connection connection,
final String schemaName, final Collection<String> tableNames) throws
SQLException {
+ Collection<String> result = new LinkedList<>();
+ try (PreparedStatement preparedStatement =
connection.prepareStatement(getViewMetaDataSQL(tableNames))) {
+ preparedStatement.setString(1, "default");
+ preparedStatement.setString(2, schemaName);
+ try (ResultSet resultSet = preparedStatement.executeQuery()) {
+ while (resultSet.next()) {
+ result.add(resultSet.getString("TABLE_NAME"));
+ }
+ }
+ }
+ return result;
+ }
+
+ private String getViewMetaDataSQL(final Collection<String> tableNames) {
+ return String.format(VIEW_META_DATA_SQL, tableNames.stream().map(each
-> String.format("'%s'",
each).toUpperCase(Locale.ENGLISH)).collect(Collectors.joining(",")));
}
@Override
diff --git
a/database/connector/dialect/hive/src/test/java/org/apache/shardingsphere/database/connector/hive/metadata/data/loader/HiveMetaDataLoaderTest.java
b/database/connector/dialect/hive/src/test/java/org/apache/shardingsphere/database/connector/hive/metadata/data/loader/HiveMetaDataLoaderTest.java
index cdf349d8508..fbcd3e2a779 100644
---
a/database/connector/dialect/hive/src/test/java/org/apache/shardingsphere/database/connector/hive/metadata/data/loader/HiveMetaDataLoaderTest.java
+++
b/database/connector/dialect/hive/src/test/java/org/apache/shardingsphere/database/connector/hive/metadata/data/loader/HiveMetaDataLoaderTest.java
@@ -26,6 +26,7 @@ import
org.apache.shardingsphere.database.connector.core.metadata.data.model.Col
import
org.apache.shardingsphere.database.connector.core.metadata.data.model.SchemaMetaData;
import
org.apache.shardingsphere.database.connector.core.metadata.data.model.TableMetaData;
import
org.apache.shardingsphere.database.connector.core.metadata.database.datatype.DataTypeRegistry;
+import
org.apache.shardingsphere.database.connector.core.metadata.database.enums.TableType;
import
org.apache.shardingsphere.database.connector.core.spi.DatabaseTypedSPILoader;
import org.apache.shardingsphere.database.connector.core.type.DatabaseType;
import org.apache.shardingsphere.infra.spi.type.typed.TypedSPILoader;
@@ -56,6 +57,7 @@ import static org.hamcrest.Matchers.is;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.verify;
import static org.mockito.Mockito.when;
@ExtendWith(AutoMockExtension.class)
@@ -90,26 +92,39 @@ class HiveMetaDataLoaderTest {
@Test
void assertLoadWithInformationSchemaAndFilter() throws SQLException {
- DataSource dataSource =
mockDataSource(mockInformationSchemaConnection(true),
mockColumnMetaDataConnectionWithInformationSchemaAndFilter());
- Collection<SchemaMetaData> filteredSchemas = loader.load(new
MetaDataLoaderMaterial(Collections.singleton("target_table"), "ds_0",
dataSource, databaseType, "def_schema"));
- SchemaMetaData filteredSchema = filteredSchemas.iterator().next();
- TableMetaData filteredTable =
filteredSchema.getTables().iterator().next();
- Iterator<ColumnMetaData> filteredColumns =
filteredTable.getColumns().iterator();
- ColumnMetaData firstColumn = filteredColumns.next();
- assertThat(firstColumn.getName(), is("c1"));
- assertThat(firstColumn.getDataType(), is(Types.VARCHAR));
- assertTrue(firstColumn.isNullable());
- ColumnMetaData secondColumn = filteredColumns.next();
- assertThat(secondColumn.getDataType(), is(Types.OTHER));
- assertFalse(secondColumn.isNullable());
+ PreparedStatement columnMetaDataStatement =
mock(PreparedStatement.class);
+ PreparedStatement viewMetaDataStatement =
mock(PreparedStatement.class);
+ DataSource dataSource =
mockDataSource(mockInformationSchemaConnection(true),
+
mockColumnMetaDataConnectionWithInformationSchemaAndFilter(columnMetaDataStatement,
viewMetaDataStatement));
+ Collection<SchemaMetaData> actualSchemas = loader.load(new
MetaDataLoaderMaterial(Collections.singleton("target_table"), "ds_0",
dataSource, databaseType, "def_schema"));
+ SchemaMetaData actualSchema = actualSchemas.iterator().next();
+ TableMetaData actualTable = actualSchema.getTables().iterator().next();
+ assertThat(actualTable.getType(), is(TableType.VIEW));
+ Iterator<ColumnMetaData> actualColumns =
actualTable.getColumns().iterator();
+ ColumnMetaData actualFirstColumn = actualColumns.next();
+ assertThat(actualFirstColumn.getName(), is("c1"));
+ assertThat(actualFirstColumn.getDataType(), is(Types.VARCHAR));
+ assertTrue(actualFirstColumn.isCaseSensitive());
+ assertTrue(actualFirstColumn.isNullable());
+ ColumnMetaData actualSecondColumn = actualColumns.next();
+ assertThat(actualSecondColumn.getDataType(), is(Types.OTHER));
+ assertFalse(actualSecondColumn.isCaseSensitive());
+ assertFalse(actualSecondColumn.isNullable());
+ verify(columnMetaDataStatement).setString(1, "default");
+ verify(columnMetaDataStatement).setString(2, "foo_schema");
+ verify(viewMetaDataStatement).setString(1, "default");
+ verify(viewMetaDataStatement).setString(2, "foo_schema");
}
@Test
void assertLoadWithInformationSchemaWithoutFilter() throws SQLException {
DataSource dataSource =
mockDataSource(mockInformationSchemaConnection(true),
mockColumnMetaDataConnectionWithInformationSchemaWithoutFilter());
- Collection<SchemaMetaData> fullSchemas = loader.load(new
MetaDataLoaderMaterial(Collections.emptyList(), "ds_1", dataSource,
databaseType, "default_db"));
- SchemaMetaData fullSchema = fullSchemas.iterator().next();
- assertThat(fullSchema.getTables().iterator().next().getName(),
is("full_table"));
+ Collection<SchemaMetaData> actualSchemas = loader.load(new
MetaDataLoaderMaterial(Collections.emptyList(), "ds_1", dataSource,
databaseType, "default_db"));
+ SchemaMetaData actualSchema = actualSchemas.iterator().next();
+ TableMetaData actualTable = actualSchema.getTables().iterator().next();
+ assertThat(actualTable.getName(), is("full_table"));
+ assertThat(actualTable.getType(), is(TableType.TABLE));
+
assertFalse(actualTable.getColumns().iterator().next().isCaseSensitive());
}
@Test
@@ -140,19 +155,25 @@ class HiveMetaDataLoaderTest {
return result;
}
- private Connection
mockColumnMetaDataConnectionWithInformationSchemaAndFilter() throws
SQLException {
+ private Connection
mockColumnMetaDataConnectionWithInformationSchemaAndFilter(final
PreparedStatement columnMetaDataStatement, final PreparedStatement
viewMetaDataStatement) throws SQLException {
ResultSet resultSet = mock(ResultSet.class);
when(resultSet.next()).thenReturn(true, true, false);
when(resultSet.getString("TABLE_NAME")).thenReturn("target_table",
"target_table");
when(resultSet.getString("COLUMN_NAME")).thenReturn("c1", "c2");
when(resultSet.getString("DATA_TYPE")).thenReturn("string", "unknown");
when(resultSet.getString("IS_NULLABLE")).thenReturn("YES", "NO");
- PreparedStatement preparedStatement = mock(PreparedStatement.class);
- when(preparedStatement.executeQuery()).thenReturn(resultSet);
+ when(columnMetaDataStatement.executeQuery()).thenReturn(resultSet);
+ ResultSet viewResultSet = mock(ResultSet.class);
+ when(viewResultSet.next()).thenReturn(true, false);
+ when(viewResultSet.getString("TABLE_NAME")).thenReturn("target_table");
+ when(viewMetaDataStatement.executeQuery()).thenReturn(viewResultSet);
Connection connection = mock(Connection.class);
String sql = "SELECT
TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS "
- + "WHERE TABLE_CATALOG=? AND UPPER(TABLE_NAME) IN
('TARGET_TABLE') ORDER BY ORDINAL_POSITION";
- when(connection.prepareStatement(sql)).thenReturn(preparedStatement);
+ + "WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND
UPPER(TABLE_NAME) IN ('TARGET_TABLE') ORDER BY ORDINAL_POSITION";
+
when(connection.prepareStatement(sql)).thenReturn(columnMetaDataStatement);
+ String viewSql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND TABLE_TYPE='VIEW' AND
UPPER(TABLE_NAME) IN ('TARGET_TABLE')";
+
when(connection.prepareStatement(viewSql)).thenReturn(viewMetaDataStatement);
+ when(connection.getSchema()).thenReturn("foo_schema");
return connection;
}
@@ -163,11 +184,18 @@ class HiveMetaDataLoaderTest {
when(resultSet.getString("COLUMN_NAME")).thenReturn("id");
when(resultSet.getString("DATA_TYPE")).thenReturn("int");
when(resultSet.getString("IS_NULLABLE")).thenReturn("NO");
- PreparedStatement preparedStatement = mock(PreparedStatement.class);
- when(preparedStatement.executeQuery()).thenReturn(resultSet);
+ PreparedStatement columnMetaDataStatement =
mock(PreparedStatement.class);
+ when(columnMetaDataStatement.executeQuery()).thenReturn(resultSet);
+ PreparedStatement viewMetaDataStatement =
mock(PreparedStatement.class);
+ ResultSet viewResultSet = mock(ResultSet.class);
+ when(viewMetaDataStatement.executeQuery()).thenReturn(viewResultSet);
Connection result = mock(Connection.class);
- String sql = "SELECT
TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG=? ORDER BY
ORDINAL_POSITION";
- when(result.prepareStatement(sql)).thenReturn(preparedStatement);
+ String sql = "SELECT
TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS"
+ + " WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? ORDER BY
ORDINAL_POSITION";
+ when(result.prepareStatement(sql)).thenReturn(columnMetaDataStatement);
+ String viewSql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND TABLE_TYPE='VIEW' AND
UPPER(TABLE_NAME) IN ('FULL_TABLE')";
+
when(result.prepareStatement(viewSql)).thenReturn(viewMetaDataStatement);
+ when(result.getSchema()).thenReturn("bar_schema");
return result;
}
}
diff --git
a/database/connector/dialect/presto/src/main/java/org/apache/shardingsphere/database/connector/presto/metadata/data/loader/PrestoMetaDataLoader.java
b/database/connector/dialect/presto/src/main/java/org/apache/shardingsphere/database/connector/presto/metadata/data/loader/PrestoMetaDataLoader.java
index aaa8d8c885d..e4bbe44d942 100644
---
a/database/connector/dialect/presto/src/main/java/org/apache/shardingsphere/database/connector/presto/metadata/data/loader/PrestoMetaDataLoader.java
+++
b/database/connector/dialect/presto/src/main/java/org/apache/shardingsphere/database/connector/presto/metadata/data/loader/PrestoMetaDataLoader.java
@@ -23,6 +23,7 @@ import
org.apache.shardingsphere.database.connector.core.metadata.data.model.Col
import
org.apache.shardingsphere.database.connector.core.metadata.data.model.SchemaMetaData;
import
org.apache.shardingsphere.database.connector.core.metadata.data.model.TableMetaData;
import
org.apache.shardingsphere.database.connector.core.metadata.database.datatype.DataTypeRegistry;
+import
org.apache.shardingsphere.database.connector.core.metadata.database.enums.TableType;
import java.sql.Connection;
import java.sql.PreparedStatement;
@@ -33,6 +34,7 @@ import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedList;
+import java.util.Locale;
import java.util.Map;
import java.util.Map.Entry;
import java.util.stream.Collectors;
@@ -45,23 +47,32 @@ import java.util.stream.Collectors;
*/
public final class PrestoMetaDataLoader implements DialectMetaDataLoader {
+ 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())
{
- Map<String, Collection<ColumnMetaData>> columnMetaDataMap =
loadColumnMetaDataMap(connection, material.getActualTableNames());
+ String catalogName = connection.getCatalog();
+ String schemaName = connection.getSchema();
+ Map<String, Collection<ColumnMetaData>> columnMetaDataMap =
loadColumnMetaDataMap(connection, catalogName, schemaName,
material.getActualTableNames());
+ Collection<String> viewNames = columnMetaDataMap.isEmpty() ?
Collections.emptySet() : loadViewNames(connection, catalogName, schemaName,
columnMetaDataMap.keySet());
for (Entry<String, Collection<ColumnMetaData>> entry :
columnMetaDataMap.entrySet()) {
- tableMetaDataList.add(new TableMetaData(entry.getKey(),
entry.getValue(), Collections.emptyList(), Collections.emptyList()));
+ tableMetaDataList.add(new TableMetaData(
+ entry.getKey(), entry.getValue(),
Collections.emptyList(), Collections.emptyList(),
viewNames.contains(entry.getKey()) ? TableType.VIEW : TableType.TABLE));
}
}
return Collections.singleton(new
SchemaMetaData(material.getDefaultSchemaName(), tableMetaDataList));
}
@SuppressWarnings("CollectionWithoutInitialCapacity")
- private Map<String, Collection<ColumnMetaData>>
loadColumnMetaDataMap(final Connection connection, final Collection<String>
tables) throws SQLException {
+ private Map<String, Collection<ColumnMetaData>>
loadColumnMetaDataMap(final Connection connection, final String catalogName,
final String schemaName,
+
final Collection<String> tables) throws SQLException {
Map<String, Collection<ColumnMetaData>> result = new HashMap<>();
try (PreparedStatement preparedStatement =
connection.prepareStatement(getTableMetaDataSQL(tables))) {
- preparedStatement.setString(1, connection.getCatalog());
+ preparedStatement.setString(1, catalogName);
+ preparedStatement.setString(2, schemaName);
try (ResultSet resultSet = preparedStatement.executeQuery()) {
while (resultSet.next()) {
String tableName = resultSet.getString("TABLE_NAME");
@@ -78,18 +89,43 @@ public final class PrestoMetaDataLoader implements
DialectMetaDataLoader {
private String getTableMetaDataSQL(final Collection<String> tables) {
if (tables.isEmpty()) {
- return "SELECT
TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG=? ORDER BY
ORDINAL_POSITION";
+ return "SELECT
TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS"
+ + " WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? ORDER BY
ORDINAL_POSITION";
}
String tableNames = tables.stream().map(each -> String.format("'%s'",
each).toUpperCase()).collect(Collectors.joining(","));
return String.format("SELECT
TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS"
- + " WHERE TABLE_CATALOG=? AND UPPER(TABLE_NAME) IN (%s) ORDER
BY ORDINAL_POSITION", tableNames);
+ + " WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND
UPPER(TABLE_NAME) IN (%s) ORDER BY ORDINAL_POSITION", tableNames);
}
private ColumnMetaData loadColumnMetaData(final ResultSet resultSet)
throws SQLException {
String columnName = resultSet.getString("COLUMN_NAME");
String dataType = resultSet.getString("DATA_TYPE");
boolean isNullable = "YES".equals(resultSet.getString("IS_NULLABLE"));
- return new ColumnMetaData(columnName,
DataTypeRegistry.getDataType(getDatabaseType(), dataType).orElse(Types.OTHER),
Boolean.FALSE, Boolean.FALSE, false, Boolean.TRUE, false, isNullable);
+ return new ColumnMetaData(columnName,
DataTypeRegistry.getDataType(getDatabaseType(), dataType).orElse(Types.OTHER),
Boolean.FALSE, Boolean.FALSE,
+ isStringDataType(dataType), Boolean.TRUE, false, isNullable);
+ }
+
+ private boolean isStringDataType(final String dataType) {
+ String normalizedDataType = dataType.toLowerCase(Locale.ENGLISH);
+ return normalizedDataType.startsWith("varchar") ||
normalizedDataType.startsWith("char");
+ }
+
+ private Collection<String> loadViewNames(final Connection connection,
final String catalogName, final String schemaName, final Collection<String>
tableNames) throws SQLException {
+ Collection<String> result = new LinkedList<>();
+ try (PreparedStatement preparedStatement =
connection.prepareStatement(getViewMetaDataSQL(tableNames))) {
+ preparedStatement.setString(1, catalogName);
+ preparedStatement.setString(2, schemaName);
+ try (ResultSet resultSet = preparedStatement.executeQuery()) {
+ while (resultSet.next()) {
+ result.add(resultSet.getString("TABLE_NAME"));
+ }
+ }
+ }
+ return result;
+ }
+
+ private String getViewMetaDataSQL(final Collection<String> tableNames) {
+ return String.format(VIEW_META_DATA_SQL, tableNames.stream().map(each
-> String.format("'%s'",
each).toUpperCase(Locale.ENGLISH)).collect(Collectors.joining(",")));
}
@Override
diff --git
a/database/connector/dialect/presto/src/test/java/org/apache/shardingsphere/database/connector/presto/metadata/data/loader/PrestoMetaDataLoaderTest.java
b/database/connector/dialect/presto/src/test/java/org/apache/shardingsphere/database/connector/presto/metadata/data/loader/PrestoMetaDataLoaderTest.java
index 4ed0b0da152..86cebf8b746 100644
---
a/database/connector/dialect/presto/src/test/java/org/apache/shardingsphere/database/connector/presto/metadata/data/loader/PrestoMetaDataLoaderTest.java
+++
b/database/connector/dialect/presto/src/test/java/org/apache/shardingsphere/database/connector/presto/metadata/data/loader/PrestoMetaDataLoaderTest.java
@@ -23,6 +23,7 @@ import
org.apache.shardingsphere.database.connector.core.metadata.data.model.Col
import
org.apache.shardingsphere.database.connector.core.metadata.data.model.SchemaMetaData;
import
org.apache.shardingsphere.database.connector.core.metadata.data.model.TableMetaData;
import
org.apache.shardingsphere.database.connector.core.metadata.database.datatype.DataTypeRegistry;
+import
org.apache.shardingsphere.database.connector.core.metadata.database.enums.TableType;
import
org.apache.shardingsphere.database.connector.core.spi.DatabaseTypedSPILoader;
import org.apache.shardingsphere.database.connector.core.type.DatabaseType;
import org.apache.shardingsphere.infra.spi.type.typed.TypedSPILoader;
@@ -47,6 +48,7 @@ import static org.hamcrest.Matchers.is;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.verify;
import static org.mockito.Mockito.when;
@ExtendWith(AutoMockExtension.class)
@@ -59,25 +61,34 @@ class PrestoMetaDataLoaderTest {
@Test
void assertLoadWithoutTableFilter() throws SQLException {
- Connection connectionWithoutTables = mockConnectionWithoutTables();
+ PreparedStatement columnMetaDataStatement =
mock(PreparedStatement.class);
+ PreparedStatement viewMetaDataStatement =
mock(PreparedStatement.class);
+ Connection connectionWithoutTables =
mockConnectionWithoutTables(columnMetaDataStatement, viewMetaDataStatement);
DataSource dataSource = mock(DataSource.class);
when(dataSource.getConnection()).thenReturn(connectionWithoutTables);
when(DataTypeRegistry.getDataType("Presto",
"int")).thenReturn(Optional.of(Types.INTEGER));
when(DataTypeRegistry.getDataType("Presto",
"varchar")).thenReturn(Optional.empty());
Collection<SchemaMetaData> actualWithoutTables = loader.load(new
MetaDataLoaderMaterial(Collections.emptyList(), "ds_0", dataSource,
databaseType, "def_schema"));
- SchemaMetaData schemaMetaDataWithoutTables =
actualWithoutTables.iterator().next();
- assertThat(schemaMetaDataWithoutTables.getName(), is("def_schema"));
- TableMetaData tableMetaDataWithoutTables =
schemaMetaDataWithoutTables.getTables().iterator().next();
- assertThat(tableMetaDataWithoutTables.getName(), is("tbl_one"));
- Iterator<ColumnMetaData> columnIterator =
tableMetaDataWithoutTables.getColumns().iterator();
- ColumnMetaData firstColumn = columnIterator.next();
- assertThat(firstColumn.getName(), is("id"));
- assertThat(firstColumn.getDataType(), is(Types.INTEGER));
- assertTrue(firstColumn.isNullable());
- ColumnMetaData secondColumn = columnIterator.next();
- assertThat(secondColumn.getName(), is("name"));
- assertThat(secondColumn.getDataType(), is(Types.OTHER));
- assertFalse(secondColumn.isNullable());
+ SchemaMetaData actualSchema = actualWithoutTables.iterator().next();
+ assertThat(actualSchema.getName(), is("def_schema"));
+ TableMetaData actualTable = actualSchema.getTables().iterator().next();
+ assertThat(actualTable.getName(), is("tbl_one"));
+ assertThat(actualTable.getType(), is(TableType.VIEW));
+ Iterator<ColumnMetaData> actualColumns =
actualTable.getColumns().iterator();
+ ColumnMetaData actualFirstColumn = actualColumns.next();
+ assertThat(actualFirstColumn.getName(), is("id"));
+ assertThat(actualFirstColumn.getDataType(), is(Types.INTEGER));
+ assertFalse(actualFirstColumn.isCaseSensitive());
+ assertTrue(actualFirstColumn.isNullable());
+ ColumnMetaData actualSecondColumn = actualColumns.next();
+ assertThat(actualSecondColumn.getName(), is("name"));
+ assertThat(actualSecondColumn.getDataType(), is(Types.OTHER));
+ assertTrue(actualSecondColumn.isCaseSensitive());
+ assertFalse(actualSecondColumn.isNullable());
+ verify(columnMetaDataStatement).setString(1, "catalog_one");
+ verify(columnMetaDataStatement).setString(2, "foo_schema");
+ verify(viewMetaDataStatement).setString(1, "catalog_one");
+ verify(viewMetaDataStatement).setString(2, "foo_schema");
}
@Test
@@ -87,21 +98,30 @@ class PrestoMetaDataLoaderTest {
when(dataSource.getConnection()).thenReturn(connectionWithTables);
when(DataTypeRegistry.getDataType("Presto",
"json")).thenReturn(Optional.of(Types.JAVA_OBJECT));
Collection<SchemaMetaData> actualWithTables = loader.load(new
MetaDataLoaderMaterial(Collections.singletonList("target_table"), "ds_0",
dataSource, databaseType, "def_schema"));
- SchemaMetaData schemaMetaDataWithTables =
actualWithTables.iterator().next();
- TableMetaData tableMetaDataWithTables =
schemaMetaDataWithTables.getTables().iterator().next();
- ColumnMetaData payloadColumn =
tableMetaDataWithTables.getColumns().iterator().next();
- assertThat(tableMetaDataWithTables.getName(), is("target_table"));
- assertThat(payloadColumn.getDataType(), is(Types.JAVA_OBJECT));
+ SchemaMetaData actualSchema = actualWithTables.iterator().next();
+ TableMetaData actualTable = actualSchema.getTables().iterator().next();
+ ColumnMetaData actualColumn =
actualTable.getColumns().iterator().next();
+ assertThat(actualTable.getName(), is("target_table"));
+ assertThat(actualTable.getType(), is(TableType.TABLE));
+ assertThat(actualColumn.getDataType(), is(Types.JAVA_OBJECT));
+ assertFalse(actualColumn.isCaseSensitive());
}
- private Connection mockConnectionWithoutTables() throws SQLException {
+ private Connection mockConnectionWithoutTables(final PreparedStatement
columnMetaDataStatement, final PreparedStatement viewMetaDataStatement) throws
SQLException {
Connection result = mock(Connection.class);
- PreparedStatement preparedStatement = mock(PreparedStatement.class);
ResultSet resultSet = mockResultSetWithoutTables();
- when(preparedStatement.executeQuery()).thenReturn(resultSet);
+ when(columnMetaDataStatement.executeQuery()).thenReturn(resultSet);
+ ResultSet viewResultSet = mock(ResultSet.class);
+ when(viewResultSet.next()).thenReturn(true, false);
+ when(viewResultSet.getString("TABLE_NAME")).thenReturn("tbl_one");
+ when(viewMetaDataStatement.executeQuery()).thenReturn(viewResultSet);
when(result.getCatalog()).thenReturn("catalog_one");
- String sql = "SELECT
TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG=? ORDER BY
ORDINAL_POSITION";
- when(result.prepareStatement(sql)).thenReturn(preparedStatement);
+ when(result.getSchema()).thenReturn("foo_schema");
+ String sql = "SELECT
TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS"
+ + " WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? ORDER BY
ORDINAL_POSITION";
+ when(result.prepareStatement(sql)).thenReturn(columnMetaDataStatement);
+ String viewSql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND TABLE_TYPE='VIEW' AND
UPPER(TABLE_NAME) IN ('TBL_ONE')";
+
when(result.prepareStatement(viewSql)).thenReturn(viewMetaDataStatement);
return result;
}
@@ -117,13 +137,19 @@ class PrestoMetaDataLoaderTest {
private Connection mockConnectionWithTables() throws SQLException {
Connection result = mock(Connection.class);
- PreparedStatement preparedStatement = mock(PreparedStatement.class);
+ PreparedStatement columnMetaDataStatement =
mock(PreparedStatement.class);
ResultSet resultSet = mockResultSetWithTables();
- when(preparedStatement.executeQuery()).thenReturn(resultSet);
+ when(columnMetaDataStatement.executeQuery()).thenReturn(resultSet);
+ PreparedStatement viewMetaDataStatement =
mock(PreparedStatement.class);
+ ResultSet viewResultSet = mock(ResultSet.class);
+ when(viewMetaDataStatement.executeQuery()).thenReturn(viewResultSet);
String sql = "SELECT
TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS "
- + "WHERE TABLE_CATALOG=? AND UPPER(TABLE_NAME) IN
('TARGET_TABLE') ORDER BY ORDINAL_POSITION";
- when(result.prepareStatement(sql)).thenReturn(preparedStatement);
+ + "WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND
UPPER(TABLE_NAME) IN ('TARGET_TABLE') ORDER BY ORDINAL_POSITION";
+ when(result.prepareStatement(sql)).thenReturn(columnMetaDataStatement);
+ String viewSql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=? AND TABLE_TYPE='VIEW' AND
UPPER(TABLE_NAME) IN ('TARGET_TABLE')";
+
when(result.prepareStatement(viewSql)).thenReturn(viewMetaDataStatement);
when(result.getCatalog()).thenReturn("catalog_two");
+ when(result.getSchema()).thenReturn("bar_schema");
return result;
}
