iqps_backend/db/
mod.rs

1//! Database stuff. See submodules also.
2
3use color_eyre::eyre::eyre;
4use sqlx::{postgres::PgPoolOptions, prelude::FromRow, PgPool, Postgres, Transaction};
5use std::time::Duration;
6
7use crate::{
8    env::EnvVars,
9    pathutils::{PaperCategory, Paths},
10    qp::{self, AdminDashboardQP, Exam, LibraryQP, Semester},
11    routing::{EditReq, FileDetails},
12};
13
14mod models;
15mod queries;
16
17/// The database
18pub struct Database {
19    connection: PgPool,
20}
21
22#[derive(FromRow)]
23/// Needed this to use the `query_as()` function of sqlx. There is probably a better way to do this but this is my first time, sorry.
24struct Breh {
25    id: i32,
26}
27
28impl Database {
29    /// Creates a new database connection given the environment variables.
30    pub async fn new(env_vars: &EnvVars) -> Result<Self, sqlx::Error> {
31        let database_url = format!(
32            "postgres://{}:{}@{}:{}/{}",
33            env_vars.db_user,
34            env_vars.db_password,
35            env_vars.db_host,
36            env_vars.db_port,
37            env_vars.db_name
38        );
39
40        let conn_pool = PgPoolOptions::new()
41            .max_connections(5)
42            .acquire_timeout(Duration::from_secs(3))
43            .connect(&database_url)
44            .await?;
45
46        Ok(Self {
47            connection: conn_pool,
48        })
49    }
50
51    /// Fetches the list of all unapproved papers
52    pub async fn get_unapproved_papers(&self) -> Result<Vec<qp::AdminDashboardQP>, sqlx::Error> {
53        let query_sql = queries::get_all_unapproved_query();
54        let papers: Vec<qp::AdminDashboardQP> = sqlx::query_as(&query_sql)
55            .fetch_all(&self.connection)
56            .await?;
57
58        Ok(papers)
59    }
60
61    /// Returns the number of unapproved papers
62    pub async fn get_unapproved_papers_count(&self) -> Result<i64, sqlx::Error> {
63        let count: (i64,) = sqlx::query_as(queries::GET_UNAPPROVED_COUNT)
64            .fetch_one(&self.connection)
65            .await?;
66
67        Ok(count.0)
68    }
69
70    /// Searches for papers from a given query. Uses some voodoo black magic by @rajivharlalka
71    pub async fn search_papers(
72        &self,
73        query: &str,
74        exam_filter: Vec<Exam>,
75    ) -> Result<Vec<qp::BaseQP>, sqlx::Error> {
76        let query_sql = queries::get_qp_search_query(exam_filter);
77        let query = sqlx::query_as(&query_sql).bind(query);
78
79        let papers: Vec<qp::BaseQP> = query.fetch_all(&self.connection).await?;
80
81        Ok(papers)
82    }
83
84    pub async fn get_paper_by_id(&self, id: i32) -> Result<qp::AdminDashboardQP, sqlx::Error> {
85        let query_sql = queries::get_get_paper_by_id_query();
86        let query = sqlx::query_as(&query_sql).bind(id);
87
88        let paper: qp::AdminDashboardQP = query.fetch_one(&self.connection).await?;
89
90        Ok(paper)
91    }
92
93    /// Edit's a paper's details.
94    ///
95    /// - Sets the `approved_by` field to the username if approved.
96    /// - Sets the `filelink` to:
97    ///     - For library papers, remains unchanged
98    ///     - For uploaded papers, approved papers are moved to the approved directory and renamed `id_coursecode_coursename_year_semester_exam.pdf` and unapproved papers are moved to the unapproved directory and named `id.pdf`
99    /// - Deletes `replace` papers from the database.
100    ///
101    /// Returns the database transaction, the old filelink and the new paper details ([`crate::qp::AdminDashboardQP`])
102    pub async fn edit_paper(
103        &self,
104        edit_req: EditReq,
105        username: &str,
106        env_vars: &EnvVars,
107    ) -> Result<(Transaction<'_, Postgres>, String, AdminDashboardQP), color_eyre::eyre::Error>
108    {
109        let EditReq {
110            id,
111            course_code,
112            course_name,
113            year,
114            semester,
115            exam,
116            approve_status,
117            note,
118            replace,
119        } = edit_req;
120
121        let current_details = self.get_paper_by_id(id).await?;
122
123        // Construct the final values to be inserted into the db
124        let course_code = course_code.unwrap_or(current_details.qp.course_code);
125        let course_name = course_name.unwrap_or(current_details.qp.course_name);
126        let year = year.unwrap_or(current_details.qp.year);
127        let semester: String = String::from(
128            &semester
129                .map(|sem| Semester::try_from(sem.as_str()))
130                .transpose()?
131                .unwrap_or(current_details.qp.semester),
132        );
133        let exam: String = String::from(
134            &exam
135                .map(|exam| Exam::try_from(exam.as_str()))
136                .transpose()?
137                .unwrap_or(current_details.qp.exam),
138        );
139        let approve_status = approve_status.unwrap_or(current_details.approve_status);
140
141        // Set the new filelink
142        let old_filelink = current_details.qp.filelink;
143
144        let new_filelink = if current_details.qp.from_library {
145            old_filelink.clone()
146        } else if approve_status {
147            env_vars.paths.get_slug(
148                &format!(
149                    "{}.pdf",
150                    Paths::sanitize_path(&format!(
151                        "{}_{}_{}_{}_{}_{}",
152                        id, course_code, course_name, year, semester, exam
153                    ))
154                ),
155                PaperCategory::Approved,
156            )
157        } else {
158            env_vars
159                .paths
160                .get_slug(&format!("{}.pdf", id), PaperCategory::Unapproved)
161        };
162
163        let mut tx = self.connection.begin().await?;
164
165        let query_sql = queries::get_edit_paper_query(approve_status);
166        let query = sqlx::query_as(&query_sql)
167            .bind(id)
168            .bind(&course_code)
169            .bind(&course_name)
170            .bind(year)
171            .bind(&semester)
172            .bind(&exam)
173            .bind(&note)
174            .bind(approve_status)
175            .bind(&new_filelink);
176
177        let query = if approve_status {
178            query.bind(username)
179        } else {
180            query
181        };
182
183        let new_qp: AdminDashboardQP = query.fetch_one(&mut *tx).await?;
184
185        // Delete the replaced papers
186        for replace_id in replace {
187            let rows_affected = sqlx::query(queries::SOFT_DELETE_ANY_BY_ID)
188                .bind(replace_id)
189                .execute(&mut *tx)
190                .await?
191                .rows_affected();
192
193            if rows_affected > 1 {
194                tx.rollback().await?;
195                return Err(eyre!(
196                    "Error: {} (> 1) papers were deleted. Rolling back.",
197                    rows_affected
198                ));
199            }
200        }
201
202        Ok((tx, old_filelink, new_qp))
203    }
204
205    /// Sets the `is_deleted` field to true and `approve_status` to false. Only deletes uploaded papers.
206    ///
207    /// Returns a boolean that represents whether a db entry was affected or not. If more than one entry was affected, an error will be thrown and the transaction will be rolled back.
208    pub async fn soft_delete(&self, id: i32) -> Result<bool, color_eyre::eyre::Error> {
209        let mut tx = self.connection.begin().await?;
210
211        let rows_affected = sqlx::query(queries::SOFT_DELETE_ANY_BY_ID)
212            .bind(id)
213            .execute(&mut *tx)
214            .await?
215            .rows_affected();
216
217        if rows_affected > 1 {
218            tx.rollback().await?;
219            Err(eyre!(
220                "Error: {} (> 1) papers were deleted. Rolling back.",
221                rows_affected
222            ))
223        } else {
224            tx.commit().await?;
225            Ok(rows_affected == 1)
226        }
227    }
228
229    /// Gets all soft-deleted papers from the database
230    pub async fn get_soft_deleted_papers(&self) -> Result<Vec<AdminDashboardQP>, sqlx::Error> {
231        let query_sql = queries::get_get_soft_deleted_papers_query();
232        let papers: Vec<AdminDashboardQP> = sqlx::query_as(&query_sql)
233            .fetch_all(&self.connection)
234            .await?;
235
236        Ok(papers)
237    }
238
239    /// Permanently deletes a paper from the database
240    pub async fn hard_delete(
241        &self,
242        id: i32,
243    ) -> Result<Transaction<'_, Postgres>, color_eyre::eyre::Error> {
244        let mut tx = self.connection.begin().await?;
245        let rows_affected = sqlx::query(queries::HARD_DELETE_BY_ID)
246            .bind(id)
247            .execute(&mut *tx)
248            .await?
249            .rows_affected();
250        if rows_affected > 1 {
251            tx.rollback().await?;
252            return Err(eyre!(
253                "Error: {} (> 1) papers were deleted. Rolling back.",
254                rows_affected
255            ));
256        } else if rows_affected < 1 {
257            tx.rollback().await?;
258            return Err(eyre!("Error: No papers were deleted."));
259        }
260        Ok(tx)
261    }
262
263    /// Returns all papers that match one or more of the specified properties exactly. `course_name` is required, other properties are optional.
264    pub async fn get_similar_papers(
265        &self,
266        course_code: &str,
267        year: Option<i32>,
268        semester: Option<&String>,
269        exam: Option<&String>,
270    ) -> Result<Vec<AdminDashboardQP>, sqlx::Error> {
271        let query_sql =
272            queries::get_similar_papers_query(year.is_some(), semester.is_some(), exam.is_some());
273        let query = sqlx::query_as(&query_sql).bind(course_code);
274
275        let query = query.bind(year);
276        let query = query.bind(semester);
277        let query = query.bind(exam);
278
279        let papers: Vec<qp::AdminDashboardQP> = query.fetch_all(&self.connection).await?;
280
281        Ok(papers)
282    }
283
284    /// Inserts a new uploaded question paper into the database. Uses a placeholder for the filelink which should be replaced once the id is known using the [crate::db::Database::update_filelink] function.
285    ///
286    /// Returns a tuple with the transaction and the id of the inserted paper.
287    pub async fn insert_new_uploaded_qp<'c>(
288        &self,
289        file_details: FileDetails,
290    ) -> Result<(Transaction<'c, Postgres>, i32), color_eyre::eyre::Error> {
291        let mut tx = self.connection.begin().await?;
292
293        let FileDetails {
294            course_code,
295            course_name,
296            year,
297            exam,
298            semester,
299            note,
300            ..
301        } = file_details;
302
303        let query = sqlx::query_as(queries::INSERT_NEW_QP)
304            .bind(course_code)
305            .bind(course_name)
306            .bind(year)
307            .bind(exam)
308            .bind(semester)
309            .bind(note)
310            .bind("placeholder_filelink")
311            .bind(false);
312
313        let Breh { id } = query.fetch_one(&mut *tx).await?;
314
315        Ok((tx, id))
316    }
317
318    /// Inserts a new library question paper into the database. Uses a placeholder for the filelink which should be replaced once the id is known using the [crate::db::Database::update_filelink] function.
319    ///
320    /// Returns a tuple with the transaction and the id of the inserted paper.
321    pub async fn insert_new_library_qp<'c>(
322        &self,
323        paper: &LibraryQP,
324    ) -> Result<(Transaction<'c, Postgres>, i32), color_eyre::eyre::Error> {
325        let mut tx = self.connection.begin().await?;
326
327        let LibraryQP {
328            course_code,
329            course_name,
330            year,
331            exam,
332            semester,
333            approve_status,
334            ..
335        } = paper;
336
337        let query = sqlx::query_as(queries::INSERT_NEW_LIBRARY_QP)
338            .bind(course_code)
339            .bind(course_name)
340            .bind(year)
341            .bind(exam)
342            .bind(semester)
343            .bind("")
344            .bind("placeholder_filelink")
345            .bind(approve_status);
346
347        let Breh { id } = query.fetch_one(&mut *tx).await?;
348
349        Ok((tx, id))
350    }
351
352    /// Updates filelink for an uploaded question paper uploaded using the [crate::db::Database::insert_new_uploaded_qp] or [crate::db::Database::insert_new_library_qp] function. Takes the same transaction that the previous function used.
353    pub async fn update_filelink(
354        &self,
355        tx: &mut Transaction<'_, Postgres>,
356        id: i32,
357        file_link: &str,
358    ) -> Result<(), color_eyre::eyre::Error> {
359        let query = sqlx::query(queries::UPDATE_FILELINK)
360            .bind(id)
361            .bind(file_link);
362
363        query.execute(&mut **tx).await?;
364
365        Ok(())
366    }
367}
iqps_backend/db/mod.rs

iqps_backend/db/
mod.rs
