From ba977c086c01726affcc96219a79584a11ccc78e Mon Sep 17 00:00:00 2001 From: "Marc G. Fournier" Date: Sun, 11 Jan 1998 21:14:56 +0000 Subject: Peter's Mega-Patch for JDBC... see README_6.3 for list of changes --- .../jdbc/postgresql/DatabaseMetaData.java | 452 ++++++++++++++++++--- 1 file changed, 392 insertions(+), 60 deletions(-) (limited to 'src/interfaces/jdbc/postgresql/DatabaseMetaData.java') diff --git a/src/interfaces/jdbc/postgresql/DatabaseMetaData.java b/src/interfaces/jdbc/postgresql/DatabaseMetaData.java index b5e8a985fb2..4e3894f66b6 100644 --- a/src/interfaces/jdbc/postgresql/DatabaseMetaData.java +++ b/src/interfaces/jdbc/postgresql/DatabaseMetaData.java @@ -4,17 +4,14 @@ import java.sql.*; import java.util.*; /** - * @version 1.0 15-APR-1997 - * @author Adrian Hall - * * This class provides information about the database as a whole. * - * Many of the methods here return lists of information in ResultSets. You + *

Many of the methods here return lists of information in ResultSets. You * can use the normal ResultSet methods such as getString and getInt to * retrieve the data from these ResultSets. If a given form of metadata is * not available, these methods should throw a SQLException. * - * Some of these methods take arguments that are String patterns. These + *

Some of these methods take arguments that are String patterns. These * arguments all have names such as fooPattern. Within a pattern String, * "%" means match any substring of 0 or more characters, and "_" means * match any one character. Only metadata entries matching the search @@ -22,7 +19,7 @@ import java.util.*; * ref, it means that argument's criteria should be dropped from the * search. * - * A SQLException will be throws if a driver does not support a meta + *

