Fix for HASH for index lookups in ODBC.

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 1bddb87cecc..d40a70cac3e 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 6babe49fafd..6e7ce67edaa 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 b9728dadab4..be4b4e54815 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)