0xdevalias/_accessing-apple-reminders-data-macos.md

Accessing / Exporting Apple's Reminders Data on macOS

Some notes on accessing / exporting Apple's Reminders data on macOS.

General

https://github.com/keith/reminders-cli
- A simple CLI for interacting with macOS reminders
https://github.com/davidlday/RemindersWidget
- RemindersWidget An Übersicht widget to display your pending Reminders tasks on the desktop.
- https://github.com/davidlday/RemindersWidget/blob/master/zremcdobject.sql
- https://github.com/davidlday/RemindersWidget/blob/master/queries.sql

Reminders SQLite CoreData Models / Tables

Core Data

https://fatbobman.com/en/
- https://fatbobman.com/en/posts/tables_and_fields_of_coredata/
  - How Core Data Saves Data in SQLite
  - Core Data is an object graph framework that has data persistence capabilities. The data organization structure of the same object graph in different persistence storage types (SQLite, XML) can vary greatly. If you have ever browsed through the SQLite database file generated by Core Data, you will have seen many strange tables and fields. This article will introduce these tables and fields, which may help you understand some of the confusion
  - If you have enabled Core Data’s debugging information output in your project, you can directly find the database path address at the top of the debugging information.
```
-com.apple.CoreData.CloudKitDebug 1
```
  - Basic tables and fields refer to the tables (non-entity tables) created by Core Data in SQLite database to meet basic functionalities without enabling other additional features (e.g. persistent history tracking, Core Data with CloudKit), and the special fields created in entity tables.
  - The following figure shows the database structure of a project created with Xcode Core Data template (with only one entity Item and one attribute timestamp), where the table corresponding to the entity Item in SQLite is ZITEM.
  - Core Data follows the following rules to convert entities in the data model to SQLite format:
    - The name of the table corresponding to an entity is Z + the entity name (all uppercase). In this example, it is ZITEM.
    - The field corresponding to an attribute in the entity is Z + the attribute name (all uppercase). In this example, it is ZTIMESTAMP.
    - For attributes that have the same uppercase name (attributes are case-sensitive in their definition), a number will be added to other properties with the same name. For example, if Item has two attributes called timestamp and timeStamp, two fields will be created in the table: ZTIMESTAMP and ZTIMESTAMP1.
    - Three special fields are added to each entity table: Z_PK, Z_ENT, and Z_OPT (all of INTEGER type).
    - If the entity definition contains a relationship, a corresponding field will be created in the entity table or a corresponding intermediate relationship table will be created (see details below).
  - Z_ENT Field: Each entity table is registered in the Z_PRIMARYKEY table (details below). This field is equivalent to the registered Z_ENT field. It can be viewed as the ID of the table.
  - Z_PK Field: An integer that starts from 1 and increments by 1. It can be viewed as the primary key of the table. Z_PK + Z_ENT (primary key + table ID) is the key for Core Data to find specific entries in a particular SQLite data file.
  - Z_OPT Field: The version number of the data record. Each modification to the data will cause this value to increment by one.
  - Z_PRIMARYKEY Table: The Z_PRIMARYKEY table is the foundation for locating data via Z_PK + Z_ENT. Its main functions are:
    - Registering tables created by Core Data in SQLite (all tables that need to be located via Z_PK + Z_ENT, excluding Z_PRIMARYKEY, Z_METADATA, and Z_MODELCACHE)
    - Marking relationships between entities (only for abstract entities)
    - Recording the names of entities (as defined in the data model)
    - Recording the current maximum Z_PK value used for each registered table
  - Z_ENT: The ID of the table. Entity tables start from number 1, while tables created for other system functions start from number 16000. The following diagram shows the correspondence between Z_ENT in the Memo table and the Z_Ent field recorded in the Z_PRIMARYKEY table.
  - Z_NAME Field: The name of the entity in the data model (case-sensitive), used for reverse lookup of corresponding data from the URL (see specific application below).
  - Z_SUPER Field: If the entity is a sub-entity of an entity (Abstract Entity), this value corresponds to the Z_ENT of its parent entity. 0 indicates that the entity has no parent entity. The following figure shows the situation of Z_SUPER when Item is an abstract entity and ItemSub is its sub-entity.
  - Z_MAX Field: Marks the last used Z_PK value for each registry table. When creating new entity data, Core Data finds the corresponding entity’s last used Z_PK value (Z_MAX) from the Z_PRIMARYKEY table, adds one to this value, and uses it as the new record’s Z_PK value, and updates the corresponding entity’s Z_MAX value.
  - Z_METADATA Table: The Z_METADATA table records information about the current SQLite file, including version, identifier, and other metadata.
  - Z_UUID Field: The ID identifier (UUID type) of the current database file. This value can be obtained through the managed object coordinator. When converting NSManagedObjectID to a storable URL, this value represents the corresponding persistent storage.
  - Z_PLIST Field: Stores metadata about persistent storage in Plist format (excluding the persistent storage UUID identifier). Developers can read or add data through the persistent storage coordinator. If necessary, developers can also save data unrelated to the database in it (which can be considered as an alternative usage of the Core Data database file to save program configurations).
  - Z_VERSION Field: The specific purpose is unknown (presumably the SQLite format version of Core Data), which is always 1.
  - Z_MODELCACHE Table: Although Core Data reserves the signature information of the current data model version used in Z_PLIST in the Z_METADATA table, because the content of Z_PLIST can be changed, in order to ensure that the data model version used by the application is completely consistent with the SQLite file, Core Data saves a cache version of the data model corresponding to the current SQLite data in the Z_MODELCACHE table (a variant of mom or omo). The cache data in Z_MODELCACHE and the data model signature in metadata together provide assurance for data model version validation and version migration.
  - Core Data automatically adds an auto-increment primary key data for each new record through the Z_MAX corresponding to the entity table. Therefore, when defining a data model in Core Data, developers do not need to define a primary key attribute for the entity (in fact, they cannot create an auto-increment primary key either).
  - The NSManagedObjectID of a managed object is composed of the database ID, table ID, and primary key in the entity table. In SQLite, these correspond to the Z_UUID, Z_ENT, and Z_PK fields. By converting the NSManagedObjectID to a URL that can be stored, its composition can be clearly displayed.
  - Core Data uses the feature of locating records in the same database with only Z_ENT + Z_PK to mark relationships between different entities. To save space, Core Data only stores the Z_PK data of each relationship record, while Z_ENT is obtained directly from the Z_PRIMARYKEY table by the data model.
    
    The rules for creating relationships in the database are:
    - One-to-Many: No new fields are created on the “one” side, while a new field is created on the “many” side, corresponding to the Z_PK value of the “one” side. The field name is Z + relationship name (uppercase).
    - One-to-One: New fields are added on both ends of the relationship, corresponding to the Z_PK values of the corresponding data.
    - Many-to-One: No new fields are added on either end of the relationship. Instead, a new table representing the many-to-many relationship is created, and the Z_PK values of the two sides of the relationship are added to the table row by row.
  - When abstract entities are enabled, in addition to recording the Z_PK value that corresponds to the relationship data, a field is also added to record which Z_ENT the data belongs to specifically (parent entity or a certain sub-entity).
  - In CoreData, if the data storage format is SQLite (most developers use this method) and the persistent history tracking function is enabled, any changes to the data in the database (deletion, addition, modification, etc.) will trigger a system notification of “database changes” to the application that has called the database and registered for the notification. In recent years, with the application of App Group, widgets, Core Data with CloudKit, Core Data in Spotlight, and other functions, more and more Core Data applications have actively or passively enabled the persistent history tracking option. After enabling this function (desc.setOption(true as NSNumber,forKey: NSPersistentHistoryTrackingKey)), Core Data will create three new tables in SQLite to manage and record transactions and register information about these three tables in the Z_PRIMARYKEY table.
  - Z_ATRANSACTIONSTRING Table: In order to distinguish the source of transactions, the creator of a transaction needs to set the transaction author for the managed object context. Core Data gathers all transaction author information in the Z_ATRANSACTIONSTRING table. If the developer has also set a name for the context, Core Data will create a record for that context name.
  - Z_ATRANSACTION Table: You can understand a persistent history tracked transaction as a persistence process in Core Data (such as calling the save method of a context). Core Data saves information related to a transaction in the Z_ATRANSACTION table. The most important information included is the time the transaction was created and the transaction author.
  - ZAUTHORTS Field: Corresponds to Z_PK of transaction author in Z_ATRANSACTIONSTRING table. In the above image, it corresponds to fatbobman whose Z_PK in Z_ATRANSACTIONSTRING is 1.
  - ZCONTEXTNAMETS Field: If a name is set for the context that created the transaction, this field corresponds to the Z_PK record of the context name in the Z_ATRANSACTIONSTRING table. In the above image, it corresponds to viewContext.
  - ZTIMESTAMP Field: The creation time of the transaction.
  - ZQUERYGEN Field: If a lock query token (NSQueryGenerationToken) is set for the managed object context, the transaction record will also save the query token at that time in the ZQUERYGEN field (BLOB type). In the figure below, Item and Tag have a many-to-many relationship, and Core Data creates the Z_2TAGS table to manage the relationship data.
  - Z_ACHANGE Table: In a transaction, there are usually several data operations (create, modify, delete). Core Data stores each data operation in the Z_CHANGE table and associates it with a specific transaction through Z_PK.
  - ZCHANGETYPE Field: Data operation type: 0 for new, 1 for update, 2 for delete
  - ZENTITY Field: Z_ENT of the corresponding entity table for the operation
  - ZENTITYPK Field: Z_PK of the corresponding data record in the entity table for the operation
  - ZTRANSACTIONID Field: Z_PK of the transaction corresponding to the operation in the Z_ATRANSACTION table
  - Creating Transactions In Core Data, the creation of transactions in the persistent history tracking is automatically done. The process is roughly as follows:
    - Get Z_MAX from Z_PRIMARYKEY table for Z_ATRANSACTION
    - Create a new transaction record in Z_ATRANSACTION using Z_PK (Z_MAX + 1) + Z_ENT (the corresponding Z_ENT in Z_PRIMARYKEY for the transaction table) + author ID + timestamp, and update Z_MAX
    - Get Z_MAX from Z_ACHANGE
    - Create data operation records one by one in Z_ACHANGE
  - Querying Transactions Since only the transaction creation timestamp is saved in the database, regardless of the query method used (Date, NSPersistentHistoryToken, NSPersistentHistoryTransaction), it will ultimately be converted into a comparison of timestamps.
    - Timestamp later than the last query time of the current application
    - Author is not the author of the current app or other system function author
    - Get all Z_CHANGE records that meet the above conditions
  - Merging Transactions The data operation records (Z_ACHANGE) extracted from the transaction contain complete operation types, corresponding instance data positions, and other information. Entity data (Z_PK + Z_ENT) is extracted from the database according to the information and merged (converted to NSManagedObjectID) into the specified context.
  - Delete transaction
    - Query and extract transactions with a timestamp earlier than the last query time of all authors (including the current application author, but excluding system function authors)
    - Delete the above transactions (Z_ATRANSACTION) and their corresponding operation data (Z_ACHANGE).
  - If your application uses Core Data with CloudKit, you will get further surprises (😱) when browsing the SQLite data structure. Core Data will create more tables to handle synchronization with CloudKit. Considering the complexity and length of the tables, we will not continue to expand on them. However, with the foundation above, it is not very difficult to understand their purpose. The following figure shows the system tables added to SQLite after enabling private database synchronization These tables mainly record information about the CloudKit private domain, last synchronization time, last synchronization token, export operation log, import operation log, data to be exported, Core Data relationship mapping table with CloudKit, CKRecordName corresponding to local data, complete CKRecord mirror image of local data in the shared public database, and so on. As Core Data functionality continues to increase, we may see more system function tables in the future.
