Class: ThingsMcp::Database

Inherits:
Object
  • Object
show all
Defined in:
lib/things_mcp/database.rb

Overview

Database access layer for Things 3 SQLite database

This class provides read-only access to the Things 3 database, enabling retrieval of todos, projects, areas, and tags. It handles dynamic database path resolution and provides formatted data structures.

Class Method Summary collapse

Class Method Details

.database_path ⇒ Object 



15
16
17
 
# File 'lib/things_mcp/database.rb', line 15

def database_path
  @database_path ||= find_database_path
end

.get_anytime ⇒ Object 



85
86
87
 
# File 'lib/things_mcp/database.rb', line 85

def get_anytime
  get_todos_by_start(2).reject { |t| t[:start_date] || t[:deadline] }
end

.get_areas(include_items: false) ⇒ Object

Get areas



152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
 
# File 'lib/things_mcp/database.rb', line 152

def get_areas(include_items: false)
  with_database do |db|
    query = "      SELECT uuid, title, tags\n      FROM TMArea\n      ORDER BY \"index\"\n    SQL\n\n    areas = db.execute(query).map { |row| format_area(row) }\n\n    if include_items\n      areas.each do |area|\n        # Get projects in this area\n        project_query = <<~SQL\n          SELECT uuid, title\n          FROM TMTask\n          WHERE type = 1\n            AND trashed = 0\n            AND area = '\#{area[:uuid]}'\n        SQL\n\n        area[:projects] = db.execute(project_query).map do |proj|\n          { uuid: proj[\"uuid\"], title: proj[\"title\"] }\n        end\n      end\n    end\n\n    areas\n  end\nend\n"

.get_inbox ⇒ Object

Get todos from specific lists



60
61
62
 
# File 'lib/things_mcp/database.rb', line 60

def get_inbox
  get_todos_by_start(0)
end

.get_logbook(period: "7d", limit: 50) ⇒ Object 



93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
# File 'lib/things_mcp/database.rb', line 93

def get_logbook(period: "7d", limit: 50)
  days = parse_period(period)
  cutoff = Date.today - days

  with_database do |db|
    query = "      SELECT \#{todo_columns}\n      FROM TMTask\n      WHERE type = 0\n        AND status IN (3, 2)\n        AND trashed = 0\n        AND stopDate >= julianday('\#{cutoff}')\n      ORDER BY stopDate DESC\n      LIMIT \#{limit}\n    SQL\n\n    db.execute(query).map { |row| format_todo(row) }\n  end\nend\n"

.get_projects(include_items: false) ⇒ Object

Get projects



128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
 
# File 'lib/things_mcp/database.rb', line 128

def get_projects(include_items: false)
  with_database do |db|
    query = "      SELECT uuid, title, notes, status, area, start, startDate, deadline,\n             creationDate, userModificationDate\n      FROM TMTask\n      WHERE type = 1\n        AND trashed = 0\n      ORDER BY userModificationDate DESC\n    SQL\n\n    projects = db.execute(query).map { |row| format_project(row) }\n\n    if include_items\n      projects.each do |project|\n        project[:todos] = get_todos(project_uuid: project[:uuid])\n      end\n    end\n\n    projects\n  end\nend\n"

.get_recent(period) ⇒ Object 



285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
 
# File 'lib/things_mcp/database.rb', line 285

def get_recent(period)
  days = parse_period(period)
  cutoff = Date.today - days

  with_database do |db|
    query = "      SELECT \#{todo_columns}\n      FROM TMTask\n      WHERE type = 0\n        AND trashed = 0\n        AND creationDate >= julianday('\#{cutoff}')\n      ORDER BY creationDate DESC\n    SQL\n\n    db.execute(query).map { |row| format_todo(row) }\n  end\nend\n"

.get_someday ⇒ Object 



89
90
91
 
# File 'lib/things_mcp/database.rb', line 89

def get_someday
  get_todos_by_start(3)
end

.get_tagged_items(tag_title) ⇒ Object 



200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
 
# File 'lib/things_mcp/database.rb', line 200

def get_tagged_items(tag_title)
  with_database do |db|
    # Find tag UUID
    tag_result = db.execute("SELECT uuid FROM TMTag WHERE title = ?", [tag_title]).first
    return [] unless tag_result

    tag_uuid = tag_result["uuid"]

    query = "      SELECT \#{todo_columns}\n      FROM TMTask\n      WHERE type = 0\n        AND status = 0\n        AND trashed = 0\n        AND tags LIKE '%\#{tag_uuid}%'\n      ORDER BY userModificationDate DESC\n    SQL\n\n    db.execute(query).map { |row| format_todo(row) }\n  end\nend\n"

.get_tags(include_items: false) ⇒ Object

Get tags



184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
 
# File 'lib/things_mcp/database.rb', line 184

