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

videre_core/
library_stats.rs
