Skip to content

Instantly share code, notes, and snippets.

@christiangenco
Last active July 13, 2023 14:47
Show Gist options
  • Save christiangenco/1098b8acc5a426815226e6a04f23c474 to your computer and use it in GitHub Desktop.
Save christiangenco/1098b8acc5a426815226e6a04f23c474 to your computer and use it in GitHub Desktop.
Convert Things database into csv and json
#!/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