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

docbox_database/models/
link.rs
