dovenest

db.rs

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