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