Any tips?

[hepcat72]

Hi,

I've been working for the last week or 2 to try and write a script to synch reminders with my raspberry pi in order to trigger automations based on reminder completion events. I had tried a Siri Shortcut (which succeeds reasonably quickly, but it annoying to repeatedly run) and various versions of an AppleScript, which runs much slower, but is unreliable, due to tccd and the size of my reminders database.

Today I decided to back up a step and looked in the caldav directories and discovered that the reminder data had moved to an sqlite DB. I just worked out which fields to query and how to retrieve the list names. I did a quick google on ZCKIDENTIFIER and got 1 result - this repo.

I was just wondering if you have any tips on dealing with the sqlite data. I don't know what issues I might encounter (like I did with my previous attempts). Like, will queries to the DB error out when the screen is locked, or any such unexpected stuff like that?

Cheers, Rob

[hepcat72]

Oh gee, like how to sift through the duplicates to get the latest version of a reminder...

[davidlday]

Hey Rob - I didn't encounter anything unexpected when running queries, but I also was very focused on some specific queries. I also made sure I opened the db in read only mode to avoid any accidental updates or deleted. If you haven't discovered them already, two files that might help:

https://github.com/davidlday/RemindersWidget/blob/master/queries.sql - just my scratch pad of queries while trying to figure out what's what. https://github.com/davidlday/RemindersWidget/blob/master/reminders.widget/pending.sh - the actual shell script that runs periodically to get the list of reminders. This script eliminates the duplicates / deleted items.

Bets of luck! Feel free to ask more questions, too. D.

[hepcat72]

Yes, those files were extremely useful files - saved me a lot of time. You'd already worked out the hard part.

The one thing I couldn't figure out was how to get the single latest version of a reminder. There are numerous copies of some reminders (probably because they're recurring - or perhaps also due to edits) - and I want to be able to catch completion (and potentially other) events to use as automation triggers, so I am selecting all incomplete plus all modified (since the last check), i.e. the union of those 2 sets.

My assumption was that if I could get a set of unique reminders, any completed ones would represent completion events since the last time I checked. I no longer think that that assumption is necessarily true, given an already completed event could be otherwise modified, but I figured it would be "close enough", so I spent yesterday trying to figure out SQL that would get me that unique representation of "the latest version" to use in that logic.

I couldn't figure it out. My guess is that you have to construct a series of queries to trace the edits hierarchically of each (unique) reminder individually or something, and decided that must be why AppleScript is so slow when I ask for it to give me (any) reminders. Because there's no field in the reminders "table" that uniquely identifies all versions of each entry belonging to the same reminder, it has to traverse the tree each time. That's my guess anyway. My reminders database is huge. 230Mb spanning heavy reminders usage over a decade.

Thus, I decided that I would assume each reminder with the same name & in the same list is distinct and that I would write some script around the results to reduce multiple (duplicate) entries down to 1 (actually - I intend to use the ones with different values to identify specific events (e.g. "completion events")).

I still have yet to write the script. Right now, I just have the SQL worked out to get the union I want, with the duplicates.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[hepcat72]

I'm running into an issue where my obtained due date is wrong for a reminder titled "Recycling is every other Monday". I cannot seem to work out the logic of obtaining the correct due date from the latest entries. Maybe you know how to determine it? The next due date displayed in the reminders app for this recurring event, whose only incomplete entry returned is August 22nd, 2021 (or 2021-08-22), but the single incomplete reminder's due date is 2021-09-05 and the last modified version of the reminder (which is completed) says the due date is 2021-07-15. (I had accidentally un-completed that one and then re-completed it.) There is one entry that shows the current due date, but it is a completed reminder. I may have also accidentally completed and un-completed it... What logic do you think I could use to get the correct due date from these entries?:

