James O'Neill's Blog

August 9, 2012

Getting to the data in Adobe Lightroom–with or without PowerShell

Filed under: Databases / SQL,Photography,Powershell — jamesone111 @ 7:01 am

Some Adobe software infuriates me (Flash), I don’t like their PDF reader and use Foxit instead, apps which use Adobe-Air always seem to leak memory. But I love Lightroom .  It does things right – like installations – which other Adobe products get wrong. It maintains a “library” of pictures and creates virtual folders of images ( “collections” ) but it maintains metadata in the images files so data stays with pictures when they are copied somewhere else – something some other programs still get badly wrong. My workflow with Lightroom goes something like this.

  1. If I expect to manipulate the image at all I set the cameras to save in RAW, DNG format not JPG (with my scuba diving camera I use CHDK to get the ability to save in DNG)
  2. Shoot pictures – delete any where the camera was pointing at the floor, lens cap was on, studio flash didn’t fire etc. But otherwise don’t edit in the camera.
  3. Copy everything to the computer – usually I create a folder for a set of pictures and put DNG files into a “RAW” subfolder. I keep full memory cards in filing sleeves meant for 35mm slides..
  4. Using PowerShell I replace the IMG prefix with something which tells me what the pictures are but keeps the camera assigned image number. 
  5. Import Pictures into Lightroom – manipulate them and export to the parent folder of the “RAW” one. Make any prints from inside Lightroom. Delete “dud” images from the Lightroom catalog.
  6. Move dud images out of the RAW folder to their own folder. Backup everything. Twice. [I’ve only recently learnt to export the Lightroom catalog information to keep the manipulations with the files]
  7. Remove RAW images from my hard disk

There is one major pain. How do I know which files I have deleted in Lightroom ? I don’t want to delete them from the hard-disk I want to move them later. It turns out Lightroom uses a SQL Lite database and there is a free Windows ODBC driver for SQL Lite available for download.  With this in place one can create a ODBC data source – point it at a Lightroom catalog and poke about with data. Want a complete listing of your Lightroom data in Excel? ODBC is the answer. But let me issue these warnings:

  • Lightroom locks the database files exclusively – you can’t use the ODBC driver and Lightroom at the same time. If something else is holding the files open, Lightroom won’t start.
  • The ODBC driver can run UPDATE queries to change the data: do I need to say that is dangerous ? Good.
  • There’s no support for this. If it goes wrong, expect Adobe support to say “You did WHAT ?” and start asking about your backups. Don’t come to me either. You can work from a copy of the data if you don’t want to risk having to fall back to one of the backups Lightroom makes automatically

   I was interested in 4 sets of data shown in the following diagrams. Below is image information with the Associated metadata, and file information. Lightroom stores images (Adobe_Images table) IPTC and EXIF metadata link to images – their “image” field joins to the “id_local” primary key in images. Images have a “root file” (in the AgLibraryFile table) which links to a library folder (AgLibraryFolder) which is expressed as a path from a root folder (AgLibraryRootFolder table). The link always goes to the “id_local” field I could get information about the folders imported into the catalog just by querying these last two tables (Outlined in red)


The SQL to fetch this data looks like this for just the folders
SELECT RootFolder.absolutePath || Folder.pathFromRoot as FullName
FROM   AgLibraryFolder     Folder
JOIN   AgLibraryRootFolder RootFolder O
N  RootFolder.id_local = Folder.rootFolder
ORDER BY FullName 

SQLlite is one of the dialects of SQL which doesn’t accept AS in the FROM part of a SELECT statement . Since I run this in PowerShell I also put a where clause in which inserts a parameter. To get all the metadata the query looks like this
SELECT    rootFolder.absolutePath || folder.pathFromRoot || rootfile.baseName || '.' || rootfile.extension AS fullName, 
          LensRef.value AS Lens,     image.id_global,       colorLabels,                Camera.Value       AS cameraModel,
          fileFormat,                fileHeight,            fileWidth,                  orientation ,
