osxphotos

forked/osxphotos

Fork 0

Table of Contents

Core Data
Albums

Album Sort Order

Import Sessions
ZINTERNALRESOURCE
Shared Photos

Comments and Likes
Dates
Import method
Burst Photos

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 ¹
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.

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. ZIMPORTSESSION may be null 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

Shared Photos

Comments and Likes

Photos in shared albums can contain comments or "likes" from all the users who have been invited to view the shared album.

These are stored in ZCLOUDSHAREDCOMMENT and can be associated with a Photo UUID using the following query:

SELECT 
ZGENERICASSET.ZUUID, -- UUID of the photo
ZCLOUDSHAREDCOMMENT.ZISLIKE, -- comment is actually a "like"
ZCLOUDSHAREDCOMMENT.ZCOMMENTDATE, -- date of comment
ZCLOUDSHAREDCOMMENT.ZCOMMENTTEXT, -- text of comment
ZCLOUDSHAREDCOMMENT.ZCOMMENTERHASHEDPERSONID, -- hashed ID of person who made comment/like
ZCLOUDSHAREDCOMMENT.ZISMYCOMMENT -- is my (this user's) comment
FROM ZCLOUDSHAREDCOMMENT
JOIN ZGENERICASSET ON
ZGENERICASSET.Z_PK = ZCLOUDSHAREDCOMMENT.ZCOMMENTEDASSET
OR
ZGENERICASSET.Z_PK = ZCLOUDSHAREDCOMMENT.ZLIKEDASSET
WHERE ZGENERICASSET.ZUUID = ?

To get the name of the person who made the comment, use ZCLOUDSHAREDCOMMENT.ZCOMMENTERHASHEDPERSONID to find the person's details in ZCLOUDSHAREDALBUMINVITATIONRECORD:

SELECT
ZINVITEEHASHEDPERSONID,
ZINVITEEFIRSTNAME,
ZINVITEELASTNAME,
ZINVITEEFULLNAME
FROM
ZCLOUDSHAREDALBUMINVITATIONRECORD
WHERE
ZINVITEEHASHEDPERSONID = ?
LIMIT 1

Dates

For photos, various dates can be found in the following columns:

ZGENERICASSET.ZMODIFICATIONDATE: on initial import = file modification date
ZGENERICASSET.ZDATECREATED: EXIF Create Date
ZGENERICASSET.ZADJUSTMENTTIMESTAMP: blank on import, even if EXIF Modify Date is set

If adjustment made, ZHASADJUSTMENTS set to 1 and ZADJUSTMENTTIMESTAMP set to time of edit and ZMODIFICATIONDATE updated to a time close to, but not exactly the same as ZADJUSTMENTTIMESTAMP (in my tests, a few seconds after ZADJUSTMENTTIMESTAMP)

Photos modifies ZMODIFICATIONDATE even for photos that have not been edited. This may be the time any data about the Photo was modified by Photos or even photoanalysisd.

On export, Photos does not set the EXIF modify date.

Import method

In Photos 5, referenced photos appear to be identified by ZGENERICASSET.ZSAVEDASSETTYPE:

3: imported by copying to Photos library
4: shared iCloud photo
6: imported by iCloud (e.g. from iPhone)
10: referenced file (not copied to Photos library)

Burst Photos

Burst photos are identified by a burstUUID and a burstPickType. (In Photos 5+ these are called "avalanche" instead of burst. The "pick type" seems to be a bit flag indicating status of images in the burst set:

In Photos <=4, these values are in `RKVersion.burstUuid` and `RKVersion.burstPickType`
In Photos >= 5, these values are in `ZGENERICASSET.ZAVALANCHEUUID` and `ZGENERICASSET.ZAVALANCHEPICKTYPE` (ZASSET table in Photos 6)

The values appear to be:

BURST_NOT_SELECTED = 0b10 # 2: burst image is not selected
BURST_SELECTED = 0b1000 # 8: burst image is selected
BURST_KEY = 0b10000 # 16: burst image is the key photo (top of burst stack)
BURST_DEFAULT_PICK = 0b100 # 4: burst image is the one Photos picked to be key image before any selections made
BURST_UNKNOWN = 0b100000 # 32: this is almost always set with BURST_DEFAULT_PICK and never if BURST_DEFAULT_PICK is not set.  I think this has something to do with what algorithm Photos used to pick the default image

Thus, an unselected burst image has a value of 2. A selected image could be

8: BURST_SELECTED
12: BURST_SELECTED & BURST_DEFAULT_PICK
44: BURST_SELECTED & BURST_DEFAULT_PICK & BURST_UNKNOWN

A key image could be:

16: BURST_KEY
20: BURST_KEY & BURST_DEFAULT_PICK
52: BURST_KEY & BURST_DEFAULT_PICK & BURST_UNKNOWN

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.