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