[golrath:~] robleach% sqlite3 "file:/Users/robleach/Library/Reminders/Container_v1/Stores/Data-A6A78E21-BA53-4867-B651-08569C902142.sqlite?mode=ro" -cmd ".mode tabs" "SELECT (978307200 + TASK.ZLASTMODIFIEDDATE) as lmdEpochSecs, TASK.ZPRIORITY AS priority, replace(replace(replace(TASK.ZTITLE1, X'0A', '\n'), X'0D', '\r'), X'09', '\t') AS title, LIST.ZNAME1 AS list, replace(replace(replace(TASK.ZNOTES, X'0A', '\n'), X'0D', '\r'), X'09', '\t') AS notes, TASK.ZCOMPLETED as completed, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZCOMPLETIONDATE),'unixepoch') as completionDate, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZCREATIONDATE),'unixepoch') as creationDate, TASK.ZDISPLAYDATEISALLDAY as isAllday, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZDISPLAYDATEDATE),'unixepoch') as dueDate, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZLASTMODIFIEDDATE),'unixepoch') as modificationDate, TASK.ZFLAGGED as flagged, TASK.ZCKIDENTIFIER as reminderID, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZACKNOWLEDGEDDATE),'unixepoch') as ackDate, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZENDDATE),'unixepoch') as endDate, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZDUEDATE),'unixepoch') as duedueDate, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZSTARTDATE),'unixepoch') as startDate FROM ZREMCDOBJECT TASK LEFT JOIN ZREMCDOBJECT LIST on TASK.ZLIST = LIST.Z_PK WHERE LIST.Z_ENT = 21 AND LIST.ZMARKEDFORDELETION = 0 AND TASK.ZMARKEDFORDELETION = 0 AND (TASK.ZCOMPLETED = 0 or modificationDate > '2021-08-03T09:25:08') ORDER BY CASE WHEN TASK.ZDISPLAYDATEDATE IS NULL THEN 1 ELSE 0 END, TASK.ZDISPLAYDATEDATE, TASK.ZPRIORITY;" | grep "Recycling is " | sort -n | perl -e 'while(<>){chomp;@x=split(/\t/);$x[0]=localtime($x[0]);print(join("\t",@x),"\n")}'
lmd priority    title   list    notes   completed   completionDate  creationDate    isAllday    displayDate modificationDate    flagged reminderID  acknowledgedDate    endDate dueDate startDate
Sun Aug  8 21:57:44 2021    0   Recycling is every other Monday ToDo Home Recurring     1   2021-08-09T01:57:44 2017-09-18T16:59:00 0   2021-08-08T22:00:00 2021-08-09T01:57:44 0   AA6C3A21-45F8-4A21-8A29-E20F00C6EFE3            2021-08-08T22:00:00 2021-08-09T04:00:00
Sun Aug  8 21:58:33 2021    0   Recycling is every other Monday ToDo Home Recurring     1   2021-08-09T01:58:33 2017-09-18T16:59:00 0   2021-08-22T22:00:00 2021-08-09T01:58:33 0   E324BFD0-4EC8-41D3-8EF2-0BEA96EC05F0            2021-08-22T22:00:00 2021-08-23T04:00:00
Sun Aug  8 21:58:33 2021    0   Recycling is every other Monday ToDo Home Recurring     0       2017-09-18T16:59:00 0   2021-09-05T22:00:00 2021-08-09T01:58:33 097F85B66-E979-5226-9E87-794C0901AEB2           2021-09-05T22:00:00 2021-09-06T04:00:00
Sun Aug 15 09:19:03 2021    0   Recycling is every other Monday ToDo Home Recurring     1   2021-08-15T13:19:03 2017-09-18T16:59:00 0   2017-07-17T00:00:00 2021-08-15T13:19:03 0   EAA3CD20-D4A7-59F6-B3BF-DD7A32175810            2017-07-17T00:00:00 2017-07-17T04:00:00
Sun Aug 15 12:53:31 2021    0   Recycling is every other Monday ToDo Home Recurring     1   2021-08-15T16:53:31 2017-09-18T16:59:00 0   2121-07-15T22:00:00 2021-08-15T16:53:31 0   0CBB413B-7BD2-4D4E-80EF-612BF08E2DAD            2021-08-08T22:00:00 2021-08-09T04:00:00

I added headers for convenience. The correct due date should be 2021-08-22T22:00:00.

[hepcat72]

OK. Never mind. Turned out to be a synch issue. My computer's reminder's app disagreed with my phone. Turning the synch off & on solved it.

[hepcat72]

I discovered a new edge-case issue today. Turns out I had 2 reminders with the same title in the same list (though different sub-lists). One had notes and the other didn't.

In my script that detects changes to reminders, I associate separate DB entries using list and title in order to know when separate recurrence entries are from the same reminder. But in this case, it thinks those 2 separate entries are the same, because they have the same list & title, so it always detects a change to the notes field every time it runs.

I was trying to think of a different or other value or strategy I could use to associate reminders stemming from the same actual created reminder, but every field I could include that differentiates single reminders breaks the association of recurring reminders. E.g. the ID is different between recurrence entries, as is created date.

I could just filter out incomplete duplicate reminders, but it just irks me that I can't identify discrete reminders where separate recurrences originate from the same reminder.