iqps_backend/db/
mod.rs

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

iqps_backend/db/
mod.rs
