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 dfee6d93a66 Support load SQLServer view metadata. (#20708)
dfee6d93a66 is described below
commit dfee6d93a66fa3c94e85f1d8c9fa5b08328a7207
Author: Zonglei Dong <[email protected]>
AuthorDate: Fri Sep 2 08:36:35 2022 +0800
Support load SQLServer view metadata. (#20708)
* Support load SQLServer view metadata.
* Support load SQLServer view metadata.
---
.../dialect/SQLServerSchemaMetaDataLoader.java | 23 ++++++++++++++++++++--
.../dialect/SQLServerSchemaMetaDataLoaderTest.java | 23 ++++++++++++++++++++++
2 files changed, 44 insertions(+), 2 deletions(-)
diff --git
a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/SQLServerSchemaMetaDataLoader.java
b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/SQLServerSchemaMetaDataLoader.java
index f49fb59249a..272da05e9cb 100644
---
a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/SQLServerSchemaMetaDataLoader.java
+++
b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/SQLServerSchemaMetaDataLoader.java
@@ -22,6 +22,7 @@ import
org.apache.shardingsphere.infra.metadata.database.schema.loader.model.Col
import
org.apache.shardingsphere.infra.metadata.database.schema.loader.model.IndexMetaData;
import
org.apache.shardingsphere.infra.metadata.database.schema.loader.model.SchemaMetaData;
import
org.apache.shardingsphere.infra.metadata.database.schema.loader.model.TableMetaData;
+import
org.apache.shardingsphere.infra.metadata.database.schema.loader.model.ViewMetaData;
import
org.apache.shardingsphere.infra.metadata.database.schema.loader.spi.DataTypeLoaderFactory;
import
org.apache.shardingsphere.infra.metadata.database.schema.loader.spi.DialectSchemaMetaDataLoader;
@@ -53,6 +54,8 @@ public final class SQLServerSchemaMetaDataLoader implements
DialectSchemaMetaDat
private static final String ORDER_BY_COLUMN_ID = " ORDER BY col.column_id";
private static final String TABLE_META_DATA_SQL =
TABLE_META_DATA_SQL_NO_ORDER + ORDER_BY_COLUMN_ID;
+
+ private static final String VIEW_META_DATA_SQL = "SELECT TABLE_NAME,
VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_CATALOG = ?";
private static final String TABLE_META_DATA_SQL_IN_TABLES =
TABLE_META_DATA_SQL_NO_ORDER + " WHERE obj.name IN (%s)" + ORDER_BY_COLUMN_ID;
@@ -72,8 +75,24 @@ public final class SQLServerSchemaMetaDataLoader implements
DialectSchemaMetaDat
tableMetaDataList.add(new TableMetaData(entry.getKey(),
entry.getValue(), indexMetaDataList, Collections.emptyList()));
}
}
- // TODO load views from SQLServer database.
- return Collections.singletonList(new SchemaMetaData(defaultSchemaName,
tableMetaDataList, Collections.emptyList()));
+ return Collections.singletonList(new SchemaMetaData(defaultSchemaName,
tableMetaDataList, loadViewMetaData(dataSource, tables)));
+ }
+
+ private Collection<ViewMetaData> loadViewMetaData(final DataSource
dataSource, final Collection<String> tables) throws SQLException {
+ Collection<ViewMetaData> result = new LinkedList<>();
+ try (
+ Connection connection = dataSource.getConnection();
+ PreparedStatement preparedStatement =
connection.prepareStatement(VIEW_META_DATA_SQL)) {
+ preparedStatement.setString(1, connection.getCatalog());
+ try (ResultSet resultSet = preparedStatement.executeQuery()) {
+ while (resultSet.next()) {
+ String tableName = resultSet.getString("TABLE_NAME");
+ String viewDefinition =
resultSet.getString("VIEW_DEFINITION");
+ result.add(new ViewMetaData(tableName, viewDefinition));
+ }
+ }
+ }
+ return result;
}
private Map<String, Collection<ColumnMetaData>>
loadColumnMetaDataMap(final DataSource dataSource, final Collection<String>
tables) throws SQLException {
diff --git
a/shardingsphere-infra/shardingsphere-infra-common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/SQLServerSchemaMetaDataLoaderTest.java
b/shardingsphere-infra/shardingsphere-infra-common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/SQLServerSchemaMetaDataLoaderTest.java
index 3759a5da6b2..6cb73f1bf7d 100644
---
a/shardingsphere-infra/shardingsphere-infra-common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/SQLServerSchemaMetaDataLoaderTest.java
+++
b/shardingsphere-infra/shardingsphere-infra-common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/SQLServerSchemaMetaDataLoaderTest.java
@@ -22,6 +22,7 @@ import
org.apache.shardingsphere.infra.metadata.database.schema.loader.model.Col
import
org.apache.shardingsphere.infra.metadata.database.schema.loader.model.IndexMetaData;
import
org.apache.shardingsphere.infra.metadata.database.schema.loader.model.SchemaMetaData;
import
org.apache.shardingsphere.infra.metadata.database.schema.loader.model.TableMetaData;
+import
org.apache.shardingsphere.infra.metadata.database.schema.loader.model.ViewMetaData;
import
org.apache.shardingsphere.infra.metadata.database.schema.loader.spi.DialectSchemaMetaDataLoader;
import
org.apache.shardingsphere.infra.metadata.database.schema.loader.spi.DialectSchemaMetaDataLoaderFactory;
import org.junit.Test;
@@ -71,6 +72,8 @@ public final class SQLServerSchemaMetaDataLoaderTest {
private static final String LOAD_INDEX_META_DATA = "SELECT a.name AS
INDEX_NAME, c.name AS TABLE_NAME FROM sys.indexes a"
+ " JOIN sys.objects c ON a.object_id = c.object_id WHERE
a.index_id NOT IN (0, 255) AND c.name IN ('tbl')";
+
+ private static final String LOAD_VIEW_META_DATA = "SELECT TABLE_NAME,
VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_CATALOG = ?";
@Test
public void assertLoadWithoutTablesWithHighVersion() throws SQLException {
@@ -141,6 +144,18 @@ public final class SQLServerSchemaMetaDataLoaderTest {
assertThat(columnsIterator.next(), is(new ColumnMetaData("id", 4,
false, true, true, true)));
assertThat(columnsIterator.next(), is(new ColumnMetaData("name", 12,
false, false, false, true)));
}
+
+ @Test
+ public void assertLoadViewMetaData() throws SQLException {
+ DataSource dataSource = mockDataSource();
+ ResultSet resultSet = mockViewMetaDataResultSet();
+ when(dataSource.getConnection().prepareStatement(LOAD_VIEW_META_DATA)
+ .executeQuery()).thenReturn(resultSet);
+ Collection<SchemaMetaData> actual =
getDialectTableMetaDataLoader().load(dataSource,
Collections.singletonList("tbl"), "sharding_db");
+ ViewMetaData actualViewMetaData =
actual.iterator().next().getViews().iterator().next();
+ assertThat(actualViewMetaData.getName(), is("v_order"));
+ assertThat(actualViewMetaData.getViewDefinition(), is("create view
v_order as select * from t_order;"));
+ }
private DataSource mockDataSource() throws SQLException {
DataSource result = mock(DataSource.class, RETURNS_DEEP_STUBS);
@@ -178,6 +193,14 @@ public final class SQLServerSchemaMetaDataLoaderTest {
return result;
}
+ private ResultSet mockViewMetaDataResultSet() throws SQLException {
+ ResultSet result = mock(ResultSet.class);
+ when(result.next()).thenReturn(true, false);
+ when(result.getString("TABLE_NAME")).thenReturn("v_order");
+ when(result.getString("VIEW_DEFINITION")).thenReturn("create view
v_order as select * from t_order;");
+ return result;
+ }
+
private DialectSchemaMetaDataLoader getDialectTableMetaDataLoader() {
Optional<DialectSchemaMetaDataLoader> result =
DialectSchemaMetaDataLoaderFactory.findInstance(DatabaseTypeFactory.getInstance("SQLServer"));
assertTrue(result.isPresent());
