Aperture and SQLite

In my previous post, I wrote that there was no way in AppleScript to get the original filename of a version. Because Aperture uses SQLite to store information about the Library, however, there is a way to access this information!

There are a number of tables in the Library database, which is normally located at ~/Pictures/Aperture Library.aplibrary/Aperture/aplib/Library.apdb, but the ones that I’ll focus on are ZRKMASTER, ZRKVERSION and ZRKFILE. Examining the table schemas reveals a common field, Z_PK. This is the unique id for each master image. AppleScript lets me retrieve the unique id for an image version; with that I can access the original filename for the file. Thanks to Scott, deriving the appropriate SQL code was fast:

SELECT zrkfile.zname FROM zrkfile, zrkmaster, zrkversion WHERE zrkfile.z_pk = zrkmaster.z_pk AND zrkversion.z_pk = zrkmaster.z_pk AND zrkversion.zuuid = “yjjEFvlGRReGkOke+xLXsw”;

or

SELECT zrkfile.zname FROM zrkfile join zrkmaster ON zrkfile.z_pk = zrkmaster.z_pk JOIN zrkversion ON zrkversion.z_pk = zrkmaster.z_pk WHERE zrkversion.zuuid = “yjjEFvlGRReGkOke+xLXsw”;

Incorporating this into my AppleScripts was equally easy, thanks to the do shell script command:

sqlite3 ~/Pictures/Aperture Library.aplibrary/Aperture.aplib/Library.apdb “select zrkfile.zname from zrkfile join zrkmaster on zrkfile.z_pk = zrkmaster.z_pk join zrkversion on zrkversion.z_pk = zrkmaster.z_pk where zrkversion.zuuid = ‘yjjEFvlGRReGkOke+xLXsw’;”

Where “yjjEFvlGRReGkOke+xLXsw” is the id of the image version.

We’re getting closer!