iqps_backend/db/
queries.rs

1//! SQL queries for the database.
2//!
3//! Some of these are functions that return a query that is dynamically generated based on requirements.
4
5use crate::qp::Exam;
6
7/// Database initialization query. Not used by the backend directly.
8#[allow(dead_code)]
9const INIT_DB: &str = "
10CREATE TABLE IF NOT EXISTS iqps (
11	id integer primary key GENERATED ALWAYS AS identity,
12	course_code TEXT NOT NULL DEFAULT '',
13	course_name TEXT NOT NULL DEFAULT '',
14	year INTEGER NOT NULL,
15    exam TEXT NOT NULL DEFAULT '',
16    semester TEXT NOT NULL DEFAULT '',
17    note TEXT NOT NULL DEFAULT '',
18    filelink TEXT NOT NULL,
19    from_library BOOLEAN DEFAULT FALSE,
20    upload_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
21    approve_status BOOLEAN DEFAULT FALSE,
22    approved_by TEXT DEFAULT '',
23    is_deleted BOOLEAN DEFAULT FALSE,
24    fts_course_details tsvector GENERATED ALWAYS AS (to_tsvector('english', course_code || ' ' || course_name)) stored
25);
26CREATE INDEX IF NOT EXISTS iqps_fts ON iqps USING gin (fts_course_details);
27CREATE EXTENSION pg_trgm;
28CREATE INDEX IF NOT EXISTS idx_course_name_trgm ON iqps USING gin (course_name gin_trgm_ops);";
29
30/// Query to get similar papers. Matches `course_code` ($1) always. Other parameters are optional and can be enabled or disabled using the arguments to this function.
31///
32/// Query parameters:
33/// `$1` - `course_code``
34/// `$2` - `year`
35/// `$3` - `semester`
36/// `$3` - `exam`
37pub fn get_similar_papers_query(year: bool, semester: bool, exam: bool) -> String {
38    let mut param_num = 1;
39
40    format!(
41        "SELECT {} from iqps where is_deleted=false and course_code = $1 {} {} {}",
42        ADMIN_DASHBOARD_QP_FIELDS,
43        if year {
44            param_num += 1;
45            format!("AND year=${}", param_num)
46        } else {
47            "".to_string()
48        },
49        if semester {
50            param_num += 1;
51            format!("AND semester=${}", param_num)
52        } else {
53            "".to_string()
54        },
55        if exam {
56            param_num += 1;
57            format!("AND exam=${}", param_num)
58        } else {
59            "".to_string()
60        },
61    )
62}
63
64/// Soft deletes a paper (sets `approve_status` to false and `is_deleted` to true) of an uploaded paper.
65pub const SOFT_DELETE_BY_ID: &str =
66    "UPDATE iqps SET approve_status=false, is_deleted = true WHERE id=$1 AND from_library = false";
67
68/// Soft deletes a paper (sets `approve_status` to false and `is_deleted` to true) of any paper.
69pub const SOFT_DELETE_ANY_BY_ID: &str =
70    "UPDATE iqps SET approve_status=false, is_deleted = true WHERE id=$1";
71
72/// Hard deletes a paper (removes it from the database)
73pub const HARD_DELETE_BY_ID: &str =
74    "DELETE FROM iqps WHERE id=$1";
75
76/// Gets all soft-deleted papers ([`crate::db::models::DBAdminDashboardQP`]) from the database
77pub fn get_get_soft_deleted_papers_query() -> String {
78    format!("SELECT {} FROM iqps WHERE is_deleted=true", ADMIN_DASHBOARD_QP_FIELDS)
79}
80
81/// Get a paper ([`crate::db::models::DBAdminDashboardQP`]) with the given id (first parameter `$1`)
82pub fn get_get_paper_by_id_query() -> String {
83    format!(
84        "SELECT {} FROM iqps WHERE id = $1",
85        ADMIN_DASHBOARD_QP_FIELDS
86    )
87}
88
89/// Returns a query that updates a paper's details by id ($1) (course_code, course_name, year, semester, exam, note, approve_status, filelink). `approved_by` optionally included if the edit is also used for approval.
90///
91/// The query also returns all the admin dashboard qp fields of the edited paper
92///
93/// Query parameters:
94/// - $1: `id`
95/// - $2: `course_code`
96/// - $3: `course_name`
97/// - $4: `year`
98/// - $5: `semester`
99/// - $6: `exam`
100/// - $7: `note`
101/// - $8: `approve_status`
102/// - $9: `filelink`
103/// - $10: `approved_by`
104pub fn get_edit_paper_query(approval: bool) -> String {
105    format!(
106		"UPDATE iqps set course_code=$2, course_name=$3, year=$4, semester=$5, exam=$6, note=$7, approve_status=$8, filelink=$9{} WHERE id=$1 AND is_deleted=false RETURNING {}",
107		if approval {", approved_by=$10"} else {""},
108        ADMIN_DASHBOARD_QP_FIELDS
109	)
110}
111
112/// Gets all unapproved papers ([`crate::db::models::DBAdminDashboardQP`]) from the database
113pub fn get_all_unapproved_query() -> String {
114    format!("SELECT {} FROM iqps WHERE approve_status = false and is_deleted=false ORDER BY upload_timestamp ASC", ADMIN_DASHBOARD_QP_FIELDS)
115}
116
117/// Gets the count of unapproved papers in the database
118pub const GET_UNAPPROVED_COUNT: &str =
119    "SELECT COUNT(*) FROM iqps WHERE approve_status = false AND is_deleted = false";
120
121/// Returns the query for searching question papers. It is mostly voodoo, see [blog post](https://rajivharlalka.in/posts/iqps-search-development/).
122///
123/// The `exam_filter` argument is a vector of exam types to filter. Pass empty vector to disable the filter.
124///
125/// Query parameters:
126/// $1 - Search query
127///
128/// Returns the query and a boolean representing whether the second argument is required
129pub fn get_qp_search_query(exam_filter: Vec<Exam>) -> String {
130    let exam_filter_clause = exam_filter
131        .iter()
132        .map(|&exam| {
133            if let Exam::CT(_) = exam {
134                "exam LIKE 'ct%'".into()
135            } else {
136                format!("exam = '{}'", String::from(exam))
137            }
138        })
139        .collect::<Vec<String>>()
140        .join(" OR ");
141
142    let exam_clause_str = if exam_filter_clause.is_empty() {
143        "".into()
144    } else {
145        format!("WHERE ({} OR exam = '')", exam_filter_clause)
146    };
147
148    format!("
149            WITH filtered AS (
150                SELECT * from iqps {exam_filter} ORDER BY year DESC
151            ),
152            fuzzy AS (
153                SELECT id,
154                similarity(course_code || ' ' || course_name, $1) AS sim_score,
155                row_number() OVER (ORDER BY similarity(course_code || ' ' || course_name, $1) DESC) AS rank_ix
156                FROM filtered
157                WHERE (course_code || ' ' || course_name) %>> $1 AND approve_status = true
158                ORDER BY rank_ix
159                LIMIT 30
160            ),
161            full_text AS (
162                SELECT id,
163                    ts_rank_cd(fts_course_details, websearch_to_tsquery($1)) AS rank_score,
164                    row_number() OVER (ORDER BY ts_rank_cd(fts_course_details, websearch_to_tsquery($1)) DESC) AS rank_ix
165                FROM filtered
166                WHERE fts_course_details @@ websearch_to_tsquery($1) AND approve_status = true
167                ORDER BY rank_ix
168                LIMIT 30
169            ),
170            partial_search AS (
171                SELECT id,
172                    ts_rank_cd(fts_course_details, {to_tsquery}) AS rank_score,
173                    row_number() OVER (ORDER BY ts_rank_cd(fts_course_details, {to_tsquery}) DESC) as rank_ix
174                FROM filtered
175                WHERE fts_course_details @@ {to_tsquery} AND approve_status = true
176                LIMIT 30
177            ),
178            result AS (
179                SELECT {intermediate_fields}
180                FROM fuzzy
181                    FULL OUTER JOIN full_text ON fuzzy.id = full_text.id
182                    FULL OUTER JOIN partial_search ON coalesce(fuzzy.id, full_text.id) = partial_search.id
183                    JOIN filtered ON coalesce(fuzzy.id, full_text.id, partial_search.id) = filtered.id
184                ORDER BY
185                    coalesce(1.0 / (50 + fuzzy.rank_ix), 0.0) * 1 +
186                    coalesce(1.0 / (50 + full_text.rank_ix), 0.0) * 1 +
187                    coalesce(1.0 / (50 + partial_search.rank_ix), 0.0) * 1
188                DESC
189            ) SELECT {search_qp_fields} FROM result",
190            search_qp_fields = SEARCH_QP_FIELDS,
191            to_tsquery = "to_tsquery('simple', websearch_to_tsquery('simple', $1)::text || ':*')",
192            exam_filter = exam_clause_str,
193            intermediate_fields = ADMIN_DASHBOARD_QP_FIELDS.split(", ").map(|field| format!("filtered.{}", field)).collect::<Vec<String>>().join(", ")
194        )
195}
196
197/// List of fields in the [`crate::db::models::DBAdminDashboardQP`] to be used with SELECT clauses
198pub const ADMIN_DASHBOARD_QP_FIELDS: &str = "id, filelink, from_library, course_code, course_name, year, semester, exam, note, upload_timestamp, approve_status";
199
200/// List of fields in the [`crate::db::models::DBSearchQP`] to be used with SELECT clauses
201pub const SEARCH_QP_FIELDS: &str =
202    "id, filelink, from_library, course_code, course_name, year, semester, exam, note";
203
204/// Insert a newly uploaded file in the db (and return the id)
205/// Parameters in the following order: `course_code`, `course_name`, `year`, `exam`, `semester`, `note`, `filelink`, `from_library`
206pub const INSERT_NEW_QP: &str = "INSERT INTO iqps (course_code, course_name, year, exam, semester, note, filelink, from_library) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING id";
207
208/// Updates the filelink ($2) of a paper with the given id ($1). Used to update the filelink after a paper is uploaded.
209pub const UPDATE_FILELINK: &str = "UPDATE iqps SET filelink=$2 WHERE id=$1";
210
211/// Insert a library pqper in the db
212/// Parameters in the following order: `course_code`, `course_name`, `year`, `exam`, `semester`, `note`, `filelink`, `approve_status`
213pub const INSERT_NEW_LIBRARY_QP: &str = "INSERT INTO iqps (course_code, course_name, year, exam, semester, note, filelink, from_library, approve_status) VALUES ($1, $2, $3, $4, $5, $6, $7, true, $8) RETURNING id";
iqps_backend/db/queries.rs

iqps_backend/db/
queries.rs
