Re: [sqlite] Attach a database dynamically if not already available within a trigger
On Mon, 15 Mar 2010 18:52:26 -0400, andy andrew.g.b...@gmail.com wrote: Hello I am hoping someone can help with the following problem. - I want to create a log of all changes to tables in a database. - I would like to keep the log table in a separate database to the main data files - I plan to use triggers to capture changes and insert them into the log table. Question: How can I embed the 'attach command within the trigger statement so that the log database is attached if it is not already available? You can't do that. Even if you make sure the log database is attached at all times, it is not possible to define triggers (or views, for that matter) that span both databases. The reason for this design is that the schema would be invalid when one database file would be missing. Your best bet is to create the log table(s) in the same database, so it would be in the same ACID domain, consistent with the contents of the database, and periodically offload rows from the log table to an attached database, and delete them from the log table in the main database. Pseudo code (untested): ATTACH DATABASE 'log.db' as logdb; BEGIN EXCLUSIVE; INSERT INTO logdb.log (col1,col2,..) SELECT col1,col2,.. FROM log WHERE something; DELETE FROM log WHERE something; COMMIT; DETACH DATABASE logdb; i.e something like create trigger insert_data after insert on data begin if database logdb does not exist attach logdb.db as logdb; insert into logdb.log values(.) end; I am also a little concerned about performance so I am not sure if testing for the presence of the database for every change will add to much overhead. Thoughts? Don't be concerned about performance until you really have a performance problem. A healthy schema usually performs well. Design, Build, Benchmark, Optimize, Benchmark. Thanks -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Attach a database dynamically if not already available within a trigger
Hello I am hoping someone can help with the following problem. - I want to create a log of all changes to tables in a database. - I would like to keep the log table in a separate database to the main data files - I plan to use triggers to capture changes and insert them into the log table. Question: How can I embed the 'attach command within the trigger statement so that the log database is attached if it is not already available? i.e something like create trigger insert_data after insert on data begin if database logdb does not exist attach logdb.db as logdb; insert into logdb.log values(.) end; I am also a little concerned about performance so I am not sure if testing for the presence of the database for every change will add to much overhead. Thoughts? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Attach a database dynamically if not already available within a trigger
How can I embed the 'attach command within the trigger statement so that the log database is attached if it is not already available? AFAIK, this is impossible. But why do you need that? If you write your own application you can always attach this database at the beginning. But if you want to spy after some third-party application I believe SQLite is not suitable for this kind of tasks... Pavel On Sun, Mar 14, 2010 at 10:50 PM, andy andrew.g.b...@gmail.com wrote: Hello I am hoping someone can help with the following problem. - I want to create a log of all changes to tables in a database. - I would like to keep the log table in a separate database to the main data files - I plan to use triggers to capture changes and insert them into the log table. Question: How can I embed the 'attach command within the trigger statement so that the log database is attached if it is not already available? i.e something like create trigger insert_data after insert on data begin if database logdb does not exist attach logdb.db as logdb; insert into logdb.log values(.) end; I am also a little concerned about performance so I am not sure if testing for the presence of the database for every change will add to much overhead. Thoughts? Thanks ___ 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] Attach a database dynamically if not already available within a trigger
Hello I am hoping someone can help with the following problem. - I want to create a log of all changes to tables in a database. - I would like to keep the log table in a separate database to the main data files - I plan to use triggers to capture changes and insert them into the log table. Question: How can I embed the 'attach command within the trigger statement so that the log database is attached if it is not already available? i.e something like create trigger insert_data after insert on data begin if database logdb does not exist attach logdb.db as logdb; insert into logdb.log values(.) end; I am also a little concerned about performance so I am not sure if testing for the presence of the database for every change will add to much overhead. Thoughts? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Attach a database dynamically if not already available within a trigger
On 15 Mar 2010, at 10:52pm, andy wrote: How can I embed the 'attach command within the trigger statement so that the log database is attached if it is not already available? Why not just attach it outside the TRIGGER ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Attach a database dynamically if not already available within a trigger
Thank you. Dont worry readers Im not trying anything 007 Rather, I have variety of separate utilities that insert/update/ delete records across several database and hoped to use triggers to log changes to a single place. By embeding the attach statement as part of the trigger (i.e becomes part of the database specification) so as each utility/program does not remember to attach the log database before it starts updating data i.e the trigger commands are fully self contained less likely to cause issues if it is executed and the log database has not already been attached (i.e the updating program 'forgot'. thanks for the help btw! On Mar 15, 2010, at 10:04 AM, Pavel Ivanov wrote: How can I embed the 'attach command within the trigger statement so that the log database is attached if it is not already available? AFAIK, this is impossible. But why do you need that? If you write your own application you can always attach this database at the beginning. But if you want to spy after some third-party application I believe SQLite is not suitable for this kind of tasks... Pavel On Sun, Mar 14, 2010 at 10:50 PM, andy andrew.g.b...@gmail.com wrote: Hello I am hoping someone can help with the following problem. - I want to create a log of all changes to tables in a database. - I would like to keep the log table in a separate database to the main data files - I plan to use triggers to capture changes and insert them into the log table. Question: How can I embed the 'attach command within the trigger statement so that the log database is attached if it is not already available? i.e something like create trigger insert_data after insert on data begin if database logdb does not exist attach logdb.db as logdb; insert into logdb.log values(.) end; I am also a little concerned about performance so I am not sure if testing for the presence of the database for every change will add to much overhead. Thoughts? Thanks ___ 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
