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(Clone)]
25pub struct Db(Arc<tokio::sync::Mutex<Connection>>);
26
27impl std::fmt::Debug for Db {
28 fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
29 f.debug_struct("Db").finish()
30 }
31}
32
33impl Db {
34 pub async fn open(path: &Path) -> anyhow::Result<Self> {
35 if let Some(parent) = path.parent() {
36 if !parent.as_os_str().is_empty() {
37 std::fs::create_dir_all(parent)?;
38 }
39 }
40 let conn = Connection::open(path)?;
41 conn.pragma_update(None, "journal_mode", "WAL")?;
42 conn.pragma_update(None, "foreign_keys", "ON")?;
43 conn.pragma_update(None, "busy_timeout", "5000")?;
44 Self::migrate(&conn)?;
45 Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
46 }
47
48 fn migrate(conn: &Connection) -> rusqlite::Result<()> {
49 conn.execute(
50 "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)",
51 [],
52 )?;
53 let version: i64 = conn
54 .query_row("SELECT value FROM meta WHERE key = 'schema_version'", [], |r| {
55 r.get::<_, String>(0)
56 })
57 .optional()?
58 .and_then(|v| v.parse().ok())
59 .unwrap_or(0);
60
61 if version < 1 {
62 conn.execute_batch(SCHEMA_V1)?;
63 conn.execute(
64 "INSERT OR REPLACE INTO meta (key, value) VALUES ('schema_version', ?1)",
65 [SCHEMA_VERSION.to_string()],
66 )?;
67 }
68 Ok(())
69 }
70
71 // ---------- users ----------
72
73 pub async fn user_count(&self) -> i64 {
74 let c = self.0.lock().await;
75 c.query_row("SELECT COUNT(*) FROM users", [], |r| r.get(0))
76 .unwrap_or(0)
77 }
78
79 /// Create the first admin account with the whole root visible (read-write).
80 /// Only valid while no users exist (enforced by the caller).
81 pub async fn create_admin(&self, name: &str, pass_hash: &str) -> Result<User, rusqlite::Error> {
82 let mut c = self.0.lock().await;
83 let tx = c.transaction()?;
84 tx.execute(
85 "INSERT INTO users (name, pass_hash, is_admin, created_at)
86 VALUES (?1, ?2, 1, ?3)",
87 params![name, pass_hash, now()],
88 )?;
89 let user_id = tx.last_insert_rowid();
90 tx.execute(
91 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, '.', 'rw')",
92 params![user_id],
93 )?;
94 tx.commit()?;
95 Ok(User {
96 id: user_id,
97 name: name.to_string(),
98 is_admin: true,
99 })
100 }
101
102 pub async fn verify_password(&self, name: &str, password: &str) -> Option<User> {
103 let c = self.0.lock().await;
104 let row: Option<(i64, String, bool, String)> = c
105 .query_row(
106 "SELECT id, name, is_admin != 0, pass_hash FROM users WHERE name = ?1",
107 [name],
108 |r| {
109 Ok((
110 r.get(0)?,
111 r.get(1)?,
112 r.get(2)?,
113 r.get(3)?,
114 ))
115 },
116 )
117 .optional()
118 .ok()
119 .flatten();
120 let (id, name, is_admin, hash) = row?;
121 crate::auth::verify_password(password, &hash).then_some(User {
122 id,
123 name,
124 is_admin,
125 })
126 }
127
128 // ---------- sessions ----------
129
130 pub async fn create_session(&self, user_id: i64, token: &str) -> Result<(), rusqlite::Error> {
131 let c = self.0.lock().await;
132 c.execute(
133 "INSERT INTO sessions (token, user_id, created_at, last_seen_at)
134 VALUES (?1, ?2, ?3, ?4)",
135 params![token, user_id, now(), now()],
136 )?;
137 Ok(())
138 }
139
140 pub async fn delete_session(&self, token: &str) -> Result<(), rusqlite::Error> {
141 let c = self.0.lock().await;
142 c.execute("DELETE FROM sessions WHERE token = ?1", [token])?;
143 Ok(())
144 }
145
146 pub async fn session_user(&self, token: &str) -> Option<User> {
147 let c = self.0.lock().await;
148 c.query_row(
149 "SELECT u.id, u.name, u.is_admin != 0
150 FROM sessions s JOIN users u ON u.id = s.user_id
151 WHERE s.token = ?1",
152 [token],
153 |r| {
154 Ok(User {
155 id: r.get(0)?,
156 name: r.get(1)?,
157 is_admin: r.get(2)?,
158 })
159 },
160 )
161 .ok()
162 }
163
164 // ---------- roots ----------
165
166 pub async fn user_roots(&self, user_id: i64) -> Vec<RootRow> {
167 let c = self.0.lock().await;
168 let mut out = Vec::new();
169 let Ok(mut stmt) = c
170 .prepare("SELECT id, path, mode FROM user_roots WHERE user_id = ?1 ORDER BY id")
171 else {
172 return out;
173 };
174 if let Ok(rows) =
175 stmt.query_map([user_id], |r| {
176 Ok(RootRow {
177 id: r.get(0)?,
178 path: r.get(1)?,
179 mode: r.get(2)?,
180 })
181 })
182 {
183 out.extend(rows.flatten());
184 }
185 out
186 }
187
188 // ---------- settings ----------
189
190 // Used from later milestones (shares/admin settings).
191 #[allow(dead_code)]
192 pub async fn get_setting(&self, key: &str) -> Option<String> {
193 let c = self.0.lock().await;
194 c.query_row("SELECT value FROM settings WHERE key = ?1", [key], |r| {
195 r.get(0)
196 })
197 .ok()
198 }
199
200 #[allow(dead_code)]
201 pub async fn set_setting(&self, key: &str, value: &str) -> Result<(), rusqlite::Error> {
202 let c = self.0.lock().await;
203 c.execute(
204 "INSERT INTO settings (key, value) VALUES (?1, ?2)
205 ON CONFLICT(key) DO UPDATE SET value = ?2",
206 params![key, value],
207 )?;
208 Ok(())
209 }
210}
211
212fn now() -> String {
213 chrono::Utc::now().to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
214}
215
216const SCHEMA_V1: &str = r#"
217CREATE TABLE IF NOT EXISTS users (
218 id INTEGER PRIMARY KEY AUTOINCREMENT,
219 name TEXT NOT NULL UNIQUE COLLATE NOCASE,
220 pass_hash TEXT NOT NULL,
221 is_admin INTEGER NOT NULL DEFAULT 0,
222 created_at TEXT NOT NULL
223);
224
225CREATE TABLE IF NOT EXISTS user_roots (
226 id INTEGER PRIMARY KEY AUTOINCREMENT,
227 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
228 path TEXT NOT NULL,
229 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
230 UNIQUE (user_id, path)
231);
232
233CREATE TABLE IF NOT EXISTS shares (
234 id INTEGER PRIMARY KEY AUTOINCREMENT,
235 token TEXT NOT NULL UNIQUE,
236 creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
237 target TEXT NOT NULL,
238 is_file INTEGER NOT NULL,
239 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
240 created_at TEXT NOT NULL,
241 expires_at TEXT
242);
243
244CREATE TABLE IF NOT EXISTS sessions (
245 token TEXT PRIMARY KEY,
246 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
247 created_at TEXT NOT NULL,
248 last_seen_at TEXT NOT NULL
249);
250
251CREATE TABLE IF NOT EXISTS settings (
252 key TEXT PRIMARY KEY,
253 value TEXT NOT NULL
254);
255INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
256"#;
257