A SQLException will be throws if a driver does not support a meta * data method. In the case of methods that return a ResultSet, either * a ResultSet (which may be empty) is returned or a SQLException is * thrown. @@ -33,6 +30,12 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData { Connection connection; // The connection association + // These define various OID's. Hopefully they will stay constant. + static final int iVarcharOid = 1043; // OID for varchar + static final int iBoolOid = 16; // OID for bool + static final int iInt2Oid = 21; // OID for int2 + static final int iInt4Oid = 23; // OID for int4 + public DatabaseMetaData(Connection conn) { this.connection = conn; @@ -152,19 +155,21 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData } /** - * What is the version of this database product. Note that - * PostgreSQL 6.1 has a system catalog called pg_version - + * What is the version of this database product. + * + *

Note that PostgreSQL 6.3 has a system catalog called pg_version - * however, select * from pg_version on any database retrieves - * no rows. For now, we will return the version 6.1 (in the - * hopes that we change this driver as often as we change the - * database) + * no rows. + * + *

For now, we will return the version 6.3 (in the hope that we change + * this driver as often as we change the database) * * @return the database version * @exception SQLException if a database access error occurs */ public String getDatabaseProductVersion() throws SQLException { - return ("6.2"); + return ("6.3"); } /** @@ -240,7 +245,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * 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 + *

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. * @@ -290,7 +295,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * case sensitive and as a result store them in mixed case? A * JDBC compliant driver will always return true. * - * Predicament - what do they mean by "SQL identifiers" - if it + *

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. * @@ -340,7 +345,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * a space if identifier quoting isn't supported. A JDBC Compliant * driver will always use a double quote character. * - * If an SQL identifier is a table name, column name, etc. then + *

If an SQL identifier is a table name, column name, etc. then * we do not support it. * * @return the quoting string @@ -355,10 +360,12 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * 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 + *

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 + * + *

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. * @@ -410,7 +417,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * Get all the "extra" characters that can bew used in unquoted * identifier names (those beyond a-zA-Z0-9 and _) * - * From the file src/backend/parser/scan.l, an identifier is + *

From the file src/backend/parser/scan.l, an identifier is * {letter}{letter_or_digit} which makes it just those listed * above. * @@ -449,14 +456,16 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData /** * Is column aliasing supported? * - * If so, the SQL AS clause can be used to provide names for + *

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. + *

e.g. * + * 

    * select count(C) as C_COUNT from T group by C;
    *
+   *

* should return a column named as C_COUNT instead of count(C) * * @return true if so @@ -506,7 +515,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData /** * Are expressions in "ORCER BY" lists supported? * - * e.g. select * from t order by a + b; + *
e.g. select * from t order by a + b; * * @return true if so * @exception SQLException if a database access error occurs @@ -607,7 +616,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * Can columns be defined as non-nullable. A JDBC Compliant driver * always returns true. * - * This changed from false to true in v6.2 of the driver, as this + *

This changed from false to true in v6.2 of the driver, as this * support was added to the backend. * * @return true if so @@ -622,9 +631,9 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * 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 + *

http://www.microsoft.com/msdn/sdk/platforms/doc/odbc/src/intropr.htm * - * In Appendix C. From this description, we seem to support the + *

In Appendix C. From this description, we seem to support the * ODBC minimal (Level 0) grammar. * * @return true if so @@ -1142,7 +1151,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * What is the maximum number of columns in a table? From the * create_table(l) manual page... * - * "The new class is created as a heap with no initial data. A + *

"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)..." @@ -1393,6 +1402,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * 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;
@@ -1400,6 +1410,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData
    * CREATE TABLE X (A INT);
    * SELECT A FROM T INTO X;
    * COMMIT;
+   *

* * does the CREATE TABLE call cause a commit? The answer is no. * @@ -1412,7 +1423,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData } /** - * Is a data definition statement within a transaction ignored? + * Is a data definition statement within a transaction ignored? * It seems to be (from experiment in previous method) * * @return true if so @@ -1426,22 +1437,26 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData /** * Get a description of stored procedures available in a catalog * - * Only procedure descriptions matching the schema and procedure + *

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: - * PROCEDURE_CAT String => procedure catalog (may be null) - * PROCEDURE_SCHEM String => procedure schema (may be null) - * PROCEDURE_NAME String => procedure name - * Field 4 reserved (make it null) - * Field 5 reserved (make it null) - * Field 6 reserved (make it null) - * REMARKS String => explanatory comment on the procedure - * PROCEDURE_TYPE short => kind of procedure - * * procedureResultUnknown - May return a result - * * procedureNoResult - Does not return a result - * * procedureReturnsResult - Returns a result + *

Each procedure description has the following columns: + *

    + *
  1. PROCEDURE_CAT String => procedure catalog (may be null) + *
  2. PROCEDURE_SCHEM String => procedure schema (may be null) + *
  3. PROCEDURE_NAME String => procedure name + *
  4. Field 4 reserved (make it null) + *
  5. Field 5 reserved (make it null) + *
  6. Field 6 reserved (make it null) + *
  7. REMARKS String => explanatory comment on the procedure + *
  8. PROCEDURE_TYPE short => kind of procedure + *
      + *
    • procedureResultUnknown - May return a result + *
    • procedureNoResult - Does not return a result + *
    • procedureReturnsResult - Returns a result + *
    + *
* * @param catalog - a catalog name; "" retrieves those without a * catalog; null means drop catalog name from criteria @@ -1451,8 +1466,6 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * @return ResultSet - each row is a procedure description * @exception SQLException if a database access error occurs */ - static final int iVarcharOid = 1043; // This is the OID for a varchar() - static final int iInt2Oid = 21; // This is the OID for an int2 public java.sql.ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern) throws SQLException { // the field descriptors for the new ResultSet @@ -1497,17 +1510,183 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData return new ResultSet(connection, f, v, "OK", 1); } + /** + * Get a description of a catalog's stored procedure parameters + * and result 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: + *

    + *
  1. PROCEDURE_CAT String => procedure catalog (may be null) + *
  2. PROCEDURE_SCHEM String => procedure schema (may be null) + *
  3. PROCEDURE_NAME String => procedure name + *
  4. COLUMN_NAME String => column/parameter name + *
  5. COLUMN_TYPE Short => kind of column/parameter: + *
    • procedureColumnUnknown - nobody knows + *
    • procedureColumnIn - IN parameter + *
    • procedureColumnInOut - INOUT parameter + *
    • procedureColumnOut - OUT parameter + *
    • procedureColumnReturn - procedure return value + *
    • procedureColumnResult - result column in ResultSet + *
    + *
  6. DATA_TYPE short => SQL type from java.sql.Types + *
  7. TYPE_NAME String => SQL type name + *
  8. PRECISION int => precision + *
  9. LENGTH int => length in bytes of data + *
  10. SCALE short => scale + *
  11. RADIX short => radix + *
  12. NULLABLE short => can it contain NULL? + *
    • procedureNoNulls - does not allow NULL values + *
    • procedureNullable - allows NULL values + *
    • procedureNullableUnknown - nullability unknown + *
    • REMARKS String => comment describing parameter/column + *
+ * @param catalog This is ignored in postgresql, advise this is set to null + * @param schemaPattern This is ignored in postgresql, advise this is set to null + * @param procedureNamePattern a procedure name pattern + * @param columnNamePattern a column name pattern + * @return each row is a stored procedure parameter or column description + * @exception SQLException if a database-access error occurs + * @see #getSearchStringEscape + */ + // Implementation note: This is required for Borland's JBuilder to work public java.sql.ResultSet getProcedureColumns(String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern) throws SQLException { - // XXX-Not Implemented - return null; + // for now, this returns an empty result set. + Field f[] = new Field[13]; + 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, new String("PROCEDURE_CAT"), iVarcharOid, 32); + f[1] = new Field(connection, new String("PROCEDURE_SCHEM"), iVarcharOid, 32); + f[2] = new Field(connection, new String("PROCEDURE_NAME"), iVarcharOid, 32); + f[3] = new Field(connection, new String("COLUMN_NAME"), iVarcharOid, 32); + f[4] = new Field(connection, new String("COLUMN_TYPE"), iInt2Oid, 2); + f[5] = new Field(connection, new String("DATA_TYPE"), iInt2Oid, 2); + f[6] = new Field(connection, new String("TYPE_NAME"), iVarcharOid, 32); + f[7] = new Field(connection, new String("PRECISION"), iInt4Oid, 4); + f[8] = new Field(connection, new String("LENGTH"), iInt4Oid, 4); + f[9] = new Field(connection, new String("SCALE"), iInt2Oid, 2); + f[10] = new Field(connection, new String("RADIX"), iInt2Oid, 2); + f[11] = new Field(connection, new String("NULLABLE"), iInt2Oid, 2); + f[12] = new Field(connection, new String("REMARKS"), iVarcharOid, 32); + + return new ResultSet(connection, f, v, "OK", 1); } + /** + * Get a description of tables available in a catalog. + * + *

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: + * + *

    + *
  1. TABLE_CAT String => table catalog (may be null) + *
  2. TABLE_SCHEM String => table schema (may be null) + *
  3. TABLE_NAME String => table name + *
  4. TABLE_TYPE String => table type. Typical types are "TABLE", + * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL + * TEMPORARY", "ALIAS", "SYNONYM". + *
  5. REMARKS String => explanatory comment on the table + *
+ * + *

The valid values for the types parameter are: + * "TABLE", "INDEX", "LARGE OBJECT", "SEQUENCE", "SYSTEM TABLE" and + * "SYSTEM INDEX" + * + * @param catalog a catalog name; For postgresql, this is ignored, and + * should be set to null + * @param schemaPattern a schema name pattern; For 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 { - return connection.createStatement().executeQuery("SELECT '' as TABLE_CAT,'' AS TABLE_SCHEM,relname AS TABLE_NAME,'TABLE' AS TABLE_TYPE,'' AS REMARKS FROM pg_class WHERE relkind = 'r' and relname !~ '^pg_' and relname !~ '^Inv' and relname ~ '"+tableNamePattern+"' ORDER BY TABLE_NAME"); + // the field descriptors for the new ResultSet + Field f[] = new Field[5]; + 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, new String("TABLE_CAT"), iVarcharOid, 32); + f[1] = new Field(connection, new String("TABLE_SCHEM"), iVarcharOid, 32); + f[2] = new Field(connection, new String("TABLE_NAME"), iVarcharOid, 32); + f[3] = new Field(connection, new String("TABLE_TYPE"), iVarcharOid, 32); + f[4] = new Field(connection, new String("REMARKS"), iVarcharOid, 32); + + // Now form the query + StringBuffer sql = new StringBuffer("select relname,oid from pg_class where "); + boolean notFirst=false; + for(int i=0;i0 order by c.relname,a.attnum"); + + while(r.next()) { + byte[][] tuple = new byte[18][0]; + + String name = r.getString(1); + String remarks = new String("no remarks"); + + // Fetch the description for the table (if any) + ResultSet dr = connection.ExecSQL("select description from pg_description where objoid="+r.getInt(1)); + if(dr.getTupleCount()==1) { + dr.next(); + remarks=dr.getString(1); + } + dr.close(); + + tuple[0] = "".getBytes(); // Catalog name + tuple[1] = "".getBytes(); // Schema name + tuple[2] = r.getString(2).getBytes(); // Table name + tuple[3] = r.getString(3).getBytes(); // Column name + + dr = connection.ExecSQL("select typname from pg_type where oid = "+r.getString(4)); + dr.next(); + String typname=dr.getString(1); + dr.close(); + tuple[4] = Integer.toString(Field.getSQLType(typname)).getBytes(); // Data type + tuple[5] = typname.getBytes(); // Type name + + tuple[6] = r.getString(7).getBytes(); // Column size + + tuple[7] = null; // Buffer length + + tuple[8] = "0".getBytes(); // Decimal Digits - how to get this? + tuple[9] = "10".getBytes(); // Num Prec Radix - assume decimal + + // tuple[10] is below + + tuple[11] = remarks.getBytes(); // Remarks + + tuple[12] = null; // 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.getString(5).getBytes(); // ordinal position + + String nullFlag = r.getString(6); + tuple[10] = Integer.toString(nullFlag.equals("f")?java.sql.DatabaseMetaData.columnNullable:java.sql.DatabaseMetaData.columnNoNulls).getBytes(); // Nullable + tuple[17] = (nullFlag.equals("f")?"YES":"NO").getBytes(); // is nullable + + v.addElement(tuple); + } + r.close(); + return new ResultSet(connection, f, v, "OK", 1); } /** @@ -1649,8 +1925,30 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData */ public java.sql.ResultSet getColumnPrivileges(String catalog, String schema, String table, String columnNamePattern) throws SQLException { - // XXX-Not Implemented + // XXX-Not Implemented as grant is broken return null; + //Field f[] = new Field[8]; + //Vector v = new Vector(); + // + //f[0] = new Field(connection,new String("TABLE_CAT"),iVarcharOid,32); + //f[1] = new Field(connection,new String("TABLE_SCHEM"),iVarcharOid,32); + //f[2] = new Field(connection,new String("TABLE_NAME"),iVarcharOid,32); + //f[3] = new Field(connection,new String("COLUMN_NAME"),iVarcharOid,32); + //f[4] = new Field(connection,new String("GRANTOR"),iVarcharOid,32); + //f[5] = new Field(connection,new String("GRANTEE"),iVarcharOid,32); + //f[6] = new Field(connection,new String("PRIVILEGE"),iVarcharOid,32); + //f[7] = new Field(connection,new String("IS_GRANTABLE"),iVarcharOid,32); + // + //// This is taken direct from the psql source + //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 ORDER BY relname"); + //while(r.next()) { + //byte[][] tuple = new byte[8][0]; + //tuple[0] = tuple[1]= "default".getBytes(); + // + //v.addElement(tuple); + //} + // + //return new ResultSet(connection,f,v,"OK",1); } /** @@ -1722,10 +2020,24 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * @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 { - // XXX-Not Implemented - return null; + // 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, new String("SCOPE"), iInt2Oid, 2); + f[1] = new Field(connection, new String("COLUMN_NAME"), iVarcharOid, 32); + f[2] = new Field(connection, new String("DATA_TYPE"), iInt2Oid, 2); + f[3] = new Field(connection, new String("TYPE_NAME"), iVarcharOid, 32); + f[4] = new Field(connection, new String("COLUMN_SIZE"), iInt4Oid, 4); + f[5] = new Field(connection, new String("BUFFER_LENGTH"), iInt4Oid, 4); + f[6] = new Field(connection, new String("DECIMAL_DIGITS"), iInt2Oid, 2); + f[7] = new Field(connection, new String("PSEUDO_COLUMN"), iInt2Oid, 2); + + return new ResultSet(connection, f, v, "OK", 1); } /** @@ -2078,9 +2390,29 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * 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 table, boolean unique, boolean approximate) throws SQLException { - // XXX-Not Implemented - return null; + // for now, this returns an empty result set. + Field f[] = new Field[13]; + 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, new String("TABLE_CAT"), iVarcharOid, 32); + f[1] = new Field(connection, new String("TABLE_SCHEM"), iVarcharOid, 32); + f[2] = new Field(connection, new String("TABLE_NAME"), iVarcharOid, 32); + f[3] = new Field(connection, new String("NON_UNIQUE"), iBoolOid, 1); + f[4] = new Field(connection, new String("INDEX_QUALIFIER"), iVarcharOid, 32); + f[5] = new Field(connection, new String("INDEX_NAME"), iVarcharOid, 32); + f[6] = new Field(connection, new String("TYPE"), iInt2Oid, 2); + f[7] = new Field(connection, new String("ORDINAL_POSITION"), iInt2Oid, 2); + f[8] = new Field(connection, new String("COLUMN_NAME"), iVarcharOid, 32); + f[9] = new Field(connection, new String("ASC_OR_DESC"), iVarcharOid, 32); + f[10] = new Field(connection, new String("CARDINALITY"), iInt4Oid, 4); + f[11] = new Field(connection, new String("PAGES"), iInt4Oid, 4); + f[12] = new Field(connection, new String("FILTER_CONDITION"), iVarcharOid, 32); + + return new ResultSet(connection, f, v, "OK", 1); } } + -- cgit v1.2.3