tool to help Shotwell migration
This tool aims to help with the process of importing from Shotwell.
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.
#1 Updated by Antoine Beaupré almost 5 years ago
Some more information here:
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:
- the Shotwell database schema is not normalized, ie. the tag/photo mapping is simply a text field in the TagTable (ouch)
- 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
- 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)
- 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.
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 almost 2 years ago
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!