0001: /*-------------------------------------------------------------------------
0002: *
0003: * Copyright (c) 2004-2005, PostgreSQL Global Development Group
0004: *
0005: * IDENTIFICATION
0006: * $PostgreSQL: pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v 1.37 2007/07/23 17:30:38 jurka Exp $
0007: *
0008: *-------------------------------------------------------------------------
0009: */
0010: package org.postgresql.jdbc2;
0011:
0012: import java.sql.*;
0013: import java.util.*;
0014: import org.postgresql.core.*;
0015: import org.postgresql.util.PSQLException;
0016: import org.postgresql.util.PSQLState;
0017: import org.postgresql.Driver;
0018: import org.postgresql.util.GT;
0019:
0020: public abstract class AbstractJdbc2DatabaseMetaData {
0021:
0022: public AbstractJdbc2DatabaseMetaData(AbstractJdbc2Connection conn) {
0023: this .connection = conn;
0024: }
0025:
0026: private static final String keywords = "abort,acl,add,aggregate,append,archive,"
0027: + "arch_store,backward,binary,boolean,change,cluster,"
0028: + "copy,database,delimiter,delimiters,do,extend,"
0029: + "explain,forward,heavy,index,inherits,isnull,"
0030: + "light,listen,load,merge,nothing,notify,"
0031: + "notnull,oids,purge,rename,replace,retrieve,"
0032: + "returns,rule,recipe,setof,stdin,stdout,store,"
0033: + "vacuum,verbose,version";
0034:
0035: protected final AbstractJdbc2Connection connection; // The connection association
0036:
0037: private int NAMEDATALEN = 0; // length for name datatype
0038: private int INDEX_MAX_KEYS = 0; // maximum number of keys in an index.
0039:
0040: protected int getMaxIndexKeys() throws SQLException {
0041: if (INDEX_MAX_KEYS == 0) {
0042: String sql;
0043: if (connection.haveMinimumServerVersion("8.0")) {
0044: sql = "SELECT setting FROM pg_catalog.pg_settings WHERE name='max_index_keys'";
0045: } else {
0046: String from;
0047: if (connection.haveMinimumServerVersion("7.3")) {
0048: from = "pg_catalog.pg_namespace n, pg_catalog.pg_type t1, pg_catalog.pg_type t2 WHERE t1.typnamespace=n.oid AND n.nspname='pg_catalog' AND ";
0049: } else {
0050: from = "pg_type t1, pg_type t2 WHERE ";
0051: }
0052: sql = "SELECT t1.typlen/t2.typlen FROM "
0053: + from
0054: + " t1.typelem=t2.oid AND t1.typname='oidvector'";
0055: }
0056: ResultSet rs = connection.createStatement().executeQuery(
0057: sql);
0058: if (!rs.next()) {
0059: throw new PSQLException(
0060: GT
0061: .tr("Unable to determine a value for MaxIndexKeys due to missing system catalog data."),
0062: PSQLState.UNEXPECTED_ERROR);
0063: }
0064: INDEX_MAX_KEYS = rs.getInt(1);
0065: rs.close();
0066: }
0067: return INDEX_MAX_KEYS;
0068: }
0069:
0070: protected int getMaxNameLength() throws SQLException {
0071: if (NAMEDATALEN == 0) {
0072: String sql;
0073: if (connection.haveMinimumServerVersion("7.3")) {
0074: sql = "SELECT t.typlen FROM pg_catalog.pg_type t, pg_catalog.pg_namespace n WHERE t.typnamespace=n.oid AND t.typname='name' AND n.nspname='pg_catalog'";
0075: } else {
0076: sql = "SELECT typlen FROM pg_type WHERE typname='name'";
0077: }
0078: ResultSet rs = connection.createStatement().executeQuery(
0079: sql);
0080: if (!rs.next()) {
0081: throw new PSQLException(
0082: GT
0083: .tr("Unable to find name datatype in the system catalogs."),
0084: PSQLState.UNEXPECTED_ERROR);
0085: }
0086: NAMEDATALEN = rs.getInt("typlen");
0087: rs.close();
0088: }
0089: return NAMEDATALEN - 1;
0090: }
0091:
0092: /*
0093: * Can all the procedures returned by getProcedures be called
0094: * by the current user?
0095: *
0096: * @return true if so
0097: * @exception SQLException if a database access error occurs
0098: */
0099: public boolean allProceduresAreCallable() throws SQLException {
0100: return true; // For now...
0101: }
0102:
0103: /*
0104: * Can all the tables returned by getTable be SELECTed by
0105: * the current user?
0106: *
0107: * @return true if so
0108: * @exception SQLException if a database access error occurs
0109: */
0110: public boolean allTablesAreSelectable() throws SQLException {
0111: return true; // For now...
0112: }
0113:
0114: /*
0115: * What is the URL for this database?
0116: *
0117: * @return the url or null if it cannott be generated
0118: * @exception SQLException if a database access error occurs
0119: */
0120: public String getURL() throws SQLException {
0121: return connection.getURL();
0122: }
0123:
0124: /*
0125: * What is our user name as known to the database?
0126: *
0127: * @return our database user name
0128: * @exception SQLException if a database access error occurs
0129: */
0130: public String getUserName() throws SQLException {
0131: return connection.getUserName();
0132: }
0133:
0134: /*
0135: * Is the database in read-only mode?
0136: *
0137: * @return true if so
0138: * @exception SQLException if a database access error occurs
0139: */
0140: public boolean isReadOnly() throws SQLException {
0141: return connection.isReadOnly();
0142: }
0143:
0144: /*
0145: * Are NULL values sorted high?
0146: *
0147: * @return true if so
0148: * @exception SQLException if a database access error occurs
0149: */
0150: public boolean nullsAreSortedHigh() throws SQLException {
0151: return connection.haveMinimumServerVersion("7.2");
0152: }
0153:
0154: /*
0155: * Are NULL values sorted low?
0156: *
0157: * @return true if so
0158: * @exception SQLException if a database access error occurs
0159: */
0160: public boolean nullsAreSortedLow() throws SQLException {
0161: return false;
0162: }
0163:
0164: /*
0165: * Are NULL values sorted at the start regardless of sort order?
0166: *
0167: * @return true if so
0168: * @exception SQLException if a database access error occurs
0169: */
0170: public boolean nullsAreSortedAtStart() throws SQLException {
0171: return false;
0172: }
0173:
0174: /*
0175: * Are NULL values sorted at the end regardless of sort order?
0176: *
0177: * @return true if so
0178: * @exception SQLException if a database access error occurs
0179: */
0180: public boolean nullsAreSortedAtEnd() throws SQLException {
0181: return !connection.haveMinimumServerVersion("7.2");
0182: }
0183:
0184: /*
0185: * What is the name of this database product - we hope that it is
0186: * PostgreSQL, so we return that explicitly.
0187: *
0188: * @return the database product name
0189: * @exception SQLException if a database access error occurs
0190: */
0191: public String getDatabaseProductName() throws SQLException {
0192: return "PostgreSQL";
0193: }
0194:
0195: /*
0196: * What is the version of this database product.
0197: *
0198: * @return the database version
0199: * @exception SQLException if a database access error occurs
0200: */
0201: public String getDatabaseProductVersion() throws SQLException {
0202: return connection.getDBVersionNumber();
0203: }
0204:
0205: /*
0206: * What is the name of this JDBC driver? If we don't know this
0207: * we are doing something wrong!
0208: *
0209: * @return the JDBC driver name
0210: * @exception SQLException why?
0211: */
0212: public String getDriverName() throws SQLException {
0213: return "PostgreSQL Native Driver";
0214: }
0215:
0216: /*
0217: * What is the version string of this JDBC driver? Again, this is
0218: * static.
0219: *
0220: * @return the JDBC driver name.
0221: * @exception SQLException why?
0222: */
0223: public String getDriverVersion() throws SQLException {
0224: return Driver.getVersion();
0225: }
0226:
0227: /*
0228: * What is this JDBC driver's major version number?
0229: *
0230: * @return the JDBC driver major version
0231: */
0232: public int getDriverMajorVersion() {
0233: return Driver.MAJORVERSION;
0234: }
0235:
0236: /*
0237: * What is this JDBC driver's minor version number?
0238: *
0239: * @return the JDBC driver minor version
0240: */
0241: public int getDriverMinorVersion() {
0242: return Driver.MINORVERSION;
0243: }
0244:
0245: /*
0246: * Does the database store tables in a local file? No - it
0247: * stores them in a file on the server.
0248: *
0249: * @return true if so
0250: * @exception SQLException if a database access error occurs
0251: */
0252: public boolean usesLocalFiles() throws SQLException {
0253: return false;
0254: }
0255:
0256: /*
0257: * Does the database use a file for each table? Well, not really,
0258: * since it doesnt use local files.
0259: *
0260: * @return true if so
0261: * @exception SQLException if a database access error occurs
0262: */
0263: public boolean usesLocalFilePerTable() throws SQLException {
0264: return false;
0265: }
0266:
0267: /*
0268: * Does the database treat mixed case unquoted SQL identifiers
0269: * as case sensitive and as a result store them in mixed case?
0270: * A JDBC-Compliant driver will always return false.
0271: *
0272: * @return true if so
0273: * @exception SQLException if a database access error occurs
0274: */
0275: public boolean supportsMixedCaseIdentifiers() throws SQLException {
0276: return false;
0277: }
0278:
0279: /*
0280: * Does the database treat mixed case unquoted SQL identifiers as
0281: * case insensitive and store them in upper case?
0282: *
0283: * @return true if so
0284: */
0285: public boolean storesUpperCaseIdentifiers() throws SQLException {
0286: return false;
0287: }
0288:
0289: /*
0290: * Does the database treat mixed case unquoted SQL identifiers as
0291: * case insensitive and store them in lower case?
0292: *
0293: * @return true if so
0294: */
0295: public boolean storesLowerCaseIdentifiers() throws SQLException {
0296: return true;
0297: }
0298:
0299: /*
0300: * Does the database treat mixed case unquoted SQL identifiers as
0301: * case insensitive and store them in mixed case?
0302: *
0303: * @return true if so
0304: */
0305: public boolean storesMixedCaseIdentifiers() throws SQLException {
0306: return false;
0307: }
0308:
0309: /*
0310: * Does the database treat mixed case quoted SQL identifiers as
0311: * case sensitive and as a result store them in mixed case? A
0312: * JDBC compliant driver will always return true.
0313: *
0314: * @return true if so
0315: * @exception SQLException if a database access error occurs
0316: */
0317: public boolean supportsMixedCaseQuotedIdentifiers()
0318: throws SQLException {
0319: return true;
0320: }
0321:
0322: /*
0323: * Does the database treat mixed case quoted SQL identifiers as
0324: * case insensitive and store them in upper case?
0325: *
0326: * @return true if so
0327: */
0328: public boolean storesUpperCaseQuotedIdentifiers()
0329: throws SQLException {
0330: return false;
0331: }
0332:
0333: /*
0334: * Does the database treat mixed case quoted SQL identifiers as case
0335: * insensitive and store them in lower case?
0336: *
0337: * @return true if so
0338: */
0339: public boolean storesLowerCaseQuotedIdentifiers()
0340: throws SQLException {
0341: return false;
0342: }
0343:
0344: /*
0345: * Does the database treat mixed case quoted SQL identifiers as case
0346: * insensitive and store them in mixed case?
0347: *
0348: * @return true if so
0349: */
0350: public boolean storesMixedCaseQuotedIdentifiers()
0351: throws SQLException {
0352: return false;
0353: }
0354:
0355: /*
0356: * What is the string used to quote SQL identifiers? This returns
0357: * a space if identifier quoting isn't supported. A JDBC Compliant
0358: * driver will always use a double quote character.
0359: *
0360: * @return the quoting string
0361: * @exception SQLException if a database access error occurs
0362: */
0363: public String getIdentifierQuoteString() throws SQLException {
0364: return "\"";
0365: }
0366:
0367: /*
0368: * Get a comma separated list of all a database's SQL keywords that
0369: * are NOT also SQL92 keywords.
0370: *
0371: * <p>Within PostgreSQL, the keywords are found in
0372: * src/backend/parser/keywords.c
0373: *
0374: * <p>For SQL Keywords, I took the list provided at
0375: * <a href="http://web.dementia.org/~shadow/sql/sql3bnf.sep93.txt">
0376: * http://web.dementia.org/~shadow/sql/sql3bnf.sep93.txt</a>
0377: * which is for SQL3, not SQL-92, but it is close enough for
0378: * this purpose.
0379: *
0380: * @return a comma separated list of keywords we use
0381: * @exception SQLException if a database access error occurs
0382: */
0383: public String getSQLKeywords() throws SQLException {
0384: return keywords;
0385: }
0386:
0387: /**
0388: * get supported escaped numeric functions
0389: * @return a comma separated list of function names
0390: */
0391: public String getNumericFunctions() throws SQLException {
0392: return EscapedFunctions.ABS + ',' + EscapedFunctions.ACOS + ','
0393: + EscapedFunctions.ASIN + ',' + EscapedFunctions.ATAN
0394: + ',' + EscapedFunctions.ATAN2 + ','
0395: + EscapedFunctions.CEILING + ',' + EscapedFunctions.COS
0396: + ',' + EscapedFunctions.COT + ','
0397: + EscapedFunctions.DEGREES + ',' + EscapedFunctions.EXP
0398: + ',' + EscapedFunctions.FLOOR + ','
0399: + EscapedFunctions.LOG + ',' + EscapedFunctions.LOG10
0400: + ',' + EscapedFunctions.MOD + ','
0401: + EscapedFunctions.PI + ',' + EscapedFunctions.POWER
0402: + ',' + EscapedFunctions.RADIANS + ','
0403: + EscapedFunctions.ROUND + ',' + EscapedFunctions.SIGN
0404: + ',' + EscapedFunctions.SIN + ','
0405: + EscapedFunctions.SQRT + ',' + EscapedFunctions.TAN
0406: + ',' + EscapedFunctions.TRUNCATE;
0407:
0408: }
0409:
0410: public String getStringFunctions() throws SQLException {
0411: String funcs = EscapedFunctions.ASCII + ','
0412: + EscapedFunctions.CHAR + ',' + EscapedFunctions.CONCAT
0413: + ',' + EscapedFunctions.LCASE + ','
0414: + EscapedFunctions.LEFT + ',' + EscapedFunctions.LENGTH
0415: + ',' + EscapedFunctions.LTRIM + ','
0416: + EscapedFunctions.REPEAT + ','
0417: + EscapedFunctions.RTRIM + ',' + EscapedFunctions.SPACE
0418: + ',' + EscapedFunctions.SUBSTRING + ','
0419: + EscapedFunctions.UCASE;
0420:
0421: // Currently these don't work correctly with parameterized
0422: // arguments, so leave them out. They reorder the arguments
0423: // when rewriting the query, but no translation layer is provided,
0424: // so a setObject(N, obj) will not go to the correct parameter.
0425: //','+EscapedFunctions.INSERT+','+EscapedFunctions.LOCATE+
0426: //','+EscapedFunctions.RIGHT+
0427:
0428: if (connection.haveMinimumServerVersion("7.3")) {
0429: funcs += ',' + EscapedFunctions.REPLACE;
0430: }
0431:
0432: return funcs;
0433: }
0434:
0435: public String getSystemFunctions() throws SQLException {
0436: if (connection.haveMinimumServerVersion("7.3")) {
0437: return EscapedFunctions.DATABASE + ','
0438: + EscapedFunctions.IFNULL + ','
0439: + EscapedFunctions.USER;
0440: } else {
0441: return EscapedFunctions.IFNULL + ','
0442: + EscapedFunctions.USER;
0443: }
0444: }
0445:
0446: public String getTimeDateFunctions() throws SQLException {
0447: String timeDateFuncs = EscapedFunctions.CURDATE + ','
0448: + EscapedFunctions.CURTIME + ','
0449: + EscapedFunctions.DAYNAME + ','
0450: + EscapedFunctions.DAYOFMONTH + ','
0451: + EscapedFunctions.DAYOFWEEK + ','
0452: + EscapedFunctions.DAYOFYEAR + ','
0453: + EscapedFunctions.HOUR + ',' + EscapedFunctions.MINUTE
0454: + ',' + EscapedFunctions.MONTH + ','
0455: + EscapedFunctions.MONTHNAME + ','
0456: + EscapedFunctions.NOW + ',' + EscapedFunctions.QUARTER
0457: + ',' + EscapedFunctions.SECOND + ','
0458: + EscapedFunctions.WEEK + ',' + EscapedFunctions.YEAR;
0459:
0460: if (connection.haveMinimumServerVersion("8.0")) {
0461: timeDateFuncs += ',' + EscapedFunctions.TIMESTAMPADD;
0462: }
0463:
0464: //+','+EscapedFunctions.TIMESTAMPDIFF;
0465:
0466: return timeDateFuncs;
0467: }
0468:
0469: /*
0470: * This is the string that can be used to escape '_' and '%' in
0471: * a search string pattern style catalog search parameters
0472: *
0473: * @return the string used to escape wildcard characters
0474: * @exception SQLException if a database access error occurs
0475: */
0476: public String getSearchStringEscape() throws SQLException {
0477: // This method originally returned "\\\\" assuming that it
0478: // would be fed directly into pg's input parser so it would
0479: // need two backslashes. This isn't how it's supposed to be
0480: // used though. If passed as a PreparedStatement parameter
0481: // or fed to a DatabaseMetaData method then double backslashes
0482: // are incorrect. If you're feeding something directly into
0483: // a query you are responsible for correctly escaping it.
0484: // With 8.2+ this escaping is a little trickier because you
0485: // must know the setting of standard_conforming_strings, but
0486: // that's not our problem.
0487:
0488: return "\\";
0489: }
0490:
0491: /*
0492: * Get all the "extra" characters that can be used in unquoted
0493: * identifier names (those beyond a-zA-Z0-9 and _)
0494: *
0495: * <p>Postgresql allows any high-bit character to be used
0496: * in an unquoted identifer, so we can't possibly list them all.
0497: *
0498: * From the file src/backend/parser/scan.l, an identifier is
0499: * ident_start [A-Za-z\200-\377_]
0500: * ident_cont [A-Za-z\200-\377_0-9\$]
0501: * identifier {ident_start}{ident_cont}*
0502: *
0503: * @return a string containing the extra characters
0504: * @exception SQLException if a database access error occurs
0505: */
0506: public String getExtraNameCharacters() throws SQLException {
0507: return "";
0508: }
0509:
0510: /*
0511: * Is "ALTER TABLE" with an add column supported?
0512: * Yes for PostgreSQL 6.1
0513: *
0514: * @return true if so
0515: * @exception SQLException if a database access error occurs
0516: */
0517: public boolean supportsAlterTableWithAddColumn()
0518: throws SQLException {
0519: return true;
0520: }
0521:
0522: /*
0523: * Is "ALTER TABLE" with a drop column supported?
0524: *
0525: * @return true if so
0526: * @exception SQLException if a database access error occurs
0527: */
0528: public boolean supportsAlterTableWithDropColumn()
0529: throws SQLException {
0530: return connection.haveMinimumServerVersion("7.3");
0531: }
0532:
0533: /*
0534: * Is column aliasing supported?
0535: *
0536: * <p>If so, the SQL AS clause can be used to provide names for
0537: * computed columns or to provide alias names for columns as
0538: * required. A JDBC Compliant driver always returns true.
0539: *
0540: * <p>e.g.
0541: *
0542: * <br><pre>
0543: * select count(C) as C_COUNT from T group by C;
0544: *
0545: * </pre><br>
0546: * should return a column named as C_COUNT instead of count(C)
0547: *
0548: * @return true if so
0549: * @exception SQLException if a database access error occurs
0550: */
0551: public boolean supportsColumnAliasing() throws SQLException {
0552: return true;
0553: }
0554:
0555: /*
0556: * Are concatenations between NULL and non-NULL values NULL? A
0557: * JDBC Compliant driver always returns true
0558: *
0559: * @return true if so
0560: * @exception SQLException if a database access error occurs
0561: */
0562: public boolean nullPlusNonNullIsNull() throws SQLException {
0563: return true;
0564: }
0565:
0566: public boolean supportsConvert() throws SQLException {
0567: return false;
0568: }
0569:
0570: public boolean supportsConvert(int fromType, int toType)
0571: throws SQLException {
0572: return false;
0573: }
0574:
0575: /*
0576: * Are table correlation names supported? A JDBC Compliant
0577: * driver always returns true.
0578: *
0579: * @return true if so; false otherwise
0580: * @exception SQLException - if a database access error occurs
0581: */
0582: public boolean supportsTableCorrelationNames() throws SQLException {
0583: return true;
0584: }
0585:
0586: /*
0587: * If table correlation names are supported, are they restricted to
0588: * be different from the names of the tables?
0589: *
0590: * @return true if so; false otherwise
0591: * @exception SQLException - if a database access error occurs
0592: */
0593: public boolean supportsDifferentTableCorrelationNames()
0594: throws SQLException {
0595: return false;
0596: }
0597:
0598: /*
0599: * Are expressions in "ORDER BY" lists supported?
0600: *
0601: * <br>e.g. select * from t order by a + b;
0602: *
0603: * @return true if so
0604: * @exception SQLException if a database access error occurs
0605: */
0606: public boolean supportsExpressionsInOrderBy() throws SQLException {
0607: return true;
0608: }
0609:
0610: /*
0611: * Can an "ORDER BY" clause use columns not in the SELECT?
0612: *
0613: * @return true if so
0614: * @exception SQLException if a database access error occurs
0615: */
0616: public boolean supportsOrderByUnrelated() throws SQLException {
0617: return connection.haveMinimumServerVersion("6.4");
0618: }
0619:
0620: /*
0621: * Is some form of "GROUP BY" clause supported?
0622: * I checked it, and yes it is.
0623: *
0624: * @return true if so
0625: * @exception SQLException if a database access error occurs
0626: */
0627: public boolean supportsGroupBy() throws SQLException {
0628: return true;
0629: }
0630:
0631: /*
0632: * Can a "GROUP BY" clause use columns not in the SELECT?
0633: *
0634: * @return true if so
0635: * @exception SQLException if a database access error occurs
0636: */
0637: public boolean supportsGroupByUnrelated() throws SQLException {
0638: return connection.haveMinimumServerVersion("6.4");
0639: }
0640:
0641: /*
0642: * Can a "GROUP BY" clause add columns not in the SELECT provided
0643: * it specifies all the columns in the SELECT? Does anyone actually
0644: * understand what they mean here?
0645: *
0646: * (I think this is a subset of the previous function. -- petere)
0647: *
0648: * @return true if so
0649: * @exception SQLException if a database access error occurs
0650: */
0651: public boolean supportsGroupByBeyondSelect() throws SQLException {
0652: return connection.haveMinimumServerVersion("6.4");
0653: }
0654:
0655: /*
0656: * Is the escape character in "LIKE" clauses supported? A
0657: * JDBC compliant driver always returns true.
0658: *
0659: * @return true if so
0660: * @exception SQLException if a database access error occurs
0661: */
0662: public boolean supportsLikeEscapeClause() throws SQLException {
0663: return connection.haveMinimumServerVersion("7.1");
0664: }
0665:
0666: /*
0667: * Are multiple ResultSets from a single execute supported?
0668: *
0669: * @return true if so
0670: * @exception SQLException if a database access error occurs
0671: */
0672: public boolean supportsMultipleResultSets() throws SQLException {
0673: return true;
0674: }
0675:
0676: /*
0677: * Can we have multiple transactions open at once (on different
0678: * connections?)
0679: * I guess we can have, since Im relying on it.
0680: *
0681: * @return true if so
0682: * @exception SQLException if a database access error occurs
0683: */
0684: public boolean supportsMultipleTransactions() throws SQLException {
0685: return true;
0686: }
0687:
0688: /*
0689: * Can columns be defined as non-nullable. A JDBC Compliant driver
0690: * always returns true.
0691: *
0692: * <p>This changed from false to true in v6.2 of the driver, as this
0693: * support was added to the backend.
0694: *
0695: * @return true if so
0696: * @exception SQLException if a database access error occurs
0697: */
0698: public boolean supportsNonNullableColumns() throws SQLException {
0699: return true;
0700: }
0701:
0702: /*
0703: * Does this driver support the minimum ODBC SQL grammar. This
0704: * grammar is defined at:
0705: *
0706: * <p><a href="http://www.microsoft.com/msdn/sdk/platforms/doc/odbc/src/intropr.htm">http://www.microsoft.com/msdn/sdk/platforms/doc/odbc/src/intropr.htm</a>
0707: *
0708: * <p>In Appendix C. From this description, we seem to support the
0709: * ODBC minimal (Level 0) grammar.
0710: *
0711: * @return true if so
0712: * @exception SQLException if a database access error occurs
0713: */
0714: public boolean supportsMinimumSQLGrammar() throws SQLException {
0715: return true;
0716: }
0717:
0718: /*
0719: * Does this driver support the Core ODBC SQL grammar. We need
0720: * SQL-92 conformance for this.
0721: *
0722: * @return true if so
0723: * @exception SQLException if a database access error occurs
0724: */
0725: public boolean supportsCoreSQLGrammar() throws SQLException {
0726: return false;
0727: }
0728:
0729: /*
0730: * Does this driver support the Extended (Level 2) ODBC SQL
0731: * grammar. We don't conform to the Core (Level 1), so we can't
0732: * conform to the Extended SQL Grammar.
0733: *
0734: * @return true if so
0735: * @exception SQLException if a database access error occurs
0736: */
0737: public boolean supportsExtendedSQLGrammar() throws SQLException {
0738: return false;
0739: }
0740:
0741: /*
0742: * Does this driver support the ANSI-92 entry level SQL grammar?
0743: * All JDBC Compliant drivers must return true. We currently
0744: * report false until 'schema' support is added. Then this
0745: * should be changed to return true, since we will be mostly
0746: * compliant (probably more compliant than many other databases)
0747: * And since this is a requirement for all JDBC drivers we
0748: * need to get to the point where we can return true.
0749: *
0750: * @return true if so
0751: * @exception SQLException if a database access error occurs
0752: */
0753: public boolean supportsANSI92EntryLevelSQL() throws SQLException {
0754: return connection.haveMinimumServerVersion("7.3");
0755: }
0756:
0757: /*
0758: * Does this driver support the ANSI-92 intermediate level SQL
0759: * grammar?
0760: *
0761: * @return true if so
0762: * @exception SQLException if a database access error occurs
0763: */
0764: public boolean supportsANSI92IntermediateSQL() throws SQLException {
0765: return false;
0766: }
0767:
0768: /*
0769: * Does this driver support the ANSI-92 full SQL grammar?
0770: *
0771: * @return true if so
0772: * @exception SQLException if a database access error occurs
0773: */
0774: public boolean supportsANSI92FullSQL() throws SQLException {
0775: return false;
0776: }
0777:
0778: /*
0779: * Is the SQL Integrity Enhancement Facility supported?
0780: * Our best guess is that this means support for constraints
0781: *
0782: * @return true if so
0783: * @exception SQLException if a database access error occurs
0784: */
0785: public boolean supportsIntegrityEnhancementFacility()
0786: throws SQLException {
0787: return true;
0788: }
0789:
0790: /*
0791: * Is some form of outer join supported?
0792: *
0793: * @return true if so
0794: * @exception SQLException if a database access error occurs
0795: */
0796: public boolean supportsOuterJoins() throws SQLException {
0797: return connection.haveMinimumServerVersion("7.1");
0798: }
0799:
0800: /*
0801: * Are full nexted outer joins supported?
0802: *
0803: * @return true if so
0804: * @exception SQLException if a database access error occurs
0805: */
0806: public boolean supportsFullOuterJoins() throws SQLException {
0807: return connection.haveMinimumServerVersion("7.1");
0808: }
0809:
0810: /*
0811: * Is there limited support for outer joins?
0812: *
0813: * @return true if so
0814: * @exception SQLException if a database access error occurs
0815: */
0816: public boolean supportsLimitedOuterJoins() throws SQLException {
0817: return connection.haveMinimumServerVersion("7.1");
0818: }
0819:
0820: /*
0821: * What is the database vendor's preferred term for "schema"?
0822: * PostgreSQL doesn't have schemas, but when it does, we'll use the
0823: * term "schema".
0824: *
0825: * @return the vendor term
0826: * @exception SQLException if a database access error occurs
0827: */
0828: public String getSchemaTerm() throws SQLException {
0829: return "schema";
0830: }
0831:
0832: /*
0833: * What is the database vendor's preferred term for "procedure"?
0834: * Traditionally, "function" has been used.
0835: *
0836: * @return the vendor term
0837: * @exception SQLException if a database access error occurs
0838: */
0839: public String getProcedureTerm() throws SQLException {
0840: return "function";
0841: }
0842:
0843: /*
0844: * What is the database vendor's preferred term for "catalog"?
0845: *
0846: * @return the vendor term
0847: * @exception SQLException if a database access error occurs
0848: */
0849: public String getCatalogTerm() throws SQLException {
0850: return "database";
0851: }
0852:
0853: /*
0854: * Does a catalog appear at the start of a qualified table name?
0855: * (Otherwise it appears at the end).
0856: *
0857: * @return true if so
0858: * @exception SQLException if a database access error occurs
0859: */
0860: public boolean isCatalogAtStart() throws SQLException {
0861: return true;
0862: }
0863:
0864: /*
0865: * What is the Catalog separator.
0866: *
0867: * @return the catalog separator string
0868: * @exception SQLException if a database access error occurs
0869: */
0870: public String getCatalogSeparator() throws SQLException {
0871: return ".";
0872: }
0873:
0874: /*
0875: * Can a schema name be used in a data manipulation statement?
0876: *
0877: * @return true if so
0878: * @exception SQLException if a database access error occurs
0879: */
0880: public boolean supportsSchemasInDataManipulation()
0881: throws SQLException {
0882: return connection.haveMinimumServerVersion("7.3");
0883: }
0884:
0885: /*
0886: * Can a schema name be used in a procedure call statement?
0887: *
0888: * @return true if so
0889: * @exception SQLException if a database access error occurs
0890: */
0891: public boolean supportsSchemasInProcedureCalls()
0892: throws SQLException {
0893: return connection.haveMinimumServerVersion("7.3");
0894: }
0895:
0896: /*
0897: * Can a schema be used in a table definition statement?
0898: *
0899: * @return true if so
0900: * @exception SQLException if a database access error occurs
0901: */
0902: public boolean supportsSchemasInTableDefinitions()
0903: throws SQLException {
0904: return connection.haveMinimumServerVersion("7.3");
0905: }
0906:
0907: /*
0908: * Can a schema name be used in an index definition statement?
0909: *
0910: * @return true if so
0911: * @exception SQLException if a database access error occurs
0912: */
0913: public boolean supportsSchemasInIndexDefinitions()
0914: throws SQLException {
0915: return connection.haveMinimumServerVersion("7.3");
0916: }
0917:
0918: /*
0919: * Can a schema name be used in a privilege definition statement?
0920: *
0921: * @return true if so
0922: * @exception SQLException if a database access error occurs
0923: */
0924: public boolean supportsSchemasInPrivilegeDefinitions()
0925: throws SQLException {
0926: return connection.haveMinimumServerVersion("7.3");
0927: }
0928:
0929: /*
0930: * Can a catalog name be used in a data manipulation statement?
0931: *
0932: * @return true if so
0933: * @exception SQLException if a database access error occurs
0934: */
0935: public boolean supportsCatalogsInDataManipulation()
0936: throws SQLException {
0937: return false;
0938: }
0939:
0940: /*
0941: * Can a catalog name be used in a procedure call statement?
0942: *
0943: * @return true if so
0944: * @exception SQLException if a database access error occurs
0945: */
0946: public boolean supportsCatalogsInProcedureCalls()
0947: throws SQLException {
0948: return false;
0949: }
0950:
0951: /*
0952: * Can a catalog name be used in a table definition statement?
0953: *
0954: * @return true if so
0955: * @exception SQLException if a database access error occurs
0956: */
0957: public boolean supportsCatalogsInTableDefinitions()
0958: throws SQLException {
0959: return false;
0960: }
0961:
0962: /*
0963: * Can a catalog name be used in an index definition?
0964: *
0965: * @return true if so
0966: * @exception SQLException if a database access error occurs
0967: */
0968: public boolean supportsCatalogsInIndexDefinitions()
0969: throws SQLException {
0970: return false;
0971: }
0972:
0973: /*
0974: * Can a catalog name be used in a privilege definition statement?
0975: *
0976: * @return true if so
0977: * @exception SQLException if a database access error occurs
0978: */
0979: public boolean supportsCatalogsInPrivilegeDefinitions()
0980: throws SQLException {
0981: return false;
0982: }
0983:
0984: /*
0985: * We support cursors for gets only it seems. I dont see a method
0986: * to get a positioned delete.
0987: *
0988: * @return true if so
0989: * @exception SQLException if a database access error occurs
0990: */
0991: public boolean supportsPositionedDelete() throws SQLException {
0992: return false; // For now...
0993: }
0994:
0995: /*
0996: * Is positioned UPDATE supported?
0997: *
0998: * @return true if so
0999: * @exception SQLException if a database access error occurs
1000: */
1001: public boolean supportsPositionedUpdate() throws SQLException {
1002: return false; // For now...
1003: }
1004:
1005: /*
1006: * Is SELECT for UPDATE supported?
1007: *
1008: * @return true if so; false otherwise
1009: * @exception SQLException - if a database access error occurs
1010: */
1011: public boolean supportsSelectForUpdate() throws SQLException {
1012: return connection.haveMinimumServerVersion("6.5");
1013: }
1014:
1015: /*
1016: * Are stored procedure calls using the stored procedure escape
1017: * syntax supported?
1018: *
1019: * @return true if so; false otherwise
1020: * @exception SQLException - if a database access error occurs
1021: */
1022: public boolean supportsStoredProcedures() throws SQLException {
1023: return true;
1024: }
1025:
1026: /*
1027: * Are subqueries in comparison expressions supported? A JDBC
1028: * Compliant driver always returns true.
1029: *
1030: * @return true if so; false otherwise
1031: * @exception SQLException - if a database access error occurs
1032: */
1033: public boolean supportsSubqueriesInComparisons()
1034: throws SQLException {
1035: return true;
1036: }
1037:
1038: /*
1039: * Are subqueries in 'exists' expressions supported? A JDBC
1040: * Compliant driver always returns true.
1041: *
1042: * @return true if so; false otherwise
1043: * @exception SQLException - if a database access error occurs
1044: */
1045: public boolean supportsSubqueriesInExists() throws SQLException {
1046: return true;
1047: }
1048:
1049: /*
1050: * Are subqueries in 'in' statements supported? A JDBC
1051: * Compliant driver always returns true.
1052: *
1053: * @return true if so; false otherwise
1054: * @exception SQLException - if a database access error occurs
1055: */
1056: public boolean supportsSubqueriesInIns() throws SQLException {
1057: return true;
1058: }
1059:
1060: /*
1061: * Are subqueries in quantified expressions supported? A JDBC
1062: * Compliant driver always returns true.
1063: *
1064: * (No idea what this is, but we support a good deal of
1065: * subquerying.)
1066: *
1067: * @return true if so; false otherwise
1068: * @exception SQLException - if a database access error occurs
1069: */
1070: public boolean supportsSubqueriesInQuantifieds()
1071: throws SQLException {
1072: return true;
1073: }
1074:
1075: /*
1076: * Are correlated subqueries supported? A JDBC Compliant driver
1077: * always returns true.
1078: *
1079: * (a.k.a. subselect in from?)
1080: *
1081: * @return true if so; false otherwise
1082: * @exception SQLException - if a database access error occurs
1083: */
1084: public boolean supportsCorrelatedSubqueries() throws SQLException {
1085: return connection.haveMinimumServerVersion("7.1");
1086: }
1087:
1088: /*
1089: * Is SQL UNION supported?
1090: *
1091: * @return true if so
1092: * @exception SQLException if a database access error occurs
1093: */
1094: public boolean supportsUnion() throws SQLException {
1095: return true; // since 6.3
1096: }
1097:
1098: /*
1099: * Is SQL UNION ALL supported?
1100: *
1101: * @return true if so
1102: * @exception SQLException if a database access error occurs
1103: */
1104: public boolean supportsUnionAll() throws SQLException {
1105: return connection.haveMinimumServerVersion("7.1");
1106: }
1107:
1108: /*
1109: * In PostgreSQL, Cursors are only open within transactions.
1110: *
1111: * @return true if so
1112: * @exception SQLException if a database access error occurs
1113: */
1114: public boolean supportsOpenCursorsAcrossCommit()
1115: throws SQLException {
1116: return false;
1117: }
1118:
1119: /*
1120: * Do we support open cursors across multiple transactions?
1121: *
1122: * @return true if so
1123: * @exception SQLException if a database access error occurs
1124: */
1125: public boolean supportsOpenCursorsAcrossRollback()
1126: throws SQLException {
1127: return false;
1128: }
1129:
1130: /*
1131: * Can statements remain open across commits? They may, but
1132: * this driver cannot guarentee that. In further reflection.
1133: * we are talking a Statement object here, so the answer is
1134: * yes, since the Statement is only a vehicle to ExecSQL()
1135: *
1136: * @return true if they always remain open; false otherwise
1137: * @exception SQLException if a database access error occurs
1138: */
1139: public boolean supportsOpenStatementsAcrossCommit()
1140: throws SQLException {
1141: return true;
1142: }
1143:
1144: /*
1145: * Can statements remain open across rollbacks? They may, but
1146: * this driver cannot guarentee that. In further contemplation,
1147: * we are talking a Statement object here, so the answer is yes,
1148: * since the Statement is only a vehicle to ExecSQL() in Connection
1149: *
1150: * @return true if they always remain open; false otherwise
1151: * @exception SQLException if a database access error occurs
1152: */
1153: public boolean supportsOpenStatementsAcrossRollback()
1154: throws SQLException {
1155: return true;
1156: }
1157:
1158: /*
1159: * How many hex characters can you have in an inline binary literal
1160: *
1161: * @return the max literal length
1162: * @exception SQLException if a database access error occurs
1163: */
1164: public int getMaxBinaryLiteralLength() throws SQLException {
1165: return 0; // no limit
1166: }
1167:
1168: /*
1169: * What is the maximum length for a character literal
1170: * I suppose it is 8190 (8192 - 2 for the quotes)
1171: *
1172: * @return the max literal length
1173: * @exception SQLException if a database access error occurs
1174: */
1175: public int getMaxCharLiteralLength() throws SQLException {
1176: return 0; // no limit
1177: }
1178:
1179: /*
1180: * Whats the limit on column name length.
1181: *
1182: * @return the maximum column name length
1183: * @exception SQLException if a database access error occurs
1184: */
1185: public int getMaxColumnNameLength() throws SQLException {
1186: return getMaxNameLength();
1187: }
1188:
1189: /*
1190: * What is the maximum number of columns in a "GROUP BY" clause?
1191: *
1192: * @return the max number of columns
1193: * @exception SQLException if a database access error occurs
1194: */
1195: public int getMaxColumnsInGroupBy() throws SQLException {
1196: return 0; // no limit
1197: }
1198:
1199: /*
1200: * What's the maximum number of columns allowed in an index?
1201: *
1202: * @return max number of columns
1203: * @exception SQLException if a database access error occurs
1204: */
1205: public int getMaxColumnsInIndex() throws SQLException {
1206: return getMaxIndexKeys();
1207: }
1208:
1209: /*
1210: * What's the maximum number of columns in an "ORDER BY clause?
1211: *
1212: * @return the max columns
1213: * @exception SQLException if a database access error occurs
1214: */
1215: public int getMaxColumnsInOrderBy() throws SQLException {
1216: return 0; // no limit
1217: }
1218:
1219: /*
1220: * What is the maximum number of columns in a "SELECT" list?
1221: *
1222: * @return the max columns
1223: * @exception SQLException if a database access error occurs
1224: */
1225: public int getMaxColumnsInSelect() throws SQLException {
1226: return 0; // no limit
1227: }
1228:
1229: /*
1230: * What is the maximum number of columns in a table? From the
1231: * CREATE TABLE reference page...
1232: *
1233: * <p>"The new class is created as a heap with no initial data. A
1234: * class can have no more than 1600 attributes (realistically,
1235: * this is limited by the fact that tuple sizes must be less than
1236: * 8192 bytes)..."
1237: *
1238: * @return the max columns
1239: * @exception SQLException if a database access error occurs
1240: */
1241: public int getMaxColumnsInTable() throws SQLException {
1242: return 1600;
1243: }
1244:
1245: /*
1246: * How many active connection can we have at a time to this
1247: * database? Well, since it depends on postmaster, which just
1248: * does a listen() followed by an accept() and fork(), its
1249: * basically very high. Unless the system runs out of processes,
1250: * it can be 65535 (the number of aux. ports on a TCP/IP system).
1251: * I will return 8192 since that is what even the largest system
1252: * can realistically handle,
1253: *
1254: * @return the maximum number of connections
1255: * @exception SQLException if a database access error occurs
1256: */
1257: public int getMaxConnections() throws SQLException {
1258: return 8192;
1259: }
1260:
1261: /*
1262: * What is the maximum cursor name length
1263: *
1264: * @return max cursor name length in bytes
1265: * @exception SQLException if a database access error occurs
1266: */
1267: public int getMaxCursorNameLength() throws SQLException {
1268: return getMaxNameLength();
1269: }
1270:
1271: /*
1272: * Retrieves the maximum number of bytes for an index, including all
1273: * of the parts of the index.
1274: *
1275: * @return max index length in bytes, which includes the composite
1276: * of all the constituent parts of the index; a result of zero means
1277: * that there is no limit or the limit is not known
1278: * @exception SQLException if a database access error occurs
1279: */
1280: public int getMaxIndexLength() throws SQLException {
1281: return 0; // no limit (larger than an int anyway)
1282: }
1283:
1284: public int getMaxSchemaNameLength() throws SQLException {
1285: return getMaxNameLength();
1286: }
1287:
1288: /*
1289: * What is the maximum length of a procedure name
1290: *
1291: * @return the max name length in bytes
1292: * @exception SQLException if a database access error occurs
1293: */
1294: public int getMaxProcedureNameLength() throws SQLException {
1295: return getMaxNameLength();
1296: }
1297:
1298: public int getMaxCatalogNameLength() throws SQLException {
1299: return getMaxNameLength();
1300: }
1301:
1302: /*
1303: * What is the maximum length of a single row?
1304: *
1305: * @return max row size in bytes
1306: * @exception SQLException if a database access error occurs
1307: */
1308: public int getMaxRowSize() throws SQLException {
1309: if (connection.haveMinimumServerVersion("7.1"))
1310: return 1073741824; // 1 GB
1311: else
1312: return 8192; // XXX could be altered
1313: }
1314:
1315: /*
1316: * Did getMaxRowSize() include LONGVARCHAR and LONGVARBINARY
1317: * blobs? We don't handle blobs yet
1318: *
1319: * @return true if so
1320: * @exception SQLException if a database access error occurs
1321: */
1322: public boolean doesMaxRowSizeIncludeBlobs() throws SQLException {
1323: return false;
1324: }
1325:
1326: /*
1327: * What is the maximum length of a SQL statement?
1328: *
1329: * @return max length in bytes
1330: * @exception SQLException if a database access error occurs
1331: */
1332: public int getMaxStatementLength() throws SQLException {
1333: if (connection.haveMinimumServerVersion("7.0"))
1334: return 0; // actually whatever fits in size_t
1335: else
1336: return 16384;
1337: }
1338:
1339: /*
1340: * How many active statements can we have open at one time to
1341: * this database? We're only limited by Java heap space really.
1342: *
1343: * @return the maximum
1344: * @exception SQLException if a database access error occurs
1345: */
1346: public int getMaxStatements() throws SQLException {
1347: return 0;
1348: }
1349:
1350: /*
1351: * What is the maximum length of a table name
1352: *
1353: * @return max name length in bytes
1354: * @exception SQLException if a database access error occurs
1355: */
1356: public int getMaxTableNameLength() throws SQLException {
1357: return getMaxNameLength();
1358: }
1359:
1360: /*
1361: * What is the maximum number of tables that can be specified
1362: * in a SELECT?
1363: *
1364: * @return the maximum
1365: * @exception SQLException if a database access error occurs
1366: */
1367: public int getMaxTablesInSelect() throws SQLException {
1368: return 0; // no limit
1369: }
1370:
1371: /*
1372: * What is the maximum length of a user name
1373: *
1374: * @return the max name length in bytes
1375: * @exception SQLException if a database access error occurs
1376: */
1377: public int getMaxUserNameLength() throws SQLException {
1378: return getMaxNameLength();
1379: }
1380:
1381: /*
1382: * What is the database's default transaction isolation level?
1383: *
1384: * @return the default isolation level
1385: * @exception SQLException if a database access error occurs
1386: * @see Connection
1387: */
1388: public int getDefaultTransactionIsolation() throws SQLException {
1389: return Connection.TRANSACTION_READ_COMMITTED;
1390: }
1391:
1392: /*
1393: * Are transactions supported? If not, commit and rollback are noops
1394: * and the isolation level is TRANSACTION_NONE. We do support
1395: * transactions.
1396: *
1397: * @return true if transactions are supported
1398: * @exception SQLException if a database access error occurs
1399: */
1400: public boolean supportsTransactions() throws SQLException {
1401: return true;
1402: }
1403:
1404: /*
1405: * Does the database support the given transaction isolation level?
1406: * We only support TRANSACTION_SERIALIZABLE and TRANSACTION_READ_COMMITTED
1407: * before 8.0; from 8.0 READ_UNCOMMITTED and REPEATABLE_READ are accepted aliases
1408: * for READ_COMMITTED.
1409: *
1410: * @param level the values are defined in java.sql.Connection
1411: * @return true if so
1412: * @exception SQLException if a database access error occurs
1413: * @see Connection
1414: */
1415: public boolean supportsTransactionIsolationLevel(int level)
1416: throws SQLException {
1417: if (level == Connection.TRANSACTION_SERIALIZABLE
1418: || level == Connection.TRANSACTION_READ_COMMITTED)
1419: return true;
1420: else if (connection.haveMinimumServerVersion("8.0")
1421: && (level == Connection.TRANSACTION_READ_UNCOMMITTED || level == Connection.TRANSACTION_REPEATABLE_READ))
1422: return true;
1423: else
1424: return false;
1425: }
1426:
1427: /*
1428: * Are both data definition and data manipulation transactions
1429: * supported?
1430: *
1431: * @return true if so
1432: * @exception SQLException if a database access error occurs
1433: */
1434: public boolean supportsDataDefinitionAndDataManipulationTransactions()
1435: throws SQLException {
1436: return true;
1437: }
1438:
1439: /*
1440: * Are only data manipulation statements withing a transaction
1441: * supported?
1442: *
1443: * @return true if so
1444: * @exception SQLException if a database access error occurs
1445: */
1446: public boolean supportsDataManipulationTransactionsOnly()
1447: throws SQLException {
1448: return false;
1449: }
1450:
1451: /*
1452: * Does a data definition statement within a transaction force
1453: * the transaction to commit? I think this means something like:
1454: *
1455: * <p><pre>
1456: * CREATE TABLE T (A INT);
1457: * INSERT INTO T (A) VALUES (2);
1458: * BEGIN;
1459: * UPDATE T SET A = A + 1;
1460: * CREATE TABLE X (A INT);
1461: * SELECT A FROM T INTO X;
1462: * COMMIT;
1463: * </pre><p>
1464: *
1465: * does the CREATE TABLE call cause a commit? The answer is no.
1466: *
1467: * @return true if so
1468: * @exception SQLException if a database access error occurs
1469: */
1470: public boolean dataDefinitionCausesTransactionCommit()
1471: throws SQLException {
1472: return false;
1473: }
1474:
1475: /*
1476: * Is a data definition statement within a transaction ignored?
1477: *
1478: * @return true if so
1479: * @exception SQLException if a database access error occurs
1480: */
1481: public boolean dataDefinitionIgnoredInTransactions()
1482: throws SQLException {
1483: return false;
1484: }
1485:
1486: /**
1487: * Escape single quotes with another single quote, escape backslashes as needed.
1488: */
1489: protected String escapeQuotes(String s) throws SQLException {
1490: return connection.escapeString(s);
1491: }
1492:
1493: /*
1494: * Get a description of stored procedures available in a catalog
1495: *
1496: * <p>Only procedure descriptions matching the schema and procedure
1497: * name criteria are returned. They are ordered by PROCEDURE_SCHEM
1498: * and PROCEDURE_NAME
1499: *
1500: * <p>Each procedure description has the following columns:
1501: * <ol>
1502: * <li><b>PROCEDURE_CAT</b> String => procedure catalog (may be null)
1503: * <li><b>PROCEDURE_SCHEM</b> String => procedure schema (may be null)
1504: * <li><b>PROCEDURE_NAME</b> String => procedure name
1505: * <li><b>Field 4</b> reserved (make it null)
1506: * <li><b>Field 5</b> reserved (make it null)
1507: * <li><b>Field 6</b> reserved (make it null)
1508: * <li><b>REMARKS</b> String => explanatory comment on the procedure
1509: * <li><b>PROCEDURE_TYPE</b> short => kind of procedure
1510: * <ul>
1511: * <li> procedureResultUnknown - May return a result
1512: * <li> procedureNoResult - Does not return a result
1513: * <li> procedureReturnsResult - Returns a result
1514: * </ul>
1515: * </ol>
1516: *
1517: * @param catalog - a catalog name; "" retrieves those without a
1518: * catalog; null means drop catalog name from criteria
1519: * @param schemaParrern - a schema name pattern; "" retrieves those
1520: * without a schema - we ignore this parameter
1521: * @param procedureNamePattern - a procedure name pattern
1522: * @return ResultSet - each row is a procedure description
1523: * @exception SQLException if a database access error occurs
1524: */
1525: public java.sql.ResultSet getProcedures(String catalog,
1526: String schemaPattern, String procedureNamePattern)
1527: throws SQLException {
1528: String sql;
1529: if (connection.haveMinimumServerVersion("7.3")) {
1530: sql = "SELECT NULL AS PROCEDURE_CAT, n.nspname AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL, NULL, NULL, d.description AS REMARKS, "
1531: + java.sql.DatabaseMetaData.procedureReturnsResult
1532: + " AS PROCEDURE_TYPE "
1533: + " FROM pg_catalog.pg_namespace n, pg_catalog.pg_proc p "
1534: + " LEFT JOIN pg_catalog.pg_description d ON (p.oid=d.objoid) "
1535: + " LEFT JOIN pg_catalog.pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') "
1536: + " LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog') "
1537: + " WHERE p.pronamespace=n.oid ";
1538: if (schemaPattern != null && !"".equals(schemaPattern)) {
1539: sql += " AND n.nspname LIKE '"
1540: + escapeQuotes(schemaPattern) + "' ";
1541: }
1542: if (procedureNamePattern != null) {
1543: sql += " AND p.proname LIKE '"
1544: + escapeQuotes(procedureNamePattern) + "' ";
1545: }
1546: sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME ";
1547: } else if (connection.haveMinimumServerVersion("7.1")) {
1548: sql = "SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL, NULL, NULL, d.description AS REMARKS, "
1549: + java.sql.DatabaseMetaData.procedureReturnsResult
1550: + " AS PROCEDURE_TYPE "
1551: + " FROM pg_proc p "
1552: + " LEFT JOIN pg_description d ON (p.oid=d.objoid) ";
1553: if (connection.haveMinimumServerVersion("7.2")) {
1554: sql += " LEFT JOIN pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') ";
1555: }
1556: if (procedureNamePattern != null) {
1557: sql += " WHERE p.proname LIKE '"
1558: + escapeQuotes(procedureNamePattern) + "' ";
1559: }
1560: sql += " ORDER BY PROCEDURE_NAME ";
1561: } else {
1562: sql = "SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL, NULL, NULL, NULL AS REMARKS, "
1563: + java.sql.DatabaseMetaData.procedureReturnsResult
1564: + " AS PROCEDURE_TYPE " + " FROM pg_proc p ";
1565: if (procedureNamePattern != null) {
1566: sql += " WHERE p.proname LIKE '"
1567: + escapeQuotes(procedureNamePattern) + "' ";
1568: }
1569: sql += " ORDER BY PROCEDURE_NAME ";
1570: }
1571: return createMetaDataStatement().executeQuery(sql);
1572: }
1573:
1574: /*
1575: * Get a description of a catalog's stored procedure parameters
1576: * and result columns.
1577: *
1578: * <p>Only descriptions matching the schema, procedure and parameter
1579: * name criteria are returned. They are ordered by PROCEDURE_SCHEM
1580: * and PROCEDURE_NAME. Within this, the return value, if any, is
1581: * first. Next are the parameter descriptions in call order. The
1582: * column descriptions follow in column number order.
1583: *
1584: * <p>Each row in the ResultSet is a parameter description or column
1585: * description with the following fields:
1586: * <ol>
1587: * <li><b>PROCEDURE_CAT</b> String => procedure catalog (may be null)
1588: * <li><b>PROCEDURE_SCHE</b>M String => procedure schema (may be null)
1589: * <li><b>PROCEDURE_NAME</b> String => procedure name
1590: * <li><b>COLUMN_NAME</b> String => column/parameter name
1591: * <li><b>COLUMN_TYPE</b> Short => kind of column/parameter:
1592: * <ul><li>procedureColumnUnknown - nobody knows
1593: * <li>procedureColumnIn - IN parameter
1594: * <li>procedureColumnInOut - INOUT parameter
1595: * <li>procedureColumnOut - OUT parameter
1596: * <li>procedureColumnReturn - procedure return value
1597: * <li>procedureColumnResult - result column in ResultSet
1598: * </ul>
1599: * <li><b>DATA_TYPE</b> short => SQL type from java.sql.Types
1600: * <li><b>TYPE_NAME</b> String => Data source specific type name
1601: * <li><b>PRECISION</b> int => precision
1602: * <li><b>LENGTH</b> int => length in bytes of data
1603: * <li><b>SCALE</b> short => scale
1604: * <li><b>RADIX</b> short => radix
1605: * <li><b>NULLABLE</b> short => can it contain NULL?
1606: * <ul><li>procedureNoNulls - does not allow NULL values
1607: * <li>procedureNullable - allows NULL values
1608: * <li>procedureNullableUnknown - nullability unknown
1609: * <li><b>REMARKS</b> String => comment describing parameter/column
1610: * </ol>
1611: * @param catalog This is ignored in org.postgresql, advise this is set to null
1612: * @param schemaPattern
1613: * @param procedureNamePattern a procedure name pattern
1614: * @param columnNamePattern a column name pattern, this is currently ignored because postgresql does not name procedure parameters.
1615: * @return each row is a stored procedure parameter or column description
1616: * @exception SQLException if a database-access error occurs
1617: * @see #getSearchStringEscape
1618: */
1619: // Implementation note: This is required for Borland's JBuilder to work
1620: public java.sql.ResultSet getProcedureColumns(String catalog,
1621: String schemaPattern, String procedureNamePattern,
1622: String columnNamePattern) throws SQLException {
1623: Field f[] = new Field[13];
1624: Vector v = new Vector(); // The new ResultSet tuple stuff
1625:
1626: f[0] = new Field("PROCEDURE_CAT", Oid.VARCHAR);
1627: f[1] = new Field("PROCEDURE_SCHEM", Oid.VARCHAR);
1628: f[2] = new Field("PROCEDURE_NAME", Oid.VARCHAR);
1629: f[3] = new Field("COLUMN_NAME", Oid.VARCHAR);
1630: f[4] = new Field("COLUMN_TYPE", Oid.INT2);
1631: f[5] = new Field("DATA_TYPE", Oid.INT2);
1632: f[6] = new Field("TYPE_NAME", Oid.VARCHAR);
1633: f[7] = new Field("PRECISION", Oid.INT4);
1634: f[8] = new Field("LENGTH", Oid.INT4);
1635: f[9] = new Field("SCALE", Oid.INT2);
1636: f[10] = new Field("RADIX", Oid.INT2);
1637: f[11] = new Field("NULLABLE", Oid.INT2);
1638: f[12] = new Field("REMARKS", Oid.VARCHAR);
1639:
1640: String sql;
1641: if (connection.haveMinimumServerVersion("7.3")) {
1642: sql = "SELECT n.nspname,p.proname,p.prorettype,p.proargtypes, t.typtype,t.typrelid ";
1643:
1644: if (connection.haveMinimumServerVersion("8.1"))
1645: sql += ", p.proargnames, p.proargmodes, p.proallargtypes ";
1646: else if (connection.haveMinimumServerVersion("8.0"))
1647: sql += ", p.proargnames, NULL AS proargmodes, NULL AS proallargtypes ";
1648: else
1649: sql += ", NULL AS proargnames, NULL AS proargmodes, NULL AS proallargtypes ";
1650:
1651: sql += " FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n, pg_catalog.pg_type t "
1652: + " WHERE p.pronamespace=n.oid AND p.prorettype=t.oid ";
1653: if (schemaPattern != null && !"".equals(schemaPattern)) {
1654: sql += " AND n.nspname LIKE '"
1655: + escapeQuotes(schemaPattern) + "' ";
1656: }
1657: if (procedureNamePattern != null) {
1658: sql += " AND p.proname LIKE '"
1659: + escapeQuotes(procedureNamePattern) + "' ";
1660: }
1661: sql += " ORDER BY n.nspname, p.proname ";
1662: } else {
1663: sql = "SELECT NULL AS nspname,p.proname,p.prorettype,p.proargtypes,t.typtype,t.typrelid, NULL AS proargnames, NULL AS proargmodes, NULL AS proallargtypes "
1664: + " FROM pg_proc p,pg_type t "
1665: + " WHERE p.prorettype=t.oid ";
1666: if (procedureNamePattern != null) {
1667: sql += " AND p.proname LIKE '"
1668: + escapeQuotes(procedureNamePattern) + "' ";
1669: }
1670: sql += " ORDER BY p.proname ";
1671: }
1672:
1673: ResultSet rs = connection.createStatement().executeQuery(sql);
1674: while (rs.next()) {
1675: byte schema[] = rs.getBytes("nspname");
1676: byte procedureName[] = rs.getBytes("proname");
1677: int returnType = rs.getInt("prorettype");
1678: String returnTypeType = rs.getString("typtype");
1679: int returnTypeRelid = rs.getInt("typrelid");
1680:
1681: String strArgTypes = rs.getString("proargtypes");
1682: StringTokenizer st = new StringTokenizer(strArgTypes);
1683: Vector argTypes = new Vector();
1684: while (st.hasMoreTokens()) {
1685: argTypes.addElement(new Long(st.nextToken()));
1686: }
1687:
1688: String argNames[] = null;
1689: Array argNamesArray = rs.getArray("proargnames");
1690: if (argNamesArray != null)
1691: argNames = (String[]) argNamesArray.getArray();
1692:
1693: String argModes[] = null;
1694: Array argModesArray = rs.getArray("proargmodes");
1695: if (argModesArray != null)
1696: argModes = (String[]) argModesArray.getArray();
1697:
1698: int numArgs = argTypes.size();
1699:
1700: long allArgTypes[] = null;
1701: Array allArgTypesArray = rs.getArray("proallargtypes");
1702: if (allArgTypesArray != null) {
1703: allArgTypes = (long[]) allArgTypesArray.getArray();
1704: numArgs = allArgTypes.length;
1705: }
1706:
1707: // decide if we are returning a single column result.
1708: if (returnTypeType.equals("b")
1709: || returnTypeType.equals("d")
1710: || (returnTypeType.equals("p") && argModesArray == null)) {
1711: byte[][] tuple = new byte[13][];
1712: tuple[0] = null;
1713: tuple[1] = schema;
1714: tuple[2] = procedureName;
1715: tuple[3] = connection.encodeString("returnValue");
1716: tuple[4] = connection
1717: .encodeString(Integer
1718: .toString(java.sql.DatabaseMetaData.procedureColumnReturn));
1719: tuple[5] = connection.encodeString(Integer
1720: .toString(connection.getSQLType(returnType)));
1721: tuple[6] = connection.encodeString(connection
1722: .getPGType(returnType));
1723: tuple[7] = null;
1724: tuple[8] = null;
1725: tuple[9] = null;
1726: tuple[10] = null;
1727: tuple[11] = connection
1728: .encodeString(Integer
1729: .toString(java.sql.DatabaseMetaData.procedureNullableUnknown));
1730: tuple[12] = null;
1731: v.addElement(tuple);
1732: }
1733:
1734: // Add a row for each argument.
1735: for (int i = 0; i < numArgs; i++) {
1736: byte[][] tuple = new byte[13][];
1737: tuple[0] = null;
1738: tuple[1] = schema;
1739: tuple[2] = procedureName;
1740:
1741: if (argNames != null)
1742: tuple[3] = connection.encodeString(argNames[i]);
1743: else
1744: tuple[3] = connection.encodeString("$" + (i + 1));
1745:
1746: int columnMode = DatabaseMetaData.procedureColumnIn;
1747: if (argModes != null && argModes[i].equals("o"))
1748: columnMode = DatabaseMetaData.procedureColumnOut;
1749: else if (argModes != null && argModes[i].equals("b"))
1750: columnMode = DatabaseMetaData.procedureColumnInOut;
1751:
1752: tuple[4] = connection.encodeString(Integer
1753: .toString(columnMode));
1754:
1755: int argOid;
1756: if (allArgTypes != null)
1757: argOid = (int) allArgTypes[i];
1758: else
1759: argOid = ((Long) argTypes.elementAt(i)).intValue();
1760:
1761: tuple[5] = connection.encodeString(Integer
1762: .toString(connection.getSQLType(argOid)));
1763: tuple[6] = connection.encodeString(connection
1764: .getPGType(argOid));
1765: tuple[7] = null;
1766: tuple[8] = null;
1767: tuple[9] = null;
1768: tuple[10] = null;
1769: tuple[11] = connection
1770: .encodeString(Integer
1771: .toString(DatabaseMetaData.procedureNullableUnknown));
1772: tuple[12] = null;
1773: v.addElement(tuple);
1774: }
1775:
1776: // if we are returning a multi-column result.
1777: if (returnTypeType.equals("c")
1778: || (returnTypeType.equals("p") && argModesArray != null)) {
1779: String columnsql = "SELECT a.attname,a.atttypid FROM pg_catalog.pg_attribute a WHERE a.attrelid = "
1780: + returnTypeRelid
1781: + " AND a.attnum > 0 ORDER BY a.attnum ";
1782: ResultSet columnrs = connection.createStatement()
1783: .executeQuery(columnsql);
1784: while (columnrs.next()) {
1785: int columnTypeOid = columnrs.getInt("atttypid");
1786: byte[][] tuple = new byte[13][];
1787: tuple[0] = null;
1788: tuple[1] = schema;
1789: tuple[2] = procedureName;
1790: tuple[3] = columnrs.getBytes("attname");
1791: tuple[4] = connection
1792: .encodeString(Integer
1793: .toString(java.sql.DatabaseMetaData.procedureColumnResult));
1794: tuple[5] = connection.encodeString(Integer
1795: .toString(connection
1796: .getSQLType(columnTypeOid)));
1797: tuple[6] = connection.encodeString(connection
1798: .getPGType(columnTypeOid));
1799: tuple[7] = null;
1800: tuple[8] = null;
1801: tuple[9] = null;
1802: tuple[10] = null;
1803: tuple[11] = connection
1804: .encodeString(Integer
1805: .toString(java.sql.DatabaseMetaData.procedureNullableUnknown));
1806: tuple[12] = null;
1807: v.addElement(tuple);
1808: }
1809: columnrs.close();
1810: }
1811: }
1812: rs.close();
1813:
1814: return (ResultSet) ((BaseStatement) createMetaDataStatement())
1815: .createDriverResultSet(f, v);
1816: }
1817:
1818: /*
1819: * Get a description of tables available in a catalog.
1820: *
1821: * <p>Only table descriptions matching the catalog, schema, table
1822: * name and type criteria are returned. They are ordered by
1823: * TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
1824: *
1825: * <p>Each table description has the following columns:
1826: *
1827: * <ol>
1828: * <li><b>TABLE_CAT</b> String => table catalog (may be null)
1829: * <li><b>TABLE_SCHEM</b> String => table schema (may be null)
1830: * <li><b>TABLE_NAME</b> String => table name
1831: * <li><b>TABLE_TYPE</b> String => table type. Typical types are "TABLE",
1832: * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL
1833: * TEMPORARY", "ALIAS", "SYNONYM".
1834: * <li><b>REMARKS</b> String => explanatory comment on the table
1835: * </ol>
1836: *
1837: * <p>The valid values for the types parameter are:
1838: * "TABLE", "INDEX", "SEQUENCE", "VIEW",
1839: * "SYSTEM TABLE", "SYSTEM INDEX", "SYSTEM VIEW",
1840: * "SYSTEM TOAST TABLE", "SYSTEM TOAST INDEX",
1841: * "TEMPORARY TABLE", and "TEMPORARY VIEW"
1842: *
1843: * @param catalog a catalog name; For org.postgresql, this is ignored, and
1844: * should be set to null
1845: * @param schemaPattern a schema name pattern
1846: * @param tableNamePattern a table name pattern. For all tables this should be "%"
1847: * @param types a list of table types to include; null returns
1848: * all types
1849: * @return each row is a table description
1850: * @exception SQLException if a database-access error occurs.
1851: */
1852: public java.sql.ResultSet getTables(String catalog,
1853: String schemaPattern, String tableNamePattern,
1854: String types[]) throws SQLException {
1855: String select;
1856: String orderby;
1857: String useSchemas;
1858: if (connection.haveMinimumServerVersion("7.3")) {
1859: useSchemas = "SCHEMAS";
1860: select = "SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, "
1861: + " CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema' "
1862: + " WHEN true THEN CASE "
1863: + " WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind "
1864: + " WHEN 'r' THEN 'SYSTEM TABLE' "
1865: + " WHEN 'v' THEN 'SYSTEM VIEW' "
1866: + " WHEN 'i' THEN 'SYSTEM INDEX' "
1867: + " ELSE NULL "
1868: + " END "
1869: + " WHEN n.nspname = 'pg_toast' THEN CASE c.relkind "
1870: + " WHEN 'r' THEN 'SYSTEM TOAST TABLE' "
1871: + " WHEN 'i' THEN 'SYSTEM TOAST INDEX' "
1872: + " ELSE NULL "
1873: + " END "
1874: + " ELSE CASE c.relkind "
1875: + " WHEN 'r' THEN 'TEMPORARY TABLE' "
1876: + " WHEN 'i' THEN 'TEMPORARY INDEX' "
1877: + " ELSE NULL "
1878: + " END "
1879: + " END "
1880: + " WHEN false THEN CASE c.relkind "
1881: + " WHEN 'r' THEN 'TABLE' "
1882: + " WHEN 'i' THEN 'INDEX' "
1883: + " WHEN 'S' THEN 'SEQUENCE' "
1884: + " WHEN 'v' THEN 'VIEW' "
1885: + " ELSE NULL "
1886: + " END "
1887: + " ELSE NULL "
1888: + " END "
1889: + " AS TABLE_TYPE, d.description AS REMARKS "
1890: + " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c "
1891: + " LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0) "
1892: + " LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class') "
1893: + " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') "
1894: + " WHERE c.relnamespace = n.oid ";
1895: if (schemaPattern != null && !"".equals(schemaPattern)) {
1896: select += " AND n.nspname LIKE '"
1897: + escapeQuotes(schemaPattern) + "' ";
1898: }
1899: orderby = " ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ";
1900: } else {
1901: useSchemas = "NOSCHEMAS";
1902: String tableType = "" + " CASE c.relname ~ '^pg_' "
1903: + " WHEN true THEN CASE c.relname ~ '^pg_toast_' "
1904: + " WHEN true THEN CASE c.relkind "
1905: + " WHEN 'r' THEN 'SYSTEM TOAST TABLE' "
1906: + " WHEN 'i' THEN 'SYSTEM TOAST INDEX' "
1907: + " ELSE NULL " + " END "
1908: + " WHEN false THEN CASE c.relname ~ '^pg_temp_' "
1909: + " WHEN true THEN CASE c.relkind "
1910: + " WHEN 'r' THEN 'TEMPORARY TABLE' "
1911: + " WHEN 'i' THEN 'TEMPORARY INDEX' "
1912: + " ELSE NULL " + " END "
1913: + " WHEN false THEN CASE c.relkind "
1914: + " WHEN 'r' THEN 'SYSTEM TABLE' "
1915: + " WHEN 'v' THEN 'SYSTEM VIEW' "
1916: + " WHEN 'i' THEN 'SYSTEM INDEX' "
1917: + " ELSE NULL " + " END " + " ELSE NULL "
1918: + " END " + " ELSE NULL " + " END "
1919: + " WHEN false THEN CASE c.relkind "
1920: + " WHEN 'r' THEN 'TABLE' "
1921: + " WHEN 'i' THEN 'INDEX' "
1922: + " WHEN 'S' THEN 'SEQUENCE' "
1923: + " WHEN 'v' THEN 'VIEW' " + " ELSE NULL "
1924: + " END " + " ELSE NULL " + " END ";
1925: orderby = " ORDER BY TABLE_TYPE,TABLE_NAME ";
1926: if (connection.haveMinimumServerVersion("7.2")) {
1927: select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, c.relname AS TABLE_NAME, "
1928: + tableType
1929: + " AS TABLE_TYPE, d.description AS REMARKS "
1930: + " FROM pg_class c "
1931: + " LEFT JOIN pg_description d ON (c.oid=d.objoid AND d.objsubid = 0) "
1932: + " LEFT JOIN pg_class dc ON (d.classoid = dc.oid AND dc.relname='pg_class') "
1933: + " WHERE true ";
1934: } else if (connection.haveMinimumServerVersion("7.1")) {
1935: select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, c.relname AS TABLE_NAME, "
1936: + tableType
1937: + " AS TABLE_TYPE, d.description AS REMARKS "
1938: + " FROM pg_class c "
1939: + " LEFT JOIN pg_description d ON (c.oid=d.objoid) "
1940: + " WHERE true ";
1941: } else {
1942: select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, c.relname AS TABLE_NAME, "
1943: + tableType
1944: + " AS TABLE_TYPE, NULL AS REMARKS "
1945: + " FROM pg_class c " + " WHERE true ";
1946: }
1947: }
1948:
1949: if (tableNamePattern != null) {
1950: select += " AND c.relname LIKE '"
1951: + escapeQuotes(tableNamePattern) + "' ";
1952: }
1953: if (types != null) {
1954: select += " AND (false ";
1955: for (int i = 0; i < types.length; i++) {
1956: Hashtable clauses = (Hashtable) tableTypeClauses
1957: .get(types[i]);
1958: if (clauses != null) {
1959: String clause = (String) clauses.get(useSchemas);
1960: select += " OR ( " + clause + " ) ";
1961: }
1962: }
1963: select += ") ";
1964: }
1965: String sql = select + orderby;
1966:
1967: return createMetaDataStatement().executeQuery(sql);
1968: }
1969:
1970: private static final Hashtable tableTypeClauses;
1971: static {
1972: tableTypeClauses = new Hashtable();
1973: Hashtable ht = new Hashtable();
1974: tableTypeClauses.put("TABLE", ht);
1975: ht
1976: .put("SCHEMAS",
1977: "c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'");
1978: ht.put("NOSCHEMAS", "c.relkind = 'r' AND c.relname !~ '^pg_'");
1979: ht = new Hashtable();
1980: tableTypeClauses.put("VIEW", ht);
1981: ht
1982: .put(
1983: "SCHEMAS",
1984: "c.relkind = 'v' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'");
1985: ht.put("NOSCHEMAS", "c.relkind = 'v' AND c.relname !~ '^pg_'");
1986: ht = new Hashtable();
1987: tableTypeClauses.put("INDEX", ht);
1988: ht
1989: .put("SCHEMAS",
1990: "c.relkind = 'i' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'");
1991: ht.put("NOSCHEMAS", "c.relkind = 'i' AND c.relname !~ '^pg_'");
1992: ht = new Hashtable();
1993: tableTypeClauses.put("SEQUENCE", ht);
1994: ht.put("SCHEMAS", "c.relkind = 'S'");
1995: ht.put("NOSCHEMAS", "c.relkind = 'S'");
1996: ht = new Hashtable();
1997: tableTypeClauses.put("SYSTEM TABLE", ht);
1998: ht
1999: .put(
2000: "SCHEMAS",
2001: "c.relkind = 'r' AND (n.nspname = 'pg_catalog' OR n.nspname = 'information_schema')");
2002: ht
2003: .put(
2004: "NOSCHEMAS",
2005: "c.relkind = 'r' AND c.relname ~ '^pg_' AND c.relname !~ '^pg_toast_' AND c.relname !~ '^pg_temp_'");
2006: ht = new Hashtable();
2007: tableTypeClauses.put("SYSTEM TOAST TABLE", ht);
2008: ht.put("SCHEMAS", "c.relkind = 'r' AND n.nspname = 'pg_toast'");
2009: ht.put("NOSCHEMAS",
2010: "c.relkind = 'r' AND c.relname ~ '^pg_toast_'");
2011: ht = new Hashtable();
2012: tableTypeClauses.put("SYSTEM TOAST INDEX", ht);
2013: ht.put("SCHEMAS", "c.relkind = 'i' AND n.nspname = 'pg_toast'");
2014: ht.put("NOSCHEMAS",
2015: "c.relkind = 'i' AND c.relname ~ '^pg_toast_'");
2016: ht = new Hashtable();
2017: tableTypeClauses.put("SYSTEM VIEW", ht);
2018: ht
2019: .put(
2020: "SCHEMAS",
2021: "c.relkind = 'v' AND (n.nspname = 'pg_catalog' OR n.nspname = 'information_schema') ");
2022: ht.put("NOSCHEMAS", "c.relkind = 'v' AND c.relname ~ '^pg_'");
2023: ht = new Hashtable();
2024: tableTypeClauses.put("SYSTEM INDEX", ht);
2025: ht
2026: .put(
2027: "SCHEMAS",
2028: "c.relkind = 'i' AND (n.nspname = 'pg_catalog' OR n.nspname = 'information_schema') ");
2029: ht
2030: .put(
2031: "NOSCHEMAS",
2032: "c.relkind = 'v' AND c.relname ~ '^pg_' AND c.relname !~ '^pg_toast_' AND c.relname !~ '^pg_temp_'");
2033: ht = new Hashtable();
2034: tableTypeClauses.put("TEMPORARY TABLE", ht);
2035: ht.put("SCHEMAS",
2036: "c.relkind = 'r' AND n.nspname ~ '^pg_temp_' ");
2037: ht.put("NOSCHEMAS",
2038: "c.relkind = 'r' AND c.relname ~ '^pg_temp_' ");
2039: ht = new Hashtable();
2040: tableTypeClauses.put("TEMPORARY INDEX", ht);
2041: ht.put("SCHEMAS",
2042: "c.relkind = 'i' AND n.nspname ~ '^pg_temp_' ");
2043: ht.put("NOSCHEMAS",
2044: "c.relkind = 'i' AND c.relname ~ '^pg_temp_' ");
2045: }
2046:
2047: /*
2048: * Get the schema names available in this database. The results
2049: * are ordered by schema name.
2050: *
2051: * <P>The schema column is:
2052: * <OL>
2053: * <LI><B>TABLE_SCHEM</B> String => schema name
2054: * </OL>
2055: *
2056: * @return ResultSet each row has a single String column that is a
2057: * schema name
2058: */
2059: public java.sql.ResultSet getSchemas() throws SQLException {
2060: String sql;
2061: if (connection.haveMinimumServerVersion("7.3")) {
2062: sql = "SELECT nspname AS TABLE_SCHEM FROM pg_catalog.pg_namespace WHERE nspname <> 'pg_toast' AND nspname !~ '^pg_temp_' ORDER BY TABLE_SCHEM";
2063: } else {
2064: sql = "SELECT ''::text AS TABLE_SCHEM ORDER BY TABLE_SCHEM";
2065: }
2066: return createMetaDataStatement().executeQuery(sql);
2067: }
2068:
2069: /*
2070: * Get the catalog names available in this database. The results
2071: * are ordered by catalog name.
2072: *
2073: * Postgresql does not support multiple catalogs from a single
2074: * connection, so to reduce confusion we only return the current
2075: * catalog.
2076: *
2077: * <P>The catalog column is:
2078: * <OL>
2079: * <LI><B>TABLE_CAT</B> String => catalog name
2080: * </OL>
2081: *
2082: * @return ResultSet each row has a single String column that is a
2083: * catalog name
2084: */
2085: public java.sql.ResultSet getCatalogs() throws SQLException {
2086: Field f[] = new Field[1];
2087: Vector v = new Vector();
2088: f[0] = new Field(new String("TABLE_CAT"), Oid.VARCHAR);
2089: byte[][] tuple = new byte[1][];
2090: tuple[0] = connection.encodeString(connection.getCatalog());
2091: v.addElement(tuple);
2092:
2093: return (ResultSet) ((BaseStatement) createMetaDataStatement())
2094: .createDriverResultSet(f, v);
2095: }
2096:
2097: /*
2098: * Get the table types available in this database. The results
2099: * are ordered by table type.
2100: *
2101: * <P>The table type is:
2102: * <OL>
2103: * <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
2104: * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
2105: * "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
2106: * </OL>
2107: *
2108: * @return ResultSet each row has a single String column that is a
2109: * table type
2110: */
2111: public java.sql.ResultSet getTableTypes() throws SQLException {
2112: String types[] = new String[tableTypeClauses.size()];
2113: Enumeration e = tableTypeClauses.keys();
2114: int i = 0;
2115: while (e.hasMoreElements()) {
2116: types[i++] = (String) e.nextElement();
2117: }
2118: sortStringArray(types);
2119:
2120: Field f[] = new Field[1];
2121: Vector v = new Vector();
2122: f[0] = new Field(new String("TABLE_TYPE"), Oid.VARCHAR);
2123: for (i = 0; i < types.length; i++) {
2124: byte[][] tuple = new byte[1][];
2125: tuple[0] = connection.encodeString(types[i]);
2126: v.addElement(tuple);
2127: }
2128:
2129: return (ResultSet) ((BaseStatement) createMetaDataStatement())
2130: .createDriverResultSet(f, v);
2131: }
2132:
2133: /*
2134: * Get a description of table columns available in a catalog.
2135: *
2136: * <P>Only column descriptions matching the catalog, schema, table
2137: * and column name criteria are returned. They are ordered by
2138: * TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
2139: *
2140: * <P>Each column description has the following columns:
2141: * <OL>
2142: * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
2143: * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
2144: * <LI><B>TABLE_NAME</B> String => table name
2145: * <LI><B>COLUMN_NAME</B> String => column name
2146: * <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
2147: * <LI><B>TYPE_NAME</B> String => Data source dependent type name
2148: * <LI><B>COLUMN_SIZE</B> int => column size. For char or date
2149: * types this is the maximum number of characters, for numeric or
2150: * decimal types this is precision.
2151: * <LI><B>BUFFER_LENGTH</B> is not used.
2152: * <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
2153: * <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
2154: * <LI><B>NULLABLE</B> int => is NULL allowed?
2155: * <UL>
2156: * <LI> columnNoNulls - might not allow NULL values
2157: * <LI> columnNullable - definitely allows NULL values
2158: * <LI> columnNullableUnknown - nullability unknown
2159: * </UL>
2160: * <LI><B>REMARKS</B> String => comment describing column (may be null)
2161: * <LI><B>COLUMN_DEF</B> String => default value (may be null)
2162: * <LI><B>SQL_DATA_TYPE</B> int => unused
2163: * <LI><B>SQL_DATETIME_SUB</B> int => unused
2164: * <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
2165: * maximum number of bytes in the column
2166: * <LI><B>ORDINAL_POSITION</B> int => index of column in table
2167: * (starting at 1)
2168: * <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
2169: * does not allow NULL values; "YES" means the column might
2170: * allow NULL values. An empty string means nobody knows.
2171: * </OL>
2172: *
2173: * @param catalog a catalog name; "" retrieves those without a catalog
2174: * @param schemaPattern a schema name pattern; "" retrieves those
2175: * without a schema
2176: * @param tableNamePattern a table name pattern
2177: * @param columnNamePattern a column name pattern
2178: * @return ResultSet each row is a column description
2179: * @see #getSearchStringEscape
2180: */
2181: public java.sql.ResultSet getColumns(String catalog,
2182: String schemaPattern, String tableNamePattern,
2183: String columnNamePattern) throws SQLException {
2184: Vector v = new Vector(); // The new ResultSet tuple stuff
2185: Field f[] = new Field[18]; // The field descriptors for the new ResultSet
2186:
2187: f[0] = new Field("TABLE_CAT", Oid.VARCHAR);
2188: f[1] = new Field("TABLE_SCHEM", Oid.VARCHAR);
2189: f[2] = new Field("TABLE_NAME", Oid.VARCHAR);
2190: f[3] = new Field("COLUMN_NAME", Oid.VARCHAR);
2191: f[4] = new Field("DATA_TYPE", Oid.INT2);
2192: f[5] = new Field("TYPE_NAME", Oid.VARCHAR);
2193: f[6] = new Field("COLUMN_SIZE", Oid.INT4);
2194: f[7] = new Field("BUFFER_LENGTH", Oid.VARCHAR);
2195: f[8] = new Field("DECIMAL_DIGITS", Oid.INT4);
2196: f[9] = new Field("NUM_PREC_RADIX", Oid.INT4);
2197: f[10] = new Field("NULLABLE", Oid.INT4);
2198: f[11] = new Field("REMARKS", Oid.VARCHAR);
2199: f[12] = new Field("COLUMN_DEF", Oid.VARCHAR);
2200: f[13] = new Field("SQL_DATA_TYPE", Oid.INT4);
2201: f[14] = new Field("SQL_DATETIME_SUB", Oid.INT4);
2202: f[15] = new Field("CHAR_OCTET_LENGTH", Oid.VARCHAR);
2203: f[16] = new Field("ORDINAL_POSITION", Oid.INT4);
2204: f[17] = new Field("IS_NULLABLE", Oid.VARCHAR);
2205:
2206: String sql;
2207: if (connection.haveMinimumServerVersion("7.3")) {
2208: sql = "SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description "
2209: + " FROM pg_catalog.pg_namespace n "
2210: + " JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) "
2211: + " JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) "
2212: + " LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) "
2213: + " LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) "
2214: + " LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') "
2215: + " LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') "
2216: + " WHERE a.attnum > 0 AND NOT a.attisdropped ";
2217: if (schemaPattern != null && !"".equals(schemaPattern)) {
2218: sql += " AND n.nspname LIKE '"
2219: + escapeQuotes(schemaPattern) + "' ";
2220: }
2221: } else if (connection.haveMinimumServerVersion("7.2")) {
2222: sql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description "
2223: + " FROM pg_class c "
2224: + " JOIN pg_attribute a ON (a.attrelid=c.oid) "
2225: + " LEFT JOIN pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) "
2226: + " LEFT JOIN pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) "
2227: + " LEFT JOIN pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') "
2228: + " WHERE a.attnum > 0 ";
2229: } else if (connection.haveMinimumServerVersion("7.1")) {
2230: sql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description "
2231: + " FROM pg_class c "
2232: + " JOIN pg_attribute a ON (a.attrelid=c.oid) "
2233: + " LEFT JOIN pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) "
2234: + " LEFT JOIN pg_description dsc ON (a.oid=dsc.objoid) "
2235: + " WHERE a.attnum > 0 ";
2236: } else {
2237: // if < 7.1 then don't get defaults or descriptions.
2238: sql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,NULL AS adsrc,NULL AS description "
2239: + " FROM pg_class c, pg_attribute a "
2240: + " WHERE a.attrelid=c.oid AND a.attnum > 0 ";
2241: }
2242:
2243: if (tableNamePattern != null && !"".equals(tableNamePattern)) {
2244: sql += " AND c.relname LIKE '"
2245: + escapeQuotes(tableNamePattern) + "' ";
2246: }
2247: if (columnNamePattern != null && !"".equals(columnNamePattern)) {
2248: sql += " AND a.attname LIKE '"
2249: + escapeQuotes(columnNamePattern) + "' ";
2250: }
2251: sql += " ORDER BY nspname,relname,attnum ";
2252:
2253: ResultSet rs = connection.createStatement().executeQuery(sql);
2254: while (rs.next()) {
2255: byte[][] tuple = new byte[18][];
2256: int typeOid = rs.getInt("atttypid");
2257: int typeMod = rs.getInt("atttypmod");
2258:
2259: tuple[0] = null; // Catalog name, not supported
2260: tuple[1] = rs.getBytes("nspname"); // Schema
2261: tuple[2] = rs.getBytes("relname"); // Table name
2262: tuple[3] = rs.getBytes("attname"); // Column name
2263: tuple[4] = connection.encodeString(Integer
2264: .toString(connection.getSQLType(typeOid)));
2265: String pgType = connection.getPGType(typeOid);
2266: tuple[5] = connection.encodeString(pgType); // Type name
2267: tuple[7] = null; // Buffer length
2268:
2269: String defval = rs.getString("adsrc");
2270:
2271: if (defval != null) {
2272: if (pgType.equals("int4")) {
2273: if (defval.indexOf("nextval(") != -1)
2274: tuple[5] = connection.encodeString("serial"); // Type name == serial
2275: } else if (pgType.equals("int8")) {
2276: if (defval.indexOf("nextval(") != -1)
2277: tuple[5] = connection.encodeString("bigserial"); // Type name == bigserial
2278: }
2279: }
2280:
2281: int decimalDigits = TypeInfoCache
2282: .getScale(typeOid, typeMod);
2283: int columnSize = TypeInfoCache.getPrecision(typeOid,
2284: typeMod);
2285: if (columnSize == 0) {
2286: columnSize = TypeInfoCache.getDisplaySize(typeOid,
2287: typeMod);
2288: }
2289:
2290: tuple[6] = connection.encodeString(Integer
2291: .toString(columnSize));
2292: tuple[8] = connection.encodeString(Integer
2293: .toString(decimalDigits));
2294:
2295: // Everything is base 10 unless we override later.
2296: tuple[9] = connection.encodeString("10");
2297:
2298: if (pgType.equals("bit") || pgType.equals("varbit")) {
2299: tuple[9] = connection.encodeString("2");
2300: }
2301:
2302: tuple[10] = connection
2303: .encodeString(Integer
2304: .toString(rs.getBoolean("attnotnull") ? java.sql.DatabaseMetaData.columnNoNulls
2305: : java.sql.DatabaseMetaData.columnNullable)); // Nullable
2306: tuple[11] = rs.getBytes("description"); // Description (if any)
2307: tuple[12] = rs.getBytes("adsrc"); // Column default
2308: tuple[13] = null; // sql data type (unused)
2309: tuple[14] = null; // sql datetime sub (unused)
2310: tuple[15] = tuple[6]; // char octet length
2311: tuple[16] = rs.getBytes("attnum"); // ordinal position
2312: tuple[17] = connection.encodeString(rs
2313: .getBoolean("attnotnull") ? "NO" : "YES"); // Is nullable
2314:
2315: v.addElement(tuple);
2316: }
2317: rs.close();
2318:
2319: return (ResultSet) ((BaseStatement) createMetaDataStatement())
2320: .createDriverResultSet(f, v);
2321: }
2322:
2323: /*
2324: * Get a description of the access rights for a table's columns.
2325: *
2326: * <P>Only privileges matching the column name criteria are
2327: * returned. They are ordered by COLUMN_NAME and PRIVILEGE.
2328: *
2329: * <P>Each privilige description has the following columns:
2330: * <OL>
2331: * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
2332: * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
2333: * <LI><B>TABLE_NAME</B> String => table name
2334: * <LI><B>COLUMN_NAME</B> String => column name
2335: * <LI><B>GRANTOR</B> => grantor of access (may be null)
2336: * <LI><B>GRANTEE</B> String => grantee of access
2337: * <LI><B>PRIVILEGE</B> String => name of access (SELECT,
2338: * INSERT, UPDATE, REFRENCES, ...)
2339: * <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
2340: * to grant to others; "NO" if not; null if unknown
2341: * </OL>
2342: *
2343: * @param catalog a catalog name; "" retrieves those without a catalog
2344: * @param schema a schema name; "" retrieves those without a schema
2345: * @param table a table name
2346: * @param columnNamePattern a column name pattern
2347: * @return ResultSet each row is a column privilege description
2348: * @see #getSearchStringEscape
2349: */
2350: public java.sql.ResultSet getColumnPrivileges(String catalog,
2351: String schema, String table, String columnNamePattern)
2352: throws SQLException {
2353: Field f[] = new Field[8];
2354: Vector v = new Vector();
2355:
2356: if (table == null)
2357: table = "%";
2358:
2359: if (columnNamePattern == null)
2360: columnNamePattern = "%";
2361:
2362: f[0] = new Field("TABLE_CAT", Oid.VARCHAR);
2363: f[1] = new Field("TABLE_SCHEM", Oid.VARCHAR);
2364: f[2] = new Field("TABLE_NAME", Oid.VARCHAR);
2365: f[3] = new Field("COLUMN_NAME", Oid.VARCHAR);
2366: f[4] = new Field("GRANTOR", Oid.VARCHAR);
2367: f[5] = new Field("GRANTEE", Oid.VARCHAR);
2368: f[6] = new Field("PRIVILEGE", Oid.VARCHAR);
2369: f[7] = new Field("IS_GRANTABLE", Oid.VARCHAR);
2370:
2371: String sql;
2372: if (connection.haveMinimumServerVersion("7.3")) {
2373: sql = "SELECT n.nspname,c.relname,u.usename,c.relacl,a.attname "
2374: + " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_attribute a "
2375: + " WHERE c.relnamespace = n.oid "
2376: + " AND u.usesysid = c.relowner "
2377: + " AND c.oid = a.attrelid "
2378: + " AND c.relkind = 'r' "
2379: + " AND a.attnum > 0 AND NOT a.attisdropped ";
2380: if (schema != null && !"".equals(schema)) {
2381: sql += " AND n.nspname = '" + escapeQuotes(schema)
2382: + "' ";
2383: }
2384: } else {
2385: sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl,a.attname "
2386: + "FROM pg_class c, pg_user u,pg_attribute a "
2387: + " WHERE u.usesysid = c.relowner "
2388: + " AND c.oid = a.attrelid "
2389: + " AND a.attnum > 0 "
2390: + " AND c.relkind = 'r' ";
2391: }
2392:
2393: sql += " AND c.relname = '" + escapeQuotes(table) + "' ";
2394: if (columnNamePattern != null && !"".equals(columnNamePattern)) {
2395: sql += " AND a.attname LIKE '"
2396: + escapeQuotes(columnNamePattern) + "' ";
2397: }
2398: sql += " ORDER BY attname ";
2399:
2400: ResultSet rs = connection.createStatement().executeQuery(sql);
2401: while (rs.next()) {
2402: byte schemaName[] = rs.getBytes("nspname");
2403: byte tableName[] = rs.getBytes("relname");
2404: byte column[] = rs.getBytes("attname");
2405: String owner = rs.getString("usename");
2406: String acl = rs.getString("relacl");
2407: Hashtable permissions = parseACL(acl, owner);
2408: String permNames[] = new String[permissions.size()];
2409: Enumeration e = permissions.keys();
2410: int i = 0;
2411: while (e.hasMoreElements()) {
2412: permNames[i++] = (String) e.nextElement();
2413: }
2414: sortStringArray(permNames);
2415: for (i = 0; i < permNames.length; i++) {
2416: byte[] privilege = connection
2417: .encodeString(permNames[i]);
2418: Vector grantees = (Vector) permissions
2419: .get(permNames[i]);
2420: for (int j = 0; j < grantees.size(); j++) {
2421: String grantee = (String) grantees.elementAt(j);
2422: String grantable = owner.equals(grantee) ? "YES"
2423: : "NO";
2424: byte[][] tuple = new byte[8][];
2425: tuple[0] = null;
2426: tuple[1] = schemaName;
2427: tuple[2] = tableName;
2428: tuple[3] = column;
2429: tuple[4] = connection.encodeString(owner);
2430: tuple[5] = connection.encodeString(grantee);
2431: tuple[6] = privilege;
2432: tuple[7] = connection.encodeString(grantable);
2433: v.addElement(tuple);
2434: }
2435: }
2436: }
2437: rs.close();
2438:
2439: return (ResultSet) ((BaseStatement) createMetaDataStatement())
2440: .createDriverResultSet(f, v);
2441: }
2442:
2443: /*
2444: * Get a description of the access rights for each table available
2445: * in a catalog.
2446: *
2447: * This method is currently unimplemented.
2448: *
2449: * <P>Only privileges matching the schema and table name
2450: * criteria are returned. They are ordered by TABLE_SCHEM,
2451: * TABLE_NAME, and PRIVILEGE.
2452: *
2453: * <P>Each privilige description has the following columns:
2454: * <OL>
2455: * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
2456: * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
2457: * <LI><B>TABLE_NAME</B> String => table name
2458: * <LI><B>GRANTOR</B> => grantor of access (may be null)
2459: * <LI><B>GRANTEE</B> String => grantee of access
2460: * <LI><B>PRIVILEGE</B> String => name of access (SELECT,
2461: * INSERT, UPDATE, REFRENCES, ...)
2462: * <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
2463: * to grant to others; "NO" if not; null if unknown
2464: * </OL>
2465: *
2466: * @param catalog a catalog name; "" retrieves those without a catalog
2467: * @param schemaPattern a schema name pattern; "" retrieves those
2468: * without a schema
2469: * @param tableNamePattern a table name pattern
2470: * @return ResultSet each row is a table privilege description
2471: * @see #getSearchStringEscape
2472: */
2473: public java.sql.ResultSet getTablePrivileges(String catalog,
2474: String schemaPattern, String tableNamePattern)
2475: throws SQLException {
2476: Field f[] = new Field[7];
2477: Vector v = new Vector();
2478:
2479: f[0] = new Field("TABLE_CAT", Oid.VARCHAR);
2480: f[1] = new Field("TABLE_SCHEM", Oid.VARCHAR);
2481: f[2] = new Field("TABLE_NAME", Oid.VARCHAR);
2482: f[3] = new Field("GRANTOR", Oid.VARCHAR);
2483: f[4] = new Field("GRANTEE", Oid.VARCHAR);
2484: f[5] = new Field("PRIVILEGE", Oid.VARCHAR);
2485: f[6] = new Field("IS_GRANTABLE", Oid.VARCHAR);
2486:
2487: String sql;
2488: if (connection.haveMinimumServerVersion("7.3")) {
2489: sql = "SELECT n.nspname,c.relname,u.usename,c.relacl "
2490: + " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c, pg_catalog.pg_user u "
2491: + " WHERE c.relnamespace = n.oid "
2492: + " AND u.usesysid = c.relowner "
2493: + " AND c.relkind = 'r' ";
2494: if (schemaPattern != null && !"".equals(schemaPattern)) {
2495: sql += " AND n.nspname LIKE '"
2496: + escapeQuotes(schemaPattern) + "' ";
2497: }
2498: } else {
2499: sql = "SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl "
2500: + "FROM pg_class c, pg_user u "
2501: + " WHERE u.usesysid = c.relowner "
2502: + " AND c.relkind = 'r' ";
2503: }
2504:
2505: if (tableNamePattern != null && !"".equals(tableNamePattern)) {
2506: sql += " AND c.relname LIKE '"
2507: + escapeQuotes(tableNamePattern) + "' ";
2508: }
2509: sql += " ORDER BY nspname, relname ";
2510:
2511: ResultSet rs = connection.createStatement().executeQuery(sql);
2512: while (rs.next()) {
2513: byte schema[] = rs.getBytes("nspname");
2514: byte table[] = rs.getBytes("relname");
2515: String owner = rs.getString("usename");
2516: String acl = rs.getString("relacl");
2517: Hashtable permissions = parseACL(acl, owner);
2518: String permNames[] = new String[permissions.size()];
2519: Enumeration e = permissions.keys();
2520: int i = 0;
2521: while (e.hasMoreElements()) {
2522: permNames[i++] = (String) e.nextElement();
2523: }
2524: sortStringArray(permNames);
2525: for (i = 0; i < permNames.length; i++) {
2526: byte[] privilege = connection
2527: .encodeString(permNames[i]);
2528: Vector grantees = (Vector) permissions
2529: .get(permNames[i]);
2530: for (int j = 0; j < grantees.size(); j++) {
2531: String grantee = (String) grantees.elementAt(j);
2532: String grantable = owner.equals(grantee) ? "YES"
2533: : "NO";
2534: byte[][] tuple = new byte[7][];
2535: tuple[0] = null;
2536: tuple[1] = schema;
2537: tuple[2] = table;
2538: tuple[3] = connection.encodeString(owner);
2539: tuple[4] = connection.encodeString(grantee);
2540: tuple[5] = privilege;
2541: tuple[6] = connection.encodeString(grantable);
2542: v.addElement(tuple);
2543: }
2544: }
2545: }
2546: rs.close();
2547:
2548: return (ResultSet) ((BaseStatement) createMetaDataStatement())
2549: .createDriverResultSet(f, v);
2550: }
2551:
2552: private static void sortStringArray(String s[]) {
2553: for (int i = 0; i < s.length - 1; i++) {
2554: for (int j = i + 1; j < s.length; j++) {
2555: if (s[i].compareTo(s[j]) > 0) {
2556: String tmp = s[i];
2557: s[i] = s[j];
2558: s[j] = tmp;
2559: }
2560: }
2561: }
2562: }
2563:
2564: /**
2565: * Parse an String of ACLs into a Vector of ACLs.
2566: */
2567: private static Vector parseACLArray(String aclString) {
2568: Vector acls = new Vector();
2569: if (aclString == null || aclString.length() == 0) {
2570: return acls;
2571: }
2572: boolean inQuotes = false;
2573: // start at 1 because of leading "{"
2574: int beginIndex = 1;
2575: char prevChar = ' ';
2576: for (int i = beginIndex; i < aclString.length(); i++) {
2577:
2578: char c = aclString.charAt(i);
2579: if (c == '"' && prevChar != '\\') {
2580: inQuotes = !inQuotes;
2581: } else if (c == ',' && !inQuotes) {
2582: acls.addElement(aclString.substring(beginIndex, i));
2583: beginIndex = i + 1;
2584: }
2585: prevChar = c;
2586: }
2587: // add last element removing the trailing "}"
2588: acls.addElement(aclString.substring(beginIndex, aclString
2589: .length() - 1));
2590:
2591: // Strip out enclosing quotes, if any.
2592: for (int i = 0; i < acls.size(); i++) {
2593: String acl = (String) acls.elementAt(i);
2594: if (acl.startsWith("\"") && acl.endsWith("\"")) {
2595: acl = acl.substring(1, acl.length() - 1);
2596: acls.setElementAt(acl, i);
2597: }
2598: }
2599: return acls;
2600: }
2601:
2602: /**
2603: * Add the user described by the given acl to the Vectors of users
2604: * with the privileges described by the acl.
2605: */
2606: private void addACLPrivileges(String acl, Hashtable privileges) {
2607: int equalIndex = acl.lastIndexOf("=");
2608: String name = acl.substring(0, equalIndex);
2609: if (name.length() == 0) {
2610: name = "PUBLIC";
2611: }
2612: String privs = acl.substring(equalIndex + 1);
2613: for (int i = 0; i < privs.length(); i++) {
2614: char c = privs.charAt(i);
2615: String sqlpriv;
2616: switch (c) {
2617: case 'a':
2618: sqlpriv = "INSERT";
2619: break;
2620: case 'r':
2621: sqlpriv = "SELECT";
2622: break;
2623: case 'w':
2624: sqlpriv = "UPDATE";
2625: break;
2626: case 'd':
2627: sqlpriv = "DELETE";
2628: break;
2629: case 'R':
2630: sqlpriv = "RULE";
2631: break;
2632: case 'x':
2633: sqlpriv = "REFERENCES";
2634: break;
2635: case 't':
2636: sqlpriv = "TRIGGER";
2637: break;
2638: // the folloowing can't be granted to a table, but
2639: // we'll keep them for completeness.
2640: case 'X':
2641: sqlpriv = "EXECUTE";
2642: break;
2643: case 'U':
2644: sqlpriv = "USAGE";
2645: break;
2646: case 'C':
2647: sqlpriv = "CREATE";
2648: break;
2649: case 'T':
2650: sqlpriv = "CREATE TEMP";
2651: break;
2652: default:
2653: sqlpriv = "UNKNOWN";
2654: }
2655: Vector usersWithPermission = (Vector) privileges
2656: .get(sqlpriv);
2657: if (usersWithPermission == null) {
2658: usersWithPermission = new Vector();
2659: privileges.put(sqlpriv, usersWithPermission);
2660: }
2661: usersWithPermission.addElement(name);
2662: }
2663: }
2664:
2665: /**
2666: * Take the a String representing an array of ACLs and return
2667: * a Hashtable mapping the SQL permission name to a Vector of
2668: * usernames who have that permission.
2669: */
2670: protected Hashtable parseACL(String aclArray, String owner) {
2671: if (aclArray == null || aclArray == "") {
2672: //null acl is a shortcut for owner having full privs
2673: aclArray = "{" + owner + "=arwdRxt}";
2674: }
2675: Vector acls = parseACLArray(aclArray);
2676: Hashtable privileges = new Hashtable();
2677: for (int i = 0; i < acls.size(); i++) {
2678: String acl = (String) acls.elementAt(i);
2679: addACLPrivileges(acl, privileges);
2680: }
2681: return privileges;
2682: }
2683:
2684: /*
2685: * Get a description of a table's optimal set of columns that
2686: * uniquely identifies a row. They are ordered by SCOPE.
2687: *
2688: * <P>Each column description has the following columns:
2689: * <OL>
2690: * <LI><B>SCOPE</B> short => actual scope of result
2691: * <UL>
2692: * <LI> bestRowTemporary - very temporary, while using row
2693: * <LI> bestRowTransaction - valid for remainder of current transaction
2694: * <LI> bestRowSession - valid for remainder of current session
2695: * </UL>
2696: * <LI><B>COLUMN_NAME</B> String => column name
2697: * <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
2698: * <LI><B>TYPE_NAME</B> String => Data source dependent type name
2699: * <LI><B>COLUMN_SIZE</B> int => precision
2700: * <LI><B>BUFFER_LENGTH</B> int => not used
2701: * <LI><B>DECIMAL_DIGITS</B> short => scale
2702: * <LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column
2703: * like an Oracle ROWID
2704: * <UL>
2705: * <LI> bestRowUnknown - may or may not be pseudo column
2706: * <LI> bestRowNotPseudo - is NOT a pseudo column
2707: * <LI> bestRowPseudo - is a pseudo column
2708: * </UL>
2709: * </OL>
2710: *
2711: * @param catalog a catalog name; "" retrieves those without a catalog
2712: * @param schema a schema name; "" retrieves those without a schema
2713: * @param table a table name
2714: * @param scope the scope of interest; use same values as SCOPE
2715: * @param nullable include columns that are nullable?
2716: * @return ResultSet each row is a column description
2717: */
2718: // Implementation note: This is required for Borland's JBuilder to work
2719: public java.sql.ResultSet getBestRowIdentifier(String catalog,
2720: String schema, String table, int scope, boolean nullable)
2721: throws SQLException {
2722: Field f[] = new Field[8];
2723: Vector v = new Vector(); // The new ResultSet tuple stuff
2724:
2725: f[0] = new Field("SCOPE", Oid.INT2);
2726: f[1] = new Field("COLUMN_NAME", Oid.VARCHAR);
2727: f[2] = new Field("DATA_TYPE", Oid.INT2);
2728: f[3] = new Field("TYPE_NAME", Oid.VARCHAR);
2729: f[4] = new Field("COLUMN_SIZE", Oid.INT4);
2730: f[5] = new Field("BUFFER_LENGTH", Oid.INT4);
2731: f[6] = new Field("DECIMAL_DIGITS", Oid.INT2);
2732: f[7] = new Field("PSEUDO_COLUMN", Oid.INT2);
2733:
2734: /* At the moment this simply returns a table's primary key,
2735: * if there is one. I believe other unique indexes, ctid,
2736: * and oid should also be considered. -KJ
2737: */
2738:
2739: String from;
2740: String where = "";
2741: if (connection.haveMinimumServerVersion("7.3")) {
2742: from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i ";
2743: where = " AND ct.relnamespace = n.oid ";
2744: if (schema != null && !"".equals(schema)) {
2745: where += " AND n.nspname = '" + escapeQuotes(schema)
2746: + "' ";
2747: }
2748: } else {
2749: from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i ";
2750: }
2751: String sql = "SELECT a.attname, a.atttypid, a.atttypmod "
2752: + from
2753: + " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "
2754: + " AND a.attrelid=ci.oid AND i.indisprimary "
2755: + " AND ct.relname = '" + escapeQuotes(table) + "' "
2756: + where + " ORDER BY a.attnum ";
2757:
2758: ResultSet rs = connection.createStatement().executeQuery(sql);
2759: while (rs.next()) {
2760: byte tuple[][] = new byte[8][];
2761: int typeOid = rs.getInt("atttypid");
2762: int typeMod = rs.getInt("atttypmod");
2763: int decimalDigits = TypeInfoCache
2764: .getScale(typeOid, typeMod);
2765: int columnSize = TypeInfoCache.getPrecision(typeOid,
2766: typeMod);
2767: if (columnSize == 0) {
2768: columnSize = TypeInfoCache.getDisplaySize(typeOid,
2769: typeMod);
2770: }
2771: tuple[0] = connection.encodeString(Integer.toString(scope));
2772: tuple[1] = rs.getBytes("attname");
2773: tuple[2] = connection.encodeString(Integer
2774: .toString(connection.getSQLType(typeOid)));
2775: tuple[3] = connection.encodeString(connection
2776: .getPGType(typeOid));
2777: tuple[4] = connection.encodeString(Integer
2778: .toString(columnSize));
2779: tuple[5] = null; // unused
2780: tuple[6] = connection.encodeString(Integer
2781: .toString(decimalDigits));
2782: tuple[7] = connection
2783: .encodeString(Integer
2784: .toString(java.sql.DatabaseMetaData.bestRowNotPseudo));
2785: v.addElement(tuple);
2786: }
2787:
2788: return (ResultSet) ((BaseStatement) createMetaDataStatement())
2789: .createDriverResultSet(f, v);
2790: }
2791:
2792: /*
2793: * Get a description of a table's columns that are automatically
2794: * updated when any value in a row is updated. They are
2795: * unordered.
2796: *
2797: * <P>Each column description has the following columns:
2798: * <OL>
2799: * <LI><B>SCOPE</B> short => is not used
2800: * <LI><B>COLUMN_NAME</B> String => column name
2801: * <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
2802: * <LI><B>TYPE_NAME</B> String => Data source dependent type name
2803: * <LI><B>COLUMN_SIZE</B> int => precision
2804: * <LI><B>BUFFER_LENGTH</B> int => length of column value in bytes
2805: * <LI><B>DECIMAL_DIGITS</B> short => scale
2806: * <LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column
2807: * like an Oracle ROWID
2808: * <UL>
2809: * <LI> versionColumnUnknown - may or may not be pseudo column
2810: * <LI> versionColumnNotPseudo - is NOT a pseudo column
2811: * <LI> versionColumnPseudo - is a pseudo column
2812: * </UL>
2813: * </OL>
2814: *
2815: * @param catalog a catalog name; "" retrieves those without a catalog
2816: * @param schema a schema name; "" retrieves those without a schema
2817: * @param table a table name
2818: * @return ResultSet each row is a column description
2819: */
2820: public java.sql.ResultSet getVersionColumns(String catalog,
2821: String schema, String table) throws SQLException {
2822: Field f[] = new Field[8];
2823: Vector v = new Vector(); // The new ResultSet tuple stuff
2824:
2825: f[0] = new Field("SCOPE", Oid.INT2);
2826: f[1] = new Field("COLUMN_NAME", Oid.VARCHAR);
2827: f[2] = new Field("DATA_TYPE", Oid.INT2);
2828: f[3] = new Field("TYPE_NAME", Oid.VARCHAR);
2829: f[4] = new Field("COLUMN_SIZE", Oid.INT4);
2830: f[5] = new Field("BUFFER_LENGTH", Oid.INT4);
2831: f[6] = new Field("DECIMAL_DIGITS", Oid.INT2);
2832: f[7] = new Field("PSEUDO_COLUMN", Oid.INT2);
2833:
2834: byte tuple[][] = new byte[8][];
2835:
2836: /* Postgresql does not have any column types that are
2837: * automatically updated like some databases' timestamp type.
2838: * We can't tell what rules or triggers might be doing, so we
2839: * are left with the system columns that change on an update.
2840: * An update may change all of the following system columns:
2841: * ctid, xmax, xmin, cmax, and cmin. Depending on if we are
2842: * in a transaction and wether we roll it back or not the
2843: * only guaranteed change is to ctid. -KJ
2844: */
2845:
2846: tuple[0] = null;
2847: tuple[1] = connection.encodeString("ctid");
2848: tuple[2] = connection.encodeString(Integer.toString(connection
2849: .getSQLType("tid")));
2850: tuple[3] = connection.encodeString("tid");
2851: tuple[4] = null;
2852: tuple[5] = null;
2853: tuple[6] = null;
2854: tuple[7] = connection
2855: .encodeString(Integer
2856: .toString(java.sql.DatabaseMetaData.versionColumnPseudo));
2857: v.addElement(tuple);
2858:
2859: /* Perhaps we should check that the given
2860: * catalog.schema.table actually exists. -KJ
2861: */
2862: return (ResultSet) ((BaseStatement) createMetaDataStatement())
2863: .createDriverResultSet(f, v);
2864: }
2865:
2866: /*
2867: * Get a description of a table's primary key columns. They
2868: * are ordered by COLUMN_NAME.
2869: *
2870: * <P>Each column description has the following columns:
2871: * <OL>
2872: * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
2873: * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
2874: * <LI><B>TABLE_NAME</B> String => table name
2875: * <LI><B>COLUMN_NAME</B> String => column name
2876: * <LI><B>KEY_SEQ</B> short => sequence number within primary key
2877: * <LI><B>PK_NAME</B> String => primary key name (may be null)
2878: * </OL>
2879: *
2880: * @param catalog a catalog name; "" retrieves those without a catalog
2881: * @param schema a schema name pattern; "" retrieves those
2882: * without a schema
2883: * @param table a table name
2884: * @return ResultSet each row is a primary key column description
2885: */
2886: public java.sql.ResultSet getPrimaryKeys(String catalog,
2887: String schema, String table) throws SQLException {
2888: String select;
2889: String from;
2890: String where = "";
2891: if (connection.haveMinimumServerVersion("7.3")) {
2892: select = "SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ";
2893: from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i ";
2894: where = " AND ct.relnamespace = n.oid ";
2895: if (schema != null && !"".equals(schema)) {
2896: where += " AND n.nspname = '" + escapeQuotes(schema)
2897: + "' ";
2898: }
2899: } else {
2900: select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
2901: from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i ";
2902: }
2903: String sql = select + " ct.relname AS TABLE_NAME, "
2904: + " a.attname AS COLUMN_NAME, "
2905: + " a.attnum AS KEY_SEQ, " + " ci.relname AS PK_NAME "
2906: + from
2907: + " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid "
2908: + " AND a.attrelid=ci.oid AND i.indisprimary ";
2909: if (table != null && !"".equals(table)) {
2910: sql += " AND ct.relname = '" + escapeQuotes(table) + "' ";
2911: }
2912: sql += where + " ORDER BY table_name, pk_name, key_seq";
2913: return createMetaDataStatement().executeQuery(sql);
2914: }
2915:
2916: /**
2917: *
2918: * @param primaryCatalog
2919: * @param primarySchema
2920: * @param primaryTable if provided will get the keys exported by this table
2921: * @param foreignTable if provided will get the keys imported by this table
2922: * @return ResultSet
2923: * @throws SQLException
2924: */
2925:
2926: protected java.sql.ResultSet getImportedExportedKeys(
2927: String primaryCatalog, String primarySchema,
2928: String primaryTable, String foreignCatalog,
2929: String foreignSchema, String foreignTable)
2930: throws SQLException {
2931: Field f[] = new Field[14];
2932:
2933: f[0] = new Field("PKTABLE_CAT", Oid.VARCHAR);
2934: f[1] = new Field("PKTABLE_SCHEM", Oid.VARCHAR);
2935: f[2] = new Field("PKTABLE_NAME", Oid.VARCHAR);
2936: f[3] = new Field("PKCOLUMN_NAME", Oid.VARCHAR);
2937: f[4] = new Field("FKTABLE_CAT", Oid.VARCHAR);
2938: f[5] = new Field("FKTABLE_SCHEM", Oid.VARCHAR);
2939: f[6] = new Field("FKTABLE_NAME", Oid.VARCHAR);
2940: f[7] = new Field("FKCOLUMN_NAME", Oid.VARCHAR);
2941: f[8] = new Field("KEY_SEQ", Oid.INT2);
2942: f[9] = new Field("UPDATE_RULE", Oid.INT2);
2943: f[10] = new Field("DELETE_RULE", Oid.INT2);
2944: f[11] = new Field("FK_NAME", Oid.VARCHAR);
2945: f[12] = new Field("PK_NAME", Oid.VARCHAR);
2946: f[13] = new Field("DEFERRABILITY", Oid.INT2);
2947:
2948: String select;
2949: String from;
2950: String where = "";
2951:
2952: /*
2953: * The addition of the pg_constraint in 7.3 table should have really
2954: * helped us out here, but it comes up just a bit short.
2955: * - The conkey, confkey columns aren't really useful without
2956: * contrib/array unless we want to issues separate queries.
2957: * - Unique indexes that can support foreign keys are not necessarily
2958: * added to pg_constraint. Also multiple unique indexes covering
2959: * the same keys can be created which make it difficult to determine
2960: * the PK_NAME field.
2961: */
2962:
2963: if (connection.haveMinimumServerVersion("7.4")) {
2964: String sql = "SELECT NULL::text AS PKTABLE_CAT, pkn.nspname AS PKTABLE_SCHEM, pkc.relname AS PKTABLE_NAME, pka.attname AS PKCOLUMN_NAME, "
2965: + "NULL::text AS FKTABLE_CAT, fkn.nspname AS FKTABLE_SCHEM, fkc.relname AS FKTABLE_NAME, fka.attname AS FKCOLUMN_NAME, "
2966: + "pos.n AS KEY_SEQ, "
2967: + "CASE con.confupdtype "
2968: + " WHEN 'c' THEN "
2969: + DatabaseMetaData.importedKeyCascade
2970: + " WHEN 'n' THEN "
2971: + DatabaseMetaData.importedKeySetNull
2972: + " WHEN 'd' THEN "
2973: + DatabaseMetaData.importedKeySetDefault
2974: + " WHEN 'r' THEN "
2975: + DatabaseMetaData.importedKeyRestrict
2976: + " WHEN 'a' THEN "
2977: + DatabaseMetaData.importedKeyNoAction
2978: + " ELSE NULL END AS UPDATE_RULE, "
2979: + "CASE con.confdeltype "
2980: + " WHEN 'c' THEN "
2981: + DatabaseMetaData.importedKeyCascade
2982: + " WHEN 'n' THEN "
2983: + DatabaseMetaData.importedKeySetNull
2984: + " WHEN 'd' THEN "
2985: + DatabaseMetaData.importedKeySetDefault
2986: + " WHEN 'r' THEN "
2987: + DatabaseMetaData.importedKeyRestrict
2988: + " WHEN 'a' THEN "
2989: + DatabaseMetaData.importedKeyNoAction
2990: + " ELSE NULL END AS DELETE_RULE, "
2991: + "con.conname AS FK_NAME, pkic.relname AS PK_NAME, "
2992: + "CASE "
2993: + " WHEN con.condeferrable AND con.condeferred THEN "
2994: + DatabaseMetaData.importedKeyInitiallyDeferred
2995: + " WHEN con.condeferrable THEN "
2996: + DatabaseMetaData.importedKeyInitiallyImmediate
2997: + " ELSE "
2998: + DatabaseMetaData.importedKeyNotDeferrable
2999: + " END AS DEFERRABILITY "
3000: + " FROM "
3001: + " pg_catalog.pg_namespace pkn, pg_catalog.pg_class pkc, pg_catalog.pg_attribute pka, "
3002: + " pg_catalog.pg_namespace fkn, pg_catalog.pg_class fkc, pg_catalog.pg_attribute fka, "
3003: + " pg_catalog.pg_constraint con, ";
3004: if (connection.haveMinimumServerVersion("8.0")) {
3005: sql += " pg_catalog.generate_series(1, "
3006: + getMaxIndexKeys() + ") pos(n), ";
3007: } else {
3008: sql += " information_schema._pg_keypositions() pos(n), ";
3009: }
3010: sql += " pg_catalog.pg_depend dep, pg_catalog.pg_class pkic "
3011: + " WHERE pkn.oid = pkc.relnamespace AND pkc.oid = pka.attrelid AND pka.attnum = con.confkey[pos.n] AND con.confrelid = pkc.oid "
3012: + " AND fkn.oid = fkc.relnamespace AND fkc.oid = fka.attrelid AND fka.attnum = con.conkey[pos.n] AND con.conrelid = fkc.oid "
3013: + " AND con.contype = 'f' AND con.oid = dep.objid AND pkic.oid = dep.refobjid AND pkic.relkind = 'i' AND dep.classid = 'pg_constraint'::regclass::oid AND dep.refclassid = 'pg_class'::regclass::oid ";
3014: if (primarySchema != null && !"".equals(primarySchema)) {
3015: sql += " AND pkn.nspname = '"
3016: + escapeQuotes(primarySchema) + "' ";
3017: }
3018: if (foreignSchema != null && !"".equals(foreignSchema)) {
3019: sql += " AND fkn.nspname = '"
3020: + escapeQuotes(foreignSchema) + "' ";
3021: }
3022: if (primaryTable != null && !"".equals(primaryTable)) {
3023: sql += " AND pkc.relname = '"
3024: + escapeQuotes(primaryTable) + "' ";
3025: }
3026: if (foreignTable != null && !"".equals(foreignTable)) {
3027: sql += " AND fkc.relname = '"
3028: + escapeQuotes(foreignTable) + "' ";
3029: }
3030:
3031: if (primaryTable != null) {
3032: sql += " ORDER BY fkn.nspname,fkc.relname,pos.n";
3033: } else {
3034: sql += " ORDER BY pkn.nspname,pkc.relname,pos.n";
3035: }
3036:
3037: return createMetaDataStatement().executeQuery(sql);
3038: } else if (connection.haveMinimumServerVersion("7.3")) {
3039: select = "SELECT DISTINCT n1.nspname as pnspname,n2.nspname as fnspname, ";
3040: from = " FROM pg_catalog.pg_namespace n1 "
3041: + " JOIN pg_catalog.pg_class c1 ON (c1.relnamespace = n1.oid) "
3042: + " JOIN pg_catalog.pg_index i ON (c1.oid=i.indrelid) "
3043: + " JOIN pg_catalog.pg_class ic ON (i.indexrelid=ic.oid) "
3044: + " JOIN pg_catalog.pg_attribute a ON (ic.oid=a.attrelid), "
3045: + " pg_catalog.pg_namespace n2 "
3046: + " JOIN pg_catalog.pg_class c2 ON (c2.relnamespace=n2.oid), "
3047: + " pg_catalog.pg_trigger t1 "
3048: + " JOIN pg_catalog.pg_proc p1 ON (t1.tgfoid=p1.oid), "
3049: + " pg_catalog.pg_trigger t2 "
3050: + " JOIN pg_catalog.pg_proc p2 ON (t2.tgfoid=p2.oid) ";
3051: if (primarySchema != null && !"".equals(primarySchema)) {
3052: where += " AND n1.nspname = '"
3053: + escapeQuotes(primarySchema) + "' ";
3054: }
3055: if (foreignSchema != null && !"".equals(foreignSchema)) {
3056: where += " AND n2.nspname = '"
3057: + escapeQuotes(foreignSchema) + "' ";
3058: }
3059: } else {
3060: select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
3061: from = " FROM pg_class c1 "
3062: + " JOIN pg_index i ON (c1.oid=i.indrelid) "
3063: + " JOIN pg_class ic ON (i.indexrelid=ic.oid) "
3064: + " JOIN pg_attribute a ON (ic.oid=a.attrelid), "
3065: + " pg_class c2, " + " pg_trigger t1 "
3066: + " JOIN pg_proc p1 ON (t1.tgfoid=p1.oid), "
3067: + " pg_trigger t2 "
3068: + " JOIN pg_proc p2 ON (t2.tgfoid=p2.oid) ";
3069: }
3070:
3071: String sql = select + "c1.relname as prelname, "
3072: + "c2.relname as frelname, " + "t1.tgconstrname, "
3073: + "a.attnum as keyseq, " + "ic.relname as fkeyname, "
3074: + "t1.tgdeferrable, "
3075: + "t1.tginitdeferred, "
3076: + "t1.tgnargs,t1.tgargs, "
3077: + "p1.proname as updaterule, "
3078: + "p2.proname as deleterule "
3079: + from
3080: + "WHERE "
3081: // isolate the update rule
3082: + "(t1.tgrelid=c1.oid " + "AND t1.tgisconstraint "
3083: + "AND t1.tgconstrrelid=c2.oid "
3084: + "AND p1.proname ~ '^RI_FKey_.*_upd$') "
3085:
3086: + "AND "
3087: // isolate the delete rule
3088: + "(t2.tgrelid=c1.oid " + "AND t2.tgisconstraint "
3089: + "AND t2.tgconstrrelid=c2.oid "
3090: + "AND p2.proname ~ '^RI_FKey_.*_del$') "
3091:
3092: + "AND i.indisprimary " + where;
3093:
3094: if (primaryTable != null) {
3095: sql += "AND c1.relname='" + escapeQuotes(primaryTable)
3096: + "' ";
3097: }
3098: if (foreignTable != null) {
3099: sql += "AND c2.relname='" + escapeQuotes(foreignTable)
3100: + "' ";
3101: }
3102:
3103: sql += "ORDER BY ";
3104:
3105: // orderby is as follows getExported, orders by FKTABLE,
3106: // getImported orders by PKTABLE
3107: // getCrossReference orders by FKTABLE, so this should work for both,
3108: // since when getting crossreference, primaryTable will be defined
3109:
3110: if (primaryTable != null) {
3111: if (connection.haveMinimumServerVersion("7.3")) {
3112: sql += "fnspname,";
3113: }
3114: sql += "frelname";
3115: } else {
3116: if (connection.haveMinimumServerVersion("7.3")) {
3117: sql += "pnspname,";
3118: }
3119: sql += "prelname";
3120: }
3121:
3122: sql += ",keyseq";
3123:
3124: ResultSet rs = connection.createStatement().executeQuery(sql);
3125:
3126: // returns the following columns
3127: // and some example data with a table defined as follows
3128:
3129: // create table people ( id int primary key);
3130: // create table policy ( id int primary key);
3131: // create table users ( id int primary key, people_id int references people(id), policy_id int references policy(id))
3132:
3133: // prelname | frelname | tgconstrname | keyseq | fkeyName | tgdeferrable | tginitdeferred
3134: // 1 | 2 | 3 | 4 | 5 | 6 | 7
3135:
3136: // people | users | <unnamed> | 1 | people_pkey | f | f
3137:
3138: // | tgnargs | tgargs | updaterule | deleterule
3139: // | 8 | 9 | 10 | 11
3140: // | 6 | <unnamed>\000users\000people\000UNSPECIFIED\000people_id\000id\000 | RI_FKey_noaction_upd | RI_FKey_noaction_del
3141:
3142: Vector tuples = new Vector();
3143:
3144: while (rs.next()) {
3145: byte tuple[][] = new byte[14][];
3146:
3147: tuple[1] = rs.getBytes(1); //PKTABLE_SCHEM
3148: tuple[5] = rs.getBytes(2); //FKTABLE_SCHEM
3149: tuple[2] = rs.getBytes(3); //PKTABLE_NAME
3150: tuple[6] = rs.getBytes(4); //FKTABLE_NAME
3151: String fKeyName = rs.getString(5);
3152: String updateRule = rs.getString(12);
3153:
3154: if (updateRule != null) {
3155: // Rules look like this RI_FKey_noaction_del so we want to pull out the part between the 'Key_' and the last '_' s
3156:
3157: String rule = updateRule.substring(8, updateRule
3158: .length() - 4);
3159:
3160: int action = java.sql.DatabaseMetaData.importedKeyNoAction;
3161:
3162: if (rule == null || "noaction".equals(rule))
3163: action = java.sql.DatabaseMetaData.importedKeyNoAction;
3164: if ("cascade".equals(rule))
3165: action = java.sql.DatabaseMetaData.importedKeyCascade;
3166: else if ("setnull".equals(rule))
3167: action = java.sql.DatabaseMetaData.importedKeySetNull;
3168: else if ("setdefault".equals(rule))
3169: action = java.sql.DatabaseMetaData.importedKeySetDefault;
3170: else if ("restrict".equals(rule))
3171: action = java.sql.DatabaseMetaData.importedKeyRestrict;
3172:
3173: tuple[9] = connection.encodeString(Integer
3174: .toString(action));
3175:
3176: }
3177:
3178: String deleteRule = rs.getString(13);
3179:
3180: if (deleteRule != null) {
3181:
3182: String rule = deleteRule.substring(8, deleteRule
3183: .length() - 4);
3184:
3185: int action = java.sql.DatabaseMetaData.importedKeyNoAction;
3186: if ("cascade".equals(rule))
3187: action = java.sql.DatabaseMetaData.importedKeyCascade;
3188: else if ("setnull".equals(rule))
3189: action = java.sql.DatabaseMetaData.importedKeySetNull;
3190: else if ("setdefault".equals(rule))
3191: action = java.sql.DatabaseMetaData.importedKeySetDefault;
3192: else if ("restrict".equals(rule))
3193: action = java.sql.DatabaseMetaData.importedKeyRestrict;
3194: tuple[10] = connection.encodeString(Integer
3195: .toString(action));
3196: }
3197:
3198: int keySequence = rs.getInt(6); //KEY_SEQ
3199:
3200: // Parse the tgargs data
3201: String fkeyColumn = "";
3202: String pkeyColumn = "";
3203: String fkName = "";
3204: // Note, I am guessing at most of this, but it should be close
3205: // if not, please correct
3206: // the keys are in pairs and start after the first four arguments
3207: // the arguments are seperated by \000
3208:
3209: String targs = rs.getString(11);
3210:
3211: // args look like this
3212: //<unnamed>\000ww\000vv\000UNSPECIFIED\000m\000a\000n\000b\000
3213: // we are primarily interested in the column names which are the last items in the string
3214:
3215: Vector tokens = tokenize(targs, "\\000");
3216: if (tokens.size() > 0) {
3217: fkName = (String) tokens.elementAt(0);
3218: }
3219:
3220: if (fkName.startsWith("<unnamed>")) {
3221: fkName = targs;
3222: }
3223:
3224: int element = 4 + (keySequence - 1) * 2;
3225: if (tokens.size() > element) {
3226: fkeyColumn = (String) tokens.elementAt(element);
3227: }
3228:
3229: element++;
3230: if (tokens.size() > element) {
3231: pkeyColumn = (String) tokens.elementAt(element);
3232: }
3233:
3234: tuple[3] = connection.encodeString(pkeyColumn); //PKCOLUMN_NAME
3235: tuple[7] = connection.encodeString(fkeyColumn); //FKCOLUMN_NAME
3236:
3237: tuple[8] = rs.getBytes(6); //KEY_SEQ
3238: tuple[11] = connection.encodeString(fkName); //FK_NAME this will give us a unique name for the foreign key
3239: tuple[12] = rs.getBytes(7); //PK_NAME
3240:
3241: // DEFERRABILITY
3242: int deferrability = java.sql.DatabaseMetaData.importedKeyNotDeferrable;
3243: boolean deferrable = rs.getBoolean(8);
3244: boolean initiallyDeferred = rs.getBoolean(9);
3245: if (deferrable) {
3246: if (initiallyDeferred)
3247: deferrability = java.sql.DatabaseMetaData.importedKeyInitiallyDeferred;
3248: else
3249: deferrability = java.sql.DatabaseMetaData.importedKeyInitiallyImmediate;
3250: }
3251: tuple[13] = connection.encodeString(Integer
3252: .toString(deferrability));
3253:
3254: tuples.addElement(tuple);
3255: }
3256:
3257: return (ResultSet) ((BaseStatement) createMetaDataStatement())
3258: .createDriverResultSet(f, tuples);
3259: }
3260:
3261: /*
3262: * Get a description of the primary key columns that are
3263: * referenced by a table's foreign key columns (the primary keys
3264: * imported by a table). They are ordered by PKTABLE_CAT,
3265: * PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.
3266: *
3267: * <P>Each primary key column description has the following columns:
3268: * <OL>
3269: * <LI><B>PKTABLE_CAT</B> String => primary key table catalog
3270: * being imported (may be null)
3271: * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema
3272: * being imported (may be null)
3273: * <LI><B>PKTABLE_NAME</B> String => primary key table name
3274: * being imported
3275: * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
3276: * being imported
3277: * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
3278: * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
3279: * <LI><B>FKTABLE_NAME</B> String => foreign key table name
3280: * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
3281: * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
3282: * <LI><B>UPDATE_RULE</B> short => What happens to
3283: * foreign key when primary is updated:
3284: * <UL>
3285: * <LI> importedKeyCascade - change imported key to agree
3286: * with primary key update
3287: * <LI> importedKeyRestrict - do not allow update of primary
3288: * key if it has been imported
3289: * <LI> importedKeySetNull - change imported key to NULL if
3290: * its primary key has been updated
3291: * </UL>
3292: * <LI><B>DELETE_RULE</B> short => What happens to
3293: * the foreign key when primary is deleted.
3294: * <UL>
3295: * <LI> importedKeyCascade - delete rows that import a deleted key
3296: * <LI> importedKeyRestrict - do not allow delete of primary
3297: * key if it has been imported
3298: * <LI> importedKeySetNull - change imported key to NULL if
3299: * its primary key has been deleted
3300: * </UL>
3301: * <LI><B>FK_NAME</B> String => foreign key name (may be null)
3302: * <LI><B>PK_NAME</B> String => primary key name (may be null)
3303: * </OL>
3304: *
3305: * @param catalog a catalog name; "" retrieves those without a catalog
3306: * @param schema a schema name pattern; "" retrieves those
3307: * without a schema
3308: * @param table a table name
3309: * @return ResultSet each row is a primary key column description
3310: * @see #getExportedKeys
3311: */
3312: public java.sql.ResultSet getImportedKeys(String catalog,
3313: String schema, String table) throws SQLException {
3314: return getImportedExportedKeys(null, null, null, catalog,
3315: schema, table);
3316: }
3317:
3318: /*
3319: * Get a description of a foreign key columns that reference a
3320: * table's primary key columns (the foreign keys exported by a
3321: * table). They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
3322: * FKTABLE_NAME, and KEY_SEQ.
3323: *
3324: * This method is currently unimplemented.
3325: *
3326: * <P>Each foreign key column description has the following columns:
3327: * <OL>
3328: * <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
3329: * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
3330: * <LI><B>PKTABLE_NAME</B> String => primary key table name
3331: * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
3332: * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
3333: * being exported (may be null)
3334: * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
3335: * being exported (may be null)
3336: * <LI><B>FKTABLE_NAME</B> String => foreign key table name
3337: * being exported
3338: * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
3339: * being exported
3340: * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
3341: * <LI><B>UPDATE_RULE</B> short => What happens to
3342: * foreign key when primary is updated:
3343: * <UL>
3344: * <LI> importedKeyCascade - change imported key to agree
3345: * with primary key update
3346: * <LI> importedKeyRestrict - do not allow update of primary
3347: * key if it has been imported
3348: * <LI> importedKeySetNull - change imported key to NULL if
3349: * its primary key has been updated
3350: * </UL>
3351: * <LI><B>DELETE_RULE</B> short => What happens to
3352: * the foreign key when primary is deleted.
3353: * <UL>
3354: * <LI> importedKeyCascade - delete rows that import a deleted key
3355: * <LI> importedKeyRestrict - do not allow delete of primary
3356: * key if it has been imported
3357: * <LI> importedKeySetNull - change imported key to NULL if
3358: * its primary key has been deleted
3359: * </UL>
3360: * <LI><B>FK_NAME</B> String => foreign key identifier (may be null)
3361: * <LI><B>PK_NAME</B> String => primary key identifier (may be null)
3362: * </OL>
3363: *
3364: * @param catalog a catalog name; "" retrieves those without a catalog
3365: * @param schema a schema name pattern; "" retrieves those
3366: * without a schema
3367: * @param table a table name
3368: * @return ResultSet each row is a foreign key column description
3369: * @see #getImportedKeys
3370: */
3371: public java.sql.ResultSet getExportedKeys(String catalog,
3372: String schema, String table) throws SQLException {
3373: return getImportedExportedKeys(catalog, schema, table, null,
3374: null, null);
3375: }
3376:
3377: /*
3378: * Get a description of the foreign key columns in the foreign key
3379: * table that reference the primary key columns of the primary key
3380: * table (describe how one table imports another's key.) This
3381: * should normally return a single foreign key/primary key pair
3382: * (most tables only import a foreign key from a table once.) They
3383: * are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and
3384: * KEY_SEQ.
3385: *
3386: * This method is currently unimplemented.
3387: *
3388: * <P>Each foreign key column description has the following columns:
3389: * <OL>
3390: * <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
3391: * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
3392: * <LI><B>PKTABLE_NAME</B> String => primary key table name
3393: * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
3394: * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
3395: * being exported (may be null)
3396: * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
3397: * being exported (may be null)
3398: * <LI><B>FKTABLE_NAME</B> String => foreign key table name
3399: * being exported
3400: * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
3401: * being exported
3402: * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
3403: * <LI><B>UPDATE_RULE</B> short => What happens to
3404: * foreign key when primary is updated:
3405: * <UL>
3406: * <LI> importedKeyCascade - change imported key to agree
3407: * with primary key update
3408: * <LI> importedKeyRestrict - do not allow update of primary
3409: * key if it has been imported
3410: * <LI> importedKeySetNull - change imported key to NULL if
3411: * its primary key has been updated
3412: * </UL>
3413: * <LI><B>DELETE_RULE</B> short => What happens to
3414: * the foreign key when primary is deleted.
3415: * <UL>
3416: * <LI> importedKeyCascade - delete rows that import a deleted key
3417: * <LI> importedKeyRestrict - do not allow delete of primary
3418: * key if it has been imported
3419: * <LI> importedKeySetNull - change imported key to NULL if
3420: * its primary key has been deleted
3421: * </UL>
3422: * <LI><B>FK_NAME</B> String => foreign key identifier (may be null)
3423: * <LI><B>PK_NAME</B> String => primary key identifier (may be null)
3424: * </OL>
3425: *
3426: * @param catalog a catalog name; "" retrieves those without a catalog
3427: * @param schema a schema name pattern; "" retrieves those
3428: * without a schema
3429: * @param table a table name
3430: * @return ResultSet each row is a foreign key column description
3431: * @see #getImportedKeys
3432: */
3433: public java.sql.ResultSet getCrossReference(String primaryCatalog,
3434: String primarySchema, String primaryTable,
3435: String foreignCatalog, String foreignSchema,
3436: String foreignTable) throws SQLException {
3437: return getImportedExportedKeys(primaryCatalog, primarySchema,
3438: primaryTable, foreignCatalog, foreignSchema,
3439: foreignTable);
3440: }
3441:
3442: /*
3443: * Get a description of all the standard SQL types supported by
3444: * this database. They are ordered by DATA_TYPE and then by how
3445: * closely the data type maps to the corresponding JDBC SQL type.
3446: *
3447: * <P>Each type description has the following columns:
3448: * <OL>
3449: * <LI><B>TYPE_NAME</B> String => Type name
3450: * <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
3451: * <LI><B>PRECISION</B> int => maximum precision
3452: * <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal
3453: * (may be null)
3454: * <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal
3455: (may be null)
3456: * <LI><B>CREATE_PARAMS</B> String => parameters used in creating
3457: * the type (may be null)
3458: * <LI><B>NULLABLE</B> short => can you use NULL for this type?
3459: * <UL>
3460: * <LI> typeNoNulls - does not allow NULL values
3461: * <LI> typeNullable - allows NULL values
3462: * <LI> typeNullableUnknown - nullability unknown
3463: * </UL>
3464: * <LI><B>CASE_SENSITIVE</B> boolean=> is it case sensitive?
3465: * <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
3466: * <UL>
3467: * <LI> typePredNone - No support
3468: * <LI> typePredChar - Only supported with WHERE .. LIKE
3469: * <LI> typePredBasic - Supported except for WHERE .. LIKE
3470: * <LI> typeSearchable - Supported for all WHERE ..
3471: * </UL>
3472: * <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned?
3473: * <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value?
3474: * <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
3475: * auto-increment value?
3476: * <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
3477: * (may be null)
3478: * <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
3479: * <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
3480: * <LI><B>SQL_DATA_TYPE</B> int => unused
3481: * <LI><B>SQL_DATETIME_SUB</B> int => unused
3482: * <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
3483: * </OL>
3484: *
3485: * @return ResultSet each row is a SQL type description
3486: */
3487: public java.sql.ResultSet getTypeInfo() throws SQLException {
3488:
3489: Field f[] = new Field[18];
3490: Vector v = new Vector(); // The new ResultSet tuple stuff
3491:
3492: f[0] = new Field("TYPE_NAME", Oid.VARCHAR);
3493: f[1] = new Field("DATA_TYPE", Oid.INT2);
3494: f[2] = new Field("PRECISION", Oid.INT4);
3495: f[3] = new Field("LITERAL_PREFIX", Oid.VARCHAR);
3496: f[4] = new Field("LITERAL_SUFFIX", Oid.VARCHAR);
3497: f[5] = new Field("CREATE_PARAMS", Oid.VARCHAR);
3498: f[6] = new Field("NULLABLE", Oid.INT2);
3499: f[7] = new Field("CASE_SENSITIVE", Oid.BOOL);
3500: f[8] = new Field("SEARCHABLE", Oid.INT2);
3501: f[9] = new Field("UNSIGNED_ATTRIBUTE", Oid.BOOL);
3502: f[10] = new Field("FIXED_PREC_SCALE", Oid.BOOL);
3503: f[11] = new Field("AUTO_INCREMENT", Oid.BOOL);
3504: f[12] = new Field("LOCAL_TYPE_NAME", Oid.VARCHAR);
3505: f[13] = new Field("MINIMUM_SCALE", Oid.INT2);
3506: f[14] = new Field("MAXIMUM_SCALE", Oid.INT2);
3507: f[15] = new Field("SQL_DATA_TYPE", Oid.INT4);
3508: f[16] = new Field("SQL_DATETIME_SUB", Oid.INT4);
3509: f[17] = new Field("NUM_PREC_RADIX", Oid.INT4);
3510:
3511: String sql;
3512: if (connection.haveMinimumServerVersion("7.3")) {
3513: sql = "SELECT typname,oid FROM pg_catalog.pg_type";
3514: } else {
3515: sql = "SELECT typname,oid FROM pg_type";
3516: }
3517:
3518: ResultSet rs = connection.createStatement().executeQuery(sql);
3519: // cache some results, this will keep memory useage down, and speed
3520: // things up a little.
3521: byte bZero[] = connection.encodeString("0");
3522: byte b10[] = connection.encodeString("10");
3523: byte bf[] = connection.encodeString("f");
3524: byte bt[] = connection.encodeString("t");
3525: byte bNullable[] = connection.encodeString(Integer
3526: .toString(java.sql.DatabaseMetaData.typeNullable));
3527: byte bSearchable[] = connection.encodeString(Integer
3528: .toString(java.sql.DatabaseMetaData.typeSearchable));
3529:
3530: while (rs.next()) {
3531: byte[][] tuple = new byte[18][];
3532: String typname = rs.getString(1);
3533: int typeOid = rs.getInt(2);
3534:
3535: tuple[0] = connection.encodeString(typname);
3536: tuple[1] = connection.encodeString(Integer
3537: .toString(connection.getSQLType(typname)));
3538: tuple[2] = connection.encodeString(Integer
3539: .toString(TypeInfoCache
3540: .getMaximumPrecision(typeOid)));
3541: tuple[6] = bNullable; // all types can be null
3542: tuple[7] = TypeInfoCache.isCaseSensitive(typeOid) ? bt : bf;
3543: tuple[8] = bSearchable; // any thing can be used in the WHERE clause
3544: tuple[9] = TypeInfoCache.isSigned(typeOid) ? bt : bf;
3545: tuple[10] = bf; // false for now - must handle money
3546: tuple[11] = bf; // false - it isn't autoincrement
3547: tuple[13] = bZero; // min scale is zero
3548: // only numeric can supports a scale.
3549: tuple[14] = (typeOid == Oid.NUMERIC) ? connection
3550: .encodeString("1000") : bZero;
3551:
3552: // 12 - LOCAL_TYPE_NAME is null
3553: // 15 & 16 are unused so we return null
3554: tuple[17] = b10; // everything is base 10
3555: v.addElement(tuple);
3556:
3557: // add pseudo-type serial, bigserial
3558: if (typname.equals("int4")) {
3559: byte[][] tuple1 = (byte[][]) tuple.clone();
3560:
3561: tuple1[0] = connection.encodeString("serial");
3562: tuple1[11] = bt;
3563: v.addElement(tuple1);
3564: } else if (typname.equals("int8")) {
3565: byte[][] tuple1 = (byte[][]) tuple.clone();
3566:
3567: tuple1[0] = connection.encodeString("bigserial");
3568: tuple1[11] = bt;
3569: v.addElement(tuple1);
3570: }
3571:
3572: }
3573: rs.close();
3574:
3575: return (ResultSet) ((BaseStatement) createMetaDataStatement())
3576: .createDriverResultSet(f, v);
3577: }
3578:
3579: /*
3580: * Get a description of a table's indices and statistics. They are
3581: * ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
3582: *
3583: * <P>Each index column description has the following columns:
3584: * <OL>
3585: * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
3586: * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
3587: * <LI><B>TABLE_NAME</B> String => table name
3588: * <LI><B>NON_UNIQUE</B> boolean => Can index values be non-unique?
3589: * false when TYPE is tableIndexStatistic
3590: * <LI><B>INDEX_QUALIFIER</B> String => index catalog (may be null);
3591: * null when TYPE is tableIndexStatistic
3592: * <LI><B>INDEX_NAME</B> String => index name; null when TYPE is
3593: * tableIndexStatistic
3594: * <LI><B>TYPE</B> short => index type:
3595: * <UL>
3596: * <LI> tableIndexStatistic - this identifies table statistics that are
3597: * returned in conjuction with a table's index descriptions
3598: * <LI> tableIndexClustered - this is a clustered index
3599: * <LI> tableIndexHashed - this is a hashed index
3600: * <LI> tableIndexOther - this is some other style of index
3601: * </UL>
3602: * <LI><B>ORDINAL_POSITION</B> short => column sequence number
3603: * within index; zero when TYPE is tableIndexStatistic
3604: * <LI><B>COLUMN_NAME</B> String => column name; null when TYPE is
3605: * tableIndexStatistic
3606: * <LI><B>ASC_OR_DESC</B> String => column sort sequence, "A" => ascending
3607: * "D" => descending, may be null if sort sequence is not supported;
3608: * null when TYPE is tableIndexStatistic
3609: * <LI><B>CARDINALITY</B> int => When TYPE is tableIndexStatisic then
3610: * this is the number of rows in the table; otherwise it is the
3611: * number of unique values in the index.
3612: * <LI><B>PAGES</B> int => When TYPE is tableIndexStatisic then
3613: * this is the number of pages used for the table, otherwise it
3614: * is the number of pages used for the current index.
3615: * <LI><B>FILTER_CONDITION</B> String => Filter condition, if any.
3616: * (may be null)
3617: * </OL>
3618: *
3619: * @param catalog a catalog name; "" retrieves those without a catalog
3620: * @param schema a schema name pattern; "" retrieves those without a schema
3621: * @param table a table name
3622: * @param unique when true, return only indices for unique values;
3623: * when false, return indices regardless of whether unique or not
3624: * @param approximate when true, result is allowed to reflect approximate
3625: * or out of data values; when false, results are requested to be
3626: * accurate
3627: * @return ResultSet each row is an index column description
3628: */
3629: // Implementation note: This is required for Borland's JBuilder to work
3630: public java.sql.ResultSet getIndexInfo(String catalog,
3631: String schema, String tableName, boolean unique,
3632: boolean approximate) throws SQLException {
3633: String select;
3634: String from;
3635: String where = "";
3636:
3637: /* This is a complicated function because we have three possible
3638: * situations:
3639: * <= 7.2 no schemas, single column functional index
3640: * 7.3 schemas, single column functional index
3641: * >= 7.4 schemas, multi-column expressional index
3642: *
3643: * with the single column functional index we need an extra
3644: * join to the table's pg_attribute data to get the column
3645: * the function operates on.
3646: */
3647: if (connection.haveMinimumServerVersion("7.3")) {
3648: select = "SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ";
3649: from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_am am ";
3650: where = " AND n.oid = ct.relnamespace ";
3651:
3652: if (!connection.haveMinimumServerVersion("7.4")) {
3653: from += ", pg_catalog.pg_attribute ai, pg_catalog.pg_index i LEFT JOIN pg_catalog.pg_proc ip ON (i.indproc = ip.oid) ";
3654: where += " AND ai.attnum = i.indkey[0] AND ai.attrelid = ct.oid ";
3655: } else {
3656: from += ", pg_catalog.pg_index i ";
3657: }
3658: if (schema != null && !"".equals(schema)) {
3659: where += " AND n.nspname = '" + escapeQuotes(schema)
3660: + "' ";
3661: }
3662: } else {
3663: select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, ";
3664: from = " FROM pg_class ct, pg_class ci, pg_attribute a, pg_am am, pg_attribute ai, pg_index i LEFT JOIN pg_proc ip ON (i.indproc = ip.oid) ";
3665: where = " AND ai.attnum = i.indkey[0] AND ai.attrelid = ct.oid ";
3666: }
3667:
3668: String sql = select
3669: + " ct.relname AS TABLE_NAME, NOT i.indisunique AS NON_UNIQUE, NULL AS INDEX_QUALIFIER, ci.relname AS INDEX_NAME, "
3670: + " CASE i.indisclustered " + " WHEN true THEN "
3671: + java.sql.DatabaseMetaData.tableIndexClustered
3672: + " ELSE CASE am.amname " + " WHEN 'hash' THEN "
3673: + java.sql.DatabaseMetaData.tableIndexHashed + " ELSE "
3674: + java.sql.DatabaseMetaData.tableIndexOther + " END "
3675: + " END AS TYPE, " + " a.attnum AS ORDINAL_POSITION, ";
3676:
3677: if (connection.haveMinimumServerVersion("7.4")) {
3678: sql += " CASE WHEN i.indexprs IS NULL THEN a.attname ELSE pg_get_indexdef(ci.oid,a.attnum,false) END AS COLUMN_NAME, ";
3679: } else {
3680: sql += " CASE i.indproc WHEN 0 THEN a.attname ELSE ip.proname || '(' || ai.attname || ')' END AS COLUMN_NAME, ";
3681: }
3682:
3683: sql += " NULL AS ASC_OR_DESC, "
3684: + " ci.reltuples AS CARDINALITY, "
3685: + " ci.relpages AS PAGES, "
3686: + " NULL AS FILTER_CONDITION "
3687: + from
3688: + " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND ci.relam=am.oid "
3689: + where + " AND ct.relname = '"
3690: + escapeQuotes(tableName) + "' ";
3691:
3692: if (unique) {
3693: sql += " AND i.indisunique ";
3694: }
3695: sql += " ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION ";
3696: return createMetaDataStatement().executeQuery(sql);
3697: }
3698:
3699: /**
3700: * Tokenize based on words not on single characters.
3701: */
3702: private static Vector tokenize(String input, String delimiter) {
3703: Vector result = new Vector();
3704: int start = 0;
3705: int end = input.length();
3706: int delimiterSize = delimiter.length();
3707:
3708: while (start < end) {
3709: int delimiterIndex = input.indexOf(delimiter, start);
3710: if (delimiterIndex < 0) {
3711: result.addElement(input.substring(start));
3712: break;
3713: } else {
3714: String token = input.substring(start, delimiterIndex);
3715: result.addElement(token);
3716: start = delimiterIndex + delimiterSize;
3717: }
3718: }
3719: return result;
3720: }
3721:
3722: // ** JDBC 2 Extensions **
3723:
3724: /*
3725: * Does the database support the given result set type?
3726: *
3727: * @param type - defined in java.sql.ResultSet
3728: * @return true if so; false otherwise
3729: * @exception SQLException - if a database access error occurs
3730: */
3731: public boolean supportsResultSetType(int type) throws SQLException {
3732: // The only type we don't support
3733: return type != java.sql.ResultSet.TYPE_SCROLL_SENSITIVE;
3734: }
3735:
3736: /*
3737: * Does the database support the concurrency type in combination
3738: * with the given result set type?
3739: *
3740: * @param type - defined in java.sql.ResultSet
3741: * @param concurrency - type defined in java.sql.ResultSet
3742: * @return true if so; false otherwise
3743: * @exception SQLException - if a database access error occurs
3744: */
3745: public boolean supportsResultSetConcurrency(int type,
3746: int concurrency) throws SQLException {
3747: // These combinations are not supported!
3748: if (type == java.sql.ResultSet.TYPE_SCROLL_SENSITIVE)
3749: return false;
3750:
3751: // We do support Updateable ResultSets
3752: if (concurrency == java.sql.ResultSet.CONCUR_UPDATABLE)
3753: return true;
3754:
3755: // Everything else we do
3756: return true;
3757: }
3758:
3759: /* lots of unsupported stuff... */
3760: public boolean ownUpdatesAreVisible(int type) throws SQLException {
3761: return true;
3762: }
3763:
3764: public boolean ownDeletesAreVisible(int type) throws SQLException {
3765: return true;
3766: }
3767:
3768: public boolean ownInsertsAreVisible(int type) throws SQLException {
3769: // indicates that
3770: return true;
3771: }
3772:
3773: public boolean othersUpdatesAreVisible(int type)
3774: throws SQLException {
3775: return false;
3776: }
3777:
3778: public boolean othersDeletesAreVisible(int i) throws SQLException {
3779: return false;
3780: }
3781:
3782: public boolean othersInsertsAreVisible(int type)
3783: throws SQLException {
3784: return false;
3785: }
3786:
3787: public boolean updatesAreDetected(int type) throws SQLException {
3788: return false;
3789: }
3790:
3791: public boolean deletesAreDetected(int i) throws SQLException {
3792: return false;
3793: }
3794:
3795: public boolean insertsAreDetected(int type) throws SQLException {
3796: return false;
3797: }
3798:
3799: /*
3800: * Indicates whether the driver supports batch updates.
3801: */
3802: public boolean supportsBatchUpdates() throws SQLException {
3803: return true;
3804: }
3805:
3806: /**
3807: *
3808: * @param catalog String
3809: * @param schemaPattern String
3810: * @param typeNamePattern String
3811: * @param types int[]
3812: * @throws SQLException
3813: * @return ResultSet
3814: */
3815: public java.sql.ResultSet getUDTs(String catalog,
3816: String schemaPattern, String typeNamePattern, int[] types)
3817: throws SQLException {
3818: String sql = "select "
3819: + "null as type_cat, n.nspname as type_schem, t.typname as type_name, null as class_name, "
3820: + "CASE WHEN t.typtype='c' then "
3821: + java.sql.Types.STRUCT
3822: + " else "
3823: + java.sql.Types.DISTINCT
3824: + " end as data_type, pg_catalog.obj_description(t.oid, 'pg_type') "
3825: + "as remarks, CASE WHEN t.typtype = 'd' then (select CASE";
3826:
3827: for (Iterator i = connection.getPGTypeNamesWithSQLTypes(); i
3828: .hasNext();) {
3829: String pgType = (String) i.next();
3830: int sqlType = connection.getSQLType(pgType);
3831: sql += " when typname = '" + escapeQuotes(pgType)
3832: + "' then " + sqlType;
3833: }
3834:
3835: sql += " else "
3836: + java.sql.Types.OTHER
3837: + " end from pg_type where oid=t.typbasetype) "
3838: + "else null end as base_type "
3839: + "from pg_catalog.pg_type t, pg_catalog.pg_namespace n where t.typnamespace = n.oid and n.nspname != 'pg_catalog' and n.nspname != 'pg_toast'";
3840:
3841: String toAdd = "";
3842: if (types != null) {
3843: toAdd += " and (false ";
3844: for (int i = 0; i < types.length; i++) {
3845: switch (types[i]) {
3846: case java.sql.Types.STRUCT:
3847: toAdd += " or t.typtype = 'c'";
3848: break;
3849: case java.sql.Types.DISTINCT:
3850: toAdd += " or t.typtype = 'd'";
3851: break;
3852: }
3853: }
3854: toAdd += " ) ";
3855: } else {
3856: toAdd += " and t.typtype IN ('c','d') ";
3857: }
3858: // spec says that if typeNamePattern is a fully qualified name
3859: // then the schema and catalog are ignored
3860:
3861: if (typeNamePattern != null) {
3862: // search for qualifier
3863: int firstQualifier = typeNamePattern.indexOf('.');
3864: int secondQualifier = typeNamePattern.lastIndexOf('.');
3865:
3866: if (firstQualifier != -1) // if one of them is -1 they both will be
3867: {
3868: if (firstQualifier != secondQualifier) {
3869: // we have a catalog.schema.typename, ignore catalog
3870: schemaPattern = typeNamePattern.substring(
3871: firstQualifier + 1, secondQualifier);
3872: } else {
3873: // we just have a schema.typename
3874: schemaPattern = typeNamePattern.substring(0,
3875: firstQualifier);
3876: }
3877: // strip out just the typeName
3878: typeNamePattern = typeNamePattern
3879: .substring(secondQualifier + 1);
3880: }
3881: toAdd += " and t.typname like '"
3882: + escapeQuotes(typeNamePattern) + "'";
3883: }
3884:
3885: // schemaPattern may have been modified above
3886: if (schemaPattern != null) {
3887: toAdd += " and n.nspname like '"
3888: + escapeQuotes(schemaPattern) + "'";
3889: }
3890: sql += toAdd;
3891: sql += " order by data_type, type_schem, type_name";
3892: java.sql.ResultSet rs = createMetaDataStatement().executeQuery(
3893: sql);
3894:
3895: return rs;
3896: }
3897:
3898: /*
3899: * Retrieves the connection that produced this metadata object.
3900: *
3901: * @return the connection that produced this metadata object
3902: */
3903: public java.sql.Connection getConnection() throws SQLException {
3904: return (java.sql.Connection) connection;
3905: }
3906:
3907: /* I don't find these in the spec!?! */
3908:
3909: public boolean rowChangesAreDetected(int type) throws SQLException {
3910: return false;
3911: }
3912:
3913: public boolean rowChangesAreVisible(int type) throws SQLException {
3914: return false;
3915: }
3916:
3917: protected java.sql.Statement createMetaDataStatement()
3918: throws SQLException {
3919: return ((AbstractJdbc2Connection) connection).createStatement(
3920: java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
3921: java.sql.ResultSet.CONCUR_READ_ONLY);
3922: }
3923:
3924: }
|