Skip to content

Instantly share code, notes, and snippets.

@AlexanderWillner

AlexanderWillner/things.sh

Forked from avdgaag/things.sh
Last active September 15, 2023 07:29
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save AlexanderWillner/dad8bb7cead74eb7679b553e8c37f477 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save AlexanderWillner/dad8bb7cead74eb7679b553e8c37f477 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. AlexanderWillner revised this gist Jun 14, 2018. 1 changed file with 1 addition and 467 deletions.
    468 changes: 1 addition & 467 deletions things.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,467 +1 @@
    #!/bin/bash
    # IMPORTANT: the latest version of this script can be found at https://github.com/AlexanderWillner/things.sh - please star, fork and contribute
    #
    # DESCRIPTION
    #
    # Simple read-only comand-line interface to your Things 3 database. Since
    # Things uses a SQLite database (which should come pre-installed on your Mac)
    # we can simply query it straight from the command line.
    #
    # We only do read operations since we don't want to mess up your data.
    #
    # INSTALLATION
    #
    # Put this file somewhere in your $PATH and make it executable.
    #
    # INSTRUCTIONS
    #
    # Note that you could override the location of the database used by setting the
    # THINGSDB environment variable.
    #
    # For usage information, run the script with no arguments or with "help".
    #
    # CREDITS
    #
    # Author : Arjan van der Gaag (script for Things 2)
    # Author : Alexander Willner (updates for Things 3, added many more commands, almost complete rewrite)
    # Date : 2017-12-24
    # License : Whatever. Use at your own risk.
    # Source : https://github.com/AlexanderWillner/things.sh
    #

    set -o errexit
    set -o nounset

    limitBy="20"
    waitingTag="Waiting for"
    orderBy="creationDate"

    readonly PROGNAME=$(basename $0)
    readonly DEFAULT_DB=~/Library/Containers/com.culturedcode.ThingsMac/Data/Library/Application\ Support/Cultured\ Code/Things/Things.sqlite3
    readonly THINGSDB=${DB:-$DEFAULT_DB}

    readonly TASKTABLE="TMTask"
    readonly AREATABLE="TMArea"
    readonly TAGTABLE="TMTag"
    readonly ISNOTTRASHED="trashed = 0"
    readonly ISTRASHED="trashed = 1"
    readonly ISOPEN="status = 0"
    readonly ISNOTSTARTED="start = 0"
    readonly ISCANCELLED="status = 2"
    readonly ISCOMPLETED="status = 3"
    readonly ISSTARTED="start = 1"
    readonly ISPOSTPONED="start = 2"
    readonly ISTASK="type = 0"
    readonly ISPROJECT="type = 1"
    readonly ISHEADING="type = 2"

    usage() {
    cat <<-EOF
    usage: $PROGNAME <OPTIONS> [COMMAND]
    List to do items from your Things database given a focus area.
    COMMAND:
    inbox
    today
    upcoming
    next / anytime
    someday
    completed
    cancelled
    trashed
    all (show all tasks)
    nextish (show $limitBy next tasks that are also in someday projects)
    old (show $limitBy tasks ordered by '$orderBy')
    due (show $limitBy tasks ordered by due date)
    waiting (show $limitBy tasks with the tag '$waitingTag' ordered by '$orderBy')
    repeating (show $limitBy repeating tasks orderd by '$orderBy')
    subtasks (show $limitBy subtasks)
    projects (show $limitBy projects ordered by creation date)
    headings (show $limitBy headings ordered by creation date)
    notes (show $limitBy notes as <headings>: <notes> ordered by creation date)
    csv (export all tasks as semicolon seperated values incl. notes and Excel friendly)
    stat (provide an overview of the numbers of tasks)
    search (provide details about specific tasks)
    feedback (give feedback, request and propose changes)
    OPTIONS:
    -l|--limitBy <number> Limit output by <number> of results
    -w|--waitingTag <tag> Set waiting tag to <tag>
    -o|--orderBy <column> Sort output by <column> (e.g. 'userModificationDate' or 'creationDate')
    -s|--string <string> String <string> to search for
    EOF
    }

    inbox() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISNOTSTARTED AND $ISOPEN;
    SQL
    }

    today() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK
    AND $ISSTARTED
    AND startdate is NOT NULL
    ORDER BY startdate, todayIndex;
    SQL
    }

    upcoming() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT date(startDate,'unixepoch'), title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK
    AND $ISPOSTPONED AND (startDate NOT NULL OR recurrenceRule NOT NULL)
    ORDER BY startdate, todayIndex;
    SQL
    }

    anytime() {
    next
    }

    next() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE t
    WHERE $ISNOTTRASHED AND $ISTASK AND $ISOPEN
    AND $ISSTARTED
    AND (
    t.area NOT NULL
    OR
    t.project in (SELECT uuid FROM $TASKTABLE WHERE uuid=t.project AND $ISSTARTED AND $ISNOTTRASHED)
    OR
    t.actionGroup in
    (SELECT uuid FROM TMTask heading WHERE uuid=t.actionGroup
    AND $ISSTARTED
    AND $ISNOTTRASHED
    AND heading.project in (SELECT uuid FROM TMTask WHERE uuid=heading.project AND $ISSTARTED AND $ISNOTTRASHED)
    )
    )
    ORDER BY todayIndex;
    SQL
    }

    someday() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE t
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISPOSTPONED AND $ISOPEN;
    SQL
    }

    completed() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISCOMPLETED;
    SQL
    }

    nextish() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISSTARTED AND $ISOPEN AND $ISTASK
    LIMIT $limitBy;
    SQL
    }

    all() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK;
    SQL
    }

    subtasks() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT T1.title
    FROM TMChecklistItem T1
    LEFT OUTER JOIN $TASKTABLE T2 ON T1.task = T2.uuid
    WHERE T1.status=0 AND T2.status=0 AND T2.trashed=0
    LIMIT $limitBy;
    SQL
    }

    waiting() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT T2.title
    FROM TMTaskTag T1
    LEFT JOIN $TASKTABLE T2 ON T1.tasks = T2.uuid
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND T1.tags=(SELECT uuid FROM $TAGTABLE WHERE title='$waitingTag')
    ORDER BY $orderBy
    LIMIT $limitBy;
    SQL
    }


    old() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT date(creationDate,'unixepoch'), title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISSTARTED AND $ISTASK
    ORDER BY $orderBy
    LIMIT $limitBy;
    SQL
    }


    due() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT date(dueDate,'unixepoch'), title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND dueDate NOT NULL
    ORDER BY dueDate
    LIMIT $limitBy;
    SQL
    }

    repeating() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISPOSTPONED
    AND recurrenceRule NOT NULL
    ORDER BY $orderBy
    LIMIT $limitBy;
    SQL
    }

    projects() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND $ISPROJECT
    ORDER BY $orderBy
    LIMIT $limitBy;
    SQL
    }

    headings() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND $ISHEADING
    ORDER BY $orderBy
    LIMIT $limitBy;
    SQL
    }

    cancelled() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISCANCELLED AND $ISTASK
    ORDER BY $orderBy;
    SQL
    }

    trashed() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISTRASHED AND $ISTASK
    ORDER BY $orderBy;
    SQL
    }

    averageCompleteTime() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT ROUND(AVG(JULIANDAY(stopDate,'unixepoch')-JULIANDAY(creationDate,'unixepoch')))
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISCOMPLETED;
    SQL
    }

    notes() {
    sqlite3 "$THINGSDB" <<-SQL
    .mode list
    .separator ": "
    SELECT
    title,
    notes
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    ORDER BY $orderBy;
    SQL
    }

    csv() {
    echo 'Title;"Creation Date";"Modification Date";"Due Date";"Start Date";Project;Area;Subtask;Notes'

    sqlite3 "$THINGSDB" <<-SQL
    .mode csv
    .separator ";"
    SELECT
    T1.title,
    date(T1.creationDate,'unixepoch'),
    date(T1.userModificationDate,'unixepoch'),
    date(T1.dueDate,'unixepoch'),
    date(T1.startDate,'unixepoch'),
    T2.title,
    T3.title,
    "",
    REPLACE(T1.notes, CHAR(10), ', ')
    FROM $TASKTABLE T1
    LEFT OUTER JOIN $TASKTABLE T2 ON T1.project = T2.uuid
    LEFT OUTER JOIN $AREATABLE T3 ON T1.area = T3.uuid
    WHERE T1.trashed = 0 AND T1.status = 0 AND T1.type = 0;
    SQL

    sqlite3 "$THINGSDB" <<-SQL
    .mode csv
    .separator ";"
    SELECT
    T2.title,
    date(T1.creationDate,'unixepoch'),
    date(T1.userModificationDate,'unixepoch'),
    ""
    "",
    "",
    "",
    "",
    T1.title
    FROM TMChecklistItem T1
    LEFT OUTER JOIN $TASKTABLE T2 ON T1.task = T2.uuid
    WHERE T1.status=0 AND T2.status=0 AND T2.trashed=0;
    SQL
    }


    stat() {
    echo -n "Inbox :"; inbox|wc -l
    echo ""
    echo -n "Today :"; today|wc -l
    echo -n "Upcoming :"; upcoming|wc -l
    echo -n "Next :"; next|wc -l
    echo -n "Someday :"; someday|wc -l
    echo ""
    echo -n "Completed :"; completed|wc -l
    echo -n "Cancelled :"; cancelled|wc -l
    echo -n "Trashed :"; trashed|wc -l
    echo ""
    echo -n "Tasks :"; all|wc -l
    echo -n "Subtasks :"; subtasks|wc -l
    echo -n "Waiting :"; waiting|wc -l
    echo -n "Projects :"; projects|wc -l
    echo -n "Repeating :"; repeating|wc -l
    echo -n "Nextish :"; nextish|wc -l
    echo -n "Headings :"; headings|wc -l
    echo ""
    echo -n "Oldest : "; limitBy="1" old
    echo -n "Farest : "; orderBy="startDate DESC" upcoming|tail -n1
    echo -n "Days/Task : "; averageCompleteTime
    }

    search() {
    [ -z ${string:-} ] && echo "HINT: Use '-s' to set search string first" && exit 1
    sqlite3 "$THINGSDB" <<-SQL
    .mode line
    SELECT
    T1.title as "Title",
    date(T1.creationDate,'unixepoch') as "Created",
    date(T1.userModificationDate,'unixepoch') as "Modified",
    date(T1.dueDate,'unixepoch') as "Due",
    date(T1.startDate,'unixepoch') as "Start",
    date(T1.stopDate,'unixepoch') as "Stopped",
    T2.title as "Project",
    T3.title as "Area"
    FROM $TASKTABLE T1
    LEFT OUTER JOIN $TASKTABLE T2 ON T1.project = T2.uuid
    LEFT OUTER JOIN $AREATABLE T3 ON T1.area = T3.uuid
    WHERE T1.trashed = 0 AND T1.type = 0
    AND (T1.title LIKE "%$string%" OR T2.title LIKE "%$string%");
    SQL

    sqlite3 "$THINGSDB" <<-SQL
    .mode line
    SELECT
    T2.title as "Title",
    date(T1.creationDate,'unixepoch') as "Created",
    date(T1.userModificationDate,'unixepoch') as "Modified",
    date(T1.stopDate,'unixepoch') as "Stopped",
    T1.title as "Task"
    FROM TMChecklistItem T1
    LEFT OUTER JOIN $TASKTABLE T2 ON T1.task = T2.uuid
    WHERE T2.trashed=0
    AND T1.title LIKE "%$string%";
    SQL
    }

    require_sqlite3() {
    command -v sqlite3 > /dev/null 2>&1 || {
    echo >&2 "ERROR: SQLite3 is required but could not be found."
    exit 1
    }
    }

    require_db() {
    test -r "$THINGSDB" -a -f "$THINGSDB" || {
    echo >&2 "ERROR: Things database not found at $THINGSDB."
    exit 2
    }
    }

    require_sqlite3
    require_db

    while [[ $# -gt 1 ]]; do
    key="$1"
    case $key in
    -l|--limitBy) limitBy="$2";shift;;
    -w|--waitingTag) waitingTag="$2";shift;;
    -o|--orderBy) orderBy="$2";shift;;
    -s|--string) string="$2";shift;;
    *) ;;
    esac
    shift
    done

    command=${1:-}

    if [[ -n $command ]]; then
    case $1 in
    inbox) inbox;;
    today) today;;
    upcoming) upcoming;;
    next) next;;
    anytime) anytime;;
    someday) someday;;
    all) all;;
    nextish) nextish;;
    completed) completed;;
    old) old;;
    due) due;;
    repeating) repeating;;
    subtasks) subtasks;;
    projects) projects;;
    headings) headings;;
    cancelled) cancelled;;
    trashed) trashed;;
    waiting) waiting;;
    notes) notes;;
    csv) csv|awk '{gsub("<[^>]*>", "")}1'|iconv -c -f UTF-8 -t WINDOWS-1252//TRANSLIT;;
    stat) limitBy="999999" stat;;
    search) search;;
    feedback) open https://github.com/AlexanderWillner/things.sh/issues/;;
    *) usage;;
    esac
    else
    usage;
    fi
    Moved to https://github.com/alexanderwillner/things.sh/
  2. AlexanderWillner revised this gist Dec 24, 2017. 1 changed file with 147 additions and 67 deletions.
    214 changes: 147 additions & 67 deletions things.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,5 @@
    #!/bin/bash
    # IMPORTANT: the latest version of this script can be found at https://github.com/AlexanderWillner/things.sh - please star, fork and contribute
    #
    # DESCRIPTION
    #
    @@ -22,23 +23,26 @@
    # CREDITS
    #
    # Author : Arjan van der Gaag (script for Things 2)
    # Author : Alexander Willner (updates for Things 3, added many more commands)
    # Date : 2017-08-14
    # Author : Alexander Willner (updates for Things 3, added many more commands, almost complete rewrite)
    # Date : 2017-12-24
    # License : Whatever. Use at your own risk.
    # Source : https://github.com/AlexanderWillner/things.sh
    #


    set -o errexit
    set -o nounset

    limitBy="20"
    waitingTag="Waiting for"
    orderBy="creationDate"

    readonly PROGNAME=$(basename $0)
    readonly ARGS="$@"
    readonly DEFAULT_DB=~/Library/Containers/com.culturedcode.ThingsMac/Data/Library/Application\ Support/Cultured\ Code/Things/Things.sqlite3
    readonly THINGSDB=${DB:-$DEFAULT_DB}

    readonly TASKTABLE="TMTask"
    readonly AREATABLE="TMArea"
    readonly TAGTABLE="TMTag"
    readonly ISNOTTRASHED="trashed = 0"
    readonly ISTRASHED="trashed = 1"
    readonly ISOPEN="status = 0"
    @@ -53,7 +57,7 @@ readonly ISHEADING="type = 2"

    usage() {
    cat <<-EOF
    usage: $PROGNAME [COMMAND]
    usage: $PROGNAME <OPTIONS> [COMMAND]
    List to do items from your Things database given a focus area.
    @@ -67,15 +71,25 @@ COMMAND:
    cancelled
    trashed
    all (show all tasks)
    nextish (show next tasks that are also in someday projects)
    old (show 20 tasks ordered by creation date)
    due (show 20 tasks ordered by due date)
    repeating (show all repeating tasks)
    subtasks (show all subtasks)
    projects (show all projects ordered by creation date)
    headings (show all headings ordered by creation date)
    csv (show all tasks as semicolon seperated values)
    stat (show an overview of the numbers of tasks)
    nextish (show $limitBy next tasks that are also in someday projects)
    old (show $limitBy tasks ordered by '$orderBy')
    due (show $limitBy tasks ordered by due date)
    waiting (show $limitBy tasks with the tag '$waitingTag' ordered by '$orderBy')
    repeating (show $limitBy repeating tasks orderd by '$orderBy')
    subtasks (show $limitBy subtasks)
    projects (show $limitBy projects ordered by creation date)
    headings (show $limitBy headings ordered by creation date)
    notes (show $limitBy notes as <headings>: <notes> ordered by creation date)
    csv (export all tasks as semicolon seperated values incl. notes and Excel friendly)
    stat (provide an overview of the numbers of tasks)
    search (provide details about specific tasks)
    feedback (give feedback, request and propose changes)
    OPTIONS:
    -l|--limitBy <number> Limit output by <number> of results
    -w|--waitingTag <tag> Set waiting tag to <tag>
    -o|--orderBy <column> Sort output by <column> (e.g. 'userModificationDate' or 'creationDate')
    -s|--string <string> String <string> to search for
    EOF
    }

    @@ -101,7 +115,7 @@ SQL

    upcoming() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    SELECT date(startDate,'unixepoch'), title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK
    AND $ISPOSTPONED AND (startDate NOT NULL OR recurrenceRule NOT NULL)
    @@ -122,7 +136,14 @@ AND $ISSTARTED
    AND (
    t.area NOT NULL
    OR
    t.project in (SELECT uuid FROM $TASKTABLE WHERE uuid=t.project AND $ISSTARTED)
    t.project in (SELECT uuid FROM $TASKTABLE WHERE uuid=t.project AND $ISSTARTED AND $ISNOTTRASHED)
    OR
    t.actionGroup in
    (SELECT uuid FROM TMTask heading WHERE uuid=t.actionGroup
    AND $ISSTARTED
    AND $ISNOTTRASHED
    AND heading.project in (SELECT uuid FROM TMTask WHERE uuid=heading.project AND $ISSTARTED AND $ISNOTTRASHED)
    )
    )
    ORDER BY todayIndex;
    SQL
    @@ -133,8 +154,7 @@ someday() {
    SELECT title
    FROM $TASKTABLE t
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISPOSTPONED
    AND $ISOPEN;
    AND $ISPOSTPONED AND $ISOPEN;
    SQL
    }

    @@ -151,10 +171,8 @@ nextish() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED
    AND $ISSTARTED
    AND $ISOPEN
    AND $ISTASK;
    WHERE $ISNOTTRASHED AND $ISSTARTED AND $ISOPEN AND $ISTASK
    LIMIT $limitBy;
    SQL
    }

    @@ -171,40 +189,31 @@ subtasks() {
    SELECT T1.title
    FROM TMChecklistItem T1
    LEFT OUTER JOIN $TASKTABLE T2 ON T1.task = T2.uuid
    WHERE T1.status=0 AND T2.status=0 AND T2.trashed=0;
    WHERE T1.status=0 AND T2.status=0 AND T2.trashed=0
    LIMIT $limitBy;
    SQL
    }

    old() {
    waiting() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT date(creationDate,'unixepoch'), title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISSTARTED
    ORDER BY creationDate
    LIMIT 20;
    SELECT T2.title
    FROM TMTaskTag T1
    LEFT JOIN $TASKTABLE T2 ON T1.tasks = T2.uuid
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND T1.tags=(SELECT uuid FROM $TAGTABLE WHERE title='$waitingTag')
    ORDER BY $orderBy
    LIMIT $limitBy;
    SQL
    }

    oldest() {
    sqlite3 "$THINGSDB" <<-SQL
    .mode tabs
    SELECT date(creationDate,'unixepoch'), title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISSTARTED
    ORDER BY creationDate
    LIMIT 1;
    SQL
    }

    future() {
    old() {
    sqlite3 "$THINGSDB" <<-SQL
    .mode tabs
    SELECT date(startDate,'unixepoch'), title
    SELECT date(creationDate,'unixepoch'), title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND startDate NOT NULL
    ORDER BY startDate DESC
    LIMIT 1;
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISSTARTED AND $ISTASK
    ORDER BY $orderBy
    LIMIT $limitBy;
    SQL
    }

    @@ -216,7 +225,7 @@ FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND dueDate NOT NULL
    ORDER BY dueDate
    LIMIT 20;
    LIMIT $limitBy;
    SQL
    }

    @@ -226,7 +235,8 @@ SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISPOSTPONED
    AND recurrenceRule NOT NULL
    ORDER BY creationDate;
    ORDER BY $orderBy
    LIMIT $limitBy;
    SQL
    }

    @@ -236,7 +246,8 @@ SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND $ISPROJECT
    ORDER BY creationDate;
    ORDER BY $orderBy
    LIMIT $limitBy;
    SQL
    }

    @@ -246,7 +257,8 @@ SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND $ISHEADING
    ORDER BY creationDate;
    ORDER BY $orderBy
    LIMIT $limitBy;
    SQL
    }

    @@ -255,7 +267,7 @@ cancelled() {
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISCANCELLED AND $ISTASK
    ORDER BY creationDate;
    ORDER BY $orderBy;
    SQL
    }

    @@ -264,7 +276,7 @@ trashed() {
    SELECT title
    FROM $TASKTABLE
    WHERE $ISTRASHED AND $ISTASK
    ORDER BY creationDate;
    ORDER BY $orderBy;
    SQL
    }

    @@ -277,11 +289,23 @@ AND $ISCOMPLETED;
    SQL
    }

    notes() {
    sqlite3 "$THINGSDB" <<-SQL
    .mode list
    .separator ": "
    SELECT
    title,
    notes
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    ORDER BY $orderBy;
    SQL
    }

    csv() {
    # fix Excel import by running ```iconv -f UTF-8 -t WINDOWS-1252```
    echo 'Title;"Creation Date";"Modification Date";"Due Date";"Start Date";Project;Area;Subtask'
    echo 'Title;"Creation Date";"Modification Date";"Due Date";"Start Date";Project;Area;Subtask;Notes'

    sqlite3 "$THINGSDB" <<-SQL
    sqlite3 "$THINGSDB" <<-SQL
    .mode csv
    .separator ";"
    SELECT
    @@ -292,7 +316,8 @@ SELECT
    date(T1.startDate,'unixepoch'),
    T2.title,
    T3.title,
    ""
    "",
    REPLACE(T1.notes, CHAR(10), ', ')
    FROM $TASKTABLE T1
    LEFT OUTER JOIN $TASKTABLE T2 ON T1.project = T2.uuid
    LEFT OUTER JOIN $AREATABLE T3 ON T1.area = T3.uuid
    @@ -333,16 +358,52 @@ stat() {
    echo ""
    echo -n "Tasks :"; all|wc -l
    echo -n "Subtasks :"; subtasks|wc -l
    echo -n "Waiting :"; waiting|wc -l
    echo -n "Projects :"; projects|wc -l
    echo -n "Repeating :"; repeating|wc -l
    echo -n "Nextish :"; nextish|wc -l
    echo -n "Headings :"; headings|wc -l
    echo ""
    echo -n "Oldest : "; oldest
    echo -n "Farest : "; future
    echo -n "Oldest : "; limitBy="1" old
    echo -n "Farest : "; orderBy="startDate DESC" upcoming|tail -n1
    echo -n "Days/Task : "; averageCompleteTime
    }

    search() {
    [ -z ${string:-} ] && echo "HINT: Use '-s' to set search string first" && exit 1
    sqlite3 "$THINGSDB" <<-SQL
    .mode line
    SELECT
    T1.title as "Title",
    date(T1.creationDate,'unixepoch') as "Created",
    date(T1.userModificationDate,'unixepoch') as "Modified",
    date(T1.dueDate,'unixepoch') as "Due",
    date(T1.startDate,'unixepoch') as "Start",
    date(T1.stopDate,'unixepoch') as "Stopped",
    T2.title as "Project",
    T3.title as "Area"
    FROM $TASKTABLE T1
    LEFT OUTER JOIN $TASKTABLE T2 ON T1.project = T2.uuid
    LEFT OUTER JOIN $AREATABLE T3 ON T1.area = T3.uuid
    WHERE T1.trashed = 0 AND T1.type = 0
    AND (T1.title LIKE "%$string%" OR T2.title LIKE "%$string%");
    SQL

    sqlite3 "$THINGSDB" <<-SQL
    .mode line
    SELECT
    T2.title as "Title",
    date(T1.creationDate,'unixepoch') as "Created",
    date(T1.userModificationDate,'unixepoch') as "Modified",
    date(T1.stopDate,'unixepoch') as "Stopped",
    T1.title as "Task"
    FROM TMChecklistItem T1
    LEFT OUTER JOIN $TASKTABLE T2 ON T1.task = T2.uuid
    WHERE T2.trashed=0
    AND T1.title LIKE "%$string%";
    SQL
    }

    require_sqlite3() {
    command -v sqlite3 > /dev/null 2>&1 || {
    echo >&2 "ERROR: SQLite3 is required but could not be found."
    @@ -357,10 +418,25 @@ require_db() {
    }
    }

    main() {
    require_sqlite3
    require_db
    case $ARGS in
    require_sqlite3
    require_db

    while [[ $# -gt 1 ]]; do
    key="$1"
    case $key in
    -l|--limitBy) limitBy="$2";shift;;
    -w|--waitingTag) waitingTag="$2";shift;;
    -o|--orderBy) orderBy="$2";shift;;
    -s|--string) string="$2";shift;;
    *) ;;
    esac
    shift
    done

    command=${1:-}

    if [[ -n $command ]]; then
    case $1 in
    inbox) inbox;;
    today) today;;
    upcoming) upcoming;;
    @@ -378,10 +454,14 @@ main() {
    headings) headings;;
    cancelled) cancelled;;
    trashed) trashed;;
    csv) csv;;
    stat) stat;;
    waiting) waiting;;
    notes) notes;;
    csv) csv|awk '{gsub("<[^>]*>", "")}1'|iconv -c -f UTF-8 -t WINDOWS-1252//TRANSLIT;;
    stat) limitBy="999999" stat;;
    search) search;;
    feedback) open https://github.com/AlexanderWillner/things.sh/issues/;;
    *) usage;;
    esac
    }

    main
    else
    usage;
    fi
  3. AlexanderWillner revised this gist Aug 14, 2017. 1 changed file with 2 additions and 22 deletions.
    24 changes: 2 additions & 22 deletions things.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -25,29 +25,9 @@
    # Author : Alexander Willner (updates for Things 3, added many more commands)
    # Date : 2017-08-14
    # License : Whatever. Use at your own risk.
    # Source : https://gist.github.com/AlexanderWillner/dad8bb7cead74eb7679b553e8c37f477
    # Source : https://github.com/AlexanderWillner/things.sh
    #
    # EXAMPLE OUTPUT
    #
    # $ things.sh stat
    # Inbox : 3
    #
    # Today : 7
    # Upcoming : 212
    # Next : 32
    # Someday : 1167
    #
    # Completed : 10973
    #
    # Tasks : 1333
    # Subtasks : 34
    # Projects : 98
    # Repeating : 83
    # Nextish : 166
    #
    # Oldest : 2016-01-22
    # Farest : 2021-01-04
    #


    set -o errexit
    set -o nounset
  4. AlexanderWillner revised this gist Aug 14, 2017. 1 changed file with 74 additions and 22 deletions.
    96 changes: 74 additions & 22 deletions things.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -23,7 +23,7 @@
    #
    # Author : Arjan van der Gaag (script for Things 2)
    # Author : Alexander Willner (updates for Things 3, added many more commands)
    # Date : 2017-08-12
    # Date : 2017-08-14
    # License : Whatever. Use at your own risk.
    # Source : https://gist.github.com/AlexanderWillner/dad8bb7cead74eb7679b553e8c37f477
    #
    @@ -57,14 +57,19 @@ readonly ARGS="$@"
    readonly DEFAULT_DB=~/Library/Containers/com.culturedcode.ThingsMac/Data/Library/Application\ Support/Cultured\ Code/Things/Things.sqlite3
    readonly THINGSDB=${DB:-$DEFAULT_DB}

    readonly TASKTABLE="TMTask"
    readonly AREATABLE="TMArea"
    readonly ISNOTTRASHED="trashed = 0"
    readonly ISTRASHED="trashed = 1"
    readonly ISOPEN="status = 0"
    readonly ISCOMPLETED="status = 3"
    readonly ISNOTSTARTED="start = 0"
    readonly ISCANCELLED="status = 2"
    readonly ISCOMPLETED="status = 3"
    readonly ISSTARTED="start = 1"
    readonly ISPOSTPONED="start = 2"
    readonly ISTASK="type = 0"
    readonly ISPROJECT="type = 1"
    readonly ISHEADING="type = 2"

    usage() {
    cat <<-EOF
    @@ -79,13 +84,16 @@ COMMAND:
    next / anytime
    someday
    completed
    cancelled
    trashed
    all (show all tasks)
    nextish (show next tasks that are also in someday projects)
    old (show 20 tasks ordered by creation date)
    due (show 20 tasks ordered by due date)
    repeating (show all repeating tasks)
    subtasks (show all subtasks)
    projects (show all projects ordered by creation date)
    headings (show all headings ordered by creation date)
    csv (show all tasks as semicolon seperated values)
    stat (show an overview of the numbers of tasks)
    EOF
    @@ -94,7 +102,7 @@ EOF
    inbox() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISNOTSTARTED AND $ISOPEN;
    SQL
    @@ -103,7 +111,7 @@ SQL
    today() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK
    AND $ISSTARTED
    AND startdate is NOT NULL
    @@ -114,7 +122,7 @@ SQL
    upcoming() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK
    AND $ISPOSTPONED AND (startDate NOT NULL OR recurrenceRule NOT NULL)
    ORDER BY startdate, todayIndex;
    @@ -128,13 +136,13 @@ anytime() {
    next() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask t
    FROM $TASKTABLE t
    WHERE $ISNOTTRASHED AND $ISTASK AND $ISOPEN
    AND $ISSTARTED
    AND (
    t.area NOT NULL
    OR
    t.project in (SELECT uuid FROM TMTask WHERE uuid=t.project AND $ISSTARTED)
    t.project in (SELECT uuid FROM $TASKTABLE WHERE uuid=t.project AND $ISSTARTED)
    )
    ORDER BY todayIndex;
    SQL
    @@ -143,7 +151,7 @@ SQL
    someday() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask t
    FROM $TASKTABLE t
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISPOSTPONED
    AND $ISOPEN;
    @@ -153,7 +161,7 @@ SQL
    completed() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISCOMPLETED;
    SQL
    @@ -162,7 +170,7 @@ SQL
    nextish() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED
    AND $ISSTARTED
    AND $ISOPEN
    @@ -173,7 +181,7 @@ SQL
    all() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK;
    SQL
    }
    @@ -182,15 +190,15 @@ subtasks() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT T1.title
    FROM TMChecklistItem T1
    LEFT OUTER JOIN TMTask T2 ON T1.task = T2.uuid
    LEFT OUTER JOIN $TASKTABLE T2 ON T1.task = T2.uuid
    WHERE T1.status=0 AND T2.status=0 AND T2.trashed=0;
    SQL
    }

    old() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT date(creationDate,'unixepoch'), title
    FROM TMTask
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISSTARTED
    ORDER BY creationDate
    LIMIT 20;
    @@ -201,7 +209,7 @@ oldest() {
    sqlite3 "$THINGSDB" <<-SQL
    .mode tabs
    SELECT date(creationDate,'unixepoch'), title
    FROM TMTask
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISSTARTED
    ORDER BY creationDate
    LIMIT 1;
    @@ -212,7 +220,7 @@ future() {
    sqlite3 "$THINGSDB" <<-SQL
    .mode tabs
    SELECT date(startDate,'unixepoch'), title
    FROM TMTask
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND startDate NOT NULL
    ORDER BY startDate DESC
    @@ -224,7 +232,7 @@ SQL
    due() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT date(dueDate,'unixepoch'), title
    FROM TMTask
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND dueDate NOT NULL
    ORDER BY dueDate
    @@ -235,7 +243,7 @@ SQL
    repeating() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISPOSTPONED
    AND recurrenceRule NOT NULL
    ORDER BY creationDate;
    @@ -245,13 +253,50 @@ SQL
    projects() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND $ISPROJECT
    ORDER BY creationDate;
    SQL
    }

    headings() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND $ISHEADING
    ORDER BY creationDate;
    SQL
    }

    cancelled() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISCANCELLED AND $ISTASK
    ORDER BY creationDate;
    SQL
    }

    trashed() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM $TASKTABLE
    WHERE $ISTRASHED AND $ISTASK
    ORDER BY creationDate;
    SQL
    }

    averageCompleteTime() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT ROUND(AVG(JULIANDAY(stopDate,'unixepoch')-JULIANDAY(creationDate,'unixepoch')))
    FROM $TASKTABLE
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISCOMPLETED;
    SQL
    }

    csv() {
    # fix Excel import by running ```iconv -f UTF-8 -t WINDOWS-1252```
    echo 'Title;"Creation Date";"Modification Date";"Due Date";"Start Date";Project;Area;Subtask'
    @@ -268,9 +313,9 @@ SELECT
    T2.title,
    T3.title,
    ""
    FROM TMTask T1
    LEFT OUTER JOIN TMTask T2 ON T1.project = T2.uuid
    LEFT OUTER JOIN TMArea T3 ON T1.area = T3.uuid
    FROM $TASKTABLE T1
    LEFT OUTER JOIN $TASKTABLE T2 ON T1.project = T2.uuid
    LEFT OUTER JOIN $AREATABLE T3 ON T1.area = T3.uuid
    WHERE T1.trashed = 0 AND T1.status = 0 AND T1.type = 0;
    SQL

    @@ -288,7 +333,7 @@ SELECT
    "",
    T1.title
    FROM TMChecklistItem T1
    LEFT OUTER JOIN TMTask T2 ON T1.task = T2.uuid
    LEFT OUTER JOIN $TASKTABLE T2 ON T1.task = T2.uuid
    WHERE T1.status=0 AND T2.status=0 AND T2.trashed=0;
    SQL
    }
    @@ -303,15 +348,19 @@ stat() {
    echo -n "Someday :"; someday|wc -l
    echo ""
    echo -n "Completed :"; completed|wc -l
    echo -n "Cancelled :"; cancelled|wc -l
    echo -n "Trashed :"; trashed|wc -l
    echo ""
    echo -n "Tasks :"; all|wc -l
    echo -n "Subtasks :"; subtasks|wc -l
    echo -n "Projects :"; projects|wc -l
    echo -n "Repeating :"; repeating|wc -l
    echo -n "Nextish :"; nextish|wc -l
    echo -n "Headings :"; headings|wc -l
    echo ""
    echo -n "Oldest : "; oldest
    echo -n "Farest : "; future
    echo -n "Days/Task : "; averageCompleteTime
    }

    require_sqlite3() {
    @@ -346,6 +395,9 @@ main() {
    repeating) repeating;;
    subtasks) subtasks;;
    projects) projects;;
    headings) headings;;
    cancelled) cancelled;;
    trashed) trashed;;
    csv) csv;;
    stat) stat;;
    *) usage;;
  5. AlexanderWillner revised this gist Aug 12, 2017. 1 changed file with 4 additions and 3 deletions.
    7 changes: 4 additions & 3 deletions things.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -23,7 +23,7 @@
    #
    # Author : Arjan van der Gaag (script for Things 2)
    # Author : Alexander Willner (updates for Things 3, added many more commands)
    # Date : 2017-08-11
    # Date : 2017-08-12
    # License : Whatever. Use at your own risk.
    # Source : https://gist.github.com/AlexanderWillner/dad8bb7cead74eb7679b553e8c37f477
    #
    @@ -183,7 +183,7 @@ subtasks() {
    SELECT T1.title
    FROM TMChecklistItem T1
    LEFT OUTER JOIN TMTask T2 ON T1.task = T2.uuid
    WHERE T1.status=0 AND T2.status=0;
    WHERE T1.status=0 AND T2.status=0 AND T2.trashed=0;
    SQL
    }

    @@ -285,10 +285,11 @@ SELECT
    "",
    "",
    "",
    "",
    T1.title
    FROM TMChecklistItem T1
    LEFT OUTER JOIN TMTask T2 ON T1.task = T2.uuid
    WHERE T1.status=0 AND T2.status=0;
    WHERE T1.status=0 AND T2.status=0 AND T2.trashed=0;
    SQL
    }

  6. AlexanderWillner revised this gist Aug 12, 2017. 1 changed file with 7 additions and 4 deletions.
    11 changes: 7 additions & 4 deletions things.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -254,7 +254,7 @@ SQL

    csv() {
    # fix Excel import by running ```iconv -f UTF-8 -t WINDOWS-1252```
    echo 'Title;"Creation Date";"Modification Date";"Due Date";"Start Date";Project;Area'
    echo 'Title;"Creation Date";"Modification Date";"Due Date";"Start Date";Project;Area;Subtask'

    sqlite3 "$THINGSDB" <<-SQL
    .mode csv
    @@ -266,7 +266,8 @@ SELECT
    date(T1.dueDate,'unixepoch'),
    date(T1.startDate,'unixepoch'),
    T2.title,
    T3.title
    T3.title,
    ""
    FROM TMTask T1
    LEFT OUTER JOIN TMTask T2 ON T1.project = T2.uuid
    LEFT OUTER JOIN TMArea T3 ON T1.area = T3.uuid
    @@ -277,12 +278,14 @@ sqlite3 "$THINGSDB" <<-SQL
    .mode csv
    .separator ";"
    SELECT
    T1.title,
    T2.title,
    date(T1.creationDate,'unixepoch'),
    date(T1.userModificationDate,'unixepoch'),
    ""
    "",
    T2.title
    "",
    "",
    T1.title
    FROM TMChecklistItem T1
    LEFT OUTER JOIN TMTask T2 ON T1.task = T2.uuid
    WHERE T1.status=0 AND T2.status=0;
  7. AlexanderWillner revised this gist Aug 12, 2017. 1 changed file with 98 additions and 8 deletions.
    106 changes: 98 additions & 8 deletions things.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -26,6 +26,28 @@
    # Date : 2017-08-11
    # License : Whatever. Use at your own risk.
    # Source : https://gist.github.com/AlexanderWillner/dad8bb7cead74eb7679b553e8c37f477
    #
    # EXAMPLE OUTPUT
    #
    # $ things.sh stat
    # Inbox : 3
    #
    # Today : 7
    # Upcoming : 212
    # Next : 32
    # Someday : 1167
    #
    # Completed : 10973
    #
    # Tasks : 1333
    # Subtasks : 34
    # Projects : 98
    # Repeating : 83
    # Nextish : 166
    #
    # Oldest : 2016-01-22
    # Farest : 2021-01-04
    #

    set -o errexit
    set -o nounset
    @@ -57,13 +79,15 @@ COMMAND:
    next / anytime
    someday
    completed
    all (show all todos)
    nextish (show next todos that are also in someday projects)
    old (show 20 todos ordered by creation date)
    due (show 20 todos ordered by due date)
    all (show all tasks)
    nextish (show next tasks that are also in someday projects)
    old (show 20 tasks ordered by creation date)
    due (show 20 tasks ordered by due date)
    repeating (show all repeating tasks)
    subtasks (show all subtasks)
    projects (show all projects ordered by creation date)
    csv (show all todos as semicolon seperated values)
    stat (give an overview)
    csv (show all tasks as semicolon seperated values)
    stat (show an overview of the numbers of tasks)
    EOF
    }

    @@ -154,6 +178,15 @@ WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK;
    SQL
    }

    subtasks() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT T1.title
    FROM TMChecklistItem T1
    LEFT OUTER JOIN TMTask T2 ON T1.task = T2.uuid
    WHERE T1.status=0 AND T2.status=0;
    SQL
    }

    old() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT date(creationDate,'unixepoch'), title
    @@ -164,6 +197,30 @@ LIMIT 20;
    SQL
    }

    oldest() {
    sqlite3 "$THINGSDB" <<-SQL
    .mode tabs
    SELECT date(creationDate,'unixepoch'), title
    FROM TMTask
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISSTARTED
    ORDER BY creationDate
    LIMIT 1;
    SQL
    }

    future() {
    sqlite3 "$THINGSDB" <<-SQL
    .mode tabs
    SELECT date(startDate,'unixepoch'), title
    FROM TMTask
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND startDate NOT NULL
    ORDER BY startDate DESC
    LIMIT 1;
    SQL
    }


    due() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT date(dueDate,'unixepoch'), title
    @@ -175,6 +232,16 @@ LIMIT 20;
    SQL
    }

    repeating() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISPOSTPONED
    AND recurrenceRule NOT NULL
    ORDER BY creationDate;
    SQL
    }

    projects() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    @@ -188,6 +255,7 @@ SQL
    csv() {
    # fix Excel import by running ```iconv -f UTF-8 -t WINDOWS-1252```
    echo 'Title;"Creation Date";"Modification Date";"Due Date";"Start Date";Project;Area'

    sqlite3 "$THINGSDB" <<-SQL
    .mode csv
    .separator ";"
    @@ -204,6 +272,21 @@ LEFT OUTER JOIN TMTask T2 ON T1.project = T2.uuid
    LEFT OUTER JOIN TMArea T3 ON T1.area = T3.uuid
    WHERE T1.trashed = 0 AND T1.status = 0 AND T1.type = 0;
    SQL

    sqlite3 "$THINGSDB" <<-SQL
    .mode csv
    .separator ";"
    SELECT
    T1.title,
    date(T1.creationDate,'unixepoch'),
    date(T1.userModificationDate,'unixepoch'),
    ""
    "",
    T2.title
    FROM TMChecklistItem T1
    LEFT OUTER JOIN TMTask T2 ON T1.task = T2.uuid
    WHERE T1.status=0 AND T2.status=0;
    SQL
    }


    @@ -217,9 +300,14 @@ stat() {
    echo ""
    echo -n "Completed :"; completed|wc -l
    echo ""
    echo -n "All :"; all|wc -l
    echo -n "Nextish :"; nextish|wc -l
    echo -n "Tasks :"; all|wc -l
    echo -n "Subtasks :"; subtasks|wc -l
    echo -n "Projects :"; projects|wc -l
    echo -n "Repeating :"; repeating|wc -l
    echo -n "Nextish :"; nextish|wc -l
    echo ""
    echo -n "Oldest : "; oldest
    echo -n "Farest : "; future
    }

    require_sqlite3() {
    @@ -251,6 +339,8 @@ main() {
    completed) completed;;
    old) old;;
    due) due;;
    repeating) repeating;;
    subtasks) subtasks;;
    projects) projects;;
    csv) csv;;
    stat) stat;;
  8. AlexanderWillner revised this gist Aug 11, 2017. 1 changed file with 16 additions and 16 deletions.
    32 changes: 16 additions & 16 deletions things.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -37,8 +37,10 @@ readonly THINGSDB=${DB:-$DEFAULT_DB}

    readonly ISNOTTRASHED="trashed = 0"
    readonly ISOPEN="status = 0"
    readonly ISACTIVE="start = 1"
    readonly ISNOTACTIVE="start = 2"
    readonly ISCOMPLETED="status = 3"
    readonly ISNOTSTARTED="start = 0"
    readonly ISSTARTED="start = 1"
    readonly ISPOSTPONED="start = 2"
    readonly ISTASK="type = 0"
    readonly ISPROJECT="type = 1"

    @@ -69,9 +71,8 @@ inbox() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE $ISNOTTRASHED AND type=0
    AND start =0
    AND $ISOPEN;
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISNOTSTARTED AND $ISOPEN;
    SQL
    }

    @@ -80,8 +81,8 @@ today() {
    SELECT title
    FROM TMTask
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK
    AND startdate is not null
    AND $ISACTIVE
    AND $ISSTARTED
    AND startdate is NOT NULL
    ORDER BY startdate, todayIndex;
    SQL
    }
    @@ -91,7 +92,7 @@ upcoming() {
    SELECT title
    FROM TMTask
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK
    AND $ISNOTACTIVE AND (startDate NOT NULL OR recurrenceRule NOT NULL)
    AND $ISPOSTPONED AND (startDate NOT NULL OR recurrenceRule NOT NULL)
    ORDER BY startdate, todayIndex;
    SQL
    }
    @@ -105,11 +106,11 @@ next() {
    SELECT title
    FROM TMTask t
    WHERE $ISNOTTRASHED AND $ISTASK AND $ISOPEN
    AND $ISACTIVE
    AND $ISSTARTED
    AND (
    t.area NOT NULL
    OR
    t.project in (SELECT uuid FROM TMTask WHERE uuid=t.project AND $ISACTIVE)
    t.project in (SELECT uuid FROM TMTask WHERE uuid=t.project AND $ISSTARTED)
    )
    ORDER BY todayIndex;
    SQL
    @@ -120,7 +121,7 @@ someday() {
    SELECT title
    FROM TMTask t
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISNOTACTIVE
    AND $ISPOSTPONED
    AND $ISOPEN;
    SQL
    }
    @@ -129,9 +130,8 @@ completed() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE $ISNOTTRASHED
    AND status = 3
    AND type=0;
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISCOMPLETED;
    SQL
    }

    @@ -140,7 +140,7 @@ nextish() {
    SELECT title
    FROM TMTask
    WHERE $ISNOTTRASHED
    AND $ISACTIVE
    AND $ISSTARTED
    AND $ISOPEN
    AND $ISTASK;
    SQL
    @@ -158,7 +158,7 @@ old() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT date(creationDate,'unixepoch'), title
    FROM TMTask
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISACTIVE
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISSTARTED
    ORDER BY creationDate
    LIMIT 20;
    SQL
  9. AlexanderWillner revised this gist Aug 11, 2017. 1 changed file with 33 additions and 9 deletions.
    42 changes: 33 additions & 9 deletions things.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -44,22 +44,23 @@ readonly ISPROJECT="type = 1"

    usage() {
    cat <<-EOF
    usage: $PROGNAME [FOCUS]
    usage: $PROGNAME [COMMAND]
    List to do items from your Things database given a focus area.
    FOCUS:
    COMMAND:
    inbox
    today
    upcoming
    next (now called 'anytime')
    next / anytime
    someday
    completed
    nextAll (next actions also in someday projects)
    all (just count all todos and projects)
    old (show 20 todos ordered by creation date)
    all (show all todos)
    nextish (show next todos that are also in someday projects)
    old (show 20 todos ordered by creation date)
    due (show 20 todos ordered by due date)
    projects (show all projects ordered by creation date)
    csv (show all todos as semicolon seperated values)
    stat (give an overview)
    EOF
    }
    @@ -134,7 +135,7 @@ AND type=0;
    SQL
    }

    nextAll() {
    nextish() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    @@ -184,6 +185,28 @@ ORDER BY creationDate;
    SQL
    }

    csv() {
    # fix Excel import by running ```iconv -f UTF-8 -t WINDOWS-1252```
    echo 'Title;"Creation Date";"Modification Date";"Due Date";"Start Date";Project;Area'
    sqlite3 "$THINGSDB" <<-SQL
    .mode csv
    .separator ";"
    SELECT
    T1.title,
    date(T1.creationDate,'unixepoch'),
    date(T1.userModificationDate,'unixepoch'),
    date(T1.dueDate,'unixepoch'),
    date(T1.startDate,'unixepoch'),
    T2.title,
    T3.title
    FROM TMTask T1
    LEFT OUTER JOIN TMTask T2 ON T1.project = T2.uuid
    LEFT OUTER JOIN TMArea T3 ON T1.area = T3.uuid
    WHERE T1.trashed = 0 AND T1.status = 0 AND T1.type = 0;
    SQL
    }


    stat() {
    echo -n "Inbox :"; inbox|wc -l
    echo ""
    @@ -195,7 +218,7 @@ stat() {
    echo -n "Completed :"; completed|wc -l
    echo ""
    echo -n "All :"; all|wc -l
    echo -n "NextAll :"; nextAll|wc -l
    echo -n "Nextish :"; nextish|wc -l
    echo -n "Projects :"; projects|wc -l
    }

    @@ -224,11 +247,12 @@ main() {
    anytime) anytime;;
    someday) someday;;
    all) all;;
    nextAll) nextAll;;
    nextish) nextish;;
    completed) completed;;
    old) old;;
    due) due;;
    projects) projects;;
    csv) csv;;
    stat) stat;;
    *) usage;;
    esac
  10. AlexanderWillner revised this gist Aug 11, 2017. 1 changed file with 79 additions and 40 deletions.
    119 changes: 79 additions & 40 deletions things.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -21,16 +21,11 @@
    #
    # CREDITS
    #
    # Author: Arjan van der Gaag (script for Things 2)
    # Author: Alexander Willner (updates for Things 3, added many more commands)
    # Date: 2017-08-10
    # License: Whatever. Use at your own risk.
    #
    # DEBUG INFORMATION
    #
    # status: 0=open, 2=cancelled, 3=repeating or done
    # type: 0=normal, 2=heading

    # Author : Arjan van der Gaag (script for Things 2)
    # Author : Alexander Willner (updates for Things 3, added many more commands)
    # Date : 2017-08-11
    # License : Whatever. Use at your own risk.
    # Source : https://gist.github.com/AlexanderWillner/dad8bb7cead74eb7679b553e8c37f477

    set -o errexit
    set -o nounset
    @@ -40,6 +35,13 @@ readonly ARGS="$@"
    readonly DEFAULT_DB=~/Library/Containers/com.culturedcode.ThingsMac/Data/Library/Application\ Support/Cultured\ Code/Things/Things.sqlite3
    readonly THINGSDB=${DB:-$DEFAULT_DB}

    readonly ISNOTTRASHED="trashed = 0"
    readonly ISOPEN="status = 0"
    readonly ISACTIVE="start = 1"
    readonly ISNOTACTIVE="start = 2"
    readonly ISTASK="type = 0"
    readonly ISPROJECT="type = 1"

    usage() {
    cat <<-EOF
    usage: $PROGNAME [FOCUS]
    @@ -50,33 +52,35 @@ FOCUS:
    inbox
    today
    upcoming
    next (now called 'anytime')
    next (now called 'anytime')
    someday
    completed
    nextAll (next actions also in someday projects)
    all (just count all todos and projects)
    stat (give an overview)
    nextAll (next actions also in someday projects)
    all (just count all todos and projects)
    old (show 20 todos ordered by creation date)
    due (show 20 todos ordered by due date)
    projects (show all projects ordered by creation date)
    stat (give an overview)
    EOF
    }

    inbox() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE trashed = 0 AND type=0
    WHERE $ISNOTTRASHED AND type=0
    AND start =0
    AND status = 0;
    AND $ISOPEN;
    SQL
    }

    today() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE trashed = 0 AND type=0
    AND status = 0
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK
    AND startdate is not null
    AND start = 1
    AND $ISACTIVE
    ORDER BY startdate, todayIndex;
    SQL
    }
    @@ -85,8 +89,8 @@ upcoming() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE trashed = 0 AND type=0
    AND status = 0 AND start = 2 AND startDate not null
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK
    AND $ISNOTACTIVE AND (startDate NOT NULL OR recurrenceRule NOT NULL)
    ORDER BY startdate, todayIndex;
    SQL
    }
    @@ -99,10 +103,13 @@ next() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask t
    WHERE trashed = 0 AND type=0
    AND start = 1
    AND status = 0
    AND t.project in (select uuid from TMTask where uuid=t.project and start=1)
    WHERE $ISNOTTRASHED AND $ISTASK AND $ISOPEN
    AND $ISACTIVE
    AND (
    t.area NOT NULL
    OR
    t.project in (SELECT uuid FROM TMTask WHERE uuid=t.project AND $ISACTIVE)
    )
    ORDER BY todayIndex;
    SQL
    }
    @@ -111,43 +118,71 @@ someday() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask t
    WHERE trashed = 0 AND type=0
    AND start = 2
    AND status = 0;
    WHERE $ISNOTTRASHED AND $ISTASK
    AND $ISNOTACTIVE
    AND $ISOPEN;
    SQL
    }

    nextAll() {
    completed() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE trashed = 0
    AND start = 1
    AND status = 0
    WHERE $ISNOTTRASHED
    AND status = 3
    AND type=0;
    SQL
    }

    completed() {
    nextAll() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE trashed = 0
    AND status = 3
    AND type=0;
    WHERE $ISNOTTRASHED
    AND $ISACTIVE
    AND $ISOPEN
    AND $ISTASK;
    SQL
    }

    all() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE trashed = 0
    AND status = 0
    AND type=0;
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISTASK;
    SQL
    }

    old() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT date(creationDate,'unixepoch'), title
    FROM TMTask
    WHERE $ISNOTTRASHED AND $ISOPEN AND $ISACTIVE
    ORDER BY creationDate
    LIMIT 20;
    SQL
    }

    due() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT date(dueDate,'unixepoch'), title
    FROM TMTask
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND dueDate NOT NULL
    ORDER BY dueDate
    LIMIT 20;
    SQL
    }

    projects() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE $ISNOTTRASHED AND $ISOPEN
    AND $ISPROJECT
    ORDER BY creationDate;
    SQL
    }

    stat() {
    echo -n "Inbox :"; inbox|wc -l
    @@ -160,7 +195,8 @@ stat() {
    echo -n "Completed :"; completed|wc -l
    echo ""
    echo -n "All :"; all|wc -l
    echo -n "NextAll :"; nextAll|wc -l
    echo -n "NextAll :"; nextAll|wc -l
    echo -n "Projects :"; projects|wc -l
    }

    require_sqlite3() {
    @@ -190,6 +226,9 @@ main() {
    all) all;;
    nextAll) nextAll;;
    completed) completed;;
    old) old;;
    due) due;;
    projects) projects;;
    stat) stat;;
    *) usage;;
    esac
  11. AlexanderWillner revised this gist Aug 11, 2017. 1 changed file with 118 additions and 38 deletions.
    156 changes: 118 additions & 38 deletions things.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,7 @@
    #
    # DESCRIPTION
    #
    # Simple read-only comand-line interface to your Things 2 database. Since
    # Simple read-only comand-line interface to your Things 3 database. Since
    # Things uses a SQLite database (which should come pre-installed on your Mac)
    # we can simply query it straight from the command line.
    #
    @@ -21,16 +21,23 @@
    #
    # CREDITS
    #
    # Author: Arjan van der Gaag
    # Date: 2014-07-23
    # Author: Arjan van der Gaag (script for Things 2)
    # Author: Alexander Willner (updates for Things 3, added many more commands)
    # Date: 2017-08-10
    # License: Whatever. Use at your own risk.
    #
    # DEBUG INFORMATION
    #
    # status: 0=open, 2=cancelled, 3=repeating or done
    # type: 0=normal, 2=heading


    set -o errexit
    set -o nounset

    readonly PROGNAME=$(basename $0)
    readonly ARGS="$@"
    readonly DEFAULT_DB=~/Library/Containers/com.culturedcode.things/Data/Library/Application\ Support/Cultured\ Code/Things/ThingsLibrary.db
    readonly DEFAULT_DB=~/Library/Containers/com.culturedcode.ThingsMac/Data/Library/Application\ Support/Cultured\ Code/Things/Things.sqlite3
    readonly THINGSDB=${DB:-$DEFAULT_DB}

    usage() {
    @@ -40,56 +47,122 @@ usage: $PROGNAME [FOCUS]
    List to do items from your Things database given a focus area.
    FOCUS:
    today
    next
    inbox
    EXAMPLES:
    List all items scheduled for today:
    $PROGNAME today
    List all next items:
    $PROGNAME next
    List all inbox items:
    $PROGNAME inbox
    today
    upcoming
    next (now called 'anytime')
    someday
    completed
    nextAll (next actions also in someday projects)
    all (just count all todos and projects)
    stat (give an overview)
    EOF
    }

    inbox() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE trashed = 0 AND type=0
    AND start =0
    AND status = 0;
    SQL
    }

    today() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT ztitle
    FROM zthing
    WHERE ztrashed = 0
    AND z_ent = 13
    AND zstatus = 0
    AND zscheduler = 1
    AND zstartdate is not null
    AND zstart = 1;
    SELECT title
    FROM TMTask
    WHERE trashed = 0 AND type=0
    AND status = 0
    AND startdate is not null
    AND start = 1
    ORDER BY startdate, todayIndex;
    SQL
    }

    upcoming() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE trashed = 0 AND type=0
    AND status = 0 AND start = 2 AND startDate not null
    ORDER BY startdate, todayIndex;
    SQL
    }

    anytime() {
    next
    }

    next() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT ztitle
    FROM zthing
    WHERE ztrashed = 0
    AND z_ent = 13
    AND zstatus = 0;
    SELECT title
    FROM TMTask t
    WHERE trashed = 0 AND type=0
    AND start = 1
    AND status = 0
    AND t.project in (select uuid from TMTask where uuid=t.project and start=1)
    ORDER BY todayIndex;
    SQL
    }

    inbox() {
    someday() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask t
    WHERE trashed = 0 AND type=0
    AND start = 2
    AND status = 0;
    SQL
    }

    nextAll() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE trashed = 0
    AND start = 1
    AND status = 0
    AND type=0;
    SQL
    }

    completed() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT ztitle
    FROM zthing
    WHERE z_ent = 13
    AND ztrashed = 0
    AND zstart =0
    AND zstatus = 0;
    SELECT title
    FROM TMTask
    WHERE trashed = 0
    AND status = 3
    AND type=0;
    SQL
    }

    all() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT title
    FROM TMTask
    WHERE trashed = 0
    AND status = 0
    AND type=0;
    SQL
    }


    stat() {
    echo -n "Inbox :"; inbox|wc -l
    echo ""
    echo -n "Today :"; today|wc -l
    echo -n "Upcoming :"; upcoming|wc -l
    echo -n "Next :"; next|wc -l
    echo -n "Someday :"; someday|wc -l
    echo ""
    echo -n "Completed :"; completed|wc -l
    echo ""
    echo -n "All :"; all|wc -l
    echo -n "NextAll :"; nextAll|wc -l
    }

    require_sqlite3() {
    command -v sqlite3 > /dev/null 2>&1 || {
    echo >&2 "ERROR: SQLite3 is required but could not be found."
    @@ -108,11 +181,18 @@ main() {
    require_sqlite3
    require_db
    case $ARGS in
    inbox) inbox;;
    today) today;;
    upcoming) upcoming;;
    next) next;;
    inbox) inbox;;
    anytime) anytime;;
    someday) someday;;
    all) all;;
    nextAll) nextAll;;
    completed) completed;;
    stat) stat;;
    *) usage;;
    esac
    }

    main
    main
  12. @avdgaag avdgaag renamed this gist Jul 23, 2014. 1 changed file with 0 additions and 0 deletions.
    0 things → things.sh
    View file
    File renamed without changes.
  13. @avdgaag avdgaag created this gist Jul 23, 2014.
    118 changes: 118 additions & 0 deletions things
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,118 @@
    #!/bin/bash
    #
    # DESCRIPTION
    #
    # Simple read-only comand-line interface to your Things 2 database. Since
    # Things uses a SQLite database (which should come pre-installed on your Mac)
    # we can simply query it straight from the command line.
    #
    # We only do read operations since we don't want to mess up your data.
    #
    # INSTALLATION
    #
    # Put this file somewhere in your $PATH and make it executable.
    #
    # INSTRUCTIONS
    #
    # Note that you could override the location of the database used by setting the
    # THINGSDB environment variable.
    #
    # For usage information, run the script with no arguments or with "help".
    #
    # CREDITS
    #
    # Author: Arjan van der Gaag
    # Date: 2014-07-23
    # License: Whatever. Use at your own risk.

    set -o errexit
    set -o nounset

    readonly PROGNAME=$(basename $0)
    readonly ARGS="$@"
    readonly DEFAULT_DB=~/Library/Containers/com.culturedcode.things/Data/Library/Application\ Support/Cultured\ Code/Things/ThingsLibrary.db
    readonly THINGSDB=${DB:-$DEFAULT_DB}

    usage() {
    cat <<-EOF
    usage: $PROGNAME [FOCUS]
    List to do items from your Things database given a focus area.
    FOCUS:
    today
    next
    inbox
    EXAMPLES:
    List all items scheduled for today:
    $PROGNAME today
    List all next items:
    $PROGNAME next
    List all inbox items:
    $PROGNAME inbox
    EOF
    }

    today() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT ztitle
    FROM zthing
    WHERE ztrashed = 0
    AND z_ent = 13
    AND zstatus = 0
    AND zscheduler = 1
    AND zstartdate is not null
    AND zstart = 1;
    SQL
    }

    next() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT ztitle
    FROM zthing
    WHERE ztrashed = 0
    AND z_ent = 13
    AND zstatus = 0;
    SQL
    }

    inbox() {
    sqlite3 "$THINGSDB" <<-SQL
    SELECT ztitle
    FROM zthing
    WHERE z_ent = 13
    AND ztrashed = 0
    AND zstart =0
    AND zstatus = 0;
    SQL
    }

    require_sqlite3() {
    command -v sqlite3 > /dev/null 2>&1 || {
    echo >&2 "ERROR: SQLite3 is required but could not be found."
    exit 1
    }
    }

    require_db() {
    test -r "$THINGSDB" -a -f "$THINGSDB" || {
    echo >&2 "ERROR: Things database not found at $THINGSDB."
    exit 2
    }
    }

    main() {
    require_sqlite3
    require_db
    case $ARGS in
    today) today;;
    next) next;;
    inbox) inbox;;
    *) usage;;
    esac
    }

    main