Can I reorganize Lightroom keyword hierarchies outside Lightroom?
Asked 3/15/2011
2 views
2 answers
0
I have an older Lightroom catalog where some keywords are properly nested, for example animals > birds > eagles, but many others were added later as flat, standalone keywords such as falcons or parrots. Rebuilding the hierarchy by dragging keywords around in Lightroom is very slow with a large catalog.
Is there a practical way to clean up and reorganize Lightroom keyword hierarchies externally, then bring the changes back into Lightroom safely? If so, what should I be careful about?
Originally by Photography Stack Exchange contributor. Source · Licensed CC BY-SA 4.0
Photography Stack Exchange contributor
15y ago
2 Answers
8
Like Sebastien mentioned, the library file is SQLite. I dug around in my own catalog and found that AgLibraryKeyword table. There are three columns you care about: id_local, name, and parent. As you might expect, parent points to the id_local of the parent keyword. In my library the "root" keyword has id_local 73.
So, assuming you had something like this:
id_local | name | parent
-----------+---------+--------
12345 | animals | 73
12346 | birds | 73
12347 | eagles | 73
12348 | parrots | 73
12349 | falcons | 73
You could "fix" the hierarchy like so:
UPDATE AgLibraryKeyword
SET parent = (SELECT id_local FROM AgLibraryKeyword WHERE name = 'animals')
WHERE name = 'birds';
UPDATE AgLibraryKeyword
SET parent = (SELECT id_local FROM AgLibraryKeyword WHERE name = 'birds')
WHERE name IN ( 'eagles', 'parrots', 'falcons' );
This would end up as Animals > Birds > Eagles, Animals > Birds > Parrots, and so on. The table ends up looking like this:
id_local | name | parent
-----------+---------+--------
12345 | animals | 73
12346 | birds | 12345
12347 | eagles | 12346
12348 | parrots | 12346
12349 | falcons | 12346
It's not as manual as doing it by hand through the UI, but it's still going to take some work. Should be just as functional though.
I should note that while investigating this, I made similar changes a few times that didn't seem to take. I'd suggest saving your SQL to a file so you can re-run it if it doesn't seem to work the first time. And, of course, you should back up your library ahead of time :)
Originally by user4335. Source · Licensed CC BY-SA 4.0
user4335
15y ago
0
Generated from our catalog & community — verify before relying on it.
Yes, but with caution. Lightroom’s catalog is an SQLite database, and keyword hierarchy data is stored in the AgLibraryKeyword table. The parent field links a keyword to its parent, and newer versions also store hierarchy information in a genealogy field. That means simply changing one column may not be enough.
So in theory you can reorganize keywords externally by editing the catalog database, but it’s risky: if you update the hierarchy incorrectly or don’t keep both hierarchy-related fields consistent, you could damage the catalog. Always work on a backup copy, not your live catalog.
A safer alternative is to write keywords to XMP, use software that can edit hierarchical keywords in metadata, then re-read the metadata in Lightroom. One answer specifically suggested Daminion for this workflow.
In short: external cleanup is possible, but direct SQLite editing is for advanced users only and requires great care. For most people, an XMP-based workflow is the safer route.
Recommended products
UniqueBot
AI15y ago