user route and database pooling using r2d2

7 files changed, 146 insertions, 37 deletions

diff --git a/src/database/database.rs b/src/database/database.rs
index 7da66f2..ca0e018 100644
--- a/src/database/database.rs
+++ b/src/database/database.rs
@@ -1,67 +1,154 @@
 use std::path::Path;
-use rusqlite::{Connection, Error};
 
-use crate::database::problem::Problem;
+use r2d2::Pool;
+use r2d2_sqlite::SqliteConnectionManager;
+use r2d2_sqlite::rusqlite::OptionalExtension;
 
+use super::problem::Problem;
+use super::user::User;
+
+#[derive(Clone)]
 pub struct Database {
-    connection: Connection,
+    pool: Pool<SqliteConnectionManager>
+}
+
+#[derive(Debug)]
+pub enum DatabaseError {
+    Connection(String),
+    Query(String),
 }
 
 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(database_path: impl AsRef<Path>) -> Result<Self, DatabaseError> {
+        let manager = SqliteConnectionManager::file(database_path);
+        let pool = Pool::new(manager)
+            .map_err(|e| DatabaseError::Connection(e.to_string()))?;
+        Ok(Database { pool })
+    }
+
+    pub fn new_in_memory() -> Result<Self, DatabaseError> {
+        let manager = SqliteConnectionManager::memory();
+        let pool = Pool::new(manager)
+            .map_err(|e| DatabaseError::Connection(e.to_string()))?;
+        Ok(Database { pool })
+    }
+
+    pub fn insert_user(&self, email: &str, username: &str, password_hash: &str) -> Result<User, DatabaseError> {
+        static QUERY: &str = include_str!("sql/insert_user.sql");
+        let conn = self.pool
+            .get()
+            .map_err(|e| DatabaseError::Connection(e.to_string()))?;
+        let mut statement = conn.prepare(QUERY)
+            .map_err(|e| DatabaseError::Query(e.to_string()))?;
+
+        Ok(statement
+            .query_one((email, username, password_hash), |row| {
+                Ok(User::new(
+                    row.get("id")?,
+                    email.to_owned(),
+                    username.to_owned(),
+                    password_hash.to_owned(),
+                ))
+            })
+            .map_err(|e| DatabaseError::Query(e.to_string()))?
+        )
+    }
+    
+    pub fn fetch_user_by_email(&self, email: &str) -> Result<Option<User>, DatabaseError> {
+        static QUERY: &str = include_str!("sql/fetch_user_by_email.sql");
+        let conn = self.pool
+            .get()
+            .map_err(|e| DatabaseError::Connection(e.to_string()))?;
+        let mut statement = conn.prepare(QUERY)
+            .map_err(|e| DatabaseError::Query(e.to_string()))?;
+
+        Ok(statement
+            .query_one([email], |row| {
+                Ok(User::new(
+                    row.get("id")?,
+                    row.get("email")?,
+                    row.get("username")?,
+                    row.get("password_hash")?,
+                ))
+            })
+            .optional()
+            .map_err(|e| DatabaseError::Query(e.to_string()))?
+        )
     }
 
-    pub fn new_in_memory() -> Result<Self, Error> {
-        let connection =  Connection::open_in_memory()?;
-        Ok(Database {
-            connection,
-        })
+    pub fn fetch_user_by_username(&self, username: &str) -> Result<Option<User>, DatabaseError> {
+        static QUERY: &str = include_str!("sql/fetch_user_by_username.sql");
+        let conn = self.pool
+            .get()
+            .map_err(|e| DatabaseError::Connection(e.to_string()))?;
+        let mut statement = conn.prepare(QUERY)
+            .map_err(|e| DatabaseError::Query(e.to_string()))?;
+
+        Ok(statement
+            .query_one([username], |row| {
+                Ok(User::new(
+                    row.get("id")?,
+                    row.get("email")?,
+                    row.get("username")?,
+                    row.get("password_hash")?,
+                ))
+            })
+            .optional()
+            .map_err(|e| DatabaseError::Query(e.to_string()))?
+        )
     }
 
-    pub fn insert_problem(&self, title: &str, description: &str) -> Result<Problem, Error> {
+    pub fn insert_problem(&self, title: &str, description: &str) -> Result<Problem, DatabaseError> {
         static QUERY: &str = include_str!("sql/insert_problem.sql");
-        let mut statement = self.connection.prepare(QUERY)?;
+        let conn = self.pool
+            .get()
+            .map_err(|e| DatabaseError::Connection(e.to_string()))?;
+        let mut statement = conn.prepare(QUERY)
+            .map_err(|e| DatabaseError::Query(e.to_string()))?;
 
-        let result = statement
+        Ok(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!();
+            })
+            .map_err(|e| DatabaseError::Query(e.to_string()))?
+        )
     }
     
