How do I find a Lightroom catalog, from an image

Question

Nope. This question isn't so much about finding an image within a Lightroom catalog, it's about finding the catalog an image belongs to.

I have an image. I know what the master image is called, and more crucially I have the file location. This is on a remote drive which over several laptops has retained the same drive letter, and in the same location.

I know the image was edited, so I know it sits in one somewhere (I remember changing the BG from red to black using an adjustment brush).

Now the issue is I have 10s of 1000s of images stored over multiple catalogs, from the days when my laptop and LR would just get upset after a certain amount of images belonged in it.. and this was from the days where my catalogs were usefully name/numbered/dated.

I would like to find the catalog that holds this image, in order to re-export it. I know this is Lightroom based question, but I expect I may need to do a search across physical catalog files in order to find it. So for clarity, my OS is Windows, (however I'm able to run shell commands).

Answer 1

This is perhaps not what you want to hear, but you should consider either using one (or very few) catalogs, or organizing the physical files so they are contextually associated with the catalogs. I guess it depends on scale, but catalogs can support 100,000 images easily (I have yet to hear of someone who hit a limit, my own is about 80,000). A key purpose of a catalog is to let you find things - using a lot of catalogs somewhat defeats the purpose.

Now that said, you did not ask "talk me out of doing it" so here's a pointer in a possible direction: the catalogs are just sqlite databases. If you are a bit tech savy, you can use any of numerous tools to query the databases (e.g. in a script to run against each one). To check if a catalog contains a specific file (name in the last line below):

select *
from 
(
  select rf.absolutePath || lfo.pathFromRoot || lf.baseName || '.' ||  lf.extension as imagePath 
  from Adobe_images ai
  inner join AgLibraryFile lf on lf.id_local = ai.rootFile
  inner join AgLibraryFolder lfo on lfo.id_local = lf.folder
  inner join AgLibraryRootFolder rf on rf.id_local = lfo.rootFolder
  where ai.masterimage is null 
) x
where x.imagePath = 'C:/somefolderpath/LR-20080704_4881.NEF'

The part in the middle returns a list of all files with their on-disk path, the outer part looks for a particular one among them. Run just the middle to list all files. Variations (such as looking only at lf.baseName) can be used if your file names are unique and you want to ignore the paths.

Sticking this inside of powershell or some other scripting language would depend on your preferred tools, skills and how frequently you want to do it. To do it manually against one catalog at a time I recommend using Firefox and the SQLite manager add-on, which is a very friendly way to run SQL commands against any SQLitem database including lightroom (just be aware that the default file type isn't .lrcat and you have to enter that manually).

Also be aware if you execute write commands (update, delete, etc.) you can really screw up your database - don't do that. On the other hand, exploring the catalog (without changing it) is a grand way to understand Lightroom better.

Also note that you may not be able to open Lightroom while the catalog is open in any other tool (and vice versa).

Answer 2

Based on @Linwood's excellent answer, I wrote this small Ruby script to recursively scan every lightroom catalog in Pictures folder and stop at the first catalog containing a filename containing 'Hanna'. The search could be optimized but it did the job just fine in a few minutes:

require 'sqlite3'

DESIRED_NAME = 'Hanna'.downcase
db_filenames = Dir[File.join(ENV['HOME'],'Pictures', '**', "*.lrcat")]

select_absolute_filenames = <<-SQL
select *
from 
(
  select rf.absolutePath || lfo.pathFromRoot || lf.baseName || '.' ||  lf.extension as imagePath 
  from Adobe_images ai
  inner join AgLibraryFile lf on lf.id_local = ai.rootFile
  inner join AgLibraryFolder lfo on lfo.id_local = lf.folder
  inner join AgLibraryRootFolder rf on rf.id_local = lfo.rootFolder
  where ai.masterimage is null 
)
SQL

db_filenames.each do |db_filename|
  puts "Checking #{db_filename}..."
  SQLite3::Database.new(db_filename) do |db|
    found_image = db.execute(select_absolute_filenames).map{|row| row.first}.find do |image_filename|
      image_filename.downcase.include? DESIRED_NAME
    end
    if found_image
      puts "  Found : #{found_image} in #{db_filename.inspect}!"
      exit
    end
  end
end