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 `videre report`'s stats tile, `videre stats`, and any other embedder.
4//! See docs/superpowers/specs/2026-07-31-dashboard-stats-backend-design.md
5//! (Pass A) for what is and isn't in scope.
6
7use rusqlite::{Connection, Result};
8use serde::Serialize;
9
10#[derive(Debug, Clone, PartialEq, Default, Serialize)]
11pub struct LibraryStats {
12    pub total_files: i64,
13    pub total_size_bytes: i64,
14    pub total_photos: i64,
15    pub total_videos: i64,
16    pub duplicate_group_count: i64,
17    pub duplicate_file_count: i64,
18    pub wasted_bytes: i64,
19    pub faces_detected: i64,
20    pub people_named: i64,
21    /// One entry per model with an embedding database for this library.
22    /// Empty when nothing has been embedded, which is a normal state.
23    #[serde(default)]
24    pub embeddings: Vec<crate::embeddings_db::ModelEmbeddingCount>,
25}
26
27use crate::db::table_exists;
28
29const PHOTO_EXTS: &str = "'jpg','jpeg','png','gif','webp','bmp','tiff','heic','dng'";
30const VIDEO_EXTS: &str = "'mov','mp4'";
31
32// `VIDEO_EXTS`'s values must stay in sync with
33// `crate::embeddings::is_video_ext` (the shared "is this a video" check).
34// The SQL below uses `lower(ext)` so this list stays case-insensitive,
35// matching that helper.
36
37pub fn compute(conn: &Connection) -> Result<LibraryStats> {
38    let total_files: i64 = conn.query_row("SELECT COUNT(*) FROM file_hashes", [], |r| r.get(0))?;
39    let total_size_bytes: i64 =
40        conn.query_row("SELECT COALESCE(SUM(size_bytes), 0) FROM file_hashes", [], |r| r.get(0))?;
41    let total_photos: i64 = conn.query_row(
42        &format!("SELECT COUNT(*) FROM file_hashes WHERE lower(ext) IN ({PHOTO_EXTS})"),
43        [],
44        |r| r.get(0),
45    )?;
46    let total_videos: i64 = conn.query_row(
47        &format!("SELECT COUNT(*) FROM file_hashes WHERE lower(ext) IN ({VIDEO_EXTS})"),
48        [],
49        |r| r.get(0),
50    )?;
51
52    let duplicate_group_count: i64 = conn.query_row(
53        "SELECT COUNT(*) FROM \
54         (SELECT hash FROM file_hashes GROUP BY hash HAVING COUNT(*) > 1)",
55        [],
56        |r| r.get(0),
57    )?;
58    let duplicate_file_count: i64 = conn.query_row(
59        "SELECT COUNT(*) FROM file_hashes \
60         WHERE hash IN (SELECT hash FROM file_hashes GROUP BY hash HAVING COUNT(*) > 1)",
61        [],
62        |r| r.get(0),
63    )?;
64    let wasted_bytes: i64 = conn.query_row(
65        "SELECT COALESCE(SUM(size_bytes * (cnt - 1)), 0) FROM \
66         (SELECT hash, size_bytes, COUNT(*) as cnt \
67          FROM file_hashes GROUP BY hash HAVING cnt > 1)",
68        [],
69        |r| r.get(0),
70    )?;
71
72    let (faces_detected, people_named) = if table_exists(conn, "faces")? {
73        let faces_detected: i64 = conn.query_row("SELECT COUNT(*) FROM faces", [], |r| r.get(0))?;
74        let people_named: i64 = conn.query_row(
75            "SELECT COUNT(DISTINCT person_label) FROM faces \
76             WHERE confirmed = 1 AND person_label IS NOT NULL",
77            [],
78            |r| r.get(0),
79        )?;
80        (faces_detected, people_named)
81    } else {
82        (0, 0)
83    };
84
85    Ok(LibraryStats {
86        total_files,
87        total_size_bytes,
88        total_photos,
89        total_videos,
90        duplicate_group_count,
91        duplicate_file_count,
92        wasted_bytes,
93        faces_detected,
94        people_named,
95        embeddings: Vec::new(),
96    })
97}
98
99/// `compute`, plus the per-model embedding inventory.
100///
101/// Separate from `compute` because embeddings live outside the connection, in
102/// files addressed by the library's own path. `videre report`'s stats tile
103/// keeps calling `compute` and is unaffected.
104pub fn compute_full(conn: &Connection, db_path: &std::path::Path) -> anyhow::Result<LibraryStats> {
105    let mut stats = compute(conn)?;
106    stats.embeddings = crate::embeddings_db::counts_by_model(db_path)?;
107    Ok(stats)
108}
109
110#[cfg(test)]
111mod tests {
112    use super::*;
113
114    fn test_db() -> Connection {
115        let conn = Connection::open_in_memory().unwrap();
116        conn.execute_batch(
117            "CREATE TABLE file_hashes (
118                path        TEXT PRIMARY KEY,
119                hash        TEXT NOT NULL,
120                size_bytes  INTEGER,
121                ext         TEXT
122            );",
123        )
124        .unwrap();
125        conn
126    }
127
128    fn insert_file(conn: &Connection, path: &str, hash: &str, size_bytes: i64, ext: &str) {
129        conn.execute(
130            "INSERT INTO file_hashes (path, hash, size_bytes, ext) VALUES (?1, ?2, ?3, ?4)",
131            rusqlite::params![path, hash, size_bytes, ext],
132        )
133        .unwrap();
134    }
135
136    #[test]
137    fn compute_counts_total_files_and_size() {
138        let conn = test_db();
139        insert_file(&conn, "/a/1.jpg", "h1", 1000, "jpg");
140        insert_file(&conn, "/a/2.png", "h2", 2500, "png");
141
142        let stats = compute(&conn).unwrap();
143        assert_eq!(stats.total_files, 2);
144        assert_eq!(stats.total_size_bytes, 3500);
145    }
146
147    #[test]
148    fn compute_on_empty_db_returns_zeros() {
149        let conn = test_db();
150        let stats = compute(&conn).unwrap();
151        assert_eq!(stats.total_files, 0);
152        assert_eq!(stats.total_size_bytes, 0);
153    }
154
155    #[test]
156    fn compute_splits_photos_and_videos_by_extension() {
157        let conn = test_db();
158        insert_file(&conn, "/a/1.jpg", "h1", 100, "jpg");
159        insert_file(&conn, "/a/2.heic", "h2", 100, "heic");
160        insert_file(&conn, "/a/3.mov", "h3", 100, "mov");
161        insert_file(&conn, "/a/4.mp4", "h4", 100, "mp4");
162        insert_file(&conn, "/a/5.unknown", "h5", 100, "xyz");
163
164        let stats = compute(&conn).unwrap();
165        assert_eq!(stats.total_photos, 2);
166        assert_eq!(stats.total_videos, 2);
167        assert_eq!(stats.total_files, 5); // unrecognized ext still counts toward total_files
168    }
169
170    #[test]
171    fn compute_counts_video_exts_case_insensitively() {
172        let conn = test_db();
173        insert_file(&conn, "/a/1.MOV", "h1", 100, "MOV");
174        insert_file(&conn, "/a/2.Mp4", "h2", 100, "Mp4");
175        insert_file(&conn, "/a/3.mov", "h3", 100, "mov");
176
177        let stats = compute(&conn).unwrap();
178        assert_eq!(stats.total_videos, 3); // uppercase/mixed-case exts still count as video
179    }
180
181    #[test]
182    fn compute_counts_duplicate_groups_and_wasted_bytes() {
183        let conn = test_db();
184        insert_file(&conn, "/a/1.jpg", "dup-hash", 1000, "jpg");
185        insert_file(&conn, "/b/1-copy.jpg", "dup-hash", 1000, "jpg");
186        insert_file(&conn, "/a/2.jpg", "dup-hash", 1000, "jpg");
187        insert_file(&conn, "/a/3.jpg", "unique-hash", 500, "jpg");
188
189        let stats = compute(&conn).unwrap();
190        assert_eq!(stats.duplicate_group_count, 1);
191        assert_eq!(stats.duplicate_file_count, 3); // all 3 members of the dup group
192        assert_eq!(stats.wasted_bytes, 2000); // (3 - 1) * 1000
193    }
194
195    #[test]
196    fn compute_with_no_duplicates_reports_zero() {
197        let conn = test_db();
198        insert_file(&conn, "/a/1.jpg", "h1", 500, "jpg");
199        insert_file(&conn, "/a/2.jpg", "h2", 500, "jpg");
200
201        let stats = compute(&conn).unwrap();
202        assert_eq!(stats.duplicate_group_count, 0);
203        assert_eq!(stats.duplicate_file_count, 0);
204        assert_eq!(stats.wasted_bytes, 0);
205    }
206
207    #[test]
208    fn compute_counts_faces_and_named_people() {
209        let conn = test_db();
210        conn.execute_batch(
211            "CREATE TABLE faces (
212                id            INTEGER PRIMARY KEY,
213                hash          TEXT NOT NULL,
214                bbox          TEXT NOT NULL,
215                landmark      TEXT,
216                embedding     BLOB NOT NULL,
217                cluster_id    INTEGER,
218                person_label  TEXT,
219                confirmed     INTEGER DEFAULT 0,
220                is_primary    INTEGER DEFAULT 0
221            );",
222        )
223        .unwrap();
224        conn.execute(
225            "INSERT INTO faces (id, hash, bbox, embedding, person_label, confirmed) \
226             VALUES (1, 'h1', '[]', X'00', 'Alice', 1)",
227            [],
228        )
229        .unwrap();
230        conn.execute(
231            "INSERT INTO faces (id, hash, bbox, embedding, person_label, confirmed) \
232             VALUES (2, 'h1', '[]', X'00', 'Alice', 1)",
233            [],
234        )
235        .unwrap();
236        conn.execute(
237            "INSERT INTO faces (id, hash, bbox, embedding, person_label, confirmed) \
238             VALUES (3, 'h2', '[]', X'00', NULL, 0)",
239            [],
240        )
241        .unwrap();
242
243        let stats = compute(&conn).unwrap();
244        assert_eq!(stats.faces_detected, 3);
245        assert_eq!(stats.people_named, 1); // distinct confirmed person_label
246    }
247
248    #[test]
249    fn compute_without_faces_table_returns_zero_not_error() {
250        let conn = test_db(); // no faces table created
251        let stats = compute(&conn).unwrap();
252        assert_eq!(stats.faces_detected, 0);
253        assert_eq!(stats.people_named, 0);
254    }
255}
videre_core/library_stats.rs

videre_core/
library_stats.rs