- https://fatbobman.com/en/posts/mastering-relationships-in-core-data-fundamentals/
  - Mastering Relationships in Core Data: Fundamentals
  - In this article, we will delve into the basic concepts of relationships in Core Data, while providing important guidance and suggestions for implementing these relationships. In this article, we will explore the fundamental knowledge related to relationships. These concepts are crucial in Core Data and are also applicable to its successor framework - SwiftData.
  - In the world of Core Data, relationships act as bridges connecting entities, determining how one entity affects another. In most cases (except for abstract entities), each entity definition in Core Data corresponds to a table in an SQLite database. Therefore, from the perspective of underlying implementation, relationships in Core Data can be seen as a mechanism for establishing connections and operations between different tables.
  - Under this framework of bidirectional relationships, we can further classify relationships into three main types:
    - One-to-One Relationships:
      
      Definition: A single instance in one entity (A) is associated with a single instance in another entity (B).
      
      Use: Applicable when there is a unique and direct connection between two entities.
      
      Example: The relationship between a person (Person) and their passport (Passport).
    - One-to-Many Relationships:
      
      Definition: A single instance in one entity (A) is associated with multiple instances in another entity (B).
      
      Use: When one entity can establish connections with multiple instances of another entity.
      
      Example: A user (User) and their multiple posts (Posts).
    - Many-to-Many Relationships:
      
      Definition: Multiple instances in one entity (A) are interrelated with multiple instances in another entity (B).
      
      Use: Suitable for scenarios where instances between two entities can be freely combined and associated.
      
      Example: The relationship between articles (Article) and tags (Tag), where an article can have multiple tags, and different articles can be marked with the same tags.
  - In Core Data, to-Many relationships are categorized into unordered and ordered types. By default, to-Many relationships are unordered (corresponding to the NSSet type, ensuring the uniqueness of objects but not their order). Developers can make these relationships ordered by selecting the ordered option in the data model (corresponding to the NSOrderedSet type).
  - In essence, for the side of an ordered relationship, Core Data creates an index-like internal attribute (field). For example, in a one-to-many ordered relationship between Item and Tag, Core Data would add a Z_FOK_ITEM field to the corresponding table for Tag and populate it with numbers in order. To avoid frequently updating all indices due to position adjustments, Core Data reserves a certain numeric space between two adjacent positions.
  - Core Data utilizes the feature in SQLite that allows records to be located within the same database using just Z_ENT + Z_PK, to establish relationships between different entities. To save space, Core Data only saves the Z_PK data of each relationship record, with Z_ENT being directly obtained by the data model from the Z_PRIMARYKEY table.
    
    The rules for creating relationships in the database are as follows:
    - One-to-Many Relationships:
      
      In the "one" side of the relationship, no new field is created. However, on the "many" side, a new field is created for the relationship, which stores the Z_PK value of the "one" side. The field's name is typically Z followed by the relationship name (in uppercase).
    - One-to-One Relationships:
      
      Both ends of the relationship add new fields, each storing the Z_PK value of the data on the other side.
    - Many-to-Many Relationships:
      
      No new fields are added to either end of the relationship. Instead, a new associative table is created to represent this many-to-many relationship. This table adds the Z_PK values of data from both ends of the relationship in each row.
      
      For example, in the case where there is a many-to-many relationship between Item and Tag, Core Data creates a Z_2TAGS table to manage the relationship data between these two entities.
