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 `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 /// let saved = tasks.insert(&task)?.get()?; // Method chaining
94 /// # Ok(())
95 /// # }
96 /// ```
97 ///
98 /// A task that continues no chain starts its own: `task_id` becomes the
99 /// new row's id. Leaving it at the column default of 0 made every such
100 /// task one task to the queries that group by chain.
101 pub fn insert(&mut self, task: &Task) -> Result<&mut Self> {
102 let id: i32 = self.conn.query_row(
103 INSERT_TASK,
104 params![
105 task.task_id,
106 task.name,
107 task.comment,
108 task.completeness,
109 task.excluded_from_search,
110 task.jira_key
111 ],
112 |row| row.get(0),
113 )?;
114 if task.task_id.is_none_or(|chain| chain == 0) {
115 self.conn.execute(UPDATE_TASK_ID, params![id, id])?;
116 }
117 self.id = Some(id);
118
119 Ok(self)
120 }
121
122 /// Fetches the most recently inserted task.
123 ///
124 /// ```rust,no_run
125 /// # use kasl::db::tasks::Tasks;
126 /// use kasl::libs::task::Task;
127 ///
128 /// # fn main() -> anyhow::Result<()> {
129 /// let mut tasks = Tasks::new()?;
130 /// let task = Task::new("New task", "Description", Some(100));
131 /// let inserted_tasks = tasks.insert(&task)?
132 /// .get()?;
133 /// # Ok(())
134 /// # }
135 /// ```
136 pub fn get(&mut self) -> Result<Vec<Task>> {
137 let id = self.id.ok_or_else(|| msg_error_anyhow!(Message::NoIdSet))?;
138 self.fetch(TaskFilter::ByIds(vec![id]))
139 }
140
141 /// Runs the query for the given filter and attaches each task's tags.
142 ///
143 /// ```rust,no_run
144 /// # fn main() -> anyhow::Result<()> {
145 /// use kasl::db::tasks::Tasks;
146 /// use kasl::libs::task::TaskFilter;
147 /// use chrono::Local;
148 ///
149 /// let mut tasks = Tasks::new()?;
150 ///
151 /// let all_tasks = tasks.fetch(TaskFilter::All)?;
152 /// let today = tasks.fetch(TaskFilter::Date(Local::now().date_naive()))?;
153 /// let urgent = tasks.fetch(TaskFilter::ByTag("urgent".to_string()))?;
154 /// let incomplete = tasks.fetch(TaskFilter::Incomplete)?;
155 /// # Ok(())
156 /// # }
157 /// ```
158 pub fn fetch(&mut self, filter: TaskFilter) -> Result<Vec<Task>> {
159 let (mut stmt, params): (Statement, Vec<Box<dyn ToSql>>) = match filter {
160 TaskFilter::All => (self.conn.prepare(SELECT_TASKS)?, vec![]),
161 TaskFilter::Date(date) => (self.conn.prepare(&format!("{} {}", SELECT_TASKS, WHERE_DATE))?, vec![Box::new(date)]),
162 TaskFilter::Incomplete => (self.conn.prepare(&format!("{} {}", SELECT_TASKS, WHERE_INCOMPLETE))?, vec![]),
163 TaskFilter::ByIds(ids) => {
164 let ids_params: Vec<Box<dyn ToSql>> = ids.clone().into_iter().map(|id| Box::new(id) as Box<dyn ToSql>).collect();
165 (self.conn.prepare(&Self::query_by_ids(&ids))?, ids_params)
166 }
167 TaskFilter::ByTag(tag_name) => (self.conn.prepare(&format!("{} {}", SELECT_TASKS, WHERE_TAG))?, vec![Box::new(tag_name)]),
168 TaskFilter::ByJiraKey(key) => (self.conn.prepare(&format!("{} {}", SELECT_TASKS, WHERE_JIRA_KEY))?, vec![Box::new(key)]),
169 TaskFilter::ByTags(tag_names) => {
170 let placeholders = vec!["?"; tag_names.len()].join(", ");
171 let query = format!("{} {} ({}))", SELECT_TASKS, WHERE_TAGS, placeholders);
172 let params: Vec<Box<dyn ToSql>> = tag_names.into_iter().map(|name| Box::new(name) as Box<dyn ToSql>).collect();
173 (self.conn.prepare(&query)?, params)
174 }
175 };
176
177 let params_refs: Vec<&dyn ToSql> = params.iter().map(|p| &**p).collect();
178 let task_iter = stmt.query_map(¶ms_refs[..], |row| {
179 Ok(Task {
180 id: row.get(0)?,
181 task_id: row.get(1)?,
182 timestamp: row.get(2)?,
183 name: row.get(3)?,
184 comment: row.get(4)?,
185 completeness: row.get(5)?,
186 excluded_from_search: row.get(6)?,
187 // By name, not position: `SELECT *` puts this after
188 // `deleted_at`, and the next migration would shift it again.
189 jira_key: row.get("jira_key")?,
190 tags: vec![], // Tags will be populated in the next step
191 })
192 })?;
193
194 let mut tasks = Vec::new();
195 for task_result in task_iter {
196 tasks.push(task_result?);
197 }
198
199 // Enrich tasks with tag information
200 let mut tags_db = crate::db::tags::Tags::new()?;
201 for task in &mut tasks {
202 if let Some(task_id) = task.id {
203 task.tags = tags_db.get_tags_by_task(task_id)?;
204 }
205 }
206
207 Ok(tasks)
208 }
209
210 /// Builds `SELECT ... WHERE id IN (?, ?, ...)` with one placeholder per id.
211 fn query_by_ids(ids: &[i32]) -> String {
212 format!("{} {} ({})", SELECT_TASKS, WHERE_ID_IN, vec!["?"; ids.len()].join(", "))
213 }
214
215 /// Deletes one task; returns the number of rows removed.
216 ///
217 /// ```rust,no_run
218 /// # use kasl::db::tasks::Tasks;
219 /// # fn main() -> anyhow::Result<()> {
220 /// let mut tasks = Tasks::new()?;
221 /// let task_id = 1;
222 /// let deleted_count = tasks.delete(task_id)?;
223 /// if deleted_count > 0 {
224 /// println!("Task deleted successfully");
225 /// }
226 /// # Ok(())
227 /// # }
228 /// ```
229 pub fn delete(&mut self, id: i32) -> Result<usize> {
230 let affected = self.conn.execute(DELETE_TASK, params![id])?;
231 Ok(affected)
232 }
233
234 /// Deletes several tasks in one statement; unknown ids are simply not
235 /// counted, and an empty slice is a no-op.
236 ///
237 /// ```rust,no_run
238 /// # use kasl::db::tasks::Tasks;
239 /// # fn main() -> anyhow::Result<()> {
240 /// let mut tasks = Tasks::new()?;
241 /// let ids_to_delete = vec![101, 102, 103];
242 /// let deleted_count = tasks.delete_many(&ids_to_delete)?;
243 /// println!("Deleted {} tasks", deleted_count);
244 /// # Ok(())
245 /// # }
246 /// ```
247 pub fn delete_many(&mut self, ids: &[i32]) -> Result<usize> {
248 if ids.is_empty() {
249 return Ok(0);
250 }
251
252 let placeholders = vec!["?"; ids.len()].join(", ");
253 let query = format!("{} ({})", DELETE_TASKS_BY_IDS, placeholders);
254
255 let params: Vec<Box<dyn ToSql>> = ids.iter().map(|id| Box::new(*id) as Box<dyn ToSql>).collect();
256 let params_refs: Vec<&dyn ToSql> = params.iter().map(|p| &**p).collect();
257
258 let affected = self.conn.execute(&query, ¶ms_refs[..])?;
259 Ok(affected)
260 }
261
262 /// True when a task with this id exists.
263 ///
264 /// ```rust,no_run
265 /// # use kasl::db::tasks::Tasks;
266 /// # fn main() -> anyhow::Result<()> {
267 /// let mut tasks = Tasks::new()?;
268 /// let task_id = 1;
269 /// if tasks.exists(task_id)? {
270 /// println!("Task exists and can be updated");
271 /// } else {
272 /// println!("Task not found");
273 /// }
274 /// # Ok(())
275 /// # }
276 /// ```
277 pub fn exists(&mut self, id: i32) -> Result<bool> {
278 let count: i32 = self.conn.query_row(SELECT_COUNT_BY_ID, params![id], |row| row.get(0))?;
279 Ok(count > 0)
280 }
281
282 /// Updates name, comment and completeness; identity fields and tag
283 /// links stay as they are. Errors when the task has no id or no longer
284 /// exists.
285 ///
286 /// ```rust,no_run
287 /// # use kasl::db::tasks::Tasks;
288 /// # fn main() -> anyhow::Result<()> {
289 /// let mut tasks = Tasks::new()?;
290 /// let task_id = 1;
291 /// let mut task = tasks.get_by_id(task_id)?.unwrap();
292 /// task.name = "Updated task name".to_string();
293 /// task.completeness = Some(75);
294 /// tasks.update(&task)?;
295 /// # Ok(())
296 /// # }
297 /// ```
298 pub fn update(&mut self, task: &Task) -> Result<()> {
299 let id = task.id.ok_or_else(|| msg_error_anyhow!(Message::NoIdSet))?;
300
301 let affected = self.conn.execute(UPDATE_TASK, params![task.name, task.comment, task.completeness, id])?;
302
303 if affected == 0 {
304 return Err(msg_error_anyhow!(Message::TaskUpdateFailed));
305 }
306
307 Ok(())
308 }
309
310 /// Fetches one task by id, tags included.
311 ///
312 /// ```rust,no_run
313 /// # use kasl::db::tasks::Tasks;
314 /// # fn main() -> anyhow::Result<()> {
315 /// let mut tasks = Tasks::new()?;
316 /// if let Some(task) = tasks.get_by_id(42)? {
317 /// println!("Found task: {}", task.name);
318 /// } else {
319 /// println!("Task with ID 42 not found");
320 /// }
321 /// # Ok(())
322 /// # }
323 /// ```
324 pub fn get_by_id(&mut self, id: i32) -> Result<Option<Task>> {
325 let mut tasks = self.fetch(TaskFilter::ByIds(vec![id]))?;
326 Ok(tasks.pop())
327 }
328}