Class: Pylonite::Database

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

Constant Summary collapse

BOARDS = 
%w[backlog todo in_progress done archived].freeze
DEFAULT_DB_DIR = 
File.expand_path("~/.pylonite/dbs")

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(project_path = Dir.pwd) ⇒ Database 



20
21
22
23
24
25
26
27
28
 
# File 'lib/pylonite/database.rb', line 20

def initialize(project_path = Dir.pwd)
  @db_path = self.class.db_path_for(project_path)
  FileUtils.mkdir_p(File.dirname(@db_path))
  @db = SQLite3::Database.new(@db_path)
  @db.results_as_hash = true
  @db.execute("PRAGMA journal_mode=WAL")
  @db.execute("PRAGMA foreign_keys=ON")
  migrate!
end

Instance Attribute Details

#db ⇒ Object (readonly)

Returns the value of attribute db.



18
19
20
 
# File 'lib/pylonite/database.rb', line 18

def db
  @db
end

#db_path ⇒ Object (readonly)

Returns the value of attribute db_path.



18
19
20
 
# File 'lib/pylonite/database.rb', line 18

def db_path
  @db_path
end

Class Method Details

.appropriate(new_path, old_db_path) ⇒ Object 



40
41
42
43
44
45
46
47
48
49
50
 
# File 'lib/pylonite/database.rb', line 40

def self.appropriate(new_path, old_db_path)
  raise "Database not found: #{old_db_path}" unless File.exist?(old_db_path)

  new_db_path = db_path_for(new_path)
  if File.expand_path(old_db_path) == File.expand_path(new_db_path)
    return new_db_path
  end
  FileUtils.mkdir_p(File.dirname(new_db_path))
  FileUtils.mv(old_db_path, new_db_path)
  new_db_path
end

.db_dir ⇒ Object 



10
11
12
 
# File 'lib/pylonite/database.rb', line 10

def self.db_dir
  @db_dir || DEFAULT_DB_DIR
end

.db_dir=(dir) ⇒ Object 



14
15
16
 
# File 'lib/pylonite/database.rb', line 14

def self.db_dir=(dir)
  @db_dir = dir
end

.db_name_for(project_path) ⇒ Object 



30
31
32
33
34
 
# File 'lib/pylonite/database.rb', line 30

def self.db_name_for(project_path)
  name = File.basename(project_path)
  hash = Digest::SHA256.hexdigest(project_path)[0, 8]
  "#{name}_#{hash}"
end

.db_path_for(project_path) ⇒ Object 



36
37
38
 
# File 'lib/pylonite/database.rb', line 36

def self.db_path_for(project_path)
  File.join(db_dir, "#{db_name_for(project_path)}.sqlite3")
end

Instance Method Details

#activity_log ⇒ Object 



242
243
244
245
246
247
248
249
 
# File 'lib/pylonite/database.rb', line 242

def activity_log
  @db.execute("    SELECT h.created_at, h.actor, h.action, h.detail, h.task_id, t.title\n    FROM task_history h\n    JOIN tasks t ON t.id = h.task_id\n    ORDER BY h.created_at DESC, h.id DESC\n  SQL\nend\n")

#add_blocker(task_id, blocker_id, actor: nil) ⇒ Object

--- Dependencies ---



170
171
172
173
174
175
176
177
178
179
180
181
 
# File 'lib/pylonite/database.rb', line 170

def add_blocker(task_id, blocker_id, actor: nil)
  actor ||= current_user
  raise "Task cannot block itself" if task_id == blocker_id
  raise "Task ##{task_id} not found" unless get_task_raw(task_id)
  raise "Task ##{blocker_id} not found" unless get_task_raw(blocker_id)

  @db.execute(
    "INSERT OR IGNORE INTO task_dependencies (task_id, depends_on_id, dependency_type) VALUES (?, ?, 'blocks')",
    [task_id, blocker_id]
  )
  record_history(task_id, actor, "blocker_added", "Added blocker: task ##{blocker_id}")
end

#add_comment(task_id, text, author: nil) ⇒ Object

--- Comments ---



149
150
151
152
153
154
155
156
157
158
159
160
161
162
 
# File 'lib/pylonite/database.rb', line 149

