Re: DatabaseMetaData.getIndexInfo() added - Mailing list pgsql-patches

Patch applied to jdbc1 and jdbc2.  This allows index lookups from jdbc.
Thanks.


> Hi all!
>
> Attached is a patch that implements DatabaseMetaData.getIndexInfo()
> for the JDBC2 driver.  It also fixes a compile error under jikes by
> casting characters in case statements of a byte-compared switch to
> bytes.
>
> A few notes on getIndexInfo():
>
> 1.) getArray() is not implemented for the postgresql JDBC driver yet,
> so getIndexInfo() parses the pg_index.indkey field into separate
> integers by hand.
>
> 2.) I have guessed that if pg_index.indisclustered is "false", then
> the index is "hashed"; if not, line 2561 of the resultant class
> should have "tableIndexOther" rather than "tableIndexHashed".
>
> 3.) I didn't know what sort sequence (if any) was used in indexes, so
> I have set it to "null" (unknown) on line 2566.
>
> 4.) For "CARDINALITY" (number of unique index items, the 11th field of
> the ResultSet returned by getIndexInfo()) I have used
> pg_classes.reltuples.
>
> I have tested this method, but hardly extensively.  Is there a proper
> regression test suite for the JDBC driver that tests can be added to?
>
> William
> --
> William Webber                               william@peopleweb.net.au
> Senior Programmer
> PeopleWeb Australia                          http://www.peopleweb.com

[ Attachment, skipping... ]

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java,v
retrieving revision 1.15
diff -c -r1.15 DatabaseMetaData.java
*** src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java    2001/05/16 16:42:28    1.15
--- src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java    2001/05/16 17:38:38
***************
*** 1688,1703 ****

      String relKind;
      switch (r.getBytes(3)[0]) {
!     case 'r':
          relKind = "TABLE";
          break;
!     case 'i':
          relKind = "INDEX";
          break;
!     case 'S':
          relKind = "SEQUENCE";
          break;
!     case 'v':
          relKind = "VIEW";
          break;
      default:
--- 1688,1703 ----

      String relKind;
      switch (r.getBytes(3)[0]) {
!     case (byte) 'r':
          relKind = "TABLE";
          break;
!     case (byte) 'i':
          relKind = "INDEX";
          break;
!     case (byte) 'S':
          relKind = "SEQUENCE";
          break;
!     case (byte) 'v':
          relKind = "VIEW";
          break;
      default:
***************
*** 2623,2633 ****
     * @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 
    {
-     // 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, "TABLE_CAT", iVarcharOid, 32);
--- 2623,2632 ----
     * @return ResultSet each row is an index column description
     */
    // Implementation note: This is required for Borland's JBuilder to work
!   public java.sql.ResultSet getIndexInfo(String catalog, String schema, String tableName, boolean unique, boolean
approximate)throws SQLException 
    {
      Field f[] = new Field[13];
!     java.sql.ResultSet r;    // ResultSet for the SQL query that we need to do
      Vector v = new Vector();        // The new ResultSet tuple stuff

      f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
***************
*** 2644,2649 ****
--- 2643,2702 ----
      f[11] = new Field(connection, "PAGES", iInt4Oid, 4);
      f[12] = new Field(connection, "FILTER_CONDITION", iVarcharOid, 32);

+
+     r = connection.ExecSQL("select " +
+                 "c.relname, " +
+                 "x.indisunique, " +
+                 "i.relname, " +
+                 "x.indisclustered, " +
+                 "a.amname, " +
+                 "x.indkey, " +
+                 "c.reltuples, " +
+                 "c.relpages " +
+                 "FROM pg_index x, pg_class c, pg_class i, pg_am a " +
+                 "WHERE ((c.relname = '" + tableName.toLowerCase() + "') " +
+                 " AND (c.oid = x.indrelid) " +
+                 " AND (i.oid = x.indexrelid) " +
+                 " AND (c.relam = a.oid)) " +
+                 "ORDER BY x.indisunique DESC, " +
+                 " x.indisclustered, a.amname, i.relname");
+     while (r.next()) {
+         // indkey is an array of column ordinals (integers).  In the JDBC
+         // interface, this has to be separated out into a separate
+         // tuple for each indexed column.  Also, getArray() is not yet
+         // implemented for Postgres JDBC, so we parse by hand.
+         String columnOrdinalString = r.getString(6);
+         StringTokenizer stok = new StringTokenizer(columnOrdinalString);
+         int [] columnOrdinals = new int[stok.countTokens()];
+         int o = 0;
+         while (stok.hasMoreTokens()) {
+             columnOrdinals[o++] = Integer.parseInt(stok.nextToken());
+         }
+         for (int i = 0; i < columnOrdinals.length; i++) {
+             byte [] [] tuple = new byte [13] [];
+             tuple[0] = "".getBytes();
+             tuple[1] = "".getBytes();
+             tuple[2] = r.getBytes(1);
+             tuple[3] = r.getBoolean(2) ? "f".getBytes() : "t".getBytes();
+             tuple[4] = null;
+             tuple[5] = r.getBytes(3);
+             tuple[6] = r.getBoolean(4) ?
+                 Integer.toString(tableIndexClustered).getBytes() :
+                 r.getString(5).equals("hash") ?
+                 Integer.toString(tableIndexHashed).getBytes() :
+                 Integer.toString(tableIndexOther).getBytes();
+             tuple[7] = Integer.toString(i + 1).getBytes();
+             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) + ")"); 
+             columnNameRS.next();
+             tuple[8] = columnNameRS.getBytes(1);
+             tuple[9] = null;  // sort sequence ???
+             tuple[10] = r.getBytes(7);  // inexact
+             tuple[11] = r.getBytes(8);
+             tuple[12] = null;
+             v.addElement(tuple);
+         }
+     }
+
      return new ResultSet(connection, f, v, "OK", 1);
    }
  }