def get_tags(include_items: false)
  with_database do |db|
    query = "SELECT uuid, title FROM TMTag ORDER BY title"

    tags = db.execute(query).map { |row| format_tag(row) }

    if include_items
      tags.each do |tag|
        tag[:items] = get_tagged_items(tag[:title])
      end
    end

    tags
  end
end

.get_today ⇒ Object 



64
65
66
 
# File 'lib/things_mcp/database.rb', line 64

def get_today
  get_todos_by_start(1)
end

.get_todos(project_uuid: nil, include_items: true) ⇒ Object

Get all todos



37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
# File 'lib/things_mcp/database.rb', line 37

def get_todos(project_uuid: nil, include_items: true)
  with_database do |db|
    query = build_todo_query(project_uuid: project_uuid)
    results = db.execute(query)

    todos = results.map { |row| format_todo(row) }

    if include_items
      todos.each do |todo|
        todo[:checklist_items] = get_checklist_items(db, todo[:uuid])
      end
    end

    # Always fetch tags for todos
    todos.each do |todo|
      todo[:tags] = get_tags_for_task(db, todo[:uuid])
    end

    todos
  end
end

.get_trash ⇒ Object 



113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/things_mcp/database.rb', line 113

def get_trash
  with_database do |db|
    query = "      SELECT \#{todo_columns}\n      FROM TMTask\n      WHERE type = 0\n        AND trashed = 1\n      ORDER BY userModificationDate DESC\n    SQL\n\n    db.execute(query).map { |row| format_todo(row) }\n  end\nend\n"

.get_upcoming ⇒ Object 



68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
# File 'lib/things_mcp/database.rb', line 68

def get_upcoming
  with_database do |db|
    query = "      SELECT \#{todo_columns}\n      FROM TMTask\n      WHERE type = 0\n        AND status = 0\n        AND trashed = 0\n        AND start = 2\n        AND (startDate IS NOT NULL OR deadline IS NOT NULL)\n      ORDER BY COALESCE(startDate, deadline)\n    SQL\n\n    db.execute(query).map { |row| format_todo(row) }\n  end\nend\n"

.search_advanced(filters = {}) ⇒ Object 



238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
 
# File 'lib/things_mcp/database.rb', line 238

def search_advanced(filters = {})
  with_database do |db|
    conditions = ["type = 0", "trashed = 0"]

    # Status filter
    if filters[:status]
      status_map = { "incomplete" => 0, "completed" => 3, "canceled" => 2 }
      conditions << "status = #{status_map[filters[:status]]}"
    end

    # Date filters
    if filters[:start_date]
      conditions << "startDate >= julianday('#{filters[:start_date]}')"
    end

    if filters[:deadline]
      conditions << "deadline <= julianday('#{filters[:deadline]}')"
    end

    # Tag filter
    if filters[:tag]
      tag_result = db.execute("SELECT uuid FROM TMTag WHERE title = ?", [filters[:tag]]).first
      conditions << "tags LIKE '%#{tag_result["uuid"]}%'" if tag_result
    end

    # Area filter
    if filters[:area]
      conditions << "area = '#{filters[:area]}'"
    end

    # Type filter
    if filters[:type]
      type_map = { "to-do" => 0, "project" => 1, "heading" => 2 }
      conditions << "type = #{type_map[filters[:type]]}"
    end

    query = "      SELECT \#{todo_columns}\n      FROM TMTask\n      WHERE \#{conditions.join(\" AND \")}\n      ORDER BY userModificationDate DESC\n    SQL\n\n    db.execute(query).map { |row| format_todo(row) }\n  end\nend\n"

.search_todos(query) ⇒ Object

Search todos



223
224
225
226
227
228
229
230
231
232
233
234
235
236
 
# File 'lib/things_mcp/database.rb', line 223

def search_todos(query)
  with_database do |db|
    search_query = "      SELECT \#{todo_columns}\n      FROM TMTask\n      WHERE type = 0\n        AND trashed = 0\n        AND (title LIKE '%\#{query}%' OR notes LIKE '%\#{query}%')\n      ORDER BY userModificationDate DESC\n    SQL\n\n    db.execute(search_query).map { |row| format_todo(row) }\n  end\nend\n"

.things_app_available? ⇒ Boolean

Returns:

  • (Boolean) 


19
20
21
 
# File 'lib/things_mcp/database.rb', line 19

def things_app_available?
  system('pgrep -x "Things3" > /dev/null 2>&1')
end

.with_database(&block) ⇒ Object 



23
24
25
26
27
28
29
30
31
32
33
34
 
# File 'lib/things_mcp/database.rb', line 23

def with_database(&block)
  db_path = database_path
  unless db_path
    raise "Things database not found. Please ensure Things 3 is installed and has been launched at least once."
  end

  db = SQLite3::Database.new(db_path, readonly: true)
  db.results_as_hash = true
  yield db
ensure
  db&.close
end