dovenest

dovenest/server/src/db.rs
db.rs
⎇
Raw
1use std::path::Path;
2use std::sync::Arc;
3
4pub use api_types::Mode;
5use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
6use rusqlite::{Connection, OptionalExtension, params};
7
8const SCHEMA_VERSION: i64 = 4;
9
10/// SQL adapter for [`Mode`]. A newtype is needed because both the rusqlite
11/// traits and `Mode` are foreign to this crate.
12///
13/// The stored strings are unchanged ("rw"/"ro"), so old databases still read.
14struct SqlMode(Mode);
15
16impl FromSql for SqlMode {
17 fn column_result(v: ValueRef<'_>) -> FromSqlResult<Self> {
18 let s = v.as_str()?;
19 Mode::from_wire(s)
20 .map(SqlMode)
21 .ok_or_else(|| FromSqlError::Other(format!("unknown mode {s:?}").into()))
22 }
23}
24
25impl ToSql for SqlMode {
26 fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
27 Ok(ToSqlOutput::from(self.0.as_str()))
28 }
29}
30
31#[derive(Debug, Clone)]
32pub struct User {
33 pub id: i64,
34 pub name: String,
35 pub is_admin: bool,
36 /// Disabled users cannot sign in and their sessions are rejected.
37 pub active: bool,
38 /// Profile setting: single click opens entries (off = click selects).
39 pub single_click: bool,
40 /// Preferred UI language tag ("en", "de", "fr"); None = follow the
41 /// browser.
42 pub language: Option<String>,
43}
44
45#[derive(Debug, Clone)]
46pub struct RootRow {
47 pub id: i64,
48 /// Path relative to the server root; "." means the whole root.
49 pub path: String,
50 pub mode: Mode,
51}
52
53#[derive(Debug, Clone)]
54pub struct ShareRow {
55 pub id: i64,
56 pub token: String,
57 pub creator_id: i64,
58 /// Path of the shared item relative to the server root.
59 pub target: String,
60 pub is_file: bool,
61 pub mode: Mode,
62 pub created_at: String,
63 pub expires_at: Option<String>,
64}
65
66impl ShareRow {
67 pub fn is_expired(&self) -> bool {
68 match &self.expires_at {
69 Some(e) => chrono::DateTime::parse_from_rfc3339(e)
70 .map(|t| chrono::Utc::now() >= t.with_timezone(&chrono::Utc))
71 .unwrap_or(false),
72 None => false,
73 }
74 }
75}
76
77/// Every query can fail, and every caller decides what to do about it.
78///
79/// Earlier versions swallowed read errors and returned a default (an empty
80/// root list, a count of 0). That turned a broken database into a plausible
81/// answer: "you have no folders" instead of an error. One contract now.
82pub type DbResult<T> = Result<T, rusqlite::Error>;
83
84#[derive(Clone)]
85pub struct Db(Arc<tokio::sync::Mutex<Connection>>);
86
87impl std::fmt::Debug for Db {
88 fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
89 f.debug_struct("Db").finish()
90 }
91}
92
93impl Db {
94 pub async fn open(path: &Path) -> anyhow::Result<Self> {
95 if let Some(parent) = path.parent()
96 && !parent.as_os_str().is_empty()
97 {
98 std::fs::create_dir_all(parent)?;
99 }
100 let conn = Connection::open(path)?;
101 conn.pragma_update(None, "journal_mode", "WAL")?;
102 conn.pragma_update(None, "foreign_keys", "ON")?;
103 conn.pragma_update(None, "busy_timeout", "5000")?;
104 Self::migrate(&conn)?;
105 Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
106 }
107
108 /// Open a fresh in-memory database (used by tests — no temp file needed).
109 pub async fn open_in_memory() -> anyhow::Result<Self> {
110 let conn = Connection::open_in_memory()?;
111 conn.pragma_update(None, "foreign_keys", "ON")?;
112 conn.pragma_update(None, "busy_timeout", "5000")?;
113 Self::migrate(&conn)?;
114 Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
115 }
116
117 fn migrate(conn: &Connection) -> rusqlite::Result<()> {
118 conn.execute(
119 "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)",
120 [],
121 )?;
122 let version: i64 = conn
123 .query_row(
124 "SELECT value FROM meta WHERE key = 'schema_version'",
125 [],
126 |r| r.get::<_, String>(0),
127 )
128 .optional()?
129 .and_then(|v| v.parse().ok())
130 .unwrap_or(0);
131
132 if version < 1 {
133 conn.execute_batch(SCHEMA_V1)?;
134 }
135 if version < 2 {
136 // User management (M7): a disabled flag so admins can suspend
137 // accounts without deleting them.
138 conn.execute_batch("ALTER TABLE users ADD COLUMN active INTEGER NOT NULL DEFAULT 1")?;
139 }
140 if version < 3 {
141 // Per-user profile settings: click-to-open mode. No migration
142 // from the old behaviour — everyone starts on the new default
143 // (off: single click selects, double click opens).
144 conn.execute_batch(
145 "ALTER TABLE users ADD COLUMN single_click INTEGER NOT NULL DEFAULT 0",
146 )?;
147 }
148 if version < 4 {
149 // Per-user UI language preference; NULL means "follow the
150 // browser".
151 conn.execute_batch("ALTER TABLE users ADD COLUMN language TEXT")?;
152 }
153 conn.execute(
154 "INSERT OR REPLACE INTO meta (key, value) VALUES ('schema_version', ?1)",
155 [SCHEMA_VERSION.to_string()],
156 )?;
157 Ok(())
158 }
159
160 // ---------- users ----------
161
162 pub async fn user_count(&self) -> DbResult<i64> {
163 let c = self.0.lock().await;
164 c.query_row("SELECT COUNT(*) FROM users", [], |r| r.get(0))
165 }
166
167 /// Create the first admin account with the whole root visible (read-write).
168 /// Only valid while no users exist (enforced by the caller).
169 pub async fn create_admin(&self, name: &str, pass_hash: &str) -> DbResult<User> {
170 let mut c = self.0.lock().await;
171 let tx = c.transaction()?;
172 tx.execute(
173 "INSERT INTO users (name, pass_hash, is_admin, created_at)
174 VALUES (?1, ?2, 1, ?3)",
175 params![name, pass_hash, now()],
176 )?;
177 let user_id = tx.last_insert_rowid();
178 tx.execute(
179 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, '.', 'rw')",
180 params![user_id],
181 )?;
182 tx.commit()?;
183 Ok(User {
184 id: user_id,
185 name: name.to_string(),
186 is_admin: true,
187 active: true,
188 single_click: false,
189 language: None,
190 })
191 }
192
193 pub async fn verify_password(&self, name: &str, password: &str) -> DbResult<Option<User>> {
194 // The guard is scoped to the query alone. Argon2 below is slow by
195 // design; holding the single connection lock across it would make one
196 // login serialize every other database access.
197 type UserRow = (i64, String, bool, String, bool, bool, Option<String>);
198 let row: Option<UserRow> = {
199 let c = self.0.lock().await;
200 c.query_row(
201 "SELECT id, name, is_admin != 0, pass_hash, active != 0, single_click != 0,
202 language
203 FROM users WHERE name = ?1",
204 [name],
205 |r| {
206 Ok((
207 r.get(0)?,
208 r.get(1)?,
209 r.get(2)?,
210 r.get(3)?,
211 r.get(4)?,
212 r.get(5)?,
213 r.get(6)?,
214 ))
215 },
216 )
217 .optional()?
218 };
219 // An unknown or disabled name still pays for one Argon2 verify, so the
220 // response time does not reveal which names exist.
221 let (row, hash) = match row {
222 Some((id, name, is_admin, hash, active, single_click, language)) if active => {
223 (Some((id, name, is_admin, single_click, language)), hash)
224 }
225 _ => (None, DUMMY_HASH.clone()),
226 };
227 // Argon2 is CPU-bound, so it must not run on an async worker thread.
228 let password = password.to_string();
229 let ok =
230 tokio::task::spawn_blocking(move || crate::auth::verify_password(&password, &hash))
231 .await
232 .unwrap_or(false);
233 let Some((id, name, is_admin, single_click, language)) = row else {
234 return Ok(None);
235 };
236 Ok(ok.then_some(User {
237 id,
238 name,
239 is_admin,
240 active: true,
241 single_click,
242 language,
243 }))
244 }
245
246 // ---------- sessions ----------
247
248 pub async fn create_session(&self, user_id: i64, token: &str) -> DbResult<()> {
249 let c = self.0.lock().await;
250 c.execute(
251 "INSERT INTO sessions (token, user_id, created_at, last_seen_at)
252 VALUES (?1, ?2, ?3, ?4)",
253 params![token, user_id, now(), now()],
254 )?;
255 Ok(())
256 }
257
258 pub async fn delete_session(&self, token: &str) -> DbResult<()> {
259 let c = self.0.lock().await;
260 c.execute("DELETE FROM sessions WHERE token = ?1", [token])?;
261 Ok(())
262 }
263
264 pub async fn session_user(&self, token: &str) -> DbResult<Option<User>> {
265 let c = self.0.lock().await;
266 c.query_row(
267 "SELECT u.id, u.name, u.is_admin != 0, u.active != 0, u.single_click != 0, u.language
268 FROM sessions s JOIN users u ON u.id = s.user_id
269 WHERE s.token = ?1 AND u.active = 1",
270 [token],
271 map_user,
272 )
273 .optional()
274 }
275
276 // ---------- roots ----------
277
278 pub async fn user_roots(&self, user_id: i64) -> DbResult<Vec<RootRow>> {
279 let c = self.0.lock().await;
280 let mut stmt =
281 c.prepare("SELECT id, path, mode FROM user_roots WHERE user_id = ?1 ORDER BY id")?;
282 let rows = stmt.query_map([user_id], |r| {
283 Ok(RootRow {
284 id: r.get(0)?,
285 path: r.get(1)?,
286 mode: r.get::<_, SqlMode>(2)?.0,
287 })
288 })?;
289 rows.collect()
290 }
291
292 // ---------- admin: user management (M7) ----------
293
294 pub async fn all_users(&self) -> DbResult<Vec<User>> {
295 let c = self.0.lock().await;
296 let mut stmt = c.prepare(
297 "SELECT id, name, is_admin != 0, active != 0, single_click != 0, language
298 FROM users ORDER BY id",
299 )?;
300 let rows = stmt.query_map([], map_user)?;
301 rows.collect()
302 }
303
304 pub async fn find_user_by_id(&self, id: i64) -> DbResult<Option<User>> {
305 let c = self.0.lock().await;
306 c.query_row(
307 "SELECT id, name, is_admin != 0, active != 0, single_click != 0, language
308 FROM users WHERE id = ?1",
309 [id],
310 map_user,
311 )
312 .optional()
313 }
314
315 pub async fn find_user_by_name(&self, name: &str) -> DbResult<Option<User>> {
316 let c = self.0.lock().await;
317 c.query_row(
318 "SELECT id, name, is_admin != 0, active != 0, single_click != 0, language
319 FROM users WHERE name = ?1",
320 [name],
321 map_user,
322 )
323 .optional()
324 }
325
326 pub async fn count_admins(&self) -> DbResult<i64> {
327 let c = self.0.lock().await;
328 c.query_row(
329 "SELECT COUNT(*) FROM users WHERE is_admin = 1 AND active = 1",
330 [],
331 |r| r.get(0),
332 )
333 }
334
335 /// Create a user with the given roots (path, mode) pairs.
336 pub async fn create_user(
337 &self,
338 name: &str,
339 pass_hash: &str,
340 is_admin: bool,
341 roots: &[(String, Mode)],
342 ) -> DbResult<User> {
343 let mut c = self.0.lock().await;
344 let tx = c.transaction()?;
345 tx.execute(
346 "INSERT INTO users (name, pass_hash, is_admin, active, created_at)
347 VALUES (?1, ?2, ?3, 1, ?4)",
348 params![name, pass_hash, is_admin as i64, now()],
349 )?;
350 let user_id = tx.last_insert_rowid();
351 for (path, mode) in roots {
352 tx.execute(
353 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
354 params![user_id, path, SqlMode(*mode)],
355 )?;
356 }
357 tx.commit()?;
358 Ok(User {
359 id: user_id,
360 name: name.to_string(),
361 is_admin,
362 active: true,
363 single_click: false,
364 language: None,
365 })
366 }
367
368 pub async fn update_user_password(&self, id: i64, pass_hash: &str) -> DbResult<()> {
369 let c = self.0.lock().await;
370 c.execute(
371 "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
372 params![pass_hash, id],
373 )?;
374 Ok(())
375 }
376
377 pub async fn set_user_admin(&self, id: i64, is_admin: bool) -> DbResult<()> {
378 let c = self.0.lock().await;
379 c.execute(
380 "UPDATE users SET is_admin = ?1 WHERE id = ?2",
381 params![is_admin as i64, id],
382 )?;
383 Ok(())
384 }
385
386 pub async fn set_user_active(&self, id: i64, active: bool) -> DbResult<()> {
387 let c = self.0.lock().await;
388 c.execute(
389 "UPDATE users SET active = ?1 WHERE id = ?2",
390 params![active as i64, id],
391 )?;
392 Ok(())
393 }
394
395 pub async fn set_user_single_click(&self, id: i64, single_click: bool) -> DbResult<()> {
396 let c = self.0.lock().await;
397 c.execute(
398 "UPDATE users SET single_click = ?1 WHERE id = ?2",
399 params![single_click as i64, id],
400 )?;
401 Ok(())
402 }
403
404 pub async fn set_user_language(&self, id: i64, language: Option<&str>) -> DbResult<()> {
405 let c = self.0.lock().await;
406 c.execute(
407 "UPDATE users SET language = ?1 WHERE id = ?2",
408 params![language, id],
409 )?;
410 Ok(())
411 }
412
413 /// Apply an admin edit atomically: every `Some` field is written in one
414 /// transaction, so a failure midway leaves the user unchanged.
415 pub async fn update_user(
416 &self,
417 id: i64,
418 pass_hash: Option<&str>,
419 is_admin: Option<bool>,
420 active: Option<bool>,
421 roots: Option<&[(String, Mode)]>,
422 ) -> DbResult<()> {
423 let mut c = self.0.lock().await;
424 let tx = c.transaction()?;
425 if let Some(h) = pass_hash {
426 tx.execute(
427 "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
428 params![h, id],
429 )?;
430 }
431 if let Some(a) = is_admin {
432 tx.execute(
433 "UPDATE users SET is_admin = ?1 WHERE id = ?2",
434 params![a as i64, id],
435 )?;
436 }
437 if let Some(a) = active {
438 tx.execute(
439 "UPDATE users SET active = ?1 WHERE id = ?2",
440 params![a as i64, id],
441 )?;
442 }
443 if let Some(roots) = roots {
444 tx.execute("DELETE FROM user_roots WHERE user_id = ?1", [id])?;
445 for (path, mode) in roots {
446 tx.execute(
447 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
448 params![id, path, SqlMode(*mode)],
449 )?;
450 }
451 }
452 tx.commit()
453 }
454
455 /// Delete a user. `false` means no row matched.
456 pub async fn delete_user(&self, id: i64) -> DbResult<bool> {
457 let c = self.0.lock().await;
458 Ok(c.execute("DELETE FROM users WHERE id = ?1", [id])? > 0)
459 }
460
461 /// Replace a user's roots with the given (path, mode) pairs.
462 pub async fn set_user_roots(&self, user_id: i64, roots: &[(String, Mode)]) -> DbResult<()> {
463 let mut c = self.0.lock().await;
464 let tx = c.transaction()?;
465 tx.execute("DELETE FROM user_roots WHERE user_id = ?1", [user_id])?;
466 for (path, mode) in roots {
467 tx.execute(
468 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
469 params![user_id, path, SqlMode(*mode)],
470 )?;
471 }
472 tx.commit()?;
473 Ok(())
474 }
475
476 // ---------- shares ----------
477
478 pub async fn create_share(
479 &self,
480 creator_id: i64,
481 token: &str,
482 target: &str,
483 is_file: bool,
484 mode: Mode,
485 expires_at: Option<&str>,
486 ) -> DbResult<ShareRow> {
487 let c = self.0.lock().await;
488 c.execute(
489 "INSERT INTO shares (token, creator_id, target, is_file, mode, created_at, expires_at)
490 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
491 params![
492 token,
493 creator_id,
494 target,
495 is_file as i64,
496 SqlMode(mode),
497 now(),
498 expires_at
499 ],
500 )?;
501 let id = c.last_insert_rowid();
502 Ok(ShareRow {
503 id,
504 token: token.to_string(),
505 creator_id,
506 target: target.to_string(),
507 is_file,
508 mode,
509 created_at: now(),
510 expires_at: expires_at.map(|s| s.to_string()),
511 })
512 }
513
514 pub async fn share_by_token(&self, token: &str) -> DbResult<Option<ShareRow>> {
515 let c = self.0.lock().await;
516 let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at
517 FROM shares WHERE token = ?1";
518 c.query_row(sql, [token], map_share).optional()
519 }
520
521 pub async fn user_shares(&self, creator_id: i64) -> DbResult<Vec<ShareRow>> {
522 let c = self.0.lock().await;
523 let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at
524 FROM shares WHERE creator_id = ?1 ORDER BY id DESC";
525 let mut stmt = c.prepare(sql)?;
526 let rows = stmt.query_map([creator_id], map_share)?;
527 rows.collect()
528 }
529
530 /// Delete one of `creator_id`'s shares. `false` means no row matched.
531 pub async fn delete_share(&self, id: i64, creator_id: i64) -> DbResult<bool> {
532 let c = self.0.lock().await;
533 let n = c.execute(
534 "DELETE FROM shares WHERE id = ?1 AND creator_id = ?2",
535 params![id, creator_id],
536 )?;
537 Ok(n > 0)
538 }
539
540 // ---------- settings ----------
541
542 pub async fn get_setting(&self, key: &str) -> DbResult<Option<String>> {
543 let c = self.0.lock().await;
544 c.query_row("SELECT value FROM settings WHERE key = ?1", [key], |r| {
545 r.get(0)
546 })
547 .optional()
548 }
549
550 pub async fn set_setting(&self, key: &str, value: &str) -> DbResult<()> {
551 let c = self.0.lock().await;
552 c.execute(
553 "INSERT INTO settings (key, value) VALUES (?1, ?2)
554 ON CONFLICT(key) DO UPDATE SET value = ?2",
555 params![key, value],
556 )?;
557 Ok(())
558 }
559
560 /// Whether users may create writable (read-write) shares. Off by default;
561 /// the admin setting gates it.
562 pub async fn allow_writable_shares(&self) -> DbResult<bool> {
563 Ok(self.get_setting("allow_writable_shares").await?.as_deref() == Some("1"))
564 }
565
566 pub async fn set_allow_writable_shares(&self, v: bool) -> DbResult<()> {
567 self.set_setting("allow_writable_shares", if v { "1" } else { "0" })
568 .await
569 }
570}
571
572/// Column order matched by the four `users` SELECTs above.
573fn map_user(r: &rusqlite::Row) -> DbResult<User> {
574 Ok(User {
575 id: r.get(0)?,
576 name: r.get(1)?,
577 is_admin: r.get(2)?,
578 active: r.get(3)?,
579 single_click: r.get(4)?,
580 language: r.get(5)?,
581 })
582}
583
584/// Column order matched by the two `shares` SELECTs above.
585fn map_share(r: &rusqlite::Row) -> DbResult<ShareRow> {
586 Ok(ShareRow {
587 id: r.get(0)?,
588 token: r.get(1)?,
589 creator_id: r.get(2)?,
590 target: r.get(3)?,
591 is_file: r.get::<_, i64>(4)? != 0,
592 mode: r.get::<_, SqlMode>(5)?.0,
593 created_at: r.get(6)?,
594 expires_at: r.get(7)?,
595 })
596}
597
598/// A hash of a random string nobody knows. Verified against when the login
599/// name does not exist, so both paths cost one Argon2 run.
600static DUMMY_HASH: std::sync::LazyLock<String> = std::sync::LazyLock::new(|| {
601 crate::auth::hash_password(&crate::auth::random_token()).expect("argon2 hash")
602});
603
604fn now() -> String {
605 chrono::Utc::now().to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
606}
607
608const SCHEMA_V1: &str = r#"
609CREATE TABLE IF NOT EXISTS users (
610 id INTEGER PRIMARY KEY AUTOINCREMENT,
611 name TEXT NOT NULL UNIQUE COLLATE NOCASE,
612 pass_hash TEXT NOT NULL,
613 is_admin INTEGER NOT NULL DEFAULT 0,
614 created_at TEXT NOT NULL
615);
616
617CREATE TABLE IF NOT EXISTS user_roots (
618 id INTEGER PRIMARY KEY AUTOINCREMENT,
619 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
620 path TEXT NOT NULL,
621 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
622 UNIQUE (user_id, path)
623);
624
625CREATE TABLE IF NOT EXISTS shares (
626 id INTEGER PRIMARY KEY AUTOINCREMENT,
627 token TEXT NOT NULL UNIQUE,
628 creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
629 target TEXT NOT NULL,
630 is_file INTEGER NOT NULL,
631 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
632 created_at TEXT NOT NULL,
633 expires_at TEXT
634);
635
636CREATE TABLE IF NOT EXISTS sessions (
637 token TEXT PRIMARY KEY,
638 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
639 created_at TEXT NOT NULL,
640 last_seen_at TEXT NOT NULL
641);
642
643CREATE TABLE IF NOT EXISTS settings (
644 key TEXT PRIMARY KEY,
645 value TEXT NOT NULL
646);
647INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
648"#;
649
650#[cfg(test)]
651mod tests {
652 use super::*;
653
654 // Most tests use an in-memory DB (the file-based path is still covered
655 // by `v1_db_migrates_to_v2` and the integration harness' `Db::open`).
656 async fn mem() -> Db {
657 Db::open_in_memory().await.unwrap()
658 }
659
660 async fn db_with_admin() -> (Db, User) {
661 let db = mem().await;
662 let hash = crate::auth::hash_password("admin1234").unwrap();
663 let admin = db.create_admin("admin", &hash).await.unwrap();
664 (db, admin)
665 }
666
667 #[tokio::test]
668 async fn fresh_db_state() {
669 let db = mem().await;
670 assert_eq!(db.user_count().await.unwrap(), 0);
671 assert_eq!(db.count_admins().await.unwrap(), 0);
672 assert!(!db.allow_writable_shares().await.unwrap());
673 assert!(db.find_user_by_name("nobody").await.unwrap().is_none());
674 assert!(db.find_user_by_id(1).await.unwrap().is_none());
675 assert!(db.all_users().await.unwrap().is_empty());
676 }
677
678 #[tokio::test]
679 async fn v1_db_migrates_to_v2() {
680 let dir = tempfile::tempdir().unwrap();
681 let path = dir.path().join("legacy.sqlite");
682 {
683 let conn = rusqlite::Connection::open(&path).unwrap();
684 conn.execute_batch(SCHEMA_V1).unwrap();
685 conn.execute(
686 "INSERT INTO users (name, pass_hash, is_admin, created_at)
687 VALUES ('legacy', 'hash', 1, '2024-01-01T00:00:00Z')",
688 [],
689 )
690 .unwrap();
691 conn.execute(
692 "INSERT INTO user_roots (user_id, path, mode) VALUES (1, 'docs', 'rw')",
693 [],
694 )
695 .unwrap();
696 }
697 let db = Db::open(&path).await.unwrap();
698 assert_eq!(db.user_count().await.unwrap(), 1);
699 let u = db.find_user_by_name("legacy").await.unwrap().unwrap();
700 assert!(u.active, "v2 migration must default active to true");
701 assert!(u.is_admin);
702 assert_eq!(db.user_roots(u.id).await.unwrap().len(), 1);
703 // Migrations are idempotent.
704 let db2 = Db::open(&path).await.unwrap();
705 assert_eq!(db2.user_count().await.unwrap(), 1);
706 assert!(
707 db2.find_user_by_name("legacy")
708 .await
709 .unwrap()
710 .unwrap()
711 .active
712 );
713 }
714
715 #[tokio::test]
716 async fn admin_user_and_passwords() {
717 let (db, admin) = db_with_admin().await;
718 assert!(admin.is_admin);
719 assert!(admin.active);
720 // Root "." rw is assigned by create_admin.
721 let roots = db.user_roots(admin.id).await.unwrap();
722 assert_eq!(roots.len(), 1);
723 assert_eq!(roots[0].path, ".");
724 assert_eq!(roots[0].mode, Mode::Rw);
725
726 assert!(
727 db.verify_password("admin", "admin1234")
728 .await
729 .unwrap()
730 .is_some()
731 );
732 assert!(db.verify_password("admin", "nope").await.unwrap().is_none());
733 // Name lookup is case-insensitive (COLLATE NOCASE).
734 assert!(
735 db.verify_password("ADMIN", "admin1234")
736 .await
737 .unwrap()
738 .is_some()
739 );
740 // Disabled users cannot verify.
741 db.set_user_active(admin.id, false).await.unwrap();
742 assert!(
743 db.verify_password("admin", "admin1234")
744 .await
745 .unwrap()
746 .is_none()
747 );
748 db.set_user_active(admin.id, true).await.unwrap();
749 assert!(
750 db.verify_password("admin", "admin1234")
751 .await
752 .unwrap()
753 .is_some()
754 );
755 }
756
757 #[tokio::test]
758 async fn sessions_lifecycle() {
759 let (db, admin) = db_with_admin().await;
760 assert!(db.session_user("ghost-token").await.unwrap().is_none());
761 db.create_session(admin.id, "tok1").await.unwrap();
762 let u = db.session_user("tok1").await.unwrap().unwrap();
763 assert_eq!(u.id, admin.id);
764 // Disabling the user invalidates existing sessions.
765 db.set_user_active(admin.id, false).await.unwrap();
766 assert!(db.session_user("tok1").await.unwrap().is_none());
767 db.set_user_active(admin.id, true).await.unwrap();
768 assert!(db.session_user("tok1").await.unwrap().is_some());
769 db.delete_session("tok1").await.unwrap();
770 assert!(db.session_user("tok1").await.unwrap().is_none());
771 }
772
773 #[tokio::test]
774 async fn user_crud_and_roots() {
775 let (db, _admin) = db_with_admin().await;
776 let h = crate::auth::hash_password("bobpass1").unwrap();
777 let bob = db
778 .create_user("bob", &h, false, &[("docs".into(), Mode::Rw)])
779 .await
780 .unwrap();
781 assert!(!bob.is_admin);
782 assert!(bob.active);
783
784 // Duplicate name (case-insensitive) is rejected.
785 let h2 = crate::auth::hash_password("carolpass1").unwrap();
786 assert!(db.create_user("BOB", &h2, false, &[]).await.is_err());
787 assert!(db.create_user("carol", &h2, false, &[]).await.is_ok());
788
789 // Lookup helpers.
790 assert_eq!(
791 db.find_user_by_name("Bob").await.unwrap().unwrap().id,
792 bob.id
793 );
794 assert_eq!(
795 db.find_user_by_id(bob.id).await.unwrap().unwrap().name,
796 "bob"
797 );
798 assert!(db.find_user_by_name("dave").await.unwrap().is_none());
799 assert_eq!(db.all_users().await.unwrap().len(), 3);
800
801 // Root replacement semantics.
802 let roots = db.user_roots(bob.id).await.unwrap();
803 assert_eq!(roots.len(), 1);
804 db.set_user_roots(bob.id, &[(".".into(), Mode::Ro), ("docs".into(), Mode::Rw)])
805 .await
806 .unwrap();
807 let roots = db.user_roots(bob.id).await.unwrap();
808 assert_eq!(roots.len(), 2);
809 assert!(roots.iter().any(|r| r.path == "." && r.mode == Mode::Ro));
810 db.set_user_roots(bob.id, &[]).await.unwrap();
811 assert!(db.user_roots(bob.id).await.unwrap().is_empty());
812
813 // Password update.
814 let new_h = crate::auth::hash_password("bobpass2").unwrap();
815 db.update_user_password(bob.id, &new_h).await.unwrap();
816 assert!(
817 db.verify_password("bob", "bobpass1")
818 .await
819 .unwrap()
820 .is_none()
821 );
822 assert!(
823 db.verify_password("bob", "bobpass2")
824 .await
825 .unwrap()
826 .is_some()
827 );
828
829 // Admin flag + count (only active admins count).
830 db.set_user_admin(bob.id, true).await.unwrap();
831 assert_eq!(db.count_admins().await.unwrap(), 2);
832 db.set_user_active(bob.id, false).await.unwrap();
833 assert_eq!(db.count_admins().await.unwrap(), 1);
834 db.set_user_admin(bob.id, false).await.unwrap();
835
836 // Deletion.
837 assert!(db.delete_user(bob.id).await.unwrap());
838 assert!(db.find_user_by_id(bob.id).await.unwrap().is_none());
839 assert!(!db.delete_user(bob.id).await.unwrap());
840 assert_eq!(db.user_count().await.unwrap(), 2);
841 }
842
843 fn share_row(expires_at: Option<&str>) -> ShareRow {
844 ShareRow {
845 id: 1,
846 token: "t".into(),
847 creator_id: 1,
848 target: "docs".into(),
849 is_file: false,
850 mode: Mode::Ro,
851 created_at: "2024-01-01T00:00:00Z".into(),
852 expires_at: expires_at.map(str::to_string),
853 }
854 }
855
856 #[test]
857 fn share_expiry_logic() {
858 assert!(!share_row(None).is_expired());
859 assert!(!share_row(Some("2999-01-01T00:00:00Z")).is_expired());
860 assert!(share_row(Some("2000-01-01T00:00:00Z")).is_expired());
861 // Unparseable expiry → treated as not expired (fail open for reads).
862 assert!(!share_row(Some("not-a-date")).is_expired());
863 }
864
865 #[tokio::test]
866 async fn shares_crud() {
867 let (db, admin) = db_with_admin().await;
868 let s1 = db
869 .create_share(admin.id, "tok-a", "docs", false, Mode::Ro, None)
870 .await
871 .unwrap();
872 let s2 = db
873 .create_share(
874 admin.id,
875 "tok-b",
876 "file.txt",
877 true,
878 Mode::Rw,
879 Some("2999-01-01T00:00:00Z"),
880 )
881 .await
882 .unwrap();
883 assert!(s2.id > s1.id);
884
885 let found = db.share_by_token("tok-b").await.unwrap().unwrap();
886 assert!(found.is_file);
887 assert_eq!(found.mode, Mode::Rw);
888 assert!(db.share_by_token("nope").await.unwrap().is_none());
889
890 // Listed newest-first.
891 let list = db.user_shares(admin.id).await.unwrap();
892 assert_eq!(list.len(), 2);
893 assert_eq!(list[0].id, s2.id);
894 // Other users see nothing.
895 let h = crate::auth::hash_password("bobpass1").unwrap();
896 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
897 assert!(db.user_shares(bob.id).await.unwrap().is_empty());
898
899 // Only the creator can delete.
900 assert!(!db.delete_share(s1.id, bob.id).await.unwrap());
901 assert!(db.delete_share(s1.id, admin.id).await.unwrap());
902 assert!(db.share_by_token("tok-a").await.unwrap().is_none());
903 assert!(!db.delete_share(s1.id, admin.id).await.unwrap());
904 }
905
906 #[tokio::test]
907 async fn settings_round_trip() {
908 let (db, _admin) = db_with_admin().await;
909 assert!(!db.allow_writable_shares().await.unwrap());
910 db.set_allow_writable_shares(true).await.unwrap();
911 assert!(db.allow_writable_shares().await.unwrap());
912 // Upsert semantics.
913 db.set_allow_writable_shares(false).await.unwrap();
914 assert!(!db.allow_writable_shares().await.unwrap());
915 // Generic get/set.
916 db.set_setting("custom", "v").await.unwrap();
917 assert_eq!(
918 db.get_setting("custom").await.unwrap().as_deref(),
919 Some("v")
920 );
921 assert_eq!(db.get_setting("missing").await.unwrap(), None);
922 }
923}
924