From a005add5513182abb0b1230cf514c7a45b290e4b Mon Sep 17 00:00:00 2001
From: Daniel Hader <dhader96@gmail.com>
Date: Sun, 10 May 2026 10:15:56 -0500
Subject: database with problem model

---
 src/database/database.rs            | 91 +++++++++++++++++++++++++++++++++++++
 src/database/problem.rs             | 16 +++++++
 src/database/sql/delete_problem.sql |  1 +
 src/database/sql/fetch_problems.sql |  1 +
 src/database/sql/initialize.sql     | 21 +++++++++
 src/database/sql/insert_problem.sql |  1 +
 6 files changed, 131 insertions(+)
 create mode 100644 src/database/database.rs
 create mode 100644 src/database/problem.rs
 create mode 100644 src/database/sql/delete_problem.sql
 create mode 100644 src/database/sql/fetch_problems.sql
 create mode 100644 src/database/sql/initialize.sql
 create mode 100644 src/database/sql/insert_problem.sql

(limited to 'src/database')

diff --git a/src/database/database.rs b/src/database/database.rs
new file mode 100644
index 0000000..7da66f2
--- /dev/null
+++ b/src/database/database.rs
@@ -0,0 +1,91 @@
+use std::path::Path;
+use rusqlite::{Connection, Error};
+
+use crate::database::problem::Problem;
+
+pub struct Database {
+    connection: Connection,
+}
+
+impl Database {
+    pub fn new(database_path: impl AsRef<Path>) -> Result<Self, Error> {
+        let connection =  Connection::open(database_path)?;
+        Ok(Database {
+            connection,
+        })
+    }
+
+    pub fn new_in_memory() -> Result<Self, Error> {
+        let connection =  Connection::open_in_memory()?;
+        Ok(Database {
+            connection,
+        })
+    }
+
+    pub fn insert_problem(&self, title: &str, description: &str) -> Result<Problem, Error> {
+        static QUERY: &str = include_str!("sql/insert_problem.sql");
+        let mut statement = self.connection.prepare(QUERY)?;
+
+        let result = statement
+            .query_one((title, description), |row| {
+                Ok(Problem::new(
+                    row.get("id")?,
+                    title.to_owned(),
+                    description.to_owned(),
+                ))
+            })?;
+
+        Ok(result)
+    }
+
+    pub fn delete_problem(&self, id: i64) -> Result<(), Error> {
+        todo!();
+    }
+    
+    pub fn fetch_problems(&self) -> Result<Vec<Problem>, Error> {
+        static QUERY: &str = include_str!("sql/fetch_problems.sql");
+        let mut statement = self.connection.prepare(QUERY)?;
+
+        let problems = statement
+            .query_map([], |row| {
+                Ok(Problem::new(
+                    row.get("id")?,
+                    row.get("title")?,
+                    row.get("description")?,
+                ))
+            })?
+            .collect::<Result<Vec<Problem>, _>>()?;
+        
+        Ok(problems)
+    }
+    
+    pub fn initialize(&self) -> Result<(), Error> {
+        static QUERY: &str = include_str!("sql/initialize.sql");        
+        self.connection.execute_batch(QUERY)?;
+        Ok(())
+    }
+}
+
+#[cfg(test)]
+mod tests {
+    use super::*;
+    
+    #[tokio::test]
+    async fn test_problem_database() {
+        let db = Database::new_in_memory().unwrap();
+
+        db.initialize().unwrap();
+
+        let title = "test problem 1";
+        let description = "description of test problem 1";
+        
+        let problem = db.insert_problem(title, description).unwrap();
+        assert_eq!(problem.title(), title);
+        assert_eq!(problem.description(), description);
+
+        let problems = db.fetch_problems().unwrap();
+        assert_eq!(problems.len(), 1);
+        assert_eq!(problems[0].title(), title);
+        assert_eq!(problems[0].description(), description);
+    }
+}
diff --git a/src/database/problem.rs b/src/database/problem.rs
new file mode 100644
index 0000000..fdbb2b5
--- /dev/null
+++ b/src/database/problem.rs
@@ -0,0 +1,16 @@
+pub struct Problem {
+    id: i64,
+    title: String,
+    description: String,
+}
+
+impl Problem {
+    pub fn new(id: i64, title: String, description: String) -> Self {
+        Self { id, title, description }
+    }
+
+    pub fn title(&self) -> &str { &self.title }
+    pub fn description(&self) -> &str { &self.description }
+}
+
+
diff --git a/src/database/sql/delete_problem.sql b/src/database/sql/delete_problem.sql
new file mode 100644
index 0000000..dcf0088
--- /dev/null
+++ b/src/database/sql/delete_problem.sql
@@ -0,0 +1 @@
+DELETE FROM problem WHERE id = ?1;
diff --git a/src/database/sql/fetch_problems.sql b/src/database/sql/fetch_problems.sql
new file mode 100644
index 0000000..1320cda
--- /dev/null
+++ b/src/database/sql/fetch_problems.sql
@@ -0,0 +1 @@
+SELECT * FROM problem;
diff --git a/src/database/sql/initialize.sql b/src/database/sql/initialize.sql
new file mode 100644
index 0000000..3baf4ff
--- /dev/null
+++ b/src/database/sql/initialize.sql
@@ -0,0 +1,21 @@
+CREATE TABLE IF NOT EXISTS problem (
+    id          INTEGER PRIMARY KEY,
+    title       TEXT NOT NULL,
+    description TEXT NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS user (
+    id            INTEGER PRIMARY KEY,
+    email         TEXT UNIQUE NOT NULL,
+    username      TEXT NOT NULL,
+    password_hash TEXT NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS submission (
+    id         INTEGER PRIMARY KEY,
+    problem_id INTEGER NOT NULL,
+    user_id    INTEGER NOT NULL,
+    language   TEXT NOT NULL,
+    code       TEXT NOT NULL,
+    validated  INTEGER NOT NULL
+);
diff --git a/src/database/sql/insert_problem.sql b/src/database/sql/insert_problem.sql
new file mode 100644
index 0000000..b5997bd
--- /dev/null
+++ b/src/database/sql/insert_problem.sql
@@ -0,0 +1 @@
+INSERT INTO problem (title, description) VALUES (?1, ?2) RETURNING id;
-- 
cgit v1.2.3