kasl/db/tasks.rs
1//! Task table access: CRUD plus the filtered queries behind `TaskFilter`.
2//!
3//! ```rust,no_run
4//! # fn main() -> anyhow::Result<()> {
5//! use kasl::db::tasks::Tasks;
6//! use kasl::libs::task::Task;
7//!
8//! let mut tasks = Tasks::new()?;
9//! let task = Task::new("Review code", "Check PR #123", Some(75));
10//! tasks.insert(&task)?;
11//! # Ok(())
12//! # }
13//! ```
14
15use super::db::Db;
16use crate::libs::messages::Message;
17use crate::libs::task::{Task, TaskFilter};
18use crate::msg_error_anyhow;
19use anyhow::Result;
20use rusqlite::{Connection, Statement, ToSql, params};
21use std::vec;
22
23const SCHEMA_TASKS: &str = "CREATE TABLE IF NOT EXISTS tasks (
24 id INTEGER NOT NULL PRIMARY KEY,
25 task_id INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0,
26 timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
27 name TEXT NOT NULL,
28 comment TEXT,
29 completeness INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 100,
30 excluded_from_search BOOLEAN NOT NULL ON CONFLICT REPLACE DEFAULT FALSE
31);";
32
33const INSERT_TASK: &str = "INSERT INTO tasks (task_id, timestamp, name, comment, completeness, excluded_from_search, jira_key) VALUES
34 (?, datetime(CURRENT_TIMESTAMP, 'localtime'), ?, ?, ?, ?, ?) RETURNING id";
35const UPDATE_TASK_ID: &str = "UPDATE tasks SET task_id = ? WHERE id = ?";
36const SELECT_TASKS: &str = "SELECT * FROM tasks";
37const WHERE_DATE: &str = "WHERE date(timestamp) = date(?1)";
38const WHERE_ID_IN: &str = "WHERE id IN";
39
40// Incomplete = the latest completion state per task_id over the last 15 days,
41// still under 100%, and not already re-listed today.
42const WHERE_INCOMPLETE: &str = "WHERE
43 completeness < 100 AND
44 task_id NOT IN (SELECT task_id FROM tasks WHERE DATE(timestamp) = DATE('now')) AND
45 (task_id, completeness) IN (SELECT task_id, MAX(completeness) FROM tasks
46 WHERE DATE(timestamp) BETWEEN datetime(CURRENT_TIMESTAMP, 'localtime', '-15 day') AND datetime(CURRENT_TIMESTAMP, 'localtime', '-1 day')
47 GROUP BY task_id)
48 GROUP BY task_id";
49
50const WHERE_JIRA_KEY: &str = "WHERE jira_key = ?1";
51const WHERE_TAG: &str = "WHERE id IN (SELECT task_id FROM task_tags tt JOIN tags t ON tt.tag_id = t.id WHERE t.name = ?1)";
52const WHERE_TAGS: &str = "WHERE id IN (SELECT task_id FROM task_tags tt JOIN tags t ON tt.tag_id = t.id WHERE t.name IN";
53const DELETE_TASK: &str = "DELETE FROM tasks WHERE id = ?";
54const DELETE_TASKS_BY_IDS: &str = "DELETE FROM tasks WHERE id IN";
55const SELECT_COUNT_BY_ID: &str = "SELECT COUNT(*) FROM tasks WHERE id = ?";
56const UPDATE_TASK: &str = "UPDATE tasks SET name = ?, comment = ?, completeness = ? WHERE id = ?";
57
58/// Task table access; remembers the last inserted id for chaining.
59#[derive(Debug)]
60pub struct Tasks {
61 pub conn: Connection,
62
63 /// Id of the most recently inserted task, for `update_id`/`get` chaining.
64 pub id: Option<i32>,
65}
66
67impl Tasks {
68 /// Opens the database and ensures the tasks table exists.
69 ///
70 /// ```rust,no_run
71 /// # fn main() -> anyhow::Result<()> {
72 /// use kasl::db::tasks::Tasks;
73 ///
74 /// let mut tasks = Tasks::new()?;
75 /// # Ok(())
76 /// # }
77 /// ```
78 pub fn new() -> Result<Self> {
79 let db = Db::new()?;
80 db.conn.execute(SCHEMA_TASKS, [])?;
81 Ok(Self { conn: db.conn, id: None })
82 }
83
84 /// Inserts the task, storing the assigned id for chaining.
85 ///
86 /// ```rust,no_run
87 /// # fn main() -> anyhow::Result<()> {
88 /// use kasl::db::tasks::Tasks;
89 /// use kasl::libs::task::Task;
90 ///
91 /// let mut tasks = Tasks::new()?;
92 /// let task = Task::new("Code review", "Review PR #123", Some(50));
93 /// tasks.insert(&task)?
94 /// .update_id()?; // Method chaining
95 /// # Ok(())
96 /// # }
97 /// ```
98 pub fn insert(&mut self, task: &Task) -> Result<&mut Self> {
99 self.id = Some(self.conn.query_row(
100 INSERT_TASK,
101 params![
102 task.task_id,
103 task.name,
104 task.comment,
105 task.completeness,
106 task.excluded_from_search,
107 task.jira_key
108 ],
109 |row| row.get(0),
110 )?);
111
112 Ok(self)
113 }
114
115 /// Points the just-inserted task's `task_id` at its own id - the
116 /// convention for a standalone task that groups its own history.
117 ///
118 /// ```rust,no_run
119 /// # use kasl::db::tasks::Tasks;
120 /// use kasl::libs::task::Task;
121 ///
122 /// # fn main() -> anyhow::Result<()> {
123 /// let mut tasks = Tasks::new()?;
124 /// let subtask = Task::new("Subtask", "Part of larger task", Some(0));
125 /// tasks.insert(&subtask)?
126 /// .update_id()?;
127 /// # Ok(())
128 /// # }
129 /// ```
130 pub fn update_id(&mut self) -> Result<&mut Self> {
131 self.conn.execute(UPDATE_TASK_ID, params![self.id, self.id])?;
132 Ok(self)
133 }
134
135 /// Fetches the most recently inserted task.
136 ///
137 /// ```rust,no_run
138 /// # use kasl::db::tasks::Tasks;
139 /// use kasl::libs::task::Task;
140 ///
141 /// # fn main() -> anyhow::Result<()> {
142 /// let mut tasks = Tasks::new()?;
143 /// let task = Task::new("New task", "Description", Some(100));
144 /// let inserted_tasks = tasks.insert(&task)?
145 /// .get()?;
146 /// # Ok(())
147 /// # }
148 /// ```
149 pub fn get(&mut self) -> Result<Vec<Task>> {
150 let id = self.id.ok_or_else(|| msg_error_anyhow!(Message::NoIdSet))?;
151 self.fetch(TaskFilter::ByIds(vec![id]))
152 }
153
154 /// Runs the query for the given filter and attaches each task's tags.
155 ///
156 /// ```rust,no_run
157 /// # fn main() -> anyhow::Result<()> {
158 /// use kasl::db::tasks::Tasks;
159 /// use kasl::libs::task::TaskFilter;
160 /// use chrono::Local;
161 ///
162 /// let mut tasks = Tasks::new()?;
163 ///
164 /// let all_tasks = tasks.fetch(TaskFilter::All)?;
165 /// let today = tasks.fetch(TaskFilter::Date(Local::now().date_naive()))?;
166 /// let urgent = tasks.fetch(TaskFilter::ByTag("urgent".to_string()))?;
167 /// let incomplete = tasks.fetch(TaskFilter::Incomplete)?;
168 /// # Ok(())
169 /// # }
170 /// ```
171 pub fn fetch(&mut self, filter: TaskFilter) -> Result<Vec<Task>> {
172 let (mut stmt, params): (Statement, Vec<Box<dyn ToSql>>) = match filter {
173 TaskFilter::All => (self.conn.prepare(SELECT_TASKS)?, vec![]),
174 TaskFilter::Date(date) => (self.conn.prepare(&format!("{} {}", SELECT_TASKS, WHERE_DATE))?, vec![Box::new(date)]),
175 TaskFilter::Incomplete => (self.conn.prepare(&format!("{} {}", SELECT_TASKS, WHERE_INCOMPLETE))?, vec![]),
176 TaskFilter::ByIds(ids) => {
177 let ids_params: Vec<Box<dyn ToSql>> = ids.clone().into_iter().map(|id| Box::new(id) as Box<dyn ToSql>).collect();
178 (self.conn.prepare(&Self::query_by_ids(&ids))?, ids_params)
179 }
180 TaskFilter::ByTag(tag_name) => (self.conn.prepare(&format!("{} {}", SELECT_TASKS, WHERE_TAG))?, vec![Box::new(tag_name)]),
181 TaskFilter::ByJiraKey(key) => (self.conn.prepare(&format!("{} {}", SELECT_TASKS, WHERE_JIRA_KEY))?, vec![Box::new(key)]),
182 TaskFilter::ByTags(tag_names) => {
183 let placeholders = vec!["?"; tag_names.len()].join(", ");
184 let query = format!("{} {} ({}))", SELECT_TASKS, WHERE_TAGS, placeholders);
185 let params: Vec<Box<dyn ToSql>> = tag_names.into_iter().map(|name| Box::new(name) as Box<dyn ToSql>).collect();
186 (self.conn.prepare(&query)?, params)
187 }
188 };
189
190 let params_refs: Vec<&dyn ToSql> = params.iter().map(|p| &**p).collect();
191 let task_iter = stmt.query_map(¶ms_refs[..], |row| {
192 Ok(Task {
193 id: row.get(0)?,
194 task_id: row.get(1)?,
195 timestamp: row.get(2)?,
196 name: row.get(3)?,
197 comment: row.get(4)?,
198 completeness: row.get(5)?,
199 excluded_from_search: row.get(6)?,
200 // By name, not position: `SELECT *` puts this after
201 // `deleted_at`, and the next migration would shift it again.
202 jira_key: row.get("jira_key")?,
203 tags: vec![], // Tags will be populated in the next step
204 })
205 })?;
206
207 let mut tasks = Vec::new();
208 for task_result in task_iter {
209 tasks.push(task_result?);
210 }
211
212 // Enrich tasks with tag information
213 let mut tags_db = crate::db::tags::Tags::new()?;
214 for task in &mut tasks {
215 if let Some(task_id) = task.id {
216 task.tags = tags_db.get_tags_by_task(task_id)?;
217 }
218 }
219
220 Ok(tasks)
221 }
222
223 /// Builds `SELECT ... WHERE id IN (?, ?, ...)` with one placeholder per id.
224 fn query_by_ids(ids: &[i32]) -> String {
225 format!("{} {} ({})", SELECT_TASKS, WHERE_ID_IN, vec!["?"; ids.len()].join(", "))
226 }
227
228 /// Deletes one task; returns the number of rows removed.
229 ///
230 /// ```rust,no_run
231 /// # use kasl::db::tasks::Tasks;
232 /// # fn main() -> anyhow::Result<()> {
233 /// let mut tasks = Tasks::new()?;
234 /// let task_id = 1;
235 /// let deleted_count = tasks.delete(task_id)?;
236 /// if deleted_count > 0 {
237 /// println!("Task deleted successfully");
238 /// }
239 /// # Ok(())
240 /// # }
241 /// ```
242 pub fn delete(&mut self, id: i32) -> Result<usize> {
243 let affected = self.conn.execute(DELETE_TASK, params![id])?;
244 Ok(affected)
245 }
246
247 /// Deletes several tasks in one statement; unknown ids are simply not
248 /// counted, and an empty slice is a no-op.
249 ///
250 /// ```rust,no_run
251 /// # use kasl::db::tasks::Tasks;
252 /// # fn main() -> anyhow::Result<()> {
253 /// let mut tasks = Tasks::new()?;
254 /// let ids_to_delete = vec![101, 102, 103];
255 /// let deleted_count = tasks.delete_many(&ids_to_delete)?;
256 /// println!("Deleted {} tasks", deleted_count);
257 /// # Ok(())
258 /// # }
259 /// ```
260 pub fn delete_many(&mut self, ids: &[i32]) -> Result<usize> {
261 if ids.is_empty() {
262 return Ok(0);
263 }
264
265 let placeholders = vec!["?"; ids.len()].join(", ");
266 let query = format!("{} ({})", DELETE_TASKS_BY_IDS, placeholders);
267
268 let params: Vec<Box<dyn ToSql>> = ids.iter().map(|id| Box::new(*id) as Box<dyn ToSql>).collect();
269 let params_refs: Vec<&dyn ToSql> = params.iter().map(|p| &**p).collect();
270
271 let affected = self.conn.execute(&query, ¶ms_refs[..])?;
272 Ok(affected)
273 }
274
275 /// True when a task with this id exists.
276 ///
277 /// ```rust,no_run
278 /// # use kasl::db::tasks::Tasks;
279 /// # fn main() -> anyhow::Result<()> {
280 /// let mut tasks = Tasks::new()?;
281 /// let task_id = 1;
282 /// if tasks.exists(task_id)? {
283 /// println!("Task exists and can be updated");
284 /// } else {
285 /// println!("Task not found");
286 /// }
287 /// # Ok(())
288 /// # }
289 /// ```
290 pub fn exists(&mut self, id: i32) -> Result<bool> {
291 let count: i32 = self.conn.query_row(SELECT_COUNT_BY_ID, params![id], |row| row.get(0))?;
292 Ok(count > 0)
293 }
294
295 /// Updates name, comment and completeness; identity fields and tag
296 /// links stay as they are. Errors when the task has no id or no longer
297 /// exists.
298 ///
299 /// ```rust,no_run
300 /// # use kasl::db::tasks::Tasks;
301 /// # fn main() -> anyhow::Result<()> {
302 /// let mut tasks = Tasks::new()?;
303 /// let task_id = 1;
304 /// let mut task = tasks.get_by_id(task_id)?.unwrap();
305 /// task.name = "Updated task name".to_string();
306 /// task.completeness = Some(75);
307 /// tasks.update(&task)?;
308 /// # Ok(())
309 /// # }
310 /// ```
311 pub fn update(&mut self, task: &Task) -> Result<()> {
312 let id = task.id.ok_or_else(|| msg_error_anyhow!(Message::NoIdSet))?;
313
314 let affected = self.conn.execute(UPDATE_TASK, params![task.name, task.comment, task.completeness, id])?;
315
316 if affected == 0 {
317 return Err(msg_error_anyhow!(Message::TaskUpdateFailed));
318 }
319
320 Ok(())
321 }
322
323 /// Fetches one task by id, tags included.
324 ///
325 /// ```rust,no_run
326 /// # use kasl::db::tasks::Tasks;
327 /// # fn main() -> anyhow::Result<()> {
328 /// let mut tasks = Tasks::new()?;
329 /// if let Some(task) = tasks.get_by_id(42)? {
330 /// println!("Found task: {}", task.name);
331 /// } else {
332 /// println!("Task with ID 42 not found");
333 /// }
334 /// # Ok(())
335 /// # }
336 /// ```
337 pub fn get_by_id(&mut self, id: i32) -> Result<Option<Task>> {
338 let mut tasks = self.fetch(TaskFilter::ByIds(vec![id]))?;
339 Ok(tasks.pop())
340 }
341}