From 36fad793c3be58b220ae319a45c8cd8afbae09fa Mon Sep 17 00:00:00 2001 From: Daniel Hader Date: Mon, 11 May 2026 19:28:00 -0500 Subject: user route and database pooling using r2d2 --- src/database/database.rs | 158 ++++++++++++++++++++++------ src/database/problem.rs | 3 +- src/database/sql/fetch_user_by_email.sql | 1 + src/database/sql/fetch_user_by_username.sql | 1 + src/database/sql/initialize.sql | 2 +- src/database/sql/insert_user.sql | 1 + src/database/user.rs | 17 +++ 7 files changed, 146 insertions(+), 37 deletions(-) create mode 100644 src/database/sql/fetch_user_by_email.sql create mode 100644 src/database/sql/fetch_user_by_username.sql create mode 100644 src/database/sql/insert_user.sql create mode 100644 src/database/user.rs (limited to 'src/database') 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 +} + +#[derive(Debug)] +pub enum DatabaseError { + Connection(String), + Query(String), } impl Database { - pub fn new(database_path: impl AsRef) -> Result { - let connection = Connection::open(database_path)?; - Ok(Database { - connection, - }) + pub fn new(database_path: impl AsRef) -> Result { + 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 { + 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 { + 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, 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 { - let connection = Connection::open_in_memory()?; - Ok(Database { - connection, - }) + pub fn fetch_user_by_username(&self, username: &str) -> Result, 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 { + pub fn insert_problem(&self, title: &str, description: &str) -> Result { 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, Error> { + pub fn fetch_problems(&self) -> Result, 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::, _>>()?; - - Ok(problems) + }) + .map_err(|e| DatabaseError::Query(e.to_string()))? + .collect::, _>>() + .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 } +} -- cgit v1.2.3