docbox_database/models/
link.rs

1use super::{
2    document_box::DocumentBoxScopeRaw,
3    folder::{FolderId, FolderPathSegment},
4    user::{User, UserId},
5};
6use crate::{
7    DbExecutor, DbResult,
8    models::{
9        folder::{WithFullPath, WithFullPathScope},
10        shared::CountResult,
11    },
12};
13use chrono::{DateTime, Utc};
14use serde::Serialize;
15use sqlx::{
16    postgres::{PgQueryResult, PgRow},
17    prelude::FromRow,
18};
19use utoipa::ToSchema;
20use uuid::Uuid;
21
22pub type LinkId = Uuid;
23
24#[derive(Debug, Clone, FromRow, Serialize)]
25pub struct Link {
26    /// Unique identifier for the link
27    pub id: LinkId,
28    /// Name of the link
29    pub name: String,
30    /// value of the link
31    pub value: String,
32    /// Whether the link is pinned
33    pub pinned: bool,
34    /// Parent folder ID
35    pub folder_id: FolderId,
36    /// When the link was created
37    pub created_at: DateTime<Utc>,
38    /// User who created the link
39    pub created_by: Option<UserId>,
40}
41
42#[derive(Debug, FromRow, Serialize)]
43pub struct LinkWithScope {
44    #[sqlx(flatten)]
45    pub link: Link,
46    pub scope: String,
47}
48
49#[derive(Debug, Clone, Serialize, FromRow, ToSchema)]
50pub struct LinkWithExtra {
51    /// Unique identifier for the link
52    pub id: Uuid,
53    /// Name of the link
54    pub name: String,
55    /// value of the link
56    pub value: String,
57    /// Whether the link is pinned
58    pub pinned: bool,
59    /// Parent folder ID
60    pub folder_id: Uuid,
61    /// When the link was created
62    pub created_at: DateTime<Utc>,
63    /// User who created the link
64    #[sqlx(flatten)]
65    #[schema(nullable, value_type = User)]
66    pub created_by: CreatedByUser,
67    /// Last time the link was modified
68    pub last_modified_at: Option<DateTime<Utc>>,
69    /// User who last modified the link
70    #[sqlx(flatten)]
71    #[schema(nullable, value_type = User)]
72    pub last_modified_by: LastModifiedByUser,
73}
74
75/// Link with extra with an additional resolved full path
76#[derive(Debug, FromRow, Serialize, ToSchema)]
77pub struct ResolvedLinkWithExtra {
78    #[serde(flatten)]
79    #[sqlx(flatten)]
80    pub link: LinkWithExtra,
81    #[sqlx(json)]
82    pub full_path: Vec<FolderPathSegment>,
83}
84
85/// Wrapper type for extracting a [User] that was joined
86/// from another table where the fields are prefixed with "cb_"
87#[derive(Debug, Clone, Serialize)]
88#[serde(transparent)]
89pub struct CreatedByUser(pub Option<User>);
90
91impl<'r> FromRow<'r, PgRow> for CreatedByUser {
92    fn from_row(row: &'r PgRow) -> Result<Self, sqlx::Error> {
93        use sqlx::Row;
94
95        let id: Option<UserId> = row.try_get("cb_id")?;
96        if let Some(id) = id {
97            let name: Option<String> = row.try_get("cb_name")?;
98            let image_id: Option<String> = row.try_get("cb_image_id")?;
99            return Ok(CreatedByUser(Some(User { id, name, image_id })));
100        }
101
102        Ok(CreatedByUser(None))
103    }
104}
105
106/// Wrapper type for extracting a [User] that was joined
107/// from another table where the fields are prefixed with "lmb_"
108#[derive(Debug, Clone, Serialize)]
109#[serde(transparent)]
110pub struct LastModifiedByUser(pub Option<User>);
111
112impl<'r> FromRow<'r, PgRow> for LastModifiedByUser {
113    fn from_row(row: &'r PgRow) -> Result<Self, sqlx::Error> {
114        use sqlx::Row;
115
116        let id: Option<UserId> = row.try_get("lmb_id")?;
117        if let Some(id) = id {
118            let name: Option<String> = row.try_get("lmb_name")?;
119            let image_id: Option<String> = row.try_get("lmb_image_id")?;
120            return Ok(LastModifiedByUser(Some(User { id, name, image_id })));
121        }
122
123        Ok(LastModifiedByUser(None))
124    }
125}
126
127pub struct CreateLink {
128    pub name: String,
129    pub value: String,
130    pub folder_id: FolderId,
131    pub created_by: Option<UserId>,
132}
133
134impl Link {
135    pub async fn create(
136        db: impl DbExecutor<'_>,
137        CreateLink {
138            name,
139            value,
140            folder_id,
141            created_by,
142        }: CreateLink,
143    ) -> DbResult<Link> {
144        let id = Uuid::new_v4();
145        let created_at = Utc::now();
146
147        sqlx::query(
148            r#"INSERT INTO "docbox_links" (
149                "id",
150                "name",
151                "value",
152                "folder_id",
153                "created_by",
154                "created_at"
155            ) VALUES ($1, $2, $3, $4, $5, $6)
156            "#,
157        )
158        .bind(id)
159        .bind(name.clone())
160        .bind(value.clone())
161        .bind(folder_id)
162        .bind(created_by.as_ref())
163        .bind(created_at)
164        .execute(db)
165        .await?;
166
167        Ok(Link {
168            id,
169            name,
170            value,
171            folder_id,
172            created_by,
173            created_at,
174            pinned: false,
175        })
176    }
177
178    pub async fn move_to_folder(
179        mut self,
180        db: impl DbExecutor<'_>,
181        folder_id: FolderId,
182    ) -> DbResult<Link> {
183        sqlx::query(r#"UPDATE "docbox_links" SET "folder_id" = $1 WHERE "id" = $2"#)
184            .bind(folder_id)
185            .bind(self.id)
186            .execute(db)
187            .await?;
188
189        self.folder_id = folder_id;
190
191        Ok(self)
192    }
193
194    pub async fn rename(mut self, db: impl DbExecutor<'_>, name: String) -> DbResult<Link> {
195        sqlx::query(r#"UPDATE "docbox_links" SET "name" = $1 WHERE "id" = $2"#)
196            .bind(name.as_str())
197            .bind(self.id)
198            .execute(db)
199            .await?;
200
201        self.name = name;
202        Ok(self)
203    }
204
205    pub async fn set_pinned(mut self, db: impl DbExecutor<'_>, pinned: bool) -> DbResult<Link> {
206        sqlx::query(r#"UPDATE "docbox_links" SET "pinned" = $1 WHERE "id" = $2"#)
207            .bind(pinned)
208            .bind(self.id)
209            .execute(db)
210            .await?;
211
212        self.pinned = pinned;
213        Ok(self)
214    }
215
216    pub async fn update_value(mut self, db: impl DbExecutor<'_>, value: String) -> DbResult<Link> {
217        sqlx::query(r#"UPDATE "docbox_links" SET "value" = $1 WHERE "id" = $2"#)
218            .bind(value.as_str())
219            .bind(self.id)
220            .execute(db)
221            .await?;
222
223        self.value = value;
224
225        Ok(self)
226    }
227
228    pub async fn all(
229        db: impl DbExecutor<'_>,
230        offset: u64,
231        page_size: u64,
232    ) -> DbResult<Vec<LinkWithScope>> {
233        sqlx::query_as(
234            r#"
235            SELECT
236            "link".*,
237            "folder"."document_box" AS "scope"
238            FROM "docbox_links" AS "link"
239            INNER JOIN "docbox_folders" "folder" ON "link"."folder_id" = "folder"."id"
240            ORDER BY "link"."created_at" ASC
241            OFFSET $1
242            LIMIT $2
243        "#,
244        )
245        .bind(offset as i64)
246        .bind(page_size as i64)
247        .fetch_all(db)
248        .await
249    }
250
251    pub async fn find(
252        db: impl DbExecutor<'_>,
253        scope: &DocumentBoxScopeRaw,
254        link_id: LinkId,
255    ) -> DbResult<Option<Link>> {
256        sqlx::query_as(
257            r#"
258            SELECT "link".*
259            FROM "docbox_links" AS "link"
260            INNER JOIN "docbox_folders" "folder" ON "link"."folder_id" = "folder"."id"
261            WHERE "link"."id" = $1 AND "folder"."document_box" = $2
262        "#,
263        )
264        .bind(link_id)
265        .bind(scope)
266        .fetch_optional(db)
267        .await
268    }
269    /// Collects the IDs and names of all parent folders of the
270    /// provided folder
271    pub async fn resolve_path(
272        db: impl DbExecutor<'_>,
273        link_id: LinkId,
274    ) -> DbResult<Vec<FolderPathSegment>> {
275        sqlx::query_as(
276            r#"
277            WITH RECURSIVE "folder_hierarchy" AS (
278                SELECT "id", "name", "folder_id", 0 AS "depth"
279                FROM "docbox_links"
280                WHERE "docbox_links"."id" = $1
281                UNION ALL (
282                    SELECT
283                        "folder"."id",
284                        "folder"."name",
285                        "folder"."folder_id",
286                        "folder_hierarchy"."depth" + 1 as "depth"
287                    FROM "docbox_folders" AS "folder"
288                    INNER JOIN "folder_hierarchy" ON "folder"."id" = "folder_hierarchy"."folder_id"
289                )
290            )
291            CYCLE "id" SET "looped" USING "traversal_path"
292            SELECT "folder_hierarchy"."id", "folder_hierarchy"."name"
293            FROM "folder_hierarchy"
294            WHERE "folder_hierarchy"."id" <> $1
295            ORDER BY "folder_hierarchy"."depth" DESC
296        "#,
297        )
298        .bind(link_id)
299        .fetch_all(db)
300        .await
301    }
302
303    /// Finds all links within the provided parent folder
304    pub async fn find_by_parent(
305        db: impl DbExecutor<'_>,
306        parent_id: FolderId,
307    ) -> DbResult<Vec<Link>> {
308        sqlx::query_as(r#"SELECT * FROM "docbox_links" WHERE "folder_id" = $1"#)
309            .bind(parent_id)
310            .fetch_all(db)
311            .await
312    }
313
314    /// Deletes the link
315    pub async fn delete(&self, db: impl DbExecutor<'_>) -> DbResult<PgQueryResult> {
316        sqlx::query(r#"DELETE FROM "docbox_links" WHERE "id" = $1"#)
317            .bind(self.id)
318            .execute(db)
319            .await
320    }
321
322    /// Finds a collection of links that are within various document box scopes, resolves
323    /// both the links themselves and the folder path to traverse to get to each link
324    pub async fn resolve_with_extra_mixed_scopes(
325        db: impl DbExecutor<'_>,
326        links_scope_with_id: Vec<(DocumentBoxScopeRaw, LinkId)>,
327    ) -> DbResult<Vec<WithFullPathScope<LinkWithExtra>>> {
328        if links_scope_with_id.is_empty() {
329            return Ok(Vec::new());
330        }
331
332        let (scopes, link_ids): (Vec<String>, Vec<LinkId>) =
333            links_scope_with_id.into_iter().unzip();
334
335        sqlx::query_as(
336            r#"
337        -- Recursively resolve the link paths for each link creating a JSON array for the path
338        WITH RECURSIVE
339            "input_links" AS (
340                SELECT link_id, document_box
341                FROM UNNEST($1::text[], $2::uuid[]) AS t(document_box, link_id)
342            ),
343            "folder_hierarchy" AS (
344                SELECT
345                    "f"."id" AS "link_id",
346                    "folder"."id" AS "folder_id",
347                    "folder"."name" AS "folder_name",
348                    "folder"."folder_id" AS "parent_folder_id",
349                    0 AS "depth",
350                    jsonb_build_array(jsonb_build_object('id', "folder"."id", 'name', "folder"."name")) AS "path"
351                FROM "docbox_links" "f"
352                JOIN "input_links" "i" ON "f"."id" = "i"."link_id"
353                JOIN "docbox_folders" "folder" ON "f"."folder_id" = "folder"."id"
354                WHERE "folder"."document_box" = "i"."document_box"
355                UNION ALL
356                SELECT
357                    "fh"."link_id",
358                    "parent"."id",
359                    "parent"."name",
360                    "parent"."folder_id",
361                    "fh"."depth" + 1,
362                    jsonb_build_array(jsonb_build_object('id', "parent"."id", 'name', "parent"."name")) || "fh"."path"
363                FROM "folder_hierarchy" "fh"
364                JOIN "docbox_folders" "parent" ON "fh"."parent_folder_id" = "parent"."id"
365            ),
366            "folder_paths" AS (
367                SELECT "link_id", "path", ROW_NUMBER() OVER (PARTITION BY "link_id" ORDER BY "depth" DESC) AS "rn"
368                FROM "folder_hierarchy"
369            )
370        SELECT
371            -- link itself
372            "link".*,
373            -- Creator user details
374            "cu"."id" AS "cb_id",
375            "cu"."name" AS "cb_name",
376            "cu"."image_id" AS "cb_image_id",
377            -- Last modified date
378            "ehl"."created_at" AS "last_modified_at",
379            -- Last modified user details
380            "mu"."id" AS "lmb_id",
381            "mu"."name" AS "lmb_name",
382            "mu"."image_id" AS "lmb_image_id" ,
383            -- link path from path lookup
384            "fp"."path" AS "full_path",
385            -- Include document box in response
386            "folder"."document_box" AS "document_box"
387        FROM "docbox_links" AS "link"
388        -- Join on the creator
389        LEFT JOIN "docbox_users" AS "cu"
390            ON "link"."created_by" = "cu"."id"
391        -- Join on the parent folder
392        INNER JOIN "docbox_folders" "folder" ON "link"."folder_id" = "folder"."id"
393        -- Join on the edit history (Latest only)
394        LEFT JOIN (
395            -- Get the latest edit history entry
396            SELECT DISTINCT ON ("link_id") "link_id", "user_id", "created_at"
397            FROM "docbox_edit_history"
398            ORDER BY "link_id", "created_at" DESC
399        ) AS "ehl" ON "link"."id" = "ehl"."link_id"
400        -- Join on the editor history latest edit user
401        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id"
402        -- Join on the resolved folder path
403        LEFT JOIN "folder_paths" "fp" ON "link".id = "fp"."link_id" AND "fp".rn = 1
404        -- Join on the input files for filtering
405        JOIN "input_links" "i" ON "link"."id" = "i"."link_id"
406        -- Ensure correct document box
407        WHERE "folder"."document_box" = "i"."document_box""#,
408        )
409        .bind(scopes)
410        .bind(link_ids)
411        .fetch_all(db)
412        .await
413    }
414
415    /// Finds a collection of links that are all within the same document box, resolves
416    /// both the links themselves and the folder path to traverse to get to each link
417    pub async fn resolve_with_extra(
418        db: impl DbExecutor<'_>,
419        scope: &DocumentBoxScopeRaw,
420        link_ids: Vec<Uuid>,
421    ) -> DbResult<Vec<WithFullPath<LinkWithExtra>>> {
422        if link_ids.is_empty() {
423            return Ok(Vec::new());
424        }
425
426        sqlx::query_as(
427            r#"
428        -- Recursively resolve the link paths for each link creating a JSON array for the path
429        WITH RECURSIVE "folder_hierarchy" AS (
430            SELECT
431                "f"."id" AS "link_id",
432                "folder"."id" AS "folder_id",
433                "folder"."name" AS "folder_name",
434                "folder"."folder_id" AS "parent_folder_id",
435                0 AS "depth",
436                jsonb_build_array(jsonb_build_object('id', "folder"."id", 'name', "folder"."name")) AS "path"
437            FROM "docbox_links" "f"
438            JOIN "docbox_folders" "folder" ON "f"."folder_id" = "folder"."id"
439            WHERE "f"."id" = ANY($1::uuid[]) AND "folder"."document_box" = $2
440            UNION ALL
441            SELECT
442                "fh"."link_id",
443                "parent"."id",
444                "parent"."name",
445                "parent"."folder_id",
446                "fh"."depth" + 1,
447                jsonb_build_array(jsonb_build_object('id', "parent"."id", 'name', "parent"."name")) || "fh"."path"
448            FROM "folder_hierarchy" "fh"
449            JOIN "docbox_folders" "parent" ON "fh"."parent_folder_id" = "parent"."id"
450        ),
451        "folder_paths" AS (
452            SELECT "link_id", "path", ROW_NUMBER() OVER (PARTITION BY "link_id" ORDER BY "depth" DESC) AS "rn"
453            FROM "folder_hierarchy"
454        )
455        SELECT
456            -- link itself
457            "link".*,
458            -- Creator user details
459            "cu"."id" AS "cb_id",
460            "cu"."name" AS "cb_name",
461            "cu"."image_id" AS "cb_image_id",
462            -- Last modified date
463            "ehl"."created_at" AS "last_modified_at",
464            -- Last modified user details
465            "mu"."id" AS "lmb_id",
466            "mu"."name" AS "lmb_name",
467            "mu"."image_id" AS "lmb_image_id" ,
468            -- link path from path lookup
469            "fp"."path" AS "full_path"
470        FROM "docbox_links" AS "link"
471        -- Join on the creator
472        LEFT JOIN "docbox_users" AS "cu"
473            ON "link"."created_by" = "cu"."id"
474        -- Join on the parent folder
475        INNER JOIN "docbox_folders" "folder" ON "link"."folder_id" = "folder"."id"
476        -- Join on the edit history (Latest only)
477        LEFT JOIN (
478            -- Get the latest edit history entry
479            SELECT DISTINCT ON ("link_id") "link_id", "user_id", "created_at"
480            FROM "docbox_edit_history"
481            ORDER BY "link_id", "created_at" DESC
482        ) AS "ehl" ON "link"."id" = "ehl"."link_id"
483        -- Join on the editor history latest edit user
484        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id"
485        -- Join on the resolved folder path
486        LEFT JOIN "folder_paths" "fp" ON "link".id = "fp"."link_id" AND "fp".rn = 1
487        WHERE "link"."id" = ANY($1::uuid[]) AND "folder"."document_box" = $2"#,
488        )
489        .bind(link_ids)
490        .bind(scope)
491        .fetch_all(db)
492        .await
493    }
494
495    /// Finds all links within the provided parent folder
496    pub async fn find_by_parent_with_extra(
497        db: impl DbExecutor<'_>,
498        parent_id: FolderId,
499    ) -> DbResult<Vec<LinkWithExtra>> {
500        sqlx::query_as(
501            r#"
502        SELECT
503            -- Link itself details
504            "link".*,
505            -- Creator user details
506            "cu"."id" AS "cb_id",
507            "cu"."name" AS "cb_name",
508            "cu"."image_id" AS "cb_image_id",
509            -- Last modified date
510            "ehl"."created_at" AS "last_modified_at",
511            -- Last modified user details
512            "mu"."id" AS "lmb_id",
513            "mu"."name" AS "lmb_name",
514            "mu"."image_id" AS "lmb_image_id"
515        FROM "docbox_links" AS "link"
516        -- Join on the creator
517        LEFT JOIN "docbox_users" AS "cu"
518            ON "link"."created_by" = "cu"."id"
519        -- Join on the edit history (Latest only)
520        LEFT JOIN LATERAL (
521            -- Get the latest edit history entry
522            SELECT DISTINCT ON ("link_id") "link_id", "user_id", "created_at"
523            FROM "docbox_edit_history"
524            ORDER BY "link_id", "created_at" DESC
525        ) AS "ehl" ON "link"."id" = "ehl"."link_id"
526        -- Join on the editor history latest edit user
527        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id"
528        WHERE "link"."folder_id" = $1"#,
529        )
530        .bind(parent_id)
531        .fetch_all(db)
532        .await
533    }
534
535    pub async fn find_with_extra(
536        db: impl DbExecutor<'_>,
537        scope: &DocumentBoxScopeRaw,
538        link_id: LinkId,
539    ) -> DbResult<Option<LinkWithExtra>> {
540        sqlx::query_as(
541            r#"
542        SELECT
543            -- Link itself details
544            "link".*,
545            -- Creator user details
546            "cu"."id" AS "cb_id",
547            "cu"."name" AS "cb_name",
548            "cu"."image_id" AS "cb_image_id",
549            -- Last modified date
550            "ehl"."created_at" AS "last_modified_at",
551            -- Last modified user details
552            "mu"."id" AS "lmb_id",
553            "mu"."name" AS "lmb_name",
554            "mu"."image_id" AS "lmb_image_id"
555        FROM "docbox_links" AS "link"
556        -- Join on the creator
557        LEFT JOIN "docbox_users" AS "cu"
558            ON "link"."created_by" = "cu"."id"
559        -- Join on the parent folder
560        INNER JOIN "docbox_folders" "folder" ON "link"."folder_id" = "folder"."id"
561        -- Join on the edit history (Latest only)
562        LEFT JOIN (
563            -- Get the latest edit history entry
564            SELECT DISTINCT ON ("link_id") "link_id", "user_id", "created_at"
565            FROM "docbox_edit_history"
566            ORDER BY "link_id", "created_at" DESC
567        ) AS "ehl" ON "link"."id" = "ehl"."link_id"
568        -- Join on the editor history latest edit user
569        LEFT JOIN "docbox_users" AS "mu" ON "ehl"."user_id" = "mu"."id"
570        WHERE "link"."id" = $1 AND "folder"."document_box" = $2"#,
571        )
572        .bind(link_id)
573        .bind(scope)
574        .fetch_optional(db)
575        .await
576    }
577
578    /// Get the total number of folders in the tenant
579    pub async fn total_count(db: impl DbExecutor<'_>) -> DbResult<i64> {
580        let count_result: CountResult =
581            sqlx::query_as(r#"SELECT COUNT(*) AS "count" FROM "docbox_links""#)
582                .fetch_one(db)
583                .await?;
584
585        Ok(count_result.count)
586    }
587}
docbox_database/models/link.rs

docbox_database/models/
link.rs
