Changeset: e2e40ccb8c75 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/e2e40ccb8c75 Modified Files: clients/odbc/ChangeLog clients/odbc/driver/SQLPrimaryKeys.c clients/odbc/driver/SQLSpecialColumns.c clients/odbc/driver/SQLStatistics.c clients/odbc/samples/metadata.c Branch: default Log Message:
Extended tests in metadata program with local and global temporary tables. Corrected SQLPrimaryKeys(), SQLSpecialColumns() and SQLStatistics() for global temporary tables. Although they are located in tmp schema, their metadata is stored in the sys tables, not the tmp tables. So the previous fix for tmp tables was not enough. This now works as desired. Also implemented some TODOs and corrections for SQLSpecialColumns() and SQLStatistics(), see ChangeLog. diffs (truncated from 941 to 300 lines): diff --git a/clients/odbc/ChangeLog b/clients/odbc/ChangeLog --- a/clients/odbc/ChangeLog +++ b/clients/odbc/ChangeLog @@ -1,6 +1,17 @@ # ChangeLog file for odbc # This file is updated with Maddlog +* Thu May 19 2022 Martin van Dinther <[email protected]> +- Corrected SQLSpecialColumns(..., SQL_BEST_ROWID, ...). Previously it only + returned rows when the table had a primary key. Now it also returns + rows when a table has a unique constraint. +- Corrected SQLStatistics(..., SQL_INDEX_ALL, ...). Previously it only + returned rows when the table had a primary or unique key. Now it also + returns rows for indices which are not based on a key. +- Corrected SQLStatistics(..., SQL_ENSURE). It now returns CARDINALITY + information for columns based on a primary/unique key. Previously it + always returned NULL for the CARDINALITY result column. + * Thu Apr 21 2022 Martin van Dinther <[email protected]> - Corrected ODBC API functions SQLPrimaryKeys(), SQLSpecialColumns() and SQLStatistics() for local temporary tables located in schema tmp. They did diff --git a/clients/odbc/driver/SQLPrimaryKeys.c b/clients/odbc/driver/SQLPrimaryKeys.c --- a/clients/odbc/driver/SQLPrimaryKeys.c +++ b/clients/odbc/driver/SQLPrimaryKeys.c @@ -45,7 +45,7 @@ MNDBPrimaryKeys(ODBCStmt *stmt, size_t querylen; size_t pos = 0; char *sch = NULL, *tab = NULL; - char *sysORtmp = "sys"; + bool addTmpQuery = false; /* deal with SQL_NTS and SQL_NULL_DATA */ fixODBCstring(CatalogName, NameLength1, SQLSMALLINT, @@ -106,16 +106,22 @@ MNDBPrimaryKeys(ODBCStmt *stmt, } } + /* determine if we need to add a query against the tmp.* tables */ + addTmpQuery = (SchemaName == NULL) + || (SchemaName != NULL + && (strcmp((const char *) SchemaName, "tmp") == 0 + || strchr((const char *) SchemaName, '%') != NULL + || strchr((const char *) SchemaName, '_') != NULL)); + /* construct the query */ - querylen = 1000 + strlen(stmt->Dbc->dbname) + (sch ? strlen(sch) : 0) + - (tab ? strlen(tab) : 0); + querylen = 1000 + strlen(stmt->Dbc->dbname) + + (sch ? strlen(sch) : 0) + (tab ? strlen(tab) : 0); + if (addTmpQuery) + querylen *= 2; query = malloc(querylen); if (query == NULL) goto nomem; - if (SchemaName != NULL && strcmp((const char *) SchemaName, "tmp") == 0) - sysORtmp = "tmp"; - /* SQLPrimaryKeys returns a table with the following columns: VARCHAR table_cat VARCHAR table_schem @@ -123,22 +129,20 @@ MNDBPrimaryKeys(ODBCStmt *stmt, VARCHAR column_name NOT NULL SMALLINT key_seq NOT NULL VARCHAR pk_name - */ + */ pos += snprintf(query + pos, querylen - pos, - "select '%s' as table_cat, " - "s.name as table_schem, " - "t.name as table_name, " - "kc.name as column_name, " - "cast(kc.nr + 1 as smallint) as key_seq, " - "k.name as pk_name " - "from sys.schemas s, %s._tables t, " - "%s.keys k, %s.objects kc " - "where k.id = kc.id and " + "select '%s' as table_cat, " + "s.name as table_schem, " + "t.name as table_name, " + "kc.name as column_name, " + "cast(kc.nr + 1 as smallint) as key_seq, " + "k.name as pk_name " + "from sys.keys k, sys.objects kc, sys._tables t, sys.schemas s " + "where k.type = 0 and " + "k.id = kc.id and " "k.table_id = t.id and " - "t.schema_id = s.id and " - "k.type = 0", - stmt->Dbc->dbname, - sysORtmp, sysORtmp, sysORtmp); + "t.schema_id = s.id", + stmt->Dbc->dbname); assert(pos < 800); /* Construct the selection condition query part */ @@ -152,17 +156,59 @@ MNDBPrimaryKeys(ODBCStmt *stmt, if (sch) { /* filtering requested on schema name */ pos += snprintf(query + pos, querylen - pos, " and %s", sch); - free(sch); } if (tab) { /* filtering requested on table name */ pos += snprintf(query + pos, querylen - pos, " and %s", tab); + } + + if (addTmpQuery) { + /* we must also include the keys of local temporary tables + which are stored in tmp.keys, tmp.objects and tmp._tables */ + pos += snprintf(query + pos, querylen - pos, + " UNION ALL " + "select '%s' as table_cat, " + "s.name as table_schem, " + "t.name as table_name, " + "kc.name as column_name, " + "cast(kc.nr + 1 as smallint) as key_seq, " + "k.name as pk_name " + "from tmp.keys k, tmp.objects kc, tmp._tables t, sys.schemas s " + "where k.type = 0 and " + "k.id = kc.id and " + "k.table_id = t.id and " + "t.schema_id = s.id", + stmt->Dbc->dbname); + + /* Construct the selection condition query part */ + if (NameLength1 > 0 && CatalogName != NULL) { + /* filtering requested on catalog name */ + if (strcmp((char *) CatalogName, stmt->Dbc->dbname) != 0) { + /* catalog name does not match the database name, so return no rows */ + pos += snprintf(query + pos, querylen - pos, " and 1=2"); + } + } + if (sch) { + /* filtering requested on schema name */ + pos += snprintf(query + pos, querylen - pos, " and %s", sch); + } + if (tab) { + /* filtering requested on table name */ + pos += snprintf(query + pos, querylen - pos, " and %s", tab); + } + } + assert(pos < (querylen - 43)); + + if (sch) + free(sch); + if (tab) free(tab); - } /* add the ordering */ pos += strcpy_len(query + pos, " order by table_schem, table_name, key_seq", querylen - pos); + /* debug: fprintf(stdout, "SQLPrimaryKeys SQL:\n%s\n\n", query); */ + /* query the MonetDB data dictionary tables */ rc = MNDBExecDirect(stmt, (SQLCHAR *) query, (SQLINTEGER) pos); diff --git a/clients/odbc/driver/SQLSpecialColumns.c b/clients/odbc/driver/SQLSpecialColumns.c --- a/clients/odbc/driver/SQLSpecialColumns.c +++ b/clients/odbc/driver/SQLSpecialColumns.c @@ -48,7 +48,6 @@ translateIdentifierType(SQLUSMALLINT Ide static char * translateScope(SQLUSMALLINT Scope) { - /* check for valid Scope argument */ switch (Scope) { case SQL_SCOPE_CURROW: return "SQL_SCOPE_CURROW"; @@ -64,7 +63,6 @@ translateScope(SQLUSMALLINT Scope) static char * translateNullable(SQLUSMALLINT Nullable) { - /* check for valid Nullable argument */ switch (Nullable) { case SQL_NO_NULLS: return "SQL_NO_NULLS"; @@ -95,7 +93,6 @@ MNDBSpecialColumns(ODBCStmt *stmt, size_t querylen; size_t pos = 0; char *sch = NULL, *tab = NULL; - char *sysORtmp = "sys"; fixODBCstring(CatalogName, NameLength1, SQLSMALLINT, addStmtError, stmt, return SQL_ERROR); fixODBCstring(SchemaName, NameLength2, SQLSMALLINT, addStmtError, stmt, return SQL_ERROR); @@ -164,8 +161,15 @@ MNDBSpecialColumns(ODBCStmt *stmt, INTEGER buffer_length SMALLINT decimal_digits SMALLINT pseudo_column - */ + */ if (IdentifierType == SQL_BEST_ROWID) { + /* determine if we need to add a query against the tmp.* tables */ + bool addTmpQuery = (SchemaName == NULL) + || (SchemaName != NULL + && (strcmp((const char *) SchemaName, "tmp") == 0 + || strchr((const char *) SchemaName, '%') != NULL + || strchr((const char *) SchemaName, '_') != NULL)); + /* Select from the key table the (smallest) primary/unique key */ if (stmt->Dbc->sql_attr_metadata_id == SQL_FALSE) { if (NameLength2 > 0) { @@ -199,15 +203,15 @@ MNDBSpecialColumns(ODBCStmt *stmt, } } - /* first create a string buffer (1000 extra bytes is plenty */ - querylen = 5000 + NameLength1 + NameLength2 + NameLength3; + /* construct the query */ + querylen = 5000 + strlen(stmt->Dbc->dbname) + + (sch ? strlen(sch) : 0) + (tab ? strlen(tab) : 0); + if (addTmpQuery) + querylen *= 2; query = malloc(querylen); if (query == NULL) goto nomem; - if (SchemaName != NULL && strcmp((const char *) SchemaName, "tmp") == 0) - sysORtmp = "tmp"; - /* Note: SCOPE is SQL_SCOPE_TRANSACTION */ /* Note: PSEUDO_COLUMN is SQL_PC_NOT_PSEUDO */ pos += snprintf(query + pos, querylen - pos, @@ -222,16 +226,16 @@ MNDBSpecialColumns(ODBCStmt *stmt, DECIMAL_DIGITS(c) ", " "cast(%d as smallint) as pseudo_column " "from sys.schemas s, " - "%s._tables t, " - "%s._columns c, " - "%s.keys k, " - "%s.objects kc " + "sys._tables t, " + "sys._columns c, " + "sys.keys k, " + "sys.objects kc " "where s.id = t.schema_id and " "t.id = c.table_id and " "t.id = k.table_id and " "c.name = kc.name and " "kc.id = k.id and " - "k.type = 0", + "k.type in (0, 1)", /* primary key (type = 0), unique key (type = 1) */ /* scope: */ SQL_SCOPE_TRANSACTION, #ifdef DATA_TYPE_ARGS @@ -250,16 +254,12 @@ MNDBSpecialColumns(ODBCStmt *stmt, DECIMAL_DIGITS_ARGS, #endif /* pseudo_column: */ - SQL_PC_NOT_PSEUDO, - sysORtmp, sysORtmp, sysORtmp, sysORtmp); + SQL_PC_NOT_PSEUDO); assert(pos < 4300); /* TODO: improve the SQL to get the correct result: - - only one set of columns should be returned, also - when multiple primary keys are available for this - table. - - when the table has NO primary key it should - return the columns of a unique key (only from ONE - unique key which is also the best/smallest key) + - only one set of unique constraint columns should be + returned when multiple unique constraints are available + for this table. Return the smallest/best one only. TODO: optimize SQL: - when no SchemaName is set (see above) also no filtering on SCHEMA NAME and join with table @@ -277,12 +277,10 @@ MNDBSpecialColumns(ODBCStmt *stmt, if (sch) { /* filtering requested on schema name */ pos += snprintf(query + pos, querylen - pos, " and %s", sch); - free(sch); } if (tab) { /* filtering requested on table name */ pos += snprintf(query + pos, querylen - pos, " and %s", tab); - free(tab); } /* add an extra selection when SQL_NO_NULLS is requested */ @@ -290,25 +288,135 @@ MNDBSpecialColumns(ODBCStmt *stmt, pos += strcpy_len(query + pos, " and c.\"null\" = false", querylen - pos); } - pos += snprintf(query + pos, querylen - pos, - "), " + pos += strcpy_len(query + pos, + "), " "tid as (" - "select t.id as tid " - "from %s._tables t, %s.keys k " - "where t.id = k.table_id and k.type = 0" - ") " + "select table_id as tid " + "from sys.keys " + "where type = 0" + ") " + , querylen - pos); + + if (addTmpQuery) { + /* we must also include the primary key or unique _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
