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//! 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    /// Counts of rated/picked/labelled/liked photos.
22    #[serde(default)]
23    pub marks: crate::marks::MarksSummary,
24    /// One entry per model with an embedding database for this library.
25    /// Empty when nothing has been embedded, which is a normal state.
26    #[serde(default)]
27    pub embeddings: Vec<crate::embeddings_db::ModelEmbeddingCount>,
28}
29
30use crate::db::table_exists;
31
32const PHOTO_MIME_LIST: &str =
33    "'image/jpeg','image/png','image/gif','image/webp','image/bmp','image/tiff','image/heic'";
34const VIDEO_MIME_LIST: &str = "'video/quicktime','video/mp4'";
35
36const PHOTO_EXTS: &str = "'jpg','jpeg','png','gif','webp','bmp','tiff','heic','dng'";
37const VIDEO_EXTS: &str = "'mov','mp4'";
38
39// `VIDEO_EXTS`'s values must stay in sync with
40// `crate::embeddings::is_video_ext` (the shared "is this a video" check).
41// The SQL below uses `lower(ext)` so this list stays case-insensitive,
42// matching that helper.
43
44pub fn compute(conn: &Connection) -> Result<LibraryStats> {
45    let total_files: i64 = conn.query_row("SELECT COUNT(*) FROM file_hashes", [], |r| r.get(0))?;
46    let total_size_bytes: i64 = conn.query_row(
47        "SELECT COALESCE(SUM(size_bytes), 0) FROM file_hashes",
48        [],
49        |r| r.get(0),
50    )?;
51    let total_photos: i64 = conn.query_row(
52        &format!(
53            "SELECT COUNT(*) FROM file_hashes
54             WHERE mime IN ({PHOTO_MIME_LIST})
55                OR (mime IS NULL AND lower(ext) IN ({PHOTO_EXTS}))"
56        ),
57        [],
58        |r| r.get(0),
59    )?;
60    let total_videos: i64 = conn.query_row(
61        &format!(
62            "SELECT COUNT(*) FROM file_hashes
63             WHERE mime IN ({VIDEO_MIME_LIST})
64                OR (mime IS NULL AND lower(ext) IN ({VIDEO_EXTS}))"
65        ),
66        [],
67        |r| r.get(0),
68    )?;
69
70    let duplicate_group_count: i64 = conn.query_row(
71        "SELECT COUNT(*) FROM \
72         (SELECT hash FROM file_hashes GROUP BY hash HAVING COUNT(*) > 1)",
73        [],
74        |r| r.get(0),
75    )?;
76    let duplicate_file_count: i64 = conn.query_row(
77        "SELECT COUNT(*) FROM file_hashes \
78         WHERE hash IN (SELECT hash FROM file_hashes GROUP BY hash HAVING COUNT(*) > 1)",
79        [],
80        |r| r.get(0),
81    )?;
82    let wasted_bytes: i64 = conn.query_row(
83        "SELECT COALESCE(SUM(size_bytes * (cnt - 1)), 0) FROM \
84         (SELECT hash, size_bytes, COUNT(*) as cnt \
85          FROM file_hashes GROUP BY hash HAVING cnt > 1)",
86        [],
87        |r| r.get(0),
88    )?;
89
90    let (faces_detected, people_named) = if table_exists(conn, "faces")? {
91        let faces_detected: i64 = conn.query_row("SELECT COUNT(*) FROM faces", [], |r| r.get(0))?;
92        let people_named: i64 = conn.query_row(
93            "SELECT COUNT(DISTINCT person_label) FROM faces \
94             WHERE confirmed = 1 AND person_label IS NOT NULL",
95            [],
96            |r| r.get(0),
97        )?;
98        (faces_detected, people_named)
99    } else {
100        (0, 0)
101    };
102
103    Ok(LibraryStats {
104        total_files,
105        total_size_bytes,
106        total_photos,
107        total_videos,
108        duplicate_group_count,
109        duplicate_file_count,
110        wasted_bytes,
111        faces_detected,
112        people_named,
113        marks: crate::marks::summary(conn)?,
114        embeddings: Vec::new(),
115    })
116}
117
118/// Compute database and model-store statistics for one selected library.
119pub fn compute_full_in(
120    conn: &Connection,
121    ctx: &crate::library::LibraryContext,
122) -> anyhow::Result<LibraryStats> {
123    ctx.ensure_root_identity()?;
124    let mut stats = compute(conn)?;
125    stats.embeddings = crate::embeddings_db::counts_by_model_in(ctx)?;
126    Ok(stats)
127}
128
129/// What the library is made of, by file type.
130///
131/// `stats` could say how many files there were and how big they were in total,
132/// but not what they *are* - and "70,601 files, 480GB" answers a different
133/// question from "12,000 of those are HEIC and they are 22GB of it".
134///
135/// Grouped by extension rather than mime, with the mime shown alongside,
136/// because extension is what a user recognises and types into `--ext`.
137#[derive(Debug, Clone, Serialize)]
138pub struct TypeBreakdown {
139    pub ext: String,
140    pub mime: String,
141    pub files: i64,
142    pub bytes: i64,
143}
144
145pub fn by_type(conn: &rusqlite::Connection, limit: usize) -> rusqlite::Result<Vec<TypeBreakdown>> {
146    let mut stmt = conn.prepare(
147        "SELECT LOWER(COALESCE(NULLIF(ext,''),'(none)')),
148                COALESCE(NULLIF(mime,''),'(unknown)'),
149                COUNT(*), COALESCE(SUM(size_bytes),0)
150           FROM file_hashes
151          GROUP BY 1, 2
152          ORDER BY 4 DESC",
153    )?;
154    let rows = stmt.query_map([], |r| {
155        Ok(TypeBreakdown {
156            ext: r.get(0)?,
157            mime: r.get(1)?,
158            files: r.get(2)?,
159            bytes: r.get(3)?,
160        })
161    })?;
162    let mut out: Vec<TypeBreakdown> = rows.collect::<rusqlite::Result<_>>()?;
163    out.truncate(limit);
164    Ok(out)
165}
166
167#[cfg(test)]
168mod tests {
169    use super::*;
170
171    fn test_db() -> Connection {
172        let conn = Connection::open_in_memory().unwrap();
173        conn.execute_batch(
174            "CREATE TABLE file_hashes (
175                path        TEXT PRIMARY KEY,
176                hash        TEXT NOT NULL,
177                size_bytes  INTEGER,
178                ext         TEXT
179            );",
180        )
181        .unwrap();
182        crate::library_db::ensure_scan_schema(&conn).unwrap();
183        conn
184    }
185
186    fn insert_file(conn: &Connection, path: &str, hash: &str, size_bytes: i64, ext: &str) {
187        conn.execute(
188            "INSERT INTO file_hashes (path, hash, size_bytes, ext) VALUES (?1, ?2, ?3, ?4)",
189            rusqlite::params![path, hash, size_bytes, ext],
190        )
191        .unwrap();
192    }
193
194    #[test]
195    fn compute_counts_total_files_and_size() {
196        let conn = test_db();
197        insert_file(&conn, "/a/1.jpg", "h1", 1000, "jpg");
198        insert_file(&conn, "/a/2.png", "h2", 2500, "png");
199
200        let stats = compute(&conn).unwrap();
201        assert_eq!(stats.total_files, 2);
202        assert_eq!(stats.total_size_bytes, 3500);
203    }
204
205    #[test]
206    fn compute_on_empty_db_returns_zeros() {
207        let conn = test_db();
208        let stats = compute(&conn).unwrap();
209        assert_eq!(stats.total_files, 0);
210        assert_eq!(stats.total_size_bytes, 0);
211    }
212
213    #[test]
214    fn compute_splits_photos_and_videos_by_extension() {
215        let conn = test_db();
216        insert_file(&conn, "/a/1.jpg", "h1", 100, "jpg");
217        insert_file(&conn, "/a/2.heic", "h2", 100, "heic");
218        insert_file(&conn, "/a/3.mov", "h3", 100, "mov");
219        insert_file(&conn, "/a/4.mp4", "h4", 100, "mp4");
220        insert_file(&conn, "/a/5.unknown", "h5", 100, "xyz");
221
222        let stats = compute(&conn).unwrap();
223        assert_eq!(stats.total_photos, 2);
224        assert_eq!(stats.total_videos, 2);
225        assert_eq!(stats.total_files, 5); // unrecognized ext still counts toward total_files
226    }
227
228    #[test]
229    fn compute_counts_video_exts_case_insensitively() {
230        let conn = test_db();
231        insert_file(&conn, "/a/1.MOV", "h1", 100, "MOV");
232        insert_file(&conn, "/a/2.Mp4", "h2", 100, "Mp4");
233        insert_file(&conn, "/a/3.mov", "h3", 100, "mov");
234
235        let stats = compute(&conn).unwrap();
236        assert_eq!(stats.total_videos, 3); // uppercase/mixed-case exts still count as video
237    }
238
239    #[test]
240    fn compute_counts_duplicate_groups_and_wasted_bytes() {
241        let conn = test_db();
242        insert_file(&conn, "/a/1.jpg", "dup-hash", 1000, "jpg");
243        insert_file(&conn, "/b/1-copy.jpg", "dup-hash", 1000, "jpg");
244        insert_file(&conn, "/a/2.jpg", "dup-hash", 1000, "jpg");
245        insert_file(&conn, "/a/3.jpg", "unique-hash", 500, "jpg");
246
247        let stats = compute(&conn).unwrap();
248        assert_eq!(stats.duplicate_group_count, 1);
249        assert_eq!(stats.duplicate_file_count, 3); // all 3 members of the dup group
250        assert_eq!(stats.wasted_bytes, 2000); // (3 - 1) * 1000
251    }
252
253    #[test]
254    fn compute_with_no_duplicates_reports_zero() {
255        let conn = test_db();
256        insert_file(&conn, "/a/1.jpg", "h1", 500, "jpg");
257        insert_file(&conn, "/a/2.jpg", "h2", 500, "jpg");
258
259        let stats = compute(&conn).unwrap();
260        assert_eq!(stats.duplicate_group_count, 0);
261        assert_eq!(stats.duplicate_file_count, 0);
262        assert_eq!(stats.wasted_bytes, 0);
263    }
264
265    #[test]
266    fn compute_counts_faces_and_named_people() {
267        let conn = test_db();
268        conn.execute_batch(
269            "CREATE TABLE faces (
270                id            INTEGER PRIMARY KEY,
271                hash          TEXT NOT NULL,
272                bbox          TEXT NOT NULL,
273                landmark      TEXT,
274                embedding     BLOB NOT NULL,
275                cluster_id    INTEGER,
276                person_label  TEXT,
277                confirmed     INTEGER DEFAULT 0,
278                is_primary    INTEGER DEFAULT 0
279            );",
280        )
281        .unwrap();
282        conn.execute(
283            "INSERT INTO faces (id, hash, bbox, embedding, person_label, confirmed) \
284             VALUES (1, 'h1', '[]', X'00', 'Alice', 1)",
285            [],
286        )
287        .unwrap();
288        conn.execute(
289            "INSERT INTO faces (id, hash, bbox, embedding, person_label, confirmed) \
290             VALUES (2, 'h1', '[]', X'00', 'Alice', 1)",
291            [],
292        )
293        .unwrap();
294        conn.execute(
295            "INSERT INTO faces (id, hash, bbox, embedding, person_label, confirmed) \
296             VALUES (3, 'h2', '[]', X'00', NULL, 0)",
297            [],
298        )
299        .unwrap();
300
301        let stats = compute(&conn).unwrap();
302        assert_eq!(stats.faces_detected, 3);
303        assert_eq!(stats.people_named, 1); // distinct confirmed person_label
304    }
305
306    #[test]
307    fn compute_without_faces_table_returns_zero_not_error() {
308        let conn = test_db(); // no faces table created
309        let stats = compute(&conn).unwrap();
310        assert_eq!(stats.faces_detected, 0);
311        assert_eq!(stats.people_named, 0);
312    }
313
314    #[test]
315    fn compute_full_in_reads_models_without_creating_missing_stores() {
316        let temp = tempfile::tempdir().unwrap();
317        let root = temp.path().join("library");
318        let cache = temp.path().join("cache");
319        std::fs::create_dir(&root).unwrap();
320        let ctx = crate::library::LibraryContext::new(&root, &cache).unwrap();
321        let conn = crate::library_db::initialize(&ctx).unwrap();
322        let stats = compute_full_in(&conn, &ctx).unwrap();
323        assert!(stats.embeddings.is_empty());
324        assert!(!ctx.paths.embeddings.exists());
325    }
326}
videre_core/library_stats.rs

videre_core/
library_stats.rs
