----- Original Message ----
From: Jay A. Kreibich <j...@kreibi.ch>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Wed, June 23, 2010 11:15:59 AM
Subject: Re: [sqlite] marking transaction boundaries
On Wed, Jun 23, 2010 at 09:19:05AM -0700, b s scratched on the wall:
> I can put the transaction identifier into tables via triggers.
>
> That's the million $ question. I don't know when transaction
> begins or ends! And my original question was pointing to
> a feature DRH had suggested way back in 2003. That would
> have been the perfect way to do it (and probably the only
> reliable way).
>
> It would have been something along these lines:
> CREATE TRIGGER ex1 AFTER BEGIN BEGIN
> INSERT or UPDATE into TT values ('xid', lower(hex(randomblob(16)))
> END;
>
> CREATE TRIGGER ex2 BEFORE COMMIT BEGIN
> DELETE from TT where key='xid'
> END;
>
> CREATE TRIGGER abc_ins AFTER INSERT on abc
> UPDATE abc set xid = (select value from TT where key='xid')
> END;
If something like this existed, the update to the TT table will
never be seen outside of this transaction. You've basically setup
localized per-transaction variable.
[bs] that should be fine. inside that transaction, i can update tables
with this xid.
Why not just do it in the application? Just create a transaction id
in the application anytime you call BEGIN and manually use the id
with any INSERT statement.
Let me guess... you want to do all this, but you can't modify the
application, only the database. I don't think that is possible.
[bs] correct. i have to assume i have no access to the application logic
> for this to work, we need a database intrinsic. please refer to
> this link again:
> http://osdir.com/ml/db.sqlite.general/2003-04/msg00137.html
I think the end result of this work was the sqlite3_*_hook() APIs.
The functionality was exposed at the C API level, rather than the SQL
level. As the message points out, there doesn't seem to be any
other database engine that provides this kind of functionality
at the SQL level.
Still, you could likely use commit hooks to do this. Create a
commit hook that generates a fresh transaction id and stores it in
an application variable. Create an SQL function that returns that ID.
Create a trigger like your third one, where you call the SQL function
to get the value. Every time a transaction commits, it prepares a
fresh ID for the next transaction. You would need per-connection
values, but if your application is only using one connection at a
time, it is pretty simple.
[bs] but the hooks are per connection. if a connection does not load
this hook, its lost. there is no way to get around it, unless i load a
function that's applicable to all connections. since functions cannot
be chained any other hook to the *hook() functions will replace it.
You could even do all that in an external extension. You'd need to
somehow load the extension, but you wouldn't need any other
modifications to the application.
[bs] same as above. functions cannot be chained so, it can be replaced
unless, its one of the core internal functions that do not let it be overridden.
thanks
brs
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
