things.database
Read from the Things SQLite database using SQL queries.
1"""Read from the Things SQLite database using SQL queries.""" 2 3# pylint: disable=C0302 4 5import datetime 6import glob 7import os 8import plistlib 9import re 10import sqlite3 11from textwrap import dedent 12import weakref 13 14 15# -------------------------------------------------- 16# Core constants 17# -------------------------------------------------- 18 19# Database filepath 20 21# in Things 3.12.6 (2020) "Containers/" changed to "Group Containers/" 22THINGS_GROUP_CONTAINER = os.path.expanduser( 23 "~/Library/Group Containers/JLMPQHK86H.com.culturedcode.ThingsMac" 24) 25# use glob pattern for version 3.17.4+ (2023+) 26DEFAULT_FILEPATH_31704015 = ( 27 f"{THINGS_GROUP_CONTAINER}/ThingsData-*/Things Database.thingsdatabase/main.sqlite" 28) 29DEFAULT_FILEPATH_31516502 = ( 30 f"{THINGS_GROUP_CONTAINER}/Things Database.thingsdatabase/main.sqlite" 31) 32ENVIRONMENT_VARIABLE_WITH_FILEPATH = "THINGSDB" 33 34# Translate app language to database language 35 36START_TO_FILTER = { 37 "Inbox": "start = 0", 38 "Anytime": "start = 1", 39 "Someday": "start = 2", 40} 41 42STATUS_TO_FILTER = { 43 "incomplete": "status = 0", 44 "canceled": "status = 2", 45 "completed": "status = 3", 46} 47 48TRASHED_TO_FILTER = {True: "trashed = 1", False: "trashed = 0"} 49 50# A repeating to-do is stored as a template row carrying a recurrence rule; 51# Things creates each instance from it. See `things.api.tasks`. 52REPEATING_TEMPLATE_TO_FILTER = { 53 True: "rt1_recurrenceRule IS NOT NULL", 54 False: "rt1_recurrenceRule IS NULL", 55} 56 57TYPE_TO_FILTER = { 58 "to-do": "type = 0", 59 "project": "type = 1", 60 "heading": "type = 2", 61} 62 63# Dates 64 65DATES = ("future", "past", True, False) 66 67# Indices 68 69INDICES = ("index", "todayIndex") 70 71# Response modification 72 73COLUMNS_TO_OMIT_IF_NONE = ( 74 "area", 75 "area_title", 76 "checklist", 77 "heading", 78 "heading_title", 79 "next_instance_start_date", 80 "project", 81 "project_title", 82 "reminder_time", 83 "trashed", 84 "tags", 85) 86COLUMNS_TO_TRANSFORM_TO_BOOL = ("checklist", "tags", "trashed") 87 88# -------------------------------------------------- 89# Table names 90# -------------------------------------------------- 91 92TABLE_AREA = "TMArea" 93TABLE_AREATAG = "TMAreaTag" 94TABLE_CHECKLIST_ITEM = "TMChecklistItem" 95TABLE_META = "Meta" 96TABLE_TAG = "TMTag" 97TABLE_TASK = "TMTask" 98TABLE_TASKTAG = "TMTaskTag" 99TABLE_SETTINGS = "TMSettings" 100 101# -------------------------------------------------- 102# Date Columns 103# -------------------------------------------------- 104 105# Note that the columns below -- contrary to their names -- seem to 106# store the full UTC datetime, not just the date. 107DATE_CREATED = "creationDate" # REAL: Unix date & time, UTC 108DATE_MODIFIED = "userModificationDate" # REAL: Unix date & time, UTC 109DATE_STOP = "stopDate" # REAL: Unix date & time, UTC 110 111# These are stored in a Things date format. 112# See `convert_isodate_sql_expression_to_thingsdate` for details. 113DATE_DEADLINE = "deadline" # INTEGER: YYYYYYYYYYYMMMMDDDDD0000000, in binary 114DATE_START = "startDate" # INTEGER: YYYYYYYYYYYMMMMDDDDD0000000, in binary 115# INTEGER: YYYYYYYYYYYMMMMDDDDD0000000, in binary; set on repeating templates only 116DATE_NEXT_INSTANCE_START = "rt1_nextInstanceStartDate" 117# See 'convert_thingstime_sql_expression_to_isotime' for details. 118REMINDER_TIME = "reminderTime" # INTEGER: hhhhhmmmmmm00000000000000000000, in binary 119 120# -------------------------------------------------- 121# Various filters 122# -------------------------------------------------- 123 124# Type 125IS_TODO = TYPE_TO_FILTER["to-do"] 126IS_PROJECT = TYPE_TO_FILTER["project"] 127IS_HEADING = TYPE_TO_FILTER["heading"] 128 129# Status 130IS_INCOMPLETE = STATUS_TO_FILTER["incomplete"] 131IS_CANCELED = STATUS_TO_FILTER["canceled"] 132IS_COMPLETED = STATUS_TO_FILTER["completed"] 133 134# Start 135IS_INBOX = START_TO_FILTER["Inbox"] 136IS_ANYTIME = START_TO_FILTER["Anytime"] 137IS_SOMEDAY = START_TO_FILTER["Someday"] 138 139# Trash 140IS_TRASHED = TRASHED_TO_FILTER[True] 141 142# -------------------------------------------------- 143# Fields and filters not yet used in the implementation. 144# This information might be of relevance in the future. 145# -------------------------------------------------- 146# 147# IS_SCHEDULED = f"{DATE_START} IS NOT NULL" 148# IS_NOT_SCHEDULED = f"{DATE_START} IS NULL" 149# IS_DEADLINE = f"{DATE_DEADLINE} IS NOT NULL" 150# RECURRING_IS_NOT_PAUSED = "rt1_instanceCreationPaused = 0" 151# IS_NOT_TRASHED = TRASHED_TO_FILTER[False] 152 153# pylint: disable=R0904,R0902 154 155 156class Database: 157 """ 158 Access Things SQL database. 159 160 Parameters 161 ---------- 162 filepath : str, optional 163 Any valid path of a SQLite database file generated by the Things app. 164 If the environment variable `THINGSDB` is set, then use that path. 165 Otherwise, access the default database path. 166 167 print_sql : bool, default False 168 Print every SQL query performed. Some may contain '?' and ':' 169 characters which correspond to SQLite parameter tokens. 170 See https://www.sqlite.org/lang_expr.html#varparam 171 172 :raises AssertionError: If the database version is too old. 173 """ 174 175 debug = False 176 connection: sqlite3.Connection 177 178 # pylint: disable=R0913 179 def __init__(self, filepath=None, print_sql=False): 180 """Set up the database.""" 181 self.filepath = ( 182 filepath 183 or os.getenv(ENVIRONMENT_VARIABLE_WITH_FILEPATH) 184 or get_default_database_filepath(raise_on_error=True) 185 ) 186 self.print_sql = print_sql 187 if self.print_sql: 188 self.execute_query_count = 0 189 190 # "ro" means read-only 191 # See: https://sqlite.org/uri.html#recognized_query_parameters 192 uri = f"file:{self.filepath}?mode=ro" 193 self.connection = sqlite3.connect(uri, uri=True) # pylint: disable=E1101 194 # Close the underlying SQLite connection when this Database object is garbage collected 195 weakref.finalize(self, sqlite3.Connection.close, self.connection) 196 197 # Test for migrated database in Things 3.17.4+ 198 assert self.get_version() > 21, ( 199 "Your database is in an older format. " 200 "Run 'pip install things.py==0.0.14' to downgrade to an older " 201 "version of this library." 202 ) 203 204 # Core methods 205 206 def get_tasks( # pylint: disable=R0914,R0913,R0917 207 self, 208 uuid: str | None = None, 209 type: str | None = None, # pylint: disable=W0622 210 status: str | None = None, 211 start: str | None = None, 212 area: str | bool | None = None, 213 project: str | bool | None = None, 214 heading: str | None = None, 215 tag: str | bool | None = None, 216 start_date: str | bool | None = None, 217 stop_date: str | bool | None = None, 218 deadline: str | bool | None = None, 219 deadline_suppressed: bool | None = None, 220 trashed: bool | None = False, 221 context_trashed: bool | None = False, 222 is_repeating_template: bool | None = False, 223 next_instance_start_date: str | bool | None = None, 224 last: str | None = None, 225 search_query: str | None = None, 226 index: str = "index", 227 count_only: bool = False, 228 ): 229 """Get tasks. See `things.api.tasks` for details on parameters.""" 230 if uuid: 231 return self.get_task_by_uuid(uuid, count_only=count_only) 232 233 # Overwrites 234 start = start and start.title() 235 236 # Validation 237 validate_date("deadline", deadline) 238 validate("deadline_suppressed", deadline_suppressed, [None, True, False]) 239 validate("start", start, [None] + list(START_TO_FILTER)) 240 validate_date("start_date", start_date) 241 validate_date("next_instance_start_date", next_instance_start_date) 242 validate_date("stop_date", stop_date) 243 validate("status", status, [None] + list(STATUS_TO_FILTER)) 244 validate("trashed", trashed, [None] + list(TRASHED_TO_FILTER)) 245 validate("type", type, [None] + list(TYPE_TO_FILTER)) 246 validate("context_trashed", context_trashed, [None, True, False]) 247 validate( 248 "is_repeating_template", 249 is_repeating_template, 250 [None] + list(REPEATING_TEMPLATE_TO_FILTER), 251 ) 252 validate("index", index, list(INDICES)) 253 validate_offset("last", last) 254 255 if tag is not None: 256 valid_tags = self.get_tags(titles_only=True) 257 validate("tag", tag, [None] + list(valid_tags)) 258 259 # Query 260 # TK: might consider executing SQL with parameters instead. 261 # See: https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute 262 263 start_filter: str = START_TO_FILTER.get(start, "") # type: ignore 264 status_filter: str = STATUS_TO_FILTER.get(status, "") # type: ignore 265 trashed_filter: str = TRASHED_TO_FILTER.get(trashed, "") # type: ignore 266 type_filter: str = TYPE_TO_FILTER.get(type, "") # type: ignore 267 repeating_template_filter: str = REPEATING_TEMPLATE_TO_FILTER.get( 268 is_repeating_template, # type: ignore 269 "", 270 ) 271 next_instance_start_date_filter = make_thingsdate_filter( 272 f"TASK.{DATE_NEXT_INSTANCE_START}", next_instance_start_date 273 ) 274 275 # Sometimes a task is _not_ set to trashed, but its context 276 # (project or heading it is contained within) is set to trashed. 277 # In those cases, the task wouldn't show up in any app view 278 # except for "Trash". 279 project_trashed_filter = make_truthy_filter("PROJECT.trashed", context_trashed) 280 project_of_heading_trashed_filter = make_truthy_filter( 281 "PROJECT_OF_HEADING.trashed", context_trashed 282 ) 283 284 # As a task assigned to a heading is not directly assigned to a project anymore, 285 # we need to check if the heading is assigned to a project. 286 # See, e.g. https://github.com/thingsapi/things.py/issues/94 287 project_filter = make_or_filter( 288 make_filter("TASK.project", project), 289 make_filter("PROJECT_OF_HEADING.uuid", project), 290 ) 291 292 where_predicate = f""" 293 TRUE 294 {repeating_template_filter and f"AND TASK.{repeating_template_filter}"} 295 {trashed_filter and f"AND TASK.{trashed_filter}"} 296 {project_trashed_filter} 297 {project_of_heading_trashed_filter} 298 {type_filter and f"AND TASK.{type_filter}"} 299 {start_filter and f"AND TASK.{start_filter}"} 300 {status_filter and f"AND TASK.{status_filter}"} 301 {make_filter("TASK.uuid", uuid)} 302 {make_filter("TASK.area", area)} 303 {project_filter} 304 {make_filter("TASK.heading", heading)} 305 {make_filter("TASK.deadlineSuppressionDate", deadline_suppressed)} 306 {make_filter("TAG.title", tag)} 307 {make_thingsdate_filter(f"TASK.{DATE_START}", start_date)} 308 {next_instance_start_date_filter} 309 {make_unixtime_filter(f"TASK.{DATE_STOP}", stop_date)} 310 {make_thingsdate_filter(f"TASK.{DATE_DEADLINE}", deadline)} 311 {make_unixtime_range_filter(f"TASK.{DATE_CREATED}", last)} 312 {make_search_filter(search_query)} 313 """ 314 order_predicate = f'TASK."{index}"' 315 316 sql_query = make_tasks_sql_query(where_predicate, order_predicate) 317 318 if count_only: 319 return self.get_count(sql_query) 320 321 return self.execute_query(sql_query) 322 323 def get_task_by_uuid(self, uuid, count_only=False): 324 """Get a task by uuid. Raise `ValueError` if not found.""" 325 where_predicate = "TASK.uuid = ?" 326 sql_query = make_tasks_sql_query(where_predicate) 327 parameters = (uuid,) 328 329 if count_only: 330 return self.get_count(sql_query, parameters) 331 332 result = self.execute_query(sql_query, parameters) 333 if not result: 334 raise ValueError(f"No such task uuid found: {uuid!r}") 335 336 return result 337 338 def get_areas(self, uuid=None, tag=None, count_only=False): 339 """Get areas. See `api.areas` for details on parameters.""" 340 # Validation 341 if tag is not None: 342 valid_tags = self.get_tags(titles_only=True) 343 validate("tag", tag, [None] + list(valid_tags)) 344 345 if ( 346 uuid 347 and count_only is False 348 and not self.get_areas(uuid=uuid, count_only=True) 349 ): 350 raise ValueError(f"No such area uuid found: {uuid!r}") 351 352 # Query 353 sql_query = f""" 354 SELECT DISTINCT 355 AREA.uuid, 356 'area' as type, 357 AREA.title, 358 CASE 359 WHEN AREA_TAG.areas IS NOT NULL THEN 1 360 END AS tags 361 FROM 362 {TABLE_AREA} AS AREA 363 LEFT OUTER JOIN 364 {TABLE_AREATAG} AREA_TAG ON AREA_TAG.areas = AREA.uuid 365 LEFT OUTER JOIN 366 {TABLE_TAG} TAG ON TAG.uuid = AREA_TAG.tags 367 WHERE 368 TRUE 369 {make_filter("TAG.title", tag)} 370 {make_filter("AREA.uuid", uuid)} 371 ORDER BY AREA."index" 372 """ 373 374 if count_only: 375 return self.get_count(sql_query) 376 377 return self.execute_query(sql_query) 378 379 def get_checklist_items(self, todo_uuid=None): 380 """Get checklist items.""" 381 sql_query = f""" 382 SELECT 383 CHECKLIST_ITEM.title, 384 CASE 385 WHEN CHECKLIST_ITEM.{IS_INCOMPLETE} THEN 'incomplete' 386 WHEN CHECKLIST_ITEM.{IS_CANCELED} THEN 'canceled' 387 WHEN CHECKLIST_ITEM.{IS_COMPLETED} THEN 'completed' 388 END AS status, 389 date(CHECKLIST_ITEM.stopDate, "unixepoch", "localtime") AS stop_date, 390 'checklist-item' as type, 391 CHECKLIST_ITEM.uuid, 392 datetime( 393 CHECKLIST_ITEM.{DATE_MODIFIED}, "unixepoch", "localtime" 394 ) AS created, 395 datetime( 396 CHECKLIST_ITEM.{DATE_MODIFIED}, "unixepoch", "localtime" 397 ) AS modified 398 FROM 399 {TABLE_CHECKLIST_ITEM} AS CHECKLIST_ITEM 400 WHERE 401 CHECKLIST_ITEM.task = ? 402 ORDER BY CHECKLIST_ITEM."index" 403 """ 404 return self.execute_query(sql_query, (todo_uuid,)) 405 406 def get_tags(self, title=None, area=None, task=None, titles_only=False): 407 """Get tags. See `api.tags` for details on parameters.""" 408 # Validation 409 if title is not None: 410 valid_titles = self.get_tags(titles_only=True) 411 validate("title", title, [None] + list(valid_titles)) 412 413 # Query 414 if task: 415 return self.get_tags_of_task(task) 416 if area: 417 return self.get_tags_of_area(area) 418 419 if titles_only: 420 sql_query = f'SELECT title FROM {TABLE_TAG} ORDER BY "index"' 421 return self.execute_query(sql_query, row_factory=list_factory) 422 423 sql_query = f""" 424 SELECT 425 uuid, 'tag' AS type, title, shortcut 426 FROM 427 {TABLE_TAG} 428 WHERE 429 TRUE 430 {make_filter("title", title)} 431 ORDER BY "index" 432 """ 433 434 return self.execute_query(sql_query) 435 436 def get_tags_of_task(self, task_uuid): 437 """Get tag titles of task.""" 438 sql_query = f""" 439 SELECT 440 TAG.title 441 FROM 442 {TABLE_TASKTAG} AS TASK_TAG 443 LEFT OUTER JOIN 444 {TABLE_TAG} TAG ON TAG.uuid = TASK_TAG.tags 445 WHERE 446 TASK_TAG.tasks = ? 447 ORDER BY TAG."index" 448 """ 449 return self.execute_query( 450 sql_query, parameters=(task_uuid,), row_factory=list_factory 451 ) 452 453 def get_tags_of_area(self, area_uuid): 454 """Get tag titles for area.""" 455 sql_query = f""" 456 SELECT 457 AREA.title 458 FROM 459 {TABLE_AREATAG} AS AREA_TAG 460 LEFT OUTER JOIN 461 {TABLE_TAG} AREA ON AREA.uuid = AREA_TAG.tags 462 WHERE 463 AREA_TAG.areas = ? 464 ORDER BY AREA."index" 465 """ 466 return self.execute_query( 467 sql_query, parameters=(area_uuid,), row_factory=list_factory 468 ) 469 470 def get_version(self): 471 """Get Things Database version.""" 472 sql_query = f"SELECT value FROM {TABLE_META} WHERE key = 'databaseVersion'" 473 result = self.execute_query(sql_query, row_factory=list_factory) 474 plist_bytes = result[0].encode() 475 return plistlib.loads(plist_bytes) 476 477 # pylint: disable=R1710 478 def get_url_scheme_auth_token(self): 479 """Get the Things URL scheme authentication token.""" 480 sql_query = f""" 481 SELECT 482 uriSchemeAuthenticationToken 483 FROM 484 {TABLE_SETTINGS} 485 WHERE 486 uuid = 'RhAzEf6qDxCD5PmnZVtBZR' 487 """ 488 rows = self.execute_query(sql_query, row_factory=list_factory) 489 return rows[0] 490 491 def get_count(self, sql_query, parameters=()): 492 """Count number of results.""" 493 count_sql_query = f"""SELECT COUNT(uuid) FROM (\n{sql_query}\n)""" 494 rows = self.execute_query( 495 count_sql_query, row_factory=list_factory, parameters=parameters 496 ) 497 return rows[0] 498 499 # TODO: add type hinting for result (List[Tuple[str, Any]]?) # pylint: disable=fixme 500 def execute_query(self, sql_query, parameters=(), row_factory=None): 501 """Run the actual SQL query.""" 502 if self.print_sql or self.debug: 503 if not hasattr(self, "execute_query_count"): 504 # This is needed for historical `self.debug`. 505 # TK: might consider removing `debug` flag. 506 self.execute_query_count = 0 507 self.execute_query_count += 1 508 if self.debug: 509 print(f"/* Filepath {self.filepath!r} */") 510 print(f"/* Query {self.execute_query_count} */") 511 if parameters: 512 print(f"/* Parameters: {parameters!r} */") 513 print() 514 print(prettify_sql(sql_query)) 515 print() 516 517 with self.connection: 518 # Using context manager to keep queries in separate transactions, 519 # see https://docs.python.org/3/library/sqlite3.html#sqlite3-connection-context-manager 520 self.connection.row_factory = row_factory or dict_factory 521 cursor = self.connection.cursor() 522 cursor.execute(sql_query, parameters) 523 524 return cursor.fetchall() 525 526 527# Helper functions 528 529 530def make_tasks_sql_query(where_predicate=None, order_predicate=None): 531 """Make SQL query for Task table.""" 532 where_predicate = where_predicate or "TRUE" 533 order_predicate = order_predicate or 'TASK."index"' 534 535 start_date_expression = convert_thingsdate_sql_expression_to_isodate( 536 f"TASK.{DATE_START}" 537 ) 538 deadline_expression = convert_thingsdate_sql_expression_to_isodate( 539 f"TASK.{DATE_DEADLINE}" 540 ) 541 reminder_time_expression = convert_thingstime_sql_expression_to_isotime( 542 f"TASK.{REMINDER_TIME}" 543 ) 544 next_instance_start_date_expression = convert_thingsdate_sql_expression_to_isodate( 545 null_if_unset_thingsdate(f"TASK.{DATE_NEXT_INSTANCE_START}") 546 ) 547 548 return f""" 549 SELECT DISTINCT 550 TASK.uuid, 551 CASE 552 WHEN TASK.{IS_TODO} THEN 'to-do' 553 WHEN TASK.{IS_PROJECT} THEN 'project' 554 WHEN TASK.{IS_HEADING} THEN 'heading' 555 END AS type, 556 CASE 557 WHEN TASK.{IS_TRASHED} THEN 1 558 END AS trashed, 559 TASK.title, 560 CASE 561 WHEN TASK.{IS_INCOMPLETE} THEN 'incomplete' 562 WHEN TASK.{IS_CANCELED} THEN 'canceled' 563 WHEN TASK.{IS_COMPLETED} THEN 'completed' 564 END AS status, 565 CASE 566 WHEN AREA.uuid IS NOT NULL THEN AREA.uuid 567 END AS area, 568 CASE 569 WHEN AREA.uuid IS NOT NULL THEN AREA.title 570 END AS area_title, 571 CASE 572 WHEN PROJECT.uuid IS NOT NULL THEN PROJECT.uuid 573 END AS project, 574 CASE 575 WHEN PROJECT.uuid IS NOT NULL THEN PROJECT.title 576 END AS project_title, 577 CASE 578 WHEN HEADING.uuid IS NOT NULL THEN HEADING.uuid 579 END AS heading, 580 CASE 581 WHEN HEADING.uuid IS NOT NULL THEN HEADING.title 582 END AS heading_title, 583 TASK.notes, 584 CASE 585 WHEN TAG.uuid IS NOT NULL THEN 1 586 END AS tags, 587 CASE 588 WHEN TASK.{IS_INBOX} THEN 'Inbox' 589 WHEN TASK.{IS_ANYTIME} THEN 'Anytime' 590 WHEN TASK.{IS_SOMEDAY} THEN 'Someday' 591 END AS start, 592 CASE 593 WHEN CHECKLIST_ITEM.uuid IS NOT NULL THEN 1 594 END AS checklist, 595 {start_date_expression} AS start_date, 596 {next_instance_start_date_expression} AS next_instance_start_date, 597 {deadline_expression} AS deadline, 598 {reminder_time_expression} AS "reminder_time", 599 datetime(TASK.{DATE_STOP}, "unixepoch", "localtime") AS "stop_date", 600 datetime(TASK.{DATE_CREATED}, "unixepoch", "localtime") AS created, 601 datetime(TASK.{DATE_MODIFIED}, "unixepoch", "localtime") AS modified, 602 TASK.'index', 603 TASK.todayIndex AS today_index 604 FROM 605 {TABLE_TASK} AS TASK 606 LEFT OUTER JOIN 607 {TABLE_TASK} PROJECT ON TASK.project = PROJECT.uuid 608 LEFT OUTER JOIN 609 {TABLE_AREA} AREA ON TASK.area = AREA.uuid 610 LEFT OUTER JOIN 611 {TABLE_TASK} HEADING ON TASK.heading = HEADING.uuid 612 LEFT OUTER JOIN 613 {TABLE_TASK} PROJECT_OF_HEADING 614 ON HEADING.project = PROJECT_OF_HEADING.uuid 615 LEFT OUTER JOIN 616 {TABLE_TASKTAG} TAGS ON TASK.uuid = TAGS.tasks 617 LEFT OUTER JOIN 618 {TABLE_TAG} TAG ON TAGS.tags = TAG.uuid 619 LEFT OUTER JOIN 620 {TABLE_CHECKLIST_ITEM} CHECKLIST_ITEM 621 ON TASK.uuid = CHECKLIST_ITEM.task 622 WHERE 623 {where_predicate} 624 ORDER BY 625 {order_predicate} 626 """ 627 628 629# In alphabetical order from here... 630 631 632def convert_isodate_sql_expression_to_thingsdate(sql_expression, null_possible=True): 633 """ 634 Return a SQL expression of an isodate converted into a "Things date". 635 636 A _Things date_ is an integer where the binary digits are 637 YYYYYYYYYYYMMMMDDDDD0000000; Y is year, M is month, and D is day. 638 639 For example, the ISO 8601 date '2021-03-28' corresponds to the Things 640 date 132464128 as integer; in binary that is: 641 111111001010011111000000000 642 YYYYYYYYYYYMMMMDDDDD0000000 643 2021 3 28 644 645 Parameters 646 ---------- 647 sql_expression : str 648 A sql expression evaluating to an ISO 8601 date str. 649 650 null_possible : bool 651 Can the input `sql_expression` evaluate to NULL? 652 653 Returns 654 ------- 655 str 656 A sql expression representing a "Things date" as integer. 657 658 Example 659 ------- 660 >>> convert_isodate_sql_expression_to_thingsdate("date('now', 'localtime')") 661 "(CASE WHEN date('now', 'localtime') THEN \ 662 ((strftime('%Y', date('now', 'localtime')) << 16) \ 663 | (strftime('%m', date('now', 'localtime')) << 12) \ 664 | (strftime('%d', date('now', 'localtime')) << 7)) \ 665 ELSE date('now', 'localtime') END)" 666 >>> convert_isodate_sql_expression_to_thingsdate("'2023-05-22'") 667 "(CASE WHEN '2023-05-22' THEN \ 668 ((strftime('%Y', '2023-05-22') << 16) \ 669 | (strftime('%m', '2023-05-22') << 12) \ 670 | (strftime('%d', '2023-05-22') << 7)) \ 671 ELSE '2023-05-22' END)" 672 """ 673 isodate = sql_expression 674 675 year = f"strftime('%Y', {isodate}) << 16" 676 month = f"strftime('%m', {isodate}) << 12" 677 day = f"strftime('%d', {isodate}) << 7" 678 679 thingsdate = f"(({year}) | ({month}) | ({day}))" 680 681 if null_possible: 682 # when isodate is NULL, return isodate as-is 683 return f"(CASE WHEN {isodate} THEN {thingsdate} ELSE {isodate} END)" 684 685 return thingsdate 686 687 688def convert_thingsdate_sql_expression_to_isodate(sql_expression): 689 """ 690 Return SQL expression as string. 691 692 Parameters 693 ---------- 694 sql_expression : str 695 A sql expression pointing to a "Things date" integer in 696 format YYYYYYYYYYYMMMMDDDDD0000000, in binary. 697 See: `convert_isodate_sql_expression_to_thingsdate` for details. 698 699 Example 700 ------- 701 >>> convert_thingsdate_sql_expression_to_isodate('132464128') 702 "CASE WHEN 132464128 THEN \ 703 printf('%d-%02d-%02d', (132464128 & 134152192) >> 16, \ 704 (132464128 & 61440) >> 12, (132464128 & 3968) >> 7) ELSE 132464128 END" 705 >>> convert_thingsdate_sql_expression_to_isodate('startDate') 706 "CASE WHEN startDate THEN \ 707 printf('%d-%02d-%02d', (startDate & 134152192) >> 16, \ 708 (startDate & 61440) >> 12, (startDate & 3968) >> 7) ELSE startDate END" 709 """ 710 y_mask = 0b111111111110000000000000000 711 m_mask = 0b000000000001111000000000000 712 d_mask = 0b000000000000000111110000000 713 714 thingsdate = sql_expression 715 year = f"({thingsdate} & {y_mask}) >> 16" 716 month = f"({thingsdate} & {m_mask}) >> 12" 717 day = f"({thingsdate} & {d_mask}) >> 7" 718 719 isodate = f"printf('%d-%02d-%02d', {year}, {month}, {day})" 720 # when thingsdate is NULL, return thingsdate as-is 721 return f"CASE WHEN {thingsdate} THEN {isodate} ELSE {thingsdate} END" 722 723 724def convert_thingstime_sql_expression_to_isotime(sql_expression: str) -> str: 725 """ 726 Return SQL Expression that decodes a Things time as a string. 727 728 A _Things time_ is an integer where the binary digits are 729 hhhhhmmmmmm00000000000000000000; h is hours, m is minutes. 730 Seconds are not encoded in a Things time. 731 732 For example, the ISO 8601 time '12:34:00' corresponds to the Things 733 time 840957952 as integer; in binary that is: 734 0110010001000000000000000000000 735 hhhhhmmmmmm00000000000000000000 736 12 34 00 737 738 Parameters 739 ---------- 740 sql_expression : str 741 A sql expression pointing to a "Things time" integer 742 in format hhhhhmmmmmm00000000000000000000, in binary. 743 744 Example 745 ------- 746 >>> convert_thingstime_sql_expression_to_isotime('840957952') 747 "CASE WHEN 840957952 THEN \ 748 printf('%02d:%02d', (840957952 & 2080374784) >> 26, \ 749 (840957952 & 66060288) >> 20) ELSE 840957952 END" 750 >>> convert_thingstime_sql_expression_to_isotime('reminderTime') 751 "CASE WHEN reminderTime THEN \ 752 printf('%02d:%02d', (reminderTime & 2080374784) >> 26, \ 753 (reminderTime & 66060288) >> 20) ELSE reminderTime END" 754 """ 755 h_mask = 0b1111100000000000000000000000000 756 m_mask = 0b0000011111100000000000000000000 757 758 thingstime = sql_expression 759 hours = f"({thingstime} & {h_mask}) >> 26" 760 minutes = f"({thingstime} & {m_mask}) >> 20" 761 762 isotime = f"printf('%02d:%02d', {hours}, {minutes})" 763 # when thingstime is NULL, return thingstime as-is 764 return f"CASE WHEN {thingstime} THEN {isotime} ELSE {thingstime} END" 765 766 767def dict_factory(cursor, row): 768 """ 769 Convert SQL result into a dictionary. 770 771 See also: 772 https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory 773 """ 774 result = {} 775 for index, column in enumerate(cursor.description): 776 key, value = column[0], row[index] 777 if value is None and key in COLUMNS_TO_OMIT_IF_NONE: 778 continue 779 if value and key in COLUMNS_TO_TRANSFORM_TO_BOOL: 780 value = bool(value) 781 result[key] = value 782 return result 783 784 785def escape_string(string): 786 r""" 787 Escape SQLite string literal. 788 789 Three notes: 790 791 1. A single quote within a SQLite string can be encoded by putting 792 two single quotes in a row. Escapes using the backslash character 793 are not supported in SQLite. 794 795 2. Null characters '\0' within strings can lead to surprising 796 behavior. However, `cursor.execute` will already throw a `ValueError` 797 if it finds a null character in the query, so we let it handle 798 this case for us. 799 800 3. Eventually we might want to make use of parameters instead of 801 manually escaping. Since this will require some refactoring, 802 we are going with the easiest solution for now. 803 804 See: https://www.sqlite.org/lang_expr.html#literal_values_constants_ 805 """ 806 return string.replace("'", "''") 807 808 809def isodate_to_yyyyyyyyyyymmmmddddd(value: str): 810 """ 811 Return integer, in binary YYYYYYYYYYYMMMMDDDDD0000000. 812 813 Y is year, M is month, D is day as binary. 814 See also `convert_isodate_sql_expression_to_thingsdate`. 815 816 Parameters 817 ---------- 818 value : str 819 ISO 8601 date str 820 821 Example 822 ------- 823 >>> isodate_to_yyyyyyyyyyymmmmddddd('2021-03-28') 824 132464128 825 """ 826 year, month, day = map(int, value.split("-")) 827 return year << 16 | month << 12 | day << 7 828 829 830def get_default_database_filepath(raise_on_error=False): 831 """Return Things 3.17.4+ path else the 3.15 path. 832 833 Optionally, raise an error if the Things 3.17.4+ path isn't found 834 and macOS can't list the files in the `Group Containers` folder. 835 """ 836 filepath = next(glob.iglob(DEFAULT_FILEPATH_31704015), None) 837 if filepath: 838 return filepath 839 840 if raise_on_error: 841 # glob hides PermissionError; this raises it 842 os.listdir(THINGS_GROUP_CONTAINER) 843 # fallback to an older Things path to try 844 return DEFAULT_FILEPATH_31516502 845 846 847def list_factory(_cursor, row): 848 """Convert SQL selects of one column into a list.""" 849 return row[0] 850 851 852def make_filter(column, value): 853 """ 854 Return SQL filter 'AND {column} = "{value}"'. 855 856 Special handling if `value` is `bool` or `None`. 857 858 Examples 859 -------- 860 >>> make_filter('title', 'Important') 861 "AND title = 'Important'" 862 863 >>> make_filter('startDate', True) 864 'AND startDate IS NOT NULL' 865 866 >>> make_filter('startDate', False) 867 'AND startDate IS NULL' 868 869 >>> make_filter('title', None) 870 '' 871 """ 872 default = f"AND {column} = '{escape_string(str(value))}'" 873 return { 874 None: "", 875 False: f"AND {column} IS NULL", 876 True: f"AND {column} IS NOT NULL", 877 }.get(value, default) 878 879 880def make_or_filter(*filters): 881 """Join filters with OR.""" 882 filters = filter(None, filters) # type: ignore 883 filters = [remove_prefix(filter, "AND ") for filter in filters] # type: ignore 884 filters = " OR ".join(filters) # type: ignore 885 return f"AND ({filters})" if filters else "" 886 887 888def make_search_filter(query: str | None) -> str: 889 """ 890 Return a SQL filter to search tasks by a string query. 891 892 Example: 893 -------- 894 >>> make_search_filter('dinner') 895 "AND (TASK.title LIKE '%dinner%' OR TASK.notes LIKE '%dinner%' OR \ 896 AREA.title LIKE '%dinner%')" 897 """ 898 if not query: 899 return "" 900 901 query = escape_string(query) 902 903 # TODO: 'TMChecklistItem.title' # pylint: disable=fixme 904 columns = ["TASK.title", "TASK.notes", "AREA.title"] 905 906 sub_searches = (f"{column} LIKE '%{query}%'" for column in columns) 907 908 return f"AND ({' OR '.join(sub_searches)})" 909 910 911def make_thingsdate_filter(date_column: str, value) -> str: 912 """ 913 Return a SQL filter for "Things date" columns. 914 915 Parameters 916 ---------- 917 date_column : str 918 Name of the column that has date information on a task 919 stored as an INTEGER in "Things date" format. 920 See `convert_isodate_sql_expression_to_thingsdate` for details 921 on Things dates. 922 923 value : bool, 'future', 'past', ISO 8601 date str, or None 924 `True` or `False` indicates whether a date is set or not. 925 `'future'` or `'past'` indicates a date in the future or past. 926 ISO 8601 date str is in the format "YYYY-MM-DD", possibly 927 prefixed with an operator such as ">YYYY-MM-DD", 928 "=YYYY-MM-DD", "<=YYYY-MM-DD", etc. 929 `None` indicates any value. 930 931 Returns 932 ------- 933 str 934 A date filter for the SQL query. If `value == None`, then 935 return the empty string. 936 937 Examples 938 -------- 939 >>> make_thingsdate_filter('startDate', True) 940 'AND startDate IS NOT NULL' 941 942 >>> make_thingsdate_filter('startDate', False) 943 'AND startDate IS NULL' 944 945 >>> make_thingsdate_filter('startDate', 'future') 946 "AND startDate > ((strftime('%Y', date('now', 'localtime')) << 16) \ 947 | (strftime('%m', date('now', 'localtime')) << 12) \ 948 | (strftime('%d', date('now', 'localtime')) << 7))" 949 950 >>> make_thingsdate_filter('deadline', '2021-03-28') 951 'AND deadline == 132464128' 952 953 >>> make_thingsdate_filter('deadline', '=2021-03-28') 954 'AND deadline = 132464128' 955 956 >>> make_thingsdate_filter('deadline', '<=2021-03-28') 957 'AND deadline <= 132464128' 958 959 >>> make_thingsdate_filter('deadline', None) 960 '' 961 962 """ 963 if value is None: 964 return "" 965 966 if isinstance(value, bool): 967 return make_filter(date_column, value) 968 969 # Check for ISO 8601 date str + optional operator 970 match = match_date(value) 971 if match: 972 comparator, isodate = match.groups() 973 if not comparator: 974 comparator = "==" 975 thingsdate = isodate_to_yyyyyyyyyyymmmmddddd(isodate) 976 threshold = str(thingsdate) 977 else: 978 # "future" or "past" 979 validate("value", value, ["future", "past"]) 980 threshold = convert_isodate_sql_expression_to_thingsdate( 981 "date('now', 'localtime')", null_possible=False 982 ) 983 comparator = ">" if value == "future" else "<=" 984 985 return f"AND {date_column} {comparator} {threshold}" 986 987 988def make_truthy_filter(column: str, value) -> str: 989 """ 990 Return a SQL filter that matches if a column is truthy or falsy. 991 992 Truthy means TRUE. Falsy means FALSE or NULL. This is akin 993 to how Python defines it natively. 994 995 Passing in `value == None` returns the empty string. 996 997 Examples 998 -------- 999 >>> make_truthy_filter('PROJECT.trashed', True) 1000 'AND PROJECT.trashed' 1001 1002 >>> make_truthy_filter('PROJECT.trashed', False) 1003 'AND NOT IFNULL(PROJECT.trashed, 0)' 1004 1005 >>> make_truthy_filter('PROJECT.trashed', None) 1006 '' 1007 """ 1008 if value is None: 1009 return "" 1010 1011 if value: 1012 return f"AND {column}" 1013 1014 return f"AND NOT IFNULL({column}, 0)" 1015 1016 1017def make_unixtime_filter(date_column: str, value) -> str: 1018 """ 1019 Return a SQL filter for UNIX time columns. 1020 1021 Parameters 1022 ---------- 1023 date_column : str 1024 Name of the column that has datetime information on a task 1025 stored in UNIX time, that is, number of seconds since 1026 1970-01-01 00:00 UTC. 1027 1028 value : bool, 'future', 'past', ISO 8601 date str, or None 1029 `True` or `False` indicates whether a date is set or not. 1030 `'future'` or `'past'` indicates a date in the future or past. 1031 ISO 8601 date str is in the format "YYYY-MM-DD", possibly 1032 prefixed with an operator such as ">YYYY-MM-DD", 1033 "=YYYY-MM-DD", "<=YYYY-MM-DD", etc. 1034 `None` indicates any value. 1035 1036 Returns 1037 ------- 1038 str 1039 A date filter for the SQL query. If `value == None`, then 1040 return the empty string. 1041 1042 Examples 1043 -------- 1044 >>> make_unixtime_filter('stopDate', True) 1045 'AND stopDate IS NOT NULL' 1046 1047 >>> make_unixtime_filter('stopDate', False) 1048 'AND stopDate IS NULL' 1049 1050 >>> make_unixtime_filter('stopDate', 'future') 1051 "AND date(stopDate, 'unixepoch', 'localtime') > date('now', 'localtime')" 1052 1053 >>> make_unixtime_filter('creationDate', '2021-03-28') 1054 "AND date(creationDate, 'unixepoch', 'localtime') == date('2021-03-28')" 1055 1056 >>> make_unixtime_filter('creationDate', '=2021-03-28') 1057 "AND date(creationDate, 'unixepoch', 'localtime') = date('2021-03-28')" 1058 1059 >>> make_unixtime_filter('creationDate', '<=2021-03-28') 1060 "AND date(creationDate, 'unixepoch', 'localtime') <= date('2021-03-28')" 1061 1062 >>> make_unixtime_filter('creationDate', None) 1063 '' 1064 1065 """ 1066 if value is None: 1067 return "" 1068 1069 if isinstance(value, bool): 1070 return make_filter(date_column, value) 1071 1072 # Check for ISO 8601 date str + optional operator 1073 match = match_date(value) 1074 if match: 1075 comparator, isodate = match.groups() 1076 if not comparator: 1077 comparator = "==" 1078 threshold = f"date('{isodate}')" 1079 else: 1080 # "future" or "past" 1081 validate("value", value, ["future", "past"]) 1082 threshold = "date('now', 'localtime')" 1083 comparator = ">" if value == "future" else "<=" 1084 1085 date = f"date({date_column}, 'unixepoch', 'localtime')" 1086 1087 return f"AND {date} {comparator} {threshold}" 1088 1089 1090def make_unixtime_range_filter(date_column: str, offset) -> str: 1091 """ 1092 Return a SQL filter to limit a Unix time to last X days, weeks, or years. 1093 1094 Parameters 1095 ---------- 1096 date_column : str 1097 Name of the column that has datetime information on a task 1098 stored in UNIX time, that is, number of seconds since 1099 1970-01-01 00:00 UTC. 1100 1101 offset : str or None 1102 A string comprised of an integer and a single character that can 1103 be 'd', 'w', or 'y' that determines whether to return all tasks 1104 for the past X days, weeks, or years. 1105 1106 Returns 1107 ------- 1108 str 1109 A date filter for the SQL query. If `offset == None`, then 1110 return the empty string. 1111 1112 Examples 1113 -------- 1114 >>> make_unixtime_range_filter('creationDate', '3d') 1115 "AND datetime(creationDate, 'unixepoch', 'localtime') > datetime('now', '-3 days')" 1116 1117 >>> make_unixtime_range_filter('creationDate', None) 1118 '' 1119 """ 1120 if offset is None: 1121 return "" 1122 1123 validate_offset("offset", offset) 1124 number, suffix = int(offset[:-1]), offset[-1] 1125 1126 if suffix == "d": 1127 modifier = f"-{number} days" 1128 elif suffix == "w": 1129 modifier = f"-{number * 7} days" 1130 elif suffix == "y": 1131 modifier = f"-{number} years" 1132 else: 1133 # Use `typing.assert_never(suffix)` from Python 3.11 onwards. 1134 raise AssertionError("line should be unreachable.") # for static code analyzers 1135 1136 column_datetime = f"datetime({date_column}, 'unixepoch', 'localtime')" 1137 offset_datetime = f"datetime('now', '{modifier}')" 1138 1139 return f"AND {column_datetime} > {offset_datetime}" 1140 1141 1142def match_date(value): 1143 """Return a match object if value is an ISO 8601 date str.""" 1144 return re.fullmatch(r"(=|==|<|<=|>|>=)?(\d{4}-\d{2}-\d{2})", value) 1145 1146 1147def null_if_unset_thingsdate(sql_expression: str) -> str: 1148 """ 1149 Return SQL expression as string. 1150 1151 It is NULL when the "Things date" is unset, else the Things date itself. 1152 Things stores 0001-01-01 instead of NULL in some date columns, such as 1153 `rt1_nextInstanceStartDate` when a repeating to-do has no next instance. 1154 Sometimes bits below the day field are set as well (69769 rather than 1155 69760), so any date in year 1 counts as unset. 1156 1157 Parameters 1158 ---------- 1159 sql_expression : str 1160 A sql expression pointing to a "Things date" integer in 1161 format YYYYYYYYYYYMMMMDDDDD0000000, in binary. 1162 See: `convert_isodate_sql_expression_to_thingsdate` for details. 1163 1164 Example 1165 ------- 1166 >>> null_if_unset_thingsdate('rt1_nextInstanceStartDate') 1167 'CASE WHEN rt1_nextInstanceStartDate >= 135296 \ 1168 THEN rt1_nextInstanceStartDate END' 1169 """ 1170 first_real_thingsdate = isodate_to_yyyyyyyyyyymmmmddddd("0002-01-01") 1171 return ( 1172 f"CASE WHEN {sql_expression} >= {first_real_thingsdate}" 1173 f" THEN {sql_expression} END" 1174 ) 1175 1176 1177def prettify_sql(sql_query): 1178 """Make a SQL query easier to read for humans.""" 1179 # remove indentation and leading and trailing whitespace 1180 result = dedent(sql_query).strip() 1181 # remove empty lines 1182 return re.sub(r"^$\n", "", result, flags=re.MULTILINE) 1183 1184 1185def remove_prefix(text, prefix): 1186 """Remove prefix from text (as removeprefix() is 3.9+ only).""" 1187 return text[text.startswith(prefix) and len(prefix) :] 1188 1189 1190def validate(parameter, argument, valid_arguments): 1191 """ 1192 For a given parameter, check if its argument type is valid. 1193 1194 If not, then raise `ValueError`. 1195 1196 Examples 1197 -------- 1198 >>> validate( 1199 ... parameter='status', 1200 ... argument='completed', 1201 ... valid_arguments=['incomplete', 'completed'] 1202 ... ) 1203 ... 1204 1205 >>> validate( 1206 ... parameter='status', 1207 ... argument='XYZXZY', 1208 ... valid_arguments=['incomplete', 'completed'] 1209 ... ) 1210 Traceback (most recent call last): 1211 ... 1212 ValueError: Unrecognized status type: 'XYZXZY' 1213 Valid status types are ['incomplete', 'completed'] 1214 """ 1215 if argument in valid_arguments: 1216 return 1217 message = f"Unrecognized {parameter} type: {argument!r}" 1218 message += f"\nValid {parameter} types are {valid_arguments}" 1219 raise ValueError(message) 1220 1221 1222def validate_date(parameter, argument): 1223 """ 1224 For a given date parameter, check if its argument is valid. 1225 1226 If not, then raise `ValueError`. 1227 1228 Examples 1229 -------- 1230 >>> validate_date(parameter='startDate', argument=None) 1231 >>> validate_date(parameter='startDate', argument='future') 1232 >>> validate_date(parameter='startDate', argument='2020-01-01') 1233 >>> validate_date(parameter='startDate', argument='<=2020-01-01') 1234 1235 >>> validate_date(parameter='stopDate', argument='=2020-01-01') 1236 1237 >>> validate_date(parameter='deadline', argument='XYZ') 1238 Traceback (most recent call last): 1239 ... 1240 ValueError: Invalid deadline argument: 'XYZ' 1241 Please see the documentation for `deadline` in `things.tasks`. 1242 """ 1243 if argument is None: 1244 return 1245 1246 if argument in list(DATES): 1247 return 1248 1249 if not isinstance(argument, str): 1250 raise ValueError( 1251 f"Invalid {parameter} argument: {argument!r}\n" 1252 f"Please specify a string or None." 1253 ) 1254 1255 match = match_date(argument) 1256 if not match: 1257 raise ValueError( 1258 f"Invalid {parameter} argument: {argument!r}\n" 1259 f"Please see the documentation for `{parameter}` in `things.tasks`." 1260 ) 1261 1262 _, isodate = match.groups() 1263 try: 1264 datetime.date.fromisoformat(isodate) 1265 except ValueError as error: 1266 raise ValueError( 1267 f"Invalid {parameter} argument: {argument!r}\n{error}" 1268 ) from error 1269 1270 1271def validate_offset(parameter, argument): 1272 """ 1273 For a given offset parameter, check if its argument is valid. 1274 1275 If not, then raise `ValueError`. 1276 1277 Examples 1278 -------- 1279 >>> validate_offset(parameter='last', argument='3d') 1280 1281 >>> validate_offset(parameter='last', argument='XYZ') 1282 Traceback (most recent call last): 1283 ... 1284 ValueError: Invalid last argument: 'XYZ' 1285 Please specify a string of the format 'X[d/w/y]' where X is ... 1286 """ 1287 if argument is None: 1288 return 1289 1290 if not isinstance(argument, str): 1291 raise ValueError( 1292 f"Invalid {parameter} argument: {argument!r}\n" 1293 f"Please specify a string or None." 1294 ) 1295 1296 suffix = argument[-1:] # slicing here to handle empty strings 1297 if suffix not in ("d", "w", "y"): 1298 raise ValueError( 1299 f"Invalid {parameter} argument: {argument!r}\n" 1300 f"Please specify a string of the format 'X[d/w/y]' " 1301 "where X is a non-negative integer followed by 'd', 'w', or 'y' " 1302 "that indicates days, weeks, or years." 1303 )
157class Database: 158 """ 159 Access Things SQL database. 160 161 Parameters 162 ---------- 163 filepath : str, optional 164 Any valid path of a SQLite database file generated by the Things app. 165 If the environment variable `THINGSDB` is set, then use that path. 166 Otherwise, access the default database path. 167 168 print_sql : bool, default False 169 Print every SQL query performed. Some may contain '?' and ':' 170 characters which correspond to SQLite parameter tokens. 171 See https://www.sqlite.org/lang_expr.html#varparam 172 173 :raises AssertionError: If the database version is too old. 174 """ 175 176 debug = False 177 connection: sqlite3.Connection 178 179 # pylint: disable=R0913 180 def __init__(self, filepath=None, print_sql=False): 181 """Set up the database.""" 182 self.filepath = ( 183 filepath 184 or os.getenv(ENVIRONMENT_VARIABLE_WITH_FILEPATH) 185 or get_default_database_filepath(raise_on_error=True) 186 ) 187 self.print_sql = print_sql 188 if self.print_sql: 189 self.execute_query_count = 0 190 191 # "ro" means read-only 192 # See: https://sqlite.org/uri.html#recognized_query_parameters 193 uri = f"file:{self.filepath}?mode=ro" 194 self.connection = sqlite3.connect(uri, uri=True) # pylint: disable=E1101 195 # Close the underlying SQLite connection when this Database object is garbage collected 196 weakref.finalize(self, sqlite3.Connection.close, self.connection) 197 198 # Test for migrated database in Things 3.17.4+ 199 assert self.get_version() > 21, ( 200 "Your database is in an older format. " 201 "Run 'pip install things.py==0.0.14' to downgrade to an older " 202 "version of this library." 203 ) 204 205 # Core methods 206 207 def get_tasks( # pylint: disable=R0914,R0913,R0917 208 self, 209 uuid: str | None = None, 210 type: str | None = None, # pylint: disable=W0622 211 status: str | None = None, 212 start: str | None = None, 213 area: str | bool | None = None, 214 project: str | bool | None = None, 215 heading: str | None = None, 216 tag: str | bool | None = None, 217 start_date: str | bool | None = None, 218 stop_date: str | bool | None = None, 219 deadline: str | bool | None = None, 220 deadline_suppressed: bool | None = None, 221 trashed: bool | None = False, 222 context_trashed: bool | None = False, 223 is_repeating_template: bool | None = False, 224 next_instance_start_date: str | bool | None = None, 225 last: str | None = None, 226 search_query: str | None = None, 227 index: str = "index", 228 count_only: bool = False, 229 ): 230 """Get tasks. See `things.api.tasks` for details on parameters.""" 231 if uuid: 232 return self.get_task_by_uuid(uuid, count_only=count_only) 233 234 # Overwrites 235 start = start and start.title() 236 237 # Validation 238 validate_date("deadline", deadline) 239 validate("deadline_suppressed", deadline_suppressed, [None, True, False]) 240 validate("start", start, [None] + list(START_TO_FILTER)) 241 validate_date("start_date", start_date) 242 validate_date("next_instance_start_date", next_instance_start_date) 243 validate_date("stop_date", stop_date) 244 validate("status", status, [None] + list(STATUS_TO_FILTER)) 245 validate("trashed", trashed, [None] + list(TRASHED_TO_FILTER)) 246 validate("type", type, [None] + list(TYPE_TO_FILTER)) 247 validate("context_trashed", context_trashed, [None, True, False]) 248 validate( 249 "is_repeating_template", 250 is_repeating_template, 251 [None] + list(REPEATING_TEMPLATE_TO_FILTER), 252 ) 253 validate("index", index, list(INDICES)) 254 validate_offset("last", last) 255 256 if tag is not None: 257 valid_tags = self.get_tags(titles_only=True) 258 validate("tag", tag, [None] + list(valid_tags)) 259 260 # Query 261 # TK: might consider executing SQL with parameters instead. 262 # See: https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute 263 264 start_filter: str = START_TO_FILTER.get(start, "") # type: ignore 265 status_filter: str = STATUS_TO_FILTER.get(status, "") # type: ignore 266 trashed_filter: str = TRASHED_TO_FILTER.get(trashed, "") # type: ignore 267 type_filter: str = TYPE_TO_FILTER.get(type, "") # type: ignore 268 repeating_template_filter: str = REPEATING_TEMPLATE_TO_FILTER.get( 269 is_repeating_template, # type: ignore 270 "", 271 ) 272 next_instance_start_date_filter = make_thingsdate_filter( 273 f"TASK.{DATE_NEXT_INSTANCE_START}", next_instance_start_date 274 ) 275 276 # Sometimes a task is _not_ set to trashed, but its context 277 # (project or heading it is contained within) is set to trashed. 278 # In those cases, the task wouldn't show up in any app view 279 # except for "Trash". 280 project_trashed_filter = make_truthy_filter("PROJECT.trashed", context_trashed) 281 project_of_heading_trashed_filter = make_truthy_filter( 282 "PROJECT_OF_HEADING.trashed", context_trashed 283 ) 284 285 # As a task assigned to a heading is not directly assigned to a project anymore, 286 # we need to check if the heading is assigned to a project. 287 # See, e.g. https://github.com/thingsapi/things.py/issues/94 288 project_filter = make_or_filter( 289 make_filter("TASK.project", project), 290 make_filter("PROJECT_OF_HEADING.uuid", project), 291 ) 292 293 where_predicate = f""" 294 TRUE 295 {repeating_template_filter and f"AND TASK.{repeating_template_filter}"} 296 {trashed_filter and f"AND TASK.{trashed_filter}"} 297 {project_trashed_filter} 298 {project_of_heading_trashed_filter} 299 {type_filter and f"AND TASK.{type_filter}"} 300 {start_filter and f"AND TASK.{start_filter}"} 301 {status_filter and f"AND TASK.{status_filter}"} 302 {make_filter("TASK.uuid", uuid)} 303 {make_filter("TASK.area", area)} 304 {project_filter} 305 {make_filter("TASK.heading", heading)} 306 {make_filter("TASK.deadlineSuppressionDate", deadline_suppressed)} 307 {make_filter("TAG.title", tag)} 308 {make_thingsdate_filter(f"TASK.{DATE_START}", start_date)} 309 {next_instance_start_date_filter} 310 {make_unixtime_filter(f"TASK.{DATE_STOP}", stop_date)} 311 {make_thingsdate_filter(f"TASK.{DATE_DEADLINE}", deadline)} 312 {make_unixtime_range_filter(f"TASK.{DATE_CREATED}", last)} 313 {make_search_filter(search_query)} 314 """ 315 order_predicate = f'TASK."{index}"' 316 317 sql_query = make_tasks_sql_query(where_predicate, order_predicate) 318 319 if count_only: 320 return self.get_count(sql_query) 321 322 return self.execute_query(sql_query) 323 324 def get_task_by_uuid(self, uuid, count_only=False): 325 """Get a task by uuid. Raise `ValueError` if not found.""" 326 where_predicate = "TASK.uuid = ?" 327 sql_query = make_tasks_sql_query(where_predicate) 328 parameters = (uuid,) 329 330 if count_only: 331 return self.get_count(sql_query, parameters) 332 333 result = self.execute_query(sql_query, parameters) 334 if not result: 335 raise ValueError(f"No such task uuid found: {uuid!r}") 336 337 return result 338 339 def get_areas(self, uuid=None, tag=None, count_only=False): 340 """Get areas. See `api.areas` for details on parameters.""" 341 # Validation 342 if tag is not None: 343 valid_tags = self.get_tags(titles_only=True) 344 validate("tag", tag, [None] + list(valid_tags)) 345 346 if ( 347 uuid 348 and count_only is False 349 and not self.get_areas(uuid=uuid, count_only=True) 350 ): 351 raise ValueError(f"No such area uuid found: {uuid!r}") 352 353 # Query 354 sql_query = f""" 355 SELECT DISTINCT 356 AREA.uuid, 357 'area' as type, 358 AREA.title, 359 CASE 360 WHEN AREA_TAG.areas IS NOT NULL THEN 1 361 END AS tags 362 FROM 363 {TABLE_AREA} AS AREA 364 LEFT OUTER JOIN 365 {TABLE_AREATAG} AREA_TAG ON AREA_TAG.areas = AREA.uuid 366 LEFT OUTER JOIN 367 {TABLE_TAG} TAG ON TAG.uuid = AREA_TAG.tags 368 WHERE 369 TRUE 370 {make_filter("TAG.title", tag)} 371 {make_filter("AREA.uuid", uuid)} 372 ORDER BY AREA."index" 373 """ 374 375 if count_only: 376 return self.get_count(sql_query) 377 378 return self.execute_query(sql_query) 379 380 def get_checklist_items(self, todo_uuid=None): 381 """Get checklist items.""" 382 sql_query = f""" 383 SELECT 384 CHECKLIST_ITEM.title, 385 CASE 386 WHEN CHECKLIST_ITEM.{IS_INCOMPLETE} THEN 'incomplete' 387 WHEN CHECKLIST_ITEM.{IS_CANCELED} THEN 'canceled' 388 WHEN CHECKLIST_ITEM.{IS_COMPLETED} THEN 'completed' 389 END AS status, 390 date(CHECKLIST_ITEM.stopDate, "unixepoch", "localtime") AS stop_date, 391 'checklist-item' as type, 392 CHECKLIST_ITEM.uuid, 393 datetime( 394 CHECKLIST_ITEM.{DATE_MODIFIED}, "unixepoch", "localtime" 395 ) AS created, 396 datetime( 397 CHECKLIST_ITEM.{DATE_MODIFIED}, "unixepoch", "localtime" 398 ) AS modified 399 FROM 400 {TABLE_CHECKLIST_ITEM} AS CHECKLIST_ITEM 401 WHERE 402 CHECKLIST_ITEM.task = ? 403 ORDER BY CHECKLIST_ITEM."index" 404 """ 405 return self.execute_query(sql_query, (todo_uuid,)) 406 407 def get_tags(self, title=None, area=None, task=None, titles_only=False): 408 """Get tags. See `api.tags` for details on parameters.""" 409 # Validation 410 if title is not None: 411 valid_titles = self.get_tags(titles_only=True) 412 validate("title", title, [None] + list(valid_titles)) 413 414 # Query 415 if task: 416 return self.get_tags_of_task(task) 417 if area: 418 return self.get_tags_of_area(area) 419 420 if titles_only: 421 sql_query = f'SELECT title FROM {TABLE_TAG} ORDER BY "index"' 422 return self.execute_query(sql_query, row_factory=list_factory) 423 424 sql_query = f""" 425 SELECT 426 uuid, 'tag' AS type, title, shortcut 427 FROM 428 {TABLE_TAG} 429 WHERE 430 TRUE 431 {make_filter("title", title)} 432 ORDER BY "index" 433 """ 434 435 return self.execute_query(sql_query) 436 437 def get_tags_of_task(self, task_uuid): 438 """Get tag titles of task.""" 439 sql_query = f""" 440 SELECT 441 TAG.title 442 FROM 443 {TABLE_TASKTAG} AS TASK_TAG 444 LEFT OUTER JOIN 445 {TABLE_TAG} TAG ON TAG.uuid = TASK_TAG.tags 446 WHERE 447 TASK_TAG.tasks = ? 448 ORDER BY TAG."index" 449 """ 450 return self.execute_query( 451 sql_query, parameters=(task_uuid,), row_factory=list_factory 452 ) 453 454 def get_tags_of_area(self, area_uuid): 455 """Get tag titles for area.""" 456 sql_query = f""" 457 SELECT 458 AREA.title 459 FROM 460 {TABLE_AREATAG} AS AREA_TAG 461 LEFT OUTER JOIN 462 {TABLE_TAG} AREA ON AREA.uuid = AREA_TAG.tags 463 WHERE 464 AREA_TAG.areas = ? 465 ORDER BY AREA."index" 466 """ 467 return self.execute_query( 468 sql_query, parameters=(area_uuid,), row_factory=list_factory 469 ) 470 471 def get_version(self): 472 """Get Things Database version.""" 473 sql_query = f"SELECT value FROM {TABLE_META} WHERE key = 'databaseVersion'" 474 result = self.execute_query(sql_query, row_factory=list_factory) 475 plist_bytes = result[0].encode() 476 return plistlib.loads(plist_bytes) 477 478 # pylint: disable=R1710 479 def get_url_scheme_auth_token(self): 480 """Get the Things URL scheme authentication token.""" 481 sql_query = f""" 482 SELECT 483 uriSchemeAuthenticationToken 484 FROM 485 {TABLE_SETTINGS} 486 WHERE 487 uuid = 'RhAzEf6qDxCD5PmnZVtBZR' 488 """ 489 rows = self.execute_query(sql_query, row_factory=list_factory) 490 return rows[0] 491 492 def get_count(self, sql_query, parameters=()): 493 """Count number of results.""" 494 count_sql_query = f"""SELECT COUNT(uuid) FROM (\n{sql_query}\n)""" 495 rows = self.execute_query( 496 count_sql_query, row_factory=list_factory, parameters=parameters 497 ) 498 return rows[0] 499 500 # TODO: add type hinting for result (List[Tuple[str, Any]]?) # pylint: disable=fixme 501 def execute_query(self, sql_query, parameters=(), row_factory=None): 502 """Run the actual SQL query.""" 503 if self.print_sql or self.debug: 504 if not hasattr(self, "execute_query_count"): 505 # This is needed for historical `self.debug`. 506 # TK: might consider removing `debug` flag. 507 self.execute_query_count = 0 508 self.execute_query_count += 1 509 if self.debug: 510 print(f"/* Filepath {self.filepath!r} */") 511 print(f"/* Query {self.execute_query_count} */") 512 if parameters: 513 print(f"/* Parameters: {parameters!r} */") 514 print() 515 print(prettify_sql(sql_query)) 516 print() 517 518 with self.connection: 519 # Using context manager to keep queries in separate transactions, 520 # see https://docs.python.org/3/library/sqlite3.html#sqlite3-connection-context-manager 521 self.connection.row_factory = row_factory or dict_factory 522 cursor = self.connection.cursor() 523 cursor.execute(sql_query, parameters) 524 525 return cursor.fetchall()
Access Things SQL database.
Parameters
- filepath (str, optional):
Any valid path of a SQLite database file generated by the Things app.
If the environment variable
THINGSDBis set, then use that path. Otherwise, access the default database path. - print_sql (bool, default False): Print every SQL query performed. Some may contain '?' and ':' characters which correspond to SQLite parameter tokens. See https://www.sqlite.org/lang_expr.html#varparam
- ###### Raises: - AssertionError: If the database version is too old.
180 def __init__(self, filepath=None, print_sql=False): 181 """Set up the database.""" 182 self.filepath = ( 183 filepath 184 or os.getenv(ENVIRONMENT_VARIABLE_WITH_FILEPATH) 185 or get_default_database_filepath(raise_on_error=True) 186 ) 187 self.print_sql = print_sql 188 if self.print_sql: 189 self.execute_query_count = 0 190 191 # "ro" means read-only 192 # See: https://sqlite.org/uri.html#recognized_query_parameters 193 uri = f"file:{self.filepath}?mode=ro" 194 self.connection = sqlite3.connect(uri, uri=True) # pylint: disable=E1101 195 # Close the underlying SQLite connection when this Database object is garbage collected 196 weakref.finalize(self, sqlite3.Connection.close, self.connection) 197 198 # Test for migrated database in Things 3.17.4+ 199 assert self.get_version() > 21, ( 200 "Your database is in an older format. " 201 "Run 'pip install things.py==0.0.14' to downgrade to an older " 202 "version of this library." 203 )
Set up the database.
207 def get_tasks( # pylint: disable=R0914,R0913,R0917 208 self, 209 uuid: str | None = None, 210 type: str | None = None, # pylint: disable=W0622 211 status: str | None = None, 212 start: str | None = None, 213 area: str | bool | None = None, 214 project: str | bool | None = None, 215 heading: str | None = None, 216 tag: str | bool | None = None, 217 start_date: str | bool | None = None, 218 stop_date: str | bool | None = None, 219 deadline: str | bool | None = None, 220 deadline_suppressed: bool | None = None, 221 trashed: bool | None = False, 222 context_trashed: bool | None = False, 223 is_repeating_template: bool | None = False, 224 next_instance_start_date: str | bool | None = None, 225 last: str | None = None, 226 search_query: str | None = None, 227 index: str = "index", 228 count_only: bool = False, 229 ): 230 """Get tasks. See `things.api.tasks` for details on parameters.""" 231 if uuid: 232 return self.get_task_by_uuid(uuid, count_only=count_only) 233 234 # Overwrites 235 start = start and start.title() 236 237 # Validation 238 validate_date("deadline", deadline) 239 validate("deadline_suppressed", deadline_suppressed, [None, True, False]) 240 validate("start", start, [None] + list(START_TO_FILTER)) 241 validate_date("start_date", start_date) 242 validate_date("next_instance_start_date", next_instance_start_date) 243 validate_date("stop_date", stop_date) 244 validate("status", status, [None] + list(STATUS_TO_FILTER)) 245 validate("trashed", trashed, [None] + list(TRASHED_TO_FILTER)) 246 validate("type", type, [None] + list(TYPE_TO_FILTER)) 247 validate("context_trashed", context_trashed, [None, True, False]) 248 validate( 249 "is_repeating_template", 250 is_repeating_template, 251 [None] + list(REPEATING_TEMPLATE_TO_FILTER), 252 ) 253 validate("index", index, list(INDICES)) 254 validate_offset("last", last) 255 256 if tag is not None: 257 valid_tags = self.get_tags(titles_only=True) 258 validate("tag", tag, [None] + list(valid_tags)) 259 260 # Query 261 # TK: might consider executing SQL with parameters instead. 262 # See: https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute 263 264 start_filter: str = START_TO_FILTER.get(start, "") # type: ignore 265 status_filter: str = STATUS_TO_FILTER.get(status, "") # type: ignore 266 trashed_filter: str = TRASHED_TO_FILTER.get(trashed, "") # type: ignore 267 type_filter: str = TYPE_TO_FILTER.get(type, "") # type: ignore 268 repeating_template_filter: str = REPEATING_TEMPLATE_TO_FILTER.get( 269 is_repeating_template, # type: ignore 270 "", 271 ) 272 next_instance_start_date_filter = make_thingsdate_filter( 273 f"TASK.{DATE_NEXT_INSTANCE_START}", next_instance_start_date 274 ) 275 276 # Sometimes a task is _not_ set to trashed, but its context 277 # (project or heading it is contained within) is set to trashed. 278 # In those cases, the task wouldn't show up in any app view 279 # except for "Trash". 280 project_trashed_filter = make_truthy_filter("PROJECT.trashed", context_trashed) 281 project_of_heading_trashed_filter = make_truthy_filter( 282 "PROJECT_OF_HEADING.trashed", context_trashed 283 ) 284 285 # As a task assigned to a heading is not directly assigned to a project anymore, 286 # we need to check if the heading is assigned to a project. 287 # See, e.g. https://github.com/thingsapi/things.py/issues/94 288 project_filter = make_or_filter( 289 make_filter("TASK.project", project), 290 make_filter("PROJECT_OF_HEADING.uuid", project), 291 ) 292 293 where_predicate = f""" 294 TRUE 295 {repeating_template_filter and f"AND TASK.{repeating_template_filter}"} 296 {trashed_filter and f"AND TASK.{trashed_filter}"} 297 {project_trashed_filter} 298 {project_of_heading_trashed_filter} 299 {type_filter and f"AND TASK.{type_filter}"} 300 {start_filter and f"AND TASK.{start_filter}"} 301 {status_filter and f"AND TASK.{status_filter}"} 302 {make_filter("TASK.uuid", uuid)} 303 {make_filter("TASK.area", area)} 304 {project_filter} 305 {make_filter("TASK.heading", heading)} 306 {make_filter("TASK.deadlineSuppressionDate", deadline_suppressed)} 307 {make_filter("TAG.title", tag)} 308 {make_thingsdate_filter(f"TASK.{DATE_START}", start_date)} 309 {next_instance_start_date_filter} 310 {make_unixtime_filter(f"TASK.{DATE_STOP}", stop_date)} 311 {make_thingsdate_filter(f"TASK.{DATE_DEADLINE}", deadline)} 312 {make_unixtime_range_filter(f"TASK.{DATE_CREATED}", last)} 313 {make_search_filter(search_query)} 314 """ 315 order_predicate = f'TASK."{index}"' 316 317 sql_query = make_tasks_sql_query(where_predicate, order_predicate) 318 319 if count_only: 320 return self.get_count(sql_query) 321 322 return self.execute_query(sql_query)
Get tasks. See things.api.tasks for details on parameters.
324 def get_task_by_uuid(self, uuid, count_only=False): 325 """Get a task by uuid. Raise `ValueError` if not found.""" 326 where_predicate = "TASK.uuid = ?" 327 sql_query = make_tasks_sql_query(where_predicate) 328 parameters = (uuid,) 329 330 if count_only: 331 return self.get_count(sql_query, parameters) 332 333 result = self.execute_query(sql_query, parameters) 334 if not result: 335 raise ValueError(f"No such task uuid found: {uuid!r}") 336 337 return result
Get a task by uuid. Raise ValueError if not found.
339 def get_areas(self, uuid=None, tag=None, count_only=False): 340 """Get areas. See `api.areas` for details on parameters.""" 341 # Validation 342 if tag is not None: 343 valid_tags = self.get_tags(titles_only=True) 344 validate("tag", tag, [None] + list(valid_tags)) 345 346 if ( 347 uuid 348 and count_only is False 349 and not self.get_areas(uuid=uuid, count_only=True) 350 ): 351 raise ValueError(f"No such area uuid found: {uuid!r}") 352 353 # Query 354 sql_query = f""" 355 SELECT DISTINCT 356 AREA.uuid, 357 'area' as type, 358 AREA.title, 359 CASE 360 WHEN AREA_TAG.areas IS NOT NULL THEN 1 361 END AS tags 362 FROM 363 {TABLE_AREA} AS AREA 364 LEFT OUTER JOIN 365 {TABLE_AREATAG} AREA_TAG ON AREA_TAG.areas = AREA.uuid 366 LEFT OUTER JOIN 367 {TABLE_TAG} TAG ON TAG.uuid = AREA_TAG.tags 368 WHERE 369 TRUE 370 {make_filter("TAG.title", tag)} 371 {make_filter("AREA.uuid", uuid)} 372 ORDER BY AREA."index" 373 """ 374 375 if count_only: 376 return self.get_count(sql_query) 377 378 return self.execute_query(sql_query)
Get areas. See api.areas for details on parameters.
380 def get_checklist_items(self, todo_uuid=None): 381 """Get checklist items.""" 382 sql_query = f""" 383 SELECT 384 CHECKLIST_ITEM.title, 385 CASE 386 WHEN CHECKLIST_ITEM.{IS_INCOMPLETE} THEN 'incomplete' 387 WHEN CHECKLIST_ITEM.{IS_CANCELED} THEN 'canceled' 388 WHEN CHECKLIST_ITEM.{IS_COMPLETED} THEN 'completed' 389 END AS status, 390 date(CHECKLIST_ITEM.stopDate, "unixepoch", "localtime") AS stop_date, 391 'checklist-item' as type, 392 CHECKLIST_ITEM.uuid, 393 datetime( 394 CHECKLIST_ITEM.{DATE_MODIFIED}, "unixepoch", "localtime" 395 ) AS created, 396 datetime( 397 CHECKLIST_ITEM.{DATE_MODIFIED}, "unixepoch", "localtime" 398 ) AS modified 399 FROM 400 {TABLE_CHECKLIST_ITEM} AS CHECKLIST_ITEM 401 WHERE 402 CHECKLIST_ITEM.task = ? 403 ORDER BY CHECKLIST_ITEM."index" 404 """ 405 return self.execute_query(sql_query, (todo_uuid,))
Get checklist items.
471 def get_version(self): 472 """Get Things Database version.""" 473 sql_query = f"SELECT value FROM {TABLE_META} WHERE key = 'databaseVersion'" 474 result = self.execute_query(sql_query, row_factory=list_factory) 475 plist_bytes = result[0].encode() 476 return plistlib.loads(plist_bytes)
Get Things Database version.
479 def get_url_scheme_auth_token(self): 480 """Get the Things URL scheme authentication token.""" 481 sql_query = f""" 482 SELECT 483 uriSchemeAuthenticationToken 484 FROM 485 {TABLE_SETTINGS} 486 WHERE 487 uuid = 'RhAzEf6qDxCD5PmnZVtBZR' 488 """ 489 rows = self.execute_query(sql_query, row_factory=list_factory) 490 return rows[0]
Get the Things URL scheme authentication token.
492 def get_count(self, sql_query, parameters=()): 493 """Count number of results.""" 494 count_sql_query = f"""SELECT COUNT(uuid) FROM (\n{sql_query}\n)""" 495 rows = self.execute_query( 496 count_sql_query, row_factory=list_factory, parameters=parameters 497 ) 498 return rows[0]
Count number of results.
501 def execute_query(self, sql_query, parameters=(), row_factory=None): 502 """Run the actual SQL query.""" 503 if self.print_sql or self.debug: 504 if not hasattr(self, "execute_query_count"): 505 # This is needed for historical `self.debug`. 506 # TK: might consider removing `debug` flag. 507 self.execute_query_count = 0 508 self.execute_query_count += 1 509 if self.debug: 510 print(f"/* Filepath {self.filepath!r} */") 511 print(f"/* Query {self.execute_query_count} */") 512 if parameters: 513 print(f"/* Parameters: {parameters!r} */") 514 print() 515 print(prettify_sql(sql_query)) 516 print() 517 518 with self.connection: 519 # Using context manager to keep queries in separate transactions, 520 # see https://docs.python.org/3/library/sqlite3.html#sqlite3-connection-context-manager 521 self.connection.row_factory = row_factory or dict_factory 522 cursor = self.connection.cursor() 523 cursor.execute(sql_query, parameters) 524 525 return cursor.fetchall()
Run the actual SQL query.
531def make_tasks_sql_query(where_predicate=None, order_predicate=None): 532 """Make SQL query for Task table.""" 533 where_predicate = where_predicate or "TRUE" 534 order_predicate = order_predicate or 'TASK."index"' 535 536 start_date_expression = convert_thingsdate_sql_expression_to_isodate( 537 f"TASK.{DATE_START}" 538 ) 539 deadline_expression = convert_thingsdate_sql_expression_to_isodate( 540 f"TASK.{DATE_DEADLINE}" 541 ) 542 reminder_time_expression = convert_thingstime_sql_expression_to_isotime( 543 f"TASK.{REMINDER_TIME}" 544 ) 545 next_instance_start_date_expression = convert_thingsdate_sql_expression_to_isodate( 546 null_if_unset_thingsdate(f"TASK.{DATE_NEXT_INSTANCE_START}") 547 ) 548 549 return f""" 550 SELECT DISTINCT 551 TASK.uuid, 552 CASE 553 WHEN TASK.{IS_TODO} THEN 'to-do' 554 WHEN TASK.{IS_PROJECT} THEN 'project' 555 WHEN TASK.{IS_HEADING} THEN 'heading' 556 END AS type, 557 CASE 558 WHEN TASK.{IS_TRASHED} THEN 1 559 END AS trashed, 560 TASK.title, 561 CASE 562 WHEN TASK.{IS_INCOMPLETE} THEN 'incomplete' 563 WHEN TASK.{IS_CANCELED} THEN 'canceled' 564 WHEN TASK.{IS_COMPLETED} THEN 'completed' 565 END AS status, 566 CASE 567 WHEN AREA.uuid IS NOT NULL THEN AREA.uuid 568 END AS area, 569 CASE 570 WHEN AREA.uuid IS NOT NULL THEN AREA.title 571 END AS area_title, 572 CASE 573 WHEN PROJECT.uuid IS NOT NULL THEN PROJECT.uuid 574 END AS project, 575 CASE 576 WHEN PROJECT.uuid IS NOT NULL THEN PROJECT.title 577 END AS project_title, 578 CASE 579 WHEN HEADING.uuid IS NOT NULL THEN HEADING.uuid 580 END AS heading, 581 CASE 582 WHEN HEADING.uuid IS NOT NULL THEN HEADING.title 583 END AS heading_title, 584 TASK.notes, 585 CASE 586 WHEN TAG.uuid IS NOT NULL THEN 1 587 END AS tags, 588 CASE 589 WHEN TASK.{IS_INBOX} THEN 'Inbox' 590 WHEN TASK.{IS_ANYTIME} THEN 'Anytime' 591 WHEN TASK.{IS_SOMEDAY} THEN 'Someday' 592 END AS start, 593 CASE 594 WHEN CHECKLIST_ITEM.uuid IS NOT NULL THEN 1 595 END AS checklist, 596 {start_date_expression} AS start_date, 597 {next_instance_start_date_expression} AS next_instance_start_date, 598 {deadline_expression} AS deadline, 599 {reminder_time_expression} AS "reminder_time", 600 datetime(TASK.{DATE_STOP}, "unixepoch", "localtime") AS "stop_date", 601 datetime(TASK.{DATE_CREATED}, "unixepoch", "localtime") AS created, 602 datetime(TASK.{DATE_MODIFIED}, "unixepoch", "localtime") AS modified, 603 TASK.'index', 604 TASK.todayIndex AS today_index 605 FROM 606 {TABLE_TASK} AS TASK 607 LEFT OUTER JOIN 608 {TABLE_TASK} PROJECT ON TASK.project = PROJECT.uuid 609 LEFT OUTER JOIN 610 {TABLE_AREA} AREA ON TASK.area = AREA.uuid 611 LEFT OUTER JOIN 612 {TABLE_TASK} HEADING ON TASK.heading = HEADING.uuid 613 LEFT OUTER JOIN 614 {TABLE_TASK} PROJECT_OF_HEADING 615 ON HEADING.project = PROJECT_OF_HEADING.uuid 616 LEFT OUTER JOIN 617 {TABLE_TASKTAG} TAGS ON TASK.uuid = TAGS.tasks 618 LEFT OUTER JOIN 619 {TABLE_TAG} TAG ON TAGS.tags = TAG.uuid 620 LEFT OUTER JOIN 621 {TABLE_CHECKLIST_ITEM} CHECKLIST_ITEM 622 ON TASK.uuid = CHECKLIST_ITEM.task 623 WHERE 624 {where_predicate} 625 ORDER BY 626 {order_predicate} 627 """
Make SQL query for Task table.
633def convert_isodate_sql_expression_to_thingsdate(sql_expression, null_possible=True): 634 """ 635 Return a SQL expression of an isodate converted into a "Things date". 636 637 A _Things date_ is an integer where the binary digits are 638 YYYYYYYYYYYMMMMDDDDD0000000; Y is year, M is month, and D is day. 639 640 For example, the ISO 8601 date '2021-03-28' corresponds to the Things 641 date 132464128 as integer; in binary that is: 642 111111001010011111000000000 643 YYYYYYYYYYYMMMMDDDDD0000000 644 2021 3 28 645 646 Parameters 647 ---------- 648 sql_expression : str 649 A sql expression evaluating to an ISO 8601 date str. 650 651 null_possible : bool 652 Can the input `sql_expression` evaluate to NULL? 653 654 Returns 655 ------- 656 str 657 A sql expression representing a "Things date" as integer. 658 659 Example 660 ------- 661 >>> convert_isodate_sql_expression_to_thingsdate("date('now', 'localtime')") 662 "(CASE WHEN date('now', 'localtime') THEN \ 663 ((strftime('%Y', date('now', 'localtime')) << 16) \ 664 | (strftime('%m', date('now', 'localtime')) << 12) \ 665 | (strftime('%d', date('now', 'localtime')) << 7)) \ 666 ELSE date('now', 'localtime') END)" 667 >>> convert_isodate_sql_expression_to_thingsdate("'2023-05-22'") 668 "(CASE WHEN '2023-05-22' THEN \ 669 ((strftime('%Y', '2023-05-22') << 16) \ 670 | (strftime('%m', '2023-05-22') << 12) \ 671 | (strftime('%d', '2023-05-22') << 7)) \ 672 ELSE '2023-05-22' END)" 673 """ 674 isodate = sql_expression 675 676 year = f"strftime('%Y', {isodate}) << 16" 677 month = f"strftime('%m', {isodate}) << 12" 678 day = f"strftime('%d', {isodate}) << 7" 679 680 thingsdate = f"(({year}) | ({month}) | ({day}))" 681 682 if null_possible: 683 # when isodate is NULL, return isodate as-is 684 return f"(CASE WHEN {isodate} THEN {thingsdate} ELSE {isodate} END)" 685 686 return thingsdate
Return a SQL expression of an isodate converted into a "Things date".
A _Things date_ is an integer where the binary digits are YYYYYYYYYYYMMMMDDDDD0000000; Y is year, M is month, and D is day.
For example, the ISO 8601 date '2021-03-28' corresponds to the Things date 132464128 as integer; in binary that is: 111111001010011111000000000 YYYYYYYYYYYMMMMDDDDD0000000 2021 3 28
Parameters
- sql_expression (str): A sql expression evaluating to an ISO 8601 date str.
- null_possible (bool):
Can the input
sql_expressionevaluate to NULL?
Returns
- str: A sql expression representing a "Things date" as integer.
Example
>>> convert_isodate_sql_expression_to_thingsdate("date('now', 'localtime')")
"(CASE WHEN date('now', 'localtime') THEN ((strftime('%Y', date('now', 'localtime')) << 16) | (strftime('%m', date('now', 'localtime')) << 12) | (strftime('%d', date('now', 'localtime')) << 7)) ELSE date('now', 'localtime') END)"
>>> convert_isodate_sql_expression_to_thingsdate("'2023-05-22'")
"(CASE WHEN '2023-05-22' THEN ((strftime('%Y', '2023-05-22') << 16) | (strftime('%m', '2023-05-22') << 12) | (strftime('%d', '2023-05-22') << 7)) ELSE '2023-05-22' END)"
689def convert_thingsdate_sql_expression_to_isodate(sql_expression): 690 """ 691 Return SQL expression as string. 692 693 Parameters 694 ---------- 695 sql_expression : str 696 A sql expression pointing to a "Things date" integer in 697 format YYYYYYYYYYYMMMMDDDDD0000000, in binary. 698 See: `convert_isodate_sql_expression_to_thingsdate` for details. 699 700 Example 701 ------- 702 >>> convert_thingsdate_sql_expression_to_isodate('132464128') 703 "CASE WHEN 132464128 THEN \ 704 printf('%d-%02d-%02d', (132464128 & 134152192) >> 16, \ 705 (132464128 & 61440) >> 12, (132464128 & 3968) >> 7) ELSE 132464128 END" 706 >>> convert_thingsdate_sql_expression_to_isodate('startDate') 707 "CASE WHEN startDate THEN \ 708 printf('%d-%02d-%02d', (startDate & 134152192) >> 16, \ 709 (startDate & 61440) >> 12, (startDate & 3968) >> 7) ELSE startDate END" 710 """ 711 y_mask = 0b111111111110000000000000000 712 m_mask = 0b000000000001111000000000000 713 d_mask = 0b000000000000000111110000000 714 715 thingsdate = sql_expression 716 year = f"({thingsdate} & {y_mask}) >> 16" 717 month = f"({thingsdate} & {m_mask}) >> 12" 718 day = f"({thingsdate} & {d_mask}) >> 7" 719 720 isodate = f"printf('%d-%02d-%02d', {year}, {month}, {day})" 721 # when thingsdate is NULL, return thingsdate as-is 722 return f"CASE WHEN {thingsdate} THEN {isodate} ELSE {thingsdate} END"
Return SQL expression as string.
Parameters
- sql_expression (str):
A sql expression pointing to a "Things date" integer in
format YYYYYYYYYYYMMMMDDDDD0000000, in binary.
See:
convert_isodate_sql_expression_to_thingsdatefor details.
Example
>>> convert_thingsdate_sql_expression_to_isodate('132464128')
"CASE WHEN 132464128 THEN printf('%d-%02d-%02d', (132464128 & 134152192) >> 16, (132464128 & 61440) >> 12, (132464128 & 3968) >> 7) ELSE 132464128 END"
>>> convert_thingsdate_sql_expression_to_isodate('startDate')
"CASE WHEN startDate THEN printf('%d-%02d-%02d', (startDate & 134152192) >> 16, (startDate & 61440) >> 12, (startDate & 3968) >> 7) ELSE startDate END"
725def convert_thingstime_sql_expression_to_isotime(sql_expression: str) -> str: 726 """ 727 Return SQL Expression that decodes a Things time as a string. 728 729 A _Things time_ is an integer where the binary digits are 730 hhhhhmmmmmm00000000000000000000; h is hours, m is minutes. 731 Seconds are not encoded in a Things time. 732 733 For example, the ISO 8601 time '12:34:00' corresponds to the Things 734 time 840957952 as integer; in binary that is: 735 0110010001000000000000000000000 736 hhhhhmmmmmm00000000000000000000 737 12 34 00 738 739 Parameters 740 ---------- 741 sql_expression : str 742 A sql expression pointing to a "Things time" integer 743 in format hhhhhmmmmmm00000000000000000000, in binary. 744 745 Example 746 ------- 747 >>> convert_thingstime_sql_expression_to_isotime('840957952') 748 "CASE WHEN 840957952 THEN \ 749 printf('%02d:%02d', (840957952 & 2080374784) >> 26, \ 750 (840957952 & 66060288) >> 20) ELSE 840957952 END" 751 >>> convert_thingstime_sql_expression_to_isotime('reminderTime') 752 "CASE WHEN reminderTime THEN \ 753 printf('%02d:%02d', (reminderTime & 2080374784) >> 26, \ 754 (reminderTime & 66060288) >> 20) ELSE reminderTime END" 755 """ 756 h_mask = 0b1111100000000000000000000000000 757 m_mask = 0b0000011111100000000000000000000 758 759 thingstime = sql_expression 760 hours = f"({thingstime} & {h_mask}) >> 26" 761 minutes = f"({thingstime} & {m_mask}) >> 20" 762 763 isotime = f"printf('%02d:%02d', {hours}, {minutes})" 764 # when thingstime is NULL, return thingstime as-is 765 return f"CASE WHEN {thingstime} THEN {isotime} ELSE {thingstime} END"
Return SQL Expression that decodes a Things time as a string.
A _Things time_ is an integer where the binary digits are hhhhhmmmmmm00000000000000000000; h is hours, m is minutes. Seconds are not encoded in a Things time.
For example, the ISO 8601 time '12:34:00' corresponds to the Things time 840957952 as integer; in binary that is: 0110010001000000000000000000000 hhhhhmmmmmm00000000000000000000 12 34 00
Parameters
- sql_expression (str): A sql expression pointing to a "Things time" integer in format hhhhhmmmmmm00000000000000000000, in binary.
Example
>>> convert_thingstime_sql_expression_to_isotime('840957952')
"CASE WHEN 840957952 THEN printf('%02d:%02d', (840957952 & 2080374784) >> 26, (840957952 & 66060288) >> 20) ELSE 840957952 END"
>>> convert_thingstime_sql_expression_to_isotime('reminderTime')
"CASE WHEN reminderTime THEN printf('%02d:%02d', (reminderTime & 2080374784) >> 26, (reminderTime & 66060288) >> 20) ELSE reminderTime END"
768def dict_factory(cursor, row): 769 """ 770 Convert SQL result into a dictionary. 771 772 See also: 773 https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory 774 """ 775 result = {} 776 for index, column in enumerate(cursor.description): 777 key, value = column[0], row[index] 778 if value is None and key in COLUMNS_TO_OMIT_IF_NONE: 779 continue 780 if value and key in COLUMNS_TO_TRANSFORM_TO_BOOL: 781 value = bool(value) 782 result[key] = value 783 return result
Convert SQL result into a dictionary.
See also: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory
786def escape_string(string): 787 r""" 788 Escape SQLite string literal. 789 790 Three notes: 791 792 1. A single quote within a SQLite string can be encoded by putting 793 two single quotes in a row. Escapes using the backslash character 794 are not supported in SQLite. 795 796 2. Null characters '\0' within strings can lead to surprising 797 behavior. However, `cursor.execute` will already throw a `ValueError` 798 if it finds a null character in the query, so we let it handle 799 this case for us. 800 801 3. Eventually we might want to make use of parameters instead of 802 manually escaping. Since this will require some refactoring, 803 we are going with the easiest solution for now. 804 805 See: https://www.sqlite.org/lang_expr.html#literal_values_constants_ 806 """ 807 return string.replace("'", "''")
Escape SQLite string literal.
Three notes:
A single quote within a SQLite string can be encoded by putting two single quotes in a row. Escapes using the backslash character are not supported in SQLite.
Null characters '\0' within strings can lead to surprising behavior. However,
cursor.executewill already throw aValueErrorif it finds a null character in the query, so we let it handle this case for us.Eventually we might want to make use of parameters instead of manually escaping. Since this will require some refactoring, we are going with the easiest solution for now.
See: https://www.sqlite.org/lang_expr.html#literal_values_constants_
810def isodate_to_yyyyyyyyyyymmmmddddd(value: str): 811 """ 812 Return integer, in binary YYYYYYYYYYYMMMMDDDDD0000000. 813 814 Y is year, M is month, D is day as binary. 815 See also `convert_isodate_sql_expression_to_thingsdate`. 816 817 Parameters 818 ---------- 819 value : str 820 ISO 8601 date str 821 822 Example 823 ------- 824 >>> isodate_to_yyyyyyyyyyymmmmddddd('2021-03-28') 825 132464128 826 """ 827 year, month, day = map(int, value.split("-")) 828 return year << 16 | month << 12 | day << 7
Return integer, in binary YYYYYYYYYYYMMMMDDDDD0000000.
Y is year, M is month, D is day as binary.
See also convert_isodate_sql_expression_to_thingsdate.
Parameters
- value (str): ISO 8601 date str
Example
>>> isodate_to_yyyyyyyyyyymmmmddddd('2021-03-28')
132464128
831def get_default_database_filepath(raise_on_error=False): 832 """Return Things 3.17.4+ path else the 3.15 path. 833 834 Optionally, raise an error if the Things 3.17.4+ path isn't found 835 and macOS can't list the files in the `Group Containers` folder. 836 """ 837 filepath = next(glob.iglob(DEFAULT_FILEPATH_31704015), None) 838 if filepath: 839 return filepath 840 841 if raise_on_error: 842 # glob hides PermissionError; this raises it 843 os.listdir(THINGS_GROUP_CONTAINER) 844 # fallback to an older Things path to try 845 return DEFAULT_FILEPATH_31516502
Return Things 3.17.4+ path else the 3.15 path.
Optionally, raise an error if the Things 3.17.4+ path isn't found
and macOS can't list the files in the Group Containers folder.
848def list_factory(_cursor, row): 849 """Convert SQL selects of one column into a list.""" 850 return row[0]
Convert SQL selects of one column into a list.
853def make_filter(column, value): 854 """ 855 Return SQL filter 'AND {column} = "{value}"'. 856 857 Special handling if `value` is `bool` or `None`. 858 859 Examples 860 -------- 861 >>> make_filter('title', 'Important') 862 "AND title = 'Important'" 863 864 >>> make_filter('startDate', True) 865 'AND startDate IS NOT NULL' 866 867 >>> make_filter('startDate', False) 868 'AND startDate IS NULL' 869 870 >>> make_filter('title', None) 871 '' 872 """ 873 default = f"AND {column} = '{escape_string(str(value))}'" 874 return { 875 None: "", 876 False: f"AND {column} IS NULL", 877 True: f"AND {column} IS NOT NULL", 878 }.get(value, default)
Return SQL filter 'AND {column} = "{value}"'.
Special handling if value is bool or None.
Examples
>>> make_filter('title', 'Important')
"AND title = 'Important'"
>>> make_filter('startDate', True)
'AND startDate IS NOT NULL'
>>> make_filter('startDate', False)
'AND startDate IS NULL'
>>> make_filter('title', None)
''
881def make_or_filter(*filters): 882 """Join filters with OR.""" 883 filters = filter(None, filters) # type: ignore 884 filters = [remove_prefix(filter, "AND ") for filter in filters] # type: ignore 885 filters = " OR ".join(filters) # type: ignore 886 return f"AND ({filters})" if filters else ""
Join filters with OR.
889def make_search_filter(query: str | None) -> str: 890 """ 891 Return a SQL filter to search tasks by a string query. 892 893 Example: 894 -------- 895 >>> make_search_filter('dinner') 896 "AND (TASK.title LIKE '%dinner%' OR TASK.notes LIKE '%dinner%' OR \ 897 AREA.title LIKE '%dinner%')" 898 """ 899 if not query: 900 return "" 901 902 query = escape_string(query) 903 904 # TODO: 'TMChecklistItem.title' # pylint: disable=fixme 905 columns = ["TASK.title", "TASK.notes", "AREA.title"] 906 907 sub_searches = (f"{column} LIKE '%{query}%'" for column in columns) 908 909 return f"AND ({' OR '.join(sub_searches)})"
Return a SQL filter to search tasks by a string query.
Example:
>>> make_search_filter('dinner')
"AND (TASK.title LIKE '%dinner%' OR TASK.notes LIKE '%dinner%' OR AREA.title LIKE '%dinner%')"
912def make_thingsdate_filter(date_column: str, value) -> str: 913 """ 914 Return a SQL filter for "Things date" columns. 915 916 Parameters 917 ---------- 918 date_column : str 919 Name of the column that has date information on a task 920 stored as an INTEGER in "Things date" format. 921 See `convert_isodate_sql_expression_to_thingsdate` for details 922 on Things dates. 923 924 value : bool, 'future', 'past', ISO 8601 date str, or None 925 `True` or `False` indicates whether a date is set or not. 926 `'future'` or `'past'` indicates a date in the future or past. 927 ISO 8601 date str is in the format "YYYY-MM-DD", possibly 928 prefixed with an operator such as ">YYYY-MM-DD", 929 "=YYYY-MM-DD", "<=YYYY-MM-DD", etc. 930 `None` indicates any value. 931 932 Returns 933 ------- 934 str 935 A date filter for the SQL query. If `value == None`, then 936 return the empty string. 937 938 Examples 939 -------- 940 >>> make_thingsdate_filter('startDate', True) 941 'AND startDate IS NOT NULL' 942 943 >>> make_thingsdate_filter('startDate', False) 944 'AND startDate IS NULL' 945 946 >>> make_thingsdate_filter('startDate', 'future') 947 "AND startDate > ((strftime('%Y', date('now', 'localtime')) << 16) \ 948 | (strftime('%m', date('now', 'localtime')) << 12) \ 949 | (strftime('%d', date('now', 'localtime')) << 7))" 950 951 >>> make_thingsdate_filter('deadline', '2021-03-28') 952 'AND deadline == 132464128' 953 954 >>> make_thingsdate_filter('deadline', '=2021-03-28') 955 'AND deadline = 132464128' 956 957 >>> make_thingsdate_filter('deadline', '<=2021-03-28') 958 'AND deadline <= 132464128' 959 960 >>> make_thingsdate_filter('deadline', None) 961 '' 962 963 """ 964 if value is None: 965 return "" 966 967 if isinstance(value, bool): 968 return make_filter(date_column, value) 969 970 # Check for ISO 8601 date str + optional operator 971 match = match_date(value) 972 if match: 973 comparator, isodate = match.groups() 974 if not comparator: 975 comparator = "==" 976 thingsdate = isodate_to_yyyyyyyyyyymmmmddddd(isodate) 977 threshold = str(thingsdate) 978 else: 979 # "future" or "past" 980 validate("value", value, ["future", "past"]) 981 threshold = convert_isodate_sql_expression_to_thingsdate( 982 "date('now', 'localtime')", null_possible=False 983 ) 984 comparator = ">" if value == "future" else "<=" 985 986 return f"AND {date_column} {comparator} {threshold}"
Return a SQL filter for "Things date" columns.
Parameters
- date_column (str):
Name of the column that has date information on a task
stored as an INTEGER in "Things date" format.
See
convert_isodate_sql_expression_to_thingsdatefor details on Things dates. - value (bool, 'future', 'past', ISO 8601 date str, or None):
TrueorFalseindicates whether a date is set or not.'future'or'past'indicates a date in the future or past. ISO 8601 date str is in the format "YYYY-MM-DD", possibly prefixed with an operator such as ">YYYY-MM-DD", "=YYYY-MM-DD", "<=YYYY-MM-DD", etc.Noneindicates any value.
Returns
- str: A date filter for the SQL query. If
value == None, then return the empty string.
Examples
>>> make_thingsdate_filter('startDate', True)
'AND startDate IS NOT NULL'
>>> make_thingsdate_filter('startDate', False)
'AND startDate IS NULL'
>>> make_thingsdate_filter('startDate', 'future')
"AND startDate > ((strftime('%Y', date('now', 'localtime')) << 16) | (strftime('%m', date('now', 'localtime')) << 12) | (strftime('%d', date('now', 'localtime')) << 7))"
>>> make_thingsdate_filter('deadline', '2021-03-28')
'AND deadline == 132464128'
>>> make_thingsdate_filter('deadline', '=2021-03-28')
'AND deadline = 132464128'
>>> make_thingsdate_filter('deadline', '<=2021-03-28')
'AND deadline <= 132464128'
>>> make_thingsdate_filter('deadline', None)
''
989def make_truthy_filter(column: str, value) -> str: 990 """ 991 Return a SQL filter that matches if a column is truthy or falsy. 992 993 Truthy means TRUE. Falsy means FALSE or NULL. This is akin 994 to how Python defines it natively. 995 996 Passing in `value == None` returns the empty string. 997 998 Examples 999 -------- 1000 >>> make_truthy_filter('PROJECT.trashed', True) 1001 'AND PROJECT.trashed' 1002 1003 >>> make_truthy_filter('PROJECT.trashed', False) 1004 'AND NOT IFNULL(PROJECT.trashed, 0)' 1005 1006 >>> make_truthy_filter('PROJECT.trashed', None) 1007 '' 1008 """ 1009 if value is None: 1010 return "" 1011 1012 if value: 1013 return f"AND {column}" 1014 1015 return f"AND NOT IFNULL({column}, 0)"
Return a SQL filter that matches if a column is truthy or falsy.
Truthy means TRUE. Falsy means FALSE or NULL. This is akin to how Python defines it natively.
Passing in value == None returns the empty string.
Examples
>>> make_truthy_filter('PROJECT.trashed', True)
'AND PROJECT.trashed'
>>> make_truthy_filter('PROJECT.trashed', False)
'AND NOT IFNULL(PROJECT.trashed, 0)'
>>> make_truthy_filter('PROJECT.trashed', None)
''
1018def make_unixtime_filter(date_column: str, value) -> str: 1019 """ 1020 Return a SQL filter for UNIX time columns. 1021 1022 Parameters 1023 ---------- 1024 date_column : str 1025 Name of the column that has datetime information on a task 1026 stored in UNIX time, that is, number of seconds since 1027 1970-01-01 00:00 UTC. 1028 1029 value : bool, 'future', 'past', ISO 8601 date str, or None 1030 `True` or `False` indicates whether a date is set or not. 1031 `'future'` or `'past'` indicates a date in the future or past. 1032 ISO 8601 date str is in the format "YYYY-MM-DD", possibly 1033 prefixed with an operator such as ">YYYY-MM-DD", 1034 "=YYYY-MM-DD", "<=YYYY-MM-DD", etc. 1035 `None` indicates any value. 1036 1037 Returns 1038 ------- 1039 str 1040 A date filter for the SQL query. If `value == None`, then 1041 return the empty string. 1042 1043 Examples 1044 -------- 1045 >>> make_unixtime_filter('stopDate', True) 1046 'AND stopDate IS NOT NULL' 1047 1048 >>> make_unixtime_filter('stopDate', False) 1049 'AND stopDate IS NULL' 1050 1051 >>> make_unixtime_filter('stopDate', 'future') 1052 "AND date(stopDate, 'unixepoch', 'localtime') > date('now', 'localtime')" 1053 1054 >>> make_unixtime_filter('creationDate', '2021-03-28') 1055 "AND date(creationDate, 'unixepoch', 'localtime') == date('2021-03-28')" 1056 1057 >>> make_unixtime_filter('creationDate', '=2021-03-28') 1058 "AND date(creationDate, 'unixepoch', 'localtime') = date('2021-03-28')" 1059 1060 >>> make_unixtime_filter('creationDate', '<=2021-03-28') 1061 "AND date(creationDate, 'unixepoch', 'localtime') <= date('2021-03-28')" 1062 1063 >>> make_unixtime_filter('creationDate', None) 1064 '' 1065 1066 """ 1067 if value is None: 1068 return "" 1069 1070 if isinstance(value, bool): 1071 return make_filter(date_column, value) 1072 1073 # Check for ISO 8601 date str + optional operator 1074 match = match_date(value) 1075 if match: 1076 comparator, isodate = match.groups() 1077 if not comparator: 1078 comparator = "==" 1079 threshold = f"date('{isodate}')" 1080 else: 1081 # "future" or "past" 1082 validate("value", value, ["future", "past"]) 1083 threshold = "date('now', 'localtime')" 1084 comparator = ">" if value == "future" else "<=" 1085 1086 date = f"date({date_column}, 'unixepoch', 'localtime')" 1087 1088 return f"AND {date} {comparator} {threshold}"
Return a SQL filter for UNIX time columns.
Parameters
- date_column (str): Name of the column that has datetime information on a task stored in UNIX time, that is, number of seconds since 1970-01-01 00:00 UTC.
- value (bool, 'future', 'past', ISO 8601 date str, or None):
TrueorFalseindicates whether a date is set or not.'future'or'past'indicates a date in the future or past. ISO 8601 date str is in the format "YYYY-MM-DD", possibly prefixed with an operator such as ">YYYY-MM-DD", "=YYYY-MM-DD", "<=YYYY-MM-DD", etc.Noneindicates any value.
Returns
- str: A date filter for the SQL query. If
value == None, then return the empty string.
Examples
>>> make_unixtime_filter('stopDate', True)
'AND stopDate IS NOT NULL'
>>> make_unixtime_filter('stopDate', False)
'AND stopDate IS NULL'
>>> make_unixtime_filter('stopDate', 'future')
"AND date(stopDate, 'unixepoch', 'localtime') > date('now', 'localtime')"
>>> make_unixtime_filter('creationDate', '2021-03-28')
"AND date(creationDate, 'unixepoch', 'localtime') == date('2021-03-28')"
>>> make_unixtime_filter('creationDate', '=2021-03-28')
"AND date(creationDate, 'unixepoch', 'localtime') = date('2021-03-28')"
>>> make_unixtime_filter('creationDate', '<=2021-03-28')
"AND date(creationDate, 'unixepoch', 'localtime') <= date('2021-03-28')"
>>> make_unixtime_filter('creationDate', None)
''
1091def make_unixtime_range_filter(date_column: str, offset) -> str: 1092 """ 1093 Return a SQL filter to limit a Unix time to last X days, weeks, or years. 1094 1095 Parameters 1096 ---------- 1097 date_column : str 1098 Name of the column that has datetime information on a task 1099 stored in UNIX time, that is, number of seconds since 1100 1970-01-01 00:00 UTC. 1101 1102 offset : str or None 1103 A string comprised of an integer and a single character that can 1104 be 'd', 'w', or 'y' that determines whether to return all tasks 1105 for the past X days, weeks, or years. 1106 1107 Returns 1108 ------- 1109 str 1110 A date filter for the SQL query. If `offset == None`, then 1111 return the empty string. 1112 1113 Examples 1114 -------- 1115 >>> make_unixtime_range_filter('creationDate', '3d') 1116 "AND datetime(creationDate, 'unixepoch', 'localtime') > datetime('now', '-3 days')" 1117 1118 >>> make_unixtime_range_filter('creationDate', None) 1119 '' 1120 """ 1121 if offset is None: 1122 return "" 1123 1124 validate_offset("offset", offset) 1125 number, suffix = int(offset[:-1]), offset[-1] 1126 1127 if suffix == "d": 1128 modifier = f"-{number} days" 1129 elif suffix == "w": 1130 modifier = f"-{number * 7} days" 1131 elif suffix == "y": 1132 modifier = f"-{number} years" 1133 else: 1134 # Use `typing.assert_never(suffix)` from Python 3.11 onwards. 1135 raise AssertionError("line should be unreachable.") # for static code analyzers 1136 1137 column_datetime = f"datetime({date_column}, 'unixepoch', 'localtime')" 1138 offset_datetime = f"datetime('now', '{modifier}')" 1139 1140 return f"AND {column_datetime} > {offset_datetime}"
Return a SQL filter to limit a Unix time to last X days, weeks, or years.
Parameters
- date_column (str): Name of the column that has datetime information on a task stored in UNIX time, that is, number of seconds since 1970-01-01 00:00 UTC.
- offset (str or None): A string comprised of an integer and a single character that can be 'd', 'w', or 'y' that determines whether to return all tasks for the past X days, weeks, or years.
Returns
- str: A date filter for the SQL query. If
offset == None, then return the empty string.
Examples
>>> make_unixtime_range_filter('creationDate', '3d')
"AND datetime(creationDate, 'unixepoch', 'localtime') > datetime('now', '-3 days')"
>>> make_unixtime_range_filter('creationDate', None)
''
1143def match_date(value): 1144 """Return a match object if value is an ISO 8601 date str.""" 1145 return re.fullmatch(r"(=|==|<|<=|>|>=)?(\d{4}-\d{2}-\d{2})", value)
Return a match object if value is an ISO 8601 date str.
1148def null_if_unset_thingsdate(sql_expression: str) -> str: 1149 """ 1150 Return SQL expression as string. 1151 1152 It is NULL when the "Things date" is unset, else the Things date itself. 1153 Things stores 0001-01-01 instead of NULL in some date columns, such as 1154 `rt1_nextInstanceStartDate` when a repeating to-do has no next instance. 1155 Sometimes bits below the day field are set as well (69769 rather than 1156 69760), so any date in year 1 counts as unset. 1157 1158 Parameters 1159 ---------- 1160 sql_expression : str 1161 A sql expression pointing to a "Things date" integer in 1162 format YYYYYYYYYYYMMMMDDDDD0000000, in binary. 1163 See: `convert_isodate_sql_expression_to_thingsdate` for details. 1164 1165 Example 1166 ------- 1167 >>> null_if_unset_thingsdate('rt1_nextInstanceStartDate') 1168 'CASE WHEN rt1_nextInstanceStartDate >= 135296 \ 1169 THEN rt1_nextInstanceStartDate END' 1170 """ 1171 first_real_thingsdate = isodate_to_yyyyyyyyyyymmmmddddd("0002-01-01") 1172 return ( 1173 f"CASE WHEN {sql_expression} >= {first_real_thingsdate}" 1174 f" THEN {sql_expression} END" 1175 )
Return SQL expression as string.
It is NULL when the "Things date" is unset, else the Things date itself.
Things stores 0001-01-01 instead of NULL in some date columns, such as
rt1_nextInstanceStartDate when a repeating to-do has no next instance.
Sometimes bits below the day field are set as well (69769 rather than
69760), so any date in year 1 counts as unset.
Parameters
- sql_expression (str):
A sql expression pointing to a "Things date" integer in
format YYYYYYYYYYYMMMMDDDDD0000000, in binary.
See:
convert_isodate_sql_expression_to_thingsdatefor details.
Example
>>> null_if_unset_thingsdate('rt1_nextInstanceStartDate')
'CASE WHEN rt1_nextInstanceStartDate >= 135296 THEN rt1_nextInstanceStartDate END'
1178def prettify_sql(sql_query): 1179 """Make a SQL query easier to read for humans.""" 1180 # remove indentation and leading and trailing whitespace 1181 result = dedent(sql_query).strip() 1182 # remove empty lines 1183 return re.sub(r"^$\n", "", result, flags=re.MULTILINE)
Make a SQL query easier to read for humans.
1186def remove_prefix(text, prefix): 1187 """Remove prefix from text (as removeprefix() is 3.9+ only).""" 1188 return text[text.startswith(prefix) and len(prefix) :]
Remove prefix from text (as removeprefix() is 3.9+ only).
1191def validate(parameter, argument, valid_arguments): 1192 """ 1193 For a given parameter, check if its argument type is valid. 1194 1195 If not, then raise `ValueError`. 1196 1197 Examples 1198 -------- 1199 >>> validate( 1200 ... parameter='status', 1201 ... argument='completed', 1202 ... valid_arguments=['incomplete', 'completed'] 1203 ... ) 1204 ... 1205 1206 >>> validate( 1207 ... parameter='status', 1208 ... argument='XYZXZY', 1209 ... valid_arguments=['incomplete', 'completed'] 1210 ... ) 1211 Traceback (most recent call last): 1212 ... 1213 ValueError: Unrecognized status type: 'XYZXZY' 1214 Valid status types are ['incomplete', 'completed'] 1215 """ 1216 if argument in valid_arguments: 1217 return 1218 message = f"Unrecognized {parameter} type: {argument!r}" 1219 message += f"\nValid {parameter} types are {valid_arguments}" 1220 raise ValueError(message)
For a given parameter, check if its argument type is valid.
If not, then raise ValueError.
Examples
>>> validate(
... parameter='status',
... argument='completed',
... valid_arguments=['incomplete', 'completed']
... )
...
>>> validate(
... parameter='status',
... argument='XYZXZY',
... valid_arguments=['incomplete', 'completed']
... )
Traceback (most recent call last):
...
ValueError: Unrecognized status type: 'XYZXZY'
Valid status types are ['incomplete', 'completed']
1223def validate_date(parameter, argument): 1224 """ 1225 For a given date parameter, check if its argument is valid. 1226 1227 If not, then raise `ValueError`. 1228 1229 Examples 1230 -------- 1231 >>> validate_date(parameter='startDate', argument=None) 1232 >>> validate_date(parameter='startDate', argument='future') 1233 >>> validate_date(parameter='startDate', argument='2020-01-01') 1234 >>> validate_date(parameter='startDate', argument='<=2020-01-01') 1235 1236 >>> validate_date(parameter='stopDate', argument='=2020-01-01') 1237 1238 >>> validate_date(parameter='deadline', argument='XYZ') 1239 Traceback (most recent call last): 1240 ... 1241 ValueError: Invalid deadline argument: 'XYZ' 1242 Please see the documentation for `deadline` in `things.tasks`. 1243 """ 1244 if argument is None: 1245 return 1246 1247 if argument in list(DATES): 1248 return 1249 1250 if not isinstance(argument, str): 1251 raise ValueError( 1252 f"Invalid {parameter} argument: {argument!r}\n" 1253 f"Please specify a string or None." 1254 ) 1255 1256 match = match_date(argument) 1257 if not match: 1258 raise ValueError( 1259 f"Invalid {parameter} argument: {argument!r}\n" 1260 f"Please see the documentation for `{parameter}` in `things.tasks`." 1261 ) 1262 1263 _, isodate = match.groups() 1264 try: 1265 datetime.date.fromisoformat(isodate) 1266 except ValueError as error: 1267 raise ValueError( 1268 f"Invalid {parameter} argument: {argument!r}\n{error}" 1269 ) from error
For a given date parameter, check if its argument is valid.
If not, then raise ValueError.
Examples
>>> validate_date(parameter='startDate', argument=None)
>>> validate_date(parameter='startDate', argument='future')
>>> validate_date(parameter='startDate', argument='2020-01-01')
>>> validate_date(parameter='startDate', argument='<=2020-01-01')
>>> validate_date(parameter='stopDate', argument='=2020-01-01')
>>> validate_date(parameter='deadline', argument='XYZ')
Traceback (most recent call last):
...
ValueError: Invalid deadline argument: 'XYZ'
Please see the documentation for `deadline` in `things.tasks`.
1272def validate_offset(parameter, argument): 1273 """ 1274 For a given offset parameter, check if its argument is valid. 1275 1276 If not, then raise `ValueError`. 1277 1278 Examples 1279 -------- 1280 >>> validate_offset(parameter='last', argument='3d') 1281 1282 >>> validate_offset(parameter='last', argument='XYZ') 1283 Traceback (most recent call last): 1284 ... 1285 ValueError: Invalid last argument: 'XYZ' 1286 Please specify a string of the format 'X[d/w/y]' where X is ... 1287 """ 1288 if argument is None: 1289 return 1290 1291 if not isinstance(argument, str): 1292 raise ValueError( 1293 f"Invalid {parameter} argument: {argument!r}\n" 1294 f"Please specify a string or None." 1295 ) 1296 1297 suffix = argument[-1:] # slicing here to handle empty strings 1298 if suffix not in ("d", "w", "y"): 1299 raise ValueError( 1300 f"Invalid {parameter} argument: {argument!r}\n" 1301 f"Please specify a string of the format 'X[d/w/y]' " 1302 "where X is a non-negative integer followed by 'd', 'w', or 'y' " 1303 "that indicates days, weeks, or years." 1304 )
For a given offset parameter, check if its argument is valid.
If not, then raise ValueError.
Examples
>>> validate_offset(parameter='last', argument='3d')
>>> validate_offset(parameter='last', argument='XYZ')
Traceback (most recent call last):
...
ValueError: Invalid last argument: 'XYZ'
Please specify a string of the format 'X[d/w/y]' where X is ...