- https://fatbobman.com/en/posts/from-data-model-construction-to-managed-object-instances-in-core-data/
  - Exploring CoreData - From Data Model Creation to Managed Object Instances
  - When creating a new project with Core Data included, Xcode will automatically create a data model file called ProjectName.xcdatamodeld in the project. Alternatively, we can manually create a Core Data data model file in the project with a file extension of .xcdatamodeld.
  - Xcode stores all the information created by the developer in the model editor in xcdatamodeld. Specifically, xcdatamodeld is a directory commonly referred to as a “Core Data Model Bundle”. It is a special bundle used to store and manage the data model information for Core Data. It contains one or more data model files (.xcdatamodel) as well as other information related to the data model. Xcode creates a separate VersionName.xcdatamodel bundle for each model version within the xcdatamodeld directory.
  Now, open the content file in the xcdatamodel with a text editor, and you will see that all the model information of the current version is saved in XML format.
  - Xcode, when compiling a project, will include the .xcdatamodel directory as a momd bundle in the application’s resources. The .xcdatamodel bundle will be compiled into a binary file with the mom extension. This reduces the space occupied and improves loading speed. This is also why we need to set the extension to momd when loading the model file using code. Developers should understand that the model file created through Xcode’s model editor is just a structured representation of the model, not a programmatic representation.