-    pub fn fetch_problems(&self) -> Result<Vec<Problem>, Error> {
+    pub fn fetch_problems(&self) -> Result<Vec<Problem>, DatabaseError> {
         static QUERY: &str = include_str!("sql/fetch_problems.sql");
-        let mut statement = self.connection.prepare(QUERY)?;
+        let conn = self.pool
+            .get()
+            .map_err(|e| DatabaseError::Connection(e.to_string()))?;
+        let mut statement = conn.prepare(QUERY)
+            .map_err(|e| DatabaseError::Query(e.to_string()))?;
 
-        let problems = statement
+        Ok(statement
             .query_map([], |row| {
                 Ok(Problem::new(
                     row.get("id")?,
                     row.get("title")?,
                     row.get("description")?,
                 ))
-            })?
-            .collect::<Result<Vec<Problem>, _>>()?;
-        
-        Ok(problems)
+            })
+            .map_err(|e| DatabaseError::Query(e.to_string()))?
+            .collect::<Result<Vec<Problem>, _>>()
+            .map_err(|e| DatabaseError::Query(e.to_string()))?
+        )
     }
-    
-    pub fn initialize(&self) -> Result<(), Error> {
-        static QUERY: &str = include_str!("sql/initialize.sql");        
-        self.connection.execute_batch(QUERY)?;
+
+    pub fn initialize(&self) -> Result<(), DatabaseError> {
+        static QUERY: &str = include_str!("sql/initialize.sql");
+        let conn = self.pool
+            .get()
+            .map_err(|e| DatabaseError::Connection(e.to_string()))?;
+        
+        conn.execute_batch(QUERY)
+            .map_err(|e| DatabaseError::Query(e.to_string()))?;
+        
         Ok(())
     }
 }
@@ -82,10 +169,11 @@ mod tests {
         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);
+        assert_eq!(problems[0].id(), problem.id());
     }
 }
diff --git a/src/database/problem.rs b/src/database/problem.rs
index fdbb2b5..c3e04c2 100644
--- a/src/database/problem.rs
+++ b/src/database/problem.rs
@@ -5,10 +5,11 @@ pub struct Problem {
 }
 
 impl Problem {
-    pub fn new(id: i64, title: String, description: String) -> Self {
+    pub(super) fn new(id: i64, title: String, description: String) -> Self {
         Self { id, title, description }
     }
 
+    pub fn id(&self) -> i64 { self.id }
     pub fn title(&self) -> &str { &self.title }
     pub fn description(&self) -> &str { &self.description }
 }
diff --git a/src/database/sql/fetch_user_by_email.sql b/src/database/sql/fetch_user_by_email.sql
new file mode 100644
index 0000000..154b5ef
--- /dev/null
+++ b/src/database/sql/fetch_user_by_email.sql
@@ -0,0 +1 @@
+SELECT * FROM user WHERE user.email = ?1;
diff --git a/src/database/sql/fetch_user_by_username.sql b/src/database/sql/fetch_user_by_username.sql
new file mode 100644
index 0000000..f90d9d1
--- /dev/null
+++ b/src/database/sql/fetch_user_by_username.sql
@@ -0,0 +1 @@
+SELECT * FROM user WHERE user.username = ?1;
diff --git a/src/database/sql/initialize.sql b/src/database/sql/initialize.sql
index 3baf4ff..065bac9 100644
--- a/src/database/sql/initialize.sql
+++ b/src/database/sql/initialize.sql
@@ -7,7 +7,7 @@ CREATE TABLE IF NOT EXISTS problem (
 CREATE TABLE IF NOT EXISTS user (
     id            INTEGER PRIMARY KEY,
     email         TEXT UNIQUE NOT NULL,
-    username      TEXT NOT NULL,
+    username      TEXT UNIQUE NOT NULL,
     password_hash TEXT NOT NULL
 );
 
diff --git a/src/database/sql/insert_user.sql b/src/database/sql/insert_user.sql
new file mode 100644
index 0000000..6b67678
--- /dev/null
+++ b/src/database/sql/insert_user.sql
@@ -0,0 +1 @@
+INSERT INTO user (email, username, password_hash) VALUES (?1, ?2, ?3) RETURNING id;
diff --git a/src/database/user.rs b/src/database/user.rs
new file mode 100644
index 0000000..c9aaf51
--- /dev/null
+++ b/src/database/user.rs
@@ -0,0 +1,17 @@
+pub struct User {
+    id: i64,
+    email: String,
+    username: String,
+    password_hash: String,
+}
+
+impl User {
+    pub(super) fn new(id: i64, email: String, username: String, password_hash: String) -> Self {
+        Self { id, email, username, password_hash }
+    }
+
+    pub fn id(&self) -> i64 { self.id }
+    pub fn email(&self) -> &str { &self.email }
+    pub fn username(&self) -> &str { &self.username }
+    pub fn password_hash(&self) -> &str { &self.password_hash }
+}