Some database querying tools that you might find useful

[Joanna (Joanna Carter)]

Please don’t take what I am about to say as being in any way harsh, but my main concern with all this SQL “stuff” is that it doesn’t interact directly in any way with either PhotoLab or any other app, without an in depth knowledge of BASIC, scripting, using the console and generally messing around manually with the file system.

I would like to ask a question - how do I use it to find images that match a keyword search term and then open those images in PhotoLab?

I referred to my app because, from the average photographer’s point of view, it allows them to simply enter keywords (including in hierarchies) in a lookup list and manage them visually using drag and drop, add those keywords to images (without the requirement to use the PL database), transfer those files to any other app and then use the keyword list to populate searches. You don’t even need to use PL. Once the search returns, it is a simple matter of double clicking on any of the included images to open them in PL.

But, whatever you do or don’t do, don’t use PL to modify metadata that is managed in an external app, even mine. That is one sure way to mess everything up.

As for the PL database, I don’t even use it and, in fact, regularly delete it after changing the structure of my file storage.

Why am I so insistent on avoiding using the database for metadata? Because instead of using SPOD (single point of definition) PL manages to conflate multiple definitions in…

• the database
• DOP files
• XMP files
• it even reads keywords directly from RAW files.

As opposed to the sensible way of having a SPOD in either the original file or an XMP sidecar.

[freixas]

@Joanna

my main concern with all this SQL “stuff” is that it doesn’t interact directly in any way with either PhotoLab or any other app

My thread is about extracting useful info from PL using SQL (since PL provides no formal hooks to its internal workings). The tools I have provided so far all deal with keywords.

If your keyword system is external to PL, then the tools are of no use to you. If you use PL’s database for keywording, then they are. Arguments about which approach is superior are irrelevant in this thread. If you’d like to argue about the merits of one approach vs. another, please start your own thread on the topic.

[Joanna (Joanna Carter)]

@freixas

My thread is about extracting useful info from PL using SQL

Might I ask for what purpose? How do you intend that folks make use of what they have extracted?

@freixas

The tools I have provided so far all deal with keywords.

But Mac users don’t need such tools since Finder, with its Spotlight search mechanism can find files that match multiple keywords all on its own…

Or, just press Cmd-I on a file to see what keywords it contains…

@freixas

If you use PL’s database for keywording, then they are

Unless you use XMP sidecar files for metadata. In which case, both PL and a majority of other tools can access them without any reference to the database, which, as I said before can cause confusion.

Finally, how would you move the metadata to keep it in sync when you move images?

[freixas]

@Joanna

Might I ask for what purpose? How do you intend that folks make use of what they have extracted?

Every script describes its purpose. People can use the information for whatever they want.

Nothing else in your post is relevant to this thread. You can DM me if you want to discuss these side issues with me.

[platypus (Canon & Mac)]

@freixas

Here are the Mac versions.

Thank you @freixas

I tested the sqls against a full import of the images that I have in Lightroom.
All sqls worked without errors.

Comparing the V1 export to Lightroom’s direct export, I found that DPL’s database is a few dozens of keywords short…due to files that DPL can’t or won’t read while indexing.

As of now, the sqls are informative and can serve to check indexing, e.g. in the case of exchanging DPL and LrC. Also, the lists could help to e.g flatten a hierarchical list of keywords (and vice versa), but after all, having a list does not link the keywords to the respective images. For that, we still need to fully import (or index) the respective photo archive and then, the keywords will be read, catalogued and, most importantly, linked to the respective images.

My current Macs index about 1000 images per minute and a direct DB-transfer would be much faster and comfortable … if only DxO (and Adobe) provided and maintained such services. I’d wonder if any such possibility were provided by whoever is bold enough for this (fools?) errand.

[freixas]

@platypus

I tested the sqls against a full import of the images that I have in Lightroom.
All sqls worked without errors.

Thanks for the reply.

@platypus

As of now, the sqls are informative

As long as you limit operations to read-only, informative is the best you get. The output from the SQL can be further manipulated for various uses, through spreadsheets or other methods, but it’s still just information—the value being that it is information that PL doesn’t provide.

@platypus

I’d wonder if any such possibility were provided by whoever is bold enough for this (fools?) errand.

It’s possible and it’s the kind of thing I could do if I had copies of both programs and access to whatever database they used. But giving away tools that write into databases not directly under one’s control is a risky proposition.

[platypus (Canon & Mac)]

@Stenis

has very little to do with muscles at all. It is all about brains

…and the notion of “what our customers need” and the respective tendency to ignore the customers, “they keep paying anyways” …

Take people like Dave Coffin, Phil Harvey and others. Their devotion has moved more ground than many a Dollar.

[platypus (Canon & Mac)]

@freixas

write into databases not directly under one’s control is a risky proposition.

What’s the risk? Having to do something for the rest of your natural live while your users yell at you because the new release was due out 7 hours ago - you know what I mean. Fool’s errand. Bottomless vat, Sisyphus. Greetings from the hamster-wheel.

