dovenest

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