things.database

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