You will, of course, be slowing down your app to do this but if all you're
wanting to do is log SQL statements then sqlite3_profile sounds like the way to
go (although it is subject to change).
All you would do is open your own database connection inside your callback and
insert the SQL string passed in.
You could add or refer to the process id or name if you want to filter things.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Frans Knibbe [frans.kni...@geodan.nl]
Sent: Friday, September 02, 2011 10:32 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Track DML (from certain clients only)
Hello Michael,
I am sorry for being unclear, I will try to explain:
What would work is each application writing its own DML to a trace
table. For example, Application A might do this (in pseudocode):
SqlStatement = "insert into tab values (1, 'hello')";
SqlLite.Execute(SqlStatement);
LogStatement = "insert into dml_tab values ('" || SqlStatement || "')";
SqlLite.Execute(LogStatement);
Note that in this case an identification of the application would not
have to be written to the DML log table, because I only need to
distinguish between the P2P module and all other applications as sources
of DML. If the P2P module does not write to the DML log table, all
entries will be made by the other applications.
But I think it would be nicer if each application would not have to
implement tracing like this, if it could just issue a statement and the
database would capture the statement and put it in a table. So in
Application A the only code would be:
SqlStatement = "insert into tab values (1, 'hello')";
SqlLite.Execute(SqlStatement);
Couldn't a callback function registered with sqlite3_trace put
statements in a table? With some kind of identifier of the source of
the statement?
I was just thinking about this some more.. Perhaps it is possible to log
statements but not their source. In that case, could applications insert
comments for identification? Application A could issue the following
statement
insert into tab values (1, 'hello') /* $$$ application A $$$*/;
If SQLite could log this statement including the comment, it would still
be possible to distinguish sources. Or reject the logging of statement
with a trigger similar to the one you proposed..
Greetings,
Frans
On 2011-09-02 16:51, Black, Michael (IS) wrote:
> Maybe I don't understand your setup completely...but can't you have your
> applications send a string identifying who they are? Like the 1st argument
> to whatever function you're setting up?
>
>
>
> I don't think sqlite3_trace is going to let you stop or modify the SQL insert
> or such....all you could do is watch the SQL fly by....
>
>
>
> Maybe if you could explain your API a bit better that would help. You say
> you don't want application to have to insert rows but you said you want only
> rows from certain processes to insert....so I'm confused....if your
> applications don't insert rows what exactly is the problem?
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> ________________________________
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Frans Knibbe [frans.kni...@geodan.nl]
> Sent: Friday, September 02, 2011 9:16 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Track DML (from certain clients only)
>
> Hello Michael,
>
> Thanks for the quick response. Yes, I could add a field to put the
> source string in. But I would not like to bother applications with
> having to insert rows in the DML table. I would prefer that to happen
> automatically because I want to prevent entanglement of separate modules.
>
> I have just read about the sqlite3_trace function. That might be what I
> am looking for, although it might be a tough job getting it to do what I
> want (with me not knowing C). For now I am not concerned yet with making
> it actually happen. My project is a pilot study. But I would like to
> make sure that it is really possible, i.e. that the building blocks are
> there. So I wonder if a hypothetical callback function that is
> registered with sqlite3_trace would have access to some kind of
> identifier of the program or process that has executed each SQL statement?
>
> Regards,
> Frans
>
> On 2011-09-02 12:58, Black, Michael (IS) wrote:
>> If you can add a field that you can put a source string in you can do this:
>>
>> create table dml (dmlstuff text, source text);
>> create trigger before insert on dml
>> for each row when new.source not like 'p2p'
>> begin
>> select raise(rollback,'Not p2p source');
>> end;
>>
>> sqlite> insert into dml values('dml1','p2p');
>> sqlite> select * from dml;
>> dml1|p2p
>> sqlite> insert into dml values('dml2','other');
>> Error: Not p2p source
>> sqlite> select * from dml;
>> dml1|p2p
>>
>>
>>
>> Michael D. Black
>> Senior Scientist
>> NG Information Systems
>> Advanced Analytics Directorate
>>
>>
>>
>>
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
>> behalf of Frans Knibbe [frans.kni...@geodan.nl]
>> Sent: Friday, September 02, 2011 4:21 AM
>> To: sqlite-users@sqlite.org
>> Subject: EXT :[sqlite] Track DML (from certain clients only)
>>
>>
>> Hello all,
>>
>> I am trying to use SQLite as a data store for a P2P (peer to peer)
>> system. There could be several applications reading from and writing to
>> the database. One of these is the P2P module. It reads local additions
>> to the database (only INSERTs are allowed, rows are never DELETEd or
>> UPDATEd) and distributes to to other peers. The P2P module also insert
>> data it receives from other peers.
>>
>> I would like to be able to distinguish data that has been written by the
>> P2P module from data that has been written by other local applications.
>> The latter data need to be handled by the P2P module, the former not
>> (otherwise the data would de replicated over and over again).
>>
>> I thought I could make use of a table that stores all DML statements
>> from all applications except the P2P module. The P2P module could use
>> that table as a task list, and periodically check if there is any
>> unhandled DML in that table. All other applications could be made to
>> write their DML to that table, but that is not very elegant. I would
>> rather like this to be done automatically.
>>
>> Is it possible to create a process (a trigger maybe) that monitors all
>> DML statements and writes them to a table, except when the statement is
>> issued by a specific application?
>>
>> Or are there smarter ways of doing what I am trying to do?
>>
>> Thanks in advance!
>>
>> Frans
>> _______________________________________________
>> 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-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
