Class: Timely::Database

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

Constant Summary collapse

SCHEMA_VERSION = 
1

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(db_path = TIMELY_DB) ⇒ Database

Returns a new instance of Database.



11
12
13
14
15
16
17
18
 
# File 'lib/timely/database.rb', line 11

def initialize(db_path = TIMELY_DB)
  @db_path = db_path
  @db = SQLite3::Database.new(@db_path)
  @db.results_as_hash = true
  @db.execute("PRAGMA journal_mode=WAL")
  @db.execute("PRAGMA busy_timeout=5000")
  setup_schema
end

Instance Attribute Details

#db ⇒ Object (readonly)

Returns the value of attribute db.



7
8
9
 
# File 'lib/timely/database.rb', line 7

def db
  @db
end

Instance Method Details

#close ⇒ Object 



354
355
356
 
# File 'lib/timely/database.rb', line 354

def close
  @db.close if @db
end

#create_default_calendar ⇒ Object 



125
126
127
128
129
130
131
132
133
134
 
# File 'lib/timely/database.rb', line 125

def create_default_calendar
  count = @db.get_first_value("SELECT COUNT(*) FROM calendars")
  return if count && count > 0

  now = Time.now.to_i
  @db.execute(
    "INSERT INTO calendars (name, source_type, color, enabled, created_at) VALUES (?, ?, ?, ?, ?)",
    ['Personal', 'local', 39, 1, now]
  )
end

#delete_calendar_with_events(id) ⇒ Object 



330
331
332
333
 
# File 'lib/timely/database.rb', line 330

def delete_calendar_with_events(id)
  @db.execute("DELETE FROM events WHERE calendar_id = ?", [id])
  @db.execute("DELETE FROM calendars WHERE id = ?", [id])
end

#delete_event(event_id) ⇒ Object 



216
217
218
 
# File 'lib/timely/database.rb', line 216

def delete_event(event_id)
  @db.execute("DELETE FROM events WHERE id = ?", [event_id])
end

#delete_event_by_external_id(calendar_id, external_id) ⇒ Object 



298
299
300
301
302
303
 
# File 'lib/timely/database.rb', line 298

def delete_event_by_external_id(calendar_id, external_id)
  @db.execute(
    "DELETE FROM events WHERE calendar_id = ? AND external_id = ?",
    [calendar_id, external_id]
  )
end

#event_duplicate?(title, start_time) ⇒ Boolean

Check if a matching event exists on ANY calendar (cross-source dedup) Matches on title + start_time (within 60s tolerance)

Returns:

  • (Boolean) 


282
283
284
285
286
287
288
289
 
# File 'lib/timely/database.rb', line 282

def event_duplicate?(title, start_time)
  return false unless title && start_time
  count = @db.get_first_value(
    "SELECT COUNT(*) FROM events WHERE title = ? AND start_time BETWEEN ? AND ?",
    [title, start_time.to_i - 60, start_time.to_i + 60]
  )
  count.to_i > 0
end

#event_exists?(calendar_id, external_id) ⇒ Boolean

Lookup helpers

Returns:

  • (Boolean) 


271
272
273
274
275
276
277
278
 
# File 'lib/timely/database.rb', line 271

def event_exists?(calendar_id, external_id)
  return false unless external_id
  count = @db.get_first_value(
    "SELECT COUNT(*) FROM events WHERE calendar_id = ? AND external_id = ?",
    [calendar_id, external_id]
  )
  count.to_i > 0
end

#execute(query, params = []) ⇒ Object

General operations



346
347
348
 
# File 'lib/timely/database.rb', line 346

def execute(query, params = [])
  @db.execute(query, params)
end

#find_event_by_external_id(calendar_id, external_id) ⇒ Object 



291
292
293
294
295
296
 
# File 'lib/timely/database.rb', line 291

def find_event_by_external_id(calendar_id, external_id)
  @db.execute(
    "SELECT * FROM events WHERE calendar_id = ? AND external_id = ? LIMIT 1",
    [calendar_id, external_id]
  ).first
end

#get_calendars(enabled_only = true) ⇒ Object

Calendar operations



222
223
224
225
 
# File 'lib/timely/database.rb', line 222

def get_calendars(enabled_only = true)
  query = enabled_only ? "SELECT * FROM calendars WHERE enabled = 1 ORDER BY id" : "SELECT * FROM calendars ORDER BY id"
  @db.execute(query)
end

#get_events_for_date(date) ⇒ Object 



155
156
157
158
159
160
 
# File 'lib/timely/database.rb', line 155

def get_events_for_date(date)
  # date is a Date object; get all events that overlap this day
  start_ts = Time.new(date.year, date.month, date.day, 0, 0, 0).to_i
  end_ts = start_ts + 86400
  get_events_in_range(start_ts, end_ts)
end

