Hacking Lightroom or Why Everyone Should Know How to Program

I firmly believe that everyone should have basic programming skills. Whether you are a photographer, a mechanic or a doctor, pretty much every system in today’s world is ran by software; understanding how that software works will lead to better understanding of tools that you use to do your job and will make you a better professional. A little while ago Wired magazine ran an article about a Facebook software engineer who taught his 8-year-old daughter to program – he even wrote a book (cleverly titled Lauren Ipsum) to explain programming concepts to young children. Let me make my case as to why photographers need to have basic understanding of coding and databases. A few days ago my friend Jenny Karlsson sent me a link to an Adobe Lightroom plug-in that would tell you what focal lengths you use the most in your photographs. Such information is really useful if you are trying to decide what lens to purchase next. After talking to Jenny I decided to investigate this question further – I wanted more information than just focal lengths. I wanted to see what lenses I used most often; I wanted to see in how many of my photos I used fill flash (I’ve been told that I’m too obsessed with artificial lighting). There are plenty of plug-ins and stand-along programs out there that would pull that information for you. The caveat is that good apps cost money and crappy apps are just that – crappy apps. I did a quick Google search on “Developing Adobe Lightroom 4 plugins” and one of the first hits took me to a document describing Adobe Ligthroom 4 SDK (software development kit). After skimming through the documentation for a few minutes, I learned that Adobe Lightroom stores its data in an SQLite database – a standard approach to local data storage for most desktop and mobile applications. There are several free applications that allow you to look at SQLite data. The ones that I use the most are SQLite Manager plugin for Mozilla Firefox browser and Run!. SQLite Manager requires that you have Firefox installed on your computer and Run! requires Adobe Air. All the examples in this post use SQLite Manager, but Run! has a very similar user interface. Download and install Firefox and SQLite Manager plugin. If you are using Microsoft Windows, start SQLite plugin by clicking the orange “Firefox” tab in the left top corner of your browser window, then selecting “Web Developer” and “SQLite Manager”. On a Mac, go to Firefox → Tools → SQLite Manager.

Hacking Lightroom | Dmitriy Babichenko Photography
When SQLite manager launches, click the “Open” icon at the top menu bar.
Hacking Lightroom | Dmitriy Babichenko Photography
Browse to the location of your Adobe Lightroom catalog (a file with “.lrcat” extension). Make sure to select “All Files” in format choices.
Hacking Lightroom | Dmitriy Babichenko Photography
Double-click on Lightroom catalog that you’d like to load.
Hacking Lightroom | Dmitriy Babichenko Photography
Once Lightroom catalog loads, click on “Execute SQL” tab. On the left-hand side of window you’ll see a list of tables that contain all of Lightroom’s data. On the right you will see a text field where you can type in your queries. Database queries are written in SQL (Structured Query Language) – it’s fairly standardized across all database vendors, so you only have to learn it once. Copy and paste the following query into the text field and click “Run SQL”. SELECT focalLength, COUNT(focalLength) FROM AgHarvestedExifMetadata GROUP BY focalLength ORDER BY COUNT(focalLength) DESC You’ll see a list of all lens focal lengths that you’ve used in your photographs.
Hacking Lightroom | Dmitriy Babichenko Photography
Now type in SELECT b.value, COUNT(b.value) FROM AgHarvestedExifMetadata a JOIN AgInternedExifLens b ON a.lensRef = b.id_local GROUP BY b.value ORDER BY COUNT(b.value) DESC and click “Run SQL” Now you should see a list of all lenses used to take your photographs and you’ll be able to easily tell which lenses you use most often. When I have a bit of downtime this winter, I would like to write a cross-platform application that will pull statistics from Adobe Lightroom and Apple Aperture. I am planning on releasing this app under Creative Commons license, which means it will be free to use and modify. If you are a photographer and would be interested in being able to compile statistics from Lightroom or Aperture, shoot me an email or post on my Facebook page and let me know what statistics would be most useful to you. If you are interested in learning more about databases, check out http://www.sqlcourse.com/index.html and http://beginner-sql-tutorial.com/sql.htm for SQL tutorials. If you want to learn a programming language, Python is a very useful language to know. It has reasonably low learning curve if you are trying to do simple things and it has many built-in libraries for dealing with text and image files. If you actually want to know the engineering details of how digital cameras work, I highly recommend the following books: