> Le 9 mai 2017 à 08:23, Gwendal Roué <gwendal.r...@gmail.com> a écrit : > > 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.
Let me please rewrite the above process, to make it more clear. Goal: notify that the last commit may have changed the results of statement S1, without any false negative, and a number of false positives that is as low as possible. Let's use `SELECT col1 FROM t1` as S1. # LEVEL 1: notify after each commit with sqlite3_commit_hook. Need for improvement: there are many many false positives. Even an empty transaction `BEGIN; COMMIT;` triggers a notification. # LEVEL 2: notify only for statements that write in the columns and tables read by S1 (thanks to the authorizer callbacks) Good: `INSERT INTO t2 ...` and `UPDATE t1 SET col2 = 'foo'` no longer trigger a change notification, because authorizer callbacks prove that they have no impact of S1. Need for improvement 1: if S1 uses the COUNT function, then all statements may have an impact on it, and we're back to level 1 above (read my previous emails about his trouble that SQLITE_READ_TABLE aims at solving) Need for improvement 2: a statement that may modify S1 may not modify the database: for example `DELETE FROM t1 WHERE 0` won't delete a single line, but the change notification will be triggered anyway. # LEVEL 3: notify only for statements that perform database modifications (thanks to the database change callbacks) Good: `DELETE FROM t1 WHERE 0` no longer triggers a change notification, because it did not update, delete, or insert a single row. Need for improvement: a database change callback may be invalidated by a rollbacked savepoint. # LEVEL 4: filter out database changes that are rollbacked by a savepoint (thanks to the authorizer callbacks that allow the sqlite3 client to track the savepoint stack) Good: `INSERT INTO t1 ...` no longer triggers a change notification if it is rollbacked. # LEVEL 5: there is no level 5, I think we've gone as far as SQLite can do, and this is already quite tremendous. Again, the problem is with the COUNT function. By I should stop repeating myself :-) Thanks for reading, Gwendal Roué _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
