Last in a series:

1. First impressions
2. Asset management
3. Under the hood: file format internals

This article was never completed because I switched to Lightroom and lost interest. What was done may be of interest to Aperture users, although the data model probably changed since 1.0

Aperture stores its library as a bundle with the extension .aplibrary. This is a concept inherited from NeXTstep, where an entire directory that has the bundle bit set is handled as if it were a single file. A much more elegant system than Mac OS Classic’s data and resource forks.

Inside the bundle, there is a directory Aperture.aplib which contains the metadata for the library in a file Library.apdb. This file is actually a SQLite3 database. SQLite is an excellent, lightweight open-source embedded relational database engine. Sun uses SQLite 2 as the central repository for SMF, the next-generation service management facility that controls booting the Solaris operating system and its automatic fault recovery, a strong vote of confidence by Sun in SQLite’s suitability for mission-critical use. SQLite is also one of the underlying data storage mechanisms used by Apple’s over-engineered Core Data framework.

You don’t have to use Core Data to go through the database, the /usr/bin/sqlite3 command-line utility is perfectly fine for this purpose. Warning: using sqlite3 to access Aperture’s data directly is obviously unsupported by Apple, and should not be done on a mission-critical library. At the very least, make sure Aperture is not running.

ormag ~/Pictures/Aperture Library.aplibrary/Aperture.aplib>sqlite3 Library.apdb
SQLite version 3.1.3
Enter ".help" for instructions
sqlite> .tables
ZRKARCHIVE             ZRKIMAGEADJUSTMENT     ZRKVERSION
ZRKARCHIVERECORD       ZRKMASTER              Z_10VERSIONS
ZRKARCHIVEVOLUME       ZRKPERSISTENTALBUM     Z_METADATA
ZRKFILE                ZRKPROPERTYIDENTIFIER  Z_PRIMARYKEY
ZRKFOLDER              ZRKSEARCHABLEPROPERTY
sqlite> .schema z_metadata
ormag ~/Pictures/Aperture Library.aplibrary/Aperture.aplib>sqlite3 Library.apdb
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> .tables
ZRKARCHIVE             ZRKKEYWORD             ZRKVOLUME
ZRKARCHIVERECORD       ZRKMASTER              Z_11VERSIONS
ZRKARCHIVEVOLUME       ZRKPERSISTENTALBUM     Z_9VERSIONS
ZRKFILE                ZRKPROPERTYIDENTIFIER  Z_METADATA
ZRKFOLDER              ZRKSEARCHABLEPROPERTY  Z_PRIMARYKEY
ZRKIMAGEADJUSTMENT     ZRKVERSION
sqlite> .schema zrkfile
CREATE TABLE ZRKFILE ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZASSHOTNEUTRALY FLOAT, ZFILECREATIONDATE TIMESTAMP, ZIMAGEPATH VARCHAR, ZFILESIZE INTEGER, ZUUID VARCHAR, ZPERMISSIONS INTEGER, ZNAME VARCHAR, ZFILEISREFERENCE INTEGER, ZTYPE VARCHAR, ZFILEMODIFICATIONDATE TIMESTAMP, ZASSHOTNEUTRALX FLOAT, ZFILEALIASDATA BLOB, ZSUBTYPE VARCHAR, ZCHECKSUM VARCHAR, ZPROJECTUUIDCHANGEDATE TIMESTAMP, ZCREATEDATE TIMESTAMP, ZISFILEPROXY INTEGER, ZDATELASTSAVEDINDATABASE TIMESTAMP, ZISMISSING INTEGER, ZVERSIONNAME VARCHAR, ZISTRULYRAW INTEGER, ZPROJECTUUID VARCHAR, ZEXTENSION VARCHAR, ZISORIGINALFILE INTEGER, ZISEXTERNALLYEDITABLE INTEGER, ZFILEVOLUME INTEGER, ZMASTER INTEGER );
CREATE INDEX ZRKFILE_ZCHECKSUM_INDEX ON ZRKFILE (ZCHECKSUM);
CREATE INDEX ZRKFILE_ZCREATEDATE_INDEX ON ZRKFILE (ZCREATEDATE);
CREATE INDEX ZRKFILE_ZFILECREATIONDATE_INDEX ON ZRKFILE (ZFILECREATIONDATE);
CREATE INDEX ZRKFILE_ZFILEMODIFICATIONDATE_INDEX ON ZRKFILE (ZFILEMODIFICATIONDATE);
CREATE INDEX ZRKFILE_ZFILESIZE_INDEX ON ZRKFILE (ZFILESIZE);
CREATE INDEX ZRKFILE_ZFILEVOLUME_INDEX ON ZRKFILE (ZFILEVOLUME);
CREATE INDEX ZRKFILE_ZISEXTERNALLYEDITABLE_INDEX ON ZRKFILE (ZISEXTERNALLYEDITABLE);
CREATE INDEX ZRKFILE_ZMASTER_INDEX ON ZRKFILE (ZMASTER);
CREATE INDEX ZRKFILE_ZNAME_INDEX ON ZRKFILE (ZNAME);
CREATE INDEX ZRKFILE_ZPROJECTUUIDCHANGEDATE_INDEX ON ZRKFILE (ZPROJECTUUIDCHANGEDATE);
CREATE INDEX ZRKFILE_ZUUID_INDEX ON ZRKFILE (ZUUID);
sqlite> .schema z_metadata
CREATE TABLE Z_METADATA (Z_VERSION INTEGER PRIMARY KEY, Z_UUID VARCHAR(255), Z_PLIST BLOB);
sqlite> .schema z_primarykey
CREATE TABLE Z_PRIMARYKEY (Z_ENT INTEGER PRIMARY KEY, Z_NAME VARCHAR, Z_SUPER INTEGER, Z_MAX INTEGER);
sqlite> select * from z_primarykey;
1|RKArchive|0|1
2|RKArchiveRecord|0|0
3|RKArchiveVolume|0|1
4|RKFile|0|2604
5|RKFolder|0|23
6|RKProject|5|0
7|RKProjectSubfolder|5|0
8|RKImageAdjustment|0|1086
9|RKKeyword|0|758
10|RKMaster|0|2604
11|RKPersistentAlbum|0|99
12|RKPropertyIdentifier|0|119
13|RKSearchableProperty|0|84191
14|RKVersion|0|2606
15|RKVolume|0|0
sqlite>

One useful command is .dump, which will dump the entire database in the form of the SQL commands required to recreate it. Even with a single-photo library, this generates many pages of output.

Here is my attempt to reverse engineer the Aperture 1.5.2 data model. CoreData, like all object-relational mappers (ORMs) leaves much to be desired from the relational perspective. The fact SQLite foreign keys constraints are not enforced (and not even set by CoreData) doesn’t help. Click on the diagram below to expand it.

All tables are linked to Z_PRIMARYKEY which implements a form of inheritance using the column Z_ENT to identify classes. The only table that seems to use this today is ZRKFOLDER, where the rows can have a Z_ENT of 5 (Folder), 6 (Project) or 7 (ProjectSubfolder). For clarity, I have omitted the links between all tables and Z_PRIMARYKEY.

ZRKIMAGEADJUSTMENT looks like the table that records the transformations that turn a master image into a version, Live Image style.