Reverse engineering Things SQLite tasks database

things_app_sqlite_reverse_engineered_schema.md

To open SQLite Things.app database run this command in Terminal.app:

$ sqlite3 ~/Library/Containers/com.culturedcode.things/Data/Library/Application\ Support/Cultured\ Code/Things/ThingsLibrary.db

In SQLite command-line type this query to get your tasks stats:

sqlite> .mode column
sqlite> .header on
sqlite> select zscheduler, zstatus, ztrashed, count(*) from ZTHING where z_ent = 13 group by  zstatus,ztrashed order by Z_pk desc;
ZSCHEDULER  ZSTATUS     ZTRASHED    count(*)
----------  ----------  ----------  ----------
1           0           0           132
            3           0           1005
            0           1           84
            3           1           2
            2           0           6

Explaination:

• Z_ENT equals 13 represents tasks
• ZTRASHED equals 1 is obvious - shows that element is deleted
• ZSTATUS equals 0 represents items not yet done
• ZSTATUS equals 3 represents items that are done and in Logbook
• ZSTATUS equals 2 represents items that were canceled
• ZSCHEDULER equals 1 represents items that are planned to be done (as scheduled or recurring tasks or those in section Today)

BONUS:

• ZSCHEDULER = 1 AND ZSTART = 1 AND ZTRASHED = 0 - means it's in Things Today section :)
• Projects can be found by: ZCOMPACT = 0 AND Z_ENT = 13
• Z_ENT equals 9 are Contacts, and 14 are tags

Rest of fields are self explanatory ( ZTITLE or ZSTARTDATE or ZNOTES or ZPARENT).

P.S. I don't recommend direct edits on this SQLite DB. If you don't want to corrupt your data use it only for SELECTs.

Happy hacking!

@ravbaker