Tagging images by path in Shotwell

I've finally decided to use an image manager, and since it comes with Ubuntu this week I've gone with Shotwell. I've got a directory hierarchy containing most of my images which is sort-of sorted already, and I'm probably going to keep adding to it, if for no other reason than force of habit.

I know that one of the wonderful features of these photo managers is that you can tag photos, and obviously a photo can be in more than one tag rather more easily than it can be in several directories. That said, all photo managers seem to have decided that an easy, fast way to tag photos isn't what's needed.

Additionally, shotwell's got this weird thing for hiding the fact that there's a filesystem from you, and I can't find any way to tag files by directory. So I've poked around the database and written a script to do it, which is below and here and pasted below in case I change my mind about file hierarchies later.

The oddest bit is the way the filenames are linked to the tags. The TagTable table has a field `photo_id_list` which contains a list of photo IDs in a format that I've not found anywhere else in the (admittedly not very extensive) db.

They're created by taking the id of the image (its value in the `id` field of the PhotoTable table), converting it to a hex value, padding it out to 16 characters with leading zeroes, and then concatenating it onto the string 'thumb':

  1. my <span class="katex math inline">hexPhotoId = sprintf("%x",</span>photoId);
  2. my <span class="katex math inline">thumbString = "thumb".sprintf('%016s',</span>hexPhotoId);

Anyway, the script's a bit simple because bash is quite good at handling loads of files; usage is like this to tag the contents of ~/Pictures/2011-france/ with the tag 'morzine':

[email protected]:~$ find ~/Pictures/2011-france/ -type f -exec ./shotwell-tag {} morzine \;
Creating tag morzine
tagged /home/avi/Pictures/2011-france/R0012810.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012850.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012911.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012931.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012921.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012794.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012883.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012881.JPG with morzine

I've no idea if it breaks anything - I wrote it about an hour ago, have tagged ~500 photos with it since, and Shotwell doesn't seem to be annoyed. YMMV. Here's the script:

  1.  
  2. #! /usr/bin/perl
  3.  
  4. # shotwell-tag
  5. #
  6. # Tags files specified by filename in shotwell. Handy for
  7. # getting round shotwell's attempts at hiding the filesystem.
  8. #
  9. # Avi 2011
  10.  
  11. use strict;
  12. use DBI;
  13.  
  14. my <span class="katex math inline">file = shift;
  15. my</span>tag = shift;
  16.  
  17. if (<span class="katex math inline">tag !~ /.+/){
  18. print "Usage:\n\n\tshotwell-tag [file] [tag]\n\n";
  19. print "Tags [file] with [tag] in shotwell's db\n";
  20. exit 1;
  21. }
  22.  
  23. my</span>dbfile = <span class="katex math inline">ENV{'HOME'}."/.shotwell/data/photo.db";
  24. my</span>dbh = DBI->connect("dbi:SQLite:dbname=<span class="katex math inline">dbfile","","");
  25.  
  26. # Each tag has a string of photo 'ids'. These are generated
  27. # by taking the ID of the photo from PhotoTable, representing
  28. # it in hex, padding that out to 16 characters with leading
  29. # zeroes and then appending it to the string 'thumb'
  30. my</span>sth = <span class="katex math inline">dbh->prepare("select id from PhotoTable where filename='</span>file'");
  31. <span class="katex math inline">sth->execute();
  32. my</span>row = <span class="katex math inline">sth->fetch;
  33. my</span>photoId = <span class="katex math inline">row->[0];
  34. unless(</span>photoId =~ /\d+/){print "<span class="katex math inline">file is not in shotwell library\n"; exit 0;}
  35. my</span>hexPhotoId = sprintf("%x", <span class="katex math inline">photoId);
  36. my</span>thumbString = "thumb".sprintf('%016s', <span class="katex math inline">hexPhotoId);</span>sth = <span class="katex math inline">dbh->prepare("select id from TagTable where name='</span>tag'");
  37. <span class="katex math inline">sth->execute();</span>row = <span class="katex math inline">sth->fetch;
  38. my</span>tagId = <span class="katex math inline">row->[0];
  39. unless(</span>tagId =~ /\d+/){
  40. print "Creating tag <span class="katex math inline">tag\n";
  41. my</span>sth = <span class="katex math inline">dbh->prepare("insert into TagTable (name) values('</span>tag')");
  42. <span class="katex math inline">sth->execute;
  43. }</span>sth = <span class="katex math inline">dbh->prepare("Select photo_id_list from TagTable where name='</span>tag'");
  44. <span class="katex math inline">sth->execute();</span>row = <span class="katex math inline">sth->fetch;
  45. my</span>photoList = <span class="katex math inline">row->[0];
  46. if(</span>photoList !~ /,<span class="katex math inline">/ &&</span>photoList =~ /._/){
  47. <span class="katex math inline">photoList.=',';
  48. }
  49. if(</span>photoList =~ /<span class="katex math inline">thumbString/){
  50. print "</span>file is already tagged with <span class="katex math inline">tag\n";
  51. exit 0;
  52. }else{</span>photoList.=<span class="katex math inline">thumbString.',';</span>sth = <span class="katex math inline">dbh->prepare("update TagTable set photo_id_list = '</span>photoList' where name='<span class="katex math inline">tag'");</span>sth->execute;
  53. print "tagged <span class="katex math inline">file with</span>tag\n";
  54. exit 0;
  55. }
  56.