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/// Get a paper ([`crate::db::models::DBAdminDashboardQP`]) with the given id (first parameter `$1`)
69pub fn get_get_paper_by_id_query() -> String {
70    format!(
71        "SELECT {} FROM iqps WHERE id = $1",
72        ADMIN_DASHBOARD_QP_FIELDS
73    )
74}
75
76/// 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.
77///
78/// The query also returns all the admin dashboard qp fields of the edited paper
79///
80/// Query parameters:
81/// - $1: `id`
82/// - $2: `course_code`
83/// - $3: `course_name`
84/// - $4: `year`
85/// - $5: `semester`
86/// - $6: `exam`
87/// - $7: `note`
88/// - $8: `approve_status`
89/// - $9: `filelink`
90/// - $10: `approved_by`
91pub fn get_edit_paper_query(approval: bool) -> String {
92    format!(
93		"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 {}",
94		if approval {", approved_by=$10"} else {""},
95        ADMIN_DASHBOARD_QP_FIELDS
96	)
97}
98
99/// Gets all unapproved papers ([`crate::db::models::DBAdminDashboardQP`]) from the database
100pub fn get_all_unapproved_query() -> String {
101    format!("SELECT {} FROM iqps WHERE approve_status = false and is_deleted=false ORDER BY upload_timestamp ASC", ADMIN_DASHBOARD_QP_FIELDS)
102}
103
104/// Returns the query for searching question papers. It is mostly voodoo, see [blog post](https://rajivharlalka.in/posts/iqps-search-development/).
105///
106/// The `exam_filter` argument is a vector of exam types to filter. Pass empty vector to disable the filter.
107///
108/// Query parameters:
109/// $1 - Search query
110///
111/// Returns the query and a boolean representing whether the second argument is required
112pub fn get_qp_search_query(exam_filter: Vec<Exam>) -> String {
113    let exam_filter_clause = exam_filter
114        .iter()
115        .map(|&exam| {
116            if let Exam::CT(_) = exam {
117                "exam LIKE 'ct%'".into()
118            } else {
119                format!("exam = '{}'", String::from(exam))
120            }
121        })
122        .collect::<Vec<String>>()
123        .join(" OR ");
124
125    let exam_clause_str = if exam_filter_clause.is_empty() {
126        "".into()
127    } else {
128        format!("WHERE ({} OR exam = '')", exam_filter_clause)
129    };
130
131    format!("
132            WITH filtered AS (
133                SELECT * from iqps {exam_filter} ORDER BY year DESC
134            ),
135            fuzzy AS (
136                SELECT id,
137                similarity(course_code || ' ' || course_name, $1) AS sim_score,
138                row_number() OVER (ORDER BY similarity(course_code || ' ' || course_name, $1) DESC) AS rank_ix
139                FROM filtered
140                WHERE (course_code || ' ' || course_name) %>> $1 AND approve_status = true
141                ORDER BY rank_ix
142                LIMIT 30
143            ),
144            full_text AS (
145                SELECT id,
146                    ts_rank_cd(fts_course_details, websearch_to_tsquery($1)) AS rank_score,
147                    row_number() OVER (ORDER BY ts_rank_cd(fts_course_details, websearch_to_tsquery($1)) DESC) AS rank_ix
148                FROM filtered
149                WHERE fts_course_details @@ websearch_to_tsquery($1) AND approve_status = true
150                ORDER BY rank_ix
151                LIMIT 30
152            ),
153            partial_search AS (
154                SELECT id,
155                    ts_rank_cd(fts_course_details, {to_tsquery}) AS rank_score,
156                    row_number() OVER (ORDER BY ts_rank_cd(fts_course_details, {to_tsquery}) DESC) as rank_ix
157                FROM filtered
158                WHERE fts_course_details @@ {to_tsquery} AND approve_status = true
159                LIMIT 30
160            ),
161            result AS (
162                SELECT {intermediate_fields}
163                FROM fuzzy
164                    FULL OUTER JOIN full_text ON fuzzy.id = full_text.id
165                    FULL OUTER JOIN partial_search ON coalesce(fuzzy.id, full_text.id) = partial_search.id
166                    JOIN filtered ON coalesce(fuzzy.id, full_text.id, partial_search.id) = filtered.id
167                ORDER BY
168                    coalesce(1.0 / (50 + fuzzy.rank_ix), 0.0) * 1 +
169                    coalesce(1.0 / (50 + full_text.rank_ix), 0.0) * 1 +
170                    coalesce(1.0 / (50 + partial_search.rank_ix), 0.0) * 1
171                DESC
172            ) SELECT {search_qp_fields} FROM result",
173            search_qp_fields = SEARCH_QP_FIELDS,
174            to_tsquery = "to_tsquery('simple', websearch_to_tsquery('simple', $1)::text || ':*')",
175            exam_filter = exam_clause_str,
176            intermediate_fields = ADMIN_DASHBOARD_QP_FIELDS.split(", ").map(|field| format!("filtered.{}", field)).collect::<Vec<String>>().join(", ")
177        )
178}
179
180/// List of fields in the [`crate::db::models::DBAdminDashboardQP`] to be used with SELECT clauses
181pub const ADMIN_DASHBOARD_QP_FIELDS: &str = "id, filelink, from_library, course_code, course_name, year, semester, exam, note, upload_timestamp, approve_status";
182
183/// List of fields in the [`crate::db::models::DBSearchQP`] to be used with SELECT clauses
184pub const SEARCH_QP_FIELDS: &str =
185    "id, filelink, from_library, course_code, course_name, year, semester, exam, note";
186
187/// Insert a newly uploaded file in the db (and return the id)
188/// Parameters in the following order: `course_code`, `course_name`, `year`, `exam`, `semester`, `note`, `filelink`, `from_library`
189pub 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";
190
191/// Updates the filelink ($2) of a paper with the given id ($1). Used to update the filelink after a paper is uploaded.
192pub const UPDATE_FILELINK: &str = "UPDATE iqps SET filelink=$2 WHERE id=$1";
193
194/// Insert a library pqper in the db
195/// Parameters in the following order: `course_code`, `course_name`, `year`, `exam`, `semester`, `note`, `filelink`, `approve_status`
196pub 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
