Re: [sqlite] logging statements executed by the db
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 P Kishor wrote: > I have a rather opaque application that is calling my db. Is there > something I can turn on in SQLite that will log all the statements > executed against it? If you are using Linux then you can use the ltrace program to trace calls and their parameters. You would want to track execution of sqlite3_prepare. http://linux.die.net/man/1/ltrace Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFICc9vmOOfHg372QQRAiX6AKCxaMN8kzLVGmSYagQIcdrjeV544gCgtUhR M51O8+VbS4RR43Ussb8a1UM= =jiLg -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] logging statements executed by the db
> You can't create a trigger on a select. Only insert, delete, and > update statements can fire a trigger. True. Though now that you mention it, it would obviously be useful to be able to trigger from a select. >> CREATE TRIGGER log_foo >> INSTEAD OF SELECT ON view_bar >> INSERT INTO log_foo (); >> > Even if you could do this, you would have to replace the table the > application is reading with a view so you could use the trigger. > Then you would be susceptible to breaking the application if it > write back to the table. Well, we'd really use a "before" or "after" here, rather than "instead of", so it could work on a table. Tom BareFeet http://www.tandb.com.au/sqlite/compare/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] logging statements executed by the db
P Kishor wrote: > > Ok. Let's translate the above in English (for my sake). > > Keep in mind the following as you answer the above -- I have SQLite > compiled already; I can recompile it. I have an application that I > can't change that is calling SQLite through its own interface. I want > to log whatever data requests are being made from my SQLite db. My > hunch is that recompiling SQLite won't help because this application > has its own SQLite driver. > OK, I think I see your problem now. I was confused when you said the application was calling your db. What you meant was it is accessing your db file. If the application is using its own internal statically linked sqlite library, you are probably out of luck. If it is using an external shared library you could replace it with a customized version that logged the SQL by registering a trace callback on initialization. > Perhaps I can do the following -- create some kind of TRIGGER in my db > that automatically fires on every SELECT and logs every SELECT > statement in a table. Would that work? What would that TRIGGER look > like? Something like so -- > You can't create a trigger on a select. Only insert, delete, and update statements can fire a trigger. > CREATE TABLE foo (a, b); > CREATE VIEW view_bar AS > SELECT a, b FROM foo; > CREATE TRIGGER log_foo > INSTEAD OF SELECT ON view_bar > INSERT INTO log_foo (); > > CREATE TABLE log_foo (timestamp, action); > > Would something like work, and if it will, what would it really look like? > Even if you could do this, you would have to replace the table the application is reading with a view so you could use the trigger. Then you would be susceptible to breaking the application if it write back to the table. > I believe a while back, IIRC, someone (perhaps Richard himself) had > posted a way to log every action on a db in a table for posterity. I > can't find that right now, but that could work for me. > The undo/redo code at http://www.sqlite.org/cvstrac/wiki?p=UndoRedo tracks every change to a database, but not read accesses. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] logging statements executed by the db
On 4/18/08, Dennis Cote <[EMAIL PROTECTED]> wrote: > P Kishor wrote: > > > I have a rather opaque application that is calling my db. Is there > > something I can turn on in SQLite that will log all the statements > > executed against it? > > > > > > You can use sqlite3_trace() to register a callback that is passed each SQL > statement as it is executed. Your callback can log this to a file. See > http://www.sqlite.org/c3ref/profile.html for more info. > > One drawback of this technique is that you don't get to see the values of > any bound parameters. > Ok. Let's translate the above in English (for my sake). Keep in mind the following as you answer the above -- I have SQLite compiled already; I can recompile it. I have an application that I can't change that is calling SQLite through its own interface. I want to log whatever data requests are being made from my SQLite db. My hunch is that recompiling SQLite won't help because this application has its own SQLite driver. Perhaps I can do the following -- create some kind of TRIGGER in my db that automatically fires on every SELECT and logs every SELECT statement in a table. Would that work? What would that TRIGGER look like? Something like so -- CREATE TABLE foo (a, b); CREATE VIEW view_bar AS SELECT a, b FROM foo; CREATE TRIGGER log_foo INSTEAD OF SELECT ON view_bar INSERT INTO log_foo (); CREATE TABLE log_foo (timestamp, action); Would something like work, and if it will, what would it really look like? I believe a while back, IIRC, someone (perhaps Richard himself) had posted a way to log every action on a db in a table for posterity. I can't find that right now, but that could work for me. > Dennis Cote > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] logging statements executed by the db
P Kishor wrote: > I have a rather opaque application that is calling my db. Is there > something I can turn on in SQLite that will log all the statements > executed against it? > You can use sqlite3_trace() to register a callback that is passed each SQL statement as it is executed. Your callback can log this to a file. See http://www.sqlite.org/c3ref/profile.html for more info. One drawback of this technique is that you don't get to see the values of any bound parameters. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] logging statements executed by the db
I have a rather opaque application that is calling my db. Is there something I can turn on in SQLite that will log all the statements executed against it? -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
