diff options
Diffstat (limited to 'src/database')
| -rw-r--r-- | src/database/database.rs | 51 | ||||
| -rw-r--r-- | src/database/sql/fetch_display_submissions.sql | 10 | ||||
| -rw-r--r-- | src/database/sql/fetch_submissions_by_problem_id.sql | 1 | ||||
| -rw-r--r-- | src/database/sql/fetch_user.sql | 1 | ||||
| -rw-r--r-- | src/database/submission.rs | 10 |
5 files changed, 71 insertions, 2 deletions
diff --git a/src/database/database.rs b/src/database/database.rs index 6ebbe1c..961c39c 100644 --- a/src/database/database.rs +++ b/src/database/database.rs @@ -4,7 +4,7 @@ use r2d2::Pool; use r2d2_sqlite::SqliteConnectionManager; use r2d2_sqlite::rusqlite::OptionalExtension; -use crate::database::submission::Submission; +use crate::database::submission::{DisplaySubmission, Submission}; use super::problem::Problem; use super::user::User; @@ -195,6 +195,55 @@ impl Database { .map_err(|e| DatabaseError::Query(e.to_string()))? ) } + + pub fn fetch_submissions_by_problem_id(&self, problem_id: i64) -> Result<Vec<Submission>, DatabaseError> { + static QUERY: &str = include_str!("sql/fetch_submissions_by_problem_id.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_map([problem_id], |row| { + Ok(Submission::new( + row.get("id")?, + row.get("user_id")?, + row.get("problem_id")?, + row.get("language")?, + row.get("details")?, + row.get("code")?, + )) + }) + .map_err(|e| DatabaseError::Query(e.to_string()))? + .collect::<Result<Vec<_>, _>>() + .map_err(|e| DatabaseError::Query(e.to_string()))? + ) + } + + pub fn fetch_display_submissions(&self, problem_id: i64) -> Result<Vec<DisplaySubmission>, DatabaseError> { + static QUERY: &str = include_str!("sql/fetch_display_submissions.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_map([problem_id], |row| { + Ok(DisplaySubmission { + id: row.get("id")?, + username: row.get("username")?, + language: row.get("language")?, + details: row.get("details")?, + code: row.get("code")?, + }) + }) + .map_err(|e| DatabaseError::Query(e.to_string()))? + .collect::<Result<Vec<_>, _>>() + .map_err(|e| DatabaseError::Query(e.to_string()))? + ) + } pub fn initialize(&self) -> Result<(), DatabaseError> { static QUERY: &str = include_str!("sql/initialize.sql"); diff --git a/src/database/sql/fetch_display_submissions.sql b/src/database/sql/fetch_display_submissions.sql new file mode 100644 index 0000000..5e836b3 --- /dev/null +++ b/src/database/sql/fetch_display_submissions.sql @@ -0,0 +1,10 @@ +SELECT +submission.id AS id, +user.username AS username, +submission.language AS language, +submission.details AS details, +submission.code AS code +FROM submission JOIN user +ON user.id = submission.user_id +WHERE submission.problem_id = ?1 +ORDER BY length(submission.code); diff --git a/src/database/sql/fetch_submissions_by_problem_id.sql b/src/database/sql/fetch_submissions_by_problem_id.sql new file mode 100644 index 0000000..3b5a4ae --- /dev/null +++ b/src/database/sql/fetch_submissions_by_problem_id.sql @@ -0,0 +1 @@ +SELECT * FROM submission WHERE submission.problem_id = ?1; diff --git a/src/database/sql/fetch_user.sql b/src/database/sql/fetch_user.sql new file mode 100644 index 0000000..caadee9 --- /dev/null +++ b/src/database/sql/fetch_user.sql @@ -0,0 +1 @@ +SELECT * FROM user WHERE user.id = ?1; diff --git a/src/database/submission.rs b/src/database/submission.rs index 4a83edd..b117e17 100644 --- a/src/database/submission.rs +++ b/src/database/submission.rs @@ -28,5 +28,13 @@ impl Submission { pub fn language(&self) -> &str { &self.language } pub fn details(&self) -> &str { &self.details } pub fn code(&self) -> &str { &self.code } - +} + +#[derive(Serialize)] +pub struct DisplaySubmission { + pub(crate) id: i64, + pub(crate) username: String, + pub(crate) language: String, + pub(crate) details: String, + pub(crate) code: String, } |