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 // A room gets its inbox with its first message.
2511 ensure_inbox(tx, *principal_id)?;
2512 let inbox: i64 = tx.query_row(
2513 "SELECT id FROM pim_collections
2514 WHERE principal_id = ?1 AND kind = 'cal' AND slug = 'inbox'",
2515 [principal_id],
2516 |r| r.get(0),
2517 )?;
2518 put_object(tx, inbox, obj, data)?;
2519 // ponytail: a fixed cap. Clients that never empty the inbox
2520 // would fill it forever; an age limit may suit better.
2521 let old: Vec<String> = tx
2522 .prepare_cached(
2523 "SELECT name FROM pim_objects WHERE collection_id = ?1
2524 ORDER BY id DESC LIMIT -1 OFFSET ?2",
2525 )?
2526 .query_map(params![inbox, INBOX_KEEP], |r| r.get(0))?
2527 .collect::<DbResult<_>>()?;
2528 for name in old {
2529 tx.execute(
2530 "DELETE FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
2531 params![inbox, name],
2532 )?;
2533 record_pim_change(tx, inbox, &name, true)?;
2534 }
2535 }
2536 PimOp::DeleteCollection(id) => {
2537 tx.execute("DELETE FROM pim_collections WHERE id = ?1", [id])?;
2538 }
2539 }
2540 }
2541 Ok(())
2542}
2543
2544/// Stores an object under `obj.name`, replacing one of that name.
2545fn put_object(
2546 tx: &rusqlite::Transaction,
2547 collection_id: i64,
2548 obj: &PimObject,
2549 data: &[u8],
2550) -> DbResult<()> {
2551 tx.execute(
2552 "INSERT INTO pim_objects (collection_id, name, uid, component, data, etag, modified_at,
2553 schedule_tag)
2554 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)
2555 ON CONFLICT (collection_id, name) DO UPDATE SET uid = ?3, component = ?4,
2556 data = ?5, etag = ?6, modified_at = ?7, schedule_tag = ?8",
2557 params![
2558 collection_id,
2559 obj.name,
2560 obj.uid,
2561 obj.component,
2562 data,
2563 obj.etag,
2564 now(),
2565 obj.schedule_tag
2566 ],
2567 )?;
2568 record_pim_change(tx, collection_id, &obj.name, false)
2569}
2570
2571/// Bumps the collection's `seq` and records it as the latest change of `name`.
2572fn record_pim_change(
2573 tx: &rusqlite::Transaction,
2574 collection_id: i64,
2575 name: &str,
2576 deleted: bool,
2577) -> DbResult<()> {
2578 let seq: i64 = tx.query_row(
2579 "UPDATE pim_collections SET seq = seq + 1 WHERE id = ?1 RETURNING seq",
2580 [collection_id],
2581 |r| r.get(0),
2582 )?;
2583 tx.execute(
2584 "INSERT INTO pim_changes (collection_id, name, seq, deleted) VALUES (?1, ?2, ?3, ?4)
2585 ON CONFLICT (collection_id, name) DO UPDATE SET seq = ?3, deleted = ?4",
2586 params![collection_id, name, seq, deleted],
2587 )?;
2588 Ok(())
2589}
2590
2591/// An account, room or resource, as CalDAV sees it.
2592#[derive(Debug, Clone)]
2593pub struct PimPrincipal {
2594 pub id: i64,
2595 /// The account of a person; rooms and resources have none.
2596 pub user_id: Option<i64>,
2597 /// The URL segment.
2598 pub name: String,
2599 pub display_name: Option<String>,
2600 pub kind: UserType,
2601 /// False for a disabled account.
2602 pub active: bool,
2603}
2604
2605impl PimPrincipal {
2606 pub fn display(&self) -> &str {
2607 self.display_name.as_deref().unwrap_or(&self.name)
2608 }
2609}
2610
2611// The last column repeats VISIBLE.
2612const PRINCIPAL_COLS: &str =
2613 "p.id, p.user_id, p.name, p.display_name, p.kind, (p.user_id IS NULL OR u.active = 1)";
2614const PRINCIPALS: &str = "principals p LEFT JOIN users u ON u.id = p.user_id";
2615/// Disabled accounts are hidden.
2616const VISIBLE: &str = "(p.user_id IS NULL OR u.active = 1)";
2617
2618fn map_principal(r: &rusqlite::Row) -> DbResult<PimPrincipal> {
2619 Ok(PimPrincipal {
2620 id: r.get(0)?,
2621 user_id: r.get(1)?,
2622 name: r.get(2)?,
2623 display_name: r.get(3)?,
2624 kind: match r.get::<_, String>(4)?.as_str() {
2625 "room" => UserType::Room,
2626 "resource" => UserType::Resource,
2627 _ => UserType::Individual,
2628 },
2629 active: r.get(5)?,
2630 })
2631}
2632
2633fn kind_str(kind: UserType) -> &'static str {
2634 match kind {
2635 UserType::Individual => "person",
2636 UserType::Room => "room",
2637 UserType::Resource => "resource",
2638 }
2639}
2640
2641fn map_shared(r: &rusqlite::Row) -> DbResult<(PimCollection, String, PimShareMode)> {
2642 Ok((
2643 map_pim_collection(r)?,
2644 r.get(10)?,
2645 r.get::<_, SqlShareMode>(11)?.0,
2646 ))
2647}
2648
2649const PIM_COLLECTION_COLS_C: &str = "c.id, c.slug, c.displayname, c.description, c.color,
2650 c.timezone, c.sort_order, c.components, c.seq, c.transparent";
2651const PIM_COLLECTION_COLS: &str = "id, slug, displayname, description, color, timezone,
2652 sort_order, components, seq, transparent";
2653
2654fn write_props(
2655 tx: &rusqlite::Transaction,
2656 place: PropPlace,
2657 set: &[DeadProp],
2658 remove: &[(String, String)],
2659) -> DbResult<()> {
2660 let key = place.key();
2661 let (principal_id, collection_id) = place.owners();
2662 for (ns, name) in remove {
2663 tx.execute(
2664 "DELETE FROM pim_props WHERE place = ?1 AND ns = ?2 AND name = ?3",
2665 params![key, ns, name],
2666 )?;
2667 }
2668 for p in set {
2669 tx.execute(
2670 "INSERT OR REPLACE INTO pim_props (place, principal_id, collection_id, ns, name, xml)
2671 VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
2672 params![key, principal_id, collection_id, p.ns, p.name, p.xml],
2673 )?;
2674 }
2675 Ok(())
2676}
2677
2678fn map_pim_collection(r: &rusqlite::Row) -> DbResult<PimCollection> {
2679 Ok(PimCollection {
2680 id: r.get(0)?,
2681 slug: r.get(1)?,
2682 displayname: r.get(2)?,
2683 description: r.get(3)?,
2684 color: r.get(4)?,
2685 timezone: r.get(5)?,
2686 sort_order: r.get(6)?,
2687 components: r.get(7)?,
2688 seq: r.get(8)?,
2689 transparent: r.get(9)?,
2690 })
2691}
2692
2693const PIM_OBJECT_COLS: &str = "name, uid, component, etag, length(data), modified_at, schedule_tag";
2694const PIM_OBJECT_COLS_O: &str =
2695 "o.name, o.uid, o.component, o.etag, length(o.data), o.modified_at, o.schedule_tag";
2696
2697fn map_pim_object(r: &rusqlite::Row) -> DbResult<PimObject> {
2698 map_pim_object_at(r, 0)
2699}
2700
2701/// [`PIM_OBJECT_COLS`] starting at column `at`.
2702fn map_pim_object_at(r: &rusqlite::Row, at: usize) -> DbResult<PimObject> {
2703 Ok(PimObject {
2704 name: r.get(at)?,
2705 uid: r.get(at + 1)?,
2706 component: r.get(at + 2)?,
2707 etag: r.get(at + 3)?,
2708 size: r.get(at + 4)?,
2709 modified_at: r.get(at + 5)?,
2710 schedule_tag: r.get(at + 6)?,
2711 })
2712}
2713
2714const PIM_LINK_COLS: &str =
2715 "id, token, collection_id, busy_only, created_at, expires_at, password_hash";
2716
2717fn map_pim_link(r: &rusqlite::Row) -> DbResult<PimLink> {
2718 Ok(PimLink {
2719 id: r.get(0)?,
2720 token: r.get(1)?,
2721 collection_id: r.get(2)?,
2722 busy_only: r.get(3)?,
2723 created_at: r.get(4)?,
2724 expires_at: r.get(5)?,
2725 password_hash: r.get(6)?,
2726 })
2727}
2728
2729/// Column order matched by the two `shares` SELECTs above.
2730fn map_share(r: &rusqlite::Row) -> DbResult<ShareRow> {
2731 Ok(ShareRow {
2732 id: r.get(0)?,
2733 token: r.get(1)?,
2734 creator_id: r.get(2)?,
2735 target: r.get(3)?,
2736 is_file: r.get::<_, i64>(4)? != 0,
2737 mode: r.get::<_, SqlMode>(5)?.0,
2738 created_at: r.get(6)?,
2739 expires_at: r.get(7)?,
2740 password_hash: r.get(8)?,
2741 })
2742}
2743
2744/// A hash of a random string nobody knows. Verified against when the login
2745/// name does not exist, so both paths cost one Argon2 run.
2746static DUMMY_HASH: std::sync::LazyLock<String> = std::sync::LazyLock::new(|| {
2747 crate::auth::hash_password(&crate::auth::random_token()).expect("argon2 hash")
2748});
2749
2750/// How long an unlock row outlives its cookie. The cookie dies with the
2751/// browser, so this only bounds the rows left behind by closed sessions.
2752const UNLOCK_MAX_AGE_DAYS: i64 = 7;
2753
2754/// How often [`Db::sweep`] runs. A share the picker can only set to the
2755/// minute is gone from the owner's list about when it says it is.
2756const SWEEP_EVERY: std::time::Duration = std::time::Duration::from_secs(60);
2757
2758/// Run [`Db::sweep`] until the process ends. A failed pass is logged and
2759/// retried on the next one: nothing downstream depends on it having run.
2760pub async fn sweep_forever(db: Db) {
2761 loop {
2762 tokio::time::sleep(SWEEP_EVERY).await;
2763 match db.sweep().await {
2764 Ok((0, 0)) => {}
2765 Ok((shares, unlocks)) => tracing::debug!(shares, unlocks, "swept expired shares"),
2766 Err(e) => tracing::warn!(error = %e, "the share sweep failed"),
2767 }
2768 }
2769}
2770
2771/// The time `ago` in the past as a stored stamp. The format sorts
2772/// lexicographically, so SQL can compare stamps as text.
2773fn stamp(ago: chrono::Duration) -> String {
2774 (chrono::Utc::now() - ago).to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
2775}
2776
2777fn now() -> String {
2778 stamp(chrono::Duration::zero())
2779}
2780
2781/// Deletion records kept per collection for sync tokens.
2782const PIM_TOMBSTONES_KEPT: i64 = 1000;
2783
2784const SCHEMA_V1: &str = r#"
2785CREATE TABLE IF NOT EXISTS users (
2786 id INTEGER PRIMARY KEY AUTOINCREMENT,
2787 name TEXT NOT NULL UNIQUE COLLATE NOCASE,
2788 pass_hash TEXT NOT NULL,
2789 is_admin INTEGER NOT NULL DEFAULT 0,
2790 created_at TEXT NOT NULL
2791);
2792
2793CREATE TABLE IF NOT EXISTS user_roots (
2794 id INTEGER PRIMARY KEY AUTOINCREMENT,
2795 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
2796 path TEXT NOT NULL,
2797 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
2798 UNIQUE (user_id, path)
2799);
2800
2801CREATE TABLE IF NOT EXISTS shares (
2802 id INTEGER PRIMARY KEY AUTOINCREMENT,
2803 token TEXT NOT NULL UNIQUE,
2804 creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
2805 target TEXT NOT NULL,
2806 is_file INTEGER NOT NULL,
2807 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
2808 created_at TEXT NOT NULL,
2809 expires_at TEXT
2810);
2811
2812CREATE TABLE IF NOT EXISTS sessions (
2813 token TEXT PRIMARY KEY,
2814 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
2815 created_at TEXT NOT NULL,
2816 last_seen_at TEXT NOT NULL
2817);
2818
2819CREATE TABLE IF NOT EXISTS settings (
2820 key TEXT PRIMARY KEY,
2821 value TEXT NOT NULL
2822);
2823INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
2824"#;
2825
2826#[cfg(test)]
2827mod tests {
2828 use super::*;
2829
2830 // Most tests use an in-memory DB (the file-based path is still covered
2831 // by `v1_db_migrates_to_v2` and the integration harness' `Db::open`).
2832 async fn mem() -> Db {
2833 Db::open(Path::new(":memory:")).await.unwrap()
2834 }
2835
2836 /// `update_user` is the only way production edits these fields, so the
2837 /// tests exercise that path rather than per-field helpers.
2838 async fn edit(db: &Db, id: i64, pass: Option<&str>, admin: Option<bool>, active: Option<bool>) {
2839 db.update_user(id, pass, admin, active, None).await.unwrap();
2840 }
2841
2842 /// Backdate a stamp, which production code has no reason to do.
2843 async fn set_last_used(db: &Db, secret_hash: &str, at: &str) {
2844 db.conn
2845 .lock()
2846 .await
2847 .execute(
2848 "UPDATE app_passwords SET last_used_at = ?1 WHERE secret_hash = ?2",
2849 params![at, secret_hash],
2850 )
2851 .unwrap();
2852 }
2853
2854 async fn db_with_admin() -> (Db, User) {
2855 let db = mem().await;
2856 let hash = crate::auth::hash_password("admin1234").unwrap();
2857 let admin = db.create_admin("admin", &hash).await.unwrap().unwrap();
2858 (db, admin)
2859 }
2860
2861 #[tokio::test]
2862 async fn pim_patch_keeps_concurrent_changes() {
2863 let (db, admin) = db_with_admin().await;
2864 let pid = db.principal_of(admin.id).await.unwrap();
2865 db.pim_ensure_defaults(pid).await.unwrap();
2866 let before = db.pim_collections(pid, PimKind::Calendar).await.unwrap()[0].clone();
2867 let place = PropPlace::Collection(before.id);
2868 let named = PimCollection {
2869 displayname: Some("Work".into()),
2870 ..before.clone()
2871 };
2872 let colored = PimCollection {
2873 color: Some("#ff0000".into()),
2874 ..before.clone()
2875 };
2876 // Two writers that both read `before`.
2877 db.pim_patch(place, Some((&before, &named)), &[], &[])
2878 .await
2879 .unwrap();
2880 db.pim_patch(place, Some((&before, &colored)), &[], &[])
2881 .await
2882 .unwrap();
2883 let (_, _, now) = db.pim_collection_by_id(before.id).await.unwrap().unwrap();
2884 assert_eq!(now.displayname.as_deref(), Some("Work"));
2885 assert_eq!(now.color.as_deref(), Some("#ff0000"));
2886 assert_eq!(now.seq, before.seq + 2);
2887 }
2888
2889 #[tokio::test]
2890 async fn fresh_db_state() {
2891 let db = mem().await;
2892 assert_eq!(db.user_count().await.unwrap(), 0);
2893 assert_eq!(db.count_admins().await.unwrap(), 0);
2894 assert!(!db.allow_writable_shares().await.unwrap());
2895 assert!(db.find_user_by_name("nobody").await.unwrap().is_none());
2896 assert!(db.find_user_by_id(1).await.unwrap().is_none());
2897 assert!(db.all_users_with_roots().await.unwrap().is_empty());
2898 }
2899
2900 #[tokio::test]
2901 async fn app_passwords_open_one_account_and_stamp_their_use() {
2902 let (db, admin) = db_with_admin().await;
2903 let secret = crate::auth::short_token();
2904 let hash = crate::auth::app_password_hash(&secret);
2905 let row = db
2906 .add_app_password(admin.id, "laptop", &hash)
2907 .await
2908 .unwrap()
2909 .unwrap();
2910 assert_eq!(row.name, "laptop");
2911 assert!(row.last_used_at.is_none());
2912
2913 let hit = db.user_by_app_password(&hash).await.unwrap().unwrap();
2914 assert_eq!(hit.id, admin.id);
2915 let listed = |db: &Db| {
2916 let db = db.clone();
2917 async move {
2918 db.app_passwords(admin.id).await.unwrap()[0]
2919 .last_used_at
2920 .clone()
2921 }
2922 };
2923 assert!(listed(&db).await.is_some(), "first use must stamp the row");
2924
2925 // Ten minutes back is inside the hour window, and far enough from
2926 // `now()` that a rewrite would show at second resolution.
2927 let inside = stamp(chrono::Duration::minutes(10));
2928 set_last_used(&db, &hash, &inside).await;
2929 db.user_by_app_password(&hash).await.unwrap().unwrap();
2930 assert_eq!(
2931 listed(&db).await.as_deref(),
2932 Some(inside.as_str()),
2933 "a second use inside the hour wrote the stamp again"
2934 );
2935
2936 let outside = stamp(chrono::Duration::hours(2));
2937 set_last_used(&db, &hash, &outside).await;
2938 db.user_by_app_password(&hash).await.unwrap().unwrap();
2939 assert_ne!(
2940 listed(&db).await.as_deref(),
2941 Some(outside.as_str()),
2942 "a use after the window left the stamp stale"
2943 );
2944
2945 // The raw secret is not the key, and an inactive account does not match.
2946 let other = crate::auth::app_password_hash(&crate::auth::short_token());
2947 assert!(db.user_by_app_password(&other).await.unwrap().is_none());
2948 assert!(db.user_by_app_password(&secret).await.unwrap().is_none());
2949 edit(&db, admin.id, None, None, Some(false)).await;
2950 assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
2951 edit(&db, admin.id, None, None, Some(true)).await;
2952 assert!(db.user_by_app_password(&hash).await.unwrap().is_some());
2953
2954 for i in 1..APP_PASSWORD_LIMIT {
2955 let h = crate::auth::app_password_hash(&crate::auth::short_token());
2956 assert!(
2957 db.add_app_password(admin.id, &format!("c{i}"), &h)
2958 .await
2959 .unwrap()
2960 .is_some()
2961 );
2962 }
2963 let h = crate::auth::app_password_hash(&crate::auth::short_token());
2964 assert!(
2965 db.add_app_password(admin.id, "one-too-many", &h)
2966 .await
2967 .unwrap()
2968 .is_none()
2969 );
2970
2971 assert!(db.delete_app_password(row.id, admin.id).await.unwrap());
2972 assert!(!db.delete_app_password(row.id, admin.id).await.unwrap());
2973 assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
2974 }
2975
2976 #[tokio::test]
2977 async fn an_admin_password_reset_revokes_the_app_passwords() {
2978 let (db, admin) = db_with_admin().await;
2979 let hash = crate::auth::app_password_hash(&crate::auth::short_token());
2980 db.add_app_password(admin.id, "mount", &hash)
2981 .await
2982 .unwrap()
2983 .unwrap();
2984
2985 // An edit that sets no password leaves them alone.
2986 edit(&db, admin.id, None, None, Some(true)).await;
2987 assert_eq!(db.app_passwords(admin.id).await.unwrap().len(), 1);
2988
2989 // A reset is meant to revoke every way in that existed before it.
2990 let fresh = crate::auth::hash_password("rescued12").unwrap();
2991 edit(&db, admin.id, Some(&fresh), None, None).await;
2992 assert!(db.app_passwords(admin.id).await.unwrap().is_empty());
2993 assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
2994 }
2995
2996 #[tokio::test]
2997 async fn v1_db_migrates_to_v2() {
2998 let dir = tempfile::tempdir().unwrap();
2999 let path = dir.path().join("legacy.sqlite");
3000 {
3001 let conn = rusqlite::Connection::open(&path).unwrap();
3002 conn.execute_batch(SCHEMA_V1).unwrap();
3003 conn.execute(
3004 "INSERT INTO users (name, pass_hash, is_admin, created_at)
3005 VALUES ('legacy', 'hash', 1, '2024-01-01T00:00:00Z')",
3006 [],
3007 )
3008 .unwrap();
3009 conn.execute(
3010 "INSERT INTO user_roots (user_id, path, mode) VALUES (1, 'docs', 'rw')",
3011 [],
3012 )
3013 .unwrap();
3014 }
3015 let db = Db::open(&path).await.unwrap();
3016 assert_eq!(db.user_count().await.unwrap(), 1);
3017 let u = db.find_user_by_name("legacy").await.unwrap().unwrap();
3018 assert!(u.active, "v2 migration must default active to true");
3019 assert!(u.is_admin);
3020 assert_eq!(db.user_roots(u.id).await.unwrap().len(), 1);
3021 // Accounts from before CalDAV get their principal.
3022 let p = db.pim_principal("legacy").await.unwrap().unwrap();
3023 assert_eq!(p.user_id, Some(u.id));
3024 assert_eq!(db.principal_of(u.id).await.unwrap(), p.id);
3025 let indexes: i64 = db
3026 .conn
3027 .lock()
3028 .await
3029 .query_row(
3030 "SELECT COUNT(*) FROM sqlite_master WHERE type = 'index'
3031 AND name IN ('idx_pim_props_principal', 'idx_pim_props_collection')",
3032 [],
3033 |r| r.get(0),
3034 )
3035 .unwrap();
3036 assert_eq!(indexes, 2);
3037 // Migrations are idempotent.
3038 let db2 = Db::open(&path).await.unwrap();
3039 assert_eq!(db2.user_count().await.unwrap(), 1);
3040 assert!(
3041 db2.find_user_by_name("legacy")
3042 .await
3043 .unwrap()
3044 .unwrap()
3045 .active
3046 );
3047 }
3048
3049 #[tokio::test]
3050 async fn deleted_defaults_come_back() {
3051 let (db, admin) = db_with_admin().await;
3052 let p = db.principal_of(admin.id).await.unwrap();
3053 db.pim_ensure_defaults(p).await.unwrap();
3054 let books = db.pim_collections(p, PimKind::AddressBook).await.unwrap();
3055 assert_eq!(books.len(), 1);
3056 db.pim_delete_collection(books[0].id).await.unwrap();
3057 // Remembered defaults must not hide that one is gone.
3058 db.pim_ensure_defaults(p).await.unwrap();
3059 assert_eq!(
3060 db.pim_collections(p, PimKind::AddressBook)
3061 .await
3062 .unwrap()
3063 .len(),
3064 1
3065 );
3066 }
3067
3068 #[tokio::test]
3069 async fn rooms_are_principals_not_accounts() {
3070 let (db, admin) = db_with_admin().await;
3071 let bob = db.create_user("bob", "hash", false, &[]).await.unwrap();
3072 let room = db
3073 .create_room("board", "Board", UserType::Room)
3074 .await
3075 .unwrap()
3076 .unwrap();
3077 assert_eq!(room.user_id, None);
3078
3079 // No account query returns the room.
3080 assert_eq!(db.user_count().await.unwrap(), 2);
3081 assert!(db.find_user_by_name("board").await.unwrap().is_none());
3082 assert!(db.verify_password("board", "").await.unwrap().is_none());
3083 let names: Vec<_> = db.all_users_with_roots().await.unwrap();
3084 assert!(names.iter().all(|(u, _)| u.name != "board"));
3085
3086 // One name space, without case.
3087 assert!(db.name_taken("BOARD").await.unwrap());
3088 assert!(db.create_user("Board", "hash", false, &[]).await.is_err());
3089 assert!(
3090 db.create_room("BOB", "Bob", UserType::Resource)
3091 .await
3092 .unwrap()
3093 .is_none()
3094 );
3095
3096 // Deleting an account takes its principal, collections and loans.
3097 let bob_p = db.principal_of(bob.id).await.unwrap();
3098 db.pim_ensure_defaults(bob_p).await.unwrap();
3099 let admin_p = db.principal_of(admin.id).await.unwrap();
3100 db.pim_ensure_defaults(admin_p).await.unwrap();
3101 let bobs = db.pim_collections(bob_p, PimKind::Calendar).await.unwrap();
3102 let admins = db
3103 .pim_collections(admin_p, PimKind::Calendar)
3104 .await
3105 .unwrap();
3106 db.pim_set_share(admins[0].id, bob.id, PimShareMode::Ro)
3107 .await
3108 .unwrap();
3109 assert!(db.delete_user(bob.id, &[]).await.unwrap());
3110 assert!(db.pim_principal("bob").await.unwrap().is_none());
3111 assert!(!db.name_taken("bob").await.unwrap());
3112 assert!(db.pim_collection_by_id(bobs[0].id).await.unwrap().is_none());
3113 assert!(db.pim_shares(admins[0].id).await.unwrap().is_empty());
3114 // The room outlives it.
3115 assert!(db.pim_principal("board").await.unwrap().is_some());
3116 }
3117
3118 #[tokio::test]
3119 async fn admin_user_and_passwords() {
3120 let (db, admin) = db_with_admin().await;
3121 assert!(admin.is_admin);
3122 assert!(admin.active);
3123 // Root "." rw is assigned by create_admin.
3124 let roots = db.user_roots(admin.id).await.unwrap();
3125 assert_eq!(roots.len(), 1);
3126 assert_eq!(roots[0].path, ".");
3127 assert_eq!(roots[0].mode, Mode::Rw);
3128
3129 assert!(
3130 db.verify_password("admin", "admin1234")
3131 .await
3132 .unwrap()
3133 .is_some()
3134 );
3135 assert!(db.verify_password("admin", "nope").await.unwrap().is_none());
3136 // Name lookup is case-insensitive (COLLATE NOCASE).
3137 assert!(
3138 db.verify_password("ADMIN", "admin1234")
3139 .await
3140 .unwrap()
3141 .is_some()
3142 );
3143 // Disabled users cannot verify.
3144 edit(&db, admin.id, None, None, Some(false)).await;
3145 assert!(
3146 db.verify_password("admin", "admin1234")
3147 .await
3148 .unwrap()
3149 .is_none()
3150 );
3151 edit(&db, admin.id, None, None, Some(true)).await;
3152 assert!(
3153 db.verify_password("admin", "admin1234")
3154 .await
3155 .unwrap()
3156 .is_some()
3157 );
3158 }
3159
3160 #[tokio::test]
3161 async fn setup_is_won_by_exactly_one_caller() {
3162 let db = mem().await;
3163 let hash = crate::auth::hash_password("admin1234").unwrap();
3164 assert!(db.create_admin("first", &hash).await.unwrap().is_some());
3165 // The guard lives in the insert, so a different name loses too.
3166 assert!(db.create_admin("second", &hash).await.unwrap().is_none());
3167 assert_eq!(db.user_count().await.unwrap(), 1);
3168 // The loser rolled back cleanly: no orphaned root row.
3169 let first = db.find_user_by_name("first").await.unwrap().unwrap();
3170 assert_eq!(db.user_roots(first.id).await.unwrap().len(), 1);
3171 }
3172
3173 #[tokio::test]
3174 async fn changing_a_password_drops_that_users_sessions() {
3175 let (db, admin) = db_with_admin().await;
3176 let h = crate::auth::hash_password("bobpass1").unwrap();
3177 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
3178 db.create_session(admin.id, "admin-tok").await.unwrap();
3179 db.create_session(bob.id, "bob-tok-1").await.unwrap();
3180 db.create_session(bob.id, "bob-tok-2").await.unwrap();
3181
3182 let new_h = crate::auth::hash_password("bobpass2").unwrap();
3183 edit(&db, bob.id, Some(&new_h), None, None).await;
3184 assert!(
3185 db.session_user_with_roots("bob-tok-1")
3186 .await
3187 .unwrap()
3188 .is_none()
3189 );
3190 assert!(
3191 db.session_user_with_roots("bob-tok-2")
3192 .await
3193 .unwrap()
3194 .is_none()
3195 );
3196 // Only the reset user is signed out.
3197 assert!(
3198 db.session_user_with_roots("admin-tok")
3199 .await
3200 .unwrap()
3201 .is_some()
3202 );
3203
3204 // The admin-edit path bundles the same rule into its transaction.
3205 db.create_session(bob.id, "bob-tok-3").await.unwrap();
3206 let h3 = crate::auth::hash_password("bobpass3").unwrap();
3207 db.update_user(bob.id, Some(&h3), None, None, None)
3208 .await
3209 .unwrap();
3210 assert!(
3211 db.session_user_with_roots("bob-tok-3")
3212 .await
3213 .unwrap()
3214 .is_none()
3215 );
3216 // An edit that leaves the password alone keeps the session.
3217 db.create_session(bob.id, "bob-tok-4").await.unwrap();
3218 db.update_user(bob.id, None, Some(true), None, None)
3219 .await
3220 .unwrap();
3221 assert!(
3222 db.session_user_with_roots("bob-tok-4")
3223 .await
3224 .unwrap()
3225 .is_some()
3226 );
3227 }
3228
3229 #[tokio::test]
3230 async fn sessions_lifecycle() {
3231 let (db, admin) = db_with_admin().await;
3232 assert!(
3233 db.session_user_with_roots("ghost-token")
3234 .await
3235 .unwrap()
3236 .is_none()
3237 );
3238 db.create_session(admin.id, "tok1").await.unwrap();
3239 let (u, _) = db.session_user_with_roots("tok1").await.unwrap().unwrap();
3240 assert_eq!(u.id, admin.id);
3241 // Disabling the user invalidates existing sessions.
3242 edit(&db, admin.id, None, None, Some(false)).await;
3243 assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
3244 edit(&db, admin.id, None, None, Some(true)).await;
3245 assert!(db.session_user_with_roots("tok1").await.unwrap().is_some());
3246 db.delete_session("tok1").await.unwrap();
3247 assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
3248 }
3249
3250 #[tokio::test]
3251 async fn user_crud_and_roots() {
3252 let (db, _admin) = db_with_admin().await;
3253 let h = crate::auth::hash_password("bobpass1").unwrap();
3254 let bob = db
3255 .create_user("bob", &h, false, &[("docs".into(), Mode::Rw)])
3256 .await
3257 .unwrap();
3258 assert!(!bob.is_admin);
3259 assert!(bob.active);
3260
3261 // Duplicate name (case-insensitive) is rejected.
3262 let h2 = crate::auth::hash_password("carolpass1").unwrap();
3263 assert!(db.create_user("BOB", &h2, false, &[]).await.is_err());
3264 assert!(db.create_user("carol", &h2, false, &[]).await.is_ok());
3265
3266 // Lookup helpers.
3267 assert_eq!(
3268 db.find_user_by_name("Bob").await.unwrap().unwrap().id,
3269 bob.id
3270 );
3271 assert_eq!(
3272 db.find_user_by_id(bob.id).await.unwrap().unwrap().name,
3273 "bob"
3274 );
3275 assert!(db.find_user_by_name("dave").await.unwrap().is_none());
3276 assert_eq!(db.all_users_with_roots().await.unwrap().len(), 3);
3277
3278 // Root replacement semantics.
3279 let roots = db.user_roots(bob.id).await.unwrap();
3280 assert_eq!(roots.len(), 1);
3281 db.update_user(
3282 bob.id,
3283 None,
3284 None,
3285 None,
3286 Some(&[(".".into(), Mode::Ro), ("docs".into(), Mode::Rw)]),
3287 )
3288 .await
3289 .unwrap();
3290 let roots = db.user_roots(bob.id).await.unwrap();
3291 assert_eq!(roots.len(), 2);
3292 assert!(roots.iter().any(|r| r.path == "." && r.mode == Mode::Ro));
3293 db.update_user(bob.id, None, None, None, Some(&[]))
3294 .await
3295 .unwrap();
3296 assert!(db.user_roots(bob.id).await.unwrap().is_empty());
3297
3298 // Password update.
3299 let new_h = crate::auth::hash_password("bobpass2").unwrap();
3300 edit(&db, bob.id, Some(&new_h), None, None).await;
3301 assert!(
3302 db.verify_password("bob", "bobpass1")
3303 .await
3304 .unwrap()
3305 .is_none()
3306 );
3307 assert!(
3308 db.verify_password("bob", "bobpass2")
3309 .await
3310 .unwrap()
3311 .is_some()
3312 );
3313
3314 // Admin flag + count (only active admins count).
3315 edit(&db, bob.id, None, Some(true), None).await;
3316 assert_eq!(db.count_admins().await.unwrap(), 2);
3317 edit(&db, bob.id, None, None, Some(false)).await;
3318 assert_eq!(db.count_admins().await.unwrap(), 1);
3319 edit(&db, bob.id, None, Some(false), None).await;
3320
3321 // Deletion.
3322 assert!(db.delete_user(bob.id, &[]).await.unwrap());
3323 assert!(db.find_user_by_id(bob.id).await.unwrap().is_none());
3324 assert!(!db.delete_user(bob.id, &[]).await.unwrap());
3325 assert_eq!(db.user_count().await.unwrap(), 2);
3326 }
3327
3328 fn share_row(expires_at: Option<&str>) -> ShareRow {
3329 ShareRow {
3330 id: 1,
3331 token: "t".into(),
3332 creator_id: 1,
3333 target: "docs".into(),
3334 is_file: false,
3335 mode: Mode::Ro,
3336 created_at: "2024-01-01T00:00:00Z".into(),
3337 expires_at: expires_at.map(str::to_string),
3338 password_hash: None,
3339 }
3340 }
3341
3342 #[test]
3343 fn share_expiry_logic() {
3344 assert!(!share_row(None).is_expired());
3345 assert!(!share_row(Some("2999-01-01T00:00:00Z")).is_expired());
3346 assert!(share_row(Some("2000-01-01T00:00:00Z")).is_expired());
3347 // Unparseable expiry → treated as not expired (fail open for reads).
3348 assert!(!share_row(Some("not-a-date")).is_expired());
3349 }
3350
3351 #[tokio::test]
3352 async fn shares_crud() {
3353 let (db, admin) = db_with_admin().await;
3354 let s1 = db
3355 .create_share(admin.id, "tok-a", "docs", false, Mode::Ro, None, None)
3356 .await
3357 .unwrap();
3358 let s2 = db
3359 .create_share(
3360 admin.id,
3361 "tok-b",
3362 "file.txt",
3363 true,
3364 Mode::Rw,
3365 Some("2999-01-01T00:00:00Z"),
3366 None,
3367 )
3368 .await
3369 .unwrap();
3370 assert!(s2.id > s1.id);
3371
3372 let found = db.share_by_token("tok-b").await.unwrap().unwrap();
3373 assert!(found.is_file);
3374 assert_eq!(found.mode, Mode::Rw);
3375 assert!(db.share_by_token("nope").await.unwrap().is_none());
3376
3377 // Listed newest-first.
3378 let list = db.user_shares(admin.id).await.unwrap();
3379 assert_eq!(list.len(), 2);
3380 assert_eq!(list[0].id, s2.id);
3381 // Other users see nothing.
3382 let h = crate::auth::hash_password("bobpass1").unwrap();
3383 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
3384 assert!(db.user_shares(bob.id).await.unwrap().is_empty());
3385
3386 // Only the creator can delete.
3387 assert!(!db.delete_share(s1.id, bob.id).await.unwrap());
3388 assert!(db.delete_share(s1.id, admin.id).await.unwrap());
3389 assert!(db.share_by_token("tok-a").await.unwrap().is_none());
3390 assert!(!db.delete_share(s1.id, admin.id).await.unwrap());
3391 }
3392
3393 /// The sweep decides which timestamps are past, and `expires_at` is
3394 /// stored in whatever RFC 3339 shape the client sent.
3395 #[tokio::test]
3396 async fn the_sweep_drops_expired_shares_and_stale_unlocks() {
3397 let (db, admin) = db_with_admin().await;
3398 let make = async |token: &str, expires: Option<&str>| {
3399 db.create_share(admin.id, token, "docs", false, Mode::Ro, expires, None)
3400 .await
3401 .unwrap()
3402 };
3403 let past_offset = make("gone-offset", Some("2000-01-01T00:00:00+02:00")).await;
3404 make("gone-utc", Some("2000-01-01T00:00:00Z")).await;
3405 let future = make("stays-future", Some("2999-01-01T00:00:00Z")).await;
3406 make("stays-forever", None).await;
3407 // `is_expired` keeps an unreadable timestamp; the sweep must agree,
3408 // or the two disagree about the same row.
3409 make("stays-garbage", Some("not-a-date")).await;
3410
3411 let fresh = db.create_share_unlock(future.id).await.unwrap();
3412 let stale = db.create_share_unlock(future.id).await.unwrap();
3413 let doomed = db.create_share_unlock(past_offset.id).await.unwrap();
3414 {
3415 let c = db.conn.lock().await;
3416 c.execute(
3417 "UPDATE share_unlocks SET created_at = '2000-01-01T00:00:00Z' WHERE token = ?1",
3418 [&stale],
3419 )
3420 .unwrap();
3421 }
3422
3423 let (shares, unlocks) = db.sweep().await.unwrap();
3424 assert_eq!(shares, 2, "only the two past timestamps");
3425 // The stale one, plus the cascade from the expired share it opened.
3426 assert_eq!(unlocks, 1, "the cascade runs with the share, not here");
3427
3428 for token in ["gone-offset", "gone-utc"] {
3429 assert!(db.share_by_token(token).await.unwrap().is_none(), "{token}");
3430 }
3431 for token in ["stays-future", "stays-forever", "stays-garbage"] {
3432 assert!(db.share_by_token(token).await.unwrap().is_some(), "{token}");
3433 }
3434
3435 assert!(db.share_unlock_valid(&fresh, future.id).await.unwrap());
3436 assert!(!db.share_unlock_valid(&stale, future.id).await.unwrap());
3437 assert!(
3438 !db.share_unlock_valid(&doomed, past_offset.id)
3439 .await
3440 .unwrap(),
3441 "an unlock must not outlive the share it opened"
3442 );
3443
3444 assert_eq!(
3445 db.sweep().await.unwrap(),
3446 (0, 0),
3447 "a second pass is a no-op"
3448 );
3449 }
3450
3451 /// The unlock token is what a visitor's cookie carries, so an unlock
3452 /// that opened the wrong share would be a full bypass of the password.
3453 #[tokio::test]
3454 async fn share_unlocks_are_bound_to_one_share() {
3455 let (db, admin) = db_with_admin().await;
3456 let a = db
3457 .create_share(
3458 admin.id,
3459 "tok-a",
3460 "docs",
3461 false,
3462 Mode::Ro,
3463 None,
3464 Some("hash"),
3465 )
3466 .await
3467 .unwrap();
3468 let b = db
3469 .create_share(
3470 admin.id,
3471 "tok-b",
3472 "other",
3473 false,
3474 Mode::Ro,
3475 None,
3476 Some("hash"),
3477 )
3478 .await
3479 .unwrap();
3480 assert_eq!(
3481 db.share_by_token("tok-a")
3482 .await
3483 .unwrap()
3484 .unwrap()
3485 .password_hash,
3486 Some("hash".to_string())
3487 );
3488
3489 let unlock = db.create_share_unlock(a.id).await.unwrap();
3490 assert!(db.share_unlock_valid(&unlock, a.id).await.unwrap());
3491 assert!(!db.share_unlock_valid(&unlock, b.id).await.unwrap());
3492 assert!(!db.share_unlock_valid("nonsense", a.id).await.unwrap());
3493
3494 // Deleting the share takes its unlocks with it, so a re-created
3495 // share that happened to reuse the id could not inherit them.
3496 assert!(db.delete_share(a.id, admin.id).await.unwrap());
3497 assert!(!db.share_unlock_valid(&unlock, a.id).await.unwrap());
3498 }
3499
3500 #[tokio::test]
3501 async fn revoking_a_path_takes_its_descendants_only() {
3502 let (db, admin) = db_with_admin().await;
3503 let mk = async |token: &str, target: &str| {
3504 db.create_share(admin.id, token, target, false, Mode::Ro, None, None)
3505 .await
3506 .unwrap();
3507 };
3508 mk("t-self", "docs").await;
3509 mk("t-child", "docs/a.txt").await;
3510 mk("t-deep", "docs/inner/b.txt").await;
3511 // A sibling whose name merely starts with "docs" must survive.
3512 mk("t-sibling", "docs2/c.txt").await;
3513 mk("t-other", "src").await;
3514 // SQL wildcards in a path are literal characters, not patterns.
3515 mk("t-wild", "do%s/d.txt").await;
3516
3517 assert_eq!(db.revoke_shares_at("docs").await.unwrap(), 3);
3518 for gone in ["t-self", "t-child", "t-deep"] {
3519 assert!(db.share_by_token(gone).await.unwrap().is_none(), "{gone}");
3520 }
3521 for kept in ["t-sibling", "t-other", "t-wild"] {
3522 assert!(db.share_by_token(kept).await.unwrap().is_some(), "{kept}");
3523 }
3524 // Revoking a path nobody shared is a no-op, not an error.
3525 assert_eq!(db.revoke_shares_at("nothing/here").await.unwrap(), 0);
3526 }
3527
3528 #[tokio::test]
3529 async fn settings_round_trip() {
3530 let (db, _admin) = db_with_admin().await;
3531 assert!(!db.allow_writable_shares().await.unwrap());
3532 db.set_allow_writable_shares(true).await.unwrap();
3533 assert!(db.allow_writable_shares().await.unwrap());
3534 // Upsert semantics.
3535 db.set_allow_writable_shares(false).await.unwrap();
3536 assert!(!db.allow_writable_shares().await.unwrap());
3537 // Generic get/set.
3538 db.set_setting("custom", "v").await.unwrap();
3539 assert_eq!(
3540 db.get_setting("custom").await.unwrap().as_deref(),
3541 Some("v")
3542 );
3543 assert_eq!(db.get_setting("missing").await.unwrap(), None);
3544 }
3545}
3546