def add_comment(task_id, text, author: nil)
  author ||= current_user
  task = get_task(task_id)
  raise "Task ##{task_id} not found" unless task

  now = Time.now.utc.iso8601
  @db.execute(
    "INSERT INTO comments (task_id, author, text, created_at) VALUES (?, ?, ?, ?)",
    [task_id, author, text, now]
  )
  @db.execute("UPDATE tasks SET updated_at = ? WHERE id = ?", [now, task_id])
  record_history(task_id, author, "commented", "Added a comment")
  @db.last_insert_row_id
end

#add_subtask(parent_id, title, author: nil) ⇒ Object

--- Subtasks ---



208
209
210
211
212
213
214
215
216
217
218
219
220
 
# File 'lib/pylonite/database.rb', line 208

def add_subtask(parent_id, title, author: nil)
  author ||= current_user
  raise "Task ##{parent_id} not found" unless get_task_raw(parent_id)

  task_id = add_task(title, author: author)
  @db.execute(
    "INSERT INTO task_dependencies (task_id, depends_on_id, dependency_type) VALUES (?, ?, 'subtask')",
    [task_id, parent_id]
  )
  record_history(task_id, author, "subtask_created", "Created as subtask of ##{parent_id}")
  record_history(parent_id, author, "subtask_added", "Added subtask ##{task_id}")
  task_id
end

#add_task(title, author: nil, board: "backlog", assignee: nil, description: nil) ⇒ Object

--- Tasks ---



54
55
56
57
58
59
60
61
62
63
64
 
# File 'lib/pylonite/database.rb', line 54

def add_task(title, author: nil, board: "backlog", assignee: nil, description: nil)
  author ||= current_user
  now = Time.now.utc.iso8601
  @db.execute(
    "INSERT INTO tasks (title, description, board, author, assignee, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
    [title, description, board, author, assignee, now, now]
  )
  task_id = @db.last_insert_row_id
  record_history(task_id, author, "created", "Created task in #{board}")
  task_id
end

#archive_task(task_id, actor: nil) ⇒ Object 



102
103
104
 
# File 'lib/pylonite/database.rb', line 102

def archive_task(task_id, actor: nil)
  move_task(task_id, "archived", actor: actor)
end

#assign_task(task_id, assignee, actor: nil) ⇒ Object 



106
107
108
109
110
111
112
113
114
 
# File 'lib/pylonite/database.rb', line 106

def assign_task(task_id, assignee, actor: nil)
  actor ||= current_user
  task = get_task(task_id)
  raise "Task ##{task_id} not found" unless task

  now = Time.now.utc.iso8601
  @db.execute("UPDATE tasks SET assignee = ?, updated_at = ? WHERE id = ?", [assignee, now, task_id])
  record_history(task_id, actor, "assigned", "Assigned to #{assignee}")
end

#close ⇒ Object 



251
252
253
 
# File 'lib/pylonite/database.rb', line 251

def close
  @db.close
end

#get_blocked_by_this(task_id) ⇒ Object 



199
200
201
202
203
204
 
# File 'lib/pylonite/database.rb', line 199

def get_blocked_by_this(task_id)
  @db.execute(
    "SELECT t.* FROM tasks t JOIN task_dependencies d ON d.task_id = t.id WHERE d.depends_on_id = ? AND d.dependency_type = 'blocks'",
    [task_id]
  )
end

#get_blockers(task_id) ⇒ Object 



192
193
194
195
196
197
 
# File 'lib/pylonite/database.rb', line 192

def get_blockers(task_id)
  @db.execute(
    "SELECT t.* FROM tasks t JOIN task_dependencies d ON d.depends_on_id = t.id WHERE d.task_id = ? AND d.dependency_type = 'blocks'",
    [task_id]
  )
end

#get_comments(task_id) ⇒ Object 



164
165
166
 
# File 'lib/pylonite/database.rb', line 164

def get_comments(task_id)
  @db.execute("SELECT * FROM comments WHERE task_id = ? ORDER BY created_at ASC", [task_id])
end

#get_history(task_id) ⇒ Object

--- History ---



238
239
240
 
# File 'lib/pylonite/database.rb', line 238

def get_history(task_id)
  @db.execute("SELECT * FROM task_history WHERE task_id = ? ORDER BY created_at ASC", [task_id])
end

#get_parent(task_id) ⇒ Object 



229
230
231
232
233
234
 
# File 'lib/pylonite/database.rb', line 229

