Project

General

Profile

Feature #9461

tool to help Shotwell migration

Added by Antoine Beaupré over 4 years ago. Updated over 1 year ago.

Status:
New
Priority:
Low
Assignee:
-
Category:
General
Target version:
-
Start date:
06/09/2013
Due date:
% Done:

0%

Affected Version:
System:
bitness:
hardware architecture:
amd64/x86

Description

This tool aims to help with the process of importing from Shotwell.

http://www.darktable.org/redmine/projects/users/wiki/Import_from_other_software

It assumes that tags are all created in Darktable, otherwise it could be improved to do just that, but I didn't bother because most of the tags were in and I just had to create two by hand using:

anarcat@marcos:~$ sqlite3 .config/darktable/library.db
sqlite> insert into tags (name) values ('char', 'Mt. St.-Grégoire');

Then the script runs just fine.

Here's the example of a dry run:

anarcat@marcos:~$ python bin/shotwell2darktable.py  -v -l 1 -n
not doing any changes
loading shotwell database...
loading darktable database...
finding images without metadata...
thumb0000000000001db4   7604    0       fanfare,ferme   /home/anarcat/Photos/2012/06/30/IMG_6634.JPG
would be executing UPDATE images SET flags = ( (flags&4088) + 0 ) WHERE id = 7195
would be executing exiv2 -eaX  -M 'add Xmp.dc.subject XmpBag "fanfare, ferme"' /home/anarcat/Photos/2012/06/30/IMG_6634.JPG
renaming /home/anarcat/Photos/2012/06/30/IMG_6634.xmp to /home/anarcat/Photos/2012/06/30/IMG_6634.JPG.xmp
would be executing INSERT INTO tagged_images SELECT 7195, id FROM tags WHERE name = fanfare
would be executing INSERT INTO tagged_images SELECT 7195, id FROM tags WHERE name = ferme
996/1183 images found, 1 found with metadata, 0 changed

Here's a real run with only one entry:

anarcat@marcos:~$ python bin/shotwell2darktable.py  -v -l 1
loading shotwell database...
loading darktable database...
finding images without metadata...
thumb0000000000001db4   7604    0       fanfare,ferme   /home/anarcat/Photos/2012/06/30/IMG_6634.JPG
executing UPDATE images SET flags = ( (flags&4088) + 0 ) WHERE id = 7196
executing exiv2 -eaX  -M 'add Xmp.dc.subject XmpBag "fanfare, ferme"' /home/anarcat/Photos/2012/06/30/IMG_6634.JPG
renaming /home/anarcat/Photos/2012/06/30/IMG_6634.xmp to /home/anarcat/Photos/2012/06/30/IMG_6634.JPG.xmp
executing INSERT INTO tagged_images SELECT 7196, id FROM tags WHERE name = fanfare
executing INSERT INTO tagged_images SELECT 7196, id FROM tags WHERE name = ferme
996/1183 images found, 1 found with metadata, 1 changed

May this help you in your migratory endeavors.

shotwell2darktable.py Magnifier (4.19 KB) Antoine Beaupré, 06/09/2013 06:58 AM

History

#1 Updated by Antoine Beaupré over 4 years ago

Some more information here:

The script requires exiv2 (I could have used http://redmine.yorba.org/projects/gexiv2/wiki but i forgot) and sqlite3 bindings: http://docs.python.org/2/library/sqlite3.html

It talks directly to your sqlite darktable database, so you will want to make a backup of it before messing around there. The files themselves are not touched, although the exiv tool will write a .xmp sidecar file.

Make sure you stop darktable before running the script.

Here are other notes from the import page which are not really relevant to that page anymore, but more to the design of this script:

I have tried to find a way to import that metadata in, but only managed to find a bug in DT (#9457) so far. We need a way to batch-edit tags in Darktable for this to work. This is what I have found out so far:

  1. the Shotwell database schema is not normalized, ie. the tag/photo mapping is simply a text field in the TagTable (ouch)
  2. worse: the mapping is done weirdly. the data stored is the name of the thumbnail for the picture (!!) and that name is based on the hexadecimal of the id of the photo
  3. writing tags into Darktable is hard: writing it to the XMP file seems to be the only way, and it may crash DT if you are not careful (#9457)
  4. we seem to need to either remove and reimport those files into darktable or edit the darktable DB by hand

This is what I have done to import those tags in DT:

exiv2 -eaX  -M 'add Xmp.dc.subject XmpBag "tag"'   IMG_0850_CR2.jpg
# this writes in IMG_0850_CR2.xmp, so we need to fix that for DT to find it
mv IMG_0850_CR2.xmp IMG_0850_CR2.jpg.xmp

Then you need to remove and import that file. This doesn't seem very efficient, and i'm waiting for feedback on how to do this better.

Then on the shotwell side we need to figure out how to extract photo -> tags mapping. This is mostly hellish. Say I know I have photo IMG_0850.CR2 somewhere. I can find it in the photo database with:

sqlite> SELECT * FROM PhotoTable p  WHERE p.filename like '%IMG_0850.CR2' limit 10;
9229|/home/anarcat/Photos/2013/02/24/IMG_0850.CR2|3684|2760|10425751|1361735550|1361735551|1|1|1362703538|744||e1fd28cb81c180911dac942aa6ad79f7|3eecc4dc614166edb46a1c19b96b208b||1362703631|0|1||||-1|0|0|CAMERA|-1|605|-1

So far, so good. But to find the thumbnails associated with it, i need to convert that first field to hexadecimal and then look it up in the tag table. 9229 is 0x240D in hex, so that will be:

sqlite> select name from TagTable where photo_id_list LIKE '%240d%';
/Lieu
/Lieu/obitciwan

In short, there's absolutely no way to do this through a simple shell script, it will need O(N*M) queries where N is the number of pictures and M is the number of tags per picture. Wheepee. Oh, and to add to the fun: some of the TagTable rows are actual numeric ids, not hex.

See also http://redmine.yorba.org/projects/shotwell/wiki/ShotwellArchDatabase

Other useful SQL queries, on the DT tables:

  • finding all JPG imports that are duplicates of their RAW (.CR2 here) counterpart: SELECT i.filename, i.id, i.group_id, i2.filename, i2.id, i2.group_id FROM images i JOIN images i2 WHERE i.filename NOT LIKE '%.jpg' AND rtrim(i.filename, ".CR2") || "_CR2.jpg" LIKE i2.filename AND i.group_id <> i2.group_id LIMIT 10; (this spawned another tool, see #9460)
  • finding images without tags: SELECT i.filename, t.imgid FROM images i LEFT JOIN tagged_images t ON t.imgid = i.id WHERE t.imgid IS NULL;
  • finding images without tags, excluding darktable-specific ones or the ones i made in the import from shotwell: SELECT i.filename, ti.imgid FROM images i LEFT JOIN tagged_images ti ON ti.imgid = i.id AND ti.tagid NOT IN (SELECT id from tags where name LIKE 'darktable%' OR 'shotwelll%') WHERE ti.imgid IS NULL

#2 Updated by Anonymous over 1 year ago

Morning. Antoine!

I switched from Ubuntu to iMac in 2013, and since then have been having to manage my Shotwell library of 20,000+ images, mostly JPG and RAW, using Ubuntu in a virtual machine.

I'd like to migrate to a native application on the iMac, but worry about the loss of all the metadata stored in the Shotwell database.

Have you done any more work on this utility? I'm not scared of doing a bit of code-tweaking to get things done, but with 20,000 images mistakes are expensive!

Michael

Also available in: Atom PDF