Keywords again

I see many threads on keywords. Like many I would like to copy or import my keyword list in Adobe Bride to the list in DXO PL. I know where the list is in Bridge: List includes the hierarchy designations. I can edit the xml file with a text editor.

I have edited DXO PL workspaces this way
Also presets. Those have file type .preset but they are just text files.

I don’t know what form DXO uses, but I haven’t even found where it keyword list is saved. Anyone found it?

Unfortunately, there is no editable list of keywords and hierarchies. When keywords are added to images, they are written into the database but are not easily accessible unless you are prepared to enter into the risky business of using an external SQLite database editor.

There is definitely no way to import a ready built list.

@Joanna

I am afraid that the database structure isn’t going to help a whole lot because the keyword hierarchies are held in a flattened (linked) form in the ‘Keywords’ Table.

For simple keywords it might be possible to load the DxPL DB ‘Keywords’ table and they should then be available in the keyword selection table in the Users interface but hierarchies, e.g. A|B|C|D (A>B>C>D)

2024-10-29_003038_

are held like this

2024-10-29_003121_

Best of luck with that, sorry, @gregglee.

You would need to take every hierarchical keyword from a list (extracted from another product) and flatten it.

Then determine the highest id already used and add 1.

Then take the first simple keyword from the hierarchy and create a keywords entry, assigning the id determined above.

Then add the next simple keyword to id + 1 and make it point to the previous simple keyword in the hierarchy and repeat until all elements have been added

then …

You can write easily write an SQL query to extract this as a hierarchy. The question is how do you want to represent the hierarchy? A tab character for each level, our some other character.

If you give me your required text file layout then I can give you the SQL to generate it.

Simplest method is to make a copy of your database to another directory and then use that for your queries using a free SQLite database browser.

Here is the SQL query to generate a hierarchy of keywords in two formats:

  1. Path as understood by PL8
  2. Indented text

With Recursive
KWHierarchy As (
Select
Id,
Value,
ParentId,
Value As Path,
‘’ as Level
From
Keywords
Where
ParentId Is Null
Union All
Select
c.Id,
c.Value,
c.ParentId,
ch.Path || ‘->’ || c.Value,
ch.level || ’ ’
From
Keywords c Inner Join
KWHierarchy ch On c.ParentId = ch.Id
)
Select
Value, Path, Level || Value as Indented
From
KWHierarchy
where Path like ‘Bird%’
Order by Path
;

Sorry about the formatting - leading spaces/tabs don’t show in this forum!

Output looks like this:

Please note: delete the line:

where Path like ‘Bird%’

To show all keywords. Or, edit to show what you want (% matches any number of any characters).

1 Like

@KeithRJ Thank you for your SQL. To get the formatting right use this Forum construct

image

which then gives this

type or paste code here

So you get this by cutting and pasting, from DB Browser in my case

With Recursive
    KWHierarchy As (
     Select
         Id,
         Value,
         ParentId,
         Value As Path,
         ‘’ as Level
     From
         Keywords
     Where
         ParentId Is Null
     Union All
     Select
         c.Id,
         c.Value,
         c.ParentId,
         ch.Path || ‘->’ || c.Value,
         ch.level || ’ ’
      From
         Keywords c Inner Join
         KWHierarchy ch On c.ParentId = ch.Id
     )
Select
    Value, Path, Level || Value as Indented
From
    KWHierarchy

Order by Path
;

However, I can’t get the syntax to work in DB Browser!?

I am concerned about the code I have copied here " ‘’ as Level" but the errors from DB Browser are

"Execution finished with errors.

Result: no such column: ‘

At line 1:"

I bought an SQL course a year or so ago but have never taken it and the database product (Burroughs/Unisys DMS II) I worked with for 36 years pre-dated SQL. Although the later versions of the “Inquirer” package did acquire an SQL interface, I tended to write COBOL programs for anything I wanted from the database!?

PS:- The SQL is potentially useful to me for unloading a DxPL database but @gregglee wants to upload a list of keywords from Bridge to DxPL DB I believe?

I use Flyspeed SQL Query as it has a good visual query builder.

You can download and use it for free.

Just tried this in DB Browser and it works just fine.

Here is the SQL as a zipped text file.

KWHierarchy.zip (417 Bytes)

@KeithRJ You mean it works like this, after I commented out the ‘Path’ line.

Thank you for supplying the working code, I will check later to find where the error(s) crept into my “version” of it!

I downloaded Flyspeed SQL Query, which also looks interesting, but got a different syntax error with my “copy” of your code!?

So your copy of your code looks like this

With Recursive
    KWHierarchy As (
     Select
         Id,
         Value,
         ParentId,
         Value As Path,
         '' as Level
     From
         Keywords
     Where
         ParentId Is Null
     Union All
     Select
         c.Id,
         c.Value,
         c.ParentId,
         ch.Path || '->' || c.Value,
         ch.level || '    '
     From
         Keywords c Inner Join
         KWHierarchy ch On c.ParentId = ch.Id
    )
Select
    Value, Path, Level || Value as Indented
From
    KWHierarchy
/*where Path like 'Bird%'*/
Order by Path
;

