On 22 Jul 2011, at 10:06pm, Kent Tenney wrote: > I need to monitor changes in an an sqlite3 file an application uses. > > I make copies periodically, so I have 2 files > shotwell_2011-07-21.db and shotwell_2011-07-22.db > > For example, I'm interested in PhotoTable, which had rows added, > deleted and changed between those days. > > Figuring out additions seems straightforward, there is an 'id' column. > > I would like advice on ways to determine changes and deletions. > I'm currently accessing the files via Python's sqlite3 library, though > plan to start using SqlAlchemy. > > I expect I'm not the first to have interest in this, but have been > unable to locate any discussion of it.
You're effectively asking how to sych two copies of the same database. That's something I used to write about a lot. It's impossible unless you add more data. You first do a LEFT JOIN and a RIGHT JOIN to find rows which were added and rows which were deleted. No real problem, just two SELECTs on the 'rowid' column every TABLE must have. But how do you find rows which were updated ? You have to write a SELECT that compares every field, which means either some custom logic for each TABLE, or a routine that finds out all the column names returned when you SELECT * then turns that into a SELECT that compares each column of each row. Messy. And even then, do you care if a row was changed twice ? If the same field was changed twice do you need to know the 'middle' value ? If two fields were changed do you need to know which order the changes were made ? If a row was changed then deleted do you need to know the change ? The most detailed approach is journaling. Write your own routine that you call whenever you do DELETE or UPDATE. It does what you want but also writes a timestamped entry to a 'log' TABLE. To list all the changes, just read whatever you noted in your 'log' TABLE. This gives you as much data as you care to log. It might be simplest to log the SQL command itself. A less detailed approach is to add a 'lastChange' column to every table. Whenever you INSERT or UPDATE, make sure you update this column too. You use a SELECT ... JOINs to find deletions, but you can SELECT on the lastChange column to find insertions and updates. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
