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	/// Public feed links with their collection and owner: those of the
2181	/// account `owner`, or every one for the admin.
2182	pub async fn pim_links_with_owner(
2183	&self,
2184	owner: Option<i64>,
2185	) -> DbResult<Vec<PimLinkWithOwner>> {
2186	let c = self.conn.lock().await;
2187	let mut stmt = c.prepare_cached(
2188	"SELECT l.id, l.token, l.collection_id, l.busy_only, l.created_at, l.expires_at,
2189	l.password_hash, coalesce(c.displayname, c.slug), c.kind, u.id, u.name,
2190	u.active != 0
2191	FROM pim_links l
2192	JOIN pim_collections c ON c.id = l.collection_id
2193	JOIN principals p ON p.id = c.principal_id
2194	JOIN users u ON u.id = p.user_id
2195	WHERE ?1 IS NULL OR u.id = ?1
2196	ORDER BY u.name COLLATE NOCASE, l.id DESC",
2197	)?;
2198	stmt.query_map([owner], \|r\| {
2199	Ok(PimLinkWithOwner {
2200	link: map_pim_link(r)?,
2201	collection_name: r.get(7)?,
2202	kind: PimKind::parse(&r.get::<_, String>(8)?),
2203	owner_id: r.get(9)?,
2204	owner_name: r.get(10)?,
2205	owner_active: r.get(11)?,
2206	})
2207	})?
2208	.collect()
2209	}
2210
2211	/// The loans of the account `owner`'s collections:
2212	/// `(collection id, name, kind, borrower id, borrower name, mode)`.
2213	pub async fn pim_lends(
2214	&self,
2215	owner: i64,
2216	) -> DbResult<Vec<(i64, String, PimKind, i64, String, PimShareMode)>> {
2217	let c = self.conn.lock().await;
2218	let mut stmt = c.prepare_cached(
2219	"SELECT c.id, coalesce(c.displayname, c.slug), c.kind, u.id, u.name, s.mode
2220	FROM pim_shares s
2221	JOIN pim_collections c ON c.id = s.collection_id
2222	JOIN principals p ON p.id = c.principal_id
2223	JOIN users u ON u.id = s.user_id
2224	WHERE p.user_id = ?1
2225	ORDER BY coalesce(c.displayname, c.slug) COLLATE NOCASE, u.name COLLATE NOCASE",
2226	)?;
2227	stmt.query_map([owner], \|r\| {
2228	Ok((
2229	r.get(0)?,
2230	r.get(1)?,
2231	PimKind::parse(&r.get::<_, String>(2)?),
2232	r.get(3)?,
2233	r.get(4)?,
2234	r.get::<_, SqlShareMode>(5)?.0,
2235	))
2236	})?
2237	.collect()
2238	}
2239
2240	/// Revokes a feed link whoever made it.
2241	pub async fn admin_delete_pim_link(&self, id: i64) -> DbResult<bool> {
2242	let c = self.conn.lock().await;
2243	Ok(c.execute("DELETE FROM pim_links WHERE id = ?1", [id])? > 0)
2244	}
2245
2246	/// Whether users may create writable (read-write) shares. Off by default;
2247	/// the admin setting gates it.
2248	pub async fn allow_writable_shares(&self) -> DbResult<bool> {
2249	Ok(self.get_setting("allow_writable_shares").await?.as_deref() == Some("1"))
2250	}
2251
2252	pub async fn set_allow_writable_shares(&self, v: bool) -> DbResult<()> {
2253	self.set_setting("allow_writable_shares", if v { "1" } else { "0" })
2254	.await
2255	}
2256	}
2257
2258	/// Write a new password hash and drop every session that was opened with the
2259	/// old one.
2260	///
2261	/// The two belong together: a password is changed because the old one is
2262	/// suspect (an admin resetting a compromised account), and a session that
2263	/// survives the reset leaves whoever holds it signed in. Takes the
2264	/// transaction so the caller can bundle it with its other edits.
2265	fn set_password(tx: &rusqlite::Transaction<'_>, id: i64, pass_hash: &str) -> DbResult<()> {
2266	tx.execute(
2267	"UPDATE users SET pass_hash = ?1 WHERE id = ?2",
2268	params![pass_hash, id],
2269	)?;
2270	tx.execute("DELETE FROM sessions WHERE user_id = ?1", [id])?;
2271	Ok(())
2272	}
2273
2274	/// Commit a credential change, or undo it if the account would be left with
2275	/// no way to sign in.
2276	///
2277	/// The handlers check the same rules first, so the person gets a message that
2278	/// says which rule. This is the guarantee behind those checks: a count read
2279	/// before the write can already be stale when the write lands, and two
2280	/// requests racing could otherwise take away the last credential between them.
2281	/// `false` means the change was rolled back.
2282	fn commit_if_reachable(tx: rusqlite::Transaction<'_>, user_id: i64) -> DbResult<bool> {
2283	let (has_password, mode) = tx.query_row(
2284	"SELECT pass_hash != '', auth_mode FROM users WHERE id = ?1",
2285	[user_id],
2286	\|r\| Ok((r.get::<_, bool>(0)?, r.get::<_, SqlAuthMode>(1)?)),
2287	)?;
2288	let passkeys: i64 = tx.query_row(
2289	"SELECT COUNT(*) FROM passkeys WHERE user_id = ?1",
2290	[user_id],
2291	\|r\| r.get(0),
2292	)?;
2293	let reachable = match mode.0 {
2294	AuthMode::Either => has_password \|\| passkeys > 0,
2295	AuthMode::Both => has_password && passkeys > 0,
2296	};
2297	if !reachable {
2298	tx.rollback()?;
2299	return Ok(false);
2300	}
2301	tx.commit()?;
2302	Ok(true)
2303	}
2304
2305	/// Column order matched by [`USER_COLS`] and [`USER_COLS_U`].
2306	fn map_user(r: &rusqlite::Row) -> DbResult<User> {
2307	Ok(User {
2308	id: r.get(0)?,
2309	name: r.get(1)?,
2310	is_admin: r.get(2)?,
2311	active: r.get(3)?,
2312	single_click: r.get(4)?,
2313	thumbnails: r.get(5)?,
2314	language: r.get(6)?,
2315	default_root_id: r.get(7)?,
2316	auth_mode: r.get::<_, SqlAuthMode>(8)?.0,
2317	has_password: r.get(9)?,
2318	week_start: r.get(10)?,
2319	})
2320	}
2321
2322	/// Column order matched by the `passkeys` SELECTs above.
2323	fn map_passkey(r: &rusqlite::Row) -> DbResult<PasskeyRow> {
2324	Ok(PasskeyRow {
2325	id: r.get(0)?,
2326	name: r.get(1)?,
2327	created_at: r.get(2)?,
2328	last_used_at: r.get(3)?,
2329	discoverable: r.get(4)?,
2330	passkey: r.get(5)?,
2331	})
2332	}
2333
2334	/// A just-created account: the column defaults of `users`.
2335	fn new_user(id: i64, name: &str, is_admin: bool) -> User {
2336	User {
2337	id,
2338	name: name.to_string(),
2339	is_admin,
2340	active: true,
2341	single_click: false,
2342	thumbnails: true,
2343	language: None,
2344	week_start: 1,
2345	default_root_id: None,
2346	auth_mode: AuthMode::Either,
2347	has_password: true,
2348	}
2349	}
2350
2351	/// Column order matched by the `app_passwords` SELECTs above.
2352	fn map_app_password(r: &rusqlite::Row) -> DbResult<AppPasswordInfo> {
2353	Ok(AppPasswordInfo {
2354	id: r.get(0)?,
2355	name: r.get(1)?,
2356	created_at: r.get(2)?,
2357	last_used_at: r.get(3)?,
2358	})
2359	}
2360
2361	/// Messages an inbox keeps; older ones are dropped.
2362	const INBOX_KEEP: i64 = 100;
2363
2364	fn ensure_inbox(c: &Connection, principal_id: i64) -> DbResult<()> {
2365	c.prepare_cached(
2366	"INSERT INTO pim_collections (principal_id, kind, slug, displayname, components, created_at)
2367	SELECT ?1, 'cal', 'inbox', 'Inbox', 'VEVENT,VTODO,VJOURNAL', ?2
2368	WHERE NOT EXISTS (SELECT 1 FROM pim_collections
2369	WHERE principal_id = ?1 AND kind = 'cal' AND slug = 'inbox')",
2370	)?
2371	.execute(params![principal_id, now()])?;
2372	Ok(())
2373	}
2374
2375	/// The writes of [`PimOp`]s, inside the caller's transaction.
2376	fn apply_ops(tx: &rusqlite::Transaction, ops: &[PimOp]) -> DbResult<()> {
2377	for op in ops {
2378	match op {
2379	PimOp::Put {
2380	collection_id,
2381	obj,
2382	data,
2383	} => {
2384	put_object(tx, *collection_id, obj, data)?;
2385	}
2386	PimOp::Delete {
2387	collection_id,
2388	name,
2389	} => {
2390	tx.execute(
2391	"DELETE FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
2392	params![collection_id, name],
2393	)?;
2394	record_pim_change(tx, *collection_id, name, true)?;
2395	}
2396	PimOp::Inbox {
2397	principal_id,
2398	obj,
2399	data,
2400	} => {
2401	let inbox: i64 = tx.query_row(
2402	"SELECT id FROM pim_collections
2403	WHERE principal_id = ?1 AND kind = 'cal' AND slug = 'inbox'",
2404	[principal_id],
2405	\|r\| r.get(0),
2406	)?;
2407	put_object(tx, inbox, obj, data)?;
2408	// ponytail: a fixed cap. Clients that never empty the inbox
2409	// would fill it forever; an age limit may suit better.
2410	let old: Vec<String> = tx
2411	.prepare_cached(
2412	"SELECT name FROM pim_objects WHERE collection_id = ?1
2413	ORDER BY id DESC LIMIT -1 OFFSET ?2",
2414	)?
2415	.query_map(params![inbox, INBOX_KEEP], \|r\| r.get(0))?
2416	.collect::<DbResult<_>>()?;
2417	for name in old {
2418	tx.execute(
2419	"DELETE FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
2420	params![inbox, name],
2421	)?;
2422	record_pim_change(tx, inbox, &name, true)?;
2423	}
2424	}
2425	PimOp::DeleteCollection(id) => {
2426	tx.execute("DELETE FROM pim_collections WHERE id = ?1", [id])?;
2427	}
2428	}
2429	}
2430	Ok(())
2431	}
2432
2433	/// Stores an object under `obj.name`, replacing one of that name.
2434	fn put_object(
2435	tx: &rusqlite::Transaction,
2436	collection_id: i64,
2437	obj: &PimObject,
2438	data: &[u8],
2439	) -> DbResult<()> {
2440	tx.execute(
2441	"INSERT INTO pim_objects (collection_id, name, uid, component, data, etag, modified_at,
2442	schedule_tag)
2443	VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)
2444	ON CONFLICT (collection_id, name) DO UPDATE SET uid = ?3, component = ?4,
2445	data = ?5, etag = ?6, modified_at = ?7, schedule_tag = ?8",
2446	params![
2447	collection_id,
2448	obj.name,
2449	obj.uid,
2450	obj.component,
2451	data,
2452	obj.etag,
2453	now(),
2454	obj.schedule_tag
2455	],
2456	)?;
2457	record_pim_change(tx, collection_id, &obj.name, false)
2458	}
2459
2460	/// Bumps the collection's `seq` and records it as the latest change of `name`.
2461	fn record_pim_change(
2462	tx: &rusqlite::Transaction,
2463	collection_id: i64,
2464	name: &str,
2465	deleted: bool,
2466	) -> DbResult<()> {
2467	let seq: i64 = tx.query_row(
2468	"UPDATE pim_collections SET seq = seq + 1 WHERE id = ?1 RETURNING seq",
2469	[collection_id],
2470	\|r\| r.get(0),
2471	)?;
2472	tx.execute(
2473	"INSERT INTO pim_changes (collection_id, name, seq, deleted) VALUES (?1, ?2, ?3, ?4)
2474	ON CONFLICT (collection_id, name) DO UPDATE SET seq = ?3, deleted = ?4",
2475	params![collection_id, name, seq, deleted],
2476	)?;
2477	Ok(())
2478	}
2479
2480	/// An account, room or resource, as CalDAV sees it.
2481	#[derive(Debug, Clone)]
2482	pub struct PimPrincipal {
2483	pub id: i64,
2484	/// The account of a person; rooms and resources have none.
2485	pub user_id: Option<i64>,
2486	/// The URL segment.
2487	pub name: String,
2488	pub display_name: Option<String>,
2489	pub kind: UserType,
2490	}
2491
2492	impl PimPrincipal {
2493	pub fn display(&self) -> &str {
2494	self.display_name.as_deref().unwrap_or(&self.name)
2495	}
2496	}
2497
2498	const PRINCIPAL_COLS: &str = "p.id, p.user_id, p.name, p.display_name, p.kind";
2499	const PRINCIPALS: &str = "principals p LEFT JOIN users u ON u.id = p.user_id";
2500	/// Disabled accounts are hidden.
2501	const VISIBLE: &str = "(p.user_id IS NULL OR u.active = 1)";
2502
2503	fn map_principal(r: &rusqlite::Row) -> DbResult<PimPrincipal> {
2504	Ok(PimPrincipal {
2505	id: r.get(0)?,
2506	user_id: r.get(1)?,
2507	name: r.get(2)?,
2508	display_name: r.get(3)?,
2509	kind: match r.get::<_, String>(4)?.as_str() {
2510	"room" => UserType::Room,
2511	"resource" => UserType::Resource,
2512	_ => UserType::Individual,
2513	},
2514	})
2515	}
2516
2517	fn kind_str(kind: UserType) -> &'static str {
2518	match kind {
2519	UserType::Individual => "person",
2520	UserType::Room => "room",
2521	UserType::Resource => "resource",
2522	}
2523	}
2524
2525	fn map_shared(r: &rusqlite::Row) -> DbResult<(PimCollection, String, PimShareMode)> {
2526	Ok((
2527	map_pim_collection(r)?,
2528	r.get(10)?,
2529	r.get::<_, SqlShareMode>(11)?.0,
2530	))
2531	}
2532
2533	const PIM_COLLECTION_COLS_C: &str = "c.id, c.slug, c.displayname, c.description, c.color,
2534	c.timezone, c.sort_order, c.components, c.seq, c.transparent";
2535	const PIM_COLLECTION_COLS: &str = "id, slug, displayname, description, color, timezone,
2536	sort_order, components, seq, transparent";
2537
2538	fn write_props(
2539	tx: &rusqlite::Transaction,
2540	place: PropPlace,
2541	set: &[DeadProp],
2542	remove: &[(String, String)],
2543	) -> DbResult<()> {
2544	let key = place.key();
2545	let (principal_id, collection_id) = place.owners();
2546	for (ns, name) in remove {
2547	tx.execute(
2548	"DELETE FROM pim_props WHERE place = ?1 AND ns = ?2 AND name = ?3",
2549	params![key, ns, name],
2550	)?;
2551	}
2552	for p in set {
2553	tx.execute(
2554	"INSERT OR REPLACE INTO pim_props (place, principal_id, collection_id, ns, name, xml)
2555	VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
2556	params![key, principal_id, collection_id, p.ns, p.name, p.xml],
2557	)?;
2558	}
2559	Ok(())
2560	}
2561
2562	fn map_pim_collection(r: &rusqlite::Row) -> DbResult<PimCollection> {
2563	Ok(PimCollection {
2564	id: r.get(0)?,
2565	slug: r.get(1)?,
2566	displayname: r.get(2)?,
2567	description: r.get(3)?,
2568	color: r.get(4)?,
2569	timezone: r.get(5)?,
2570	sort_order: r.get(6)?,
2571	components: r.get(7)?,
2572	seq: r.get(8)?,
2573	transparent: r.get(9)?,
2574	})
2575	}
2576
2577	const PIM_OBJECT_COLS: &str = "name, uid, component, etag, length(data), modified_at, schedule_tag";
2578	const PIM_OBJECT_COLS_O: &str =
2579	"o.name, o.uid, o.component, o.etag, length(o.data), o.modified_at, o.schedule_tag";
2580
2581	fn map_pim_object(r: &rusqlite::Row) -> DbResult<PimObject> {
2582	map_pim_object_at(r, 0)
2583	}
2584
2585	/// [`PIM_OBJECT_COLS`] starting at column `at`.
2586	fn map_pim_object_at(r: &rusqlite::Row, at: usize) -> DbResult<PimObject> {
2587	Ok(PimObject {
2588	name: r.get(at)?,
2589	uid: r.get(at + 1)?,
2590	component: r.get(at + 2)?,
2591	etag: r.get(at + 3)?,
2592	size: r.get(at + 4)?,
2593	modified_at: r.get(at + 5)?,
2594	schedule_tag: r.get(at + 6)?,
2595	})
2596	}
2597
2598	const PIM_LINK_COLS: &str =
2599	"id, token, collection_id, busy_only, created_at, expires_at, password_hash";
2600
2601	fn map_pim_link(r: &rusqlite::Row) -> DbResult<PimLink> {
2602	Ok(PimLink {
2603	id: r.get(0)?,
2604	token: r.get(1)?,
2605	collection_id: r.get(2)?,
2606	busy_only: r.get(3)?,
2607	created_at: r.get(4)?,
2608	expires_at: r.get(5)?,
2609	password_hash: r.get(6)?,
2610	})
2611	}
2612
2613	/// Column order matched by the two `shares` SELECTs above.
2614	fn map_share(r: &rusqlite::Row) -> DbResult<ShareRow> {
2615	Ok(ShareRow {
2616	id: r.get(0)?,
2617	token: r.get(1)?,
2618	creator_id: r.get(2)?,
2619	target: r.get(3)?,
2620	is_file: r.get::<_, i64>(4)? != 0,
2621	mode: r.get::<_, SqlMode>(5)?.0,
2622	created_at: r.get(6)?,
2623	expires_at: r.get(7)?,
2624	password_hash: r.get(8)?,
2625	})
2626	}
2627
2628	/// A hash of a random string nobody knows. Verified against when the login
2629	/// name does not exist, so both paths cost one Argon2 run.
2630	static DUMMY_HASH: std::sync::LazyLock<String> = std::sync::LazyLock::new(\|\| {
2631	crate::auth::hash_password(&crate::auth::random_token()).expect("argon2 hash")
2632	});
2633
2634	/// How long an unlock row outlives its cookie. The cookie dies with the
2635	/// browser, so this only bounds the rows left behind by closed sessions.
2636	const UNLOCK_MAX_AGE_DAYS: i64 = 7;
2637
2638	/// How often [`Db::sweep`] runs. A share the picker can only set to the
2639	/// minute is gone from the owner's list about when it says it is.
2640	const SWEEP_EVERY: std::time::Duration = std::time::Duration::from_secs(60);
2641
2642	/// Run [`Db::sweep`] until the process ends. A failed pass is logged and
2643	/// retried on the next one: nothing downstream depends on it having run.
2644	pub async fn sweep_forever(db: Db) {
2645	loop {
2646	tokio::time::sleep(SWEEP_EVERY).await;
2647	match db.sweep().await {
2648	Ok((0, 0)) => {}
2649	Ok((shares, unlocks)) => tracing::debug!(shares, unlocks, "swept expired shares"),
2650	Err(e) => tracing::warn!(error = %e, "the share sweep failed"),
2651	}
2652	}
2653	}
2654
2655	/// The time `ago` in the past as a stored stamp. The format sorts
2656	/// lexicographically, so SQL can compare stamps as text.
2657	fn stamp(ago: chrono::Duration) -> String {
2658	(chrono::Utc::now() - ago).to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
2659	}
2660
2661	fn now() -> String {
2662	stamp(chrono::Duration::zero())
2663	}
2664
2665	const SCHEMA_V1: &str = r#"
2666	CREATE TABLE IF NOT EXISTS users (
2667	id INTEGER PRIMARY KEY AUTOINCREMENT,
2668	name TEXT NOT NULL UNIQUE COLLATE NOCASE,
2669	pass_hash TEXT NOT NULL,
2670	is_admin INTEGER NOT NULL DEFAULT 0,
2671	created_at TEXT NOT NULL
2672	);
2673
2674	CREATE TABLE IF NOT EXISTS user_roots (
2675	id INTEGER PRIMARY KEY AUTOINCREMENT,
2676	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
2677	path TEXT NOT NULL,
2678	mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
2679	UNIQUE (user_id, path)
2680	);
2681
2682	CREATE TABLE IF NOT EXISTS shares (
2683	id INTEGER PRIMARY KEY AUTOINCREMENT,
2684	token TEXT NOT NULL UNIQUE,
2685	creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
2686	target TEXT NOT NULL,
2687	is_file INTEGER NOT NULL,
2688	mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
2689	created_at TEXT NOT NULL,
2690	expires_at TEXT
2691	);
2692
2693	CREATE TABLE IF NOT EXISTS sessions (
2694	token TEXT PRIMARY KEY,
2695	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
2696	created_at TEXT NOT NULL,
2697	last_seen_at TEXT NOT NULL
2698	);
2699
2700	CREATE TABLE IF NOT EXISTS settings (
2701	key TEXT PRIMARY KEY,
2702	value TEXT NOT NULL
2703	);
2704	INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
2705	"#;
2706
2707	#[cfg(test)]
2708	mod tests {
2709	use super::*;
2710
2711	// Most tests use an in-memory DB (the file-based path is still covered
2712	// by `v1_db_migrates_to_v2` and the integration harness' `Db::open`).
2713	async fn mem() -> Db {
2714	Db::open(Path::new(":memory:")).await.unwrap()
2715	}
2716
2717	/// `update_user` is the only way production edits these fields, so the
2718	/// tests exercise that path rather than per-field helpers.
2719	async fn edit(db: &Db, id: i64, pass: Option<&str>, admin: Option<bool>, active: Option<bool>) {
2720	db.update_user(id, pass, admin, active, None).await.unwrap();
2721	}
2722
2723	/// Backdate a stamp, which production code has no reason to do.
2724	async fn set_last_used(db: &Db, secret_hash: &str, at: &str) {
2725	db.conn
2726	.lock()
2727	.await
2728	.execute(
2729	"UPDATE app_passwords SET last_used_at = ?1 WHERE secret_hash = ?2",
2730	params![at, secret_hash],
2731	)
2732	.unwrap();
2733	}
2734
2735	async fn db_with_admin() -> (Db, User) {
2736	let db = mem().await;
2737	let hash = crate::auth::hash_password("admin1234").unwrap();
2738	let admin = db.create_admin("admin", &hash).await.unwrap().unwrap();
2739	(db, admin)
2740	}
2741
2742	#[tokio::test]
2743	async fn fresh_db_state() {
2744	let db = mem().await;
2745	assert_eq!(db.user_count().await.unwrap(), 0);
2746	assert_eq!(db.count_admins().await.unwrap(), 0);
2747	assert!(!db.allow_writable_shares().await.unwrap());
2748	assert!(db.find_user_by_name("nobody").await.unwrap().is_none());
2749	assert!(db.find_user_by_id(1).await.unwrap().is_none());
2750	assert!(db.all_users_with_roots().await.unwrap().is_empty());
2751	}
2752
2753	#[tokio::test]
2754	async fn app_passwords_open_one_account_and_stamp_their_use() {
2755	let (db, admin) = db_with_admin().await;
2756	let secret = crate::auth::short_token();
2757	let hash = crate::auth::app_password_hash(&secret);
2758	let row = db
2759	.add_app_password(admin.id, "laptop", &hash)
2760	.await
2761	.unwrap()
2762	.unwrap();
2763	assert_eq!(row.name, "laptop");
2764	assert!(row.last_used_at.is_none());
2765
2766	let hit = db.user_by_app_password(&hash).await.unwrap().unwrap();
2767	assert_eq!(hit.id, admin.id);
2768	let listed = \|db: &Db\| {
2769	let db = db.clone();
2770	async move {
2771	db.app_passwords(admin.id).await.unwrap()[0]
2772	.last_used_at
2773	.clone()
2774	}
2775	};
2776	assert!(listed(&db).await.is_some(), "first use must stamp the row");
2777
2778	// Ten minutes back is inside the hour window, and far enough from
2779	// `now()` that a rewrite would show at second resolution.
2780	let inside = stamp(chrono::Duration::minutes(10));
2781	set_last_used(&db, &hash, &inside).await;
2782	db.user_by_app_password(&hash).await.unwrap().unwrap();
2783	assert_eq!(
2784	listed(&db).await.as_deref(),
2785	Some(inside.as_str()),
2786	"a second use inside the hour wrote the stamp again"
2787	);
2788
2789	let outside = stamp(chrono::Duration::hours(2));
2790	set_last_used(&db, &hash, &outside).await;
2791	db.user_by_app_password(&hash).await.unwrap().unwrap();
2792	assert_ne!(
2793	listed(&db).await.as_deref(),
2794	Some(outside.as_str()),
2795	"a use after the window left the stamp stale"
2796	);
2797
2798	// The raw secret is not the key, and an inactive account does not match.
2799	let other = crate::auth::app_password_hash(&crate::auth::short_token());
2800	assert!(db.user_by_app_password(&other).await.unwrap().is_none());
2801	assert!(db.user_by_app_password(&secret).await.unwrap().is_none());
2802	edit(&db, admin.id, None, None, Some(false)).await;
2803	assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
2804	edit(&db, admin.id, None, None, Some(true)).await;
2805	assert!(db.user_by_app_password(&hash).await.unwrap().is_some());
2806
2807	for i in 1..APP_PASSWORD_LIMIT {
2808	let h = crate::auth::app_password_hash(&crate::auth::short_token());
2809	assert!(
2810	db.add_app_password(admin.id, &format!("c{i}"), &h)
2811	.await
2812	.unwrap()
2813	.is_some()
2814	);
2815	}
2816	let h = crate::auth::app_password_hash(&crate::auth::short_token());
2817	assert!(
2818	db.add_app_password(admin.id, "one-too-many", &h)
2819	.await
2820	.unwrap()
2821	.is_none()
2822	);
2823
2824	assert!(db.delete_app_password(row.id, admin.id).await.unwrap());
2825	assert!(!db.delete_app_password(row.id, admin.id).await.unwrap());
2826	assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
2827	}
2828
2829	#[tokio::test]
2830	async fn an_admin_password_reset_revokes_the_app_passwords() {
2831	let (db, admin) = db_with_admin().await;
2832	let hash = crate::auth::app_password_hash(&crate::auth::short_token());
2833	db.add_app_password(admin.id, "mount", &hash)
2834	.await
2835	.unwrap()
2836	.unwrap();
2837
2838	// An edit that sets no password leaves them alone.
2839	edit(&db, admin.id, None, None, Some(true)).await;
2840	assert_eq!(db.app_passwords(admin.id).await.unwrap().len(), 1);
2841
2842	// A reset is meant to revoke every way in that existed before it.
2843	let fresh = crate::auth::hash_password("rescued12").unwrap();
2844	edit(&db, admin.id, Some(&fresh), None, None).await;
2845	assert!(db.app_passwords(admin.id).await.unwrap().is_empty());
2846	assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
2847	}
2848
2849	#[tokio::test]
2850	async fn v1_db_migrates_to_v2() {
2851	let dir = tempfile::tempdir().unwrap();
2852	let path = dir.path().join("legacy.sqlite");
2853	{
2854	let conn = rusqlite::Connection::open(&path).unwrap();
2855	conn.execute_batch(SCHEMA_V1).unwrap();
2856	conn.execute(
2857	"INSERT INTO users (name, pass_hash, is_admin, created_at)
2858	VALUES ('legacy', 'hash', 1, '2024-01-01T00:00:00Z')",
2859	[],
2860	)
2861	.unwrap();
2862	conn.execute(
2863	"INSERT INTO user_roots (user_id, path, mode) VALUES (1, 'docs', 'rw')",
2864	[],
2865	)
2866	.unwrap();
2867	}
2868	let db = Db::open(&path).await.unwrap();
2869	assert_eq!(db.user_count().await.unwrap(), 1);
2870	let u = db.find_user_by_name("legacy").await.unwrap().unwrap();
2871	assert!(u.active, "v2 migration must default active to true");
2872	assert!(u.is_admin);
2873	assert_eq!(db.user_roots(u.id).await.unwrap().len(), 1);
2874	// Accounts from before CalDAV get their principal.
2875	let p = db.pim_principal("legacy").await.unwrap().unwrap();
2876	assert_eq!(p.user_id, Some(u.id));
2877	assert_eq!(db.principal_of(u.id).await.unwrap(), p.id);
2878	// Migrations are idempotent.
2879	let db2 = Db::open(&path).await.unwrap();
2880	assert_eq!(db2.user_count().await.unwrap(), 1);
2881	assert!(
2882	db2.find_user_by_name("legacy")
2883	.await
2884	.unwrap()
2885	.unwrap()
2886	.active
2887	);
2888	}
2889
2890	#[tokio::test]
2891	async fn deleted_defaults_come_back() {
2892	let (db, admin) = db_with_admin().await;
2893	let p = db.principal_of(admin.id).await.unwrap();
2894	db.pim_ensure_defaults(p).await.unwrap();
2895	let books = db.pim_collections(p, PimKind::AddressBook).await.unwrap();
2896	assert_eq!(books.len(), 1);
2897	db.pim_delete_collection(books[0].id).await.unwrap();
2898	// Remembered defaults must not hide that one is gone.
2899	db.pim_ensure_defaults(p).await.unwrap();
2900	assert_eq!(
2901	db.pim_collections(p, PimKind::AddressBook)
2902	.await
2903	.unwrap()
2904	.len(),
2905	1
2906	);
2907	}
2908
2909	#[tokio::test]
2910	async fn rooms_are_principals_not_accounts() {
2911	let (db, admin) = db_with_admin().await;
2912	let bob = db.create_user("bob", "hash", false, &[]).await.unwrap();
2913	let room = db
2914	.create_room("board", "Board", UserType::Room)
2915	.await
2916	.unwrap()
2917	.unwrap();
2918	assert_eq!(room.user_id, None);
2919
2920	// No account query returns the room.
2921	assert_eq!(db.user_count().await.unwrap(), 2);
2922	assert!(db.find_user_by_name("board").await.unwrap().is_none());
2923	assert!(db.verify_password("board", "").await.unwrap().is_none());
2924	let names: Vec<_> = db.all_users_with_roots().await.unwrap();
2925	assert!(names.iter().all(\|(u, _)\| u.name != "board"));
2926
2927	// One name space, without case.
2928	assert!(db.name_taken("BOARD").await.unwrap());
2929	assert!(db.create_user("Board", "hash", false, &[]).await.is_err());
2930	assert!(
2931	db.create_room("BOB", "Bob", UserType::Resource)
2932	.await
2933	.unwrap()
2934	.is_none()
2935	);
2936
2937	// Deleting an account takes its principal, collections and loans.
2938	let bob_p = db.principal_of(bob.id).await.unwrap();
2939	db.pim_ensure_defaults(bob_p).await.unwrap();
2940	let admin_p = db.principal_of(admin.id).await.unwrap();
2941	db.pim_ensure_defaults(admin_p).await.unwrap();
2942	let bobs = db.pim_collections(bob_p, PimKind::Calendar).await.unwrap();
2943	let admins = db
2944	.pim_collections(admin_p, PimKind::Calendar)
2945	.await
2946	.unwrap();
2947	db.pim_set_share(admins[0].id, bob.id, PimShareMode::Ro)
2948	.await
2949	.unwrap();
2950	assert!(db.delete_user(bob.id, &[]).await.unwrap());
2951	assert!(db.pim_principal("bob").await.unwrap().is_none());
2952	assert!(!db.name_taken("bob").await.unwrap());
2953	assert!(db.pim_collection_by_id(bobs[0].id).await.unwrap().is_none());
2954	assert!(db.pim_shares(admins[0].id).await.unwrap().is_empty());
2955	// The room outlives it.
2956	assert!(db.pim_principal("board").await.unwrap().is_some());
2957	}
2958
2959	#[tokio::test]
2960	async fn admin_user_and_passwords() {
2961	let (db, admin) = db_with_admin().await;
2962	assert!(admin.is_admin);
2963	assert!(admin.active);
2964	// Root "." rw is assigned by create_admin.
2965	let roots = db.user_roots(admin.id).await.unwrap();
2966	assert_eq!(roots.len(), 1);
2967	assert_eq!(roots[0].path, ".");
2968	assert_eq!(roots[0].mode, Mode::Rw);
2969
2970	assert!(
2971	db.verify_password("admin", "admin1234")
2972	.await
2973	.unwrap()
2974	.is_some()
2975	);
2976	assert!(db.verify_password("admin", "nope").await.unwrap().is_none());
2977	// Name lookup is case-insensitive (COLLATE NOCASE).
2978	assert!(
2979	db.verify_password("ADMIN", "admin1234")
2980	.await
2981	.unwrap()
2982	.is_some()
2983	);
2984	// Disabled users cannot verify.
2985	edit(&db, admin.id, None, None, Some(false)).await;
2986	assert!(
2987	db.verify_password("admin", "admin1234")
2988	.await
2989	.unwrap()
2990	.is_none()
2991	);
2992	edit(&db, admin.id, None, None, Some(true)).await;
2993	assert!(
2994	db.verify_password("admin", "admin1234")
2995	.await
2996	.unwrap()
2997	.is_some()
2998	);
2999	}
3000
3001	#[tokio::test]
3002	async fn setup_is_won_by_exactly_one_caller() {
3003	let db = mem().await;
3004	let hash = crate::auth::hash_password("admin1234").unwrap();
3005	assert!(db.create_admin("first", &hash).await.unwrap().is_some());
3006	// The guard lives in the insert, so a different name loses too.
3007	assert!(db.create_admin("second", &hash).await.unwrap().is_none());
3008	assert_eq!(db.user_count().await.unwrap(), 1);
3009	// The loser rolled back cleanly: no orphaned root row.
3010	let first = db.find_user_by_name("first").await.unwrap().unwrap();
3011	assert_eq!(db.user_roots(first.id).await.unwrap().len(), 1);
3012	}
3013
3014	#[tokio::test]
3015	async fn changing_a_password_drops_that_users_sessions() {
3016	let (db, admin) = db_with_admin().await;
3017	let h = crate::auth::hash_password("bobpass1").unwrap();
3018	let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
3019	db.create_session(admin.id, "admin-tok").await.unwrap();
3020	db.create_session(bob.id, "bob-tok-1").await.unwrap();
3021	db.create_session(bob.id, "bob-tok-2").await.unwrap();
3022
3023	let new_h = crate::auth::hash_password("bobpass2").unwrap();
3024	edit(&db, bob.id, Some(&new_h), None, None).await;
3025	assert!(
3026	db.session_user_with_roots("bob-tok-1")
3027	.await
3028	.unwrap()
3029	.is_none()
3030	);
3031	assert!(
3032	db.session_user_with_roots("bob-tok-2")
3033	.await
3034	.unwrap()
3035	.is_none()
3036	);
3037	// Only the reset user is signed out.
3038	assert!(
3039	db.session_user_with_roots("admin-tok")
3040	.await
3041	.unwrap()
3042	.is_some()
3043	);
3044
3045	// The admin-edit path bundles the same rule into its transaction.
3046	db.create_session(bob.id, "bob-tok-3").await.unwrap();
3047	let h3 = crate::auth::hash_password("bobpass3").unwrap();
3048	db.update_user(bob.id, Some(&h3), None, None, None)
3049	.await
3050	.unwrap();
3051	assert!(
3052	db.session_user_with_roots("bob-tok-3")
3053	.await
3054	.unwrap()
3055	.is_none()
3056	);
3057	// An edit that leaves the password alone keeps the session.
3058	db.create_session(bob.id, "bob-tok-4").await.unwrap();
3059	db.update_user(bob.id, None, Some(true), None, None)
3060	.await
3061	.unwrap();
3062	assert!(
3063	db.session_user_with_roots("bob-tok-4")
3064	.await
3065	.unwrap()
3066	.is_some()
3067	);
3068	}
3069
3070	#[tokio::test]
3071	async fn sessions_lifecycle() {
3072	let (db, admin) = db_with_admin().await;
3073	assert!(
3074	db.session_user_with_roots("ghost-token")
3075	.await
3076	.unwrap()
3077	.is_none()
3078	);
3079	db.create_session(admin.id, "tok1").await.unwrap();
3080	let (u, _) = db.session_user_with_roots("tok1").await.unwrap().unwrap();
3081	assert_eq!(u.id, admin.id);
3082	// Disabling the user invalidates existing sessions.
3083	edit(&db, admin.id, None, None, Some(false)).await;
3084	assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
3085	edit(&db, admin.id, None, None, Some(true)).await;
3086	assert!(db.session_user_with_roots("tok1").await.unwrap().is_some());
3087	db.delete_session("tok1").await.unwrap();
3088	assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
3089	}
3090
3091	#[tokio::test]
3092	async fn user_crud_and_roots() {
3093	let (db, _admin) = db_with_admin().await;
3094	let h = crate::auth::hash_password("bobpass1").unwrap();
3095	let bob = db
3096	.create_user("bob", &h, false, &[("docs".into(), Mode::Rw)])
3097	.await
3098	.unwrap();
3099	assert!(!bob.is_admin);
3100	assert!(bob.active);
3101
3102	// Duplicate name (case-insensitive) is rejected.
3103	let h2 = crate::auth::hash_password("carolpass1").unwrap();
3104	assert!(db.create_user("BOB", &h2, false, &[]).await.is_err());
3105	assert!(db.create_user("carol", &h2, false, &[]).await.is_ok());
3106
3107	// Lookup helpers.
3108	assert_eq!(
3109	db.find_user_by_name("Bob").await.unwrap().unwrap().id,
3110	bob.id
3111	);
3112	assert_eq!(
3113	db.find_user_by_id(bob.id).await.unwrap().unwrap().name,
3114	"bob"
3115	);
3116	assert!(db.find_user_by_name("dave").await.unwrap().is_none());
3117	assert_eq!(db.all_users_with_roots().await.unwrap().len(), 3);
3118
3119	// Root replacement semantics.
3120	let roots = db.user_roots(bob.id).await.unwrap();
3121	assert_eq!(roots.len(), 1);
3122	db.update_user(
3123	bob.id,
3124	None,
3125	None,
3126	None,
3127	Some(&[(".".into(), Mode::Ro), ("docs".into(), Mode::Rw)]),
3128	)
3129	.await
3130	.unwrap();
3131	let roots = db.user_roots(bob.id).await.unwrap();
3132	assert_eq!(roots.len(), 2);
3133	assert!(roots.iter().any(\|r\| r.path == "." && r.mode == Mode::Ro));
3134	db.update_user(bob.id, None, None, None, Some(&[]))
3135	.await
3136	.unwrap();
3137	assert!(db.user_roots(bob.id).await.unwrap().is_empty());
3138
3139	// Password update.
3140	let new_h = crate::auth::hash_password("bobpass2").unwrap();
3141	edit(&db, bob.id, Some(&new_h), None, None).await;
3142	assert!(
3143	db.verify_password("bob", "bobpass1")
3144	.await
3145	.unwrap()
3146	.is_none()
3147	);
3148	assert!(
3149	db.verify_password("bob", "bobpass2")
3150	.await
3151	.unwrap()
3152	.is_some()
3153	);
3154
3155	// Admin flag + count (only active admins count).
3156	edit(&db, bob.id, None, Some(true), None).await;
3157	assert_eq!(db.count_admins().await.unwrap(), 2);
3158	edit(&db, bob.id, None, None, Some(false)).await;
3159	assert_eq!(db.count_admins().await.unwrap(), 1);
3160	edit(&db, bob.id, None, Some(false), None).await;
3161
3162	// Deletion.
3163	assert!(db.delete_user(bob.id, &[]).await.unwrap());
3164	assert!(db.find_user_by_id(bob.id).await.unwrap().is_none());
3165	assert!(!db.delete_user(bob.id, &[]).await.unwrap());
3166	assert_eq!(db.user_count().await.unwrap(), 2);
3167	}
3168
3169	fn share_row(expires_at: Option<&str>) -> ShareRow {
3170	ShareRow {
3171	id: 1,
3172	token: "t".into(),
3173	creator_id: 1,
3174	target: "docs".into(),
3175	is_file: false,
3176	mode: Mode::Ro,
3177	created_at: "2024-01-01T00:00:00Z".into(),
3178	expires_at: expires_at.map(str::to_string),
3179	password_hash: None,
3180	}
3181	}
3182
3183	#[test]
3184	fn share_expiry_logic() {
3185	assert!(!share_row(None).is_expired());
3186	assert!(!share_row(Some("2999-01-01T00:00:00Z")).is_expired());
3187	assert!(share_row(Some("2000-01-01T00:00:00Z")).is_expired());
3188	// Unparseable expiry → treated as not expired (fail open for reads).
3189	assert!(!share_row(Some("not-a-date")).is_expired());
3190	}
3191
3192	#[tokio::test]
3193	async fn shares_crud() {
3194	let (db, admin) = db_with_admin().await;
3195	let s1 = db
3196	.create_share(admin.id, "tok-a", "docs", false, Mode::Ro, None, None)
3197	.await
3198	.unwrap();
3199	let s2 = db
3200	.create_share(
3201	admin.id,
3202	"tok-b",
3203	"file.txt",
3204	true,
3205	Mode::Rw,
3206	Some("2999-01-01T00:00:00Z"),
3207	None,
3208	)
3209	.await
3210	.unwrap();
3211	assert!(s2.id > s1.id);
3212
3213	let found = db.share_by_token("tok-b").await.unwrap().unwrap();
3214	assert!(found.is_file);
3215	assert_eq!(found.mode, Mode::Rw);
3216	assert!(db.share_by_token("nope").await.unwrap().is_none());
3217
3218	// Listed newest-first.
3219	let list = db.user_shares(admin.id).await.unwrap();
3220	assert_eq!(list.len(), 2);
3221	assert_eq!(list[0].id, s2.id);
3222	// Other users see nothing.
3223	let h = crate::auth::hash_password("bobpass1").unwrap();
3224	let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
3225	assert!(db.user_shares(bob.id).await.unwrap().is_empty());
3226
3227	// Only the creator can delete.
3228	assert!(!db.delete_share(s1.id, bob.id).await.unwrap());
3229	assert!(db.delete_share(s1.id, admin.id).await.unwrap());
3230	assert!(db.share_by_token("tok-a").await.unwrap().is_none());
3231	assert!(!db.delete_share(s1.id, admin.id).await.unwrap());
3232	}
3233
3234	/// The sweep decides which timestamps are past, and `expires_at` is
3235	/// stored in whatever RFC 3339 shape the client sent.
3236	#[tokio::test]
3237	async fn the_sweep_drops_expired_shares_and_stale_unlocks() {
3238	let (db, admin) = db_with_admin().await;
3239	let make = async \|token: &str, expires: Option<&str>\| {
3240	db.create_share(admin.id, token, "docs", false, Mode::Ro, expires, None)
3241	.await
3242	.unwrap()
3243	};
3244	let past_offset = make("gone-offset", Some("2000-01-01T00:00:00+02:00")).await;
3245	make("gone-utc", Some("2000-01-01T00:00:00Z")).await;
3246	let future = make("stays-future", Some("2999-01-01T00:00:00Z")).await;
3247	make("stays-forever", None).await;
3248	// `is_expired` keeps an unreadable timestamp; the sweep must agree,
3249	// or the two disagree about the same row.
3250	make("stays-garbage", Some("not-a-date")).await;
3251
3252	let fresh = db.create_share_unlock(future.id).await.unwrap();
3253	let stale = db.create_share_unlock(future.id).await.unwrap();
3254	let doomed = db.create_share_unlock(past_offset.id).await.unwrap();
3255	{
3256	let c = db.conn.lock().await;
3257	c.execute(
3258	"UPDATE share_unlocks SET created_at = '2000-01-01T00:00:00Z' WHERE token = ?1",
3259	[&stale],
3260	)
3261	.unwrap();
3262	}
3263
3264	let (shares, unlocks) = db.sweep().await.unwrap();
3265	assert_eq!(shares, 2, "only the two past timestamps");
3266	// The stale one, plus the cascade from the expired share it opened.
3267	assert_eq!(unlocks, 1, "the cascade runs with the share, not here");
3268
3269	for token in ["gone-offset", "gone-utc"] {
3270	assert!(db.share_by_token(token).await.unwrap().is_none(), "{token}");
3271	}
3272	for token in ["stays-future", "stays-forever", "stays-garbage"] {
3273	assert!(db.share_by_token(token).await.unwrap().is_some(), "{token}");
3274	}
3275
3276	assert!(db.share_unlock_valid(&fresh, future.id).await.unwrap());
3277	assert!(!db.share_unlock_valid(&stale, future.id).await.unwrap());
3278	assert!(
3279	!db.share_unlock_valid(&doomed, past_offset.id)
3280	.await
3281	.unwrap(),
3282	"an unlock must not outlive the share it opened"
3283	);
3284
3285	assert_eq!(
3286	db.sweep().await.unwrap(),
3287	(0, 0),
3288	"a second pass is a no-op"
3289	);
3290	}
3291
3292	/// The unlock token is what a visitor's cookie carries, so an unlock
3293	/// that opened the wrong share would be a full bypass of the password.
3294	#[tokio::test]
3295	async fn share_unlocks_are_bound_to_one_share() {
3296	let (db, admin) = db_with_admin().await;
3297	let a = db
3298	.create_share(
3299	admin.id,
3300	"tok-a",
3301	"docs",
3302	false,
3303	Mode::Ro,
3304	None,
3305	Some("hash"),
3306	)
3307	.await
3308	.unwrap();
3309	let b = db
3310	.create_share(
3311	admin.id,
3312	"tok-b",
3313	"other",
3314	false,
3315	Mode::Ro,
3316	None,
3317	Some("hash"),
3318	)
3319	.await
3320	.unwrap();
3321	assert_eq!(
3322	db.share_by_token("tok-a")
3323	.await
3324	.unwrap()
3325	.unwrap()
3326	.password_hash,
3327	Some("hash".to_string())
3328	);
3329
3330	let unlock = db.create_share_unlock(a.id).await.unwrap();
3331	assert!(db.share_unlock_valid(&unlock, a.id).await.unwrap());
3332	assert!(!db.share_unlock_valid(&unlock, b.id).await.unwrap());
3333	assert!(!db.share_unlock_valid("nonsense", a.id).await.unwrap());
3334
3335	// Deleting the share takes its unlocks with it, so a re-created
3336	// share that happened to reuse the id could not inherit them.
3337	assert!(db.delete_share(a.id, admin.id).await.unwrap());
3338	assert!(!db.share_unlock_valid(&unlock, a.id).await.unwrap());
3339	}
3340
3341	#[tokio::test]
3342	async fn revoking_a_path_takes_its_descendants_only() {
3343	let (db, admin) = db_with_admin().await;
3344	let mk = async \|token: &str, target: &str\| {
3345	db.create_share(admin.id, token, target, false, Mode::Ro, None, None)
3346	.await
3347	.unwrap();
3348	};
3349	mk("t-self", "docs").await;
3350	mk("t-child", "docs/a.txt").await;
3351	mk("t-deep", "docs/inner/b.txt").await;
3352	// A sibling whose name merely starts with "docs" must survive.
3353	mk("t-sibling", "docs2/c.txt").await;
3354	mk("t-other", "src").await;
3355	// SQL wildcards in a path are literal characters, not patterns.
3356	mk("t-wild", "do%s/d.txt").await;
3357
3358	assert_eq!(db.revoke_shares_at("docs").await.unwrap(), 3);
3359	for gone in ["t-self", "t-child", "t-deep"] {
3360	assert!(db.share_by_token(gone).await.unwrap().is_none(), "{gone}");
3361	}
3362	for kept in ["t-sibling", "t-other", "t-wild"] {
3363	assert!(db.share_by_token(kept).await.unwrap().is_some(), "{kept}");
3364	}
3365	// Revoking a path nobody shared is a no-op, not an error.
3366	assert_eq!(db.revoke_shares_at("nothing/here").await.unwrap(), 0);
3367	}
3368
3369	#[tokio::test]
3370	async fn settings_round_trip() {
3371	let (db, _admin) = db_with_admin().await;
3372	assert!(!db.allow_writable_shares().await.unwrap());
3373	db.set_allow_writable_shares(true).await.unwrap();
3374	assert!(db.allow_writable_shares().await.unwrap());
3375	// Upsert semantics.
3376	db.set_allow_writable_shares(false).await.unwrap();
3377	assert!(!db.allow_writable_shares().await.unwrap());
3378	// Generic get/set.
3379	db.set_setting("custom", "v").await.unwrap();
3380	assert_eq!(
3381	db.get_setting("custom").await.unwrap().as_deref(),
3382	Some("v")
3383	);
3384	assert_eq!(db.get_setting("missing").await.unwrap(), None);
3385	}
3386	}
3387