Some versions ago, DxO added the ability to tag images with keywords. Despite some changes since the initial introduction, there are some missing tools. DxO is a small company with limited resources and I suspect that they aren’t going to prioritize further enhancements of the keywording system.
I’ve developed tools to fill the gaps. Unfortunately, I wrote my code in PHP, and it’s not easily shareable.
As a spin-off of my work, I’ve developed a few SQL scripts that might be useful to some of you. These are a bit easier to share. I’ll offer these here. If you don’t need them, ignore them.
UPDATE: Clarifications (based on feedback received so far):
- There’s some potential that the tools won’t work with a Mac DB—I’m still trying to find out. If you are on a Mac and you try them and they don’t work, let me know.
- The tools won’t affect your database in any way. They only retrieve information.
- The tools only report keyword information from Photolab’s database; they don’t get any information directly from any of your images or DOP files.
- If you use some other software to manage your image’s keywords, these tools will be of little interest to you.
The process for running these tools is:
- Download and install DB Browser for SQLite (DB4S), This is available for both Windows and Macs from Downloads - DB Browser for SQLite or—use any other equivalent software.
- Start DB4S and open the Photolab database in read-only mode.
- Go to the Execute SQL tab.
- Copy the code I give you into this window and execute it (right-pointing triangle in the tool bar).
- Copy the resulting table, into a spreadsheet or a text file.
Keep in mind that the SQL I am giving you cannot maliciously affect your database since you opened the database in read-only mode (right?).
Get the Keyword Structure
Photolab doesn’t have any method of importing/exporting the keyword structure. The tools below only export your existing structure. I’ll give you three variants.
Version 1: Get the basic structure only
/*
* Create a table of all keywords. Every keyword is written using a
* full path. The results are in alphabetical order.
*/
WITH RECURSIVE
/*
* Create a table that matches keyword IDs to the full path of the
* keyword it is associated with.
*/
fullKeywordPath AS (
SELECT Id, Value, ParentId, Value AS path
FROM Keywords
WHERE ParentId IS NULL
UNION ALL
SELECT Keywords.Id, Keywords.Value, Keywords.ParentId, fullKeywordPath.path || '|' || Keywords.Value
FROM Keywords, fullKeywordPath
WHERE Keywords.ParentId = fullKeywordPath.Id
)
SELECT path
FROM fullKeywordPath
ORDER BY path
The output will look something like this:
Contents|Wildlife|Birds|Australasian Robins
Contents|Wildlife|Birds|Australasian Robins|South Island Robin
Contents|Wildlife|Birds|Australasian Robins|Tomtit
Contents|Wildlife|Birds|Australasian Warblers
Contents|Wildlife|Birds|Australasian Warblers|Grey Warbler
Each keyword will be identified by its full path name, with “|” separating the paths. You could change this character to something like " > " if you wanted.
Version 2: Get the basic structure and counts
This version just adds an image count to each keyword. The count should match what you see when you view keywords in Photolab. It runs a lot slower than the version above.
/*
* Create a table of all keywords. Every keyword is written using a
* full path. The results are in alphabetical order. Each path is
* followed by a count of the number of items associated with that
* keyword. Getting the count slows the query a bit.
*/
WITH RECURSIVE
/*
* Create a table that matches keyword IDs to the full path of the
* keyword it is associated with.
*/
fullKeywordPath AS (
SELECT Id, Value, ParentId, Value AS path
FROM Keywords
WHERE ParentId IS NULL
UNION ALL
SELECT Keywords.Id, Keywords.Value, Keywords.ParentId, fullKeywordPath.path || '|' || Keywords.Value
FROM Keywords, fullKeywordPath
WHERE Keywords.ParentId = fullKeywordPath.Id
)
SELECT
path,
( SELECT COUNT(*) FROM ItemsKeywords WHERE KeywordId = fullKeywordPath.Id) AS count
FROM fullKeywordPath
ORDER BY path
The output is now:
Contents|Wildlife|Birds|Australasian Robins 52
Contents|Wildlife|Birds|Australasian Robins|South Island Robin 33
Contents|Wildlife|Birds|Australasian Robins|Tomtit 19
Contents|Wildlife|Birds|Australasian Warblers 11
Contents|Wildlife|Birds|Australasian Warblers|Grey Warbler 11
Version 3: Get the basic structure in Adobe Bridge format
Adobe Bridge can import keyword structures. They use tabs to indicate the level of each keyword.
/*
* Create a table of all keywords. If each row of the table is
* converted into a line in a file, the file can be imported into
* Adobe Bridge to create the same structure.
*/
WITH RECURSIVE
/*
* Create a table that matches keyword IDs to the full path of the
* keyword it is associated with. Create a prefix that consists of a
* tab for each level of depth of a keyword, with the top level
* being level 0.
*/
fullKeywordPath AS (
SELECT Id, Value, ParentId, Value AS path, "" AS prefix
FROM Keywords
WHERE ParentId IS NULL
UNION ALL
SELECT
Keywords.Id,
Keywords.Value,
Keywords.ParentId,
fullKeywordPath.path || '|' || Keywords.Value,
prefix || char(9) AS prefix
FROM Keywords, fullKeywordPath
WHERE Keywords.ParentId = fullKeywordPath.Id
)
SELECT prefix || Value AS name
FROM fullKeywordPath
ORDER BY path
The output is something like:
Contents
Wildlife
Birds
Australasian Robins
South Island Robin
Tomtit
Australasian Warblers
Grey Warbler
Find Hierarchy Problems
When you tag an image with a keyword, you can set up a preference that ensures that all parent keywords are also tagged. Drag-and-drop changes and other things (an accidental cursor click) can screw up this scheme.
The code below will output any image name/keyword path pairs that should be enabled to maintain a complete hierarchy path, but aren’t. It also lists a UUID.
The UUID can distinguish between an image and a virtual copy. You might have a problem in some and not others. It’s tough to match a UUID to a specific image/virtual copy; if the tool reports problems with an image name/keyword pair and the image has one or more virtual copies, then check them all.
This script can be very slow if you have a lot of tagged images. I just ran it and it took 6 seconds to display any results. If it says “0 rows returned”, then there are no problems.
/*
* Get a table of each item/keyword pair that should be in
* ItemsKeywords in order to have complete hierarchies but is not. The
* information is returned as filename and keyword path.
*
* The result rows also include a UUID. Each source file has a main
* image and one or more virtual copies. Each is uniquely identified
* by its UUID. If the same filename/keyword path appears more than
* once, then the UUIDs will be different. This indicates there is a
* problem in more than one version of the image. If a file/keyword
* path appears only once, but there are virtual copies of the image,
* then only one of the versions has a problem, but you will have to
* check them all to see which has the problem.
*
* This may take a few seconds to run. If everything is OK, it should
* return 0 rows.
*/
WITH RECURSIVE
/*
* Create a table that matches folder IDs to the full path of the
* folder it is associated with.
*/
fullFolderPath AS (
SELECT Id, Name, ParentFolderId, Name AS path FROM Folders
WHERE ParentFolderId IS NULL
UNION ALL
SELECT Folders.Id, Folders.Name, Folders.ParentFolderId, fullFolderPath.path || '/' || Folders.Name
FROM Folders, fullFolderPath
WHERE Folders.ParentFolderId = fullFolderPath.Id
),
/*
* Create a table that matches keyword IDs to the full path of the
* keyword it is associated with.
*/
fullKeywordPath AS (
SELECT Id, Value, ParentId, Value AS path
FROM Keywords
WHERE ParentId IS NULL
UNION ALL
SELECT Keywords.Id, Keywords.Value, Keywords.ParentId, fullKeywordPath.path || '|' || Keywords.Value
FROM Keywords, fullKeywordPath
WHERE Keywords.ParentId = fullKeywordPath.Id
),
potentials AS (
SELECT ItemId, KeywordId, ParentId FROM ItemsKeywords
JOIN Keywords ON Keywords.Id = ItemsKeywords.KeywordId
UNION
SELECT potentials.ItemId, Keywords.Id AS KeywordId, Keywords.ParentId FROM potentials
JOIN Keywords ON Keywords.Id = potentials.ParentId
)
SELECT
( SELECT path FROM fullFolderPath WHERE fullFolderPath.Id = Sources.FolderId ) || '/' || Sources.Name AS filename,
( SELECT path FROM fullKeywordPath WHERE fullKeywordPath.Id = Keywords.Id ) AS keywordpath,
Items.Uuid
FROM potentials
JOIN Items ON Items.Id = potentials.ItemId
JOIN Keywords ON Keywords.Id = potentials.KeywordId
JOIN Sources ON Sources.Id = Items.SourceId
WHERE NOT EXISTS ( SELECT 1 FROM ItemsKeywords AS ik WHERE ik.ItemId = potentials.ItemId AND ik.KeywordId = potentials.KeywordId )
ORDER BY filename, keywordpath
The output looks like this:
D:/Users/sample/Pictures/Photos/2025/IMG_1294.CR3 Test|A 2E7224C3-0A39-463B-9F54-9451B3FB3F61
In this case, the image is tagged with Test and Test|A|B, but not Test|A.
