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