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