I believe I've got solutions to the various little problems I've run into. My experiments have helped me shed some light on what the semantics of DB triggers should be, to the point where I think I've reached stable conclusions about those semantics. I'm also ready to characterize performance and memory footprint impacts, and to describe how DB triggers will work. And I'm ready to solicit opinions and advice regarding design and implementation as I want to contribute this code to SQLite3, and I want to make sure to have code that is as clean as possible -- any feedback will be greatly appreciated.
(Incidentally, I'm doing this work to enable other work in an existing open source project.) DB trigger semantics: - BEGIN triggers are AFTER BEGIN triggers, and they fire upon (and before) the first INSERT/UPDATE/DELETE statement in a transaction, on every INSERT/UPDATE/DELETE executed in auto-commit mode, or a BEGIN IMMEDIATE, but they fire once and only once per-transaction. BEGIN triggers may throw errors, in which case the triggering statement will fail with no way to override this (i.e. a triggering INSERT OR IGNORE will not cause a BEGIN trigger exception to be ignored). - COMMIT triggers should (and if I can help it, will) come in two varieties: BEFORE COMMIT and AFTER COMMIT. BEFORE COMMIT triggers can cause a commit to fail by RAISE()ing exceptions, and therefore can run many times, but only once per-commit, except that in auto-commit mode the triggering statement's changes will be rolled back. AFTER COMMIT triggers cannot cause a commit to fail, and they execute in their own transaction. I will only code these if I can easily make it so that the commit does its work without dropping the lock so that the AFTER COMMIT trigger can run immediately. Exceptions thrown by AFTER COMMIT triggers will only result in their transaction being rolled back with no effect on the prior transaction, and the COMMIT statement will not return and error in such cases. - ROLLBACK triggers will only come in AFTER ROLLBACK flavor, and will be very similar to AFTER COMMIT triggers, running after the triggering event completes, and not being allowed to return errors. No change to the CONNECT and DISCONNECT trigger semantics I described earlier, except that I now know how to ensure that DISCONNECT triggers fire no more than once per-connection. I believe the above semantics are retrospectively obvious and reliable, whereas my earlier proposed semantics were unclear and clearly not finished. How to use DB triggers: - CONNECT triggers are useful for: auditing purposes (of limited value, of course, since there's no guarantee that a client will run these triggers), and setup (e.g., load_extension()). (A SQL function roughly corresponding to the internal execExecSQL() function might be useful for automatically setting up TEMP schema.) - AFTER BEGIN triggers could be useful for: logging, auditing, and for demarcating the start of a write transaction. For example, one might ensure that there's a single row in a table named "TX", with a unique (autoincremented) transaction ID to brand all rows of other tables with, then one might use this to implement DB synchronization/replication. - BEFORE COMMIT triggers should be used primarily for deferred constraint checking, particularly of constraints that cannot easily be expressed with existing, natively supported constraints. If anyone is interested I can give some elaborate examples of such constraints based on extensive personal experience with an object-oriented DB back in the late 90s (that DB is now open source too). - AFTER COMMIT triggers could be used for logging, auditing, and to clean up after AFTER BEGIN triggers (though AFTER BEGIN triggers can always clean up after the preceding AFTER BEGIN trigger firings, which makes me think that AFTER COMMIT triggers add little value). - AFTER ROLLBACK triggers have very little utility in my view, mostly logging. - DISCONNECT triggers also have very little utility IMO, mostly logging and auditing. To make this all feasible I'm having to add a few opcodes: to check whether a begin (or connect) trigger has run for the current transaction, to mark the begin trigger as having run, an opcode to set an address to jump to when a rollback exception is thrown and the rollback completed, an opcode to mark the end of a DB trigger firing (for housekeeping, otherwise COMMIT triggers cause the commit to fail due to the OP_AutoCommit instruction thinking there are still active statements writing in the same transaction), and maybe a couple of others. All DB trigger firings, excepting DISCONNECT, will be coded into compiled statements other than SELECT. The reason is that any one prepared statement could be the one to trigger any CONNECT, BEGIN, COMMIT (think auto-commit mode) and/or ROLLBACK triggers -- it all depends on the context in which the statement in question is stepped. PRAGMA statements may get CONNECT trigger firing coded in so that the pragma that enables CONNECT triggers can fire them immediately upon enabling them, or I might re-enter SQLite3 to exec an "INSERT INTO sqlite_intview_connect DEFAULT VALUES" statement. Or I could code CONNECT trigger firing into prepared statements such that the first one executed fires it if CONNECT triggers are enabled. I've not decided this matter yet, and it may be decided for me when I get around to implementing. My current feeling is that re-entrance for this will result in much less code change and with less run-time impact. Performance impact characterization: - If no DB triggers are defined, then the run-time performance of compiled statements will be completely unaffected, whereas compilation of statements will have a minor penalty as the compiler checks whether relevant DB triggers are defined. - If DB triggers are defined there may be some run-time impact of compiled statements: certainly, for those that fire any such triggers, but also there will be at least one more VM branching opcode for those statements that have trigger firing coded into them but which do not fire them. Memory footprint characterization: - I've not measured text change yet, and there's no point doing so until I'm done (since there's quite a bit left for me to do), but source code changes so far are quite minor (which confirms for me that it was a good idea to internally represent DB triggers as INSTEAD OF triggers on crutch views, at least for now). - Two or three run-time structures will at most gain a small (int-sized at most) field: the Vdbe struct and the DB handle struct, and maybe one other. I've not decided whether to create a new SQLITE_OMIT_ define for this feature. The code changes are quite small, considering! So bundling DB triggers into SQLITE_OMIT_TRIGGER seems fine to me. Opinions? Description of trigger firing code generation: - For each type of DB trigger (other than DISCONNECT) for which at least one trigger is defined, the compiler will add a sub-program before or after, as appropriate, the instruction that conceptually should fire the given DB trigger. The sub-program's instructions will be those needed for an INSERT (with DEFAULT VALUES) into one of the crutch views with triggers. The INSERT coding is done by calling sqlite3Insert() on the sub-program. - The sub-program will be executed with default or ignore (depending on the specific type of DB trigger - see above) on-conflict rules (meaning that the triggering statement's OR {ABORT, FAIl, ROLLBACK, IGNORE, REPLACE} on-conflict handling will not apply. This is because DB triggers' exceptions ought not be masked by any statement's on-conflict handling, but by the semantics of the given DB trigger type. - For DISCONNECT triggers I think I'll just have sqlite3_close() call sqlite3_exec() an "INSERT INTO sqlite_intview_disconnect DEFAULT VALUES" statement. I will probably make it so that if sqlite3_close() fails then subsequent sqlite3_step() calls will also fail if there was a DISCONNECT trigger defined and the trigger fired. This way DISCONNECT triggers can be made reliable. Finally, I now also know how to implement statement-level triggers, though I don't see any value in such triggers. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users