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

videre_core/
library_stats.rs
