1 package org.postgresql.jdbc1;
3 // IMPORTANT NOTE: This file implements the JDBC 1 version of the driver.
4 // If you make any modifications to this file, you must make sure that the
5 // changes are also made (if relevent) to the related JDBC 2 class in the
6 // org.postgresql.jdbc2 package.
10 import org.postgresql.Field;
11 import org.postgresql.util.PSQLException;
14 * This class provides information about the database as a whole.
16 * <p>Many of the methods here return lists of information in ResultSets. You
17 * can use the normal ResultSet methods such as getString and getInt to
18 * retrieve the data from these ResultSets. If a given form of metadata is
19 * not available, these methods should throw a SQLException.
21 * <p>Some of these methods take arguments that are String patterns. These
22 * arguments all have names such as fooPattern. Within a pattern String,
23 * "%" means match any substring of 0 or more characters, and "_" means
24 * match any one character. Only metadata entries matching the search
25 * pattern are returned. if a search pattern argument is set to a null
26 * ref, it means that argument's criteria should be dropped from the
29 * <p>A SQLException will be throws if a driver does not support a meta
30 * data method. In the case of methods that return a ResultSet, either
31 * a ResultSet (which may be empty) is returned or a SQLException is
34 * @see java.sql.DatabaseMetaData
36 public class DatabaseMetaData implements java.sql.DatabaseMetaData
38 Connection connection; // The connection association
40 // These define various OID's. Hopefully they will stay constant.
41 static final int iVarcharOid = 1043; // OID for varchar
42 static final int iBoolOid = 16; // OID for bool
43 static final int iInt2Oid = 21; // OID for int2
44 static final int iInt4Oid = 23; // OID for int4
45 static final int VARHDRSZ = 4; // length for int4
47 public DatabaseMetaData(Connection conn)
49 this.connection = conn;
53 * Can all the procedures returned by getProcedures be called
54 * by the current user?
57 * @exception SQLException if a database access error occurs
59 public boolean allProceduresAreCallable() throws SQLException
61 return true; // For now...
65 * Can all the tables returned by getTable be SELECTed by
69 * @exception SQLException if a database access error occurs
71 public boolean allTablesAreSelectable() throws SQLException
73 return true; // For now...
77 * What is the URL for this database?
79 * @return the url or null if it cannott be generated
80 * @exception SQLException if a database access error occurs
82 public String getURL() throws SQLException
84 return connection.getURL();
88 * What is our user name as known to the database?
90 * @return our database user name
91 * @exception SQLException if a database access error occurs
93 public String getUserName() throws SQLException
95 return connection.getUserName();
99 * Is the database in read-only mode?
102 * @exception SQLException if a database access error occurs
104 public boolean isReadOnly() throws SQLException
106 return connection.isReadOnly();
110 * Are NULL values sorted high?
113 * @exception SQLException if a database access error occurs
115 public boolean nullsAreSortedHigh() throws SQLException
117 return connection.haveMinimumServerVersion("7.2");
121 * Are NULL values sorted low?
124 * @exception SQLException if a database access error occurs
126 public boolean nullsAreSortedLow() throws SQLException
132 * Are NULL values sorted at the start regardless of sort order?
135 * @exception SQLException if a database access error occurs
137 public boolean nullsAreSortedAtStart() throws SQLException
143 * Are NULL values sorted at the end regardless of sort order?
146 * @exception SQLException if a database access error occurs
148 public boolean nullsAreSortedAtEnd() throws SQLException
150 return ! connection.haveMinimumServerVersion("7.2");
154 * What is the name of this database product - we hope that it is
155 * PostgreSQL, so we return that explicitly.
157 * @return the database product name
158 * @exception SQLException if a database access error occurs
160 public String getDatabaseProductName() throws SQLException
166 * What is the version of this database product.
168 * @return the database version
169 * @exception SQLException if a database access error occurs
171 public String getDatabaseProductVersion() throws SQLException
173 return connection.getDBVersionNumber();
177 * What is the name of this JDBC driver? If we don't know this
178 * we are doing something wrong!
180 * @return the JDBC driver name
181 * @exception SQLException why?
183 public String getDriverName() throws SQLException
185 return "PostgreSQL Native Driver";
189 * What is the version string of this JDBC driver? Again, this is
192 * @return the JDBC driver name.
193 * @exception SQLException why?
195 public String getDriverVersion() throws SQLException
197 return connection.this_driver.getVersion();
201 * What is this JDBC driver's major version number?
203 * @return the JDBC driver major version
205 public int getDriverMajorVersion()
207 return connection.this_driver.getMajorVersion();
211 * What is this JDBC driver's minor version number?
213 * @return the JDBC driver minor version
215 public int getDriverMinorVersion()
217 return connection.this_driver.getMinorVersion();
221 * Does the database store tables in a local file? No - it
222 * stores them in a file on the server.
225 * @exception SQLException if a database access error occurs
227 public boolean usesLocalFiles() throws SQLException
233 * Does the database use a file for each table? Well, not really,
234 * since it doesnt use local files.
237 * @exception SQLException if a database access error occurs
239 public boolean usesLocalFilePerTable() throws SQLException
245 * Does the database treat mixed case unquoted SQL identifiers
246 * as case sensitive and as a result store them in mixed case?
247 * A JDBC-Compliant driver will always return false.
249 * <p>Predicament - what do they mean by "SQL identifiers" - if it
250 * means the names of the tables and columns, then the answers
251 * given below are correct - otherwise I don't know.
254 * @exception SQLException if a database access error occurs
256 public boolean supportsMixedCaseIdentifiers() throws SQLException
262 * Does the database treat mixed case unquoted SQL identifiers as
263 * case insensitive and store them in upper case?
267 public boolean storesUpperCaseIdentifiers() throws SQLException
273 * Does the database treat mixed case unquoted SQL identifiers as
274 * case insensitive and store them in lower case?
278 public boolean storesLowerCaseIdentifiers() throws SQLException
284 * Does the database treat mixed case unquoted SQL identifiers as
285 * case insensitive and store them in mixed case?
289 public boolean storesMixedCaseIdentifiers() throws SQLException
295 * Does the database treat mixed case quoted SQL identifiers as
296 * case sensitive and as a result store them in mixed case? A
297 * JDBC compliant driver will always return true.
300 * @exception SQLException if a database access error occurs
302 public boolean supportsMixedCaseQuotedIdentifiers() throws SQLException
308 * Does the database treat mixed case quoted SQL identifiers as
309 * case insensitive and store them in upper case?
313 public boolean storesUpperCaseQuotedIdentifiers() throws SQLException
319 * Does the database treat mixed case quoted SQL identifiers as case
320 * insensitive and store them in lower case?
324 public boolean storesLowerCaseQuotedIdentifiers() throws SQLException
330 * Does the database treat mixed case quoted SQL identifiers as case
331 * insensitive and store them in mixed case?
335 public boolean storesMixedCaseQuotedIdentifiers() throws SQLException
341 * What is the string used to quote SQL identifiers? This returns
342 * a space if identifier quoting isn't supported. A JDBC Compliant
343 * driver will always use a double quote character.
345 * @return the quoting string
346 * @exception SQLException if a database access error occurs
348 public String getIdentifierQuoteString() throws SQLException
354 * Get a comma separated list of all a database's SQL keywords that
355 * are NOT also SQL92 keywords.
357 * <p>Within PostgreSQL, the keywords are found in
358 * src/backend/parser/keywords.c
360 * <p>For SQL Keywords, I took the list provided at
361 * <a href="http://web.dementia.org/~shadow/sql/sql3bnf.sep93.txt">
362 * http://web.dementia.org/~shadow/sql/sql3bnf.sep93.txt</a>
363 * which is for SQL3, not SQL-92, but it is close enough for
366 * @return a comma separated list of keywords we use
367 * @exception SQLException if a database access error occurs
369 public String getSQLKeywords() throws SQLException
371 return "abort,acl,add,aggregate,append,archive,arch_store,backward,binary,change,cluster,copy,database,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";
374 public String getNumericFunctions() throws SQLException
376 // XXX-Not Implemented
380 public String getStringFunctions() throws SQLException
382 // XXX-Not Implemented
386 public String getSystemFunctions() throws SQLException
388 // XXX-Not Implemented
392 public String getTimeDateFunctions() throws SQLException
394 // XXX-Not Implemented
399 * This is the string that can be used to escape '_' and '%' in
400 * a search string pattern style catalog search parameters
402 * @return the string used to escape wildcard characters
403 * @exception SQLException if a database access error occurs
405 public String getSearchStringEscape() throws SQLException
411 * Get all the "extra" characters that can be used in unquoted
412 * identifier names (those beyond a-zA-Z0-9 and _)
414 * <p>From the file src/backend/parser/scan.l, an identifier is
415 * {letter}{letter_or_digit} which makes it just those listed
418 * @return a string containing the extra characters
419 * @exception SQLException if a database access error occurs
421 public String getExtraNameCharacters() throws SQLException
427 * Is "ALTER TABLE" with an add column supported?
428 * Yes for PostgreSQL 6.1
431 * @exception SQLException if a database access error occurs
433 public boolean supportsAlterTableWithAddColumn() throws SQLException
439 * Is "ALTER TABLE" with a drop column supported?
440 * Peter 10/10/2000 This was set to true, but 7.1devel doesn't support it!
443 * @exception SQLException if a database access error occurs
445 public boolean supportsAlterTableWithDropColumn() throws SQLException
451 * Is column aliasing supported?
453 * <p>If so, the SQL AS clause can be used to provide names for
454 * computed columns or to provide alias names for columns as
455 * required. A JDBC Compliant driver always returns true.
460 * select count(C) as C_COUNT from T group by C;
463 * should return a column named as C_COUNT instead of count(C)
466 * @exception SQLException if a database access error occurs
468 public boolean supportsColumnAliasing() throws SQLException
474 * Are concatenations between NULL and non-NULL values NULL? A
475 * JDBC Compliant driver always returns true
478 * @exception SQLException if a database access error occurs
480 public boolean nullPlusNonNullIsNull() throws SQLException
485 public boolean supportsConvert() throws SQLException
487 // XXX-Not Implemented
491 public boolean supportsConvert(int fromType, int toType) throws SQLException
493 // XXX-Not Implemented
498 * Are table correlation names supported? A JDBC Compliant
499 * driver always returns true.
501 * @return true if so; false otherwise
502 * @exception SQLException - if a database access error occurs
504 public boolean supportsTableCorrelationNames() throws SQLException
510 * If table correlation names are supported, are they restricted to
511 * be different from the names of the tables?
513 * @return true if so; false otherwise
514 * @exception SQLException - if a database access error occurs
516 public boolean supportsDifferentTableCorrelationNames() throws SQLException
522 * Are expressions in "ORDER BY" lists supported?
524 * <br>e.g. select * from t order by a + b;
527 * @exception SQLException if a database access error occurs
529 public boolean supportsExpressionsInOrderBy() throws SQLException
535 * Can an "ORDER BY" clause use columns not in the SELECT?
538 * @exception SQLException if a database access error occurs
540 public boolean supportsOrderByUnrelated() throws SQLException
542 return connection.haveMinimumServerVersion("6.4");
546 * Is some form of "GROUP BY" clause supported?
547 * I checked it, and yes it is.
550 * @exception SQLException if a database access error occurs
552 public boolean supportsGroupBy() throws SQLException
558 * Can a "GROUP BY" clause use columns not in the SELECT?
561 * @exception SQLException if a database access error occurs
563 public boolean supportsGroupByUnrelated() throws SQLException
565 return connection.haveMinimumServerVersion("6.4");
569 * Can a "GROUP BY" clause add columns not in the SELECT provided
570 * it specifies all the columns in the SELECT? Does anyone actually
571 * understand what they mean here?
573 * (I think this is a subset of the previous function. -- petere)
576 * @exception SQLException if a database access error occurs
578 public boolean supportsGroupByBeyondSelect() throws SQLException
580 return supportsGroupByUnrelated();
584 * Is the escape character in "LIKE" clauses supported? A
585 * JDBC compliant driver always returns true.
588 * @exception SQLException if a database access error occurs
590 public boolean supportsLikeEscapeClause() throws SQLException
592 return connection.haveMinimumServerVersion("7.1");
596 * Are multiple ResultSets from a single execute supported?
597 * Well, I implemented it, but I dont think this is possible from
598 * the back ends point of view.
601 * @exception SQLException if a database access error occurs
603 public boolean supportsMultipleResultSets() throws SQLException
609 * Can we have multiple transactions open at once (on different
611 * I guess we can have, since Im relying on it.
614 * @exception SQLException if a database access error occurs
616 public boolean supportsMultipleTransactions() throws SQLException
622 * Can columns be defined as non-nullable. A JDBC Compliant driver
623 * always returns true.
625 * <p>This changed from false to true in v6.2 of the driver, as this
626 * support was added to the backend.
629 * @exception SQLException if a database access error occurs
631 public boolean supportsNonNullableColumns() throws SQLException
637 * Does this driver support the minimum ODBC SQL grammar. This
638 * grammar is defined at:
640 * <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>
642 * <p>In Appendix C. From this description, we seem to support the
643 * ODBC minimal (Level 0) grammar.
646 * @exception SQLException if a database access error occurs
648 public boolean supportsMinimumSQLGrammar() throws SQLException
654 * Does this driver support the Core ODBC SQL grammar. We need
655 * SQL-92 conformance for this.
658 * @exception SQLException if a database access error occurs
660 public boolean supportsCoreSQLGrammar() throws SQLException
666 * Does this driver support the Extended (Level 2) ODBC SQL
667 * grammar. We don't conform to the Core (Level 1), so we can't
668 * conform to the Extended SQL Grammar.
671 * @exception SQLException if a database access error occurs
673 public boolean supportsExtendedSQLGrammar() throws SQLException
679 * Does this driver support the ANSI-92 entry level SQL grammar?
680 * All JDBC Compliant drivers must return true. We currently
681 * report false until 'schema' support is added. Then this
682 * should be changed to return true, since we will be mostly
683 * compliant (probably more compliant than many other databases)
684 * And since this is a requirement for all JDBC drivers we
685 * need to get to the point where we can return true.
688 * @exception SQLException if a database access error occurs
690 public boolean supportsANSI92EntryLevelSQL() throws SQLException
696 * Does this driver support the ANSI-92 intermediate level SQL
700 * @exception SQLException if a database access error occurs
702 public boolean supportsANSI92IntermediateSQL() throws SQLException
708 * Does this driver support the ANSI-92 full SQL grammar?
711 * @exception SQLException if a database access error occurs
713 public boolean supportsANSI92FullSQL() throws SQLException
719 * Is the SQL Integrity Enhancement Facility supported?
720 * I haven't seen this mentioned anywhere, so I guess not
723 * @exception SQLException if a database access error occurs
725 public boolean supportsIntegrityEnhancementFacility() throws SQLException
731 * Is some form of outer join supported?
734 * @exception SQLException if a database access error occurs
736 public boolean supportsOuterJoins() throws SQLException
738 return connection.haveMinimumServerVersion("7.1");
742 * Are full nexted outer joins supported?
745 * @exception SQLException if a database access error occurs
747 public boolean supportsFullOuterJoins() throws SQLException
749 return connection.haveMinimumServerVersion("7.1");
753 * Is there limited support for outer joins?
756 * @exception SQLException if a database access error occurs
758 public boolean supportsLimitedOuterJoins() throws SQLException
760 return supportsFullOuterJoins();
764 * What is the database vendor's preferred term for "schema"?
765 * PostgreSQL doesn't have schemas, but when it does, we'll use the
768 * @return the vendor term
769 * @exception SQLException if a database access error occurs
771 public String getSchemaTerm() throws SQLException
777 * What is the database vendor's preferred term for "procedure"?
778 * Traditionally, "function" has been used.
780 * @return the vendor term
781 * @exception SQLException if a database access error occurs
783 public String getProcedureTerm() throws SQLException
789 * What is the database vendor's preferred term for "catalog"?
791 * @return the vendor term
792 * @exception SQLException if a database access error occurs
794 public String getCatalogTerm() throws SQLException
800 * Does a catalog appear at the start of a qualified table name?
801 * (Otherwise it appears at the end).
804 * @exception SQLException if a database access error occurs
806 public boolean isCatalogAtStart() throws SQLException
808 throw org.postgresql.Driver.notImplemented();
812 * What is the Catalog separator.
814 * @return the catalog separator string
815 * @exception SQLException if a database access error occurs
817 public String getCatalogSeparator() throws SQLException
819 throw org.postgresql.Driver.notImplemented();
823 * Can a schema name be used in a data manipulation statement? Nope.
826 * @exception SQLException if a database access error occurs
828 public boolean supportsSchemasInDataManipulation() throws SQLException
834 * Can a schema name be used in a procedure call statement? Nope.
837 * @exception SQLException if a database access error occurs
839 public boolean supportsSchemasInProcedureCalls() throws SQLException
845 * Can a schema be used in a table definition statement? Nope.
848 * @exception SQLException if a database access error occurs
850 public boolean supportsSchemasInTableDefinitions() throws SQLException
856 * Can a schema name be used in an index definition statement?
859 * @exception SQLException if a database access error occurs
861 public boolean supportsSchemasInIndexDefinitions() throws SQLException
867 * Can a schema name be used in a privilege definition statement?
870 * @exception SQLException if a database access error occurs
872 public boolean supportsSchemasInPrivilegeDefinitions() throws SQLException
878 * Can a catalog name be used in a data manipulation statement?
881 * @exception SQLException if a database access error occurs
883 public boolean supportsCatalogsInDataManipulation() throws SQLException
889 * Can a catalog name be used in a procedure call statement?
892 * @exception SQLException if a database access error occurs
894 public boolean supportsCatalogsInProcedureCalls() throws SQLException
900 * Can a catalog name be used in a table definition statement?
903 * @exception SQLException if a database access error occurs
905 public boolean supportsCatalogsInTableDefinitions() throws SQLException
911 * Can a catalog name be used in an index definition?
914 * @exception SQLException if a database access error occurs
916 public boolean supportsCatalogsInIndexDefinitions() throws SQLException
922 * Can a catalog name be used in a privilege definition statement?
925 * @exception SQLException if a database access error occurs
927 public boolean supportsCatalogsInPrivilegeDefinitions() throws SQLException
933 * We support cursors for gets only it seems. I dont see a method
934 * to get a positioned delete.
937 * @exception SQLException if a database access error occurs
939 public boolean supportsPositionedDelete() throws SQLException
941 return false; // For now...
945 * Is positioned UPDATE supported?
948 * @exception SQLException if a database access error occurs
950 public boolean supportsPositionedUpdate() throws SQLException
952 return false; // For now...
956 * Is SELECT for UPDATE supported?
958 * @return true if so; false otherwise
959 * @exception SQLException - if a database access error occurs
961 public boolean supportsSelectForUpdate() throws SQLException
963 return connection.haveMinimumServerVersion("6.5");
967 * Are stored procedure calls using the stored procedure escape
970 * @return true if so; false otherwise
971 * @exception SQLException - if a database access error occurs
973 public boolean supportsStoredProcedures() throws SQLException
979 * Are subqueries in comparison expressions supported? A JDBC
980 * Compliant driver always returns true.
982 * @return true if so; false otherwise
983 * @exception SQLException - if a database access error occurs
985 public boolean supportsSubqueriesInComparisons() throws SQLException
991 * Are subqueries in 'exists' expressions supported? A JDBC
992 * Compliant driver always returns true.
994 * @return true if so; false otherwise
995 * @exception SQLException - if a database access error occurs
997 public boolean supportsSubqueriesInExists() throws SQLException
1003 * Are subqueries in 'in' statements supported? A JDBC
1004 * Compliant driver always returns true.
1006 * @return true if so; false otherwise
1007 * @exception SQLException - if a database access error occurs
1009 public boolean supportsSubqueriesInIns() throws SQLException
1015 * Are subqueries in quantified expressions supported? A JDBC
1016 * Compliant driver always returns true.
1018 * (No idea what this is, but we support a good deal of
1021 * @return true if so; false otherwise
1022 * @exception SQLException - if a database access error occurs
1024 public boolean supportsSubqueriesInQuantifieds() throws SQLException
1030 * Are correlated subqueries supported? A JDBC Compliant driver
1031 * always returns true.
1033 * (a.k.a. subselect in from?)
1035 * @return true if so; false otherwise
1036 * @exception SQLException - if a database access error occurs
1038 public boolean supportsCorrelatedSubqueries() throws SQLException
1040 return connection.haveMinimumServerVersion("7.1");
1044 * Is SQL UNION supported?
1046 * @return true if so
1047 * @exception SQLException if a database access error occurs
1049 public boolean supportsUnion() throws SQLException
1051 return true; // since 6.3
1055 * Is SQL UNION ALL supported?
1057 * @return true if so
1058 * @exception SQLException if a database access error occurs
1060 public boolean supportsUnionAll() throws SQLException
1062 return connection.haveMinimumServerVersion("7.1");
1066 * In PostgreSQL, Cursors are only open within transactions.
1068 * @return true if so
1069 * @exception SQLException if a database access error occurs
1071 public boolean supportsOpenCursorsAcrossCommit() throws SQLException
1077 * Do we support open cursors across multiple transactions?
1079 * @return true if so
1080 * @exception SQLException if a database access error occurs
1082 public boolean supportsOpenCursorsAcrossRollback() throws SQLException
1088 * Can statements remain open across commits? They may, but
1089 * this driver cannot guarentee that. In further reflection.
1090 * we are talking a Statement object here, so the answer is
1091 * yes, since the Statement is only a vehicle to ExecSQL()
1093 * @return true if they always remain open; false otherwise
1094 * @exception SQLException if a database access error occurs
1096 public boolean supportsOpenStatementsAcrossCommit() throws SQLException
1102 * Can statements remain open across rollbacks? They may, but
1103 * this driver cannot guarentee that. In further contemplation,
1104 * we are talking a Statement object here, so the answer is yes,
1105 * since the Statement is only a vehicle to ExecSQL() in Connection
1107 * @return true if they always remain open; false otherwise
1108 * @exception SQLException if a database access error occurs
1110 public boolean supportsOpenStatementsAcrossRollback() throws SQLException
1116 * How many hex characters can you have in an inline binary literal
1118 * @return the max literal length
1119 * @exception SQLException if a database access error occurs
1121 public int getMaxBinaryLiteralLength() throws SQLException
1123 return 0; // no limit
1127 * What is the maximum length for a character literal
1128 * I suppose it is 8190 (8192 - 2 for the quotes)
1130 * @return the max literal length
1131 * @exception SQLException if a database access error occurs
1133 public int getMaxCharLiteralLength() throws SQLException
1135 return 0; // no limit
1139 * Whats the limit on column name length. The description of
1140 * pg_class would say '32' (length of pg_class.relname) - we
1141 * should probably do a query for this....but....
1143 * @return the maximum column name length
1144 * @exception SQLException if a database access error occurs
1146 public int getMaxColumnNameLength() throws SQLException
1152 * What is the maximum number of columns in a "GROUP BY" clause?
1154 * @return the max number of columns
1155 * @exception SQLException if a database access error occurs
1157 public int getMaxColumnsInGroupBy() throws SQLException
1159 return 0; // no limit
1163 * What's the maximum number of columns allowed in an index?
1164 * 6.0 only allowed one column, but 6.1 introduced multi-column
1165 * indices, so, theoretically, its all of them.
1167 * @return max number of columns
1168 * @exception SQLException if a database access error occurs
1170 public int getMaxColumnsInIndex() throws SQLException
1172 return getMaxColumnsInTable();
1176 * What's the maximum number of columns in an "ORDER BY clause?
1178 * @return the max columns
1179 * @exception SQLException if a database access error occurs
1181 public int getMaxColumnsInOrderBy() throws SQLException
1183 return 0; // no limit
1187 * What is the maximum number of columns in a "SELECT" list?
1189 * @return the max columns
1190 * @exception SQLException if a database access error occurs
1192 public int getMaxColumnsInSelect() throws SQLException
1194 return 0; // no limit
1198 * What is the maximum number of columns in a table? From the
1199 * CREATE TABLE reference page...
1201 * <p>"The new class is created as a heap with no initial data. A
1202 * class can have no more than 1600 attributes (realistically,
1203 * this is limited by the fact that tuple sizes must be less than
1206 * @return the max columns
1207 * @exception SQLException if a database access error occurs
1209 public int getMaxColumnsInTable() throws SQLException
1215 * How many active connection can we have at a time to this
1216 * database? Well, since it depends on postmaster, which just
1217 * does a listen() followed by an accept() and fork(), its
1218 * basically very high. Unless the system runs out of processes,
1219 * it can be 65535 (the number of aux. ports on a TCP/IP system).
1220 * I will return 8192 since that is what even the largest system
1221 * can realistically handle,
1223 * @return the maximum number of connections
1224 * @exception SQLException if a database access error occurs
1226 public int getMaxConnections() throws SQLException
1232 * What is the maximum cursor name length (the same as all
1233 * the other F***** identifiers!)
1235 * @return max cursor name length in bytes
1236 * @exception SQLException if a database access error occurs
1238 public int getMaxCursorNameLength() throws SQLException
1244 * Retrieves the maximum number of bytes for an index, including all
1245 * of the parts of the index.
1247 * @return max index length in bytes, which includes the composite
1248 * of all the constituent parts of the index; a result of zero means
1249 * that there is no limit or the limit is not known
1250 * @exception SQLException if a database access error occurs
1252 public int getMaxIndexLength() throws SQLException
1254 return 0; // no limit (larger than an int anyway)
1257 public int getMaxSchemaNameLength() throws SQLException
1259 // XXX-Not Implemented
1264 * What is the maximum length of a procedure name?
1265 * (length of pg_proc.proname used) - again, I really
1266 * should do a query here to get it.
1268 * @return the max name length in bytes
1269 * @exception SQLException if a database access error occurs
1271 public int getMaxProcedureNameLength() throws SQLException
1276 public int getMaxCatalogNameLength() throws SQLException
1278 // XXX-Not Implemented
1283 * What is the maximum length of a single row?
1285 * @return max row size in bytes
1286 * @exception SQLException if a database access error occurs
1288 public int getMaxRowSize() throws SQLException
1290 if (connection.haveMinimumServerVersion("7.1"))
1291 return 1073741824; // 1 GB
1293 return 8192; // XXX could be altered
1297 * Did getMaxRowSize() include LONGVARCHAR and LONGVARBINARY
1298 * blobs? We don't handle blobs yet
1300 * @return true if so
1301 * @exception SQLException if a database access error occurs
1303 public boolean doesMaxRowSizeIncludeBlobs() throws SQLException
1309 * What is the maximum length of a SQL statement?
1311 * @return max length in bytes
1312 * @exception SQLException if a database access error occurs
1314 public int getMaxStatementLength() throws SQLException
1316 if (connection.haveMinimumServerVersion("7.0"))
1317 return 0; // actually whatever fits in size_t
1323 * How many active statements can we have open at one time to
1324 * this database? Basically, since each Statement downloads
1325 * the results as the query is executed, we can have many. However,
1326 * we can only really have one statement per connection going
1327 * at once (since they are executed serially) - so we return
1330 * @return the maximum
1331 * @exception SQLException if a database access error occurs
1333 public int getMaxStatements() throws SQLException
1339 * What is the maximum length of a table name? This was found
1340 * from pg_class.relname length
1342 * @return max name length in bytes
1343 * @exception SQLException if a database access error occurs
1345 public int getMaxTableNameLength() throws SQLException
1351 * What is the maximum number of tables that can be specified
1354 * @return the maximum
1355 * @exception SQLException if a database access error occurs
1357 public int getMaxTablesInSelect() throws SQLException
1359 return 0; // no limit
1363 * What is the maximum length of a user name? Well, we generally
1364 * use UNIX like user names in PostgreSQL, so I think this would
1365 * be 8. However, showing the schema for pg_user shows a length
1366 * for username of 32.
1368 * @return the max name length in bytes
1369 * @exception SQLException if a database access error occurs
1371 public int getMaxUserNameLength() throws SQLException
1378 * What is the database's default transaction isolation level? We
1379 * do not support this, so all transactions are SERIALIZABLE.
1381 * @return the default isolation level
1382 * @exception SQLException if a database access error occurs
1385 public int getDefaultTransactionIsolation() throws SQLException
1387 return Connection.TRANSACTION_READ_COMMITTED;
1391 * Are transactions supported? If not, commit and rollback are noops
1392 * and the isolation level is TRANSACTION_NONE. We do support
1395 * @return true if transactions are supported
1396 * @exception SQLException if a database access error occurs
1398 public boolean supportsTransactions() throws SQLException
1404 * Does the database support the given transaction isolation level?
1405 * We only support TRANSACTION_SERIALIZABLE and TRANSACTION_READ_COMMITTED
1407 * @param level the values are defined in java.sql.Connection
1408 * @return true if so
1409 * @exception SQLException if a database access error occurs
1412 public boolean supportsTransactionIsolationLevel(int level) throws SQLException
1414 if (level == Connection.TRANSACTION_SERIALIZABLE ||
1415 level == Connection.TRANSACTION_READ_COMMITTED)
1422 * Are both data definition and data manipulation transactions
1425 * @return true if so
1426 * @exception SQLException if a database access error occurs
1428 public boolean supportsDataDefinitionAndDataManipulationTransactions() throws SQLException
1434 * Are only data manipulation statements withing a transaction
1437 * @return true if so
1438 * @exception SQLException if a database access error occurs
1440 public boolean supportsDataManipulationTransactionsOnly() throws SQLException
1446 * Does a data definition statement within a transaction force
1447 * the transaction to commit? I think this means something like:
1450 * CREATE TABLE T (A INT);
1451 * INSERT INTO T (A) VALUES (2);
1453 * UPDATE T SET A = A + 1;
1454 * CREATE TABLE X (A INT);
1455 * SELECT A FROM T INTO X;
1459 * does the CREATE TABLE call cause a commit? The answer is no.
1461 * @return true if so
1462 * @exception SQLException if a database access error occurs
1464 public boolean dataDefinitionCausesTransactionCommit() throws SQLException
1470 * Is a data definition statement within a transaction ignored?
1471 * It seems to be (from experiment in previous method)
1473 * @return true if so
1474 * @exception SQLException if a database access error occurs
1476 public boolean dataDefinitionIgnoredInTransactions() throws SQLException
1482 * Get a description of stored procedures available in a catalog
1484 * <p>Only procedure descriptions matching the schema and procedure
1485 * name criteria are returned. They are ordered by PROCEDURE_SCHEM
1486 * and PROCEDURE_NAME
1488 * <p>Each procedure description has the following columns:
1490 * <li><b>PROCEDURE_CAT</b> String => procedure catalog (may be null)
1491 * <li><b>PROCEDURE_SCHEM</b> String => procedure schema (may be null)
1492 * <li><b>PROCEDURE_NAME</b> String => procedure name
1493 * <li><b>Field 4</b> reserved (make it null)
1494 * <li><b>Field 5</b> reserved (make it null)
1495 * <li><b>Field 6</b> reserved (make it null)
1496 * <li><b>REMARKS</b> String => explanatory comment on the procedure
1497 * <li><b>PROCEDURE_TYPE</b> short => kind of procedure
1499 * <li> procedureResultUnknown - May return a result
1500 * <li> procedureNoResult - Does not return a result
1501 * <li> procedureReturnsResult - Returns a result
1505 * @param catalog - a catalog name; "" retrieves those without a
1506 * catalog; null means drop catalog name from criteria
1507 * @param schemaParrern - a schema name pattern; "" retrieves those
1508 * without a schema - we ignore this parameter
1509 * @param procedureNamePattern - a procedure name pattern
1510 * @return ResultSet - each row is a procedure description
1511 * @exception SQLException if a database access error occurs
1513 public java.sql.ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern) throws SQLException
1515 // the field descriptors for the new ResultSet
1516 Field f[] = new Field[8];
1517 java.sql.ResultSet r; // ResultSet for the SQL query that we need to do
1518 Vector v = new Vector(); // The new ResultSet tuple stuff
1520 f[0] = new Field(connection, "PROCEDURE_CAT", iVarcharOid, 32);
1521 f[1] = new Field(connection, "PROCEDURE_SCHEM", iVarcharOid, 32);
1522 f[2] = new Field(connection, "PROCEDURE_NAME", iVarcharOid, 32);
1523 f[3] = f[4] = f[5] = new Field(connection,"reserved",iVarcharOid,32); // null; // reserved, must be null for now
1524 f[6] = new Field(connection, "REMARKS", iVarcharOid, 8192);
1525 f[7] = new Field(connection, "PROCEDURE_TYPE", iInt2Oid, 2);
1527 // If the pattern is null, then set it to the default
1528 if(procedureNamePattern==null)
1529 procedureNamePattern="%";
1531 r = connection.ExecSQL("select proname, proretset from pg_proc where proname like '"+procedureNamePattern.toLowerCase()+"' order by proname");
1535 byte[][] tuple = new byte[8][0];
1537 tuple[0] = null; // Catalog name
1538 tuple[1] = null; // Schema name
1539 tuple[2] = r.getBytes(1); // Procedure name
1540 tuple[3] = tuple[4] = tuple[5] = null; // Reserved
1541 tuple[6] = null; // Remarks
1543 if (r.getBoolean(2))
1544 tuple[7] = Integer.toString(java.sql.DatabaseMetaData.procedureReturnsResult).getBytes();
1546 tuple[7] = Integer.toString(java.sql.DatabaseMetaData.procedureNoResult).getBytes();
1548 v.addElement(tuple);
1550 return new ResultSet(connection, f, v, "OK", 1);
1554 * Get a description of a catalog's stored procedure parameters
1555 * and result columns.
1557 * <p>Only descriptions matching the schema, procedure and parameter
1558 * name criteria are returned. They are ordered by PROCEDURE_SCHEM
1559 * and PROCEDURE_NAME. Within this, the return value, if any, is
1560 * first. Next are the parameter descriptions in call order. The
1561 * column descriptions follow in column number order.
1563 * <p>Each row in the ResultSet is a parameter description or column
1564 * description with the following fields:
1566 * <li><b>PROCEDURE_CAT</b> String => procedure catalog (may be null)
1567 * <li><b>PROCEDURE_SCHE</b>M String => procedure schema (may be null)
1568 * <li><b>PROCEDURE_NAME</b> String => procedure name
1569 * <li><b>COLUMN_NAME</b> String => column/parameter name
1570 * <li><b>COLUMN_TYPE</b> Short => kind of column/parameter:
1571 * <ul><li>procedureColumnUnknown - nobody knows
1572 * <li>procedureColumnIn - IN parameter
1573 * <li>procedureColumnInOut - INOUT parameter
1574 * <li>procedureColumnOut - OUT parameter
1575 * <li>procedureColumnReturn - procedure return value
1576 * <li>procedureColumnResult - result column in ResultSet
1578 * <li><b>DATA_TYPE</b> short => SQL type from java.sql.Types
1579 * <li><b>TYPE_NAME</b> String => SQL type name
1580 * <li><b>PRECISION</b> int => precision
1581 * <li><b>LENGTH</b> int => length in bytes of data
1582 * <li><b>SCALE</b> short => scale
1583 * <li><b>RADIX</b> short => radix
1584 * <li><b>NULLABLE</b> short => can it contain NULL?
1585 * <ul><li>procedureNoNulls - does not allow NULL values
1586 * <li>procedureNullable - allows NULL values
1587 * <li>procedureNullableUnknown - nullability unknown
1588 * <li><b>REMARKS</b> String => comment describing parameter/column
1590 * @param catalog This is ignored in org.postgresql, advise this is set to null
1591 * @param schemaPattern This is ignored in org.postgresql, advise this is set to null
1592 * @param procedureNamePattern a procedure name pattern
1593 * @param columnNamePattern a column name pattern
1594 * @return each row is a stored procedure parameter or column description
1595 * @exception SQLException if a database-access error occurs
1596 * @see #getSearchStringEscape
1598 // Implementation note: This is required for Borland's JBuilder to work
1599 public java.sql.ResultSet getProcedureColumns(String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern) throws SQLException
1601 if(procedureNamePattern==null)
1602 procedureNamePattern="%";
1604 if(columnNamePattern==null)
1605 columnNamePattern="%";
1607 // for now, this returns an empty result set.
1608 Field f[] = new Field[13];
1609 ResultSet r; // ResultSet for the SQL query that we need to do
1610 Vector v = new Vector(); // The new ResultSet tuple stuff
1612 f[0] = new Field(connection, "PROCEDURE_CAT", iVarcharOid, 32);
1613 f[1] = new Field(connection, "PROCEDURE_SCHEM", iVarcharOid, 32);
1614 f[2] = new Field(connection, "PROCEDURE_NAME", iVarcharOid, 32);
1615 f[3] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
1616 f[4] = new Field(connection, "COLUMN_TYPE", iInt2Oid, 2);
1617 f[5] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
1618 f[6] = new Field(connection, "TYPE_NAME", iVarcharOid, 32);
1619 f[7] = new Field(connection, "PRECISION", iInt4Oid, 4);
1620 f[8] = new Field(connection, "LENGTH", iInt4Oid, 4);
1621 f[9] = new Field(connection, "SCALE", iInt2Oid, 2);
1622 f[10] = new Field(connection, "RADIX", iInt2Oid, 2);
1623 f[11] = new Field(connection, "NULLABLE", iInt2Oid, 2);
1624 f[12] = new Field(connection, "REMARKS", iVarcharOid, 32);
1626 // add query loop here
1628 return new ResultSet(connection, f, v, "OK", 1);
1632 * Get a description of tables available in a catalog.
1634 * <p>Only table descriptions matching the catalog, schema, table
1635 * name and type criteria are returned. They are ordered by
1636 * TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
1638 * <p>Each table description has the following columns:
1641 * <li><b>TABLE_CAT</b> String => table catalog (may be null)
1642 * <li><b>TABLE_SCHEM</b> String => table schema (may be null)
1643 * <li><b>TABLE_NAME</b> String => table name
1644 * <li><b>TABLE_TYPE</b> String => table type. Typical types are "TABLE",
1645 * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL
1646 * TEMPORARY", "ALIAS", "SYNONYM".
1647 * <li><b>REMARKS</b> String => explanatory comment on the table
1650 * <p>The valid values for the types parameter are:
1651 * "TABLE", "INDEX", "SEQUENCE", "SYSTEM TABLE" and "SYSTEM INDEX"
1653 * @param catalog a catalog name; For org.postgresql, this is ignored, and
1654 * should be set to null
1655 * @param schemaPattern a schema name pattern; For org.postgresql, this is ignored, and
1656 * should be set to null
1657 * @param tableNamePattern a table name pattern. For all tables this should be "%"
1658 * @param types a list of table types to include; null returns
1660 * @return each row is a table description
1661 * @exception SQLException if a database-access error occurs.
1663 public java.sql.ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[]) throws SQLException
1665 // Handle default value for types
1667 types = defaultTableTypes;
1669 if(tableNamePattern==null)
1670 tableNamePattern="%";
1672 // the field descriptors for the new ResultSet
1673 Field f[] = new Field[5];
1674 java.sql.ResultSet r; // ResultSet for the SQL query that we need to do
1675 Vector v = new Vector(); // The new ResultSet tuple stuff
1677 f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
1678 f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
1679 f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
1680 f[3] = new Field(connection, "TABLE_TYPE", iVarcharOid, 32);
1681 f[4] = new Field(connection, "REMARKS", iVarcharOid, 32);
1683 // Now form the query
1684 StringBuffer sql = new StringBuffer("select relname,oid,relkind from pg_class where (");
1686 boolean notFirst=false;
1687 for(int i=0;i<types.length;i++) {
1688 for(int j=0;j<getTableTypes.length;j++)
1689 if(getTableTypes[j][0].equals(types[i])) {
1692 sql.append(getTableTypes[j][1]);
1697 // Added by Stefan Andreasen <stefan@linux.kapow.dk>
1698 // Now take the pattern into account
1699 sql.append(") and relname like '");
1700 sql.append(tableNamePattern.toLowerCase());
1701 sql.append("' order by relkind, relname");
1703 // Now run the query
1704 r = connection.ExecSQL(sql.toString());
1708 byte[][] tuple = new byte[5][0];
1710 // Fetch the description for the table (if any)
1711 String getDescriptionStatement =
1712 connection.haveMinimumServerVersion("7.2") ?
1713 "select obj_description("+r.getInt(2)+",'pg_class')" :
1714 "select description from pg_description where objoid=" + r.getInt(2);
1716 java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
1718 byte remarks[] = null;
1720 if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
1722 remarks = dr.getBytes(1);
1727 switch (r.getBytes(3)[0]) {
1735 relKind = "SEQUENCE";
1744 tuple[0] = null; // Catalog name
1745 tuple[1] = null; // Schema name
1746 tuple[2] = r.getBytes(1); // Table name
1747 tuple[3] = (relKind==null) ? null : relKind.getBytes(); // Table type
1748 tuple[4] = remarks; // Remarks
1749 v.addElement(tuple);
1752 return new ResultSet(connection, f, v, "OK", 1);
1755 // This array contains the valid values for the types argument
1758 // Each supported type consists of it's name, and the sql where
1759 // clause to retrieve that value.
1761 // IMPORTANT: the query must be enclosed in ( )
1762 private static final String getTableTypes[][] = {
1763 {"TABLE", "(relkind='r' and relhasrules='f' and relname !~ '^pg_' and relname !~ '^xinv')"},
1764 {"VIEW", "(relkind='v' and relname !~ '^pg_')"},
1765 {"INDEX", "(relkind='i' and relname !~ '^pg_')"},
1766 {"SEQUENCE", "(relkind='S' and relname !~ '^pg_')"},
1767 {"SYSTEM TABLE", "(relkind='r' and relname ~ '^pg_')"},
1768 {"SYSTEM INDEX", "(relkind='i' and relname ~ '^pg_')"}
1771 // These are the default tables, used when NULL is passed to getTables
1772 // The choice of these provide the same behaviour as psql's \d
1773 private static final String defaultTableTypes[] = {
1774 "TABLE","VIEW","INDEX","SEQUENCE"
1778 * Get the schema names available in this database. The results
1779 * are ordered by schema name.
1781 * <P>The schema column is:
1783 * <LI><B>TABLE_SCHEM</B> String => schema name
1786 * @return ResultSet each row has a single String column that is a
1789 public java.sql.ResultSet getSchemas() throws SQLException
1791 // We don't use schemas, so we simply return a single schema name "".
1793 Field f[] = new Field[1];
1794 Vector v = new Vector();
1795 byte[][] tuple = new byte[1][0];
1796 f[0] = new Field(connection,"TABLE_SCHEM",iVarcharOid,32);
1797 tuple[0] = "".getBytes();
1798 v.addElement(tuple);
1799 return new ResultSet(connection,f,v,"OK",1);
1803 * Get the catalog names available in this database. The results
1804 * are ordered by catalog name.
1806 * <P>The catalog column is:
1808 * <LI><B>TABLE_CAT</B> String => catalog name
1811 * @return ResultSet each row has a single String column that is a
1814 public java.sql.ResultSet getCatalogs() throws SQLException
1816 return connection.createStatement().executeQuery("select datname as TABLE_CAT from pg_database;");
1820 * Get the table types available in this database. The results
1821 * are ordered by table type.
1823 * <P>The table type is:
1825 * <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
1826 * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
1827 * "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
1830 * @return ResultSet each row has a single String column that is a
1833 public java.sql.ResultSet getTableTypes() throws SQLException
1835 Field f[] = new Field[1];
1836 Vector v = new Vector();
1837 f[0] = new Field(connection,new String("TABLE_TYPE"),iVarcharOid,32);
1838 for(int i=0;i<getTableTypes.length;i++) {
1839 byte[][] tuple = new byte[1][0];
1840 tuple[0] = getTableTypes[i][0].getBytes();
1841 v.addElement(tuple);
1843 return new ResultSet(connection,f,v,"OK",1);
1847 * Get a description of table columns available in a catalog.
1849 * <P>Only column descriptions matching the catalog, schema, table
1850 * and column name criteria are returned. They are ordered by
1851 * TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
1853 * <P>Each column description has the following columns:
1855 * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
1856 * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
1857 * <LI><B>TABLE_NAME</B> String => table name
1858 * <LI><B>COLUMN_NAME</B> String => column name
1859 * <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
1860 * <LI><B>TYPE_NAME</B> String => Data source dependent type name
1861 * <LI><B>COLUMN_SIZE</B> int => column size. For char or date
1862 * types this is the maximum number of characters, for numeric or
1863 * decimal types this is precision.
1864 * <LI><B>BUFFER_LENGTH</B> is not used.
1865 * <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
1866 * <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
1867 * <LI><B>NULLABLE</B> int => is NULL allowed?
1869 * <LI> columnNoNulls - might not allow NULL values
1870 * <LI> columnNullable - definitely allows NULL values
1871 * <LI> columnNullableUnknown - nullability unknown
1873 * <LI><B>REMARKS</B> String => comment describing column (may be null)
1874 * <LI><B>COLUMN_DEF</B> String => default value (may be null)
1875 * <LI><B>SQL_DATA_TYPE</B> int => unused
1876 * <LI><B>SQL_DATETIME_SUB</B> int => unused
1877 * <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
1878 * maximum number of bytes in the column
1879 * <LI><B>ORDINAL_POSITION</B> int => index of column in table
1881 * <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
1882 * does not allow NULL values; "YES" means the column might
1883 * allow NULL values. An empty string means nobody knows.
1886 * @param catalog a catalog name; "" retrieves those without a catalog
1887 * @param schemaPattern a schema name pattern; "" retrieves those
1889 * @param tableNamePattern a table name pattern
1890 * @param columnNamePattern a column name pattern
1891 * @return ResultSet each row is a column description
1892 * @see #getSearchStringEscape
1894 public java.sql.ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException
1896 // the field descriptors for the new ResultSet
1897 Field f[] = new Field[18];
1898 java.sql.ResultSet r; // ResultSet for the SQL query that we need to do
1899 Vector v = new Vector(); // The new ResultSet tuple stuff
1901 f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
1902 f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
1903 f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
1904 f[3] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
1905 f[4] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
1906 f[5] = new Field(connection, "TYPE_NAME", iVarcharOid, 32);
1907 f[6] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
1908 f[7] = new Field(connection, "BUFFER_LENGTH", iVarcharOid, 32);
1909 f[8] = new Field(connection, "DECIMAL_DIGITS", iInt4Oid, 4);
1910 f[9] = new Field(connection, "NUM_PREC_RADIX", iInt4Oid, 4);
1911 f[10] = new Field(connection, "NULLABLE", iInt4Oid, 4);
1912 f[11] = new Field(connection, "REMARKS", iVarcharOid, 32);
1913 f[12] = new Field(connection, "COLUMN_DEF", iVarcharOid, 32);
1914 f[13] = new Field(connection, "SQL_DATA_TYPE", iInt4Oid, 4);
1915 f[14] = new Field(connection, "SQL_DATETIME_SUB", iInt4Oid, 4);
1916 f[15] = new Field(connection, "CHAR_OCTET_LENGTH", iVarcharOid, 32);
1917 f[16] = new Field(connection, "ORDINAL_POSITION", iInt4Oid,4);
1918 f[17] = new Field(connection, "IS_NULLABLE", iVarcharOid, 32);
1920 // Added by Stefan Andreasen <stefan@linux.kapow.dk>
1921 // If the pattern are null then set them to %
1922 if (tableNamePattern == null) tableNamePattern="%";
1923 if (columnNamePattern == null) columnNamePattern="%";
1925 // Now form the query
1928 (connection.haveMinimumServerVersion("7.2") ? "a.attrelid" : "a.oid") +
1929 ",c.relname,a.attname,a.atttypid," +
1930 "a.attnum,a.attnotnull,a.attlen,a.atttypmod,d.adsrc from pg_class c," +
1931 "pg_attribute a,pg_attrdef d where a.attrelid=c.oid and " +
1932 "c.relname like '"+tableNamePattern.toLowerCase()+"' and " +
1933 "a.attname like '"+columnNamePattern.toLowerCase()+"' and " +
1934 "a.attnum>0 and c.oid=d.adrelid and d.adnum=a.attnum " +
1935 "order by c.relname,a.attnum";
1937 r = connection.ExecSQL(query);
1940 byte[][] tuple = new byte[18][0];
1942 // Fetch the description for the table (if any)
1943 String getDescriptionStatement =
1944 connection.haveMinimumServerVersion("7.2") ?
1945 "select col_description(" + r.getInt(1) + "," + r.getInt(5) + ")" :
1946 "select description from pg_description where objoid=" + r.getInt(1);
1948 java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
1950 if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
1952 tuple[11] = dr.getBytes(1);
1957 tuple[0] = "".getBytes(); // Catalog name
1958 tuple[1] = "".getBytes(); // Schema name
1959 tuple[2] = r.getBytes(2); // Table name
1960 tuple[3] = r.getBytes(3); // Column name
1962 dr = connection.ExecSQL("select typname from pg_type where oid = "+r.getString(4));
1964 String typname=dr.getString(1);
1966 tuple[4] = Integer.toString(connection.getSQLType(typname)).getBytes(); // Data type
1967 tuple[5] = typname.getBytes(); // Type name
1970 // Looking at the psql source,
1971 // I think the length of a varchar as specified when the table was created
1972 // should be extracted from atttypmod which contains this length + sizeof(int32)
1973 if (typname.equals("bpchar") || typname.equals("varchar")) {
1974 int atttypmod = r.getInt(8);
1975 tuple[6] = Integer.toString(atttypmod != -1 ? atttypmod - VARHDRSZ : 0).getBytes();
1977 tuple[6] = r.getBytes(7);
1979 tuple[7] = null; // Buffer length
1981 tuple[8] = "0".getBytes(); // Decimal Digits - how to get this?
1982 tuple[9] = "10".getBytes(); // Num Prec Radix - assume decimal
1984 // tuple[10] is below
1985 // tuple[11] is above
1987 tuple[12] = r.getBytes(9); // column default
1989 tuple[13] = null; // sql data type (unused)
1990 tuple[14] = null; // sql datetime sub (unused)
1992 tuple[15] = tuple[6]; // char octet length
1994 tuple[16] = r.getBytes(5); // ordinal position
1996 String nullFlag = r.getString(6);
1997 tuple[10] = Integer.toString(nullFlag.equals("f")?java.sql.DatabaseMetaData.columnNullable:java.sql.DatabaseMetaData.columnNoNulls).getBytes(); // Nullable
1998 tuple[17] = (nullFlag.equals("f")?"YES":"NO").getBytes(); // is nullable
2000 v.addElement(tuple);
2003 return new ResultSet(connection, f, v, "OK", 1);
2006 * Get a description of the access rights for a table's columns.
2008 * <P>Only privileges matching the column name criteria are
2009 * returned. They are ordered by COLUMN_NAME and PRIVILEGE.
2011 * <P>Each privilige description has the following columns:
2013 * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
2014 * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
2015 * <LI><B>TABLE_NAME</B> String => table name
2016 * <LI><B>COLUMN_NAME</B> String => column name
2017 * <LI><B>GRANTOR</B> => grantor of access (may be null)
2018 * <LI><B>GRANTEE</B> String => grantee of access
2019 * <LI><B>PRIVILEGE</B> String => name of access (SELECT,
2020 * INSERT, UPDATE, REFRENCES, ...)
2021 * <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
2022 * to grant to others; "NO" if not; null if unknown
2025 * @param catalog a catalog name; "" retrieves those without a catalog
2026 * @param schema a schema name; "" retrieves those without a schema
2027 * @param table a table name
2028 * @param columnNamePattern a column name pattern
2029 * @return ResultSet each row is a column privilege description
2030 * @see #getSearchStringEscape
2032 public java.sql.ResultSet getColumnPrivileges(String catalog, String schema, String table, String columnNamePattern) throws SQLException
2034 Field f[] = new Field[8];
2035 Vector v = new Vector();
2040 if(columnNamePattern==null)
2041 columnNamePattern="%";
2043 columnNamePattern=columnNamePattern.toLowerCase();
2045 f[0] = new Field(connection,"TABLE_CAT",iVarcharOid,32);
2046 f[1] = new Field(connection,"TABLE_SCHEM",iVarcharOid,32);
2047 f[2] = new Field(connection,"TABLE_NAME",iVarcharOid,32);
2048 f[3] = new Field(connection,"COLUMN_NAME",iVarcharOid,32);
2049 f[4] = new Field(connection,"GRANTOR",iVarcharOid,32);
2050 f[5] = new Field(connection,"GRANTEE",iVarcharOid,32);
2051 f[6] = new Field(connection,"PRIVILEGE",iVarcharOid,32);
2052 f[7] = new Field(connection,"IS_GRANTABLE",iVarcharOid,32);
2054 // This is taken direct from the psql source
2055 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");
2057 byte[][] tuple = new byte[8][0];
2058 tuple[0] = tuple[1]= "".getBytes();
2059 DriverManager.println("relname=\""+r.getString(1)+"\" relacl=\""+r.getString(2)+"\"");
2061 // For now, don't add to the result as relacl needs to be processed.
2062 //v.addElement(tuple);
2065 return new ResultSet(connection,f,v,"OK",1);
2069 * Get a description of the access rights for each table available
2072 * This method is currently unimplemented.
2074 * <P>Only privileges matching the schema and table name
2075 * criteria are returned. They are ordered by TABLE_SCHEM,
2076 * TABLE_NAME, and PRIVILEGE.
2078 * <P>Each privilige description has the following columns:
2080 * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
2081 * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
2082 * <LI><B>TABLE_NAME</B> String => table name
2083 * <LI><B>COLUMN_NAME</B> String => column name
2084 * <LI><B>GRANTOR</B> => grantor of access (may be null)
2085 * <LI><B>GRANTEE</B> String => grantee of access
2086 * <LI><B>PRIVILEGE</B> String => name of access (SELECT,
2087 * INSERT, UPDATE, REFRENCES, ...)
2088 * <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
2089 * to grant to others; "NO" if not; null if unknown
2092 * @param catalog a catalog name; "" retrieves those without a catalog
2093 * @param schemaPattern a schema name pattern; "" retrieves those
2095 * @param tableNamePattern a table name pattern
2096 * @return ResultSet each row is a table privilege description
2097 * @see #getSearchStringEscape
2099 public java.sql.ResultSet getTablePrivileges(String catalog, String schemaPattern, String tableNamePattern) throws SQLException
2101 throw org.postgresql.Driver.notImplemented();
2105 * Get a description of a table's optimal set of columns that
2106 * uniquely identifies a row. They are ordered by SCOPE.
2108 * <P>Each column description has the following columns:
2110 * <LI><B>SCOPE</B> short => actual scope of result
2112 * <LI> bestRowTemporary - very temporary, while using row
2113 * <LI> bestRowTransaction - valid for remainder of current transaction
2114 * <LI> bestRowSession - valid for remainder of current session
2116 * <LI><B>COLUMN_NAME</B> String => column name
2117 * <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
2118 * <LI><B>TYPE_NAME</B> String => Data source dependent type name
2119 * <LI><B>COLUMN_SIZE</B> int => precision
2120 * <LI><B>BUFFER_LENGTH</B> int => not used
2121 * <LI><B>DECIMAL_DIGITS</B> short => scale
2122 * <LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column
2123 * like an Oracle ROWID
2125 * <LI> bestRowUnknown - may or may not be pseudo column
2126 * <LI> bestRowNotPseudo - is NOT a pseudo column
2127 * <LI> bestRowPseudo - is a pseudo column
2131 * @param catalog a catalog name; "" retrieves those without a catalog
2132 * @param schema a schema name; "" retrieves those without a schema
2133 * @param table a table name
2134 * @param scope the scope of interest; use same values as SCOPE
2135 * @param nullable include columns that are nullable?
2136 * @return ResultSet each row is a column description
2138 // Implementation note: This is required for Borland's JBuilder to work
2139 public java.sql.ResultSet getBestRowIdentifier(String catalog, String schema, String table, int scope, boolean nullable) throws SQLException
2141 // for now, this returns an empty result set.
2142 Field f[] = new Field[8];
2143 ResultSet r; // ResultSet for the SQL query that we need to do
2144 Vector v = new Vector(); // The new ResultSet tuple stuff
2146 f[0] = new Field(connection, "SCOPE", iInt2Oid, 2);
2147 f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
2148 f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
2149 f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, 32);
2150 f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
2151 f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4);
2152 f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2);
2153 f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2);
2155 return new ResultSet(connection, f, v, "OK", 1);
2159 * Get a description of a table's columns that are automatically
2160 * updated when any value in a row is updated. They are
2163 * This method is currently unimplemented.
2165 * <P>Each column description has the following columns:
2167 * <LI><B>SCOPE</B> short => is not used
2168 * <LI><B>COLUMN_NAME</B> String => column name
2169 * <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
2170 * <LI><B>TYPE_NAME</B> String => Data source dependent type name
2171 * <LI><B>COLUMN_SIZE</B> int => precision
2172 * <LI><B>BUFFER_LENGTH</B> int => length of column value in bytes
2173 * <LI><B>DECIMAL_DIGITS</B> short => scale
2174 * <LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column
2175 * like an Oracle ROWID
2177 * <LI> versionColumnUnknown - may or may not be pseudo column
2178 * <LI> versionColumnNotPseudo - is NOT a pseudo column
2179 * <LI> versionColumnPseudo - is a pseudo column
2183 * @param catalog a catalog name; "" retrieves those without a catalog
2184 * @param schema a schema name; "" retrieves those without a schema
2185 * @param table a table name
2186 * @return ResultSet each row is a column description
2188 public java.sql.ResultSet getVersionColumns(String catalog, String schema, String table) throws SQLException
2190 throw org.postgresql.Driver.notImplemented();
2194 * Get a description of a table's primary key columns. They
2195 * are ordered by COLUMN_NAME.
2197 * <P>Each column description has the following columns:
2199 * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
2200 * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
2201 * <LI><B>TABLE_NAME</B> String => table name
2202 * <LI><B>COLUMN_NAME</B> String => column name
2203 * <LI><B>KEY_SEQ</B> short => sequence number within primary key
2204 * <LI><B>PK_NAME</B> String => primary key name (may be null)
2207 * @param catalog a catalog name; "" retrieves those without a catalog
2208 * @param schema a schema name pattern; "" retrieves those
2210 * @param table a table name
2211 * @return ResultSet each row is a primary key column description
2213 public java.sql.ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException
2215 return connection.createStatement().executeQuery("SELECT " +
2216 "'' as TABLE_CAT," +
2217 "'' AS TABLE_SCHEM," +
2218 "bc.relname AS TABLE_NAME," +
2219 "a.attname AS COLUMN_NAME," +
2220 "a.attnum as KEY_SEQ,"+
2221 "ic.relname as PK_NAME " +
2222 " FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a" +
2223 " WHERE bc.relkind = 'r' " + // -- not indices
2224 " and upper(bc.relname) = upper('"+table+"')" +
2225 " and i.indrelid = bc.oid" +
2226 " and i.indexrelid = ic.oid" +
2227 " and ic.oid = a.attrelid" +
2228 " and i.indisprimary='t' " +
2229 " ORDER BY table_name, pk_name, key_seq"
2233 private void importLoop(Vector tuples, java.sql.ResultSet keyRelation) throws SQLException {
2235 String origTable=null, primTable=new String(""), schema;
2237 Vector v=new Vector();
2239 s=keyRelation.getString(1);
2241 //System.out.println(s);
2244 s=s.substring(s.indexOf("\\000")+4);
2245 if (s.compareTo("")==0) {
2246 //System.out.println();
2249 s2=s.substring(0,s.indexOf("\\000"));
2265 java.sql.ResultSet rstmp=connection.ExecSQL("select * from "+origTable+" where 1=0");
2266 java.sql.ResultSetMetaData origCols=rstmp.getMetaData();
2269 // Vector tuples=new Vector();
2272 // the foreign keys are only on even positions in the Vector.
2273 for (i=0;i<v.size();i+=2) {
2274 stmp=(String)v.elementAt(i);
2276 for (int j=1;j<=origCols.getColumnCount();j++) {
2277 if (stmp.compareTo(origCols.getColumnName(j))==0) {
2278 tuple=new byte[14][0];
2280 for (int k=0;k<14;k++)
2284 tuple[2]=primTable.getBytes();
2286 stmp=(String)v.elementAt(i+1);
2287 tuple[3]=stmp.getBytes();
2289 tuple[6]=origTable.getBytes();
2291 tuple[7]=origCols.getColumnName(j).getBytes();
2293 tuple[8]=Integer.toString(j).getBytes();
2295 tuples.addElement(tuple);
2297 //System.out.println(origCols.getColumnName(j)+
2298 //": "+j+" -> "+primTable+": "+
2299 //(String)v.elementAt(i+1));
2309 * Get a description of the primary key columns that are
2310 * referenced by a table's foreign key columns (the primary keys
2311 * imported by a table). They are ordered by PKTABLE_CAT,
2312 * PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.
2314 * <P>Each primary key column description has the following columns:
2316 * <LI><B>PKTABLE_CAT</B> String => primary key table catalog
2317 * being imported (may be null)
2318 * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema
2319 * being imported (may be null)
2320 * <LI><B>PKTABLE_NAME</B> String => primary key table name
2322 * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
2324 * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
2325 * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
2326 * <LI><B>FKTABLE_NAME</B> String => foreign key table name
2327 * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
2328 * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
2329 * <LI><B>UPDATE_RULE</B> short => What happens to
2330 * foreign key when primary is updated:
2332 * <LI> importedKeyCascade - change imported key to agree
2333 * with primary key update
2334 * <LI> importedKeyRestrict - do not allow update of primary
2335 * key if it has been imported
2336 * <LI> importedKeySetNull - change imported key to NULL if
2337 * its primary key has been updated
2339 * <LI><B>DELETE_RULE</B> short => What happens to
2340 * the foreign key when primary is deleted.
2342 * <LI> importedKeyCascade - delete rows that import a deleted key
2343 * <LI> importedKeyRestrict - do not allow delete of primary
2344 * key if it has been imported
2345 * <LI> importedKeySetNull - change imported key to NULL if
2346 * its primary key has been deleted
2348 * <LI><B>FK_NAME</B> String => foreign key name (may be null)
2349 * <LI><B>PK_NAME</B> String => primary key name (may be null)
2352 * @param catalog a catalog name; "" retrieves those without a catalog
2353 * @param schema a schema name pattern; "" retrieves those
2355 * @param table a table name
2356 * @return ResultSet each row is a primary key column description
2357 * @see #getExportedKeys
2359 public java.sql.ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException
2361 // Added by Ola Sundell <ola@miranda.org>
2362 // FIXME: error checking galore!
2363 java.sql.ResultSet rsret;
2364 Field f[]=new Field[14];
2367 f[0]=new Field(connection, "PKTABLE_CAT", iVarcharOid, 32);
2368 f[1]=new Field(connection, "PKTABLE_SCHEM", iVarcharOid, 32);
2369 f[2]=new Field(connection, "PKTABLE_NAME", iVarcharOid, 32);
2370 f[3]=new Field(connection, "PKCOLUMN_NAME", iVarcharOid, 32);
2371 f[4]=new Field(connection, "FKTABLE_CAT", iVarcharOid, 32);
2372 f[5]=new Field(connection, "FKTABLE_SCHEM", iVarcharOid, 32);
2373 f[6]=new Field(connection, "FKTABLE_NAME", iVarcharOid, 32);
2374 f[7]=new Field(connection, "FKCOLUMN_NAME", iVarcharOid, 32);
2375 f[8]=new Field(connection, "KEY_SEQ", iInt2Oid, 2);
2376 f[9]=new Field(connection, "UPDATE_RULE", iInt2Oid, 2);
2377 f[10]=new Field(connection, "DELETE_RULE", iInt2Oid, 2);
2378 f[11]=new Field(connection, "FK_NAME", iVarcharOid, 32);
2379 f[12]=new Field(connection, "PK_NAME", iVarcharOid, 32);
2380 f[13]=new Field(connection, "DEFERRABILITY", iInt2Oid, 2);
2382 java.sql.ResultSet rs=connection.ExecSQL("select t.tgargs "+
2383 "from pg_class as c, pg_trigger as t "+
2384 "where c.relname like '"+table+"' and c.relfilenode=t.tgrelid");
2385 Vector tuples=new Vector();
2388 importLoop(tuples,rs);
2391 rsret=new ResultSet(connection, f, tuples, "OK", 1);
2397 * Get a description of a foreign key columns that reference a
2398 * table's primary key columns (the foreign keys exported by a
2399 * table). They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
2400 * FKTABLE_NAME, and KEY_SEQ.
2402 * This method is currently unimplemented.
2404 * <P>Each foreign key column description has the following columns:
2406 * <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
2407 * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
2408 * <LI><B>PKTABLE_NAME</B> String => primary key table name
2409 * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
2410 * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
2411 * being exported (may be null)
2412 * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
2413 * being exported (may be null)
2414 * <LI><B>FKTABLE_NAME</B> String => foreign key table name
2416 * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
2418 * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
2419 * <LI><B>UPDATE_RULE</B> short => What happens to
2420 * foreign key when primary is updated:
2422 * <LI> importedKeyCascade - change imported key to agree
2423 * with primary key update
2424 * <LI> importedKeyRestrict - do not allow update of primary
2425 * key if it has been imported
2426 * <LI> importedKeySetNull - change imported key to NULL if
2427 * its primary key has been updated
2429 * <LI><B>DELETE_RULE</B> short => What happens to
2430 * the foreign key when primary is deleted.
2432 * <LI> importedKeyCascade - delete rows that import a deleted key
2433 * <LI> importedKeyRestrict - do not allow delete of primary
2434 * key if it has been imported
2435 * <LI> importedKeySetNull - change imported key to NULL if
2436 * its primary key has been deleted
2438 * <LI><B>FK_NAME</B> String => foreign key identifier (may be null)
2439 * <LI><B>PK_NAME</B> String => primary key identifier (may be null)
2442 * @param catalog a catalog name; "" retrieves those without a catalog
2443 * @param schema a schema name pattern; "" retrieves those
2445 * @param table a table name
2446 * @return ResultSet each row is a foreign key column description
2447 * @see #getImportedKeys
2449 public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException
2451 throw org.postgresql.Driver.notImplemented();
2455 * Get a description of the foreign key columns in the foreign key
2456 * table that reference the primary key columns of the primary key
2457 * table (describe how one table imports another's key.) This
2458 * should normally return a single foreign key/primary key pair
2459 * (most tables only import a foreign key from a table once.) They
2460 * are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and
2463 * This method is currently unimplemented.
2465 * <P>Each foreign key column description has the following columns:
2467 * <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
2468 * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
2469 * <LI><B>PKTABLE_NAME</B> String => primary key table name
2470 * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
2471 * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
2472 * being exported (may be null)
2473 * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
2474 * being exported (may be null)
2475 * <LI><B>FKTABLE_NAME</B> String => foreign key table name
2477 * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
2479 * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
2480 * <LI><B>UPDATE_RULE</B> short => What happens to
2481 * foreign key when primary is updated:
2483 * <LI> importedKeyCascade - change imported key to agree
2484 * with primary key update
2485 * <LI> importedKeyRestrict - do not allow update of primary
2486 * key if it has been imported
2487 * <LI> importedKeySetNull - change imported key to NULL if
2488 * its primary key has been updated
2490 * <LI><B>DELETE_RULE</B> short => What happens to
2491 * the foreign key when primary is deleted.
2493 * <LI> importedKeyCascade - delete rows that import a deleted key
2494 * <LI> importedKeyRestrict - do not allow delete of primary
2495 * key if it has been imported
2496 * <LI> importedKeySetNull - change imported key to NULL if
2497 * its primary key has been deleted
2499 * <LI><B>FK_NAME</B> String => foreign key identifier (may be null)
2500 * <LI><B>PK_NAME</B> String => primary key identifier (may be null)
2503 * @param catalog a catalog name; "" retrieves those without a catalog
2504 * @param schema a schema name pattern; "" retrieves those
2506 * @param table a table name
2507 * @return ResultSet each row is a foreign key column description
2508 * @see #getImportedKeys
2510 public java.sql.ResultSet getCrossReference(String primaryCatalog, String primarySchema, String primaryTable, String foreignCatalog, String foreignSchema, String foreignTable) throws SQLException
2512 throw org.postgresql.Driver.notImplemented();
2516 * Get a description of all the standard SQL types supported by
2517 * this database. They are ordered by DATA_TYPE and then by how
2518 * closely the data type maps to the corresponding JDBC SQL type.
2520 * <P>Each type description has the following columns:
2522 * <LI><B>TYPE_NAME</B> String => Type name
2523 * <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
2524 * <LI><B>PRECISION</B> int => maximum precision
2525 * <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal
2527 * <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal
2529 * <LI><B>CREATE_PARAMS</B> String => parameters used in creating
2530 * the type (may be null)
2531 * <LI><B>NULLABLE</B> short => can you use NULL for this type?
2533 * <LI> typeNoNulls - does not allow NULL values
2534 * <LI> typeNullable - allows NULL values
2535 * <LI> typeNullableUnknown - nullability unknown
2537 * <LI><B>CASE_SENSITIVE</B> boolean=> is it case sensitive?
2538 * <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
2540 * <LI> typePredNone - No support
2541 * <LI> typePredChar - Only supported with WHERE .. LIKE
2542 * <LI> typePredBasic - Supported except for WHERE .. LIKE
2543 * <LI> typeSearchable - Supported for all WHERE ..
2545 * <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned?
2546 * <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value?
2547 * <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
2548 * auto-increment value?
2549 * <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
2551 * <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
2552 * <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
2553 * <LI><B>SQL_DATA_TYPE</B> int => unused
2554 * <LI><B>SQL_DATETIME_SUB</B> int => unused
2555 * <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
2558 * @return ResultSet each row is a SQL type description
2560 public java.sql.ResultSet getTypeInfo() throws SQLException
2562 java.sql.ResultSet rs = connection.ExecSQL("select typname from pg_type");
2564 Field f[] = new Field[18];
2565 ResultSet r; // ResultSet for the SQL query that we need to do
2566 Vector v = new Vector(); // The new ResultSet tuple stuff
2568 f[0] = new Field(connection, "TYPE_NAME", iVarcharOid, 32);
2569 f[1] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
2570 f[2] = new Field(connection, "PRECISION", iInt4Oid, 4);
2571 f[3] = new Field(connection, "LITERAL_PREFIX", iVarcharOid, 32);
2572 f[4] = new Field(connection, "LITERAL_SUFFIX", iVarcharOid, 32);
2573 f[5] = new Field(connection, "CREATE_PARAMS", iVarcharOid, 32);
2574 f[6] = new Field(connection, "NULLABLE", iInt2Oid, 2);
2575 f[7] = new Field(connection, "CASE_SENSITIVE", iBoolOid, 1);
2576 f[8] = new Field(connection, "SEARCHABLE", iInt2Oid, 2);
2577 f[9] = new Field(connection, "UNSIGNED_ATTRIBUTE", iBoolOid, 1);
2578 f[10] = new Field(connection, "FIXED_PREC_SCALE", iBoolOid, 1);
2579 f[11] = new Field(connection, "AUTO_INCREMENT", iBoolOid, 1);
2580 f[12] = new Field(connection, "LOCAL_TYPE_NAME", iVarcharOid, 32);
2581 f[13] = new Field(connection, "MINIMUM_SCALE", iInt2Oid, 2);
2582 f[14] = new Field(connection, "MAXIMUM_SCALE", iInt2Oid, 2);
2583 f[15] = new Field(connection, "SQL_DATA_TYPE", iInt4Oid, 4);
2584 f[16] = new Field(connection, "SQL_DATETIME_SUB", iInt4Oid, 4);
2585 f[17] = new Field(connection, "NUM_PREC_RADIX", iInt4Oid, 4);
2587 // cache some results, this will keep memory useage down, and speed
2588 // things up a little.
2589 byte b9[] = "9".getBytes();
2590 byte b10[] = "10".getBytes();
2591 byte bf[] = "f".getBytes();
2592 byte bnn[] = Integer.toString(typeNoNulls).getBytes();
2593 byte bts[] = Integer.toString(typeSearchable).getBytes();
2596 byte[][] tuple = new byte[18][];
2597 String typname=rs.getString(1);
2598 tuple[0] = typname.getBytes();
2599 tuple[1] = Integer.toString(connection.getSQLType(typname)).getBytes();
2600 tuple[2] = b9; // for now
2601 tuple[6] = bnn; // for now
2602 tuple[7] = bf; // false for now - not case sensitive
2604 tuple[9] = bf; // false for now - it's signed
2605 tuple[10] = bf; // false for now - must handle money
2606 tuple[11] = bf; // false for now - handle autoincrement
2607 // 12 - LOCAL_TYPE_NAME is null
2609 // 15 & 16 are unused so we return null
2610 tuple[17] = b10; // everything is base 10
2611 v.addElement(tuple);
2614 return new ResultSet(connection, f, v, "OK", 1);
2617 throw new PSQLException("postgresql.metadata.unavailable");
2621 * Get a description of a table's indices and statistics. They are
2622 * ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
2624 * <P>Each index column description has the following columns:
2626 * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
2627 * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
2628 * <LI><B>TABLE_NAME</B> String => table name
2629 * <LI><B>NON_UNIQUE</B> boolean => Can index values be non-unique?
2630 * false when TYPE is tableIndexStatistic
2631 * <LI><B>INDEX_QUALIFIER</B> String => index catalog (may be null);
2632 * null when TYPE is tableIndexStatistic
2633 * <LI><B>INDEX_NAME</B> String => index name; null when TYPE is
2634 * tableIndexStatistic
2635 * <LI><B>TYPE</B> short => index type:
2637 * <LI> tableIndexStatistic - this identifies table statistics that are
2638 * returned in conjuction with a table's index descriptions
2639 * <LI> tableIndexClustered - this is a clustered index
2640 * <LI> tableIndexHashed - this is a hashed index
2641 * <LI> tableIndexOther - this is some other style of index
2643 * <LI><B>ORDINAL_POSITION</B> short => column sequence number
2644 * within index; zero when TYPE is tableIndexStatistic
2645 * <LI><B>COLUMN_NAME</B> String => column name; null when TYPE is
2646 * tableIndexStatistic
2647 * <LI><B>ASC_OR_DESC</B> String => column sort sequence, "A" => ascending
2648 * "D" => descending, may be null if sort sequence is not supported;
2649 * null when TYPE is tableIndexStatistic
2650 * <LI><B>CARDINALITY</B> int => When TYPE is tableIndexStatisic then
2651 * this is the number of rows in the table; otherwise it is the
2652 * number of unique values in the index.
2653 * <LI><B>PAGES</B> int => When TYPE is tableIndexStatisic then
2654 * this is the number of pages used for the table, otherwise it
2655 * is the number of pages used for the current index.
2656 * <LI><B>FILTER_CONDITION</B> String => Filter condition, if any.
2660 * @param catalog a catalog name; "" retrieves those without a catalog
2661 * @param schema a schema name pattern; "" retrieves those without a schema
2662 * @param table a table name
2663 * @param unique when true, return only indices for unique values;
2664 * when false, return indices regardless of whether unique or not
2665 * @param approximate when true, result is allowed to reflect approximate
2666 * or out of data values; when false, results are requested to be
2668 * @return ResultSet each row is an index column description
2670 // Implementation note: This is required for Borland's JBuilder to work
2671 public java.sql.ResultSet getIndexInfo(String catalog, String schema, String tableName, boolean unique, boolean approximate) throws SQLException
2673 Field f[] = new Field[13];
2674 java.sql.ResultSet r; // ResultSet for the SQL query that we need to do
2675 Vector v = new Vector(); // The new ResultSet tuple stuff
2677 f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
2678 f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
2679 f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
2680 f[3] = new Field(connection, "NON_UNIQUE", iBoolOid, 1);
2681 f[4] = new Field(connection, "INDEX_QUALIFIER", iVarcharOid, 32);
2682 f[5] = new Field(connection, "INDEX_NAME", iVarcharOid, 32);
2683 f[6] = new Field(connection, "TYPE", iInt2Oid, 2);
2684 f[7] = new Field(connection, "ORDINAL_POSITION", iInt2Oid, 2);
2685 f[8] = new Field(connection, "COLUMN_NAME", iVarcharOid, 32);
2686 f[9] = new Field(connection, "ASC_OR_DESC", iVarcharOid, 32);
2687 f[10] = new Field(connection, "CARDINALITY", iInt4Oid, 4);
2688 f[11] = new Field(connection, "PAGES", iInt4Oid, 4);
2689 f[12] = new Field(connection, "FILTER_CONDITION", iVarcharOid, 32);
2691 r = connection.ExecSQL("select " +
2695 "x.indisclustered, " +
2700 "FROM pg_index x, pg_class c, pg_class i, pg_am a " +
2701 "WHERE ((c.relname = '" + tableName.toLowerCase() + "') " +
2702 " AND (c.oid = x.indrelid) " +
2703 " AND (i.oid = x.indexrelid) " +
2704 " AND (i.relam = a.oid)) " +
2705 "ORDER BY x.indisunique DESC, " +
2706 " x.indisclustered, a.amname, i.relname");
2708 // indkey is an array of column ordinals (integers). In the JDBC
2709 // interface, this has to be separated out into a separate
2710 // tuple for each indexed column. Also, getArray() is not yet
2711 // implemented for Postgres JDBC, so we parse by hand.
2712 String columnOrdinalString = r.getString(6);
2713 StringTokenizer stok = new StringTokenizer(columnOrdinalString);
2714 int [] columnOrdinals = new int[stok.countTokens()];
2716 while (stok.hasMoreTokens()) {
2717 columnOrdinals[o++] = Integer.parseInt(stok.nextToken());
2719 for (int i = 0; i < columnOrdinals.length; i++) {
2720 byte [] [] tuple = new byte [13] [];
2721 tuple[0] = "".getBytes();
2722 tuple[1] = "".getBytes();
2723 tuple[2] = r.getBytes(1);
2724 tuple[3] = r.getBoolean(2) ? "f".getBytes() : "t".getBytes();
2726 tuple[5] = r.getBytes(3);
2727 tuple[6] = r.getBoolean(4) ?
2728 Integer.toString(tableIndexClustered).getBytes() :
2729 r.getString(5).equals("hash") ?
2730 Integer.toString(tableIndexHashed).getBytes() :
2731 Integer.toString(tableIndexOther).getBytes();
2732 tuple[7] = Integer.toString(i + 1).getBytes();
2733 java.sql.ResultSet columnNameRS = connection.ExecSQL("select a.attname FROM pg_attribute a, pg_class c WHERE (a.attnum = " + columnOrdinals[i] + ") AND (a.attrelid = " + r.getInt(8) + ")");
2734 columnNameRS.next();
2735 tuple[8] = columnNameRS.getBytes(1);
2736 tuple[9] = null; // sort sequence ???
2737 tuple[10] = r.getBytes(7); // inexact
2738 tuple[11] = r.getBytes(8);
2740 v.addElement(tuple);
2744 return new ResultSet(connection, f, v, "OK", 1);