Index: src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java,v
retrieving revision 1.19
diff -c -r1.19 DatabaseMetaData.java
*** src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java    2001/05/16 16:42:31    1.19
--- src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java    2001/05/16 17:38:42
***************
*** 1688,1703 ****

      String relKind;
      switch (r.getBytes(3)[0]) {
!     case 'r':
          relKind = "TABLE";
          break;
!     case 'i':
          relKind = "INDEX";
          break;
!     case 'S':
          relKind = "SEQUENCE";
          break;
!     case 'v':
          relKind = "VIEW";
          break;
      default:
--- 1688,1703 ----

      String relKind;
      switch (r.getBytes(3)[0]) {
!     case (byte) 'r':
          relKind = "TABLE";
          break;
!     case (byte) 'i':
          relKind = "INDEX";
          break;
!     case (byte) 'S':
          relKind = "SEQUENCE";
          break;
!     case (byte) 'v':
          relKind = "VIEW";
          break;
      default:
***************
*** 2622,2632 ****
     * @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 
    {
-     // 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, "TABLE_CAT", iVarcharOid, 32);
--- 2622,2631 ----
     * @return ResultSet each row is an index column description
     */
    // Implementation note: This is required for Borland's JBuilder to work
!   public java.sql.ResultSet getIndexInfo(String catalog, String schema, String tableName, boolean unique, boolean
approximate)throws SQLException 
    {
      Field f[] = new Field[13];
!     java.sql.ResultSet r;    // ResultSet for the SQL query that we need to do
      Vector v = new Vector();        // The new ResultSet tuple stuff

      f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
***************
*** 2642,2647 ****
--- 2641,2699 ----
      f[10] = new Field(connection, "CARDINALITY", iInt4Oid, 4);
      f[11] = new Field(connection, "PAGES", iInt4Oid, 4);
      f[12] = new Field(connection, "FILTER_CONDITION", iVarcharOid, 32);
+
+     r = connection.ExecSQL("select " +
+                 "c.relname, " +
+                 "x.indisunique, " +
+                 "i.relname, " +
+                 "x.indisclustered, " +
+                 "a.amname, " +
+                 "x.indkey, " +
+                 "c.reltuples, " +
+                 "c.relpages " +
+                 "FROM pg_index x, pg_class c, pg_class i, pg_am a " +
+                 "WHERE ((c.relname = '" + tableName.toLowerCase() + "') " +
+                 " AND (c.oid = x.indrelid) " +
+                 " AND (i.oid = x.indexrelid) " +
+                 " AND (c.relam = a.oid)) " +
+                 "ORDER BY x.indisunique DESC, " +
+                 " x.indisclustered, a.amname, i.relname");
+     while (r.next()) {
+         // indkey is an array of column ordinals (integers).  In the JDBC
+         // interface, this has to be separated out into a separate
+         // tuple for each indexed column.  Also, getArray() is not yet
+         // implemented for Postgres JDBC, so we parse by hand.
+         String columnOrdinalString = r.getString(6);
+         StringTokenizer stok = new StringTokenizer(columnOrdinalString);
+         int [] columnOrdinals = new int[stok.countTokens()];
+         int o = 0;
+         while (stok.hasMoreTokens()) {
+             columnOrdinals[o++] = Integer.parseInt(stok.nextToken());
+         }
+         for (int i = 0; i < columnOrdinals.length; i++) {
+             byte [] [] tuple = new byte [13] [];
+             tuple[0] = "".getBytes();
+             tuple[1] = "".getBytes();
+             tuple[2] = r.getBytes(1);
+             tuple[3] = r.getBoolean(2) ? "f".getBytes() : "t".getBytes();
+             tuple[4] = null;
+             tuple[5] = r.getBytes(3);
+             tuple[6] = r.getBoolean(4) ?
+                 Integer.toString(tableIndexClustered).getBytes() :
+                 r.getString(5).equals("hash") ?
+                 Integer.toString(tableIndexHashed).getBytes() :
+                 Integer.toString(tableIndexOther).getBytes();
+             tuple[7] = Integer.toString(i + 1).getBytes();
+             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) + ")"); 
+             columnNameRS.next();
+             tuple[8] = columnNameRS.getBytes(1);
+             tuple[9] = null;  // sort sequence ???
+             tuple[10] = r.getBytes(7);  // inexact
+             tuple[11] = r.getBytes(8);
+             tuple[12] = null;
+             v.addElement(tuple);
+         }
+     }

      return new ResultSet(connection, f, v, "OK", 1);
    }