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

iqps_backend/db/
mod.rs
