smriti/db/
connection.rs

1//! Database connection management
2
3use std::path::{Path, PathBuf};
4
5use rusqlite::{Connection, Result as SqliteResult};
6use thiserror::Error;
7
8#[derive(Error, Debug)]
9pub enum DatabaseError {
10    #[error("Failed to open database: {0}")]
11    OpenError(#[from] rusqlite::Error),
12
13    #[error("Database path does not exist: {0}")]
14    PathNotFound(PathBuf),
15
16    #[error("Failed to create .photovault directory: {0}")]
17    DirectoryCreationError(std::io::Error),
18}
19
20/// Database wrapper with path information
21pub struct Database {
22    pub conn: Connection,
23}
24
25impl Drop for Database {
26    fn drop(&mut self) {
27        // Best-effort PASSIVE checkpoint on drop. The clean-exit path
28        // already calls `flush_and_close` (TRUNCATE), so this only
29        // catches accidental drops (panics, early-return paths) and
30        // never blocks long.
31        let _ = self.conn.execute_batch("PRAGMA wal_checkpoint(PASSIVE);");
32    }
33}
34
35/// Returns the on-drive metadata directory.
36///
37/// The folder name is `.photovault` for backwards compatibility with
38/// libraries indexed before the rename — see `LIBRARY_METADATA_DIR`.
39pub fn library_metadata_dir(drive_root: &Path) -> PathBuf {
40    drive_root.join(super::LIBRARY_METADATA_DIR)
41}
42
43/// Returns the SQLite database file path for a drive root.
44///
45/// Use this when a background task needs to open its own connection
46/// to the same database — e.g. duplicate / burst detection runs in
47/// `spawn_blocking` and shouldn't compete with the shared
48/// `Arc<Mutex<Database>>` for a lock. SQLite WAL handles the
49/// concurrent reader/writer.
50pub fn db_path_for(drive_root: &Path) -> PathBuf {
51    library_metadata_dir(drive_root).join("photovault.db")
52}
53
54/// Returns the map tile cache directory for a drive root.
55pub fn tile_cache_dir(drive_root: &Path) -> PathBuf {
56    library_metadata_dir(drive_root).join("tile_cache")
57}
58
59/// Open a fresh connection to an existing Smriti database.
60///
61/// Configures the same WAL / cache pragmas as `open_for_drive` so
62/// background detection tasks see the same performance profile.
63/// Returns an error if the path doesn't exist or can't be opened.
64pub fn open_secondary(db_path: &Path) -> Result<Connection, DatabaseError> {
65    let conn = Connection::open(db_path)?;
66    Database::configure_connection(&conn)?;
67    Ok(conn)
68}
69
70impl Database {
71    /// Open or create database on a drive
72    ///
73    /// # Arguments
74    /// * `drive_root` - Root path of the drive to index (e.g., "/media/photos")
75    ///
76    /// # Returns
77    /// A Database instance with an open connection
78    pub fn open_for_drive<P: AsRef<Path>>(drive_root: P) -> Result<Self, DatabaseError> {
79        let drive_root = drive_root.as_ref().to_path_buf();
80
81        if !drive_root.exists() {
82            return Err(DatabaseError::PathNotFound(drive_root));
83        }
84
85        let metadata_dir = library_metadata_dir(&drive_root);
86        if !metadata_dir.exists() {
87            std::fs::create_dir_all(&metadata_dir)
88                .map_err(DatabaseError::DirectoryCreationError)?;
89        }
90
91        let db_path = metadata_dir.join("photovault.db");
92        let conn = Connection::open(&db_path)?;
93
94        // Configure SQLite for optimal performance
95        Self::configure_connection(&conn)?;
96        let _ = Self::create_indexes(&conn);
97
98        Ok(Self { conn })
99    }
100
101    /// Configure SQLite connection for optimal performance
102    pub(crate) fn configure_connection(conn: &Connection) -> SqliteResult<()> {
103        // Write-Ahead Logging for better concurrent read performance
104        conn.pragma_update(None, "journal_mode", "WAL")?;
105
106        // Balance between safety and speed
107        conn.pragma_update(None, "synchronous", "NORMAL")?;
108
109        // 64MB cache
110        conn.pragma_update(None, "cache_size", -64000)?;
111
112        // Temp tables in memory
113        conn.pragma_update(None, "temp_store", "MEMORY")?;
114
115        // Memory-map up to 256MB
116        conn.pragma_update(None, "mmap_size", 268435456)?;
117
118        // Foreign key enforcement
119        conn.pragma_update(None, "foreign_keys", "ON")?;
120
121        // Wait up to 5 seconds for a busy lock instead of failing
122        // immediately. Without this, two writers (e.g. face-processor
123        // writer thread + scanner upserts during a re-index) surface
124        // SQLITE_BUSY to the user instead of retrying.
125        conn.pragma_update(None, "busy_timeout", 5000)?;
126
127        Ok(())
128    }
129
130    /// Create recommended indexes for query performance.
131    fn create_indexes(conn: &Connection) -> SqliteResult<()> {
132        conn.execute_batch(
133            r#"
134            CREATE INDEX IF NOT EXISTS idx_photos_date ON photos(date_taken);
135            CREATE INDEX IF NOT EXISTS idx_photos_hash ON photos(file_hash);
136            CREATE INDEX IF NOT EXISTS idx_photos_location ON photos(location_country, location_city);
137            CREATE INDEX IF NOT EXISTS idx_photos_trashed ON photos(is_trashed);
138            CREATE INDEX IF NOT EXISTS idx_photos_path ON photos(file_path);
139            CREATE INDEX IF NOT EXISTS idx_photos_faces_trashed ON photos(faces_processed, is_trashed);
140            CREATE INDEX IF NOT EXISTS idx_photos_hash_trashed ON photos(file_hash, is_trashed);
141            CREATE INDEX IF NOT EXISTS idx_faces_cluster ON faces(cluster_id);
142            CREATE INDEX IF NOT EXISTS idx_faces_photo ON faces(photo_id);
143            CREATE INDEX IF NOT EXISTS idx_faces_photo_cluster ON faces(photo_id, cluster_id);
144            CREATE INDEX IF NOT EXISTS idx_clusters_name ON face_clusters(name);
145            "#,
146        )
147    }
148
149    /// Check if this is a fresh database (needs schema creation)
150    pub fn needs_schema(&self) -> SqliteResult<bool> {
151        let count: i32 = self.conn.query_row(
152            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='photos'",
153            [],
154            |row| row.get(0),
155        )?;
156        Ok(count == 0)
157    }
158
159    /// Run periodic database maintenance.
160    ///
161    /// Call after bulk operations (scan, trash empty, reindex).
162    pub fn run_maintenance(&self) -> SqliteResult<()> {
163        // Let SQLite optimize its query planner statistics
164        self.conn.execute_batch("PRAGMA optimize;")?;
165        // Checkpoint WAL to keep it from growing unbounded
166        self.conn.execute_batch("PRAGMA wal_checkpoint(PASSIVE);")?;
167        Ok(())
168    }
169
170    /// Aggressively flush WAL to the main DB file and consume the
171    /// connection. Use on drive deselect / app exit so a yanked USB
172    /// drive doesn't leave unwritten data behind. TRUNCATE is the
173    /// strict mode (vs PASSIVE) — it both checkpoints AND empties the
174    /// WAL file.
175    pub fn flush_and_close(self) {
176        if let Err(e) = self.conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE);") {
177            tracing::debug!("WAL truncate-checkpoint failed: {}", e);
178        }
179        // Connection drops here, releasing file handles.
180    }
181
182    /// Run integrity check on the database.
183    ///
184    /// Returns Ok(true) if database is healthy, Ok(false) if corrupt.
185    pub fn check_integrity(&self) -> SqliteResult<bool> {
186        let result: String = self
187            .conn
188            .query_row("PRAGMA quick_check;", [], |row| row.get(0))?;
189        Ok(result == "ok")
190    }
191
192    /// Create a backup of the database file.
193    ///
194    /// Copies the DB to `<db_path>.backup`. Keeps up to `max_backups` copies.
195    pub fn backup(drive_root: &Path, max_backups: usize) -> std::io::Result<PathBuf> {
196        let db_path = library_metadata_dir(drive_root).join("photovault.db");
197        if !db_path.exists() {
198            return Err(std::io::Error::new(
199                std::io::ErrorKind::NotFound,
200                "Database file not found",
201            ));
202        }
203
204        let backup_dir = library_metadata_dir(drive_root).join("backups");
205        std::fs::create_dir_all(&backup_dir)?;
206
207        // Name with timestamp
208        let timestamp = chrono::Utc::now().format("%Y%m%d_%H%M%S");
209        let backup_path = backup_dir.join(format!("photovault_{}.db", timestamp));
210
211        std::fs::copy(&db_path, &backup_path)?;
212
213        // Clean old backups (keep max_backups newest)
214        let mut backups: Vec<_> = std::fs::read_dir(&backup_dir)?
215            .filter_map(|e| e.ok())
216            .filter(|e| {
217                e.path()
218                    .file_name()
219                    .and_then(|n| n.to_str())
220                    .map(|n| n.starts_with("photovault_") && n.ends_with(".db"))
221                    .unwrap_or(false)
222            })
223            .collect();
224
225        backups.sort_by_key(|e| std::cmp::Reverse(e.path()));
226
227        for old in backups.into_iter().skip(max_backups) {
228            let _ = std::fs::remove_file(old.path());
229        }
230
231        tracing::info!("Database backed up to {}", backup_path.display());
232        Ok(backup_path)
233    }
234}
235
236#[cfg(test)]
237mod tests {
238    use super::*;
239    use tempfile::tempdir;
240
241    #[test]
242    fn test_open_for_drive() {
243        let temp = tempdir().unwrap();
244        let db = Database::open_for_drive(temp.path()).unwrap();
245
246        let table_count: i64 = db
247            .conn
248            .query_row(
249                "SELECT COUNT(*) FROM sqlite_master WHERE type='table'",
250                [],
251                |row| row.get(0),
252            )
253            .unwrap();
254        assert!(table_count >= 0);
255        assert!(temp.path().join(".photovault").exists());
256    }
257}
smriti/db/connection.rs

smriti/db/
connection.rs