- https://fatbobman.com/en/posts/derivedandtransient/
  - How to use Derived and Transient Properties in Core Data
  - The values of derived data are calculated and updated directly by Sqlite.
    
    The calculation of derived values is one of the few operations in Core Data that uses Sqlite’s built-in mechanisms directly, rather than being calculated by Swift (or Objective-C) code.
    
    For example, the expression now(), when used in Core Data, will generate Sql code similar to the following when creating a data table:
```
CREATE TRIGGER IF NOT EXISTS Z_DA_ZITEM_Item_update_UPDATE AFTER UPDATE OF Z_OPT ON ZITEM FOR EACH ROW BEGIN UPDATE ZITEM SET ZUPDATE = NSCoreDataNow() WHERE Z_PK = NEW.Z_PK; SELECT NSCoreDataDATriggerUpdatedAffectedObjectValue('ZITEM', Z_ENT, Z_PK, 'update', ZUPDATE) FROM ZITEM WHERE Z_PK = NEW.Z_PK; END'
```
    Code for @count:
```
UPDATE ZITEM SET ZCOUNT = (SELECT IFNULL(COUNT(ZITEM), 0) FROM ZATTACHEMENT WHERE ZITEM = ZITEM.Z_PK);
```
- https://fatbobman.com/en/posts/persistenthistorytracking/
  - Using Persistent History Tracking in CoreData
  - Update February 2022: I have rewritten the code and organized it into a library PersistentHistoryTrackingKit for everyone’s convenience.
    - https://github.com/fatbobman/PersistentHistoryTrackingKit
      - A library for managing Core Data's Persistent History Tracking
      - When Persistent History Tracking is enabled, your application will begin creating transactions for any changes that occur in Core Data Storage. Whether they come from application extensions, background contexts, or the main application.
        
        Each target of your application can fetch the transactions that have occurred since a given date and merge them into the local storage. This way, you can keep up to date with changes made by other persistent storage coordinators and keep your storage up to date. After merging all transactions, you can update the merge date so that the next time you merge, you will only get the new transactions that have not yet been processed.
        
        The Persistent History Tracking Kit will automate the above process for you.
  - In CoreData, if your data storage format is Sqlite (which most developers use) and you’ve enabled persistent history tracking, any changes in the database (deletions, additions, modifications, etc.), will trigger a system alert notifying apps that have registered for this notification of the change in the database.
  - Persistent history tracking currently has the following application scenarios:
    - In the App, merge the data changes generated by the App’s batch operations (BatchInsert, BatchUpdate, BatchDelete) into the current view context (ViewContext).
    - In an App Group, when an App and an App Extension share a database file, the modifications made by one member in the database are promptly reflected in the view context of another member.
    - When synchronizing your CoreData database with CloudKit using PersistentCloudKitContainer.
    - When using NSCoreDataCoreSpotlightDelegate.
  - After enabling Persistent History Tracking for persistent storage, your application will start creating transaction records for any changes that occur in CoreData’s persistent storage. These transactions are meticulously recorded regardless of how they are generated (whether through context or not), or by which App or Extension. All changes are saved in your Sqlite database file. Apple has created several tables in Sqlite to record information corresponding to the transactions.
    - ACHANGE
    - ATRANSACTION
    - ATRANSACTIONSTRING
  - If you are interested, you can also take a look at the contents of these tables, which Apple has organized very compactly. ATRANSACTION contains the transactions that have not yet been cleared, ATRANSACTIONSTRING contains the string identifiers for authors and contextName, and ACHANGE is the changed data. This data is ultimately converted into corresponding ManagedObjectIDs.
  - All Transactions are stored in the Sqlite file, which not only occupies space but also affects the access speed of Sqlite as the records increase. We need to establish a clear cleaning strategy to delete the processed Transactions.
  - CoreData will automatically handle and clear Transactions generated by CloudKit synchronization, but if we accidentally delete CloudKit Transactions that have not yet been processed by CoreData, it may lead to database synchronization errors, and CoreData may clear all current data and attempt to reload data from remote.
  Therefore, if you are using Persistent History Tracking on PersistentCloudKitContainer, be sure to only clear Transactions generated by members of the App Group.
