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