captureTime,               dateDay,               dateMonth,                  dateYear,
          hasGPS ,                   gpsLatitude,           gpsLongitude,               flashFired,
focalLength,               isoSpeedRating ,       caption,                    copyright
FROM      AgLibraryIPTC              IPTC
JOIN      Adobe_images               image      ON      image.id_local = IPTC.image
JOIN      AgLibraryFile              rootFile   ON   rootfile.id_local = image.rootFile
JOIN      AgLibraryFolder            folder     ON     folder.id_local = rootfile.folder
JOIN      AgLibraryRootFolder        rootFolder ON rootFolder.id_local = folder.rootFolder
JOIN      AgharvestedExifMetadata    metadata   ON      image.id_local = metadata.image
LEFT JOIN AgInternedExifLens         LensRef    ON    LensRef.id_Local = metadata.lensRef
LEFT JOIN AgInternedExifCameraModel  Camera     ON     Camera.id_local = metadata.cameraModelRef

Note that since some images don’t have a camera or lens logged the joins to those tables needs to be a LEFT join not an inner join. Again the version I use in PowerShell has a Where clause which inserts a parameter.

OK so much for file data – the other data I wanted was about collections. The list of collections is in just one table (AgLibraryCollection) so very easy to query, and but I also wanted to know the images in each collection.


Since one image can be in many collections,and each collection holds many images AgLibraryCollectionImage is a table to provide a many to relationship. Different tables might be attached to AdobeImages depending on what information one wants from about the images in a collection, I’m interested only in mapping files on disk to collections in Lightroom, so I have linked to the file information and I have a query like this.

SELECT   Collection.name AS CollectionName ,
         RootFolder.absolutePath || Folder.pathFromRoot || RootFile.baseName || '.' || RootFile.extension AS FullName
FROM     AgLibraryCollection Collection
JOIN     AgLibraryCollectionimage cimage     ON collection.id_local = cimage.Collection
OIN     Adobe_images             Image      ON      Image.id_local = cimage.image
JOIN     AgLibraryFile            RootFile   ON   Rootfile.id_local = image.rootFile
JOIN     AgLibraryFolder          Folder     ON     folder.id_local = RootFile.folder
JOIN     AgLibraryRootFolder      RootFolder ON RootFolder.id_local = Folder.rootFolder
ORDER BY CollectionName, FullName

Once I have an ODBC driver (or an OLE DB driver) I have a ready-made PowerShell template for getting data from the data source. So I wrote functions to let me do :
Get-LightRoomItem -ListFolders -include $pwd
To List folders, below the current one, which are in the LightRoom Library
Get-LightRoomItem  -include "dive"
To list files in LightRoom Library where the path contains  "dive" in the folder or filename
Get-LightRoomItem | Group-Object -no -Property "Lens" | sort count | ft -a count,name
To produce a summary of lightroom items by lens used. And
$paths = (Get-LightRoomItem -include "$pwd%dng" | select -ExpandProperty path)  ;   dir *.dng |
           where {$paths -notcontains $_.FullName} | move -Destination scrap -whatif

  Stores paths of lightroom items in the current folder ending in .DNG in $paths;  then gets files in the current folder and moves those which are not in $paths (i.e. in Lightroom.) specifying  -Whatif allows the files to be confirmed before being moved.

Get-LightRoomCollection to list all collections
Get-LightRoomCollectionItem -include musicians | copy -Destination e:\raw\musicians    to copies the original files in the “musicians” collection to another disk

I’ve shared the PowerShell code on Skydrive

August 7, 2012

The cloud, passwords, and problems of trust and reliance

Filed under: Privacy,Security and Malware — jamesone111 @ 9:02 pm

