Re: [sqlite] Get transaction number

2008-08-26 Thread Kees Nuyt
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

2008-08-26 Thread Alexey Pechnikov
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

2008-08-26 Thread Igor Tandetnik
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

2008-08-26 Thread Alexey Pechnikov
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

2008-08-26 Thread Igor Tandetnik
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

2008-08-26 Thread Alexey Pechnikov
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

2008-08-26 Thread Igor Tandetnik
"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

2008-08-26 Thread Alexey Pechnikov
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

2008-08-26 Thread Jeffrey Becker
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


[sqlite] Get transaction number

2008-08-26 Thread Alexey Pechnikov
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