dovenest

db.rs

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