Some database querying tools that you might find useful

I have developed a few tools that extract information from the Photolab database. You may find some of these tools useful.

The tools are written in a database language called SQL. You don’t need to know SQL to use them. Just follow these steps:

  1. 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.
  2. Start DB4S and open the Photolab database in read-only mode .
  3. Go to the Execute SQL tab.
  4. Copy one of my SQL scripts into this tab and execute it (right-pointing triangle in the tool bar).
  5. Copy the resulting table, into a spreadsheet or a text file.

The tools only query the database—they don’t change it in any way (and they can’t since you opened the database in read-only mode).

I have Windows and Mac versions of the tools.

I am not following this thread, and will not see any comments posted here. DM me if you have questions, concerns, and suggestions related to SQL scripts.

The SQL scripts are in these ZIP files:

Windows SQL Tools.zip (3.5 KB)
Mac SQL Tools.zip (3.6 KB)

The tools are:

  • getKeywordPaths—displays the entire keyword structure.
  • getKeywordPathsWithCount—same as above, but also displays a count of the number of images associated with each keyword.
  • getAdobeKeywords—displays the entire keyword structure in a format that can be imported into Adobe Bridge.
  • getIncompleteHierarchies—displays a list of all images with missing parent keywords (e.g. the image is tagged with keyword A|B|C, but not A|B.
  • findImagesWithoutKeywords—displays a list of all images that have no keywords assigned.

Each script includes further documentation.

1 Like

I suppose that these SQLs work with a DB from PhotoLab for Windows.
The DB of PL (Mac) has a different structure. Maybe I’ll adapt the SQLs…
or DxO could add import and export functionality to PhotoLab :wink:

What are the differences?

It would be crazy to design totally different structures for the two OSs. My SQL doesn’t deal with every table or even field in a table, so there may be nothing to adapt.

The second sentence of my post states that I think this is unlikely. I gave up waiting for DxO to provide much help with keywords.

1 Like

One important difference for the Mac DB is that its schema is auto-generated using the CoreData designer, from an object-oriented model. This means that, if DxO were to change the O-O model, the corresponding RDBMS model would change and could cause problems.

Not forgetting that the keywords are stored, not only in the database but, also, in an XMP sidecar for RAW images and the DOP sidecar for all files. These could need synchronising, depending on your preferred metadata storage. Any hierarchical keywords are also stored in an inconsistent manner between the DOP and XMP files as well as the database.

This doesn’t tell me what the structural differences are. For all I know, the DxO people generate the schema on a Mac and then use that same schema on Windows.

DB4S can display the structure. It’s the first tab. A screenshot of that tab would go a long way toward answering my question.

I should have highlighted that the scripts are currently only tested for PL8 on Windows. They probably work on PL 7 as well. For many software tools, DB changes occur only when the major version number increments. These SQL scripts may or may not work on PL 9. However, the scripts don’t make any changes to the DB, so the worst that happens is that they don’t work.

True, but irrelevant. The tools I provided only operate on the database.

I should perhaps have added that the tools only operate on DxO’s database. If you use another tool for managing keywords (as you do), nothing I provided will be of any use to you.

I think I’ll edit the OP to reflect some of these points.

@freixas Thank you for providing the scripts they may well prove useful. But the schema for the Mac database, regardless of how it was/is generated, has a number of differences to the Windows schema, not least the naming convention!?

So it will be possible to create the Mac equivalent scripts @platypus but the current ones won’t work.

PL8 has a slight difference in schema from PL7 (with PL8 on Windows), the order of fields in ‘Folders’ was changed in PL8 and an additional index or two was added to one structure or another (sorry I have forgotten exactly which ones).

In the meantime, I have been working with the DOPs where SQL certainly isn’t going, to help, except maybe to compare results between outputs from the scripts and outputs from the DOPs, but I have managed this so far in PureBasic

2025/06/03_09:29:03.518 @ line      1   Sidecar = {
2025/06/03_09:29:03.518 @ line      2   Date = "2025-06-01T15:55:50.6298766Z",
2025/06/03_09:29:03.518 @ line      3   Software = "DxO PhotoLab 8.6",
2025/06/03_09:29:03.518 @ line      4   Source = {
2025/06/03_09:29:03.518 @ line      5   CafId = "C61004c",
2025/06/03_09:29:03.518 @ line      6   Items = {
2025/06/03_09:29:03.518 @ line      7   {
2025/06/03_09:29:03.518 ---------------------------------------------------------------------------
2025/06/03_09:29:03.518 @ line      8   Albums = "",                                           <===[
2025/06/03_09:29:03.529 @ line      9   CreationDate = "2025-05-29T09:41:02.0233595Z",
2025/06/03_09:29:03.529 @ line     13   Keywords = {
2025/06/03_09:29:03.529 Keyword = A
2025/06/03_09:29:03.529 Keyword = A|B
2025/06/03_09:29:03.529 Keyword = A|B|C
2025/06/03_09:29:03.529 Keyword = A|B|C|D
2025/06/03_09:29:03.529 @ line     39   Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.529 @ line     41   OutputItems = {
2025/06/03_09:29:03.529 @ line     45   Rating = 0,
2025/06/03_09:29:03.530 @ line    317   ColorLookupPath = "",
2025/06/03_09:29:03.531 @ line    504   Overrides = {
2025/06/03_09:29:03.531 @ line    616   Version = "19.5",
2025/06/03_09:29:03.531 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
2025/06/03_09:29:03.531 @ line    621   Uuid = "6A9404D6-3FD0-459E-B164-8DC08F223AD3",         <---]
2025/06/03_09:29:03.531 {Album_count = 1 Uuid_count = 1}
2025/06/03_09:29:03.531 ---------------------------------------------------------------------------
2025/06/03_09:29:03.540 ---------------------------------------------------------------------------
2025/06/03_09:29:03.540 @ line    625   Albums = "",                                           <===[
2025/06/03_09:29:03.540 @ line    626   CreationDate = "2025-06-01T15:52:54.4250729Z",
2025/06/03_09:29:03.540 @ line    630   Keywords = {
2025/06/03_09:29:03.540 Keyword = A
2025/06/03_09:29:03.540 Keyword = A|B
2025/06/03_09:29:03.540 Keyword = A|B|C
2025/06/03_09:29:03.540 Keyword = A|B|C|D
2025/06/03_09:29:03.540 @ line    656   Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.540 @ line    658   OutputItems = {
2025/06/03_09:29:03.540 @ line    662   Rating = 0,
2025/06/03_09:29:03.541 @ line    934   ColorLookupPath = "",
2025/06/03_09:29:03.542 @ line   1121   Overrides = {
2025/06/03_09:29:03.542 @ line   1131   Version = "19.5",
2025/06/03_09:29:03.542 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
2025/06/03_09:29:03.551 @ line   1136   Uuid = "62FD850F-8EA1-4AAB-8DA4-817E1E316BE8",         <---]
2025/06/03_09:29:03.551 {Album_count = 2 Uuid_count = 2}
2025/06/03_09:29:03.551 ---------------------------------------------------------------------------
2025/06/03_09:29:03.551 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
2025/06/03_09:29:03.551 @ line   1141   Uuid = "A0944FE9-B476-4874-93EA-CDDF6AC1BDA0",         <----
2025/06/03_09:29:03.551 {Album_count = 2 Uuid_count = 3}
2025/06/03_09:29:03.551 ---------------------------------------------------------------------------
2025/06/03_09:29:03.551 @ line   1144   Version = "19.0",
2025/06/03_09:29:03.551 ---------------------------------------------------------------------------
2025/06/03_09:29:03.551  
2025/06/03_09:29:03.551 Creation Date list:- 
2025/06/03_09:29:03.551        Album     #               Creation Date
2025/06/03_09:29:03.551 @ line     9     1  2025-05-29T09:41:02.0233595Z  Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562 @ line   626     2  2025-06-01T15:52:54.4250729Z  Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562  
2025/06/03_09:29:03.562 Sorted Creation Date List:- 
2025/06/03_09:29:03.562        Album            #        Creation Date
2025/06/03_09:29:03.562 @ line     9     1      1  2025-05-29T09:41:02.0233595Z  Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562 @ line   626     2      2  2025-06-01T15:52:54.4250729Z  Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562  
2025/06/03_09:29:03.562 Modification Date list:- 
2025/06/03_09:29:03.562 @ line     0     0    Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562 @ line     0     0    Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562  
2025/06/03_09:29:03.562 Sorted Modification Date List:- 
2025/06/03_09:29:03.562 @ line     0     0    Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562 @ line     0     0    Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.573  
2025/06/03_09:29:03.573 ==================================================================================

The 2 read passes are necessary only with DOPs from the Mac where it appears that the actual presentation of ‘Albums’ (copies) in the DOPs is not in order, which it is for Windows DOPs, i.e. with Windows the order of presentation in the DOP is always [M]aster, VC[1] etc. (according to my tests)

To achieve correct ordering with Mac DOPs the ‘Albums’ need to be sorted on ‘CreationDate’, hence the first pass to obtain the ‘CreationDate’ followed by a sort and that provides the data to mark or present the ‘Albums’ in the “correct” order.

The analysis program from which the screen grab was taken is just a vehicle for experimentation but the code from it is being added to programs intended for a more serious purpose, e.g. locating images that have not been assigned a keyword by the user in DxPL, which is impossible with the current ‘Search’ facility in DxPL @RAGING_FURY

The times shown are from the one of the two log files created (a User Log and a Debug Log) and it outputs to the Debug screen as well and it takes 33 milliseconds to process a single pass (all that is necessary for Windows),

So processing a directory of 1,000 images would take 33 seconds (on my 5900X but taking a single core)) and on Windows no second pass is necessary.

If the ‘Pick’/‘Reject’ flag is not included in the selection criteria only the first 50 lines need to be processed so it will take even less time!?

To do the same in SQL I would need code added to select a directory (‘Folders’), locate an image (‘Sources’ and thence to ‘Items’ ) and check for the ‘Items’ in ‘ItemsKeywords’, if absent (not found) then there is a candidate for an un-keyworded image.

My program will also be able to find any combination of keywords (including no ‘Keyword’ at all), and with ‘Rating’ and/or ‘ColorLabels’ and even the ‘Pick’/‘Reject’ flag thrown in for good measure, if I can be bothered to provide an appropriate user interface(?).

The selected (located) images can then be passed back to DxPL as an External Selection (stored as a form of ‘Projects’ entry in the database.) which can easily be converted to a real ‘Project’ by the user, if necessary.

This is a nice initiativve, thankss. But some one at DXO needs to address this, they maybe a small company but it is not as if they are giving away their software for free. (not to mention the sneaky money grab ‘you need to buy filmpack to get luminocity masking scam’)

Not too impressed, hope some effort goes into the next version - we will not know until then as any development efforts are treated as state secrets.

All very well developing the headline stuff, but some focus on basic functionality and keeping users happy, would go a long way.

Of course I know it is unlikely anyone from DXO reads this, nevermind acting upon it.

@They may well read it but these days don’t respond and I am afraid that finishing what they started does not seem to figure in their ‘raison d’être’.

You may “rant and rage”, I have since I first started testing with PL5, but it is like “banging your head against a brick wall” it only get better when you stop!

So I have mostly given up the forum, but have turned my attention to coding (having been coding since I buttoned in my first program into the console of an ICT 1301 in 1965, at the start of my degree course) to get some intellectual stimulus.

I started with Python (somewhat different from COBOL, which I used for 36 years) and had an online tutorial with my Grandson on Sunday as he prepares for his mock GSCEs but then turned to PureBasic, which allows me to create .exes.

So I can release anything I write but realised I have a potential trust issue, i.e. how do I prove that I am not going to cause damage, deliberately or even accidentally?!

The PureBasis compiler is released with some limitations to any one who wants access with no time limit but a code limit of 800 lines. So if I can stay within those limits I could release the source code and the .exe and any potential users could have both and choose which they want (trust) to use!?

I was hoping @RAGING_FURY that you would test my ‘No keyword’ version of the program when it is finished. That might be reasonably soon given that we are due some wet weather, which will make gardening somewhat difficult/unpleasant!

Regards

Bryan

sure, happy to try it whenever you get round to it. Am available off & on . You can DM me if I don’t respond.
Garden look inviting !

@RAGING_FURY Thank you I will use DM once I have a releasable version and keep the code between us for the time being and we can test not only the program but what it could/should contain in the way of features and how the process of interacting between the developer (me) and the client (you) in this case can/should be conducted.

whatever works for you.

Re pittosporum, mine is getting leggy inplaces, need to do a serious prune too. Still, i like the natural look!

Thanks to @platypus, I’ve been able to look at the Mac database. My SQL will work if I rename the tables and fields. When I get around to it, I’ll try to post Mac-compatible versions if someone doesn’t beat me to it.

With regards to my SQL, the order of the fields and the presence or absence of an index doesn’t matter.

The SQL shouldn’t be hard to write (if you know SQL). It will be order of magnitudes faster than getting the info from DOP files. You could probably do a database of 100,000 images in 30 seconds or less.

If it is of any interest, I have written a full blown macOS app to manage Keywords, Star Ratings, Finder Tags, Descriptions, etc.

You can build up a library of keywords, including hierarchical ones as well as a library of Finder Tags.

Then you can add keywords to images and search for them. Once you have a search result, you can save it as a Smart Folder, just like you can in Finder. In fact, once you have used my app to add metadata, you can just as well use Spotlight to find files that match. Metadata can even be stored (safely) in RAW files, or in XMP sidecars if you prefer.

It has been designed from the ground up to work hand in hand with PL and you can open files from it, directly in PL or any other app.

The image browser shows a flattened folder hierarchy, based on the selected folder. In fact, you can browse the entire disk.

Drop me a DM if you are interested in beta testing it.

I went ahead and wrote the SQL for this (for Windows only right now, sorry). One problem is that, if you delete or rename files or folders from outside Photolab AND
you didn’t assign keywords to the renamed images before the rename, then the original filenames for these images will show up in the results.

Performance is good. For ~71,000 source files, the query took about 12 seconds.

If you just want to check a specific folder, add this line between the WHERE line and the ORDER BY line:

AND filename LIKE 'D:/Users/.../%'

where the “…” part is filled in with the full path to the file. Note that even though Windows uses “\” is a path separator, you have to use “/” here. Also, to match the exact folder name, you need to end with “/%”.

/*
 * Get a table of image files which have no keywords assigned.
 *
 * The result rows also include a UUID. Virtual copies of an image
 * will have the same filename but different UUIDs.
 *
 * 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
  )

SELECT
  ( SELECT path FROM fullFolderPath WHERE fullFolderPath.Id = Sources.FolderId ) || '/' || Sources.Name AS filename,
  Items.Uuid

FROM Items
  JOIN Sources ON Sources.Id = Items.SourceId
WHERE NOT EXISTS ( SELECT 1 FROM ItemsKeywords AS ik WHERE ik.ItemId = Items.Id )
ORDER BY filename

As I’m going out of town for a few days, if anyone wants to try running these for a Mac, here are the translations:

  • Globally, Id → Z_PK
  • Globally, Name → ZNAME
  • Sources → ZDOPSOURCE
  • Items → ZDOINPUTITEM
  • Items.Source → ZSOURCE
  • Items.Uuid → ZUUID
  • Keywords → ZDOPKEYWORD
  • Keywords.Value → ZTITLE
  • Keywords.ParentId → ZPARENT
  • Folders → ZDOPFOLDER
  • Folders.Name → ZNAME
  • Folders.ParentFolderId → ZPARENT
  • ItemsKeywords → Z_8KEYWORDS
  • ItemsKeywords.ItemId → Z_8ITEMS
  • ItemsKeywordsKeywordId → Z_10KEYWORDS

When I say “globally”, I don’t mean you can replace any occurrence of “Id” with “Z_PK”. I mean you can replace the exact word “Id” with “Z_PK”. The difference is that the former will change “KeywordId” to “KeywordZ_PK” and the latter won’t.

Let me know how it works.

@freixas
I wrote this earlier but didn’t post it

“If you know SQL” is the key part of your statement, I started using databases while SEQUEL was still a “twinkle in someone’s eye” and managed to survive without learning it until know, hence my unsubtle hint to someone who knows SQL, i.e. you!

PureBasic can actually submit SQL commands to an SQLite database (with a maximum of two databases open at any one time) and I can actually submit SQL commands individual requests

@freixas Thank you for your efforts, I will get to your script either later today or first thing tomorrow.

@Joanna I think that this is the first time I have seen you offering your application in the forum (I apologise if that is not the case).

Sadly it is Mac specific and uses features not available on the PC.

The DOP analyser program I am working on will work on both the PC and the Mac. PureBasic has compilers for Windows, MacOS, Linux and the Pi and all versions are included in the one licence fee as is the rights to sell, giveaway the programs compiled with it…

But, although I have considered buying an old, second-hand Mac, currently I only have personal access to PCs.

However, my comments about being able to use PureBasic with a trial licence on programs of less than 800 lines ,covers all the languages I listed above, with certain limitations on the facilities available.

If only the “threat” of publishing “adjunct” software would spur DxO to finish at least some of what they started it would be a win-win (or mac-mac) situation!

2 Likes

@freixas Thank you for your SQL scripts, I have tried all of them and I also changed the last to use “EXISTS” in place of “NOT EXISTS” when I ran the script on a test database…

The database I used has the vital statistics which I output from a PureBasic program I wrote, that uses SQL commands to interrogate the PhotoLab SQLite database to obtain the counts etc…

As part of the test I Loaded 1,000 test images, a mixture of RAW (RW2) and JPG images and set the first 500 of the 1,000 with the same two keywords and got this as part of the output from your original “NOT EXISTS” script

i.e starting from “00501.ORF” the images have been flagged as not keyworded.

My thoughts about keywording.
I indexed old picture folders using DxO Photolab. That added a lot of keywords without hierarchy. I don’t know if hierarchy exists in old picture. Or DxO can read it.
So I have a lot of keywords to organize.
First problem. I can only move one keyword at time with mouse. There is no option to select many keywords at time.
Second problem. I can’t move keyword if exists the same keyword in destination place in hierarchy. There is no option to merge the same keywords and mark pictures with it.
I can open DxO database and change manually ParentId in Keywords table. But I have the same keyword many times in hierarchy then in DxO Photolab.

You said that you tried them all, but you didn’t say if they worked. I think the implication is that they all worked, but it’s unclear.

Do you have a thread for your PureBasic tool? If not, could you start one and ensure that all discussions about your tool take place there and not here? Thanks! I realize my thread title was generic, and I’ve edited it. I’d like to restrict this thread to using SQL scripts through DB4S (or equivalent).

@BHAYT, @RAGING_FURY The gardening talk might be better done through direct messaging.

First, congratulations for finally reaching the point that you are willing to offer this tool for others! Do you have a thread for this? If not, I have the same request for you as for Brian: could you delete your announcement in this thread, and post it elsewhere? I apologize for making my original thread title a little too general. Your work certainly deserves a thread of its own.

Your comments about the Mac database are completely relevant—no issues there. Thanks for the feedback.

I actually have tools to deal with this, but they directly modify the PL database, so I’m not releasing them. Nor are they simple SQL scripts.

I’m not sure what you’re saying here, but it sounds dangerous. What you’d like to do probably requires a mix of SQL and coding (an SQL super-guru might be able to do it all in one SQL script, but it’s beyond my abilities).