Thread: Fix for SQLStatistics
Attached is a small patch to SQLStatistics() to allow proper information about CLUSTER and HASH indexes, as proposed by the ODBC code. Right now the CLUSTER information is not maintained, but the HASH is accurate. Also, I found that the tests for UNIQUE and CLUSTER were done using atoi(). I don't think that works because these queries return 't' or 'f', not 1 or 0. Can someone confirm I have have the t/f and 1/0 stuff correct, and can someone test a HASH index to see that it displays properly? I don't know how to call SQLStatistics. Thanks. -- 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/odbc/info.c =================================================================== RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/odbc/info.c,v retrieving revision 1.47 diff -c -r1.47 info.c *** src/interfaces/odbc/info.c 2001/05/14 21:53:16 1.47 --- src/interfaces/odbc/info.c 2001/05/15 19:17:00 *************** *** 2009,2015 **** char *table_name; char index_name[MAX_INFO_STRING]; short fields_vector[16]; ! char isunique[10]; SDWORD index_name_len, fields_vector_len; TupleNode *row; --- 2009,2017 ---- char *table_name; char index_name[MAX_INFO_STRING]; short fields_vector[16]; ! char isunique[10], ! isclustered[10], ! ishash[MAX_INFO_STRING]; SDWORD index_name_len, fields_vector_len; TupleNode *row; *************** *** 2169,2178 **** 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); result = SQLExecDirect(hindx_stmt, index_query, strlen(index_query)); if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) --- 2171,2183 ---- indx_stmt = (StatementClass *) hindx_stmt; sprintf(index_query, "select c.relname, i.indkey, i.indisunique" ! ", i.indisclustered, a.amname, c.relhasrules" ! " from pg_index i, pg_class c, pg_class d, pg_am a" ! " where d.relname = '%s'" ! " and d.oid = i.indrelid" ! " and i.indexrelid = c.oid" ! " and c.relam = a.oid" ! , table_name); result = SQLExecDirect(hindx_stmt, index_query, strlen(index_query)); if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) *************** *** 2224,2230 **** --- 2229,2261 ---- 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,2260 **** --- 2286,2295 ---- sprintf(buf, "%s_idx_fake_oid", table_name); set_tuplefield_string(&row->tuple[5], buf); + /* + * Clustered index? I think non-clustered should be type OTHER + * not HASHED + */ set_tuplefield_int2(&row->tuple[6], (Int2) SQL_INDEX_OTHER); set_tuplefield_int2(&row->tuple[7], (Int2) 1); *************** *** 2272,2278 **** { /* If only requesting unique indexs, then just return those. */ if (fUnique == SQL_INDEX_ALL || ! (fUnique == SQL_INDEX_UNIQUE && atoi(isunique))) { i = 0; /* add a row in this table for each field in the index */ --- 2307,2313 ---- { /* If only requesting unique indexs, then just return those. */ if (fUnique == SQL_INDEX_ALL || ! (fUnique == SQL_INDEX_UNIQUE && isunique[0] == 't')) { i = 0; /* add a row in this table for each field in the index */ *************** *** 2289,2295 **** /* non-unique index? */ if (globals.unique_index) ! set_tuplefield_int2(&row->tuple[3], (Int2) (atoi(isunique) ? FALSE : TRUE)); else set_tuplefield_int2(&row->tuple[3], TRUE); --- 2324,2330 ---- /* non-unique index? */ if (globals.unique_index) ! set_tuplefield_int2(&row->tuple[3], (Int2) (isunique[0] == 't' ? FALSE : TRUE)); else set_tuplefield_int2(&row->tuple[3], TRUE); *************** *** 2297,2303 **** set_tuplefield_string(&row->tuple[4], ""); set_tuplefield_string(&row->tuple[5], index_name); ! set_tuplefield_int2(&row->tuple[6], (Int2) SQL_INDEX_OTHER); set_tuplefield_int2(&row->tuple[7], (Int2) (i + 1)); if (fields_vector[i] == OID_ATTNUM) --- 2332,2344 ---- set_tuplefield_string(&row->tuple[4], ""); set_tuplefield_string(&row->tuple[5], index_name); ! /* ! * Clustered index? I think non-clustered should be type ! * OTHER not HASHED ! */ ! set_tuplefield_int2(&row->tuple[6], (Int2) ! (isclustered[0] == 't') ? 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)
Bruce Momjian wrote: > > Attached is a small patch to SQLStatistics() to allow proper information > about CLUSTER and HASH indexes, as proposed by the ODBC code. Right now > the CLUSTER information is not maintained, but the HASH is accurate. > Also, I found that the tests for UNIQUE and CLUSTER were done using > atoi(). I don't think that works because these queries return 't' or > 'f', not 1 or 0. > Psqlodbc driver automatically coverts t/f to 1/0. regards, Hiroshi Inoue
[ Charset US-ASCII unsupported, converting... ] > Bruce Momjian wrote: > > > > Attached is a small patch to SQLStatistics() to allow proper information > > about CLUSTER and HASH indexes, as proposed by the ODBC code. Right now > > the CLUSTER information is not maintained, but the HASH is accurate. > > Also, I found that the tests for UNIQUE and CLUSTER were done using > > atoi(). I don't think that works because these queries return 't' or > > 'f', not 1 or 0. > > > > Psqlodbc driver automatically coverts t/f to 1/0. How does it know when to do the conversion? Is there something that flags is as a boolean? -- 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
> [ Charset US-ASCII unsupported, converting... ] > > Bruce Momjian wrote: > > > > > > Attached is a small patch to SQLStatistics() to allow proper information > > > about CLUSTER and HASH indexes, as proposed by the ODBC code. Right now > > > the CLUSTER information is not maintained, but the HASH is accurate. > > > Also, I found that the tests for UNIQUE and CLUSTER were done using > > > atoi(). I don't think that works because these queries return 't' or > > > 'f', not 1 or 0. > > > > > > > Psqlodbc driver automatically coverts t/f to 1/0. > > How does it know when to do the conversion? Is there something that > flags is as a boolean? I think I see how it is done. Fixing patch now. -- 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
OK, here is a new version of the patch, with the true/false stuff fixed. [ Charset US-ASCII unsupported, converting... ] > Bruce Momjian wrote: > > > > Attached is a small patch to SQLStatistics() to allow proper information > > about CLUSTER and HASH indexes, as proposed by the ODBC code. Right now > > the CLUSTER information is not maintained, but the HASH is accurate. > > Also, I found that the tests for UNIQUE and CLUSTER were done using > > atoi(). I don't think that works because these queries return 't' or > > 'f', not 1 or 0. > > > > Psqlodbc driver automatically coverts t/f to 1/0. > > regards, > Hiroshi Inoue > -- 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/odbc/info.c =================================================================== RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/odbc/info.c,v retrieving revision 1.47 diff -c -r1.47 info.c *** src/interfaces/odbc/info.c 2001/05/14 21:53:16 1.47 --- src/interfaces/odbc/info.c 2001/05/16 02:36:41 *************** *** 2009,2015 **** char *table_name; char index_name[MAX_INFO_STRING]; short fields_vector[16]; ! char isunique[10]; SDWORD index_name_len, fields_vector_len; TupleNode *row; --- 2009,2017 ---- char *table_name; char index_name[MAX_INFO_STRING]; short fields_vector[16]; ! char isunique[10], ! isclustered[10], ! ishash[MAX_INFO_STRING]; SDWORD index_name_len, fields_vector_len; TupleNode *row; *************** *** 2169,2178 **** 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); result = SQLExecDirect(hindx_stmt, index_query, strlen(index_query)); if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) --- 2171,2183 ---- indx_stmt = (StatementClass *) hindx_stmt; sprintf(index_query, "select c.relname, i.indkey, i.indisunique" ! ", 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,2230 **** --- 2229,2261 ---- 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,2260 **** --- 2286,2294 ---- 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,2303 **** set_tuplefield_string(&row->tuple[4], ""); set_tuplefield_string(&row->tuple[5], index_name); ! set_tuplefield_int2(&row->tuple[6], (Int2) SQL_INDEX_OTHER); set_tuplefield_int2(&row->tuple[7], (Int2) (i + 1)); if (fields_vector[i] == OID_ATTNUM) --- 2331,2342 ---- set_tuplefield_string(&row->tuple[4], ""); set_tuplefield_string(&row->tuple[5], index_name); ! /* ! * 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)