dovenest

dovenest/server/src/db.rs
db.rs
⎇
Raw
1use std::path::Path;
2use std::sync::Arc;
3
4pub use api_types::{AppPasswordInfo, AuthMode, Mode, PimShareMode};
5pub use pimdav::principal::UserType;
6use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ValueRef};
7use rusqlite::{Connection, OptionalExtension, params};
8use webauthn_rs::prelude::Uuid;
9
10const SCHEMA_VERSION: i64 = 12;
11
12/// SQL adapter for reading a [`Mode`]. A newtype is needed because both the
13/// rusqlite traits and `Mode` are foreign to this crate. Writes bind
14/// `Mode::as_str` directly.
15///
16/// The stored strings are unchanged ("rw"/"ro"), so old databases still read.
17struct SqlMode(Mode);
18
19impl FromSql for SqlMode {
20 fn column_result(v: ValueRef<'_>) -> FromSqlResult<Self> {
21 let s = v.as_str()?;
22 Mode::from_wire(s)
23 .map(SqlMode)
24 .ok_or_else(|| FromSqlError::Other(format!("unknown mode {s:?}").into()))
25 }
26}
27
28/// SQL adapter for [`PimShareMode`], for the same reason as [`SqlMode`].
29struct SqlShareMode(PimShareMode);
30
31impl FromSql for SqlShareMode {
32 fn column_result(v: ValueRef<'_>) -> FromSqlResult<Self> {
33 let s = v.as_str()?;
34 PimShareMode::from_wire(s)
35 .map(SqlShareMode)
36 .ok_or_else(|| FromSqlError::Other(format!("unknown share mode {s:?}").into()))
37 }
38}
39
40/// SQL adapter for [`AuthMode`], for the same reason as [`SqlMode`].
41struct SqlAuthMode(AuthMode);
42
43impl FromSql for SqlAuthMode {
44 fn column_result(v: ValueRef<'_>) -> FromSqlResult<Self> {
45 let s = v.as_str()?;
46 AuthMode::from_wire(s)
47 .map(SqlAuthMode)
48 .ok_or_else(|| FromSqlError::Other(format!("unknown auth mode {s:?}").into()))
49 }
50}
51
52#[derive(Debug, Clone)]
53pub struct User {
54 pub id: i64,
55 pub name: String,
56 pub is_admin: bool,
57 /// Disabled users cannot sign in and their sessions are rejected.
58 pub active: bool,
59 /// Profile setting: single click opens entries (off = click selects).
60 pub single_click: bool,
61 /// Profile setting: show thumbnails in the grid.
62 pub thumbnails: bool,
63 /// Preferred UI language tag ("en", "de", "fr"); None = follow the
64 /// browser.
65 pub language: Option<String>,
66 /// Profile setting: the root the UI opens by default. May point at a
67 /// root the user no longer has; the API filters that out.
68 pub default_root_id: Option<i64>,
69 /// What this account needs to sign in.
70 pub auth_mode: AuthMode,
71 /// Whether a password is set. False means the account signs in with
72 /// passkeys only. See [`NO_PASSWORD`].
73 pub has_password: bool,
74}
75
76/// The `pass_hash` of an account with no password.
77///
78/// A sentinel rather than NULL: `users.pass_hash` is `NOT NULL`, and SQLite
79/// cannot drop that constraint without rebuilding the table. Nothing verifies
80/// against it — [`Db::verify_password`] swaps in [`DUMMY_HASH`] so the reject
81/// costs the same as a wrong password, and `PasswordHash::new("")` fails
82/// anyway.
83pub const NO_PASSWORD: &str = "";
84
85/// How many passkeys one account may hold.
86///
87/// Also the exact number of credentials a named sign-in challenge lists. The
88/// two are one number on purpose: the challenge pads a short list with decoys
89/// so its length says nothing about the account, and that only works while no
90/// account can push past the padding.
91pub const PASSKEY_LIMIT: usize = 8;
92
93/// How many app passwords one account may hold. One per client is the point.
94pub const APP_PASSWORD_LIMIT: usize = 10;
95
96/// What [`Db::delete_passkey`] did.
97#[derive(Debug, Clone, Copy, PartialEq, Eq)]
98pub enum PasskeyDeleted {
99 Gone,
100 /// No such passkey, or it belongs to someone else.
101 NotFound,
102 /// Removing it would have left the account unreachable.
103 LastCredential,
104}
105
106/// One registered passkey. `passkey` is the serialized `webauthn-rs`
107/// credential; everything else is for the settings list.
108#[derive(Debug, Clone)]
109pub struct PasskeyRow {
110 pub id: i64,
111 pub name: String,
112 pub created_at: String,
113 pub last_used_at: Option<String>,
114 pub discoverable: Option<bool>,
115 /// `webauthn_rs::prelude::Passkey` as JSON.
116 pub passkey: String,
117}
118
119#[derive(Debug, Clone)]
120pub struct RootRow {
121 pub id: i64,
122 /// Path relative to the server root; "." means the whole root.
123 pub path: String,
124 pub mode: Mode,
125}
126
127#[derive(Debug, Clone)]
128pub struct ShareRow {
129 pub id: i64,
130 pub token: String,
131 pub creator_id: i64,
132 /// Path of the shared item relative to the server root.
133 pub target: String,
134 pub is_file: bool,
135 pub mode: Mode,
136 pub created_at: String,
137 pub expires_at: Option<String>,
138 /// Argon2 hash of the share's password, when it has one. Resolve,
139 /// listing and download all stay locked until the visitor enters it and
140 /// gets an unlock cookie.
141 pub password_hash: Option<String>,
142}
143
144impl ShareRow {
145 pub fn is_expired(&self) -> bool {
146 expired(self.expires_at.as_deref())
147 }
148}
149
150/// An unparseable stamp never expires; the API refuses one on creation.
151fn expired(expires_at: Option<&str>) -> bool {
152 match expires_at {
153 Some(e) => chrono::DateTime::parse_from_rfc3339(e)
154 .map(|t| chrono::Utc::now() >= t.with_timezone(&chrono::Utc))
155 .unwrap_or(false),
156 None => false,
157 }
158}
159
160/// A public feed of one calendar or address book.
161#[derive(Debug, Clone)]
162pub struct PimLink {
163 pub id: i64,
164 pub token: String,
165 pub collection_id: i64,
166 /// Events reduced to their times.
167 pub busy_only: bool,
168 pub created_at: String,
169 pub expires_at: Option<String>,
170 pub password_hash: Option<String>,
171}
172
173impl PimLink {
174 pub fn is_expired(&self) -> bool {
175 expired(self.expires_at.as_deref())
176 }
177}
178
179/// A [`ShareRow`] together with the account that created it.
180#[derive(Debug, Clone)]
181/// A feed link with what the admin overview shows about it.
182pub struct PimLinkWithOwner {
183 pub link: PimLink,
184 pub collection_name: String,
185 pub kind: PimKind,
186 pub owner_id: i64,
187 pub owner_name: String,
188 pub owner_active: bool,
189}
190
191pub struct ShareWithCreator {
192 pub share: ShareRow,
193 pub creator_name: String,
194 /// Whether that account can still sign in. Deactivating an account leaves
195 /// its shares live.
196 pub creator_active: bool,
197}
198
199#[derive(Debug, Clone, Copy, PartialEq, Eq)]
200pub enum PimKind {
201 Calendar,
202 AddressBook,
203}
204
205impl PimKind {
206 fn parse(s: &str) -> Self {
207 match s {
208 "cal" => PimKind::Calendar,
209 _ => PimKind::AddressBook,
210 }
211 }
212
213 fn as_str(self) -> &'static str {
214 match self {
215 PimKind::Calendar => "cal",
216 PimKind::AddressBook => "card",
217 }
218 }
219}
220
221/// A calendar or address book.
222#[derive(Debug, Clone, Default)]
223pub struct PimCollection {
224 pub id: i64,
225 /// The URL segment.
226 pub slug: String,
227 pub displayname: Option<String>,
228 pub description: Option<String>,
229 pub color: Option<String>,
230 /// A VCALENDAR holding one VTIMEZONE.
231 pub timezone: Option<String>,
232 pub sort_order: Option<String>,
233 /// The component types a calendar takes, comma-separated. Empty for an
234 /// address book.
235 pub components: String,
236 /// Adds no busy time to scheduling.
237 pub transparent: bool,
238 /// Grows with every change to the collection or its members.
239 pub seq: i64,
240}
241
242/// A calendar or address object, without its data.
243#[derive(Debug, Clone, Default)]
244pub struct PimObject {
245 /// The URL segment.
246 pub name: String,
247 pub uid: String,
248 /// `VEVENT`, `VTODO`, `VJOURNAL` or `VCARD`.
249 pub component: String,
250 /// With the quotes.
251 pub etag: String,
252 pub size: i64,
253 pub modified_at: String,
254 /// With the quotes. Only on scheduling objects.
255 pub schedule_tag: Option<String>,
256}
257
258/// One write of [`Db::pim_apply`].
259#[derive(Debug)]
260pub enum PimOp {
261 Put {
262 collection_id: i64,
263 obj: PimObject,
264 data: Vec<u8>,
265 },
266 Delete {
267 collection_id: i64,
268 name: String,
269 },
270 /// A scheduling message for the inbox of `principal_id`. `obj.uid` must
271 /// be unique in the inbox: several messages share one iCalendar UID.
272 Inbox {
273 principal_id: i64,
274 obj: PimObject,
275 data: Vec<u8>,
276 },
277}
278
279#[derive(Debug, PartialEq, Eq)]
280pub enum PimWrite {
281 Created,
282 Updated,
283 Deleted,
284 NotFound,
285 PreconditionFailed,
286 /// Another object in the collection has this UID, under this name.
287 UidConflict(String),
288}
289
290/// The `If-Match` and `If-None-Match` headers of a write.
291#[derive(Debug, Default)]
292pub struct Precondition {
293 pub if_match: Option<String>,
294 pub if_none_match: Option<String>,
295}
296
297impl Precondition {
298 /// Whether the write may go ahead given the current ETag, if any.
299 pub fn allows(&self, current: Option<&str>) -> bool {
300 let listed = |header: &str| match current {
301 Some(etag) => header.split(',').any(|t| {
302 let t = t.trim();
303 t == "*" || t.strip_prefix("W/").unwrap_or(t) == etag
304 }),
305 None => false,
306 };
307 self.if_match.as_deref().is_none_or(listed)
308 && !self.if_none_match.as_deref().is_some_and(listed)
309 }
310}
311
312/// The columns [`map_user`] reads, in order. Every SELECT that builds a
313/// [`User`] uses one of these two, so a new column is added in one place.
314/// `USER_COLS_U` is the same list qualified for the queries that join
315/// `users u` against `user_roots`.
316const USER_COLS: &str = "id, name, is_admin != 0, active != 0, single_click != 0,
317 thumbnails != 0, language, default_root_id, auth_mode, pass_hash != ''";
318const USER_COLS_U: &str = "u.id, u.name, u.is_admin != 0, u.active != 0, u.single_click != 0,
319 u.thumbnails != 0, u.language, u.default_root_id, u.auth_mode, u.pass_hash != ''";
320/// How many columns the two lists above cover. The joined queries read the
321/// root columns starting here.
322const USER_COL_COUNT: usize = 10;
323
324/// Every query can fail, and every caller decides what to do about it.
325///
326/// Earlier versions swallowed read errors and returned a default (an empty
327/// root list, a count of 0). That turned a broken database into a plausible
328/// answer: "you have no folders" instead of an error. One contract now.
329pub type DbResult<T> = Result<T, rusqlite::Error>;
330
331#[derive(Clone)]
332pub struct Db(Arc<tokio::sync::Mutex<Connection>>);
333
334impl Db {
335 pub async fn open(path: &Path) -> anyhow::Result<Self> {
336 if let Some(parent) = path.parent()
337 && !parent.as_os_str().is_empty()
338 {
339 std::fs::create_dir_all(parent)?;
340 }
341 let conn = Connection::open(path)?;
342 conn.pragma_update(None, "journal_mode", "WAL")?;
343 // WAL plus NORMAL: fsync only at checkpoints. A crash can lose the
344 // last commits, never the database file.
345 conn.pragma_update(None, "synchronous", "NORMAL")?;
346 conn.pragma_update(None, "foreign_keys", "ON")?;
347 conn.pragma_update(None, "busy_timeout", "5000")?;
348 Self::migrate(&conn)?;
349 Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
350 }
351
352 fn migrate(conn: &Connection) -> rusqlite::Result<()> {
353 conn.execute(
354 "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)",
355 [],
356 )?;
357 let version: i64 = conn
358 .query_row(
359 "SELECT value FROM meta WHERE key = 'schema_version'",
360 [],
361 |r| r.get::<_, String>(0),
362 )
363 .optional()?
364 .and_then(|v| v.parse().ok())
365 .unwrap_or(0);
366
367 if version < 1 {
368 conn.execute_batch(SCHEMA_V1)?;
369 }
370 if version < 2 {
371 // User management (M7): a disabled flag so admins can suspend
372 // accounts without deleting them.
373 conn.execute_batch("ALTER TABLE users ADD COLUMN active INTEGER NOT NULL DEFAULT 1")?;
374 }
375 if version < 3 {
376 // Per-user profile settings: click-to-open mode. No migration
377 // from the old behaviour — everyone starts on the new default
378 // (off: single click selects, double click opens).
379 conn.execute_batch(
380 "ALTER TABLE users ADD COLUMN single_click INTEGER NOT NULL DEFAULT 0",
381 )?;
382 }
383 if version < 4 {
384 // Per-user UI language preference; NULL means "follow the
385 // browser".
386 conn.execute_batch("ALTER TABLE users ADD COLUMN language TEXT")?;
387 }
388 if version < 5 {
389 // The share list is queried by creator on every shares page.
390 conn.execute_batch(
391 "CREATE INDEX IF NOT EXISTS idx_shares_creator ON shares(creator_id)",
392 )?;
393 }
394 if version < 6 {
395 // Unlocks cascade with their share, which cascades with its
396 // creator's account.
397 conn.execute_batch(
398 "ALTER TABLE shares ADD COLUMN password_hash TEXT;
399 CREATE TABLE IF NOT EXISTS share_unlocks (
400 token TEXT PRIMARY KEY,
401 share_id INTEGER NOT NULL REFERENCES shares(id) ON DELETE CASCADE,
402 created_at TEXT NOT NULL
403 );",
404 )?;
405 }
406 if version < 7 {
407 // `delete_share` cascades into share_unlocks, which is a full
408 // scan of that table without this.
409 conn.execute_batch(
410 "CREATE INDEX IF NOT EXISTS idx_share_unlocks_share
411 ON share_unlocks(share_id)",
412 )?;
413 }
414 if version < 8 {
415 // On by default, so `--cache` is the only step needed to get
416 // thumbnails.
417 conn.execute_batch(
418 "ALTER TABLE users ADD COLUMN thumbnails INTEGER NOT NULL DEFAULT 1",
419 )?;
420 }
421 if version < 9 {
422 // Per-user default root. No foreign key on purpose: removing a
423 // root must not fail because of this column.
424 conn.execute_batch("ALTER TABLE users ADD COLUMN default_root_id INTEGER")?;
425 }
426 if version < 10 {
427 // Passkeys, and how they combine with the password.
428 //
429 // `webauthn_id` is the WebAuthn user handle: a random uuid the
430 // authenticator stores inside a discoverable credential and hands
431 // back at sign-in. It must never change once a passkey exists, or
432 // that passkey can no longer be traced to its account. Filled in
433 // lazily on the first registration, so accounts that never use a
434 // passkey keep it NULL.
435 conn.execute_batch(
436 "ALTER TABLE users ADD COLUMN auth_mode TEXT NOT NULL DEFAULT 'either';
437 ALTER TABLE users ADD COLUMN webauthn_id TEXT;
438 CREATE TABLE IF NOT EXISTS passkeys (
439 id INTEGER PRIMARY KEY AUTOINCREMENT,
440 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
441 cred_id BLOB NOT NULL UNIQUE,
442 passkey TEXT NOT NULL,
443 name TEXT NOT NULL,
444 discoverable INTEGER,
445 created_at TEXT NOT NULL,
446 last_used_at TEXT
447 );
448 CREATE INDEX IF NOT EXISTS idx_passkeys_user ON passkeys(user_id);
449 CREATE UNIQUE INDEX IF NOT EXISTS idx_users_webauthn_id
450 ON users(webauthn_id) WHERE webauthn_id IS NOT NULL;",
451 )?;
452 }
453 if version < 11 {
454 // `secret_hash` is UNIQUE because the lookup keys on it.
455 conn.execute_batch(
456 "CREATE TABLE IF NOT EXISTS app_passwords (
457 id INTEGER PRIMARY KEY AUTOINCREMENT,
458 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
459 name TEXT NOT NULL,
460 secret_hash TEXT NOT NULL UNIQUE,
461 created_at TEXT NOT NULL,
462 last_used_at TEXT
463 );
464 CREATE INDEX IF NOT EXISTS idx_app_passwords_user
465 ON app_passwords(user_id);",
466 )?;
467 }
468 if version < 12 {
469 // CalDAV and CardDAV. A principal owns collections: every account
470 // has one, and rooms and resources are principals without an
471 // account, so they share the account name space but no account
472 // query can return them. The trigger gives each new account its
473 // principal and refuses a name a room already has.
474 //
475 // `seq` counts every change to a collection and its members;
476 // `pim_changes` keeps the latest change per member, deletions
477 // included, for sync tokens. `pim_shares` lends a collection to
478 // another account. `schedule_tag` is NULL for objects that
479 // schedule nothing. A `transparent` calendar adds no busy time
480 // to scheduling (RFC 6638 `schedule-calendar-transp`).
481 // `pim_links` are public feeds; not rows of `shares`, because
482 // every path-based share query would then have to skip them.
483 conn.execute_batch(
484 "CREATE TABLE IF NOT EXISTS principals (
485 id INTEGER PRIMARY KEY AUTOINCREMENT,
486 user_id INTEGER UNIQUE REFERENCES users(id) ON DELETE CASCADE,
487 kind TEXT NOT NULL CHECK (kind IN ('person','room','resource')),
488 name TEXT NOT NULL UNIQUE COLLATE NOCASE,
489 display_name TEXT,
490 CHECK ((kind = 'person') = (user_id IS NOT NULL))
491 );
492 INSERT INTO principals (user_id, kind, name)
493 SELECT id, 'person', name FROM users;
494 CREATE TRIGGER IF NOT EXISTS principal_of_user AFTER INSERT ON users
495 BEGIN
496 INSERT INTO principals (user_id, kind, name)
497 VALUES (NEW.id, 'person', NEW.name);
498 END;
499 CREATE TABLE IF NOT EXISTS pim_collections (
500 id INTEGER PRIMARY KEY AUTOINCREMENT,
501 principal_id INTEGER NOT NULL
502 REFERENCES principals(id) ON DELETE CASCADE,
503 kind TEXT NOT NULL CHECK (kind IN ('cal','card')),
504 slug TEXT NOT NULL,
505 displayname TEXT,
506 description TEXT,
507 color TEXT,
508 timezone TEXT,
509 sort_order TEXT,
510 components TEXT NOT NULL DEFAULT '',
511 transparent INTEGER NOT NULL DEFAULT 0,
512 seq INTEGER NOT NULL DEFAULT 0,
513 created_at TEXT NOT NULL,
514 UNIQUE (principal_id, kind, slug)
515 );
516 CREATE TABLE IF NOT EXISTS pim_objects (
517 id INTEGER PRIMARY KEY AUTOINCREMENT,
518 collection_id INTEGER NOT NULL
519 REFERENCES pim_collections(id) ON DELETE CASCADE,
520 name TEXT NOT NULL,
521 uid TEXT NOT NULL,
522 component TEXT NOT NULL,
523 data BLOB NOT NULL,
524 etag TEXT NOT NULL,
525 modified_at TEXT NOT NULL,
526 schedule_tag TEXT,
527 UNIQUE (collection_id, name),
528 UNIQUE (collection_id, uid)
529 );
530 CREATE TABLE IF NOT EXISTS pim_changes (
531 collection_id INTEGER NOT NULL
532 REFERENCES pim_collections(id) ON DELETE CASCADE,
533 name TEXT NOT NULL,
534 seq INTEGER NOT NULL,
535 deleted INTEGER NOT NULL,
536 PRIMARY KEY (collection_id, name)
537 );
538 CREATE INDEX IF NOT EXISTS idx_pim_changes_seq
539 ON pim_changes(collection_id, seq);
540 CREATE TABLE IF NOT EXISTS pim_shares (
541 collection_id INTEGER NOT NULL
542 REFERENCES pim_collections(id) ON DELETE CASCADE,
543 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
544 mode TEXT NOT NULL CHECK (mode IN ('ro','rw','rw+schedule')),
545 PRIMARY KEY (collection_id, user_id)
546 );
547 CREATE INDEX IF NOT EXISTS idx_pim_shares_user ON pim_shares(user_id);
548 CREATE TABLE IF NOT EXISTS pim_links (
549 id INTEGER PRIMARY KEY AUTOINCREMENT,
550 token TEXT NOT NULL UNIQUE,
551 collection_id INTEGER NOT NULL
552 REFERENCES pim_collections(id) ON DELETE CASCADE,
553 busy_only INTEGER NOT NULL DEFAULT 0,
554 created_at TEXT NOT NULL,
555 expires_at TEXT,
556 password_hash TEXT
557 );
558 CREATE INDEX IF NOT EXISTS idx_pim_links_collection
559 ON pim_links(collection_id);",
560 )?;
561 }
562 conn.execute(
563 "INSERT OR REPLACE INTO meta (key, value) VALUES ('schema_version', ?1)",
564 [SCHEMA_VERSION.to_string()],
565 )?;
566 Ok(())
567 }
568
569 // ---------- users ----------
570
571 pub async fn user_count(&self) -> DbResult<i64> {
572 let c = self.0.lock().await;
573 let mut stmt = c.prepare_cached("SELECT COUNT(*) FROM users")?;
574 stmt.query_row([], |r| r.get(0))
575 }
576
577 /// Create the first admin account with the whole root visible (read-write).
578 ///
579 /// `None` means a user already existed. The `WHERE NOT EXISTS` guard runs
580 /// inside the same transaction as the insert, so two concurrent first-boot
581 /// setups cannot both win; a caller's earlier `user_count` check is only
582 /// an optimization, not the guarantee.
583 pub async fn create_admin(&self, name: &str, pass_hash: &str) -> DbResult<Option<User>> {
584 let mut c = self.0.lock().await;
585 let tx = c.transaction()?;
586 let inserted = tx.execute(
587 "INSERT INTO users (name, pass_hash, is_admin, created_at)
588 SELECT ?1, ?2, 1, ?3 WHERE NOT EXISTS (SELECT 1 FROM users)",
589 params![name, pass_hash, now()],
590 )?;
591 if inserted == 0 {
592 return Ok(None); // dropping `tx` rolls back
593 }
594 let user_id = tx.last_insert_rowid();
595 tx.execute(
596 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, '.', 'rw')",
597 params![user_id],
598 )?;
599 tx.commit()?;
600 Ok(Some(new_user(user_id, name, true)))
601 }
602
603 pub async fn verify_password(&self, name: &str, password: &str) -> DbResult<Option<User>> {
604 // The guard is scoped to the query alone. Argon2 below is slow by
605 // design; holding the single connection lock across it would make one
606 // login serialize every other database access.
607 let row: Option<(User, String)> = {
608 let c = self.0.lock().await;
609 c.query_row(
610 &format!("SELECT {USER_COLS}, pass_hash FROM users WHERE name = ?1"),
611 [name],
612 |r| Ok((map_user(r)?, r.get(USER_COL_COUNT)?)),
613 )
614 .optional()?
615 };
616 // An unknown name, a disabled account and a passkey-only account all
617 // still pay for one Argon2 verify, so the response time does not tell
618 // them apart from a real account with a wrong password.
619 let (user, hash) = match row {
620 Some((u, h)) if u.active && u.has_password => (Some(u), h),
621 _ => (None, DUMMY_HASH.clone()),
622 };
623 let ok = crate::auth::verify_password_async(password, &hash).await;
624 Ok(user.filter(|_| ok))
625 }
626
627 pub async fn create_session(&self, user_id: i64, token: &str) -> DbResult<()> {
628 let c = self.0.lock().await;
629 c.execute(
630 "INSERT INTO sessions (token, user_id, created_at, last_seen_at)
631 VALUES (?1, ?2, ?3, ?4)",
632 params![token, user_id, now(), now()],
633 )?;
634 Ok(())
635 }
636
637 pub async fn delete_session(&self, token: &str) -> DbResult<()> {
638 let c = self.0.lock().await;
639 c.execute("DELETE FROM sessions WHERE token = ?1", [token])?;
640 Ok(())
641 }
642
643 /// The session's user plus that user's roots, in one round trip. Every
644 /// authenticated request needs both, so they are not two queries.
645 pub async fn session_user_with_roots(
646 &self,
647 token: &str,
648 ) -> DbResult<Option<(User, Vec<RootRow>)>> {
649 let c = self.0.lock().await;
650 let mut stmt = c.prepare_cached(&format!(
651 "SELECT {USER_COLS_U}, r.id, r.path, r.mode
652 FROM sessions s
653 JOIN users u ON u.id = s.user_id
654 LEFT JOIN user_roots r ON r.user_id = u.id
655 WHERE s.token = ?1 AND u.active = 1
656 ORDER BY r.id",
657 ))?;
658 // One row per root; a user without roots still returns one row, with
659 // the root columns NULL.
660 let mut user: Option<User> = None;
661 let mut roots: Vec<RootRow> = Vec::new();
662 let mut rows = stmt.query([token])?;
663 while let Some(r) = rows.next()? {
664 if user.is_none() {
665 user = Some(map_user(r)?);
666 }
667 if let Some(id) = r.get::<_, Option<i64>>(USER_COL_COUNT)? {
668 roots.push(RootRow {
669 id,
670 path: r.get(USER_COL_COUNT + 1)?,
671 mode: r.get::<_, SqlMode>(USER_COL_COUNT + 2)?.0,
672 });
673 }
674 }
675 Ok(user.map(|u| (u, roots)))
676 }
677
678 // ---------- roots ----------
679
680 pub async fn user_roots(&self, user_id: i64) -> DbResult<Vec<RootRow>> {
681 let c = self.0.lock().await;
682 let mut stmt = c.prepare_cached(
683 "SELECT id, path, mode FROM user_roots WHERE user_id = ?1 ORDER BY id",
684 )?;
685 let rows = stmt.query_map([user_id], |r| {
686 Ok(RootRow {
687 id: r.get(0)?,
688 path: r.get(1)?,
689 mode: r.get::<_, SqlMode>(2)?.0,
690 })
691 })?;
692 rows.collect()
693 }
694
695 // ---------- admin: user management (M7) ----------
696
697 /// Every user with their roots, in one query. The admin user list needs
698 /// both, and a per-user roots query would be one round trip per user.
699 pub async fn all_users_with_roots(&self) -> DbResult<Vec<(User, Vec<RootRow>)>> {
700 let c = self.0.lock().await;
701 let mut stmt = c.prepare_cached(&format!(
702 "SELECT {USER_COLS_U}, r.id, r.path, r.mode
703 FROM users u
704 LEFT JOIN user_roots r ON r.user_id = u.id
705 ORDER BY u.id, r.id",
706 ))?;
707 // Rows arrive grouped by user, so a new user id starts a new group.
708 let mut out: Vec<(User, Vec<RootRow>)> = Vec::new();
709 let mut rows = stmt.query([])?;
710 while let Some(r) = rows.next()? {
711 let uid: i64 = r.get(0)?;
712 if out.last().is_none_or(|(u, _)| u.id != uid) {
713 out.push((map_user(r)?, Vec::new()));
714 }
715 if let Some(id) = r.get::<_, Option<i64>>(USER_COL_COUNT)? {
716 out.last_mut().expect("pushed above").1.push(RootRow {
717 id,
718 path: r.get(USER_COL_COUNT + 1)?,
719 mode: r.get::<_, SqlMode>(USER_COL_COUNT + 2)?.0,
720 });
721 }
722 }
723 Ok(out)
724 }
725
726 pub async fn find_user_by_id(&self, id: i64) -> DbResult<Option<User>> {
727 let c = self.0.lock().await;
728 c.query_row(
729 &format!("SELECT {USER_COLS} FROM users WHERE id = ?1"),
730 [id],
731 map_user,
732 )
733 .optional()
734 }
735
736 pub async fn find_user_by_name(&self, name: &str) -> DbResult<Option<User>> {
737 let c = self.0.lock().await;
738 c.query_row(
739 &format!("SELECT {USER_COLS} FROM users WHERE name = ?1"),
740 [name],
741 map_user,
742 )
743 .optional()
744 }
745
746 pub async fn count_admins(&self) -> DbResult<i64> {
747 let c = self.0.lock().await;
748 c.query_row(
749 "SELECT COUNT(*) FROM users WHERE is_admin = 1 AND active = 1",
750 [],
751 |r| r.get(0),
752 )
753 }
754
755 /// Create a user with the given roots (path, mode) pairs.
756 pub async fn create_user(
757 &self,
758 name: &str,
759 pass_hash: &str,
760 is_admin: bool,
761 roots: &[(String, Mode)],
762 ) -> DbResult<User> {
763 let mut c = self.0.lock().await;
764 let tx = c.transaction()?;
765 tx.execute(
766 "INSERT INTO users (name, pass_hash, is_admin, active, created_at)
767 VALUES (?1, ?2, ?3, 1, ?4)",
768 params![name, pass_hash, is_admin as i64, now()],
769 )?;
770 let user_id = tx.last_insert_rowid();
771 for (path, mode) in roots {
772 tx.execute(
773 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
774 params![user_id, path, mode.as_str()],
775 )?;
776 }
777 tx.commit()?;
778 Ok(new_user(user_id, name, is_admin))
779 }
780
781 /// Write the profile settings a user edits for themselves.
782 pub async fn set_user_profile(&self, u: &User) -> DbResult<()> {
783 let c = self.0.lock().await;
784 c.execute(
785 "UPDATE users SET single_click = ?1, thumbnails = ?2, language = ?3,
786 default_root_id = ?4
787 WHERE id = ?5",
788 params![
789 u.single_click,
790 u.thumbnails,
791 u.language,
792 u.default_root_id,
793 u.id
794 ],
795 )?;
796 Ok(())
797 }
798
799 /// Apply an admin edit atomically: every `Some` field is written in one
800 /// transaction, so a failure midway leaves the user unchanged.
801 pub async fn update_user(
802 &self,
803 id: i64,
804 pass_hash: Option<&str>,
805 is_admin: Option<bool>,
806 active: Option<bool>,
807 roots: Option<&[(String, Mode)]>,
808 ) -> DbResult<()> {
809 let mut c = self.0.lock().await;
810 let tx = c.transaction()?;
811 // An admin sets a password to get someone back into a locked-out
812 // account, so every other way in goes with it: the passkeys, the app
813 // passwords, the requirement to use one. Always rather than on
814 // request, so a reset cannot leave a credential the user no longer
815 // has.
816 if let Some(h) = pass_hash {
817 set_password(&tx, id, h)?;
818 tx.execute("DELETE FROM passkeys WHERE user_id = ?1", [id])?;
819 tx.execute("DELETE FROM app_passwords WHERE user_id = ?1", [id])?;
820 tx.execute(
821 "UPDATE users SET auth_mode = ?1 WHERE id = ?2",
822 params![AuthMode::Either.as_str(), id],
823 )?;
824 }
825 if let Some(a) = is_admin {
826 tx.execute(
827 "UPDATE users SET is_admin = ?1 WHERE id = ?2",
828 params![a as i64, id],
829 )?;
830 }
831 if let Some(a) = active {
832 tx.execute(
833 "UPDATE users SET active = ?1 WHERE id = ?2",
834 params![a as i64, id],
835 )?;
836 }
837 if let Some(roots) = roots {
838 tx.execute("DELETE FROM user_roots WHERE user_id = ?1", [id])?;
839 for (path, mode) in roots {
840 tx.execute(
841 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
842 params![id, path, mode.as_str()],
843 )?;
844 }
845 }
846 tx.commit()
847 }
848
849 // ---------- credentials: password, passkeys, sign-in mode ----------
850
851 /// Set the password. Leaves every session alone, so the caller must pair
852 /// this with [`Db::delete_other_sessions`] — see
853 /// [`crate::api::passkeys`]'s `invalidate_elsewhere`. The admin path uses
854 /// [`set_password`] instead, which ends every session including its own.
855 ///
856 /// Only for setting a real one. Clearing it is [`Db::clear_user_password`],
857 /// which has a rule to keep.
858 pub async fn set_password_keeping_sessions(&self, id: i64, pass_hash: &str) -> DbResult<()> {
859 let c = self.0.lock().await;
860 c.execute(
861 "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
862 params![pass_hash, id],
863 )?;
864 Ok(())
865 }
866
867 /// Leave the account on its passkeys alone. `false` means that would have
868 /// locked it out, so nothing changed.
869 pub async fn clear_user_password(&self, id: i64) -> DbResult<bool> {
870 let mut c = self.0.lock().await;
871 let tx = c.transaction()?;
872 tx.execute(
873 "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
874 params![NO_PASSWORD, id],
875 )?;
876 commit_if_reachable(tx, id)
877 }
878
879 /// `false` means the account does not satisfy the new mode, so it stands.
880 pub async fn set_user_auth_mode(&self, id: i64, mode: AuthMode) -> DbResult<bool> {
881 let mut c = self.0.lock().await;
882 let tx = c.transaction()?;
883 tx.execute(
884 "UPDATE users SET auth_mode = ?1 WHERE id = ?2",
885 params![mode.as_str(), id],
886 )?;
887 commit_if_reachable(tx, id)
888 }
889
890 /// Drop every session of this user except `keep`.
891 ///
892 /// Called after any credential change. Otherwise a session stolen before
893 /// the change keeps working for its full 30 days.
894 pub async fn delete_other_sessions(&self, user_id: i64, keep: &str) -> DbResult<()> {
895 let c = self.0.lock().await;
896 c.execute(
897 "DELETE FROM sessions WHERE user_id = ?1 AND token != ?2",
898 params![user_id, keep],
899 )?;
900 Ok(())
901 }
902
903 /// This account's WebAuthn user handle, creating it on first use.
904 ///
905 /// Stable for the lifetime of the account: a discoverable passkey stores
906 /// this value and hands it back at sign-in, so changing it would orphan
907 /// every existing passkey.
908 pub async fn user_webauthn_id(&self, id: i64) -> DbResult<Uuid> {
909 let c = self.0.lock().await;
910 let existing: Option<String> = c
911 .query_row("SELECT webauthn_id FROM users WHERE id = ?1", [id], |r| {
912 r.get(0)
913 })
914 .optional()?
915 .flatten();
916 if let Some(parsed) = existing.as_deref().and_then(|s| Uuid::parse_str(s).ok()) {
917 return Ok(parsed);
918 }
919 let fresh = Uuid::new_v4();
920 c.execute(
921 "UPDATE users SET webauthn_id = ?1 WHERE id = ?2",
922 params![fresh.to_string(), id],
923 )?;
924 Ok(fresh)
925 }
926
927 /// The account a discoverable credential's user handle points at.
928 pub async fn find_user_by_webauthn_id(&self, wid: &Uuid) -> DbResult<Option<User>> {
929 let c = self.0.lock().await;
930 c.query_row(
931 &format!("SELECT {USER_COLS} FROM users WHERE webauthn_id = ?1"),
932 [wid.to_string()],
933 map_user,
934 )
935 .optional()
936 }
937
938 pub async fn user_passkeys(&self, user_id: i64) -> DbResult<Vec<PasskeyRow>> {
939 let c = self.0.lock().await;
940 let mut stmt = c.prepare_cached(
941 "SELECT id, name, created_at, last_used_at, discoverable, passkey
942 FROM passkeys WHERE user_id = ?1 ORDER BY id",
943 )?;
944 let rows = stmt.query_map([user_id], map_passkey)?;
945 rows.collect()
946 }
947
948 pub async fn count_passkeys(&self, user_id: i64) -> DbResult<i64> {
949 let c = self.0.lock().await;
950 c.query_row(
951 "SELECT COUNT(*) FROM passkeys WHERE user_id = ?1",
952 [user_id],
953 |r| r.get(0),
954 )
955 }
956
957 /// The per-install secret behind the decoy credentials a named passkey
958 /// challenge is padded with. Created on first use, so no migration.
959 pub async fn decoy_secret(&self) -> DbResult<String> {
960 let c = self.0.lock().await;
961 let existing: Option<String> = c
962 .query_row(
963 "SELECT value FROM meta WHERE key = 'decoy_secret'",
964 [],
965 |r| r.get(0),
966 )
967 .optional()?;
968 if let Some(secret) = existing {
969 return Ok(secret);
970 }
971 let fresh = crate::auth::random_token();
972 c.execute(
973 "INSERT OR REPLACE INTO meta (key, value) VALUES ('decoy_secret', ?1)",
974 [&fresh],
975 )?;
976 Ok(fresh)
977 }
978
979 /// The length in bytes of every credential id registered on this server,
980 /// one entry per credential.
981 ///
982 /// Decoys draw their length from this list. Authenticators disagree on it —
983 /// a security key emits 64 bytes, a platform key often 16 or 32 — so a
984 /// decoy of one fixed length would stand out next to the real entries.
985 ///
986 /// Duplicates are kept deliberately. Drawing from the distinct lengths
987 /// would make a rare length as likely as a common one, and decoys that do
988 /// not match how the install actually looks are the thing worth avoiding.
989 pub async fn cred_id_lengths(&self) -> DbResult<Vec<usize>> {
990 let c = self.0.lock().await;
991 let mut stmt = c.prepare("SELECT length(cred_id) FROM passkeys ORDER BY id")?;
992 let rows = stmt.query_map([], |r| r.get::<_, i64>(0))?;
993 rows.map(|r| r.map(|n| n.max(1) as usize))
994 .collect::<Result<Vec<_>, _>>()
995 }
996
997 /// Store a freshly registered passkey. A duplicate `cred_id` is a unique
998 /// violation, which is the intended answer: the same credential must not
999 /// be registered twice, not even to a second account.
1000 ///
1001 /// `None` means the account is already at [`PASSKEY_LIMIT`]. The count and
1002 /// the insert share one transaction, so two registrations landing together
1003 /// cannot put the account one over and give its sign-in challenge a
1004 /// telltale length.
1005 pub async fn add_passkey(
1006 &self,
1007 user_id: i64,
1008 cred_id: &[u8],
1009 passkey: &str,
1010 name: &str,
1011 discoverable: Option<bool>,
1012 ) -> DbResult<Option<PasskeyRow>> {
1013 let mut c = self.0.lock().await;
1014 let tx = c.transaction()?;
1015 let held: i64 = tx.query_row(
1016 "SELECT COUNT(*) FROM passkeys WHERE user_id = ?1",
1017 [user_id],
1018 |r| r.get(0),
1019 )?;
1020 if held as usize >= PASSKEY_LIMIT {
1021 return Ok(None);
1022 }
1023 tx.execute(
1024 "INSERT INTO passkeys (user_id, cred_id, passkey, name, discoverable, created_at)
1025 VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
1026 params![user_id, cred_id, passkey, name, discoverable, now()],
1027 )?;
1028 let id = tx.last_insert_rowid();
1029 let row = tx.query_row(
1030 "SELECT id, name, created_at, last_used_at, discoverable, passkey
1031 FROM passkeys WHERE id = ?1",
1032 [id],
1033 map_passkey,
1034 )?;
1035 tx.commit()?;
1036 Ok(Some(row))
1037 }
1038
1039 pub async fn delete_passkey(&self, id: i64, user_id: i64) -> DbResult<PasskeyDeleted> {
1040 let mut c = self.0.lock().await;
1041 let tx = c.transaction()?;
1042 let hit = tx.execute(
1043 "DELETE FROM passkeys WHERE id = ?1 AND user_id = ?2",
1044 params![id, user_id],
1045 )? > 0;
1046 if !hit {
1047 tx.rollback()?;
1048 return Ok(PasskeyDeleted::NotFound);
1049 }
1050 Ok(if commit_if_reachable(tx, user_id)? {
1051 PasskeyDeleted::Gone
1052 } else {
1053 PasskeyDeleted::LastCredential
1054 })
1055 }
1056
1057 /// Record a successful assertion: the re-serialized credential (its
1058 /// signature counter and backup flags may have moved) and the time.
1059 pub async fn passkey_used(&self, id: i64, passkey: &str) -> DbResult<()> {
1060 let c = self.0.lock().await;
1061 c.execute(
1062 "UPDATE passkeys SET passkey = ?1, last_used_at = ?2 WHERE id = ?3",
1063 params![passkey, now(), id],
1064 )?;
1065 Ok(())
1066 }
1067
1068 // ---------- app passwords (WebDAV) ----------
1069
1070 pub async fn app_passwords(&self, user_id: i64) -> DbResult<Vec<AppPasswordInfo>> {
1071 let c = self.0.lock().await;
1072 let mut stmt = c.prepare_cached(
1073 "SELECT id, name, created_at, last_used_at
1074 FROM app_passwords WHERE user_id = ?1 ORDER BY id",
1075 )?;
1076 let rows = stmt.query_map([user_id], map_app_password)?;
1077 rows.collect()
1078 }
1079
1080 /// `None` means the account is already at [`APP_PASSWORD_LIMIT`]. Count
1081 /// and insert share one transaction, so the cap cannot be raced.
1082 pub async fn add_app_password(
1083 &self,
1084 user_id: i64,
1085 name: &str,
1086 secret_hash: &str,
1087 ) -> DbResult<Option<AppPasswordInfo>> {
1088 let mut c = self.0.lock().await;
1089 let tx = c.transaction()?;
1090 let held: i64 = tx.query_row(
1091 "SELECT COUNT(*) FROM app_passwords WHERE user_id = ?1",
1092 [user_id],
1093 |r| r.get(0),
1094 )?;
1095 if held as usize >= APP_PASSWORD_LIMIT {
1096 return Ok(None);
1097 }
1098 tx.execute(
1099 "INSERT INTO app_passwords (user_id, name, secret_hash, created_at)
1100 VALUES (?1, ?2, ?3, ?4)",
1101 params![user_id, name, secret_hash, now()],
1102 )?;
1103 let row = tx.query_row(
1104 "SELECT id, name, created_at, last_used_at FROM app_passwords WHERE id = ?1",
1105 [tx.last_insert_rowid()],
1106 map_app_password,
1107 )?;
1108 tx.commit()?;
1109 Ok(Some(row))
1110 }
1111
1112 /// `false` means no row matched: an unknown id, or someone else's.
1113 ///
1114 /// No reachability check, unlike [`Db::delete_passkey`]: an app password
1115 /// never signs in to the web UI.
1116 pub async fn delete_app_password(&self, id: i64, user_id: i64) -> DbResult<bool> {
1117 let c = self.0.lock().await;
1118 Ok(c.execute(
1119 "DELETE FROM app_passwords WHERE id = ?1 AND user_id = ?2",
1120 params![id, user_id],
1121 )? > 0)
1122 }
1123
1124 /// The account an app password opens. Stamps `last_used_at` on the way.
1125 ///
1126 /// Inactive accounts are excluded here, because nothing downstream in the
1127 /// WebDAV path looks at the flag.
1128 pub async fn user_by_app_password(&self, secret_hash: &str) -> DbResult<Option<User>> {
1129 let c = self.0.lock().await;
1130 let user = c
1131 .query_row(
1132 &format!(
1133 "SELECT {USER_COLS_U}
1134 FROM app_passwords a
1135 JOIN users u ON u.id = a.user_id
1136 WHERE a.secret_hash = ?1 AND u.active = 1"
1137 ),
1138 [secret_hash],
1139 map_user,
1140 )
1141 .optional()?;
1142 if user.is_some() {
1143 // A mount re-sends its credential on every request. An hour's
1144 // resolution keeps that from writing the stamp on each one.
1145 c.execute(
1146 "UPDATE app_passwords SET last_used_at = ?1
1147 WHERE secret_hash = ?2 AND (last_used_at IS NULL OR last_used_at < ?3)",
1148 params![now(), secret_hash, stamp(chrono::Duration::hours(1))],
1149 )?;
1150 }
1151 Ok(user)
1152 }
1153
1154 /// Delete a user. `false` means no row matched.
1155 /// The stored spelling of the name, which a Basic login may differ from
1156 /// in case.
1157 pub async fn user_name(&self, id: i64) -> DbResult<Option<String>> {
1158 let c = self.0.lock().await;
1159 let mut stmt = c.prepare_cached("SELECT name FROM users WHERE id = ?1")?;
1160 stmt.query_row([id], |r| r.get(0)).optional()
1161 }
1162
1163 /// Deletes an account after `ops`, in one transaction. The ops make other
1164 /// principals' objects forget it (`pim_schedule::forget`).
1165 pub async fn delete_user(&self, id: i64, ops: &[PimOp]) -> DbResult<bool> {
1166 let mut c = self.0.lock().await;
1167 let tx = c.transaction()?;
1168 apply_ops(&tx, ops)?;
1169 let deleted = tx.execute("DELETE FROM users WHERE id = ?1", [id])? > 0;
1170 tx.commit()?;
1171 Ok(deleted)
1172 }
1173
1174 // ---------- shares ----------
1175
1176 #[allow(clippy::too_many_arguments)] // one row's columns, all required
1177 pub async fn create_share(
1178 &self,
1179 creator_id: i64,
1180 token: &str,
1181 target: &str,
1182 is_file: bool,
1183 mode: Mode,
1184 expires_at: Option<&str>,
1185 password_hash: Option<&str>,
1186 ) -> DbResult<ShareRow> {
1187 let c = self.0.lock().await;
1188 c.execute(
1189 "INSERT INTO shares
1190 (token, creator_id, target, is_file, mode, created_at, expires_at, password_hash)
1191 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
1192 params![
1193 token,
1194 creator_id,
1195 target,
1196 is_file as i64,
1197 mode.as_str(),
1198 now(),
1199 expires_at,
1200 password_hash
1201 ],
1202 )?;
1203 let id = c.last_insert_rowid();
1204 Ok(ShareRow {
1205 id,
1206 token: token.to_string(),
1207 creator_id,
1208 target: target.to_string(),
1209 is_file,
1210 mode,
1211 created_at: now(),
1212 expires_at: expires_at.map(|s| s.to_string()),
1213 password_hash: password_hash.map(|s| s.to_string()),
1214 })
1215 }
1216
1217 /// Record that a visitor entered `share_id`'s password, and return the
1218 /// token that proves it (the value of their unlock cookie).
1219 pub async fn create_share_unlock(&self, share_id: i64) -> DbResult<String> {
1220 let token = crate::auth::random_token();
1221 let c = self.0.lock().await;
1222 c.execute(
1223 "INSERT INTO share_unlocks (token, share_id, created_at) VALUES (?1, ?2, ?3)",
1224 params![token, share_id, now()],
1225 )?;
1226 Ok(token)
1227 }
1228
1229 /// Delete what has outlived its use. Returns how many shares and feed
1230 /// links, and how many unlock rows went.
1231 ///
1232 /// Housekeeping only: every read already refuses an expired share, so
1233 /// nothing here is load-bearing and the interval does not matter.
1234 pub async fn sweep(&self) -> DbResult<(usize, usize)> {
1235 let c = self.0.lock().await;
1236 // SQLite parses the timestamp instead of comparing it as text:
1237 // `expires_at` is stored exactly as the client sent it and may carry
1238 // an offset or fractional seconds. An unparseable one yields NULL and
1239 // so survives, which is what `ShareRow::is_expired` decided too.
1240 let mut shares = 0;
1241 for table in ["shares", "pim_links"] {
1242 shares += c.execute(
1243 &format!(
1244 "DELETE FROM {table}
1245 WHERE expires_at IS NOT NULL AND julianday(expires_at) <= julianday('now')"
1246 ),
1247 [],
1248 )?;
1249 }
1250 // The cookie carrying an unlock is a session cookie, so it is already
1251 // gone from every browser. Deleting a share takes its own with it.
1252 let unlocks = c.execute(
1253 "DELETE FROM share_unlocks WHERE created_at < ?1",
1254 [stamp(chrono::Duration::days(UNLOCK_MAX_AGE_DAYS))],
1255 )?;
1256 Ok((shares, unlocks))
1257 }
1258
1259 /// Whether `token` is a live unlock for `share_id`.
1260 ///
1261 /// The share id is part of the lookup, so an unlock for one share cannot
1262 /// open another.
1263 pub async fn share_unlock_valid(&self, token: &str, share_id: i64) -> DbResult<bool> {
1264 let c = self.0.lock().await;
1265 let mut stmt =
1266 c.prepare_cached("SELECT 1 FROM share_unlocks WHERE token = ?1 AND share_id = ?2")?;
1267 Ok(stmt
1268 .query_row(params![token, share_id], |_| Ok(()))
1269 .optional()?
1270 .is_some())
1271 }
1272
1273 pub async fn share_by_token(&self, token: &str) -> DbResult<Option<ShareRow>> {
1274 let c = self.0.lock().await;
1275 let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at,
1276 password_hash
1277 FROM shares WHERE token = ?1";
1278 let mut stmt = c.prepare_cached(sql)?;
1279 stmt.query_row([token], map_share).optional()
1280 }
1281
1282 pub async fn user_shares(&self, creator_id: i64) -> DbResult<Vec<ShareRow>> {
1283 let c = self.0.lock().await;
1284 let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at,
1285 password_hash
1286 FROM shares WHERE creator_id = ?1 ORDER BY id DESC";
1287 let mut stmt = c.prepare_cached(sql)?;
1288 let rows = stmt.query_map([creator_id], map_share)?;
1289 rows.collect()
1290 }
1291
1292 /// Revoke every share on `target` or on anything beneath it. Returns how
1293 /// many were dropped.
1294 ///
1295 /// Called when a path stops meaning what it meant: the item was deleted,
1296 /// renamed, or moved away. A share names a path, and a path is not a
1297 /// stable identity, so leaving the row behind would let a *new* item that
1298 /// later takes the freed path inherit the old link's audience.
1299 ///
1300 /// `substr` rather than `LIKE`: a target containing `%` or `_` would make
1301 /// a `LIKE` pattern over-match and revoke unrelated shares.
1302 pub async fn revoke_shares_at(&self, target: &str) -> DbResult<usize> {
1303 let c = self.0.lock().await;
1304 c.execute(
1305 "DELETE FROM shares
1306 WHERE target = ?1 OR substr(target, 1, length(?1) + 1) = ?1 || '/'",
1307 [target],
1308 )
1309 }
1310
1311 /// Delete one of `creator_id`'s shares. `false` means no row matched.
1312 pub async fn delete_share(&self, id: i64, creator_id: i64) -> DbResult<bool> {
1313 let c = self.0.lock().await;
1314 let n = c.execute(
1315 "DELETE FROM shares WHERE id = ?1 AND creator_id = ?2",
1316 params![id, creator_id],
1317 )?;
1318 Ok(n > 0)
1319 }
1320
1321 /// Every share on the server with its creator. Grouped by account name,
1322 /// newest link within an account first.
1323 ///
1324 /// The join cannot miss: `shares.creator_id` cascades on delete, so a share
1325 /// never outlives the account that made it.
1326 pub async fn all_shares_with_creators(&self) -> DbResult<Vec<ShareWithCreator>> {
1327 let c = self.0.lock().await;
1328 // Columns 0..8 are `map_share`'s order, unchanged from `user_shares`.
1329 let sql = "SELECT s.id, s.token, s.creator_id, s.target, s.is_file, s.mode,
1330 s.created_at, s.expires_at, s.password_hash,
1331 u.name, u.active != 0
1332 FROM shares s
1333 JOIN users u ON u.id = s.creator_id
1334 ORDER BY u.name COLLATE NOCASE, s.id DESC";
1335 let mut stmt = c.prepare_cached(sql)?;
1336 let rows = stmt.query_map([], |r| {
1337 Ok(ShareWithCreator {
1338 share: map_share(r)?,
1339 creator_name: r.get(9)?,
1340 creator_active: r.get(10)?,
1341 })
1342 })?;
1343 rows.collect()
1344 }
1345
1346 /// Revoke a share whoever created it. The owner-scoped
1347 /// [`Self::delete_share`] is what the user-facing API uses.
1348 pub async fn admin_delete_share(&self, id: i64) -> DbResult<bool> {
1349 let c = self.0.lock().await;
1350 Ok(c.execute("DELETE FROM shares WHERE id = ?1", [id])? > 0)
1351 }
1352
1353 // ---------- settings ----------
1354
1355 /// Folders excluded from search, as paths relative to the server root.
1356 ///
1357 /// Stored as one JSON array in a settings row. A table of its own would
1358 /// be overkill for a hand-edited list read once per search.
1359 pub async fn search_excludes(&self) -> DbResult<Vec<String>> {
1360 let raw = self.get_setting("search_excludes").await?;
1361 // Normalised on read as well as on write. A value edited straight
1362 // into the database would otherwise never match: `is_excluded`
1363 // compares against paths with no slash at either end.
1364 let clean = |v: Vec<String>| -> Vec<String> {
1365 v.into_iter()
1366 .map(|p| p.trim().replace('\\', "/").trim_matches('/').to_string())
1367 .filter(|p| !p.is_empty() && p != ".")
1368 .collect()
1369 };
1370 // A hand-edited, unparseable value falls back to no exclusions,
1371 // the same as an absent row.
1372 Ok(raw
1373 .as_deref()
1374 .and_then(|v| serde_json::from_str::<Vec<String>>(v).ok())
1375 .map(clean)
1376 .unwrap_or_default())
1377 }
1378
1379 pub async fn set_search_excludes(&self, paths: &[String]) -> DbResult<()> {
1380 let json = serde_json::to_string(paths).unwrap_or_else(|_| "[]".to_string());
1381 self.set_setting("search_excludes", &json).await
1382 }
1383
1384 pub async fn get_setting(&self, key: &str) -> DbResult<Option<String>> {
1385 let c = self.0.lock().await;
1386 let mut stmt = c.prepare_cached("SELECT value FROM settings WHERE key = ?1")?;
1387 stmt.query_row([key], |r| r.get(0)).optional()
1388 }
1389
1390 pub async fn set_setting(&self, key: &str, value: &str) -> DbResult<()> {
1391 let c = self.0.lock().await;
1392 c.execute(
1393 "INSERT INTO settings (key, value) VALUES (?1, ?2)
1394 ON CONFLICT(key) DO UPDATE SET value = ?2",
1395 params![key, value],
1396 )?;
1397 Ok(())
1398 }
1399
1400 // ---------- CalDAV and CardDAV ----------
1401
1402 /// Gives an account's principal a calendar, an address book and a
1403 /// scheduling inbox when it has none.
1404 pub async fn pim_ensure_defaults(&self, principal_id: i64) -> DbResult<()> {
1405 let c = self.0.lock().await;
1406 let mut stmt = c.prepare_cached(
1407 "INSERT INTO pim_collections (principal_id, kind, slug, displayname, components, created_at)
1408 SELECT ?1, ?2, 'default', ?3, ?4, ?5
1409 WHERE NOT EXISTS (SELECT 1 FROM pim_collections
1410 WHERE principal_id = ?1 AND kind = ?2 AND slug != 'inbox')",
1411 )?;
1412 let now = now();
1413 stmt.execute(params![
1414 principal_id,
1415 "cal",
1416 "Calendar",
1417 "VEVENT,VTODO,VJOURNAL",
1418 now
1419 ])?;
1420 stmt.execute(params![principal_id, "card", "Contacts", "", now])?;
1421 ensure_inbox(&c, principal_id)
1422 }
1423
1424 /// The scheduling inbox alone, for rooms and resources.
1425 pub async fn pim_ensure_inbox(&self, principal_id: i64) -> DbResult<()> {
1426 let c = self.0.lock().await;
1427 ensure_inbox(&c, principal_id)
1428 }
1429
1430 /// The calendar that receives new invitations of `component`: the
1431 /// oldest one that takes it.
1432 pub async fn pim_calendar_for(
1433 &self,
1434 principal_id: i64,
1435 component: &str,
1436 ) -> DbResult<Option<PimCollection>> {
1437 let c = self.0.lock().await;
1438 let mut stmt = c.prepare_cached(&format!(
1439 "SELECT {PIM_COLLECTION_COLS} FROM pim_collections
1440 WHERE principal_id = ?1 AND kind = 'cal' AND slug != 'inbox'
1441 AND ',' || components || ',' LIKE '%,' || ?2 || ',%'
1442 ORDER BY id LIMIT 1"
1443 ))?;
1444 stmt.query_row(params![principal_id, component], map_pim_collection)
1445 .optional()
1446 }
1447
1448 /// The object with `uid` in any of the user's own calendars.
1449 pub async fn pim_find_uid(
1450 &self,
1451 principal_id: i64,
1452 uid: &str,
1453 ) -> DbResult<Option<(i64, PimObject, Vec<u8>)>> {
1454 let c = self.0.lock().await;
1455 let mut stmt = c.prepare_cached(
1456 "SELECT o.name, o.uid, o.component, o.etag, length(o.data), o.modified_at,
1457 o.schedule_tag, o.data, o.collection_id
1458 FROM pim_objects o JOIN pim_collections c ON c.id = o.collection_id
1459 WHERE c.principal_id = ?1 AND c.kind = 'cal' AND c.slug != 'inbox' AND o.uid = ?2
1460 ORDER BY o.id LIMIT 1",
1461 )?;
1462 stmt.query_row(params![principal_id, uid], |r| {
1463 Ok((r.get(8)?, map_pim_object(r)?, r.get(7)?))
1464 })
1465 .optional()
1466 }
1467
1468 /// The name of another object in the collection that has `uid`.
1469 pub async fn pim_uid_holder(
1470 &self,
1471 collection_id: i64,
1472 uid: &str,
1473 name: &str,
1474 ) -> DbResult<Option<String>> {
1475 let c = self.0.lock().await;
1476 c.query_row(
1477 "SELECT name FROM pim_objects WHERE collection_id = ?1 AND uid = ?2 AND name != ?3",
1478 params![collection_id, uid, name],
1479 |r| r.get(0),
1480 )
1481 .optional()
1482 }
1483
1484 /// Several object writes in one transaction.
1485 pub async fn pim_apply(&self, ops: &[PimOp]) -> DbResult<()> {
1486 let mut c = self.0.lock().await;
1487 let tx = c.transaction()?;
1488 apply_ops(&tx, ops)?;
1489 tx.commit()?;
1490 Ok(())
1491 }
1492
1493 pub async fn pim_collections(
1494 &self,
1495 principal_id: i64,
1496 kind: PimKind,
1497 ) -> DbResult<Vec<PimCollection>> {
1498 let c = self.0.lock().await;
1499 let mut stmt = c.prepare_cached(&format!(
1500 "SELECT {PIM_COLLECTION_COLS} FROM pim_collections
1501 WHERE principal_id = ?1 AND kind = ?2 ORDER BY id"
1502 ))?;
1503 stmt.query_map(params![principal_id, kind.as_str()], map_pim_collection)?
1504 .collect()
1505 }
1506
1507 pub async fn pim_collection(
1508 &self,
1509 principal_id: i64,
1510 kind: PimKind,
1511 slug: &str,
1512 ) -> DbResult<Option<PimCollection>> {
1513 let c = self.0.lock().await;
1514 let mut stmt = c.prepare_cached(&format!(
1515 "SELECT {PIM_COLLECTION_COLS} FROM pim_collections
1516 WHERE principal_id = ?1 AND kind = ?2 AND slug = ?3"
1517 ))?;
1518 stmt.query_row(
1519 params![principal_id, kind.as_str(), slug],
1520 map_pim_collection,
1521 )
1522 .optional()
1523 }
1524
1525 /// `false` if the slug is taken. `id` and `seq` of `new` are ignored.
1526 pub async fn pim_create_collection(
1527 &self,
1528 principal_id: i64,
1529 kind: PimKind,
1530 new: &PimCollection,
1531 ) -> DbResult<bool> {
1532 let c = self.0.lock().await;
1533 let n = c.execute(
1534 "INSERT OR IGNORE INTO pim_collections (principal_id, kind, slug, displayname,
1535 description, color, timezone, sort_order, components, transparent, created_at)
1536 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)",
1537 params![
1538 principal_id,
1539 kind.as_str(),
1540 new.slug,
1541 new.displayname,
1542 new.description,
1543 new.color,
1544 new.timezone,
1545 new.sort_order,
1546 new.components,
1547 new.transparent,
1548 now()
1549 ],
1550 )?;
1551 Ok(n > 0)
1552 }
1553
1554 /// Writes the properties of `col` and counts it as a change.
1555 pub async fn pim_update_collection(&self, col: &PimCollection) -> DbResult<()> {
1556 let c = self.0.lock().await;
1557 c.execute(
1558 "UPDATE pim_collections SET displayname = ?2, description = ?3, color = ?4,
1559 timezone = ?5, sort_order = ?6, transparent = ?7, seq = seq + 1
1560 WHERE id = ?1",
1561 params![
1562 col.id,
1563 col.displayname,
1564 col.description,
1565 col.color,
1566 col.timezone,
1567 col.sort_order,
1568 col.transparent
1569 ],
1570 )?;
1571 Ok(())
1572 }
1573
1574 pub async fn pim_delete_collection(&self, id: i64) -> DbResult<()> {
1575 let c = self.0.lock().await;
1576 c.execute("DELETE FROM pim_collections WHERE id = ?1", [id])?;
1577 Ok(())
1578 }
1579
1580 pub async fn pim_objects(&self, collection_id: i64) -> DbResult<Vec<PimObject>> {
1581 let c = self.0.lock().await;
1582 let mut stmt = c.prepare_cached(&format!(
1583 "SELECT {PIM_OBJECT_COLS} FROM pim_objects WHERE collection_id = ?1 ORDER BY name"
1584 ))?;
1585 stmt.query_map([collection_id], map_pim_object)?.collect()
1586 }
1587
1588 pub async fn pim_object(
1589 &self,
1590 collection_id: i64,
1591 name: &str,
1592 ) -> DbResult<Option<(PimObject, Vec<u8>)>> {
1593 let c = self.0.lock().await;
1594 let mut stmt = c.prepare_cached(&format!(
1595 "SELECT {PIM_OBJECT_COLS}, data FROM pim_objects
1596 WHERE collection_id = ?1 AND name = ?2"
1597 ))?;
1598 stmt.query_row(params![collection_id, name], |r| {
1599 Ok((map_pim_object(r)?, r.get(7)?))
1600 })
1601 .optional()
1602 }
1603
1604 pub async fn pim_objects_with_data(
1605 &self,
1606 collection_id: i64,
1607 ) -> DbResult<Vec<(PimObject, Vec<u8>)>> {
1608 let c = self.0.lock().await;
1609 let mut stmt = c.prepare_cached(&format!(
1610 "SELECT {PIM_OBJECT_COLS}, data FROM pim_objects WHERE collection_id = ?1 ORDER BY name"
1611 ))?;
1612 stmt.query_map([collection_id], |r| Ok((map_pim_object(r)?, r.get(7)?)))?
1613 .collect()
1614 }
1615
1616 /// `(name, seq, deleted)` of the members changed after `since`, oldest
1617 /// first. Without `since`, the members that exist.
1618 pub async fn pim_changes(
1619 &self,
1620 collection_id: i64,
1621 since: Option<i64>,
1622 ) -> DbResult<Vec<(String, i64, bool)>> {
1623 let c = self.0.lock().await;
1624 let mut stmt = c.prepare_cached(
1625 "SELECT name, seq, deleted != 0 FROM pim_changes
1626 WHERE collection_id = ?1 AND (?2 IS NULL AND deleted = 0 OR seq > ?2)
1627 ORDER BY seq",
1628 )?;
1629 stmt.query_map(params![collection_id, since], |r| {
1630 Ok((r.get(0)?, r.get(1)?, r.get(2)?))
1631 })?
1632 .collect()
1633 }
1634
1635 /// Moves an object to `to_name` in collection `to`, which may be the
1636 /// same collection. `Updated` means an object at the destination was
1637 /// replaced.
1638 pub async fn pim_move_object(
1639 &self,
1640 from: i64,
1641 name: &str,
1642 to: i64,
1643 to_name: &str,
1644 overwrite: bool,
1645 cond: &Precondition,
1646 ) -> DbResult<PimWrite> {
1647 let mut c = self.0.lock().await;
1648 let tx = c.transaction()?;
1649 let source: Option<(String, String)> = tx
1650 .query_row(
1651 "SELECT etag, uid FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
1652 params![from, name],
1653 |r| Ok((r.get(0)?, r.get(1)?)),
1654 )
1655 .optional()?;
1656 let Some((etag, uid)) = source else {
1657 return Ok(PimWrite::NotFound);
1658 };
1659 if !cond.allows(Some(&etag)) {
1660 return Ok(PimWrite::PreconditionFailed);
1661 }
1662 let taken: bool = tx.query_row(
1663 "SELECT EXISTS (SELECT 1 FROM pim_objects WHERE collection_id = ?1 AND name = ?2)",
1664 params![to, to_name],
1665 |r| r.get(0),
1666 )?;
1667 if taken && !overwrite {
1668 return Ok(PimWrite::PreconditionFailed);
1669 }
1670 let holder: Option<String> = tx
1671 .query_row(
1672 "SELECT name FROM pim_objects WHERE collection_id = ?1 AND uid = ?2
1673 AND name != ?3 AND NOT (collection_id = ?4 AND name = ?5)",
1674 params![to, uid, to_name, from, name],
1675 |r| r.get(0),
1676 )
1677 .optional()?;
1678 if let Some(holder) = holder {
1679 return Ok(PimWrite::UidConflict(holder));
1680 }
1681 tx.execute(
1682 "DELETE FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
1683 params![to, to_name],
1684 )?;
1685 tx.execute(
1686 "UPDATE pim_objects SET collection_id = ?3, name = ?4, modified_at = ?5
1687 WHERE collection_id = ?1 AND name = ?2",
1688 params![from, name, to, to_name, now()],
1689 )?;
1690 record_pim_change(&tx, from, name, true)?;
1691 record_pim_change(&tx, to, to_name, false)?;
1692 tx.commit()?;
1693 Ok(if taken {
1694 PimWrite::Updated
1695 } else {
1696 PimWrite::Created
1697 })
1698 }
1699
1700 // ---------- principals, sharing, rooms ----------
1701
1702 /// An account, room or resource by URL name. Disabled accounts are
1703 /// invisible.
1704 pub async fn pim_principal(&self, name: &str) -> DbResult<Option<PimPrincipal>> {
1705 let c = self.0.lock().await;
1706 let mut stmt = c.prepare_cached(&format!(
1707 "SELECT {PRINCIPAL_COLS} FROM {PRINCIPALS} WHERE p.name = ?1 AND {VISIBLE}"
1708 ))?;
1709 stmt.query_row([name], map_principal).optional()
1710 }
1711
1712 pub async fn pim_principals(&self) -> DbResult<Vec<PimPrincipal>> {
1713 let c = self.0.lock().await;
1714 let mut stmt = c.prepare_cached(&format!(
1715 "SELECT {PRINCIPAL_COLS} FROM {PRINCIPALS} WHERE {VISIBLE} ORDER BY p.id"
1716 ))?;
1717 stmt.query_map([], map_principal)?.collect()
1718 }
1719
1720 /// The principal of an account.
1721 pub async fn principal_of(&self, user_id: i64) -> DbResult<i64> {
1722 let c = self.0.lock().await;
1723 c.prepare_cached("SELECT id FROM principals WHERE user_id = ?1")?
1724 .query_row([user_id], |r| r.get(0))
1725 }
1726
1727 /// Whether an account, room or resource has this name.
1728 pub async fn name_taken(&self, name: &str) -> DbResult<bool> {
1729 let c = self.0.lock().await;
1730 c.prepare_cached("SELECT EXISTS (SELECT 1 FROM principals WHERE name = ?1)")?
1731 .query_row([name], |r| r.get(0))
1732 }
1733
1734 /// The collection `collection_id` as lent to `user_id`, with its owner's
1735 /// name and the mode.
1736 pub async fn pim_shared_collection(
1737 &self,
1738 user_id: i64,
1739 kind: PimKind,
1740 collection_id: i64,
1741 ) -> DbResult<Option<(PimCollection, String, PimShareMode)>> {
1742 let c = self.0.lock().await;
1743 let mut stmt = c.prepare_cached(&format!(
1744 "SELECT {PIM_COLLECTION_COLS_C}, p.name, s.mode
1745 FROM pim_shares s
1746 JOIN pim_collections c ON c.id = s.collection_id
1747 JOIN principals p ON p.id = c.principal_id
1748 WHERE s.user_id = ?1 AND c.kind = ?2 AND c.id = ?3"
1749 ))?;
1750 stmt.query_row(params![user_id, kind.as_str(), collection_id], map_shared)
1751 .optional()
1752 }
1753
1754 /// Every collection of `kind` lent to `user_id`.
1755 pub async fn pim_shared_collections(
1756 &self,
1757 user_id: i64,
1758 kind: PimKind,
1759 ) -> DbResult<Vec<(PimCollection, String, PimShareMode)>> {
1760 let c = self.0.lock().await;
1761 let mut stmt = c.prepare_cached(&format!(
1762 "SELECT {PIM_COLLECTION_COLS_C}, p.name, s.mode
1763 FROM pim_shares s
1764 JOIN pim_collections c ON c.id = s.collection_id
1765 JOIN principals p ON p.id = c.principal_id
1766 WHERE s.user_id = ?1 AND c.kind = ?2 ORDER BY c.id"
1767 ))?;
1768 stmt.query_map(params![user_id, kind.as_str()], map_shared)?
1769 .collect()
1770 }
1771
1772 /// The owner and kind of a collection.
1773 pub async fn pim_collection_by_id(
1774 &self,
1775 id: i64,
1776 ) -> DbResult<Option<(i64, PimKind, PimCollection)>> {
1777 let c = self.0.lock().await;
1778 let mut stmt = c.prepare_cached(&format!(
1779 "SELECT {PIM_COLLECTION_COLS}, principal_id, kind FROM pim_collections WHERE id = ?1"
1780 ))?;
1781 stmt.query_row([id], |r| {
1782 Ok((
1783 r.get(10)?,
1784 PimKind::parse(&r.get::<_, String>(11)?),
1785 map_pim_collection(r)?,
1786 ))
1787 })
1788 .optional()
1789 }
1790
1791 pub async fn pim_create_link(
1792 &self,
1793 collection_id: i64,
1794 token: &str,
1795 busy_only: bool,
1796 expires_at: Option<&str>,
1797 password_hash: Option<&str>,
1798 ) -> DbResult<PimLink> {
1799 let c = self.0.lock().await;
1800 let created_at = now();
1801 c.execute(
1802 "INSERT INTO pim_links
1803 (token, collection_id, busy_only, created_at, expires_at, password_hash)
1804 VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
1805 params![
1806 token,
1807 collection_id,
1808 busy_only,
1809 created_at,
1810 expires_at,
1811 password_hash
1812 ],
1813 )?;
1814 Ok(PimLink {
1815 id: c.last_insert_rowid(),
1816 token: token.to_string(),
1817 collection_id,
1818 busy_only,
1819 created_at,
1820 expires_at: expires_at.map(str::to_string),
1821 password_hash: password_hash.map(str::to_string),
1822 })
1823 }
1824
1825 pub async fn pim_links(&self, collection_id: i64) -> DbResult<Vec<PimLink>> {
1826 let c = self.0.lock().await;
1827 let mut stmt = c.prepare_cached(&format!(
1828 "SELECT {PIM_LINK_COLS} FROM pim_links WHERE collection_id = ?1 ORDER BY id"
1829 ))?;
1830 stmt.query_map([collection_id], map_pim_link)?.collect()
1831 }
1832
1833 pub async fn pim_link_by_token(&self, token: &str) -> DbResult<Option<PimLink>> {
1834 let c = self.0.lock().await;
1835 let mut stmt = c.prepare_cached(&format!(
1836 "SELECT {PIM_LINK_COLS} FROM pim_links WHERE token = ?1"
1837 ))?;
1838 stmt.query_row([token], map_pim_link).optional()
1839 }
1840
1841 /// `false` means no link of that collection had the id.
1842 pub async fn pim_delete_link(&self, collection_id: i64, id: i64) -> DbResult<bool> {
1843 let c = self.0.lock().await;
1844 Ok(c.execute(
1845 "DELETE FROM pim_links WHERE id = ?1 AND collection_id = ?2",
1846 params![id, collection_id],
1847 )? > 0)
1848 }
1849
1850 /// `(user id, name, mode)` of everyone a collection is lent to.
1851 pub async fn pim_shares(
1852 &self,
1853 collection_id: i64,
1854 ) -> DbResult<Vec<(i64, String, PimShareMode)>> {
1855 let c = self.0.lock().await;
1856 let mut stmt = c.prepare_cached(
1857 "SELECT u.id, u.name, s.mode FROM pim_shares s JOIN users u ON u.id = s.user_id
1858 WHERE s.collection_id = ?1 ORDER BY u.name",
1859 )?;
1860 stmt.query_map([collection_id], |r| {
1861 Ok((r.get(0)?, r.get(1)?, r.get::<_, SqlShareMode>(2)?.0))
1862 })?
1863 .collect()
1864 }
1865
1866 /// Lends a collection, or changes the mode of an existing loan.
1867 pub async fn pim_set_share(
1868 &self,
1869 collection_id: i64,
1870 user_id: i64,
1871 mode: PimShareMode,
1872 ) -> DbResult<()> {
1873 let c = self.0.lock().await;
1874 c.execute(
1875 "INSERT INTO pim_shares (collection_id, user_id, mode) VALUES (?1, ?2, ?3)
1876 ON CONFLICT (collection_id, user_id) DO UPDATE SET mode = ?3",
1877 params![collection_id, user_id, mode.as_str()],
1878 )?;
1879 Ok(())
1880 }
1881
1882 pub async fn pim_remove_share(&self, collection_id: i64, user_id: i64) -> DbResult<bool> {
1883 let c = self.0.lock().await;
1884 Ok(c.execute(
1885 "DELETE FROM pim_shares WHERE collection_id = ?1 AND user_id = ?2",
1886 params![collection_id, user_id],
1887 )? > 0)
1888 }
1889
1890 pub async fn rooms(&self) -> DbResult<Vec<PimPrincipal>> {
1891 let c = self.0.lock().await;
1892 let mut stmt = c.prepare_cached(&format!(
1893 "SELECT {PRINCIPAL_COLS} FROM {PRINCIPALS} WHERE p.user_id IS NULL ORDER BY p.name"
1894 ))?;
1895 stmt.query_map([], map_principal)?.collect()
1896 }
1897
1898 /// A room or resource with its booking calendar. `None` if the name is
1899 /// taken by any principal.
1900 pub async fn create_room(
1901 &self,
1902 name: &str,
1903 display_name: &str,
1904 kind: UserType,
1905 ) -> DbResult<Option<PimPrincipal>> {
1906 let mut c = self.0.lock().await;
1907 let tx = c.transaction()?;
1908 let inserted = tx.execute(
1909 "INSERT INTO principals (kind, name, display_name)
1910 SELECT ?1, ?2, ?3 WHERE NOT EXISTS (SELECT 1 FROM principals WHERE name = ?2)",
1911 params![kind_str(kind), name, display_name],
1912 )?;
1913 if inserted == 0 {
1914 return Ok(None);
1915 }
1916 let id = tx.last_insert_rowid();
1917 tx.execute(
1918 "INSERT INTO pim_collections (principal_id, kind, slug, displayname, components, created_at)
1919 VALUES (?1, 'cal', 'default', ?2, 'VEVENT', ?3)",
1920 params![id, display_name, now()],
1921 )?;
1922 tx.commit()?;
1923 Ok(Some(PimPrincipal {
1924 id,
1925 user_id: None,
1926 name: name.to_string(),
1927 display_name: Some(display_name.to_string()),
1928 kind,
1929 }))
1930 }
1931
1932 pub async fn set_room_display_name(&self, id: i64, display_name: &str) -> DbResult<bool> {
1933 let c = self.0.lock().await;
1934 Ok(c.execute(
1935 "UPDATE principals SET display_name = ?2 WHERE id = ?1 AND user_id IS NULL",
1936 params![id, display_name],
1937 )? > 0)
1938 }
1939
1940 /// Deletes a room or resource after `ops`, as [`Self::delete_user`] does.
1941 pub async fn delete_room(&self, id: i64, ops: &[PimOp]) -> DbResult<bool> {
1942 let mut c = self.0.lock().await;
1943 let tx = c.transaction()?;
1944 apply_ops(&tx, ops)?;
1945 let deleted = tx.execute(
1946 "DELETE FROM principals WHERE id = ?1 AND user_id IS NULL",
1947 [id],
1948 )? > 0;
1949 tx.commit()?;
1950 Ok(deleted)
1951 }
1952
1953 /// A principal by id, a disabled account's too.
1954 pub async fn pim_principal_by_id(&self, id: i64) -> DbResult<Option<PimPrincipal>> {
1955 let c = self.0.lock().await;
1956 let mut stmt = c.prepare_cached(&format!(
1957 "SELECT {PRINCIPAL_COLS} FROM {PRINCIPALS} WHERE p.id = ?1"
1958 ))?;
1959 stmt.query_row([id], map_principal).optional()
1960 }
1961
1962 /// `(collection id, object, data)` of the calendar objects of other
1963 /// principals whose text holds one of `needles`, compared without ASCII
1964 /// case. The inbox is left out: its messages are only a record.
1965 pub async fn pim_objects_mentioning(
1966 &self,
1967 principal_id: i64,
1968 needles: &[&str],
1969 ) -> DbResult<Vec<(i64, PimObject, Vec<u8>)>> {
1970 let c = self.0.lock().await;
1971 let any: Vec<String> = (0..needles.len())
1972 .map(|i| format!("instr(lower(CAST(o.data AS TEXT)), ?{}) > 0", i + 2))
1973 .collect();
1974 let mut stmt = c.prepare(&format!(
1975 "SELECT o.collection_id, {PIM_OBJECT_COLS_O}, o.data
1976 FROM pim_objects o JOIN pim_collections c ON c.id = o.collection_id
1977 WHERE c.principal_id != ?1 AND c.kind = 'cal' AND c.slug != 'inbox'
1978 AND ({})",
1979 any.join(" OR ")
1980 ))?;
1981 let lower: Vec<String> = needles.iter().map(|n| n.to_ascii_lowercase()).collect();
1982 let mut params: Vec<&dyn rusqlite::ToSql> = vec![&principal_id];
1983 params.extend(lower.iter().map(|n| n as &dyn rusqlite::ToSql));
1984 stmt.query_map(params.as_slice(), |r| {
1985 Ok((r.get(0)?, map_pim_object_at(r, 1)?, r.get(8)?))
1986 })?
1987 .collect()
1988 }
1989
1990 /// Every public feed link with its collection and owner, for the admin.
1991 pub async fn all_pim_links(&self) -> DbResult<Vec<PimLinkWithOwner>> {
1992 let c = self.0.lock().await;
1993 let mut stmt = c.prepare_cached(
1994 "SELECT l.id, l.token, l.collection_id, l.busy_only, l.created_at, l.expires_at,
1995 l.password_hash, coalesce(c.displayname, c.slug), c.kind, u.id, u.name,
1996 u.active != 0
1997 FROM pim_links l
1998 JOIN pim_collections c ON c.id = l.collection_id
1999 JOIN principals p ON p.id = c.principal_id
2000 JOIN users u ON u.id = p.user_id
2001 ORDER BY u.name COLLATE NOCASE, l.id DESC",
2002 )?;
2003 stmt.query_map([], |r| {
2004 Ok(PimLinkWithOwner {
2005 link: map_pim_link(r)?,
2006 collection_name: r.get(7)?,
2007 kind: PimKind::parse(&r.get::<_, String>(8)?),
2008 owner_id: r.get(9)?,
2009 owner_name: r.get(10)?,
2010 owner_active: r.get(11)?,
2011 })
2012 })?
2013 .collect()
2014 }
2015
2016 /// Revokes a feed link whoever made it.
2017 pub async fn admin_delete_pim_link(&self, id: i64) -> DbResult<bool> {
2018 let c = self.0.lock().await;
2019 Ok(c.execute("DELETE FROM pim_links WHERE id = ?1", [id])? > 0)
2020 }
2021
2022 /// Whether users may create writable (read-write) shares. Off by default;
2023 /// the admin setting gates it.
2024 pub async fn allow_writable_shares(&self) -> DbResult<bool> {
2025 Ok(self.get_setting("allow_writable_shares").await?.as_deref() == Some("1"))
2026 }
2027
2028 pub async fn set_allow_writable_shares(&self, v: bool) -> DbResult<()> {
2029 self.set_setting("allow_writable_shares", if v { "1" } else { "0" })
2030 .await
2031 }
2032}
2033
2034/// Write a new password hash and drop every session that was opened with the
2035/// old one.
2036///
2037/// The two belong together: a password is changed because the old one is
2038/// suspect (an admin resetting a compromised account), and a session that
2039/// survives the reset leaves whoever holds it signed in. Takes the
2040/// transaction so the caller can bundle it with its other edits.
2041fn set_password(tx: &rusqlite::Transaction<'_>, id: i64, pass_hash: &str) -> DbResult<()> {
2042 tx.execute(
2043 "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
2044 params![pass_hash, id],
2045 )?;
2046 tx.execute("DELETE FROM sessions WHERE user_id = ?1", [id])?;
2047 Ok(())
2048}
2049
2050/// Commit a credential change, or undo it if the account would be left with
2051/// no way to sign in.
2052///
2053/// The handlers check the same rules first, so the person gets a message that
2054/// says which rule. This is the guarantee behind those checks: a count read
2055/// before the write can already be stale when the write lands, and two
2056/// requests racing could otherwise take away the last credential between them.
2057/// `false` means the change was rolled back.
2058fn commit_if_reachable(tx: rusqlite::Transaction<'_>, user_id: i64) -> DbResult<bool> {
2059 let (has_password, mode) = tx.query_row(
2060 "SELECT pass_hash != '', auth_mode FROM users WHERE id = ?1",
2061 [user_id],
2062 |r| Ok((r.get::<_, bool>(0)?, r.get::<_, SqlAuthMode>(1)?)),
2063 )?;
2064 let passkeys: i64 = tx.query_row(
2065 "SELECT COUNT(*) FROM passkeys WHERE user_id = ?1",
2066 [user_id],
2067 |r| r.get(0),
2068 )?;
2069 let reachable = match mode.0 {
2070 AuthMode::Either => has_password || passkeys > 0,
2071 AuthMode::Both => has_password && passkeys > 0,
2072 };
2073 if !reachable {
2074 tx.rollback()?;
2075 return Ok(false);
2076 }
2077 tx.commit()?;
2078 Ok(true)
2079}
2080
2081/// Column order matched by [`USER_COLS`] and [`USER_COLS_U`].
2082fn map_user(r: &rusqlite::Row) -> DbResult<User> {
2083 Ok(User {
2084 id: r.get(0)?,
2085 name: r.get(1)?,
2086 is_admin: r.get(2)?,
2087 active: r.get(3)?,
2088 single_click: r.get(4)?,
2089 thumbnails: r.get(5)?,
2090 language: r.get(6)?,
2091 default_root_id: r.get(7)?,
2092 auth_mode: r.get::<_, SqlAuthMode>(8)?.0,
2093 has_password: r.get(9)?,
2094 })
2095}
2096
2097/// Column order matched by the `passkeys` SELECTs above.
2098fn map_passkey(r: &rusqlite::Row) -> DbResult<PasskeyRow> {
2099 Ok(PasskeyRow {
2100 id: r.get(0)?,
2101 name: r.get(1)?,
2102 created_at: r.get(2)?,
2103 last_used_at: r.get(3)?,
2104 discoverable: r.get(4)?,
2105 passkey: r.get(5)?,
2106 })
2107}
2108
2109/// A just-created account: the column defaults of `users`.
2110fn new_user(id: i64, name: &str, is_admin: bool) -> User {
2111 User {
2112 id,
2113 name: name.to_string(),
2114 is_admin,
2115 active: true,
2116 single_click: false,
2117 thumbnails: true,
2118 language: None,
2119 default_root_id: None,
2120 auth_mode: AuthMode::Either,
2121 has_password: true,
2122 }
2123}
2124
2125/// Column order matched by the `app_passwords` SELECTs above.
2126fn map_app_password(r: &rusqlite::Row) -> DbResult<AppPasswordInfo> {
2127 Ok(AppPasswordInfo {
2128 id: r.get(0)?,
2129 name: r.get(1)?,
2130 created_at: r.get(2)?,
2131 last_used_at: r.get(3)?,
2132 })
2133}
2134
2135/// Messages an inbox keeps; older ones are dropped.
2136const INBOX_KEEP: i64 = 100;
2137
2138fn ensure_inbox(c: &Connection, principal_id: i64) -> DbResult<()> {
2139 c.prepare_cached(
2140 "INSERT INTO pim_collections (principal_id, kind, slug, displayname, components, created_at)
2141 SELECT ?1, 'cal', 'inbox', 'Inbox', 'VEVENT,VTODO,VJOURNAL', ?2
2142 WHERE NOT EXISTS (SELECT 1 FROM pim_collections
2143 WHERE principal_id = ?1 AND kind = 'cal' AND slug = 'inbox')",
2144 )?
2145 .execute(params![principal_id, now()])?;
2146 Ok(())
2147}
2148
2149/// The writes of [`PimOp`]s, inside the caller's transaction.
2150fn apply_ops(tx: &rusqlite::Transaction, ops: &[PimOp]) -> DbResult<()> {
2151 for op in ops {
2152 match op {
2153 PimOp::Put {
2154 collection_id,
2155 obj,
2156 data,
2157 } => {
2158 put_object(tx, *collection_id, obj, data)?;
2159 }
2160 PimOp::Delete {
2161 collection_id,
2162 name,
2163 } => {
2164 tx.execute(
2165 "DELETE FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
2166 params![collection_id, name],
2167 )?;
2168 record_pim_change(tx, *collection_id, name, true)?;
2169 }
2170 PimOp::Inbox {
2171 principal_id,
2172 obj,
2173 data,
2174 } => {
2175 let inbox: i64 = tx.query_row(
2176 "SELECT id FROM pim_collections
2177 WHERE principal_id = ?1 AND kind = 'cal' AND slug = 'inbox'",
2178 [principal_id],
2179 |r| r.get(0),
2180 )?;
2181 put_object(tx, inbox, obj, data)?;
2182 // ponytail: a fixed cap. Clients that never empty the inbox
2183 // would fill it forever; an age limit may suit better.
2184 let old: Vec<String> = tx
2185 .prepare_cached(
2186 "SELECT name FROM pim_objects WHERE collection_id = ?1
2187 ORDER BY id DESC LIMIT -1 OFFSET ?2",
2188 )?
2189 .query_map(params![inbox, INBOX_KEEP], |r| r.get(0))?
2190 .collect::<DbResult<_>>()?;
2191 for name in old {
2192 tx.execute(
2193 "DELETE FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
2194 params![inbox, name],
2195 )?;
2196 record_pim_change(tx, inbox, &name, true)?;
2197 }
2198 }
2199 }
2200 }
2201 Ok(())
2202}
2203
2204/// Stores an object under `obj.name`, replacing one of that name.
2205fn put_object(
2206 tx: &rusqlite::Transaction,
2207 collection_id: i64,
2208 obj: &PimObject,
2209 data: &[u8],
2210) -> DbResult<()> {
2211 tx.execute(
2212 "INSERT INTO pim_objects (collection_id, name, uid, component, data, etag, modified_at,
2213 schedule_tag)
2214 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)
2215 ON CONFLICT (collection_id, name) DO UPDATE SET uid = ?3, component = ?4,
2216 data = ?5, etag = ?6, modified_at = ?7, schedule_tag = ?8",
2217 params![
2218 collection_id,
2219 obj.name,
2220 obj.uid,
2221 obj.component,
2222 data,
2223 obj.etag,
2224 now(),
2225 obj.schedule_tag
2226 ],
2227 )?;
2228 record_pim_change(tx, collection_id, &obj.name, false)
2229}
2230
2231/// Bumps the collection's `seq` and records it as the latest change of `name`.
2232fn record_pim_change(
2233 tx: &rusqlite::Transaction,
2234 collection_id: i64,
2235 name: &str,
2236 deleted: bool,
2237) -> DbResult<()> {
2238 let seq: i64 = tx.query_row(
2239 "UPDATE pim_collections SET seq = seq + 1 WHERE id = ?1 RETURNING seq",
2240 [collection_id],
2241 |r| r.get(0),
2242 )?;
2243 tx.execute(
2244 "INSERT INTO pim_changes (collection_id, name, seq, deleted) VALUES (?1, ?2, ?3, ?4)
2245 ON CONFLICT (collection_id, name) DO UPDATE SET seq = ?3, deleted = ?4",
2246 params![collection_id, name, seq, deleted],
2247 )?;
2248 Ok(())
2249}
2250
2251/// An account, room or resource, as CalDAV sees it.
2252#[derive(Debug, Clone)]
2253pub struct PimPrincipal {
2254 pub id: i64,
2255 /// The account of a person; rooms and resources have none.
2256 pub user_id: Option<i64>,
2257 /// The URL segment.
2258 pub name: String,
2259 pub display_name: Option<String>,
2260 pub kind: UserType,
2261}
2262
2263impl PimPrincipal {
2264 pub fn display(&self) -> &str {
2265 self.display_name.as_deref().unwrap_or(&self.name)
2266 }
2267}
2268
2269const PRINCIPAL_COLS: &str = "p.id, p.user_id, p.name, p.display_name, p.kind";
2270const PRINCIPALS: &str = "principals p LEFT JOIN users u ON u.id = p.user_id";
2271/// Disabled accounts are hidden.
2272const VISIBLE: &str = "(p.user_id IS NULL OR u.active = 1)";
2273
2274fn map_principal(r: &rusqlite::Row) -> DbResult<PimPrincipal> {
2275 Ok(PimPrincipal {
2276 id: r.get(0)?,
2277 user_id: r.get(1)?,
2278 name: r.get(2)?,
2279 display_name: r.get(3)?,
2280 kind: match r.get::<_, String>(4)?.as_str() {
2281 "room" => UserType::Room,
2282 "resource" => UserType::Resource,
2283 _ => UserType::Individual,
2284 },
2285 })
2286}
2287
2288fn kind_str(kind: UserType) -> &'static str {
2289 match kind {
2290 UserType::Individual => "person",
2291 UserType::Room => "room",
2292 UserType::Resource => "resource",
2293 }
2294}
2295
2296fn map_shared(r: &rusqlite::Row) -> DbResult<(PimCollection, String, PimShareMode)> {
2297 Ok((
2298 map_pim_collection(r)?,
2299 r.get(10)?,
2300 r.get::<_, SqlShareMode>(11)?.0,
2301 ))
2302}
2303
2304const PIM_COLLECTION_COLS_C: &str = "c.id, c.slug, c.displayname, c.description, c.color,
2305 c.timezone, c.sort_order, c.components, c.seq, c.transparent";
2306const PIM_COLLECTION_COLS: &str = "id, slug, displayname, description, color, timezone,
2307 sort_order, components, seq, transparent";
2308
2309fn map_pim_collection(r: &rusqlite::Row) -> DbResult<PimCollection> {
2310 Ok(PimCollection {
2311 id: r.get(0)?,
2312 slug: r.get(1)?,
2313 displayname: r.get(2)?,
2314 description: r.get(3)?,
2315 color: r.get(4)?,
2316 timezone: r.get(5)?,
2317 sort_order: r.get(6)?,
2318 components: r.get(7)?,
2319 seq: r.get(8)?,
2320 transparent: r.get(9)?,
2321 })
2322}
2323
2324const PIM_OBJECT_COLS: &str = "name, uid, component, etag, length(data), modified_at, schedule_tag";
2325const PIM_OBJECT_COLS_O: &str =
2326 "o.name, o.uid, o.component, o.etag, length(o.data), o.modified_at, o.schedule_tag";
2327
2328fn map_pim_object(r: &rusqlite::Row) -> DbResult<PimObject> {
2329 map_pim_object_at(r, 0)
2330}
2331
2332/// [`PIM_OBJECT_COLS`] starting at column `at`.
2333fn map_pim_object_at(r: &rusqlite::Row, at: usize) -> DbResult<PimObject> {
2334 Ok(PimObject {
2335 name: r.get(at)?,
2336 uid: r.get(at + 1)?,
2337 component: r.get(at + 2)?,
2338 etag: r.get(at + 3)?,
2339 size: r.get(at + 4)?,
2340 modified_at: r.get(at + 5)?,
2341 schedule_tag: r.get(at + 6)?,
2342 })
2343}
2344
2345const PIM_LINK_COLS: &str =
2346 "id, token, collection_id, busy_only, created_at, expires_at, password_hash";
2347
2348fn map_pim_link(r: &rusqlite::Row) -> DbResult<PimLink> {
2349 Ok(PimLink {
2350 id: r.get(0)?,
2351 token: r.get(1)?,
2352 collection_id: r.get(2)?,
2353 busy_only: r.get(3)?,
2354 created_at: r.get(4)?,
2355 expires_at: r.get(5)?,
2356 password_hash: r.get(6)?,
2357 })
2358}
2359
2360/// Column order matched by the two `shares` SELECTs above.
2361fn map_share(r: &rusqlite::Row) -> DbResult<ShareRow> {
2362 Ok(ShareRow {
2363 id: r.get(0)?,
2364 token: r.get(1)?,
2365 creator_id: r.get(2)?,
2366 target: r.get(3)?,
2367 is_file: r.get::<_, i64>(4)? != 0,
2368 mode: r.get::<_, SqlMode>(5)?.0,
2369 created_at: r.get(6)?,
2370 expires_at: r.get(7)?,
2371 password_hash: r.get(8)?,
2372 })
2373}
2374
2375/// A hash of a random string nobody knows. Verified against when the login
2376/// name does not exist, so both paths cost one Argon2 run.
2377static DUMMY_HASH: std::sync::LazyLock<String> = std::sync::LazyLock::new(|| {
2378 crate::auth::hash_password(&crate::auth::random_token()).expect("argon2 hash")
2379});
2380
2381/// How long an unlock row outlives its cookie. The cookie dies with the
2382/// browser, so this only bounds the rows left behind by closed sessions.
2383const UNLOCK_MAX_AGE_DAYS: i64 = 7;
2384
2385/// How often [`Db::sweep`] runs. A share the picker can only set to the
2386/// minute is gone from the owner's list about when it says it is.
2387const SWEEP_EVERY: std::time::Duration = std::time::Duration::from_secs(60);
2388
2389/// Run [`Db::sweep`] until the process ends. A failed pass is logged and
2390/// retried on the next one: nothing downstream depends on it having run.
2391pub async fn sweep_forever(db: Db) {
2392 loop {
2393 tokio::time::sleep(SWEEP_EVERY).await;
2394 match db.sweep().await {
2395 Ok((0, 0)) => {}
2396 Ok((shares, unlocks)) => tracing::debug!(shares, unlocks, "swept expired shares"),
2397 Err(e) => tracing::warn!(error = %e, "the share sweep failed"),
2398 }
2399 }
2400}
2401
2402/// The time `ago` in the past as a stored stamp. The format sorts
2403/// lexicographically, so SQL can compare stamps as text.
2404fn stamp(ago: chrono::Duration) -> String {
2405 (chrono::Utc::now() - ago).to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
2406}
2407
2408fn now() -> String {
2409 stamp(chrono::Duration::zero())
2410}
2411
2412const SCHEMA_V1: &str = r#"
2413CREATE TABLE IF NOT EXISTS users (
2414 id INTEGER PRIMARY KEY AUTOINCREMENT,
2415 name TEXT NOT NULL UNIQUE COLLATE NOCASE,
2416 pass_hash TEXT NOT NULL,
2417 is_admin INTEGER NOT NULL DEFAULT 0,
2418 created_at TEXT NOT NULL
2419);
2420
2421CREATE TABLE IF NOT EXISTS user_roots (
2422 id INTEGER PRIMARY KEY AUTOINCREMENT,
2423 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
2424 path TEXT NOT NULL,
2425 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
2426 UNIQUE (user_id, path)
2427);
2428
2429CREATE TABLE IF NOT EXISTS shares (
2430 id INTEGER PRIMARY KEY AUTOINCREMENT,
2431 token TEXT NOT NULL UNIQUE,
2432 creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
2433 target TEXT NOT NULL,
2434 is_file INTEGER NOT NULL,
2435 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
2436 created_at TEXT NOT NULL,
2437 expires_at TEXT
2438);
2439
2440CREATE TABLE IF NOT EXISTS sessions (
2441 token TEXT PRIMARY KEY,
2442 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
2443 created_at TEXT NOT NULL,
2444 last_seen_at TEXT NOT NULL
2445);
2446
2447CREATE TABLE IF NOT EXISTS settings (
2448 key TEXT PRIMARY KEY,
2449 value TEXT NOT NULL
2450);
2451INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
2452"#;
2453
2454#[cfg(test)]
2455mod tests {
2456 use super::*;
2457
2458 // Most tests use an in-memory DB (the file-based path is still covered
2459 // by `v1_db_migrates_to_v2` and the integration harness' `Db::open`).
2460 async fn mem() -> Db {
2461 Db::open(Path::new(":memory:")).await.unwrap()
2462 }
2463
2464 /// `update_user` is the only way production edits these fields, so the
2465 /// tests exercise that path rather than per-field helpers.
2466 async fn edit(db: &Db, id: i64, pass: Option<&str>, admin: Option<bool>, active: Option<bool>) {
2467 db.update_user(id, pass, admin, active, None).await.unwrap();
2468 }
2469
2470 /// Backdate a stamp, which production code has no reason to do.
2471 async fn set_last_used(db: &Db, secret_hash: &str, at: &str) {
2472 db.0.lock()
2473 .await
2474 .execute(
2475 "UPDATE app_passwords SET last_used_at = ?1 WHERE secret_hash = ?2",
2476 params![at, secret_hash],
2477 )
2478 .unwrap();
2479 }
2480
2481 async fn db_with_admin() -> (Db, User) {
2482 let db = mem().await;
2483 let hash = crate::auth::hash_password("admin1234").unwrap();
2484 let admin = db.create_admin("admin", &hash).await.unwrap().unwrap();
2485 (db, admin)
2486 }
2487
2488 #[tokio::test]
2489 async fn fresh_db_state() {
2490 let db = mem().await;
2491 assert_eq!(db.user_count().await.unwrap(), 0);
2492 assert_eq!(db.count_admins().await.unwrap(), 0);
2493 assert!(!db.allow_writable_shares().await.unwrap());
2494 assert!(db.find_user_by_name("nobody").await.unwrap().is_none());
2495 assert!(db.find_user_by_id(1).await.unwrap().is_none());
2496 assert!(db.all_users_with_roots().await.unwrap().is_empty());
2497 }
2498
2499 #[tokio::test]
2500 async fn app_passwords_open_one_account_and_stamp_their_use() {
2501 let (db, admin) = db_with_admin().await;
2502 let secret = crate::auth::short_token();
2503 let hash = crate::auth::app_password_hash(&secret);
2504 let row = db
2505 .add_app_password(admin.id, "laptop", &hash)
2506 .await
2507 .unwrap()
2508 .unwrap();
2509 assert_eq!(row.name, "laptop");
2510 assert!(row.last_used_at.is_none());
2511
2512 let hit = db.user_by_app_password(&hash).await.unwrap().unwrap();
2513 assert_eq!(hit.id, admin.id);
2514 let listed = |db: &Db| {
2515 let db = db.clone();
2516 async move {
2517 db.app_passwords(admin.id).await.unwrap()[0]
2518 .last_used_at
2519 .clone()
2520 }
2521 };
2522 assert!(listed(&db).await.is_some(), "first use must stamp the row");
2523
2524 // Ten minutes back is inside the hour window, and far enough from
2525 // `now()` that a rewrite would show at second resolution.
2526 let inside = stamp(chrono::Duration::minutes(10));
2527 set_last_used(&db, &hash, &inside).await;
2528 db.user_by_app_password(&hash).await.unwrap().unwrap();
2529 assert_eq!(
2530 listed(&db).await.as_deref(),
2531 Some(inside.as_str()),
2532 "a second use inside the hour wrote the stamp again"
2533 );
2534
2535 let outside = stamp(chrono::Duration::hours(2));
2536 set_last_used(&db, &hash, &outside).await;
2537 db.user_by_app_password(&hash).await.unwrap().unwrap();
2538 assert_ne!(
2539 listed(&db).await.as_deref(),
2540 Some(outside.as_str()),
2541 "a use after the window left the stamp stale"
2542 );
2543
2544 // The raw secret is not the key, and an inactive account does not match.
2545 let other = crate::auth::app_password_hash(&crate::auth::short_token());
2546 assert!(db.user_by_app_password(&other).await.unwrap().is_none());
2547 assert!(db.user_by_app_password(&secret).await.unwrap().is_none());
2548 edit(&db, admin.id, None, None, Some(false)).await;
2549 assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
2550 edit(&db, admin.id, None, None, Some(true)).await;
2551 assert!(db.user_by_app_password(&hash).await.unwrap().is_some());
2552
2553 for i in 1..APP_PASSWORD_LIMIT {
2554 let h = crate::auth::app_password_hash(&crate::auth::short_token());
2555 assert!(
2556 db.add_app_password(admin.id, &format!("c{i}"), &h)
2557 .await
2558 .unwrap()
2559 .is_some()
2560 );
2561 }
2562 let h = crate::auth::app_password_hash(&crate::auth::short_token());
2563 assert!(
2564 db.add_app_password(admin.id, "one-too-many", &h)
2565 .await
2566 .unwrap()
2567 .is_none()
2568 );
2569
2570 assert!(db.delete_app_password(row.id, admin.id).await.unwrap());
2571 assert!(!db.delete_app_password(row.id, admin.id).await.unwrap());
2572 assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
2573 }
2574
2575 #[tokio::test]
2576 async fn an_admin_password_reset_revokes_the_app_passwords() {
2577 let (db, admin) = db_with_admin().await;
2578 let hash = crate::auth::app_password_hash(&crate::auth::short_token());
2579 db.add_app_password(admin.id, "mount", &hash)
2580 .await
2581 .unwrap()
2582 .unwrap();
2583
2584 // An edit that sets no password leaves them alone.
2585 edit(&db, admin.id, None, None, Some(true)).await;
2586 assert_eq!(db.app_passwords(admin.id).await.unwrap().len(), 1);
2587
2588 // A reset is meant to revoke every way in that existed before it.
2589 let fresh = crate::auth::hash_password("rescued12").unwrap();
2590 edit(&db, admin.id, Some(&fresh), None, None).await;
2591 assert!(db.app_passwords(admin.id).await.unwrap().is_empty());
2592 assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
2593 }
2594
2595 #[tokio::test]
2596 async fn v1_db_migrates_to_v2() {
2597 let dir = tempfile::tempdir().unwrap();
2598 let path = dir.path().join("legacy.sqlite");
2599 {
2600 let conn = rusqlite::Connection::open(&path).unwrap();
2601 conn.execute_batch(SCHEMA_V1).unwrap();
2602 conn.execute(
2603 "INSERT INTO users (name, pass_hash, is_admin, created_at)
2604 VALUES ('legacy', 'hash', 1, '2024-01-01T00:00:00Z')",
2605 [],
2606 )
2607 .unwrap();
2608 conn.execute(
2609 "INSERT INTO user_roots (user_id, path, mode) VALUES (1, 'docs', 'rw')",
2610 [],
2611 )
2612 .unwrap();
2613 }
2614 let db = Db::open(&path).await.unwrap();
2615 assert_eq!(db.user_count().await.unwrap(), 1);
2616 let u = db.find_user_by_name("legacy").await.unwrap().unwrap();
2617 assert!(u.active, "v2 migration must default active to true");
2618 assert!(u.is_admin);
2619 assert_eq!(db.user_roots(u.id).await.unwrap().len(), 1);
2620 // Accounts from before CalDAV get their principal.
2621 let p = db.pim_principal("legacy").await.unwrap().unwrap();
2622 assert_eq!(p.user_id, Some(u.id));
2623 assert_eq!(db.principal_of(u.id).await.unwrap(), p.id);
2624 // Migrations are idempotent.
2625 let db2 = Db::open(&path).await.unwrap();
2626 assert_eq!(db2.user_count().await.unwrap(), 1);
2627 assert!(
2628 db2.find_user_by_name("legacy")
2629 .await
2630 .unwrap()
2631 .unwrap()
2632 .active
2633 );
2634 }
2635
2636 #[tokio::test]
2637 async fn rooms_are_principals_not_accounts() {
2638 let (db, admin) = db_with_admin().await;
2639 let bob = db.create_user("bob", "hash", false, &[]).await.unwrap();
2640 let room = db
2641 .create_room("board", "Board", UserType::Room)
2642 .await
2643 .unwrap()
2644 .unwrap();
2645 assert_eq!(room.user_id, None);
2646
2647 // No account query returns the room.
2648 assert_eq!(db.user_count().await.unwrap(), 2);
2649 assert!(db.find_user_by_name("board").await.unwrap().is_none());
2650 assert!(db.verify_password("board", "").await.unwrap().is_none());
2651 let names: Vec<_> = db.all_users_with_roots().await.unwrap();
2652 assert!(names.iter().all(|(u, _)| u.name != "board"));
2653
2654 // One name space, without case.
2655 assert!(db.name_taken("BOARD").await.unwrap());
2656 assert!(db.create_user("Board", "hash", false, &[]).await.is_err());
2657 assert!(
2658 db.create_room("BOB", "Bob", UserType::Resource)
2659 .await
2660 .unwrap()
2661 .is_none()
2662 );
2663
2664 // Deleting an account takes its principal, collections and loans.
2665 let bob_p = db.principal_of(bob.id).await.unwrap();
2666 db.pim_ensure_defaults(bob_p).await.unwrap();
2667 let admin_p = db.principal_of(admin.id).await.unwrap();
2668 db.pim_ensure_defaults(admin_p).await.unwrap();
2669 let bobs = db.pim_collections(bob_p, PimKind::Calendar).await.unwrap();
2670 let admins = db
2671 .pim_collections(admin_p, PimKind::Calendar)
2672 .await
2673 .unwrap();
2674 db.pim_set_share(admins[0].id, bob.id, PimShareMode::Ro)
2675 .await
2676 .unwrap();
2677 assert!(db.delete_user(bob.id, &[]).await.unwrap());
2678 assert!(db.pim_principal("bob").await.unwrap().is_none());
2679 assert!(!db.name_taken("bob").await.unwrap());
2680 assert!(db.pim_collection_by_id(bobs[0].id).await.unwrap().is_none());
2681 assert!(db.pim_shares(admins[0].id).await.unwrap().is_empty());
2682 // The room outlives it.
2683 assert!(db.pim_principal("board").await.unwrap().is_some());
2684 }
2685
2686 #[tokio::test]
2687 async fn admin_user_and_passwords() {
2688 let (db, admin) = db_with_admin().await;
2689 assert!(admin.is_admin);
2690 assert!(admin.active);
2691 // Root "." rw is assigned by create_admin.
2692 let roots = db.user_roots(admin.id).await.unwrap();
2693 assert_eq!(roots.len(), 1);
2694 assert_eq!(roots[0].path, ".");
2695 assert_eq!(roots[0].mode, Mode::Rw);
2696
2697 assert!(
2698 db.verify_password("admin", "admin1234")
2699 .await
2700 .unwrap()
2701 .is_some()
2702 );
2703 assert!(db.verify_password("admin", "nope").await.unwrap().is_none());
2704 // Name lookup is case-insensitive (COLLATE NOCASE).
2705 assert!(
2706 db.verify_password("ADMIN", "admin1234")
2707 .await
2708 .unwrap()
2709 .is_some()
2710 );
2711 // Disabled users cannot verify.
2712 edit(&db, admin.id, None, None, Some(false)).await;
2713 assert!(
2714 db.verify_password("admin", "admin1234")
2715 .await
2716 .unwrap()
2717 .is_none()
2718 );
2719 edit(&db, admin.id, None, None, Some(true)).await;
2720 assert!(
2721 db.verify_password("admin", "admin1234")
2722 .await
2723 .unwrap()
2724 .is_some()
2725 );
2726 }
2727
2728 #[tokio::test]
2729 async fn setup_is_won_by_exactly_one_caller() {
2730 let db = mem().await;
2731 let hash = crate::auth::hash_password("admin1234").unwrap();
2732 assert!(db.create_admin("first", &hash).await.unwrap().is_some());
2733 // The guard lives in the insert, so a different name loses too.
2734 assert!(db.create_admin("second", &hash).await.unwrap().is_none());
2735 assert_eq!(db.user_count().await.unwrap(), 1);
2736 // The loser rolled back cleanly: no orphaned root row.
2737 let first = db.find_user_by_name("first").await.unwrap().unwrap();
2738 assert_eq!(db.user_roots(first.id).await.unwrap().len(), 1);
2739 }
2740
2741 #[tokio::test]
2742 async fn changing_a_password_drops_that_users_sessions() {
2743 let (db, admin) = db_with_admin().await;
2744 let h = crate::auth::hash_password("bobpass1").unwrap();
2745 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
2746 db.create_session(admin.id, "admin-tok").await.unwrap();
2747 db.create_session(bob.id, "bob-tok-1").await.unwrap();
2748 db.create_session(bob.id, "bob-tok-2").await.unwrap();
2749
2750 let new_h = crate::auth::hash_password("bobpass2").unwrap();
2751 edit(&db, bob.id, Some(&new_h), None, None).await;
2752 assert!(
2753 db.session_user_with_roots("bob-tok-1")
2754 .await
2755 .unwrap()
2756 .is_none()
2757 );
2758 assert!(
2759 db.session_user_with_roots("bob-tok-2")
2760 .await
2761 .unwrap()
2762 .is_none()
2763 );
2764 // Only the reset user is signed out.
2765 assert!(
2766 db.session_user_with_roots("admin-tok")
2767 .await
2768 .unwrap()
2769 .is_some()
2770 );
2771
2772 // The admin-edit path bundles the same rule into its transaction.
2773 db.create_session(bob.id, "bob-tok-3").await.unwrap();
2774 let h3 = crate::auth::hash_password("bobpass3").unwrap();
2775 db.update_user(bob.id, Some(&h3), None, None, None)
2776 .await
2777 .unwrap();
2778 assert!(
2779 db.session_user_with_roots("bob-tok-3")
2780 .await
2781 .unwrap()
2782 .is_none()
2783 );
2784 // An edit that leaves the password alone keeps the session.
2785 db.create_session(bob.id, "bob-tok-4").await.unwrap();
2786 db.update_user(bob.id, None, Some(true), None, None)
2787 .await
2788 .unwrap();
2789 assert!(
2790 db.session_user_with_roots("bob-tok-4")
2791 .await
2792 .unwrap()
2793 .is_some()
2794 );
2795 }
2796
2797 #[tokio::test]
2798 async fn sessions_lifecycle() {
2799 let (db, admin) = db_with_admin().await;
2800 assert!(
2801 db.session_user_with_roots("ghost-token")
2802 .await
2803 .unwrap()
2804 .is_none()
2805 );
2806 db.create_session(admin.id, "tok1").await.unwrap();
2807 let (u, _) = db.session_user_with_roots("tok1").await.unwrap().unwrap();
2808 assert_eq!(u.id, admin.id);
2809 // Disabling the user invalidates existing sessions.
2810 edit(&db, admin.id, None, None, Some(false)).await;
2811 assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
2812 edit(&db, admin.id, None, None, Some(true)).await;
2813 assert!(db.session_user_with_roots("tok1").await.unwrap().is_some());
2814 db.delete_session("tok1").await.unwrap();
2815 assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
2816 }
2817
2818 #[tokio::test]
2819 async fn user_crud_and_roots() {
2820 let (db, _admin) = db_with_admin().await;
2821 let h = crate::auth::hash_password("bobpass1").unwrap();
2822 let bob = db
2823 .create_user("bob", &h, false, &[("docs".into(), Mode::Rw)])
2824 .await
2825 .unwrap();
2826 assert!(!bob.is_admin);
2827 assert!(bob.active);
2828
2829 // Duplicate name (case-insensitive) is rejected.
2830 let h2 = crate::auth::hash_password("carolpass1").unwrap();
2831 assert!(db.create_user("BOB", &h2, false, &[]).await.is_err());
2832 assert!(db.create_user("carol", &h2, false, &[]).await.is_ok());
2833
2834 // Lookup helpers.
2835 assert_eq!(
2836 db.find_user_by_name("Bob").await.unwrap().unwrap().id,
2837 bob.id
2838 );
2839 assert_eq!(
2840 db.find_user_by_id(bob.id).await.unwrap().unwrap().name,
2841 "bob"
2842 );
2843 assert!(db.find_user_by_name("dave").await.unwrap().is_none());
2844 assert_eq!(db.all_users_with_roots().await.unwrap().len(), 3);
2845
2846 // Root replacement semantics.
2847 let roots = db.user_roots(bob.id).await.unwrap();
2848 assert_eq!(roots.len(), 1);
2849 db.update_user(
2850 bob.id,
2851 None,
2852 None,
2853 None,
2854 Some(&[(".".into(), Mode::Ro), ("docs".into(), Mode::Rw)]),
2855 )
2856 .await
2857 .unwrap();
2858 let roots = db.user_roots(bob.id).await.unwrap();
2859 assert_eq!(roots.len(), 2);
2860 assert!(roots.iter().any(|r| r.path == "." && r.mode == Mode::Ro));
2861 db.update_user(bob.id, None, None, None, Some(&[]))
2862 .await
2863 .unwrap();
2864 assert!(db.user_roots(bob.id).await.unwrap().is_empty());
2865
2866 // Password update.
2867 let new_h = crate::auth::hash_password("bobpass2").unwrap();
2868 edit(&db, bob.id, Some(&new_h), None, None).await;
2869 assert!(
2870 db.verify_password("bob", "bobpass1")
2871 .await
2872 .unwrap()
2873 .is_none()
2874 );
2875 assert!(
2876 db.verify_password("bob", "bobpass2")
2877 .await
2878 .unwrap()
2879 .is_some()
2880 );
2881
2882 // Admin flag + count (only active admins count).
2883 edit(&db, bob.id, None, Some(true), None).await;
2884 assert_eq!(db.count_admins().await.unwrap(), 2);
2885 edit(&db, bob.id, None, None, Some(false)).await;
2886 assert_eq!(db.count_admins().await.unwrap(), 1);
2887 edit(&db, bob.id, None, Some(false), None).await;
2888
2889 // Deletion.
2890 assert!(db.delete_user(bob.id, &[]).await.unwrap());
2891 assert!(db.find_user_by_id(bob.id).await.unwrap().is_none());
2892 assert!(!db.delete_user(bob.id, &[]).await.unwrap());
2893 assert_eq!(db.user_count().await.unwrap(), 2);
2894 }
2895
2896 fn share_row(expires_at: Option<&str>) -> ShareRow {
2897 ShareRow {
2898 id: 1,
2899 token: "t".into(),
2900 creator_id: 1,
2901 target: "docs".into(),
2902 is_file: false,
2903 mode: Mode::Ro,
2904 created_at: "2024-01-01T00:00:00Z".into(),
2905 expires_at: expires_at.map(str::to_string),
2906 password_hash: None,
2907 }
2908 }
2909
2910 #[test]
2911 fn share_expiry_logic() {
2912 assert!(!share_row(None).is_expired());
2913 assert!(!share_row(Some("2999-01-01T00:00:00Z")).is_expired());
2914 assert!(share_row(Some("2000-01-01T00:00:00Z")).is_expired());
2915 // Unparseable expiry → treated as not expired (fail open for reads).
2916 assert!(!share_row(Some("not-a-date")).is_expired());
2917 }
2918
2919 #[tokio::test]
2920 async fn shares_crud() {
2921 let (db, admin) = db_with_admin().await;
2922 let s1 = db
2923 .create_share(admin.id, "tok-a", "docs", false, Mode::Ro, None, None)
2924 .await
2925 .unwrap();
2926 let s2 = db
2927 .create_share(
2928 admin.id,
2929 "tok-b",
2930 "file.txt",
2931 true,
2932 Mode::Rw,
2933 Some("2999-01-01T00:00:00Z"),
2934 None,
2935 )
2936 .await
2937 .unwrap();
2938 assert!(s2.id > s1.id);
2939
2940 let found = db.share_by_token("tok-b").await.unwrap().unwrap();
2941 assert!(found.is_file);
2942 assert_eq!(found.mode, Mode::Rw);
2943 assert!(db.share_by_token("nope").await.unwrap().is_none());
2944
2945 // Listed newest-first.
2946 let list = db.user_shares(admin.id).await.unwrap();
2947 assert_eq!(list.len(), 2);
2948 assert_eq!(list[0].id, s2.id);
2949 // Other users see nothing.
2950 let h = crate::auth::hash_password("bobpass1").unwrap();
2951 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
2952 assert!(db.user_shares(bob.id).await.unwrap().is_empty());
2953
2954 // Only the creator can delete.
2955 assert!(!db.delete_share(s1.id, bob.id).await.unwrap());
2956 assert!(db.delete_share(s1.id, admin.id).await.unwrap());
2957 assert!(db.share_by_token("tok-a").await.unwrap().is_none());
2958 assert!(!db.delete_share(s1.id, admin.id).await.unwrap());
2959 }
2960
2961 /// The sweep decides which timestamps are past, and `expires_at` is
2962 /// stored in whatever RFC 3339 shape the client sent.
2963 #[tokio::test]
2964 async fn the_sweep_drops_expired_shares_and_stale_unlocks() {
2965 let (db, admin) = db_with_admin().await;
2966 let make = async |token: &str, expires: Option<&str>| {
2967 db.create_share(admin.id, token, "docs", false, Mode::Ro, expires, None)
2968 .await
2969 .unwrap()
2970 };
2971 let past_offset = make("gone-offset", Some("2000-01-01T00:00:00+02:00")).await;
2972 make("gone-utc", Some("2000-01-01T00:00:00Z")).await;
2973 let future = make("stays-future", Some("2999-01-01T00:00:00Z")).await;
2974 make("stays-forever", None).await;
2975 // `is_expired` keeps an unreadable timestamp; the sweep must agree,
2976 // or the two disagree about the same row.
2977 make("stays-garbage", Some("not-a-date")).await;
2978
2979 let fresh = db.create_share_unlock(future.id).await.unwrap();
2980 let stale = db.create_share_unlock(future.id).await.unwrap();
2981 let doomed = db.create_share_unlock(past_offset.id).await.unwrap();
2982 {
2983 let c = db.0.lock().await;
2984 c.execute(
2985 "UPDATE share_unlocks SET created_at = '2000-01-01T00:00:00Z' WHERE token = ?1",
2986 [&stale],
2987 )
2988 .unwrap();
2989 }
2990
2991 let (shares, unlocks) = db.sweep().await.unwrap();
2992 assert_eq!(shares, 2, "only the two past timestamps");
2993 // The stale one, plus the cascade from the expired share it opened.
2994 assert_eq!(unlocks, 1, "the cascade runs with the share, not here");
2995
2996 for token in ["gone-offset", "gone-utc"] {
2997 assert!(db.share_by_token(token).await.unwrap().is_none(), "{token}");
2998 }
2999 for token in ["stays-future", "stays-forever", "stays-garbage"] {
3000 assert!(db.share_by_token(token).await.unwrap().is_some(), "{token}");
3001 }
3002
3003 assert!(db.share_unlock_valid(&fresh, future.id).await.unwrap());
3004 assert!(!db.share_unlock_valid(&stale, future.id).await.unwrap());
3005 assert!(
3006 !db.share_unlock_valid(&doomed, past_offset.id)
3007 .await
3008 .unwrap(),
3009 "an unlock must not outlive the share it opened"
3010 );
3011
3012 assert_eq!(
3013 db.sweep().await.unwrap(),
3014 (0, 0),
3015 "a second pass is a no-op"
3016 );
3017 }
3018
3019 /// The unlock token is what a visitor's cookie carries, so an unlock
3020 /// that opened the wrong share would be a full bypass of the password.
3021 #[tokio::test]
3022 async fn share_unlocks_are_bound_to_one_share() {
3023 let (db, admin) = db_with_admin().await;
3024 let a = db
3025 .create_share(
3026 admin.id,
3027 "tok-a",
3028 "docs",
3029 false,
3030 Mode::Ro,
3031 None,
3032 Some("hash"),
3033 )
3034 .await
3035 .unwrap();
3036 let b = db
3037 .create_share(
3038 admin.id,
3039 "tok-b",
3040 "other",
3041 false,
3042 Mode::Ro,
3043 None,
3044 Some("hash"),
3045 )
3046 .await
3047 .unwrap();
3048 assert_eq!(
3049 db.share_by_token("tok-a")
3050 .await
3051 .unwrap()
3052 .unwrap()
3053 .password_hash,
3054 Some("hash".to_string())
3055 );
3056
3057 let unlock = db.create_share_unlock(a.id).await.unwrap();
3058 assert!(db.share_unlock_valid(&unlock, a.id).await.unwrap());
3059 assert!(!db.share_unlock_valid(&unlock, b.id).await.unwrap());
3060 assert!(!db.share_unlock_valid("nonsense", a.id).await.unwrap());
3061
3062 // Deleting the share takes its unlocks with it, so a re-created
3063 // share that happened to reuse the id could not inherit them.
3064 assert!(db.delete_share(a.id, admin.id).await.unwrap());
3065 assert!(!db.share_unlock_valid(&unlock, a.id).await.unwrap());
3066 }
3067
3068 #[tokio::test]
3069 async fn revoking_a_path_takes_its_descendants_only() {
3070 let (db, admin) = db_with_admin().await;
3071 let mk = async |token: &str, target: &str| {
3072 db.create_share(admin.id, token, target, false, Mode::Ro, None, None)
3073 .await
3074 .unwrap();
3075 };
3076 mk("t-self", "docs").await;
3077 mk("t-child", "docs/a.txt").await;
3078 mk("t-deep", "docs/inner/b.txt").await;
3079 // A sibling whose name merely starts with "docs" must survive.
3080 mk("t-sibling", "docs2/c.txt").await;
3081 mk("t-other", "src").await;
3082 // SQL wildcards in a path are literal characters, not patterns.
3083 mk("t-wild", "do%s/d.txt").await;
3084
3085 assert_eq!(db.revoke_shares_at("docs").await.unwrap(), 3);
3086 for gone in ["t-self", "t-child", "t-deep"] {
3087 assert!(db.share_by_token(gone).await.unwrap().is_none(), "{gone}");
3088 }
3089 for kept in ["t-sibling", "t-other", "t-wild"] {
3090 assert!(db.share_by_token(kept).await.unwrap().is_some(), "{kept}");
3091 }
3092 // Revoking a path nobody shared is a no-op, not an error.
3093 assert_eq!(db.revoke_shares_at("nothing/here").await.unwrap(), 0);
3094 }
3095
3096 #[tokio::test]
3097 async fn settings_round_trip() {
3098 let (db, _admin) = db_with_admin().await;
3099 assert!(!db.allow_writable_shares().await.unwrap());
3100 db.set_allow_writable_shares(true).await.unwrap();
3101 assert!(db.allow_writable_shares().await.unwrap());
3102 // Upsert semantics.
3103 db.set_allow_writable_shares(false).await.unwrap();
3104 assert!(!db.allow_writable_shares().await.unwrap());
3105 // Generic get/set.
3106 db.set_setting("custom", "v").await.unwrap();
3107 assert_eq!(
3108 db.get_setting("custom").await.unwrap().as_deref(),
3109 Some("v")
3110 );
3111 assert_eq!(db.get_setting("missing").await.unwrap(), None);
3112 }
3113}
3114