Re: DatabaseMetaData.getIndexInfo() added
От | Bruce Momjian |
---|---|
Тема | Re: DatabaseMetaData.getIndexInfo() added |
Дата | |
Msg-id | 200105161746.f4GHkEQ20763@candle.pha.pa.us обсуждение исходный текст |
Ответ на | DatabaseMetaData.getIndexInfo() added (William Webber <william@ferengi.live.com.au>) |
Список | 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); }
В списке pgsql-patches по дате отправления: