package org.postgresql.jdbc2; import java.sql.*; import java.util.*; import org.postgresql.Driver; import org.postgresql.Field; import org.postgresql.util.PSQLException; public abstract class AbstractJdbc2DatabaseMetaData extends org.postgresql.jdbc1.AbstractJdbc1DatabaseMetaData { public AbstractJdbc2DatabaseMetaData(AbstractJdbc2Connection conn) { super(conn); } /* * Can all the procedures returned by getProcedures be called * by the current user? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean allProceduresAreCallable() throws SQLException { if (Driver.logDebug) Driver.debug("allProceduresAreCallable"); return true; // For now... } /* * Can all the tables returned by getTable be SELECTed by * the current user? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean allTablesAreSelectable() throws SQLException { if (Driver.logDebug) Driver.debug("allTablesAreSelectable"); return true; // For now... } /* * What is the URL for this database? * * @return the url or null if it cannott be generated * @exception SQLException if a database access error occurs */ public String getURL() throws SQLException { String url = connection.getURL(); if (Driver.logDebug) Driver.debug("getURL " + url); return url; } /* * What is our user name as known to the database? * * @return our database user name * @exception SQLException if a database access error occurs */ public String getUserName() throws SQLException { String userName = connection.getUserName(); if (Driver.logDebug) Driver.debug("getUserName " + userName); return userName; } /* * Is the database in read-only mode? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean isReadOnly() throws SQLException { boolean isReadOnly = connection.isReadOnly(); if (Driver.logDebug) Driver.debug("isReadOnly " + isReadOnly); return isReadOnly; } /* * Are NULL values sorted high? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean nullsAreSortedHigh() throws SQLException { boolean nullSortedHigh = connection.haveMinimumServerVersion("7.2"); if (Driver.logDebug) Driver.debug("nullsAreSortedHigh " + nullSortedHigh); return nullSortedHigh; } /* * Are NULL values sorted low? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean nullsAreSortedLow() throws SQLException { if (Driver.logDebug) Driver.debug("nullsAreSortedLow false"); return false; } /* * Are NULL values sorted at the start regardless of sort order? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean nullsAreSortedAtStart() throws SQLException { if (Driver.logDebug) Driver.debug("nullsAreSortedAtStart false"); return false; } /* * Are NULL values sorted at the end regardless of sort order? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean nullsAreSortedAtEnd() throws SQLException { boolean nullsAreSortedAtEnd = ! connection.haveMinimumServerVersion("7.2"); if (Driver.logDebug) Driver.debug("nullsAreSortedAtEnd " + nullsAreSortedAtEnd); return nullsAreSortedAtEnd; } /* * What is the name of this database product - we hope that it is * PostgreSQL, so we return that explicitly. * * @return the database product name * @exception SQLException if a database access error occurs */ public String getDatabaseProductName() throws SQLException { if (Driver.logDebug) Driver.debug("getDatabaseProductName PostgresSQL"); return "PostgreSQL"; } /* * What is the version of this database product. * * @return the database version * @exception SQLException if a database access error occurs */ public String getDatabaseProductVersion() throws SQLException { String versionNumber = connection.getDBVersionNumber(); if (Driver.logDebug) Driver.debug("getDatabaseProductVersion " + versionNumber); return versionNumber; } /* * What is the name of this JDBC driver? If we don't know this * we are doing something wrong! * * @return the JDBC driver name * @exception SQLException why? */ public String getDriverName() throws SQLException { String driverName = "PostgreSQL Native Driver"; if (Driver.logDebug) Driver.debug("getDriverName" + driverName); return driverName; } /* * What is the version string of this JDBC driver? Again, this is * static. * * @return the JDBC driver name. * @exception SQLException why? */ public String getDriverVersion() throws SQLException { String driverVersion = connection.this_driver.getVersion(); if (Driver.logDebug) Driver.debug("getDriverVersion " + driverVersion); return driverVersion; } /* * What is this JDBC driver's major version number? * * @return the JDBC driver major version */ public int getDriverMajorVersion() { int majorVersion = connection.this_driver.getMajorVersion(); if (Driver.logDebug) Driver.debug("getMajorVersion " + majorVersion); return majorVersion; } /* * What is this JDBC driver's minor version number? * * @return the JDBC driver minor version */ public int getDriverMinorVersion() { int minorVersion = connection.this_driver.getMinorVersion(); if (Driver.logDebug) Driver.debug("getMinorVersion " + minorVersion); return minorVersion; } /* * Does the database store tables in a local file? No - it * stores them in a file on the server. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean usesLocalFiles() throws SQLException { if (Driver.logDebug) Driver.debug("usesLocalFiles " + false); return false; } /* * Does the database use a file for each table? Well, not really, * since it doesnt use local files. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean usesLocalFilePerTable() throws SQLException { if (Driver.logDebug) Driver.debug("usesLocalFilePerTable " + false); return false; } /* * Does the database treat mixed case unquoted SQL identifiers * as case sensitive and as a result store them in mixed case? * A JDBC-Compliant driver will always return false. * *
Predicament - what do they mean by "SQL identifiers" - if it * means the names of the tables and columns, then the answers * given below are correct - otherwise I don't know. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsMixedCaseIdentifiers() throws SQLException { if (Driver.logDebug) Driver.debug("supportsMixedCaseIdentifiers " + false); return false; } /* * Does the database treat mixed case unquoted SQL identifiers as * case insensitive and store them in upper case? * * @return true if so */ public boolean storesUpperCaseIdentifiers() throws SQLException { if (Driver.logDebug) Driver.debug("storesUpperCaseIdentifiers " + false); return false; } /* * Does the database treat mixed case unquoted SQL identifiers as * case insensitive and store them in lower case? * * @return true if so */ public boolean storesLowerCaseIdentifiers() throws SQLException { if (Driver.logDebug) Driver.debug("storesLowerCaseIdentifiers " + true); return true; } /* * Does the database treat mixed case unquoted SQL identifiers as * case insensitive and store them in mixed case? * * @return true if so */ public boolean storesMixedCaseIdentifiers() throws SQLException { if (Driver.logDebug) Driver.debug("storesMixedCaseIdentifiers " + false); return false; } /* * Does the database treat mixed case quoted SQL identifiers as * case sensitive and as a result store them in mixed case? A * JDBC compliant driver will always return true. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsMixedCaseQuotedIdentifiers() throws SQLException { if (Driver.logDebug) Driver.debug("supportsMixedCaseQuotedIdentifiers " + true); return true; } /* * Does the database treat mixed case quoted SQL identifiers as * case insensitive and store them in upper case? * * @return true if so */ public boolean storesUpperCaseQuotedIdentifiers() throws SQLException { if (Driver.logDebug) Driver.debug("storesUpperCaseQuotedIdentifiers " + false); return false; } /* * Does the database treat mixed case quoted SQL identifiers as case * insensitive and store them in lower case? * * @return true if so */ public boolean storesLowerCaseQuotedIdentifiers() throws SQLException { if (Driver.logDebug) Driver.debug("storesLowerCaseQuotedIdentifiers " + false); return false; } /* * Does the database treat mixed case quoted SQL identifiers as case * insensitive and store them in mixed case? * * @return true if so */ public boolean storesMixedCaseQuotedIdentifiers() throws SQLException { if (Driver.logDebug) Driver.debug("storesMixedCaseQuotedIdentifiers " + false); return false; } /* * What is the string used to quote SQL identifiers? This returns * a space if identifier quoting isn't supported. A JDBC Compliant * driver will always use a double quote character. * * @return the quoting string * @exception SQLException if a database access error occurs */ public String getIdentifierQuoteString() throws SQLException { if (Driver.logDebug) Driver.debug("getIdentifierQuoteString \"" ); return "\""; } /* * Get a comma separated list of all a database's SQL keywords that * are NOT also SQL92 keywords. * *
Within PostgreSQL, the keywords are found in * src/backend/parser/keywords.c * *
For SQL Keywords, I took the list provided at * * http://web.dementia.org/~shadow/sql/sql3bnf.sep93.txt * which is for SQL3, not SQL-92, but it is close enough for * this purpose. * * @return a comma separated list of keywords we use * @exception SQLException if a database access error occurs */ public String getSQLKeywords() throws SQLException { if (Driver.logDebug) Driver.debug("getSQLKeyWords"); return "abort,acl,add,aggregate,append,archive,arch_store,backward,binary,change,cluster,copy,database,delimiter,delimiters,do,extend,explain,forward,heavy,index,inherits,isnull,light,listen,load,merge,nothing,notify,notnull,oids,purge,rename,replace,retrieve,returns,rule,recipe,setof,stdin,stdout,store,vacuum,verbose,version"; } public String getNumericFunctions() throws SQLException { // XXX-Not Implemented if (Driver.logDebug) Driver.debug("getNumericFunctions"); return ""; } public String getStringFunctions() throws SQLException { // XXX-Not Implemented if (Driver.logDebug) Driver.debug("getStringFunctions"); return ""; } public String getSystemFunctions() throws SQLException { // XXX-Not Implemented if (Driver.logDebug) Driver.debug("getSystemFunctions"); return ""; } public String getTimeDateFunctions() throws SQLException { // XXX-Not Implemented if (Driver.logDebug) Driver.debug("getTimeDateFunctions"); return ""; } /* * This is the string that can be used to escape '_' and '%' in * a search string pattern style catalog search parameters * * @return the string used to escape wildcard characters * @exception SQLException if a database access error occurs */ public String getSearchStringEscape() throws SQLException { if (Driver.logDebug) Driver.debug("getSearchStringEscape"); return "\\"; } /* * Get all the "extra" characters that can be used in unquoted * identifier names (those beyond a-zA-Z0-9 and _) * *
From the file src/backend/parser/scan.l, an identifier is * {letter}{letter_or_digit} which makes it just those listed * above. * * @return a string containing the extra characters * @exception SQLException if a database access error occurs */ public String getExtraNameCharacters() throws SQLException { if (Driver.logDebug) Driver.debug("getExtraNameCharacters"); return ""; } /* * Is "ALTER TABLE" with an add column supported? * Yes for PostgreSQL 6.1 * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsAlterTableWithAddColumn() throws SQLException { if (Driver.logDebug) Driver.debug("supportsAlterTableWithAddColumn " + true); return true; } /* * Is "ALTER TABLE" with a drop column supported? * Peter 10/10/2000 This was set to true, but 7.1devel doesn't support it! * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsAlterTableWithDropColumn() throws SQLException { if (Driver.logDebug) Driver.debug("supportsAlterTableWithDropColumn " + false); return false; } /* * Is column aliasing supported? * *
If so, the SQL AS clause can be used to provide names for * computed columns or to provide alias names for columns as * required. A JDBC Compliant driver always returns true. * *
e.g.
*
*
* select count(C) as C_COUNT from T group by C; * *
This changed from false to true in v6.2 of the driver, as this * support was added to the backend. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsNonNullableColumns() throws SQLException { if (Driver.logDebug) Driver.debug("supportsNonNullableColumns true"); return true; } /* * Does this driver support the minimum ODBC SQL grammar. This * grammar is defined at: * *
http://www.microsoft.com/msdn/sdk/platforms/doc/odbc/src/intropr.htm * *
In Appendix C. From this description, we seem to support the * ODBC minimal (Level 0) grammar. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsMinimumSQLGrammar() throws SQLException { if (Driver.logDebug) Driver.debug("supportsMinimumSQLGrammar TRUE"); return true; } /* * Does this driver support the Core ODBC SQL grammar. We need * SQL-92 conformance for this. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsCoreSQLGrammar() throws SQLException { if (Driver.logDebug) Driver.debug("supportsCoreSQLGrammar FALSE "); return false; } /* * Does this driver support the Extended (Level 2) ODBC SQL * grammar. We don't conform to the Core (Level 1), so we can't * conform to the Extended SQL Grammar. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsExtendedSQLGrammar() throws SQLException { if (Driver.logDebug) Driver.debug("supportsExtendedSQLGrammar FALSE"); return false; } /* * Does this driver support the ANSI-92 entry level SQL grammar? * All JDBC Compliant drivers must return true. We currently * report false until 'schema' support is added. Then this * should be changed to return true, since we will be mostly * compliant (probably more compliant than many other databases) * And since this is a requirement for all JDBC drivers we * need to get to the point where we can return true. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsANSI92EntryLevelSQL() throws SQLException { boolean schemas = connection.haveMinimumServerVersion("7.3"); if (Driver.logDebug) Driver.debug("supportsANSI92EntryLevelSQL " + schemas); return schemas; } /* * Does this driver support the ANSI-92 intermediate level SQL * grammar? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsANSI92IntermediateSQL() throws SQLException { if (Driver.logDebug) Driver.debug("supportsANSI92IntermediateSQL false "); return false; } /* * Does this driver support the ANSI-92 full SQL grammar? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsANSI92FullSQL() throws SQLException { if (Driver.logDebug) Driver.debug("supportsANSI92FullSQL false "); return false; } /* * Is the SQL Integrity Enhancement Facility supported? * I haven't seen this mentioned anywhere, so I guess not * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsIntegrityEnhancementFacility() throws SQLException { if (Driver.logDebug) Driver.debug("supportsIntegrityEnhancementFacility false "); return false; } /* * Is some form of outer join supported? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsOuterJoins() throws SQLException { boolean supportsOuterJoins = connection.haveMinimumServerVersion("7.1"); if (Driver.logDebug) Driver.debug("supportsOuterJoins " + supportsOuterJoins); return supportsOuterJoins; } /* * Are full nexted outer joins supported? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsFullOuterJoins() throws SQLException { boolean supportsFullOuterJoins = connection.haveMinimumServerVersion("7.1"); if (Driver.logDebug) Driver.debug("supportsFullOuterJoins " + supportsFullOuterJoins); return supportsFullOuterJoins; } /* * Is there limited support for outer joins? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsLimitedOuterJoins() throws SQLException { boolean supportsLimitedOuterJoins = connection.haveMinimumServerVersion("7.1"); if (Driver.logDebug) Driver.debug("supportsFullOuterJoins " + supportsLimitedOuterJoins); return supportsLimitedOuterJoins; } /* * What is the database vendor's preferred term for "schema"? * PostgreSQL doesn't have schemas, but when it does, we'll use the * term "schema". * * @return the vendor term * @exception SQLException if a database access error occurs */ public String getSchemaTerm() throws SQLException { if (Driver.logDebug) Driver.debug("getSchemaTerm schema"); return "schema"; } /* * What is the database vendor's preferred term for "procedure"? * Traditionally, "function" has been used. * * @return the vendor term * @exception SQLException if a database access error occurs */ public String getProcedureTerm() throws SQLException { if (Driver.logDebug) Driver.debug("getProcedureTerm function "); return "function"; } /* * What is the database vendor's preferred term for "catalog"? * * @return the vendor term * @exception SQLException if a database access error occurs */ public String getCatalogTerm() throws SQLException { if (Driver.logDebug) Driver.debug("getCatalogTerm database "); return "database"; } /* * Does a catalog appear at the start of a qualified table name? * (Otherwise it appears at the end). * * @return true if so * @exception SQLException if a database access error occurs */ public boolean isCatalogAtStart() throws SQLException { // return true here; we return false for every other catalog function // so it won't matter what we return here D.C. if (Driver.logDebug) Driver.debug("isCatalogAtStart not implemented"); return true; } /* * What is the Catalog separator. * * @return the catalog separator string * @exception SQLException if a database access error occurs */ public String getCatalogSeparator() throws SQLException { // Give them something to work with here // everything else returns false so it won't matter what we return here D.C. if (Driver.logDebug) Driver.debug("getCatalogSeparator not implemented "); return "."; } /* * Can a schema name be used in a data manipulation statement? Nope. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsSchemasInDataManipulation() throws SQLException { if (Driver.logDebug) Driver.debug("supportsSchemasInDataManipulation false"); return false; } /* * Can a schema name be used in a procedure call statement? Nope. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsSchemasInProcedureCalls() throws SQLException { if (Driver.logDebug) Driver.debug("supportsSchemasInProcedureCalls false"); return false; } /* * Can a schema be used in a table definition statement? Nope. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsSchemasInTableDefinitions() throws SQLException { boolean schemas = connection.haveMinimumServerVersion("7.3"); if (Driver.logDebug) Driver.debug("supportsSchemasInTableDefinitions " + schemas); return schemas; } /* * Can a schema name be used in an index definition statement? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsSchemasInIndexDefinitions() throws SQLException { if (Driver.logDebug) Driver.debug("supportsSchemasInIndexDefinitions false"); return false; } /* * Can a schema name be used in a privilege definition statement? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsSchemasInPrivilegeDefinitions() throws SQLException { if (Driver.logDebug) Driver.debug("supportsSchemasInPrivilegeDefinitions false"); return false; } /* * Can a catalog name be used in a data manipulation statement? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsCatalogsInDataManipulation() throws SQLException { if (Driver.logDebug) Driver.debug("supportsCatalogsInDataManipulation false"); return false; } /* * Can a catalog name be used in a procedure call statement? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsCatalogsInProcedureCalls() throws SQLException { if (Driver.logDebug) Driver.debug("supportsCatalogsInDataManipulation false"); return false; } /* * Can a catalog name be used in a table definition statement? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsCatalogsInTableDefinitions() throws SQLException { if (Driver.logDebug) Driver.debug("supportsCatalogsInTableDefinitions false"); return false; } /* * Can a catalog name be used in an index definition? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsCatalogsInIndexDefinitions() throws SQLException { if (Driver.logDebug) Driver.debug("supportsCatalogsInIndexDefinitions false"); return false; } /* * Can a catalog name be used in a privilege definition statement? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsCatalogsInPrivilegeDefinitions() throws SQLException { if (Driver.logDebug) Driver.debug("supportsCatalogsInPrivilegeDefinitions false"); return false; } /* * We support cursors for gets only it seems. I dont see a method * to get a positioned delete. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsPositionedDelete() throws SQLException { if (Driver.logDebug) Driver.debug("supportsPositionedDelete false"); return false; // For now... } /* * Is positioned UPDATE supported? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsPositionedUpdate() throws SQLException { if (Driver.logDebug) Driver.debug("supportsPositionedUpdate false"); return false; // For now... } /* * Is SELECT for UPDATE supported? * * @return true if so; false otherwise * @exception SQLException - if a database access error occurs */ public boolean supportsSelectForUpdate() throws SQLException { return connection.haveMinimumServerVersion("6.5"); } /* * Are stored procedure calls using the stored procedure escape * syntax supported? * * @return true if so; false otherwise * @exception SQLException - if a database access error occurs */ public boolean supportsStoredProcedures() throws SQLException { return false; } /* * Are subqueries in comparison expressions supported? A JDBC * Compliant driver always returns true. * * @return true if so; false otherwise * @exception SQLException - if a database access error occurs */ public boolean supportsSubqueriesInComparisons() throws SQLException { return true; } /* * Are subqueries in 'exists' expressions supported? A JDBC * Compliant driver always returns true. * * @return true if so; false otherwise * @exception SQLException - if a database access error occurs */ public boolean supportsSubqueriesInExists() throws SQLException { return true; } /* * Are subqueries in 'in' statements supported? A JDBC * Compliant driver always returns true. * * @return true if so; false otherwise * @exception SQLException - if a database access error occurs */ public boolean supportsSubqueriesInIns() throws SQLException { return true; } /* * Are subqueries in quantified expressions supported? A JDBC * Compliant driver always returns true. * * (No idea what this is, but we support a good deal of * subquerying.) * * @return true if so; false otherwise * @exception SQLException - if a database access error occurs */ public boolean supportsSubqueriesInQuantifieds() throws SQLException { return true; } /* * Are correlated subqueries supported? A JDBC Compliant driver * always returns true. * * (a.k.a. subselect in from?) * * @return true if so; false otherwise * @exception SQLException - if a database access error occurs */ public boolean supportsCorrelatedSubqueries() throws SQLException { return connection.haveMinimumServerVersion("7.1"); } /* * Is SQL UNION supported? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsUnion() throws SQLException { return true; // since 6.3 } /* * Is SQL UNION ALL supported? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsUnionAll() throws SQLException { return connection.haveMinimumServerVersion("7.1"); } /* * In PostgreSQL, Cursors are only open within transactions. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsOpenCursorsAcrossCommit() throws SQLException { return false; } /* * Do we support open cursors across multiple transactions? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsOpenCursorsAcrossRollback() throws SQLException { return false; } /* * Can statements remain open across commits? They may, but * this driver cannot guarentee that. In further reflection. * we are talking a Statement object here, so the answer is * yes, since the Statement is only a vehicle to ExecSQL() * * @return true if they always remain open; false otherwise * @exception SQLException if a database access error occurs */ public boolean supportsOpenStatementsAcrossCommit() throws SQLException { return true; } /* * Can statements remain open across rollbacks? They may, but * this driver cannot guarentee that. In further contemplation, * we are talking a Statement object here, so the answer is yes, * since the Statement is only a vehicle to ExecSQL() in Connection * * @return true if they always remain open; false otherwise * @exception SQLException if a database access error occurs */ public boolean supportsOpenStatementsAcrossRollback() throws SQLException { return true; } /* * How many hex characters can you have in an inline binary literal * * @return the max literal length * @exception SQLException if a database access error occurs */ public int getMaxBinaryLiteralLength() throws SQLException { return 0; // no limit } /* * What is the maximum length for a character literal * I suppose it is 8190 (8192 - 2 for the quotes) * * @return the max literal length * @exception SQLException if a database access error occurs */ public int getMaxCharLiteralLength() throws SQLException { return 0; // no limit } /* * Whats the limit on column name length. The description of * pg_class would say '32' (length of pg_class.relname) - we * should probably do a query for this....but.... * * @return the maximum column name length * @exception SQLException if a database access error occurs */ public int getMaxColumnNameLength() throws SQLException { return 32; } /* * What is the maximum number of columns in a "GROUP BY" clause? * * @return the max number of columns * @exception SQLException if a database access error occurs */ public int getMaxColumnsInGroupBy() throws SQLException { return 0; // no limit } /* * What's the maximum number of columns allowed in an index? * 6.0 only allowed one column, but 6.1 introduced multi-column * indices, so, theoretically, its all of them. * * @return max number of columns * @exception SQLException if a database access error occurs */ public int getMaxColumnsInIndex() throws SQLException { return getMaxColumnsInTable(); } /* * What's the maximum number of columns in an "ORDER BY clause? * * @return the max columns * @exception SQLException if a database access error occurs */ public int getMaxColumnsInOrderBy() throws SQLException { return 0; // no limit } /* * What is the maximum number of columns in a "SELECT" list? * * @return the max columns * @exception SQLException if a database access error occurs */ public int getMaxColumnsInSelect() throws SQLException { return 0; // no limit } /* * What is the maximum number of columns in a table? From the * CREATE TABLE reference page... * *
"The new class is created as a heap with no initial data. A * class can have no more than 1600 attributes (realistically, * this is limited by the fact that tuple sizes must be less than * 8192 bytes)..." * * @return the max columns * @exception SQLException if a database access error occurs */ public int getMaxColumnsInTable() throws SQLException { return 1600; } /* * How many active connection can we have at a time to this * database? Well, since it depends on postmaster, which just * does a listen() followed by an accept() and fork(), its * basically very high. Unless the system runs out of processes, * it can be 65535 (the number of aux. ports on a TCP/IP system). * I will return 8192 since that is what even the largest system * can realistically handle, * * @return the maximum number of connections * @exception SQLException if a database access error occurs */ public int getMaxConnections() throws SQLException { return 8192; } /* * What is the maximum cursor name length (the same as all * the other F***** identifiers!) * * @return max cursor name length in bytes * @exception SQLException if a database access error occurs */ public int getMaxCursorNameLength() throws SQLException { return 32; } /* * Retrieves the maximum number of bytes for an index, including all * of the parts of the index. * * @return max index length in bytes, which includes the composite * of all the constituent parts of the index; a result of zero means * that there is no limit or the limit is not known * @exception SQLException if a database access error occurs */ public int getMaxIndexLength() throws SQLException { return 0; // no limit (larger than an int anyway) } public int getMaxSchemaNameLength() throws SQLException { // XXX-Not Implemented return 0; } /* * What is the maximum length of a procedure name? * (length of pg_proc.proname used) - again, I really * should do a query here to get it. * * @return the max name length in bytes * @exception SQLException if a database access error occurs */ public int getMaxProcedureNameLength() throws SQLException { return 32; } public int getMaxCatalogNameLength() throws SQLException { // XXX-Not Implemented return 0; } /* * What is the maximum length of a single row? * * @return max row size in bytes * @exception SQLException if a database access error occurs */ public int getMaxRowSize() throws SQLException { if (connection.haveMinimumServerVersion("7.1")) return 1073741824; // 1 GB else return 8192; // XXX could be altered } /* * Did getMaxRowSize() include LONGVARCHAR and LONGVARBINARY * blobs? We don't handle blobs yet * * @return true if so * @exception SQLException if a database access error occurs */ public boolean doesMaxRowSizeIncludeBlobs() throws SQLException { return false; } /* * What is the maximum length of a SQL statement? * * @return max length in bytes * @exception SQLException if a database access error occurs */ public int getMaxStatementLength() throws SQLException { if (connection.haveMinimumServerVersion("7.0")) return 0; // actually whatever fits in size_t else return 16384; } /* * How many active statements can we have open at one time to * this database? Basically, since each Statement downloads * the results as the query is executed, we can have many. However, * we can only really have one statement per connection going * at once (since they are executed serially) - so we return * one. * * @return the maximum * @exception SQLException if a database access error occurs */ public int getMaxStatements() throws SQLException { return 1; } /* * What is the maximum length of a table name? This was found * from pg_class.relname length * * @return max name length in bytes * @exception SQLException if a database access error occurs */ public int getMaxTableNameLength() throws SQLException { return 32; } /* * What is the maximum number of tables that can be specified * in a SELECT? * * @return the maximum * @exception SQLException if a database access error occurs */ public int getMaxTablesInSelect() throws SQLException { return 0; // no limit } /* * What is the maximum length of a user name? Well, we generally * use UNIX like user names in PostgreSQL, so I think this would * be 8. However, showing the schema for pg_user shows a length * for username of 32. * * @return the max name length in bytes * @exception SQLException if a database access error occurs */ public int getMaxUserNameLength() throws SQLException { return 32; } /* * What is the database's default transaction isolation level? We * do not support this, so all transactions are SERIALIZABLE. * * @return the default isolation level * @exception SQLException if a database access error occurs * @see Connection */ public int getDefaultTransactionIsolation() throws SQLException { return Connection.TRANSACTION_READ_COMMITTED; } /* * Are transactions supported? If not, commit and rollback are noops * and the isolation level is TRANSACTION_NONE. We do support * transactions. * * @return true if transactions are supported * @exception SQLException if a database access error occurs */ public boolean supportsTransactions() throws SQLException { return true; } /* * Does the database support the given transaction isolation level? * We only support TRANSACTION_SERIALIZABLE and TRANSACTION_READ_COMMITTED * * @param level the values are defined in java.sql.Connection * @return true if so * @exception SQLException if a database access error occurs * @see Connection */ public boolean supportsTransactionIsolationLevel(int level) throws SQLException { if (level == Connection.TRANSACTION_SERIALIZABLE || level == Connection.TRANSACTION_READ_COMMITTED) return true; else return false; } /* * Are both data definition and data manipulation transactions * supported? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsDataDefinitionAndDataManipulationTransactions() throws SQLException { return true; } /* * Are only data manipulation statements withing a transaction * supported? * * @return true if so * @exception SQLException if a database access error occurs */ public boolean supportsDataManipulationTransactionsOnly() throws SQLException { return false; } /* * Does a data definition statement within a transaction force * the transaction to commit? I think this means something like: * *
* CREATE TABLE T (A INT); * INSERT INTO T (A) VALUES (2); * BEGIN; * UPDATE T SET A = A + 1; * CREATE TABLE X (A INT); * SELECT A FROM T INTO X; * COMMIT; *
* * does the CREATE TABLE call cause a commit? The answer is no. * * @return true if so * @exception SQLException if a database access error occurs */ public boolean dataDefinitionCausesTransactionCommit() throws SQLException { return false; } /* * Is a data definition statement within a transaction ignored? * It seems to be (from experiment in previous method) * * @return true if so * @exception SQLException if a database access error occurs */ public boolean dataDefinitionIgnoredInTransactions() throws SQLException { return true; } /* * Get a description of stored procedures available in a catalog * *
Only procedure descriptions matching the schema and procedure * name criteria are returned. They are ordered by PROCEDURE_SCHEM * and PROCEDURE_NAME * *
Each procedure description has the following columns: *
Only descriptions matching the schema, procedure and parameter * name criteria are returned. They are ordered by PROCEDURE_SCHEM * and PROCEDURE_NAME. Within this, the return value, if any, is * first. Next are the parameter descriptions in call order. The * column descriptions follow in column number order. * *
Each row in the ResultSet is a parameter description or column * description with the following fields: *
Only table descriptions matching the catalog, schema, table * name and type criteria are returned. They are ordered by * TABLE_TYPE, TABLE_SCHEM and TABLE_NAME. * *
Each table description has the following columns: * *
The valid values for the types parameter are:
* "TABLE", "INDEX", "SEQUENCE", "SYSTEM TABLE" and "SYSTEM INDEX"
*
* @param catalog a catalog name; For org.postgresql, this is ignored, and
* should be set to null
* @param schemaPattern a schema name pattern; For org.postgresql, this is ignored, and
* should be set to null
* @param tableNamePattern a table name pattern. For all tables this should be "%"
* @param types a list of table types to include; null returns
* all types
* @return each row is a table description
* @exception SQLException if a database-access error occurs.
*/
public java.sql.ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[]) throws SQLException
{
// Handle default value for types
if (types == null)
types = defaultTableTypes;
if (tableNamePattern == null)
tableNamePattern = "%";
// the field descriptors for the new ResultSet
Field f[] = new Field[5];
java.sql.ResultSet r; // ResultSet for the SQL query that we need to do
Vector v = new Vector(); // The new ResultSet tuple stuff
f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
f[3] = new Field(connection, "TABLE_TYPE", iVarcharOid, 32);
f[4] = new Field(connection, "REMARKS", iVarcharOid, 32);
// Now form the query
StringBuffer sql = new StringBuffer("select relname,oid,relkind from pg_class where (");
boolean notFirst = false;
for (int i = 0;i < types.length;i++)
{
for (int j = 0;j < getTableTypes.length;j++)
if (getTableTypes[j][0].equals(types[i]))
{
if (notFirst)
sql.append(" or ");
sql.append(getTableTypes[j][1]);
notFirst = true;
}
}
// Added by Stefan Andreasen The schema column is:
* The catalog column is:
* The table type is:
* Only column descriptions matching the catalog, schema, table
* and column name criteria are returned. They are ordered by
* TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
*
* Each column description has the following columns:
* Only privileges matching the column name criteria are
* returned. They are ordered by COLUMN_NAME and PRIVILEGE.
*
* Each privilige description has the following columns:
* Only privileges matching the schema and table name
* criteria are returned. They are ordered by TABLE_SCHEM,
* TABLE_NAME, and PRIVILEGE.
*
* Each privilige description has the following columns:
* Each column description has the following columns:
* Each column description has the following columns:
* Each column description has the following columns:
* Each primary key column description has the following columns:
* Each foreign key column description has the following columns:
* Each foreign key column description has the following columns:
* Each type description has the following columns:
* Each index column description has the following columns:
*
*
*
* @return ResultSet each row has a single String column that is a
* schema name
*/
public java.sql.ResultSet getSchemas() throws SQLException
{
// We don't use schemas, so we simply return a single schema name "".
//
Field f[] = new Field[1];
Vector v = new Vector();
byte[][] tuple = new byte[1][0];
f[0] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
tuple[0] = "".getBytes();
v.addElement(tuple);
return connection.getResultSet(null, f, v, "OK", 1);
}
/*
* Get the catalog names available in this database. The results
* are ordered by catalog name.
*
*
*
*
* @return ResultSet each row has a single String column that is a
* catalog name
*/
public java.sql.ResultSet getCatalogs() throws SQLException
{
return connection.createStatement().executeQuery("select datname as TABLE_CAT from pg_database;");
}
/*
* Get the table types available in this database. The results
* are ordered by table type.
*
*
*
*
* @return ResultSet each row has a single String column that is a
* table type
*/
public java.sql.ResultSet getTableTypes() throws SQLException
{
Field f[] = new Field[1];
Vector v = new Vector();
f[0] = new Field(connection, new String("TABLE_TYPE"), iVarcharOid, 32);
for (int i = 0;i < getTableTypes.length;i++)
{
byte[][] tuple = new byte[2][0];
tuple[0] = getTableTypes[i][0].getBytes();
v.addElement(tuple);
}
return connection.getResultSet(null, f, v, "OK", 1);
}
/*
* Get a description of table columns available in a catalog.
*
*
*
*
* @param catalog a catalog name; "" retrieves those without a catalog
* @param schemaPattern a schema name pattern; "" retrieves those
* without a schema
* @param tableNamePattern a table name pattern
* @param columnNamePattern a column name pattern
* @return ResultSet each row is a column description
* @see #getSearchStringEscape
*/
public java.sql.ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException
{
Vector v = new Vector(); // The new ResultSet tuple stuff
Field f[] = new Field[18]; // The field descriptors for the new ResultSet
f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
f[3] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
f[4] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
f[5] = new Field(connection, "TYPE_NAME", iVarcharOid, 32);
f[6] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
f[7] = new Field(connection, "BUFFER_LENGTH", iVarcharOid, 32);
f[8] = new Field(connection, "DECIMAL_DIGITS", iInt4Oid, 4);
f[9] = new Field(connection, "NUM_PREC_RADIX", iInt4Oid, 4);
f[10] = new Field(connection, "NULLABLE", iInt4Oid, 4);
f[11] = new Field(connection, "REMARKS", iVarcharOid, 32);
f[12] = new Field(connection, "COLUMN_DEF", iVarcharOid, 32);
f[13] = new Field(connection, "SQL_DATA_TYPE", iInt4Oid, 4);
f[14] = new Field(connection, "SQL_DATETIME_SUB", iInt4Oid, 4);
f[15] = new Field(connection, "CHAR_OCTET_LENGTH", iVarcharOid, 32);
f[16] = new Field(connection, "ORDINAL_POSITION", iInt4Oid, 4);
f[17] = new Field(connection, "IS_NULLABLE", iVarcharOid, 32);
StringBuffer sql = new StringBuffer(512);
/* Build a >= 7.1 SQL statement to list all columns */
sql.append("select " +
(connection.haveMinimumServerVersion("7.2") ? "a.attrelid, " : "a.oid, ") +
" c.relname, " +
" a.attname, " +
" a.atttypid, " +
" a.attnum, " +
" a.attnotnull, " +
" a.attlen, " +
" a.atttypmod, " +
" d.adsrc, " +
" t.typname, " +
/* Use the new col_description in 7.2 or an additional outer join in 7.1 */
(connection.haveMinimumServerVersion("7.2") ? "col_description(a.attrelid, a.attnum) " : "e.description ") +
"from" +
" (" +
" (pg_class c inner join pg_attribute a on" +
" (" +
" a.attrelid=c.oid");
if ((tableNamePattern != null) && ! tableNamePattern.equals("%"))
{
sql.append(" and c.relname like \'" + tableNamePattern.toLowerCase() + "\'");
}
if ((columnNamePattern != null) && ! columnNamePattern.equals("%"))
{
sql.append(" and a.attname like \'" + columnNamePattern + "\'");
}
sql.append(
" and a.attnum > 0" +
" )" +
" ) inner join pg_type t on" +
" (" +
" t.oid = a.atttypid" +
" )" +
" )" +
" left outer join pg_attrdef d on" +
" (" +
" c.oid = d.adrelid" +
" and a.attnum = d.adnum" +
" ) ");
if (!connection.haveMinimumServerVersion("7.2"))
{
/* Only for 7.1 */
sql.append(
" left outer join pg_description e on" +
" (" +
" e.objoid = a.oid" +
" ) ");
}
sql.append("order by" +
" c.relname, a.attnum");
java.sql.ResultSet r = connection.ExecSQL(sql.toString());
while (r.next())
{
byte[][] tuple = new byte[18][0];
String nullFlag = r.getString(6);
String typname = r.getString(10);
tuple[0] = null; // Catalog name, not supported
tuple[1] = null; // Schema name, not supported
tuple[2] = r.getBytes(2); // Table name
tuple[3] = r.getBytes(3); // Column name
tuple[4] = Integer.toString(connection.getSQLType(typname)).getBytes(); // Data type
tuple[5] = typname.getBytes(); // Type name
// Column size
// Looking at the psql source,
// I think the length of a varchar as specified when the table was created
// should be extracted from atttypmod which contains this length + sizeof(int32)
if (typname.equals("bpchar") || typname.equals("varchar"))
{
int atttypmod = r.getInt(8);
tuple[6] = Integer.toString(atttypmod != -1 ? atttypmod - VARHDRSZ : 0).getBytes();
}
else
{
tuple[6] = r.getBytes(7);
}
tuple[7] = null; // Buffer length
// Decimal digits = scale
// From the source (see e.g. backend/utils/adt/format_type.c,
// function numeric()) the scale and precision can be calculated
// from the typmod value.
if (typname.equals("numeric") || typname.equals("decimal"))
{
int attypmod = r.getInt(8) - VARHDRSZ;
tuple[8] =
Integer.toString( attypmod & 0xffff ).getBytes();
tuple[9] =
Integer.toString( ( attypmod >> 16 ) & 0xffff ).getBytes();
}
else
{
tuple[8] = "0".getBytes();
tuple[9] = "10".getBytes(); // Num Prec Radix - assume decimal
}
tuple[10] = Integer.toString(nullFlag.equals("f") ?
java.sql.DatabaseMetaData.columnNullable :
java.sql.DatabaseMetaData.columnNoNulls).getBytes(); // Nullable
tuple[11] = r.getBytes(11); // Description (if any)
tuple[12] = r.getBytes(9); // Column default
tuple[13] = null; // sql data type (unused)
tuple[14] = null; // sql datetime sub (unused)
tuple[15] = tuple[6]; // char octet length
tuple[16] = r.getBytes(5); // ordinal position
tuple[17] = (nullFlag.equals("f") ? "YES" : "NO").getBytes(); // Is nullable
v.addElement(tuple);
}
r.close();
return connection.getResultSet(null, f, v, "OK", 1);
}
/*
* Get a description of the access rights for a table's columns.
*
*
*
*
*
*
* @param catalog a catalog name; "" retrieves those without a catalog
* @param schema a schema name; "" retrieves those without a schema
* @param table a table name
* @param columnNamePattern a column name pattern
* @return ResultSet each row is a column privilege description
* @see #getSearchStringEscape
*/
public java.sql.ResultSet getColumnPrivileges(String catalog, String schema, String table, String columnNamePattern) throws SQLException
{
Field f[] = new Field[8];
Vector v = new Vector();
if (table == null)
table = "%";
if (columnNamePattern == null)
columnNamePattern = "%";
else
columnNamePattern = columnNamePattern.toLowerCase();
f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
f[3] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
f[4] = new Field(connection, "GRANTOR", iVarcharOid, 32);
f[5] = new Field(connection, "GRANTEE", iVarcharOid, 32);
f[6] = new Field(connection, "PRIVILEGE", iVarcharOid, 32);
f[7] = new Field(connection, "IS_GRANTABLE", iVarcharOid, 32);
// This is taken direct from the psql source
java.sql.ResultSet r = connection.ExecSQL("SELECT relname, relacl FROM pg_class, pg_user WHERE ( relkind = 'r' OR relkind = 'i') and relname !~ '^pg_' and relname !~ '^xin[vx][0-9]+' and usesysid = relowner and relname like '" + table.toLowerCase() + "' ORDER BY relname");
while (r.next())
{
byte[][] tuple = new byte[8][0];
tuple[0] = tuple[1] = "".getBytes();
if (Driver.logDebug) Driver.debug("relname=\"" + r.getString(1) + "\" relacl=\"" + r.getString(2) + "\"");
// For now, don't add to the result as relacl needs to be processed.
//v.addElement(tuple);
}
return connection.getResultSet(null, f, v, "OK", 1);
}
/*
* Get a description of the access rights for each table available
* in a catalog.
*
* This method is currently unimplemented.
*
*
*
*
* @param catalog a catalog name; "" retrieves those without a catalog
* @param schemaPattern a schema name pattern; "" retrieves those
* without a schema
* @param tableNamePattern a table name pattern
* @return ResultSet each row is a table privilege description
* @see #getSearchStringEscape
*/
public java.sql.ResultSet getTablePrivileges(String catalog, String schemaPattern, String tableNamePattern) throws SQLException
{
Field f[] = new Field[8];
Vector v = new Vector();
if (tableNamePattern == null)
tableNamePattern = "%";
f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
f[3] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
f[4] = new Field(connection, "GRANTOR", iVarcharOid, 32);
f[5] = new Field(connection, "GRANTEE", iVarcharOid, 32);
f[6] = new Field(connection, "PRIVILEGE", iVarcharOid, 32);
f[7] = new Field(connection, "IS_GRANTABLE", iVarcharOid, 32);
// This is taken direct from the psql source
java.sql.ResultSet r = connection.ExecSQL("SELECT relname, relacl FROM pg_class, pg_user WHERE ( relkind = 'r' OR relkind = 'i') and relname !~ '^pg_' and relname !~ '^xin[vx][0-9]+' and usesysid = relowner and relname like '" + tableNamePattern.toLowerCase() + "' ORDER BY relname");
while (r.next())
{
byte[][] tuple = new byte[8][0];
tuple[0] = tuple[1] = "".getBytes();
if (Driver.logDebug) Driver.debug("relname=\"" + r.getString(1) + "\" relacl=\"" + r.getString(2) + "\"");
// For now, don't add to the result as relacl needs to be processed.
//v.addElement(tuple);
}
return connection.getResultSet(null, f, v, "OK", 1);
}
/*
* Get a description of a table's optimal set of columns that
* uniquely identifies a row. They are ordered by SCOPE.
*
* This method is currently not implemented.
*
*
*
*
* @param catalog a catalog name; "" retrieves those without a catalog
* @param schema a schema name; "" retrieves those without a schema
* @param table a table name
* @param scope the scope of interest; use same values as SCOPE
* @param nullable include columns that are nullable?
* @return ResultSet each row is a column description
*/
// Implementation note: This is required for Borland's JBuilder to work
public java.sql.ResultSet getBestRowIdentifier(String catalog, String schema, String table, int scope, boolean nullable) throws SQLException
{
// for now, this returns an empty result set.
Field f[] = new Field[8];
ResultSet r; // ResultSet for the SQL query that we need to do
Vector v = new Vector(); // The new ResultSet tuple stuff
f[0] = new Field(connection, "SCOPE", iInt2Oid, 2);
f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, 32);
f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4);
f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2);
f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2);
return connection.getResultSet(null, f, v, "OK", 1);
}
/*
* Get a description of a table's columns that are automatically
* updated when any value in a row is updated. They are
* unordered.
*
* This method is currently unimplemented.
*
*
*
*
*
*
*
*
* @param catalog a catalog name; "" retrieves those without a catalog
* @param schema a schema name; "" retrieves those without a schema
* @param table a table name
* @return ResultSet each row is a column description
*/
public java.sql.ResultSet getVersionColumns(String catalog, String schema, String table) throws SQLException
{
throw org.postgresql.Driver.notImplemented();
}
/*
* Get a description of a table's primary key columns. They
* are ordered by COLUMN_NAME.
*
*
*
*
*
*
* @param catalog a catalog name; "" retrieves those without a catalog
* @param schema a schema name pattern; "" retrieves those
* without a schema
* @param table a table name
* @return ResultSet each row is a primary key column description
*/
public java.sql.ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException
{
return connection.createStatement().executeQuery("SELECT " +
"'' as TABLE_CAT," +
"'' AS TABLE_SCHEM," +
"bc.relname AS TABLE_NAME," +
"a.attname AS COLUMN_NAME," +
"a.attnum as KEY_SEQ," +
"ic.relname as PK_NAME " +
" FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a" +
" WHERE bc.relkind = 'r' " + // -- not indices
" and upper(bc.relname) = upper('" + table + "')" +
" and i.indrelid = bc.oid" +
" and i.indexrelid = ic.oid" +
" and ic.oid = a.attrelid" +
" and i.indisprimary='t' " +
" ORDER BY table_name, pk_name, key_seq"
);
}
/*
SELECT
c.relname as primary,
c2.relname as foreign,
t.tgconstrname,
ic.relname as fkeyname,
af.attnum as fkeyseq,
ipc.relname as pkeyname,
ap.attnum as pkeyseq,
t.tgdeferrable,
t.tginitdeferred,
t.tgnargs,t.tgargs,
p1.proname as updaterule,
p2.proname as deleterule
FROM
pg_trigger t,
pg_trigger t1,
pg_class c,
pg_class c2,
pg_class ic,
pg_class ipc,
pg_proc p1,
pg_proc p2,
pg_index if,
pg_index ip,
pg_attribute af,
pg_attribute ap
WHERE
(t.tgrelid=c.oid
AND t.tgisconstraint
AND t.tgconstrrelid=c2.oid
AND t.tgfoid=p1.oid
and p1.proname like '%%upd')
and
(t1.tgrelid=c.oid
and t1.tgisconstraint
and t1.tgconstrrelid=c2.oid
AND t1.tgfoid=p2.oid
and p2.proname like '%%del')
AND c2.relname='users'
AND
(if.indrelid=c.oid
AND if.indexrelid=ic.oid
and ic.oid=af.attrelid
AND if.indisprimary)
and
(ip.indrelid=c2.oid
and ip.indexrelid=ipc.oid
and ipc.oid=ap.attrelid
and ip.indisprimary)
*/
/**
*
* @param catalog
* @param schema
* @param primaryTable if provided will get the keys exported by this table
* @param foreignTable if provided will get the keys imported by this table
* @return ResultSet
* @throws SQLException
*/
private java.sql.ResultSet getImportedExportedKeys(String catalog, String schema, String primaryTable, String foreignTable) throws SQLException
{
Field f[] = new Field[14];
f[0] = new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
f[1] = new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
f[2] = new Field(connection, "PKTABLE_NAME", iVarcharOid, 32);
f[3] = new Field(connection, "PKCOLUMN_NAME", iVarcharOid, 32);
f[4] = new Field(connection, "FKTABLE_CAT", iVarcharOid, 32);
f[5] = new Field(connection, "FKTABLE_SCHEM", iVarcharOid, 32);
f[6] = new Field(connection, "FKTABLE_NAME", iVarcharOid, 32);
f[7] = new Field(connection, "FKCOLUMN_NAME", iVarcharOid, 32);
f[8] = new Field(connection, "KEY_SEQ", iInt2Oid, 2);
f[9] = new Field(connection, "UPDATE_RULE", iInt2Oid, 2);
f[10] = new Field(connection, "DELETE_RULE", iInt2Oid, 2);
f[11] = new Field(connection, "FK_NAME", iVarcharOid, 32);
f[12] = new Field(connection, "PK_NAME", iVarcharOid, 32);
f[13] = new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
java.sql.ResultSet rs = connection.ExecSQL(
"SELECT distinct "
+ "c.relname as prelname, "
+ "c2.relname as frelname, "
+ "t.tgconstrname, "
+ "a.attnum as keyseq, "
+ "ic.relname as fkeyname, "
+ "t.tgdeferrable, "
+ "t.tginitdeferred, "
+ "t.tgnargs,t.tgargs, "
+ "p1.proname as updaterule, "
+ "p2.proname as deleterule "
+ "FROM "
+ "pg_trigger t, "
+ "pg_trigger t1, "
+ "pg_class c, "
+ "pg_class c2, "
+ "pg_class ic, "
+ "pg_proc p1, "
+ "pg_proc p2, "
+ "pg_index i, "
+ "pg_attribute a "
+ "WHERE "
// isolate the update rule
+ "(t.tgrelid=c.oid "
+ "AND t.tgisconstraint "
+ "AND t.tgconstrrelid=c2.oid "
+ "AND t.tgfoid=p1.oid "
+ "and p1.proname like '%%upd') "
+ "and "
// isolate the delete rule
+ "(t1.tgrelid=c.oid "
+ "and t1.tgisconstraint "
+ "and t1.tgconstrrelid=c2.oid "
+ "AND t1.tgfoid=p2.oid "
+ "and p2.proname like '%%del') "
// if we are looking for exported keys then primary table will be used
+ ((primaryTable != null) ? "AND c.relname='" + primaryTable + "' " : "")
// if we are looking for imported keys then the foreign table will be used
+ ((foreignTable != null) ? "AND c2.relname='" + foreignTable + "' " : "")
+ "AND i.indrelid=c.oid "
+ "AND i.indexrelid=ic.oid "
+ "AND ic.oid=a.attrelid "
+ "AND i.indisprimary "
+ "ORDER BY "
// orderby is as follows getExported, orders by FKTABLE,
// getImported orders by PKTABLE
// getCrossReference orders by FKTABLE, so this should work for both,
// since when getting crossreference, primaryTable will be defined
+ (primaryTable != null ? "frelname" : "prelname") + ",keyseq");
// returns the following columns
// and some example data with a table defined as follows
// create table people ( id int primary key);
// create table policy ( id int primary key);
// create table users ( id int primary key, people_id int references people(id), policy_id int references policy(id))
// prelname | frelname | tgconstrname | keyseq | fkeyName | tgdeferrable | tginitdeferred
// 1 | 2 | 3 | 4 | 5 | 6 | 7
// people | users |
*
*
* @param catalog a catalog name; "" retrieves those without a catalog
* @param schema a schema name pattern; "" retrieves those
* without a schema
* @param table a table name
* @return ResultSet each row is a primary key column description
* @see #getExportedKeys
*/
public java.sql.ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException
{
return getImportedExportedKeys(catalog, schema, null, table);
}
/*
* Get a description of a foreign key columns that reference a
* table's primary key columns (the foreign keys exported by a
* table). They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
* FKTABLE_NAME, and KEY_SEQ.
*
* This method is currently unimplemented.
*
*
*
*
*
*
*
*
* @param catalog a catalog name; "" retrieves those without a catalog
* @param schema a schema name pattern; "" retrieves those
* without a schema
* @param table a table name
* @return ResultSet each row is a foreign key column description
* @see #getImportedKeys
*/
public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
{
return getImportedExportedKeys(catalog, schema, table, null);
}
/*
* Get a description of the foreign key columns in the foreign key
* table that reference the primary key columns of the primary key
* table (describe how one table imports another's key.) This
* should normally return a single foreign key/primary key pair
* (most tables only import a foreign key from a table once.) They
* are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and
* KEY_SEQ.
*
* This method is currently unimplemented.
*
*
*
*
*
*
*
*
* @param catalog a catalog name; "" retrieves those without a catalog
* @param schema a schema name pattern; "" retrieves those
* without a schema
* @param table a table name
* @return ResultSet each row is a foreign key column description
* @see #getImportedKeys
*/
public java.sql.ResultSet getCrossReference(String primaryCatalog, String primarySchema, String primaryTable, String foreignCatalog, String foreignSchema, String foreignTable) throws SQLException
{
return getImportedExportedKeys(primaryCatalog, primarySchema, primaryTable, foreignTable);
}
/*
* Get a description of all the standard SQL types supported by
* this database. They are ordered by DATA_TYPE and then by how
* closely the data type maps to the corresponding JDBC SQL type.
*
*
*
*
*
*
*
*
* @return ResultSet each row is a SQL type description
*/
public java.sql.ResultSet getTypeInfo() throws SQLException
{
java.sql.ResultSet rs = connection.ExecSQL("select typname from pg_type");
if (rs != null)
{
Field f[] = new Field[18];
ResultSet r; // ResultSet for the SQL query that we need to do
Vector v = new Vector(); // The new ResultSet tuple stuff
f[0] = new Field(connection, "TYPE_NAME", iVarcharOid, 32);
f[1] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
f[2] = new Field(connection, "PRECISION", iInt4Oid, 4);
f[3] = new Field(connection, "LITERAL_PREFIX", iVarcharOid, 32);
f[4] = new Field(connection, "LITERAL_SUFFIX", iVarcharOid, 32);
f[5] = new Field(connection, "CREATE_PARAMS", iVarcharOid, 32);
f[6] = new Field(connection, "NULLABLE", iInt2Oid, 2);
f[7] = new Field(connection, "CASE_SENSITIVE", iBoolOid, 1);
f[8] = new Field(connection, "SEARCHABLE", iInt2Oid, 2);
f[9] = new Field(connection, "UNSIGNED_ATTRIBUTE", iBoolOid, 1);
f[10] = new Field(connection, "FIXED_PREC_SCALE", iBoolOid, 1);
f[11] = new Field(connection, "AUTO_INCREMENT", iBoolOid, 1);
f[12] = new Field(connection, "LOCAL_TYPE_NAME", iVarcharOid, 32);
f[13] = new Field(connection, "MINIMUM_SCALE", iInt2Oid, 2);
f[14] = new Field(connection, "MAXIMUM_SCALE", iInt2Oid, 2);
f[15] = new Field(connection, "SQL_DATA_TYPE", iInt4Oid, 4);
f[16] = new Field(connection, "SQL_DATETIME_SUB", iInt4Oid, 4);
f[17] = new Field(connection, "NUM_PREC_RADIX", iInt4Oid, 4);
// cache some results, this will keep memory useage down, and speed
// things up a little.
byte b9[] = "9".getBytes();
byte b10[] = "10".getBytes();
byte bf[] = "f".getBytes();
byte bnn[] = Integer.toString(java.sql.DatabaseMetaData.typeNoNulls).getBytes();
byte bts[] = Integer.toString(java.sql.DatabaseMetaData.typeSearchable).getBytes();
while (rs.next())
{
byte[][] tuple = new byte[18][];
String typname = rs.getString(1);
tuple[0] = typname.getBytes();
tuple[1] = Integer.toString(connection.getSQLType(typname)).getBytes();
tuple[2] = b9; // for now
tuple[6] = bnn; // for now
tuple[7] = bf; // false for now - not case sensitive
tuple[8] = bts;
tuple[9] = bf; // false for now - it's signed
tuple[10] = bf; // false for now - must handle money
tuple[11] = bf; // false for now - handle autoincrement
// 12 - LOCAL_TYPE_NAME is null
// 13 & 14 ?
// 15 & 16 are unused so we return null
tuple[17] = b10; // everything is base 10
v.addElement(tuple);
}
rs.close();
return connection.getResultSet(null, f, v, "OK", 1);
}
throw new PSQLException("postgresql.metadata.unavailable");
}
/*
* Get a description of a table's indices and statistics. They are
* ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
*
*
*
*
*
*
*
*
* @param catalog a catalog name; "" retrieves those without a catalog
* @param schema a schema name pattern; "" retrieves those without a schema
* @param table a table name
* @param unique when true, return only indices for unique values;
* when false, return indices regardless of whether unique or not
* @param approximate when true, result is allowed to reflect approximate
* or out of data values; when false, results are requested to be
* accurate
* @return ResultSet each row is an index column description
*/
// Implementation note: This is required for Borland's JBuilder to work
public java.sql.ResultSet getIndexInfo(String catalog, String schema, String tableName, boolean unique, boolean approximate) throws SQLException
{
Field f[] = new Field[13];
java.sql.ResultSet r; // ResultSet for the SQL query that we need to do
Vector v = new Vector(); // The new ResultSet tuple stuff
f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
f[3] = new Field(connection, "NON_UNIQUE", iBoolOid, 1);
f[4] = new Field(connection, "INDEX_QUALIFIER", iVarcharOid, 32);
f[5] = new Field(connection, "INDEX_NAME", iVarcharOid, 32);
f[6] = new Field(connection, "TYPE", iInt2Oid, 2);
f[7] = new Field(connection, "ORDINAL_POSITION", iInt2Oid, 2);
f[8] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
f[9] = new Field(connection, "ASC_OR_DESC", iVarcharOid, 32);
f[10] = new Field(connection, "CARDINALITY", iInt4Oid, 4);
f[11] = new Field(connection, "PAGES", iInt4Oid, 4);
f[12] = new Field(connection, "FILTER_CONDITION", iVarcharOid, 32);
r = connection.ExecSQL("select " +
"c.relname, " +
"x.indisunique, " +
"i.relname, " +
"x.indisclustered, " +
"a.amname, " +
"x.indkey, " +
"c.reltuples, " +
"c.relpages, " +
"x.indexrelid " +
"FROM pg_index x, pg_class c, pg_class i, pg_am a " +
"WHERE ((c.relname = '" + tableName.toLowerCase() + "') " +
" AND (c.oid = x.indrelid) " +
" AND (i.oid = x.indexrelid) " +
" AND (i.relam = a.oid)) " +
"ORDER BY x.indisunique DESC, " +
" x.indisclustered, a.amname, i.relname");
while (r.next())
{
// indkey is an array of column ordinals (integers). In the JDBC
// interface, this has to be separated out into a separate
// tuple for each indexed column. Also, getArray() is not yet
// implemented for Postgres JDBC, so we parse by hand.
String columnOrdinalString = r.getString(6);
StringTokenizer stok = new StringTokenizer(columnOrdinalString);
int [] columnOrdinals = new int[stok.countTokens()];
int o = 0;
while (stok.hasMoreTokens())
{
columnOrdinals[o++] = Integer.parseInt(stok.nextToken());
}
java.sql.ResultSet columnNameRS = connection.ExecSQL("select a.attname FROM pg_attribute a WHERE a.attrelid = " + r.getInt(9));
for (int i = 0; i < columnOrdinals.length; i++)
{
byte [] [] tuple = new byte [13] [];
tuple[0] = "".getBytes();
tuple[1] = "".getBytes();
tuple[2] = r.getBytes(1);
tuple[3] = r.getBoolean(2) ? "false".getBytes() : "true".getBytes();
tuple[4] = null;
tuple[5] = r.getBytes(3);
tuple[6] = r.getBoolean(4) ?
Integer.toString(java.sql.DatabaseMetaData.tableIndexClustered).getBytes() :
r.getString(5).equals("hash") ?
Integer.toString(java.sql.DatabaseMetaData.tableIndexHashed).getBytes() :
Integer.toString(java.sql.DatabaseMetaData.tableIndexOther).getBytes();
tuple[7] = Integer.toString(i + 1).getBytes();
if (columnNameRS.next())
tuple[8] = columnNameRS.getBytes(1);
else
tuple[8] = "".getBytes();
tuple[9] = null; // sort sequence ???
tuple[10] = r.getBytes(7); // inexact
tuple[11] = r.getBytes(8);
tuple[12] = null;
v.addElement(tuple);
}
}
return connection.getResultSet(null, f, v, "OK", 1);
}
// ** JDBC 2 Extensions **
/*
* Does the database support the given result set type?
*
* @param type - defined in java.sql.ResultSet
* @return true if so; false otherwise
* @exception SQLException - if a database access error occurs
*/
public boolean supportsResultSetType(int type) throws SQLException
{
// The only type we don't support
return type != java.sql.ResultSet.TYPE_SCROLL_SENSITIVE;
}
/*
* Does the database support the concurrency type in combination
* with the given result set type?
*
* @param type - defined in java.sql.ResultSet
* @param concurrency - type defined in java.sql.ResultSet
* @return true if so; false otherwise
* @exception SQLException - if a database access error occurs
*/
public boolean supportsResultSetConcurrency(int type, int concurrency) throws SQLException
{
// These combinations are not supported!
if (type == java.sql.ResultSet.TYPE_SCROLL_SENSITIVE)
return false;
// We don't yet support Updateable ResultSets
if (concurrency == java.sql.ResultSet.CONCUR_UPDATABLE)
return false;
// Everything else we do
return true;
}
/* lots of unsupported stuff... */
public boolean ownUpdatesAreVisible(int type) throws SQLException
{
return false;
}
public boolean ownDeletesAreVisible(int type) throws SQLException
{
return false;
}
public boolean ownInsertsAreVisible(int type) throws SQLException
{
return false;
}
public boolean othersUpdatesAreVisible(int type) throws SQLException
{
return false;
}
public boolean othersDeletesAreVisible(int i) throws SQLException
{
return false;
}
public boolean othersInsertsAreVisible(int type) throws SQLException
{
return false;
}
public boolean updatesAreDetected(int type) throws SQLException
{
return false;
}
public boolean deletesAreDetected(int i) throws SQLException
{
return false;
}
public boolean insertsAreDetected(int type) throws SQLException
{
return false;
}
/*
* Indicates whether the driver supports batch updates.
*/
public boolean supportsBatchUpdates() throws SQLException
{
return true;
}
/*
* Return user defined types in a schema
*/
public java.sql.ResultSet getUDTs(String catalog,
String schemaPattern,
String typeNamePattern,
int[] types
) throws SQLException
{
throw org.postgresql.Driver.notImplemented();
}
/*
* Retrieves the connection that produced this metadata object.
*
* @return the connection that produced this metadata object
*/
public java.sql.Connection getConnection() throws SQLException
{
return (java.sql.Connection)connection;
}
/* I don't find these in the spec!?! */
public boolean rowChangesAreDetected(int type) throws SQLException
{
return false;
}
public boolean rowChangesAreVisible(int type) throws SQLException
{
return false;
}
}
*
*