Some database querying tools that you might find useful

DxO PhotoLab Tutorials, tips & tricks
52

Why is it hard to understand Freixas SQL and why some people that know SQL might find it necessary to use it since DXO as it is now have locked them in if they want to migrate for example. Both Lightroom and Capture One can export

I can see several cases:

  • First: As you might know there are organizations and companies using so called controlled vocabularies. They are impossible to use though with Photolab since there is no possibility to import one or export one. Just of that reason Photolab is not a serious Picture Library. I have myself once started using hierarchic keywords in PhotoMechanic just by importing one from a Lightroom-resource. The format was in TAB-separated text. This is the normal way to do this.

  • Second: Since I found PhotoMechanic like all RAW-converters or Photo-DAM (except iMatch (will explain that exception) totally hopeless in maintaining hierarchic keywords manually, the only rational way I found was to export them all from PM PLus in TAB-formatted text - editing them in Excel and then reimport them. So I guess that since it really is terrible to maintain hierarchic keywords rationally most organisations and companies using them don´t alter them if they don´t have to.

  • The need for exporting a vocabulary is that it might contain keyword structures that haven´t been used yet. So extracting them from the database records might not be sufficient right?

  • AI, yes! That is fantastically efficient when it comes to handling even hierarchic keywords BUT it can also create a total mess if you just let it run without any control. Then we comes to how to handle that because you just can´t let a tool like the Autotagger lose without a thought. That is why the developer Mario Westphal has felt it necessary to build a lot of tools and processes to harness it.

image
image2203×1283 167 KB

Here the Autotagger is configured:

Number of keywords are specified, formatted and handled both through predefined user interfaces and through three different AI-prompts that controls the AI-services output (Descriptions, Keywords and Landmarks.

This is how my Keyword prompt looks like:

(I have limited the maximum number of keywords above to eight.)

Prompt
[[-c-]]

Keywords never in plural form
Use simple English, common words, factual language.
Max one word in each keyword.
No geografic data or time info as keywords.
No words with only capital letters.

Write the specie of the animal, animal family name and scientific name in latin into Keywords.{File.MD.keywords}
If you cannot detect any animals, return.

image
image1132×925 38.1 KB

Here you can totally control the behavior of this keywording-system and how the AI generated keywords are handled compared to your active vocabulary.

As you understand these tools are pretty unique to other softwares you might have seen before because unlike software like Photolab it is built with a real “holistic” engineering where very little is falling besides or between.

image
image1040×1237 219 KB

This is the “Thesaurus” or Keyword-list if you prefer that word and as you can see I don´t use any hierarchic keywords at all since I have handle that already via the keyword prompt.

As you can see there is an interface to support migration: the options to Import and Export Thesaurus. With the import from database you can import both all the plain keywords used on the pictures in the database or hierarchic ones if people use those instead.

To migrate the XMP-metadata to iMatch is a non-problem as long as it is OK for you just to let iMatch use the keywords-data that is stored in the JPEG-files embedded XMP or in the RAW-files XMP-sidecars. In that case you just import the pictures into iMatch and chose the function “Import from database”. That will build a new Thesaurus in no time based on the keywords imported to the database.

If you are lucky you can use the same way to do it even with other softwares but there are issues when it comes to hierarchic keywords in some of them. You have to chose what works for your special case in the Preferences og Photolab.

image
image1370×565 26.7 KB

Maybe at least some people understand there are far better alternatives to use if you want to build yourselves a really good picture archive with very little effort and it isn´t even expensive! So the days when there was a need for writing SQL-queries to extract keywords from a Photolab-database or building your own keyword application are gone. It is just not worth it - unless you think it´s fun of some reason.

I´m fully convinced we will see AI-supported tools like the Autotagger in iMatch, even in RAW-converters with Photo-DAM solutions from other vendors than Photools in the future and if they are implemented they also have to take care of the keyword related problems that comes with it.

1 Like
53

Thanks for this detailed explanation, iMatch looks quite interesting. I never used keywords myself and I wonder if keywords are even necessary at all with AI. I think instead many applications will include a direct text search, it is already included in Apple Photos, you can just enter a text description in the search box and it will list all the pictures that match that description. On the iPhone that does not work that well yet, but I guess that is the future with better models that run locally on the phone.

I can see the use for keywording for professional applications (which I would hope Photolab falls into), where you want to be sure not to miss any images, but for consumer grade apps I believe they can just skip the keywording step altogether.

54

You are right about the limited need for keywords outside local environments like a personal archive or in smaller companies up to global enterprises or for that matter organisations of all sorts. BUT, they are absolutely essential when it comes to a rational and effecive way to handle a picture library. You just can’t do without them. It is fantastic what a difference they make in a tool like iMatch for fast searches without the need for typing at all - just selections - where the keywords are used as so called “cathegories”.

Doing something like that in Photolab would not be hard at all since almost all of it already is there in the interface.

So, I don’t really believe in something spectacular will happen with the PictureLibrary in Photolab BUT that doesn’t really matter because no other RAW-converter is a better and more seamless match with thirdparty DAM than Photolab and THAT is an indeniable strength, advantage and sellingpoint for Photolab. If you really want to scale up scale up and get a really efficient XMP-metadata workflow all the metadata maintenance tools are there in iMatch.

Nothing gives a better image quality today than Photolab and it is a seamless and rock-solid match for consuming and displaying iMatch XMP-data that have supported all but one of the 25 XMP-metadata fields/elements that I use right out default. Just update and maintain the metadata in iMatch or even in PhotoMechanic if you have your XMP-metadata life there and Photolab will instant display the changes by its excellent synchronization.

This really leaves the metadata maintenace environments both in Photolab PictureLibrary, Adobe Lighroom and Capture One is the historical dust from an ancient time.

There are a lot of people out there these days looking for alternatives to Lightroom and a ever more gready Adobe so I would love to see Photools and DXO join in a good and mutually benificial market partnership by offering attractive bundles with DXO products and iMatch. That could be a really European killer alternative to a technically tired Adobe and CameraBits that developes PhotoMechanic.

55

After my latest posts in this DXO tread, it seems to have totally died. That was really not my intention.

I don´t know if people might have felt stupid about the level of that somewhat bizarre discussion that was going on here when I pointed out where the development-front around the keyword issues really lies today with iMatch and Autotagger as an example.

What’s even more bizarre is the fact that these discussions have been going on for probably 4 years or more here at DXO Forums, not the least around the problems DXO Photolab previously had with the handling of hierarchical keywords (it has not always been fully compatible with, for example, PhotoMechnaic’s handling of keyword data exchange between applications). So, it has far from just being a matter of being able to export and import vocabularies.

Mario Westphal the developer wrote a comment in the Photool community: “From the thread you posted, I learned what I already knew: Many people don’t know what they are missing. This applies to both RAW processing and Digital Asset Management (DAM). Users often express surprise upon discovering that other RAW processors produce superior results compared to Adobe Lightroom!
Additionally, many learn about the subpar state of metadata in their files and how improving it—through keywords, controlled vocabularies, descriptions, AI-generated content, and standardized, interchangeable metadata via IMatch and ExifTool—can be beneficial.”

I think that comment: “many just don´t know what they are missing” is spot on. Many of us including me have for long been stuck in a “bubble” where Photolab, Lightroom or for that matter Capture One has been our “whole” monolithic metadata management world and relatively few of us have taken the steps to scale up by using PhotoMechanic och iMatch and know what it really has to offer.

I also just find it astonishing what Mario Westphal just wrote: “I began working on AutoTagger in July 2024, delving into AI technologies and testing with OpenAI, Mistral, and Ollama. During this period of discovery, I encountered the dynamic nature of AI—vendors like OpenAI frequently updated their APIs to adapt and evolve, introducing new models that significantly enhanced previous offerings.”

So, he hasn´t even spent a full year developing Autotagger (about 10 months?) to the state and full power and control it now empower the users of iMatch with, together with at least one on the AI-providers that Autotagger supports today. I think that is both pretty unique and remarkable not the least when it comes to how brilliant all keyword related tasks are handled and controlled in iMatch 2025. … and again, this not really a question about the need for enormous R&D resources and big AI-centered development teams to achive this but more about one single developers brilliant skills och totally focused Holistic Engineering. Until now it hasn´t either helped Adobe with all their billions or anybody else, has it? They might still be dreaming of doing what Mario Westphal already have done.

With Holistic Engineering I mean how both iMatch Face Detection and the Autotaggers handling of the AI-driven image analysis through the four different promps together manage to write so often very well written Descriptions and even hierarchical keywords to the pictures and on top of that even managege to automatically manage our Thesaurus to our liking totally automatically regardless if we prefer using “controlled vocabularies” or prefer our own hierarchical keywords or just unstructured ones. Keywords with “Zero Administration” who saw that coming a year ago. Did you?? I guess most people could not believe that even would be possible. Today this is already solved and have become a “non-issue” for the iMatch 2025 users.

It will be very interesting to see how both Adobe and the rest will react and respond to this because from now on old excuses will no longer be excused.

56

@freixas has started the thread with sharing an idea and a few scripts for us to use or not use.

We could test the ideas/workarounds/solutions and say thank you or propose improvements/extensions or simply go our ways.

But it is quite common that human reactions revolve around whether something is good, useful, nonsense or whatever qualities people want to tag things with, often based on preconceptions or hearsay. This speeds up discussions … unless someone else will reiterate the process of adding a tag to the new response etc. and the reason that created the thread gets lost in the process.

Elephants can lift tree trunks, but when it’s about toothpicks, elephants are probably not the preferred solution.

Having said that, let’s return to the topic of the thread. We can always start threads about trees and toothpicks separately.

Let those, who want to discuss these, open a respective thread.

57

I don´t see what you see Patypus. Freixas has edited his first post three times and in the first version I saw something like this script which I have copied from Bryans post #16 :

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

… and Freixas originally also displayed the result of the script which was a list full of hierachic keywords from the database of Picture Library and that is just part of a long history here at DXO Forums with all sorts of problems revolving around DXO:s poor implementation of hierarchical keywords and how to handle that.

These discussions about all the problems people have found and suggestions of how to work around these problems have gone on and on and on for I think at least four years. From the beginning it also touched non working mark-up with stars and/or color labels in our workflows. Joanna even ending up building her own application taking care of both stars, color labels and keywords that looks very much as a file viewer that we even have here in Photolab now.

My conclusion is that after all these years of endless keyword-discussions DXO hasn´t really listened at all and here comes new community members every year that keeps this eternal flame burning. Freixas contribution here is just another attempt to handle things that are not there and like Joanna he might end up building jet another parallell solution that doesn´t really change anything.

I just described where the innovation front lays today and that Mario Westphal during dedicated work under ten months has managed to build a complete solution that practically solves all these old keyword-related problems. So these old keyword issues have become a non-issue now since iMatch integrates very well and seamlessly with Photolab and solves all the problems Photolab don´t. Properly configured it gives Zero Administration even to the use of hierarchical keywords with and automatic maintenance of a Thesaurus too.

For the ones really interested in scaling up and establishing a really efficient Keywords- and Descriptions-workflow with practically Zero Administration even with Photolab - now you know where to find the tools to fix that.

For the ones not interested - I leave you here in this “Death Valley” of the keywords to find your way out by yourselves. Good Luck!

58

…which seems normal, different people see things differently.

59

If DXO moderators spend some time digesting or commenting in this forum rather than deleting posts, we would actually get somewhere.
This, to me is not a friendly place to exchange ideas. If someone wants the topic a focussed only on what they deem right, why not make it a private chat. disappointing! DXO: not impressed!

60

Apparently discussing commercial products (PS, Lr, etc) is OK but discussing other non-commercial contributions from real world users is not :roll_eyes:

1 Like
61

@RAGING_FURY Which posts (in which topics) are you suggesting DxO have removed!?

@Joanna I have been otherwise engaged so I must have missed it but to what are you referring?

62

Someone reported a couple of my posts as being off-topic and they have been deleted.

1 Like
63

Really @Joanna? Many, if not most threads tend to wander off topic. I was not aware of posts being deleted for that reason.

Mark

64

@Joanna how does a person go about having a post removed? Given some of the posts that have remained when they could easily be deemed as offensive to either DxO (staff) or another use or both, I am surprised.

I actually took a copy of the topic, something I rarely do but have done from time to times, but when @freixas threatened to pull the plug I felt that I wanted to preserve the topic for my files so here it is as at the date and time in the title.

2025-06-08_011246_Keywording SQL topic.pdf (11.6 MB)

@mwsilvers When do topics ever wander off topic?

Sorry I misstated (correct spelling??) that, it should have read “when do topics ever not wander off topic!?”.

PS:- Would you like to see a picture of my garden as the heat consumes it in spite of having 18 x 200 litre water butts, ops sorry off topic, better take another snapshot!!.

2025-06-19_191049_Keywording SQL topic_V02.pdf (12.5 MB)

With the temperature at 28.5 C at my desk I am going to use some of that water on the plants.

2 Likes
65

If I had make an absolute statement regarding threads going off topic someone would have called me on it.

The weather here in New Jersey today is 87 degrees Fahrenheit which is 30.6 C. Over the next few days the temperature will rise to 100 degrees F which is equivalent to 37.8 C . By comparison your current 28.5 C would be a comfortable temperature here.

Mark

1 Like
66

… to find I’m king on the hill, top of the heat … - New Yoork, New Yooooork!

…is there no flag for “hot topic” ?
:grin:

67

Dm’d screenshots of the 2 emails I received from DXO stuff with the offending posts that were removed. I don’t see why the injection of a bit of humanity (a photo or personal comment) here and there harms a thread but clearly Mr Orwell thinks otherwise. Plus side of the nice weather: less time behind a pc winding myself up with things like this :grinning:

1 Like
68

@RAGING_FURY I am sorry that you became a “victim” of the “thought police” as a result of a deviation in the theme of the posts that I made, sorry. As a result of the comments made by the author of the topic I removed my photos.

It is a wake up call that deviating from the topic can have consequences when the author of the topic so chooses, we have been “warned”! and must learn which situations may be tolerated and by which topic author.

It has been an interesting topic both for the original content and for the subsequent fallout.

Don’t blame DxO they are “respecting” the author’s wishes and we can always use the “Share your image and chat” topic and link back to the original topic, if we care to, and do what that particular topic category purports to promote, chat, possibly about the finer points of the photography and what PhotoLab managed to help us achieve or failed to help us achieve or …

Now about you Pittosporum …

Regards

Bryan

1 Like
69

I was actually born and raised in New York City and moved just across the Hudson River to New Jersey a few years after I got married. I have lived in the New York Metropolitan area my entire life. At one time or another I lived in four of the five boroughs (counties) that make up New York City.

Mark

1 Like