Patch adding empty missing columns for getTables - Mailing list pgsql-jdbc

Hi,

The attached patch add empty columns to the resultset returned by
AbstractJdbc2MetaData.getTables().
According to JAVA 1.5 javadoc, it should return 10 columns and we are
currently returning only 5.
Maybe someone familiar with the system tables could modify the query to
return the expected values. With this patch, at least (and it is
allowed) null columns are returned and we don't get array indices
exceptions.

Xavier Poinsard.
Index: jdbc2/AbstractJdbc2DatabaseMetaData.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v
retrieving revision 1.30
diff -u -r1.30 AbstractJdbc2DatabaseMetaData.java
--- jdbc2/AbstractJdbc2DatabaseMetaData.java    27 Mar 2006 12:07:57 -0000    1.30
+++ jdbc2/AbstractJdbc2DatabaseMetaData.java    18 Apr 2006 15:10:46 -0000
@@ -1853,6 +1853,14 @@
      * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL
      * TEMPORARY", "ALIAS", "SYNONYM".
      * <li><b>REMARKS</b> String => explanatory comment on the table
+     * <li><b>TYPE_CAT</b> String => the types catalog (may be <code>null</code>)
+     * <li><b>TYPE_SCHEM</b> String => the types schema (may be <code>null</code>)
+     * <li><b>TYPE_NAME</b> String => type name (may be <code>null</code>)
+     * <li><b>SELF_REFERENCING_COL_NAME</b> String => name of the designated
+     *                  "identifier" column of a typed table (may be <code>null</code>)
+     * <li><b>REF_GENERATION</b> String => specifies how values in
+     *                  SELF_REFERENCING_COL_NAME are created. Values are
+     *                  "SYSTEM", "USER", "DERIVED". (may be <code>null</code>)
      * </ol>
      *
      * <p>The valid values for the types parameter are:
@@ -1907,7 +1915,9 @@
                      " END " +
                      " ELSE NULL " +
                      " END " +
-                     " AS TABLE_TYPE, d.description AS REMARKS " +
+                     " AS TABLE_TYPE, d.description AS REMARKS, " +
+                     " NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME," +
+                     " NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION " +
                      " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c " +
                      " LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0) " +
                      " LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class') " +
@@ -1958,7 +1968,9 @@
             orderby = " ORDER BY TABLE_TYPE,TABLE_NAME ";
             if (connection.haveMinimumServerVersion("7.2"))
             {
-                select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, c.relname AS TABLE_NAME, " + tableType + " AS
TABLE_TYPE,d.description AS REMARKS " + 
+                select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, c.relname AS TABLE_NAME, " + tableType + " AS
TABLE_TYPE,d.description AS REMARKS , " + 
+                     " NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME," +
+                     " NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION " +
                          " FROM pg_class c " +
                          " LEFT JOIN pg_description d ON (c.oid=d.objoid AND d.objsubid = 0) " +
                          " LEFT JOIN pg_class dc ON (d.classoid = dc.oid AND dc.relname='pg_class') " +
@@ -1966,14 +1978,18 @@
             }
             else if (connection.haveMinimumServerVersion("7.1"))
             {
-                select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, c.relname AS TABLE_NAME, " + tableType + " AS
TABLE_TYPE,d.description AS REMARKS " + 
+                select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, c.relname AS TABLE_NAME, " + tableType + " AS
TABLE_TYPE,d.description AS REMARKS , " + 
+                     " NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME," +
+                     " NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION " +
                          " FROM pg_class c " +
                          " LEFT JOIN pg_description d ON (c.oid=d.objoid) " +
                          " WHERE true ";
             }
             else
             {
-                select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, c.relname AS TABLE_NAME, " + tableType + " AS
TABLE_TYPE,NULL AS REMARKS " + 
+                select = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, c.relname AS TABLE_NAME, " + tableType + " AS
TABLE_TYPE,NULL AS REMARKS , " + 
+                     " NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME," +
+                     " NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION " +
                          " FROM pg_class c " +
                          " WHERE true ";
             }
Index: test/jdbc2/DatabaseMetaDataTest.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java,v
retrieving revision 1.36
diff -u -r1.36 DatabaseMetaDataTest.java
--- test/jdbc2/DatabaseMetaDataTest.java    3 Feb 2006 21:10:15 -0000    1.36
+++ test/jdbc2/DatabaseMetaDataTest.java    18 Apr 2006 15:10:46 -0000
@@ -69,6 +69,8 @@
         assertEquals( "testmetadata", tableName );
         String tableType = rs.getString("TABLE_TYPE");
         assertEquals( "TABLE", tableType );
+        // at least 10 columns
+        assertTrue("getTables() doesn't return enough columns",rs.getMetaData().getColumnCount()>=10);
         //There should only be one row returned
         assertTrue( "getTables() returned too many rows", rs.next() == false);
         rs.close();