Feature request: please have the sqlite3_set_authorizer callback given the full list of tables and columns used by a statement

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Feature request: please have the sqlite3_set_authorizer callback given the full list of tables and columns used by a statement

Gwendal Roué-2
Hello,

I'm the author of GRDB.swift [1], a Swift wrapper around SQLite which aims, among other things, at notifying of transactions that may have an impact on a the results of a SELECT statement.

For example, `SELECT a, b FROM table1` is impacted by `DELETE FROM table1`, but not by `UPDATE table1 SET c = 1` or `INSERT INTO table2 (...)`.

To achieve this feature, GRDB uses a sqlite3_set_authorizer callback [2]. For example, the callback is given SQLITE_READ which tells that `SELECT a, b FROM table1` uses the columns a and b from table1. The authorizer callback can also been given SQLITE_INSERT, which tells that `INSERT INTO table2 (...)` performs an insertion in table2.

Those pieces of information can be compared together, so that one can deduce that `INSERT INTO table2 (...)` has no impact on `SELECT a, b FROM table1`, but `DELETE FROM table1` has.

Now, enter `COUNT(*)`. The sqlite3_set_authorizer callback is told nearly nothing about the `SELECT COUNT(*) FROM table1`. Especially not that table1 is used. It is only told that the COUNT function is called throuh SQLITE_FUNCTION. That's all. That is more than nothing, because one can deduce from a call to the COUNT function that *any* statement can have an impact on `SELECT COUNT(*) FROM table1`. For example, `INSERT INTO table2 (...)` will be assumed to have an impact on `SELECT COUNT(*) FROM table1`.

Unfortunately, this is less than ideal. I understand the situation: `SELECT COUNT(*) FROM table1` does not access values from the table1 table, and thus does not need any authorization. But I suggest that sqlite3_set_authorizer is so close from giving a full picture of the columns and table read by a statement that it's a pity that a simple COUNT(*) is able to ruin the picture.

So here is my feature request: please have the sqlite3_set_authorizer callback given the full list of tables and columns used by a statement.

Cheers,
Gwendal Roué
[1] http://github.com/groue/GRDB.swift
[2] https://sqlite.org/c3ref/set_authorizer.html

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...