Open theuberuser opened 7 months ago
AFAICT tags aren't in the public API https://developer.apple.com/documentation/eventkit/ekevent
Semi-related:
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 https://github.com/keith/reminders-cli/issues/72#issuecomment-1829203825
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? TheZNAME1
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 https://github.com/keith/reminders-cli/issues/72#issuecomment-1829214924
In /Users/devalias/Library/Reminders/Container_v1/Stores/Data-SOME-UUID-TYPE-THING.sqlite
:
ZREMCDHASHTAGLABEL
table:
Z_ENT
: seems to be 3
for all of the hashtag entriesZNAME
/ ZCANONICALNAME
: seem to contain the text of my hashtagsZREMCDREMINDER
table:
Z_PK
: ?reminder primary key?Z_ENT
: seems to be 32
for all of the reminder entriesZCOMPLETED
: 1
for completed, 0
for not completedZFLAGGED
: ?probably 1
if flagged, otherwise 0
?ZPRIORITY
: ?reminder priority?ZLIST
: ?ID of list the reminder relates to?ZTITLE
: Main reminder textZNOTES
: Reminder notesZREMCDOBJECT
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 smartlistsZNAME3
: This seems to contain the name I assigned to the smartlistsZSMARTLISTTYPE
: 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
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 listZSORTINGSTYLE
: eg. manual
24
: seems to (at least partially?) correlate to hashtags for reminders
ZNAME1
: seems to contain the text of a hashtag for the reminder22
: ???21
: ???18
: ?URL related?
ZUTI
(eg. public.url
), ZURL
, etc; may be related to this..ZURL
: reminder URL field17
: ?attachment related?
ZUTI
(eg. public.jpeg
), ZFILENAME
(eg. 24B9CB35-CC7F-45F0-B52F-8BED9C2F2769-732-00055908B19E5135.jpeg
), ZSHA512SUM
, etc; may be related to this..10
: ?location related?
ZLATITUDE
/ ZLONGITUDE
/ ZADDRESS
/ ZLOCATIONUID
/ ZTITLE
etc are related to this9
: ?reminder date/time related?
ZALARM
/ ZDATECOMPONENTSDATA
/ etc may be related to this7
: ???
ZREMINDER
/ ZTRIGGER
/ Z8TRIGGER
/ etc may be related to this6
: ?settings/flags related?
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._
_Edit 4: I wrote a basic python script to extract the CoreData models + hierarchy from the Z_PRIMARYKEY
table and display them as an indented markdown list: https://gist.github.com/0xdevalias/ccc2b083ff58b52aa701462f2cfb3cc8#file-extract-coredata-model-hierarchy-py_
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:
Z_ENT
18
that contains the URL in ZURL
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)
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
Tags were introduced in the Reminders app in macOS Monterey and can be extremely useful when managing various types of tasks. I think adding this to the reminders CLI would make it all the more powerful than it already is, especially when used with scripts and automation.
Usage examples:
NOTE: Adding support for multiple tags would be ideal but not required
These are just some basic examples but you can probably see how useful this could be.
Also, yes, I did see that there was one comment about this already on https://github.com/keith/reminders-cli/issues/59, but, I felt it was worth explicitly calling it out in a separate issue, so as not to get lost. If you'd rather work on this in the already open issue for enhancements, then please feel free to delete this one.