Re: Merging new and old databases

["David Berg" <drberg1000 gmail com>]

On Tue, Jan 6, 2009 at 2:11 AM, David Collett <david collett gmail com> wrote:
> 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:

Thanks for the pseudo code David.  It looks easy enough, but it's been
so long since I've coded anything (and never with a database) that I'm
sure it would still take me at least an afternoon.

When f-spot switched added the jobs and other tables, wasn't there a
script that would convert the database?  If I could get that script, I
would think I could just export the tags from the old DB and re-import
them into the new db.

--Dave




>
> 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
>>
>