https://techblog.lycorp.co.jp/en/exploring-best-practices-for-core-data-from-the-sqlite-perspective
- Copying a SQLite database is not as simple as it seems. Especially after Apple changed the default journal mode for Core Data SQLite stores to Write-Ahead Logging (WAL) in iOS 7 and OS X Mavericks 10.9. Compared with the original default journal mode DELETE, two temporary files are added. Simply put, the .wal file can be considered as a transactions container, where all changes are eventually transferred back into the original .sqlite file when a checkpoint operation is triggered. The .shm file is used as part of the mechanism that allows multiple database connections to SQLite databases.
  
  When attempting to copy a SQLite database that uses the default WAL mode, a common mistake is only copying the .sqlite file. As you can guess, the result is that transactions that were previously committed to the database might be lost. Even worse, the database file could become corrupted.
  - https://www.sqlite.org/wal.html
    - SQLite - Write-Ahead Logging

Unsorted

After some investigation it seems that Tags themselves aren't exposed in the API for the EKReminders class

When you say 'tags', what specifically are you referring to? Do you mean the specific 'hashtags' tags assigned to a reminder? Or are you referring to something else?

The rules for smart lists can have many different aspects, not just tags, so even if they aren't currently accessible, the others may be:

Originally posted by @0xdevalias in keith/reminders-cli#72 (comment)

It looks like the underlying sqlite database backing the Reminders app is stored at:

/Users/devalias/Library/Reminders/Container_v1/Stores/Data-SOME-UUID-TYPE-THING.sqlite

Within that I can see the tags information in the ZREMCDHASHTAGLABEL table.

The ZREMCDOBJECT table seems to contain a lot of data, possibly related to the reminders themselves? The ZNAME1 column seems to include some of the tags as well.

The ZREMCDREMINDER table seems to have a lot of the actual reminder data, and seemingly foreign keys to link to some of the other tables.

While I wouldn't personally risk writing to this DB for fear of corrupting it or similar; it might be possible to extract some relevant details from it in a 'read only' mode, that could then be used to filter the reminders returned from the official API's 'in app'.

Originally posted by @0xdevalias in keith/reminders-cli#72 (comment)

In /Users/devalias/Library/Reminders/Container_v1/Stores/Data-SOME-UUID-TYPE-THING.sqlite:

In the ZREMCDHASHTAGLABEL table:

Z_ENT: seems to be 3 for all of the hashtag entries

ZNAME / ZCANONICALNAME: seem to contain the text of my hashtags

In the ZREMCDREMINDER table:

Z_PK: ?reminder primary key?

Z_ENT: seems to be 32 for all of the reminder entries

ZCOMPLETED: 1 for completed, 0 for not completed

ZFLAGGED: ?probably 1 if flagged, otherwise 0?

ZPRIORITY: ?reminder priority?

ZLIST: ?ID of list the reminder relates to?

ZTITLE: Main reminder text

ZNOTES: Reminder notes

etc

In the ZREMCDOBJECT table, columns such as the following look potentially useful:

ZCKIDENTIFIER: ?some sort of UUID?

ZREMINDERIDENTIFIER: ?some sort of UUID?

ZREMINDER3: ?might contain the ID of the reminder the row relates to?

ZHASHTAGLABEL: seems to contain the PK of the hashtag from ZREMCDHASHTAGLABEL

ZDATECOMPONENTSDATA: ?stuff related to the reminder date?

Z_ENT:

30: seems to correlate to the smart lists

Z_FOK_PARENTLIST1: ?Foreign key for the associated parent list?

ZBADGEEMBLEM1: Contains data like: {"Emoji" : "🎥"} that I assigned to the smartlists

ZNAME3: This seems to contain the name I assigned to the smartlists

ZSMARTLISTTYPE: seems to contain things like:

com.apple.reminders.smartlist.today

com.apple.reminders.smartlist.assigned

com.apple.reminders.smartlist.custom

com.apple.reminders.smartlist.flagged

ZSORTINGSTYLE1: eg. manual

ZFILTEREDDATA: json data relating to the smartlist config; eg:

{"hashtags":{"hashtags":["health"]}}

{"date":{"relativeRange":["inNext","1","week"]}}

{"hashtags":{"hashtags":{"include":["to-watch","youtube-aaa","youtube-bbb","youtube-ccc","youtube-ddd"],"exclude":[],"operation":"or"}},"date":{"any":""},"operation":"and"}

26: ???

ZLISTID: seems to contain things like:

com.apple.reminders.smartlist.today

Or sometimes a UUID (eg. in my data: BA189B19-F050-43FD-A76F-115415ED91A2 / 5C703F5C-7ED3-4AC3-B064-7F9C1E01AA95)

25: seems to correlate to normal lists

ZBADGEEMBLEM seems to be the image I assigned to the normal lists (eg. {"Emoji" : "💲"})

ZNAME2 seems to be the name of the normal list

ZSORTINGSTYLE: eg. manual

24: seems to (at least partially?) correlate to hashtags for reminders

ZNAME1: seems to contain the text of a hashtag for the reminder

