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 f462c953efd Support SQLServer column visible. (#20260)
f462c953efd is described below
commit f462c953efd079a4c972ebb0b5069b0b0b39eb75
Author: Zonglei Dong <[email protected]>
AuthorDate: Thu Aug 18 18:45:46 2022 +0800
Support SQLServer column visible. (#20260)
* Optimize Oracle visible column code, remove version judgment.
* Support visible column for SQLServer database.
* Optimize SQLServer visible column code, add version judgment.
---
.../loader/dialect/OracleSchemaMetaDataLoader.java | 15 +--
.../dialect/SQLServerSchemaMetaDataLoader.java | 33 ++++--
.../dialect/OracleSchemaMetaDataLoaderTest.java | 16 +--
.../dialect/SQLServerSchemaMetaDataLoaderTest.java | 112 ++++++++++++++++-----
4 files changed, 119 insertions(+), 57 deletions(-)
diff --git
a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/OracleSchemaMetaDataLoader.java
b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/OracleSchemaMetaDataLoader.java
index 904b4b647a5..e0aad44a6f2 100644
---
a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/OracleSchemaMetaDataLoader.java
+++
b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/OracleSchemaMetaDataLoader.java
@@ -47,7 +47,7 @@ import java.util.stream.Collectors;
*/
public final class OracleSchemaMetaDataLoader implements
DialectSchemaMetaDataLoader {
- private static final String TABLE_META_DATA_SQL_NO_ORDER = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID %s FROM
ALL_TAB_COLS WHERE OWNER = ?";
+ private static final String TABLE_META_DATA_SQL_NO_ORDER = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID, HIDDEN_COLUMN
%s FROM ALL_TAB_COLS WHERE OWNER = ?";
private static final String ORDER_BY_COLUMN_ID = " ORDER BY COLUMN_ID";
@@ -68,8 +68,6 @@ public final class OracleSchemaMetaDataLoader implements
DialectSchemaMetaDataLo
private static final int IDENTITY_COLUMN_START_MINOR_VERSION = 1;
- private static final int HIDDEN_COLUMN_START_MINOR_VERSION = 1;
-
private static final int MAX_EXPRESSION_SIZE = 1000;
@Override
@@ -117,7 +115,7 @@ public final class OracleSchemaMetaDataLoader implements
DialectSchemaMetaDataLo
boolean generated = versionContainsIdentityColumn(databaseMetaData) &&
"YES".equals(resultSet.getString("IDENTITY_COLUMN"));
// TODO need to support caseSensitive when version < 12.2.
boolean caseSensitive = versionContainsCollation(databaseMetaData) &&
resultSet.getString("COLLATION").endsWith("_CS");
- boolean isVisible = !(versionContainsHiddenColumn(databaseMetaData) &&
"YES".equals(resultSet.getString("HIDDEN_COLUMN")));
+ boolean isVisible = "NO".equals(resultSet.getString("HIDDEN_COLUMN"));
return new ColumnMetaData(columnName, dataTypeMap.get(dataType),
primaryKey, generated, caseSensitive, isVisible);
}
@@ -130,10 +128,7 @@ public final class OracleSchemaMetaDataLoader implements
DialectSchemaMetaDataLo
}
private String getTableMetaDataSQL(final Collection<String> tables, final
DatabaseMetaData databaseMetaData) throws SQLException {
- StringBuilder stringBuilder = new StringBuilder(43);
- if (versionContainsHiddenColumn(databaseMetaData)) {
- stringBuilder.append(", HIDDEN_COLUMN");
- }
+ StringBuilder stringBuilder = new StringBuilder(28);
if (versionContainsIdentityColumn(databaseMetaData)) {
stringBuilder.append(", IDENTITY_COLUMN");
}
@@ -145,10 +140,6 @@ public final class OracleSchemaMetaDataLoader implements
DialectSchemaMetaDataLo
: String.format(TABLE_META_DATA_SQL_IN_TABLES, collation,
tables.stream().map(each -> String.format("'%s'",
each)).collect(Collectors.joining(",")));
}
- private boolean versionContainsHiddenColumn(final DatabaseMetaData
databaseMetaData) throws SQLException {
- return databaseMetaData.getDatabaseMajorVersion() >=
COLLATION_START_MAJOR_VERSION && databaseMetaData.getDatabaseMinorVersion() >=
HIDDEN_COLUMN_START_MINOR_VERSION;
- }
-
private boolean versionContainsCollation(final DatabaseMetaData
databaseMetaData) throws SQLException {
return databaseMetaData.getDatabaseMajorVersion() >=
COLLATION_START_MAJOR_VERSION && databaseMetaData.getDatabaseMinorVersion() >=
COLLATION_START_MINOR_VERSION;
}
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 e82c5687144..bfe8db45afc 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
@@ -27,6 +27,7 @@ import
org.apache.shardingsphere.infra.metadata.database.schema.loader.spi.Diale
import javax.sql.DataSource;
import java.sql.Connection;
+import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@@ -44,19 +45,21 @@ import java.util.stream.Collectors;
public final class SQLServerSchemaMetaDataLoader implements
DialectSchemaMetaDataLoader {
private static final String TABLE_META_DATA_SQL_NO_ORDER = "SELECT
obj.name AS TABLE_NAME, col.name AS COLUMN_NAME, t.name AS DATA_TYPE,"
- + " col.collation_name AS COLLATION_NAME, col.column_id,
is_identity AS IS_IDENTITY,"
+ + " col.collation_name AS COLLATION_NAME, col.column_id,
is_identity AS IS_IDENTITY, %s"
+ " (SELECT TOP 1 ind.is_primary_key FROM sys.index_columns ic
LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id"
- + " AND ic.index_id = ind.index_id AND ind.name LIKE 'PK_%' WHERE
ic.object_id = obj.object_id AND ic.column_id = col.column_id) AS
IS_PRIMARY_KEY"
+ + " AND ic.index_id = ind.index_id AND ind.name LIKE 'PK_%%' WHERE
ic.object_id = obj.object_id AND ic.column_id = col.column_id) AS
IS_PRIMARY_KEY"
+ " FROM sys.objects obj INNER JOIN sys.columns col ON
obj.object_id = col.object_id LEFT JOIN sys.types t ON t.user_type_id =
col.user_type_id";
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 TABLE_META_DATA_SQL_IN_TABLES = " WHERE
obj.name IN (%s)" + ORDER_BY_COLUMN_ID;
+ 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;
private static final String INDEX_META_DATA_SQL = "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 (%s)";
+
+ private static final int HIDDEN_COLUMN_START_MAJOR_VERSION = 15;
@Override
public Collection<SchemaMetaData> load(final DataSource dataSource, final
Collection<String> tables, final String defaultSchemaName) throws SQLException {
@@ -76,12 +79,12 @@ public final class SQLServerSchemaMetaDataLoader implements
DialectSchemaMetaDat
Map<String, Collection<ColumnMetaData>> result = new HashMap<>();
try (
Connection connection = dataSource.getConnection();
- PreparedStatement preparedStatement =
connection.prepareStatement(getTableMetaDataSQL(tables))) {
+ PreparedStatement preparedStatement =
connection.prepareStatement(getTableMetaDataSQL(tables,
connection.getMetaData()))) {
Map<String, Integer> dataTypes =
DataTypeLoaderFactory.getInstance(DatabaseTypeFactory.getInstance("SQLServer")).load(connection.getMetaData());
try (ResultSet resultSet = preparedStatement.executeQuery()) {
while (resultSet.next()) {
String tableName = resultSet.getString("TABLE_NAME");
- ColumnMetaData columnMetaData =
loadColumnMetaData(dataTypes, resultSet);
+ ColumnMetaData columnMetaData =
loadColumnMetaData(dataTypes, resultSet, connection.getMetaData());
if (!result.containsKey(tableName)) {
result.put(tableName, new LinkedList<>());
}
@@ -92,19 +95,29 @@ public final class SQLServerSchemaMetaDataLoader implements
DialectSchemaMetaDat
return result;
}
- private ColumnMetaData loadColumnMetaData(final Map<String, Integer>
dataTypeMap, final ResultSet resultSet) throws SQLException {
+ private ColumnMetaData loadColumnMetaData(final Map<String, Integer>
dataTypeMap, final ResultSet resultSet, final DatabaseMetaData
databaseMetaData) throws SQLException {
String columnName = resultSet.getString("COLUMN_NAME");
String dataType = resultSet.getString("DATA_TYPE");
String collationName = resultSet.getString("COLLATION_NAME");
boolean primaryKey = "1".equals(resultSet.getString("IS_PRIMARY_KEY"));
boolean generated = "1".equals(resultSet.getString("IS_IDENTITY"));
boolean caseSensitive = null != collationName &&
collationName.indexOf("_CS") > 0;
- return new ColumnMetaData(columnName, dataTypeMap.get(dataType),
primaryKey, generated, caseSensitive, true);
+ boolean isVisible = !(versionContainsHiddenColumn(databaseMetaData) &&
"1".equals(resultSet.getString("IS_HIDDEN")));
+ return new ColumnMetaData(columnName, dataTypeMap.get(dataType),
primaryKey, generated, caseSensitive, isVisible);
}
- private String getTableMetaDataSQL(final Collection<String> tables) {
- return tables.isEmpty() ? TABLE_META_DATA_SQL
- : TABLE_META_DATA_SQL_NO_ORDER +
String.format(TABLE_META_DATA_SQL_IN_TABLES, tables.stream().map(each ->
String.format("'%s'", each)).collect(Collectors.joining(",")));
+ private String getTableMetaDataSQL(final Collection<String> tables, final
DatabaseMetaData databaseMetaData) throws SQLException {
+ StringBuilder stringBuilder = new StringBuilder(24);
+ if (versionContainsHiddenColumn(databaseMetaData)) {
+ stringBuilder.append("is_hidden AS IS_HIDDEN,");
+ }
+ String isHidden = stringBuilder.toString();
+ return tables.isEmpty() ? String.format(TABLE_META_DATA_SQL, isHidden)
+ : String.format(TABLE_META_DATA_SQL_IN_TABLES, isHidden,
tables.stream().map(each -> String.format("'%s'",
each)).collect(Collectors.joining(",")));
+ }
+
+ private boolean versionContainsHiddenColumn(final DatabaseMetaData
databaseMetaData) throws SQLException {
+ return databaseMetaData.getDatabaseMajorVersion() >=
HIDDEN_COLUMN_START_MAJOR_VERSION;
}
private Map<String, Collection<IndexMetaData>> loadIndexMetaData(final
DataSource dataSource, final Collection<String> tableNames) throws SQLException
{
diff --git
a/shardingsphere-infra/shardingsphere-infra-common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/OracleSchemaMetaDataLoaderTest.java
b/shardingsphere-infra/shardingsphere-infra-common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/OracleSchemaMetaDataLoaderTest.java
index b9c5aaba561..5db4573c9fb 100644
---
a/shardingsphere-infra/shardingsphere-infra-common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/OracleSchemaMetaDataLoaderTest.java
+++
b/shardingsphere-infra/shardingsphere-infra-common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/loader/dialect/OracleSchemaMetaDataLoaderTest.java
@@ -51,22 +51,22 @@ public final class OracleSchemaMetaDataLoaderTest {
private static final String ALL_INDEXES_SQL = "SELECT OWNER AS
TABLE_SCHEMA, TABLE_NAME, INDEX_NAME FROM ALL_INDEXES WHERE OWNER = ? AND
TABLE_NAME IN ('tbl')";
- private static final String ALL_TAB_COLUMNS_SQL_CONDITION1 = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID , HIDDEN_COLUMN,
IDENTITY_COLUMN, COLLATION"
+ private static final String ALL_TAB_COLUMNS_SQL_CONDITION1 = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID, HIDDEN_COLUMN ,
IDENTITY_COLUMN, COLLATION"
+ " FROM ALL_TAB_COLS WHERE OWNER = ? AND TABLE_NAME IN ('tbl')
ORDER BY COLUMN_ID";
- private static final String ALL_TAB_COLUMNS_SQL_CONDITION2 = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID , HIDDEN_COLUMN,
IDENTITY_COLUMN"
+ private static final String ALL_TAB_COLUMNS_SQL_CONDITION2 = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID, HIDDEN_COLUMN ,
IDENTITY_COLUMN"
+ " FROM ALL_TAB_COLS WHERE OWNER = ? AND TABLE_NAME IN ('tbl')
ORDER BY COLUMN_ID";
- private static final String ALL_TAB_COLUMNS_SQL_CONDITION3 = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID FROM
ALL_TAB_COLS WHERE OWNER = ?"
+ private static final String ALL_TAB_COLUMNS_SQL_CONDITION3 = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID, HIDDEN_COLUMN
FROM ALL_TAB_COLS WHERE OWNER = ?"
+ " AND TABLE_NAME IN ('tbl') ORDER BY COLUMN_ID";
- private static final String ALL_TAB_COLUMNS_SQL_CONDITION4 = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID , HIDDEN_COLUMN,
IDENTITY_COLUMN, COLLATION"
+ private static final String ALL_TAB_COLUMNS_SQL_CONDITION4 = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID, HIDDEN_COLUMN ,
IDENTITY_COLUMN, COLLATION"
+ " FROM ALL_TAB_COLS WHERE OWNER = ? AND TABLE_NAME IN ('tbl')
ORDER BY COLUMN_ID";
- private static final String ALL_TAB_COLUMNS_SQL_CONDITION5 = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID , HIDDEN_COLUMN,
IDENTITY_COLUMN FROM ALL_TAB_COLS"
+ private static final String ALL_TAB_COLUMNS_SQL_CONDITION5 = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID, HIDDEN_COLUMN ,
IDENTITY_COLUMN FROM ALL_TAB_COLS"
+ " WHERE OWNER = ? AND TABLE_NAME IN ('tbl') ORDER BY COLUMN_ID";
- private static final String ALL_TAB_COLUMNS_SQL_CONDITION6 = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID FROM
ALL_TAB_COLS"
+ private static final String ALL_TAB_COLUMNS_SQL_CONDITION6 = "SELECT OWNER
AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_ID, HIDDEN_COLUMN
FROM ALL_TAB_COLS"
+ " WHERE OWNER = ? AND TABLE_NAME IN ('tbl') ORDER BY COLUMN_ID";
@Test
@@ -123,7 +123,7 @@ public final class OracleSchemaMetaDataLoaderTest {
TableMetaData actualTableMetaData =
actual.iterator().next().getTables().iterator().next();
Iterator<ColumnMetaData> columnsIterator =
actualTableMetaData.getColumns().iterator();
assertThat(columnsIterator.next(), is(new ColumnMetaData("id", 4,
false, false, false, true)));
- assertThat(columnsIterator.next(), is(new ColumnMetaData("name", 12,
false, false, false, true)));
+ assertThat(columnsIterator.next(), is(new ColumnMetaData("name", 12,
false, false, false, false)));
}
@Test
@@ -180,7 +180,7 @@ public final class OracleSchemaMetaDataLoaderTest {
TableMetaData actualTableMetaData =
actual.iterator().next().getTables().iterator().next();
Iterator<ColumnMetaData> columnsIterator =
actualTableMetaData.getColumns().iterator();
assertThat(columnsIterator.next(), is(new ColumnMetaData("id", 4,
true, false, false, true)));
- assertThat(columnsIterator.next(), is(new ColumnMetaData("name", 12,
false, false, false, true)));
+ assertThat(columnsIterator.next(), is(new ColumnMetaData("name", 12,
false, false, false, false)));
}
private DataSource mockDataSource() 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 82dda8e3e3c..be75b6f47be 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
@@ -42,44 +42,104 @@ import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.when;
public final class SQLServerSchemaMetaDataLoaderTest {
+
+ private static final String
LOAD_COLUMN_META_DATA_WITHOUT_TABLES_HIGH_VERSION = "SELECT obj.name AS
TABLE_NAME, col.name AS COLUMN_NAME, t.name AS DATA_TYPE,"
+ + " col.collation_name AS COLLATION_NAME, col.column_id,
is_identity AS IS_IDENTITY, is_hidden AS IS_HIDDEN,"
+ + " (SELECT TOP 1 ind.is_primary_key FROM sys.index_columns ic
LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id"
+ + " AND ic.index_id = ind.index_id AND ind.name LIKE 'PK_%' WHERE
ic.object_id = obj.object_id AND ic.column_id = col.column_id) AS
IS_PRIMARY_KEY"
+ + " FROM sys.objects obj INNER JOIN sys.columns col ON
obj.object_id = col.object_id LEFT JOIN sys.types t ON t.user_type_id =
col.user_type_id ORDER BY col.column_id";
+
+ private static final String
LOAD_COLUMN_META_DATA_WITHOUT_TABLES_LOW_VERSION = "SELECT obj.name AS
TABLE_NAME, col.name AS COLUMN_NAME, t.name AS DATA_TYPE,"
+ + " col.collation_name AS COLLATION_NAME, col.column_id,
is_identity AS IS_IDENTITY,"
+ + " (SELECT TOP 1 ind.is_primary_key FROM sys.index_columns ic
LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id"
+ + " AND ic.index_id = ind.index_id AND ind.name LIKE 'PK_%' WHERE
ic.object_id = obj.object_id AND ic.column_id = col.column_id) AS
IS_PRIMARY_KEY"
+ + " FROM sys.objects obj INNER JOIN sys.columns col ON
obj.object_id = col.object_id LEFT JOIN sys.types t ON t.user_type_id =
col.user_type_id ORDER BY col.column_id";
+
+ private static final String LOAD_COLUMN_META_DATA_WITH_TABLES_HIGH_VERSION
= "SELECT obj.name AS TABLE_NAME, col.name AS COLUMN_NAME, t.name AS DATA_TYPE,"
+ + " col.collation_name AS COLLATION_NAME, col.column_id,
is_identity AS IS_IDENTITY, is_hidden AS IS_HIDDEN,"
+ + " (SELECT TOP 1 ind.is_primary_key FROM sys.index_columns ic
LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id"
+ + " AND ic.index_id = ind.index_id AND ind.name LIKE 'PK_%' WHERE
ic.object_id = obj.object_id AND ic.column_id = col.column_id) AS
IS_PRIMARY_KEY"
+ + " FROM sys.objects obj INNER JOIN sys.columns col ON
obj.object_id = col.object_id LEFT JOIN sys.types t ON t.user_type_id =
col.user_type_id"
+ + " WHERE obj.name IN ('tbl') ORDER BY col.column_id";
+
+ private static final String LOAD_COLUMN_META_DATA_WITH_TABLES_LOW_VERSION
= "SELECT obj.name AS TABLE_NAME, col.name AS COLUMN_NAME, t.name AS DATA_TYPE,"
+ + " col.collation_name AS COLLATION_NAME, col.column_id,
is_identity AS IS_IDENTITY,"
+ + " (SELECT TOP 1 ind.is_primary_key FROM sys.index_columns ic
LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id"
+ + " AND ic.index_id = ind.index_id AND ind.name LIKE 'PK_%' WHERE
ic.object_id = obj.object_id AND ic.column_id = col.column_id) AS
IS_PRIMARY_KEY"
+ + " FROM sys.objects obj INNER JOIN sys.columns col ON
obj.object_id = col.object_id LEFT JOIN sys.types t ON t.user_type_id =
col.user_type_id"
+ + " WHERE obj.name IN ('tbl') ORDER BY col.column_id";
+
+ 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')";
@Test
- public void assertLoadWithoutTables() throws SQLException {
+ public void assertLoadWithoutTablesWithHighVersion() throws SQLException {
DataSource dataSource = mockDataSource();
ResultSet resultSet = mockTableMetaDataResultSet();
- when(dataSource.getConnection().prepareStatement(
- "SELECT obj.name AS TABLE_NAME, col.name AS COLUMN_NAME,
t.name AS DATA_TYPE,"
- + " col.collation_name AS COLLATION_NAME,
col.column_id, is_identity AS IS_IDENTITY,"
- + " (SELECT TOP 1 ind.is_primary_key FROM
sys.index_columns ic LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id"
- + " AND ic.index_id = ind.index_id AND ind.name LIKE
'PK_%' WHERE ic.object_id = obj.object_id AND ic.column_id = col.column_id) AS
IS_PRIMARY_KEY"
- + " FROM sys.objects obj INNER JOIN sys.columns col ON
obj.object_id = col.object_id LEFT JOIN sys.types t ON t.user_type_id =
col.user_type_id ORDER BY col.column_id")
+
when(dataSource.getConnection().prepareStatement(LOAD_COLUMN_META_DATA_WITHOUT_TABLES_HIGH_VERSION)
.executeQuery()).thenReturn(resultSet);
ResultSet indexResultSet = mockIndexMetaDataResultSet();
- when(dataSource.getConnection().prepareStatement(
- "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')")
+ when(dataSource.getConnection().prepareStatement(LOAD_INDEX_META_DATA)
.executeQuery()).thenReturn(indexResultSet);
-
assertTableMetaDataMap(getDialectTableMetaDataLoader().load(dataSource,
Collections.emptyList(), "sharding_db"));
+
when(dataSource.getConnection().getMetaData().getDatabaseMajorVersion()).thenReturn(15);
+ Collection<SchemaMetaData> actual =
getDialectTableMetaDataLoader().load(dataSource, Collections.emptyList(),
"sharding_db");
+ assertTableMetaDataMap(actual);
+ TableMetaData actualTableMetaData =
actual.iterator().next().getTables().iterator().next();
+ Iterator<ColumnMetaData> columnsIterator =
actualTableMetaData.getColumns().iterator();
+ assertThat(columnsIterator.next(), is(new ColumnMetaData("id", 4,
false, true, true, true)));
+ assertThat(columnsIterator.next(), is(new ColumnMetaData("name", 12,
false, false, false, false)));
}
-
+
@Test
- public void assertLoadWithTables() throws SQLException {
+ public void assertLoadWithoutTablesWithLowVersion() throws SQLException {
DataSource dataSource = mockDataSource();
ResultSet resultSet = mockTableMetaDataResultSet();
- when(dataSource.getConnection().prepareStatement(
- "SELECT obj.name AS TABLE_NAME, col.name AS COLUMN_NAME,
t.name AS DATA_TYPE,"
- + " col.collation_name AS COLLATION_NAME,
col.column_id, is_identity AS IS_IDENTITY,"
- + " (SELECT TOP 1 ind.is_primary_key FROM
sys.index_columns ic LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id"
- + " AND ic.index_id = ind.index_id AND ind.name LIKE
'PK_%' WHERE ic.object_id = obj.object_id AND ic.column_id = col.column_id) AS
IS_PRIMARY_KEY"
- + " FROM sys.objects obj INNER JOIN sys.columns col ON
obj.object_id = col.object_id LEFT JOIN sys.types t ON t.user_type_id =
col.user_type_id"
- + " WHERE obj.name IN ('tbl') ORDER BY col.column_id")
+
when(dataSource.getConnection().prepareStatement(LOAD_COLUMN_META_DATA_WITHOUT_TABLES_LOW_VERSION)
.executeQuery()).thenReturn(resultSet);
ResultSet indexResultSet = mockIndexMetaDataResultSet();
- when(dataSource.getConnection().prepareStatement(
- "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')")
+ when(dataSource.getConnection().prepareStatement(LOAD_INDEX_META_DATA)
+ .executeQuery()).thenReturn(indexResultSet);
+
when(dataSource.getConnection().getMetaData().getDatabaseMajorVersion()).thenReturn(14);
+ Collection<SchemaMetaData> actual =
getDialectTableMetaDataLoader().load(dataSource, Collections.emptyList(),
"sharding_db");
+ assertTableMetaDataMap(actual);
+ TableMetaData actualTableMetaData =
actual.iterator().next().getTables().iterator().next();
+ Iterator<ColumnMetaData> columnsIterator =
actualTableMetaData.getColumns().iterator();
+ 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 assertLoadWithTablesWithHighVersion() throws SQLException {
+ DataSource dataSource = mockDataSource();
+ ResultSet resultSet = mockTableMetaDataResultSet();
+
when(dataSource.getConnection().prepareStatement(LOAD_COLUMN_META_DATA_WITH_TABLES_HIGH_VERSION).executeQuery()).thenReturn(resultSet);
+ ResultSet indexResultSet = mockIndexMetaDataResultSet();
+ when(dataSource.getConnection().prepareStatement(LOAD_INDEX_META_DATA)
.executeQuery()).thenReturn(indexResultSet);
-
assertTableMetaDataMap(getDialectTableMetaDataLoader().load(dataSource,
Collections.singletonList("tbl"), "sharding_db"));
+
when(dataSource.getConnection().getMetaData().getDatabaseMajorVersion()).thenReturn(15);
+ Collection<SchemaMetaData> actual =
getDialectTableMetaDataLoader().load(dataSource,
Collections.singletonList("tbl"), "sharding_db");
+ assertTableMetaDataMap(actual);
+ TableMetaData actualTableMetaData =
actual.iterator().next().getTables().iterator().next();
+ Iterator<ColumnMetaData> columnsIterator =
actualTableMetaData.getColumns().iterator();
+ assertThat(columnsIterator.next(), is(new ColumnMetaData("id", 4,
false, true, true, true)));
+ assertThat(columnsIterator.next(), is(new ColumnMetaData("name", 12,
false, false, false, false)));
+ }
+
+ @Test
+ public void assertLoadWithTablesWithLowVersion() throws SQLException {
+ DataSource dataSource = mockDataSource();
+ ResultSet resultSet = mockTableMetaDataResultSet();
+
when(dataSource.getConnection().prepareStatement(LOAD_COLUMN_META_DATA_WITH_TABLES_LOW_VERSION).executeQuery()).thenReturn(resultSet);
+ ResultSet indexResultSet = mockIndexMetaDataResultSet();
+ when(dataSource.getConnection().prepareStatement(LOAD_INDEX_META_DATA)
+ .executeQuery()).thenReturn(indexResultSet);
+
when(dataSource.getConnection().getMetaData().getDatabaseMajorVersion()).thenReturn(14);
+ Collection<SchemaMetaData> actual =
getDialectTableMetaDataLoader().load(dataSource,
Collections.singletonList("tbl"), "sharding_db");
+ assertTableMetaDataMap(actual);
+ TableMetaData actualTableMetaData =
actual.iterator().next().getTables().iterator().next();
+ Iterator<ColumnMetaData> columnsIterator =
actualTableMetaData.getColumns().iterator();
+ assertThat(columnsIterator.next(), is(new ColumnMetaData("id", 4,
false, true, true, true)));
+ assertThat(columnsIterator.next(), is(new ColumnMetaData("name", 12,
false, false, false, true)));
}
private DataSource mockDataSource() throws SQLException {
@@ -105,6 +165,7 @@ public final class SQLServerSchemaMetaDataLoaderTest {
when(result.getString("DATA_TYPE")).thenReturn("int", "varchar");
when(result.getString("COLUMN_KEY")).thenReturn("1", "");
when(result.getString("IS_IDENTITY")).thenReturn("1", "");
+ when(result.getString("IS_HIDDEN")).thenReturn("0", "1");
when(result.getString("COLLATION_NAME")).thenReturn("SQL_Latin1_General_CP1_CS_AS",
"utf8");
return result;
}
@@ -127,9 +188,6 @@ public final class SQLServerSchemaMetaDataLoaderTest {
assertThat(schemaMetaDataList.size(), is(1));
TableMetaData actualTableMetaData =
schemaMetaDataList.iterator().next().getTables().iterator().next();
assertThat(actualTableMetaData.getColumns().size(), is(2));
- Iterator<ColumnMetaData> columnsIterator =
actualTableMetaData.getColumns().iterator();
- assertThat(columnsIterator.next(), is(new ColumnMetaData("id", 4,
false, true, true, true)));
- assertThat(columnsIterator.next(), is(new ColumnMetaData("name", 12,
false, false, false, true)));
assertThat(actualTableMetaData.getIndexes().size(), is(1));
Iterator<IndexMetaData> indexesIterator =
actualTableMetaData.getIndexes().iterator();
assertThat(indexesIterator.next(), is(new IndexMetaData("id")));
