submission logic and fetching submissions

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,
 }