Photos version 5 database

The full schema of the Photos 5 database is available here.

PhotosDB._process_database5() reads the Photos.sqlite file to extract all known metadata.

The primary tables of concern are:

Table Name	Description
ZGENERICASSET	The primary table with information on photo assets containing filename, directory, favorite, created date, etc.
ZADDITIONALASSETATTRIBUTES	Additional info on photos including original name, title, etc.
ZPERSON	Names of persons detected in photos
ZDETECTEDFACE	Faces detected in photos
ZGENERICALBUM	Information about albums
Z_26ASSETS	Needed to join albums to photos 1
ZKEYWORD	Information about keywords
ZASSETDESCRIPTION	The photo description (e.g. text typed into Photos by the user)
ZUNMANAGEDADJUSTMENT	Information about edited photos
ZINTERNALRESOURCE	Appears to be information on every internal resource used by Photos; used by osxphotos to find which photos are available locally or in iCloud
ZCLOUDMASTER	Information about photos in iCloud; used to determine which photos have been synched to the cloud
ZUNIFORMTYPEIDENTIFIER	Used to determine the UTI (file type) for photos in the library
Z_PRIMARYKEY	Defines the Core Data entity types in use by the Photos libary

Core Data

Under the hood, Photos.app uses Core Data to mediate access to the Photos library which is the source of the Z prefix on table and column names. Of particular note is the Z_PRIMARYKEY table which defines all the entities of the data model. Every record has an entity id Z_ENT, name Z_NAME, parent entity Z_SUPER, and its max record ID Z_MAX. Each root entity (Z_SUPER = 0) has a corresponding sqlite table named Z${Z_NAME} which contains all the records for itself and any child entities. For example, the ZGENERICALBUM table contains all the 'Album', 'Folder', 'ImportSession', etc records.

Albums

Album Sort Order

In Catalina, the table Z_26ASSETS maps albums to photos. It has 3 columns:

• Z_26ALBUMS: foreign key to ZGENERICALBUM
• Z_34ASSETS: foreign key to ZGENERICASSET
• Z_FOK_34ASSETS: while this appears to be a foreign key I think it's actually a sort order (see discussion below)

If you add photos to an album, the value for Z_FOK_34ASSETS for each photos begins at 2048 then increments by 1024 (2^10) for each photo added, e.g. 2048, 3072, 4096, 5120. In the screenshot below, album with Z_26ALBUMS = 64 (ZGENERICALBUM.Z__PK = 64) has 4 photos added one at a time but not manually sorted:

In the screenshot below, the last photo (Z_34ASSETS = 2) was manually re-ordered to the beginning of the album and has a new value for Z_FOK_34ASSETS of 1024 which places it before the previous first photo with value of 2048.

If you change the sort order manually, the value of Z_FOK_34ASSETS of the photo(s) you manually moved changes so that if ordered by Z_FOK_34ASSETS, the photos will be arranged in the new order.

See also the discussion in #184

Import Sessions

Import sessions are stored in ZGENERICALBUM with 'ZKIND = 1506. When a photo is imported, the value ZGENERICASSET.ZIMPORTSESSION is set to the primary key of the corresponding import album in ZGENERICALBUM. ZIMPORTSESSIONmay benull` meaning there is no associated import session.

In Photos 5, the import order is stored in a similar manner as the discussion above on album sort order. The value in ZGENERICASSET.Z_FOK_IMPORTSESSION defines the sort order for an import session. In Photos 6, ZGENERICASSET is renamed ZASSET and it no longer contains the the Z_FOK_IMPORTSESSION column. I've not yet determined how Photos 6 stores the import order.

ZINTERNALRESOURCE

Appears to contain additional information on every resource (e.g. original, edited, thumbnail) in the database.

• ZDATASTORESUBTYPE: type of the resource
  • 0: thumbnail
  • 1: original
  • 2: edited
  • 3: unknown
  • 17: raw
• ZUNIFORMTYPEIDENTIFIER: UTI of the resource
• ZLOCALAVAILABILITY: resource is available in local library
• ZREMOTEAVAILABILITY: resource is available in cloud library
• ZASSET: foreign key to ZADDITIONALASSETATTRIBUTES.ZASSET

TODO Document the following

• Joining assets
• Smart albums (e.g. 'FetchingAlbum')
  • ZUSERQUERYDATA looks like an NSPredicate
• Import sessions
• Folder hierarchy
• Sorting

---

1: This is actually a dynamically named table of the form Z_##ASSETS. The ## is the Z_ENT value for the record with Z_NAME = 'Album' from the Z_PRIMARYKEY table. It always appears to be 26 for the macOS Photos library but in this post it is Z_23ASSETS for an iOS Photos library.