or how to tag your pictures efficiently on the Mac OSX platform…
Most projects are probably better managed with flexible tags than rigid directory structures but there are some bothersome details. General purpose tagging programs like Tags and also Spotlight can not read proprietary keywords in LR 3.
I have been thinking a while to bridge both worlds. Having no Applescript experience it turned out to be a nightmare as SQL code from the terminal sent to LR’s sqlite3 database is NOT running properly in the Applescript environment! So don’t try to improve the following code that was largely obtained by try + error with the help of about one dozen websites.
PLEASE NOTE: 1. LR needs to be closed first! 2. adjust path names! 3. adjust SQL code (I am tagging only clean + geotagged JPGs but never RAW files) and 4. keep in mind that everything is at your own risk. Maybe it’s a good idea to start with a backup copy of the LR database, yea, yea.
(* WJST 6/11/2010 Spotlight tagging of pictures using Lightroom keywords *)
on replaceText(find, replace, subject) set prevTIDs to text item delimiters of AppleScript set text item delimiters of AppleScript to find set subject to text items of subject set text item delimiters of AppleScript to replace set subject to "" & subject set text item delimiters of AppleScript to prevTIDs return subject end replaceText
-- CHANGE PATH HERE TAKE CARE OF " AND ' QUOTATIONS set db to "'/Lightroom 3 Catalog.lrcat'" set head to "sqlite3" & space & db & space & quote
-- CHANGE QUERY FOR EXAMPLE DROP WHERE CLAUSE set getTags to "select rf.absolutePath as f1, fo.pathFromRoot as f2, fi.baseName as f3, fi.extension as f4, group_concat(kw.name) as f5 from Adobe_images i join AgLibraryFile fi on i.rootFile = fi.id_local join AgLibraryFolder fo on fi.folder = fo.id_local join AgLibraryRootFolder rf on fo.rootFolder = rf.id_local join AgLibraryKeyword kw on ki.tag = kw.id_local join AgLibraryKeywordImage ki on i.id_local = ki.image where (captureTime between '2010-01-01' and '2010-12-13') and f4='jpg' group by f3 order by f3 limit 10000;"
set all to (do shell script head & getTags & quote) set all to get replaceText("|", "", all) set all to get replaceText("jpg", ".jpg|", all) set AppleScript's text item delimiters to return set all to every text item of all
repeat with k from 1 to (count all) by 1 set f0 to item (k) of all set fn to text 1 thru ((offset of "|" in f0) - 1) of f0 set tn to text ((offset of "|" in f0) + 1) thru -1 of f0 tell application "Tags" set fn to tag items for paths {fn} set fn to first item of fn set AppleScript's text item delimiters to "," set tn to every text item of tn set tags of fn to tn end tell end repeat