kasl/db/
tags.rs

1//! Tags and the task_tags junction table linking them to tasks.
2//!
3//! ```rust,no_run
4//! # fn main() -> anyhow::Result<()> {
5//! use kasl::db::tags::{Tags, Tag};
6//!
7//! let mut tags = Tags::new()?;
8//! let urgent_tag = Tag::new("urgent".to_string(), Some("red".to_string()));
9//! let tag_id = tags.create(&urgent_tag)?;
10//! let task_id = 1;
11//! tags.add_tag_to_task(task_id, tag_id)?;
12//! # Ok(())
13//! # }
14//! ```
15
16use crate::db::db::Db;
17use crate::libs::messages::Message;
18use crate::msg_error_anyhow;
19use anyhow::Result;
20use rusqlite::{Connection, OptionalExtension, params};
21use serde::{Deserialize, Serialize};
22use std::sync::atomic::{AtomicUsize, Ordering};
23
24const SCHEMA_TAGS: &str = "CREATE TABLE IF NOT EXISTS tags (
25    id INTEGER PRIMARY KEY,
26    name TEXT NOT NULL UNIQUE,
27    color TEXT,
28    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
29)";
30
31// Composite primary key plus ON DELETE CASCADE on both sides: removing a
32// task or a tag cleans its links automatically.
33const SCHEMA_TASK_TAGS: &str = "CREATE TABLE IF NOT EXISTS task_tags (
34    task_id INTEGER NOT NULL,
35    tag_id INTEGER NOT NULL,
36    PRIMARY KEY (task_id, tag_id),
37    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
38    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
39)";
40
41const INSERT_TAG: &str = "INSERT INTO tags (name, color) VALUES (?1, ?2)";
42const UPDATE_TAG: &str = "UPDATE tags SET name = ?2, color = ?3 WHERE id = ?1";
43const DELETE_TAG: &str = "DELETE FROM tags WHERE id = ?1";
44const SELECT_ALL_TAGS: &str = "SELECT * FROM tags ORDER BY name";
45const SELECT_TAG_BY_NAME: &str = "SELECT * FROM tags WHERE name = ?1";
46const SELECT_TAG_BY_ID: &str = "SELECT * FROM tags WHERE id = ?1";
47const SELECT_TAGS_BY_TASK: &str = "
48    SELECT t.* FROM tags t
49    JOIN task_tags tt ON t.id = tt.tag_id
50    WHERE tt.task_id = ?1
51    ORDER BY t.name
52";
53const SELECT_TASKS_BY_TAG: &str = "SELECT task_id FROM task_tags WHERE tag_id = ?1";
54const INSERT_TASK_TAG: &str = "INSERT OR IGNORE INTO task_tags (task_id, tag_id) VALUES (?1, ?2)";
55const DELETE_TASK_TAG: &str = "DELETE FROM task_tags WHERE task_id = ?1 AND tag_id = ?2";
56const DELETE_ALL_TASK_TAGS: &str = "DELETE FROM task_tags WHERE task_id = ?1";
57
58/// A label attachable to tasks, with an optional display color.
59#[derive(Debug, Clone, Serialize, Deserialize)]
60pub struct Tag {
61    /// Database primary key; `None` until saved.
62    pub id: Option<i32>,
63
64    /// Unique, case-sensitive name.
65    pub name: String,
66
67    /// Display color (name or hex), if any.
68    pub color: Option<String>,
69
70    /// Set by the database on insert.
71    pub created_at: Option<String>,
72}
73
74impl Tag {
75    /// Builds an unsaved tag; id and timestamp are assigned on `create`.
76    ///
77    /// ```rust
78    /// use kasl::db::tags::Tag;
79    ///
80    /// let urgent_tag = Tag::new("urgent".to_string(), Some("red".to_string()));
81    /// let general_tag = Tag::new("general".to_string(), None);
82    /// ```
83    pub fn new(name: String, color: Option<String>) -> Self {
84        Self {
85            id: None,
86            name,
87            color,
88            created_at: None,
89        }
90    }
91}
92
93/// Tag table access, including task-tag links.
94pub struct Tags {
95    conn: Connection,
96}
97
98impl Tags {
99    /// Opens the database and ensures both tag tables exist
100    /// (migration v3 creates them officially).
101    ///
102    /// ```rust,no_run
103    /// # fn main() -> anyhow::Result<()> {
104    /// use kasl::db::tags::Tags;
105    ///
106    /// let mut tags = Tags::new()?;
107    /// # Ok(())
108    /// # }
109    /// ```
110    pub fn new() -> Result<Self> {
111        let db = Db::new()?;
112        db.conn.execute(SCHEMA_TAGS, [])?;
113        db.conn.execute(SCHEMA_TASK_TAGS, [])?;
114        Ok(Tags { conn: db.conn })
115    }
116
117    /// Inserts the tag and returns its assigned id; duplicate names are
118    /// rejected by the unique constraint.
119    ///
120    /// ```rust,no_run
121    /// # fn main() -> anyhow::Result<()> {
122    /// use kasl::db::tags::{Tags, Tag};
123    ///
124    /// let mut tags = Tags::new()?;
125    /// let tag = Tag::new("priority".to_string(), Some("orange".to_string()));
126    /// let tag_id = tags.create(&tag)?;
127    /// println!("Created tag with ID: {}", tag_id);
128    /// # Ok(())
129    /// # }
130    /// ```
131    pub fn create(&mut self, tag: &Tag) -> Result<i32> {
132        self.conn.execute(INSERT_TAG, params![tag.name, tag.color])?;
133        Ok(self.conn.last_insert_rowid() as i32)
134    }
135
136    /// Updates name and color by id; errors when the tag has no id or no
137    /// longer exists.
138    ///
139    /// ```rust,no_run
140    /// # use kasl::db::tags::Tags;
141    /// # fn main() -> anyhow::Result<()> {
142    /// let mut tags = Tags::new()?;
143    /// let tag_id = 1;
144    /// let mut tag = tags.get_by_id(tag_id)?.unwrap();
145    /// tag.name = "high-priority".to_string();
146    /// tag.color = Some("crimson".to_string());
147    /// tags.update(&tag)?;
148    /// # Ok(())
149    /// # }
150    /// ```
151    pub fn update(&mut self, tag: &Tag) -> Result<()> {
152        let id = tag.id.ok_or_else(|| msg_error_anyhow!(Message::TagNotFound(tag.name.to_string())))?;
153
154        let affected = self.conn.execute(UPDATE_TAG, params![id, tag.name, tag.color])?;
155
156        if affected == 0 {
157            return Err(msg_error_anyhow!(Message::TagNotFound(tag.name.to_string())));
158        }
159
160        Ok(())
161    }
162
163    /// Deletes the tag; its task links go with it via CASCADE.
164    ///
165    /// ```rust,no_run
166    /// # use kasl::db::tags::Tags;
167    /// # fn main() -> anyhow::Result<()> {
168    /// let mut tags = Tags::new()?;
169    /// let tag_id = 1;
170    /// tags.delete(tag_id)?;
171    /// # Ok(())
172    /// # }
173    /// ```
174    pub fn delete(&mut self, id: i32) -> Result<()> {
175        let affected = self.conn.execute(DELETE_TAG, params![id])?;
176        if affected == 0 {
177            return Err(msg_error_anyhow!(Message::TagNotFound(id.to_string())));
178        }
179        Ok(())
180    }
181
182    /// Returns every tag, sorted by name.
183    ///
184    /// ```rust,no_run
185    /// # use kasl::db::tags::Tags;
186    /// # fn main() -> anyhow::Result<()> {
187    /// let mut tags = Tags::new()?;
188    /// let all_tags = tags.get_all()?;
189    /// for tag in all_tags {
190    ///     println!("Tag: {} ({})", tag.name, tag.color.unwrap_or("no color".to_string()));
191    /// }
192    /// # Ok(())
193    /// # }
194    /// ```
195    pub fn get_all(&mut self) -> Result<Vec<Tag>> {
196        let mut stmt = self.conn.prepare(SELECT_ALL_TAGS)?;
197        let tag_iter = stmt.query_map([], |row| {
198            Ok(Tag {
199                id: row.get(0)?,
200                name: row.get(1)?,
201                color: row.get(2)?,
202                created_at: row.get(3)?,
203            })
204        })?;
205
206        let mut tags = Vec::new();
207        for tag in tag_iter {
208            tags.push(tag?);
209        }
210        Ok(tags)
211    }
212
213    /// Fetches one tag by exact (case-sensitive) name.
214    ///
215    /// ```rust,no_run
216    /// # use kasl::db::tags::Tags;
217    /// # fn main() -> anyhow::Result<()> {
218    /// let mut tags = Tags::new()?;
219    /// if let Some(tag) = tags.get_by_name("urgent")? {
220    ///     println!("Found tag: {} with color: {:?}", tag.name, tag.color);
221    /// } else {
222    ///     println!("Tag 'urgent' not found");
223    /// }
224    /// # Ok(())
225    /// # }
226    /// ```
227    pub fn get_by_name(&mut self, name: &str) -> Result<Option<Tag>> {
228        let tag = self
229            .conn
230            .query_row(SELECT_TAG_BY_NAME, params![name], |row| {
231                Ok(Tag {
232                    id: row.get(0)?,
233                    name: row.get(1)?,
234                    color: row.get(2)?,
235                    created_at: row.get(3)?,
236                })
237            })
238            .optional()?;
239        Ok(tag)
240    }
241
242    /// Fetches one tag by id.
243    ///
244    /// ```rust,no_run
245    /// # use kasl::db::tags::Tags;
246    /// # fn main() -> anyhow::Result<()> {
247    /// let mut tags = Tags::new()?;
248    /// if let Some(tag) = tags.get_by_id(42)? {
249    ///     println!("Tag ID 42: {}", tag.name);
250    /// }
251    /// # Ok(())
252    /// # }
253    /// ```
254    pub fn get_by_id(&mut self, id: i32) -> Result<Option<Tag>> {
255        let tag = self
256            .conn
257            .query_row(SELECT_TAG_BY_ID, params![id], |row| {
258                Ok(Tag {
259                    id: row.get(0)?,
260                    name: row.get(1)?,
261                    color: row.get(2)?,
262                    created_at: row.get(3)?,
263                })
264            })
265            .optional()?;
266        Ok(tag)
267    }
268
269    /// Returns the task's tags, sorted by name; empty when it has none.
270    ///
271    /// ```rust,no_run
272    /// # use kasl::db::tags::Tags;
273    /// # fn main() -> anyhow::Result<()> {
274    /// let mut tags = Tags::new()?;
275    /// let task_id = 1;
276    /// let task_tags = tags.get_tags_by_task(task_id)?;
277    /// for tag in task_tags {
278    ///     println!("Task has tag: {}", tag.name);
279    /// }
280    /// # Ok(())
281    /// # }
282    /// ```
283    pub fn get_tags_by_task(&mut self, task_id: i32) -> Result<Vec<Tag>> {
284        let mut stmt = self.conn.prepare(SELECT_TAGS_BY_TASK)?;
285        let tag_iter = stmt.query_map(params![task_id], |row| {
286            Ok(Tag {
287                id: row.get(0)?,
288                name: row.get(1)?,
289                color: row.get(2)?,
290                created_at: row.get(3)?,
291            })
292        })?;
293
294        let mut tags = Vec::new();
295        for tag in tag_iter {
296            tags.push(tag?);
297        }
298        Ok(tags)
299    }
300
301    /// Returns the ids of tasks carrying the tag.
302    ///
303    /// ```rust,no_run
304    /// # use kasl::db::tags::Tags;
305    /// # fn main() -> anyhow::Result<()> {
306    /// let mut tags = Tags::new()?;
307    /// let tag_id = 1;
308    /// let task_ids = tags.get_tasks_by_tag(tag_id)?;
309    /// println!("Tag is used by {} tasks", task_ids.len());
310    /// # Ok(())
311    /// # }
312    /// ```
313    pub fn get_tasks_by_tag(&mut self, tag_id: i32) -> Result<Vec<i32>> {
314        let mut stmt = self.conn.prepare(SELECT_TASKS_BY_TAG)?;
315        let task_iter = stmt.query_map(params![tag_id], |row| row.get(0))?;
316
317        let mut task_ids = Vec::new();
318        for task_id in task_iter {
319            task_ids.push(task_id?);
320        }
321        Ok(task_ids)
322    }
323
324    /// Links a tag to a task; idempotent thanks to `OR IGNORE`.
325    ///
326    /// ```rust,no_run
327    /// # use kasl::db::tags::Tags;
328    /// # fn main() -> anyhow::Result<()> {
329    /// let mut tags = Tags::new()?;
330    /// let (task_id, tag_id) = (1, 1);
331    /// tags.add_tag_to_task(task_id, tag_id)?;
332    /// # Ok(())
333    /// # }
334    /// ```
335    pub fn add_tag_to_task(&mut self, task_id: i32, tag_id: i32) -> Result<()> {
336        self.conn.execute(INSERT_TASK_TAG, params![task_id, tag_id])?;
337        Ok(())
338    }
339
340    /// Unlinks a tag from a task; a missing link is not an error.
341    ///
342    /// ```rust,no_run
343    /// # use kasl::db::tags::Tags;
344    /// # fn main() -> anyhow::Result<()> {
345    /// let mut tags = Tags::new()?;
346    /// let (task_id, tag_id) = (1, 1);
347    /// tags.remove_tag_from_task(task_id, tag_id)?;
348    /// # Ok(())
349    /// # }
350    /// ```
351    pub fn remove_tag_from_task(&mut self, task_id: i32, tag_id: i32) -> Result<()> {
352        self.conn.execute(DELETE_TASK_TAG, params![task_id, tag_id])?;
353        Ok(())
354    }
355
356    /// Removes every tag link from the task; returns how many went.
357    ///
358    /// ```rust,no_run
359    /// # use kasl::db::tags::Tags;
360    /// # fn main() -> anyhow::Result<()> {
361    /// let mut tags = Tags::new()?;
362    /// let task_id = 1;
363    /// let removed_count = tags.remove_all_tags_from_task(task_id)?;
364    /// println!("Removed {} tag associations", removed_count);
365    /// # Ok(())
366    /// # }
367    /// ```
368    pub fn remove_all_tags_from_task(&mut self, task_id: i32) -> Result<usize> {
369        let affected = self.conn.execute(DELETE_ALL_TASK_TAGS, params![task_id])?;
370        Ok(affected)
371    }
372
373    /// Replaces the task's tag set: clears existing links, then adds the
374    /// given ids. Not transactional - a failure after the clear leaves the
375    /// task untagged. Ids must exist; use [`Tags::get_or_create_tags`] when
376    /// unsure.
377    ///
378    /// ```rust,no_run
379    /// # use kasl::db::tags::Tags;
380    /// # fn main() -> anyhow::Result<()> {
381    /// let mut tags = Tags::new()?;
382    /// let task_id = 1;
383    ///
384    /// let new_tag_ids = vec![1, 3, 5]; // urgent, backend, review
385    /// tags.set_task_tags(task_id, &new_tag_ids)?;
386    ///
387    /// let current_tags = tags.get_tags_by_task(task_id)?;
388    /// assert_eq!(current_tags.len(), 3);
389    /// # Ok(())
390    /// # }
391    /// ```
392    pub fn set_task_tags(&mut self, task_id: i32, tag_ids: &[i32]) -> Result<()> {
393        self.remove_all_tags_from_task(task_id)?;
394
395        for tag_id in tag_ids {
396            self.add_tag_to_task(task_id, *tag_id)?;
397        }
398
399        Ok(())
400    }
401
402    /// Resolves names to tag ids, creating missing tags with a color from
403    /// the default rotation.
404    ///
405    /// ```rust,no_run
406    /// # use kasl::db::tags::Tags;
407    /// # fn main() -> anyhow::Result<()> {
408    /// let mut tags = Tags::new()?;
409    /// let tag_names = vec!["urgent".to_string(), "backend".to_string()];
410    /// let tag_ids = tags.get_or_create_tags(&tag_names)?;
411    /// # Ok(())
412    /// # }
413    /// ```
414    pub fn get_or_create_tags(&mut self, names: &[String]) -> Result<Vec<i32>> {
415        let mut tag_ids = Vec::new();
416
417        for name in names {
418            let tag = match self.get_by_name(name)? {
419                Some(existing_tag) => existing_tag,
420                None => {
421                    let tag = Tag::new(name.clone(), Some(Self::get_default_color()));
422                    let id = self.create(&tag)?;
423                    Tag {
424                        id: Some(id),
425                        name: name.clone(),
426                        color: tag.color,
427                        created_at: None,
428                    }
429                }
430            };
431
432            if let Some(id) = tag.id {
433                tag_ids.push(id);
434            }
435        }
436
437        Ok(tag_ids)
438    }
439
440    /// Next color from a fixed palette, so consecutively created tags look
441    /// distinct without anyone choosing.
442    fn get_default_color() -> String {
443        static COLORS: &[&str] = &["blue", "green", "yellow", "red", "purple", "cyan", "orange"];
444        static COLOR_INDEX: AtomicUsize = AtomicUsize::new(0);
445
446        let index = COLOR_INDEX.fetch_add(1, Ordering::Relaxed);
447        COLORS[index % COLORS.len()].to_string()
448    }
449}
kasl/db/tags.rs

kasl/db/
tags.rs
