Here are the Mac versions.
Get the Keyword Structure
Photolab doesn’t have any method of importing/exporting the keyword structure. The tools below only export your existing structure. I’ll give you three variants.
Version 1: Get the basic structure only
/*
* Create a table of all keywords. Every keyword is written using a
* full path. The results are in alphabetical order.
*/
WITH RECURSIVE
/*
* Create a table that matches keyword IDs to the full path of the
* keyword it is associated with.
*/
fullKeywordPath AS (
SELECT Z_PK, ZTITLE, ZPARENT, ZTITLE AS path
FROM ZDOPKEYWORD
WHERE ZPARENT IS NULL
UNION ALL
SELECT ZDOPKEYWORD.Z_PK, ZDOPKEYWORD.ZTITLE, ZDOPKEYWORD.ZPARENT, fullKeywordPath.path || '|' || ZDOPKEYWORD.ZTITLE
FROM ZDOPKEYWORD, fullKeywordPath
WHERE ZDOPKEYWORD.ZPARENT = fullKeywordPath.Z_PK
)
SELECT path FROM fullKeywordPath ORDER BY path
Version 2: Get the basic structure and counts
This version just adds an image count to each keyword. The count should match what you see when you view keywords in Photolab. It runs a lot slower than the version above.
/*
* Create a table of all keywords. Every keyword is written using a
* full path. The results are in alphabetical order. Each path is
* followed by a count of the number of items associated with that
* keyword. Getting the count slows the query a bit.
*/
WITH RECURSIVE
/*
* Create a table that matches keyword IDs to the full path of the
* keyword it is associated with.
*/
fullKeywordPath AS (
SELECT Z_PK, ZTITLE, ZPARENT, ZTITLE AS path
FROM ZDOPKEYWORD
WHERE ZPARENT IS NULL
UNION ALL
SELECT ZDOPKEYWORD.Z_PK, ZDOPKEYWORD.ZTITLE, ZDOPKEYWORD.ZPARENT, fullKeywordPath.path || '|' || ZDOPKEYWORD.ZTITLE
FROM ZDOPKEYWORD, fullKeywordPath
WHERE ZDOPKEYWORD.ZPARENT = fullKeywordPath.Z_PK
)
SELECT
path,
( SELECT COUNT(*) FROM Z_8KEYWORDS WHERE Z_10KEYWORDS = fullKeywordPath.Z_PK) AS count
FROM fullKeywordPath
ORDER BY path
Version 3: Get the basic structure in Adobe Bridge format
Adobe Bridge can import keyword structures. They use tabs to indicate the level of each keyword.
/*
* Create a table of all keywords. If each row of the table is
* converted into a line in a file, the file can be imported into
* Adobe Bridge to create the same structure.
*/
WITH RECURSIVE
/*
* Create a table that matches keyword IDs to the full path of the
* keyword it is associated with. Create a prefix that consists of a
* tab for each level of depth of a keyword, with the top level
* being level 0.
*/
fullKeywordPath AS (
SELECT Z_PK, ZTITLE, ZPARENT, ZTITLE AS path, "" AS prefix
FROM ZDOPKEYWORD
WHERE ZPARENT IS NULL
UNION ALL
SELECT
ZDOPKEYWORD.Z_PK,
ZDOPKEYWORD.ZTITLE,
ZDOPKEYWORD.ZPARENT,
fullKeywordPath.path || '|' || ZDOPKEYWORD.ZTITLE,
prefix || char(9) AS prefix
FROM ZDOPKEYWORD, fullKeywordPath
WHERE ZDOPKEYWORD.ZPARENT = fullKeywordPath.Z_PK
)
SELECT prefix || ZTITLE AS name
FROM fullKeywordPath
ORDER BY path
Find Hierarchy Problems
When you tag an image with a keyword, you can set up a preference that ensures that all parent keywords are also tagged. Drag-and-drop changes and other things (an accidental cursor click) can screw up this scheme.
The code below will output any image name/keyword path pairs that should be enabled to maintain a complete hierarchy path, but aren’t. It also lists a UUID.
The UUID can distinguish between an image and a virtual copy. You might have a problem in some and not others. 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.
This script can be very slow if you have a lot of tagged images. I just ran it and it took 6 seconds to display any results. If it says “0 rows returned”, then there are no problems.
/*
* Get a table of each item/keyword pair that should be in
* Z_8KEYWORDS in order to have complete hierarchies but is not. The
* information is returned as filename and keyword path.
*
* The result rows also include a UUID. Each source file has a main
* image and one or more virtual copies. Each is uniquely identified
* by its UUID. If the same filename/keyword path appears more than
* once, then the UUIDs will be different. This indicates there is a
* problem in more than one version of the image. If a file/keyword
* path appears only once, but there are virtual copies of the image,
* then only one of the versions has a problem, but you will have to
* check them all to see which has the problem.
*
* 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 Z_PK, ZNAME, ZPARENT, ZNAME AS path FROM ZDOPFOLDER
WHERE ZPARENT IS NULL
UNION ALL
SELECT ZDOPFOLDER.Z_PK, ZDOPFOLDER.ZNAME, ZDOPFOLDER.ZPARENT, fullFolderPath.path || '/' || ZDOPFOLDER.ZNAME
FROM ZDOPFOLDER, fullFolderPath
WHERE ZDOPFOLDER.ZPARENT = fullFolderPath.Z_PK
),
/*
* Create a table that matches keyword IDs to the full path of the
* keyword it is associated with.
*/
fullKeywordPath AS (
SELECT Z_PK, ZTITLE, ZPARENT, ZTITLE AS path
FROM ZDOPKEYWORD
WHERE ZPARENT IS NULL
UNION ALL
SELECT ZDOPKEYWORD.Z_PK, ZDOPKEYWORD.ZTITLE, ZDOPKEYWORD.ZPARENT, fullKeywordPath.path || '|' || ZDOPKEYWORD.ZTITLE
FROM ZDOPKEYWORD, fullKeywordPath
WHERE ZDOPKEYWORD.ZPARENT = fullKeywordPath.Z_PK
),
potentials AS (
SELECT Z_8ITEMS, Z_10KEYWORDS, ZPARENT FROM Z_8KEYWORDS
JOIN ZDOPKEYWORD ON ZDOPKEYWORD.Z_PK = Z_8KEYWORDS.Z_10KEYWORDS
UNION
SELECT potentials.Z_8ITEMS, ZDOPKEYWORD.Z_PK AS Z_10KEYWORDS, ZDOPKEYWORD.ZPARENT FROM potentials
JOIN ZDOPKEYWORD ON ZDOPKEYWORD.Z_PK = potentials.ZPARENT
)
SELECT
( SELECT path FROM fullFolderPath WHERE fullFolderPath.Z_PK = ZDOPSOURCE.ZPARENT ) || '/' || ZDOPSOURCE.ZNAME AS filename,
( SELECT path FROM fullKeywordPath WHERE fullKeywordPath.Z_PK = ZDOPKEYWORD.Z_PK ) AS keywordpath,
ZDOPINPUTITEM.ZUUID
FROM potentials
JOIN ZDOPINPUTITEM ON ZDOPINPUTITEM.Z_PK = potentials.Z_8ITEMS
JOIN ZDOPKEYWORD ON ZDOPKEYWORD.Z_PK = potentials.Z_10KEYWORDS
JOIN ZDOPSOURCE ON ZDOPSOURCE.Z_PK = ZDOPINPUTITEM.ZSOURCE
WHERE NOT EXISTS ( SELECT 1 FROM Z_8KEYWORDS AS ik WHERE ik.Z_8ITEMS = potentials.Z_8ITEMS AND ik.Z_10KEYWORDS = potentials.Z_10KEYWORDS )
ORDER BY filename, keywordpath
Find Images Without Any Keywords
This script was not part of the OP, but was added as a request. It outputs a list of all images that have no keywords.
/*
* 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 Z_PK, ZNAME, ZPARENT, ZNAME AS path FROM ZDOPFOLDER
WHERE ZPARENT IS NULL
UNION ALL
SELECT ZDOPFOLDER.Z_PK, ZDOPFOLDER.ZNAME, ZDOPFOLDER.ZPARENT, fullFolderPath.path || '/' || ZDOPFOLDER.ZNAME
FROM ZDOPFOLDER, fullFolderPath
WHERE ZDOPFOLDER.ZPARENT = fullFolderPath.Z_PK
)
SELECT
( SELECT path FROM fullFolderPath WHERE fullFolderPath.Z_PK = ZDOPSOURCE.ZPARENT ) || '/' || ZDOPSOURCE.ZNAME AS filename,
ZDOPINPUTITEM.ZUUID
FROM ZDOPINPUTITEM
JOIN ZDOPSOURCE ON ZDOPSOURCE.Z_PK = ZDOPINPUTITEM.ZSOURCE
WHERE NOT EXISTS ( SELECT 1 FROM Z_8KEYWORDS AS ik WHERE ik.Z_8ITEMS = ZDOPINPUTITEM.Z_PK )
ORDER BY filename
These were tested on a small test database sent by @platypus. Let me know if you run into any problems using them.
