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

Peter, can you comment on this patch?


> 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
--- DatabaseMetaData.java.old    Thu Feb  1 14:28:09 2001
+++ DatabaseMetaData.java    Thu Feb  1 14:43:07 2001
@@ -1689,13 +1689,13 @@

     String relKind;
     switch (r.getBytes(3)[0]) {
-    case 'r':
+    case (byte) 'r':
         relKind = "TABLE";
         break;
-    case 'i':
+    case (byte) 'i':
         relKind = "INDEX";
         break;
-    case 'S':
+    case (byte) 'S':
         relKind = "SEQUENCE";
         break;
     default:
@@ -2515,11 +2515,10 @@
    * @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 
+  public java.sql.ResultSet getIndexInfo(String catalog, String schema, String tableName, 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
+    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);
@@ -2535,6 +2534,42 @@
     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, x.indkey, c.reltuples,
c.relpages,c.oid FROM pg_index x, pg_class c, pg_class i WHERE ((c.oid = x.indrelid) AND (i.oid = x.indexrelid) AND
(c.relname= '" + tableName.toLowerCase() + "')) ORDER BY x.indisunique DESC, x.indisclustered, 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(5);
+        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()
+                : Integer.toString(tableIndexHashed).getBytes(); // ??? I guess
+            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(6);  // inexact
+            tuple[11] = r.getBytes(7);
+            tuple[12] = null;
+            v.addElement(tuple);
+        }
+    }

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