docbox_database/models/
file.rs

1use chrono::{DateTime, Utc};
2use serde::Serialize;
3use sqlx::{
4    postgres::{PgQueryResult, PgRow},
5    prelude::FromRow,
6};
7use utoipa::ToSchema;
8use uuid::Uuid;
9
10use super::{
11    document_box::DocumentBoxScopeRaw,
12    folder::{FolderId, FolderPathSegment},
13    user::{User, UserId},
14};
15use crate::{
16    DbExecutor, DbResult,
17    models::{
18        document_box::DocumentBoxScopeRawRef,
19        folder::{WithFullPath, WithFullPathScope},
20        shared::{CountResult, TotalSizeResult},
21    },
22};
23
24pub type FileId = Uuid;
25
26#[derive(Debug, Clone, FromRow, Serialize)]
27pub struct File {
28    /// Unique identifier for the file
29    pub id: FileId,
30    /// Name of the file
31    pub name: String,
32    /// Mime type of the file content
33    pub mime: String,
34    /// Parent folder ID
35    pub folder_id: FolderId,
36    /// Hash of the file bytes stored in S3
37    pub hash: String,
38    /// Size of the file in bytes
39    pub size: i32,
40    /// Whether the file was determined to be encrypted when processing
41    pub encrypted: bool,
42    /// Whether the file is marked as pinned
43    pub pinned: bool,
44    /// S3 key pointing to the file
45    #[serde(skip)]
46    pub file_key: String,
47    /// When the file was created
48    pub created_at: DateTime<Utc>,
49    /// User who created the file
50    pub created_by: Option<UserId>,
51    /// Optional parent file ID if the file is a child of
52    /// some other file (i.e attachment for an email file)
53    pub parent_id: Option<Uuid>,
54}
55
56#[derive(Debug, FromRow, Serialize)]
57pub struct FileWithScope {
58    #[sqlx(flatten)]
59    pub file: File,
60    pub scope: String,
61}
62
63/// File with the resolved creator and last modified data
64#[derive(Debug, Clone, FromRow, Serialize, ToSchema)]
65pub struct FileWithExtra {
66    /// Unique identifier for the file
67    #[schema(value_type = Uuid)]
68    pub id: FileId,
69    /// Name of the file
70    pub name: String,
71    /// Mime type of the file content
72    pub mime: String,
73    /// Parent folder ID
74    #[schema(value_type = Uuid)]
75    pub folder_id: FolderId,
76    /// Hash of the file bytes stored in S3
77    pub hash: String,
78    /// Size of the file in bytes
79    pub size: i32,
80    /// Whether the file was determined to be encrypted when processing
81    pub encrypted: bool,
82    /// Whether the file is marked as pinned
83    pub pinned: bool,
84    /// When the file was created
85    pub created_at: DateTime<Utc>,
86    /// User who created the file
87    #[sqlx(flatten)]
88    #[schema(nullable, value_type = User)]
89    pub created_by: CreatedByUser,
90    /// Last time the file was modified
91    pub last_modified_at: Option<DateTime<Utc>>,
92    /// User who last modified the file
93    #[sqlx(flatten)]
94    #[schema(nullable, value_type = User)]
95    pub last_modified_by: LastModifiedByUser,
96    /// Optional parent file if the file is a child
97    #[schema(value_type = Option<Uuid>)]
98    pub parent_id: Option<FileId>,
99}
100
101/// File with extra with an additional resolved full path
102#[derive(Debug, FromRow, Serialize, ToSchema)]
103pub struct ResolvedFileWithExtra {
104    #[serde(flatten)]
105    #[sqlx(flatten)]
106    pub file: FileWithExtra,
107    #[sqlx(json)]
108    pub full_path: Vec<FolderPathSegment>,
109}
110
111/// Wrapper type for extracting a [User] that was joined
112/// from another table where the fields are prefixed with "cb_"
113#[derive(Debug, Clone, Serialize)]
114#[serde(transparent)]
115pub struct CreatedByUser(pub Option<User>);
116
117impl<'r> FromRow<'r, PgRow> for CreatedByUser {
118    fn from_row(row: &'r PgRow) -> Result<Self, sqlx::Error> {
119        use sqlx::Row;
120
121        let id: Option<UserId> = row.try_get("cb_id")?;
122        if let Some(id) = id {
123            let name: Option<String> = row.try_get("cb_name")?;
124            let image_id: Option<String> = row.try_get("cb_image_id")?;
125            return Ok(CreatedByUser(Some(User { id, name, image_id })));
126        }
127
128        Ok(CreatedByUser(None))
129    }
130}
131
132/// Wrapper type for extracting a [User] that was joined
133/// from another table where the fields are prefixed with "lmb_"
134#[derive(Debug, Clone, Serialize)]
135#[serde(transparent)]
136pub struct LastModifiedByUser(pub Option<User>);
137
138impl<'r> FromRow<'r, PgRow> for LastModifiedByUser {
139    fn from_row(row: &'r PgRow) -> Result<Self, sqlx::Error> {
140        use sqlx::Row;
141
142        let id: Option<UserId> = row.try_get("lmb_id")?;
143        if let Some(id) = id {
144            let name: Option<String> = row.try_get("lmb_name")?;
145            let image_id: Option<String> = row.try_get("lmb_image_id")?;
146            return Ok(LastModifiedByUser(Some(User { id, name, image_id })));
147        }
148
149        Ok(LastModifiedByUser(None))
150    }
151}
152
153#[derive(Debug, Serialize)]
154pub struct FileIdWithScope {
155    pub file_id: FileId,
156    pub scope: DocumentBoxScopeRaw,
157}
158
159#[derive(Debug)]
160pub struct CreateFile {
161    /// ID for the file to use
162    pub id: FileId,
163
164    /// Optional parent file if the file was created
165    /// as the result of another file (i.e. email attachments)
166    pub parent_id: Option<FileId>,
167
168    pub name: String,
169    pub mime: String,
170    pub folder_id: FolderId,
171    pub hash: String,
172    pub size: i32,
173    pub file_key: String,
174    pub created_by: Option<UserId>,
175    pub created_at: DateTime<Utc>,
176    pub encrypted: bool,
177}
178
179impl File {
180    pub async fn all(
181        db: impl DbExecutor<'_>,
182        offset: u64,
183        page_size: u64,
184    ) -> DbResult<Vec<FileWithScope>> {
185        sqlx::query_as(
186            r#"
187            SELECT
188            "file".*,
189            "folder"."document_box" AS "scope"
190            FROM "docbox_files" "file"
191            INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
192            ORDER BY "created_at" ASC
193            OFFSET $1
194            LIMIT $2
195        "#,
196        )
197        .bind(offset as i64)
198        .bind(page_size as i64)
199        .fetch_all(db)
200        .await
201    }
202
203    pub async fn all_by_mime(
204        db: impl DbExecutor<'_>,
205        mime: &str,
206        offset: u64,
207        page_size: u64,
208    ) -> DbResult<Vec<FileWithScope>> {
209        sqlx::query_as(
210            r#"
211            SELECT
212            "file".*,
213            "folder"."document_box" AS "scope"
214            FROM "docbox_files" "file"
215            INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
216            WHERE "file"."mime" = $1
217            ORDER BY "created_at" ASC
218            OFFSET $2
219            LIMIT $3
220        "#,
221        )
222        .bind(mime)
223        .bind(offset as i64)
224        .bind(page_size as i64)
225        .fetch_all(db)
226        .await
227    }
228
229    pub async fn move_to_folder(
230        mut self,
231        db: impl DbExecutor<'_>,
232        folder_id: FolderId,
233    ) -> DbResult<File> {
234        sqlx::query(r#"UPDATE "docbox_files" SET "folder_id" = $1 WHERE "id" = $2"#)
235            .bind(folder_id)
236            .bind(self.id)
237            .execute(db)
238            .await?;
239
240        self.folder_id = folder_id;
241
242        Ok(self)
243    }
244
245    pub async fn rename(mut self, db: impl DbExecutor<'_>, name: String) -> DbResult<File> {
246        sqlx::query(r#"UPDATE "docbox_files" SET "name" = $1 WHERE "id" = $2"#)
247            .bind(name.as_str())
248            .bind(self.id)
249            .execute(db)
250            .await?;
251
252        self.name = name;
253
254        Ok(self)
255    }
256
257    /// Updates the pinned state of the file
258    pub async fn set_pinned(mut self, db: impl DbExecutor<'_>, pinned: bool) -> DbResult<File> {
259        sqlx::query(r#"UPDATE "docbox_files" SET "pinned" = $1 WHERE "id" = $2"#)
260            .bind(pinned)
261            .bind(self.id)
262            .execute(db)
263            .await?;
264
265        self.pinned = pinned;
266
267        Ok(self)
268    }
269
270    /// Updates the encryption state of the file
271    pub async fn set_encrypted(
272        mut self,
273        db: impl DbExecutor<'_>,
274        encrypted: bool,
275    ) -> DbResult<File> {
276        sqlx::query(r#"UPDATE "docbox_files" SET "encrypted" = $1 WHERE "id" = $2"#)
277            .bind(encrypted)
278            .bind(self.id)
279            .execute(db)
280            .await?;
281
282        self.encrypted = encrypted;
283
284        Ok(self)
285    }
286
287    /// Updates the mime type of a file
288    pub async fn set_mime(mut self, db: impl DbExecutor<'_>, mime: String) -> DbResult<File> {
289        sqlx::query(r#"UPDATE "docbox_files" SET "mime" = $1 WHERE "id" = $2"#)
290            .bind(&mime)
291            .bind(self.id)
292            .execute(db)
293            .await?;
294
295        self.mime = mime;
296
297        Ok(self)
298    }
299
300    pub async fn create(
301        db: impl DbExecutor<'_>,
302        CreateFile {
303            id,
304            parent_id,
305            name,
306            mime,
307            folder_id,
308            hash,
309            size,
310            file_key,
311            created_by,
312            created_at,
313            encrypted,
314        }: CreateFile,
315    ) -> DbResult<File> {
316        sqlx::query(
317            r#"INSERT INTO "docbox_files" (
318                    "id", "name", "mime", "folder_id", "hash", "size",
319                    "encrypted", "file_key", "created_by", "created_at",
320                    "parent_id"
321                )
322                VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
323                "#,
324        )
325        .bind(id)
326        .bind(name.as_str())
327        .bind(mime.as_str())
328        .bind(folder_id)
329        .bind(hash.as_str())
330        .bind(size)
331        .bind(encrypted)
332        .bind(file_key.as_str())
333        .bind(created_by.as_ref())
334        .bind(created_at)
335        .bind(parent_id)
336        .execute(db)
337        .await?;
338
339        Ok(File {
340            id,
341            name,
342            mime,
343            folder_id,
344            hash,
345            size,
346            encrypted,
347            file_key,
348            created_by,
349            created_at,
350            parent_id,
351            pinned: false,
352        })
353    }
354
355    pub async fn all_convertable_paged(
356        db: impl DbExecutor<'_>,
357        offset: u64,
358        page_size: u64,
359        convertable_formats: Vec<&str>,
360    ) -> DbResult<Vec<FileWithScope>> {
361        sqlx::query_as(
362            r#"
363            SELECT
364                "file".*,
365                "folder"."document_box" AS "scope"
366            FROM "docbox_files" AS "file"
367            INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
368            WHERE "mime" IS IN $1 AND "file"."encrypted" = FALSE
369            ORDER BY "file"."created_at" ASC
370            OFFSET $2
371            LIMIT $3
372        "#,
373        )
374        .bind(convertable_formats)
375        .bind(offset as i64)
376        .bind(page_size as i64)
377        .fetch_all(db)
378        .await
379    }
380
381    /// Finds a specific file using its full path scope -> folder -> file
382    pub async fn find(
383        db: impl DbExecutor<'_>,
384        scope: &DocumentBoxScopeRaw,
385        file_id: FileId,
386    ) -> DbResult<Option<File>> {
387        sqlx::query_as(
388            r#"
389            SELECT "file".*
390            FROM "docbox_files" AS "file"
391            INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
392            WHERE "file"."id" = $1 AND "folder"."document_box" = $2
393        "#,
394        )
395        .bind(file_id)
396        .bind(scope)
397        .fetch_optional(db)
398        .await
399    }
400
401    /// Collects the IDs and names of all parent folders of the
402    /// provided folder
403    pub async fn resolve_path(
404        db: impl DbExecutor<'_>,
405        file_id: FileId,
406    ) -> DbResult<Vec<FolderPathSegment>> {
407        sqlx::query_as(
408            r#"
409            WITH RECURSIVE "folder_hierarchy" AS (
410                SELECT "id", "name", "folder_id", 0 AS "depth"
411                FROM "docbox_files"
412                WHERE "docbox_files"."id" = $1
413                UNION ALL (
414                    SELECT
415                        "folder"."id",
416                        "folder"."name",
417                        "folder"."folder_id",
418                        "folder_hierarchy"."depth" + 1 as "depth"
419                    FROM "docbox_folders" AS "folder"
420                    INNER JOIN "folder_hierarchy" ON "folder"."id" = "folder_hierarchy"."folder_id"
421                )
422            )
423            CYCLE "id" SET "looped" USING "traversal_path"
424            SELECT "folder_hierarchy"."id", "folder_hierarchy"."name"
425            FROM "folder_hierarchy"
426            WHERE "folder_hierarchy"."id" <> $1
427            ORDER BY "folder_hierarchy"."depth" DESC
428        "#,
429        )
430        .bind(file_id)
431        .fetch_all(db)
432        .await
433    }
434
435    pub async fn find_by_parent(
436        db: impl DbExecutor<'_>,
437        parent_id: FolderId,
438    ) -> DbResult<Vec<File>> {
439        sqlx::query_as(r#"SELECT * FROM "docbox_files" WHERE "folder_id" = $1"#)
440            .bind(parent_id)
441            .fetch_all(db)
442            .await
443    }
444
445    /// Deletes the file
446    pub async fn delete(&self, db: impl DbExecutor<'_>) -> DbResult<PgQueryResult> {
447        sqlx::query(r#"DELETE FROM "docbox_files" WHERE "id" = $1"#)
448            .bind(self.id)
449            .execute(db)
450            .await
451    }
452
453    /// Finds a collection of files that are all within the same document box, resolves
454    /// both the files themselves and the folder path to traverse to get to each file
455    pub async fn resolve_with_extra(
456        db: impl DbExecutor<'_>,
457        scope: &DocumentBoxScopeRaw,
458        file_ids: Vec<Uuid>,
459    ) -> DbResult<Vec<WithFullPath<FileWithExtra>>> {
460        if file_ids.is_empty() {
461            return Ok(Vec::new());
462        }
463
464        sqlx::query_as(
465            r#"
466        -- Recursively resolve the file paths for each file creating a JSON array for the path
467        WITH RECURSIVE "folder_hierarchy" AS (
468            SELECT
469                "f"."id" AS "file_id",
470                "folder"."id" AS "folder_id",
471                "folder"."name" AS "folder_name",
472                "folder"."folder_id" AS "parent_folder_id",
473                0 AS "depth",
474                jsonb_build_array(jsonb_build_object('id', "folder"."id", 'name', "folder"."name")) AS "path"
475            FROM "docbox_files" "f"
476            JOIN "docbox_folders" "folder" ON "f"."folder_id" = "folder"."id"
477            WHERE "f"."id" = ANY($1::uuid[]) AND "folder"."document_box" = $2
478            UNION ALL
479            SELECT
480                "fh"."file_id",
481                "parent"."id",
482                "parent"."name",
483                "parent"."folder_id",
484                "fh"."depth" + 1,
485                jsonb_build_array(jsonb_build_object('id', "parent"."id", 'name', "parent"."name")) || "fh"."path"
486            FROM "folder_hierarchy" "fh"
487            JOIN "docbox_folders" "parent" ON "fh"."parent_folder_id" = "parent"."id"
488        ),
489        "folder_paths" AS (
490            SELECT "file_id", "path", ROW_NUMBER() OVER (PARTITION BY "file_id" ORDER BY "depth" DESC) AS "rn"
491            FROM "folder_hierarchy"
492        )
493        SELECT
494            -- File itself
495            "file".*,
496            -- Creator user details
497            "cu"."id" AS "cb_id",
498            "cu"."name" AS "cb_name",
499            "cu"."image_id" AS "cb_image_id",
500            -- Last modified date
501            "ehl"."created_at" AS "last_modified_at",
502            -- Last modified user details
503            "mu"."id" AS "lmb_id",
504            "mu"."name" AS "lmb_name",
505            "mu"."image_id" AS "lmb_image_id" ,
506            -- File path from path lookup
507            "fp"."path" AS "full_path"
508        FROM "docbox_files" AS "file"
509        -- Join on the creator
510        LEFT JOIN "docbox_users" AS "cu"
511            ON "file"."created_by" = "cu"."id"
512        -- Join on the parent folder
513        INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
514        -- Join on the edit history (Latest only)
515        LEFT JOIN (
516            -- Get the latest edit history entry
517            SELECT DISTINCT ON ("file_id") "file_id", "user_id", "created_at"
518            FROM "docbox_edit_history"
519            ORDER BY "file_id", "created_at" DESC
520        ) AS "ehl" ON "file"."id" = "ehl"."file_id"
521        -- Join on the editor history latest edit user
522        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id"
523        -- Join on the resolved folder path
524        LEFT JOIN "folder_paths" "fp" ON "file".id = "fp"."file_id" AND "fp".rn = 1
525        WHERE "file"."id" = ANY($1::uuid[]) AND "folder"."document_box" = $2"#,
526        )
527        .bind(file_ids)
528        .bind(scope)
529        .fetch_all(db)
530        .await
531    }
532
533    /// Finds a collection of files that are within various document box scopes, resolves
534    /// both the files themselves and the folder path to traverse to get to each file
535    pub async fn resolve_with_extra_mixed_scopes(
536        db: impl DbExecutor<'_>,
537        files_scope_with_id: Vec<(DocumentBoxScopeRaw, FileId)>,
538    ) -> DbResult<Vec<WithFullPathScope<FileWithExtra>>> {
539        if files_scope_with_id.is_empty() {
540            return Ok(Vec::new());
541        }
542
543        let (scopes, file_ids): (Vec<String>, Vec<FileId>) =
544            files_scope_with_id.into_iter().unzip();
545
546        sqlx::query_as(
547            r#"
548        -- Recursively resolve the file paths for each file creating a JSON array for the path
549        WITH RECURSIVE
550            "input_files" AS (
551                SELECT file_id, document_box
552                FROM UNNEST($1::text[], $2::uuid[]) AS t(document_box, file_id)
553            ),
554            "folder_hierarchy" AS (
555                SELECT
556                    "f"."id" AS "file_id",
557                    "folder"."id" AS "folder_id",
558                    "folder"."name" AS "folder_name",
559                    "folder"."folder_id" AS "parent_folder_id",
560                    0 AS "depth",
561                    jsonb_build_array(jsonb_build_object('id', "folder"."id", 'name', "folder"."name")) AS "path"
562                FROM "docbox_files" "f"
563                JOIN "input_files" "i" ON "f"."id" = "i"."file_id"
564                JOIN "docbox_folders" "folder" ON "f"."folder_id" = "folder"."id"
565                WHERE "folder"."document_box" = "i"."document_box"
566                UNION ALL
567                SELECT
568                    "fh"."file_id",
569                    "parent"."id",
570                    "parent"."name",
571                    "parent"."folder_id",
572                    "fh"."depth" + 1,
573                    jsonb_build_array(jsonb_build_object('id', "parent"."id", 'name', "parent"."name")) || "fh"."path"
574                FROM "folder_hierarchy" "fh"
575                JOIN "docbox_folders" "parent" ON "fh"."parent_folder_id" = "parent"."id"
576            ),
577            "folder_paths" AS (
578                SELECT "file_id", "path", ROW_NUMBER() OVER (PARTITION BY "file_id" ORDER BY "depth" DESC) AS "rn"
579                FROM "folder_hierarchy"
580            )
581        SELECT
582            -- File itself
583            "file".*,
584            -- Creator user details
585            "cu"."id" AS "cb_id",
586            "cu"."name" AS "cb_name",
587            "cu"."image_id" AS "cb_image_id",
588            -- Last modified date
589            "ehl"."created_at" AS "last_modified_at",
590            -- Last modified user details
591            "mu"."id" AS "lmb_id",
592            "mu"."name" AS "lmb_name",
593            "mu"."image_id" AS "lmb_image_id" ,
594            -- File path from path lookup
595            "fp"."path" AS "full_path",
596            -- Include document box in response
597            "folder"."document_box" AS "document_box"
598        FROM "docbox_files" AS "file"
599        -- Join on the creator
600        LEFT JOIN "docbox_users" AS "cu"
601            ON "file"."created_by" = "cu"."id"
602        -- Join on the parent folder
603        INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
604        -- Join on the edit history (Latest only)
605        LEFT JOIN (
606            -- Get the latest edit history entry
607            SELECT DISTINCT ON ("file_id") "file_id", "user_id", "created_at"
608            FROM "docbox_edit_history"
609            ORDER BY "file_id", "created_at" DESC
610        ) AS "ehl" ON "file"."id" = "ehl"."file_id"
611        -- Join on the editor history latest edit user
612        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id"
613        -- Join on the resolved folder path
614        LEFT JOIN "folder_paths" "fp" ON "file".id = "fp"."file_id" AND "fp".rn = 1
615        -- Join on the input files for filtering
616        JOIN "input_files" "i" ON "file"."id" = "i"."file_id"
617        -- Ensure correct document box
618        WHERE "folder"."document_box" = "i"."document_box""#,
619        )
620        .bind(scopes)
621        .bind(file_ids)
622        .fetch_all(db)
623        .await
624    }
625
626    /// Finds a specific file using its full path scope -> folder -> file
627    /// fetching the additional details about the file like the creator and
628    /// last modified
629    pub async fn find_with_extra(
630        db: impl DbExecutor<'_>,
631        scope: &DocumentBoxScopeRaw,
632        file_id: FileId,
633    ) -> DbResult<Option<FileWithExtra>> {
634        sqlx::query_as(
635            r#"
636        SELECT
637            -- File itself
638            "file".*,
639            -- Creator user details
640            "cu"."id" AS "cb_id",
641            "cu"."name" AS "cb_name",
642            "cu"."image_id" AS "cb_image_id",
643            -- Last modified date
644            "ehl"."created_at" AS "last_modified_at",
645            -- Last modified user details
646            "mu"."id" AS "lmb_id",
647            "mu"."name" AS "lmb_name",
648            "mu"."image_id" AS "lmb_image_id"
649        FROM "docbox_files" AS "file"
650        -- Join on the creator
651        LEFT JOIN "docbox_users" AS "cu"
652            ON "file"."created_by" = "cu"."id"
653        -- Join on the parent folder
654        INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
655        -- Join on the edit history (Latest only)
656        LEFT JOIN (
657            -- Get the latest edit history entry
658            SELECT DISTINCT ON ("file_id") "file_id", "user_id", "created_at"
659            FROM "docbox_edit_history"
660            ORDER BY "file_id", "created_at" DESC
661        ) AS "ehl" ON "file"."id" = "ehl"."file_id"
662        -- Join on the editor history latest edit user
663        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id"
664        WHERE "file"."id" = $1 AND "folder"."document_box" = $2"#,
665        )
666        .bind(file_id)
667        .bind(scope)
668        .fetch_optional(db)
669        .await
670    }
671
672    pub async fn find_by_parent_folder_with_extra(
673        db: impl DbExecutor<'_>,
674        parent_id: FolderId,
675    ) -> DbResult<Vec<FileWithExtra>> {
676        sqlx::query_as(
677            r#"
678        SELECT
679            -- File itself
680            "file".*,
681            -- Creator user details
682            "cu"."id" AS "cb_id",
683            "cu"."name" AS "cb_name",
684            "cu"."image_id" AS "cb_image_id",
685            -- Last modified date
686            "ehl"."created_at" AS "last_modified_at",
687            -- Last modified user details
688            "mu"."id" AS "lmb_id",
689            "mu"."name" AS "lmb_name",
690            "mu"."image_id" AS "lmb_image_id"
691        FROM "docbox_files" AS "file"
692        -- Join on the creator
693        LEFT JOIN "docbox_users" AS "cu"
694            ON "file"."created_by" = "cu"."id"
695        -- Join on the edit history (Latest only)
696        LEFT JOIN (
697            -- Get the latest edit history entry
698            SELECT DISTINCT ON ("file_id") "file_id", "user_id", "created_at"
699            FROM "docbox_edit_history"
700            ORDER BY "file_id", "created_at" DESC
701        ) AS "ehl" ON "file"."id" = "ehl"."file_id"
702        -- Join on the editor history latest edit user
703        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id"
704        WHERE "file"."folder_id" = $1"#,
705        )
706        .bind(parent_id)
707        .fetch_all(db)
708        .await
709    }
710
711    pub async fn find_by_parent_file_with_extra(
712        db: impl DbExecutor<'_>,
713        parent_id: FileId,
714    ) -> DbResult<Vec<FileWithExtra>> {
715        sqlx::query_as(
716            r#"
717        SELECT
718            -- File itself
719            "file".*,
720            -- Creator user details
721            "cu"."id" AS "cb_id",
722            "cu"."name" AS "cb_name",
723            "cu"."image_id" AS "cb_image_id",
724            -- Last modified date
725            "ehl"."created_at" AS "last_modified_at",
726            -- Last modified user details
727            "mu"."id" AS "lmb_id",
728            "mu"."name" AS "lmb_name",
729            "mu"."image_id" AS "lmb_image_id"
730        FROM "docbox_files" AS "file"
731        -- Join on the creator
732        LEFT JOIN "docbox_users" AS "cu"
733            ON "file"."created_by" = "cu"."id"
734        -- Join on the edit history (Latest only)
735        LEFT JOIN (
736            -- Get the latest edit history entry
737            SELECT DISTINCT ON ("file_id") "file_id", "user_id", "created_at"
738            FROM "docbox_edit_history"
739            ORDER BY "file_id", "created_at" DESC
740        ) AS "ehl" ON "file"."id" = "ehl"."file_id"
741        -- Join on the editor history latest edit user
742        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id"
743        WHERE "file"."parent_id" = $1"#,
744        )
745        .bind(parent_id)
746        .fetch_all(db)
747        .await
748    }
749
750    /// Get the total number of files in the tenant
751    pub async fn total_count(db: impl DbExecutor<'_>) -> DbResult<i64> {
752        let count_result: CountResult =
753            sqlx::query_as(r#"SELECT COUNT(*) AS "count" FROM "docbox_files""#)
754                .fetch_one(db)
755                .await?;
756
757        Ok(count_result.count)
758    }
759
760    /// Get the total "size" of files within the current tenant, this does not include
761    /// the size of generated files
762    pub async fn total_size(db: impl DbExecutor<'_>) -> DbResult<i64> {
763        let size_result: TotalSizeResult = sqlx::query_as(
764            r#"
765            SELECT COALESCE(SUM("file"."size"), 0) AS "total_size"
766            FROM "docbox_files" "file";
767        "#,
768        )
769        .fetch_one(db)
770        .await?;
771
772        Ok(size_result.total_size)
773    }
774
775    /// Get the total "size" of files within a specific scope, this does not include
776    /// the size of generated files
777    pub async fn total_size_within_scope(
778        db: impl DbExecutor<'_>,
779        scope: DocumentBoxScopeRawRef<'_>,
780    ) -> DbResult<i64> {
781        let size_result: TotalSizeResult = sqlx::query_as(
782            r#"
783            SELECT COALESCE(SUM("file"."size"), 0) AS "total_size"
784            FROM "docbox_files" "file"
785            INNER JOIN "docbox_folders" "folder" ON "file"."folder_id" = "folder"."id"
786            WHERE "folder"."document_box" = $1;
787        "#,
788        )
789        .bind(scope)
790        .fetch_one(db)
791        .await?;
792
793        Ok(size_result.total_size)
794    }
795}
docbox_database/models/file.rs

docbox_database/models/
file.rs
