1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 package org.apache.shardingsphere.infra.database.oracle.metadata.data.loader;
19
20 import com.google.common.collect.Lists;
21 import org.apache.shardingsphere.infra.database.core.metadata.data.loader.DialectMetaDataLoader;
22 import org.apache.shardingsphere.infra.database.core.metadata.data.loader.MetaDataLoaderConnection;
23 import org.apache.shardingsphere.infra.database.core.metadata.data.loader.MetaDataLoaderMaterial;
24 import org.apache.shardingsphere.infra.database.core.metadata.data.model.ColumnMetaData;
25 import org.apache.shardingsphere.infra.database.core.metadata.data.model.IndexMetaData;
26 import org.apache.shardingsphere.infra.database.core.metadata.data.model.SchemaMetaData;
27 import org.apache.shardingsphere.infra.database.core.metadata.data.model.TableMetaData;
28 import org.apache.shardingsphere.infra.database.core.metadata.database.datatype.DataTypeLoader;
29 import org.apache.shardingsphere.infra.database.core.metadata.database.enums.TableType;
30 import org.apache.shardingsphere.infra.database.core.type.DatabaseType;
31 import org.apache.shardingsphere.infra.spi.type.typed.TypedSPILoader;
32
33 import java.sql.Connection;
34 import java.sql.DatabaseMetaData;
35 import java.sql.PreparedStatement;
36 import java.sql.ResultSet;
37 import java.sql.SQLException;
38 import java.sql.Types;
39 import java.util.ArrayList;
40 import java.util.Collection;
41 import java.util.Collections;
42 import java.util.HashMap;
43 import java.util.LinkedList;
44 import java.util.List;
45 import java.util.Map;
46 import java.util.Map.Entry;
47 import java.util.Optional;
48 import java.util.stream.Collectors;
49
50
51
52
53 public final class OracleMetaDataLoader implements DialectMetaDataLoader {
54
55 private static final String TABLE_META_DATA_SQL_NO_ORDER =
56 "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, NULLABLE, DATA_TYPE, COLUMN_ID, HIDDEN_COLUMN %s FROM ALL_TAB_COLS WHERE OWNER = ?";
57
58 private static final String ORDER_BY_COLUMN_ID = " ORDER BY COLUMN_ID";
59
60 private static final String TABLE_META_DATA_SQL = TABLE_META_DATA_SQL_NO_ORDER + ORDER_BY_COLUMN_ID;
61
62 private static final String TABLE_META_DATA_SQL_IN_TABLES = TABLE_META_DATA_SQL_NO_ORDER + " AND TABLE_NAME IN (%s)" + ORDER_BY_COLUMN_ID;
63
64 private static final String VIEW_META_DATA_SQL = "SELECT VIEW_NAME FROM ALL_VIEWS WHERE OWNER = ? AND VIEW_NAME IN (%s)";
65
66 private static final String INDEX_META_DATA_SQL = "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, UNIQUENESS FROM ALL_INDEXES WHERE OWNER = ? AND TABLE_NAME IN (%s)";
67
68 private static final String PRIMARY_KEY_META_DATA_SQL = "SELECT A.OWNER AS TABLE_SCHEMA, A.TABLE_NAME AS TABLE_NAME, B.COLUMN_NAME AS COLUMN_NAME FROM ALL_CONSTRAINTS A INNER JOIN"
69 + " ALL_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'P' AND A.OWNER = '%s'";
70
71 private static final String PRIMARY_KEY_META_DATA_SQL_IN_TABLES = PRIMARY_KEY_META_DATA_SQL + " AND A.TABLE_NAME IN (%s)";
72
73 private static final String INDEX_COLUMN_META_DATA_SQL = "SELECT COLUMN_NAME FROM ALL_IND_COLUMNS WHERE INDEX_OWNER = ? AND TABLE_NAME = ? AND INDEX_NAME = ?";
74
75 private static final int COLLATION_START_MAJOR_VERSION = 12;
76
77 private static final int COLLATION_START_MINOR_VERSION = 2;
78
79 private static final int IDENTITY_COLUMN_START_MINOR_VERSION = 1;
80
81 private static final int MAX_EXPRESSION_SIZE = 1000;
82
83 @Override
84 public Collection<SchemaMetaData> load(final MetaDataLoaderMaterial material) throws SQLException {
85 Collection<TableMetaData> tableMetaDataList = new LinkedList<>();
86 try (Connection connection = new MetaDataLoaderConnection(TypedSPILoader.getService(DatabaseType.class, "Oracle"), material.getDataSource().getConnection())) {
87 tableMetaDataList.addAll(getTableMetaDataList(connection, connection.getSchema(), material.getActualTableNames()));
88 }
89 return Collections.singletonList(new SchemaMetaData(material.getDefaultSchemaName(), tableMetaDataList));
90 }
91
92 private Collection<TableMetaData> getTableMetaDataList(final Connection connection, final String schema, final Collection<String> tableNames) throws SQLException {
93 Collection<String> viewNames = new LinkedList<>();
94 Map<String, Collection<ColumnMetaData>> columnMetaDataMap = new HashMap<>(tableNames.size(), 1F);
95 Map<String, Collection<IndexMetaData>> indexMetaDataMap = new HashMap<>(tableNames.size(), 1F);
96 for (List<String> each : Lists.partition(new ArrayList<>(tableNames), MAX_EXPRESSION_SIZE)) {
97 viewNames.addAll(loadViewNames(connection, each, schema));
98 columnMetaDataMap.putAll(loadColumnMetaDataMap(connection, each, schema));
99 indexMetaDataMap.putAll(loadIndexMetaData(connection, each, schema));
100 }
101 Collection<TableMetaData> result = new LinkedList<>();
102 for (Entry<String, Collection<ColumnMetaData>> entry : columnMetaDataMap.entrySet()) {
103 result.add(new TableMetaData(entry.getKey(), entry.getValue(), indexMetaDataMap.getOrDefault(entry.getKey(), Collections.emptyList()), Collections.emptyList(),
104 viewNames.contains(entry.getKey()) ? TableType.VIEW : TableType.TABLE));
105 }
106 return result;
107 }
108
109 private Collection<String> loadViewNames(final Connection connection, final Collection<String> tables, final String schema) throws SQLException {
110 Collection<String> result = new LinkedList<>();
111 try (PreparedStatement preparedStatement = connection.prepareStatement(getViewMetaDataSQL(tables))) {
112 preparedStatement.setString(1, schema);
113 try (ResultSet resultSet = preparedStatement.executeQuery()) {
114 while (resultSet.next()) {
115 result.add(resultSet.getString(1));
116 }
117 }
118 }
119 return result;
120 }
121
122 private String getViewMetaDataSQL(final Collection<String> tableNames) {
123 return String.format(VIEW_META_DATA_SQL, tableNames.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
124 }
125
126 private Map<String, Collection<ColumnMetaData>> loadColumnMetaDataMap(final Connection connection, final Collection<String> tables, final String schema) throws SQLException {
127 Map<String, Collection<ColumnMetaData>> result = new HashMap<>(tables.size(), 1F);
128 try (PreparedStatement preparedStatement = connection.prepareStatement(getTableMetaDataSQL(tables, connection.getMetaData()))) {
129 Map<String, Integer> dataTypes = new DataTypeLoader().load(connection.getMetaData(), getType());
130 Map<String, Collection<String>> tablePrimaryKeys = loadTablePrimaryKeys(connection, tables);
131 preparedStatement.setString(1, schema);
132 try (ResultSet resultSet = preparedStatement.executeQuery()) {
133 while (resultSet.next()) {
134 String tableName = resultSet.getString("TABLE_NAME");
135 ColumnMetaData columnMetaData = loadColumnMetaData(dataTypes, resultSet, tablePrimaryKeys.getOrDefault(tableName, Collections.emptyList()), connection.getMetaData());
136 if (!result.containsKey(tableName)) {
137 result.put(tableName, new LinkedList<>());
138 }
139 result.get(tableName).add(columnMetaData);
140 }
141 }
142 }
143 return result;
144 }
145
146 private ColumnMetaData loadColumnMetaData(final Map<String, Integer> dataTypeMap, final ResultSet resultSet, final Collection<String> primaryKeys,
147 final DatabaseMetaData databaseMetaData) throws SQLException {
148 String columnName = resultSet.getString("COLUMN_NAME");
149 String dataType = getOriginalDataType(resultSet.getString("DATA_TYPE"));
150 boolean primaryKey = primaryKeys.contains(columnName);
151 boolean generated = versionContainsIdentityColumn(databaseMetaData) && "YES".equals(resultSet.getString("IDENTITY_COLUMN"));
152
153 String collation = versionContainsCollation(databaseMetaData) ? resultSet.getString("COLLATION") : null;
154 boolean caseSensitive = null != collation && collation.endsWith("_CS");
155 boolean isVisible = "NO".equals(resultSet.getString("HIDDEN_COLUMN"));
156 boolean nullable = "Y".equals(resultSet.getString("NULLABLE"));
157 Integer dataTypeCode = Optional.ofNullable(dataTypeMap.get(dataType)).orElse(Types.OTHER);
158 return new ColumnMetaData(columnName, dataTypeCode, primaryKey, generated, caseSensitive, isVisible, false, nullable);
159 }
160
161 private String getOriginalDataType(final String dataType) {
162 int index = dataType.indexOf('(');
163 if (index > 0) {
164 return dataType.substring(0, index);
165 }
166 return dataType;
167 }
168
169 private String getTableMetaDataSQL(final Collection<String> tables, final DatabaseMetaData databaseMetaData) throws SQLException {
170 StringBuilder stringBuilder = new StringBuilder(28);
171 if (versionContainsIdentityColumn(databaseMetaData)) {
172 stringBuilder.append(", IDENTITY_COLUMN");
173 }
174 if (versionContainsCollation(databaseMetaData)) {
175 stringBuilder.append(", COLLATION");
176 }
177 String collation = stringBuilder.toString();
178 return tables.isEmpty() ? String.format(TABLE_META_DATA_SQL, collation)
179 : String.format(TABLE_META_DATA_SQL_IN_TABLES, collation, tables.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
180 }
181
182 private boolean versionContainsCollation(final DatabaseMetaData databaseMetaData) throws SQLException {
183 return databaseMetaData.getDatabaseMajorVersion() >= COLLATION_START_MAJOR_VERSION && databaseMetaData.getDatabaseMinorVersion() >= COLLATION_START_MINOR_VERSION;
184 }
185
186 private boolean versionContainsIdentityColumn(final DatabaseMetaData databaseMetaData) throws SQLException {
187 return databaseMetaData.getDatabaseMajorVersion() >= COLLATION_START_MAJOR_VERSION && databaseMetaData.getDatabaseMinorVersion() >= IDENTITY_COLUMN_START_MINOR_VERSION;
188 }
189
190 private Map<String, Collection<IndexMetaData>> loadIndexMetaData(final Connection connection, final Collection<String> tableNames, final String schema) throws SQLException {
191 Map<String, Collection<IndexMetaData>> result = new HashMap<>(tableNames.size(), 1F);
192 try (PreparedStatement preparedStatement = connection.prepareStatement(getIndexMetaDataSQL(tableNames))) {
193 preparedStatement.setString(1, schema);
194 try (ResultSet resultSet = preparedStatement.executeQuery()) {
195 while (resultSet.next()) {
196 String indexName = resultSet.getString("INDEX_NAME");
197 String tableName = resultSet.getString("TABLE_NAME");
198 boolean isUnique = "UNIQUE".equals(resultSet.getString("UNIQUENESS"));
199 if (!result.containsKey(tableName)) {
200 result.put(tableName, new LinkedList<>());
201 }
202 IndexMetaData indexMetaData = new IndexMetaData(indexName);
203 indexMetaData.setUnique(isUnique);
204 indexMetaData.getColumns().addAll(loadIndexColumnNames(connection, tableName, indexName));
205 result.get(tableName).add(indexMetaData);
206 }
207 }
208 }
209 return result;
210 }
211
212 private List<String> loadIndexColumnNames(final Connection connection, final String tableName, final String indexName) throws SQLException {
213 try (PreparedStatement preparedStatement = connection.prepareStatement(INDEX_COLUMN_META_DATA_SQL)) {
214 preparedStatement.setString(1, connection.getSchema());
215 preparedStatement.setString(2, tableName);
216 preparedStatement.setString(3, indexName);
217 List<String> result = new LinkedList<>();
218 ResultSet resultSet = preparedStatement.executeQuery();
219 while (resultSet.next()) {
220 result.add(resultSet.getString("COLUMN_NAME"));
221 }
222 return result;
223 }
224 }
225
226 private String getIndexMetaDataSQL(final Collection<String> tableNames) {
227
228 return String.format(INDEX_META_DATA_SQL, tableNames.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
229 }
230
231 private Map<String, Collection<String>> loadTablePrimaryKeys(final Connection connection, final Collection<String> tableNames) throws SQLException {
232 Map<String, Collection<String>> result = new HashMap<>();
233 try (PreparedStatement preparedStatement = connection.prepareStatement(getPrimaryKeyMetaDataSQL(connection.getSchema(), tableNames))) {
234 try (ResultSet resultSet = preparedStatement.executeQuery()) {
235 while (resultSet.next()) {
236 String columnName = resultSet.getString("COLUMN_NAME");
237 String tableName = resultSet.getString("TABLE_NAME");
238 result.computeIfAbsent(tableName, k -> new LinkedList<>()).add(columnName);
239 }
240 }
241 }
242 return result;
243 }
244
245 private String getPrimaryKeyMetaDataSQL(final String schemaName, final Collection<String> tables) {
246 return tables.isEmpty() ? String.format(PRIMARY_KEY_META_DATA_SQL, schemaName)
247 : String.format(PRIMARY_KEY_META_DATA_SQL_IN_TABLES, schemaName, tables.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
248 }
249
250 @Override
251 public String getDatabaseType() {
252 return "Oracle";
253 }
254 }