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(
68        &self,
69    ) -> Result<i64, sqlx::Error> {
70        let count: (i64,) = sqlx::query_as(queries::GET_UNAPPROVED_COUNT)
71            .fetch_one(&self.connection)
72            .await?;
73
74        Ok(count.0)
75    }
76
77    /// Searches for papers from a given query. Uses some voodoo black magic by @rajivharlalka
78    pub async fn search_papers(
79        &self,
80        query: &str,
81        exam_filter: Vec<Exam>,
82    ) -> Result<Vec<qp::BaseQP>, sqlx::Error> {
83        let query_sql = queries::get_qp_search_query(exam_filter);
84        let query = sqlx::query_as(&query_sql).bind(query);
85
86        let papers: Vec<models::DBBaseQP> = query.fetch_all(&self.connection).await?;
87
88        Ok(papers
89            .iter()
90            .map(|qp| qp::BaseQP::from(qp.clone()))
91            .collect())
92    }
93
94    pub async fn get_paper_by_id(&self, id: i32) -> Result<qp::AdminDashboardQP, sqlx::Error> {
95        let query_sql = queries::get_get_paper_by_id_query();
96        let query = sqlx::query_as(&query_sql).bind(id);
97
98        let paper: models::DBAdminDashboardQP = query.fetch_one(&self.connection).await?;
99
100        Ok(paper.into())
101    }
102
103    /// Edit's a paper's details.
104    ///
105    /// - Sets the `approved_by` field to the username if approved.
106    /// - Sets the `filelink` to:
107    ///     - For library papers, remains unchanged
108    ///     - 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`
109    /// - Deletes `replace` papers from the database.
110    ///
111    /// Returns the database transaction, the old filelink and the new paper details ([`crate::qp::AdminDashboardQP`])
112    pub async fn edit_paper<'c>(
113        &self,
114        edit_req: EditReq,
115        username: &str,
116        env_vars: &EnvVars,
117    ) -> Result<(Transaction<'c, Postgres>, String, AdminDashboardQP), color_eyre::eyre::Error>
118    {
119        let EditReq {
120            id,
121            course_code,
122            course_name,
123            year,
124            semester,
125            exam,
126            approve_status,
127            note,
128            replace,
129        } = edit_req;
130
131        let current_details = self.get_paper_by_id(id).await?;
132
133        // Construct the final values to be inserted into the db
134        let course_code = course_code.unwrap_or(current_details.qp.course_code);
135        let course_name = course_name.unwrap_or(current_details.qp.course_name);
136        let year = year.unwrap_or(current_details.qp.year);
137        let semester: String = semester
138            .map(|sem| Semester::try_from(&sem))
139            .transpose()?
140            .unwrap_or(current_details.qp.semester)
141            .into();
142        let exam: String = exam
143            .map(|exam| Exam::try_from(&exam))
144            .transpose()?
145            .unwrap_or(current_details.qp.exam)
146            .into();
147        let approve_status = approve_status.unwrap_or(current_details.approve_status);
148
149        // Set the new filelink
150        let old_filelink = current_details.qp.filelink;
151        let new_filelink = if current_details.qp.from_library {
152            old_filelink.clone() // TODO use consistent format
153        } else if approve_status {
154            env_vars.paths.get_slug(
155                &format!(
156                    "{}.pdf",
157                    Paths::sanitize_path(&format!(
158                        "{}_{}_{}_{}_{}_{}",
159                        id, course_code, course_name, year, semester, exam
160                    ))
161                ),
162                PaperCategory::Approved,
163            )
164        } else {
165            env_vars
166                .paths
167                .get_slug(&format!("{}.pdf", id), PaperCategory::Unapproved)
168        };
169
170        let mut tx = self.connection.begin().await?;
171
172        let query_sql = queries::get_edit_paper_query(approve_status);
173        let query = sqlx::query_as(&query_sql)
174            .bind(id)
175            .bind(&course_code)
176            .bind(&course_name)
177            .bind(year)
178            .bind(&semester)
179            .bind(&exam)
180            .bind(&note)
181            .bind(approve_status)
182            .bind(&new_filelink);
183
184        let query = if approve_status {
185            query.bind(username)
186        } else {
187            query
188        };
189
190        let new_qp: DBAdminDashboardQP = query.fetch_one(&mut *tx).await?;
191        let new_qp = AdminDashboardQP::from(new_qp);
192
193        // Delete the replaced papers
194        for replace_id in replace {
195            let rows_affected = sqlx::query(queries::SOFT_DELETE_ANY_BY_ID)
196                .bind(replace_id)
197                .execute(&mut *tx)
198                .await?
199                .rows_affected();
200
201            if rows_affected > 1 {
202                tx.rollback().await?;
203                return Err(eyre!(
204                    "Error: {} (> 1) papers were deleted. Rolling back.",
205                    rows_affected
206                ));
207            }
208        }
209
210        Ok((tx, old_filelink, new_qp))
211    }
212
213    // /// Adds a new upload paper's details to the database. Sets the `from_library` field to false.
214    // ///
215    // /// Returns the database transaction and the id of the uploaded paper
216    // pub async fn add_uploaded_paper<'c>(
217    //     &self,
218    //     file_details:
219    // ) -> Result<(Transaction<'c, Postgres>, i32), color_eyre::eyre::Error> {
220    // }
221
222    /// Sets the `is_deleted` field to true and `approve_status` to false. Only deletes uploaded papers.
223    ///
224    /// 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.
225    pub async fn soft_delete(&self, id: i32) -> Result<bool, color_eyre::eyre::Error> {
226        let mut tx = self.connection.begin().await?;
227
228        let rows_affected = sqlx::query(queries::SOFT_DELETE_BY_ID)
229            .bind(id)
230            .execute(&mut *tx)
231            .await?
232            .rows_affected();
233
234        if rows_affected > 1 {
235            tx.rollback().await?;
236            Err(eyre!(
237                "Error: {} (> 1) papers were deleted. Rolling back.",
238                rows_affected
239            ))
240        } else {
241            tx.commit().await?;
242            Ok(rows_affected == 1)
243        }
244    }
245
246    /// Returns all papers that match one or more of the specified properties exactly. `course_name` is required, other properties are optional.
247    pub async fn get_similar_papers(
248        &self,
249        course_code: &str,
250        year: Option<i32>,
251        semester: Option<&String>,
252        exam: Option<&String>,
253    ) -> Result<Vec<AdminDashboardQP>, sqlx::Error> {
254        let query_sql =
255            queries::get_similar_papers_query(year.is_some(), semester.is_some(), exam.is_some());
256        let query = sqlx::query_as(&query_sql).bind(course_code);
257
258        let query = query.bind(year);
259        let query = query.bind(semester);
260        let query = query.bind(exam);
261
262        let papers: Vec<models::DBAdminDashboardQP> = query.fetch_all(&self.connection).await?;
263
264        Ok(papers
265            .iter()
266            .map(|qp| qp::AdminDashboardQP::from(qp.clone()))
267            .collect())
268    }
269
270    /// 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.
271    ///
272    /// Returns a tuple with the transaction and the id of the inserted paper.
273    pub async fn insert_new_uploaded_qp<'c>(
274        &self,
275        file_details: &FileDetails,
276    ) -> Result<(Transaction<'c, Postgres>, i32), color_eyre::eyre::Error> {
277        let mut tx = self.connection.begin().await?;
278
279        let FileDetails {
280            course_code,
281            course_name,
282            year,
283            exam,
284            semester,
285            note,
286            ..
287        } = file_details;
288
289        let query = sqlx::query_as(queries::INSERT_NEW_QP)
290            .bind(course_code)
291            .bind(course_name)
292            .bind(year)
293            .bind(exam)
294            .bind(semester)
295            .bind(note)
296            .bind("placeholder_filelink")
297            .bind(false);
298
299        let Breh { id } = query.fetch_one(&mut *tx).await?;
300
301        Ok((tx, id))
302    }
303
304    #[allow(unused)]
305    /// 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.
306    ///
307    /// Returns a tuple with the transaction and the id of the inserted paper.
308    pub async fn insert_new_library_qp<'c>(
309        &self,
310        paper: &LibraryQP,
311    ) -> Result<(Transaction<'c, Postgres>, i32), color_eyre::eyre::Error> {
312        let mut tx = self.connection.begin().await?;
313
314        let LibraryQP {
315            course_code,
316            course_name,
317            year,
318            exam,
319            semester,
320            approve_status,
321            ..
322        } = paper;
323
324        let query = sqlx::query_as(queries::INSERT_NEW_LIBRARY_QP)
325            .bind(course_code)
326            .bind(course_name)
327            .bind(year)
328            .bind(exam)
329            .bind(semester)
330            .bind("")
331            .bind("placeholder_filelink")
332            .bind(approve_status);
333
334        let Breh { id } = query.fetch_one(&mut *tx).await?;
335
336        Ok((tx, id))
337    }
338
339    /// 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.
340    pub async fn update_filelink(
341        &self,
342        tx: &mut Transaction<'_, Postgres>,
343        id: i32,
344        file_link: &str,
345    ) -> Result<(), color_eyre::eyre::Error> {
346        let query = sqlx::query(queries::UPDATE_FILELINK)
347            .bind(id)
348            .bind(file_link);
349
350        query.execute(&mut **tx).await?;
351
352        Ok(())
353    }
354}
iqps_backend/db/mod.rs

iqps_backend/db/
mod.rs
