You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2022/09/02 00:36:53 UTC
[shardingsphere] branch master updated: Support load SQLServer view metadata. (#20708)
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 <do...@apache.org>
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());