#get_events_in_range(start_time, end_time) ⇒ Object

Event operations



138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/timely/database.rb', line 138

def get_events_in_range(start_time, end_time)
  start_ts = start_time.is_a?(Time) ? start_time.to_i : start_time.to_i
  end_ts = end_time.is_a?(Time) ? end_time.to_i : end_time.to_i

  rows = @db.execute(
    "SELECT e.*, c.name as calendar_name, c.color as calendar_color
     FROM events e
     JOIN calendars c ON e.calendar_id = c.id
     WHERE c.enabled = 1
       AND e.start_time < ?
       AND (e.end_time > ? OR e.end_time IS NULL)
     ORDER BY e.start_time, e.title",
    [end_ts, start_ts]
  )
  rows.map { |row| normalize_event_row(row) }
end

#get_setting(key, default = nil) ⇒ Object

Settings operations



250
251
252
253
254
255
256
257
258
 
# File 'lib/timely/database.rb', line 250

def get_setting(key, default = nil)
  result = @db.get_first_value("SELECT value FROM settings WHERE key = ?", [key])
  return default unless result
  begin
    JSON.parse(result)
  rescue JSON::ParserError
    result
  end
end

#migrate ⇒ Object 



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

def migrate
  current_version = @db.get_first_value("SELECT MAX(version) FROM schema_version") || 0

  if current_version < SCHEMA_VERSION
    @db.transaction do
      @db.execute("INSERT INTO schema_version (version, applied_at) VALUES (?, ?)",
                 [SCHEMA_VERSION, Time.now.to_i])
    end
  end
end

#save_calendar(cal_data) ⇒ Object 



227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
 
# File 'lib/timely/database.rb', line 227

def save_calendar(cal_data)
  now = Time.now.to_i
  config_val = json_field(cal_data[:source_config])
  if cal_data[:id]
    @db.execute(
      "UPDATE calendars SET name=?, source_type=?, source_config=?, color=?, enabled=?, sync_token=?, last_synced_at=? WHERE id=?",
      [cal_data[:name], cal_data[:source_type], config_val,
       cal_data[:color], cal_data[:enabled] ? 1 : 0,
       cal_data[:sync_token], cal_data[:last_synced_at], cal_data[:id]]
    )
  else
    @db.execute(
      "INSERT INTO calendars (name, source_type, source_config, color, enabled, created_at) VALUES (?, ?, ?, ?, ?, ?)",
      [cal_data[:name], cal_data[:source_type] || 'local',
       config_val, cal_data[:color] || 39,
       cal_data[:enabled] ? 1 : 0, now]
    )
    @db.last_insert_row_id
  end
end

#save_event(event_data) ⇒ Object 



162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
 
# File 'lib/timely/database.rb', line 162

def save_event(event_data)
  now = Time.now.to_i
  attendees_val = json_field(event_data[:attendees])
  alarms_val = json_field(event_data[:alarms])
  metadata_val = json_field(event_data[:metadata])

  if event_data[:id]
    @db.execute(
      "UPDATE events SET calendar_id=?, external_id=?, title=?, description=?,
       location=?, start_time=?, end_time=?, all_day=?, timezone=?,
       recurrence_rule=?, series_master_id=?, status=?, organizer=?, attendees=?, my_status=?,
       alarms=?, metadata=?, updated_at=? WHERE id=?",
      [
        event_data[:calendar_id], event_data[:external_id],
        event_data[:title], event_data[:description],
        event_data[:location], event_data[:start_time], event_data[:end_time],
        event_data[:all_day] ? 1 : 0, event_data[:timezone],
        event_data[:recurrence_rule], event_data[:series_master_id],
        event_data[:status],
        event_data[:organizer],
        attendees_val,
        event_data[:my_status],
        alarms_val,
        metadata_val,
        now, event_data[:id]
      ]
    )
    event_data[:id]
  else
    @db.execute(
      "INSERT INTO events (calendar_id, external_id, title, description,
       location, start_time, end_time, all_day, timezone,
       recurrence_rule, series_master_id, status, organizer, attendees, my_status,
       alarms, metadata, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
      [
        event_data[:calendar_id] || 1, event_data[:external_id],
        event_data[:title], event_data[:description],
        event_data[:location], event_data[:start_time], event_data[:end_time],
        event_data[:all_day] ? 1 : 0, event_data[:timezone],
        event_data[:recurrence_rule], event_data[:series_master_id],
        event_data[:status] || 'confirmed',
        event_data[:organizer],
        attendees_val,
        event_data[:my_status],
        alarms_val,
        metadata_val,
        now, now
      ]
    )
    @db.last_insert_row_id
  end
end

#set_setting(key, value) ⇒ Object 



260
261
262
263
264
265
266
267
 
# File 'lib/timely/database.rb', line 260

