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        )
THINGS_GROUP_CONTAINER = '/home/runner/Library/Group Containers/JLMPQHK86H.com.culturedcode.ThingsMac'
DEFAULT_FILEPATH_31704015 = '/home/runner/Library/Group Containers/JLMPQHK86H.com.culturedcode.ThingsMac/ThingsData-*/Things Database.thingsdatabase/main.sqlite'
DEFAULT_FILEPATH_31516502 = '/home/runner/Library/Group Containers/JLMPQHK86H.com.culturedcode.ThingsMac/Things Database.thingsdatabase/main.sqlite'
ENVIRONMENT_VARIABLE_WITH_FILEPATH = 'THINGSDB'
START_TO_FILTER = {'Inbox': 'start = 0', 'Anytime': 'start = 1', 'Someday': 'start = 2'}
STATUS_TO_FILTER = {'incomplete': 'status = 0', 'canceled': 'status = 2', 'completed': 'status = 3'}
TRASHED_TO_FILTER = {True: 'trashed = 1', False: 'trashed = 0'}
REPEATING_TEMPLATE_TO_FILTER = {True: 'rt1_recurrenceRule IS NOT NULL', False: 'rt1_recurrenceRule IS NULL'}
TYPE_TO_FILTER = {'to-do': 'type = 0', 'project': 'type = 1', 'heading': 'type = 2'}
DATES = ('future', 'past', True, False)
INDICES = ('index', 'todayIndex')
COLUMNS_TO_OMIT_IF_NONE = ('area', 'area_title', 'checklist', 'heading', 'heading_title', 'next_instance_start_date', 'project', 'project_title', 'reminder_time', 'trashed', 'tags')
COLUMNS_TO_TRANSFORM_TO_BOOL = ('checklist', 'tags', 'trashed')
TABLE_AREA = 'TMArea'
TABLE_AREATAG = 'TMAreaTag'
TABLE_CHECKLIST_ITEM = 'TMChecklistItem'
TABLE_META = 'Meta'
TABLE_TAG = 'TMTag'
TABLE_TASK = 'TMTask'
TABLE_TASKTAG = 'TMTaskTag'
TABLE_SETTINGS = 'TMSettings'
DATE_CREATED = 'creationDate'
DATE_MODIFIED = 'userModificationDate'
DATE_STOP = 'stopDate'
DATE_DEADLINE = 'deadline'
DATE_START = 'startDate'
DATE_NEXT_INSTANCE_START = 'rt1_nextInstanceStartDate'
REMINDER_TIME = 'reminderTime'
IS_TODO = 'type = 0'
IS_PROJECT = 'type = 1'
IS_HEADING = 'type = 2'
IS_INCOMPLETE = 'status = 0'
IS_CANCELED = 'status = 2'
IS_COMPLETED = 'status = 3'
IS_INBOX = 'start = 0'
IS_ANYTIME = 'start = 1'
IS_SOMEDAY = 'start = 2'
IS_TRASHED = 'trashed = 1'
class Database:
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 THINGSDB is 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.
Database(filepath=None, print_sql=False)
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.

debug = False
connection: sqlite3.Connection
filepath
print_sql
def get_tasks( self, uuid: str | None = None, type: str | None = None, status: str | None = None, start: str | None = None, area: str | bool | None = None, project: str | bool | None = None, heading: str | None = None, tag: str | bool | None = None, start_date: str | bool | None = None, stop_date: str | bool | None = None, deadline: str | bool | None = None, deadline_suppressed: bool | None = None, trashed: bool | None = False, context_trashed: bool | None = False, is_repeating_template: bool | None = False, next_instance_start_date: str | bool | None = None, last: str | None = None, search_query: str | None = None, index: str = 'index', count_only: bool = False):
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.

def get_task_by_uuid(self, uuid, count_only=False):
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.

def get_areas(self, uuid=None, tag=None, count_only=False):
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.

def get_checklist_items(self, todo_uuid=None):
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.

def get_tags(self, title=None, area=None, task=None, titles_only=False):
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)

Get tags. See api.tags for details on parameters.

def get_tags_of_task(self, task_uuid):
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        )

Get tag titles of task.

def get_tags_of_area(self, area_uuid):
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        )

Get tag titles for area.

def get_version(self):
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.

def get_url_scheme_auth_token(self):
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.

def get_count(self, sql_query, parameters=()):
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.

def execute_query(self, sql_query, parameters=(), row_factory=None):
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.

def make_tasks_sql_query(where_predicate=None, order_predicate=None):
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.

def convert_isodate_sql_expression_to_thingsdate(sql_expression, null_possible=True):
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_expression evaluate 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)"
def convert_thingsdate_sql_expression_to_isodate(sql_expression):
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
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"
def convert_thingstime_sql_expression_to_isotime(sql_expression: str) -> str:
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"
def dict_factory(cursor, row):
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
def escape_string(string):
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:

  1. 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.

  2. Null characters '\0' within strings can lead to surprising behavior. However, cursor.execute will already throw a ValueError if it finds a null character in the query, so we let it handle this case for us.

  3. 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_

def isodate_to_yyyyyyyyyyymmmmddddd(value: str):
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
def get_default_database_filepath(raise_on_error=False):
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.

def list_factory(_cursor, row):
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.

def make_filter(column, value):
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)
''
def make_or_filter(*filters):
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.

def make_search_filter(query: str | None) -> str:
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%')"
def make_thingsdate_filter(date_column: str, value) -> str:
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_thingsdate for details on Things dates.
  • value (bool, 'future', 'past', ISO 8601 date str, or None): True or False indicates 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. None indicates 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)
''
def make_truthy_filter(column: str, value) -> str:
 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)
''
def make_unixtime_filter(date_column: str, value) -> str:
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): True or False indicates 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. None indicates 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)
''
def make_unixtime_range_filter(date_column: str, offset) -> str:
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)
''
def match_date(value):
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.

def null_if_unset_thingsdate(sql_expression: str) -> 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
Example
>>> null_if_unset_thingsdate('rt1_nextInstanceStartDate')
'CASE WHEN rt1_nextInstanceStartDate >= 135296     THEN rt1_nextInstanceStartDate END'
def prettify_sql(sql_query):
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.

def remove_prefix(text, prefix):
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).

def validate(parameter, argument, valid_arguments):
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']
def validate_date(parameter, argument):
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`.
def validate_offset(parameter, argument):
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 ...