Re: [sqlite] Database Diff libs or applications
If your application is creating/deleting tables, then it (the program) can insert its own 'I am creating/deleting a table' record into the replay table, at the same time it creates the triggers. *** Doug -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman (Firaxis) Sent: Thursday, October 01, 2009 8:10 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database Diff libs or applications I've been looking into using triggers to create a replay table. This seems to be pretty restrictive in that it requires a trigger to be created for each table and cannot track when tables are dropped or created (documentation says that triggers will not be applied to tables like sqlite_master) Is there a way around this? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman (Firaxis) Sent: Thursday, October 01, 2009 10:13 AM To: punk...@eidesis.org; General Discussion of SQLite Database Subject: Re: [sqlite] Database Diff libs or applications Are there any limitations to this? Will this track any and all SQL actions done to the database or just table data changes? I'll hunt down the post now :) -Shaun -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Thursday, October 01, 2009 10:03 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database Diff libs or applications On Thu, Oct 1, 2009 at 8:44 AM, Shaun Seckman (Firaxis) wrote: > Hello, > > I'm looking to externally track the actions made to a > database so that I can apply those same actions to another database > (assuming the other database has a similar schema). I've searched the > documentation and there doesn't seem to be an easy way to extract this > data so my only option seems to be utilizing some sort of library or > application to diff the two databases and generate a SQL script based on > the changes. > > Store the "actions made to a database," in other words, the SQL, in a log and then replay that log against the other database. You can use TRIGGERs to store the SQL in a replay table. Search the archives. Dennis Cote has contributed some code for that in one of the threads. > > Has anyone done this before? Does such a library or application exist? > I'm sure I could write my own if needed. > > > > -Shaun > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, WI, United States ___ 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 __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Diff libs or applications
On 1 Oct 2009, at 4:10pm, Shaun Seckman (Firaxis) wrote: > I've been looking into using triggers to create a replay table. > This seems to be pretty restrictive in that it requires a trigger to > be created for each table and cannot track when tables are dropped > or created (documentation says that triggers will not be applied to > tables like sqlite_master) > > Is there a way around this? Not really. It might be best to do it in your software: whenever you execute a command on copy A of the database you log (or 'journal') the command in another table. When you synchronise you play back this journal against copy B of the database. It'll work fine as long as copy B of the database is not modified by users. But if you ever execute independent commands on copy B of the database then you will have a nasty synchronisation problem which cannot be solved in this way. > Are there any limitations to this? Will this track any and all SQL > actions done to the database or just table data changes? I'll hunt > down the post now :) It can handle things that change your schema (which columns and indexes are in each table, even the creation of a new table) and other things too. If you do the logging in your own code this will work fine. If you use TRIGGERs then you will not see anything except INSERT/DELETE/UPDATE. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Diff libs or applications
> Is there a way around this? No, there's no way around this. If you want to freely create and drop tables and then propagate these changes to other database then I think the easiest way to do it for you is dump the whole database and then create second database from scratch. Or you can write some analyzer of dump's output that will find what was changed when compared to the previous dump. Pavel On Thu, Oct 1, 2009 at 11:10 AM, Shaun Seckman (Firaxis) wrote: > I've been looking into using triggers to create a replay table. This seems > to be pretty restrictive in that it requires a trigger to be created for each > table and cannot track when tables are dropped or created (documentation says > that triggers will not be applied to tables like sqlite_master) > > Is there a way around this? > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman (Firaxis) > Sent: Thursday, October 01, 2009 10:13 AM > To: punk...@eidesis.org; General Discussion of SQLite Database > Subject: Re: [sqlite] Database Diff libs or applications > > Are there any limitations to this? Will this track any and all SQL actions > done to the database or just table data changes? I'll hunt down the post now > :) > > -Shaun > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor > Sent: Thursday, October 01, 2009 10:03 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Database Diff libs or applications > > On Thu, Oct 1, 2009 at 8:44 AM, Shaun Seckman (Firaxis) > wrote: >> Hello, >> >> I'm looking to externally track the actions made to a >> database so that I can apply those same actions to another database >> (assuming the other database has a similar schema). I've searched the >> documentation and there doesn't seem to be an easy way to extract this >> data so my only option seems to be utilizing some sort of library or >> application to diff the two databases and generate a SQL script based on >> the changes. >> >> > > Store the "actions made to a database," in other words, the SQL, in a > log and then replay that log against the other database. > > You can use TRIGGERs to store the SQL in a replay table. Search the > archives. Dennis Cote has contributed some code for that in one of the > threads. > > >> >> Has anyone done this before? Does such a library or application exist? >> I'm sure I could write my own if needed. >> >> >> >> -Shaun >> >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Puneet Kishor http://www.punkish.org > Carbon Model http://carbonmodel.org > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor > Nelson Institute, UW-Madison http://www.nelson.wisc.edu > --- > Assertions are politics; backing up assertions with evidence is science > === > Sent from Madison, WI, United States > ___ > 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
Re: [sqlite] Database Diff libs or applications
I've been looking into using triggers to create a replay table. This seems to be pretty restrictive in that it requires a trigger to be created for each table and cannot track when tables are dropped or created (documentation says that triggers will not be applied to tables like sqlite_master) Is there a way around this? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman (Firaxis) Sent: Thursday, October 01, 2009 10:13 AM To: punk...@eidesis.org; General Discussion of SQLite Database Subject: Re: [sqlite] Database Diff libs or applications Are there any limitations to this? Will this track any and all SQL actions done to the database or just table data changes? I'll hunt down the post now :) -Shaun -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Thursday, October 01, 2009 10:03 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database Diff libs or applications On Thu, Oct 1, 2009 at 8:44 AM, Shaun Seckman (Firaxis) wrote: > Hello, > > I'm looking to externally track the actions made to a > database so that I can apply those same actions to another database > (assuming the other database has a similar schema). I've searched the > documentation and there doesn't seem to be an easy way to extract this > data so my only option seems to be utilizing some sort of library or > application to diff the two databases and generate a SQL script based on > the changes. > > Store the "actions made to a database," in other words, the SQL, in a log and then replay that log against the other database. You can use TRIGGERs to store the SQL in a replay table. Search the archives. Dennis Cote has contributed some code for that in one of the threads. > > Has anyone done this before? Does such a library or application exist? > I'm sure I could write my own if needed. > > > > -Shaun > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, WI, United States ___ 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
Re: [sqlite] Database Diff libs or applications
Are there any limitations to this? Will this track any and all SQL actions done to the database or just table data changes? I'll hunt down the post now :) -Shaun -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Thursday, October 01, 2009 10:03 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database Diff libs or applications On Thu, Oct 1, 2009 at 8:44 AM, Shaun Seckman (Firaxis) wrote: > Hello, > > I'm looking to externally track the actions made to a > database so that I can apply those same actions to another database > (assuming the other database has a similar schema). I've searched the > documentation and there doesn't seem to be an easy way to extract this > data so my only option seems to be utilizing some sort of library or > application to diff the two databases and generate a SQL script based on > the changes. > > Store the "actions made to a database," in other words, the SQL, in a log and then replay that log against the other database. You can use TRIGGERs to store the SQL in a replay table. Search the archives. Dennis Cote has contributed some code for that in one of the threads. > > Has anyone done this before? Does such a library or application exist? > I'm sure I could write my own if needed. > > > > -Shaun > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, WI, United States ___ 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
Re: [sqlite] Database Diff libs or applications
It's mostly context A but sometimes context B :) Here's a better description of what will happen.. I start out with database A. This database is mostly constant. User Bob opens database A in a custom editor and performs changes and tweaks to the database. Bob then saves out his changes either in the form of some separate transaction file. User Joe opens database A in the same editor and performs different changes to the database and saves them out to a separate file as well. The third user Adam then loads up database A in another application, applies the changes from Bob, applies the changes from Joe, and then performs read-only operations from that point on. When a user has the database open in the editor to make changes, Database A can be considered constant and read-only. I needn't worry about maintaining a constant connection as the file will only ever be updated outside the use of that editor. The final order in which to apply changes needs to be flexible and at the user's discretion (this will result in a different final output but that is expected). I'm assuming I'll need a custom editor in order to load/save incremental changes as opposed to the final database. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita Sent: Thursday, October 01, 2009 9:49 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database Diff libs or applications There has been a lot of discussion of this and several of us are doing it. Are you talking about A) DB1 which has modify data and DB2 which only receives modifications from DB1 only, or B) DB1 and DB2 both get updates independently and need to be synchronized? or C) something else Not (A or B) Your context implies what automated or custom solution you would use. On Thu, Oct 1, 2009 at 9:44 AM, Shaun Seckman (Firaxis) < shaun.seck...@firaxis.com> wrote: > Hello, > >I'm looking to externally track the actions made to a > database so that I can apply those same actions to another database > (assuming the other database has a similar schema). I've searched the > documentation and there doesn't seem to be an easy way to extract this > data so my only option seems to be utilizing some sort of library or > application to diff the two databases and generate a SQL script based on > the changes. > > > > Has anyone done this before? Does such a library or application exist? > I'm sure I could write my own if needed. > > > > -Shaun > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ 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
Re: [sqlite] Database Diff libs or applications
On Thu, Oct 1, 2009 at 8:44 AM, Shaun Seckman (Firaxis) wrote: > Hello, > > I'm looking to externally track the actions made to a > database so that I can apply those same actions to another database > (assuming the other database has a similar schema). I've searched the > documentation and there doesn't seem to be an easy way to extract this > data so my only option seems to be utilizing some sort of library or > application to diff the two databases and generate a SQL script based on > the changes. > > Store the "actions made to a database," in other words, the SQL, in a log and then replay that log against the other database. You can use TRIGGERs to store the SQL in a replay table. Search the archives. Dennis Cote has contributed some code for that in one of the threads. > > Has anyone done this before? Does such a library or application exist? > I'm sure I could write my own if needed. > > > > -Shaun > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, WI, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Diff libs or applications
There has been a lot of discussion of this and several of us are doing it. Are you talking about A) DB1 which has modify data and DB2 which only receives modifications from DB1 only, or B) DB1 and DB2 both get updates independently and need to be synchronized? or C) something else Not (A or B) Your context implies what automated or custom solution you would use. On Thu, Oct 1, 2009 at 9:44 AM, Shaun Seckman (Firaxis) < shaun.seck...@firaxis.com> wrote: > Hello, > >I'm looking to externally track the actions made to a > database so that I can apply those same actions to another database > (assuming the other database has a similar schema). I've searched the > documentation and there doesn't seem to be an easy way to extract this > data so my only option seems to be utilizing some sort of library or > application to diff the two databases and generate a SQL script based on > the changes. > > > > Has anyone done this before? Does such a library or application exist? > I'm sure I could write my own if needed. > > > > -Shaun > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database Diff libs or applications
Hello, I'm looking to externally track the actions made to a database so that I can apply those same actions to another database (assuming the other database has a similar schema). I've searched the documentation and there doesn't seem to be an easy way to extract this data so my only option seems to be utilizing some sort of library or application to diff the two databases and generate a SQL script based on the changes. Has anyone done this before? Does such a library or application exist? I'm sure I could write my own if needed. -Shaun ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users