sbol_db_sqlite/repo/
users.rs

1//! The account store over SQLite.
2//!
3//! Mirrors the Postgres lookup semantics: an exact username wins over email and
4//! an ambiguous email is rejected rather than choosing an arbitrary account.
5//! The development SQLite schema still keeps emails unique; production classic
6//! migration targets Postgres.
7//! UUIDs are TEXT and booleans are INTEGER (0/1).
8
9use async_trait::async_trait;
10use chrono::Utc;
11use sbol_db_core::{DomainError, NewUser, User, UserId};
12use sbol_db_storage::{UserListQuery, UserPage, UserStore};
13use sqlx::Row;
14
15use crate::pool::db_err;
16use crate::SqlitePool;
17
18const USER_COLS: &str = "id, username, name, email, affiliation, password_hash, \
19    graph_uri, is_admin, is_curator, is_member, reset_password_link, created_at, updated_at";
20
21#[derive(Clone)]
22pub struct SqliteUserStore {
23    pool: SqlitePool,
24}
25
26impl SqliteUserStore {
27    pub fn new(pool: SqlitePool) -> Self {
28        Self { pool }
29    }
30}
31
32#[async_trait]
33impl UserStore for SqliteUserStore {
34    async fn create_user(&self, new_user: NewUser) -> Result<User, DomainError> {
35        let id = UserId::new();
36        let now = Utc::now();
37        let row = sqlx::query(&format!(
38            "INSERT INTO sbh_user \
39             (id, username, name, email, affiliation, password_hash, \
40              graph_uri, is_admin, is_curator, is_member, created_at, updated_at) \
41             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) \
42             RETURNING {USER_COLS}"
43        ))
44        .bind(id.as_uuid().to_string())
45        .bind(&new_user.username)
46        .bind(&new_user.name)
47        .bind(&new_user.email)
48        .bind(new_user.affiliation.as_deref())
49        .bind(&new_user.password_hash)
50        .bind(&new_user.graph_uri)
51        .bind(new_user.is_admin as i64)
52        .bind(new_user.is_curator as i64)
53        .bind(new_user.is_member as i64)
54        .bind(now)
55        .bind(now)
56        .fetch_one(&self.pool)
57        .await
58        .map_err(db_err)?;
59        row_to_user(row)
60    }
61
62    async fn find_by_email_or_username(
63        &self,
64        identifier: &str,
65    ) -> Result<Option<User>, DomainError> {
66        let username = sqlx::query(&format!(
67            "SELECT {USER_COLS} FROM sbh_user WHERE username = ?"
68        ))
69        .bind(identifier)
70        .fetch_optional(&self.pool)
71        .await
72        .map_err(db_err)?;
73        if let Some(row) = username {
74            return row_to_user(row).map(Some);
75        }
76        let rows = sqlx::query(&format!(
77            "SELECT {USER_COLS} FROM sbh_user WHERE email = ? ORDER BY username LIMIT 2"
78        ))
79        .bind(identifier)
80        .fetch_all(&self.pool)
81        .await
82        .map_err(db_err)?;
83        match rows.len() {
84            0 => Ok(None),
85            1 => row_to_user(rows.into_iter().next().expect("one row")).map(Some),
86            _ => Err(DomainError::Validation(
87                "multiple accounts use this email; log in with your username".to_owned(),
88            )),
89        }
90    }
91
92    async fn get_by_id(&self, id: UserId) -> Result<Option<User>, DomainError> {
93        let row = sqlx::query(&format!("SELECT {USER_COLS} FROM sbh_user WHERE id = ?"))
94            .bind(id.as_uuid().to_string())
95            .fetch_optional(&self.pool)
96            .await
97            .map_err(db_err)?;
98        row.map(row_to_user).transpose()
99    }
100
101    async fn list_users(&self) -> Result<Vec<User>, DomainError> {
102        let rows = sqlx::query(&format!(
103            "SELECT {USER_COLS} FROM sbh_user ORDER BY username"
104        ))
105        .fetch_all(&self.pool)
106        .await
107        .map_err(db_err)?;
108        rows.into_iter().map(row_to_user).collect()
109    }
110
111    async fn page_users(&self, query: &UserListQuery) -> Result<UserPage, DomainError> {
112        let needle = query
113            .text
114            .as_deref()
115            .map(str::trim)
116            .filter(|value| !value.is_empty())
117            .map(str::to_lowercase);
118        let predicate = "(?1 IS NULL OR instr(lower(username), ?1) > 0 OR \
119            instr(lower(name), ?1) > 0 OR instr(lower(email), ?1) > 0 OR \
120            instr(lower(coalesce(affiliation, '')), ?1) > 0)";
121        let total: i64 =
122            sqlx::query_scalar(&format!("SELECT count(*) FROM sbh_user WHERE {predicate}"))
123                .bind(needle.as_deref())
124                .fetch_one(&self.pool)
125                .await
126                .map_err(db_err)?;
127        let rows = sqlx::query(&format!(
128            "SELECT {USER_COLS} FROM sbh_user WHERE {predicate} \
129             ORDER BY lower(username), username LIMIT ?2 OFFSET ?3"
130        ))
131        .bind(needle.as_deref())
132        .bind(i64::from(query.limit))
133        .bind(i64::try_from(query.offset).unwrap_or(i64::MAX))
134        .fetch_all(&self.pool)
135        .await
136        .map_err(db_err)?;
137        Ok(UserPage {
138            total: total.max(0) as u64,
139            items: rows
140                .into_iter()
141                .map(row_to_user)
142                .collect::<Result<_, _>>()?,
143        })
144    }
145
146    async fn update_user(&self, user: &User) -> Result<User, DomainError> {
147        let row = sqlx::query(&format!(
148            "UPDATE sbh_user \
149                SET name = ?, email = ?, affiliation = ?, \
150                    is_admin = ?, is_curator = ?, is_member = ?, updated_at = ? \
151              WHERE id = ? \
152             RETURNING {USER_COLS}"
153        ))
154        .bind(&user.name)
155        .bind(&user.email)
156        .bind(user.affiliation.as_deref())
157        .bind(user.is_admin as i64)
158        .bind(user.is_curator as i64)
159        .bind(user.is_member as i64)
160        .bind(Utc::now())
161        .bind(user.id.as_uuid().to_string())
162        .fetch_optional(&self.pool)
163        .await
164        .map_err(db_err)?
165        .ok_or_else(|| DomainError::NotFound(format!("user {}", user.id)))?;
166        row_to_user(row)
167    }
168
169    async fn set_sole_admin(&self, id: UserId) -> Result<(), DomainError> {
170        let mut tx = self.pool.begin().await.map_err(db_err)?;
171        let id = id.as_uuid().to_string();
172        let exists: i64 = sqlx::query_scalar("SELECT EXISTS(SELECT 1 FROM sbh_user WHERE id = ?)")
173            .bind(&id)
174            .fetch_one(&mut *tx)
175            .await
176            .map_err(db_err)?;
177        if exists == 0 {
178            return Err(DomainError::NotFound(format!("user {id}")));
179        }
180        let now = Utc::now();
181        sqlx::query(
182            "UPDATE sbh_user \
183                SET updated_at = CASE \
184                        WHEN is_admin != CASE WHEN id = ? THEN 1 ELSE 0 END THEN ? \
185                        ELSE updated_at \
186                    END, \
187                    is_admin = CASE WHEN id = ? THEN 1 ELSE 0 END",
188        )
189        .bind(&id)
190        .bind(now)
191        .bind(&id)
192        .execute(&mut *tx)
193        .await
194        .map_err(db_err)?;
195        tx.commit().await.map_err(db_err)?;
196        Ok(())
197    }
198
199    async fn set_password_hash(&self, id: UserId, password_hash: &str) -> Result<(), DomainError> {
200        sqlx::query("UPDATE sbh_user SET password_hash = ?, updated_at = ? WHERE id = ?")
201            .bind(password_hash)
202            .bind(Utc::now())
203            .bind(id.as_uuid().to_string())
204            .execute(&self.pool)
205            .await
206            .map_err(db_err)?;
207        Ok(())
208    }
209
210    async fn set_reset_link(&self, id: UserId, link: Option<&str>) -> Result<(), DomainError> {
211        sqlx::query("UPDATE sbh_user SET reset_password_link = ? WHERE id = ?")
212            .bind(link)
213            .bind(id.as_uuid().to_string())
214            .execute(&self.pool)
215            .await
216            .map_err(db_err)?;
217        Ok(())
218    }
219
220    async fn consume_reset_link(&self, link: &str) -> Result<Option<User>, DomainError> {
221        let row = sqlx::query(&format!(
222            "UPDATE sbh_user SET reset_password_link = NULL \
223              WHERE reset_password_link = ? \
224             RETURNING {USER_COLS}"
225        ))
226        .bind(link)
227        .fetch_optional(&self.pool)
228        .await
229        .map_err(db_err)?;
230        row.map(row_to_user).transpose()
231    }
232
233    async fn delete_user(&self, id: UserId) -> Result<bool, DomainError> {
234        let result = sqlx::query("DELETE FROM sbh_user WHERE id = ?")
235            .bind(id.as_uuid().to_string())
236            .execute(&self.pool)
237            .await
238            .map_err(db_err)?;
239        Ok(result.rows_affected() > 0)
240    }
241
242    async fn any_admin(&self) -> Result<bool, DomainError> {
243        let exists: bool =
244            sqlx::query_scalar("SELECT EXISTS(SELECT 1 FROM sbh_user WHERE is_admin = 1)")
245                .fetch_one(&self.pool)
246                .await
247                .map_err(db_err)?;
248        Ok(exists)
249    }
250}
251
252fn row_to_user(row: sqlx::sqlite::SqliteRow) -> Result<User, DomainError> {
253    let id: String = row.try_get("id").map_err(db_err)?;
254    Ok(User {
255        id: UserId(uuid::Uuid::parse_str(&id).map_err(db_err)?),
256        username: row.try_get("username").map_err(db_err)?,
257        name: row.try_get("name").map_err(db_err)?,
258        email: row.try_get("email").map_err(db_err)?,
259        affiliation: row.try_get("affiliation").map_err(db_err)?,
260        password_hash: row.try_get("password_hash").map_err(db_err)?,
261        graph_uri: row.try_get("graph_uri").map_err(db_err)?,
262        is_admin: row.try_get::<i64, _>("is_admin").map_err(db_err)? != 0,
263        is_curator: row.try_get::<i64, _>("is_curator").map_err(db_err)? != 0,
264        is_member: row.try_get::<i64, _>("is_member").map_err(db_err)? != 0,
265        reset_password_link: row.try_get("reset_password_link").map_err(db_err)?,
266        created_at: row.try_get("created_at").map_err(db_err)?,
267        updated_at: row.try_get("updated_at").map_err(db_err)?,
268    })
269}
sbol_db_sqlite/repo/users.rs

sbol_db_sqlite/repo/
users.rs
