The Aperture Database Structure

December 9, 2005

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 Name	Description
ZRKARCHIVE
ZRKARCHIVERECORD
ZRKARCHIVEVOLUME
ZRKFILE	Information about the on-disk files
ZRKFOLDER
ZRKIMAGEADJUSTMENT	Image adjustments made to versions
ZRKMASTER	Information about master images
ZRKPERSISTENTALBUM
ZRKPROPERTYIDENTIFIER	Property types (and each keyword!?)
ZRKSEARCHABLEPROPERTY	Links between properties and versions
ZRKVERSION	Versions of all masters

Searchable Property
Attribute Name	Description
Z_PK	A unique ID for this property link
ZVERSION	Link to an image version in the ZRKVERSION table
ZPROPERTYIDENTIFIER	Link to the a property identifier in ZRKPROPERTYIDENTIFIER

Property Identifier
Attribute Name	Description
Z_PK	A unique ID for this property description
ZPROPERTYKEY	A description of this property
ZPROPERTYTYPE	2=Keyword, 3=EXIF, 5=Aperture, 7=Timezone

Version
Attribute Name	Description
Z_PK	A unique ID for this version
ZFILE	Link to the original file in ZRKFILE
ZMASTER	Link to the master image in ZRKMASTER
ZDATELASTSAVEDINDATABASE	Time the image was last modified (NSDate)

Folder (projects and albums are considered folders)
Attribute Name	Description
Z_PK	A unique ID for this folder
ZLIBRARYRELATIVEPATH	Path 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
add keyword to hud

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)