juiceback/
db.rs

1//! SQLite database stuff -- schema, migrations, and CRUD for files and admins.
2
3use rusqlite::{params, Connection, Result};
4
5/// An admin user from the admins table.
6#[derive(Debug, Clone)]
7pub struct AdminUser {
8    pub id: i64,
9    pub username: String,
10    pub password_hash: String,
11    pub created_at: i64,
12}
13
14/// A file record from the files table.
15///
16/// storage_path points to where the file lives on juicehost's disk.
17/// The actual bytes never touch juiceback's filesystem during a streaming upload.
18#[derive(Debug, Clone)]
19pub struct FileRecord {
20    pub id: String,
21    pub filename: String,
22    pub mime_type: String,
23    pub size_bytes: i64,
24    pub storage_path: String,
25    pub delete_token: String,
26    pub uploaded_at: i64,
27    pub expires_at: i64,
28    pub uploader_ip: Option<String>,
29    pub storage_host: String,
30}
31/// Set up the database tables and run any needed migrations.
32///
33/// Creates the files and admins tables plus an index on expires_at.
34/// Also handles the storage_host column migration for older databases.
35pub fn init_db(conn: &Connection) -> Result<()> {
36    conn.execute(
37        "CREATE TABLE IF NOT EXISTS files (
38            id            TEXT PRIMARY KEY,
39            filename      TEXT NOT NULL,
40            mime_type     TEXT NOT NULL,
41            size_bytes    INTEGER NOT NULL,
42            storage_path  TEXT NOT NULL UNIQUE,
43            delete_token  TEXT NOT NULL,
44            uploaded_at   INTEGER NOT NULL,
45            expires_at    INTEGER NOT NULL,
46            uploader_ip   TEXT
47        )",
48        [],
49    )?;
50
51    conn.execute(
52        "CREATE TABLE IF NOT EXISTS admins (
53            id            INTEGER PRIMARY KEY AUTOINCREMENT,
54            username      TEXT NOT NULL UNIQUE,
55            password_hash TEXT NOT NULL,
56            created_at    INTEGER NOT NULL DEFAULT (unixepoch())
57        )",
58        [],
59    )?;
60
61    conn.execute(
62        "CREATE INDEX IF NOT EXISTS idx_expires_at ON files(expires_at)",
63        [],
64    )?;
65
66    // Migration: add storage_host column if it doesn't exist
67    let has_storage_host: bool = conn
68        .prepare("PRAGMA table_info(files)")?
69        .query_map([], |row| row.get::<_, String>(1))?
70        .any(|name| name.map_or(false, |n| n == "storage_host"));
71    if !has_storage_host {
72        conn.execute("ALTER TABLE files ADD COLUMN storage_host TEXT NOT NULL DEFAULT ''", [])?;
73    }
74
75    Ok(())
76}
77
78/// Insert a new admin user.
79pub fn insert_admin(conn: &Connection, username: &str, password_hash: &str) -> Result<()> {
80    conn.execute(
81        "INSERT INTO admins (username, password_hash) VALUES (?1, ?2)",
82        params![username, password_hash],
83    )?;
84    Ok(())
85}
86
87/// Delete an admin user by username. Returns true if a row was removed.
88pub fn delete_admin(conn: &Connection, username: &str) -> Result<bool> {
89    let affected = conn.execute("DELETE FROM admins WHERE username = ?1", params![username])?;
90    Ok(affected > 0)
91}
92
93/// Look up an admin by username.
94pub fn get_admin_by_username(conn: &Connection, username: &str) -> Result<Option<AdminUser>> {
95    let mut stmt = conn.prepare(
96        "SELECT id, username, password_hash, created_at FROM admins WHERE username = ?1",
97    )?;
98    let mut rows = stmt.query(params![username])?;
99    if let Some(row) = rows.next()? {
100        Ok(Some(AdminUser {
101            id: row.get(0)?,
102            username: row.get(1)?,
103            password_hash: row.get(2)?,
104            created_at: row.get(3)?,
105        }))
106    } else {
107        Ok(None)
108    }
109}
110
111/// List all admin users ordered by creation time.
112pub fn list_admins(conn: &Connection) -> Result<Vec<AdminUser>> {
113    let mut stmt = conn.prepare(
114        "SELECT id, username, password_hash, created_at FROM admins ORDER BY created_at ASC",
115    )?;
116    let rows = stmt.query_map([], |row| {
117        Ok(AdminUser {
118            id: row.get(0)?,
119            username: row.get(1)?,
120            password_hash: row.get(2)?,
121            created_at: row.get(3)?,
122        })
123    })?;
124    let mut admins = Vec::new();
125    for row in rows {
126        admins.push(row?);
127    }
128    Ok(admins)
129}
130
131/// Insert a new file record into the database.
132pub fn insert_file(conn: &Connection, record: &FileRecord) -> Result<()> {
133    conn.execute(
134        "INSERT INTO files (
135            id, filename, mime_type, size_bytes, storage_path,
136            delete_token, uploaded_at, expires_at, uploader_ip, storage_host
137        ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)",
138        params![
139            record.id,
140            record.filename,
141            record.mime_type,
142            record.size_bytes,
143            record.storage_path,
144            record.delete_token,
145            record.uploaded_at,
146            record.expires_at,
147            record.uploader_ip,
148            record.storage_host,
149        ],
150    )?;
151    Ok(())
152}
153
154/// Look up a single file record by its ID.
155pub fn get_file(conn: &Connection, id: &str) -> Result<Option<FileRecord>> {
156    let mut stmt = conn.prepare(
157        "SELECT id, filename, mime_type, size_bytes, storage_path,
158                delete_token, uploaded_at, expires_at, uploader_ip, storage_host
159         FROM files WHERE id = ?1",
160    )?;
161
162    let mut rows = stmt.query(params![id])?;
163
164    if let Some(row) = rows.next()? {
165        Ok(Some(FileRecord {
166            id: row.get(0)?,
167            filename: row.get(1)?,
168            mime_type: row.get(2)?,
169            size_bytes: row.get(3)?,
170            storage_path: row.get(4)?,
171            delete_token: row.get(5)?,
172            uploaded_at: row.get(6)?,
173            expires_at: row.get(7)?,
174            uploader_ip: row.get(8)?,
175            storage_host: row.get(9)?,
176        }))
177    } else {
178        Ok(None)
179    }
180}
181
182/// Look up multiple file records by their IDs.
183pub fn get_files_by_ids(conn: &Connection, ids: &[String]) -> Result<Vec<FileRecord>> {
184    if ids.is_empty() {
185        return Ok(Vec::new());
186    }
187    let placeholders: String = ids
188        .iter()
189        .enumerate()
190        .map(|(i, _)| format!("?{}", i + 1))
191        .collect::<Vec<_>>()
192        .join(", ");
193    let sql = format!(
194        "SELECT id, filename, mime_type, size_bytes, storage_path,
195                delete_token, uploaded_at, expires_at, uploader_ip, storage_host
196         FROM files WHERE id IN ({})",
197        placeholders
198    );
199    let mut stmt = conn.prepare(&sql)?;
200    let params: Vec<&dyn rusqlite::ToSql> = ids.iter().map(|s| s as &dyn rusqlite::ToSql).collect();
201    let rows = stmt.query_map(params.as_slice(), |row| {
202        Ok(FileRecord {
203            id: row.get(0)?,
204            filename: row.get(1)?,
205            mime_type: row.get(2)?,
206            size_bytes: row.get(3)?,
207            storage_path: row.get(4)?,
208            delete_token: row.get(5)?,
209            uploaded_at: row.get(6)?,
210            expires_at: row.get(7)?,
211            uploader_ip: row.get(8)?,
212            storage_host: row.get(9)?,
213        })
214    })?;
215    let mut records = Vec::new();
216    for row in rows {
217        records.push(row?);
218    }
219    Ok(records)
220}
221
222/// Delete a file record by ID. Returns true if a row was removed.
223pub fn delete_file(conn: &Connection, id: &str) -> Result<bool> {
224    let affected = conn.execute("DELETE FROM files WHERE id = ?1", params![id])?;
225    Ok(affected > 0)
226}
227
228/// List every file record, newest first.
229pub fn list_all_files(conn: &Connection) -> Result<Vec<FileRecord>> {
230    let mut stmt = conn.prepare(
231        "SELECT id, filename, mime_type, size_bytes, storage_path,
232                delete_token, uploaded_at, expires_at, uploader_ip, storage_host
233         FROM files ORDER BY uploaded_at DESC",
234    )?;
235    let rows = stmt.query_map([], |row| {
236        Ok(FileRecord {
237            id: row.get(0)?,
238            filename: row.get(1)?,
239            mime_type: row.get(2)?,
240            size_bytes: row.get(3)?,
241            storage_path: row.get(4)?,
242            delete_token: row.get(5)?,
243            uploaded_at: row.get(6)?,
244            expires_at: row.get(7)?,
245            uploader_ip: row.get(8)?,
246            storage_host: row.get(9)?,
247        })
248    })?;
249    let mut files = Vec::new();
250    for row in rows {
251        files.push(row?);
252    }
253    Ok(files)
254}
255
256/// Give a file a new ID and update its public URL.
257///
258/// The delete_token, filename, and expiry stay the same. Only the ID and
259/// storage_path change. Returns true if a row was actually updated.
260pub fn renew_file_id(
261    conn: &Connection,
262    old_id: &str,
263    new_id: &str,
264    new_storage_path: &str,
265) -> Result<bool> {
266    let affected = conn.execute(
267        "UPDATE files SET id = ?1, storage_path = ?2 WHERE id = ?3",
268        params![new_id, new_storage_path, old_id],
269    )?;
270    Ok(affected > 0)
271}
272
273/// List non-expired file records, newest first.
274pub fn list_active(conn: &Connection) -> Result<Vec<FileRecord>> {
275    let mut stmt = conn.prepare(
276        "SELECT id, filename, mime_type, size_bytes, storage_path,
277                delete_token, uploaded_at, expires_at, uploader_ip, storage_host
278         FROM files WHERE expires_at >= unixepoch()
279         ORDER BY uploaded_at DESC",
280    )?;
281
282    let rows = stmt.query_map([], |row| {
283        Ok(FileRecord {
284            id: row.get(0)?,
285            filename: row.get(1)?,
286            mime_type: row.get(2)?,
287            size_bytes: row.get(3)?,
288            storage_path: row.get(4)?,
289            delete_token: row.get(5)?,
290            uploaded_at: row.get(6)?,
291            expires_at: row.get(7)?,
292            uploader_ip: row.get(8)?,
293            storage_host: row.get(9)?,
294        })
295    })?;
296
297    let mut records = Vec::new();
298    for row in rows {
299        records.push(row?);
300    }
301    Ok(records)
302}
303
304/// List expired file records only.
305pub fn list_expired(conn: &Connection) -> Result<Vec<FileRecord>> {
306    let mut stmt = conn.prepare(
307        "SELECT id, filename, mime_type, size_bytes, storage_path,
308                delete_token, uploaded_at, expires_at, uploader_ip, storage_host
309         FROM files WHERE expires_at < unixepoch()",
310    )?;
311
312    let rows = stmt.query_map([], |row| {
313        Ok(FileRecord {
314            id: row.get(0)?,
315            filename: row.get(1)?,
316            mime_type: row.get(2)?,
317            size_bytes: row.get(3)?,
318            storage_path: row.get(4)?,
319            delete_token: row.get(5)?,
320            uploaded_at: row.get(6)?,
321            expires_at: row.get(7)?,
322            uploader_ip: row.get(8)?,
323            storage_host: row.get(9)?,
324        })
325    })?;
326
327    let mut records = Vec::new();
328    for row in rows {
329        records.push(row?);
330    }
331    Ok(records)
332}
333
334/// List every storage path in the database.
335pub fn list_all_storage_paths(conn: &Connection) -> Result<Vec<String>> {
336    let mut stmt = conn.prepare("SELECT storage_path FROM files")?;
337    let rows = stmt.query_map([], |row| row.get::<_, String>(0))?;
338    let mut paths = Vec::new();
339    for row in rows {
340        paths.push(row?);
341    }
342    Ok(paths)
343}
344
345/// Bulk-delete file records by their IDs. Returns the number of rows deleted.
346pub fn delete_files_by_ids(conn: &Connection, ids: &[String]) -> Result<usize> {
347    if ids.is_empty() {
348        return Ok(0);
349    }
350    let placeholders: String = ids
351        .iter()
352        .enumerate()
353        .map(|(i, _)| format!("?{}", i + 1))
354        .collect::<Vec<_>>()
355        .join(", ");
356    let sql = format!("DELETE FROM files WHERE id IN ({})", placeholders);
357    let params: Vec<&dyn rusqlite::ToSql> = ids.iter().map(|s| s as &dyn rusqlite::ToSql).collect();
358    let affected = conn.execute(&sql, params.as_slice())?;
359    Ok(affected)
360}
juiceback/db.rs

juiceback/
db.rs
