docbox_database/models/
search.rs

1use chrono::{DateTime, Utc};
2use serde::{Deserialize, Serialize};
3use sqlx::prelude::FromRow;
4use uuid::Uuid;
5
6use crate::{
7    DbPool, DbResult,
8    models::document_box::{DocumentBoxScopeRaw, DocumentBoxScopeRawRef},
9};
10
11#[derive(Debug, Clone, FromRow, Serialize)]
12pub struct DbPageResult {
13    pub page: i32,
14    pub highlighted_content: String,
15    pub rank: f32,
16}
17
18#[derive(Debug, Clone, FromRow, Serialize)]
19pub struct DbPageCountResult {
20    pub count: i64,
21}
22
23#[derive(Debug, Clone, Serialize, Deserialize, FromRow, sqlx::Type)]
24#[sqlx(type_name = "docbox_search_page_match")]
25pub struct DocboxSearchPageMatch {
26    pub page: i64,
27    pub matched: String,
28    pub content_match_rank: f64,
29    pub total_hits: i64,
30}
31
32#[derive(Debug, Clone, Serialize, Deserialize, FromRow, sqlx::Type)]
33#[sqlx(type_name = "docbox_search_date_range")]
34pub struct DocboxSearchDateRange {
35    pub start: Option<DateTime<Utc>>,
36    pub end: Option<DateTime<Utc>>,
37}
38
39#[derive(Debug, Clone, Serialize, Deserialize, FromRow, sqlx::Type)]
40#[sqlx(type_name = "docbox_search_filters")]
41pub struct DocboxSearchFilters {
42    pub document_boxes: Vec<String>,
43    pub folder_children: Option<Vec<Uuid>>,
44    pub include_name: bool,
45    pub include_content: bool,
46    pub created_at: Option<DocboxSearchDateRange>,
47    pub created_by: Option<String>,
48}
49
50#[derive(Debug, Clone, Serialize, Deserialize, FromRow, sqlx::Type)]
51#[sqlx(type_name = "docbox_search_match")]
52pub struct DocboxSearchMatch {
53    pub item_type: String,
54    pub item_id: Uuid,
55    pub document_box: String,
56    pub name_match_tsv: bool,
57    pub name_match_tsv_rank: f64,
58    pub name_match: bool,
59    pub content_match: bool,
60    pub content_rank: f64,
61    pub total_hits: i64,
62    pub page_matches: Vec<DocboxSearchPageMatch>,
63    pub created_at: DateTime<Utc>,
64}
65
66#[derive(Debug, Clone, Serialize, Deserialize, FromRow, sqlx::Type)]
67#[sqlx(type_name = "docbox_search_match_ranked")]
68pub struct DocboxSearchMatchRanked {
69    pub search_match: DocboxSearchMatch,
70    pub rank: f64,
71    pub total_count: i64,
72}
73
74pub async fn count_search_file_pages(
75    db: &DbPool,
76    scope: &DocumentBoxScopeRaw,
77    file_id: Uuid,
78    query: &str,
79) -> DbResult<DbPageCountResult> {
80    sqlx::query_as(
81        r#"
82        SELECT COUNT(*) AS "count"
83        FROM "docbox_folders" "folder"
84        JOIN "docbox_files" "file" ON "file"."folder_id" = "folder"."id"
85        JOIN "docbox_files_pages" "page" ON "page"."file_id" = "file"."id"
86        WHERE "folder"."document_box" = $1
87            AND "file"."id" = $2
88            AND "page"."file_id" = $2
89            AND "page"."content_tsv" @@ plainto_tsquery('english', $3)
90    "#,
91    )
92    .bind(scope)
93    .bind(file_id)
94    .bind(query)
95    .fetch_one(db)
96    .await
97}
98
99pub async fn search_file_pages(
100    db: &DbPool,
101    scope: &DocumentBoxScopeRaw,
102    file_id: Uuid,
103    query: &str,
104    limit: i64,
105    offset: i64,
106) -> DbResult<Vec<DbPageResult>> {
107    sqlx::query_as(r#"
108        SELECT
109            "page"."page",
110            ts_headline('english', "page"."content", plainto_tsquery('english', $3)) AS "highlighted_content",
111            ts_rank("page"."content_tsv", plainto_tsquery('english', $3)) AS "rank"
112        FROM "docbox_folders" "folder"
113        JOIN "docbox_files" "file" ON "file"."folder_id" = "folder"."id"
114        JOIN "docbox_files_pages" "page" ON "page"."file_id" = "file"."id"
115        WHERE "folder"."document_box" = $1
116            AND "file"."id" = $2
117            AND "page"."file_id" = $2
118            AND "page"."content_tsv" @@ plainto_tsquery('english', $3)
119        ORDER BY "rank" DESC
120        LIMIT $4
121        OFFSET $5
122    "#)
123    .bind(scope)
124    .bind(file_id)
125    .bind(query)
126    .bind(limit)
127    .bind(offset)
128    .fetch_all(db)
129    .await
130}
131
132pub async fn delete_file_pages_by_scope(
133    db: &DbPool,
134    scope: DocumentBoxScopeRawRef<'_>,
135) -> DbResult<()> {
136    sqlx::query(
137        r#"
138        DELETE FROM "docbox_files_pages" AS "page"
139        USING "docbox_files" AS "file"
140        JOIN "docbox_folders" AS "folder" ON "file"."folder_id" = "folder"."id"
141        WHERE "page"."file_id" = "file"."id" AND "folder"."document_box" = $1;
142    "#,
143    )
144    .bind(scope)
145    .execute(db)
146    .await?;
147    Ok(())
148}
149
150pub async fn delete_file_pages_by_file_id(db: &DbPool, file_id: Uuid) -> DbResult<()> {
151    sqlx::query(
152        r#"
153        DELETE FROM "docbox_files_pages" AS "page"
154        WHERE "page"."file_id" = $1;
155    "#,
156    )
157    .bind(file_id)
158    .execute(db)
159    .await?;
160    Ok(())
161}
162
163#[derive(Debug, Deserialize)]
164pub struct DbSearchPageResult {
165    pub page: i64,
166    pub matched: String,
167}
168
169#[derive(Debug, FromRow)]
170pub struct DbSearchResult {
171    pub item_type: String,
172    pub item_id: Uuid,
173    pub document_box: DocumentBoxScopeRaw,
174    pub name_match_tsv: bool,
175    pub name_match: bool,
176    pub content_match: bool,
177    pub total_hits: i64,
178    #[sqlx(json)]
179    pub page_matches: Vec<DbSearchPageResult>,
180    pub total_count: i64,
181    pub rank: f64,
182}
docbox_database/models/search.rs

docbox_database/models/
search.rs
