docbox_database/models/
search.rs

1use serde::{Deserialize, Serialize};
2use sqlx::prelude::FromRow;
3use uuid::Uuid;
4
5use crate::{
6    DbPool, DbResult,
7    models::document_box::{DocumentBoxScopeRaw, DocumentBoxScopeRawRef},
8};
9
10#[derive(Debug, Clone, FromRow, Serialize)]
11pub struct DbPageResult {
12    pub page: i32,
13    pub highlighted_content: String,
14    pub rank: f32,
15}
16
17#[derive(Debug, Clone, FromRow, Serialize)]
18pub struct DbPageCountResult {
19    pub count: i64,
20}
21
22pub async fn count_search_file_pages(
23    db: &DbPool,
24    scope: &DocumentBoxScopeRaw,
25    file_id: Uuid,
26    query: &str,
27) -> DbResult<DbPageCountResult> {
28    sqlx::query_as(
29        r#"
30        SELECT COUNT(*) AS "count"
31        FROM "docbox_folders" "folder"
32        JOIN "docbox_files" "file" ON "file"."folder_id" = "folder"."id"
33        JOIN "docbox_files_pages" "page" ON "page"."file_id" = "file"."id"
34        WHERE "folder"."document_box" = $1
35            AND "file"."id" = $2
36            AND "page"."file_id" = $2
37            AND "page"."content_tsv" @@ plainto_tsquery('english', $3)
38    "#,
39    )
40    .bind(scope)
41    .bind(file_id)
42    .bind(query)
43    .fetch_one(db)
44    .await
45}
46
47pub async fn search_file_pages(
48    db: &DbPool,
49    scope: &DocumentBoxScopeRaw,
50    file_id: Uuid,
51    query: &str,
52    limit: i64,
53    offset: i64,
54) -> DbResult<Vec<DbPageResult>> {
55    sqlx::query_as(r#"
56        SELECT
57            "page"."page",
58            ts_headline('english', "page"."content", plainto_tsquery('english', $3)) AS "highlighted_content",
59            ts_rank("page"."content_tsv", plainto_tsquery('english', $3)) AS "rank"
60        FROM "docbox_folders" "folder"
61        JOIN "docbox_files" "file" ON "file"."folder_id" = "folder"."id"
62        JOIN "docbox_files_pages" "page" ON "page"."file_id" = "file"."id"
63        WHERE "folder"."document_box" = $1
64            AND "file"."id" = $2
65            AND "page"."file_id" = $2
66            AND "page"."content_tsv" @@ plainto_tsquery('english', $3)
67        ORDER BY "rank" DESC
68        LIMIT $4
69        OFFSET $5
70    "#)
71    .bind(scope)
72    .bind(file_id)
73    .bind(query)
74    .bind(limit)
75    .bind(offset)
76    .fetch_all(db)
77    .await
78}
79
80pub async fn delete_file_pages_by_scope(
81    db: &DbPool,
82    scope: DocumentBoxScopeRawRef<'_>,
83) -> DbResult<()> {
84    sqlx::query(
85        r#"
86        DELETE FROM "docbox_files_pages" AS "page"
87        USING "docbox_files" AS "file"
88        JOIN "docbox_folders" AS "folder" ON "file"."folder_id" = "folder"."id"
89        WHERE "page"."file_id" = "file"."id" AND "folder"."document_box" = $1;
90    "#,
91    )
92    .bind(scope)
93    .execute(db)
94    .await?;
95    Ok(())
96}
97
98pub async fn delete_file_pages_by_file_id(db: &DbPool, file_id: Uuid) -> DbResult<()> {
99    sqlx::query(
100        r#"
101        DELETE FROM "docbox_files_pages" AS "page"
102        WHERE "page"."file_id" = $1;
103    "#,
104    )
105    .bind(file_id)
106    .execute(db)
107    .await?;
108    Ok(())
109}
110
111#[derive(Debug, Deserialize)]
112pub struct DbSearchPageResult {
113    pub page: i64,
114    pub matched: String,
115}
116
117#[derive(Debug, FromRow)]
118pub struct DbSearchResult {
119    pub item_type: String,
120    pub item_id: Uuid,
121    pub document_box: DocumentBoxScopeRaw,
122    pub name_match_tsv: bool,
123    pub name_match: bool,
124    pub content_match: bool,
125    pub total_hits: i64,
126    #[sqlx(json)]
127    pub page_matches: Vec<DbSearchPageResult>,
128    pub total_count: i64,
129    pub rank: f64,
130}
docbox_database/models/search.rs

docbox_database/models/
search.rs
