> Le 9 mai 2017 à 00:21, Simon Slavin <slav...@bigfraud.org> a écrit : > > Hardly anyone uses the authentication system, so far fewer people know the > answers.
As a reminder, I intend to use the authorisation system in order to tell if a statement has an opportunity to impact on another statement, as a support for a general database observation feature. Here is the general process: 1. During the compilation of a statement S1, authorizer callbacks tell which tables and columns are read by the statement. 2. During the compilation of any other statement S2, authorizer callbacks tell which tables and columns are written by the statement. 3. If the two sets of tables and columns do not intersect, then S2 can not change the results of S1: exit. 4. Until callbacks registered by sqlite3_commit_hook or sqlite3_rollback_hook [2] are invoked, authoriser callbacks allows the app to follow the savepoint stack. This is important for the next step: 5. During the execution of S2, sqlite3_update_hook [1] tell if S2 actually performs any change. Those changes are remembered until the transaction commits [2], or the eventual current savepoint is rollbacked (see step 4 above). 6. After the transaction has been committed, if S2 has performed changes, then S1 is reputed "dirty", and the application is notified that S1 results may have changed. The process above is able to provide false positives: for example `UPDATE TABLE t1 SET a = a` will trigger a notification, even though no change did occur. What is important is that the process above doesn't miss any potential change. Because of the current authorizer callbacks for queries like `SELECT COUNT(*) FROM t1`, which do not tell anything about t1, the step 3 above has to assume that *any* statement has the opportunity to modify the results of this select. This yields too many false positives. My suggestion is there to allow a less paranoid observation of statements that use the COUNT function. It is important to stress that I perfectly know that all those authorizer, update, commit, rollback hooks *can not* help observing a database as soon as there are several writer connections. But they *can* help as soon as there is a single writer connection. That's exactly why I'm here: the above algorithm is already used by GRDB.swift [3], a database library focused on application development that puts all bets on SQLite. GRDB has a serious and robust concurrency model [4] which supports a single connection to a regular database, or a pool of several connections on a WAL database. Both use a single writer connection. Both would be improved with my suggestion. Of course, the core team may prefer not implementing my suggested SQLITE_READ_TABLE. But I wish they would react to the above scenario. Gwendal Roué [1] https://www.sqlite.org/c3ref/update_hook.html <https://www.sqlite.org/c3ref/update_hook.html> [2] https://sqlite.org/c3ref/commit_hook.html <https://sqlite.org/c3ref/commit_hook.html> [3] http://github.com/groue/GRDB.swift <http://github.com/groue/GRDB.swift> [4] https://github.com/groue/GRDB.swift/blob/master/README.md#concurrency <https://github.com/groue/GRDB.swift/blob/master/README.md#concurrency> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