def set_setting(key, value)
  now = Time.now.to_i
  value_str = value.is_a?(String) ? value : value.to_json
  @db.execute(
    "INSERT OR REPLACE INTO settings (key, value, updated_at) VALUES (?, ?, ?)",
    [key, value_str, now]
  )
end

#setup_schema ⇒ Object 



20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/timely/database.rb', line 20

def setup_schema
  # Schema version tracking
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS schema_version (
      version INTEGER PRIMARY KEY,
      applied_at INTEGER NOT NULL
    )
  SQL

  # Calendars table
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS calendars (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      source_type TEXT NOT NULL,
      source_config TEXT,
      color INTEGER DEFAULT 39,
      enabled INTEGER DEFAULT 1,
      sync_token TEXT,
      last_synced_at INTEGER,
      created_at INTEGER NOT NULL
    )
  SQL

  @db.execute "CREATE INDEX IF NOT EXISTS idx_calendars_enabled ON calendars(enabled)"

  # Events table
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS events (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      calendar_id INTEGER NOT NULL,
      external_id TEXT,
      title TEXT NOT NULL,
      description TEXT,
      location TEXT,
      start_time INTEGER NOT NULL,
      end_time INTEGER,
      all_day INTEGER DEFAULT 0,
      timezone TEXT,
      recurrence_rule TEXT,
      series_master_id INTEGER,
      status TEXT DEFAULT 'confirmed',
      organizer TEXT,
      attendees TEXT,
      my_status TEXT,
      alarms TEXT,
      metadata TEXT,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL,
      FOREIGN KEY(calendar_id) REFERENCES calendars(id) ON DELETE CASCADE
    )
  SQL

  @db.execute "CREATE INDEX IF NOT EXISTS idx_events_calendar ON events(calendar_id)"
  @db.execute "CREATE INDEX IF NOT EXISTS idx_events_start ON events(start_time)"
  @db.execute "CREATE INDEX IF NOT EXISTS idx_events_end ON events(end_time)"
  @db.execute "CREATE INDEX IF NOT EXISTS idx_events_range ON events(start_time, end_time)"
  @db.execute "CREATE INDEX IF NOT EXISTS idx_events_external ON events(calendar_id, external_id)"

  # Settings table
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS settings (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL,
      updated_at INTEGER NOT NULL
    )
  SQL

  # Weather cache
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS weather_cache (
      date TEXT NOT NULL,
      hour INTEGER,
      data TEXT,
      fetched_at INTEGER NOT NULL,
      PRIMARY KEY(date, hour)
    )
  SQL

  # Astronomy cache
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS astronomy_cache (
      date TEXT PRIMARY KEY,
      moon_phase REAL,
      moon_phase_name TEXT,
      events TEXT,
      fetched_at INTEGER NOT NULL
    )
  SQL

  migrate
  create_default_calendar
end

#toggle_calendar_enabled(id) ⇒ Object 



326
327
328
 
# File 'lib/timely/database.rb', line 326

def toggle_calendar_enabled(id)
  @db.execute("UPDATE calendars SET enabled = CASE WHEN enabled = 1 THEN 0 ELSE 1 END WHERE id = ?", [id])
end

#transaction(&block) ⇒ Object 



350
351
352
 
# File 'lib/timely/database.rb', line 350

def transaction(&block)
  @db.transaction(&block)
end

#update_calendar_color(id, color) ⇒ Object

Calendar update helpers



322
323
324
 
# File 'lib/timely/database.rb', line 322

def update_calendar_color(id, color)
  @db.execute("UPDATE calendars SET color = ? WHERE id = ?", [color, id])
end

#update_calendar_sync(id, last_synced_at, source_config = nil) ⇒ Object 



335
336
337
338
339
340
341
342
 
# File 'lib/timely/database.rb', line 335

def update_calendar_sync(id, last_synced_at, source_config = nil)
  if source_config
    @db.execute("UPDATE calendars SET source_config = ?, last_synced_at = ? WHERE id = ?",
                [source_config.is_a?(String) ? source_config : JSON.generate(source_config), last_synced_at, id])
  else
    @db.execute("UPDATE calendars SET last_synced_at = ? WHERE id = ?", [last_synced_at, id])
  end
end

#upsert_synced_event(calendar_id, evt) ⇒ Object

Sync helpers



307
308
309
310
311
312
313
314
315
316
317
318
 
# File 'lib/timely/database.rb', line 307

def upsert_synced_event(calendar_id, evt)
  existing = find_event_by_external_id(calendar_id, evt[:external_id])
  if existing
    save_event(id: existing['id'], calendar_id: calendar_id, **evt)
    return :updated
  elsif event_duplicate?(evt[:title], evt[:start_time])
    return :skipped
  else
    save_event(calendar_id: calendar_id, **evt)
    return :new
  end
end