[KPhotoAlbum] Optimization of index.xml

[Shawn Willden]

On Tuesday 29 August 2006 08:13, Tuomas Suutari wrote:
> Anyway MySQL and PostgreSQL are also supported by the SQL backend.

Awesome!  This is truly excellent, and I'm really excited to be able for both 
my wife and I to be able to work with images at the same time, safely.

That said, do you have any ideas about how I can manage the scenario where 
multiple copies of the database are in use?  Here's what I'm thinking:  Most 
of the time, we work on our photo database from home.  In that environment, 
MySQL or PostgreSQL are perfect (and, in fact, I already have a MySQL server 
installed on the home file server).  However, I not infrequently search for 
and/or modify photos when I'm traveling, and I want to be able to do that, 
too.

Have you thought any about this sort of multi-database scenario?  Obviously, 
it's easy enough for me to take a copy of the database with me when I leave 
home, whether MySQL (which is already running on my laptop), SQLite or even 
XML.  The issue is how to synchronize any changes that I might make back to 
the authoritative database on the file server when I get home (or when I get 
an Internet connection).

Obviously, any workable synchronization mechanism would have to recognize 
conflicting changes and offer the user the opportunity to resolve them.

One way to do it would be a three-way compare.  If when I copied the DB to my 
laptop I stored two copies, one for active use and the other for reference, 
then it would be a simple matter to write a tool that compared my active 
laptop database to my reference database and propagated any differences to 
the server database, provided the server's copy of that row was the same as 
the reference database.

Another way to do it would be to add an "updated" timestamp to each row.  Then 
I could do the synchronization without needing the extra copy of the 
database, I'd just have to know the time at which I performed the copy from 
server to laptop.  In order to avoid clock synchronization problems, 
the 'timestamp' I refer to would probably be a version counter, rather than a 
real timestamp, but the effect is the same.

A third way would be to store a journal in the laptop DB of all of the changes 
made, along with their previous values,  This journal could then 
be "executed" against the server -- but only where the previous value matched 
the server version.  Differences would constitute conflicts.

Another variant of the journaling approach would be to have both servers 
journal all of the changes, and record on the laptop the server's journal 
pointer.

I'm sure there are other approaches, and perhaps some simpler, and better, 
than what I've described.

Has anyone put any thought into this?  I'd be willing to write a 
synchronization tool, but it would be good to discuss an appropriate approach 
first.

Oh, and what's the current state of the SQL support?  Can I try it now?

	Shawn.