Re: Merging new and old databases

[David Berg <drberg1000 gmail com>]

I added the jobs table to the old database and f-spot was able to open
it.  Thanks for the help.  I've got what I needed.

--Dave

On Fri, Jan 16, 2009 at 8:20 AM, David Berg <drberg1000 gmail com> wrote:
> 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
>>>
>>
>