things.database

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