videre_core/
library_stats.rs

1//! Aggregate library statistics for dashboard-style callers.
2//! Plain queries over an open `rusqlite::Connection`, shared source of truth
3//! for the gallery's stats tile, `videre stats`, and any other embedder.
4
5use rusqlite::{Connection, Result};
6use serde::Serialize;
7
8#[derive(Debug, Clone, PartialEq, Default, Serialize)]
9pub struct LibraryStats {
10    pub total_files: i64,
11    pub total_size_bytes: i64,
12    pub total_photos: i64,
13    pub total_videos: i64,
14    pub duplicate_group_count: i64,
15    pub duplicate_file_count: i64,
16    pub wasted_bytes: i64,
17    pub faces_detected: i64,
18    pub people_named: i64,
19    /// Counts of rated/picked/labelled/liked photos.
20    #[serde(default)]
21    pub marks: crate::marks::MarksSummary,
22    /// One entry per model with an embedding database for this library.
23    /// Empty when nothing has been embedded, which is a normal state.
24    #[serde(default)]
25    pub embeddings: Vec<crate::embeddings_db::ModelEmbeddingCount>,
26}
27
28use crate::db::table_exists;
29
30const PHOTO_MIME_LIST: &str =
31    "'image/jpeg','image/png','image/gif','image/webp','image/bmp','image/tiff','image/heic'";
32const VIDEO_MIME_LIST: &str = "'video/quicktime','video/mp4'";
33
34const PHOTO_EXTS: &str = "'jpg','jpeg','png','gif','webp','bmp','tiff','heic','dng'";
35const VIDEO_EXTS: &str = "'mov','mp4'";
36
37// `VIDEO_EXTS`'s values must stay in sync with
38// `crate::embeddings::is_video_ext` (the shared "is this a video" check).
39// The SQL below uses `lower(ext)` so this list stays case-insensitive,
40// matching that helper.
41
42pub fn compute(conn: &Connection) -> Result<LibraryStats> {
43    let total_files: i64 = conn.query_row("SELECT COUNT(*) FROM file_hashes", [], |r| r.get(0))?;
44    let total_size_bytes: i64 = conn.query_row(
45        "SELECT COALESCE(SUM(size_bytes), 0) FROM file_hashes",
46        [],
47        |r| r.get(0),
48    )?;
49    let total_photos: i64 = conn.query_row(
50        &format!(
51            "SELECT COUNT(*) FROM file_hashes
52             WHERE mime IN ({PHOTO_MIME_LIST})
53                OR (mime IS NULL AND lower(ext) IN ({PHOTO_EXTS}))"
54        ),
55        [],
56        |r| r.get(0),
57    )?;
58    let total_videos: i64 = conn.query_row(
59        &format!(
60            "SELECT COUNT(*) FROM file_hashes
61             WHERE mime IN ({VIDEO_MIME_LIST})
62                OR (mime IS NULL AND lower(ext) IN ({VIDEO_EXTS}))"
63        ),
64        [],
65        |r| r.get(0),
66    )?;
67
68    let duplicate_group_count: i64 = conn.query_row(
69        "SELECT COUNT(*) FROM \
70         (SELECT hash FROM file_hashes GROUP BY hash HAVING COUNT(*) > 1)",
71        [],
72        |r| r.get(0),
73    )?;
74    let duplicate_file_count: i64 = conn.query_row(
75        "SELECT COUNT(*) FROM file_hashes \
76         WHERE hash IN (SELECT hash FROM file_hashes GROUP BY hash HAVING COUNT(*) > 1)",
77        [],
78        |r| r.get(0),
79    )?;
80    let wasted_bytes: i64 = conn.query_row(
81        "SELECT COALESCE(SUM(size_bytes * (cnt - 1)), 0) FROM \
82         (SELECT hash, size_bytes, COUNT(*) as cnt \
83          FROM file_hashes GROUP BY hash HAVING cnt > 1)",
84        [],
85        |r| r.get(0),
86    )?;
87
88    let (faces_detected, people_named) = if table_exists(conn, "faces")? {
89        let faces_detected: i64 = conn.query_row("SELECT COUNT(*) FROM faces", [], |r| r.get(0))?;
90        let people_named: i64 = conn.query_row(
91            "SELECT COUNT(DISTINCT person_label) FROM faces \
92             WHERE confirmed = 1 AND person_label IS NOT NULL",
93            [],
94            |r| r.get(0),
95        )?;
96        (faces_detected, people_named)
97    } else {
98        (0, 0)
99    };
100
101    Ok(LibraryStats {
102        total_files,
103        total_size_bytes,
104        total_photos,
105        total_videos,
106        duplicate_group_count,
107        duplicate_file_count,
108        wasted_bytes,
109        faces_detected,
110        people_named,
111        marks: crate::marks::summary(conn)?,
112        embeddings: Vec::new(),
113    })
114}
115
116/// Compute database and model-store statistics for one selected library.
117pub fn compute_full_in(
118    conn: &Connection,
119    ctx: &crate::library::LibraryContext,
120) -> anyhow::Result<LibraryStats> {
121    ctx.ensure_root_identity()?;
122    let mut stats = compute(conn)?;
123    stats.embeddings = crate::embeddings_db::counts_by_model_in(ctx)?;
124    Ok(stats)
125}
126
127/// What the library is made of, by file type.
128///
129/// `stats` could say how many files there were and how big they were in total,
130/// but not what they *are* - and "70,601 files, 480GB" answers a different
131/// question from "12,000 of those are HEIC and they are 22GB of it".
132///
133/// Grouped by extension rather than mime, with the mime shown alongside,
134/// because extension is what a user recognises and types into `--ext`.
135#[derive(Debug, Clone, Serialize)]
136pub struct TypeBreakdown {
137    pub ext: String,
138    pub mime: String,
139    pub files: i64,
140    pub bytes: i64,
141}
142
143pub fn by_type(conn: &rusqlite::Connection, limit: usize) -> rusqlite::Result<Vec<TypeBreakdown>> {
144    let mut stmt = conn.prepare(
145        "SELECT LOWER(COALESCE(NULLIF(ext,''),'(none)')),
146                COALESCE(NULLIF(mime,''),'(unknown)'),
147                COUNT(*), COALESCE(SUM(size_bytes),0)
148           FROM file_hashes
149          GROUP BY 1, 2
150          ORDER BY 4 DESC",
151    )?;
152    let rows = stmt.query_map([], |r| {
153        Ok(TypeBreakdown {
154            ext: r.get(0)?,
155            mime: r.get(1)?,
156            files: r.get(2)?,
157            bytes: r.get(3)?,
158        })
159    })?;
160    let mut out: Vec<TypeBreakdown> = rows.collect::<rusqlite::Result<_>>()?;
161    out.truncate(limit);
162    Ok(out)
163}
164
165/// A scanned path whose filename extension conflicts with detected content.
166#[derive(Debug, Clone, PartialEq, Serialize)]
167pub struct MismatchFile {
168    pub path: String,
169    pub ext: String,
170    pub mime: String,
171}
172
173/// Exact mismatch count with a bounded or complete path list.
174#[derive(Debug, Clone, PartialEq, Serialize)]
175pub struct MismatchReport {
176    pub count: usize,
177    pub files: Vec<MismatchFile>,
178    pub truncated: bool,
179}
180
181/// Report disagreements from the last scan without opening source files.
182///
183/// `Some(limit)` retains at most that many paths; `None` retains all paths.
184/// Every row is still examined so `count` is exact even for a bounded report.
185pub fn mismatched_files(conn: &Connection, limit: Option<usize>) -> Result<MismatchReport> {
186    let mut stmt = conn.prepare("SELECT path, ext, mime FROM file_hashes ORDER BY path")?;
187    let rows = stmt.query_map([], |r| {
188        Ok((
189            r.get::<_, String>(0)?,
190            r.get::<_, Option<String>>(1)?,
191            r.get::<_, Option<String>>(2)?,
192        ))
193    })?;
194    let mut count = 0;
195    let mut files = Vec::new();
196    for row in rows {
197        let (path, ext, mime) = row?;
198        let (Some(ext), Some(mime)) = (ext, mime) else {
199            continue;
200        };
201        if crate::mime_probe::is_content_mismatch(&ext, Some(&mime)) {
202            count += 1;
203            if limit.is_none_or(|bound| files.len() < bound) {
204                files.push(MismatchFile { path, ext, mime });
205            }
206        }
207    }
208    Ok(MismatchReport {
209        count,
210        truncated: count > files.len(),
211        files,
212    })
213}
214
215#[cfg(test)]
216mod tests {
217    use super::*;
218
219    fn test_db() -> Connection {
220        let conn = Connection::open_in_memory().unwrap();
221        conn.execute_batch(
222            "CREATE TABLE file_hashes (
223                path        TEXT PRIMARY KEY,
224                hash        TEXT NOT NULL,
225                size_bytes  INTEGER,
226                ext         TEXT
227            );",
228        )
229        .unwrap();
230        crate::library_db::ensure_scan_schema(&conn).unwrap();
231        conn
232    }
233
234    fn insert_file(conn: &Connection, path: &str, hash: &str, size_bytes: i64, ext: &str) {
235        conn.execute(
236            "INSERT INTO file_hashes (path, hash, size_bytes, ext) VALUES (?1, ?2, ?3, ?4)",
237            rusqlite::params![path, hash, size_bytes, ext],
238        )
239        .unwrap();
240    }
241
242    fn insert_scanned_file(conn: &Connection, path: &str, hash: &str, ext: &str, mime: &str) {
243        conn.execute(
244            "INSERT INTO file_hashes (path, hash, ext, mime) VALUES (?1, ?2, ?3, ?4)",
245            rusqlite::params![path, hash, ext, mime],
246        )
247        .unwrap();
248    }
249
250    #[test]
251    fn mismatched_files_counts_paths_not_hashes() {
252        let conn = test_db();
253        insert_scanned_file(&conn, "/a/1.png", "same", "png", "image/jpeg");
254        insert_scanned_file(&conn, "/a/2.png", "same", "png", "image/jpeg");
255        insert_scanned_file(&conn, "/a/3.jpg", "same", "jpg", "image/jpeg");
256
257        let report = mismatched_files(&conn, Some(10)).unwrap();
258        assert_eq!(report.count, 2);
259        assert_eq!(report.files.len(), 2);
260        assert_eq!(report.files[0].path, "/a/1.png");
261        assert_eq!(report.files[0].ext, "png");
262        assert_eq!(report.files[0].mime, "image/jpeg");
263        assert_eq!(report.files[1].path, "/a/2.png");
264        assert!(!report.truncated);
265    }
266
267    #[test]
268    fn mismatched_files_samples_in_path_order() {
269        let conn = test_db();
270        for i in (0..12).rev() {
271            insert_scanned_file(
272                &conn,
273                &format!("/a/{i:02}.png"),
274                &format!("hash-{i}"),
275                "png",
276                "image/jpeg",
277            );
278        }
279        insert_scanned_file(&conn, "/a/movie.mov", "movie", "mov", "video/mp4");
280        insert_scanned_file(
281            &conn,
282            "/a/unknown.jpg",
283            "unknown",
284            "jpg",
285            crate::mime_probe::UNKNOWN_MIME,
286        );
287
288        let bounded = mismatched_files(&conn, Some(10)).unwrap();
289        assert_eq!(bounded.count, 12);
290        assert_eq!(bounded.files.len(), 10);
291        assert_eq!(bounded.files.first().unwrap().path, "/a/00.png");
292        assert_eq!(bounded.files.last().unwrap().path, "/a/09.png");
293        assert!(bounded.truncated);
294
295        let full = mismatched_files(&conn, None).unwrap();
296        assert_eq!(full.count, 12);
297        assert_eq!(full.files.len(), 12);
298        assert_eq!(full.files.first().unwrap().path, "/a/00.png");
299        assert_eq!(full.files.last().unwrap().path, "/a/11.png");
300        assert!(!full.truncated);
301    }
302
303    #[test]
304    fn mismatched_files_propagates_sql_errors() {
305        let conn = test_db();
306        conn.execute_batch("DROP TABLE file_hashes").unwrap();
307        assert!(mismatched_files(&conn, Some(10)).is_err());
308    }
309
310    #[test]
311    fn compute_counts_total_files_and_size() {
312        let conn = test_db();
313        insert_file(&conn, "/a/1.jpg", "h1", 1000, "jpg");
314        insert_file(&conn, "/a/2.png", "h2", 2500, "png");
315
316        let stats = compute(&conn).unwrap();
317        assert_eq!(stats.total_files, 2);
318        assert_eq!(stats.total_size_bytes, 3500);
319    }
320
321    #[test]
322    fn compute_on_empty_db_returns_zeros() {
323        let conn = test_db();
324        let stats = compute(&conn).unwrap();
325        assert_eq!(stats.total_files, 0);
326        assert_eq!(stats.total_size_bytes, 0);
327    }
328
329    #[test]
330    fn compute_splits_photos_and_videos_by_extension() {
331        let conn = test_db();
332        insert_file(&conn, "/a/1.jpg", "h1", 100, "jpg");
333        insert_file(&conn, "/a/2.heic", "h2", 100, "heic");
334        insert_file(&conn, "/a/3.mov", "h3", 100, "mov");
335        insert_file(&conn, "/a/4.mp4", "h4", 100, "mp4");
336        insert_file(&conn, "/a/5.unknown", "h5", 100, "xyz");
337
338        let stats = compute(&conn).unwrap();
339        assert_eq!(stats.total_photos, 2);
340        assert_eq!(stats.total_videos, 2);
341        assert_eq!(stats.total_files, 5); // unrecognized ext still counts toward total_files
342    }
343
344    #[test]
345    fn compute_counts_video_exts_case_insensitively() {
346        let conn = test_db();
347        insert_file(&conn, "/a/1.MOV", "h1", 100, "MOV");
348        insert_file(&conn, "/a/2.Mp4", "h2", 100, "Mp4");
349        insert_file(&conn, "/a/3.mov", "h3", 100, "mov");
350
351        let stats = compute(&conn).unwrap();
352        assert_eq!(stats.total_videos, 3); // uppercase/mixed-case exts still count as video
353    }
354
355    #[test]
356    fn compute_counts_duplicate_groups_and_wasted_bytes() {
357        let conn = test_db();
358        insert_file(&conn, "/a/1.jpg", "dup-hash", 1000, "jpg");
359        insert_file(&conn, "/b/1-copy.jpg", "dup-hash", 1000, "jpg");
360        insert_file(&conn, "/a/2.jpg", "dup-hash", 1000, "jpg");
361        insert_file(&conn, "/a/3.jpg", "unique-hash", 500, "jpg");
362
363        let stats = compute(&conn).unwrap();
364        assert_eq!(stats.duplicate_group_count, 1);
365        assert_eq!(stats.duplicate_file_count, 3); // all 3 members of the dup group
366        assert_eq!(stats.wasted_bytes, 2000); // (3 - 1) * 1000
367    }
368
369    #[test]
370    fn compute_with_no_duplicates_reports_zero() {
371        let conn = test_db();
372        insert_file(&conn, "/a/1.jpg", "h1", 500, "jpg");
373        insert_file(&conn, "/a/2.jpg", "h2", 500, "jpg");
374
375        let stats = compute(&conn).unwrap();
376        assert_eq!(stats.duplicate_group_count, 0);
377        assert_eq!(stats.duplicate_file_count, 0);
378        assert_eq!(stats.wasted_bytes, 0);
379    }
380
381    #[test]
382    fn compute_counts_faces_and_named_people() {
383        let conn = test_db();
384        conn.execute_batch(
385            "CREATE TABLE faces (
386                id            INTEGER PRIMARY KEY,
387                hash          TEXT NOT NULL,
388                bbox          TEXT NOT NULL,
389                landmark      TEXT,
390                embedding     BLOB NOT NULL,
391                cluster_id    INTEGER,
392                person_label  TEXT,
393                confirmed     INTEGER DEFAULT 0,
394                is_primary    INTEGER DEFAULT 0
395            );",
396        )
397        .unwrap();
398        conn.execute(
399            "INSERT INTO faces (id, hash, bbox, embedding, person_label, confirmed) \
400             VALUES (1, 'h1', '[]', X'00', 'Alice', 1)",
401            [],
402        )
403        .unwrap();
404        conn.execute(
405            "INSERT INTO faces (id, hash, bbox, embedding, person_label, confirmed) \
406             VALUES (2, 'h1', '[]', X'00', 'Alice', 1)",
407            [],
408        )
409        .unwrap();
410        conn.execute(
411            "INSERT INTO faces (id, hash, bbox, embedding, person_label, confirmed) \
412             VALUES (3, 'h2', '[]', X'00', NULL, 0)",
413            [],
414        )
415        .unwrap();
416
417        let stats = compute(&conn).unwrap();
418        assert_eq!(stats.faces_detected, 3);
419        assert_eq!(stats.people_named, 1); // distinct confirmed person_label
420    }
421
422    #[test]
423    fn compute_without_faces_table_returns_zero_not_error() {
424        let conn = test_db(); // no faces table created
425        let stats = compute(&conn).unwrap();
426        assert_eq!(stats.faces_detected, 0);
427        assert_eq!(stats.people_named, 0);
428    }
429
430    #[test]
431    fn compute_full_in_reads_models_without_creating_missing_stores() {
432        let temp = tempfile::tempdir().unwrap();
433        let root = temp.path().join("library");
434        let cache = temp.path().join("cache");
435        std::fs::create_dir(&root).unwrap();
436        let ctx = crate::library::LibraryContext::new(&root, &cache).unwrap();
437        let conn = crate::library_db::initialize(&ctx).unwrap();
438        let stats = compute_full_in(&conn, &ctx).unwrap();
439        assert!(stats.embeddings.is_empty());
440        assert!(!ctx.paths.embeddings.exists());
441    }
442}
videre_core/library_stats.rs

videre_core/
library_stats.rs