[freixas]

@platypus

What’s the risk?

I meant the risk to me. I could write code to transfer PL to Lr and back, but it’s risky since I don’t control either tool. Read-only SQL scripts aren’t going to hurt anything.

[Stenis (Sten-Åke Sändh (Sony, Win 11, PL 6, CO 16, PM Plus 6, XnView))]

No, Frexas is right about this. Running a relatively simple SELECT query like this won´t harm anything in the database. If I understood Frexas his point is just to expose how simple it is to extract these keyword data from this database even if they happen to be structured.

Would it be too much to humbly ask DXO for an appropriate export-interface for this SELECT-query Freixas just have helped them with. All the competition has a function like that so it is really basic in the XMP-tool world.

[Joanna (Joanna Carter)]

Can I just reiterate, an export mechanism already exists, in the form of XMP sidecar files. When it comes to metadata, there is absolutely no need to use the database.

Why should DxO spend time and effort reinventing that particular wheel?

[freixas]

@Stenis

No, Frexas is right about this. Running a relatively simple SELECT query like this won´t harm anything in the database. If I understood Freixas his point is just to expose how simple it is to extract these keyword data from this database even if they happen to be structured.

My point was to provide a tool that PL lacks. I wasn’t “exposing” anything…

For DxO, the feature is not quite as simple. It requires capturing the SQL in code, writing the GUI, developing regression tests, etc. Compare to a lot of stuff, it’s relatively simple, but it’s not one-SQL-script simple.

There is a “Feature Request” forum that is appropriate for suggesting features you think are missing from PL. This particular feature may already be in the requested pile; if so, vote for it.

@Joanna

Can I just reiterate, an export mechanism already exists, in the form of XMP sidecar files. When it comes to metadata, there is absolutely no need to use the database.

For performance reasons, I don’t keep the database and keywords in synch. I do have my personal tool to synch the files should I find it useful (PL is the master). You are welcome to choose a different way of working.

[freixas]

Note: Given the high amount of irrelevant content, I plan to delete this thread in a few hours. I will try to find alternative ways of providing SQL scripts to PL users who might find them useful.

[platypus (Canon & Mac)]

Yes, I understood and was agreeing in a somewhat unusual way.

And even if you’d be able to do it, effort would probably still be bigger than waiting for an indexing run to finish.

[Joanna (Joanna Carter)]

@Stenis

Making XMP-files or embedded XMP is NOT a substitute for an Import-Export function. You might experience that the day you want to migrate otherwise the risk is high you will never understand what we are discussiong here (Joanna)

Unlike Adobe’s offerings and other software, PhotoLab doesn’t make use of a “catalogue”. Instead, it uses DOP files to transfer editing steps and XMP files to transfer metadata.

Yes, PhotoLab does have a database, but it is a real mongrel with both editing and metadata in the one place - but also duplicated, but not necessarily synchronised in separate DOP and XMP files.

You might be able to “extract” metadata from the PL database but you can’t easily import that metadata into other apps because they might not use the same tag names. e.g. DOP files and the PL database uses keywords only, but XMP compliant apps use a combination of dc:subject and lr:hierarchicalSubject tags. Which is what gets recorded in XMP files but not in the PL database.

[BHAYT (Bryan [WIN10, PL9, VP5, FP7, G9, 5060Ti(16GB), 3060(12GB)])]

@freixas

Note: Given the high amount of irrelevant content, I plan to delete this thread in a few hours. I will try to find alternative ways of providing SQL scripts to PL users who might find them useful.

@freixas You should be aware by now of the way that the Forum works.

Unfortunately, as the author of the topic you will receive all the “traffic” that it generates, good, bad and indifferent, a fact of Forum “life”.

Yesterday (actually before your “hiatus”) I collected your scripts and saved them so that I could easily retrieve them when I wanted them, not necessarily to “protect” them

@freixas SQL Scripts.zip (5.3 KB)

I had also found a new “toy” in the shape of “SQLiteStudio” and ran the scripts through that program one after another and I could bore you with all the outputs but I have them if required and have included the outputs from the Script 4 test and the run history summary, the latter because it is a useful component of the program

My concern with script 4 is what the output is actually telling me and why!?

The database being used is just one against which I have run numerous recent tests, i.e. I have used the “_Downloads” directory and picked up images I have downloaded from Forum posts and tested in the past, and Script 4 has detected an issue or two.

But first one comment you made in your discussion about Script 4 went something like this (taken from my file of your script)

 * 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.
 * 

The statement is certainly true but what we discovered when checking data with @platypus, from his Mac, was that DxPL was almost certainly sorting the “Album” entries in the Mac DOP to re-establish the order and using the ‘CreationDate’ entry of each ‘Albums’ in the DOP.

On the PC they are all arranged in the DOP in the expected order i.e. [M]aster, VC[1}m VC[2] etc, but that is not the case with Mac DOPs but the order can be determined by sorting the ‘Albums’ into ‘CreationDate’ order.

