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#[cfg(test)]
166mod tests {
167    use super::*;
168
169    fn test_db() -> Connection {
170        let conn = Connection::open_in_memory().unwrap();
171        conn.execute_batch(
172            "CREATE TABLE file_hashes (
173                path        TEXT PRIMARY KEY,
174                hash        TEXT NOT NULL,
175                size_bytes  INTEGER,
176                ext         TEXT
177            );",
178        )
179        .unwrap();
180        crate::library_db::ensure_scan_schema(&conn).unwrap();
181        conn
182    }
183
184    fn insert_file(conn: &Connection, path: &str, hash: &str, size_bytes: i64, ext: &str) {
185        conn.execute(
186            "INSERT INTO file_hashes (path, hash, size_bytes, ext) VALUES (?1, ?2, ?3, ?4)",
187            rusqlite::params![path, hash, size_bytes, ext],
188        )
189        .unwrap();
190    }
191
192    #[test]
193    fn compute_counts_total_files_and_size() {
194        let conn = test_db();
195        insert_file(&conn, "/a/1.jpg", "h1", 1000, "jpg");
196        insert_file(&conn, "/a/2.png", "h2", 2500, "png");
197
198        let stats = compute(&conn).unwrap();
199        assert_eq!(stats.total_files, 2);
200        assert_eq!(stats.total_size_bytes, 3500);
201    }
202
203    #[test]
204    fn compute_on_empty_db_returns_zeros() {
205        let conn = test_db();
206        let stats = compute(&conn).unwrap();
207        assert_eq!(stats.total_files, 0);
208        assert_eq!(stats.total_size_bytes, 0);
209    }
210
211    #[test]
212    fn compute_splits_photos_and_videos_by_extension() {
213        let conn = test_db();
214        insert_file(&conn, "/a/1.jpg", "h1", 100, "jpg");
215        insert_file(&conn, "/a/2.heic", "h2", 100, "heic");
216        insert_file(&conn, "/a/3.mov", "h3", 100, "mov");
217        insert_file(&conn, "/a/4.mp4", "h4", 100, "mp4");
218        insert_file(&conn, "/a/5.unknown", "h5", 100, "xyz");
219
220        let stats = compute(&conn).unwrap();
221        assert_eq!(stats.total_photos, 2);
222        assert_eq!(stats.total_videos, 2);
223        assert_eq!(stats.total_files, 5); // unrecognized ext still counts toward total_files
224    }
225
226    #[test]
227    fn compute_counts_video_exts_case_insensitively() {
228        let conn = test_db();
229        insert_file(&conn, "/a/1.MOV", "h1", 100, "MOV");
230        insert_file(&conn, "/a/2.Mp4", "h2", 100, "Mp4");
231        insert_file(&conn, "/a/3.mov", "h3", 100, "mov");
232
233        let stats = compute(&conn).unwrap();
234        assert_eq!(stats.total_videos, 3); // uppercase/mixed-case exts still count as video
235    }
236
237    #[test]
238    fn compute_counts_duplicate_groups_and_wasted_bytes() {
239        let conn = test_db();
240        insert_file(&conn, "/a/1.jpg", "dup-hash", 1000, "jpg");
241        insert_file(&conn, "/b/1-copy.jpg", "dup-hash", 1000, "jpg");
242        insert_file(&conn, "/a/2.jpg", "dup-hash", 1000, "jpg");
243        insert_file(&conn, "/a/3.jpg", "unique-hash", 500, "jpg");
244
245        let stats = compute(&conn).unwrap();
246        assert_eq!(stats.duplicate_group_count, 1);
247        assert_eq!(stats.duplicate_file_count, 3); // all 3 members of the dup group
248        assert_eq!(stats.wasted_bytes, 2000); // (3 - 1) * 1000
249    }
250
251    #[test]
252    fn compute_with_no_duplicates_reports_zero() {
253        let conn = test_db();
254        insert_file(&conn, "/a/1.jpg", "h1", 500, "jpg");
255        insert_file(&conn, "/a/2.jpg", "h2", 500, "jpg");
256
257        let stats = compute(&conn).unwrap();
258        assert_eq!(stats.duplicate_group_count, 0);
259        assert_eq!(stats.duplicate_file_count, 0);
260        assert_eq!(stats.wasted_bytes, 0);
261    }
262
263    #[test]
264    fn compute_counts_faces_and_named_people() {
265        let conn = test_db();
266        conn.execute_batch(
267            "CREATE TABLE faces (
268                id            INTEGER PRIMARY KEY,
269                hash          TEXT NOT NULL,
270                bbox          TEXT NOT NULL,
271                landmark      TEXT,
272                embedding     BLOB NOT NULL,
273                cluster_id    INTEGER,
274                person_label  TEXT,
275                confirmed     INTEGER DEFAULT 0,
276                is_primary    INTEGER DEFAULT 0
277            );",
278        )
279        .unwrap();
280        conn.execute(
281            "INSERT INTO faces (id, hash, bbox, embedding, person_label, confirmed) \
282             VALUES (1, 'h1', '[]', X'00', 'Alice', 1)",
283            [],
284        )
285        .unwrap();
286        conn.execute(
287            "INSERT INTO faces (id, hash, bbox, embedding, person_label, confirmed) \
288             VALUES (2, 'h1', '[]', X'00', 'Alice', 1)",
289            [],
290        )
291        .unwrap();
292        conn.execute(
293            "INSERT INTO faces (id, hash, bbox, embedding, person_label, confirmed) \
294             VALUES (3, 'h2', '[]', X'00', NULL, 0)",
295            [],
296        )
297        .unwrap();
298
299        let stats = compute(&conn).unwrap();
300        assert_eq!(stats.faces_detected, 3);
301        assert_eq!(stats.people_named, 1); // distinct confirmed person_label
302    }
303
304    #[test]
305    fn compute_without_faces_table_returns_zero_not_error() {
306        let conn = test_db(); // no faces table created
307        let stats = compute(&conn).unwrap();
308        assert_eq!(stats.faces_detected, 0);
309        assert_eq!(stats.people_named, 0);
310    }
311
312    #[test]
313    fn compute_full_in_reads_models_without_creating_missing_stores() {
314        let temp = tempfile::tempdir().unwrap();
315        let root = temp.path().join("library");
316        let cache = temp.path().join("cache");
317        std::fs::create_dir(&root).unwrap();
318        let ctx = crate::library::LibraryContext::new(&root, &cache).unwrap();
319        let conn = crate::library_db::initialize(&ctx).unwrap();
320        let stats = compute_full_in(&conn, &ctx).unwrap();
321        assert!(stats.embeddings.is_empty());
322        assert!(!ctx.paths.embeddings.exists());
323    }
324}
videre_core/library_stats.rs

videre_core/
library_stats.rs
