docbox-database 0.11.1

Docbox database structures, logic, and migrations
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

use crate::{DbExecutor, DbResult, models::shared::CountResult};
use serde::Serialize;
use sqlx::{postgres::PgQueryResult, prelude::FromRow};
use utoipa::ToSchema;

pub type UserId = String;

#[derive(Debug, Clone, Serialize, FromRow, ToSchema, sqlx::Type, PartialEq, Eq)]
#[sqlx(type_name = "docbox_user")]
pub struct User {
    /// Unique ID of the user
    pub id: String,
    /// Last saved name for the user
    pub name: Option<String>,
    /// Last saved image ID for the user
    pub image_id: Option<String>,
}

impl User {
    /// Stores / updates the stored user data, returns back the user ID
    pub async fn store(
        db: impl DbExecutor<'_>,
        id: UserId,
        name: Option<String>,
        image_id: Option<String>,
    ) -> DbResult<User> {
        sqlx::query(
            r#"
            INSERT INTO "docbox_users" ("id", "name", "image_id")
            VALUES ($1, $2, $3)
            ON CONFLICT ("id")
            DO UPDATE SET "name" = EXCLUDED."name", "image_id" = EXCLUDED."image_id"
        "#,
        )
        .bind(id.as_str())
        .bind(name.as_ref())
        .bind(image_id.as_ref())
        .execute(db)
        .await?;

        Ok(User { id, name, image_id })
    }

    /// Find a user by ID
    pub async fn find(db: impl DbExecutor<'_>, id: &str) -> DbResult<Option<User>> {
        sqlx::query_as(r#"SELECT * FROM "docbox_users" WHERE "id" = $1"#)
            .bind(id)
            .fetch_optional(db)
            .await
    }

    /// Get a page from the users list
    pub async fn query(db: impl DbExecutor<'_>, offset: u64, limit: u64) -> DbResult<Vec<User>> {
        sqlx::query_as(
            r#"
            SELECT * FROM "docbox_users"
            ORDER BY "id" DESC
            OFFSET $1 LIMIT $2"#,
        )
        .bind(offset as i64)
        .bind(limit as i64)
        .fetch_all(db)
        .await
    }

    /// Get the total number of users
    pub async fn total(db: impl DbExecutor<'_>) -> DbResult<i64> {
        let result: CountResult =
            sqlx::query_as(r#"SELECT COUNT(*) as "count" FROM "docbox_users""#)
                .fetch_one(db)
                .await?;

        Ok(result.count)
    }

    /// Delete a user
    pub async fn delete(self, db: impl DbExecutor<'_>) -> DbResult<PgQueryResult> {
        sqlx::query(r#"DELETE FROM "docbox_users" WHERE "id" = $1"#)
            .bind(self.id)
            .execute(db)
            .await
    }
}