dovenest

dovenest/server/src/db.rs
db.rs
⎇
Raw
1use std::path::Path;
2use std::sync::Arc;
3
4use rusqlite::{params, Connection, OptionalExtension};
5
6const SCHEMA_VERSION: i64 = 2;
7
8#[derive(Debug, Clone)]
9pub struct User {
10 pub id: i64,
11 pub name: String,
12 pub is_admin: bool,
13 /// Disabled users cannot sign in and their sessions are rejected.
14 pub active: bool,
15}
16
17#[derive(Debug, Clone)]
18pub struct RootRow {
19 pub id: i64,
20 /// Path relative to the server root; "." means the whole root.
21 pub path: String,
22 /// "rw" or "ro"
23 pub mode: String,
24}
25
26#[derive(Debug, Clone)]
27pub struct ShareRow {
28 pub id: i64,
29 pub token: String,
30 pub creator_id: i64,
31 /// Path of the shared item relative to the server root.
32 pub target: String,
33 pub is_file: bool,
34 /// "rw" or "ro"
35 pub mode: String,
36 pub created_at: String,
37 pub expires_at: Option<String>,
38}
39
40impl ShareRow {
41 pub fn is_expired(&self) -> bool {
42 match &self.expires_at {
43 Some(e) => chrono::DateTime::parse_from_rfc3339(e)
44 .map(|t| chrono::Utc::now() >= t.with_timezone(&chrono::Utc))
45 .unwrap_or(false),
46 None => false,
47 }
48 }
49}
50
51#[derive(Clone)]
52pub struct Db(Arc<tokio::sync::Mutex<Connection>>);
53
54impl std::fmt::Debug for Db {
55 fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
56 f.debug_struct("Db").finish()
57 }
58}
59
60impl Db {
61 pub async fn open(path: &Path) -> anyhow::Result<Self> {
62 if let Some(parent) = path.parent() {
63 if !parent.as_os_str().is_empty() {
64 std::fs::create_dir_all(parent)?;
65 }
66 }
67 let conn = Connection::open(path)?;
68 conn.pragma_update(None, "journal_mode", "WAL")?;
69 conn.pragma_update(None, "foreign_keys", "ON")?;
70 conn.pragma_update(None, "busy_timeout", "5000")?;
71 Self::migrate(&conn)?;
72 Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
73 }
74
75 fn migrate(conn: &Connection) -> rusqlite::Result<()> {
76 conn.execute(
77 "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)",
78 [],
79 )?;
80 let version: i64 = conn
81 .query_row("SELECT value FROM meta WHERE key = 'schema_version'", [], |r| {
82 r.get::<_, String>(0)
83 })
84 .optional()?
85 .and_then(|v| v.parse().ok())
86 .unwrap_or(0);
87
88 if version < 1 {
89 conn.execute_batch(SCHEMA_V1)?;
90 }
91 if version < 2 {
92 // User management (M7): a disabled flag so admins can suspend
93 // accounts without deleting them.
94 conn.execute_batch(
95 "ALTER TABLE users ADD COLUMN active INTEGER NOT NULL DEFAULT 1",
96 )?;
97 }
98 conn.execute(
99 "INSERT OR REPLACE INTO meta (key, value) VALUES ('schema_version', ?1)",
100 [SCHEMA_VERSION.to_string()],
101 )?;
102 Ok(())
103 }
104
105 // ---------- users ----------
106
107 pub async fn user_count(&self) -> i64 {
108 let c = self.0.lock().await;
109 c.query_row("SELECT COUNT(*) FROM users", [], |r| r.get(0))
110 .unwrap_or(0)
111 }
112
113 /// Create the first admin account with the whole root visible (read-write).
114 /// Only valid while no users exist (enforced by the caller).
115 pub async fn create_admin(&self, name: &str, pass_hash: &str) -> Result<User, rusqlite::Error> {
116 let mut c = self.0.lock().await;
117 let tx = c.transaction()?;
118 tx.execute(
119 "INSERT INTO users (name, pass_hash, is_admin, created_at)
120 VALUES (?1, ?2, 1, ?3)",
121 params![name, pass_hash, now()],
122 )?;
123 let user_id = tx.last_insert_rowid();
124 tx.execute(
125 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, '.', 'rw')",
126 params![user_id],
127 )?;
128 tx.commit()?;
129 Ok(User {
130 id: user_id,
131 name: name.to_string(),
132 is_admin: true,
133 active: true,
134 })
135 }
136
137 pub async fn verify_password(&self, name: &str, password: &str) -> Option<User> {
138 let c = self.0.lock().await;
139 let row: Option<(i64, String, bool, String, bool)> = c
140 .query_row(
141 "SELECT id, name, is_admin != 0, pass_hash, active != 0 FROM users WHERE name = ?1",
142 [name],
143 |r| {
144 Ok((
145 r.get(0)?,
146 r.get(1)?,
147 r.get(2)?,
148 r.get(3)?,
149 r.get(4)?,
150 ))
151 },
152 )
153 .optional()
154 .ok()
155 .flatten();
156 let (id, name, is_admin, hash, active) = row?;
157 if !active {
158 return None;
159 }
160 crate::auth::verify_password(password, &hash).then_some(User {
161 id,
162 name,
163 is_admin,
164 active,
165 })
166 }
167
168 // ---------- sessions ----------
169
170 pub async fn create_session(&self, user_id: i64, token: &str) -> Result<(), rusqlite::Error> {
171 let c = self.0.lock().await;
172 c.execute(
173 "INSERT INTO sessions (token, user_id, created_at, last_seen_at)
174 VALUES (?1, ?2, ?3, ?4)",
175 params![token, user_id, now(), now()],
176 )?;
177 Ok(())
178 }
179
180 pub async fn delete_session(&self, token: &str) -> Result<(), rusqlite::Error> {
181 let c = self.0.lock().await;
182 c.execute("DELETE FROM sessions WHERE token = ?1", [token])?;
183 Ok(())
184 }
185
186 pub async fn session_user(&self, token: &str) -> Option<User> {
187 let c = self.0.lock().await;
188 c.query_row(
189 "SELECT u.id, u.name, u.is_admin != 0, u.active != 0
190 FROM sessions s JOIN users u ON u.id = s.user_id
191 WHERE s.token = ?1 AND u.active = 1",
192 [token],
193 |r| {
194 Ok(User {
195 id: r.get(0)?,
196 name: r.get(1)?,
197 is_admin: r.get(2)?,
198 active: r.get(3)?,
199 })
200 },
201 )
202 .ok()
203 }
204
205 // ---------- roots ----------
206
207 pub async fn user_roots(&self, user_id: i64) -> Vec<RootRow> {
208 let c = self.0.lock().await;
209 let mut out = Vec::new();
210 let Ok(mut stmt) = c
211 .prepare("SELECT id, path, mode FROM user_roots WHERE user_id = ?1 ORDER BY id")
212 else {
213 return out;
214 };
215 if let Ok(rows) =
216 stmt.query_map([user_id], |r| {
217 Ok(RootRow {
218 id: r.get(0)?,
219 path: r.get(1)?,
220 mode: r.get(2)?,
221 })
222 })
223 {
224 out.extend(rows.flatten());
225 }
226 out
227 }
228
229 // ---------- admin: user management (M7) ----------
230
231 pub async fn all_users(&self) -> Vec<User> {
232 let c = self.0.lock().await;
233 let mut out = Vec::new();
234 if let Ok(mut stmt) = c.prepare(
235 "SELECT id, name, is_admin != 0, active != 0 FROM users ORDER BY id",
236 ) {
237 if let Ok(rows) = stmt.query_map([], |r| {
238 Ok(User {
239 id: r.get(0)?,
240 name: r.get(1)?,
241 is_admin: r.get(2)?,
242 active: r.get(3)?,
243 })
244 }) {
245 out.extend(rows.flatten());
246 }
247 }
248 out
249 }
250
251 pub async fn find_user_by_id(&self, id: i64) -> Option<User> {
252 let c = self.0.lock().await;
253 c.query_row(
254 "SELECT id, name, is_admin != 0, active != 0 FROM users WHERE id = ?1",
255 [id],
256 |r| {
257 Ok(User {
258 id: r.get(0)?,
259 name: r.get(1)?,
260 is_admin: r.get(2)?,
261 active: r.get(3)?,
262 })
263 },
264 )
265 .ok()
266 }
267
268 pub async fn find_user_by_name(&self, name: &str) -> Option<User> {
269 let c = self.0.lock().await;
270 c.query_row(
271 "SELECT id, name, is_admin != 0, active != 0 FROM users WHERE name = ?1",
272 [name],
273 |r| {
274 Ok(User {
275 id: r.get(0)?,
276 name: r.get(1)?,
277 is_admin: r.get(2)?,
278 active: r.get(3)?,
279 })
280 },
281 )
282 .ok()
283 }
284
285 pub async fn count_admins(&self) -> i64 {
286 let c = self.0.lock().await;
287 c.query_row(
288 "SELECT COUNT(*) FROM users WHERE is_admin = 1 AND active = 1",
289 [],
290 |r| r.get(0),
291 )
292 .unwrap_or(0)
293 }
294
295 /// Create a user with the given roots (path, mode) pairs.
296 pub async fn create_user(
297 &self,
298 name: &str,
299 pass_hash: &str,
300 is_admin: bool,
301 roots: &[(String, String)],
302 ) -> Result<User, rusqlite::Error> {
303 let mut c = self.0.lock().await;
304 let tx = c.transaction()?;
305 tx.execute(
306 "INSERT INTO users (name, pass_hash, is_admin, active, created_at)
307 VALUES (?1, ?2, ?3, 1, ?4)",
308 params![name, pass_hash, is_admin as i64, now()],
309 )?;
310 let user_id = tx.last_insert_rowid();
311 for (path, mode) in roots {
312 tx.execute(
313 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
314 params![user_id, path, mode],
315 )?;
316 }
317 tx.commit()?;
318 Ok(User {
319 id: user_id,
320 name: name.to_string(),
321 is_admin,
322 active: true,
323 })
324 }
325
326 pub async fn update_user_password(
327 &self,
328 id: i64,
329 pass_hash: &str,
330 ) -> Result<(), rusqlite::Error> {
331 let c = self.0.lock().await;
332 c.execute(
333 "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
334 params![pass_hash, id],
335 )?;
336 Ok(())
337 }
338
339 pub async fn set_user_admin(&self, id: i64, is_admin: bool) -> Result<(), rusqlite::Error> {
340 let c = self.0.lock().await;
341 c.execute(
342 "UPDATE users SET is_admin = ?1 WHERE id = ?2",
343 params![is_admin as i64, id],
344 )?;
345 Ok(())
346 }
347
348 pub async fn set_user_active(&self, id: i64, active: bool) -> Result<(), rusqlite::Error> {
349 let c = self.0.lock().await;
350 c.execute(
351 "UPDATE users SET active = ?1 WHERE id = ?2",
352 params![active as i64, id],
353 )?;
354 Ok(())
355 }
356
357 pub async fn delete_user(&self, id: i64) -> bool {
358 let c = self.0.lock().await;
359 c.execute("DELETE FROM users WHERE id = ?1", [id])
360 .ok()
361 .map(|n| n > 0)
362 .unwrap_or(false)
363 }
364
365 /// Replace a user's roots with the given (path, mode) pairs.
366 pub async fn set_user_roots(
367 &self,
368 user_id: i64,
369 roots: &[(String, String)],
370 ) -> Result<(), rusqlite::Error> {
371 let mut c = self.0.lock().await;
372 let tx = c.transaction()?;
373 tx.execute("DELETE FROM user_roots WHERE user_id = ?1", [user_id])?;
374 for (path, mode) in roots {
375 tx.execute(
376 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
377 params![user_id, path, mode],
378 )?;
379 }
380 tx.commit()?;
381 Ok(())
382 }
383
384 // ---------- shares ----------
385
386 pub async fn create_share(
387 &self,
388 creator_id: i64,
389 token: &str,
390 target: &str,
391 is_file: bool,
392 mode: &str,
393 expires_at: Option<&str>,
394 ) -> Result<ShareRow, rusqlite::Error> {
395 let c = self.0.lock().await;
396 c.execute(
397 "INSERT INTO shares (token, creator_id, target, is_file, mode, created_at, expires_at)
398 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
399 params![token, creator_id, target, is_file as i64, mode, now(), expires_at],
400 )?;
401 let id = c.last_insert_rowid();
402 Ok(ShareRow {
403 id,
404 token: token.to_string(),
405 creator_id,
406 target: target.to_string(),
407 is_file,
408 mode: mode.to_string(),
409 created_at: now(),
410 expires_at: expires_at.map(|s| s.to_string()),
411 })
412 }
413
414 pub async fn share_by_token(&self, token: &str) -> Option<ShareRow> {
415 let c = self.0.lock().await;
416 c.query_row(&*SHARE_BY_TOKEN, [token], map_share).ok()
417 }
418
419 pub async fn user_shares(&self, creator_id: i64) -> Vec<ShareRow> {
420 let c = self.0.lock().await;
421 let mut out = Vec::new();
422 if let Ok(mut stmt) = c.prepare(&*USER_SHARES) {
423 if let Ok(rows) = stmt.query_map([creator_id], map_share) {
424 out.extend(rows.flatten());
425 }
426 }
427 out
428 }
429
430 pub async fn delete_share(&self, id: i64, creator_id: i64) -> bool {
431 let c = self.0.lock().await;
432 c.execute(
433 "DELETE FROM shares WHERE id = ?1 AND creator_id = ?2",
434 params![id, creator_id],
435 )
436 .ok()
437 .map(|n| n > 0)
438 .unwrap_or(false)
439 }
440
441 // ---------- settings ----------
442
443 pub async fn get_setting(&self, key: &str) -> Option<String> {
444 let c = self.0.lock().await;
445 c.query_row("SELECT value FROM settings WHERE key = ?1", [key], |r| {
446 r.get(0)
447 })
448 .ok()
449 }
450
451 pub async fn set_setting(&self, key: &str, value: &str) -> Result<(), rusqlite::Error> {
452 let c = self.0.lock().await;
453 c.execute(
454 "INSERT INTO settings (key, value) VALUES (?1, ?2)
455 ON CONFLICT(key) DO UPDATE SET value = ?2",
456 params![key, value],
457 )?;
458 Ok(())
459 }
460
461 /// Whether users may create writable (read-write) shares. Off by default;
462 /// the admin setting gates it.
463 pub async fn allow_writable_shares(&self) -> bool {
464 self.get_setting("allow_writable_shares").await.as_deref() == Some("1")
465 }
466
467 pub async fn set_allow_writable_shares(
468 &self,
469 v: bool,
470 ) -> Result<(), rusqlite::Error> {
471 self.set_setting("allow_writable_shares", if v { "1" } else { "0" })
472 .await
473 }
474}
475
476const SHARE_COLS: &str =
477 "id, token, creator_id, target, is_file, mode, created_at, expires_at";
478const SHARE_BY_TOKEN: std::sync::LazyLock<String> =
479 std::sync::LazyLock::new(|| format!("SELECT {SHARE_COLS} FROM shares WHERE token = ?1"));
480const USER_SHARES: std::sync::LazyLock<String> =
481 std::sync::LazyLock::new(|| {
482 format!("SELECT {SHARE_COLS} FROM shares WHERE creator_id = ?1 ORDER BY id DESC")
483 });
484
485fn map_share(r: &rusqlite::Row) -> rusqlite::Result<ShareRow> {
486 Ok(ShareRow {
487 id: r.get(0)?,
488 token: r.get(1)?,
489 creator_id: r.get(2)?,
490 target: r.get(3)?,
491 is_file: r.get::<_, i64>(4)? != 0,
492 mode: r.get(5)?,
493 created_at: r.get(6)?,
494 expires_at: r.get(7)?,
495 })
496}
497
498fn now() -> String {
499 chrono::Utc::now().to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
500}
501
502const SCHEMA_V1: &str = r#"
503CREATE TABLE IF NOT EXISTS users (
504 id INTEGER PRIMARY KEY AUTOINCREMENT,
505 name TEXT NOT NULL UNIQUE COLLATE NOCASE,
506 pass_hash TEXT NOT NULL,
507 is_admin INTEGER NOT NULL DEFAULT 0,
508 created_at TEXT NOT NULL
509);
510
511CREATE TABLE IF NOT EXISTS user_roots (
512 id INTEGER PRIMARY KEY AUTOINCREMENT,
513 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
514 path TEXT NOT NULL,
515 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
516 UNIQUE (user_id, path)
517);
518
519CREATE TABLE IF NOT EXISTS shares (
520 id INTEGER PRIMARY KEY AUTOINCREMENT,
521 token TEXT NOT NULL UNIQUE,
522 creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
523 target TEXT NOT NULL,
524 is_file INTEGER NOT NULL,
525 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
526 created_at TEXT NOT NULL,
527 expires_at TEXT
528);
529
530CREATE TABLE IF NOT EXISTS sessions (
531 token TEXT PRIMARY KEY,
532 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
533 created_at TEXT NOT NULL,
534 last_seen_at TEXT NOT NULL
535);
536
537CREATE TABLE IF NOT EXISTS settings (
538 key TEXT PRIMARY KEY,
539 value TEXT NOT NULL
540);
541INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
542"#;
543