Re: [PATCHES] DatabaseMetaData.getIndexInfo() added - Mailing list pgsql-jdbc
From | Bruce Momjian |
---|---|
Subject | Re: [PATCHES] DatabaseMetaData.getIndexInfo() added |
Date | |
Msg-id | 200105160235.f4G2Zk523773@candle.pha.pa.us Whole thread Raw |
List | pgsql-jdbc |
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); }
pgsql-jdbc by date: