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