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 ///
179 /// `None` means a user already existed. The `WHERE NOT EXISTS` guard runs
180 /// inside the same transaction as the insert, so two concurrent first-boot
181 /// setups cannot both win; a caller's earlier `user_count` check is only
182 /// an optimization, not the guarantee.
183 pub async fn create_admin(&self, name: &str, pass_hash: &str) -> DbResult<Option<User>> {
184 let mut c = self.0.lock().await;
185 let tx = c.transaction()?;
186 let inserted = tx.execute(
187 "INSERT INTO users (name, pass_hash, is_admin, created_at)
188 SELECT ?1, ?2, 1, ?3 WHERE NOT EXISTS (SELECT 1 FROM users)",
189 params![name, pass_hash, now()],
190 )?;
191 if inserted == 0 {
192 return Ok(None); // dropping `tx` rolls back
193 }
194 let user_id = tx.last_insert_rowid();
195 tx.execute(
196 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, '.', 'rw')",
197 params![user_id],
198 )?;
199 tx.commit()?;
200 Ok(Some(User {
201 id: user_id,
202 name: name.to_string(),
203 is_admin: true,
204 active: true,
205 single_click: false,
206 language: None,
207 }))
208 }
209
210 pub async fn verify_password(&self, name: &str, password: &str) -> DbResult<Option<User>> {
211 // The guard is scoped to the query alone. Argon2 below is slow by
212 // design; holding the single connection lock across it would make one
213 // login serialize every other database access.
214 type UserRow = (i64, String, bool, String, bool, bool, Option<String>);
215 let row: Option<UserRow> = {
216 let c = self.0.lock().await;
217 c.query_row(
218 "SELECT id, name, is_admin != 0, pass_hash, active != 0, single_click != 0,
219 language
220 FROM users WHERE name = ?1",
221 [name],
222 |r| {
223 Ok((
224 r.get(0)?,
225 r.get(1)?,
226 r.get(2)?,
227 r.get(3)?,
228 r.get(4)?,
229 r.get(5)?,
230 r.get(6)?,
231 ))
232 },
233 )
234 .optional()?
235 };
236 // An unknown or disabled name still pays for one Argon2 verify, so the
237 // response time does not reveal which names exist.
238 let (row, hash) = match row {
239 Some((id, name, is_admin, hash, active, single_click, language)) if active => {
240 (Some((id, name, is_admin, single_click, language)), hash)
241 }
242 _ => (None, DUMMY_HASH.clone()),
243 };
244 // Argon2 is CPU-bound, so it must not run on an async worker thread.
245 let _slot = crate::auth::ARGON2_SLOTS.acquire().await;
246 let password = password.to_string();
247 let ok =
248 tokio::task::spawn_blocking(move || crate::auth::verify_password(&password, &hash))
249 .await
250 .unwrap_or(false);
251 let Some((id, name, is_admin, single_click, language)) = row else {
252 return Ok(None);
253 };
254 Ok(ok.then_some(User {
255 id,
256 name,
257 is_admin,
258 active: true,
259 single_click,
260 language,
261 }))
262 }
263
264 // ---------- sessions ----------
265
266 pub async fn create_session(&self, user_id: i64, token: &str) -> DbResult<()> {
267 let c = self.0.lock().await;
268 c.execute(
269 "INSERT INTO sessions (token, user_id, created_at, last_seen_at)
270 VALUES (?1, ?2, ?3, ?4)",
271 params![token, user_id, now(), now()],
272 )?;
273 Ok(())
274 }
275
276 pub async fn delete_session(&self, token: &str) -> DbResult<()> {
277 let c = self.0.lock().await;
278 c.execute("DELETE FROM sessions WHERE token = ?1", [token])?;
279 Ok(())
280 }
281
282 /// The session's user plus that user's roots, in one round trip. Every
283 /// authenticated request needs both, so they are not two queries.
284 pub async fn session_user_with_roots(
285 &self,
286 token: &str,
287 ) -> DbResult<Option<(User, Vec<RootRow>)>> {
288 let c = self.0.lock().await;
289 let mut stmt = c.prepare_cached(
290 "SELECT u.id, u.name, u.is_admin != 0, u.active != 0, u.single_click != 0, u.language,
291 r.id, r.path, r.mode
292 FROM sessions s
293 JOIN users u ON u.id = s.user_id
294 LEFT JOIN user_roots r ON r.user_id = u.id
295 WHERE s.token = ?1 AND u.active = 1
296 ORDER BY r.id",
297 )?;
298 // One row per root; a user without roots still returns one row, with
299 // the root columns NULL.
300 let mut user: Option<User> = None;
301 let mut roots: Vec<RootRow> = Vec::new();
302 let mut rows = stmt.query([token])?;
303 while let Some(r) = rows.next()? {
304 if user.is_none() {
305 user = Some(map_user(r)?);
306 }
307 if let Some(id) = r.get::<_, Option<i64>>(6)? {
308 roots.push(RootRow {
309 id,
310 path: r.get(7)?,
311 mode: r.get::<_, SqlMode>(8)?.0,
312 });
313 }
314 }
315 Ok(user.map(|u| (u, roots)))
316 }
317
318 // ---------- roots ----------
319
320 pub async fn user_roots(&self, user_id: i64) -> DbResult<Vec<RootRow>> {
321 let c = self.0.lock().await;
322 let mut stmt = c.prepare_cached(
323 "SELECT id, path, mode FROM user_roots WHERE user_id = ?1 ORDER BY id",
324 )?;
325 let rows = stmt.query_map([user_id], |r| {
326 Ok(RootRow {
327 id: r.get(0)?,
328 path: r.get(1)?,
329 mode: r.get::<_, SqlMode>(2)?.0,
330 })
331 })?;
332 rows.collect()
333 }
334
335 // ---------- admin: user management (M7) ----------
336
337 /// Every user with their roots, in one query. The admin user list needs
338 /// both, and a per-user roots query would be one round trip per user.
339 pub async fn all_users_with_roots(&self) -> DbResult<Vec<(User, Vec<RootRow>)>> {
340 let c = self.0.lock().await;
341 let mut stmt = c.prepare_cached(
342 "SELECT u.id, u.name, u.is_admin != 0, u.active != 0, u.single_click != 0, u.language,
343 r.id, r.path, r.mode
344 FROM users u
345 LEFT JOIN user_roots r ON r.user_id = u.id
346 ORDER BY u.id, r.id",
347 )?;
348 // Rows arrive grouped by user, so a new user id starts a new group.
349 let mut out: Vec<(User, Vec<RootRow>)> = Vec::new();
350 let mut rows = stmt.query([])?;
351 while let Some(r) = rows.next()? {
352 let uid: i64 = r.get(0)?;
353 if out.last().is_none_or(|(u, _)| u.id != uid) {
354 out.push((map_user(r)?, Vec::new()));
355 }
356 if let Some(id) = r.get::<_, Option<i64>>(6)? {
357 out.last_mut().expect("pushed above").1.push(RootRow {
358 id,
359 path: r.get(7)?,
360 mode: r.get::<_, SqlMode>(8)?.0,
361 });
362 }
363 }
364 Ok(out)
365 }
366
367 pub async fn find_user_by_id(&self, id: i64) -> DbResult<Option<User>> {
368 let c = self.0.lock().await;
369 c.query_row(
370 "SELECT id, name, is_admin != 0, active != 0, single_click != 0, language
371 FROM users WHERE id = ?1",
372 [id],
373 map_user,
374 )
375 .optional()
376 }
377
378 pub async fn find_user_by_name(&self, name: &str) -> DbResult<Option<User>> {
379 let c = self.0.lock().await;
380 c.query_row(
381 "SELECT id, name, is_admin != 0, active != 0, single_click != 0, language
382 FROM users WHERE name = ?1",
383 [name],
384 map_user,
385 )
386 .optional()
387 }
388
389 pub async fn count_admins(&self) -> DbResult<i64> {
390 let c = self.0.lock().await;
391 c.query_row(
392 "SELECT COUNT(*) FROM users WHERE is_admin = 1 AND active = 1",
393 [],
394 |r| r.get(0),
395 )
396 }
397
398 /// Create a user with the given roots (path, mode) pairs.
399 pub async fn create_user(
400 &self,
401 name: &str,
402 pass_hash: &str,
403 is_admin: bool,
404 roots: &[(String, Mode)],
405 ) -> DbResult<User> {
406 let mut c = self.0.lock().await;
407 let tx = c.transaction()?;
408 tx.execute(
409 "INSERT INTO users (name, pass_hash, is_admin, active, created_at)
410 VALUES (?1, ?2, ?3, 1, ?4)",
411 params![name, pass_hash, is_admin as i64, now()],
412 )?;
413 let user_id = tx.last_insert_rowid();
414 for (path, mode) in roots {
415 tx.execute(
416 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
417 params![user_id, path, SqlMode(*mode)],
418 )?;
419 }
420 tx.commit()?;
421 Ok(User {
422 id: user_id,
423 name: name.to_string(),
424 is_admin,
425 active: true,
426 single_click: false,
427 language: None,
428 })
429 }
430
431 pub async fn set_user_single_click(&self, id: i64, single_click: bool) -> DbResult<()> {
432 let c = self.0.lock().await;
433 c.execute(
434 "UPDATE users SET single_click = ?1 WHERE id = ?2",
435 params![single_click as i64, id],
436 )?;
437 Ok(())
438 }
439
440 pub async fn set_user_language(&self, id: i64, language: Option<&str>) -> DbResult<()> {
441 let c = self.0.lock().await;
442 c.execute(
443 "UPDATE users SET language = ?1 WHERE id = ?2",
444 params![language, id],
445 )?;
446 Ok(())
447 }
448
449 /// Apply an admin edit atomically: every `Some` field is written in one
450 /// transaction, so a failure midway leaves the user unchanged.
451 pub async fn update_user(
452 &self,
453 id: i64,
454 pass_hash: Option<&str>,
455 is_admin: Option<bool>,
456 active: Option<bool>,
457 roots: Option<&[(String, Mode)]>,
458 ) -> DbResult<()> {
459 let mut c = self.0.lock().await;
460 let tx = c.transaction()?;
461 if let Some(h) = pass_hash {
462 set_password(&tx, id, h)?;
463 }
464 if let Some(a) = is_admin {
465 tx.execute(
466 "UPDATE users SET is_admin = ?1 WHERE id = ?2",
467 params![a as i64, id],
468 )?;
469 }
470 if let Some(a) = active {
471 tx.execute(
472 "UPDATE users SET active = ?1 WHERE id = ?2",
473 params![a as i64, id],
474 )?;
475 }
476 if let Some(roots) = roots {
477 tx.execute("DELETE FROM user_roots WHERE user_id = ?1", [id])?;
478 for (path, mode) in roots {
479 tx.execute(
480 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
481 params![id, path, SqlMode(*mode)],
482 )?;
483 }
484 }
485 tx.commit()
486 }
487
488 /// Delete a user. `false` means no row matched.
489 pub async fn delete_user(&self, id: i64) -> DbResult<bool> {
490 let c = self.0.lock().await;
491 Ok(c.execute("DELETE FROM users WHERE id = ?1", [id])? > 0)
492 }
493
494 // ---------- shares ----------
495
496 pub async fn create_share(
497 &self,
498 creator_id: i64,
499 token: &str,
500 target: &str,
501 is_file: bool,
502 mode: Mode,
503 expires_at: Option<&str>,
504 ) -> DbResult<ShareRow> {
505 let c = self.0.lock().await;
506 c.execute(
507 "INSERT INTO shares (token, creator_id, target, is_file, mode, created_at, expires_at)
508 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
509 params![
510 token,
511 creator_id,
512 target,
513 is_file as i64,
514 SqlMode(mode),
515 now(),
516 expires_at
517 ],
518 )?;
519 let id = c.last_insert_rowid();
520 Ok(ShareRow {
521 id,
522 token: token.to_string(),
523 creator_id,
524 target: target.to_string(),
525 is_file,
526 mode,
527 created_at: now(),
528 expires_at: expires_at.map(|s| s.to_string()),
529 })
530 }
531
532 pub async fn share_by_token(&self, token: &str) -> DbResult<Option<ShareRow>> {
533 let c = self.0.lock().await;
534 let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at
535 FROM shares WHERE token = ?1";
536 let mut stmt = c.prepare_cached(sql)?;
537 stmt.query_row([token], map_share).optional()
538 }
539
540 pub async fn user_shares(&self, creator_id: i64) -> DbResult<Vec<ShareRow>> {
541 let c = self.0.lock().await;
542 let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at
543 FROM shares WHERE creator_id = ?1 ORDER BY id DESC";
544 let mut stmt = c.prepare_cached(sql)?;
545 let rows = stmt.query_map([creator_id], map_share)?;
546 rows.collect()
547 }
548
549 /// Revoke every share on `target` or on anything beneath it. Returns how
550 /// many were dropped.
551 ///
552 /// Called when a path stops meaning what it meant: the item was deleted,
553 /// renamed, or moved away. A share names a path, and a path is not a
554 /// stable identity, so leaving the row behind would let a *new* item that
555 /// later takes the freed path inherit the old link's audience.
556 ///
557 /// `substr` rather than `LIKE`: a target containing `%` or `_` would make
558 /// a `LIKE` pattern over-match and revoke unrelated shares.
559 pub async fn revoke_shares_at(&self, target: &str) -> DbResult<usize> {
560 let c = self.0.lock().await;
561 c.execute(
562 "DELETE FROM shares
563 WHERE target = ?1 OR substr(target, 1, length(?1) + 1) = ?1 || '/'",
564 [target],
565 )
566 }
567
568 /// Delete one of `creator_id`'s shares. `false` means no row matched.
569 pub async fn delete_share(&self, id: i64, creator_id: i64) -> DbResult<bool> {
570 let c = self.0.lock().await;
571 let n = c.execute(
572 "DELETE FROM shares WHERE id = ?1 AND creator_id = ?2",
573 params![id, creator_id],
574 )?;
575 Ok(n > 0)
576 }
577
578 // ---------- settings ----------
579
580 pub async fn get_setting(&self, key: &str) -> DbResult<Option<String>> {
581 let c = self.0.lock().await;
582 let mut stmt = c.prepare_cached("SELECT value FROM settings WHERE key = ?1")?;
583 stmt.query_row([key], |r| r.get(0)).optional()
584 }
585
586 pub async fn set_setting(&self, key: &str, value: &str) -> DbResult<()> {
587 let c = self.0.lock().await;
588 c.execute(
589 "INSERT INTO settings (key, value) VALUES (?1, ?2)
590 ON CONFLICT(key) DO UPDATE SET value = ?2",
591 params![key, value],
592 )?;
593 Ok(())
594 }
595
596 /// Whether users may create writable (read-write) shares. Off by default;
597 /// the admin setting gates it.
598 pub async fn allow_writable_shares(&self) -> DbResult<bool> {
599 Ok(self.get_setting("allow_writable_shares").await?.as_deref() == Some("1"))
600 }
601
602 pub async fn set_allow_writable_shares(&self, v: bool) -> DbResult<()> {
603 self.set_setting("allow_writable_shares", if v { "1" } else { "0" })
604 .await
605 }
606}
607
608/// Write a new password hash and drop every session that was opened with the
609/// old one.
610///
611/// The two belong together: a password is changed because the old one is
612/// suspect (an admin resetting a compromised account), and a session that
613/// survives the reset leaves whoever holds it signed in. Takes the
614/// transaction so the caller can bundle it with its other edits.
615fn set_password(tx: &rusqlite::Transaction<'_>, id: i64, pass_hash: &str) -> DbResult<()> {
616 tx.execute(
617 "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
618 params![pass_hash, id],
619 )?;
620 tx.execute("DELETE FROM sessions WHERE user_id = ?1", [id])?;
621 Ok(())
622}
623
624/// Column order matched by the four `users` SELECTs above.
625fn map_user(r: &rusqlite::Row) -> DbResult<User> {
626 Ok(User {
627 id: r.get(0)?,
628 name: r.get(1)?,
629 is_admin: r.get(2)?,
630 active: r.get(3)?,
631 single_click: r.get(4)?,
632 language: r.get(5)?,
633 })
634}
635
636/// Column order matched by the two `shares` SELECTs above.
637fn map_share(r: &rusqlite::Row) -> DbResult<ShareRow> {
638 Ok(ShareRow {
639 id: r.get(0)?,
640 token: r.get(1)?,
641 creator_id: r.get(2)?,
642 target: r.get(3)?,
643 is_file: r.get::<_, i64>(4)? != 0,
644 mode: r.get::<_, SqlMode>(5)?.0,
645 created_at: r.get(6)?,
646 expires_at: r.get(7)?,
647 })
648}
649
650/// A hash of a random string nobody knows. Verified against when the login
651/// name does not exist, so both paths cost one Argon2 run.
652static DUMMY_HASH: std::sync::LazyLock<String> = std::sync::LazyLock::new(|| {
653 crate::auth::hash_password(&crate::auth::random_token()).expect("argon2 hash")
654});
655
656fn now() -> String {
657 chrono::Utc::now().to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
658}
659
660const SCHEMA_V1: &str = r#"
661CREATE TABLE IF NOT EXISTS users (
662 id INTEGER PRIMARY KEY AUTOINCREMENT,
663 name TEXT NOT NULL UNIQUE COLLATE NOCASE,
664 pass_hash TEXT NOT NULL,
665 is_admin INTEGER NOT NULL DEFAULT 0,
666 created_at TEXT NOT NULL
667);
668
669CREATE TABLE IF NOT EXISTS user_roots (
670 id INTEGER PRIMARY KEY AUTOINCREMENT,
671 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
672 path TEXT NOT NULL,
673 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
674 UNIQUE (user_id, path)
675);
676
677CREATE TABLE IF NOT EXISTS shares (
678 id INTEGER PRIMARY KEY AUTOINCREMENT,
679 token TEXT NOT NULL UNIQUE,
680 creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
681 target TEXT NOT NULL,
682 is_file INTEGER NOT NULL,
683 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
684 created_at TEXT NOT NULL,
685 expires_at TEXT
686);
687
688CREATE TABLE IF NOT EXISTS sessions (
689 token TEXT PRIMARY KEY,
690 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
691 created_at TEXT NOT NULL,
692 last_seen_at TEXT NOT NULL
693);
694
695CREATE TABLE IF NOT EXISTS settings (
696 key TEXT PRIMARY KEY,
697 value TEXT NOT NULL
698);
699INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
700"#;
701
702#[cfg(test)]
703mod tests {
704 use super::*;
705
706 // Most tests use an in-memory DB (the file-based path is still covered
707 // by `v1_db_migrates_to_v2` and the integration harness' `Db::open`).
708 async fn mem() -> Db {
709 Db::open_in_memory().await.unwrap()
710 }
711
712 /// `update_user` is the only way production edits these fields, so the
713 /// tests exercise that path rather than per-field helpers.
714 async fn edit(db: &Db, id: i64, pass: Option<&str>, admin: Option<bool>, active: Option<bool>) {
715 db.update_user(id, pass, admin, active, None).await.unwrap();
716 }
717
718 async fn db_with_admin() -> (Db, User) {
719 let db = mem().await;
720 let hash = crate::auth::hash_password("admin1234").unwrap();
721 let admin = db.create_admin("admin", &hash).await.unwrap().unwrap();
722 (db, admin)
723 }
724
725 #[tokio::test]
726 async fn fresh_db_state() {
727 let db = mem().await;
728 assert_eq!(db.user_count().await.unwrap(), 0);
729 assert_eq!(db.count_admins().await.unwrap(), 0);
730 assert!(!db.allow_writable_shares().await.unwrap());
731 assert!(db.find_user_by_name("nobody").await.unwrap().is_none());
732 assert!(db.find_user_by_id(1).await.unwrap().is_none());
733 assert!(db.all_users_with_roots().await.unwrap().is_empty());
734 }
735
736 #[tokio::test]
737 async fn v1_db_migrates_to_v2() {
738 let dir = tempfile::tempdir().unwrap();
739 let path = dir.path().join("legacy.sqlite");
740 {
741 let conn = rusqlite::Connection::open(&path).unwrap();
742 conn.execute_batch(SCHEMA_V1).unwrap();
743 conn.execute(
744 "INSERT INTO users (name, pass_hash, is_admin, created_at)
745 VALUES ('legacy', 'hash', 1, '2024-01-01T00:00:00Z')",
746 [],
747 )
748 .unwrap();
749 conn.execute(
750 "INSERT INTO user_roots (user_id, path, mode) VALUES (1, 'docs', 'rw')",
751 [],
752 )
753 .unwrap();
754 }
755 let db = Db::open(&path).await.unwrap();
756 assert_eq!(db.user_count().await.unwrap(), 1);
757 let u = db.find_user_by_name("legacy").await.unwrap().unwrap();
758 assert!(u.active, "v2 migration must default active to true");
759 assert!(u.is_admin);
760 assert_eq!(db.user_roots(u.id).await.unwrap().len(), 1);
761 // Migrations are idempotent.
762 let db2 = Db::open(&path).await.unwrap();
763 assert_eq!(db2.user_count().await.unwrap(), 1);
764 assert!(
765 db2.find_user_by_name("legacy")
766 .await
767 .unwrap()
768 .unwrap()
769 .active
770 );
771 }
772
773 #[tokio::test]
774 async fn admin_user_and_passwords() {
775 let (db, admin) = db_with_admin().await;
776 assert!(admin.is_admin);
777 assert!(admin.active);
778 // Root "." rw is assigned by create_admin.
779 let roots = db.user_roots(admin.id).await.unwrap();
780 assert_eq!(roots.len(), 1);
781 assert_eq!(roots[0].path, ".");
782 assert_eq!(roots[0].mode, Mode::Rw);
783
784 assert!(
785 db.verify_password("admin", "admin1234")
786 .await
787 .unwrap()
788 .is_some()
789 );
790 assert!(db.verify_password("admin", "nope").await.unwrap().is_none());
791 // Name lookup is case-insensitive (COLLATE NOCASE).
792 assert!(
793 db.verify_password("ADMIN", "admin1234")
794 .await
795 .unwrap()
796 .is_some()
797 );
798 // Disabled users cannot verify.
799 edit(&db, admin.id, None, None, Some(false)).await;
800 assert!(
801 db.verify_password("admin", "admin1234")
802 .await
803 .unwrap()
804 .is_none()
805 );
806 edit(&db, admin.id, None, None, Some(true)).await;
807 assert!(
808 db.verify_password("admin", "admin1234")
809 .await
810 .unwrap()
811 .is_some()
812 );
813 }
814
815 #[tokio::test]
816 async fn setup_is_won_by_exactly_one_caller() {
817 let db = mem().await;
818 let hash = crate::auth::hash_password("admin1234").unwrap();
819 assert!(db.create_admin("first", &hash).await.unwrap().is_some());
820 // The guard lives in the insert, so a different name loses too.
821 assert!(db.create_admin("second", &hash).await.unwrap().is_none());
822 assert_eq!(db.user_count().await.unwrap(), 1);
823 // The loser rolled back cleanly: no orphaned root row.
824 let first = db.find_user_by_name("first").await.unwrap().unwrap();
825 assert_eq!(db.user_roots(first.id).await.unwrap().len(), 1);
826 }
827
828 #[tokio::test]
829 async fn changing_a_password_drops_that_users_sessions() {
830 let (db, admin) = db_with_admin().await;
831 let h = crate::auth::hash_password("bobpass1").unwrap();
832 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
833 db.create_session(admin.id, "admin-tok").await.unwrap();
834 db.create_session(bob.id, "bob-tok-1").await.unwrap();
835 db.create_session(bob.id, "bob-tok-2").await.unwrap();
836
837 let new_h = crate::auth::hash_password("bobpass2").unwrap();
838 edit(&db, bob.id, Some(&new_h), None, None).await;
839 assert!(
840 db.session_user_with_roots("bob-tok-1")
841 .await
842 .unwrap()
843 .is_none()
844 );
845 assert!(
846 db.session_user_with_roots("bob-tok-2")
847 .await
848 .unwrap()
849 .is_none()
850 );
851 // Only the reset user is signed out.
852 assert!(
853 db.session_user_with_roots("admin-tok")
854 .await
855 .unwrap()
856 .is_some()
857 );
858
859 // The admin-edit path bundles the same rule into its transaction.
860 db.create_session(bob.id, "bob-tok-3").await.unwrap();
861 let h3 = crate::auth::hash_password("bobpass3").unwrap();
862 db.update_user(bob.id, Some(&h3), None, None, None)
863 .await
864 .unwrap();
865 assert!(
866 db.session_user_with_roots("bob-tok-3")
867 .await
868 .unwrap()
869 .is_none()
870 );
871 // An edit that leaves the password alone keeps the session.
872 db.create_session(bob.id, "bob-tok-4").await.unwrap();
873 db.update_user(bob.id, None, Some(true), None, None)
874 .await
875 .unwrap();
876 assert!(
877 db.session_user_with_roots("bob-tok-4")
878 .await
879 .unwrap()
880 .is_some()
881 );
882 }
883
884 #[tokio::test]
885 async fn sessions_lifecycle() {
886 let (db, admin) = db_with_admin().await;
887 assert!(
888 db.session_user_with_roots("ghost-token")
889 .await
890 .unwrap()
891 .is_none()
892 );
893 db.create_session(admin.id, "tok1").await.unwrap();
894 let (u, _) = db.session_user_with_roots("tok1").await.unwrap().unwrap();
895 assert_eq!(u.id, admin.id);
896 // Disabling the user invalidates existing sessions.
897 edit(&db, admin.id, None, None, Some(false)).await;
898 assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
899 edit(&db, admin.id, None, None, Some(true)).await;
900 assert!(db.session_user_with_roots("tok1").await.unwrap().is_some());
901 db.delete_session("tok1").await.unwrap();
902 assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
903 }
904
905 #[tokio::test]
906 async fn user_crud_and_roots() {
907 let (db, _admin) = db_with_admin().await;
908 let h = crate::auth::hash_password("bobpass1").unwrap();
909 let bob = db
910 .create_user("bob", &h, false, &[("docs".into(), Mode::Rw)])
911 .await
912 .unwrap();
913 assert!(!bob.is_admin);
914 assert!(bob.active);
915
916 // Duplicate name (case-insensitive) is rejected.
917 let h2 = crate::auth::hash_password("carolpass1").unwrap();
918 assert!(db.create_user("BOB", &h2, false, &[]).await.is_err());
919 assert!(db.create_user("carol", &h2, false, &[]).await.is_ok());
920
921 // Lookup helpers.
922 assert_eq!(
923 db.find_user_by_name("Bob").await.unwrap().unwrap().id,
924 bob.id
925 );
926 assert_eq!(
927 db.find_user_by_id(bob.id).await.unwrap().unwrap().name,
928 "bob"
929 );
930 assert!(db.find_user_by_name("dave").await.unwrap().is_none());
931 assert_eq!(db.all_users_with_roots().await.unwrap().len(), 3);
932
933 // Root replacement semantics.
934 let roots = db.user_roots(bob.id).await.unwrap();
935 assert_eq!(roots.len(), 1);
936 db.update_user(
937 bob.id,
938 None,
939 None,
940 None,
941 Some(&[(".".into(), Mode::Ro), ("docs".into(), Mode::Rw)]),
942 )
943 .await
944 .unwrap();
945 let roots = db.user_roots(bob.id).await.unwrap();
946 assert_eq!(roots.len(), 2);
947 assert!(roots.iter().any(|r| r.path == "." && r.mode == Mode::Ro));
948 db.update_user(bob.id, None, None, None, Some(&[]))
949 .await
950 .unwrap();
951 assert!(db.user_roots(bob.id).await.unwrap().is_empty());
952
953 // Password update.
954 let new_h = crate::auth::hash_password("bobpass2").unwrap();
955 edit(&db, bob.id, Some(&new_h), None, None).await;
956 assert!(
957 db.verify_password("bob", "bobpass1")
958 .await
959 .unwrap()
960 .is_none()
961 );
962 assert!(
963 db.verify_password("bob", "bobpass2")
964 .await
965 .unwrap()
966 .is_some()
967 );
968
969 // Admin flag + count (only active admins count).
970 edit(&db, bob.id, None, Some(true), None).await;
971 assert_eq!(db.count_admins().await.unwrap(), 2);
972 edit(&db, bob.id, None, None, Some(false)).await;
973 assert_eq!(db.count_admins().await.unwrap(), 1);
974 edit(&db, bob.id, None, Some(false), None).await;
975
976 // Deletion.
977 assert!(db.delete_user(bob.id).await.unwrap());
978 assert!(db.find_user_by_id(bob.id).await.unwrap().is_none());
979 assert!(!db.delete_user(bob.id).await.unwrap());
980 assert_eq!(db.user_count().await.unwrap(), 2);
981 }
982
983 fn share_row(expires_at: Option<&str>) -> ShareRow {
984 ShareRow {
985 id: 1,
986 token: "t".into(),
987 creator_id: 1,
988 target: "docs".into(),
989 is_file: false,
990 mode: Mode::Ro,
991 created_at: "2024-01-01T00:00:00Z".into(),
992 expires_at: expires_at.map(str::to_string),
993 }
994 }
995
996 #[test]
997 fn share_expiry_logic() {
998 assert!(!share_row(None).is_expired());
999 assert!(!share_row(Some("2999-01-01T00:00:00Z")).is_expired());
1000 assert!(share_row(Some("2000-01-01T00:00:00Z")).is_expired());
1001 // Unparseable expiry → treated as not expired (fail open for reads).
1002 assert!(!share_row(Some("not-a-date")).is_expired());
1003 }
1004
1005 #[tokio::test]
1006 async fn shares_crud() {
1007 let (db, admin) = db_with_admin().await;
1008 let s1 = db
1009 .create_share(admin.id, "tok-a", "docs", false, Mode::Ro, None)
1010 .await
1011 .unwrap();
1012 let s2 = db
1013 .create_share(
1014 admin.id,
1015 "tok-b",
1016 "file.txt",
1017 true,
1018 Mode::Rw,
1019 Some("2999-01-01T00:00:00Z"),
1020 )
1021 .await
1022 .unwrap();
1023 assert!(s2.id > s1.id);
1024
1025 let found = db.share_by_token("tok-b").await.unwrap().unwrap();
1026 assert!(found.is_file);
1027 assert_eq!(found.mode, Mode::Rw);
1028 assert!(db.share_by_token("nope").await.unwrap().is_none());
1029
1030 // Listed newest-first.
1031 let list = db.user_shares(admin.id).await.unwrap();
1032 assert_eq!(list.len(), 2);
1033 assert_eq!(list[0].id, s2.id);
1034 // Other users see nothing.
1035 let h = crate::auth::hash_password("bobpass1").unwrap();
1036 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
1037 assert!(db.user_shares(bob.id).await.unwrap().is_empty());
1038
1039 // Only the creator can delete.
1040 assert!(!db.delete_share(s1.id, bob.id).await.unwrap());
1041 assert!(db.delete_share(s1.id, admin.id).await.unwrap());
1042 assert!(db.share_by_token("tok-a").await.unwrap().is_none());
1043 assert!(!db.delete_share(s1.id, admin.id).await.unwrap());
1044 }
1045
1046 #[tokio::test]
1047 async fn revoking_a_path_takes_its_descendants_only() {
1048 let (db, admin) = db_with_admin().await;
1049 let mk = async |token: &str, target: &str| {
1050 db.create_share(admin.id, token, target, false, Mode::Ro, None)
1051 .await
1052 .unwrap();
1053 };
1054 mk("t-self", "docs").await;
1055 mk("t-child", "docs/a.txt").await;
1056 mk("t-deep", "docs/inner/b.txt").await;
1057 // A sibling whose name merely starts with "docs" must survive.
1058 mk("t-sibling", "docs2/c.txt").await;
1059 mk("t-other", "src").await;
1060 // SQL wildcards in a path are literal characters, not patterns.
1061 mk("t-wild", "do%s/d.txt").await;
1062
1063 assert_eq!(db.revoke_shares_at("docs").await.unwrap(), 3);
1064 for gone in ["t-self", "t-child", "t-deep"] {
1065 assert!(db.share_by_token(gone).await.unwrap().is_none(), "{gone}");
1066 }
1067 for kept in ["t-sibling", "t-other", "t-wild"] {
1068 assert!(db.share_by_token(kept).await.unwrap().is_some(), "{kept}");
1069 }
1070 // Revoking a path nobody shared is a no-op, not an error.
1071 assert_eq!(db.revoke_shares_at("nothing/here").await.unwrap(), 0);
1072 }
1073
1074 #[tokio::test]
1075 async fn settings_round_trip() {
1076 let (db, _admin) = db_with_admin().await;
1077 assert!(!db.allow_writable_shares().await.unwrap());
1078 db.set_allow_writable_shares(true).await.unwrap();
1079 assert!(db.allow_writable_shares().await.unwrap());
1080 // Upsert semantics.
1081 db.set_allow_writable_shares(false).await.unwrap();
1082 assert!(!db.allow_writable_shares().await.unwrap());
1083 // Generic get/set.
1084 db.set_setting("custom", "v").await.unwrap();
1085 assert_eq!(
1086 db.get_setting("custom").await.unwrap().as_deref(),
1087 Some("v")
1088 );
1089 assert_eq!(db.get_setting("missing").await.unwrap(), None);
1090 }
1091}
1092