Re: [sqlite] Get transaction number
On Tue, 26 Aug 2008 20:11:05 +0400, you wrote: >Hello! > >? ? ?? Tuesday 26 August 2008 18:59:55 Igor Tandetnik ???(?): >> > I don't know method to set triggers on begin and commit transaction >> > events. >> >> You could log begin/end transaction (along with any other statements) by >> installing sqlite3_trace hook. >> >> Another approach would be to use sqlite3_commit_hook, >> sqlite3_rollback_hook, sqlite3_get_autocommit. Call >> sqlite3_get_autocommit from inside your custom function to see whether >> the statement is part of an explicit transaction. sqlite3_commit_hook >> and sqlite3_rollback_hook tell you when a transaction ends. The first >> query that's part of an explicit transaction after a previous >> transaction (if any) has ended, starts a new one. >> >> By the way, see sqlite3_update_hook. Perhaps you could use that in lieu >> of your triggers. > >Thanks, it's better way for my task. But is it safe to logging uncommited >changes? Logging uncommitted changes is fine as long as - you also log commits - only replay / evaluate complete transactions, complete being defined as "having a commit on the logfile" Logging rollbacks strictly isn't necessary, because rolled back transactions have to be identified by the missing commit logrecord, not by the presence of a rollback logrecord. Reason: In case of crashes you won't be able to log rollbacks, anyway, whereas SQLite will rollback using the journal the first time a new connection is made. >Best regards, Alexey. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get transaction number
Hello! В сообщении от Tuesday 26 August 2008 21:24:24 Igor Tandetnik написал(а): > > Thanks, it's better way for my task. But is it safe to logging > > uncommited changes? > > You tell me. Aren't your triggers doing that already? Well, if I'm writing log to other database in single transaction it's correct. But for disk-based log It's may be unsafe, I think. Thanks for answer, I'll use hooks. Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get transaction number
Alexey Pechnikov <[EMAIL PROTECTED]> wrote: >> By the way, see sqlite3_update_hook. Perhaps you could use that in >> lieu of your triggers. > > Thanks, it's better way for my task. But is it safe to logging > uncommited changes? You tell me. Aren't your triggers doing that already? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get transaction number
Hello! В сообщении от Tuesday 26 August 2008 18:59:55 Igor Tandetnik написал(а): > > I don't know method to set triggers on begin and commit transaction > > events. > > You could log begin/end transaction (along with any other statements) by > installing sqlite3_trace hook. > > Another approach would be to use sqlite3_commit_hook, > sqlite3_rollback_hook, sqlite3_get_autocommit. Call > sqlite3_get_autocommit from inside your custom function to see whether > the statement is part of an explicit transaction. sqlite3_commit_hook > and sqlite3_rollback_hook tell you when a transaction ends. The first > query that's part of an explicit transaction after a previous > transaction (if any) has ended, starts a new one. > > By the way, see sqlite3_update_hook. Perhaps you could use that in lieu > of your triggers. Thanks, it's better way for my task. But is it safe to logging uncommited changes? Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get transaction number
Alexey Pechnikov <[EMAIL PROTECTED]> wrote: > ÷ ÓÏÏÂÝÅÎÉÉ ÏÔ Tuesday 26 August 2008 16:44:56 Igor Tandetnik > ÎÁÐÉÓÁÌ(Á): >>> Can I group logged queries by any transaction id? >> >> Well, BEGIN TRANSACTION and END TRANSACTION are themselves queries, >> and thus will presumably appear in your log. Any queries in between >> would then belong to that transaction. > > I don't know method to set triggers on begin and commit transaction > events. You could log begin/end transaction (along with any other statements) by installing sqlite3_trace hook. Another approach would be to use sqlite3_commit_hook, sqlite3_rollback_hook, sqlite3_get_autocommit. Call sqlite3_get_autocommit from inside your custom function to see whether the statement is part of an explicit transaction. sqlite3_commit_hook and sqlite3_rollback_hook tell you when a transaction ends. The first query that's part of an explicit transaction after a previous transaction (if any) has ended, starts a new one. By the way, see sqlite3_update_hook. Perhaps you could use that in lieu of your triggers. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get transaction number
Hello!
В сообщении от Tuesday 26 August 2008 16:44:56 Igor Tandetnik написал(а):
> > Can I group logged queries by any transaction id?
>
> Well, BEGIN TRANSACTION and END TRANSACTION are themselves queries, and
> thus will presumably appear in your log. Any queries in between would
> then belong to that transaction.
I don't know method to set triggers on begin and commit transaction events.
For example:
CREATE TABLE groups
(
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
save_date REAL,
update_date REAL,
delete_date REAL NOT NULL DEFAULT '',
name TEXT COLLATE russian UNIQUE NOT NULL DEFAULT ''
);
create trigger groups_insert after insert on groups begin
SELECT notify_insert('groups', NEW.rowid, NEW.id, NEW.save_date,
NEW.update_date, NEW.delete_date, NEW.name);
update groups set save_date = coalesce(save_date,
julianday('now')),update_date = julianday('now') where rowid=NEW.rowid;
end;
create trigger groups_update_after after update on groups begin
update groups set update_date = julianday('now') where rowid=OLD.rowid;
SELECT notify_update('groups', NEW.rowid, NEW.id, NEW.save_date,
NEW.update_date, NEW.delete_date, NEW.name);
end;
In my log I get lines as:
DATABASE NOTIFY UPDATE table=groups rowid=1003646
1003646 2454528.5 2454704.97969 {group_name}
...
(and I'm saving log records to another SQLite database).
Best regards, Alexey.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get transaction number
"Alexey Pechnikov" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Â ñîîáùåíèè îò Tuesday 26 August 2008 15:58:56 Jeffrey Becker > íàïèñàë(à): >> I dont believe sqlite supports named transactions. As such there >> wouldnt be any identifier to get. > > Can I group logged queries by any transaction id? Well, BEGIN TRANSACTION and END TRANSACTION are themselves queries, and thus will presumably appear in your log. Any queries in between would then belong to that transaction. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get transaction number
Hello! В сообщении от Tuesday 26 August 2008 15:58:56 Jeffrey Becker написал(а): > I dont believe sqlite supports named transactions. As such there > wouldnt be any identifier to get. Can I group logged queries by any transaction id? Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get transaction number
I dont believe sqlite supports named transactions. As such there wouldnt be any identifier to get. On Tue, Aug 26, 2008 at 4:47 AM, Alexey Pechnikov <[EMAIL PROTECTED]> wrote: > Hello! > > How can I get transaction identifier or number (my programm lang is tcl)? > > Best regards, Alexey. > ___ > 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
