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