def get_parent(task_id)
  @db.get_first_row(
    "SELECT t.* FROM tasks t JOIN task_dependencies d ON d.depends_on_id = t.id WHERE d.task_id = ? AND d.dependency_type = 'subtask'",
    [task_id]
  )
end

#get_subtasks(task_id) ⇒ Object 



222
223
224
225
226
227
 
# File 'lib/pylonite/database.rb', line 222

def get_subtasks(task_id)
  @db.execute(
    "SELECT t.* FROM tasks t JOIN task_dependencies d ON d.task_id = t.id WHERE d.depends_on_id = ? AND d.dependency_type = 'subtask'",
    [task_id]
  )
end

#get_task(task_id) ⇒ Object 



66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/pylonite/database.rb', line 66

def get_task(task_id)
  task = @db.get_first_row("SELECT * FROM tasks WHERE id = ?", [task_id])
  return nil unless task

  task["comments"] = get_comments(task_id)
  task["history"] = get_history(task_id)
  task["blockers"] = get_blockers(task_id)
  task["blocked_by_this"] = get_blocked_by_this(task_id)
  task["subtasks"] = get_subtasks(task_id)
  task["parent"] = get_parent(task_id)
  task
end

#list_tasks(board: nil, include_archived: false) ⇒ Object 



79
80
81
82
83
84
85
86
87
 
# File 'lib/pylonite/database.rb', line 79

def list_tasks(board: nil, include_archived: false)
  if board
    @db.execute("SELECT * FROM tasks WHERE board = ? ORDER BY updated_at DESC", [board])
  elsif include_archived
    @db.execute("SELECT * FROM tasks ORDER BY board, updated_at DESC")
  else
    @db.execute("SELECT * FROM tasks WHERE board != 'archived' ORDER BY board, updated_at DESC")
  end
end

#move_task(task_id, new_board, actor: nil) ⇒ Object 



89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/pylonite/database.rb', line 89

def move_task(task_id, new_board, actor: nil)
  actor ||= current_user
  raise "Invalid board: #{new_board}. Valid boards: #{BOARDS.join(', ')}" unless BOARDS.include?(new_board)

  task = get_task(task_id)
  raise "Task ##{task_id} not found" unless task

  old_board = task["board"]
  now = Time.now.utc.iso8601
  @db.execute("UPDATE tasks SET board = ?, updated_at = ? WHERE id = ?", [new_board, now, task_id])
  record_history(task_id, actor, "moved", "Moved from #{old_board} to #{new_board}")
end

#remove_blocker(task_id, blocker_id, actor: nil) ⇒ Object 



183
184
185
186
187
188
189
190
 
# File 'lib/pylonite/database.rb', line 183

def remove_blocker(task_id, blocker_id, actor: nil)
  actor ||= current_user
  @db.execute(
    "DELETE FROM task_dependencies WHERE task_id = ? AND depends_on_id = ? AND dependency_type = 'blocks'",
    [task_id, blocker_id]
  )
  record_history(task_id, actor, "blocker_removed", "Removed blocker: task ##{blocker_id}")
end

#schema_version ⇒ Object 



255
256
257
 
# File 'lib/pylonite/database.rb', line 255

def schema_version
  @db.get_first_value("PRAGMA user_version")
end

#search_tasks(query) ⇒ Object 



140
141
142
143
144
145
 
# File 'lib/pylonite/database.rb', line 140

def search_tasks(query)
  @db.execute(
    "SELECT * FROM tasks WHERE title LIKE ? OR description LIKE ? ORDER BY updated_at DESC",
    ["%#{query}%", "%#{query}%"]
  )
end

#update_task(task_id, title: nil, description: nil, actor: nil) ⇒ Object 



116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
 
# File 'lib/pylonite/database.rb', line 116

def update_task(task_id, title: nil, description: nil, actor: nil)
  actor ||= current_user
  task = get_task(task_id)
  raise "Task ##{task_id} not found" unless task

  updates = []
  params = []
  if title
    updates << "title = ?"
    params << title
  end
  if description
    updates << "description = ?"
    params << description
  end
  return if updates.empty?

  updates << "updated_at = ?"
  params << Time.now.utc.iso8601
  params << task_id
  @db.execute("UPDATE tasks SET #{updates.join(', ')} WHERE id = ?", params)
  record_history(task_id, actor, "updated", "Updated task details")
end