From 8f8fd10dc2b185ca0a8e8908229c4d4bbefd70b7 Mon Sep 17 00:00:00 2001
From: Mroik <mroik@delayed.space>
Date: Tue, 31 Mar 2026 21:32:23 +0200
Subject: Add scaffolding for DB interactions

The mailing list will need to save various data to operate for things
such as the subscriber's email.

Add database interaction machanism.

Signed-off-by: Mroik <mroik@delayed.space>
---
 src/database.rs | 80 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 src/main.rs     |  2 ++
 2 files changed, 82 insertions(+)
 create mode 100644 src/database.rs

(limited to 'src')

diff --git a/src/database.rs b/src/database.rs
new file mode 100644
index 0000000..a1d0a18
--- /dev/null
+++ b/src/database.rs
@@ -0,0 +1,80 @@
+use anyhow::Result;
+use rusqlite::{Connection, Transaction, config::DbConfig};
+
+const DB_NAME: &str = "database.sqlite";
+const DB_VERSION: i64 = 0;
+
+struct Database {
+    conn: Connection,
+}
+
+impl Database {
+    fn new() -> Result<Self> {
+        let mut init = false;
+        if !std::fs::exists(DB_NAME)? {
+            init = true;
+        }
+
+        let mut db = Database {
+            conn: Connection::open(DB_NAME)?,
+        };
+        if init {
+            db.initialize()?;
+        }
+
+        db.conn.db_config(DbConfig::SQLITE_DBCONFIG_ENABLE_FKEY)?;
+
+        Ok(db)
+    }
+
+    fn initialize(&mut self) -> Result<()> {
+        let tx = self.conn.transaction()?;
+        let mut q = "CREATE TABLE version (
+            version INTEGER NOT NULL,
+            UNIQUE (version))";
+        tx.execute(q, ())?;
+
+        q = "INSERT INTO version VALUES (?)";
+        tx.execute(q, &[&DB_VERSION])?;
+
+        q = "CREATE TABLE user (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            name TEXT,
+            email TEXT NOT NULL,
+            UNIQUE (email))";
+        tx.execute(q, ())?;
+
+        q = "CREATE TABLE list (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            name TEXT NOT NULL,
+            description TEXT,
+            UNIQUE (name))";
+        tx.execute(q, ())?;
+
+        q = "CREATE TABLE subscription (
+            user_id INTEGER REFERENCES user (id),
+            list_id INTEGER REFERENCES list (id),
+            UNIQUE (user_id, list_id))";
+        tx.execute(q, ())?;
+
+        tx.commit()?;
+        Ok(())
+    }
+
+    fn execute<Ex>(&mut self, q: Ex) -> Result<QueryResult<Ex::T>> where Ex: DBExecutable {
+        let tx = self.conn.transaction()?;
+        let ris = q.execute(&tx)?;
+        tx.commit()?;
+        Ok(ris)
+    }
+}
+
+pub trait DBExecutable {
+    type T;
+    fn execute(&self, tx: &Transaction) -> Result<QueryResult<Self::T>>;
+}
+
+pub enum QueryResult<T> {
+    Empty,
+    Vec(Vec<T>)
+}
diff --git a/src/main.rs b/src/main.rs
index e7a11a9..189b68b 100644
--- a/src/main.rs
+++ b/src/main.rs
@@ -1,3 +1,5 @@
+mod database;
+
 fn main() {
     println!("Hello, world!");
 }
-- 
cgit v1.3