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