# The Aperture Database Structure

I'm working on decoding the Aperture database structure. Just playing around in the database has been very interesting.

Update: this was written with Aperture 1.0 in mind. This is probably completely inaccurate now

These tables are incomplete. However, they do contain the columns that I used in my test queries.

Update: I just found a cool tool called SQLite Database Browser that makes it much easier to look around the db than using the command line sqlite3 tool.

The Tables
Table NameDescription
ZRKARCHIVE
ZRKARCHIVERECORD
ZRKARCHIVEVOLUME
ZRKFOLDER
ZRKPERSISTENTALBUM
ZRKPROPERTYIDENTIFIERProperty types (and each keyword!?)
ZRKVERSIONVersions of all masters
Searchable Property
Attribute NameDescription
Z_PKA unique ID for this property link
ZVERSIONLink to an image version in the ZRKVERSION table
ZPROPERTYIDENTIFIER Link to the a property identifier in ZRKPROPERTYIDENTIFIER
Property Identifier
Attribute NameDescription
Z_PKA unique ID for this property description
ZPROPERTYKEYA description of this property
ZPROPERTYTYPE2=Keyword, 3=EXIF, 5=Aperture, 7=Timezone
Version
Attribute NameDescription
Z_PKA unique ID for this version
ZFILELink to the original file in ZRKFILE
ZMASTERLink to the master image in ZRKMASTER
Folder (projects and albums are considered folders)
Attribute NameDescription
Z_PKA unique ID for this folder
ZLIBRARYRELATIVEPATHPath relative to the root of the library
 Master Attribute Name Description Z_PK A unique ID for this master ZPROJECT A link to the project this master is part of in ZRKFOLDER

Here are some example queries:

Print out all properties that can be assigned to an image.

select ZPROPERTYKEY from ZRKPROPERTYIDENTIFIER


Print out all images that are part of project 51.

select * from ZRKMASTER where ZPROJECT=51


Search all properties of specific type (keywords):

select * from ZRKPROPERTYIDENTIFIER where ZPROPERTYTYPE=2


I would like to reproduce the database query that goes along with slowdown in the filter hud. It involves selecting all property names for properties that are keywords and are assigned to versions of masters that are part of the selected folder. It's hard to say and even harder to read the query that does it.

The brute force N^3 algorithm in psuedocode. On my machine a query of the searchable property table keyed on a specific property indentifier takes around 0.009s of user time. For my problem case that would be 600 versions * 118 keywords * 0.009 seconds = 10 minutes.

foreach version in folder
foreach propertyidentifier of type 'keyword'
foreach searchableproperty with propkey of propertyidentifier.key


SQL provides a method for doing this in one query that will speed things up significantly. Using the "IN" selection option you can build up combinations of queries. Note that I'm not even using the speed optimized index data in the database and it's still really fast.

select ZPROPERTYKEY from ZRKPROPERTYIDENTIFIER where ZPROPERTYTYPE=2 and Z_PK in
(select ZPROPERTYIDENTIFIER from ZRKSEARCHABLEPROPERTY where ZVERSION in
(select Z_PK from ZRKVERSION where ZMASTER in
(select Z_PK from ZRKMASTER where ZPROJECT=61)))

Sean Houghton
Motorcycle
Crash
Trackday
Joel Pritchett
Stephane Etienne
Sean Houghton   People
Joel Pritchett  People
Stephane Etienne        People

real    0m0.269s
user    0m0.236s
sys     0m0.033s


Here's a selection to count the number of versions that had to be checked for keywords: 664. Opening the filter hud with this folder takes 3 seconds in Aperture, but the database query only takes around 1/4 second of that.

select count(*) from ZRKVERSION where ZMASTER in
(select Z_PK from ZRKMASTER where ZPROJECT=61)

664