2 files changed, 83 insertions, 6 deletions

diff --git a/lib/mihi/src/lib.rs b/lib/mihi/src/lib.rs
index 0c44335..c15e1ed 100644
--- a/lib/mihi/src/lib.rs
+++ b/lib/mihi/src/lib.rs
@@ -910,6 +910,56 @@ pub fn select_words_except(
     Ok(res)
 }
 
+/// Returns a vector with the names for tags that match the given `filter`, or
+/// all of them if None is passed as the filter.
+pub fn select_tag_names(filter: &Option<String>) -> Result<Vec<String>, String> {
+    let conn = get_connection()?;
+
+    let mut stmt;
+    let mut it = match filter {
+        Some(filter) => {
+            stmt = conn
+                .prepare("SELECT name FROM tags WHERE name LIKE ('%' || ?1 || '%') ORDER BY name")
+                .unwrap();
+            stmt.query([filter.as_str()]).unwrap()
+        }
+        None => {
+            stmt = conn.prepare("SELECT name FROM tags ORDER BY name").unwrap();
+            stmt.query([]).unwrap()
+        }
+    };
+
+    let mut res = vec![];
+    while let Some(row) = it.next().unwrap() {
+        res.push(row.get::<usize, String>(0).unwrap());
+    }
+    Ok(res)
+}
+
+/// Insert into the database the tag identified by the given name.
+pub fn create_tag(name: &str) -> Result<(), String> {
+    let conn = get_connection()?;
+
+    match conn.execute(
+        "INSERT INTO tags (name, updated_at, created_at) \
+         VALUES (?1, datetime('now'), datetime('now'))",
+        params![name.trim()],
+    ) {
+        Ok(_) => Ok(()),
+        Err(e) => Err(format!("could not create '{}': {}", name, e)),
+    }
+}
+
+/// Delete the tag from the database.
+pub fn delete_tag(name: &String) -> Result<(), String> {
+    let conn = get_connection()?;
+
+    match conn.execute("DELETE FROM tags WHERE name = ?1", params![name.trim()]) {
+        Ok(_) => Ok(()),
+        Err(e) => Err(format!("could not remove '{name}': {e}")),
+    }
+}
+
 pub fn update_success(word: &Word, success: isize, steps: isize) -> Result<(), String> {
     let conn = get_connection()?;
 
diff --git a/lib/mihi/src/migrate.rs b/lib/mihi/src/migrate.rs
index a561b9c..b1ff0c3 100644
--- a/lib/mihi/src/migrate.rs
+++ b/lib/mihi/src/migrate.rs
@@ -1,11 +1,5 @@
 use rusqlite::{Connection, Result};
 
-// TODO: not just the definition, but also the bootstrapping (into Git as there is nothing sensitive?).
-//  - conjugations
-//  - declensions
-//  - forms
-//  - word_relations
-
 /// Use the given `connection` in order to initialize the database.
 pub fn init(connection: Connection) -> Result<usize> {
     connection.execute(
@@ -71,5 +65,38 @@ CREATE UNIQUE INDEX IF NOT EXISTS "index_exercises_on_title" ON "exercises" ("ti
         (),
     )?;
 
+    connection.execute(
+        r#"
+CREATE TABLE IF NOT EXISTS "tags" (
+       "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+       "name" varchar NOT NULL,
+       "created_at" datetime(6) NOT NULL,
+       "updated_at" datetime(6) NOT NULL
+);
+"#,
+        (),
+    )?;
+
+    connection.execute(
+        r#"
+CREATE TABLE IF NOT EXISTS "tag_associations" (
+       "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+       "word_id" integer NOT NULL,
+       "tag_id" integer NOT NULL,
+       "created_at" datetime(6) NOT NULL,
+       "updated_at" datetime(6) NOT NULL
+);
+"#,
+        (),
+    )?;
+
+    connection.execute(
+        r#"
+CREATE UNIQUE INDEX IF NOT EXISTS "index_tags_on_name" ON "tags" ("name");
+CREATE UNIQUE INDEX IF NOT EXISTS "word_tag_unique" ON tag_associations (word_id, tag_id);
+"#,
+        (),
+    )?;
+
     Ok(0)
 }