Re: keep 2 f-spot databases synchronized

["Richard Bronosky" <mythtv bronosky com>]

Wow, no follow up?  I put a lot of work into this for you.  I didn't do all the work for you and create a GUI or anything.  But, I promise any moderate hack can use what I've explained to solve the task at hand.  If you take the time to learn to do it, you will definitely better yourself.  I bet you'll even walk with your head held a little higher.

On 3/28/07, Richard Bronosky <mythtv bronosky com> wrote:

On 3/28/07, Warren Baird < photogeekmtl gmail com> wrote:

Lorenzo Milesi wrote:
> On 3/28/07, Mike Gemünde < mike weingutjanson de> wrote:
>> I have 2 computers running f-spot and I want to keep the 2 databased
>> synchronized. Have anybody an idea for doing this? At the moment I use,
>> as a workaround, rsync, but this is not very comfortable.
>
> Why you feel rsync not comfortable?
> What would you want?

Well not to put words in the original poster's mouth, but what *I* would
want is the ability to deal with the scenario where I add a new tag to
photo1.jpg on machine A, and a new tag to photo2.jpg on machine B, and
after running the sync seeing the tag on photo1.jpg on machine B and the
tag on photo2.jpg on machine A.

given that metadata is stored in a single database, a file level sync
like rsync or unison will never be able to do that.

If I needed to do this I would start by install the sqlite3 package, then looking at the man page and exploring to CLI.  Actually, that is exactly what I did.  I found that this will convert the binary db file into a usable text file.
sqlite3 ~/.gnome2/f-spot/photos.db '.dump'
If you pipe this to vim like so:
sqlite3 ~/.gnome2/f-spot/photos.db '.dump'|vim +'set nowrap' -
You will see that it is pretty simple SQL in there.  The doc starts with a "BEGIN TRANSACTION;" line, ends with a "COMMIT;" line, and in between you'll find 7 "CREATE TABLE..." lines each followed by a bunch of "INSERT..." lines.  Don't let the long BASE64 encoded tag icons overwhelm you.  Each insert line reflect one row in a table.  As long as you do not change the same row on both machines between syncs, this level of granularity should suffice.  If you need column level granularity, you will need to pipe this through an awk (or similar text manipulating language) script to get each of the comma separated column entries on their own line.  (SQL is comfortable with multi-line statements because it requires semi-colons to denote the end of a statement.  the script will, however, need to be smart enough not to insert line break after commas inside of quoted fields.)

So, now what?  Instead of sending it to vim, send it to a file like so:
sqlite3 ~/.gnome2/f-spot/photos.db '.dump'>/tmp/fspot-local.db.sql
And grab the db from your remote machine like so:

ssh user machine sqlite3 ~/.gnome2/f-spot/photos.db '.dump'>/tmp/fspot-remote.db.sql
Then use vimdiff to manually merge them like so
vimdiff /tmp/fspot-local.db.sql /tmp/fspot-remote.db.sql

Once merged, you can backup your original local db, and replace it:
mv ~/.gnome2/f-spot/photos.db ~/.gnome2/f-spot/photos.db.bak; sqlite3 ~/.gnome2/f-spot/photos.db '.read /tmp/fspot.sql'

That should do it.  You can script every bit of this except for the merging via vimdiff. and that is only necessary for situations were rows are removed from tables.  Row inserts and changes can be automated.  For full automation you'd need versioning.

Let me know how this works for you.

I assume that this 'Conduit' project mentioned in another post is doing
database level syncing.

of course, there's also the issue of conflicts.   What if we remove
photo1.jpg on machine A, and add a tag to photo1.jpg on machine B?
what if we perform red-eye fixing on one machine, and a contrast adjust
on the other...

Until they add scripted manipulations to the DB (like picasa does) this isn't happening!

Warren

_______________________________________________
F-spot-list mailing list
F-spot-list gnome org
http://mail.gnome.org/mailman/listinfo/f-spot-list

--
.!# RichardBronosky #!.

-- 
.!# RichardBronosky #!.