All sorts of things bring me great satisfaction. Excluding family activities my top three would be photography, coding & problem solving and I love it when they all come together. In my IT work I write code to eliminate manual data input wherever possible as to err is human!

I put the images from Mal’s party online so that all her friends could help her rate them. Only the ones they flag will end up on Facebook and in her photo album.

Problem: How to ensure I export only the correct images from Lightroom?
Solution: With a couple of Perl scripts.

Lightroom uses a SQLite database to store its data. One script on the web server outputs a list of rated files from the Coppermine gallery as ratings.lis. It’s a simple list, one file per line. A second script on the PC (shown below) takes the list, finds all of the right images in the LR3 database and updates their rating values to 5.

Job done, all the right images selected and updated in a few seconds, no errors 🙂

If you want to use the code I recommend installing ActiveState Perl if running on Windows. Ensure you have your ratings.lis input file and this script in the same directory as the .lrcat file. Update the .lrcat file name in the script to reflect your catalogue name and comment out the “$folder=18;” line by prefixing it with a #. When run the script will list the folder numbers in the catalogue and exit. Pick the correct folder number for your files, uncomment the line and update the number. The second run will update the ratings.

[update.pl]

$folder=18;

use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=Mals 30th.lrcat","","",$dbargs);

if (! defined $folder) {
  my $query="SELECT AgLibraryFolder.id_local, AgLibraryRootFolder.absolutePath, AgLibraryFolder.pathFromRoot FROM AgLibraryFolder INNER JOIN AgLibraryRootFolder where AgLibraryFolder.rootFolder=AgLibraryRootFolder.id_local";
  my $query_handle = $dbh->prepare($query);
  $query_handle->execute();
  $query_handle->bind_columns(\my($id_local,$rootFolder,$pathFromRoot ));
  print "Please set \$folder to the right value, this will ensure you don't update duplicate file names in another directory\n";
  print "id_local,rootFolder,pathFromRoot\n";
  while ($query_handle->fetch()) {
    print "$id_local,$rootFolder,$pathFromRoot\n";
    $folder{$id_local}=$rootFolder . $pathFromRoot;
  }
  exit;
}

my $query="SELECT Adobe_images.rootFile,Adobe_images.rating,baseName from AgLibraryFile,Adobe_images WHERE Adobe_images.rootFile like AgLibraryFile.id_local AND folder=$folder";
my $query_handle = $dbh->prepare($query);
$query_handle->execute();

$query_handle->bind_columns(\my($rootFile,$rating,$baseName));
print "rootFile,Rating,baseName\n";
while ($query_handle->fetch()) {
  # print "$rootFile,$rating,$baseName\n";
  $index{$baseName}=$rootFile;
}

open (RATINGS,"<ratings.lis");
while (<RATINGS>) {
  $_=~/^(.*)\..*?$/;
  $base=$1;
  print $base . "\n";
  my $update="UPDATE Adobe_images SET rating=5 WHERE rootFile=$index{$base}";
  my $update_handle = $dbh->prepare($update);
  $update_handle->execute();
}

$query_handle->finish;
undef ($dbh);