Hacking the Adobe Lightroom Catalog

I had a problem the other day.. I wanted to moved all my Photographs from my workstation to my server, and access them over the network. The main issue is that I keep all of them organized in Adobe Lightroom catalogs (v. 1.4.1), and it does not allow the catalogs to be in network drives. That’s not a big deal, as I prefer to have the catalogs local (just a couple of GBs) and the bulk of the files in the server (around 130 GBs).

Since I moved the files using a USB drive, the Catalog had all files referencing the location of the drive. So I first tried to “find missing folders” option to relocate them. Unfortunately it asks me to apply the changes to every folder manually, and that was too much work. Besides, it was renaming the folder entry to “/new-path/date”, which looks very ugly and renders the folder browser in the library useless.

So I started to look for another option. Fortunately, the Lightroom catalog file (.lrcat) is a regular SQLite database file. So I took my database browser and after a few minutes inspection, I came up with the following two commands:

UPDATE Adobe_imageFiles
SET absolutePath = replace(absolutePath,"old-path","new-path");

UPDATE AgFolderTagInfo
SET absolutePath = replace(absolutePath,"old-path","new-path");

the catalog keeps the absolutePath of every image in a table, and the first update replaces the old path with the new one. Similarly, it keeps every folder path in another table, and the second query takes care of it.

For best results backup before doing any changes, and tell Ligthroom to optimize the catalog after reopening.

One can do some other stuff too, like remove “missing file” tags, or correct filenames for images. For example, let’s suppose we want to check the entries of missing files:

SELECT * FROM Adobe_imageFiles where markedMissing is not null;

then we can clear it with:

UPDATE Adobe_imageFiles SET markedMissing=NULL where markedMissing is not NULL;

in addition, Lightroom will add a tag to the image, which is the kindName ‘AgMissingFileTagKind’. You can find the tag at AgLibraryTag and applied to the image at the AgLibraryTagImage:

SELECT * FROM AgLibraryTag where kindName = 'AgMissingFileTagKind';
SELECT * FROM AgLibraryTagImage where tagKind = 'AgMissingFileTagKind';

The last one links the tag to the imageID. You only need to remove the corresponding entries in the table to remove the “missing” flag from a file, as well as to remove the ‘markedMissing’ flag.

It is similar for the Folders, using the AgFolderTagInfo table and the same AgLibraryTag and AgLibraryTagImage, but of kind ‘AgMissingFolderTagKind’.

3 thoughts on “Hacking the Adobe Lightroom Catalog

  1. Question… have you spent any time researching using same technique on LR v3 database? After browsing one interested in using this approach to quickly update folder paths, looks like some of the table names have changed. I don’t see a table called Adobe_imageFiles table though did find ones like AGLibraryRootFolder, AGLibraryFolder and AGLibraryFile. Appears I can accomplish what’s needed by editing pathFromRoot in AGLibraryFolder but don’t see a table with name like %Tag%. There are some with Keyword in name but none with an apparent path column.

    Thanks,
    Nate

  2. Hi Nate,

    I have not tried in LR3. If I remember correctly, the structure in LR3 changed to accommodate multiple sources. Also, I have experienced some issues when migrating a modified catalog to LR3. So I think it does not work well in newer versions. Fortunately, the “find missing files” works much better, and they do better support network drives, making these manipulations not needed anymore. Hope it helps!

    Guillermo

  3. I found your site and address on internet. Currently I am exploring the Lightroom database (v6) but cannot find much documentation about it. With some DBtools it is easy to see and study all tables, but it is unclear how the are related onto each other.
    Can you help in that area? Do you have an database model showing HOW tables are related, by what fields and their relationship (many to one, one to many, etc)? Any version documentation can be useful to me; hope you can help directly or point me to some resource.
    Many thanks in advance, sincerely,

    Adri Hogewoning
    The Netherlands

Leave a Reply

Your email address will not be published. Required fields are marked *