Re: Merging new and old databases

["David Collett" <david collett gmail com>]

Doing it by hand would require only fairly basic SQL skills, from
memory the schemas are pretty straight-forward You could use the
sqlite libraries provided by most languages (python for instance
supports sqlite in it's standard library).

in (ugly, incomplete and probably flawed) pseudo-code it might look
something like:

photos = olddb.execute("select * from photos")
for photo in photos:
  tags = olddb.execute("select * from photo_tags where photo_id=%s",
photo['id'])
  for tag in tags:
    tagdata = olddb.execute("select * from tag where id=%s", tag['tag_id'])
    // 1. check if tagdata['name'] exists in newdb.tags, else insert
(you may need some extra logic to preserve tag hierarchies)
    // 2. insert row into photo_tags table in newdb. You will have to
lookup the id of the photo (by filename?) in newdb unless you know for
sure it will be the same as in the olddb (e.g. if olddb is a previous
incarnation of newdb, and even then it may not be a guarantee)

Take plenty of backups and give it try!

Hope this helps,
Dave

On Mon, Jan 5, 2009 at 9:09 AM, David Berg <drberg1000 gmail com> wrote:
> On Sat, Jan 3, 2009 at 3:08 PM, David Berg <drberg1000 gmail com> wrote:
>> I have a backed-up photos.db from version 0.2.1 that has a lot of tags
>> not in my current database in f-spot 0.5.0.3.  Of course, there are
>> many tags in the 0.5.0.3 database that aren't in the 0.2.1.
>>
>> Database       |     .5        |    .2
>> -------------------+-------------+-----
>> New Photos    |     X        |
>> New Tags        |     X        |
>> Old Photos     |     X         |    X
>> Old Tags          |   Some   |    X
>>
>>
>> Is there a way I could pull the tags from photos.db-0.2.1 into my
>> current database?  My skills with sql aren't up to the task of writing
>> a script to do the job so I'm hoping it's already written or there's
>> another way to do it.  Otherwise I'll be tagging them all over again.
>
> Looking into this a bit more...
>
> F-Spot 0.5 won't open the old database which is sqlite3 but doesn't
> have all of the tables a new database does.  If I could find away to
> have the old database converted into a form that 0.5 could read, I the
> MergeDb addin might let me do the job.  If not, I could load the old
> database, write metadata to file then reimport the old files, but
> again I need to beable to read the old database.  Is there a tool
> somewhere that will convert it?
>
> Thanks again.
>
> --Dave
> _______________________________________________
> F-spot-list mailing list
> F-spot-list gnome org
> http://mail.gnome.org/mailman/listinfo/f-spot-list
>