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

my $hexPhotoId = sprintf("%x", $photoId);
my $thumbString = "thumb".sprintf('%016s', $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’:

avi@brilliant:~$ 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:

#! /usr/bin/perl

# shotwell-tag
#
# Tags files specified by filename in shotwell. Handy for 
# getting round shotwell's attempts at hiding the filesystem.
# 
# Avi 2011

use strict;
use DBI;

my $file = shift;
my $tag = shift;

if ($tag !~ /.+/){
        print "Usage:\n\n\tshotwell-tag [file] [tag]\n\n";
        print "Tags [file] with [tag] in shotwell's db\n";
        exit 1;
}

my $dbfile = $ENV{'HOME'}."/.shotwell/data/photo.db";
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");

# Each tag has a string of photo 'ids'. These are generated
# by taking the ID of the photo from PhotoTable, representing
# it in hex, padding that out to 16 characters with leading 
# zeroes and then appending it to the string 'thumb'
my $sth = $dbh->prepare("select id from PhotoTable where filename='$file'");
$sth->execute();
my $row = $sth->fetch;
my $photoId = $row->[0];
unless($photoId =~ /\d+/){print "$file is not in shotwell library\n"; exit 0;}
my $hexPhotoId = sprintf("%x", $photoId);
my $thumbString = "thumb".sprintf('%016s', $hexPhotoId);

$sth = $dbh->prepare("select id from TagTable where name='$tag'");
$sth->execute();
$row = $sth->fetch;
my $tagId = $row->[0];
unless($tagId =~ /\d+/){
	print "Creating tag $tag\n";
	my $sth = $dbh->prepare("insert into TagTable (name) values('$tag')");
	$sth->execute;
}

$sth = $dbh->prepare("Select photo_id_list from TagTable where name='$tag'");
$sth->execute();
$row = $sth->fetch;
my $photoList = $row->[0];
if($photoList !~ /,$/ && $photoList =~ /._/){
	$photoList.=',';
}
if($photoList =~ /$thumbString/){
	print "$file is already tagged with $tag\n";
	exit 0;
}else{
	$photoList.=$thumbString.',';
	$sth = $dbh->prepare("update TagTable set photo_id_list = '$photoList' where name='$tag'");
	$sth->execute;
	print "tagged $file with $tag\n";
	exit 0;
}