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