From 5abaa779c46eef880c993aad5266e64ad754904e Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Wed, 16 May 2001 17:47:27 +0000 Subject: [PATCH] Fix for HASH for index lookups in ODBC. --- .../postgresql/jdbc1/DatabaseMetaData.java | 67 +++++++++++++++++-- .../postgresql/jdbc2/DatabaseMetaData.java | 66 ++++++++++++++++-- src/interfaces/odbc/info.c | 51 ++++++++++++-- 3 files changed, 164 insertions(+), 20 deletions(-) diff --git a/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java b/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java index 1bddb87cec..d40a70cac3 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java @@ -1688,16 +1688,16 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData 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; - case 'v': + case (byte) 'v': relKind = "VIEW"; break; default: @@ -2623,11 +2623,10 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * @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); @@ -2644,6 +2643,60 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData 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); } } diff --git a/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java b/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java index 6babe49faf..6e7ce67eda 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java @@ -1688,16 +1688,16 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData 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; - case 'v': + case (byte) 'v': relKind = "VIEW"; break; default: @@ -2622,11 +2622,10 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * @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); @@ -2643,6 +2642,59 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData 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); } diff --git a/src/interfaces/odbc/info.c b/src/interfaces/odbc/info.c index b9728dadab..be4b4e5481 100644 --- a/src/interfaces/odbc/info.c +++ b/src/interfaces/odbc/info.c @@ -2009,7 +2009,9 @@ SQLStatistics( char *table_name; char index_name[MAX_INFO_STRING]; short fields_vector[16]; - char isunique[10]; + char isunique[10], + isclustered[10], + ishash[MAX_INFO_STRING]; SDWORD index_name_len, fields_vector_len; TupleNode *row; @@ -2169,10 +2171,13 @@ SQLStatistics( indx_stmt = (StatementClass *) hindx_stmt; sprintf(index_query, "select c.relname, i.indkey, i.indisunique" - ", c.relhasrules" - " from pg_index i, pg_class c, pg_class d" - " where c.oid = i.indexrelid and d.relname = '%s'" - " and d.oid = i.indrelid", table_name); + ", x.indisclustered, a.amname, i.relhasrules" + " from pg_index x, pg_class i, pg_class c, pg_am a" + " where c.relname = '%s'" + " and c.oid = x.indrelid" + " and x.indexrelid = i.oid" + " and i.relam = a.oid" + , table_name); result = SQLExecDirect(hindx_stmt, index_query, strlen(index_query)); if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) @@ -2224,7 +2229,33 @@ SQLStatistics( goto SEEYA; } + /* bind the "is clustered" column */ result = SQLBindCol(hindx_stmt, 4, SQL_C_CHAR, + isclustered, sizeof(isclustered), NULL); + if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) + { + stmt->errormsg = indx_stmt->errormsg; /* "Couldn't bind column + * in SQLStatistics."; */ + stmt->errornumber = indx_stmt->errornumber; + SQLFreeStmt(hindx_stmt, SQL_DROP); + goto SEEYA; + + } + + /* bind the "is hash" column */ + result = SQLBindCol(hindx_stmt, 5, SQL_C_CHAR, + ishash, sizeof(ishash), NULL); + if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) + { + stmt->errormsg = indx_stmt->errormsg; /* "Couldn't bind column + * in SQLStatistics."; */ + stmt->errornumber = indx_stmt->errornumber; + SQLFreeStmt(hindx_stmt, SQL_DROP); + goto SEEYA; + + } + + result = SQLBindCol(hindx_stmt, 6, SQL_C_CHAR, relhasrules, MAX_INFO_STRING, NULL); if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) { @@ -2255,6 +2286,9 @@ SQLStatistics( sprintf(buf, "%s_idx_fake_oid", table_name); set_tuplefield_string(&row->tuple[5], buf); + /* + * Clustered/HASH index? + */ set_tuplefield_int2(&row->tuple[6], (Int2) SQL_INDEX_OTHER); set_tuplefield_int2(&row->tuple[7], (Int2) 1); @@ -2297,7 +2331,12 @@ SQLStatistics( set_tuplefield_string(&row->tuple[4], ""); set_tuplefield_string(&row->tuple[5], index_name); - set_tuplefield_int2(&row->tuple[6], (Int2) SQL_INDEX_OTHER); + /* + * Clustered/HASH index? + */ + set_tuplefield_int2(&row->tuple[6], (Int2) + (atoi(isclustered) ? SQL_INDEX_CLUSTERED : + (!strncmp(ishash, "hash", 4)) ? SQL_INDEX_HASHED : SQL_INDEX_OTHER); set_tuplefield_int2(&row->tuple[7], (Int2) (i + 1)); if (fields_vector[i] == OID_ATTNUM) -- 2.40.0