dovenest

db.rs

1	use std::path::Path;
2	use std::sync::Arc;
3
4	pub use api_types::{AppPasswordInfo, AuthMode, Mode};
5	pub use pimdav::principal::UserType;
6	use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ValueRef};
7	use rusqlite::{Connection, OptionalExtension, params};
8	use webauthn_rs::prelude::Uuid;
9
10	const SCHEMA_VERSION: i64 = 13;
11
12	/// SQL adapter for reading a [`Mode`]. A newtype is needed because both the
13	/// rusqlite traits and `Mode` are foreign to this crate. Writes bind
14	/// `Mode::as_str` directly.
15	///
16	/// The stored strings are unchanged ("rw"/"ro"), so old databases still read.
17	struct SqlMode(Mode);
18
19	impl FromSql for SqlMode {
20	fn column_result(v: ValueRef<'_>) -> FromSqlResult<Self> {
21	let s = v.as_str()?;
22	Mode::from_wire(s)
23	.map(SqlMode)
24	.ok_or_else(\|\| FromSqlError::Other(format!("unknown mode {s:?}").into()))
25	}
26	}
27
28	/// SQL adapter for [`AuthMode`], for the same reason as [`SqlMode`].
29	struct SqlAuthMode(AuthMode);
30
31	impl FromSql for SqlAuthMode {
32	fn column_result(v: ValueRef<'_>) -> FromSqlResult<Self> {
33	let s = v.as_str()?;
34	AuthMode::from_wire(s)
35	.map(SqlAuthMode)
36	.ok_or_else(\|\| FromSqlError::Other(format!("unknown auth mode {s:?}").into()))
37	}
38	}
39
40	#[derive(Debug, Clone)]
41	pub struct User {
42	pub id: i64,
43	pub name: String,
44	pub is_admin: bool,
45	/// Disabled users cannot sign in and their sessions are rejected.
46	pub active: bool,
47	/// Profile setting: single click opens entries (off = click selects).
48	pub single_click: bool,
49	/// Profile setting: show thumbnails in the grid.
50	pub thumbnails: bool,
51	/// Preferred UI language tag ("en", "de", "fr"); None = follow the
52	/// browser.
53	pub language: Option<String>,
54	/// Profile setting: the root the UI opens by default. May point at a
55	/// root the user no longer has; the API filters that out.
56	pub default_root_id: Option<i64>,
57	/// What this account needs to sign in.
58	pub auth_mode: AuthMode,
59	/// Whether a password is set. False means the account signs in with
60	/// passkeys only. See [`NO_PASSWORD`].
61	pub has_password: bool,
62	}
63
64	/// The `pass_hash` of an account with no password.
65	///
66	/// A sentinel rather than NULL: `users.pass_hash` is `NOT NULL`, and SQLite
67	/// cannot drop that constraint without rebuilding the table. Nothing verifies
68	/// against it — [`Db::verify_password`] swaps in [`DUMMY_HASH`] so the reject
69	/// costs the same as a wrong password, and `PasswordHash::new("")` fails
70	/// anyway.
71	pub const NO_PASSWORD: &str = "";
72
73	/// How many passkeys one account may hold.
74	///
75	/// Also the exact number of credentials a named sign-in challenge lists. The
76	/// two are one number on purpose: the challenge pads a short list with decoys
77	/// so its length says nothing about the account, and that only works while no
78	/// account can push past the padding.
79	pub const PASSKEY_LIMIT: usize = 8;
80
81	/// How many app passwords one account may hold. One per client is the point.
82	pub const APP_PASSWORD_LIMIT: usize = 10;
83
84	/// What [`Db::delete_passkey`] did.
85	#[derive(Debug, Clone, Copy, PartialEq, Eq)]
86	pub enum PasskeyDeleted {
87	Gone,
88	/// No such passkey, or it belongs to someone else.
89	NotFound,
90	/// Removing it would have left the account unreachable.
91	LastCredential,
92	}
93
94	/// One registered passkey. `passkey` is the serialized `webauthn-rs`
95	/// credential; everything else is for the settings list.
96	#[derive(Debug, Clone)]
97	pub struct PasskeyRow {
98	pub id: i64,
99	pub name: String,
100	pub created_at: String,
101	pub last_used_at: Option<String>,
102	pub discoverable: Option<bool>,
103	/// `webauthn_rs::prelude::Passkey` as JSON.
104	pub passkey: String,
105	}
106
107	#[derive(Debug, Clone)]
108	pub struct RootRow {
109	pub id: i64,
110	/// Path relative to the server root; "." means the whole root.
111	pub path: String,
112	pub mode: Mode,
113	}
114
115	#[derive(Debug, Clone)]
116	pub struct ShareRow {
117	pub id: i64,
118	pub token: String,
119	pub creator_id: i64,
120	/// Path of the shared item relative to the server root.
121	pub target: String,
122	pub is_file: bool,
123	pub mode: Mode,
124	pub created_at: String,
125	pub expires_at: Option<String>,
126	/// Argon2 hash of the share's password, when it has one. Resolve,
127	/// listing and download all stay locked until the visitor enters it and
128	/// gets an unlock cookie.
129	pub password_hash: Option<String>,
130	}
131
132	impl ShareRow {
133	pub fn is_expired(&self) -> bool {
134	match &self.expires_at {
135	Some(e) => chrono::DateTime::parse_from_rfc3339(e)
136	.map(\|t\| chrono::Utc::now() >= t.with_timezone(&chrono::Utc))
137	.unwrap_or(false),
138	None => false,
139	}
140	}
141	}
142
143	/// A [`ShareRow`] together with the account that created it.
144	#[derive(Debug, Clone)]
145	pub struct ShareWithCreator {
146	pub share: ShareRow,
147	pub creator_name: String,
148	/// Whether that account can still sign in. Deactivating an account leaves
149	/// its shares live.
150	pub creator_active: bool,
151	}
152
153	#[derive(Debug, Clone, Copy, PartialEq, Eq)]
154	pub enum PimKind {
155	Calendar,
156	AddressBook,
157	}
158
159	impl PimKind {
160	fn parse(s: &str) -> Self {
161	match s {
162	"cal" => PimKind::Calendar,
163	_ => PimKind::AddressBook,
164	}
165	}
166
167	fn as_str(self) -> &'static str {
168	match self {
169	PimKind::Calendar => "cal",
170	PimKind::AddressBook => "card",
171	}
172	}
173	}
174
175	/// A calendar or address book.
176	#[derive(Debug, Clone, Default)]
177	pub struct PimCollection {
178	pub id: i64,
179	/// The URL segment.
180	pub slug: String,
181	pub displayname: Option<String>,
182	pub description: Option<String>,
183	pub color: Option<String>,
184	/// A VCALENDAR holding one VTIMEZONE.
185	pub timezone: Option<String>,
186	pub sort_order: Option<String>,
187	/// The component types a calendar takes, comma-separated. Empty for an
188	/// address book.
189	pub components: String,
190	/// Grows with every change to the collection or its members.
191	pub seq: i64,
192	}
193
194	/// A calendar or address object, without its data.
195	#[derive(Debug, Clone, Default)]
196	pub struct PimObject {
197	/// The URL segment.
198	pub name: String,
199	pub uid: String,
200	/// `VEVENT`, `VTODO`, `VJOURNAL` or `VCARD`.
201	pub component: String,
202	/// With the quotes.
203	pub etag: String,
204	pub size: i64,
205	pub modified_at: String,
206	}
207
208	#[derive(Debug, PartialEq, Eq)]
209	pub enum PimWrite {
210	Created,
211	Updated,
212	Deleted,
213	NotFound,
214	PreconditionFailed,
215	/// Another object in the collection has this UID, under this name.
216	UidConflict(String),
217	}
218
219	/// The `If-Match` and `If-None-Match` headers of a write.
220	#[derive(Debug, Default)]
221	pub struct Precondition {
222	pub if_match: Option<String>,
223	pub if_none_match: Option<String>,
224	}
225
226	impl Precondition {
227	/// Whether the write may go ahead given the current ETag, if any.
228	fn allows(&self, current: Option<&str>) -> bool {
229	let listed = \|header: &str\| match current {
230	Some(etag) => header.split(',').any(\|t\| {
231	let t = t.trim();
232	t == "*" \|\| t.strip_prefix("W/").unwrap_or(t) == etag
233	}),
234	None => false,
235	};
236	self.if_match.as_deref().is_none_or(listed)
237	&& !self.if_none_match.as_deref().is_some_and(listed)
238	}
239	}
240
241	/// The columns [`map_user`] reads, in order. Every SELECT that builds a
242	/// [`User`] uses one of these two, so a new column is added in one place.
243	/// `USER_COLS_U` is the same list qualified for the queries that join
244	/// `users u` against `user_roots`.
245	const USER_COLS: &str = "id, name, is_admin != 0, active != 0, single_click != 0,
246	thumbnails != 0, language, default_root_id, auth_mode, pass_hash != ''";
247	const USER_COLS_U: &str = "u.id, u.name, u.is_admin != 0, u.active != 0, u.single_click != 0,
248	u.thumbnails != 0, u.language, u.default_root_id, u.auth_mode, u.pass_hash != ''";
249	/// How many columns the two lists above cover. The joined queries read the
250	/// root columns starting here.
251	const USER_COL_COUNT: usize = 10;
252
253	/// Every query can fail, and every caller decides what to do about it.
254	///
255	/// Earlier versions swallowed read errors and returned a default (an empty
256	/// root list, a count of 0). That turned a broken database into a plausible
257	/// answer: "you have no folders" instead of an error. One contract now.
258	pub type DbResult<T> = Result<T, rusqlite::Error>;
259
260	#[derive(Clone)]
261	pub struct Db(Arc<tokio::sync::Mutex<Connection>>);
262
263	impl Db {
264	pub async fn open(path: &Path) -> anyhow::Result<Self> {
265	if let Some(parent) = path.parent()
266	&& !parent.as_os_str().is_empty()
267	{
268	std::fs::create_dir_all(parent)?;
269	}
270	let conn = Connection::open(path)?;
271	conn.pragma_update(None, "journal_mode", "WAL")?;
272	// WAL plus NORMAL: fsync only at checkpoints. A crash can lose the
273	// last commits, never the database file.
274	conn.pragma_update(None, "synchronous", "NORMAL")?;
275	conn.pragma_update(None, "foreign_keys", "ON")?;
276	conn.pragma_update(None, "busy_timeout", "5000")?;
277	Self::migrate(&conn)?;
278	Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
279	}
280
281	fn migrate(conn: &Connection) -> rusqlite::Result<()> {
282	conn.execute(
283	"CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)",
284	[],
285	)?;
286	let version: i64 = conn
287	.query_row(
288	"SELECT value FROM meta WHERE key = 'schema_version'",
289	[],
290	\|r\| r.get::<_, String>(0),
291	)
292	.optional()?
293	.and_then(\|v\| v.parse().ok())
294	.unwrap_or(0);
295
296	if version < 1 {
297	conn.execute_batch(SCHEMA_V1)?;
298	}
299	if version < 2 {
300	// User management (M7): a disabled flag so admins can suspend
301	// accounts without deleting them.
302	conn.execute_batch("ALTER TABLE users ADD COLUMN active INTEGER NOT NULL DEFAULT 1")?;
303	}
304	if version < 3 {
305	// Per-user profile settings: click-to-open mode. No migration
306	// from the old behaviour — everyone starts on the new default
307	// (off: single click selects, double click opens).
308	conn.execute_batch(
309	"ALTER TABLE users ADD COLUMN single_click INTEGER NOT NULL DEFAULT 0",
310	)?;
311	}
312	if version < 4 {
313	// Per-user UI language preference; NULL means "follow the
314	// browser".
315	conn.execute_batch("ALTER TABLE users ADD COLUMN language TEXT")?;
316	}
317	if version < 5 {
318	// The share list is queried by creator on every shares page.
319	conn.execute_batch(
320	"CREATE INDEX IF NOT EXISTS idx_shares_creator ON shares(creator_id)",
321	)?;
322	}
323	if version < 6 {
324	// Unlocks cascade with their share, which cascades with its
325	// creator's account.
326	conn.execute_batch(
327	"ALTER TABLE shares ADD COLUMN password_hash TEXT;
328	CREATE TABLE IF NOT EXISTS share_unlocks (
329	token TEXT PRIMARY KEY,
330	share_id INTEGER NOT NULL REFERENCES shares(id) ON DELETE CASCADE,
331	created_at TEXT NOT NULL
332	);",
333	)?;
334	}
335	if version < 7 {
336	// `delete_share` cascades into share_unlocks, which is a full
337	// scan of that table without this.
338	conn.execute_batch(
339	"CREATE INDEX IF NOT EXISTS idx_share_unlocks_share
340	ON share_unlocks(share_id)",
341	)?;
342	}
343	if version < 8 {
344	// On by default, so `--cache` is the only step needed to get
345	// thumbnails.
346	conn.execute_batch(
347	"ALTER TABLE users ADD COLUMN thumbnails INTEGER NOT NULL DEFAULT 1",
348	)?;
349	}
350	if version < 9 {
351	// Per-user default root. No foreign key on purpose: removing a
352	// root must not fail because of this column.
353	conn.execute_batch("ALTER TABLE users ADD COLUMN default_root_id INTEGER")?;
354	}
355	if version < 10 {
356	// Passkeys, and how they combine with the password.
357	//
358	// `webauthn_id` is the WebAuthn user handle: a random uuid the
359	// authenticator stores inside a discoverable credential and hands
360	// back at sign-in. It must never change once a passkey exists, or
361	// that passkey can no longer be traced to its account. Filled in
362	// lazily on the first registration, so accounts that never use a
363	// passkey keep it NULL.
364	conn.execute_batch(
365	"ALTER TABLE users ADD COLUMN auth_mode TEXT NOT NULL DEFAULT 'either';
366	ALTER TABLE users ADD COLUMN webauthn_id TEXT;
367	CREATE TABLE IF NOT EXISTS passkeys (
368	id INTEGER PRIMARY KEY AUTOINCREMENT,
369	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
370	cred_id BLOB NOT NULL UNIQUE,
371	passkey TEXT NOT NULL,
372	name TEXT NOT NULL,
373	discoverable INTEGER,
374	created_at TEXT NOT NULL,
375	last_used_at TEXT
376	);
377	CREATE INDEX IF NOT EXISTS idx_passkeys_user ON passkeys(user_id);
378	CREATE UNIQUE INDEX IF NOT EXISTS idx_users_webauthn_id
379	ON users(webauthn_id) WHERE webauthn_id IS NOT NULL;",
380	)?;
381	}
382	if version < 11 {
383	// `secret_hash` is UNIQUE because the lookup keys on it.
384	conn.execute_batch(
385	"CREATE TABLE IF NOT EXISTS app_passwords (
386	id INTEGER PRIMARY KEY AUTOINCREMENT,
387	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
388	name TEXT NOT NULL,
389	secret_hash TEXT NOT NULL UNIQUE,
390	created_at TEXT NOT NULL,
391	last_used_at TEXT
392	);
393	CREATE INDEX IF NOT EXISTS idx_app_passwords_user
394	ON app_passwords(user_id);",
395	)?;
396	}
397	if version < 12 {
398	// CalDAV and CardDAV. `seq` counts every change to a collection
399	// and its members; `pim_changes` keeps the latest change per
400	// member, deletions included, for sync tokens.
401	conn.execute_batch(
402	"CREATE TABLE IF NOT EXISTS pim_collections (
403	id INTEGER PRIMARY KEY AUTOINCREMENT,
404	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
405	kind TEXT NOT NULL CHECK (kind IN ('cal','card')),
406	slug TEXT NOT NULL,
407	displayname TEXT,
408	description TEXT,
409	color TEXT,
410	timezone TEXT,
411	sort_order TEXT,
412	components TEXT NOT NULL DEFAULT '',
413	seq INTEGER NOT NULL DEFAULT 0,
414	created_at TEXT NOT NULL,
415	UNIQUE (user_id, kind, slug)
416	);
417	CREATE TABLE IF NOT EXISTS pim_objects (
418	id INTEGER PRIMARY KEY AUTOINCREMENT,
419	collection_id INTEGER NOT NULL
420	REFERENCES pim_collections(id) ON DELETE CASCADE,
421	name TEXT NOT NULL,
422	uid TEXT NOT NULL,
423	component TEXT NOT NULL,
424	data BLOB NOT NULL,
425	etag TEXT NOT NULL,
426	modified_at TEXT NOT NULL,
427	UNIQUE (collection_id, name),
428	UNIQUE (collection_id, uid)
429	);
430	CREATE TABLE IF NOT EXISTS pim_changes (
431	collection_id INTEGER NOT NULL
432	REFERENCES pim_collections(id) ON DELETE CASCADE,
433	name TEXT NOT NULL,
434	seq INTEGER NOT NULL,
435	deleted INTEGER NOT NULL,
436	PRIMARY KEY (collection_id, name)
437	);
438	CREATE INDEX IF NOT EXISTS idx_pim_changes_seq
439	ON pim_changes(collection_id, seq);",
440	)?;
441	}
442	if version < 13 {
443	// Rooms and resources are rows of `users` that cannot sign in, so
444	// they share the name space and the collection ownership of
445	// accounts. `pim_shares` lends a collection to another account.
446	conn.execute_batch(
447	"ALTER TABLE users ADD COLUMN kind TEXT NOT NULL DEFAULT 'person'
448	CHECK (kind IN ('person','room','resource'));
449	ALTER TABLE users ADD COLUMN display_name TEXT;
450	CREATE TABLE IF NOT EXISTS pim_shares (
451	collection_id INTEGER NOT NULL
452	REFERENCES pim_collections(id) ON DELETE CASCADE,
453	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
454	mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
455	PRIMARY KEY (collection_id, user_id)
456	);
457	CREATE INDEX IF NOT EXISTS idx_pim_shares_user ON pim_shares(user_id);",
458	)?;
459	}
460	conn.execute(
461	"INSERT OR REPLACE INTO meta (key, value) VALUES ('schema_version', ?1)",
462	[SCHEMA_VERSION.to_string()],
463	)?;
464	Ok(())
465	}
466
467	// ---------- users ----------
468
469	pub async fn user_count(&self) -> DbResult<i64> {
470	let c = self.0.lock().await;
471	let mut stmt = c.prepare_cached("SELECT COUNT(*) FROM users")?;
472	stmt.query_row([], \|r\| r.get(0))
473	}
474
475	/// Create the first admin account with the whole root visible (read-write).
476	///
477	/// `None` means a user already existed. The `WHERE NOT EXISTS` guard runs
478	/// inside the same transaction as the insert, so two concurrent first-boot
479	/// setups cannot both win; a caller's earlier `user_count` check is only
480	/// an optimization, not the guarantee.
481	pub async fn create_admin(&self, name: &str, pass_hash: &str) -> DbResult<Option<User>> {
482	let mut c = self.0.lock().await;
483	let tx = c.transaction()?;
484	let inserted = tx.execute(
485	"INSERT INTO users (name, pass_hash, is_admin, created_at)
486	SELECT ?1, ?2, 1, ?3 WHERE NOT EXISTS (SELECT 1 FROM users)",
487	params![name, pass_hash, now()],
488	)?;
489	if inserted == 0 {
490	return Ok(None); // dropping `tx` rolls back
491	}
492	let user_id = tx.last_insert_rowid();
493	tx.execute(
494	"INSERT INTO user_roots (user_id, path, mode) VALUES (?1, '.', 'rw')",
495	params![user_id],
496	)?;
497	tx.commit()?;
498	Ok(Some(new_user(user_id, name, true)))
499	}
500
501	pub async fn verify_password(&self, name: &str, password: &str) -> DbResult<Option<User>> {
502	// The guard is scoped to the query alone. Argon2 below is slow by
503	// design; holding the single connection lock across it would make one
504	// login serialize every other database access.
505	let row: Option<(User, String)> = {
506	let c = self.0.lock().await;
507	c.query_row(
508	&format!(
509	"SELECT {USER_COLS}, pass_hash FROM users WHERE name = ?1 AND kind = 'person'"
510	),
511	[name],
512	\|r\| Ok((map_user(r)?, r.get(USER_COL_COUNT)?)),
513	)
514	.optional()?
515	};
516	// An unknown name, a disabled account and a passkey-only account all
517	// still pay for one Argon2 verify, so the response time does not tell
518	// them apart from a real account with a wrong password.
519	let (user, hash) = match row {
520	Some((u, h)) if u.active && u.has_password => (Some(u), h),
521	_ => (None, DUMMY_HASH.clone()),
522	};
523	let ok = crate::auth::verify_password_async(password, &hash).await;
524	Ok(user.filter(\|_\| ok))
525	}
526
527	pub async fn create_session(&self, user_id: i64, token: &str) -> DbResult<()> {
528	let c = self.0.lock().await;
529	c.execute(
530	"INSERT INTO sessions (token, user_id, created_at, last_seen_at)
531	VALUES (?1, ?2, ?3, ?4)",
532	params![token, user_id, now(), now()],
533	)?;
534	Ok(())
535	}
536
537	pub async fn delete_session(&self, token: &str) -> DbResult<()> {
538	let c = self.0.lock().await;
539	c.execute("DELETE FROM sessions WHERE token = ?1", [token])?;
540	Ok(())
541	}
542
543	/// The session's user plus that user's roots, in one round trip. Every
544	/// authenticated request needs both, so they are not two queries.
545	pub async fn session_user_with_roots(
546	&self,
547	token: &str,
548	) -> DbResult<Option<(User, Vec<RootRow>)>> {
549	let c = self.0.lock().await;
550	let mut stmt = c.prepare_cached(&format!(
551	"SELECT {USER_COLS_U}, r.id, r.path, r.mode
552	FROM sessions s
553	JOIN users u ON u.id = s.user_id
554	LEFT JOIN user_roots r ON r.user_id = u.id
555	WHERE s.token = ?1 AND u.active = 1
556	ORDER BY r.id",
557	))?;
558	// One row per root; a user without roots still returns one row, with
559	// the root columns NULL.
560	let mut user: Option<User> = None;
561	let mut roots: Vec<RootRow> = Vec::new();
562	let mut rows = stmt.query([token])?;
563	while let Some(r) = rows.next()? {
564	if user.is_none() {
565	user = Some(map_user(r)?);
566	}
567	if let Some(id) = r.get::<_, Option<i64>>(USER_COL_COUNT)? {
568	roots.push(RootRow {
569	id,
570	path: r.get(USER_COL_COUNT + 1)?,
571	mode: r.get::<_, SqlMode>(USER_COL_COUNT + 2)?.0,
572	});
573	}
574	}
575	Ok(user.map(\|u\| (u, roots)))
576	}
577
578	// ---------- roots ----------
579
580	pub async fn user_roots(&self, user_id: i64) -> DbResult<Vec<RootRow>> {
581	let c = self.0.lock().await;
582	let mut stmt = c.prepare_cached(
583	"SELECT id, path, mode FROM user_roots WHERE user_id = ?1 ORDER BY id",
584	)?;
585	let rows = stmt.query_map([user_id], \|r\| {
586	Ok(RootRow {
587	id: r.get(0)?,
588	path: r.get(1)?,
589	mode: r.get::<_, SqlMode>(2)?.0,
590	})
591	})?;
592	rows.collect()
593	}
594
595	// ---------- admin: user management (M7) ----------
596
597	/// Every user with their roots, in one query. The admin user list needs
598	/// both, and a per-user roots query would be one round trip per user.
599	pub async fn all_users_with_roots(&self) -> DbResult<Vec<(User, Vec<RootRow>)>> {
600	let c = self.0.lock().await;
601	let mut stmt = c.prepare_cached(&format!(
602	"SELECT {USER_COLS_U}, r.id, r.path, r.mode
603	FROM users u
604	LEFT JOIN user_roots r ON r.user_id = u.id
605	WHERE u.kind = 'person'
606	ORDER BY u.id, r.id",
607	))?;
608	// Rows arrive grouped by user, so a new user id starts a new group.
609	let mut out: Vec<(User, Vec<RootRow>)> = Vec::new();
610	let mut rows = stmt.query([])?;
611	while let Some(r) = rows.next()? {
612	let uid: i64 = r.get(0)?;
613	if out.last().is_none_or(\|(u, _)\| u.id != uid) {
614	out.push((map_user(r)?, Vec::new()));
615	}
616	if let Some(id) = r.get::<_, Option<i64>>(USER_COL_COUNT)? {
617	out.last_mut().expect("pushed above").1.push(RootRow {
618	id,
619	path: r.get(USER_COL_COUNT + 1)?,
620	mode: r.get::<_, SqlMode>(USER_COL_COUNT + 2)?.0,
621	});
622	}
623	}
624	Ok(out)
625	}
626
627	pub async fn find_user_by_id(&self, id: i64) -> DbResult<Option<User>> {
628	let c = self.0.lock().await;
629	c.query_row(
630	&format!("SELECT {USER_COLS} FROM users WHERE id = ?1 AND kind = 'person'"),
631	[id],
632	map_user,
633	)
634	.optional()
635	}
636
637	/// Rooms and resources too, since they share the name space.
638	pub async fn find_user_by_name(&self, name: &str) -> DbResult<Option<User>> {
639	let c = self.0.lock().await;
640	c.query_row(
641	&format!("SELECT {USER_COLS} FROM users WHERE name = ?1"),
642	[name],
643	map_user,
644	)
645	.optional()
646	}
647
648	pub async fn count_admins(&self) -> DbResult<i64> {
649	let c = self.0.lock().await;
650	c.query_row(
651	"SELECT COUNT(*) FROM users WHERE is_admin = 1 AND active = 1",
652	[],
653	\|r\| r.get(0),
654	)
655	}
656
657	/// Create a user with the given roots (path, mode) pairs.
658	pub async fn create_user(
659	&self,
660	name: &str,
661	pass_hash: &str,
662	is_admin: bool,
663	roots: &[(String, Mode)],
664	) -> DbResult<User> {
665	let mut c = self.0.lock().await;
666	let tx = c.transaction()?;
667	tx.execute(
668	"INSERT INTO users (name, pass_hash, is_admin, active, created_at)
669	VALUES (?1, ?2, ?3, 1, ?4)",
670	params![name, pass_hash, is_admin as i64, now()],
671	)?;
672	let user_id = tx.last_insert_rowid();
673	for (path, mode) in roots {
674	tx.execute(
675	"INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
676	params![user_id, path, mode.as_str()],
677	)?;
678	}
679	tx.commit()?;
680	Ok(new_user(user_id, name, is_admin))
681	}
682
683	/// Write the profile settings a user edits for themselves.
684	pub async fn set_user_profile(&self, u: &User) -> DbResult<()> {
685	let c = self.0.lock().await;
686	c.execute(
687	"UPDATE users SET single_click = ?1, thumbnails = ?2, language = ?3,
688	default_root_id = ?4
689	WHERE id = ?5",
690	params![
691	u.single_click,
692	u.thumbnails,
693	u.language,
694	u.default_root_id,
695	u.id
696	],
697	)?;
698	Ok(())
699	}
700
701	/// Apply an admin edit atomically: every `Some` field is written in one
702	/// transaction, so a failure midway leaves the user unchanged.
703	pub async fn update_user(
704	&self,
705	id: i64,
706	pass_hash: Option<&str>,
707	is_admin: Option<bool>,
708	active: Option<bool>,
709	roots: Option<&[(String, Mode)]>,
710	) -> DbResult<()> {
711	let mut c = self.0.lock().await;
712	let tx = c.transaction()?;
713	// An admin sets a password to get someone back into a locked-out
714	// account, so every other way in goes with it: the passkeys, the app
715	// passwords, the requirement to use one. Always rather than on
716	// request, so a reset cannot leave a credential the user no longer
717	// has.
718	if let Some(h) = pass_hash {
719	set_password(&tx, id, h)?;
720	tx.execute("DELETE FROM passkeys WHERE user_id = ?1", [id])?;
721	tx.execute("DELETE FROM app_passwords WHERE user_id = ?1", [id])?;
722	tx.execute(
723	"UPDATE users SET auth_mode = ?1 WHERE id = ?2",
724	params![AuthMode::Either.as_str(), id],
725	)?;
726	}
727	if let Some(a) = is_admin {
728	tx.execute(
729	"UPDATE users SET is_admin = ?1 WHERE id = ?2",
730	params![a as i64, id],
731	)?;
732	}
733	if let Some(a) = active {
734	tx.execute(
735	"UPDATE users SET active = ?1 WHERE id = ?2",
736	params![a as i64, id],
737	)?;
738	}
739	if let Some(roots) = roots {
740	tx.execute("DELETE FROM user_roots WHERE user_id = ?1", [id])?;
741	for (path, mode) in roots {
742	tx.execute(
743	"INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
744	params![id, path, mode.as_str()],
745	)?;
746	}
747	}
748	tx.commit()
749	}
750
751	// ---------- credentials: password, passkeys, sign-in mode ----------
752
753	/// Set the password. Leaves every session alone, so the caller must pair
754	/// this with [`Db::delete_other_sessions`] — see
755	/// [`crate::api::passkeys`]'s `invalidate_elsewhere`. The admin path uses
756	/// [`set_password`] instead, which ends every session including its own.
757	///
758	/// Only for setting a real one. Clearing it is [`Db::clear_user_password`],
759	/// which has a rule to keep.
760	pub async fn set_password_keeping_sessions(&self, id: i64, pass_hash: &str) -> DbResult<()> {
761	let c = self.0.lock().await;
762	c.execute(
763	"UPDATE users SET pass_hash = ?1 WHERE id = ?2",
764	params![pass_hash, id],
765	)?;
766	Ok(())
767	}
768
769	/// Leave the account on its passkeys alone. `false` means that would have
770	/// locked it out, so nothing changed.
771	pub async fn clear_user_password(&self, id: i64) -> DbResult<bool> {
772	let mut c = self.0.lock().await;
773	let tx = c.transaction()?;
774	tx.execute(
775	"UPDATE users SET pass_hash = ?1 WHERE id = ?2",
776	params![NO_PASSWORD, id],
777	)?;
778	commit_if_reachable(tx, id)
779	}
780
781	/// `false` means the account does not satisfy the new mode, so it stands.
782	pub async fn set_user_auth_mode(&self, id: i64, mode: AuthMode) -> DbResult<bool> {
783	let mut c = self.0.lock().await;
784	let tx = c.transaction()?;
785	tx.execute(
786	"UPDATE users SET auth_mode = ?1 WHERE id = ?2",
787	params![mode.as_str(), id],
788	)?;
789	commit_if_reachable(tx, id)
790	}
791
792	/// Drop every session of this user except `keep`.
793	///
794	/// Called after any credential change. Otherwise a session stolen before
795	/// the change keeps working for its full 30 days.
796	pub async fn delete_other_sessions(&self, user_id: i64, keep: &str) -> DbResult<()> {
797	let c = self.0.lock().await;
798	c.execute(
799	"DELETE FROM sessions WHERE user_id = ?1 AND token != ?2",
800	params![user_id, keep],
801	)?;
802	Ok(())
803	}
804
805	/// This account's WebAuthn user handle, creating it on first use.
806	///
807	/// Stable for the lifetime of the account: a discoverable passkey stores
808	/// this value and hands it back at sign-in, so changing it would orphan
809	/// every existing passkey.
810	pub async fn user_webauthn_id(&self, id: i64) -> DbResult<Uuid> {
811	let c = self.0.lock().await;
812	let existing: Option<String> = c
813	.query_row("SELECT webauthn_id FROM users WHERE id = ?1", [id], \|r\| {
814	r.get(0)
815	})
816	.optional()?
817	.flatten();
818	if let Some(parsed) = existing.as_deref().and_then(\|s\| Uuid::parse_str(s).ok()) {
819	return Ok(parsed);
820	}
821	let fresh = Uuid::new_v4();
822	c.execute(
823	"UPDATE users SET webauthn_id = ?1 WHERE id = ?2",
824	params![fresh.to_string(), id],
825	)?;
826	Ok(fresh)
827	}
828
829	/// The account a discoverable credential's user handle points at.
830	pub async fn find_user_by_webauthn_id(&self, wid: &Uuid) -> DbResult<Option<User>> {
831	let c = self.0.lock().await;
832	c.query_row(
833	&format!("SELECT {USER_COLS} FROM users WHERE webauthn_id = ?1 AND kind = 'person'"),
834	[wid.to_string()],
835	map_user,
836	)
837	.optional()
838	}
839
840	pub async fn user_passkeys(&self, user_id: i64) -> DbResult<Vec<PasskeyRow>> {
841	let c = self.0.lock().await;
842	let mut stmt = c.prepare_cached(
843	"SELECT id, name, created_at, last_used_at, discoverable, passkey
844	FROM passkeys WHERE user_id = ?1 ORDER BY id",
845	)?;
846	let rows = stmt.query_map([user_id], map_passkey)?;
847	rows.collect()
848	}
849
850	pub async fn count_passkeys(&self, user_id: i64) -> DbResult<i64> {
851	let c = self.0.lock().await;
852	c.query_row(
853	"SELECT COUNT(*) FROM passkeys WHERE user_id = ?1",
854	[user_id],
855	\|r\| r.get(0),
856	)
857	}
858
859	/// The per-install secret behind the decoy credentials a named passkey
860	/// challenge is padded with. Created on first use, so no migration.
861	pub async fn decoy_secret(&self) -> DbResult<String> {
862	let c = self.0.lock().await;
863	let existing: Option<String> = c
864	.query_row(
865	"SELECT value FROM meta WHERE key = 'decoy_secret'",
866	[],
867	\|r\| r.get(0),
868	)
869	.optional()?;
870	if let Some(secret) = existing {
871	return Ok(secret);
872	}
873	let fresh = crate::auth::random_token();
874	c.execute(
875	"INSERT OR REPLACE INTO meta (key, value) VALUES ('decoy_secret', ?1)",
876	[&fresh],
877	)?;
878	Ok(fresh)
879	}
880
881	/// The length in bytes of every credential id registered on this server,
882	/// one entry per credential.
883	///
884	/// Decoys draw their length from this list. Authenticators disagree on it —
885	/// a security key emits 64 bytes, a platform key often 16 or 32 — so a
886	/// decoy of one fixed length would stand out next to the real entries.
887	///
888	/// Duplicates are kept deliberately. Drawing from the distinct lengths
889	/// would make a rare length as likely as a common one, and decoys that do
890	/// not match how the install actually looks are the thing worth avoiding.
891	pub async fn cred_id_lengths(&self) -> DbResult<Vec<usize>> {
892	let c = self.0.lock().await;
893	let mut stmt = c.prepare("SELECT length(cred_id) FROM passkeys ORDER BY id")?;
894	let rows = stmt.query_map([], \|r\| r.get::<_, i64>(0))?;
895	rows.map(\|r\| r.map(\|n\| n.max(1) as usize))
896	.collect::<Result<Vec<_>, _>>()
897	}
898
899	/// Store a freshly registered passkey. A duplicate `cred_id` is a unique
900	/// violation, which is the intended answer: the same credential must not
901	/// be registered twice, not even to a second account.
902	///
903	/// `None` means the account is already at [`PASSKEY_LIMIT`]. The count and
904	/// the insert share one transaction, so two registrations landing together
905	/// cannot put the account one over and give its sign-in challenge a
906	/// telltale length.
907	pub async fn add_passkey(
908	&self,
909	user_id: i64,
910	cred_id: &[u8],
911	passkey: &str,
912	name: &str,
913	discoverable: Option<bool>,
914	) -> DbResult<Option<PasskeyRow>> {
915	let mut c = self.0.lock().await;
916	let tx = c.transaction()?;
917	let held: i64 = tx.query_row(
918	"SELECT COUNT(*) FROM passkeys WHERE user_id = ?1",
919	[user_id],
920	\|r\| r.get(0),
921	)?;
922	if held as usize >= PASSKEY_LIMIT {
923	return Ok(None);
924	}
925	tx.execute(
926	"INSERT INTO passkeys (user_id, cred_id, passkey, name, discoverable, created_at)
927	VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
928	params![user_id, cred_id, passkey, name, discoverable, now()],
929	)?;
930	let id = tx.last_insert_rowid();
931	let row = tx.query_row(
932	"SELECT id, name, created_at, last_used_at, discoverable, passkey
933	FROM passkeys WHERE id = ?1",
934	[id],
935	map_passkey,
936	)?;
937	tx.commit()?;
938	Ok(Some(row))
939	}
940
941	pub async fn delete_passkey(&self, id: i64, user_id: i64) -> DbResult<PasskeyDeleted> {
942	let mut c = self.0.lock().await;
943	let tx = c.transaction()?;
944	let hit = tx.execute(
945	"DELETE FROM passkeys WHERE id = ?1 AND user_id = ?2",
946	params![id, user_id],
947	)? > 0;
948	if !hit {
949	tx.rollback()?;
950	return Ok(PasskeyDeleted::NotFound);
951	}
952	Ok(if commit_if_reachable(tx, user_id)? {
953	PasskeyDeleted::Gone
954	} else {
955	PasskeyDeleted::LastCredential
956	})
957	}
958
959	/// Record a successful assertion: the re-serialized credential (its
960	/// signature counter and backup flags may have moved) and the time.
961	pub async fn passkey_used(&self, id: i64, passkey: &str) -> DbResult<()> {
962	let c = self.0.lock().await;
963	c.execute(
964	"UPDATE passkeys SET passkey = ?1, last_used_at = ?2 WHERE id = ?3",
965	params![passkey, now(), id],
966	)?;
967	Ok(())
968	}
969
970	// ---------- app passwords (WebDAV) ----------
971
972	pub async fn app_passwords(&self, user_id: i64) -> DbResult<Vec<AppPasswordInfo>> {
973	let c = self.0.lock().await;
974	let mut stmt = c.prepare_cached(
975	"SELECT id, name, created_at, last_used_at
976	FROM app_passwords WHERE user_id = ?1 ORDER BY id",
977	)?;
978	let rows = stmt.query_map([user_id], map_app_password)?;
979	rows.collect()
980	}
981
982	/// `None` means the account is already at [`APP_PASSWORD_LIMIT`]. Count
983	/// and insert share one transaction, so the cap cannot be raced.
984	pub async fn add_app_password(
985	&self,
986	user_id: i64,
987	name: &str,
988	secret_hash: &str,
989	) -> DbResult<Option<AppPasswordInfo>> {
990	let mut c = self.0.lock().await;
991	let tx = c.transaction()?;
992	let held: i64 = tx.query_row(
993	"SELECT COUNT(*) FROM app_passwords WHERE user_id = ?1",
994	[user_id],
995	\|r\| r.get(0),
996	)?;
997	if held as usize >= APP_PASSWORD_LIMIT {
998	return Ok(None);
999	}
1000	tx.execute(
1001	"INSERT INTO app_passwords (user_id, name, secret_hash, created_at)
1002	VALUES (?1, ?2, ?3, ?4)",
1003	params![user_id, name, secret_hash, now()],
1004	)?;
1005	let row = tx.query_row(
1006	"SELECT id, name, created_at, last_used_at FROM app_passwords WHERE id = ?1",
1007	[tx.last_insert_rowid()],
1008	map_app_password,
1009	)?;
1010	tx.commit()?;
1011	Ok(Some(row))
1012	}
1013
1014	/// `false` means no row matched: an unknown id, or someone else's.
1015	///
1016	/// No reachability check, unlike [`Db::delete_passkey`]: an app password
1017	/// never signs in to the web UI.
1018	pub async fn delete_app_password(&self, id: i64, user_id: i64) -> DbResult<bool> {
1019	let c = self.0.lock().await;
1020	Ok(c.execute(
1021	"DELETE FROM app_passwords WHERE id = ?1 AND user_id = ?2",
1022	params![id, user_id],
1023	)? > 0)
1024	}
1025
1026	/// The account an app password opens. Stamps `last_used_at` on the way.
1027	///
1028	/// Inactive accounts are excluded here, because nothing downstream in the
1029	/// WebDAV path looks at the flag.
1030	pub async fn user_by_app_password(&self, secret_hash: &str) -> DbResult<Option<User>> {
1031	let c = self.0.lock().await;
1032	let user = c
1033	.query_row(
1034	&format!(
1035	"SELECT {USER_COLS_U}
1036	FROM app_passwords a
1037	JOIN users u ON u.id = a.user_id
1038	WHERE a.secret_hash = ?1 AND u.active = 1"
1039	),
1040	[secret_hash],
1041	map_user,
1042	)
1043	.optional()?;
1044	if user.is_some() {
1045	// A mount re-sends its credential on every request. An hour's
1046	// resolution keeps that from writing the stamp on each one.
1047	c.execute(
1048	"UPDATE app_passwords SET last_used_at = ?1
1049	WHERE secret_hash = ?2 AND (last_used_at IS NULL OR last_used_at < ?3)",
1050	params![now(), secret_hash, stamp(chrono::Duration::hours(1))],
1051	)?;
1052	}
1053	Ok(user)
1054	}
1055
1056	/// Delete a user. `false` means no row matched.
1057	/// The stored spelling of the name, which a Basic login may differ from
1058	/// in case.
1059	pub async fn user_name(&self, id: i64) -> DbResult<Option<String>> {
1060	let c = self.0.lock().await;
1061	let mut stmt = c.prepare_cached("SELECT name FROM users WHERE id = ?1")?;
1062	stmt.query_row([id], \|r\| r.get(0)).optional()
1063	}
1064
1065	pub async fn delete_user(&self, id: i64) -> DbResult<bool> {
1066	let c = self.0.lock().await;
1067	Ok(c.execute("DELETE FROM users WHERE id = ?1", [id])? > 0)
1068	}
1069
1070	// ---------- shares ----------
1071
1072	#[allow(clippy::too_many_arguments)] // one row's columns, all required
1073	pub async fn create_share(
1074	&self,
1075	creator_id: i64,
1076	token: &str,
1077	target: &str,
1078	is_file: bool,
1079	mode: Mode,
1080	expires_at: Option<&str>,
1081	password_hash: Option<&str>,
1082	) -> DbResult<ShareRow> {
1083	let c = self.0.lock().await;
1084	c.execute(
1085	"INSERT INTO shares
1086	(token, creator_id, target, is_file, mode, created_at, expires_at, password_hash)
1087	VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
1088	params![
1089	token,
1090	creator_id,
1091	target,
1092	is_file as i64,
1093	mode.as_str(),
1094	now(),
1095	expires_at,
1096	password_hash
1097	],
1098	)?;
1099	let id = c.last_insert_rowid();
1100	Ok(ShareRow {
1101	id,
1102	token: token.to_string(),
1103	creator_id,
1104	target: target.to_string(),
1105	is_file,
1106	mode,
1107	created_at: now(),
1108	expires_at: expires_at.map(\|s\| s.to_string()),
1109	password_hash: password_hash.map(\|s\| s.to_string()),
1110	})
1111	}
1112
1113	/// Record that a visitor entered `share_id`'s password, and return the
1114	/// token that proves it (the value of their unlock cookie).
1115	pub async fn create_share_unlock(&self, share_id: i64) -> DbResult<String> {
1116	let token = crate::auth::random_token();
1117	let c = self.0.lock().await;
1118	c.execute(
1119	"INSERT INTO share_unlocks (token, share_id, created_at) VALUES (?1, ?2, ?3)",
1120	params![token, share_id, now()],
1121	)?;
1122	Ok(token)
1123	}
1124
1125	/// Delete what has outlived its use. Returns how many shares and how
1126	/// many unlock rows went.
1127	///
1128	/// Housekeeping only: every read already refuses an expired share, so
1129	/// nothing here is load-bearing and the interval does not matter.
1130	pub async fn sweep(&self) -> DbResult<(usize, usize)> {
1131	let c = self.0.lock().await;
1132	// SQLite parses the timestamp instead of comparing it as text:
1133	// `expires_at` is stored exactly as the client sent it and may carry
1134	// an offset or fractional seconds. An unparseable one yields NULL and
1135	// so survives, which is what `ShareRow::is_expired` decided too.
1136	let shares = c.execute(
1137	"DELETE FROM shares
1138	WHERE expires_at IS NOT NULL AND julianday(expires_at) <= julianday('now')",
1139	[],
1140	)?;
1141	// The cookie carrying an unlock is a session cookie, so it is already
1142	// gone from every browser. Deleting a share takes its own with it.
1143	let unlocks = c.execute(
1144	"DELETE FROM share_unlocks WHERE created_at < ?1",
1145	[stamp(chrono::Duration::days(UNLOCK_MAX_AGE_DAYS))],
1146	)?;
1147	Ok((shares, unlocks))
1148	}
1149
1150	/// Whether `token` is a live unlock for `share_id`.
1151	///
1152	/// The share id is part of the lookup, so an unlock for one share cannot
1153	/// open another.
1154	pub async fn share_unlock_valid(&self, token: &str, share_id: i64) -> DbResult<bool> {
1155	let c = self.0.lock().await;
1156	let mut stmt =
1157	c.prepare_cached("SELECT 1 FROM share_unlocks WHERE token = ?1 AND share_id = ?2")?;
1158	Ok(stmt
1159	.query_row(params![token, share_id], \|_\| Ok(()))
1160	.optional()?
1161	.is_some())
1162	}
1163
1164	pub async fn share_by_token(&self, token: &str) -> DbResult<Option<ShareRow>> {
1165	let c = self.0.lock().await;
1166	let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at,
1167	password_hash
1168	FROM shares WHERE token = ?1";
1169	let mut stmt = c.prepare_cached(sql)?;
1170	stmt.query_row([token], map_share).optional()
1171	}
1172
1173	pub async fn user_shares(&self, creator_id: i64) -> DbResult<Vec<ShareRow>> {
1174	let c = self.0.lock().await;
1175	let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at,
1176	password_hash
1177	FROM shares WHERE creator_id = ?1 ORDER BY id DESC";
1178	let mut stmt = c.prepare_cached(sql)?;
1179	let rows = stmt.query_map([creator_id], map_share)?;
1180	rows.collect()
1181	}
1182
1183	/// Revoke every share on `target` or on anything beneath it. Returns how
1184	/// many were dropped.
1185	///
1186	/// Called when a path stops meaning what it meant: the item was deleted,
1187	/// renamed, or moved away. A share names a path, and a path is not a
1188	/// stable identity, so leaving the row behind would let a new item that
1189	/// later takes the freed path inherit the old link's audience.
1190	///
1191	/// `substr` rather than `LIKE`: a target containing `%` or `_` would make
1192	/// a `LIKE` pattern over-match and revoke unrelated shares.
1193	pub async fn revoke_shares_at(&self, target: &str) -> DbResult<usize> {
1194	let c = self.0.lock().await;
1195	c.execute(
1196	"DELETE FROM shares
1197	WHERE target = ?1 OR substr(target, 1, length(?1) + 1) = ?1 \|\| '/'",
1198	[target],
1199	)
1200	}
1201
1202	/// Delete one of `creator_id`'s shares. `false` means no row matched.
1203	pub async fn delete_share(&self, id: i64, creator_id: i64) -> DbResult<bool> {
1204	let c = self.0.lock().await;
1205	let n = c.execute(
1206	"DELETE FROM shares WHERE id = ?1 AND creator_id = ?2",
1207	params![id, creator_id],
1208	)?;
1209	Ok(n > 0)
1210	}
1211
1212	/// Every share on the server with its creator. Grouped by account name,
1213	/// newest link within an account first.
1214	///
1215	/// The join cannot miss: `shares.creator_id` cascades on delete, so a share
1216	/// never outlives the account that made it.
1217	pub async fn all_shares_with_creators(&self) -> DbResult<Vec<ShareWithCreator>> {
1218	let c = self.0.lock().await;
1219	// Columns 0..8 are `map_share`'s order, unchanged from `user_shares`.
1220	let sql = "SELECT s.id, s.token, s.creator_id, s.target, s.is_file, s.mode,
1221	s.created_at, s.expires_at, s.password_hash,
1222	u.name, u.active != 0
1223	FROM shares s
1224	JOIN users u ON u.id = s.creator_id
1225	ORDER BY u.name COLLATE NOCASE, s.id DESC";
1226	let mut stmt = c.prepare_cached(sql)?;
1227	let rows = stmt.query_map([], \|r\| {
1228	Ok(ShareWithCreator {
1229	share: map_share(r)?,
1230	creator_name: r.get(9)?,
1231	creator_active: r.get(10)?,
1232	})
1233	})?;
1234	rows.collect()
1235	}
1236
1237	/// Revoke a share whoever created it. The owner-scoped
1238	/// [`Self::delete_share`] is what the user-facing API uses.
1239	pub async fn admin_delete_share(&self, id: i64) -> DbResult<bool> {
1240	let c = self.0.lock().await;
1241	Ok(c.execute("DELETE FROM shares WHERE id = ?1", [id])? > 0)
1242	}
1243
1244	// ---------- settings ----------
1245
1246	/// Folders excluded from search, as paths relative to the server root.
1247	///
1248	/// Stored as one JSON array in a settings row. A table of its own would
1249	/// be overkill for a hand-edited list read once per search.
1250	pub async fn search_excludes(&self) -> DbResult<Vec<String>> {
1251	let raw = self.get_setting("search_excludes").await?;
1252	// Normalised on read as well as on write. A value edited straight
1253	// into the database would otherwise never match: `is_excluded`
1254	// compares against paths with no slash at either end.
1255	let clean = \|v: Vec<String>\| -> Vec<String> {
1256	v.into_iter()
1257	.map(\|p\| p.trim().replace('\\', "/").trim_matches('/').to_string())
1258	.filter(\|p\| !p.is_empty() && p != ".")
1259	.collect()
1260	};
1261	// A hand-edited, unparseable value falls back to no exclusions,
1262	// the same as an absent row.
1263	Ok(raw
1264	.as_deref()
1265	.and_then(\|v\| serde_json::from_str::<Vec<String>>(v).ok())
1266	.map(clean)
1267	.unwrap_or_default())
1268	}
1269
1270	pub async fn set_search_excludes(&self, paths: &[String]) -> DbResult<()> {
1271	let json = serde_json::to_string(paths).unwrap_or_else(\|_\| "[]".to_string());
1272	self.set_setting("search_excludes", &json).await
1273	}
1274
1275	pub async fn get_setting(&self, key: &str) -> DbResult<Option<String>> {
1276	let c = self.0.lock().await;
1277	let mut stmt = c.prepare_cached("SELECT value FROM settings WHERE key = ?1")?;
1278	stmt.query_row([key], \|r\| r.get(0)).optional()
1279	}
1280
1281	pub async fn set_setting(&self, key: &str, value: &str) -> DbResult<()> {
1282	let c = self.0.lock().await;
1283	c.execute(
1284	"INSERT INTO settings (key, value) VALUES (?1, ?2)
1285	ON CONFLICT(key) DO UPDATE SET value = ?2",
1286	params![key, value],
1287	)?;
1288	Ok(())
1289	}
1290
1291	// ---------- CalDAV and CardDAV ----------
1292
1293	/// Gives the user a calendar and an address book when they have none.
1294	/// Scheduling needs a calendar to deliver into, so a user who deletes the
1295	/// last one gets a new one.
1296	pub async fn pim_ensure_defaults(&self, user_id: i64) -> DbResult<()> {
1297	let c = self.0.lock().await;
1298	let mut stmt = c.prepare_cached(
1299	"INSERT INTO pim_collections (user_id, kind, slug, displayname, components, created_at)
1300	SELECT ?1, ?2, 'default', ?3, ?4, ?5
1301	WHERE NOT EXISTS (SELECT 1 FROM pim_collections WHERE user_id = ?1 AND kind = ?2)",
1302	)?;
1303	let now = now();
1304	stmt.execute(params![
1305	user_id,
1306	"cal",
1307	"Calendar",
1308	"VEVENT,VTODO,VJOURNAL",
1309	now
1310	])?;
1311	stmt.execute(params![user_id, "card", "Contacts", "", now])?;
1312	Ok(())
1313	}
1314
1315	pub async fn pim_collections(
1316	&self,
1317	user_id: i64,
1318	kind: PimKind,
1319	) -> DbResult<Vec<PimCollection>> {
1320	let c = self.0.lock().await;
1321	let mut stmt = c.prepare_cached(&format!(
1322	"SELECT {PIM_COLLECTION_COLS} FROM pim_collections
1323	WHERE user_id = ?1 AND kind = ?2 ORDER BY id"
1324	))?;
1325	stmt.query_map(params![user_id, kind.as_str()], map_pim_collection)?
1326	.collect()
1327	}
1328
1329	pub async fn pim_collection(
1330	&self,
1331	user_id: i64,
1332	kind: PimKind,
1333	slug: &str,
1334	) -> DbResult<Option<PimCollection>> {
1335	let c = self.0.lock().await;
1336	let mut stmt = c.prepare_cached(&format!(
1337	"SELECT {PIM_COLLECTION_COLS} FROM pim_collections
1338	WHERE user_id = ?1 AND kind = ?2 AND slug = ?3"
1339	))?;
1340	stmt.query_row(params![user_id, kind.as_str(), slug], map_pim_collection)
1341	.optional()
1342	}
1343
1344	/// `false` if the slug is taken. `id` and `seq` of `new` are ignored.
1345	pub async fn pim_create_collection(
1346	&self,
1347	user_id: i64,
1348	kind: PimKind,
1349	new: &PimCollection,
1350	) -> DbResult<bool> {
1351	let c = self.0.lock().await;
1352	let n = c.execute(
1353	"INSERT OR IGNORE INTO pim_collections (user_id, kind, slug, displayname,
1354	description, color, timezone, sort_order, components, created_at)
1355	VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)",
1356	params![
1357	user_id,
1358	kind.as_str(),
1359	new.slug,
1360	new.displayname,
1361	new.description,
1362	new.color,
1363	new.timezone,
1364	new.sort_order,
1365	new.components,
1366	now()
1367	],
1368	)?;
1369	Ok(n > 0)
1370	}
1371
1372	/// Writes the properties of `col` and counts it as a change.
1373	pub async fn pim_update_collection(&self, col: &PimCollection) -> DbResult<()> {
1374	let c = self.0.lock().await;
1375	c.execute(
1376	"UPDATE pim_collections SET displayname = ?2, description = ?3, color = ?4,
1377	timezone = ?5, sort_order = ?6, seq = seq + 1
1378	WHERE id = ?1",
1379	params![
1380	col.id,
1381	col.displayname,
1382	col.description,
1383	col.color,
1384	col.timezone,
1385	col.sort_order
1386	],
1387	)?;
1388	Ok(())
1389	}
1390
1391	pub async fn pim_delete_collection(&self, id: i64) -> DbResult<()> {
1392	let c = self.0.lock().await;
1393	c.execute("DELETE FROM pim_collections WHERE id = ?1", [id])?;
1394	Ok(())
1395	}
1396
1397	pub async fn pim_objects(&self, collection_id: i64) -> DbResult<Vec<PimObject>> {
1398	let c = self.0.lock().await;
1399	let mut stmt = c.prepare_cached(&format!(
1400	"SELECT {PIM_OBJECT_COLS} FROM pim_objects WHERE collection_id = ?1 ORDER BY name"
1401	))?;
1402	stmt.query_map([collection_id], map_pim_object)?.collect()
1403	}
1404
1405	pub async fn pim_object(
1406	&self,
1407	collection_id: i64,
1408	name: &str,
1409	) -> DbResult<Option<(PimObject, Vec<u8>)>> {
1410	let c = self.0.lock().await;
1411	let mut stmt = c.prepare_cached(&format!(
1412	"SELECT {PIM_OBJECT_COLS}, data FROM pim_objects
1413	WHERE collection_id = ?1 AND name = ?2"
1414	))?;
1415	stmt.query_row(params![collection_id, name], \|r\| {
1416	Ok((map_pim_object(r)?, r.get(6)?))
1417	})
1418	.optional()
1419	}
1420
1421	pub async fn pim_objects_with_data(
1422	&self,
1423	collection_id: i64,
1424	) -> DbResult<Vec<(PimObject, Vec<u8>)>> {
1425	let c = self.0.lock().await;
1426	let mut stmt = c.prepare_cached(&format!(
1427	"SELECT {PIM_OBJECT_COLS}, data FROM pim_objects WHERE collection_id = ?1 ORDER BY name"
1428	))?;
1429	stmt.query_map([collection_id], \|r\| Ok((map_pim_object(r)?, r.get(6)?)))?
1430	.collect()
1431	}
1432
1433	/// `(name, seq, deleted)` of the members changed after `since`, oldest
1434	/// first. Without `since`, the members that exist.
1435	pub async fn pim_changes(
1436	&self,
1437	collection_id: i64,
1438	since: Option<i64>,
1439	) -> DbResult<Vec<(String, i64, bool)>> {
1440	let c = self.0.lock().await;
1441	let mut stmt = c.prepare_cached(
1442	"SELECT name, seq, deleted != 0 FROM pim_changes
1443	WHERE collection_id = ?1 AND (?2 IS NULL AND deleted = 0 OR seq > ?2)
1444	ORDER BY seq",
1445	)?;
1446	stmt.query_map(params![collection_id, since], \|r\| {
1447	Ok((r.get(0)?, r.get(1)?, r.get(2)?))
1448	})?
1449	.collect()
1450	}
1451
1452	/// Moves an object to `to_name` in collection `to`, which may be the
1453	/// same collection. `Updated` means an object at the destination was
1454	/// replaced.
1455	pub async fn pim_move_object(
1456	&self,
1457	from: i64,
1458	name: &str,
1459	to: i64,
1460	to_name: &str,
1461	overwrite: bool,
1462	cond: &Precondition,
1463	) -> DbResult<PimWrite> {
1464	let mut c = self.0.lock().await;
1465	let tx = c.transaction()?;
1466	let source: Option<(String, String)> = tx
1467	.query_row(
1468	"SELECT etag, uid FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
1469	params![from, name],
1470	\|r\| Ok((r.get(0)?, r.get(1)?)),
1471	)
1472	.optional()?;
1473	let Some((etag, uid)) = source else {
1474	return Ok(PimWrite::NotFound);
1475	};
1476	if !cond.allows(Some(&etag)) {
1477	return Ok(PimWrite::PreconditionFailed);
1478	}
1479	let taken: bool = tx.query_row(
1480	"SELECT EXISTS (SELECT 1 FROM pim_objects WHERE collection_id = ?1 AND name = ?2)",
1481	params![to, to_name],
1482	\|r\| r.get(0),
1483	)?;
1484	if taken && !overwrite {
1485	return Ok(PimWrite::PreconditionFailed);
1486	}
1487	let holder: Option<String> = tx
1488	.query_row(
1489	"SELECT name FROM pim_objects WHERE collection_id = ?1 AND uid = ?2
1490	AND name != ?3 AND NOT (collection_id = ?4 AND name = ?5)",
1491	params![to, uid, to_name, from, name],
1492	\|r\| r.get(0),
1493	)
1494	.optional()?;
1495	if let Some(holder) = holder {
1496	return Ok(PimWrite::UidConflict(holder));
1497	}
1498	tx.execute(
1499	"DELETE FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
1500	params![to, to_name],
1501	)?;
1502	tx.execute(
1503	"UPDATE pim_objects SET collection_id = ?3, name = ?4, modified_at = ?5
1504	WHERE collection_id = ?1 AND name = ?2",
1505	params![from, name, to, to_name, now()],
1506	)?;
1507	record_pim_change(&tx, from, name, true)?;
1508	record_pim_change(&tx, to, to_name, false)?;
1509	tx.commit()?;
1510	Ok(if taken {
1511	PimWrite::Updated
1512	} else {
1513	PimWrite::Created
1514	})
1515	}
1516
1517	/// Stores an object under `obj.name`. The precondition and the UID check
1518	/// run in the same transaction as the write.
1519	pub async fn pim_put_object(
1520	&self,
1521	collection_id: i64,
1522	obj: &PimObject,
1523	data: &[u8],
1524	cond: &Precondition,
1525	) -> DbResult<PimWrite> {
1526	let mut c = self.0.lock().await;
1527	let tx = c.transaction()?;
1528	let current: Option<String> = tx
1529	.query_row(
1530	"SELECT etag FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
1531	params![collection_id, obj.name],
1532	\|r\| r.get(0),
1533	)
1534	.optional()?;
1535	if !cond.allows(current.as_deref()) {
1536	return Ok(PimWrite::PreconditionFailed);
1537	}
1538	let holder: Option<String> = tx
1539	.query_row(
1540	"SELECT name FROM pim_objects
1541	WHERE collection_id = ?1 AND uid = ?2 AND name != ?3",
1542	params![collection_id, obj.uid, obj.name],
1543	\|r\| r.get(0),
1544	)
1545	.optional()?;
1546	if let Some(holder) = holder {
1547	return Ok(PimWrite::UidConflict(holder));
1548	}
1549	tx.execute(
1550	"INSERT INTO pim_objects (collection_id, name, uid, component, data, etag, modified_at)
1551	VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
1552	ON CONFLICT (collection_id, name) DO UPDATE SET uid = ?3, component = ?4,
1553	data = ?5, etag = ?6, modified_at = ?7",
1554	params![
1555	collection_id,
1556	obj.name,
1557	obj.uid,
1558	obj.component,
1559	data,
1560	obj.etag,
1561	now()
1562	],
1563	)?;
1564	record_pim_change(&tx, collection_id, &obj.name, false)?;
1565	tx.commit()?;
1566	Ok(match current {
1567	Some(_) => PimWrite::Updated,
1568	None => PimWrite::Created,
1569	})
1570	}
1571
1572	pub async fn pim_delete_object(
1573	&self,
1574	collection_id: i64,
1575	name: &str,
1576	cond: &Precondition,
1577	) -> DbResult<PimWrite> {
1578	let mut c = self.0.lock().await;
1579	let tx = c.transaction()?;
1580	let current: Option<String> = tx
1581	.query_row(
1582	"SELECT etag FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
1583	params![collection_id, name],
1584	\|r\| r.get(0),
1585	)
1586	.optional()?;
1587	if current.is_none() {
1588	return Ok(PimWrite::NotFound);
1589	}
1590	if !cond.allows(current.as_deref()) {
1591	return Ok(PimWrite::PreconditionFailed);
1592	}
1593	tx.execute(
1594	"DELETE FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
1595	params![collection_id, name],
1596	)?;
1597	record_pim_change(&tx, collection_id, name, true)?;
1598	tx.commit()?;
1599	Ok(PimWrite::Deleted)
1600	}
1601
1602	// ---------- principals, sharing, rooms ----------
1603
1604	/// An account, room or resource by URL name. Disabled accounts are
1605	/// invisible.
1606	pub async fn pim_principal(&self, name: &str) -> DbResult<Option<PimPrincipal>> {
1607	let c = self.0.lock().await;
1608	let mut stmt = c.prepare_cached(&format!(
1609	"SELECT {PRINCIPAL_COLS} FROM users WHERE name = ?1 AND {VISIBLE}"
1610	))?;
1611	stmt.query_row([name], map_principal).optional()
1612	}
1613
1614	pub async fn pim_principals(&self) -> DbResult<Vec<PimPrincipal>> {
1615	let c = self.0.lock().await;
1616	let mut stmt = c.prepare_cached(&format!(
1617	"SELECT {PRINCIPAL_COLS} FROM users WHERE {VISIBLE} ORDER BY id"
1618	))?;
1619	stmt.query_map([], map_principal)?.collect()
1620	}
1621
1622	/// The collection `collection_id` as lent to `user_id`, with its owner's
1623	/// name and the mode.
1624	pub async fn pim_shared_collection(
1625	&self,
1626	user_id: i64,
1627	kind: PimKind,
1628	collection_id: i64,
1629	) -> DbResult<Option<(PimCollection, String, Mode)>> {
1630	let c = self.0.lock().await;
1631	let mut stmt = c.prepare_cached(&format!(
1632	"SELECT {PIM_COLLECTION_COLS_C}, u.name, s.mode
1633	FROM pim_shares s
1634	JOIN pim_collections c ON c.id = s.collection_id
1635	JOIN users u ON u.id = c.user_id
1636	WHERE s.user_id = ?1 AND c.kind = ?2 AND c.id = ?3"
1637	))?;
1638	stmt.query_row(params![user_id, kind.as_str(), collection_id], map_shared)
1639	.optional()
1640	}
1641
1642	/// Every collection of `kind` lent to `user_id`.
1643	pub async fn pim_shared_collections(
1644	&self,
1645	user_id: i64,
1646	kind: PimKind,
1647	) -> DbResult<Vec<(PimCollection, String, Mode)>> {
1648	let c = self.0.lock().await;
1649	let mut stmt = c.prepare_cached(&format!(
1650	"SELECT {PIM_COLLECTION_COLS_C}, u.name, s.mode
1651	FROM pim_shares s
1652	JOIN pim_collections c ON c.id = s.collection_id
1653	JOIN users u ON u.id = c.user_id
1654	WHERE s.user_id = ?1 AND c.kind = ?2 ORDER BY c.id"
1655	))?;
1656	stmt.query_map(params![user_id, kind.as_str()], map_shared)?
1657	.collect()
1658	}
1659
1660	/// The owner and kind of a collection.
1661	pub async fn pim_collection_by_id(
1662	&self,
1663	id: i64,
1664	) -> DbResult<Option<(i64, PimKind, PimCollection)>> {
1665	let c = self.0.lock().await;
1666	let mut stmt = c.prepare_cached(&format!(
1667	"SELECT {PIM_COLLECTION_COLS}, user_id, kind FROM pim_collections WHERE id = ?1"
1668	))?;
1669	stmt.query_row([id], \|r\| {
1670	Ok((
1671	r.get(9)?,
1672	PimKind::parse(&r.get::<_, String>(10)?),
1673	map_pim_collection(r)?,
1674	))
1675	})
1676	.optional()
1677	}
1678
1679	/// `(user id, name, mode)` of everyone a collection is lent to.
1680	pub async fn pim_shares(&self, collection_id: i64) -> DbResult<Vec<(i64, String, Mode)>> {
1681	let c = self.0.lock().await;
1682	let mut stmt = c.prepare_cached(
1683	"SELECT u.id, u.name, s.mode FROM pim_shares s JOIN users u ON u.id = s.user_id
1684	WHERE s.collection_id = ?1 ORDER BY u.name",
1685	)?;
1686	stmt.query_map([collection_id], \|r\| {
1687	Ok((r.get(0)?, r.get(1)?, r.get::<_, SqlMode>(2)?.0))
1688	})?
1689	.collect()
1690	}
1691
1692	/// Lends a collection, or changes the mode of an existing loan.
1693	pub async fn pim_set_share(
1694	&self,
1695	collection_id: i64,
1696	user_id: i64,
1697	mode: Mode,
1698	) -> DbResult<()> {
1699	let c = self.0.lock().await;
1700	c.execute(
1701	"INSERT INTO pim_shares (collection_id, user_id, mode) VALUES (?1, ?2, ?3)
1702	ON CONFLICT (collection_id, user_id) DO UPDATE SET mode = ?3",
1703	params![collection_id, user_id, mode.as_str()],
1704	)?;
1705	Ok(())
1706	}
1707
1708	pub async fn pim_remove_share(&self, collection_id: i64, user_id: i64) -> DbResult<bool> {
1709	let c = self.0.lock().await;
1710	Ok(c.execute(
1711	"DELETE FROM pim_shares WHERE collection_id = ?1 AND user_id = ?2",
1712	params![collection_id, user_id],
1713	)? > 0)
1714	}
1715
1716	pub async fn rooms(&self) -> DbResult<Vec<PimPrincipal>> {
1717	let c = self.0.lock().await;
1718	let mut stmt = c.prepare_cached(&format!(
1719	"SELECT {PRINCIPAL_COLS} FROM users WHERE kind != 'person' ORDER BY name"
1720	))?;
1721	stmt.query_map([], map_principal)?.collect()
1722	}
1723
1724	/// A room or resource with its booking calendar. `None` if the name is
1725	/// taken by any principal.
1726	pub async fn create_room(
1727	&self,
1728	name: &str,
1729	display_name: &str,
1730	kind: UserType,
1731	) -> DbResult<Option<PimPrincipal>> {
1732	let mut c = self.0.lock().await;
1733	let tx = c.transaction()?;
1734	// No password and never active: no sign-in path accepts it.
1735	let inserted = tx.execute(
1736	"INSERT INTO users (name, pass_hash, is_admin, active, created_at, kind, display_name)
1737	SELECT ?1, '', 0, 0, ?2, ?3, ?4
1738	WHERE NOT EXISTS (SELECT 1 FROM users WHERE name = ?1)",
1739	params![name, now(), kind_str(kind), display_name],
1740	)?;
1741	if inserted == 0 {
1742	return Ok(None);
1743	}
1744	let id = tx.last_insert_rowid();
1745	tx.execute(
1746	"INSERT INTO pim_collections (user_id, kind, slug, displayname, components, created_at)
1747	VALUES (?1, 'cal', 'default', ?2, 'VEVENT', ?3)",
1748	params![id, display_name, now()],
1749	)?;
1750	tx.commit()?;
1751	Ok(Some(PimPrincipal {
1752	id,
1753	name: name.to_string(),
1754	display_name: Some(display_name.to_string()),
1755	kind,
1756	}))
1757	}
1758
1759	pub async fn set_room_display_name(&self, id: i64, display_name: &str) -> DbResult<bool> {
1760	let c = self.0.lock().await;
1761	Ok(c.execute(
1762	"UPDATE users SET display_name = ?2 WHERE id = ?1 AND kind != 'person'",
1763	params![id, display_name],
1764	)? > 0)
1765	}
1766
1767	pub async fn delete_room(&self, id: i64) -> DbResult<bool> {
1768	let c = self.0.lock().await;
1769	Ok(c.execute("DELETE FROM users WHERE id = ?1 AND kind != 'person'", [id])? > 0)
1770	}
1771
1772	/// Whether users may create writable (read-write) shares. Off by default;
1773	/// the admin setting gates it.
1774	pub async fn allow_writable_shares(&self) -> DbResult<bool> {
1775	Ok(self.get_setting("allow_writable_shares").await?.as_deref() == Some("1"))
1776	}
1777
1778	pub async fn set_allow_writable_shares(&self, v: bool) -> DbResult<()> {
1779	self.set_setting("allow_writable_shares", if v { "1" } else { "0" })
1780	.await
1781	}
1782	}
1783
1784	/// Write a new password hash and drop every session that was opened with the
1785	/// old one.
1786	///
1787	/// The two belong together: a password is changed because the old one is
1788	/// suspect (an admin resetting a compromised account), and a session that
1789	/// survives the reset leaves whoever holds it signed in. Takes the
1790	/// transaction so the caller can bundle it with its other edits.
1791	fn set_password(tx: &rusqlite::Transaction<'_>, id: i64, pass_hash: &str) -> DbResult<()> {
1792	tx.execute(
1793	"UPDATE users SET pass_hash = ?1 WHERE id = ?2",
1794	params![pass_hash, id],
1795	)?;
1796	tx.execute("DELETE FROM sessions WHERE user_id = ?1", [id])?;
1797	Ok(())
1798	}
1799
1800	/// Commit a credential change, or undo it if the account would be left with
1801	/// no way to sign in.
1802	///
1803	/// The handlers check the same rules first, so the person gets a message that
1804	/// says which rule. This is the guarantee behind those checks: a count read
1805	/// before the write can already be stale when the write lands, and two
1806	/// requests racing could otherwise take away the last credential between them.
1807	/// `false` means the change was rolled back.
1808	fn commit_if_reachable(tx: rusqlite::Transaction<'_>, user_id: i64) -> DbResult<bool> {
1809	let (has_password, mode) = tx.query_row(
1810	"SELECT pass_hash != '', auth_mode FROM users WHERE id = ?1",
1811	[user_id],
1812	\|r\| Ok((r.get::<_, bool>(0)?, r.get::<_, SqlAuthMode>(1)?)),
1813	)?;
1814	let passkeys: i64 = tx.query_row(
1815	"SELECT COUNT(*) FROM passkeys WHERE user_id = ?1",
1816	[user_id],
1817	\|r\| r.get(0),
1818	)?;
1819	let reachable = match mode.0 {
1820	AuthMode::Either => has_password \|\| passkeys > 0,
1821	AuthMode::Both => has_password && passkeys > 0,
1822	};
1823	if !reachable {
1824	tx.rollback()?;
1825	return Ok(false);
1826	}
1827	tx.commit()?;
1828	Ok(true)
1829	}
1830
1831	/// Column order matched by [`USER_COLS`] and [`USER_COLS_U`].
1832	fn map_user(r: &rusqlite::Row) -> DbResult<User> {
1833	Ok(User {
1834	id: r.get(0)?,
1835	name: r.get(1)?,
1836	is_admin: r.get(2)?,
1837	active: r.get(3)?,
1838	single_click: r.get(4)?,
1839	thumbnails: r.get(5)?,
1840	language: r.get(6)?,
1841	default_root_id: r.get(7)?,
1842	auth_mode: r.get::<_, SqlAuthMode>(8)?.0,
1843	has_password: r.get(9)?,
1844	})
1845	}
1846
1847	/// Column order matched by the `passkeys` SELECTs above.
1848	fn map_passkey(r: &rusqlite::Row) -> DbResult<PasskeyRow> {
1849	Ok(PasskeyRow {
1850	id: r.get(0)?,
1851	name: r.get(1)?,
1852	created_at: r.get(2)?,
1853	last_used_at: r.get(3)?,
1854	discoverable: r.get(4)?,
1855	passkey: r.get(5)?,
1856	})
1857	}
1858
1859	/// A just-created account: the column defaults of `users`.
1860	fn new_user(id: i64, name: &str, is_admin: bool) -> User {
1861	User {
1862	id,
1863	name: name.to_string(),
1864	is_admin,
1865	active: true,
1866	single_click: false,
1867	thumbnails: true,
1868	language: None,
1869	default_root_id: None,
1870	auth_mode: AuthMode::Either,
1871	has_password: true,
1872	}
1873	}
1874
1875	/// Column order matched by the `app_passwords` SELECTs above.
1876	fn map_app_password(r: &rusqlite::Row) -> DbResult<AppPasswordInfo> {
1877	Ok(AppPasswordInfo {
1878	id: r.get(0)?,
1879	name: r.get(1)?,
1880	created_at: r.get(2)?,
1881	last_used_at: r.get(3)?,
1882	})
1883	}
1884
1885	/// Bumps the collection's `seq` and records it as the latest change of `name`.
1886	fn record_pim_change(
1887	tx: &rusqlite::Transaction,
1888	collection_id: i64,
1889	name: &str,
1890	deleted: bool,
1891	) -> DbResult<()> {
1892	let seq: i64 = tx.query_row(
1893	"UPDATE pim_collections SET seq = seq + 1 WHERE id = ?1 RETURNING seq",
1894	[collection_id],
1895	\|r\| r.get(0),
1896	)?;
1897	tx.execute(
1898	"INSERT INTO pim_changes (collection_id, name, seq, deleted) VALUES (?1, ?2, ?3, ?4)
1899	ON CONFLICT (collection_id, name) DO UPDATE SET seq = ?3, deleted = ?4",
1900	params![collection_id, name, seq, deleted],
1901	)?;
1902	Ok(())
1903	}
1904
1905	/// A signed-in-capable account or a room, as CalDAV sees it.
1906	#[derive(Debug, Clone)]
1907	pub struct PimPrincipal {
1908	pub id: i64,
1909	/// The URL segment.
1910	pub name: String,
1911	pub display_name: Option<String>,
1912	pub kind: UserType,
1913	}
1914
1915	impl PimPrincipal {
1916	pub fn display(&self) -> &str {
1917	self.display_name.as_deref().unwrap_or(&self.name)
1918	}
1919	}
1920
1921	const PRINCIPAL_COLS: &str = "id, name, display_name, kind";
1922	/// Rooms are never active; disabled accounts are hidden.
1923	const VISIBLE: &str = "(kind != 'person' OR active = 1)";
1924
1925	fn map_principal(r: &rusqlite::Row) -> DbResult<PimPrincipal> {
1926	Ok(PimPrincipal {
1927	id: r.get(0)?,
1928	name: r.get(1)?,
1929	display_name: r.get(2)?,
1930	kind: match r.get::<_, String>(3)?.as_str() {
1931	"room" => UserType::Room,
1932	"resource" => UserType::Resource,
1933	_ => UserType::Individual,
1934	},
1935	})
1936	}
1937
1938	fn kind_str(kind: UserType) -> &'static str {
1939	match kind {
1940	UserType::Individual => "person",
1941	UserType::Room => "room",
1942	UserType::Resource => "resource",
1943	}
1944	}
1945
1946	fn map_shared(r: &rusqlite::Row) -> DbResult<(PimCollection, String, Mode)> {
1947	Ok((
1948	map_pim_collection(r)?,
1949	r.get(9)?,
1950	r.get::<_, SqlMode>(10)?.0,
1951	))
1952	}
1953
1954	const PIM_COLLECTION_COLS_C: &str = "c.id, c.slug, c.displayname, c.description, c.color,
1955	c.timezone, c.sort_order, c.components, c.seq";
1956	const PIM_COLLECTION_COLS: &str = "id, slug, displayname, description, color, timezone,
1957	sort_order, components, seq";
1958
1959	fn map_pim_collection(r: &rusqlite::Row) -> DbResult<PimCollection> {
1960	Ok(PimCollection {
1961	id: r.get(0)?,
1962	slug: r.get(1)?,
1963	displayname: r.get(2)?,
1964	description: r.get(3)?,
1965	color: r.get(4)?,
1966	timezone: r.get(5)?,
1967	sort_order: r.get(6)?,
1968	components: r.get(7)?,
1969	seq: r.get(8)?,
1970	})
1971	}
1972
1973	const PIM_OBJECT_COLS: &str = "name, uid, component, etag, length(data), modified_at";
1974
1975	fn map_pim_object(r: &rusqlite::Row) -> DbResult<PimObject> {
1976	Ok(PimObject {
1977	name: r.get(0)?,
1978	uid: r.get(1)?,
1979	component: r.get(2)?,
1980	etag: r.get(3)?,
1981	size: r.get(4)?,
1982	modified_at: r.get(5)?,
1983	})
1984	}
1985
1986	/// Column order matched by the two `shares` SELECTs above.
1987	fn map_share(r: &rusqlite::Row) -> DbResult<ShareRow> {
1988	Ok(ShareRow {
1989	id: r.get(0)?,
1990	token: r.get(1)?,
1991	creator_id: r.get(2)?,
1992	target: r.get(3)?,
1993	is_file: r.get::<_, i64>(4)? != 0,
1994	mode: r.get::<_, SqlMode>(5)?.0,
1995	created_at: r.get(6)?,
1996	expires_at: r.get(7)?,
1997	password_hash: r.get(8)?,
1998	})
1999	}
2000
2001	/// A hash of a random string nobody knows. Verified against when the login
2002	/// name does not exist, so both paths cost one Argon2 run.
2003	static DUMMY_HASH: std::sync::LazyLock<String> = std::sync::LazyLock::new(\|\| {
2004	crate::auth::hash_password(&crate::auth::random_token()).expect("argon2 hash")
2005	});
2006
2007	/// How long an unlock row outlives its cookie. The cookie dies with the
2008	/// browser, so this only bounds the rows left behind by closed sessions.
2009	const UNLOCK_MAX_AGE_DAYS: i64 = 7;
2010
2011	/// How often [`Db::sweep`] runs. A share the picker can only set to the
2012	/// minute is gone from the owner's list about when it says it is.
2013	const SWEEP_EVERY: std::time::Duration = std::time::Duration::from_secs(60);
2014
2015	/// Run [`Db::sweep`] until the process ends. A failed pass is logged and
2016	/// retried on the next one: nothing downstream depends on it having run.
2017	pub async fn sweep_forever(db: Db) {
2018	loop {
2019	tokio::time::sleep(SWEEP_EVERY).await;
2020	match db.sweep().await {
2021	Ok((0, 0)) => {}
2022	Ok((shares, unlocks)) => tracing::debug!(shares, unlocks, "swept expired shares"),
2023	Err(e) => tracing::warn!(error = %e, "the share sweep failed"),
2024	}
2025	}
2026	}
2027
2028	/// The time `ago` in the past as a stored stamp. The format sorts
2029	/// lexicographically, so SQL can compare stamps as text.
2030	fn stamp(ago: chrono::Duration) -> String {
2031	(chrono::Utc::now() - ago).to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
2032	}
2033
2034	fn now() -> String {
2035	stamp(chrono::Duration::zero())
2036	}
2037
2038	const SCHEMA_V1: &str = r#"
2039	CREATE TABLE IF NOT EXISTS users (
2040	id INTEGER PRIMARY KEY AUTOINCREMENT,
2041	name TEXT NOT NULL UNIQUE COLLATE NOCASE,
2042	pass_hash TEXT NOT NULL,
2043	is_admin INTEGER NOT NULL DEFAULT 0,
2044	created_at TEXT NOT NULL
2045	);
2046
2047	CREATE TABLE IF NOT EXISTS user_roots (
2048	id INTEGER PRIMARY KEY AUTOINCREMENT,
2049	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
2050	path TEXT NOT NULL,
2051	mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
2052	UNIQUE (user_id, path)
2053	);
2054
2055	CREATE TABLE IF NOT EXISTS shares (
2056	id INTEGER PRIMARY KEY AUTOINCREMENT,
2057	token TEXT NOT NULL UNIQUE,
2058	creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
2059	target TEXT NOT NULL,
2060	is_file INTEGER NOT NULL,
2061	mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
2062	created_at TEXT NOT NULL,
2063	expires_at TEXT
2064	);
2065
2066	CREATE TABLE IF NOT EXISTS sessions (
2067	token TEXT PRIMARY KEY,
2068	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
2069	created_at TEXT NOT NULL,
2070	last_seen_at TEXT NOT NULL
2071	);
2072
2073	CREATE TABLE IF NOT EXISTS settings (
2074	key TEXT PRIMARY KEY,
2075	value TEXT NOT NULL
2076	);
2077	INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
2078	"#;
2079
2080	#[cfg(test)]
2081	mod tests {
2082	use super::*;
2083
2084	// Most tests use an in-memory DB (the file-based path is still covered
2085	// by `v1_db_migrates_to_v2` and the integration harness' `Db::open`).
2086	async fn mem() -> Db {
2087	Db::open(Path::new(":memory:")).await.unwrap()
2088	}
2089
2090	/// `update_user` is the only way production edits these fields, so the
2091	/// tests exercise that path rather than per-field helpers.
2092	async fn edit(db: &Db, id: i64, pass: Option<&str>, admin: Option<bool>, active: Option<bool>) {
2093	db.update_user(id, pass, admin, active, None).await.unwrap();
2094	}
2095
2096	/// Backdate a stamp, which production code has no reason to do.
2097	async fn set_last_used(db: &Db, secret_hash: &str, at: &str) {
2098	db.0.lock()
2099	.await
2100	.execute(
2101	"UPDATE app_passwords SET last_used_at = ?1 WHERE secret_hash = ?2",
2102	params![at, secret_hash],
2103	)
2104	.unwrap();
2105	}
2106
2107	async fn db_with_admin() -> (Db, User) {
2108	let db = mem().await;
2109	let hash = crate::auth::hash_password("admin1234").unwrap();
2110	let admin = db.create_admin("admin", &hash).await.unwrap().unwrap();
2111	(db, admin)
2112	}
2113
2114	#[tokio::test]
2115	async fn fresh_db_state() {
2116	let db = mem().await;
2117	assert_eq!(db.user_count().await.unwrap(), 0);
2118	assert_eq!(db.count_admins().await.unwrap(), 0);
2119	assert!(!db.allow_writable_shares().await.unwrap());
2120	assert!(db.find_user_by_name("nobody").await.unwrap().is_none());
2121	assert!(db.find_user_by_id(1).await.unwrap().is_none());
2122	assert!(db.all_users_with_roots().await.unwrap().is_empty());
2123	}
2124
2125	#[tokio::test]
2126	async fn app_passwords_open_one_account_and_stamp_their_use() {
2127	let (db, admin) = db_with_admin().await;
2128	let secret = crate::auth::short_token();
2129	let hash = crate::auth::app_password_hash(&secret);
2130	let row = db
2131	.add_app_password(admin.id, "laptop", &hash)
2132	.await
2133	.unwrap()
2134	.unwrap();
2135	assert_eq!(row.name, "laptop");
2136	assert!(row.last_used_at.is_none());
2137
2138	let hit = db.user_by_app_password(&hash).await.unwrap().unwrap();
2139	assert_eq!(hit.id, admin.id);
2140	let listed = \|db: &Db\| {
2141	let db = db.clone();
2142	async move {
2143	db.app_passwords(admin.id).await.unwrap()[0]
2144	.last_used_at
2145	.clone()
2146	}
2147	};
2148	assert!(listed(&db).await.is_some(), "first use must stamp the row");
2149
2150	// Ten minutes back is inside the hour window, and far enough from
2151	// `now()` that a rewrite would show at second resolution.
2152	let inside = stamp(chrono::Duration::minutes(10));
2153	set_last_used(&db, &hash, &inside).await;
2154	db.user_by_app_password(&hash).await.unwrap().unwrap();
2155	assert_eq!(
2156	listed(&db).await.as_deref(),
2157	Some(inside.as_str()),
2158	"a second use inside the hour wrote the stamp again"
2159	);
2160
2161	let outside = stamp(chrono::Duration::hours(2));
2162	set_last_used(&db, &hash, &outside).await;
2163	db.user_by_app_password(&hash).await.unwrap().unwrap();
2164	assert_ne!(
2165	listed(&db).await.as_deref(),
2166	Some(outside.as_str()),
2167	"a use after the window left the stamp stale"
2168	);
2169
2170	// The raw secret is not the key, and an inactive account does not match.
2171	let other = crate::auth::app_password_hash(&crate::auth::short_token());
2172	assert!(db.user_by_app_password(&other).await.unwrap().is_none());
2173	assert!(db.user_by_app_password(&secret).await.unwrap().is_none());
2174	edit(&db, admin.id, None, None, Some(false)).await;
2175	assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
2176	edit(&db, admin.id, None, None, Some(true)).await;
2177	assert!(db.user_by_app_password(&hash).await.unwrap().is_some());
2178
2179	for i in 1..APP_PASSWORD_LIMIT {
2180	let h = crate::auth::app_password_hash(&crate::auth::short_token());
2181	assert!(
2182	db.add_app_password(admin.id, &format!("c{i}"), &h)
2183	.await
2184	.unwrap()
2185	.is_some()
2186	);
2187	}
2188	let h = crate::auth::app_password_hash(&crate::auth::short_token());
2189	assert!(
2190	db.add_app_password(admin.id, "one-too-many", &h)
2191	.await
2192	.unwrap()
2193	.is_none()
2194	);
2195
2196	assert!(db.delete_app_password(row.id, admin.id).await.unwrap());
2197	assert!(!db.delete_app_password(row.id, admin.id).await.unwrap());
2198	assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
2199	}
2200
2201	#[tokio::test]
2202	async fn an_admin_password_reset_revokes_the_app_passwords() {
2203	let (db, admin) = db_with_admin().await;
2204	let hash = crate::auth::app_password_hash(&crate::auth::short_token());
2205	db.add_app_password(admin.id, "mount", &hash)
2206	.await
2207	.unwrap()
2208	.unwrap();
2209
2210	// An edit that sets no password leaves them alone.
2211	edit(&db, admin.id, None, None, Some(true)).await;
2212	assert_eq!(db.app_passwords(admin.id).await.unwrap().len(), 1);
2213
2214	// A reset is meant to revoke every way in that existed before it.
2215	let fresh = crate::auth::hash_password("rescued12").unwrap();
2216	edit(&db, admin.id, Some(&fresh), None, None).await;
2217	assert!(db.app_passwords(admin.id).await.unwrap().is_empty());
2218	assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
2219	}
2220
2221	#[tokio::test]
2222	async fn v1_db_migrates_to_v2() {
2223	let dir = tempfile::tempdir().unwrap();
2224	let path = dir.path().join("legacy.sqlite");
2225	{
2226	let conn = rusqlite::Connection::open(&path).unwrap();
2227	conn.execute_batch(SCHEMA_V1).unwrap();
2228	conn.execute(
2229	"INSERT INTO users (name, pass_hash, is_admin, created_at)
2230	VALUES ('legacy', 'hash', 1, '2024-01-01T00:00:00Z')",
2231	[],
2232	)
2233	.unwrap();
2234	conn.execute(
2235	"INSERT INTO user_roots (user_id, path, mode) VALUES (1, 'docs', 'rw')",
2236	[],
2237	)
2238	.unwrap();
2239	}
2240	let db = Db::open(&path).await.unwrap();
2241	assert_eq!(db.user_count().await.unwrap(), 1);
2242	let u = db.find_user_by_name("legacy").await.unwrap().unwrap();
2243	assert!(u.active, "v2 migration must default active to true");
2244	assert!(u.is_admin);
2245	assert_eq!(db.user_roots(u.id).await.unwrap().len(), 1);
2246	// Migrations are idempotent.
2247	let db2 = Db::open(&path).await.unwrap();
2248	assert_eq!(db2.user_count().await.unwrap(), 1);
2249	assert!(
2250	db2.find_user_by_name("legacy")
2251	.await
2252	.unwrap()
2253	.unwrap()
2254	.active
2255	);
2256	}
2257
2258	#[tokio::test]
2259	async fn admin_user_and_passwords() {
2260	let (db, admin) = db_with_admin().await;
2261	assert!(admin.is_admin);
2262	assert!(admin.active);
2263	// Root "." rw is assigned by create_admin.
2264	let roots = db.user_roots(admin.id).await.unwrap();
2265	assert_eq!(roots.len(), 1);
2266	assert_eq!(roots[0].path, ".");
2267	assert_eq!(roots[0].mode, Mode::Rw);
2268
2269	assert!(
2270	db.verify_password("admin", "admin1234")
2271	.await
2272	.unwrap()
2273	.is_some()
2274	);
2275	assert!(db.verify_password("admin", "nope").await.unwrap().is_none());
2276	// Name lookup is case-insensitive (COLLATE NOCASE).
2277	assert!(
2278	db.verify_password("ADMIN", "admin1234")
2279	.await
2280	.unwrap()
2281	.is_some()
2282	);
2283	// Disabled users cannot verify.
2284	edit(&db, admin.id, None, None, Some(false)).await;
2285	assert!(
2286	db.verify_password("admin", "admin1234")
2287	.await
2288	.unwrap()
2289	.is_none()
2290	);
2291	edit(&db, admin.id, None, None, Some(true)).await;
2292	assert!(
2293	db.verify_password("admin", "admin1234")
2294	.await
2295	.unwrap()
2296	.is_some()
2297	);
2298	}
2299
2300	#[tokio::test]
2301	async fn setup_is_won_by_exactly_one_caller() {
2302	let db = mem().await;
2303	let hash = crate::auth::hash_password("admin1234").unwrap();
2304	assert!(db.create_admin("first", &hash).await.unwrap().is_some());
2305	// The guard lives in the insert, so a different name loses too.
2306	assert!(db.create_admin("second", &hash).await.unwrap().is_none());
2307	assert_eq!(db.user_count().await.unwrap(), 1);
2308	// The loser rolled back cleanly: no orphaned root row.
2309	let first = db.find_user_by_name("first").await.unwrap().unwrap();
2310	assert_eq!(db.user_roots(first.id).await.unwrap().len(), 1);
2311	}
2312
2313	#[tokio::test]
2314	async fn changing_a_password_drops_that_users_sessions() {
2315	let (db, admin) = db_with_admin().await;
2316	let h = crate::auth::hash_password("bobpass1").unwrap();
2317	let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
2318	db.create_session(admin.id, "admin-tok").await.unwrap();
2319	db.create_session(bob.id, "bob-tok-1").await.unwrap();
2320	db.create_session(bob.id, "bob-tok-2").await.unwrap();
2321
2322	let new_h = crate::auth::hash_password("bobpass2").unwrap();
2323	edit(&db, bob.id, Some(&new_h), None, None).await;
2324	assert!(
2325	db.session_user_with_roots("bob-tok-1")
2326	.await
2327	.unwrap()
2328	.is_none()
2329	);
2330	assert!(
2331	db.session_user_with_roots("bob-tok-2")
2332	.await
2333	.unwrap()
2334	.is_none()
2335	);
2336	// Only the reset user is signed out.
2337	assert!(
2338	db.session_user_with_roots("admin-tok")
2339	.await
2340	.unwrap()
2341	.is_some()
2342	);
2343
2344	// The admin-edit path bundles the same rule into its transaction.
2345	db.create_session(bob.id, "bob-tok-3").await.unwrap();
2346	let h3 = crate::auth::hash_password("bobpass3").unwrap();
2347	db.update_user(bob.id, Some(&h3), None, None, None)
2348	.await
2349	.unwrap();
2350	assert!(
2351	db.session_user_with_roots("bob-tok-3")
2352	.await
2353	.unwrap()
2354	.is_none()
2355	);
2356	// An edit that leaves the password alone keeps the session.
2357	db.create_session(bob.id, "bob-tok-4").await.unwrap();
2358	db.update_user(bob.id, None, Some(true), None, None)
2359	.await
2360	.unwrap();
2361	assert!(
2362	db.session_user_with_roots("bob-tok-4")
2363	.await
2364	.unwrap()
2365	.is_some()
2366	);
2367	}
2368
2369	#[tokio::test]
2370	async fn sessions_lifecycle() {
2371	let (db, admin) = db_with_admin().await;
2372	assert!(
2373	db.session_user_with_roots("ghost-token")
2374	.await
2375	.unwrap()
2376	.is_none()
2377	);
2378	db.create_session(admin.id, "tok1").await.unwrap();
2379	let (u, _) = db.session_user_with_roots("tok1").await.unwrap().unwrap();
2380	assert_eq!(u.id, admin.id);
2381	// Disabling the user invalidates existing sessions.
2382	edit(&db, admin.id, None, None, Some(false)).await;
2383	assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
2384	edit(&db, admin.id, None, None, Some(true)).await;
2385	assert!(db.session_user_with_roots("tok1").await.unwrap().is_some());
2386	db.delete_session("tok1").await.unwrap();
2387	assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
2388	}
2389
2390	#[tokio::test]
2391	async fn user_crud_and_roots() {
2392	let (db, _admin) = db_with_admin().await;
2393	let h = crate::auth::hash_password("bobpass1").unwrap();
2394	let bob = db
2395	.create_user("bob", &h, false, &[("docs".into(), Mode::Rw)])
2396	.await
2397	.unwrap();
2398	assert!(!bob.is_admin);
2399	assert!(bob.active);
2400
2401	// Duplicate name (case-insensitive) is rejected.
2402	let h2 = crate::auth::hash_password("carolpass1").unwrap();
2403	assert!(db.create_user("BOB", &h2, false, &[]).await.is_err());
2404	assert!(db.create_user("carol", &h2, false, &[]).await.is_ok());
2405
2406	// Lookup helpers.
2407	assert_eq!(
2408	db.find_user_by_name("Bob").await.unwrap().unwrap().id,
2409	bob.id
2410	);
2411	assert_eq!(
2412	db.find_user_by_id(bob.id).await.unwrap().unwrap().name,
2413	"bob"
2414	);
2415	assert!(db.find_user_by_name("dave").await.unwrap().is_none());
2416	assert_eq!(db.all_users_with_roots().await.unwrap().len(), 3);
2417
2418	// Root replacement semantics.
2419	let roots = db.user_roots(bob.id).await.unwrap();
2420	assert_eq!(roots.len(), 1);
2421	db.update_user(
2422	bob.id,
2423	None,
2424	None,
2425	None,
2426	Some(&[(".".into(), Mode::Ro), ("docs".into(), Mode::Rw)]),
2427	)
2428	.await
2429	.unwrap();
2430	let roots = db.user_roots(bob.id).await.unwrap();
2431	assert_eq!(roots.len(), 2);
2432	assert!(roots.iter().any(\|r\| r.path == "." && r.mode == Mode::Ro));
2433	db.update_user(bob.id, None, None, None, Some(&[]))
2434	.await
2435	.unwrap();
2436	assert!(db.user_roots(bob.id).await.unwrap().is_empty());
2437
2438	// Password update.
2439	let new_h = crate::auth::hash_password("bobpass2").unwrap();
2440	edit(&db, bob.id, Some(&new_h), None, None).await;
2441	assert!(
2442	db.verify_password("bob", "bobpass1")
2443	.await
2444	.unwrap()
2445	.is_none()
2446	);
2447	assert!(
2448	db.verify_password("bob", "bobpass2")
2449	.await
2450	.unwrap()
2451	.is_some()
2452	);
2453
2454	// Admin flag + count (only active admins count).
2455	edit(&db, bob.id, None, Some(true), None).await;
2456	assert_eq!(db.count_admins().await.unwrap(), 2);
2457	edit(&db, bob.id, None, None, Some(false)).await;
2458	assert_eq!(db.count_admins().await.unwrap(), 1);
2459	edit(&db, bob.id, None, Some(false), None).await;
2460
2461	// Deletion.
2462	assert!(db.delete_user(bob.id).await.unwrap());
2463	assert!(db.find_user_by_id(bob.id).await.unwrap().is_none());
2464	assert!(!db.delete_user(bob.id).await.unwrap());
2465	assert_eq!(db.user_count().await.unwrap(), 2);
2466	}
2467
2468	fn share_row(expires_at: Option<&str>) -> ShareRow {
2469	ShareRow {
2470	id: 1,
2471	token: "t".into(),
2472	creator_id: 1,
2473	target: "docs".into(),
2474	is_file: false,
2475	mode: Mode::Ro,
2476	created_at: "2024-01-01T00:00:00Z".into(),
2477	expires_at: expires_at.map(str::to_string),
2478	password_hash: None,
2479	}
2480	}
2481
2482	#[test]
2483	fn share_expiry_logic() {
2484	assert!(!share_row(None).is_expired());
2485	assert!(!share_row(Some("2999-01-01T00:00:00Z")).is_expired());
2486	assert!(share_row(Some("2000-01-01T00:00:00Z")).is_expired());
2487	// Unparseable expiry → treated as not expired (fail open for reads).
2488	assert!(!share_row(Some("not-a-date")).is_expired());
2489	}
2490
2491	#[tokio::test]
2492	async fn shares_crud() {
2493	let (db, admin) = db_with_admin().await;
2494	let s1 = db
2495	.create_share(admin.id, "tok-a", "docs", false, Mode::Ro, None, None)
2496	.await
2497	.unwrap();
2498	let s2 = db
2499	.create_share(
2500	admin.id,
2501	"tok-b",
2502	"file.txt",
2503	true,
2504	Mode::Rw,
2505	Some("2999-01-01T00:00:00Z"),
2506	None,
2507	)
2508	.await
2509	.unwrap();
2510	assert!(s2.id > s1.id);
2511
2512	let found = db.share_by_token("tok-b").await.unwrap().unwrap();
2513	assert!(found.is_file);
2514	assert_eq!(found.mode, Mode::Rw);
2515	assert!(db.share_by_token("nope").await.unwrap().is_none());
2516
2517	// Listed newest-first.
2518	let list = db.user_shares(admin.id).await.unwrap();
2519	assert_eq!(list.len(), 2);
2520	assert_eq!(list[0].id, s2.id);
2521	// Other users see nothing.
2522	let h = crate::auth::hash_password("bobpass1").unwrap();
2523	let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
2524	assert!(db.user_shares(bob.id).await.unwrap().is_empty());
2525
2526	// Only the creator can delete.
2527	assert!(!db.delete_share(s1.id, bob.id).await.unwrap());
2528	assert!(db.delete_share(s1.id, admin.id).await.unwrap());
2529	assert!(db.share_by_token("tok-a").await.unwrap().is_none());
2530	assert!(!db.delete_share(s1.id, admin.id).await.unwrap());
2531	}
2532
2533	/// The sweep decides which timestamps are past, and `expires_at` is
2534	/// stored in whatever RFC 3339 shape the client sent.
2535	#[tokio::test]
2536	async fn the_sweep_drops_expired_shares_and_stale_unlocks() {
2537	let (db, admin) = db_with_admin().await;
2538	let make = async \|token: &str, expires: Option<&str>\| {
2539	db.create_share(admin.id, token, "docs", false, Mode::Ro, expires, None)
2540	.await
2541	.unwrap()
2542	};
2543	let past_offset = make("gone-offset", Some("2000-01-01T00:00:00+02:00")).await;
2544	make("gone-utc", Some("2000-01-01T00:00:00Z")).await;
2545	let future = make("stays-future", Some("2999-01-01T00:00:00Z")).await;
2546	make("stays-forever", None).await;
2547	// `is_expired` keeps an unreadable timestamp; the sweep must agree,
2548	// or the two disagree about the same row.
2549	make("stays-garbage", Some("not-a-date")).await;
2550
2551	let fresh = db.create_share_unlock(future.id).await.unwrap();
2552	let stale = db.create_share_unlock(future.id).await.unwrap();
2553	let doomed = db.create_share_unlock(past_offset.id).await.unwrap();
2554	{
2555	let c = db.0.lock().await;
2556	c.execute(
2557	"UPDATE share_unlocks SET created_at = '2000-01-01T00:00:00Z' WHERE token = ?1",
2558	[&stale],
2559	)
2560	.unwrap();
2561	}
2562
2563	let (shares, unlocks) = db.sweep().await.unwrap();
2564	assert_eq!(shares, 2, "only the two past timestamps");
2565	// The stale one, plus the cascade from the expired share it opened.
2566	assert_eq!(unlocks, 1, "the cascade runs with the share, not here");
2567
2568	for token in ["gone-offset", "gone-utc"] {
2569	assert!(db.share_by_token(token).await.unwrap().is_none(), "{token}");
2570	}
2571	for token in ["stays-future", "stays-forever", "stays-garbage"] {
2572	assert!(db.share_by_token(token).await.unwrap().is_some(), "{token}");
2573	}
2574
2575	assert!(db.share_unlock_valid(&fresh, future.id).await.unwrap());
2576	assert!(!db.share_unlock_valid(&stale, future.id).await.unwrap());
2577	assert!(
2578	!db.share_unlock_valid(&doomed, past_offset.id)
2579	.await
2580	.unwrap(),
2581	"an unlock must not outlive the share it opened"
2582	);
2583
2584	assert_eq!(
2585	db.sweep().await.unwrap(),
2586	(0, 0),
2587	"a second pass is a no-op"
2588	);
2589	}
2590
2591	/// The unlock token is what a visitor's cookie carries, so an unlock
2592	/// that opened the wrong share would be a full bypass of the password.
2593	#[tokio::test]
2594	async fn share_unlocks_are_bound_to_one_share() {
2595	let (db, admin) = db_with_admin().await;
2596	let a = db
2597	.create_share(
2598	admin.id,
2599	"tok-a",
2600	"docs",
2601	false,
2602	Mode::Ro,
2603	None,
2604	Some("hash"),
2605	)
2606	.await
2607	.unwrap();
2608	let b = db
2609	.create_share(
2610	admin.id,
2611	"tok-b",
2612	"other",
2613	false,
2614	Mode::Ro,
2615	None,
2616	Some("hash"),
2617	)
2618	.await
2619	.unwrap();
2620	assert_eq!(
2621	db.share_by_token("tok-a")
2622	.await
2623	.unwrap()
2624	.unwrap()
2625	.password_hash,
2626	Some("hash".to_string())
2627	);
2628
2629	let unlock = db.create_share_unlock(a.id).await.unwrap();
2630	assert!(db.share_unlock_valid(&unlock, a.id).await.unwrap());
2631	assert!(!db.share_unlock_valid(&unlock, b.id).await.unwrap());
2632	assert!(!db.share_unlock_valid("nonsense", a.id).await.unwrap());
2633
2634	// Deleting the share takes its unlocks with it, so a re-created
2635	// share that happened to reuse the id could not inherit them.
2636	assert!(db.delete_share(a.id, admin.id).await.unwrap());
2637	assert!(!db.share_unlock_valid(&unlock, a.id).await.unwrap());
2638	}
2639
2640	#[tokio::test]
2641	async fn revoking_a_path_takes_its_descendants_only() {
2642	let (db, admin) = db_with_admin().await;
2643	let mk = async \|token: &str, target: &str\| {
2644	db.create_share(admin.id, token, target, false, Mode::Ro, None, None)
2645	.await
2646	.unwrap();
2647	};
2648	mk("t-self", "docs").await;
2649	mk("t-child", "docs/a.txt").await;
2650	mk("t-deep", "docs/inner/b.txt").await;
2651	// A sibling whose name merely starts with "docs" must survive.
2652	mk("t-sibling", "docs2/c.txt").await;
2653	mk("t-other", "src").await;
2654	// SQL wildcards in a path are literal characters, not patterns.
2655	mk("t-wild", "do%s/d.txt").await;
2656
2657	assert_eq!(db.revoke_shares_at("docs").await.unwrap(), 3);
2658	for gone in ["t-self", "t-child", "t-deep"] {
2659	assert!(db.share_by_token(gone).await.unwrap().is_none(), "{gone}");
2660	}
2661	for kept in ["t-sibling", "t-other", "t-wild"] {
2662	assert!(db.share_by_token(kept).await.unwrap().is_some(), "{kept}");
2663	}
2664	// Revoking a path nobody shared is a no-op, not an error.
2665	assert_eq!(db.revoke_shares_at("nothing/here").await.unwrap(), 0);
2666	}
2667
2668	#[tokio::test]
2669	async fn settings_round_trip() {
2670	let (db, _admin) = db_with_admin().await;
2671	assert!(!db.allow_writable_shares().await.unwrap());
2672	db.set_allow_writable_shares(true).await.unwrap();
2673	assert!(db.allow_writable_shares().await.unwrap());
2674	// Upsert semantics.
2675	db.set_allow_writable_shares(false).await.unwrap();
2676	assert!(!db.allow_writable_shares().await.unwrap());
2677	// Generic get/set.
2678	db.set_setting("custom", "v").await.unwrap();
2679	assert_eq!(
2680	db.get_setting("custom").await.unwrap().as_deref(),
2681	Some("v")
2682	);
2683	assert_eq!(db.get_setting("missing").await.unwrap(), None);
2684	}
2685	}
2686