Here is my version of your patch. I have gotten the hash test to work.
Can someone test this? It compiles OK, but I am unsure how to test this
feature.
I read about Cardinality in the comments and I think you got it fine.
> 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/jdbc2/DatabaseMetaData.java
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java,v
retrieving revision 1.15
diff -c -r1.15 DatabaseMetaData.java
*** src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java 2001/02/16 16:45:00 1.15
--- src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java 2001/05/16 02:31:17
***************
*** 1688,1700 ****
String relKind;
switch (r.getBytes(3)[0]) {
! case 'r':
relKind = "TABLE";
break;
! case 'i':
relKind = "INDEX";
break;
! case 'S':
relKind = "SEQUENCE";
break;
default:
--- 1688,1700 ----
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;
default:
***************
*** 2513,2523 ****
* @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);
--- 2513,2522 ----
* @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);
***************
*** 2533,2538 ****
--- 2532,2590 ----
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);
}