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 = 1;
7
8#[derive(Debug, Clone)]
9pub struct User {
10 pub id: i64,
11 pub name: String,
12 pub is_admin: bool,
13}
14
15#[derive(Debug, Clone)]
16pub struct RootRow {
17 pub id: i64,
18 /// Path relative to the server root; "." means the whole root.
19 pub path: String,
20 /// "rw" or "ro"
21 pub mode: String,
22}
23
24#[derive(Debug, Clone)]
25pub struct ShareRow {
26 pub id: i64,
27 pub token: String,
28 pub creator_id: i64,
29 /// Path of the shared item relative to the server root.
30 pub target: String,
31 pub is_file: bool,
32 /// "rw" or "ro"
33 pub mode: String,
34 pub created_at: String,
35 pub expires_at: Option<String>,
36}
37
38impl ShareRow {
39 pub fn is_expired(&self) -> bool {
40 match &self.expires_at {
41 Some(e) => chrono::DateTime::parse_from_rfc3339(e)
42 .map(|t| chrono::Utc::now() >= t.with_timezone(&chrono::Utc))
43 .unwrap_or(false),
44 None => false,
45 }
46 }
47}
48
49#[derive(Clone)]
50pub struct Db(Arc<tokio::sync::Mutex<Connection>>);
51
52impl std::fmt::Debug for Db {
53 fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
54 f.debug_struct("Db").finish()
55 }
56}
57
58impl Db {
59 pub async fn open(path: &Path) -> anyhow::Result<Self> {
60 if let Some(parent) = path.parent() {
61 if !parent.as_os_str().is_empty() {
62 std::fs::create_dir_all(parent)?;
63 }
64 }
65 let conn = Connection::open(path)?;
66 conn.pragma_update(None, "journal_mode", "WAL")?;
67 conn.pragma_update(None, "foreign_keys", "ON")?;
68 conn.pragma_update(None, "busy_timeout", "5000")?;
69 Self::migrate(&conn)?;
70 Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
71 }
72
73 fn migrate(conn: &Connection) -> rusqlite::Result<()> {
74 conn.execute(
75 "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)",
76 [],
77 )?;
78 let version: i64 = conn
79 .query_row("SELECT value FROM meta WHERE key = 'schema_version'", [], |r| {
80 r.get::<_, String>(0)
81 })
82 .optional()?
83 .and_then(|v| v.parse().ok())
84 .unwrap_or(0);
85
86 if version < 1 {
87 conn.execute_batch(SCHEMA_V1)?;
88 conn.execute(
89 "INSERT OR REPLACE INTO meta (key, value) VALUES ('schema_version', ?1)",
90 [SCHEMA_VERSION.to_string()],
91 )?;
92 }
93 Ok(())
94 }
95
96 // ---------- users ----------
97
98 pub async fn user_count(&self) -> i64 {
99 let c = self.0.lock().await;
100 c.query_row("SELECT COUNT(*) FROM users", [], |r| r.get(0))
101 .unwrap_or(0)
102 }
103
104 /// Create the first admin account with the whole root visible (read-write).
105 /// Only valid while no users exist (enforced by the caller).
106 pub async fn create_admin(&self, name: &str, pass_hash: &str) -> Result<User, rusqlite::Error> {
107 let mut c = self.0.lock().await;
108 let tx = c.transaction()?;
109 tx.execute(
110 "INSERT INTO users (name, pass_hash, is_admin, created_at)
111 VALUES (?1, ?2, 1, ?3)",
112 params![name, pass_hash, now()],
113 )?;
114 let user_id = tx.last_insert_rowid();
115 tx.execute(
116 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, '.', 'rw')",
117 params![user_id],
118 )?;
119 tx.commit()?;
120 Ok(User {
121 id: user_id,
122 name: name.to_string(),
123 is_admin: true,
124 })
125 }
126
127 pub async fn verify_password(&self, name: &str, password: &str) -> Option<User> {
128 let c = self.0.lock().await;
129 let row: Option<(i64, String, bool, String)> = c
130 .query_row(
131 "SELECT id, name, is_admin != 0, pass_hash FROM users WHERE name = ?1",
132 [name],
133 |r| {
134 Ok((
135 r.get(0)?,
136 r.get(1)?,
137 r.get(2)?,
138 r.get(3)?,
139 ))
140 },
141 )
142 .optional()
143 .ok()
144 .flatten();
145 let (id, name, is_admin, hash) = row?;
146 crate::auth::verify_password(password, &hash).then_some(User {
147 id,
148 name,
149 is_admin,
150 })
151 }
152
153 // ---------- sessions ----------
154
155 pub async fn create_session(&self, user_id: i64, token: &str) -> Result<(), rusqlite::Error> {
156 let c = self.0.lock().await;
157 c.execute(
158 "INSERT INTO sessions (token, user_id, created_at, last_seen_at)
159 VALUES (?1, ?2, ?3, ?4)",
160 params![token, user_id, now(), now()],
161 )?;
162 Ok(())
163 }
164
165 pub async fn delete_session(&self, token: &str) -> Result<(), rusqlite::Error> {
166 let c = self.0.lock().await;
167 c.execute("DELETE FROM sessions WHERE token = ?1", [token])?;
168 Ok(())
169 }
170
171 pub async fn session_user(&self, token: &str) -> Option<User> {
172 let c = self.0.lock().await;
173 c.query_row(
174 "SELECT u.id, u.name, u.is_admin != 0
175 FROM sessions s JOIN users u ON u.id = s.user_id
176 WHERE s.token = ?1",
177 [token],
178 |r| {
179 Ok(User {
180 id: r.get(0)?,
181 name: r.get(1)?,
182 is_admin: r.get(2)?,
183 })
184 },
185 )
186 .ok()
187 }
188
189 // ---------- roots ----------
190
191 pub async fn user_roots(&self, user_id: i64) -> Vec<RootRow> {
192 let c = self.0.lock().await;
193 let mut out = Vec::new();
194 let Ok(mut stmt) = c
195 .prepare("SELECT id, path, mode FROM user_roots WHERE user_id = ?1 ORDER BY id")
196 else {
197 return out;
198 };
199 if let Ok(rows) =
200 stmt.query_map([user_id], |r| {
201 Ok(RootRow {
202 id: r.get(0)?,
203 path: r.get(1)?,
204 mode: r.get(2)?,
205 })
206 })
207 {
208 out.extend(rows.flatten());
209 }
210 out
211 }
212
213 // ---------- shares ----------
214
215 pub async fn create_share(
216 &self,
217 creator_id: i64,
218 token: &str,
219 target: &str,
220 is_file: bool,
221 mode: &str,
222 expires_at: Option<&str>,
223 ) -> Result<ShareRow, rusqlite::Error> {
224 let c = self.0.lock().await;
225 c.execute(
226 "INSERT INTO shares (token, creator_id, target, is_file, mode, created_at, expires_at)
227 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
228 params![token, creator_id, target, is_file as i64, mode, now(), expires_at],
229 )?;
230 let id = c.last_insert_rowid();
231 Ok(ShareRow {
232 id,
233 token: token.to_string(),
234 creator_id,
235 target: target.to_string(),
236 is_file,
237 mode: mode.to_string(),
238 created_at: now(),
239 expires_at: expires_at.map(|s| s.to_string()),
240 })
241 }
242
243 pub async fn share_by_token(&self, token: &str) -> Option<ShareRow> {
244 let c = self.0.lock().await;
245 c.query_row(&*SHARE_BY_TOKEN, [token], map_share).ok()
246 }
247
248 pub async fn user_shares(&self, creator_id: i64) -> Vec<ShareRow> {
249 let c = self.0.lock().await;
250 let mut out = Vec::new();
251 if let Ok(mut stmt) = c.prepare(&*USER_SHARES) {
252 if let Ok(rows) = stmt.query_map([creator_id], map_share) {
253 out.extend(rows.flatten());
254 }
255 }
256 out
257 }
258
259 pub async fn delete_share(&self, id: i64, creator_id: i64) -> bool {
260 let c = self.0.lock().await;
261 c.execute(
262 "DELETE FROM shares WHERE id = ?1 AND creator_id = ?2",
263 params![id, creator_id],
264 )
265 .ok()
266 .map(|n| n > 0)
267 .unwrap_or(false)
268 }
269
270 // ---------- settings ----------
271
272 pub async fn get_setting(&self, key: &str) -> Option<String> {
273 let c = self.0.lock().await;
274 c.query_row("SELECT value FROM settings WHERE key = ?1", [key], |r| {
275 r.get(0)
276 })
277 .ok()
278 }
279
280 #[allow(dead_code)]
281 pub async fn set_setting(&self, key: &str, value: &str) -> Result<(), rusqlite::Error> {
282 let c = self.0.lock().await;
283 c.execute(
284 "INSERT INTO settings (key, value) VALUES (?1, ?2)
285 ON CONFLICT(key) DO UPDATE SET value = ?2",
286 params![key, value],
287 )?;
288 Ok(())
289 }
290
291 /// Whether users may create writable (read-write) shares. Off by default;
292 /// the admin setting gates it.
293 pub async fn allow_writable_shares(&self) -> bool {
294 self.get_setting("allow_writable_shares").await.as_deref() == Some("1")
295 }
296}
297
298const SHARE_COLS: &str =
299 "id, token, creator_id, target, is_file, mode, created_at, expires_at";
300const SHARE_BY_TOKEN: std::sync::LazyLock<String> =
301 std::sync::LazyLock::new(|| format!("SELECT {SHARE_COLS} FROM shares WHERE token = ?1"));
302const USER_SHARES: std::sync::LazyLock<String> =
303 std::sync::LazyLock::new(|| {
304 format!("SELECT {SHARE_COLS} FROM shares WHERE creator_id = ?1 ORDER BY id DESC")
305 });
306
307fn map_share(r: &rusqlite::Row) -> rusqlite::Result<ShareRow> {
308 Ok(ShareRow {
309 id: r.get(0)?,
310 token: r.get(1)?,
311 creator_id: r.get(2)?,
312 target: r.get(3)?,
313 is_file: r.get::<_, i64>(4)? != 0,
314 mode: r.get(5)?,
315 created_at: r.get(6)?,
316 expires_at: r.get(7)?,
317 })
318}
319
320fn now() -> String {
321 chrono::Utc::now().to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
322}
323
324const SCHEMA_V1: &str = r#"
325CREATE TABLE IF NOT EXISTS users (
326 id INTEGER PRIMARY KEY AUTOINCREMENT,
327 name TEXT NOT NULL UNIQUE COLLATE NOCASE,
328 pass_hash TEXT NOT NULL,
329 is_admin INTEGER NOT NULL DEFAULT 0,
330 created_at TEXT NOT NULL
331);
332
333CREATE TABLE IF NOT EXISTS user_roots (
334 id INTEGER PRIMARY KEY AUTOINCREMENT,
335 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
336 path TEXT NOT NULL,
337 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
338 UNIQUE (user_id, path)
339);
340
341CREATE TABLE IF NOT EXISTS shares (
342 id INTEGER PRIMARY KEY AUTOINCREMENT,
343 token TEXT NOT NULL UNIQUE,
344 creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
345 target TEXT NOT NULL,
346 is_file INTEGER NOT NULL,
347 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
348 created_at TEXT NOT NULL,
349 expires_at TEXT
350);
351
352CREATE TABLE IF NOT EXISTS sessions (
353 token TEXT PRIMARY KEY,
354 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
355 created_at TEXT NOT NULL,
356 last_seen_at TEXT NOT NULL
357);
358
359CREATE TABLE IF NOT EXISTS settings (
360 key TEXT PRIMARY KEY,
361 value TEXT NOT NULL
362);
363INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
364"#;
365