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