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

kasl/db/
tasks.rs
