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
