Skip to content

Photos version 5 database

Jump to bottom Edit New page
Rhet Turnbull edited this page Jul 19, 2021 · 25 revisions

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.

photos-z_primarykey

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:

Screenshot 2020-07-05 16 42 44

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.

Screenshot 2020-07-05 16 45 10

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

If you sort albums using the View | Sort menu, ZGENERICALBUM.ZCUSTOMSORTKEY = 1 and:

Sort by newest first: ZGENERICALBUM.ZCUSTOMSORTASCENDING=0

Sort by oldest first: ZGENERICALBUM.ZCUSTOMSORTASCENDING=1

Sort by Title: ZGENERICALBUM.ZCUSTOMSORTKEY=5

If manually sorted, ZGENERICALBUM.ZCUSTOMSORTKEY=0 and the Z_FOK_34ASSETS column is updated.

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

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.

Add a custom footer
Add a custom sidebar
Clone this wiki locally