Repository navigation
Expand file tree
/
Copy pathremctl_serialization.py
More file actions
495 lines (440 loc) · 17.5 KB
/
Copy pathremctl_serialization.py
File metadata and controls
495 lines (440 loc) · 17.5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
"""Shared reminder serialization helpers for RemCTL."""
from __future__ import annotations
import json
from datetime import datetime, timezone
RECURRENCE_FREQUENCIES = {
0: "daily",
1: "weekly",
2: "monthly",
3: "yearly",
}
# Reminders timestamps count seconds from 2001-01-01T00:00:00Z, itself a UTC midnight.
APPLE_EPOCH_UNIX = 978307200
SECONDS_PER_DAY = 86400
DUE_DATE_DELTA_UNITS = {
0: ("minute", "minutes"),
1: ("hour", "hours"),
2: ("day", "days"),
3: ("week", "weeks"),
4: ("month", "months"),
}
# Core Data numbers entities alphabetically per model version, so a macOS update
# that adds an entity shifts every Z_ENT after it (REMCDAlarm is 15 on some
# systems and 16 on others). These are fallbacks; core_data_entity() reads the
# store's Z_PRIMARYKEY table.
ALARM_ENTITY = ("REMCDAlarm", 15)
ASSIGNMENT_ENTITY = ("REMCDAssignment", 21)
RECURRENCE_RULE_ENTITY = ("REMCDRecurrenceRule", 34)
SHAREE_ENTITY = ("REMCDSharee", 36)
def core_data_entity(db, entity):
# Deliberately uncached: Z_PRIMARYKEY is tiny, and sqlite3.Connection can't be
# weak-referenced, so an id(db)-keyed cache could alias a reused connection id.
name, fallback = entity
try:
row = db.execute("SELECT Z_ENT FROM Z_PRIMARYKEY WHERE Z_NAME = ?", (name,)).fetchone()
except Exception:
return fallback
return int(row[0]) if row and row[0] is not None else fallback
def _row_has(row, key):
keys = getattr(row, "keys", None)
if callable(keys):
try:
return key in keys()
except Exception:
return False
return isinstance(row, dict) and key in row
def _row_get(row, key, default=None):
if isinstance(row, dict):
return row.get(key, default)
if _row_has(row, key):
return row[key]
return default
def _json_blob(value):
if value in (None, ""):
return None
if isinstance(value, bytes):
value = value.decode("utf-8", "replace")
try:
return json.loads(value)
except (TypeError, ValueError):
return None
def recurrence_from_row(row, *, ts=None):
"""Extract a stable recurrence object from aliased ZREMCDOBJECT fields."""
frequency_raw = _row_get(row, "recurrence_frequency")
if frequency_raw is None:
return None
try:
frequency = RECURRENCE_FREQUENCIES[int(frequency_raw)]
except (KeyError, TypeError, ValueError):
return None
interval = _row_get(row, "recurrence_interval") or 1
recurrence = {"frequency": frequency, "interval": int(interval)}
days_of_week = _json_blob(_row_get(row, "recurrence_days_of_week")) or []
if days_of_week:
recurrence["daysOfWeekDetailed"] = days_of_week
recurrence["daysOfWeek"] = [
int(item["dayOfTheWeek"])
for item in days_of_week
if isinstance(item, dict) and item.get("dayOfTheWeek")
]
for alias, output_key in (
("recurrence_days_of_month", "daysOfMonth"),
("recurrence_months_of_year", "monthsOfYear"),
("recurrence_days_of_year", "daysOfYear"),
("recurrence_weeks_of_year", "weeksOfYear"),
("recurrence_set_positions", "setPositions"),
):
value = _json_blob(_row_get(row, alias))
if value:
recurrence[output_key] = value
count = _row_get(row, "recurrence_count") or 0
if count:
recurrence["count"] = int(count)
end_date = _row_get(row, "recurrence_end_date")
if end_date and ts is not None:
recurrence["endDate"] = ts(end_date).isoformat()
return recurrence
def due_date_delta_alerts_from_row(row, *, ts=None):
"""Extract Early Reminder due-date delta alerts from a reminder row."""
payload = _json_blob(_row_get(row, "ZDUEDATEDELTAALERTSDATA"))
if not isinstance(payload, dict):
return []
alerts = payload.get("dueDateDeltaAlerts")
if not isinstance(alerts, list):
return []
result = []
for alert in alerts:
if not isinstance(alert, dict):
continue
try:
unit_raw = int(alert.get("dueDateDeltaUnit"))
count = int(alert.get("dueDateDeltaCount"))
except (TypeError, ValueError):
continue
singular, plural = DUE_DATE_DELTA_UNITS.get(unit_raw, ("unknown", "unknown"))
value = abs(count)
unit_name = singular if value == 1 else plural
direction = "before" if count < 0 else "after"
item = {
"unit": unit_name,
"unitCode": unit_raw,
"count": count,
"value": value,
"direction": direction,
"label": f"{value} {unit_name} {direction}",
}
identifier = alert.get("identifier")
if identifier:
item["identifier"] = identifier
creation_date = alert.get("creationDate")
if creation_date and ts is not None:
try:
item["creationDate"] = ts(float(creation_date)).isoformat()
except (TypeError, ValueError):
pass
min_version = alert.get("minimumSupportedAppVersion")
if min_version is not None:
item["minimumSupportedAppVersion"] = min_version
result.append(item)
return result
def preload_extras(db, pks):
"""Batch-load subtask counts and hashtags to avoid N+1 queries.
Successful queries include zero/empty entries so callers do not requery
reminders without extras. Failed queries leave their map empty, preserving
per-reminder fallback independently for each table.
"""
if not pks:
return {}, {}
placeholders = ",".join("?" * len(pks))
subtask_counts = {}
hashtags = {}
try:
subtask_rows = db.execute(
f"SELECT ZPARENTREMINDER, COUNT(*) FROM ZREMCDREMINDER "
f"WHERE ZPARENTREMINDER IN ({placeholders}) AND +ZMARKEDFORDELETION = 0 "
f"AND ZCOMPLETED = 0 GROUP BY ZPARENTREMINDER",
pks,
).fetchall()
subtask_counts = dict.fromkeys(pks, 0)
subtask_counts.update({row[0]: row[1] for row in subtask_rows})
except Exception:
# Minimal test fixtures omit this table; extras are best-effort.
pass
try:
hashtag_rows = db.execute(
f"SELECT o.ZREMINDER3, h.ZNAME FROM ZREMCDOBJECT o "
f"JOIN ZREMCDHASHTAGLABEL h ON o.ZHASHTAGLABEL = h.Z_PK "
f"WHERE o.ZREMINDER3 IN ({placeholders}) AND +o.ZMARKEDFORDELETION = 0",
pks,
).fetchall()
hashtags = {pk: [] for pk in pks}
for row in hashtag_rows:
hashtags.setdefault(row[0], []).append(row[1])
except Exception:
pass
return subtask_counts, hashtags
def _table_columns(db, table):
try:
return {row[1] for row in db.execute(f"PRAGMA table_info({table})")}
except Exception:
return set()
def preload_attachments(db, pks):
"""Batch-load attachment rows grouped by parent reminder pk.
One query per backing table over the page of reminders; mirrors
preload_extras. Returns {reminder_pk: [row, ...]} with sha/width/height
included when the schema has those columns ("" placeholders otherwise).
"""
if not pks or db is None:
return {}
placeholders = ",".join("?" * len(pks))
saved_cols = _table_columns(db, "ZREMCDSAVEDATTACHMENT")
object_cols = _table_columns(db, "ZREMCDOBJECT")
saved_sha = "ZSHA512SUM" if "ZSHA512SUM" in saved_cols else "NULL AS ZSHA512SUM"
object_sha = "ZSHA512SUM" if "ZSHA512SUM" in object_cols else "NULL AS ZSHA512SUM"
grouped = {}
try:
saved_rows = db.execute(
f"SELECT ZREMINDER, ZFILENAME, ZUTI, ZATTACHMENTTYPERAWVALUE, {saved_sha}, "
"NULL AS ZWIDTH, NULL AS ZHEIGHT "
f"FROM ZREMCDSAVEDATTACHMENT "
f"WHERE ZREMINDER IN ({placeholders}) AND +ZMARKEDFORDELETION = 0",
pks,
).fetchall()
object_rows = db.execute(
f"SELECT ZREMINDER2, ZFILENAME, ZUTI, "
"CASE WHEN ZWIDTH IS NOT NULL OR ZHEIGHT IS NOT NULL THEN 'image' ELSE 'file' END AS ZATTACHMENTTYPERAWVALUE, "
f"{object_sha}, ZWIDTH, ZHEIGHT "
f"FROM ZREMCDOBJECT "
f"WHERE ZREMINDER2 IN ({placeholders}) AND ZFILENAME IS NOT NULL AND ZFILENAME != '' "
"AND +ZMARKEDFORDELETION = 0",
pks,
).fetchall()
except Exception:
return {}
for row in saved_rows + object_rows:
grouped.setdefault(row[0], []).append(row)
for pk in grouped:
grouped[pk].sort(key=lambda row: row[1] or "")
return grouped
def preload_indicators(db, pks):
"""Batch-load one-liner badge flags: {pk: {"image": bool, "link": bool}}.
image: the reminder has at least one image attachment (ZREMCDSAVEDATTACHMENT
row typed 'image', or a ZREMCDOBJECT file row with dimensions).
link: the reminder has at least one rich link (ZREMCDOBJECT row with ZURL)
or a legacy saved attachment typed 'url'.
Independent of preload_attachments: a reminder can carry a link without any
attachment rows. Each table pair is queried under its own try/except so a
minimal fixture missing a table degrades to False flags, never an error.
"""
if not pks or db is None:
return {}
placeholders = ",".join("?" * len(pks))
image_pks = set()
link_pks = set()
try:
saved_rows = db.execute(
f"SELECT ZREMINDER, ZATTACHMENTTYPERAWVALUE FROM ZREMCDSAVEDATTACHMENT "
f"WHERE ZREMINDER IN ({placeholders}) AND +ZMARKEDFORDELETION = 0",
pks,
).fetchall()
for row in saved_rows:
raw = (_row_get(row, "ZATTACHMENTTYPERAWVALUE") or "").strip().lower()
if raw == "url":
link_pks.add(row[0])
elif raw == "image":
image_pks.add(row[0])
except Exception:
pass
try:
object_rows = db.execute(
f"SELECT ZREMINDER2, ZWIDTH, ZHEIGHT, ZURL, ZFILENAME FROM ZREMCDOBJECT "
f"WHERE ZREMINDER2 IN ({placeholders}) AND +ZMARKEDFORDELETION = 0 "
f"AND ((ZURL IS NOT NULL AND ZURL != '') OR (ZFILENAME IS NOT NULL AND ZFILENAME != ''))",
pks,
).fetchall()
for row in object_rows:
url = _row_get(row, "ZURL")
if url:
link_pks.add(row[0])
filename = _row_get(row, "ZFILENAME")
if filename and (
_row_get(row, "ZWIDTH") is not None
or _row_get(row, "ZHEIGHT") is not None
):
image_pks.add(row[0])
except Exception:
pass
return {
pk: {"image": pk in image_pks, "link": pk in link_pks}
for pk in image_pks | link_pks
}
def all_day_due_iso(raw, *, ts):
"""The day of an all-day due date, as local midnight in ISO form.
Reminders stores an all-day due date as midnight UTC of that day. Read as
local time, that instant is 02:00 in Rome and the evening before in New
York, so the day comes from UTC. A value that is not a UTC midnight keeps
its local day.
"""
if float(raw) % SECONDS_PER_DAY == 0:
day = datetime.fromtimestamp(float(raw) + APPLE_EPOCH_UNIX, tz=timezone.utc).date()
else:
day = ts(raw).date()
return datetime(day.year, day.month, day.day).isoformat()
_MISSING = object()
def timed_due_raw(row, db=None):
"""A timed reminder's due date as a real instant, in Apple-epoch seconds.
Reminders stores a timed due date either as an instant, with ZTIMEZONE
set, or as floating wall-clock time encoded as if it were UTC, as GoodTask
and older versions of Reminders do: a 5 PM reminder is stored as 17:00Z.
A row is floating when it has no time zone, or when its display date is
exactly the wall-clock reading. The match has to be exact, because the
display date can also be an alarm near the due time. When a zoned row's
display date matches that conversion, an absolute alarm is evidence that
the display date belongs to the alarm instead. All-day rows and instants
come back unchanged.
"""
raw = _row_get(row, "ZDUEDATE")
if raw is None or _row_get(row, "ZALLDAY"):
return raw
wall = datetime.fromtimestamp(float(raw) + APPLE_EPOCH_UNIX, tz=timezone.utc).replace(tzinfo=None)
floating = wall.timestamp() - APPLE_EPOCH_UNIX
time_zone = _row_get(row, "ZTIMEZONE", _MISSING)
display = _row_get(row, "ZDISPLAYDATEDATE")
if time_zone in (None, ""):
return floating
if display is not None and round(float(display)) == round(floating):
if round(float(raw)) == round(floating):
return raw
# Only ambiguous dates need this indexed lookup; ordinary rows do no
# extra work. Ignore deleted alarms and triggers, as q_alarms does.
if db is not None and time_zone is not _MISSING and db.execute(
"SELECT 1 FROM ZREMCDOBJECT a JOIN ZREMCDOBJECT t ON a.ZTRIGGER = t.Z_PK "
"WHERE a.ZREMINDER = ? AND +a.Z_ENT = ? AND +a.ZMARKEDFORDELETION = 0 "
"AND t.ZMARKEDFORDELETION = 0 AND t.ZDATECOMPONENTSDATA IS NOT NULL LIMIT 1",
(_row_get(row, "Z_PK"), core_data_entity(db, ALARM_ENTITY)),
).fetchone():
return raw
return floating
return raw
def serialize_reminder(
row,
*,
ts,
priority_names,
db=None,
section=None,
subtask_counts=None,
hashtags=None,
attachments=None,
rich_link_resolver=None,
fallback_subtask_count=None,
fallback_hashtags=None,
attachment_rows_to_json=None,
):
"""Convert a reminder row to a JSON-serializable dict."""
subtask_counts = subtask_counts or {}
hashtags = hashtags or {}
attachments = attachments or {}
subtask_count = subtask_counts.get(row["Z_PK"])
if subtask_count is None:
if db is not None and fallback_subtask_count is not None:
subtask_count = fallback_subtask_count(db, row["Z_PK"])
else:
subtask_count = 0
tags = hashtags.get(row["Z_PK"])
if tags is None:
if db is not None and fallback_hashtags is not None:
tags = fallback_hashtags(db, row["Z_PK"])
else:
tags = []
reminder = {
"id": row["Z_PK"],
"title": row["ZTITLE"],
"list": row["list_name"],
"completed": bool(row["ZCOMPLETED"]),
"flagged": bool(row["ZFLAGGED"]),
"urgent": bool(_row_get(row, "ZISURGENTSTATEENABLEDFORCURRENTUSER", False)),
"priority": priority_names.get(row["ZPRIORITY"] or 0, "none"),
"subtaskCount": subtask_count,
"isSubtask": bool(row["ZPARENTREMINDER"]),
}
if section:
reminder["section"] = section
if row["ZNOTES"]:
reminder["notes"] = row["ZNOTES"]
url = row["ZICSURL"]
if not url and db is not None and rich_link_resolver is not None:
url = rich_link_resolver(db, row["Z_PK"])
if url:
reminder["url"] = url
due_date = None
due_raw = timed_due_raw(row, db=db)
if row["ZDUEDATE"]:
if _row_get(row, "ZALLDAY"):
due_date = all_day_due_iso(row["ZDUEDATE"], ts=ts)
else:
due_date = ts(due_raw).isoformat()
reminder["dueDate"] = due_date
display_date = _row_get(row, "ZDISPLAYDATEDATE")
if display_date and display_date != due_raw:
display_iso = ts(display_date).isoformat()
if display_iso != due_date:
reminder["displayDate"] = display_iso
if _row_get(row, "ZALLDAY") is not None:
reminder["allDay"] = bool(_row_get(row, "ZALLDAY"))
if row["ZCREATIONDATE"]:
reminder["createdDate"] = ts(row["ZCREATIONDATE"]).isoformat()
if row["ZCOMPLETIONDATE"]:
reminder["completionDate"] = ts(row["ZCOMPLETIONDATE"]).isoformat()
if row["ZPARENTREMINDER"]:
reminder["parentID"] = row["ZPARENTREMINDER"]
if tags:
reminder["tags"] = tags
if attachments and attachment_rows_to_json is not None:
attachment_payload = attachment_rows_to_json(attachments.get(row["Z_PK"], []))
if attachment_payload:
reminder["attachments"] = attachment_payload
recurrence = recurrence_from_row(row, ts=ts)
if recurrence:
reminder["recurrence"] = recurrence
early_reminders = due_date_delta_alerts_from_row(row, ts=ts)
if early_reminders:
reminder["earlyReminder"] = early_reminders[0]
reminder["earlyReminders"] = early_reminders
if row["ZCKIDENTIFIER"]:
reminder["deepLink"] = f"x-apple-reminderkit://REMCDReminder/{row['ZCKIDENTIFIER']}"
return reminder
def serialize_reminders(
rows,
*,
ts,
priority_names,
db=None,
memberships=None,
rich_link_resolver=None,
fallback_subtask_count=None,
fallback_hashtags=None,
attachment_rows_to_json=None,
):
"""Convert reminder rows with shared preloaded metadata."""
pks = [row["Z_PK"] for row in rows]
subtask_counts, hashtags = preload_extras(db, pks) if db else ({}, {})
attachments = preload_attachments(db, pks) if db and attachment_rows_to_json is not None else {}
memberships = memberships or {}
return [
serialize_reminder(
row,
ts=ts,
priority_names=priority_names,
db=db,
section=memberships.get(row["ZCKIDENTIFIER"]),
subtask_counts=subtask_counts,
hashtags=hashtags,
attachments=attachments,
rich_link_resolver=rich_link_resolver,
fallback_subtask_count=fallback_subtask_count,
fallback_hashtags=fallback_hashtags,
attachment_rows_to_json=attachment_rows_to_json,
)
for row in rows
]