Re: [sqlite] marking transaction boundaries
i don't quite get how i can wrap around triggers. My stated case is i have to take that i have no access to application logic. however, as you state below, having some kind of global access to a variable, via a function call will be very useful. net net, i see the need for simple facility like being able to say: select xid(); which could be some number or string maintained by the engine. since sqlite locks out any other write access during a transaction, this value is guaranteed to be atomic. i believe it can be implemented via a pragma, which provides a decent way in without breaking any other existing semantics. thanks brs - Original Message From: Nicolas Williams <nicolas.willi...@oracle.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Wed, June 23, 2010 9:39:17 AM Subject: Re: [sqlite] marking transaction boundaries On Tue, Jun 22, 2010 at 08:35:14AM -0700, b s wrote: > hi, > long ago, drh had proposed a trigger like mechanism that > can be invoked at the begin/end of a transaction. > http://osdir.com/ml/db.sqlite.general/2003-04/msg00137.html > > the general consensus was there is no use other than up'ng > a counter. however, i have a requirement where i would like > to mark transaction boundaries from a user level. That's not how I read that thread. Around-transaction triggers are incredibly useful (they're even more useful with a procedural language or at least some way to apply logic to all rows modified/inserted/ deleted by the transaction). Since there aren't any, your only choice is to manually wrap the transaction statements -- at least the COMMIT statement, while the BEGIN could be handled with a temp table and a trigger to do an INSERT OR IGNORE into it which you DELETE on COMMIT. CREATE TEMP TABLE current_transaction (rowid INTEGER PRIMARY KEY, tran_id TEXT); -- The trigger has to be a TEMP trigger too... CREATE TEMP TRIGGER ... INSERT OR IGNORE INTO current_transaction VALUES (1, randomblob(16)); UPDATE ... SET NEW.tran_id = (SELECT tran_id FROM current_transaction); END; ... and add "DELETE FROM current_transaction;" before every COMMIT. (You also need to do this DELETE on rollbacks. If you re-open your DB connection for every transaction (well, OK, that's heavy-duty), then you don't even need to wrap the COMMIT, but you do need to create the temp table before you BEGIN. So that's your choice: wrap the BEGIN or the COMMIT/ROLLBACK, or both. Back to the 2003 thread... Triggers on ROLLBACK, BEFORE BEGIN or AFTER COMMIT obviously can't modify the DB, so they are only useful for invoking user-defined functions, really, but that _is_ a useful feature! Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] marking transaction boundaries
- 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
Re: [sqlite] marking transaction boundaries
I get the point that transactions can be nested and i am assuming the begin transaction trigger only happens at the outer most transaction. It still is the logical envelope which will be rolled back on a default abort. (yes, i know one can back off to a save point). Store a value... I don't know when a transaction begins or ends, and I don't have a model of my own transaction. If i did, this conversation is moot. thanks brs - Original Message From: Simon Slavin <slav...@bigfraud.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Wed, June 23, 2010 9:30:25 AM Subject: Re: [sqlite] marking transaction boundaries On 23 Jun 2010, at 5:19pm, b s wrote: > I don't know when transaction > begins or ends! You are still ignoring the fact that transactions can be nested. One specific INSERT command might be a member of three different transactions. Your model allows only for one transaction per command. If you have your own model of transactions, just do it all with triggers: store a value somewhere for which transaction you're on, and have the triggers copy this value and put it into your log. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] marking transaction boundaries
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;
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
- Original Message
From: Pavel Ivanov
To: General Discussion of SQLite Database
Sent: Wed, June 23, 2010 2:40:53 AM
Subject: Re: [sqlite] marking transaction boundaries
> The question following your suggestion is how do i maintain transaction
> start and stop times? I have no control of knowing when a transaction
> starts and ends and i said earlier, i cannot use transaction hooks.
And you didn't answer my question: how will you put this transaction
identifier into tables? How will you know which transaction each
INSERT or UPDATE belongs to?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] marking transaction boundaries
Hi, Blob was just an example. My original email said hex(randomblob(16)) as a means of generating an uuid. It is not the important part. The question following your suggestion is how do i maintain transaction start and stop times? I have no control of knowing when a transaction starts and ends and i said earlier, i cannot use transaction hooks. thanks brs - Original Message From: Simon Slavin <slav...@bigfraud.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Tue, June 22, 2010 3:45:26 PM Subject: Re: [sqlite] marking transaction boundaries On 22 Jun 2010, at 10:25pm, b s wrote: > I want to mark that the following records inserted or updated > belong to a certain transaction. Transactions can be nested. In other words, an INSERT or UPDATE can belong to more than one transaction. BLOBs are hugely expensive in terms of programming, processing time and storage. It would be far less 'expensive' to mark your transactions with INTEGERs. If you have your own description of what a transaction is, just use a trigger to maintain a 'last updated' field for every row, and make a table which lists your transactions and their start and stop times. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] marking transaction boundaries
Hi, I want to mark that the following records inserted or updated belong to a certain transaction. Unless i know the explicit begin/end, i cannot use just sticking a blob in a column, because it will change for every call to blob. what i want is during begin transaction store this value in a key/value table as 'tranid'/ and delete it at end of transaction. this can be queried either by application or trigger. but i expect this insertion to happen in a trigger like the original proposal. brs - Original Message From: Pavel Ivanov <paiva...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Tue, June 22, 2010 12:34:16 PM Subject: Re: [sqlite] marking transaction boundaries Do you want to put that blob into all tables using a trigger or changing all applications using database? If changing applications then you can also change them to create that blob value. If using a trigger then you can insert into that trigger creation of the blob value if it doesn't exist. Is there something wrong with such solutions? Pavel On Tue, Jun 22, 2010 at 11:35 AM, b s <gaiet...@yahoo.com> wrote: > hi, > long ago, drh had proposed a trigger like mechanism that > can be invoked at the begin/end of a transaction. > http://osdir.com/ml/db.sqlite.general/2003-04/msg00137.html > > the general consensus was there is no use other than up'ng > a counter. however, i have a requirement where i would like > to mark transaction boundaries from a user level. > > my requirement: on begin transaction, i would like to store > a hex(randomblob(16)) in a table during begin transaction and > put this value in all tables that were involved during that > transaction. > > there is no way it can be done across all connections, unless > a trigger level like facility is available. otherwise the hook functions > all work only on a sqlite connection and i dont want to load modules. > > are there any other methods available to get the same result? > or, as someone mentioned bring some Interbase like facility? > > > thanks > brs > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] marking transaction boundaries
hi, long ago, drh had proposed a trigger like mechanism that can be invoked at the begin/end of a transaction. http://osdir.com/ml/db.sqlite.general/2003-04/msg00137.html the general consensus was there is no use other than up'ng a counter. however, i have a requirement where i would like to mark transaction boundaries from a user level. my requirement: on begin transaction, i would like to store a hex(randomblob(16)) in a table during begin transaction and put this value in all tables that were involved during that transaction. there is no way it can be done across all connections, unless a trigger level like facility is available. otherwise the hook functions all work only on a sqlite connection and i dont want to load modules. are there any other methods available to get the same result? or, as someone mentioned bring some Interbase like facility? thanks brs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite 2.8, HP-UX, Linux NFS server
Hi,
I am trying the following program and it fails with 'database is locked'. I
have set the 'insecure_locks' option in /etc/exports of the NFS server (RH 7.3,
2.4.18-3 kernel).
It only fails for HP-UX clients (11iv1 aka 11.11).
Anybody has success with this combo?
thanks
bal
#include
#include
char *schema = "create table foo (bar varchar(20));";
int main()
{
sqlite *db;
char *errmsg=NULL;
char *dbname = "testdb";
if (NULL == (db = sqlite_open(dbname, 0, ))) {
fprintf(stderr, "Error opening %s err: %s\n", dbname, errmsg);
return 1;
}
if (SQLITE_OK != sqlite_exec(db, schema, NULL, NULL, )) {
fprintf(stderr, "Error creating %s err: %s\n", dbname, errmsg);
sqlite_close(db);
//unlink(dbname);
return 1;
}
sqlite_close(db);
return 0;
}
__
Yahoo! DSL Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com
