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