22: ???

21: ???

18: ?URL related?

Looks like ZUTI (eg. public.url), ZURL, etc; may be related to this..

ZURL: reminder URL field

17: ?attachment related?

Looks like ZUTI (eg. public.jpeg), ZFILENAME (eg. 24B9CB35-CC7F-45F0-B52F-8BED9C2F2769-732-00055908B19E5135.jpeg), ZSHA512SUM, etc; may be related to this..

10: ?location related?

Looks like ZLATITUDE / ZLONGITUDE / ZADDRESS / ZLOCATIONUID / ZTITLE etc are related to this

9: ?reminder date/time related?

Looks like ZALARM / ZDATECOMPONENTSDATA / etc may be related to this

7: ???

Looks like ZREMINDER / ZTRIGGER / Z8TRIGGER / etc may be related to this

6: ?settings/flags related?

I only seem to have a single entry for this type, and it seems to correlate with fields like ZDAALLOWSCALENDARADDDELETEMODIFY / ZDASUPPORTSSHAREDCALENDARS / etc (seemingly at least 10 fields like this seem to correlate with it), as well as ZCKUSERRECORDNAME, ZNAME (iCloud), ZPERSONID (PRIMARY-CLOUDKIT)

That seems to be enough basic info to figure out resolving both this issue, and maybe also #72

Would just need to figure out how to match up the ID/data that the API is currently able to provide, with an ID that can be looked up in the sqlite database; and then extract the associated hashtags/etc.

I was thinking that maybe externalId from reminders show Reminders --sort creation-date --sort-order ascending --format json might have worked.. but it only seems to show up in ZREMCDOBJECT within some fields with LOTS of other IDs in them, so doesn't seem ideal; though ZREMCDREMINDER seems to have a single row match with the ID being in ZCKIDENTIFIER / ZDACALENDARITEMUNIQUEIDENTIFIER, so maybe we can do it that way in like 2 steps..

Edit: Collated/cross-posted the above on the following gist for future reference: https://gist.github.com/0xdevalias/ccc2b083ff58b52aa701462f2cfb3cc8#accessing--exporting-apples-reminders-data-on-macos

Edit 2: Found this cool blog post laying out a lot of the specifics of how the internals of Apple CoreData based SQLite databases are laid out: https://fatbobman.com/en/posts/tables_and_fields_of_coredata/

Edit 3: Based on that blog post, we can see that we can look up what the Z_ENT's represent within the Z_PRIMARYKEY table to see that what 'class'/'superclass' it corresponds to (eg. REMCDObject). Using that method, we can confirm that Z_ENT 30 is REMCDSmartList, 24 is REMCDHashtag, 18 is REMCDURLAttachment, etc.

Originally posted by @0xdevalias in keith/reminders-cli#74 (comment)

Working with the above rough notes (and remembering how SQL joins work), I eventually came up with this, that would seem to extract all of the relevant entries from ZREMCDOBJECT based on the single entry in ZREMINDER that matches the externalId in the data from a command like follows:
⇒ reminders show Reminders --sort creation-date --sort-order ascending --format json | jq '[limit(1;.[])]'
[
  {
    "dueDate": "2023-08-13T14:00:00Z",
    "externalId": "EAA1A308-368E-4F90-B9D5-6E6A12AE7E6D",
    "isCompleted": false,
    "list": "Reminders",
    "priority": 0,
    "title": "Foo Bar Baz"
  }
]
SELECT ZO.*
FROM ZREMCDOBJECT ZO
JOIN (
    SELECT Z_PK
    FROM ZREMCDREMINDER
    WHERE ZCKIDENTIFIER = 'EAA1A308-368E-4F90-B9D5-6E6A12AE7E6D'
    OR ZDACALENDARITEMUNIQUEIDENTIFIER = 'EAA1A308-368E-4F90-B9D5-6E6A12AE7E6D'
) AS ZR ON ZO.ZREMINDER = ZR.Z_PK 
    OR ZO.ZREMINDER1 = ZR.Z_PK 
    OR ZO.ZREMINDER2 = ZR.Z_PK 
    OR ZO.ZREMINDER3 = ZR.Z_PK 
    OR ZO.ZREMINDER4 = ZR.Z_PK 
    OR ZO.ZREMINDER5 = ZR.Z_PK
ORDER BY ZO.Z_ENT;
This gives me 3 rows:

1 row with Z_ENT 18 that contains the URL in ZURL

2 rows with Z_ENT 24 that each contain one of the 2 associated hashtags in ZNAME1
⇒ EXTERNAL_ID='EAA1A308-368E-4F90-B9D5-6E6A12AE7E6D' &&
sqlite3 -readonly -json /Users/devalias/Library/Reminders/Container_v1/Stores/Data-5070B790-D66D-40F7-8F4A-EC8E0FA88F3A.sqlite "
  SELECT ZO.*
  FROM ZREMCDOBJECT ZO
  JOIN (
      SELECT Z_PK
      FROM ZREMCDREMINDER
      WHERE ZCKIDENTIFIER = '$EXTERNAL_ID'
      OR ZDACALENDARITEMUNIQUEIDENTIFIER = '$EXTERNAL_ID'
  ) AS ZR ON ZO.ZREMINDER = ZR.Z_PK
      OR ZO.ZREMINDER1 = ZR.Z_PK
      OR ZO.ZREMINDER2 = ZR.Z_PK
      OR ZO.ZREMINDER3 = ZR.Z_PK
      OR ZO.ZREMINDER4 = ZR.Z_PK
      OR ZO.ZREMINDER5 = ZR.Z_PK
  ORDER BY ZO.Z_ENT;
