0001: /*
0002: Copyright (C) 2005-2007 MySQL AB
0003:
0004: This program is free software; you can redistribute it and/or modify
0005: it under the terms of version 2 of the GNU General Public License as
0006: published by the Free Software Foundation.
0007:
0008: There are special exceptions to the terms and conditions of the GPL
0009: as it is applied to this software. View the full text of the
0010: exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
0011: software distribution.
0012:
0013: This program is distributed in the hope that it will be useful,
0014: but WITHOUT ANY WARRANTY; without even the implied warranty of
0015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0016: GNU General Public License for more details.
0017:
0018: You should have received a copy of the GNU General Public License
0019: along with this program; if not, write to the Free Software
0020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0021:
0022: */
0023: package com.mysql.jdbc;
0024:
0025: import java.sql.ResultSet;
0026: import java.sql.SQLException;
0027: import java.sql.Types;
0028:
0029: /**
0030: * DatabaseMetaData implementation that uses INFORMATION_SCHEMA available in
0031: * MySQL-5.0 and newer.
0032: *
0033: * The majority of the queries in this code were built for Connector/OO.org by
0034: * Georg Richter (georg_at_mysql.com).
0035: */
0036: public class DatabaseMetaDataUsingInfoSchema extends DatabaseMetaData {
0037:
0038: private boolean hasReferentialConstraintsView;
0039:
0040: protected DatabaseMetaDataUsingInfoSchema(ConnectionImpl connToSet,
0041: String databaseToSet) throws SQLException {
0042: super (connToSet, databaseToSet);
0043:
0044: this .hasReferentialConstraintsView = this .conn
0045: .versionMeetsMinimum(5, 1, 10);
0046: }
0047:
0048: private ResultSet executeMetadataQuery(PreparedStatement pStmt)
0049: throws SQLException {
0050: ResultSet rs = pStmt.executeQuery();
0051: ((com.mysql.jdbc.ResultSetInternalMethods) rs)
0052: .setOwningStatement(null);
0053:
0054: return rs;
0055: }
0056:
0057: /**
0058: * Get a description of the access rights for a table's columns.
0059: * <P>
0060: * Only privileges matching the column name criteria are returned. They are
0061: * ordered by COLUMN_NAME and PRIVILEGE.
0062: * </p>
0063: * <P>
0064: * Each privilige description has the following columns:
0065: * <OL>
0066: * <li> <B>TABLE_CAT</B> String => table catalog (may be null) </li>
0067: * <li> <B>TABLE_SCHEM</B> String => table schema (may be null) </li>
0068: * <li> <B>TABLE_NAME</B> String => table name </li>
0069: * <li> <B>COLUMN_NAME</B> String => column name </li>
0070: * <li> <B>GRANTOR</B> => grantor of access (may be null) </li>
0071: * <li> <B>GRANTEE</B> String => grantee of access </li>
0072: * <li> <B>PRIVILEGE</B> String => name of access (SELECT, INSERT, UPDATE,
0073: * REFRENCES, ...) </li>
0074: * <li> <B>IS_GRANTABLE</B> String => "YES" if grantee is permitted to
0075: * grant to others; "NO" if not; null if unknown </li>
0076: * </ol>
0077: * </p>
0078: *
0079: * @param catalog
0080: * a catalog name; "" retrieves those without a catalog
0081: * @param schema
0082: * a schema name; "" retrieves those without a schema
0083: * @param table
0084: * a table name
0085: * @param columnNamePattern
0086: * a column name pattern
0087: * @return ResultSet each row is a column privilege description
0088: * @throws SQLException
0089: * if a database access error occurs
0090: * @see #getSearchStringEscape
0091: */
0092: public java.sql.ResultSet getColumnPrivileges(String catalog,
0093: String schema, String table, String columnNamePattern)
0094: throws SQLException {
0095: if (columnNamePattern == null) {
0096: if (this .conn.getNullNamePatternMatchesAll()) {
0097: columnNamePattern = "%";
0098: } else {
0099: throw SQLError
0100: .createSQLException(
0101: "Column name pattern can not be NULL or empty.",
0102: SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
0103: }
0104: }
0105:
0106: if (catalog == null) {
0107: if (this .conn.getNullCatalogMeansCurrent()) {
0108: catalog = this .database;
0109: }
0110: }
0111:
0112: String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME,"
0113: + "COLUMN_NAME, NULL AS GRANTOR, GRANTEE, PRIVILEGE_TYPE AS PRIVILEGE, IS_GRANTABLE FROM "
0114: + "INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE "
0115: + "TABLE_SCHEMA LIKE ? AND "
0116: + "TABLE_NAME =? AND COLUMN_NAME LIKE ? ORDER BY "
0117: + "COLUMN_NAME, PRIVILEGE_TYPE";
0118:
0119: PreparedStatement pStmt = null;
0120:
0121: try {
0122: pStmt = prepareMetaDataSafeStatement(sql);
0123:
0124: if (catalog != null) {
0125: pStmt.setString(1, catalog);
0126: } else {
0127: pStmt.setString(1, "%");
0128: }
0129:
0130: pStmt.setString(2, table);
0131: pStmt.setString(3, columnNamePattern);
0132:
0133: ResultSet rs = executeMetadataQuery(pStmt);
0134: ((com.mysql.jdbc.ResultSetInternalMethods) rs)
0135: .redefineFieldsForDBMD(new Field[] {
0136: new Field("", "TABLE_CAT", Types.CHAR, 64),
0137: new Field("", "TABLE_SCHEM", Types.CHAR, 1),
0138: new Field("", "TABLE_NAME", Types.CHAR, 64),
0139: new Field("", "COLUMN_NAME", Types.CHAR, 64),
0140: new Field("", "GRANTOR", Types.CHAR, 77),
0141: new Field("", "GRANTEE", Types.CHAR, 77),
0142: new Field("", "PRIVILEGE", Types.CHAR, 64),
0143: new Field("", "IS_GRANTABLE", Types.CHAR, 3) });
0144:
0145: return rs;
0146: } finally {
0147: if (pStmt != null) {
0148: pStmt.close();
0149: }
0150: }
0151: }
0152:
0153: /**
0154: * Get a description of table columns available in a catalog.
0155: * <P>
0156: * Only column descriptions matching the catalog, schema, table and column
0157: * name criteria are returned. They are ordered by TABLE_SCHEM, TABLE_NAME
0158: * and ORDINAL_POSITION.
0159: * </p>
0160: * <P>
0161: * Each column description has the following columns:
0162: * <OL>
0163: * <li> <B>TABLE_CAT</B> String => table catalog (may be null) </li>
0164: * <li> <B>TABLE_SCHEM</B> String => table schema (may be null) </li>
0165: * <li> <B>TABLE_NAME</B> String => table name </li>
0166: * <li> <B>COLUMN_NAME</B> String => column name </li>
0167: * <li> <B>DATA_TYPE</B> short => SQL type from java.sql.Types </li>
0168: * <li> <B>TYPE_NAME</B> String => Data source dependent type name </li>
0169: * <li> <B>COLUMN_SIZE</B> int => column size. For char or date types this
0170: * is the maximum number of characters, for numeric or decimal types this is
0171: * precision. </li>
0172: * <li> <B>BUFFER_LENGTH</B> is not used. </li>
0173: * <li> <B>DECIMAL_DIGITS</B> int => the number of fractional digits </li>
0174: * <li> <B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2) </li>
0175: * <li> <B>NULLABLE</B> int => is NULL allowed?
0176: * <UL>
0177: * <li> columnNoNulls - might not allow NULL values </li>
0178: * <li> columnNullable - definitely allows NULL values </li>
0179: * <li> columnNullableUnknown - nullability unknown </li>
0180: * </ul>
0181: * </li>
0182: * <li> <B>REMARKS</B> String => comment describing column (may be null)
0183: * </li>
0184: * <li> <B>COLUMN_DEF</B> String => default value (may be null) </li>
0185: * <li> <B>SQL_DATA_TYPE</B> int => unused </li>
0186: * <li> <B>SQL_DATETIME_SUB</B> int => unused </li>
0187: * <li> <B>CHAR_OCTET_LENGTH</B> int => for char types the maximum number
0188: * of bytes in the column </li>
0189: * <li> <B>ORDINAL_POSITION</B> int => index of column in table (starting
0190: * at 1) </li>
0191: * <li> <B>IS_NULLABLE</B> String => "NO" means column definitely does not
0192: * allow NULL values; "YES" means the column might allow NULL values. An
0193: * empty string means nobody knows. </li>
0194: * </ol>
0195: * </p>
0196: */
0197: public ResultSet getColumns(String catalog, String schemaPattern,
0198: String tableName, String columnNamePattern)
0199: throws SQLException {
0200: if (columnNamePattern == null) {
0201: if (this .conn.getNullNamePatternMatchesAll()) {
0202: columnNamePattern = "%";
0203: } else {
0204: throw SQLError
0205: .createSQLException(
0206: "Column name pattern can not be NULL or empty.",
0207: SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
0208: }
0209: }
0210:
0211: if (catalog == null) {
0212: if (this .conn.getNullCatalogMeansCurrent()) {
0213: catalog = this .database;
0214: }
0215: }
0216:
0217: StringBuffer sqlBuf = new StringBuffer("SELECT "
0218: + "TABLE_SCHEMA AS TABLE_CAT, "
0219: + "NULL AS TABLE_SCHEM," + "TABLE_NAME,"
0220: + "COLUMN_NAME,");
0221: MysqlDefs.appendJdbcTypeMappingQuery(sqlBuf, "DATA_TYPE");
0222:
0223: sqlBuf.append(" AS DATA_TYPE, ");
0224:
0225: if (conn.getCapitalizeTypeNames()) {
0226: sqlBuf
0227: .append("UPPER(CASE WHEN LOCATE('unsigned', COLUMN_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END) AS TYPE_NAME,");
0228: } else {
0229: sqlBuf
0230: .append("CASE WHEN LOCATE('unsigned', COLUMN_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END AS TYPE_NAME,");
0231: }
0232:
0233: sqlBuf
0234: .append("CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION ELSE CASE WHEN CHARACTER_MAXIMUM_LENGTH > "
0235: + Integer.MAX_VALUE
0236: + " THEN "
0237: + Integer.MAX_VALUE
0238: + " ELSE CHARACTER_MAXIMUM_LENGTH END END AS COLUMN_SIZE, "
0239: + MysqlIO.getMaxBuf()
0240: + " AS BUFFER_LENGTH,"
0241: + "NUMERIC_SCALE AS DECIMAL_DIGITS,"
0242: + "10 AS NUM_PREC_RADIX,"
0243: + "CASE WHEN IS_NULLABLE='NO' THEN "
0244: + columnNoNulls
0245: + " ELSE CASE WHEN IS_NULLABLE='YES' THEN "
0246: + columnNullable
0247: + " ELSE "
0248: + columnNullableUnknown
0249: + " END END AS NULLABLE,"
0250: + "COLUMN_COMMENT AS REMARKS,"
0251: + "COLUMN_DEFAULT AS COLUMN_DEF,"
0252: + "0 AS SQL_DATA_TYPE,"
0253: + "0 AS SQL_DATETIME_SUB,"
0254: + "CASE WHEN CHARACTER_OCTET_LENGTH > "
0255: + Integer.MAX_VALUE
0256: + " THEN "
0257: + Integer.MAX_VALUE
0258: + " ELSE CHARACTER_OCTET_LENGTH END AS CHAR_OCTET_LENGTH,"
0259: + "ORDINAL_POSITION,"
0260: + "IS_NULLABLE,"
0261: + "NULL AS SCOPE_CATALOG,"
0262: + "NULL AS SCOPE_SCHEMA,"
0263: + "NULL AS SCOPE_TABLE,"
0264: + "NULL AS SOURCE_DATA_TYPE,"
0265: + "IF (EXTRA LIKE '%auto_increment%','YES','NO') AS IS_AUTOINCREMENT "
0266: + "FROM INFORMATION_SCHEMA.COLUMNS WHERE "
0267: + "TABLE_SCHEMA LIKE ? AND "
0268: + "TABLE_NAME LIKE ? AND COLUMN_NAME LIKE ? "
0269: + "ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION");
0270:
0271: PreparedStatement pStmt = null;
0272:
0273: try {
0274: pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());
0275:
0276: if (catalog != null) {
0277: pStmt.setString(1, catalog);
0278: } else {
0279: pStmt.setString(1, "%");
0280: }
0281:
0282: pStmt.setString(2, tableName);
0283: pStmt.setString(3, columnNamePattern);
0284:
0285: ResultSet rs = executeMetadataQuery(pStmt);
0286:
0287: ((com.mysql.jdbc.ResultSetInternalMethods) rs)
0288: .redefineFieldsForDBMD(new Field[] {
0289: new Field("", "TABLE_CAT", Types.CHAR, 255),
0290: new Field("", "TABLE_SCHEM", Types.CHAR, 0),
0291: new Field("", "TABLE_NAME", Types.CHAR, 255),
0292: new Field("", "COLUMN_NAME", Types.CHAR, 32),
0293: new Field("", "DATA_TYPE", Types.SMALLINT,
0294: 5),
0295: new Field("", "TYPE_NAME", Types.CHAR, 16),
0296: new Field("", "COLUMN_SIZE", Types.INTEGER,
0297: Integer.toString(Integer.MAX_VALUE)
0298: .length()),
0299: new Field("", "BUFFER_LENGTH",
0300: Types.INTEGER, 10),
0301: new Field("", "DECIMAL_DIGITS",
0302: Types.INTEGER, 10),
0303: new Field("", "NUM_PREC_RADIX",
0304: Types.INTEGER, 10),
0305: new Field("", "NULLABLE", Types.INTEGER, 10),
0306: new Field("", "REMARKS", Types.CHAR, 0),
0307: new Field("", "COLUMN_DEF", Types.CHAR, 0),
0308: new Field("", "SQL_DATA_TYPE",
0309: Types.INTEGER, 10),
0310: new Field("", "SQL_DATETIME_SUB",
0311: Types.INTEGER, 10),
0312: new Field("", "CHAR_OCTET_LENGTH",
0313: Types.INTEGER, Integer.toString(
0314: Integer.MAX_VALUE).length()),
0315: new Field("", "ORDINAL_POSITION",
0316: Types.INTEGER, 10),
0317: new Field("", "IS_NULLABLE", Types.CHAR, 3),
0318: new Field("", "SCOPE_CATALOG", Types.CHAR,
0319: 255),
0320: new Field("", "SCOPE_SCHEMA", Types.CHAR,
0321: 255),
0322: new Field("", "SCOPE_TABLE", Types.CHAR,
0323: 255),
0324: new Field("", "SOURCE_DATA_TYPE",
0325: Types.SMALLINT, 10),
0326: new Field("", "IS_AUTOINCREMENT",
0327: Types.CHAR, 3) });
0328: return rs;
0329: } finally {
0330: if (pStmt != null) {
0331: pStmt.close();
0332: }
0333: }
0334: }
0335:
0336: /**
0337: * Get a description of the foreign key columns in the foreign key table
0338: * that reference the primary key columns of the primary key table (describe
0339: * how one table imports another's key.) This should normally return a
0340: * single foreign key/primary key pair (most tables only import a foreign
0341: * key from a table once.) They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
0342: * FKTABLE_NAME, and KEY_SEQ.
0343: * <P>
0344: * Each foreign key column description has the following columns:
0345: * <OL>
0346: * <li> <B>PKTABLE_CAT</B> String => primary key table catalog (may be
0347: * null) </li>
0348: * <li> <B>PKTABLE_SCHEM</B> String => primary key table schema (may be
0349: * null) </li>
0350: * <li> <B>PKTABLE_NAME</B> String => primary key table name </li>
0351: * <li> <B>PKCOLUMN_NAME</B> String => primary key column name </li>
0352: * <li> <B>FKTABLE_CAT</B> String => foreign key table catalog (may be
0353: * null) being exported (may be null) </li>
0354: * <li> <B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
0355: * null) being exported (may be null) </li>
0356: * <li> <B>FKTABLE_NAME</B> String => foreign key table name being exported
0357: * </li>
0358: * <li> <B>FKCOLUMN_NAME</B> String => foreign key column name being
0359: * exported </li>
0360: * <li> <B>KEY_SEQ</B> short => sequence number within foreign key </li>
0361: * <li> <B>UPDATE_RULE</B> short => What happens to foreign key when
0362: * primary is updated:
0363: * <UL>
0364: * <li> importedKeyCascade - change imported key to agree with primary key
0365: * update </li>
0366: * <li> importedKeyRestrict - do not allow update of primary key if it has
0367: * been imported </li>
0368: * <li> importedKeySetNull - change imported key to NULL if its primary key
0369: * has been updated </li>
0370: * </ul>
0371: * </li>
0372: * <li> <B>DELETE_RULE</B> short => What happens to the foreign key when
0373: * primary is deleted.
0374: * <UL>
0375: * <li> importedKeyCascade - delete rows that import a deleted key </li>
0376: * <li> importedKeyRestrict - do not allow delete of primary key if it has
0377: * been imported </li>
0378: * <li> importedKeySetNull - change imported key to NULL if its primary key
0379: * has been deleted </li>
0380: * </ul>
0381: * </li>
0382: * <li> <B>FK_NAME</B> String => foreign key identifier (may be null) </li>
0383: * <li> <B>PK_NAME</B> String => primary key identifier (may be null) </li>
0384: * </ol>
0385: * </p>
0386: *
0387: * @param primaryCatalog
0388: * a catalog name; "" retrieves those without a catalog
0389: * @param primarySchema
0390: * a schema name pattern; "" retrieves those without a schema
0391: * @param primaryTable
0392: * a table name
0393: * @param foreignCatalog
0394: * a catalog name; "" retrieves those without a catalog
0395: * @param foreignSchema
0396: * a schema name pattern; "" retrieves those without a schema
0397: * @param foreignTable
0398: * a table name
0399: * @return ResultSet each row is a foreign key column description
0400: * @throws SQLException
0401: * if a database access error occurs
0402: */
0403: public java.sql.ResultSet getCrossReference(String primaryCatalog,
0404: String primarySchema, String primaryTable,
0405: String foreignCatalog, String foreignSchema,
0406: String foreignTable) throws SQLException {
0407: if (primaryTable == null) {
0408: throw SQLError.createSQLException("Table not specified.",
0409: SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
0410: }
0411:
0412: if (primaryCatalog == null) {
0413: if (this .conn.getNullCatalogMeansCurrent()) {
0414: primaryCatalog = this .database;
0415: }
0416: }
0417:
0418: if (foreignCatalog == null) {
0419: if (this .conn.getNullCatalogMeansCurrent()) {
0420: foreignCatalog = this .database;
0421: }
0422: }
0423:
0424: Field[] fields = new Field[14];
0425: fields[0] = new Field("", "PKTABLE_CAT", Types.CHAR, 255);
0426: fields[1] = new Field("", "PKTABLE_SCHEM", Types.CHAR, 0);
0427: fields[2] = new Field("", "PKTABLE_NAME", Types.CHAR, 255);
0428: fields[3] = new Field("", "PKCOLUMN_NAME", Types.CHAR, 32);
0429: fields[4] = new Field("", "FKTABLE_CAT", Types.CHAR, 255);
0430: fields[5] = new Field("", "FKTABLE_SCHEM", Types.CHAR, 0);
0431: fields[6] = new Field("", "FKTABLE_NAME", Types.CHAR, 255);
0432: fields[7] = new Field("", "FKCOLUMN_NAME", Types.CHAR, 32);
0433: fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
0434: fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
0435: fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
0436: fields[11] = new Field("", "FK_NAME", Types.CHAR, 0);
0437: fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
0438: fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);
0439:
0440: String sql = "SELECT "
0441: + "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,"
0442: + "NULL AS PKTABLE_SCHEM,"
0443: + "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,"
0444: + "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,"
0445: + "A.TABLE_SCHEMA AS FKTABLE_CAT,"
0446: + "NULL AS FKTABLE_SCHEM,"
0447: + "A.TABLE_NAME AS FKTABLE_NAME, "
0448: + "A.COLUMN_NAME AS FKCOLUMN_NAME, "
0449: + "A.ORDINAL_POSITION AS KEY_SEQ,"
0450: + generateUpdateRuleClause()
0451: + " AS UPDATE_RULE,"
0452: + generateDeleteRuleClause()
0453: + " AS DELETE_RULE,"
0454: + "A.CONSTRAINT_NAME AS FK_NAME,"
0455: + "(SELECT CONSTRAINT_NAME FROM"
0456: + " INFORMATION_SCHEMA.TABLE_CONSTRAINTS"
0457: + " WHERE TABLE_SCHEMA = REFERENCED_TABLE_SCHEMA AND"
0458: + " TABLE_NAME = REFERENCED_TABLE_NAME AND"
0459: + " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)"
0460: + " AS PK_NAME,"
0461: + importedKeyNotDeferrable
0462: + " AS DEFERRABILITY "
0463: + "FROM "
0464: + "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN "
0465: + "INFORMATION_SCHEMA.TABLE_CONSTRAINTS B "
0466: + "USING (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) "
0467: + generateOptionalRefContraintsJoin()
0468: + "WHERE "
0469: + "B.CONSTRAINT_TYPE = 'FOREIGN KEY' "
0470: + "AND A.REFERENCED_TABLE_SCHEMA LIKE ? AND A.REFERENCED_TABLE_NAME=? "
0471: + "AND A.TABLE_SCHEMA LIKE ? AND A.TABLE_NAME=? "
0472: + "ORDER BY "
0473: + "A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION";
0474:
0475: PreparedStatement pStmt = null;
0476:
0477: try {
0478: pStmt = prepareMetaDataSafeStatement(sql);
0479: if (primaryCatalog != null) {
0480: pStmt.setString(1, primaryCatalog);
0481: } else {
0482: pStmt.setString(1, "%");
0483: }
0484:
0485: pStmt.setString(2, primaryTable);
0486:
0487: if (foreignCatalog != null) {
0488: pStmt.setString(3, foreignCatalog);
0489: } else {
0490: pStmt.setString(3, "%");
0491: }
0492:
0493: pStmt.setString(4, foreignTable);
0494:
0495: ResultSet rs = executeMetadataQuery(pStmt);
0496: ((com.mysql.jdbc.ResultSetInternalMethods) rs)
0497: .redefineFieldsForDBMD(new Field[] {
0498: new Field("", "PKTABLE_CAT", Types.CHAR,
0499: 255),
0500: new Field("", "PKTABLE_SCHEM", Types.CHAR,
0501: 0),
0502: new Field("", "PKTABLE_NAME", Types.CHAR,
0503: 255),
0504: new Field("", "PKCOLUMN_NAME", Types.CHAR,
0505: 32),
0506: new Field("", "FKTABLE_CAT", Types.CHAR,
0507: 255),
0508: new Field("", "FKTABLE_SCHEM", Types.CHAR,
0509: 0),
0510: new Field("", "FKTABLE_NAME", Types.CHAR,
0511: 255),
0512: new Field("", "FKCOLUMN_NAME", Types.CHAR,
0513: 32),
0514: new Field("", "KEY_SEQ", Types.SMALLINT, 2),
0515: new Field("", "UPDATE_RULE",
0516: Types.SMALLINT, 2),
0517: new Field("", "DELETE_RULE",
0518: Types.SMALLINT, 2),
0519: new Field("", "FK_NAME", Types.CHAR, 0),
0520: new Field("", "PK_NAME", Types.CHAR, 0),
0521: new Field("", "DEFERRABILITY",
0522: Types.INTEGER, 2) });
0523:
0524: return rs;
0525: } finally {
0526: if (pStmt != null) {
0527: pStmt.close();
0528: }
0529: }
0530: }
0531:
0532: /**
0533: * Get a description of a foreign key columns that reference a table's
0534: * primary key columns (the foreign keys exported by a table). They are
0535: * ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ.
0536: * <P>
0537: * Each foreign key column description has the following columns:
0538: * <OL>
0539: * <li> <B>PKTABLE_CAT</B> String => primary key table catalog (may be
0540: * null) </li>
0541: * <li> <B>PKTABLE_SCHEM</B> String => primary key table schema (may be
0542: * null) </li>
0543: * <li> <B>PKTABLE_NAME</B> String => primary key table name </li>
0544: * <li> <B>PKCOLUMN_NAME</B> String => primary key column name </li>
0545: * <li> <B>FKTABLE_CAT</B> String => foreign key table catalog (may be
0546: * null) being exported (may be null) </li>
0547: * <li> <B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
0548: * null) being exported (may be null) </li>
0549: * <li> <B>FKTABLE_NAME</B> String => foreign key table name being exported
0550: * </li>
0551: * <li> <B>FKCOLUMN_NAME</B> String => foreign key column name being
0552: * exported </li>
0553: * <li> <B>KEY_SEQ</B> short => sequence number within foreign key </li>
0554: * <li> <B>UPDATE_RULE</B> short => What happens to foreign key when
0555: * primary is updated:
0556: * <UL>
0557: * <li> importedKeyCascade - change imported key to agree with primary key
0558: * update </li>
0559: * <li> importedKeyRestrict - do not allow update of primary key if it has
0560: * been imported </li>
0561: * <li> importedKeySetNull - change imported key to NULL if its primary key
0562: * has been updated </li>
0563: * </ul>
0564: * </li>
0565: * <li> <B>DELETE_RULE</B> short => What happens to the foreign key when
0566: * primary is deleted.
0567: * <UL>
0568: * <li> importedKeyCascade - delete rows that import a deleted key </li>
0569: * <li> importedKeyRestrict - do not allow delete of primary key if it has
0570: * been imported </li>
0571: * <li> importedKeySetNull - change imported key to NULL if its primary key
0572: * has been deleted </li>
0573: * </ul>
0574: * </li>
0575: * <li> <B>FK_NAME</B> String => foreign key identifier (may be null) </li>
0576: * <li> <B>PK_NAME</B> String => primary key identifier (may be null) </li>
0577: * </ol>
0578: * </p>
0579: *
0580: * @param catalog
0581: * a catalog name; "" retrieves those without a catalog
0582: * @param schema
0583: * a schema name pattern; "" retrieves those without a schema
0584: * @param table
0585: * a table name
0586: * @return ResultSet each row is a foreign key column description
0587: * @throws SQLException
0588: * if a database access error occurs
0589: * @see #getImportedKeys
0590: */
0591: public java.sql.ResultSet getExportedKeys(String catalog,
0592: String schema, String table) throws SQLException {
0593: // TODO: Can't determine actions using INFORMATION_SCHEMA yet...
0594:
0595: if (table == null) {
0596: throw SQLError.createSQLException("Table not specified.",
0597: SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
0598: }
0599:
0600: if (catalog == null) {
0601: if (this .conn.getNullCatalogMeansCurrent()) {
0602: catalog = this .database;
0603: }
0604: }
0605:
0606: //CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION
0607:
0608: String sql = "SELECT "
0609: + "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,"
0610: + "NULL AS PKTABLE_SCHEM,"
0611: + "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, "
0612: + "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, "
0613: + "A.TABLE_SCHEMA AS FKTABLE_CAT,"
0614: + "NULL AS FKTABLE_SCHEM,"
0615: + "A.TABLE_NAME AS FKTABLE_NAME,"
0616: + "A.COLUMN_NAME AS FKCOLUMN_NAME, "
0617: + "A.ORDINAL_POSITION AS KEY_SEQ,"
0618: + generateUpdateRuleClause()
0619: + " AS UPDATE_RULE,"
0620: + generateDeleteRuleClause()
0621: + " AS DELETE_RULE,"
0622: + "A.CONSTRAINT_NAME AS FK_NAME,"
0623: + "(SELECT CONSTRAINT_NAME FROM"
0624: + " INFORMATION_SCHEMA.TABLE_CONSTRAINTS"
0625: + " WHERE TABLE_SCHEMA = REFERENCED_TABLE_SCHEMA AND"
0626: + " TABLE_NAME = REFERENCED_TABLE_NAME AND"
0627: + " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)"
0628: + " AS PK_NAME,"
0629: + importedKeyNotDeferrable
0630: + " AS DEFERRABILITY "
0631: + "FROM "
0632: + "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN "
0633: + "INFORMATION_SCHEMA.TABLE_CONSTRAINTS B "
0634: + "USING (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) "
0635: + generateOptionalRefContraintsJoin()
0636: + "WHERE "
0637: + "B.CONSTRAINT_TYPE = 'FOREIGN KEY' "
0638: + "AND A.REFERENCED_TABLE_SCHEMA LIKE ? AND A.REFERENCED_TABLE_NAME=? "
0639: + "ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION";
0640:
0641: PreparedStatement pStmt = null;
0642:
0643: try {
0644: pStmt = prepareMetaDataSafeStatement(sql);
0645:
0646: if (catalog != null) {
0647: pStmt.setString(1, catalog);
0648: } else {
0649: pStmt.setString(1, "%");
0650: }
0651:
0652: pStmt.setString(2, table);
0653:
0654: ResultSet rs = executeMetadataQuery(pStmt);
0655:
0656: ((com.mysql.jdbc.ResultSetInternalMethods) rs)
0657: .redefineFieldsForDBMD(new Field[] {
0658: new Field("", "PKTABLE_CAT", Types.CHAR,
0659: 255),
0660: new Field("", "PKTABLE_SCHEM", Types.CHAR,
0661: 0),
0662: new Field("", "PKTABLE_NAME", Types.CHAR,
0663: 255),
0664: new Field("", "PKCOLUMN_NAME", Types.CHAR,
0665: 32),
0666: new Field("", "FKTABLE_CAT", Types.CHAR,
0667: 255),
0668: new Field("", "FKTABLE_SCHEM", Types.CHAR,
0669: 0),
0670: new Field("", "FKTABLE_NAME", Types.CHAR,
0671: 255),
0672: new Field("", "FKCOLUMN_NAME", Types.CHAR,
0673: 32),
0674: new Field("", "KEY_SEQ", Types.SMALLINT, 2),
0675: new Field("", "UPDATE_RULE",
0676: Types.SMALLINT, 2),
0677: new Field("", "DELETE_RULE",
0678: Types.SMALLINT, 2),
0679: new Field("", "FK_NAME", Types.CHAR, 255),
0680: new Field("", "PK_NAME", Types.CHAR, 0),
0681: new Field("", "DEFERRABILITY",
0682: Types.INTEGER, 2) });
0683:
0684: return rs;
0685: } finally {
0686: if (pStmt != null) {
0687: pStmt.close();
0688: }
0689: }
0690:
0691: }
0692:
0693: private String generateOptionalRefContraintsJoin() {
0694: return ((this .hasReferentialConstraintsView) ? "JOIN "
0695: + "INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R "
0696: + "ON (R.CONSTRAINT_NAME = B.CONSTRAINT_NAME "
0697: + "AND R.TABLE_NAME = B.TABLE_NAME AND "
0698: + "R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA) " : "");
0699: }
0700:
0701: private String generateDeleteRuleClause() {
0702: return ((this .hasReferentialConstraintsView) ? "CASE WHEN R.DELETE_RULE='CASCADE' THEN "
0703: + String.valueOf(importedKeyCascade)
0704: + " WHEN R.DELETE_RULE='SET NULL' THEN "
0705: + String.valueOf(importedKeySetNull)
0706: + " WHEN R.DELETE_RULE='SET DEFAULT' THEN "
0707: + String.valueOf(importedKeySetDefault)
0708: + " WHEN R.DELETE_RULE='RESTRICT' THEN "
0709: + String.valueOf(importedKeyRestrict)
0710: + " WHEN R.DELETE_RULE='NO ACTION' THEN "
0711: + String.valueOf(importedKeyNoAction)
0712: + " ELSE "
0713: + String.valueOf(importedKeyNoAction) + " END "
0714: : String.valueOf(importedKeyRestrict));
0715: }
0716:
0717: private String generateUpdateRuleClause() {
0718: return ((this .hasReferentialConstraintsView) ? "CASE WHEN R.UPDATE_RULE='CASCADE' THEN "
0719: + String.valueOf(importedKeyCascade)
0720: + " WHEN R.UPDATE_RULE='SET NULL' THEN "
0721: + String.valueOf(importedKeySetNull)
0722: + " WHEN R.UPDATE_RULE='SET DEFAULT' THEN "
0723: + String.valueOf(importedKeySetDefault)
0724: + " WHEN R.UPDATE_RULE='RESTRICT' THEN "
0725: + String.valueOf(importedKeyRestrict)
0726: + " WHEN R.UPDATE_RULE='NO ACTION' THEN "
0727: + String.valueOf(importedKeyNoAction)
0728: + " ELSE "
0729: + String.valueOf(importedKeyNoAction) + " END "
0730: : String.valueOf(importedKeyRestrict));
0731: }
0732:
0733: /**
0734: * Get a description of the primary key columns that are referenced by a
0735: * table's foreign key columns (the primary keys imported by a table). They
0736: * are ordered by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.
0737: * <P>
0738: * Each primary key column description has the following columns:
0739: * <OL>
0740: * <li> <B>PKTABLE_CAT</B> String => primary key table catalog being
0741: * imported (may be null) </li>
0742: * <li> <B>PKTABLE_SCHEM</B> String => primary key table schema being
0743: * imported (may be null) </li>
0744: * <li> <B>PKTABLE_NAME</B> String => primary key table name being imported
0745: * </li>
0746: * <li> <B>PKCOLUMN_NAME</B> String => primary key column name being
0747: * imported </li>
0748: * <li> <B>FKTABLE_CAT</B> String => foreign key table catalog (may be
0749: * null) </li>
0750: * <li> <B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
0751: * null) </li>
0752: * <li> <B>FKTABLE_NAME</B> String => foreign key table name </li>
0753: * <li> <B>FKCOLUMN_NAME</B> String => foreign key column name </li>
0754: * <li> <B>KEY_SEQ</B> short => sequence number within foreign key </li>
0755: * <li> <B>UPDATE_RULE</B> short => What happens to foreign key when
0756: * primary is updated:
0757: * <UL>
0758: * <li> importedKeyCascade - change imported key to agree with primary key
0759: * update </li>
0760: * <li> importedKeyRestrict - do not allow update of primary key if it has
0761: * been imported </li>
0762: * <li> importedKeySetNull - change imported key to NULL if its primary key
0763: * has been updated </li>
0764: * </ul>
0765: * </li>
0766: * <li> <B>DELETE_RULE</B> short => What happens to the foreign key when
0767: * primary is deleted.
0768: * <UL>
0769: * <li> importedKeyCascade - delete rows that import a deleted key </li>
0770: * <li> importedKeyRestrict - do not allow delete of primary key if it has
0771: * been imported </li>
0772: * <li> importedKeySetNull - change imported key to NULL if its primary key
0773: * has been deleted </li>
0774: * </ul>
0775: * </li>
0776: * <li> <B>FK_NAME</B> String => foreign key name (may be null) </li>
0777: * <li> <B>PK_NAME</B> String => primary key name (may be null) </li>
0778: * </ol>
0779: * </p>
0780: *
0781: * @param catalog
0782: * a catalog name; "" retrieves those without a catalog
0783: * @param schema
0784: * a schema name pattern; "" retrieves those without a schema
0785: * @param table
0786: * a table name
0787: * @return ResultSet each row is a primary key column description
0788: * @throws SQLException
0789: * if a database access error occurs
0790: * @see #getExportedKeys
0791: */
0792: public java.sql.ResultSet getImportedKeys(String catalog,
0793: String schema, String table) throws SQLException {
0794: if (table == null) {
0795: throw SQLError.createSQLException("Table not specified.",
0796: SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
0797: }
0798:
0799: if (catalog == null) {
0800: if (this .conn.getNullCatalogMeansCurrent()) {
0801: catalog = this .database;
0802: }
0803: }
0804:
0805: String sql = "SELECT "
0806: + "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,"
0807: + "NULL AS PKTABLE_SCHEM,"
0808: + "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,"
0809: + "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,"
0810: + "A.TABLE_SCHEMA AS FKTABLE_CAT,"
0811: + "NULL AS FKTABLE_SCHEM,"
0812: + "A.TABLE_NAME AS FKTABLE_NAME, "
0813: + "A.COLUMN_NAME AS FKCOLUMN_NAME, "
0814: + "A.ORDINAL_POSITION AS KEY_SEQ,"
0815: + generateUpdateRuleClause()
0816: + " AS UPDATE_RULE,"
0817: + generateDeleteRuleClause()
0818: + " AS DELETE_RULE,"
0819: + "A.CONSTRAINT_NAME AS FK_NAME,"
0820: + "(SELECT CONSTRAINT_NAME FROM"
0821: + " INFORMATION_SCHEMA.TABLE_CONSTRAINTS"
0822: + " WHERE TABLE_SCHEMA = REFERENCED_TABLE_SCHEMA AND"
0823: + " TABLE_NAME = REFERENCED_TABLE_NAME AND"
0824: + " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)"
0825: + " AS PK_NAME,"
0826: + importedKeyNotDeferrable
0827: + " AS DEFERRABILITY "
0828: + "FROM "
0829: + "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A "
0830: + "JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING "
0831: + "(CONSTRAINT_NAME, TABLE_NAME) "
0832: + generateOptionalRefContraintsJoin()
0833: + "WHERE "
0834: + "B.CONSTRAINT_TYPE = 'FOREIGN KEY' "
0835: + "AND A.TABLE_SCHEMA LIKE ? "
0836: + "AND A.TABLE_NAME=? "
0837: + "AND A.REFERENCED_TABLE_SCHEMA IS NOT NULL "
0838: + "ORDER BY "
0839: + "A.REFERENCED_TABLE_SCHEMA, A.REFERENCED_TABLE_NAME, "
0840: + "A.ORDINAL_POSITION";
0841:
0842: PreparedStatement pStmt = null;
0843:
0844: try {
0845: pStmt = prepareMetaDataSafeStatement(sql);
0846:
0847: if (catalog != null) {
0848: pStmt.setString(1, catalog);
0849: } else {
0850: pStmt.setString(1, "%");
0851: }
0852:
0853: pStmt.setString(2, table);
0854:
0855: ResultSet rs = executeMetadataQuery(pStmt);
0856:
0857: ((com.mysql.jdbc.ResultSetInternalMethods) rs)
0858: .redefineFieldsForDBMD(new Field[] {
0859: new Field("", "PKTABLE_CAT", Types.CHAR,
0860: 255),
0861: new Field("", "PKTABLE_SCHEM", Types.CHAR,
0862: 0),
0863: new Field("", "PKTABLE_NAME", Types.CHAR,
0864: 255),
0865: new Field("", "PKCOLUMN_NAME", Types.CHAR,
0866: 32),
0867: new Field("", "FKTABLE_CAT", Types.CHAR,
0868: 255),
0869: new Field("", "FKTABLE_SCHEM", Types.CHAR,
0870: 0),
0871: new Field("", "FKTABLE_NAME", Types.CHAR,
0872: 255),
0873: new Field("", "FKCOLUMN_NAME", Types.CHAR,
0874: 32),
0875: new Field("", "KEY_SEQ", Types.SMALLINT, 2),
0876: new Field("", "UPDATE_RULE",
0877: Types.SMALLINT, 2),
0878: new Field("", "DELETE_RULE",
0879: Types.SMALLINT, 2),
0880: new Field("", "FK_NAME", Types.CHAR, 255),
0881: new Field("", "PK_NAME", Types.CHAR, 0),
0882: new Field("", "DEFERRABILITY",
0883: Types.INTEGER, 2) });
0884:
0885: return rs;
0886: } finally {
0887: if (pStmt != null) {
0888: pStmt.close();
0889: }
0890: }
0891: }
0892:
0893: /**
0894: * Get a description of a table's indices and statistics. They are ordered
0895: * by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
0896: * <P>
0897: * Each index column description has the following columns:
0898: * <OL>
0899: * <li> <B>TABLE_CAT</B> String => table catalog (may be null) </li>
0900: * <li> <B>TABLE_SCHEM</B> String => table schema (may be null) </li>
0901: * <li> <B>TABLE_NAME</B> String => table name </li>
0902: * <li> <B>NON_UNIQUE</B> boolean => Can index values be non-unique? false
0903: * when TYPE is tableIndexStatistic </li>
0904: * <li> <B>INDEX_QUALIFIER</B> String => index catalog (may be null); null
0905: * when TYPE is tableIndexStatistic </li>
0906: * <li> <B>INDEX_NAME</B> String => index name; null when TYPE is
0907: * tableIndexStatistic </li>
0908: * <li> <B>TYPE</B> short => index type:
0909: * <UL>
0910: * <li> tableIndexStatistic - this identifies table statistics that are
0911: * returned in conjuction with a table's index descriptions </li>
0912: * <li> tableIndexClustered - this is a clustered index </li>
0913: * <li> tableIndexHashed - this is a hashed index </li>
0914: * <li> tableIndexOther - this is some other style of index </li>
0915: * </ul>
0916: * </li>
0917: * <li> <B>ORDINAL_POSITION</B> short => column sequence number within
0918: * index; zero when TYPE is tableIndexStatistic </li>
0919: * <li> <B>COLUMN_NAME</B> String => column name; null when TYPE is
0920: * tableIndexStatistic </li>
0921: * <li> <B>ASC_OR_DESC</B> String => column sort sequence, "A" =>
0922: * ascending, "D" => descending, may be null if sort sequence is not
0923: * supported; null when TYPE is tableIndexStatistic </li>
0924: * <li> <B>CARDINALITY</B> int => When TYPE is tableIndexStatisic then this
0925: * is the number of rows in the table; otherwise it is the number of unique
0926: * values in the index. </li>
0927: * <li> <B>PAGES</B> int => When TYPE is tableIndexStatisic then this is
0928: * the number of pages used for the table, otherwise it is the number of
0929: * pages used for the current index. </li>
0930: * <li> <B>FILTER_CONDITION</B> String => Filter condition, if any. (may be
0931: * null) </li>
0932: * </ol>
0933: * </p>
0934: *
0935: * @param catalog
0936: * a catalog name; "" retrieves those without a catalog
0937: * @param schema
0938: * a schema name pattern; "" retrieves those without a schema
0939: * @param table
0940: * a table name
0941: * @param unique
0942: * when true, return only indices for unique values; when false,
0943: * return indices regardless of whether unique or not
0944: * @param approximate
0945: * when true, result is allowed to reflect approximate or out of
0946: * data values; when false, results are requested to be accurate
0947: * @return ResultSet each row is an index column description
0948: * @throws SQLException
0949: * DOCUMENT ME!
0950: */
0951: public ResultSet getIndexInfo(String catalog, String schema,
0952: String table, boolean unique, boolean approximate)
0953: throws SQLException {
0954: StringBuffer sqlBuf = new StringBuffer("SELECT "
0955: + "TABLE_SCHEMA AS TABLE_CAT, "
0956: + "NULL AS TABLE_SCHEM," + "TABLE_NAME,"
0957: + "NON_UNIQUE," + "TABLE_SCHEMA AS INDEX_QUALIFIER,"
0958: + "INDEX_NAME," + tableIndexOther + " AS TYPE,"
0959: + "SEQ_IN_INDEX AS ORDINAL_POSITION," + "COLUMN_NAME,"
0960: + "COLLATION AS ASC_OR_DESC," + "CARDINALITY,"
0961: + "NULL AS PAGES," + "NULL AS FILTER_CONDITION "
0962: + "FROM INFORMATION_SCHEMA.STATISTICS WHERE "
0963: + "TABLE_SCHEMA LIKE ? AND " + "TABLE_NAME LIKE ?");
0964:
0965: if (unique) {
0966: sqlBuf.append(" AND NON_UNIQUE=0 ");
0967: }
0968:
0969: sqlBuf.append("ORDER BY NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX");
0970:
0971: PreparedStatement pStmt = null;
0972:
0973: try {
0974: if (catalog == null) {
0975: if (this .conn.getNullCatalogMeansCurrent()) {
0976: catalog = this .database;
0977: }
0978: }
0979:
0980: pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());
0981:
0982: if (catalog != null) {
0983: pStmt.setString(1, catalog);
0984: } else {
0985: pStmt.setString(1, "%");
0986: }
0987:
0988: pStmt.setString(2, table);
0989:
0990: ResultSet rs = executeMetadataQuery(pStmt);
0991:
0992: ((com.mysql.jdbc.ResultSetInternalMethods) rs)
0993: .redefineFieldsForDBMD(new Field[] {
0994: new Field("", "TABLE_CAT", Types.CHAR, 255),
0995: new Field("", "TABLE_SCHEM", Types.CHAR, 0),
0996: new Field("", "TABLE_NAME", Types.CHAR, 255),
0997: new Field("", "NON_UNIQUE", Types.CHAR, 4),
0998: new Field("", "INDEX_QUALIFIER",
0999: Types.CHAR, 1),
1000: new Field("", "INDEX_NAME", Types.CHAR, 32),
1001: new Field("", "TYPE", Types.CHAR, 32),
1002: new Field("", "ORDINAL_POSITION",
1003: Types.SMALLINT, 5),
1004: new Field("", "COLUMN_NAME", Types.CHAR, 32),
1005: new Field("", "ASC_OR_DESC", Types.CHAR, 1),
1006: new Field("", "CARDINALITY", Types.INTEGER,
1007: 10),
1008: new Field("", "PAGES", Types.INTEGER, 10),
1009: new Field("", "FILTER_CONDITION",
1010: Types.CHAR, 32) });
1011:
1012: return rs;
1013: } finally {
1014: if (pStmt != null) {
1015: pStmt.close();
1016: }
1017: }
1018: }
1019:
1020: /**
1021: * Get a description of a table's primary key columns. They are ordered by
1022: * COLUMN_NAME.
1023: * <P>
1024: * Each column description has the following columns:
1025: * <OL>
1026: * <li> <B>TABLE_CAT</B> String => table catalog (may be null) </li>
1027: * <li> <B>TABLE_SCHEM</B> String => table schema (may be null) </li>
1028: * <li> <B>TABLE_NAME</B> String => table name </li>
1029: * <li> <B>COLUMN_NAME</B> String => column name </li>
1030: * <li> <B>KEY_SEQ</B> short => sequence number within primary key </li>
1031: * <li> <B>PK_NAME</B> String => primary key name (may be null) </li>
1032: * </ol>
1033: * </p>
1034: *
1035: * @param catalog
1036: * a catalog name; "" retrieves those without a catalog
1037: * @param schema
1038: * a schema name pattern; "" retrieves those without a schema
1039: * @param table
1040: * a table name
1041: * @return ResultSet each row is a primary key column description
1042: * @throws SQLException
1043: * DOCUMENT ME!
1044: */
1045: public java.sql.ResultSet getPrimaryKeys(String catalog,
1046: String schema, String table) throws SQLException {
1047:
1048: if (catalog == null) {
1049: if (this .conn.getNullCatalogMeansCurrent()) {
1050: catalog = this .database;
1051: }
1052: }
1053:
1054: if (table == null) {
1055: throw SQLError.createSQLException("Table not specified.",
1056: SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
1057: }
1058:
1059: String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, "
1060: + "COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, 'PRIMARY' AS PK_NAME FROM INFORMATION_SCHEMA.STATISTICS "
1061: + "WHERE TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ? AND "
1062: + "INDEX_NAME='PRIMARY' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX";
1063:
1064: PreparedStatement pStmt = null;
1065:
1066: try {
1067: pStmt = prepareMetaDataSafeStatement(sql);
1068:
1069: if (catalog != null) {
1070: pStmt.setString(1, catalog);
1071: } else {
1072: pStmt.setString(1, "%");
1073: }
1074:
1075: pStmt.setString(2, table);
1076:
1077: ResultSet rs = executeMetadataQuery(pStmt);
1078: ((com.mysql.jdbc.ResultSetInternalMethods) rs)
1079: .redefineFieldsForDBMD(new Field[] {
1080: new Field("", "TABLE_CAT", Types.CHAR, 255),
1081: new Field("", "TABLE_SCHEM", Types.CHAR, 0),
1082: new Field("", "TABLE_NAME", Types.CHAR, 255),
1083: new Field("", "COLUMN_NAME", Types.CHAR, 32),
1084: new Field("", "KEY_SEQ", Types.SMALLINT, 5),
1085: new Field("", "PK_NAME", Types.CHAR, 32) });
1086:
1087: return rs;
1088: } finally {
1089: if (pStmt != null) {
1090: pStmt.close();
1091: }
1092: }
1093: }
1094:
1095: /**
1096: * Get a description of stored procedures available in a catalog.
1097: * <P>
1098: * Only procedure descriptions matching the schema and procedure name
1099: * criteria are returned. They are ordered by PROCEDURE_SCHEM, and
1100: * PROCEDURE_NAME.
1101: * </p>
1102: * <P>
1103: * Each procedure description has the the following columns:
1104: * <OL>
1105: * <li> <B>PROCEDURE_CAT</B> String => procedure catalog (may be null)
1106: * </li>
1107: * <li> <B>PROCEDURE_SCHEM</B> String => procedure schema (may be null)
1108: * </li>
1109: * <li> <B>PROCEDURE_NAME</B> String => procedure name </li>
1110: * <li> reserved for future use </li>
1111: * <li> reserved for future use </li>
1112: * <li> reserved for future use </li>
1113: * <li> <B>REMARKS</B> String => explanatory comment on the procedure </li>
1114: * <li> <B>PROCEDURE_TYPE</B> short => kind of procedure:
1115: * <UL>
1116: * <li> procedureResultUnknown - May return a result </li>
1117: * <li> procedureNoResult - Does not return a result </li>
1118: * <li> procedureReturnsResult - Returns a result </li>
1119: * </ul>
1120: * </li>
1121: * </ol>
1122: * </p>
1123: *
1124: * @param catalog
1125: * a catalog name; "" retrieves those without a catalog
1126: * @param schemaPattern
1127: * a schema name pattern; "" retrieves those without a schema
1128: * @param procedureNamePattern
1129: * a procedure name pattern
1130: * @return ResultSet each row is a procedure description
1131: * @throws SQLException
1132: * if a database access error occurs
1133: * @see #getSearchStringEscape
1134: */
1135: public ResultSet getProcedures(String catalog,
1136: String schemaPattern, String procedureNamePattern)
1137: throws SQLException {
1138:
1139: if ((procedureNamePattern == null)
1140: || (procedureNamePattern.length() == 0)) {
1141: if (this .conn.getNullNamePatternMatchesAll()) {
1142: procedureNamePattern = "%";
1143: } else {
1144: throw SQLError
1145: .createSQLException(
1146: "Procedure name pattern can not be NULL or empty.",
1147: SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
1148: }
1149: }
1150:
1151: String db = null;
1152:
1153: if (catalog == null) {
1154: if (this .conn.getNullCatalogMeansCurrent()) {
1155: db = this .database;
1156: }
1157: }
1158:
1159: String sql = "SELECT ROUTINE_SCHEMA AS PROCEDURE_CAT, "
1160: + "NULL AS PROCEDURE_SCHEM, "
1161: + "ROUTINE_NAME AS PROCEDURE_NAME, "
1162: + "NULL AS RESERVED_1, " + "NULL AS RESERVED_2, "
1163: + "NULL AS RESERVED_3, "
1164: + "ROUTINE_COMMENT AS REMARKS, "
1165: + "CASE WHEN ROUTINE_TYPE = 'PROCEDURE' THEN "
1166: + procedureNoResult
1167: + " WHEN ROUTINE_TYPE='FUNCTION' THEN "
1168: + procedureReturnsResult + " ELSE "
1169: + procedureResultUnknown + " END AS PROCEDURE_TYPE "
1170: + "FROM INFORMATION_SCHEMA.ROUTINES WHERE "
1171: + "ROUTINE_SCHEMA LIKE ? AND ROUTINE_NAME LIKE ? "
1172: + "ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME";
1173:
1174: PreparedStatement pStmt = null;
1175:
1176: try {
1177: pStmt = prepareMetaDataSafeStatement(sql);
1178:
1179: if (db != null) {
1180: pStmt.setString(1, db);
1181: } else {
1182: pStmt.setString(1, "%");
1183: }
1184:
1185: pStmt.setString(2, procedureNamePattern);
1186:
1187: ResultSet rs = executeMetadataQuery(pStmt);
1188: ((com.mysql.jdbc.ResultSetInternalMethods) rs)
1189: .redefineFieldsForDBMD(new Field[] {
1190: new Field("", "PROCEDURE_CAT", Types.CHAR,
1191: 0),
1192: new Field("", "PROCEDURE_SCHEM",
1193: Types.CHAR, 0),
1194: new Field("", "PROCEDURE_NAME", Types.CHAR,
1195: 0),
1196: new Field("", "reserved1", Types.CHAR, 0),
1197: new Field("", "reserved2", Types.CHAR, 0),
1198: new Field("", "reserved3", Types.CHAR, 0),
1199: new Field("", "REMARKS", Types.CHAR, 0),
1200: new Field("", "PROCEDURE_TYPE",
1201: Types.SMALLINT, 0) });
1202:
1203: return rs;
1204: } finally {
1205: if (pStmt != null) {
1206: pStmt.close();
1207: }
1208: }
1209: }
1210:
1211: /**
1212: * Get a description of tables available in a catalog.
1213: * <P>
1214: * Only table descriptions matching the catalog, schema, table name and type
1215: * criteria are returned. They are ordered by TABLE_TYPE, TABLE_SCHEM and
1216: * TABLE_NAME.
1217: * </p>
1218: * <P>
1219: * Each table description has the following columns:
1220: * <OL>
1221: * <li> <B>TABLE_CAT</B> String => table catalog (may be null) </li>
1222: * <li> <B>TABLE_SCHEM</B> String => table schema (may be null) </li>
1223: * <li> <B>TABLE_NAME</B> String => table name </li>
1224: * <li> <B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
1225: * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS",
1226: * "SYNONYM". </li>
1227: * <li> <B>REMARKS</B> String => explanatory comment on the table </li>
1228: * </ol>
1229: * </p>
1230: * <P>
1231: * <B>Note:</B> Some databases may not return information for all tables.
1232: * </p>
1233: *
1234: * @param catalog
1235: * a catalog name; "" retrieves those without a catalog
1236: * @param schemaPattern
1237: * a schema name pattern; "" retrieves those without a schema
1238: * @param tableNamePattern
1239: * a table name pattern
1240: * @param types
1241: * a list of table types to include; null returns all types
1242: * @return ResultSet each row is a table description
1243: * @throws SQLException
1244: * DOCUMENT ME!
1245: * @see #getSearchStringEscape
1246: */
1247: public ResultSet getTables(String catalog, String schemaPattern,
1248: String tableNamePattern, String[] types)
1249: throws SQLException {
1250: if (catalog == null) {
1251: if (this .conn.getNullCatalogMeansCurrent()) {
1252: catalog = this .database;
1253: }
1254: }
1255:
1256: if (tableNamePattern == null) {
1257: if (this .conn.getNullNamePatternMatchesAll()) {
1258: tableNamePattern = "%";
1259: } else {
1260: throw SQLError.createSQLException(
1261: "Table name pattern can not be NULL or empty.",
1262: SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
1263: }
1264: }
1265:
1266: PreparedStatement pStmt = null;
1267:
1268: String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, "
1269: + "NULL AS TABLE_SCHEM, TABLE_NAME, "
1270: + "CASE WHEN TABLE_TYPE='BASE TABLE' THEN 'TABLE' WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, "
1271: + "TABLE_COMMENT AS REMARKS "
1272: + "FROM INFORMATION_SCHEMA.TABLES WHERE "
1273: + "TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ? AND TABLE_TYPE IN (?,?,?) "
1274: + "ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME";
1275: try {
1276: pStmt = prepareMetaDataSafeStatement(sql);
1277:
1278: if (catalog != null) {
1279: pStmt.setString(1, catalog);
1280: } else {
1281: pStmt.setString(1, "%");
1282: }
1283:
1284: pStmt.setString(2, tableNamePattern);
1285:
1286: // This overloading of IN (...) allows us to cache this
1287: // prepared statement
1288: if (types == null || types.length == 0) {
1289: pStmt.setString(3, "BASE TABLE");
1290: pStmt.setString(4, "VIEW");
1291: pStmt.setString(5, "TEMPORARY");
1292: } else {
1293: pStmt.setNull(3, Types.VARCHAR);
1294: pStmt.setNull(4, Types.VARCHAR);
1295: pStmt.setNull(5, Types.VARCHAR);
1296:
1297: for (int i = 0; i < types.length; i++) {
1298: if ("TABLE".equalsIgnoreCase(types[i])) {
1299: pStmt.setString(3, "BASE TABLE");
1300: }
1301:
1302: if ("VIEW".equalsIgnoreCase(types[i])) {
1303: pStmt.setString(4, "VIEW");
1304: }
1305:
1306: if ("LOCAL TEMPORARY".equalsIgnoreCase(types[i])) {
1307: pStmt.setString(5, "TEMPORARY");
1308: }
1309: }
1310: }
1311:
1312: ResultSet rs = executeMetadataQuery(pStmt);
1313:
1314: ((com.mysql.jdbc.ResultSetInternalMethods) rs)
1315: .redefineFieldsForDBMD(new Field[] {
1316: new Field("", "TABLE_CAT",
1317: java.sql.Types.VARCHAR,
1318: (catalog == null) ? 0 : catalog
1319: .length()),
1320: new Field("", "TABLE_SCHEM",
1321: java.sql.Types.VARCHAR, 0),
1322: new Field("", "TABLE_NAME",
1323: java.sql.Types.VARCHAR, 255),
1324: new Field("", "TABLE_TYPE",
1325: java.sql.Types.VARCHAR, 5),
1326: new Field("", "REMARKS",
1327: java.sql.Types.VARCHAR, 0) });
1328:
1329: return rs;
1330: } finally {
1331: if (pStmt != null) {
1332: pStmt.close();
1333: }
1334: }
1335: }
1336:
1337: private PreparedStatement prepareMetaDataSafeStatement(String sql)
1338: throws SQLException {
1339: // Can't use server-side here as we coerce a lot of types to match
1340: // the spec.
1341: PreparedStatement pStmt = this .conn.clientPrepareStatement(sql);
1342:
1343: if (pStmt.getMaxRows() != 0) {
1344: pStmt.setMaxRows(0);
1345: }
1346:
1347: pStmt.setHoldResultsOpenOverClose(true);
1348:
1349: return pStmt;
1350: }
1351: }
|