user route and database pooling using r2d2

1 files changed, 123 insertions, 35 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());
     }
 }