" | jq 'length'
3
If there were other associated rows, they would obviously have been included as well.

This would allow for a much more robust/complete amount of reminders data to be shown/exported; without needing to wait for the official API's to be updated to support it (and then if/when the API's are updated, these methods could then use the official API)

Originally posted by @0xdevalias in keith/reminders-cli#74 (comment)

Following on from my above research, looking at the sqlite DB that contains the reminders data:

..snip..

Based on that, it should be possible to get a list of all of the normal lists from the ZREMCDOBJECT table like follows:

-- SELECT *
SELECT
  Z_PK,
  Z_ENT,
  ZBADGEEMBLEM,
  ZNAME2,
  ZSORTINGSTYLE
FROM ZREMCDOBJECT
WHERE Z_ENT = '25'

Which for my data, returns 9 entries:

7 active lists
1 (recently) deleted list
1 folder

⇒ sqlite3 -readonly -json /Users/devalias/Library/Reminders/Container_v1/Stores/Data-5070B790-D66D-40F7-8F4A-EC8E0FA88F3A.sqlite "
  SELECT
    Z_PK,
    Z_ENT,
    ZBADGEEMBLEM,
    ZNAME2,
    ZSORTINGSTYLE
  FROM ZREMCDOBJECT
  WHERE Z_ENT = '25'
"
[{"Z_PK":8,"Z_ENT":25,"ZBADGEEMBLEM":null,"ZNAME2":"Reminders","ZSORTINGSTYLE":"manual"},
{"Z_PK":2936,"Z_ENT":25,"ZBADGEEMBLEM":"{\"Emoji\" : \"💲\"}","ZNAME2":"To Buy","ZSORTINGSTYLE":"manual"},
{"Z_PK":2965,"Z_ENT":25,"ZBADGEEMBLEM":"{\"Emoji\" : \"🎓\"}","ZNAME2":"Learning","ZSORTINGSTYLE":"manual"},
{"Z_PK":2966,"Z_ENT":25,"ZBADGEEMBLEM":null,"ZNAME2":"Adulting","ZSORTINGSTYLE":"manual"},
{"Z_PK":2967,"Z_ENT":25,"ZBADGEEMBLEM":null,"ZNAME2":"Quality of Life","ZSORTINGSTYLE":"manual"},
{"Z_PK":3001,"Z_ENT":25,"ZBADGEEMBLEM":null,"ZNAME2":"People","ZSORTINGSTYLE":"manual"},
{"Z_PK":5060,"Z_ENT":25,"ZBADGEEMBLEM":"{\"Emoji\" : \"🛠️\"}","ZNAME2":"Projects","ZSORTINGSTYLE":"manual"},
{"Z_PK":7176,"Z_ENT":25,"ZBADGEEMBLEM":null,"ZNAME2":"Smart Lists","ZSORTINGSTYLE":"manual"},
{"Z_PK":9740,"Z_ENT":25,"ZBADGEEMBLEM":"{\"Emoji\" : \"☀️\"}","ZNAME2":"Daily","ZSORTINGSTYLE":"manual"}]

And then I can access my smart lists as follows:

-- SELECT *
SELECT
  Z_PK,
  Z_ENT,
  ZBADGEEMBLEM1,
  ZNAME3,
  ZSORTINGSTYLE1,
  ZSMARTLISTTYPE,
  ZFILTERDATA
FROM ZREMCDOBJECT
WHERE Z_ENT = '30'

Which for my data, returns 10 entries:

1 com.apple.reminders.smartlist.today
1 com.apple.reminders.smartlist.flagged
1 com.apple.reminders.smartlist.assigned
7 com.apple.reminders.smartlist.custom

⇒ sqlite3 -readonly -json /Users/devalias/Library/Reminders/Container_v1/Stores/Data-5070B790-D66D-40F7-8F4A-EC8E0FA88F3A.sqlite "
  SELECT
    Z_PK,
    Z_ENT,
    ZBADGEEMBLEM1,
    ZNAME3,
    ZSORTINGSTYLE1,
    ZSMARTLISTTYPE,
    ZFILTERDATA
  FROM ZREMCDOBJECT
  WHERE Z_ENT = '30'
"

