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