1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 package org.apache.shardingsphere.infra.database.sqlserver.metadata.data.loader;
19
20 import org.apache.shardingsphere.infra.database.core.metadata.data.loader.DialectMetaDataLoader;
21 import org.apache.shardingsphere.infra.database.core.metadata.data.loader.MetaDataLoaderMaterial;
22 import org.apache.shardingsphere.infra.database.core.metadata.data.model.ColumnMetaData;
23 import org.apache.shardingsphere.infra.database.core.metadata.data.model.IndexMetaData;
24 import org.apache.shardingsphere.infra.database.core.metadata.data.model.SchemaMetaData;
25 import org.apache.shardingsphere.infra.database.core.metadata.data.model.TableMetaData;
26 import org.apache.shardingsphere.infra.database.core.metadata.database.datatype.DataTypeLoader;
27
28 import javax.sql.DataSource;
29 import java.sql.Connection;
30 import java.sql.DatabaseMetaData;
31 import java.sql.PreparedStatement;
32 import java.sql.ResultSet;
33 import java.sql.SQLException;
34 import java.util.Collection;
35 import java.util.Collections;
36 import java.util.HashMap;
37 import java.util.LinkedList;
38 import java.util.Map;
39 import java.util.Map.Entry;
40 import java.util.stream.Collectors;
41
42
43
44
45 public final class SQLServerMetaDataLoader implements DialectMetaDataLoader {
46
47 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,"
48 + " col.collation_name AS COLLATION_NAME, col.column_id, is_identity AS IS_IDENTITY, col.is_nullable AS IS_NULLABLE, %s"
49 + " (SELECT TOP 1 ind.is_primary_key FROM sys.index_columns ic LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id"
50 + " 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"
51 + " 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";
52
53 private static final String ORDER_BY_COLUMN_ID = " ORDER BY col.column_id";
54
55 private static final String TABLE_META_DATA_SQL = TABLE_META_DATA_SQL_NO_ORDER + ORDER_BY_COLUMN_ID;
56
57 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;
58
59 private static final String INDEX_META_DATA_SQL = "SELECT idx.name AS INDEX_NAME, obj.name AS TABLE_NAME, col.name AS COLUMN_NAME,"
60 + " idx.is_unique AS IS_UNIQUE FROM sys.indexes idx"
61 + " LEFT JOIN sys.objects obj ON idx.object_id = obj.object_id"
62 + " LEFT JOIN sys.columns col ON obj.object_id = col.object_id"
63 + " WHERE idx.index_id NOT IN (0, 255) AND obj.name IN (%s) ORDER BY idx.index_id";
64
65 private static final int HIDDEN_COLUMN_START_MAJOR_VERSION = 15;
66
67 @Override
68 public Collection<SchemaMetaData> load(final MetaDataLoaderMaterial material) throws SQLException {
69 Collection<TableMetaData> tableMetaDataList = new LinkedList<>();
70 Map<String, Collection<ColumnMetaData>> columnMetaDataMap = loadColumnMetaDataMap(material.getDataSource(), material.getActualTableNames());
71 if (!columnMetaDataMap.isEmpty()) {
72 Map<String, Collection<IndexMetaData>> indexMetaDataMap = loadIndexMetaData(material.getDataSource(), columnMetaDataMap.keySet());
73 for (Entry<String, Collection<ColumnMetaData>> entry : columnMetaDataMap.entrySet()) {
74 Collection<IndexMetaData> indexMetaDataList = indexMetaDataMap.getOrDefault(entry.getKey(), Collections.emptyList());
75 tableMetaDataList.add(new TableMetaData(entry.getKey(), entry.getValue(), indexMetaDataList, Collections.emptyList()));
76 }
77 }
78 return Collections.singleton(new SchemaMetaData(material.getDefaultSchemaName(), tableMetaDataList));
79 }
80
81 private Map<String, Collection<ColumnMetaData>> loadColumnMetaDataMap(final DataSource dataSource, final Collection<String> tables) throws SQLException {
82 Map<String, Collection<ColumnMetaData>> result = new HashMap<>();
83 try (
84 Connection connection = dataSource.getConnection();
85 PreparedStatement preparedStatement = connection.prepareStatement(getTableMetaDataSQL(tables, connection.getMetaData()))) {
86 Map<String, Integer> dataTypes = new DataTypeLoader().load(connection.getMetaData(), getType());
87 try (ResultSet resultSet = preparedStatement.executeQuery()) {
88 while (resultSet.next()) {
89 String tableName = resultSet.getString("TABLE_NAME");
90 ColumnMetaData columnMetaData = loadColumnMetaData(dataTypes, resultSet, connection.getMetaData());
91 if (!result.containsKey(tableName)) {
92 result.put(tableName, new LinkedList<>());
93 }
94 result.get(tableName).add(columnMetaData);
95 }
96 }
97 }
98 return result;
99 }
100
101 private ColumnMetaData loadColumnMetaData(final Map<String, Integer> dataTypeMap, final ResultSet resultSet, final DatabaseMetaData databaseMetaData) throws SQLException {
102 String columnName = resultSet.getString("COLUMN_NAME");
103 String dataType = resultSet.getString("DATA_TYPE");
104 String collationName = resultSet.getString("COLLATION_NAME");
105 boolean primaryKey = "1".equals(resultSet.getString("IS_PRIMARY_KEY"));
106 boolean generated = "1".equals(resultSet.getString("IS_IDENTITY"));
107 boolean caseSensitive = null != collationName && collationName.contains("_CS");
108 boolean isVisible = !(versionContainsHiddenColumn(databaseMetaData) && "1".equals(resultSet.getString("IS_HIDDEN")));
109 boolean isNullable = "1".equals(resultSet.getString("IS_NULLABLE"));
110 return new ColumnMetaData(columnName, dataTypeMap.get(dataType), primaryKey, generated, caseSensitive, isVisible, false, isNullable);
111 }
112
113 private String getTableMetaDataSQL(final Collection<String> tables, final DatabaseMetaData databaseMetaData) throws SQLException {
114 StringBuilder stringBuilder = new StringBuilder(24);
115 if (versionContainsHiddenColumn(databaseMetaData)) {
116 stringBuilder.append("is_hidden AS IS_HIDDEN,");
117 }
118 String hiddenFlag = stringBuilder.toString();
119 return tables.isEmpty() ? String.format(TABLE_META_DATA_SQL, hiddenFlag)
120 : String.format(TABLE_META_DATA_SQL_IN_TABLES, hiddenFlag, tables.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
121 }
122
123 private boolean versionContainsHiddenColumn(final DatabaseMetaData databaseMetaData) throws SQLException {
124 return databaseMetaData.getDatabaseMajorVersion() >= HIDDEN_COLUMN_START_MAJOR_VERSION;
125 }
126
127 private Map<String, Collection<IndexMetaData>> loadIndexMetaData(final DataSource dataSource, final Collection<String> tableNames) throws SQLException {
128 Map<String, Map<String, IndexMetaData>> tableToIndex = new HashMap<>();
129 try (
130 Connection connection = dataSource.getConnection();
131 PreparedStatement preparedStatement = connection.prepareStatement(getIndexMetaDataSQL(tableNames))) {
132 try (ResultSet resultSet = preparedStatement.executeQuery()) {
133 while (resultSet.next()) {
134 String indexName = resultSet.getString("INDEX_NAME");
135 String tableName = resultSet.getString("TABLE_NAME");
136 if (!tableToIndex.containsKey(tableName)) {
137 tableToIndex.put(tableName, new HashMap<>());
138 }
139 Map<String, IndexMetaData> indexMap = tableToIndex.get(tableName);
140 if (indexMap.containsKey(indexName)) {
141 indexMap.get(indexName).getColumns().add(resultSet.getString("COLUMN_NAME"));
142 } else {
143 IndexMetaData indexMetaData = new IndexMetaData(indexName);
144 indexMetaData.getColumns().add(resultSet.getString("COLUMN_NAME"));
145 indexMetaData.setUnique("1".equals(resultSet.getString("IS_UNIQUE")));
146 indexMap.put(indexName, indexMetaData);
147 }
148 }
149 }
150 }
151 Map<String, Collection<IndexMetaData>> result = new HashMap<>(tableToIndex.size(), 1);
152 for (Entry<String, Map<String, IndexMetaData>> each : tableToIndex.entrySet()) {
153 result.put(each.getKey(), each.getValue().values());
154 }
155 return result;
156 }
157
158 private String getIndexMetaDataSQL(final Collection<String> tableNames) {
159 return String.format(INDEX_META_DATA_SQL, tableNames.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
160 }
161
162 @Override
163 public String getDatabaseType() {
164 return "SQLServer";
165 }
166 }