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    pub mime: Option<String>,
49}
50
51#[derive(Debug, Clone, Serialize, Deserialize, sqlx::Type)]
52#[sqlx(type_name = "docbox_search_item_type")]
53pub enum DocboxSearchItemType {
54    File,
55    Link,
56    Folder,
57}
58
59#[derive(Debug, Clone, Serialize, Deserialize, FromRow, sqlx::Type)]
60#[sqlx(type_name = "docbox_search_match")]
61pub struct DocboxSearchMatch {
62    pub item_type: DocboxSearchItemType,
63    pub item_id: Uuid,
64    pub document_box: String,
65    pub name_match_tsv: bool,
66    pub name_match_tsv_rank: f64,
67    pub name_match: bool,
68    pub content_match: bool,
69    pub content_rank: f64,
70    pub total_hits: i64,
71    pub page_matches: Vec<DocboxSearchPageMatch>,
72    pub created_at: DateTime<Utc>,
73}
74
75#[derive(Debug, Clone, Serialize, Deserialize, FromRow, sqlx::Type)]
76#[sqlx(type_name = "docbox_search_match_ranked")]
77pub struct DocboxSearchMatchRanked {
78    pub search_match: DocboxSearchMatch,
79    pub rank: f64,
80    pub total_count: i64,
81}
82
83pub struct SearchOptions {
84    pub query: String,
85    pub filters: DocboxSearchFilters,
86    pub max_pages: i64,
87    pub pages_offset: i64,
88    pub limit: i64,
89    pub offset: i64,
90}
91
92pub async fn search(db: &DbPool, options: SearchOptions) -> DbResult<Vec<DocboxSearchMatchRanked>> {
93    sqlx::query_as(
94        r#"
95        SELECT * FROM docbox_search($1, plainto_tsquery('english', $1), $2, $3, $4)
96        LIMIT $5
97        OFFSET $6
98    "#,
99    )
100    .bind(options.query)
101    .bind(options.filters)
102    .bind(options.max_pages)
103    .bind(options.pages_offset)
104    .bind(options.limit)
105    .bind(options.offset)
106    .fetch_all(db)
107    .await
108}
109
110pub async fn search_file_pages(
111    db: &DbPool,
112    scope: &DocumentBoxScopeRaw,
113    file_id: Uuid,
114    query: &str,
115    limit: i64,
116    offset: i64,
117) -> DbResult<Vec<DocboxSearchPageMatch>> {
118    sqlx::query_as(r#"
119        SELECT * FROM docbox_search_file_pages_with_scope($1, $2, $3, plainto_tsquery('english', $3))
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}
docbox_database/models/search.rs

docbox_database/models/
search.rs