In recent days a story has been emerging of a guy called Mat Honan. Mat got hacked, the hackers wanted his twitter account simply because he had a three letter twitter name. Along the way they wiped his Google mail account and (via Apple’s iCloud) his iPhone, iPad and his Macbook. Since he relied on stuff being backed up in the cloud he lost irreplaceable family photos, and lord only knows what else. There are two possible reactions Schadenfreude – “Ha, ha I don’t rely on Google or Apple look what happens to people who do” , “What an idiot, not having a backup”, or “There but for the grace of God goes any of us”.

Only people who’ve never lost data can feel unsympathetic to Mat and I’ve lost data. I’ve known tapes which couldn’t be read on a new unit after the old one was destroyed in a fire. I’ve learnt by way of a disk crash that a server wasn’t running it’s backups correctly. I’ve gone back to optical media which couldn’t be read. My backup drive failed a while back – though fortunately everything on it existed somewhere else, making a new backup showed me in just how many places. I’ve had memory cards fail in the camera before I had copied the data off them and I had some photos which existed only on a laptop and a memory card which were in the same bag that got stolen (the laptop had been backed up the day before the photos were taken). The spare memory card I carry on my key-ring failed recently, and I carry that because I’ve turned up to shoot photos with no memory card in the camera – never close the door on the camera with the battery or memory card out. I treat memory cards like film and just buy more and keep the old cards as a backstop copy. So my data practices look like a mixture of paranoia and superstition and I know, deep down, that nothing is infallible.

For many of us everything we have in the cloud comes down to one password. I don’t mean that we logon everywhere with “Secret1066!”  (no, not my password). But most of us have one or perhaps two email address which we use when we register.  I have one password which I use on many, many sites which require me to create an identity but that identity doesn’t secure anything meaningful to me. It doesn’t meet the rules of some sites (and I get increasingly cross with sites which define their own standards for passwords) and on those sites I will set a one off password. Like “2dayisTuesday!” when I come to use the site again I’ll just ask them to reset my password. Anything I have in the cloud is only as secure as my email password. 
There are Some hints here, first: any site which can mail you your current password doesn’t encrypt it properly the proper way to store passwords is as something computed from the password so it is only possible to tell if the right password was entered not what the password is. And second, these computations are case sensitive and set no maximum password length, so any site which is case insensitive or limits password length probably doesn’t have your details properly secured.  Such sites are out there – Tesco for example – and if we want to use them we have to put up with their security. However if they get hacked (and you do have to ask , if they can’t keep passwords securely, what other weaknesses are there ?) your user name , email and password are in the hands of the hackers, so you had better use different credentials anywhere security matters – which of course means on your mailbox.

So your email password is the one password to rule them all and obviously needs to be secure. But there is a weak link, and that seems to be where the people who hacked Mat found a scary loophole. The easiest way into someone’s mailbox might be to get an administrator to reset the password over the phone – not to guess or brute force it. The only time I had my password reset at Microsoft the new one was left on my voicemail – so I had to be able to login to that. If the provider texts the password to a mobile phone or resets it (say) to the town where you born (without saying what it is) that offers a level of protection; but – be honest – do you know what it takes to get someone at your provider to reset your password, or what the protocol is ?  In Mat’s case the provider was Apple – for whom the hacker knew an exploitable weakness – but it would be naive to think that Apple was uniquely vulnerable.

Mat’s pain may show the risk in having only a mailbox providers password reset policy to keep a hacker out of your computer and/or your (only) backup. One can build up a fear of other things that stop you having access to either computer or backup without knowing how realistic they are.  I like knowing that my last few phones could be wiped easily but would I want remote wipe of a laptop ? When my laptop was stolen there wasn’t any need to wipe it remotely as it had full volume encryption with Microsoft’s bitlocker (saving me a difficult conversation with corporate security) and after this story I’ll stick to that. Cloud storage does give me off-site backup and that’s valuable – it won’t be affected if I have a fire or flood at home – but I will continue to put my faith in traditional off-line backup and I’ve just ordered more disk capacity for that.

Create a free website or blog at WordPress.com.