From 0b96c6f4ae79ac659344760c9204650496048ea6 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Mon, 27 Mar 2000 17:04:47 +0000 Subject: [PATCH] Change form of query which used "DISTINCT ON" to help support primary keys. We still have an internal limit in the ODBC code of 8 columns per key, but this should lay the groundwork for resolving that. Includes reformulated query from Tom Lane. --- src/interfaces/odbc/convert.c | 47 +++++++++++++++++++------ src/interfaces/odbc/info.c | 64 +++++++++++++++++++++++++---------- 2 files changed, 83 insertions(+), 28 deletions(-) diff --git a/src/interfaces/odbc/convert.c b/src/interfaces/odbc/convert.c index cef75b258a..24906e10ff 100644 --- a/src/interfaces/odbc/convert.c +++ b/src/interfaces/odbc/convert.c @@ -110,16 +110,16 @@ int copy_and_convert_field(StatementClass *stmt, Int4 field_type, void *value, Int2 fCType, PTR rgbValue, SDWORD cbValueMax, SDWORD *pcbValue) { -Int4 len = 0, copy_len = 0; -SIMPLE_TIME st; -time_t t = time(NULL); -struct tm *tim; -int pcbValueOffset, rgbValueOffset; -char *rgbValueBindRow, *ptr; -int bind_row = stmt->bind_row; -int bind_size = stmt->options.bind_size; -int result = COPY_OK; -char tempBuf[TEXT_FIELD_SIZE+5]; + Int4 len = 0, copy_len = 0; + SIMPLE_TIME st; + time_t t = time(NULL); + struct tm *tim; + int pcbValueOffset, rgbValueOffset; + char *rgbValueBindRow, *ptr; + int bind_row = stmt->bind_row; + int bind_size = stmt->options.bind_size; + int result = COPY_OK; + char tempBuf[TEXT_FIELD_SIZE+5]; /* rgbValueOffset is *ONLY* for character and binary data */ /* pcbValueOffset is for computing any pcbValue location */ @@ -211,11 +211,35 @@ char tempBuf[TEXT_FIELD_SIZE+5]; /* This is for internal use by SQLStatistics() */ case PG_TYPE_INT2VECTOR: { + int nval, i; + char *vp; // this is an array of eight integers short *short_array = (short *) ( (char *) rgbValue + rgbValueOffset); len = 16; + vp = value; + nval = 0; + for (i = 0; i < 8; i++) + { + if (sscanf(vp, "%hd", &short_array[i]) != 1) + break; + + nval++; + + /* skip the current token */ + while ((*vp != '\0') && (! isspace(*vp))) vp++; + /* and skip the space to the next token */ + while ((*vp != '\0') && (isspace(*vp))) vp++; + if (*vp == '\0') + break; + } + for (i = nval; i < 8; i++) + { + short_array[i] = 0; + } + +#if 0 sscanf(value, "%hd %hd %hd %hd %hd %hd %hd %hd", &short_array[0], &short_array[1], @@ -225,13 +249,14 @@ char tempBuf[TEXT_FIELD_SIZE+5]; &short_array[5], &short_array[6], &short_array[7]); +#endif /* There is no corresponding fCType for this. */ if(pcbValue) *(SDWORD *) ((char *) pcbValue + pcbValueOffset) = len; return COPY_OK; /* dont go any further or the data will be trashed */ - } + } /* This is a large object OID, which is used to store LONGVARBINARY objects. */ case PG_TYPE_LO: diff --git a/src/interfaces/odbc/info.c b/src/interfaces/odbc/info.c index 9ff52a9309..e45badc67c 100644 --- a/src/interfaces/odbc/info.c +++ b/src/interfaces/odbc/info.c @@ -1201,10 +1201,12 @@ ConnInfo *ci; // ********************************************************************** // Create the query to find out the columns (Note: pre 6.3 did not have the atttypmod field) // ********************************************************************** - sprintf(columns_query, "select u.usename, c.relname, a.attname, a.atttypid,t.typname, a.attnum, a.attlen, %s, a.attnotnull, c.relhasrules " - "from pg_user u, pg_class c, pg_attribute a, pg_type t where " - "int4out(u.usesysid) = int4out(c.relowner) and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0)", - PROTOCOL_62(ci) ? "a.attlen" : "a.atttypmod"); + sprintf(columns_query, "select u.usename, c.relname, a.attname, a.atttypid" + ", t.typname, a.attnum, a.attlen, %s, a.attnotnull, c.relhasrules" + " from pg_user u, pg_class c, pg_attribute a, pg_type t" + " where u.usesysid = c.relowner" + " and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0)", + PROTOCOL_62(ci) ? "a.attlen" : "a.atttypmod"); my_strcat(columns_query, " and c.relname like '%.*s'", szTableName, cbTableName); my_strcat(columns_query, " and u.usename like '%.*s'", szTableOwner, cbTableOwner); @@ -1593,7 +1595,7 @@ mylog("%s: entering...stmt=%u\n", func, stmt); // ********************************************************************** sprintf(columns_query, "select c.relhasrules " "from pg_user u, pg_class c where " - "int4out(u.usesysid) = int4out(c.relowner) "); + "u.usesysid = c.relowner"); my_strcat(columns_query, " and c.relname like '%.*s'", szTableName, cbTableName); my_strcat(columns_query, " and u.usename like '%.*s'", szTableOwner, cbTableOwner); @@ -1848,8 +1850,11 @@ mylog("%s: entering...stmt=%u\n", func, stmt); } indx_stmt = (StatementClass *) hindx_stmt; - sprintf(index_query, "select c.relname, i.indkey, i.indisunique, i.indisclustered, 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); + sprintf(index_query, "select c.relname, i.indkey, i.indisunique" + ", i.indisclustered, 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)) { @@ -2061,7 +2066,9 @@ static char *func="SQLColumnPrivileges"; } - +/* SQLPrimaryKeys() + * Retrieve the primary key columns for the specified table. + */ RETCODE SQL_API SQLPrimaryKeys( HSTMT hstmt, UCHAR FAR * szTableQualifier, @@ -2135,7 +2142,23 @@ Int2 result_cols; return SQL_ERROR; } +#if 0 sprintf(tables_query, "select distinct on (attnum) a2.attname, a2.attnum from pg_attribute a1, pg_attribute a2, pg_class c, pg_index i where c.relname = '%s_pkey' AND c.oid = i.indexrelid AND a1.attrelid = c.oid AND a2.attrelid = c.oid AND (i.indkey[0] = a1.attnum OR i.indkey[1] = a1.attnum OR i.indkey[2] = a1.attnum OR i.indkey[3] = a1.attnum OR i.indkey[4] = a1.attnum OR i.indkey[5] = a1.attnum OR i.indkey[6] = a1.attnum OR i.indkey[7] = a1.attnum) order by a2.attnum", pktab); +#else + /* Simplified query to remove assumptions about + * number of possible index columns. + * Courtesy of Tom Lane - thomas 2000-03-21 + */ + sprintf(tables_query, "select ta.attname, ia.attnum" + " from pg_attribute ta, pg_attribute ia, pg_class c, pg_index i" + " where c.relname = '%s_pkey'" + " AND c.oid = i.indexrelid" + " AND ia.attrelid = i.indexrelid" + " AND ta.attrelid = i.indrelid" + " AND ta.attnum = i.indkey[ia.attnum-1]" + " order by ia.attnum", pktab); +#endif + mylog("SQLPrimaryKeys: tables_query='%s'\n", tables_query); @@ -2166,11 +2189,11 @@ Int2 result_cols; set_tuplefield_null(&row->tuple[0]); - // I have to hide the table owner from Access, otherwise it - // insists on referring to the table as 'owner.table'. - // (this is valid according to the ODBC SQL grammar, but - // Postgres won't support it.) - + /* I have to hide the table owner from Access, otherwise it + * insists on referring to the table as 'owner.table'. + * (this is valid according to the ODBC SQL grammar, but + * Postgres won't support it.) + */ set_tuplefield_string(&row->tuple[1], ""); set_tuplefield_string(&row->tuple[2], pktab); set_tuplefield_string(&row->tuple[3], attname); @@ -2312,8 +2335,11 @@ Int2 result_cols; */ if (fktab[0] != '\0') { - sprintf(tables_query, "select pg_trigger.tgargs, pg_trigger.tgnargs, pg_trigger.tgname from pg_proc, pg_trigger, pg_class where pg_proc.oid = pg_trigger.tgfoid and pg_trigger.tgrelid = pg_class.oid AND pg_proc.proname = 'check_primary_key' AND pg_class.relname = '%s'", - fktab); + sprintf(tables_query, "select pg_trigger.tgargs, pg_trigger.tgnargs, pg_trigger.tgname" + " from pg_proc, pg_trigger, pg_class" + " where pg_proc.oid = pg_trigger.tgfoid and pg_trigger.tgrelid = pg_class.oid" + " AND pg_proc.proname = 'check_primary_key' AND pg_class.relname = '%s'", + fktab); result = SQLExecDirect(htbl_stmt, tables_query, strlen(tables_query)); if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) { @@ -2487,8 +2513,12 @@ Int2 result_cols; */ else if (pktab[0] != '\0') { - sprintf(tables_query, "select pg_trigger.tgargs, pg_trigger.tgnargs, pg_trigger.tgtype, pg_trigger.tgname from pg_proc, pg_trigger, pg_class where pg_proc.oid = pg_trigger.tgfoid and pg_trigger.tgrelid = pg_class.oid AND pg_proc.proname = 'check_foreign_key' AND pg_class.relname = '%s'", - pktab); + sprintf(tables_query, "select pg_trigger.tgargs, pg_trigger.tgnargs" + ", pg_trigger.tgtype, pg_trigger.tgname" + " from pg_proc, pg_trigger, pg_class" + " where pg_proc.oid = pg_trigger.tgfoid and pg_trigger.tgrelid = pg_class.oid" + " AND pg_proc.proname = 'check_foreign_key' AND pg_class.relname = '%s'", + pktab); result = SQLExecDirect(htbl_stmt, tables_query, strlen(tables_query)); if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) { -- 2.40.0