Finding photos in PL9 with no keywords

DxO PhotoLab Tutorials, tips & tricks
1

I’m using PhotoLab 9 on a Win 11 PC.
I recently wanted to find my photos that didn’t have keywords, based on the contents of the database. This is what I did…

  1. Download and install Sqlite
  2. Move the .exe files info a folder of my choice.
  3. Copy Photolab 9 database (PhotoLab.db) into the same folder as in 2 above.
  4. Double-click on the sqlite3.exe which will open a new window.
  5. At the sqlite> prompt, type: .open ./Photolab.db
  6. At the sqlite> prompt, type: .schema
  7. That should list the database schema. If not, it hasn’t found the database.
  8. At the sqlite> prompt, type:
    Select Items.Name, Folders.Name
    from Items, Folders, Sources
    where Items.SourceId = Sources.Id and
    Sources.FolderId = Folders.Id
    and not exists
    (select * from ItemsKeywords where Items.Id = ItemsKeywords.ItemId)
    order by Items.Name;

PLEASE NOTE:
I hope it’s of some interest to PL9 users.
The folder listed is just the parent folder, not the full path name.
It appears to work for me on a PL9 database, but I can’t guarantee 100% accuracy.
I don’t use keyword hierarchies, so not tested against these.
It doesn’t consider keywords in .dop files or tags in .jpg images. So the database needs to be in sync with these.
I did a brief test against a PL8 database, but it didn’t appear to give correct results.
So if you have PL8, be prepared to modify it - I don’t have time to investigate.

Sample output…
IMG_5257.JPG|27 July 2013
IMG_5258.JPG|27 July 2013
IMG_5259.CR2|2014_06_12 Kettlewell
IMG_5260.CR2|2014_06_12 Kettlewell
IMG_5261.CR2|2014_06_12 Kettlewell
IMG_5262.CR2|2014_06_12 Kettlewell
IMG_5263.CR2|2014_06_12 Kettlewell

2

Update!!!
I’ve changed the code to recursively go through the Folders table to generate full path names. Not sure why the posting software incorrectly displays some of the comments by dropping the asterisk, and changes the following code to italics. Hey ho.

WITH RECURSIVE folder_hierarchy AS (
/* Base case: start with all Folders /
SELECT
Id,
ParentFolderId,
Name,
Name AS full_path
FROM Folders
UNION ALL
/ Recursive step: prepend parent folder name to child’s path /
SELECT
child.Id,
parent.ParentFolderId,
child.Name,
parent.Name || ‘/’ || child.full_path AS full_path
FROM folder_hierarchy AS child
JOIN folders AS parent
ON child.ParentFolderId = parent.Id
)
/ Now process the Items and Sources tables and JOIN to folder_hierarchy */
SELECT
i.Name,
–fh.full_path AS folder_path,
fh.full_path || ‘/’ || i.Name AS full_photo_path
FROM Items AS i, Sources AS s
JOIN folder_hierarchy AS fh
ON s.FolderId = fh.Id
WHERE fh.ParentFolderId IS NULL and i.SourceId = s.Id
and not exists
(select * from ItemsKeywords where i.Id = ItemsKeywords.ItemId)
ORDER BY full_photo_path;