Last active
July 13, 2023 14:47
-
-
Save christiangenco/1098b8acc5a426815226e6a04f23c474 to your computer and use it in GitHub Desktop.
Convert Things database into csv and json
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env ruby | |
| # require 'sqlite3' | |
| require 'shellwords' | |
| require 'json' | |
| require 'csv' | |
| # require 'pry' | |
| def tasks_to_csv(sqlite3_path, dest_path) | |
| query = 'select TASK.*, AREA.title as areaTitle, PROJECT.title as projectTitle, HEADING.title as headingTitle' | |
| # query = "select *," | |
| # query += " strftime('%Y', TASK.stopDate) as stopDateX" | |
| query += ' from TMTask as TASK' | |
| query += ' LEFT OUTER JOIN TMTask PROJECT ON TASK.project = PROJECT.uuid' | |
| query += ' LEFT OUTER JOIN TMArea AREA ON TASK.area = AREA.uuid' | |
| query += ' LEFT OUTER JOIN TMTask HEADING ON TASK.actionGroup = HEADING.uuid' | |
| `sqlite3 -header -csv #{sqlite3_path.shellescape} "#{query}" > #{dest_path.shellescape}` | |
| end | |
| BASE_DIR = File.expand_path("~/Library/Containers/com.culturedcode.ThingsMac/Data/Library/Application\ Support/Cultured\ Code/Things/") | |
| puts "translating all the sqlite3 databases into csv snapshots" | |
| backups_glob_path = File.join(BASE_DIR, 'Backups/*.sqlite3') | |
| dest_dir = File.join(BASE_DIR, 'csvs') | |
| # generate from backup any days we're missing | |
| Dir.glob(backups_glob_path).each do |backup_path| | |
| backup_path =~ /ThingsBackup (\d{4}\-\d{2}\-\d{2})/ | |
| date = Regexp.last_match(1) | |
| dest = File.join(dest_dir, date + '.csv') | |
| `mkdir -p #{File.dirname(dest).shellescape}` | |
| next if File.exist?(dest) | |
| tasks_to_csv(backup_path, dest) | |
| end | |
| today_dest = File.join(dest_dir, Time.now.strftime("%Y-%m-%d") + '.csv') | |
| tasks_to_csv(File.join(BASE_DIR, "Things.sqlite3"), today_dest) # unless File.exist?(today_dest) | |
| # exit | |
| # THEN: compute task_ids_scheduled and task_ids_completed | |
| # # csv_filename = File.join(dest_dir, "2018-10-27.csv") | |
| def read_tasks_csv(csv_filename) | |
| CSV.new(File.read(csv_filename), headers: true).to_a.map{|task| | |
| task.to_hash.map{|k, v| | |
| if(k.include?("Date")) | |
| # dynamically cast all Date fields to actual dates | |
| [k, Time.at(v.to_f)] | |
| else | |
| [k, v] | |
| end | |
| }.to_h | |
| } | |
| end | |
| def today(tasks) | |
| tasks.select do |t| | |
| t['trashed'] == '0' && # TASK.$ISNOTTRASHED | |
| t['status'] == '0' && # TASK.$ISOPEN | |
| t['type'] == '0' && # TASK.$ISTASK | |
| t['start'] == '1' && # TASK.$ISSTARTED | |
| t['startDate'].to_i != 0 && # TASK.startdate is NOT NULL | |
| true | |
| end | |
| end | |
| def completed(tasks) | |
| tasks.select do |t| | |
| t['trashed'] == '0' && # TASK.$ISNOTTRASHED | |
| t['type'] == '0' && # TASK.$ISTASK | |
| t['status'] == '3' && # TASK.$ISCOMPLETED | |
| true | |
| end | |
| end | |
| def finished_on(tasks, date) | |
| date = Date.parse(date) if date.class == String | |
| tasks.select do |t| | |
| t["stopDate"].to_date == date | |
| end | |
| end | |
| json_path = File.join(BASE_DIR, "things.json") | |
| json = File.exists?(json_path) ? JSON.parse(File.read(json_path)) : {} | |
| tasks_scheduled_completed = json["tasksScheduledCompleted"] || {} | |
| csvs_glob_path = File.join(BASE_DIR, 'csvs/*.csv') | |
| Dir.glob(csvs_glob_path).each{|csv_filename| | |
| puts "processing #{csv_filename}" | |
| tasks = read_tasks_csv(csv_filename) | |
| today_date = Date.parse(File.basename(csv_filename, ".csv")) | |
| today_tasks = today(tasks) | |
| completed_tasks = completed(tasks) | |
| completed_today = finished_on(completed_tasks, today_date) | |
| today_iso = today_date.strftime("%Y-%m-%d") | |
| tasks_scheduled_completed[today_iso] ||= {}; | |
| tasks_scheduled_completed[today_iso]["taskIdsScheduled"] = (today_tasks.map{|t| t["uuid"]} + completed_today.map{|t| t["uuid"]}).uniq | |
| } | |
| puts "work with the most #recent data to define task_ids_completed" | |
| tasks = read_tasks_csv(Dir.glob(csvs_glob_path).sort.last) | |
| completed(tasks).each{|task| | |
| date_iso = task["stopDate"].strftime("%Y-%m-%d") | |
| tasks_scheduled_completed[date_iso] ||= {} | |
| (tasks_scheduled_completed[date_iso]["taskIdsCompleted"] ||= []) << task["uuid"] | |
| } | |
| scores = json["scores"] || {} | |
| tasks_scheduled_completed.each{|date_iso, task_ids| | |
| task_ids_scheduled = task_ids["taskIdsScheduled"] || [] | |
| task_ids_completed = task_ids["taskIdsCompleted"] || [] | |
| monkey_score = task_ids_completed.length | |
| if task_ids_scheduled.length.to_f == 0 | |
| # managers don't get points if they don't schedule anything | |
| manager_score = 0 | |
| else | |
| manager_score = (task_ids_scheduled.length - (task_ids_scheduled - task_ids_completed).length).to_f / task_ids_scheduled.length.to_f | |
| end | |
| scores[date_iso] = { | |
| monkey: monkey_score, | |
| manager: manager_score, | |
| } | |
| # binding.pry | |
| } | |
| json["tasksScheduledCompleted"] = tasks_scheduled_completed | |
| json["scores"] = scores | |
| # sanity check | |
| if json && json["scores"] && json["tasksScheduledCompleted"] | |
| File.open(json_path, 'w'){|f| f.puts JSON.pretty_generate(json)} | |
| end | |
| puts json_path | |
| today_iso = Date.today.strftime("%Y-%m-%d") | |
| tasks_scheduled_today = json["tasksScheduledCompleted"][today_iso]["taskIdsScheduled"] | |
| puts "#{tasks_scheduled_today.length} tasks scheduled today" | |
| p scores[today_iso] | |
| # db_path = File.expand_path("~/Library/Containers/com.culturedcode.ThingsMac/Data/Library/Application\ Support/Cultured\ Code/Things/Things.sqlite3") | |
| # db = SQLite3::Database.new(db_path) | |
| # | |
| # db.execute( "select * from TMTask" ) do |row| | |
| # p row | |
| # binding.pry | |
| # end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment