Changeset: 742940373c8f for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/742940373c8f Branch: sql_profiler Log Message:
Merge with default. diffs (truncated from 803 to 300 lines): diff --git a/clients/odbc/CMakeLists.txt b/clients/odbc/CMakeLists.txt --- a/clients/odbc/CMakeLists.txt +++ b/clients/odbc/CMakeLists.txt @@ -14,4 +14,5 @@ if(ODBC_FOUND) endif() add_subdirectory(driver) add_subdirectory(samples) + add_subdirectory(tests) endif() diff --git a/clients/odbc/ChangeLog b/clients/odbc/ChangeLog --- a/clients/odbc/ChangeLog +++ b/clients/odbc/ChangeLog @@ -1,6 +1,12 @@ # ChangeLog file for odbc # This file is updated with Maddlog +* Thu Jun 23 2022 Martin van Dinther <[email protected]> +- Corrected ODBC functions SQLTablePrivileges() and SQLColumnPrivileges() + for local temporary tables located in schema tmp. They did not return + any rows when the temporary table had privileges set. Now they do return + rows as expected. + * Wed Jun 22 2022 Martin van Dinther <[email protected]> - Improved SQLProcedures() and SQLProcedureColumns(). They now list information also for all built-in system procedures and functions, not @@ -48,7 +54,7 @@ always returned NULL for the CARDINALITY result column. * Thu Apr 21 2022 Martin van Dinther <[email protected]> -- Corrected ODBC API functions SQLPrimaryKeys(), SQLSpecialColumns() and +- Corrected ODBC functions SQLPrimaryKeys(), SQLSpecialColumns() and SQLStatistics() for local temporary tables located in schema tmp. They did not return any rows when the temp table had a primary or unique key or index. Now they do return rows as expected. diff --git a/clients/odbc/driver/SQLColumnPrivileges.c b/clients/odbc/driver/SQLColumnPrivileges.c --- a/clients/odbc/driver/SQLColumnPrivileges.c +++ b/clients/odbc/driver/SQLColumnPrivileges.c @@ -72,14 +72,14 @@ MNDBColumnPrivileges(ODBCStmt *stmt, goto nomem; } if (NameLength3 > 0) { - tab = ODBCParseOA("t", "name", + tab = ODBCParseOA("tc", "tname", (const char *) TableName, (size_t) NameLength3); if (tab == NULL) goto nomem; } if (NameLength4 > 0) { - col = ODBCParsePV("c", "name", + col = ODBCParsePV("tc", "cname", (const char *) ColumnName, (size_t) NameLength4); if (col == NULL) @@ -94,14 +94,14 @@ MNDBColumnPrivileges(ODBCStmt *stmt, goto nomem; } if (NameLength3 > 0) { - tab = ODBCParseID("t", "name", + tab = ODBCParseID("tc", "tname", (const char *) TableName, (size_t) NameLength3); if (tab == NULL) goto nomem; } if (NameLength4 > 0) { - col = ODBCParseID("c", "name", + col = ODBCParseID("tc", "cname", (const char *) ColumnName, (size_t) NameLength4); if (col == NULL) @@ -110,7 +110,7 @@ MNDBColumnPrivileges(ODBCStmt *stmt, } /* construct the query now */ - querylen = 1200 + strlen(stmt->Dbc->dbname) + (sch ? strlen(sch) : 0) + + querylen = 1300 + strlen(stmt->Dbc->dbname) + (sch ? strlen(sch) : 0) + (tab ? strlen(tab) : 0) + (col ? strlen(col) : 0); query = malloc(querylen); if (query == NULL) @@ -130,8 +130,8 @@ MNDBColumnPrivileges(ODBCStmt *stmt, pos += snprintf(query + pos, querylen - pos, "select '%s' as \"TABLE_CAT\", " "s.name as \"TABLE_SCHEM\", " - "t.name as \"TABLE_NAME\", " - "c.name as \"COLUMN_NAME\", " + "tc.tname as \"TABLE_NAME\", " + "tc.cname as \"COLUMN_NAME\", " "case a.id " "when s.owner " "then '_SYSTEM' " @@ -147,17 +147,23 @@ MNDBColumnPrivileges(ODBCStmt *stmt, "when 0 then 'NO' " "end as \"IS_GRANTABLE\" " "from sys.schemas as s, " - "sys._tables as t, " - "sys._columns as c, " + /* next union all subquery is much more efficient than using sys.tables join sys.columns */ + "(select t1.id as tid, t1.name as tname, t1.schema_id, c1.id as cid, c1.name as cname" + " from sys._tables as t1" + " join sys._columns as c1 on t1.id = c1.table_id" + " where not t1.system" /* exclude system tables and views */ + " union all" + " select t2.id as tid, t2.name as tname, t2.schema_id, c2.id as cid, c2.name as cname" + " from tmp._tables as t2" + " join tmp._columns as c2 on t2.id = c2.table_id)" + " as tc(tid, tname, schema_id, cid, cname), " "sys.auths as a, " "sys.privileges as p, " "sys.auths as g, " "%s " - "where p.obj_id = c.id and " - "c.table_id = t.id and " + "where p.obj_id = tc.cid and " "p.auth_id = a.id and " - "t.schema_id = s.id and " - "not t.system and " + "tc.schema_id = s.id and " "p.grantor = g.id and " "p.privileges = pc.privilege_code_id", stmt->Dbc->dbname, @@ -170,7 +176,7 @@ MNDBColumnPrivileges(ODBCStmt *stmt, "(8, 'DELETE'), " "(16, 'EXECUTE'), " "(32, 'GRANT')) as pc(privilege_code_id, privilege_code_name)"); - assert(pos < querylen); + assert(pos < 1200); /* Construct the selection condition query part */ if (NameLength1 > 0 && CatalogName != NULL) { @@ -198,7 +204,9 @@ MNDBColumnPrivileges(ODBCStmt *stmt, /* add the ordering (exclude table_cat as it is the same for all rows) */ pos += strcpy_len(query + pos, " order by \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\", \"PRIVILEGE\"", querylen - pos); - assert(pos <= querylen); + assert(pos < querylen); + + /* debug: fprintf(stdout, "SQLColumnPrivileges query (pos: %zu, len: %zu):\n%s\n\n", pos, strlen(query), query); */ /* query the MonetDB data dictionary tables */ rc = MNDBExecDirect(stmt, (SQLCHAR *) query, (SQLINTEGER) pos); diff --git a/clients/odbc/driver/SQLColumns.c b/clients/odbc/driver/SQLColumns.c --- a/clients/odbc/driver/SQLColumns.c +++ b/clients/odbc/driver/SQLColumns.c @@ -244,6 +244,9 @@ MNDBColumns(ODBCStmt *stmt, /* add the ordering (exclude table_cat as it is the same for all rows) */ pos += strcpy_len(query + pos, " order by \"TABLE_SCHEM\", \"TABLE_NAME\", \"ORDINAL_POSITION\"", querylen - pos); + assert(pos < querylen); + + /* debug: fprintf(stdout, "SQLColumns query (pos: %zu, len: %zu):\n%s\n\n", pos, strlen(query), query); */ /* query the MonetDB data dictionary tables */ rc = MNDBExecDirect(stmt, (SQLCHAR *) query, (SQLINTEGER) pos); diff --git a/clients/odbc/driver/SQLForeignKeys.c b/clients/odbc/driver/SQLForeignKeys.c --- a/clients/odbc/driver/SQLForeignKeys.c +++ b/clients/odbc/driver/SQLForeignKeys.c @@ -252,8 +252,9 @@ MNDBForeignKeys(ODBCStmt *stmt, PKTableName != NULL ? "FK" : "PK", PKTableName != NULL ? "FK" : "PK", PKTableName != NULL ? "FK" : "PK"); + assert(pos < querylen); - /* debug: fprintf(stdout, "SQLForeignKeys SQL (%zu):\n%s\n\n", pos, query); */ + /* debug: fprintf(stdout, "SQLForeignKeys query (pos: %zu, len: %zu):\n%s\n\n", pos, strlen(query), query); */ /* query the MonetDB data dictionary tables */ rc = MNDBExecDirect(stmt, (SQLCHAR *) query, (SQLINTEGER) pos); 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 @@ -206,8 +206,9 @@ MNDBPrimaryKeys(ODBCStmt *stmt, /* add the ordering */ pos += strcpy_len(query + pos, " order by \"TABLE_SCHEM\", \"TABLE_NAME\", \"KEY_SEQ\"", querylen - pos); + assert(pos < querylen); - /* debug: fprintf(stdout, "SQLPrimaryKeys SQL:\n%s\n\n", query); */ + /* debug: fprintf(stdout, "SQLPrimaryKeys query (pos: %zu, len: %zu):\n%s\n\n", pos, strlen(query), query); */ /* query the MonetDB data dictionary tables */ rc = MNDBExecDirect(stmt, (SQLCHAR *) query, (SQLINTEGER) pos); diff --git a/clients/odbc/driver/SQLProcedureColumns.c b/clients/odbc/driver/SQLProcedureColumns.c --- a/clients/odbc/driver/SQLProcedureColumns.c +++ b/clients/odbc/driver/SQLProcedureColumns.c @@ -256,8 +256,9 @@ MNDBProcedureColumns(ODBCStmt *stmt, /* add the ordering (exclude procedure_cat as it is the same for all rows) */ pos += strcpy_len(query + pos, " order by \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\", \"COLUMN_TYPE\", \"ORDINAL_POSITION\"", querylen - pos); + assert(pos < querylen); - /* debug: fprintf(stdout, "SQLProcedureColumns SQL (%zu):\n%s\n\n", pos, query); */ + /* debug: fprintf(stdout, "SQLProcedureColumns query (pos: %zu, len: %zu):\n%s\n\n", pos, strlen(query), query); */ /* query the MonetDB data dictionary tables */ rc = MNDBExecDirect(stmt, (SQLCHAR *) query, (SQLINTEGER) pos); diff --git a/clients/odbc/driver/SQLProcedures.c b/clients/odbc/driver/SQLProcedures.c --- a/clients/odbc/driver/SQLProcedures.c +++ b/clients/odbc/driver/SQLProcedures.c @@ -164,8 +164,9 @@ MNDBProcedures(ODBCStmt *stmt, /* add the ordering (exclude procedure_cat as it is the same for all rows) */ pos += strcpy_len(query + pos, " order by \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\"", querylen - pos); + assert(pos < querylen); - /* debug: fprintf(stdout, "SQLProcedures SQL (%zu):\n%s\n\n", pos, query); */ + /* debug: fprintf(stdout, "SQLProcedures query (pos: %zu, len: %zu):\n%s\n\n", pos, strlen(query), 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 @@ -90,7 +90,6 @@ MNDBSpecialColumns(ODBCStmt *stmt, /* buffer for the constructed query to do meta data retrieval */ char *query = NULL; - size_t querylen; size_t pos = 0; char *sch = NULL, *tab = NULL; @@ -163,6 +162,8 @@ MNDBSpecialColumns(ODBCStmt *stmt, SMALLINT PSEUDO_COLUMN */ if (IdentifierType == SQL_BEST_ROWID) { + size_t querylen; + /* determine if we need to add a query against the tmp.* tables */ bool addTmpQuery = (SchemaName == NULL) || (SchemaName != NULL @@ -419,6 +420,7 @@ MNDBSpecialColumns(ODBCStmt *stmt, if (pos >= querylen) fprintf(stderr, "pos >= querylen, %zu > %zu\n", pos, querylen); + assert(pos < querylen); } else { assert(IdentifierType == SQL_ROWVER); /* The backend does not have such info available */ @@ -438,7 +440,7 @@ MNDBSpecialColumns(ODBCStmt *stmt, pos = strlen(query); } - /* debug: fprintf(stdout, "SQLSpecialColumns SQL:\n%s\n\n", query); */ + /* debug: fprintf(stdout, "SQLSpecialColumns query (pos: %zu, len: %zu):\n%s\n\n", pos, strlen(query), query); */ /* query the MonetDB data dictionary tables */ rc = MNDBExecDirect(stmt, (SQLCHAR *) query, (SQLINTEGER) pos); diff --git a/clients/odbc/driver/SQLStatistics.c b/clients/odbc/driver/SQLStatistics.c --- a/clients/odbc/driver/SQLStatistics.c +++ b/clients/odbc/driver/SQLStatistics.c @@ -292,8 +292,9 @@ MNDBStatistics(ODBCStmt *stmt, /* add the ordering */ pos += strcpy_len(query + pos, " order by \"NON_UNIQUE\", \"TYPE\", \"INDEX_QUALIFIER\", \"INDEX_NAME\", \"ORDINAL_POSITION\"", querylen - pos); + assert(pos < querylen); - /* debug: fprintf(stdout, "SQLStatistics SQL:\n%s\n\n", query); */ + /* debug: fprintf(stdout, "SQLStatistics query (pos: %zu, len: %zu):\n%s\n\n", pos, strlen(query), query); */ /* query the MonetDB data dictionary tables */ rc = MNDBExecDirect(stmt, (SQLCHAR *) query, (SQLINTEGER) pos); diff --git a/clients/odbc/driver/SQLTablePrivileges.c b/clients/odbc/driver/SQLTablePrivileges.c --- a/clients/odbc/driver/SQLTablePrivileges.c +++ b/clients/odbc/driver/SQLTablePrivileges.c @@ -95,7 +95,7 @@ MNDBTablePrivileges(ODBCStmt *stmt, } /* construct the query now */ - querylen = 1200 + strlen(stmt->Dbc->dbname) + + querylen = 1000 + strlen(stmt->Dbc->dbname) + (sch ? strlen(sch) : 0) + (tab ? strlen(tab) : 0); query = malloc(querylen); if (query == NULL) @@ -129,15 +129,19 @@ MNDBTablePrivileges(ODBCStmt *stmt, "when 0 then 'NO' " "end as \"IS_GRANTABLE\" " "from sys.schemas s, " - "sys._tables t, " - "sys.auths a, " - "sys.privileges p, " - "sys.auths g, " - "%s " + /* next union all subquery is much more efficient than using sys.tables */ + "(select t1.id, t1.name, t1.schema_id from sys._tables as t1" + " where not t1.system" /* exclude system tables and views */ + " union all" + " select t2.id, t2.name, t2.schema_id from tmp._tables as t2)" + " as t(id, name, schema_id), " + "sys.auths a, " + "sys.privileges p, " + "sys.auths g, " + "%s " "where p.obj_id = t.id and " "p.auth_id = a.id and " "t.schema_id = s.id and " - "not t.system and " "p.grantor = g.id and " "p.privileges = pc.privilege_code_id", stmt->Dbc->dbname, @@ -150,7 +154,7 @@ MNDBTablePrivileges(ODBCStmt *stmt, _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