Regards

Bryan

@BHAYT Yes, that looks correct :slightly_smiling_face:

I have a question:

When I tried to restore my system from a crash caused by the movers that helped me move to another flat I could not restore the backup I had. I had to switch OS too from Windows 10 to 11 since my new computer was preinstalled with that.

Suddenly I found myself with a problem.
I was able to get Photolab to rebuild a keyword list (flat) just by reindexing my folders and pictures so Photolab was fine but since I also use PhotoMechanic as a more scalable substitute for PhotoLibrary in Photolab, PhotoMechanic had no keywordlist/vocabulary. Unlike Photolab though that lacks a possibility to export and import vocabularies - even hierarchical vocabularies are possible to import to PhotoMechanic. I have even tested when I evaluated PhotoMechanic to import hierarchical vocabularies from Lightroom and that works fine too. These are very simple to handle because they are just tab-separated textfiles. Before I decided to use flat keywords in PhotoMechanic I used to export and import that vocabulary just to add my own structures because the tools in PM was so ineffective.

So how did I fix that problem with the missing vocabulary matching the keywords used in my XMP-files? Well, I indexed all my folders and pictures with Capture One that of course also can both export and import vocabularies. So, after building that database and vocabulary in Capture One I just exported it and then imported it in PhotoMechanic. Problem solved.

If it is fine for you a user to live with a vocabulary that has a 1:1 relation to the keyword data that is fine but if the vocabulary has a lot of other keywords and keyword structures that are not yet used, then you still have a problem. If even those keywords you haven´t used are a must - which they might be if it is mandatory to use the full controlled vocablulary in your organisation or company there is today no real solution for that in Photolab.

So, it is all boiling down to whether it is OK the way Photolab is handling it all when reindexing all picture or not.

SQL is very powerful but it will never be the average users’ way to do it and this is just another proof of that Photolabs PictureLibrary still is a little bit of an immature toy when it comes to serious image DAM tasks. Every responsible R&D ought to think twice about how people shall be able to migrate to and from their XMP-based softwares. Everything else I see as an agenda of locking people in. The great interoperability XMP really offers, if used in the right way is fantastic. To block that interoperability will just add bad will to those who engage in such activities whether it is deliberat or not.

So, Dear DXO, even Adobe is offering ways in and out of Lightroom and so even DXO ought to do. Declining to do so will even be contra productive for DXO when people want to migrate to Photolab from other converters and I don´t really understand this since Lightroom is the only software where DXO really have cared to establish data interchange so far. But not when it comes to the vocabularies.

@Stenis, I agree with your observations that PL is very immature when it comes to DAM functionality where keywording is a very important part of a DAM.

I believe the way PL works with image files is not very compatible with a serious DAM. A DAM relies on a databases that collects data from files and what the user enters. PL adds data to the database when it encounters a file for the first time. As files can me moved, renamed and changed by other programs, PL can get confused and it’s database will get out of sync with the actual files.

DxO provide NO functions to help fix this sync issue. The closest they get is to allow you to store all that data in sidecar files.

It is for this reason I never rely on the database and only use sidecar files. If I could turn the database off then I would. Some people delete the database before each start of PL, I just delete the database from time to time.

I also use Photo Mechanic Plus as my DAM after I abandoned LR which I found was a very good DAM.

Lastly, the way PL handles keywords is very standard in the database world, but they have neglected to provide functions to export/import keywords and other housekeeping functions: hence I classify PL as a very poor DAM.

2 Likes

Personally, I don´t think I have all those big problems with the database in Photolab or at least they don´t stop me from having a very good integration with PhotoMechanic. The database and the metadata in Photolab for me is just used for some searching and control that the synchronization works as expected and it is pretty rock solid as it is now. I never update metadata in PL.

I have used reindexing quite a few times now just in order to get the database in synch, so it is more or less a routine for me that don´t really bother me.

1 Like

The problem with Photolab´s database can be fixed through a resynch of problem folders but I´m sure whether that is solving problems with orphaned records in the database.

Deleting the database have one big problem and it is if you are using “Projects” or rely on or use the function “External Search” which can be very useful. I do it a lot since I often open a selection in PM Plus with “Edit Picture …” in PM. Even these records are stored in the databas and if you delete them all that vanishes.

I also guess it must be pretty common with unsynch between the DOP-files and the DB.

I never got a friend with the metdatafunctions in Lightroom that I found terribly ineffective compared to PM and that was one big reason to why i switched. From what I remember Lightroom also have to be configured to maintain XMP in the files and that is really dangerous because the database is single point of failure and I had a few crashes with early versions of LR. From my point of view all RAW-converters ought to update the XMP-data in files by default. Anything else is to ask for trouble.

It has not happened a lot with the PictureLibrary in these respects the last years and it is like it ought to be in Photolab. First they add a feature and then that´s it.

Thanks. What I guessed, but disappointing.
In Windows folder C:\users[username]\appdata\local\DXO, DXO PL stores presets, watermarks and workspaces as files readable as text (though with “file types” [name].preset and .[name]XML.
So I thought there was at least a chance.
(Lens) modules are in that same folder, but apparently not as text.