1 | /* |
---|
2 | Copyright (c) 2005, 2012, Oracle and/or its affiliates. All rights reserved. |
---|
3 | |
---|
4 | The MySQL Connector/J is licensed under the terms of the GPLv2 |
---|
5 | <http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most MySQL Connectors. |
---|
6 | There are special exceptions to the terms and conditions of the GPLv2 as it is applied to |
---|
7 | this software, see the FLOSS License Exception |
---|
8 | <http://www.mysql.com/about/legal/licensing/foss-exception.html>. |
---|
9 | |
---|
10 | This program is free software; you can redistribute it and/or modify it under the terms |
---|
11 | of the GNU General Public License as published by the Free Software Foundation; version 2 |
---|
12 | of the License. |
---|
13 | |
---|
14 | This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; |
---|
15 | without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
---|
16 | See the GNU General Public License for more details. |
---|
17 | |
---|
18 | You should have received a copy of the GNU General Public License along with this |
---|
19 | program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth |
---|
20 | Floor, Boston, MA 02110-1301 USA |
---|
21 | |
---|
22 | */ |
---|
23 | package com.mysql.jdbc; |
---|
24 | |
---|
25 | import java.sql.ResultSet; |
---|
26 | import java.sql.SQLException; |
---|
27 | import java.sql.Types; |
---|
28 | import java.util.List; |
---|
29 | |
---|
30 | /** |
---|
31 | * DatabaseMetaData implementation that uses INFORMATION_SCHEMA available in |
---|
32 | * MySQL-5.0 and newer. |
---|
33 | * |
---|
34 | * The majority of the queries in this code were built for Connector/OO.org by |
---|
35 | * Georg Richter (georg_at_mysql.com). |
---|
36 | */ |
---|
37 | public class DatabaseMetaDataUsingInfoSchema extends DatabaseMetaData { |
---|
38 | |
---|
39 | private boolean hasReferentialConstraintsView; |
---|
40 | private final boolean hasParametersView; |
---|
41 | |
---|
42 | protected DatabaseMetaDataUsingInfoSchema(MySQLConnection connToSet, |
---|
43 | String databaseToSet) throws SQLException { |
---|
44 | super(connToSet, databaseToSet); |
---|
45 | |
---|
46 | this.hasReferentialConstraintsView = |
---|
47 | this.conn.versionMeetsMinimum(5, 1, 10); |
---|
48 | |
---|
49 | ResultSet rs = null; |
---|
50 | |
---|
51 | try { |
---|
52 | rs = super.getTables("INFORMATION_SCHEMA", null, "PARAMETERS", new String[0]); |
---|
53 | |
---|
54 | this.hasParametersView = rs.next(); |
---|
55 | } finally { |
---|
56 | if (rs != null) { |
---|
57 | rs.close(); |
---|
58 | } |
---|
59 | } |
---|
60 | } |
---|
61 | |
---|
62 | private ResultSet executeMetadataQuery(java.sql.PreparedStatement pStmt) |
---|
63 | throws SQLException { |
---|
64 | ResultSet rs = pStmt.executeQuery(); |
---|
65 | ((com.mysql.jdbc.ResultSetInternalMethods) rs).setOwningStatement(null); |
---|
66 | |
---|
67 | return rs; |
---|
68 | } |
---|
69 | |
---|
70 | /** |
---|
71 | * Get a description of the access rights for a table's columns. |
---|
72 | * <P> |
---|
73 | * Only privileges matching the column name criteria are returned. They are |
---|
74 | * ordered by COLUMN_NAME and PRIVILEGE. |
---|
75 | * </p> |
---|
76 | * <P> |
---|
77 | * Each privilige description has the following columns: |
---|
78 | * <OL> |
---|
79 | * <li> <B>TABLE_CAT</B> String => table catalog (may be null) </li> |
---|
80 | * <li> <B>TABLE_SCHEM</B> String => table schema (may be null) </li> |
---|
81 | * <li> <B>TABLE_NAME</B> String => table name </li> |
---|
82 | * <li> <B>COLUMN_NAME</B> String => column name </li> |
---|
83 | * <li> <B>GRANTOR</B> => grantor of access (may be null) </li> |
---|
84 | * <li> <B>GRANTEE</B> String => grantee of access </li> |
---|
85 | * <li> <B>PRIVILEGE</B> String => name of access (SELECT, INSERT, UPDATE, |
---|
86 | * REFRENCES, ...) </li> |
---|
87 | * <li> <B>IS_GRANTABLE</B> String => "YES" if grantee is permitted to |
---|
88 | * grant to others; "NO" if not; null if unknown </li> |
---|
89 | * </ol> |
---|
90 | * </p> |
---|
91 | * |
---|
92 | * @param catalog |
---|
93 | * a catalog name; "" retrieves those without a catalog |
---|
94 | * @param schema |
---|
95 | * a schema name; "" retrieves those without a schema |
---|
96 | * @param table |
---|
97 | * a table name |
---|
98 | * @param columnNamePattern |
---|
99 | * a column name pattern |
---|
100 | * @return ResultSet each row is a column privilege description |
---|
101 | * @throws SQLException |
---|
102 | * if a database access error occurs |
---|
103 | * @see #getSearchStringEscape |
---|
104 | */ |
---|
105 | public java.sql.ResultSet getColumnPrivileges(String catalog, |
---|
106 | String schema, String table, String columnNamePattern) |
---|
107 | throws SQLException { |
---|
108 | if (columnNamePattern == null) { |
---|
109 | if (this.conn.getNullNamePatternMatchesAll()) { |
---|
110 | columnNamePattern = "%"; |
---|
111 | } else { |
---|
112 | throw SQLError.createSQLException( |
---|
113 | "Column name pattern can not be NULL or empty.", |
---|
114 | SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); |
---|
115 | } |
---|
116 | } |
---|
117 | |
---|
118 | if (catalog == null) { |
---|
119 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
120 | catalog = this.database; |
---|
121 | } |
---|
122 | } |
---|
123 | |
---|
124 | String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME," |
---|
125 | +"COLUMN_NAME, NULL AS GRANTOR, GRANTEE, PRIVILEGE_TYPE AS PRIVILEGE, IS_GRANTABLE FROM " |
---|
126 | + "INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE " |
---|
127 | + "TABLE_SCHEMA LIKE ? AND " |
---|
128 | + "TABLE_NAME =? AND COLUMN_NAME LIKE ? ORDER BY " |
---|
129 | + "COLUMN_NAME, PRIVILEGE_TYPE"; |
---|
130 | |
---|
131 | java.sql.PreparedStatement pStmt = null; |
---|
132 | |
---|
133 | try { |
---|
134 | pStmt = prepareMetaDataSafeStatement(sql); |
---|
135 | |
---|
136 | if (catalog != null) { |
---|
137 | pStmt.setString(1, catalog); |
---|
138 | } else { |
---|
139 | pStmt.setString(1, "%"); |
---|
140 | } |
---|
141 | |
---|
142 | pStmt.setString(2, table); |
---|
143 | pStmt.setString(3, columnNamePattern); |
---|
144 | |
---|
145 | ResultSet rs = executeMetadataQuery(pStmt); |
---|
146 | ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(new Field[] { |
---|
147 | new Field("", "TABLE_CAT", Types.CHAR, 64), |
---|
148 | new Field("", "TABLE_SCHEM", Types.CHAR, 1), |
---|
149 | new Field("", "TABLE_NAME", Types.CHAR, 64), |
---|
150 | new Field("", "COLUMN_NAME", Types.CHAR, 64), |
---|
151 | new Field("", "GRANTOR", Types.CHAR, 77), |
---|
152 | new Field("", "GRANTEE", Types.CHAR, 77), |
---|
153 | new Field("", "PRIVILEGE", Types.CHAR, 64), |
---|
154 | new Field("", "IS_GRANTABLE", Types.CHAR, 3)}); |
---|
155 | |
---|
156 | return rs; |
---|
157 | } finally { |
---|
158 | if (pStmt != null) { |
---|
159 | pStmt.close(); |
---|
160 | } |
---|
161 | } |
---|
162 | } |
---|
163 | |
---|
164 | /** |
---|
165 | * Get a description of table columns available in a catalog. |
---|
166 | * <P> |
---|
167 | * Only column descriptions matching the catalog, schema, table and column |
---|
168 | * name criteria are returned. They are ordered by TABLE_SCHEM, TABLE_NAME |
---|
169 | * and ORDINAL_POSITION. |
---|
170 | * </p> |
---|
171 | * <P> |
---|
172 | * Each column description has the following columns: |
---|
173 | * <OL> |
---|
174 | * <li> <B>TABLE_CAT</B> String => table catalog (may be null) </li> |
---|
175 | * <li> <B>TABLE_SCHEM</B> String => table schema (may be null) </li> |
---|
176 | * <li> <B>TABLE_NAME</B> String => table name </li> |
---|
177 | * <li> <B>COLUMN_NAME</B> String => column name </li> |
---|
178 | * <li> <B>DATA_TYPE</B> short => SQL type from java.sql.Types </li> |
---|
179 | * <li> <B>TYPE_NAME</B> String => Data source dependent type name </li> |
---|
180 | * <li> <B>COLUMN_SIZE</B> int => column size. For char or date types this |
---|
181 | * is the maximum number of characters, for numeric or decimal types this is |
---|
182 | * precision. </li> |
---|
183 | * <li> <B>BUFFER_LENGTH</B> is not used. </li> |
---|
184 | * <li> <B>DECIMAL_DIGITS</B> int => the number of fractional digits </li> |
---|
185 | * <li> <B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2) </li> |
---|
186 | * <li> <B>NULLABLE</B> int => is NULL allowed? |
---|
187 | * <UL> |
---|
188 | * <li> columnNoNulls - might not allow NULL values </li> |
---|
189 | * <li> columnNullable - definitely allows NULL values </li> |
---|
190 | * <li> columnNullableUnknown - nullability unknown </li> |
---|
191 | * </ul> |
---|
192 | * </li> |
---|
193 | * <li> <B>REMARKS</B> String => comment describing column (may be null) |
---|
194 | * </li> |
---|
195 | * <li> <B>COLUMN_DEF</B> String => default value (may be null) </li> |
---|
196 | * <li> <B>SQL_DATA_TYPE</B> int => unused </li> |
---|
197 | * <li> <B>SQL_DATETIME_SUB</B> int => unused </li> |
---|
198 | * <li> <B>CHAR_OCTET_LENGTH</B> int => for char types the maximum number |
---|
199 | * of bytes in the column </li> |
---|
200 | * <li> <B>ORDINAL_POSITION</B> int => index of column in table (starting |
---|
201 | * at 1) </li> |
---|
202 | * <li> <B>IS_NULLABLE</B> String => "NO" means column definitely does not |
---|
203 | * allow NULL values; "YES" means the column might allow NULL values. An |
---|
204 | * empty string means nobody knows. </li> |
---|
205 | * </ol> |
---|
206 | * </p> |
---|
207 | */ |
---|
208 | public ResultSet getColumns(String catalog, String schemaPattern, |
---|
209 | String tableName, String columnNamePattern) throws SQLException { |
---|
210 | if (columnNamePattern == null) { |
---|
211 | if (this.conn.getNullNamePatternMatchesAll()) { |
---|
212 | columnNamePattern = "%"; |
---|
213 | } else { |
---|
214 | throw SQLError.createSQLException( |
---|
215 | "Column name pattern can not be NULL or empty.", |
---|
216 | SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); |
---|
217 | } |
---|
218 | } |
---|
219 | |
---|
220 | if (catalog == null) { |
---|
221 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
222 | catalog = this.database; |
---|
223 | } |
---|
224 | } |
---|
225 | |
---|
226 | StringBuffer sqlBuf = new StringBuffer("SELECT " |
---|
227 | + "TABLE_SCHEMA AS TABLE_CAT, " + "NULL AS TABLE_SCHEM," |
---|
228 | + "TABLE_NAME," + "COLUMN_NAME,"); |
---|
229 | MysqlDefs.appendJdbcTypeMappingQuery(sqlBuf, "DATA_TYPE"); |
---|
230 | |
---|
231 | sqlBuf.append(" AS DATA_TYPE, "); |
---|
232 | |
---|
233 | if (conn.getCapitalizeTypeNames()) { |
---|
234 | sqlBuf.append("UPPER(CASE WHEN LOCATE('unsigned', COLUMN_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 AND LOCATE('set', DATA_TYPE) <> 1 AND LOCATE('enum', DATA_TYPE) <> 1 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END) AS TYPE_NAME,"); |
---|
235 | } else { |
---|
236 | sqlBuf.append("CASE WHEN LOCATE('unsigned', COLUMN_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 AND LOCATE('set', DATA_TYPE) <> 1 AND LOCATE('enum', DATA_TYPE) <> 1 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END AS TYPE_NAME,"); |
---|
237 | } |
---|
238 | |
---|
239 | sqlBuf |
---|
240 | .append("CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > " |
---|
241 | + Integer.MAX_VALUE + " THEN " + Integer.MAX_VALUE + " ELSE CHARACTER_MAXIMUM_LENGTH END AS COLUMN_SIZE, " |
---|
242 | + MysqlIO.getMaxBuf() + " AS BUFFER_LENGTH," |
---|
243 | + "NUMERIC_SCALE AS DECIMAL_DIGITS," |
---|
244 | + "10 AS NUM_PREC_RADIX," |
---|
245 | + "CASE WHEN IS_NULLABLE='NO' THEN " + columnNoNulls + " ELSE CASE WHEN IS_NULLABLE='YES' THEN " + columnNullable + " ELSE " + columnNullableUnknown + " END END AS NULLABLE," |
---|
246 | + "COLUMN_COMMENT AS REMARKS," |
---|
247 | + "COLUMN_DEFAULT AS COLUMN_DEF," |
---|
248 | + "0 AS SQL_DATA_TYPE," |
---|
249 | + "0 AS SQL_DATETIME_SUB," |
---|
250 | + "CASE WHEN CHARACTER_OCTET_LENGTH > " + Integer.MAX_VALUE + " THEN " + Integer.MAX_VALUE + " ELSE CHARACTER_OCTET_LENGTH END AS CHAR_OCTET_LENGTH," |
---|
251 | + "ORDINAL_POSITION," |
---|
252 | + "IS_NULLABLE," |
---|
253 | + "NULL AS SCOPE_CATALOG," |
---|
254 | + "NULL AS SCOPE_SCHEMA," |
---|
255 | + "NULL AS SCOPE_TABLE," |
---|
256 | + "NULL AS SOURCE_DATA_TYPE," |
---|
257 | + "IF (EXTRA LIKE '%auto_increment%','YES','NO') AS IS_AUTOINCREMENT " |
---|
258 | + "FROM INFORMATION_SCHEMA.COLUMNS WHERE "); |
---|
259 | |
---|
260 | final boolean operatingOnInformationSchema = "information_schema".equalsIgnoreCase(catalog); |
---|
261 | |
---|
262 | if (catalog != null) { |
---|
263 | if ((operatingOnInformationSchema) || ((StringUtils.indexOfIgnoreCase(0, catalog, "%") == -1) |
---|
264 | && (StringUtils.indexOfIgnoreCase(0, catalog, "_") == -1))) { |
---|
265 | sqlBuf.append("TABLE_SCHEMA = ? AND "); |
---|
266 | } else { |
---|
267 | sqlBuf.append("TABLE_SCHEMA LIKE ? AND "); |
---|
268 | } |
---|
269 | |
---|
270 | } else { |
---|
271 | sqlBuf.append("TABLE_SCHEMA LIKE ? AND "); |
---|
272 | } |
---|
273 | |
---|
274 | if (tableName != null) { |
---|
275 | if ((StringUtils.indexOfIgnoreCase(0, tableName, "%") == -1) |
---|
276 | && (StringUtils.indexOfIgnoreCase(0, tableName, "_") == -1)) { |
---|
277 | sqlBuf.append("TABLE_NAME = ? AND "); |
---|
278 | } else { |
---|
279 | sqlBuf.append("TABLE_NAME LIKE ? AND "); |
---|
280 | } |
---|
281 | |
---|
282 | } else { |
---|
283 | sqlBuf.append("TABLE_NAME LIKE ? AND "); |
---|
284 | } |
---|
285 | |
---|
286 | if ((StringUtils.indexOfIgnoreCase(0, columnNamePattern, "%") == -1) |
---|
287 | && (StringUtils.indexOfIgnoreCase(0, columnNamePattern, "_") == -1)) { |
---|
288 | sqlBuf.append("COLUMN_NAME = ? "); |
---|
289 | } else { |
---|
290 | sqlBuf.append("COLUMN_NAME LIKE ? "); |
---|
291 | } |
---|
292 | sqlBuf.append("ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION"); |
---|
293 | |
---|
294 | java.sql.PreparedStatement pStmt = null; |
---|
295 | |
---|
296 | try { |
---|
297 | pStmt = prepareMetaDataSafeStatement(sqlBuf.toString()); |
---|
298 | |
---|
299 | if (catalog != null) { |
---|
300 | pStmt.setString(1, catalog); |
---|
301 | } else { |
---|
302 | pStmt.setString(1, "%"); |
---|
303 | } |
---|
304 | |
---|
305 | pStmt.setString(2, tableName); |
---|
306 | pStmt.setString(3, columnNamePattern); |
---|
307 | |
---|
308 | ResultSet rs = executeMetadataQuery(pStmt); |
---|
309 | |
---|
310 | ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createColumnsFields()); |
---|
311 | return rs; |
---|
312 | } finally { |
---|
313 | if (pStmt != null) { |
---|
314 | pStmt.close(); |
---|
315 | } |
---|
316 | } |
---|
317 | } |
---|
318 | |
---|
319 | /** |
---|
320 | * Get a description of the foreign key columns in the foreign key table |
---|
321 | * that reference the primary key columns of the primary key table (describe |
---|
322 | * how one table imports another's key.) This should normally return a |
---|
323 | * single foreign key/primary key pair (most tables only import a foreign |
---|
324 | * key from a table once.) They are ordered by FKTABLE_CAT, FKTABLE_SCHEM, |
---|
325 | * FKTABLE_NAME, and KEY_SEQ. |
---|
326 | * <P> |
---|
327 | * Each foreign key column description has the following columns: |
---|
328 | * <OL> |
---|
329 | * <li> <B>PKTABLE_CAT</B> String => primary key table catalog (may be |
---|
330 | * null) </li> |
---|
331 | * <li> <B>PKTABLE_SCHEM</B> String => primary key table schema (may be |
---|
332 | * null) </li> |
---|
333 | * <li> <B>PKTABLE_NAME</B> String => primary key table name </li> |
---|
334 | * <li> <B>PKCOLUMN_NAME</B> String => primary key column name </li> |
---|
335 | * <li> <B>FKTABLE_CAT</B> String => foreign key table catalog (may be |
---|
336 | * null) being exported (may be null) </li> |
---|
337 | * <li> <B>FKTABLE_SCHEM</B> String => foreign key table schema (may be |
---|
338 | * null) being exported (may be null) </li> |
---|
339 | * <li> <B>FKTABLE_NAME</B> String => foreign key table name being exported |
---|
340 | * </li> |
---|
341 | * <li> <B>FKCOLUMN_NAME</B> String => foreign key column name being |
---|
342 | * exported </li> |
---|
343 | * <li> <B>KEY_SEQ</B> short => sequence number within foreign key </li> |
---|
344 | * <li> <B>UPDATE_RULE</B> short => What happens to foreign key when |
---|
345 | * primary is updated: |
---|
346 | * <UL> |
---|
347 | * <li> importedKeyCascade - change imported key to agree with primary key |
---|
348 | * update </li> |
---|
349 | * <li> importedKeyRestrict - do not allow update of primary key if it has |
---|
350 | * been imported </li> |
---|
351 | * <li> importedKeySetNull - change imported key to NULL if its primary key |
---|
352 | * has been updated </li> |
---|
353 | * </ul> |
---|
354 | * </li> |
---|
355 | * <li> <B>DELETE_RULE</B> short => What happens to the foreign key when |
---|
356 | * primary is deleted. |
---|
357 | * <UL> |
---|
358 | * <li> importedKeyCascade - delete rows that import a deleted key </li> |
---|
359 | * <li> importedKeyRestrict - do not allow delete of primary key if it has |
---|
360 | * been imported </li> |
---|
361 | * <li> importedKeySetNull - change imported key to NULL if its primary key |
---|
362 | * has been deleted </li> |
---|
363 | * </ul> |
---|
364 | * </li> |
---|
365 | * <li> <B>FK_NAME</B> String => foreign key identifier (may be null) </li> |
---|
366 | * <li> <B>PK_NAME</B> String => primary key identifier (may be null) </li> |
---|
367 | * </ol> |
---|
368 | * </p> |
---|
369 | * |
---|
370 | * @param primaryCatalog |
---|
371 | * a catalog name; "" retrieves those without a catalog |
---|
372 | * @param primarySchema |
---|
373 | * a schema name pattern; "" retrieves those without a schema |
---|
374 | * @param primaryTable |
---|
375 | * a table name |
---|
376 | * @param foreignCatalog |
---|
377 | * a catalog name; "" retrieves those without a catalog |
---|
378 | * @param foreignSchema |
---|
379 | * a schema name pattern; "" retrieves those without a schema |
---|
380 | * @param foreignTable |
---|
381 | * a table name |
---|
382 | * @return ResultSet each row is a foreign key column description |
---|
383 | * @throws SQLException |
---|
384 | * if a database access error occurs |
---|
385 | */ |
---|
386 | public java.sql.ResultSet getCrossReference(String primaryCatalog, |
---|
387 | String primarySchema, String primaryTable, String foreignCatalog, |
---|
388 | String foreignSchema, String foreignTable) throws SQLException { |
---|
389 | if (primaryTable == null) { |
---|
390 | throw SQLError.createSQLException("Table not specified.", |
---|
391 | SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); |
---|
392 | } |
---|
393 | |
---|
394 | if (primaryCatalog == null) { |
---|
395 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
396 | primaryCatalog = this.database; |
---|
397 | } |
---|
398 | } |
---|
399 | |
---|
400 | if (foreignCatalog == null) { |
---|
401 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
402 | foreignCatalog = this.database; |
---|
403 | } |
---|
404 | } |
---|
405 | |
---|
406 | String sql = "SELECT " |
---|
407 | + "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT," |
---|
408 | + "NULL AS PKTABLE_SCHEM," |
---|
409 | + "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME," |
---|
410 | + "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME," |
---|
411 | + "A.TABLE_SCHEMA AS FKTABLE_CAT," |
---|
412 | + "NULL AS FKTABLE_SCHEM," |
---|
413 | + "A.TABLE_NAME AS FKTABLE_NAME, " |
---|
414 | + "A.COLUMN_NAME AS FKCOLUMN_NAME, " |
---|
415 | + "A.ORDINAL_POSITION AS KEY_SEQ," |
---|
416 | + generateUpdateRuleClause() |
---|
417 | + " AS UPDATE_RULE," |
---|
418 | + generateDeleteRuleClause() |
---|
419 | + " AS DELETE_RULE," |
---|
420 | + "A.CONSTRAINT_NAME AS FK_NAME," |
---|
421 | + "(SELECT CONSTRAINT_NAME FROM" |
---|
422 | + " INFORMATION_SCHEMA.TABLE_CONSTRAINTS" |
---|
423 | + " WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND" |
---|
424 | + " TABLE_NAME = A.REFERENCED_TABLE_NAME AND" |
---|
425 | + " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)" |
---|
426 | + " AS PK_NAME," |
---|
427 | + importedKeyNotDeferrable |
---|
428 | + " AS DEFERRABILITY " |
---|
429 | + "FROM " |
---|
430 | + "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN " |
---|
431 | + "INFORMATION_SCHEMA.TABLE_CONSTRAINTS B " |
---|
432 | + "USING (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) " |
---|
433 | + generateOptionalRefContraintsJoin() |
---|
434 | + "WHERE " |
---|
435 | + "B.CONSTRAINT_TYPE = 'FOREIGN KEY' " |
---|
436 | + "AND A.REFERENCED_TABLE_SCHEMA LIKE ? AND A.REFERENCED_TABLE_NAME=? " |
---|
437 | + "AND A.TABLE_SCHEMA LIKE ? AND A.TABLE_NAME=? " + "ORDER BY " |
---|
438 | + "A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION"; |
---|
439 | |
---|
440 | java.sql.PreparedStatement pStmt = null; |
---|
441 | |
---|
442 | try { |
---|
443 | pStmt = prepareMetaDataSafeStatement(sql); |
---|
444 | if (primaryCatalog != null) { |
---|
445 | pStmt.setString(1, primaryCatalog); |
---|
446 | } else { |
---|
447 | pStmt.setString(1, "%"); |
---|
448 | } |
---|
449 | |
---|
450 | pStmt.setString(2, primaryTable); |
---|
451 | |
---|
452 | if (foreignCatalog != null) { |
---|
453 | pStmt.setString(3, foreignCatalog); |
---|
454 | } else { |
---|
455 | pStmt.setString(3, "%"); |
---|
456 | } |
---|
457 | |
---|
458 | pStmt.setString(4, foreignTable); |
---|
459 | |
---|
460 | ResultSet rs = executeMetadataQuery(pStmt); |
---|
461 | ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createFkMetadataFields()); |
---|
462 | |
---|
463 | return rs; |
---|
464 | } finally { |
---|
465 | if (pStmt != null) { |
---|
466 | pStmt.close(); |
---|
467 | } |
---|
468 | } |
---|
469 | } |
---|
470 | |
---|
471 | /** |
---|
472 | * Get a description of a foreign key columns that reference a table's |
---|
473 | * primary key columns (the foreign keys exported by a table). They are |
---|
474 | * ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ. |
---|
475 | * <P> |
---|
476 | * Each foreign key column description has the following columns: |
---|
477 | * <OL> |
---|
478 | * <li> <B>PKTABLE_CAT</B> String => primary key table catalog (may be |
---|
479 | * null) </li> |
---|
480 | * <li> <B>PKTABLE_SCHEM</B> String => primary key table schema (may be |
---|
481 | * null) </li> |
---|
482 | * <li> <B>PKTABLE_NAME</B> String => primary key table name </li> |
---|
483 | * <li> <B>PKCOLUMN_NAME</B> String => primary key column name </li> |
---|
484 | * <li> <B>FKTABLE_CAT</B> String => foreign key table catalog (may be |
---|
485 | * null) being exported (may be null) </li> |
---|
486 | * <li> <B>FKTABLE_SCHEM</B> String => foreign key table schema (may be |
---|
487 | * null) being exported (may be null) </li> |
---|
488 | * <li> <B>FKTABLE_NAME</B> String => foreign key table name being exported |
---|
489 | * </li> |
---|
490 | * <li> <B>FKCOLUMN_NAME</B> String => foreign key column name being |
---|
491 | * exported </li> |
---|
492 | * <li> <B>KEY_SEQ</B> short => sequence number within foreign key </li> |
---|
493 | * <li> <B>UPDATE_RULE</B> short => What happens to foreign key when |
---|
494 | * primary is updated: |
---|
495 | * <UL> |
---|
496 | * <li> importedKeyCascade - change imported key to agree with primary key |
---|
497 | * update </li> |
---|
498 | * <li> importedKeyRestrict - do not allow update of primary key if it has |
---|
499 | * been imported </li> |
---|
500 | * <li> importedKeySetNull - change imported key to NULL if its primary key |
---|
501 | * has been updated </li> |
---|
502 | * </ul> |
---|
503 | * </li> |
---|
504 | * <li> <B>DELETE_RULE</B> short => What happens to the foreign key when |
---|
505 | * primary is deleted. |
---|
506 | * <UL> |
---|
507 | * <li> importedKeyCascade - delete rows that import a deleted key </li> |
---|
508 | * <li> importedKeyRestrict - do not allow delete of primary key if it has |
---|
509 | * been imported </li> |
---|
510 | * <li> importedKeySetNull - change imported key to NULL if its primary key |
---|
511 | * has been deleted </li> |
---|
512 | * </ul> |
---|
513 | * </li> |
---|
514 | * <li> <B>FK_NAME</B> String => foreign key identifier (may be null) </li> |
---|
515 | * <li> <B>PK_NAME</B> String => primary key identifier (may be null) </li> |
---|
516 | * </ol> |
---|
517 | * </p> |
---|
518 | * |
---|
519 | * @param catalog |
---|
520 | * a catalog name; "" retrieves those without a catalog |
---|
521 | * @param schema |
---|
522 | * a schema name pattern; "" retrieves those without a schema |
---|
523 | * @param table |
---|
524 | * a table name |
---|
525 | * @return ResultSet each row is a foreign key column description |
---|
526 | * @throws SQLException |
---|
527 | * if a database access error occurs |
---|
528 | * @see #getImportedKeys |
---|
529 | */ |
---|
530 | public java.sql.ResultSet getExportedKeys(String catalog, String schema, |
---|
531 | String table) throws SQLException { |
---|
532 | // TODO: Can't determine actions using INFORMATION_SCHEMA yet... |
---|
533 | |
---|
534 | if (table == null) { |
---|
535 | throw SQLError.createSQLException("Table not specified.", |
---|
536 | SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); |
---|
537 | } |
---|
538 | |
---|
539 | if (catalog == null) { |
---|
540 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
541 | catalog = this.database; |
---|
542 | } |
---|
543 | } |
---|
544 | |
---|
545 | //CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION |
---|
546 | |
---|
547 | String sql = "SELECT " |
---|
548 | + "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT," |
---|
549 | + "NULL AS PKTABLE_SCHEM," |
---|
550 | + "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, " |
---|
551 | + "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, " |
---|
552 | + "A.TABLE_SCHEMA AS FKTABLE_CAT," |
---|
553 | + "NULL AS FKTABLE_SCHEM," |
---|
554 | + "A.TABLE_NAME AS FKTABLE_NAME," |
---|
555 | + "A.COLUMN_NAME AS FKCOLUMN_NAME, " |
---|
556 | + "A.ORDINAL_POSITION AS KEY_SEQ," |
---|
557 | + generateUpdateRuleClause() |
---|
558 | + " AS UPDATE_RULE," |
---|
559 | + generateDeleteRuleClause() |
---|
560 | + " AS DELETE_RULE," |
---|
561 | + "A.CONSTRAINT_NAME AS FK_NAME," |
---|
562 | + "(SELECT CONSTRAINT_NAME FROM" |
---|
563 | + " INFORMATION_SCHEMA.TABLE_CONSTRAINTS" |
---|
564 | + " WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND" |
---|
565 | + " TABLE_NAME = A.REFERENCED_TABLE_NAME AND" |
---|
566 | + " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)" |
---|
567 | + " AS PK_NAME," |
---|
568 | + importedKeyNotDeferrable |
---|
569 | + " AS DEFERRABILITY " |
---|
570 | + "FROM " |
---|
571 | + "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN " |
---|
572 | + "INFORMATION_SCHEMA.TABLE_CONSTRAINTS B " |
---|
573 | + "USING (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) " |
---|
574 | + generateOptionalRefContraintsJoin() |
---|
575 | + "WHERE " |
---|
576 | + "B.CONSTRAINT_TYPE = 'FOREIGN KEY' " |
---|
577 | + "AND A.REFERENCED_TABLE_SCHEMA LIKE ? AND A.REFERENCED_TABLE_NAME=? " |
---|
578 | + "ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION"; |
---|
579 | |
---|
580 | java.sql.PreparedStatement pStmt = null; |
---|
581 | |
---|
582 | try { |
---|
583 | pStmt = prepareMetaDataSafeStatement(sql); |
---|
584 | |
---|
585 | if (catalog != null) { |
---|
586 | pStmt.setString(1, catalog); |
---|
587 | } else { |
---|
588 | pStmt.setString(1, "%"); |
---|
589 | } |
---|
590 | |
---|
591 | pStmt.setString(2, table); |
---|
592 | |
---|
593 | ResultSet rs = executeMetadataQuery(pStmt); |
---|
594 | |
---|
595 | ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createFkMetadataFields()); |
---|
596 | |
---|
597 | return rs; |
---|
598 | } finally { |
---|
599 | if (pStmt != null) { |
---|
600 | pStmt.close(); |
---|
601 | } |
---|
602 | } |
---|
603 | |
---|
604 | } |
---|
605 | |
---|
606 | private String generateOptionalRefContraintsJoin() { |
---|
607 | return ((this.hasReferentialConstraintsView) ? "JOIN " |
---|
608 | + "INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R " |
---|
609 | + "ON (R.CONSTRAINT_NAME = B.CONSTRAINT_NAME " |
---|
610 | + "AND R.TABLE_NAME = B.TABLE_NAME AND " |
---|
611 | + "R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA) " : ""); |
---|
612 | } |
---|
613 | |
---|
614 | private String generateDeleteRuleClause() { |
---|
615 | return ((this.hasReferentialConstraintsView) ? |
---|
616 | "CASE WHEN R.DELETE_RULE='CASCADE' THEN " + String.valueOf(importedKeyCascade) |
---|
617 | + " WHEN R.DELETE_RULE='SET NULL' THEN " + String.valueOf(importedKeySetNull) |
---|
618 | + " WHEN R.DELETE_RULE='SET DEFAULT' THEN " + String.valueOf(importedKeySetDefault) |
---|
619 | + " WHEN R.DELETE_RULE='RESTRICT' THEN " + String.valueOf(importedKeyRestrict) |
---|
620 | + " WHEN R.DELETE_RULE='NO ACTION' THEN " + String.valueOf(importedKeyNoAction) |
---|
621 | + " ELSE " + String.valueOf(importedKeyNoAction) + " END " : String.valueOf(importedKeyRestrict)); |
---|
622 | } |
---|
623 | |
---|
624 | private String generateUpdateRuleClause() { |
---|
625 | return ((this.hasReferentialConstraintsView) ? |
---|
626 | "CASE WHEN R.UPDATE_RULE='CASCADE' THEN " + String.valueOf(importedKeyCascade) |
---|
627 | + " WHEN R.UPDATE_RULE='SET NULL' THEN " + String.valueOf(importedKeySetNull) |
---|
628 | + " WHEN R.UPDATE_RULE='SET DEFAULT' THEN " + String.valueOf(importedKeySetDefault) |
---|
629 | + " WHEN R.UPDATE_RULE='RESTRICT' THEN " + String.valueOf(importedKeyRestrict) |
---|
630 | + " WHEN R.UPDATE_RULE='NO ACTION' THEN " + String.valueOf(importedKeyNoAction) |
---|
631 | + " ELSE " + String.valueOf(importedKeyNoAction) + " END " : String.valueOf(importedKeyRestrict)); |
---|
632 | } |
---|
633 | |
---|
634 | /** |
---|
635 | * Get a description of the primary key columns that are referenced by a |
---|
636 | * table's foreign key columns (the primary keys imported by a table). They |
---|
637 | * are ordered by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ. |
---|
638 | * <P> |
---|
639 | * Each primary key column description has the following columns: |
---|
640 | * <OL> |
---|
641 | * <li> <B>PKTABLE_CAT</B> String => primary key table catalog being |
---|
642 | * imported (may be null) </li> |
---|
643 | * <li> <B>PKTABLE_SCHEM</B> String => primary key table schema being |
---|
644 | * imported (may be null) </li> |
---|
645 | * <li> <B>PKTABLE_NAME</B> String => primary key table name being imported |
---|
646 | * </li> |
---|
647 | * <li> <B>PKCOLUMN_NAME</B> String => primary key column name being |
---|
648 | * imported </li> |
---|
649 | * <li> <B>FKTABLE_CAT</B> String => foreign key table catalog (may be |
---|
650 | * null) </li> |
---|
651 | * <li> <B>FKTABLE_SCHEM</B> String => foreign key table schema (may be |
---|
652 | * null) </li> |
---|
653 | * <li> <B>FKTABLE_NAME</B> String => foreign key table name </li> |
---|
654 | * <li> <B>FKCOLUMN_NAME</B> String => foreign key column name </li> |
---|
655 | * <li> <B>KEY_SEQ</B> short => sequence number within foreign key </li> |
---|
656 | * <li> <B>UPDATE_RULE</B> short => What happens to foreign key when |
---|
657 | * primary is updated: |
---|
658 | * <UL> |
---|
659 | * <li> importedKeyCascade - change imported key to agree with primary key |
---|
660 | * update </li> |
---|
661 | * <li> importedKeyRestrict - do not allow update of primary key if it has |
---|
662 | * been imported </li> |
---|
663 | * <li> importedKeySetNull - change imported key to NULL if its primary key |
---|
664 | * has been updated </li> |
---|
665 | * </ul> |
---|
666 | * </li> |
---|
667 | * <li> <B>DELETE_RULE</B> short => What happens to the foreign key when |
---|
668 | * primary is deleted. |
---|
669 | * <UL> |
---|
670 | * <li> importedKeyCascade - delete rows that import a deleted key </li> |
---|
671 | * <li> importedKeyRestrict - do not allow delete of primary key if it has |
---|
672 | * been imported </li> |
---|
673 | * <li> importedKeySetNull - change imported key to NULL if its primary key |
---|
674 | * has been deleted </li> |
---|
675 | * </ul> |
---|
676 | * </li> |
---|
677 | * <li> <B>FK_NAME</B> String => foreign key name (may be null) </li> |
---|
678 | * <li> <B>PK_NAME</B> String => primary key name (may be null) </li> |
---|
679 | * </ol> |
---|
680 | * </p> |
---|
681 | * |
---|
682 | * @param catalog |
---|
683 | * a catalog name; "" retrieves those without a catalog |
---|
684 | * @param schema |
---|
685 | * a schema name pattern; "" retrieves those without a schema |
---|
686 | * @param table |
---|
687 | * a table name |
---|
688 | * @return ResultSet each row is a primary key column description |
---|
689 | * @throws SQLException |
---|
690 | * if a database access error occurs |
---|
691 | * @see #getExportedKeys |
---|
692 | */ |
---|
693 | public java.sql.ResultSet getImportedKeys(String catalog, String schema, |
---|
694 | String table) throws SQLException { |
---|
695 | if (table == null) { |
---|
696 | throw SQLError.createSQLException("Table not specified.", |
---|
697 | SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); |
---|
698 | } |
---|
699 | |
---|
700 | if (catalog == null) { |
---|
701 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
702 | catalog = this.database; |
---|
703 | } |
---|
704 | } |
---|
705 | |
---|
706 | String sql = "SELECT " |
---|
707 | + "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT," |
---|
708 | + "NULL AS PKTABLE_SCHEM," |
---|
709 | + "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME," |
---|
710 | + "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME," |
---|
711 | + "A.TABLE_SCHEMA AS FKTABLE_CAT," |
---|
712 | + "NULL AS FKTABLE_SCHEM," |
---|
713 | + "A.TABLE_NAME AS FKTABLE_NAME, " |
---|
714 | + "A.COLUMN_NAME AS FKCOLUMN_NAME, " |
---|
715 | + "A.ORDINAL_POSITION AS KEY_SEQ," |
---|
716 | + generateUpdateRuleClause() |
---|
717 | + " AS UPDATE_RULE," |
---|
718 | + generateDeleteRuleClause() |
---|
719 | + " AS DELETE_RULE," |
---|
720 | + "A.CONSTRAINT_NAME AS FK_NAME," |
---|
721 | + "(SELECT CONSTRAINT_NAME FROM" |
---|
722 | + " INFORMATION_SCHEMA.TABLE_CONSTRAINTS" |
---|
723 | + " WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND" |
---|
724 | + " TABLE_NAME = A.REFERENCED_TABLE_NAME AND" |
---|
725 | + " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)" |
---|
726 | + " AS PK_NAME," |
---|
727 | + importedKeyNotDeferrable |
---|
728 | + " AS DEFERRABILITY " |
---|
729 | + "FROM " |
---|
730 | + "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A " |
---|
731 | + "JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING " |
---|
732 | + "(CONSTRAINT_NAME, TABLE_NAME) " |
---|
733 | + generateOptionalRefContraintsJoin() |
---|
734 | + "WHERE " |
---|
735 | + "B.CONSTRAINT_TYPE = 'FOREIGN KEY' " |
---|
736 | + "AND A.TABLE_SCHEMA LIKE ? " |
---|
737 | + "AND A.TABLE_NAME=? " |
---|
738 | + "AND A.REFERENCED_TABLE_SCHEMA IS NOT NULL " |
---|
739 | + "ORDER BY " |
---|
740 | + "A.REFERENCED_TABLE_SCHEMA, A.REFERENCED_TABLE_NAME, " |
---|
741 | + "A.ORDINAL_POSITION"; |
---|
742 | |
---|
743 | java.sql.PreparedStatement pStmt = null; |
---|
744 | |
---|
745 | try { |
---|
746 | pStmt = prepareMetaDataSafeStatement(sql); |
---|
747 | |
---|
748 | if (catalog != null) { |
---|
749 | pStmt.setString(1, catalog); |
---|
750 | } else { |
---|
751 | pStmt.setString(1, "%"); |
---|
752 | } |
---|
753 | |
---|
754 | pStmt.setString(2, table); |
---|
755 | |
---|
756 | ResultSet rs = executeMetadataQuery(pStmt); |
---|
757 | |
---|
758 | ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createFkMetadataFields()); |
---|
759 | |
---|
760 | return rs; |
---|
761 | } finally { |
---|
762 | if (pStmt != null) { |
---|
763 | pStmt.close(); |
---|
764 | } |
---|
765 | } |
---|
766 | } |
---|
767 | |
---|
768 | /** |
---|
769 | * Get a description of a table's indices and statistics. They are ordered |
---|
770 | * by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION. |
---|
771 | * <P> |
---|
772 | * Each index column description has the following columns: |
---|
773 | * <OL> |
---|
774 | * <li> <B>TABLE_CAT</B> String => table catalog (may be null) </li> |
---|
775 | * <li> <B>TABLE_SCHEM</B> String => table schema (may be null) </li> |
---|
776 | * <li> <B>TABLE_NAME</B> String => table name </li> |
---|
777 | * <li> <B>NON_UNIQUE</B> boolean => Can index values be non-unique? false |
---|
778 | * when TYPE is tableIndexStatistic </li> |
---|
779 | * <li> <B>INDEX_QUALIFIER</B> String => index catalog (may be null); null |
---|
780 | * when TYPE is tableIndexStatistic </li> |
---|
781 | * <li> <B>INDEX_NAME</B> String => index name; null when TYPE is |
---|
782 | * tableIndexStatistic </li> |
---|
783 | * <li> <B>TYPE</B> short => index type: |
---|
784 | * <UL> |
---|
785 | * <li> tableIndexStatistic - this identifies table statistics that are |
---|
786 | * returned in conjuction with a table's index descriptions </li> |
---|
787 | * <li> tableIndexClustered - this is a clustered index </li> |
---|
788 | * <li> tableIndexHashed - this is a hashed index </li> |
---|
789 | * <li> tableIndexOther - this is some other style of index </li> |
---|
790 | * </ul> |
---|
791 | * </li> |
---|
792 | * <li> <B>ORDINAL_POSITION</B> short => column sequence number within |
---|
793 | * index; zero when TYPE is tableIndexStatistic </li> |
---|
794 | * <li> <B>COLUMN_NAME</B> String => column name; null when TYPE is |
---|
795 | * tableIndexStatistic </li> |
---|
796 | * <li> <B>ASC_OR_DESC</B> String => column sort sequence, "A" => |
---|
797 | * ascending, "D" => descending, may be null if sort sequence is not |
---|
798 | * supported; null when TYPE is tableIndexStatistic </li> |
---|
799 | * <li> <B>CARDINALITY</B> int => When TYPE is tableIndexStatisic then this |
---|
800 | * is the number of rows in the table; otherwise it is the number of unique |
---|
801 | * values in the index. </li> |
---|
802 | * <li> <B>PAGES</B> int => When TYPE is tableIndexStatisic then this is |
---|
803 | * the number of pages used for the table, otherwise it is the number of |
---|
804 | * pages used for the current index. </li> |
---|
805 | * <li> <B>FILTER_CONDITION</B> String => Filter condition, if any. (may be |
---|
806 | * null) </li> |
---|
807 | * </ol> |
---|
808 | * </p> |
---|
809 | * |
---|
810 | * @param catalog |
---|
811 | * a catalog name; "" retrieves those without a catalog |
---|
812 | * @param schema |
---|
813 | * a schema name pattern; "" retrieves those without a schema |
---|
814 | * @param table |
---|
815 | * a table name |
---|
816 | * @param unique |
---|
817 | * when true, return only indices for unique values; when false, |
---|
818 | * return indices regardless of whether unique or not |
---|
819 | * @param approximate |
---|
820 | * when true, result is allowed to reflect approximate or out of |
---|
821 | * data values; when false, results are requested to be accurate |
---|
822 | * @return ResultSet each row is an index column description |
---|
823 | * @throws SQLException |
---|
824 | * DOCUMENT ME! |
---|
825 | */ |
---|
826 | public ResultSet getIndexInfo(String catalog, String schema, String table, |
---|
827 | boolean unique, boolean approximate) throws SQLException { |
---|
828 | StringBuffer sqlBuf = new StringBuffer("SELECT " |
---|
829 | + "TABLE_SCHEMA AS TABLE_CAT, " + "NULL AS TABLE_SCHEM," |
---|
830 | + "TABLE_NAME," + "NON_UNIQUE," |
---|
831 | + "TABLE_SCHEMA AS INDEX_QUALIFIER," + "INDEX_NAME," |
---|
832 | + tableIndexOther + " AS TYPE," |
---|
833 | + "SEQ_IN_INDEX AS ORDINAL_POSITION," + "COLUMN_NAME," |
---|
834 | + "COLLATION AS ASC_OR_DESC," + "CARDINALITY," |
---|
835 | + "NULL AS PAGES," + "NULL AS FILTER_CONDITION " |
---|
836 | + "FROM INFORMATION_SCHEMA.STATISTICS WHERE " |
---|
837 | + "TABLE_SCHEMA LIKE ? AND " + "TABLE_NAME LIKE ?"); |
---|
838 | |
---|
839 | if (unique) { |
---|
840 | sqlBuf.append(" AND NON_UNIQUE=0 "); |
---|
841 | } |
---|
842 | |
---|
843 | sqlBuf.append("ORDER BY NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX"); |
---|
844 | |
---|
845 | java.sql.PreparedStatement pStmt = null; |
---|
846 | |
---|
847 | try { |
---|
848 | if (catalog == null) { |
---|
849 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
850 | catalog = this.database; |
---|
851 | } |
---|
852 | } |
---|
853 | |
---|
854 | pStmt = prepareMetaDataSafeStatement(sqlBuf.toString()); |
---|
855 | |
---|
856 | if (catalog != null) { |
---|
857 | pStmt.setString(1, catalog); |
---|
858 | } else { |
---|
859 | pStmt.setString(1, "%"); |
---|
860 | } |
---|
861 | |
---|
862 | pStmt.setString(2, table); |
---|
863 | |
---|
864 | ResultSet rs = executeMetadataQuery(pStmt); |
---|
865 | |
---|
866 | ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createIndexInfoFields()); |
---|
867 | |
---|
868 | return rs; |
---|
869 | } finally { |
---|
870 | if (pStmt != null) { |
---|
871 | pStmt.close(); |
---|
872 | } |
---|
873 | } |
---|
874 | } |
---|
875 | |
---|
876 | /** |
---|
877 | * Get a description of a table's primary key columns. They are ordered by |
---|
878 | * COLUMN_NAME. |
---|
879 | * <P> |
---|
880 | * Each column description has the following columns: |
---|
881 | * <OL> |
---|
882 | * <li> <B>TABLE_CAT</B> String => table catalog (may be null) </li> |
---|
883 | * <li> <B>TABLE_SCHEM</B> String => table schema (may be null) </li> |
---|
884 | * <li> <B>TABLE_NAME</B> String => table name </li> |
---|
885 | * <li> <B>COLUMN_NAME</B> String => column name </li> |
---|
886 | * <li> <B>KEY_SEQ</B> short => sequence number within primary key </li> |
---|
887 | * <li> <B>PK_NAME</B> String => primary key name (may be null) </li> |
---|
888 | * </ol> |
---|
889 | * </p> |
---|
890 | * |
---|
891 | * @param catalog |
---|
892 | * a catalog name; "" retrieves those without a catalog |
---|
893 | * @param schema |
---|
894 | * a schema name pattern; "" retrieves those without a schema |
---|
895 | * @param table |
---|
896 | * a table name |
---|
897 | * @return ResultSet each row is a primary key column description |
---|
898 | * @throws SQLException |
---|
899 | * DOCUMENT ME! |
---|
900 | */ |
---|
901 | public java.sql.ResultSet getPrimaryKeys(String catalog, String schema, |
---|
902 | String table) throws SQLException { |
---|
903 | |
---|
904 | if (catalog == null) { |
---|
905 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
906 | catalog = this.database; |
---|
907 | } |
---|
908 | } |
---|
909 | |
---|
910 | if (table == null) { |
---|
911 | throw SQLError.createSQLException("Table not specified.", |
---|
912 | SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); |
---|
913 | } |
---|
914 | |
---|
915 | String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, " |
---|
916 | + "COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, 'PRIMARY' AS PK_NAME FROM INFORMATION_SCHEMA.STATISTICS " |
---|
917 | + "WHERE TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ? AND " |
---|
918 | + "INDEX_NAME='PRIMARY' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX"; |
---|
919 | |
---|
920 | java.sql.PreparedStatement pStmt = null; |
---|
921 | |
---|
922 | try { |
---|
923 | pStmt = prepareMetaDataSafeStatement(sql); |
---|
924 | |
---|
925 | if (catalog != null) { |
---|
926 | pStmt.setString(1, catalog); |
---|
927 | } else { |
---|
928 | pStmt.setString(1, "%"); |
---|
929 | } |
---|
930 | |
---|
931 | pStmt.setString(2, table); |
---|
932 | |
---|
933 | ResultSet rs = executeMetadataQuery(pStmt); |
---|
934 | ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(new Field[] { |
---|
935 | new Field("", "TABLE_CAT", Types.CHAR, 255), |
---|
936 | new Field("", "TABLE_SCHEM", Types.CHAR, 0), |
---|
937 | new Field("", "TABLE_NAME", Types.CHAR, 255), |
---|
938 | new Field("", "COLUMN_NAME", Types.CHAR, 32), |
---|
939 | new Field("", "KEY_SEQ", Types.SMALLINT, 5), |
---|
940 | new Field("", "PK_NAME", Types.CHAR, 32) }); |
---|
941 | |
---|
942 | return rs; |
---|
943 | } finally { |
---|
944 | if (pStmt != null) { |
---|
945 | pStmt.close(); |
---|
946 | } |
---|
947 | } |
---|
948 | } |
---|
949 | |
---|
950 | /** |
---|
951 | * Get a description of stored procedures available in a catalog. |
---|
952 | * <P> |
---|
953 | * Only procedure descriptions matching the schema and procedure name |
---|
954 | * criteria are returned. They are ordered by PROCEDURE_SCHEM, and |
---|
955 | * PROCEDURE_NAME. |
---|
956 | * </p> |
---|
957 | * <P> |
---|
958 | * Each procedure description has the the following columns: |
---|
959 | * <OL> |
---|
960 | * <li> <B>PROCEDURE_CAT</B> String => procedure catalog (may be null) |
---|
961 | * </li> |
---|
962 | * <li> <B>PROCEDURE_SCHEM</B> String => procedure schema (may be null) |
---|
963 | * </li> |
---|
964 | * <li> <B>PROCEDURE_NAME</B> String => procedure name </li> |
---|
965 | * <li> reserved for future use </li> |
---|
966 | * <li> reserved for future use </li> |
---|
967 | * <li> reserved for future use </li> |
---|
968 | * <li> <B>REMARKS</B> String => explanatory comment on the procedure </li> |
---|
969 | * <li> <B>PROCEDURE_TYPE</B> short => kind of procedure: |
---|
970 | * <UL> |
---|
971 | * <li> procedureResultUnknown - May return a result </li> |
---|
972 | * <li> procedureNoResult - Does not return a result </li> |
---|
973 | * <li> procedureReturnsResult - Returns a result </li> |
---|
974 | * </ul> |
---|
975 | * </li> |
---|
976 | * </ol> |
---|
977 | * </p> |
---|
978 | * |
---|
979 | * @param catalog |
---|
980 | * a catalog name; "" retrieves those without a catalog |
---|
981 | * @param schemaPattern |
---|
982 | * a schema name pattern; "" retrieves those without a schema |
---|
983 | * @param procedureNamePattern |
---|
984 | * a procedure name pattern |
---|
985 | * @return ResultSet each row is a procedure description |
---|
986 | * @throws SQLException |
---|
987 | * if a database access error occurs |
---|
988 | * @see #getSearchStringEscape |
---|
989 | */ |
---|
990 | public ResultSet getProcedures(String catalog, String schemaPattern, |
---|
991 | String procedureNamePattern) throws SQLException { |
---|
992 | |
---|
993 | if ((procedureNamePattern == null) |
---|
994 | || (procedureNamePattern.length() == 0)) { |
---|
995 | if (this.conn.getNullNamePatternMatchesAll()) { |
---|
996 | procedureNamePattern = "%"; |
---|
997 | } else { |
---|
998 | throw SQLError.createSQLException( |
---|
999 | "Procedure name pattern can not be NULL or empty.", |
---|
1000 | SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); |
---|
1001 | } |
---|
1002 | } |
---|
1003 | |
---|
1004 | String db = null; |
---|
1005 | |
---|
1006 | if (catalog == null) { |
---|
1007 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
1008 | db = this.database; |
---|
1009 | } |
---|
1010 | } else { |
---|
1011 | db = catalog; |
---|
1012 | } |
---|
1013 | |
---|
1014 | String sql = "SELECT ROUTINE_SCHEMA AS PROCEDURE_CAT, " |
---|
1015 | + "NULL AS PROCEDURE_SCHEM, " |
---|
1016 | + "ROUTINE_NAME AS PROCEDURE_NAME, " + "NULL AS RESERVED_1, " |
---|
1017 | + "NULL AS RESERVED_2, " + "NULL AS RESERVED_3, " |
---|
1018 | + "ROUTINE_COMMENT AS REMARKS, " |
---|
1019 | + "CASE WHEN ROUTINE_TYPE = 'PROCEDURE' THEN " |
---|
1020 | + procedureNoResult + " WHEN ROUTINE_TYPE='FUNCTION' THEN " |
---|
1021 | + procedureReturnsResult + " ELSE " + procedureResultUnknown |
---|
1022 | + " END AS PROCEDURE_TYPE " |
---|
1023 | + "FROM INFORMATION_SCHEMA.ROUTINES WHERE " |
---|
1024 | + "ROUTINE_SCHEMA LIKE ? AND ROUTINE_NAME LIKE ? " |
---|
1025 | + "ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME"; |
---|
1026 | |
---|
1027 | java.sql.PreparedStatement pStmt = null; |
---|
1028 | |
---|
1029 | try { |
---|
1030 | pStmt = prepareMetaDataSafeStatement(sql); |
---|
1031 | |
---|
1032 | if (db != null) { |
---|
1033 | pStmt.setString(1, db); |
---|
1034 | } else { |
---|
1035 | pStmt.setString(1, "%"); |
---|
1036 | } |
---|
1037 | |
---|
1038 | pStmt.setString(2, procedureNamePattern); |
---|
1039 | |
---|
1040 | ResultSet rs = executeMetadataQuery(pStmt); |
---|
1041 | ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(new Field[] { |
---|
1042 | new Field("", "PROCEDURE_CAT", Types.CHAR, 0), |
---|
1043 | new Field("", "PROCEDURE_SCHEM", Types.CHAR, 0), |
---|
1044 | new Field("", "PROCEDURE_NAME", Types.CHAR, 0), |
---|
1045 | new Field("", "reserved1", Types.CHAR, 0), |
---|
1046 | new Field("", "reserved2", Types.CHAR, 0), |
---|
1047 | new Field("", "reserved3", Types.CHAR, 0), |
---|
1048 | new Field("", "REMARKS", Types.CHAR, 0), |
---|
1049 | new Field("", "PROCEDURE_TYPE", Types.SMALLINT, 0) }); |
---|
1050 | |
---|
1051 | return rs; |
---|
1052 | } finally { |
---|
1053 | if (pStmt != null) { |
---|
1054 | pStmt.close(); |
---|
1055 | } |
---|
1056 | } |
---|
1057 | } |
---|
1058 | |
---|
1059 | /** |
---|
1060 | * Retrieves a description of the given catalog's system or user |
---|
1061 | * function parameters and return type. |
---|
1062 | * |
---|
1063 | * <P>Only descriptions matching the schema, function and |
---|
1064 | * parameter name criteria are returned. They are ordered by |
---|
1065 | * <code>FUNCTION_CAT</code>, <code>FUNCTION_SCHEM</code>, |
---|
1066 | * <code>FUNCTION_NAME</code> and |
---|
1067 | * <code>SPECIFIC_ NAME</code>. Within this, the return value, |
---|
1068 | * if any, is first. Next are the parameter descriptions in call |
---|
1069 | * order. The column descriptions follow in column number order. |
---|
1070 | * |
---|
1071 | * <P>Each row in the <code>ResultSet</code> |
---|
1072 | * is a parameter description, column description or |
---|
1073 | * return type description with the following fields: |
---|
1074 | * <OL> |
---|
1075 | * <LI><B>FUNCTION_CAT</B> String => function catalog (may be <code>null</code>) |
---|
1076 | * <LI><B>FUNCTION_SCHEM</B> String => function schema (may be <code>null</code>) |
---|
1077 | * <LI><B>FUNCTION_NAME</B> String => function name. This is the name |
---|
1078 | * used to invoke the function |
---|
1079 | * <LI><B>COLUMN_NAME</B> String => column/parameter name |
---|
1080 | * <LI><B>COLUMN_TYPE</B> Short => kind of column/parameter: |
---|
1081 | * <UL> |
---|
1082 | * <LI> functionColumnUnknown - nobody knows |
---|
1083 | * <LI> functionColumnIn - IN parameter |
---|
1084 | * <LI> functionColumnInOut - INOUT parameter |
---|
1085 | * <LI> functionColumnOut - OUT parameter |
---|
1086 | * <LI> functionColumnReturn - function return value |
---|
1087 | * <LI> functionColumnResult - Indicates that the parameter or column |
---|
1088 | * is a column in the <code>ResultSet</code> |
---|
1089 | * </UL> |
---|
1090 | * <LI><B>DATA_TYPE</B> int => SQL type from java.sql.Types |
---|
1091 | * <LI><B>TYPE_NAME</B> String => SQL type name, for a UDT type the |
---|
1092 | * type name is fully qualified |
---|
1093 | * <LI><B>PRECISION</B> int => precision |
---|
1094 | * <LI><B>LENGTH</B> int => length in bytes of data |
---|
1095 | * <LI><B>SCALE</B> short => scale - null is returned for data types where |
---|
1096 | * SCALE is not applicable. |
---|
1097 | * <LI><B>RADIX</B> short => radix |
---|
1098 | * <LI><B>NULLABLE</B> short => can it contain NULL. |
---|
1099 | * <UL> |
---|
1100 | * <LI> functionNoNulls - does not allow NULL values |
---|
1101 | * <LI> functionNullable - allows NULL values |
---|
1102 | * <LI> functionNullableUnknown - nullability unknown |
---|
1103 | * </UL> |
---|
1104 | * <LI><B>REMARKS</B> String => comment describing column/parameter |
---|
1105 | * <LI><B>CHAR_OCTET_LENGTH</B> int => the maximum length of binary |
---|
1106 | * and character based parameters or columns. For any other datatype the returned value |
---|
1107 | * is a NULL |
---|
1108 | * <LI><B>ORDINAL_POSITION</B> int => the ordinal position, starting |
---|
1109 | * from 1, for the input and output parameters. A value of 0 |
---|
1110 | * is returned if this row describes the function's return value. |
---|
1111 | * For result set columns, it is the |
---|
1112 | * ordinal position of the column in the result set starting from 1. |
---|
1113 | * <LI><B>IS_NULLABLE</B> String => ISO rules are used to determine |
---|
1114 | * the nullability for a parameter or column. |
---|
1115 | * <UL> |
---|
1116 | * <LI> YES --- if the parameter or column can include NULLs |
---|
1117 | * <LI> NO --- if the parameter or column cannot include NULLs |
---|
1118 | * <LI> empty string --- if the nullability for the |
---|
1119 | * parameter or column is unknown |
---|
1120 | * </UL> |
---|
1121 | * <LI><B>SPECIFIC_NAME</B> String => the name which uniquely identifies |
---|
1122 | * this function within its schema. This is a user specified, or DBMS |
---|
1123 | * generated, name that may be different then the <code>FUNCTION_NAME</code> |
---|
1124 | * for example with overload functions |
---|
1125 | * </OL> |
---|
1126 | * |
---|
1127 | * <p>The PRECISION column represents the specified column size for the given |
---|
1128 | * parameter or column. |
---|
1129 | * For numeric data, this is the maximum precision. For character data, this is the length in characters. |
---|
1130 | * For datetime datatypes, this is the length in characters of the String representation (assuming the |
---|
1131 | * maximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes. For the ROWID datatype, |
---|
1132 | * this is the length in bytes. Null is returned for data types where the |
---|
1133 | * column size is not applicable. |
---|
1134 | * @param catalog a catalog name; must match the catalog name as it |
---|
1135 | * is stored in the database; "" retrieves those without a catalog; |
---|
1136 | * <code>null</code> means that the catalog name should not be used to narrow |
---|
1137 | * the search |
---|
1138 | * @param schemaPattern a schema name pattern; must match the schema name |
---|
1139 | * as it is stored in the database; "" retrieves those without a schema; |
---|
1140 | * <code>null</code> means that the schema name should not be used to narrow |
---|
1141 | * the search |
---|
1142 | * @param functionNamePattern a procedure name pattern; must match the |
---|
1143 | * function name as it is stored in the database |
---|
1144 | * @param columnNamePattern a parameter name pattern; must match the |
---|
1145 | * parameter or column name as it is stored in the database |
---|
1146 | * @return <code>ResultSet</code> - each row describes a |
---|
1147 | * user function parameter, column or return type |
---|
1148 | * |
---|
1149 | * @exception SQLException if a database access error occurs |
---|
1150 | * @see #getSearchStringEscape |
---|
1151 | * @since 1.6 |
---|
1152 | */ |
---|
1153 | public ResultSet getFunctionColumns(String catalog, |
---|
1154 | String schemaPattern, |
---|
1155 | String functionNamePattern, |
---|
1156 | String columnNamePattern) throws SQLException { |
---|
1157 | if (!this.conn.versionMeetsMinimum(5, 4, 0)) { |
---|
1158 | return super.getFunctionColumns(catalog, schemaPattern, functionNamePattern, |
---|
1159 | columnNamePattern); |
---|
1160 | } |
---|
1161 | |
---|
1162 | if (!this.hasParametersView) { |
---|
1163 | return super.getFunctionColumns(catalog, schemaPattern, functionNamePattern, columnNamePattern); |
---|
1164 | } |
---|
1165 | |
---|
1166 | if ((functionNamePattern == null) |
---|
1167 | || (functionNamePattern.length() == 0)) { |
---|
1168 | if (this.conn.getNullNamePatternMatchesAll()) { |
---|
1169 | functionNamePattern = "%"; |
---|
1170 | } else { |
---|
1171 | throw SQLError.createSQLException( |
---|
1172 | "Procedure name pattern can not be NULL or empty.", |
---|
1173 | SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); |
---|
1174 | } |
---|
1175 | } |
---|
1176 | |
---|
1177 | String db = null; |
---|
1178 | |
---|
1179 | if (catalog == null) { |
---|
1180 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
1181 | db = this.database; |
---|
1182 | } |
---|
1183 | } else { |
---|
1184 | db = catalog; |
---|
1185 | } |
---|
1186 | |
---|
1187 | |
---|
1188 | // FIXME: Use DBMD constants when we leave Java5 |
---|
1189 | // FUNCTION_CAT |
---|
1190 | // FUNCTION_SCHEM |
---|
1191 | // FUNCTION_NAME |
---|
1192 | // COLUMN_NAME |
---|
1193 | // COLUMN_TYPE |
---|
1194 | StringBuffer sqlBuf = new StringBuffer("SELECT SPECIFIC_SCHEMA AS FUNCTION_CAT, " |
---|
1195 | + "NULL AS `FUNCTION_SCHEM`, " |
---|
1196 | + "SPECIFIC_NAME AS `FUNCTION_NAME`, " |
---|
1197 | + "PARAMETER_NAME AS `COLUMN_NAME`, " |
---|
1198 | + "CASE WHEN PARAMETER_MODE = 'IN' THEN " |
---|
1199 | + 1 /* functionColumnIn */ + " WHEN PARAMETER_MODE='OUT' THEN " + 3 /* functionColumnOut */ |
---|
1200 | + " WHEN PARAMETER_MODE='INOUT' THEN " + 2 /* functionColumnInOut */ |
---|
1201 | + " WHEN ORDINAL_POSITION=0 THEN " + 4 /* functionReturn */ |
---|
1202 | + " ELSE " + 0 /* functionColumnUnknown */ |
---|
1203 | + " END AS `COLUMN_TYPE`, "); |
---|
1204 | |
---|
1205 | //DATA_TYPE |
---|
1206 | MysqlDefs.appendJdbcTypeMappingQuery(sqlBuf, "DATA_TYPE"); |
---|
1207 | |
---|
1208 | sqlBuf.append(" AS `DATA_TYPE`, "); |
---|
1209 | |
---|
1210 | // TYPE_NAME |
---|
1211 | if (conn.getCapitalizeTypeNames()) { |
---|
1212 | sqlBuf.append("UPPER(CASE WHEN LOCATE('unsigned', DATA_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END) AS `TYPE_NAME`,"); |
---|
1213 | } else { |
---|
1214 | sqlBuf.append("CASE WHEN LOCATE('unsigned', DATA_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END AS `TYPE_NAME`,"); |
---|
1215 | } |
---|
1216 | |
---|
1217 | // PRECISION</B> int => precision |
---|
1218 | sqlBuf.append("NUMERIC_PRECISION AS `PRECISION`, "); |
---|
1219 | // LENGTH</B> int => length in bytes of data |
---|
1220 | sqlBuf |
---|
1221 | .append("CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > " |
---|
1222 | + Integer.MAX_VALUE + " THEN " + Integer.MAX_VALUE + " ELSE CHARACTER_MAXIMUM_LENGTH END AS LENGTH, "); |
---|
1223 | |
---|
1224 | // SCALE</B> short => scale |
---|
1225 | sqlBuf.append("NUMERIC_SCALE AS `SCALE`, "); |
---|
1226 | // RADIX</B> short => radix |
---|
1227 | sqlBuf.append("10 AS RADIX,"); |
---|
1228 | // NULLABLE |
---|
1229 | // REMARKS |
---|
1230 | // CHAR_OCTET_LENGTH * |
---|
1231 | // ORDINAL_POSITION * |
---|
1232 | // IS_NULLABLE * |
---|
1233 | // SPECIFIC_NAME * |
---|
1234 | sqlBuf.append(2 /* functionNullableUnknown */ + " AS `NULLABLE`, " |
---|
1235 | + " NULL AS `REMARKS`, " |
---|
1236 | + "CHARACTER_OCTET_LENGTH AS `CHAR_OCTET_LENGTH`, " |
---|
1237 | + " ORDINAL_POSITION, " |
---|
1238 | + "'' AS `IS_NULLABLE`, " |
---|
1239 | + "SPECIFIC_NAME " |
---|
1240 | + "FROM INFORMATION_SCHEMA.PARAMETERS WHERE " |
---|
1241 | + "SPECIFIC_SCHEMA LIKE ? AND SPECIFIC_NAME LIKE ? AND (PARAMETER_NAME LIKE ? OR PARAMETER_NAME IS NULL) " |
---|
1242 | + "AND ROUTINE_TYPE='FUNCTION' ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION"); |
---|
1243 | |
---|
1244 | java.sql.PreparedStatement pStmt = null; |
---|
1245 | |
---|
1246 | try { |
---|
1247 | pStmt = prepareMetaDataSafeStatement(sqlBuf.toString()); |
---|
1248 | |
---|
1249 | if (db != null) { |
---|
1250 | pStmt.setString(1, db); |
---|
1251 | } else { |
---|
1252 | pStmt.setString(1, "%"); |
---|
1253 | } |
---|
1254 | |
---|
1255 | pStmt.setString(2, functionNamePattern); |
---|
1256 | pStmt.setString(3, columnNamePattern); |
---|
1257 | |
---|
1258 | ResultSet rs = executeMetadataQuery(pStmt); |
---|
1259 | ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createFunctionColumnsFields()); |
---|
1260 | |
---|
1261 | return rs; |
---|
1262 | } finally { |
---|
1263 | if (pStmt != null) { |
---|
1264 | pStmt.close(); |
---|
1265 | } |
---|
1266 | } |
---|
1267 | } |
---|
1268 | |
---|
1269 | /** |
---|
1270 | * Retrieves a description of the given catalog's stored procedure parameter |
---|
1271 | * and result columns. |
---|
1272 | * |
---|
1273 | * <P>Only descriptions matching the schema, procedure and |
---|
1274 | * parameter name criteria are returned. They are ordered by |
---|
1275 | * PROCEDURE_SCHEM and PROCEDURE_NAME. Within this, the return value, |
---|
1276 | * if any, is first. Next are the parameter descriptions in call |
---|
1277 | * order. The column descriptions follow in column number order. |
---|
1278 | * |
---|
1279 | * <P>Each row in the <code>ResultSet</code> is a parameter description or |
---|
1280 | * column description with the following fields: |
---|
1281 | * <OL> |
---|
1282 | * <LI><B>PROCEDURE_CAT</B> String => procedure catalog (may be <code>null</code>) |
---|
1283 | * <LI><B>PROCEDURE_SCHEM</B> String => procedure schema (may be <code>null</code>) |
---|
1284 | * <LI><B>PROCEDURE_NAME</B> String => procedure name |
---|
1285 | * <LI><B>COLUMN_NAME</B> String => column/parameter name |
---|
1286 | * <LI><B>COLUMN_TYPE</B> Short => kind of column/parameter: |
---|
1287 | * <UL> |
---|
1288 | * <LI> procedureColumnUnknown - nobody knows |
---|
1289 | * <LI> procedureColumnIn - IN parameter |
---|
1290 | * <LI> procedureColumnInOut - INOUT parameter |
---|
1291 | * <LI> procedureColumnOut - OUT parameter |
---|
1292 | * <LI> procedureColumnReturn - procedure return value |
---|
1293 | * <LI> procedureColumnResult - result column in <code>ResultSet</code> |
---|
1294 | * </UL> |
---|
1295 | * <LI><B>DATA_TYPE</B> int => SQL type from java.sql.Types |
---|
1296 | * <LI><B>TYPE_NAME</B> String => SQL type name, for a UDT type the |
---|
1297 | * type name is fully qualified |
---|
1298 | * <LI><B>PRECISION</B> int => precision |
---|
1299 | * <LI><B>LENGTH</B> int => length in bytes of data |
---|
1300 | * <LI><B>SCALE</B> short => scale |
---|
1301 | * <LI><B>RADIX</B> short => radix |
---|
1302 | * <LI><B>NULLABLE</B> short => can it contain NULL. |
---|
1303 | * <UL> |
---|
1304 | * <LI> procedureNoNulls - does not allow NULL values |
---|
1305 | * <LI> procedureNullable - allows NULL values |
---|
1306 | * <LI> procedureNullableUnknown - nullability unknown |
---|
1307 | * </UL> |
---|
1308 | * <LI><B>REMARKS</B> String => comment describing parameter/column |
---|
1309 | * </OL> |
---|
1310 | * |
---|
1311 | * <P><B>Note:</B> Some databases may not return the column |
---|
1312 | * descriptions for a procedure. Additional columns beyond |
---|
1313 | * REMARKS can be defined by the database. |
---|
1314 | * |
---|
1315 | * @param catalog a catalog name; must match the catalog name as it |
---|
1316 | * is stored in the database; "" retrieves those without a catalog; |
---|
1317 | * <code>null</code> means that the catalog name should not be used to narrow |
---|
1318 | * the search |
---|
1319 | * @param schemaPattern a schema name pattern; must match the schema name |
---|
1320 | * as it is stored in the database; "" retrieves those without a schema; |
---|
1321 | * <code>null</code> means that the schema name should not be used to narrow |
---|
1322 | * the search |
---|
1323 | * @param procedureNamePattern a procedure name pattern; must match the |
---|
1324 | * procedure name as it is stored in the database |
---|
1325 | * @param columnNamePattern a column name pattern; must match the column name |
---|
1326 | * as it is stored in the database |
---|
1327 | * @return <code>ResultSet</code> - each row describes a stored procedure parameter or |
---|
1328 | * column |
---|
1329 | * @exception SQLException if a database access error occurs |
---|
1330 | * @see #getSearchStringEscape |
---|
1331 | */ |
---|
1332 | public ResultSet getProcedureColumns(String catalog, String schemaPattern, |
---|
1333 | String procedureNamePattern, String columnNamePattern) |
---|
1334 | throws SQLException { |
---|
1335 | if (!this.conn.versionMeetsMinimum(5, 4, 0)) { |
---|
1336 | return super.getProcedureColumns(catalog, schemaPattern, procedureNamePattern, |
---|
1337 | columnNamePattern); |
---|
1338 | } |
---|
1339 | |
---|
1340 | if (!this.hasParametersView) { |
---|
1341 | return super.getProcedureColumns(catalog, schemaPattern, procedureNamePattern, columnNamePattern); |
---|
1342 | } |
---|
1343 | |
---|
1344 | if ((procedureNamePattern == null) |
---|
1345 | || (procedureNamePattern.length() == 0)) { |
---|
1346 | if (this.conn.getNullNamePatternMatchesAll()) { |
---|
1347 | procedureNamePattern = "%"; |
---|
1348 | } else { |
---|
1349 | throw SQLError.createSQLException( |
---|
1350 | "Procedure name pattern can not be NULL or empty.", |
---|
1351 | SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); |
---|
1352 | } |
---|
1353 | } |
---|
1354 | |
---|
1355 | String db = null; |
---|
1356 | |
---|
1357 | if (catalog == null) { |
---|
1358 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
1359 | db = this.database; |
---|
1360 | } |
---|
1361 | } else { |
---|
1362 | db = catalog; |
---|
1363 | } |
---|
1364 | |
---|
1365 | // Here's what we get from MySQL ... |
---|
1366 | // SPECIFIC_CATALOG NULL |
---|
1367 | // SPECIFIC_SCHEMA db17 |
---|
1368 | // SPECIFIC_NAME p |
---|
1369 | // ORDINAL_POSITION 1 |
---|
1370 | // PARAMETER_MODE OUT |
---|
1371 | // PARAMETER_NAME a |
---|
1372 | // DATA_TYPE int |
---|
1373 | // CHARACTER_MAXIMUM_LENGTH NULL |
---|
1374 | // CHARACTER_OCTET_LENGTH NULL |
---|
1375 | // CHARACTER_SET_NAME NULL |
---|
1376 | // COLLATION_NAME NULL |
---|
1377 | // NUMERIC_PRECISION 10 |
---|
1378 | // NUMERIC_SCALE 0 |
---|
1379 | // DTD_IDENTIFIER int(11) |
---|
1380 | |
---|
1381 | StringBuffer sqlBuf = new StringBuffer("SELECT SPECIFIC_SCHEMA AS PROCEDURE_CAT, " |
---|
1382 | + "NULL AS `PROCEDURE_SCHEM`, " |
---|
1383 | + "SPECIFIC_NAME AS `PROCEDURE_NAME`, " |
---|
1384 | + "PARAMETER_NAME AS `COLUMN_NAME`, " |
---|
1385 | + "CASE WHEN PARAMETER_MODE = 'IN' THEN " |
---|
1386 | + procedureColumnIn + " WHEN PARAMETER_MODE='OUT' THEN " + procedureColumnOut |
---|
1387 | + " WHEN PARAMETER_MODE='INOUT' THEN " + procedureColumnInOut |
---|
1388 | + " WHEN ORDINAL_POSITION=0 THEN " + procedureColumnReturn |
---|
1389 | + " ELSE " + procedureColumnUnknown |
---|
1390 | + " END AS `COLUMN_TYPE`, "); |
---|
1391 | |
---|
1392 | //DATA_TYPE |
---|
1393 | MysqlDefs.appendJdbcTypeMappingQuery(sqlBuf, "DATA_TYPE"); |
---|
1394 | |
---|
1395 | sqlBuf.append(" AS `DATA_TYPE`, "); |
---|
1396 | |
---|
1397 | // TYPE_NAME |
---|
1398 | if (conn.getCapitalizeTypeNames()) { |
---|
1399 | sqlBuf.append("UPPER(CASE WHEN LOCATE('unsigned', DATA_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END) AS `TYPE_NAME`,"); |
---|
1400 | } else { |
---|
1401 | sqlBuf.append("CASE WHEN LOCATE('unsigned', DATA_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END AS `TYPE_NAME`,"); |
---|
1402 | } |
---|
1403 | |
---|
1404 | // PRECISION</B> int => precision |
---|
1405 | sqlBuf.append("NUMERIC_PRECISION AS `PRECISION`, "); |
---|
1406 | // LENGTH</B> int => length in bytes of data |
---|
1407 | sqlBuf |
---|
1408 | .append("CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > " |
---|
1409 | + Integer.MAX_VALUE + " THEN " + Integer.MAX_VALUE + " ELSE CHARACTER_MAXIMUM_LENGTH END AS LENGTH, "); |
---|
1410 | |
---|
1411 | // SCALE</B> short => scale |
---|
1412 | sqlBuf.append("NUMERIC_SCALE AS `SCALE`, "); |
---|
1413 | // RADIX</B> short => radix |
---|
1414 | sqlBuf.append("10 AS RADIX,"); |
---|
1415 | sqlBuf.append(procedureNullableUnknown + " AS `NULLABLE`, " |
---|
1416 | + " NULL AS `REMARKS` " |
---|
1417 | + "FROM INFORMATION_SCHEMA.PARAMETERS WHERE " |
---|
1418 | + "SPECIFIC_SCHEMA LIKE ? AND SPECIFIC_NAME LIKE ? AND (PARAMETER_NAME LIKE ? OR PARAMETER_NAME IS NULL) " |
---|
1419 | + "ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION"); |
---|
1420 | |
---|
1421 | java.sql.PreparedStatement pStmt = null; |
---|
1422 | |
---|
1423 | try { |
---|
1424 | pStmt = prepareMetaDataSafeStatement(sqlBuf.toString()); |
---|
1425 | |
---|
1426 | if (db != null) { |
---|
1427 | pStmt.setString(1, db); |
---|
1428 | } else { |
---|
1429 | pStmt.setString(1, "%"); |
---|
1430 | } |
---|
1431 | |
---|
1432 | pStmt.setString(2, procedureNamePattern); |
---|
1433 | pStmt.setString(3, columnNamePattern); |
---|
1434 | |
---|
1435 | ResultSet rs = executeMetadataQuery(pStmt); |
---|
1436 | ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createProcedureColumnsFields()); |
---|
1437 | |
---|
1438 | return rs; |
---|
1439 | } finally { |
---|
1440 | if (pStmt != null) { |
---|
1441 | pStmt.close(); |
---|
1442 | } |
---|
1443 | } |
---|
1444 | } |
---|
1445 | |
---|
1446 | /** |
---|
1447 | * Get a description of tables available in a catalog. |
---|
1448 | * <P> |
---|
1449 | * Only table descriptions matching the catalog, schema, table name and type |
---|
1450 | * criteria are returned. They are ordered by TABLE_TYPE, TABLE_SCHEM and |
---|
1451 | * TABLE_NAME. |
---|
1452 | * </p> |
---|
1453 | * <P> |
---|
1454 | * Each table description has the following columns: |
---|
1455 | * <OL> |
---|
1456 | * <li> <B>TABLE_CAT</B> String => table catalog (may be null) </li> |
---|
1457 | * <li> <B>TABLE_SCHEM</B> String => table schema (may be null) </li> |
---|
1458 | * <li> <B>TABLE_NAME</B> String => table name </li> |
---|
1459 | * <li> <B>TABLE_TYPE</B> String => table type. Typical types are "TABLE", |
---|
1460 | * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", |
---|
1461 | * "SYNONYM". </li> |
---|
1462 | * <li> <B>REMARKS</B> String => explanatory comment on the table </li> |
---|
1463 | * </ol> |
---|
1464 | * </p> |
---|
1465 | * <P> |
---|
1466 | * <B>Note:</B> Some databases may not return information for all tables. |
---|
1467 | * </p> |
---|
1468 | * |
---|
1469 | * @param catalog |
---|
1470 | * a catalog name; "" retrieves those without a catalog |
---|
1471 | * @param schemaPattern |
---|
1472 | * a schema name pattern; "" retrieves those without a schema |
---|
1473 | * @param tableNamePattern |
---|
1474 | * a table name pattern |
---|
1475 | * @param types |
---|
1476 | * a list of table types to include; null returns all types |
---|
1477 | * @return ResultSet each row is a table description |
---|
1478 | * @throws SQLException |
---|
1479 | * DOCUMENT ME! |
---|
1480 | * @see #getSearchStringEscape |
---|
1481 | */ |
---|
1482 | public ResultSet getTables(String catalog, String schemaPattern, |
---|
1483 | String tableNamePattern, String[] types) throws SQLException { |
---|
1484 | if (catalog == null) { |
---|
1485 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
1486 | catalog = this.database; |
---|
1487 | } |
---|
1488 | } |
---|
1489 | |
---|
1490 | if (tableNamePattern == null) { |
---|
1491 | if (this.conn.getNullNamePatternMatchesAll()) { |
---|
1492 | tableNamePattern = "%"; |
---|
1493 | } else { |
---|
1494 | throw SQLError.createSQLException( |
---|
1495 | "Table name pattern can not be NULL or empty.", |
---|
1496 | SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); |
---|
1497 | } |
---|
1498 | } |
---|
1499 | |
---|
1500 | final String tableNamePat; |
---|
1501 | String tmpCat = ""; |
---|
1502 | |
---|
1503 | if ((catalog == null) || (catalog.length() == 0)) { |
---|
1504 | if (this.conn.getNullCatalogMeansCurrent()) { |
---|
1505 | tmpCat = this.database; |
---|
1506 | } |
---|
1507 | } else { |
---|
1508 | tmpCat = catalog; |
---|
1509 | } |
---|
1510 | |
---|
1511 | List<String> parseList = StringUtils.splitDBdotName(tableNamePattern, tmpCat, |
---|
1512 | quotedId , conn.isNoBackslashEscapesSet()); |
---|
1513 | //There *should* be 2 rows, if any. |
---|
1514 | if (parseList.size() == 2) { |
---|
1515 | tableNamePat = parseList.get(1); |
---|
1516 | } else { |
---|
1517 | tableNamePat = tableNamePattern; |
---|
1518 | } |
---|
1519 | |
---|
1520 | java.sql.PreparedStatement pStmt = null; |
---|
1521 | |
---|
1522 | String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, " |
---|
1523 | + "NULL AS TABLE_SCHEM, TABLE_NAME, " |
---|
1524 | + "CASE WHEN TABLE_TYPE='BASE TABLE' THEN 'TABLE' WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, " |
---|
1525 | + "TABLE_COMMENT AS REMARKS " |
---|
1526 | + "FROM INFORMATION_SCHEMA.TABLES WHERE "; |
---|
1527 | |
---|
1528 | final boolean operatingOnInformationSchema = "information_schema".equalsIgnoreCase(catalog); |
---|
1529 | if (catalog != null) { |
---|
1530 | if ((operatingOnInformationSchema) || ((StringUtils.indexOfIgnoreCase(0, catalog, "%") == -1) |
---|
1531 | && (StringUtils.indexOfIgnoreCase(0, catalog, "_") == -1))) { |
---|
1532 | sql = sql + "TABLE_SCHEMA = ? AND "; |
---|
1533 | } else { |
---|
1534 | sql = sql + "TABLE_SCHEMA LIKE ? AND "; |
---|
1535 | } |
---|
1536 | |
---|
1537 | } else { |
---|
1538 | sql = sql + "TABLE_SCHEMA LIKE ? AND "; |
---|
1539 | } |
---|
1540 | |
---|
1541 | if (tableNamePat != null) { |
---|
1542 | if ((StringUtils.indexOfIgnoreCase(0, tableNamePat, "%") == -1) |
---|
1543 | && (StringUtils.indexOfIgnoreCase(0, tableNamePat, "_") == -1)) { |
---|
1544 | sql = sql + "TABLE_NAME = ? AND "; |
---|
1545 | } else { |
---|
1546 | sql = sql + "TABLE_NAME LIKE ? AND "; |
---|
1547 | } |
---|
1548 | |
---|
1549 | } else { |
---|
1550 | sql = sql + "TABLE_NAME LIKE ? AND "; |
---|
1551 | } |
---|
1552 | sql = sql + "TABLE_TYPE IN (?,?,?) "; |
---|
1553 | sql = sql + "ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME"; |
---|
1554 | try { |
---|
1555 | pStmt = prepareMetaDataSafeStatement(sql); |
---|
1556 | |
---|
1557 | if (catalog != null) { |
---|
1558 | pStmt.setString(1, catalog); |
---|
1559 | } else { |
---|
1560 | pStmt.setString(1, "%"); |
---|
1561 | } |
---|
1562 | |
---|
1563 | pStmt.setString(2, tableNamePat); |
---|
1564 | |
---|
1565 | // This overloading of IN (...) allows us to cache this |
---|
1566 | // prepared statement |
---|
1567 | if (types == null || types.length == 0) { |
---|
1568 | pStmt.setString(3, "BASE TABLE"); |
---|
1569 | pStmt.setString(4, "VIEW"); |
---|
1570 | pStmt.setString(5, "TEMPORARY"); |
---|
1571 | } else { |
---|
1572 | pStmt.setNull(3, Types.VARCHAR); |
---|
1573 | pStmt.setNull(4, Types.VARCHAR); |
---|
1574 | pStmt.setNull(5, Types.VARCHAR); |
---|
1575 | |
---|
1576 | for (int i = 0; i < types.length; i++) { |
---|
1577 | if ("TABLE".equalsIgnoreCase(types[i])) { |
---|
1578 | pStmt.setString(3, "BASE TABLE"); |
---|
1579 | } |
---|
1580 | |
---|
1581 | if ("VIEW".equalsIgnoreCase(types[i])) { |
---|
1582 | pStmt.setString(4, "VIEW"); |
---|
1583 | } |
---|
1584 | |
---|
1585 | if ("LOCAL TEMPORARY".equalsIgnoreCase(types[i])) { |
---|
1586 | pStmt.setString(5, "TEMPORARY"); |
---|
1587 | } |
---|
1588 | } |
---|
1589 | } |
---|
1590 | |
---|
1591 | ResultSet rs = executeMetadataQuery(pStmt); |
---|
1592 | |
---|
1593 | ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(new Field[] { |
---|
1594 | new Field("", "TABLE_CAT", java.sql.Types.VARCHAR, |
---|
1595 | (catalog == null) ? 0 : catalog.length()), |
---|
1596 | new Field("", "TABLE_SCHEM", java.sql.Types.VARCHAR, 0), |
---|
1597 | new Field("", "TABLE_NAME", java.sql.Types.VARCHAR, 255), |
---|
1598 | new Field("", "TABLE_TYPE", java.sql.Types.VARCHAR, 5), |
---|
1599 | new Field("", "REMARKS", java.sql.Types.VARCHAR, 0) }); |
---|
1600 | |
---|
1601 | return rs; |
---|
1602 | } finally { |
---|
1603 | if (pStmt != null) { |
---|
1604 | pStmt.close(); |
---|
1605 | } |
---|
1606 | } |
---|
1607 | } |
---|
1608 | |
---|
1609 | public boolean gethasParametersView() { |
---|
1610 | return this.hasParametersView; |
---|
1611 | } |
---|
1612 | |
---|
1613 | |
---|
1614 | } |
---|