The Aperture Database Structure

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

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.

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)
 

664