dovenest

db.rs

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