Changeset: 18c3171202e0 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/18c3171202e0
Modified Files:
clients/odbc/ChangeLog
clients/odbc/driver/SQLColumnPrivileges.c
clients/odbc/driver/SQLTablePrivileges.c
clients/odbc/samples/metadata.c
Branch: default
Log Message:
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.
Extended ODBC metadata test program with more tests, especially for
SQLTablePrivileges() and SQLColumnPrivileges().
diffs (truncated from 511 to 300 lines):
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 <martin.van.dint...@monetdbsolutions.com>
+- 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 <martin.van.dint...@monetdbsolutions.com>
- 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 <martin.van.dint...@monetdbsolutions.com>
-- 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/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,
"(8, 'DELETE'), "
"(16, 'EXECUTE'), "
"(32, 'GRANT')) as pc(privilege_code_id,
privilege_code_name)");
- assert(pos < 1000);
+ assert(pos < 900);
/* Construct the selection condition query part */
if (NameLength1 > 0 && CatalogName != NULL) {
@@ -173,6 +177,9 @@ MNDBTablePrivileges(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\", \"PRIVILEGE\", \"GRANTEE\"", querylen - pos);
+ assert(pos < querylen);
+
+ /* debug: fprintf(stdout, "SQLTablePrivileges 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/samples/metadata.c b/clients/odbc/samples/metadata.c
--- a/clients/odbc/samples/metadata.c
+++ b/clients/odbc/samples/metadata.c
@@ -6,6 +6,20 @@
* Copyright 1997 - July 2008 CWI, August 2008 - 2022 MonetDB B.V.
*/
+/*
+ * MonetDB program to test ODBC metadata/catalog functions (all return a
result-set):
+ * SQLTables()
+ * SQLColumns()
+ * SQLSpecialColumns()
+ * SQLPrimaryKeys()
+ * SQLForeignKeys()
+ * SQLStatistics()
+ * SQLTablePrivileges()
+ * SQLColumnPrivileges()
+ * SQLProcedures()
+ * SQLProcedureColumns()
+ */
+
#ifdef _MSC_VER
/* Visual Studio 8 has deprecated lots of stuff: suppress warnings */
#ifndef _CRT_SECURE_NO_DEPRECATE
@@ -68,7 +82,7 @@ check(SQLRETURN ret, SQLSMALLINT tpe, SQ
break;
case SQL_INVALID_HANDLE:
fprintf(stderr, "%s: Error: invalid handle\n", func);
- exit(1);
+ break;
default:
fprintf(stderr, "%s: Unexpected return value\n", func);
break;
@@ -79,7 +93,7 @@ static void
compareResult(SQLHANDLE stmt, SQLRETURN retcode, const char * functionname,
const char * expected)
{
SQLRETURN ret;
- SQLSMALLINT columns; // Number of columns in result-set
+ SQLSMALLINT columns; /* Number of columns in result-set */
size_t expct_len = strlen(expected);
size_t outp_len = expct_len + 10000;
char * outp = malloc(outp_len);
@@ -96,12 +110,12 @@ compareResult(SQLHANDLE stmt, SQLRETURN
check(retcode, SQL_HANDLE_STMT, stmt, functionname);
- // How many columns are there
+ /* How many columns are there */
ret = SQLNumResultCols(stmt, &columns);
check(ret, SQL_HANDLE_STMT, stmt, "SQLNumResultCols()");
pos += snprintf(outp + pos, outp_len - pos, "Resultset with %d
columns\n", columns);
- // get Result Column Names and print them
+ /* get Result Column Names and print them */
for (col = 1; col <= columns; col++) {
ret = SQLDescribeCol(stmt, col, (SQLCHAR *) buf, sizeof(buf),
NULL, NULL, NULL, NULL, NULL);
@@ -126,18 +140,18 @@ compareResult(SQLHANDLE stmt, SQLRETURN
ret = SQLFetch(stmt);
check(ret, SQL_HANDLE_STMT, stmt, "SQLFetch(1)");
while (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
- // Loop through the columns
+ /* Loop through the columns */
for (col = 1; col <= columns; col++) {
- // Retrieve column data as a string
+ /* Retrieve column data as a string */
ret = SQLGetData(stmt, col, SQL_C_CHAR, buf,
sizeof(buf), &indicator);
check(ret, SQL_HANDLE_STMT, stmt, "SQLGetData()");
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
- // Check if we need to replace the system id
values to get stable output
+ /* Check if we need to replace the system id
values to get stable output */
if (replaceId == 0 ||
(replaceId == 1 && col < columns)) {
pos += snprintf(outp + pos, outp_len -
pos,
(col > 1) ? "\t%s" : "%s",
- // Handle null columns
+ /* Handle null columns */
(indicator == SQL_NULL_DATA) ?
"NULL" : buf);
} else {
pos += snprintf(outp + pos, outp_len -
pos, "\treplacedId");
@@ -154,7 +168,7 @@ compareResult(SQLHANDLE stmt, SQLRETURN
functionname, expected, outp);
}
- // cleanup
+ /* cleanup */
free(outp);
ret = SQLCloseCursor(stmt);
@@ -179,7 +193,7 @@ main(int argc, char **argv)
if (argc > 3)
pass = argv[3];
if (argc > 4 || *dsn == '-') {
- fprintf(stderr, "Usage: %s [datasource [user [password]]]\n",
argv[0]);
+ fprintf(stderr, "Wrong arguments. Usage: %s [datasource [user
[password]]]\n", argv[0]);
exit(1);
}
@@ -239,6 +253,15 @@ main(int argc, char **argv)
ret = SQLExecDirect(stmt, (SQLCHAR *)
"GRANT SELECT ON TABLE odbctst.pk_uc TO PUBLIC;\n"
"GRANT INSERT, UPDATE, DELETE ON TABLE odbctst.pk_uc TO
monetdb;\n"
+ "GRANT SELECT (id2, name2), UPDATE (name2) ON TABLE
odbctst.nopk_twoucs TO monetdb;\n"
+ "GRANT INSERT, DELETE ON TABLE tmp.tmp_pk_uc TO monetdb;\n"
+ "GRANT SELECT (id1, name1), UPDATE (name1) ON TABLE
tmp.tmp_pk_uc TO monetdb;\n"
+ "GRANT INSERT, DELETE ON TABLE tmp.glbl_pk_uc TO monetdb;\n"
+ "GRANT SELECT (id1, name1), UPDATE (name1) ON TABLE
tmp.glbl_pk_uc TO monetdb;\n"
+ "GRANT INSERT, DELETE ON TABLE tmp.tmp_nopk_twoucs TO
monetdb;\n"
+ "GRANT SELECT (id2, name2), UPDATE (name2) ON TABLE
tmp.tmp_nopk_twoucs TO monetdb;\n"
+ "GRANT DELETE, INSERT ON TABLE tmp.glbl_nopk_twoucs TO
monetdb;\n"
+ "GRANT SELECT (id2, name2), UPDATE (name2) ON TABLE
tmp.glbl_nopk_twoucs TO monetdb;\n"
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
