Woo hoo!
Aperture Raw Database Access
Disclaimer: The methods outlined in this page use raw database access to the Aperture Library database. They are likely to break with updates to the Aperture Library because of Aperture updates. The methods on this page only query into the database and do not write to any files so they should be safe, but read and understand the code before you use it. That said, you should probably not try to access the Aperture Library database directly except as a last resort.
Here is my diagram of how I see the Aperture Library database (the flow lines show how I am building file paths from version IDs):
Here is a list of database activities that I have figured out:
- Determine if a version's master file is managed or referenced
- Path for managed master
- Path for referenced master
I should now state that I am not very good at writing SQL queries and that the seed for this work came from some things that Adam Tow blogged about in regards to his development of Timeature. After some Googling I found Fazid Majid's blog posting about Aperture internals and was off and running.
See also:
- Gordon's Tech: Aperture's date problem: Adam Tow replies
- SQLite Database Browser (cool tool!)
- DPReview forum postings
Below is an AppleScript to get the path to the currently selected version(s) master files (download). It is significantly faster and more reliable than my previous methods for doing this and seems to work properly for renamed images.
Script outline:
- Find the Library path
- Determine if version's master is referenced or managed
- Get path using appropriate method
-- ---------------------------------------------------------------------------------------------------------------------------
property libPath : "/Work/Aperture Library.aplibrary"
property sql : "/usr/bin/sqlite3"
-- ---------------------------------------------------------------------------------------------------------------------------
global DBPath
-- ---------------------------------------------------------------------------------------------------------------------------
on run
set libPath to my getLibPath()
set DBPath to (libPath & "/Aperture.aplib/Library.apdb") as string
set theOut to ""
tell application "Aperture"
copy selection to theSel
repeat with curImg in theSel
set theName to name of curImg
set theName to id of curImg
set theOut to theOut & theName & return
set isManaged to my isImageManaged(theName)
if isManaged is "1" then
log ("------------------------------------" & theName & " is a referenced image")
set filePath to my path_Referenced(theName)
log "----------- File path"
log filePath
set theOut to theOut & tab & "-- Referenced" & return
set theOut to theOut & tab & filePath & return
else if isManaged is "0" then
log ("------------------------------------" & theName & " is a managed image")
set filePath to my path_managed(theName)
log "----------- File path"
log filePath
set theOut to theOut & tab & "-- Managed" & return
set theOut to theOut & tab & filePath & return
else
log "Must have had an error"
set theOut to theOut & tab & "-- ERROR --" & return
end if
end repeat
end tell
display dialog theOut
end run
-- ---------------------------------------------------------------------------------------------------------------------------
on path_managed(versName)
log ("path_managed starting")
log ("Determining file path for: " & versName)
-- ------------------------------------------------------------ Get Project path
set theQuery to quote & "select ZLIBRARYRELATIVEPATH from ZRKFOLDER where ZUUID=(select ZPROJECTUUID from ZRKFILE where Z_PK=(select ZORIGINALFILE from ZRKMASTER where Z_PK=(select ZMASTER from ZRKVERSION where ZUUID='" & versName & "')))" & quote
log theQuery
set theScript to sql & space & (quoted form of DBPath) & space & theQuery
set projPath to do shell script theScript
log ("---------------------- Project path= " & projPath)
-- ------------------------------------------------------------ Get Import Group
set theQuery to quote & "select ZIMPORTGROUP from ZRKMASTER where Z_PK=(select ZMASTER from ZRKVERSION where ZUUID='" & versName & "')" & quote
log theQuery
set theScript to sql & space & (quoted form of DBPath) & space & theQuery
set importGroup to do shell script theScript
log ("---------------------- Import Group= " & importGroup)
-- ------------------------------------------------------------ Get Folder Name
set theQuery to quote & "select ZNAME from ZRKMASTER where Z_PK=(select ZMASTER from ZRKVERSION where ZUUID='" & versName & "')" & quote
log theQuery
set theScript to sql & space & (quoted form of DBPath) & space & theQuery
set folderName to do shell script theScript
log ("---------------------- Folder Name= " & folderName)
-- ------------------------------------------------------------ Get File Name
set theQuery to quote & "select ZNAME from ZRKFILE where Z_PK=(select ZORIGINALFILE from ZRKMASTER where Z_PK=(select ZMASTER from ZRKVERSION where ZUUID='" & versName & "'))" & quote
log theQuery
set theScript to sql & space & (quoted form of DBPath) & space & theQuery
set fileName to do shell script theScript
log ("---------------------- File Name= " & fileName)
set theOut to libPath & "/" & projPath & "/" & importGroup & "/" & folderName & "/" & fileName
return theOut
end path_managed
-- ---------------------------------------------------------------------------------------------------------------------------
on path_Referenced(versName)
log ("path_Referenced starting")
log ("Determining file path for: " & versName)
set theDisk to ""
set thePath to ""
-- Get ZRKMASTER from version name
-- sqlite3 Library.apdb "select ZMASTER from ZRKVERSION where ZNAME='xxxxxxx'"
-- Get ZORIGINALFILE from ZRKMASTER
-- sqlite3 Library.apdb "select ZORIGINALFILE from ZRKMASTER where Z_PK='12111'"
-- Get ZIMAGEPATH from ZRKFILE
-- sqlite3 Library.apdb "select ZIMAGEPATH from ZRKFILE where Z_PK='12122'"
-- Full command:
-- sqlite3 Library.apdb "select ZIMAGEPATH from ZRKFILE where Z_PK=(select ZORIGINALFILE from ZRKMASTER where Z_PK=(select ZMASTER from ZRKVERSION where ZNAME='versName'))"
try
-- ----------------------- Get image path
set theQuery to quote & "select ZIMAGEPATH from ZRKFILE where Z_PK=(select ZORIGINALFILE from ZRKMASTER where Z_PK=(select ZMASTER from ZRKVERSION where ZUUID='" & versName & "'))" & quote
log theQuery
set theScript to sql & space & (quoted form of DBPath) & space & theQuery
log theScript
set thePath to do shell script theScript
-- -------------------------- Get disk name
set theQuery to quote & "select ZNAME from ZRKVOLUME where Z_PK=(select ZFILEVOLUME from ZRKFILE where Z_PK=(select ZORIGINALFILE from ZRKMASTER where Z_PK=(select ZMASTER from ZRKVERSION where ZUUID='" & versName & "')))" & quote
log "Get disk name query"
log theQuery
set theScript to sql & space & (quoted form of DBPath) & space & theQuery
log theScript
set theDisk to do shell script theScript
set theOut to ("/Volumes/" & theDisk & "/" & thePath) as string
return theOut
on error
log "ERROR"
return "ERROR"
end try
end path_Referenced
-- ---------------------------------------------------------------------------------------------------------------------------
on isImageManaged(versName)
log ("Checking: " & versName)
-- Get ZRKMASTER from version name
-- sqlite3 Library.apdb "select ZMASTER from ZRKVERSION where ZNAME='xxxxxxx'"
-- Get ZORIGINALFILE from ZRKMASTER
-- sqlite3 Library.apdb "select ZORIGINALFILE from ZRKMASTER where Z_PK='12111'"
-- Get ZFILEISREFERENCE from ZRKFILE
-- sqlite3 Library.apdb "select ZFILEISREFERENCE from ZRKFILE where Z_PK='12122'"
-- Full command:
-- sqlite3 Library.apdb "select ZFILEISREFERENCE from ZRKFILE where Z_PK=(select ZORIGINALFILE from ZRKMASTER where Z_PK=(select ZMASTER from ZRKVERSION where ZNAME='versName'))"
-- 0 = managed
-- 1 = referenced
try
set theQuery to quote & "select ZFILEISREFERENCE from ZRKFILE where Z_PK=(select ZORIGINALFILE from ZRKMASTER where Z_PK=(select ZMASTER from ZRKVERSION where ZUUID='" & versName & "'))" & quote
log theQuery
set theScript to sql & space & (quoted form of DBPath) & space & theQuery
log theScript
set theOut to do shell script theScript
return theOut
on error
log "ERROR"
return "ERROR"
end try
end isImageManaged
-- ---------------------------------------------------------------------------------------------------------------------------
on getLibPath()
(*
set p_defaults to "/usr/bin/defaults"
set p_opts to "read com.apple.aperture LibraryPath"
set p_script to p_defaults & space & p_opts
set p_libPath to do shell script p_script
*)
tell application "System Events" to set p_libPath to value of property list item "LibraryPath" of property list file ((path to preferences as Unicode text) & "com.apple.aperture.plist")
if ((offset of "~" in p_libPath) is not 0) then
-- set p_posix to POSIX file p_libPath
set p_script to "/bin/echo $HOME"
set p_homePath to (do shell script p_script)
set p_offset to offset of "~" in p_libPath
set p_path to text (p_offset + 1) thru -1 of p_libPath
set g_libPath to p_homePath & p_path
log g_libPath
--set g_libPath to "/Work/brett/Pictures/Aperture Library.aplibrary"
else
set g_libPath to p_libPath
end if
end getLibPath