However, there is no such index in the database and no indicator in any structure about whether an item is a {M]aster or a VC, nor what the order actually is.

In fact, I believe it is on the ‘Id’ field, which is allocated when an image is discovered or a VC is created by the user and, as far as I can tell, the ‘Id’ is an ever increasing field with no re-use of “old” numbers, i.e. the order in which the entries were added to the database, first entry for the original image and then one for each VC as and when added.

but as you stated there is actually no way of knowing whether an image is the [M]aster or a VC simply by looking at the database entry.

@Joanna The XMP sidecar is not necessarily a useful “export” device because users are deliberately stopping DxO from updating the XMP sidecar for fear of it “re-formatting” their carefully crafted hierarchical keyword layout.

That “re-formatting” of keywords into the chosen DxPL format has always been there with PhotoLab but was restricted to the Export files which users seemed happy to accept until PL5, when it was extended to “adjusting” the keyword in the image (JPGs etc. or XMP sidecar for RAWs).

However, as a result I turned my attention to the one sidecar PhotoLab users do tend to keep, namely the DOP.

As for the process of making use of the outputs from the scripts or from my DOP analysis program (the new version is still under development @RAGING_FURY) the answer lies in a bit of coding somewhere to either take the output list from the scripts and run the list through a program that submits it as a command string to DxPL or, as in the case of my DOP programs, read the DOP directly and submit the located files to DxPL in a command line.

There may be issue with the number that can be passed at any one time, i.e. in the past I have managed up to 350 images at a time but the program can pass any number as a group of 300 at a time again and again and …

@Joanna

Instead, it uses DOP files to transfer editing steps

@Joanna As I have stated above, the DOP contains both metadata (including the ‘Pick/Reject’ flag and edits, much to the chagrin of many users, you in particular because it well and truly breaks the SPOD rule.

The “risk” is that the (latest) XMP sidecar file may not have been “absorbed” into PhotoLab, automatically or manually, but my program could easily be enhanced to interrogate the XMP sidecar file just to be on the safe side.

Any PureBasic program creates an exe file on Windows and any user could take the symbol file and compile it using the free version of PureBasic providing it is less than 800 lines long.

I am prepared to supply both if I decide to “publish” anything!

I want to avoid using ExifTool if possible, simply because of the added level of complexity right now, and your product would be off limits because it (potentially) pushes the metadata into the RAW image etc.

However, the Mac is off-limits anyway, for me at least, because I don’t own a Mac computer

Weather is looking OK. so no coding till later today or tomorrow…

[freixas]

@BHAYT

But first one comment you made in your discussion about Script 4 went something like this (taken from my file of your script)

 * 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.
 * 

The statement is certainly true but what we discovered when checking data with @platypus, from his Mac, was that DxPL was almost certainly sorting the “Album” entries in the Mac DOP to re-establish the order and using the ‘CreationDate’ entry of each ‘Albums’ in the DOP.

I know how to match a UUID to the main image or one of its virtual copies. What I meant is that it is difficult to do so in the SQL script that I provided.

@BHAYT

and, as far as I can tell, the ‘Id’ is an ever increasing field with no re-use of “old” numbers,

Yes, the Id fields are auto-incrementing fields. They start at 1 and increment with each new record added.

[BHAYT (Bryan [WIN10, PL9, VP5, FP7, G9, 5060Ti(16GB), 3060(12GB)])]

@freixas

I know how to match a UUID to the main image or one of its virtual copies. What I meant is that it is difficult to do so in the SQL script that I provided.

@freixas That was the point I was trying to make about there being an absence of data that identified the “Copy Number” of a particular ‘Items’ entry, i.e. there is nothing to ask SQLite to find.

A custom program on the other hand could do that, because that is exactly what DxPL does, and the DOP has that data already to hand in the Windows DOP and available after a Sort in the Mac DOP.

Once again, thank you for the scripts and for confirming the auto-incrementing Id.

PS:- The view count is at 331 in spite of or because of the dialogues contained herein sadly we can’t tell which.

[platypus (Canon & Mac)]

@freixas

Note: Given the high amount of irrelevant content, I plan to delete this thread in a few hours. I will try to find alternative ways of providing SQL scripts to PL users who might find them useful.

@freixas , I’d not delete the thread, others carry way more noise and still contain interesting information. It simply takes more digging to get to it, but such is the character of unmoderated forums.

As for the scripts, they’d be easier to access if they were saved as text files, Copying text out of a forum post is not complicated, but it’s not comfortable.

For those interested:
Keyword SQLs MAC.zip (5.9 KB)

[BHAYT (Bryan [WIN10, PL9, VP5, FP7, G9, 5060Ti(16GB), 3060(12GB)])]

@platypus The title of the topic has changed as has the first post from @freixas

Some database querying tools that you might find useful which now contains the scripts for both the Mac and Windows but thank you for including a Mac one here.