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
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.
|ZRKFILE||Information about the on-disk files|
|ZRKIMAGEADJUSTMENT||Image adjustments made to versions|
|ZRKMASTER||Information about master images|
|ZRKPROPERTYIDENTIFIER||Property types (and each keyword!?)|
|ZRKSEARCHABLEPROPERTY||Links between properties and versions|
|ZRKVERSION||Versions of all masters|
|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|
|Z_PK||A unique ID for this property description|
|ZPROPERTYKEY||A description of this property|
|ZPROPERTYTYPE||2=Keyword, 3=EXIF, 5=Aperture, 7=Timezone|
|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)|
|Z_PK||A unique ID for this folder|
|ZLIBRARYRELATIVEPATH||Path relative to the root of the library|
|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)