[{"Z_PK":18,"Z_ENT":30,"ZBADGEEMBLEM1":null,"ZNAME3":null,"ZSORTINGSTYLE1":"manual","ZSMARTLISTTYPE":"com.apple.reminders.smartlist.today","ZFILTERDATA":null},
{"Z_PK":2932,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"🏥\"}","ZNAME3":"Health","ZSORTINGSTYLE1":"manual","ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"hashtags\":{\"hashtags\":[\"health\"]}}"},
{"Z_PK":3125,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"⏱\"}","ZNAME3":"This Week","ZSORTINGSTYLE1":null,"ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"date\":{\"relativeRange\":[\"inNext\",\"1\",\"week\"]}}"},
{"Z_PK":4293,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"🎫\"}","ZNAME3":"Events","ZSORTINGSTYLE1":null,"ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"hashtags\":{\"hashtags\":[\"event\"]}}"},
{"Z_PK":7157,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"📚\"}","ZNAME3":"To Read","ZSORTINGSTYLE1":"manual","ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"hashtags\":{\"hashtags\":[\"to-read\"]}}"},
{"Z_PK":7177,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"🎥\"}","ZNAME3":"To Watch","ZSORTINGSTYLE1":null,"ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"hashtags\":{\"hashtags\":{\"include\":[\"to-watch\",\"youtube-aitrepreneur\",\"youtube-crypto-crew-university\",\"youtube-daveshapiro\",\"youtube-mattvidpro\"],\"exclude\":[],\"operation\":\"or\"}},\"date\":{\"any\":\"\"},\"operation\":\"and\"}"},
{"Z_PK":14539,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"💾\"}","ZNAME3":"Backup","ZSORTINGSTYLE1":"manual","ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"hashtags\":{\"hashtags\":[\"backup\"]}}"},
{"Z_PK":25188,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"🎥\"}","ZNAME3":"To Watch - Dave Shapiro","ZSORTINGSTYLE1":null,"ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"hashtags\":{\"hashtags\":{\"exclude\":[],\"operation\":\"or\",\"include\":[\"youtube-daveshapiro\"]}},\"operation\":\"and\"}"},
{"Z_PK":25191,"Z_ENT":30,"ZBADGEEMBLEM1":null,"ZNAME3":null,"ZSORTINGSTYLE1":null,"ZSMARTLISTTYPE":"com.apple.reminders.smartlist.flagged","ZFILTERDATA":null},
{"Z_PK":25192,"Z_ENT":30,"ZBADGEEMBLEM1":null,"ZNAME3":null,"ZSORTINGSTYLE1":null,"ZSMARTLISTTYPE":"com.apple.reminders.smartlist.assigned","ZFILTERDATA":null}]

Edit: Collated/cross-posted the above on the following gist for future reference: https://gist.github.com/0xdevalias/ccc2b083ff58b52aa701462f2cfb3cc8#accessing--exporting-apples-reminders-data-on-macos

Edit 2: Found this cool blog post laying out a lot of the specifics of how the internals of Apple CoreData based SQLite databases are laid out: https://fatbobman.com/en/posts/tables_and_fields_of_coredata/

Edit 3: Based on that blog post, we can correlate the Z_ENT 30 with the Z_PRIMARYKEY table to see that it represents a REMCDSmartList, which has a superclass of REMCDObject (which explains why it's in that table). We can also see that Z_ENT 25 is a REMCDList (also with a superclass of REMCDObject). Etc

Originally posted by @0xdevalias in keith/reminders-cli#72 (comment)

	#!/usr/bin/env python

	# ChatGPT Ref: https://chat.openai.com/c/24090115-3356-40f3-9c6f-50ee84c6e48f

	# Usage Example:
	# ./extract-coredata-model-hierarchy.py /Users/devalias/Library/Reminders/Container_v1/Stores/Data-5070B790-D66D-40F7-8F4A-EC8E0FA88F3A.sqlite

	import argparse
	import sqlite3

	# Set up argument parsing
	parser = argparse.ArgumentParser(description='Extract and organize model hierarchy from a CoreData SQLite database.')
	parser.add_argument('db_path', help='Path to the CoreData SQLite database file.')

	# Parse arguments
	args = parser.parse_args()

	# Connect to the SQLite database using the provided path
	conn = sqlite3.connect(args.db_path)
	cur = conn.cursor()

	# Execute the SQL query to fetch model data
	cur.execute("SELECT Z_ENT, Z_NAME, Z_SUPER FROM Z_PRIMARYKEY")
	data = cur.fetchall()

	# Close the database connection
	conn.close()

	# Process and organize the data

	# Convert list of tuples into a dictionary for easier processing
	# models = {ent: {'name': name, 'super': super_, 'children': []} for ent, name, super_ in data}
	models = {ent: {'id': ent, 'name': name, 'super': super_, 'children': []} for ent, name, super_ in data}

	# Organize models into a hierarchy
	for ent, model in models.items():
	super_ = model['super']
	if super_ != 0:
	models[super_]['children'].append(model)

	# Function to format the hierarchy as a markdown list
	def format_as_markdown(model, indent=0):
	# markdown = " " * indent + f"- {model['name']}\n"
	# markdown = " " * indent + f"- {model['id']}: {model['name']}\n"

	# Calculate table name for top-level models
	table_name = f" (Table: Z{model['name'].upper()})" if model['super'] == 0 and model['id'] < 16000 else ""
	markdown = " " * indent + f"- {model['id']}: {model['name']}{table_name}\n"

	for child in model['children']:
	markdown += format_as_markdown(child, indent + 1)
	return markdown

	# Generate markdown for top-level models
	markdown = ""
	for model in models.values():
	if model['super'] == 0:
	markdown += format_as_markdown(model)

	print(markdown)