smriti/db/
album_repo.rs

1//! Album database operations
2
3use rusqlite::{params, types::ToSql, Connection, Result as SqliteResult};
4
5use super::MAX_ROWS_PER_INSERT;
6
7/// Album record for list views (cover + summary info)
8#[allow(dead_code)]
9#[derive(Debug, Clone)]
10pub struct AlbumRecord {
11    pub id: i64,
12    pub name: String,
13    pub cover_photo_id: Option<i64>,
14    pub cover_auto_picked: bool,
15    pub photo_count: i64,
16    /// Earliest date_taken among album photos (ISO string or None)
17    pub date_range_start: Option<String>,
18    /// Latest date_taken among album photos (ISO string or None)
19    pub date_range_end: Option<String>,
20    pub created_at: String,
21    pub updated_at: String,
22    pub created_by: String,
23    /// Resolved absolute thumbnail path for the cover photo (set during loading, not from DB)
24    pub cover_thumbnail_path: Option<String>,
25}
26
27pub struct AlbumRepo<'a> {
28    conn: &'a Connection,
29}
30
31impl<'a> AlbumRepo<'a> {
32    pub fn new(conn: &'a Connection) -> Self {
33        Self { conn }
34    }
35
36    /// Create a new album with the given name. Returns the new album ID.
37    pub fn create(&self, name: &str) -> SqliteResult<i64> {
38        self.create_with_source(name, "user")
39    }
40
41    /// Create a new album and mark who created it.
42    pub fn create_with_source(&self, name: &str, created_by: &str) -> SqliteResult<i64> {
43        self.conn.execute(
44            "INSERT INTO albums (name, created_by) VALUES (?1, ?2)",
45            params![name, created_by],
46        )?;
47        Ok(self.conn.last_insert_rowid())
48    }
49
50    /// Rename an existing album.
51    pub fn rename(&self, album_id: i64, name: &str) -> SqliteResult<()> {
52        self.conn.execute(
53            "UPDATE albums SET name = ?1, updated_at = CURRENT_TIMESTAMP WHERE id = ?2",
54            params![name, album_id],
55        )?;
56        Ok(())
57    }
58
59    /// Delete an album and its photo associations. Photos themselves are NOT trashed.
60    pub fn delete(&self, album_id: i64) -> SqliteResult<()> {
61        self.conn.execute(
62            "DELETE FROM album_photos WHERE album_id = ?1",
63            params![album_id],
64        )?;
65        self.conn
66            .execute("DELETE FROM albums WHERE id = ?1", params![album_id])?;
67        Ok(())
68    }
69
70    /// Add photos to an album. Returns count of newly added (ignores duplicates).
71    pub fn add_photos(&self, album_id: i64, photo_ids: &[i64]) -> SqliteResult<usize> {
72        if photo_ids.is_empty() {
73            return Ok(0);
74        }
75        let tx = self.conn.unchecked_transaction()?;
76        let mut added = 0usize;
77        for chunk in photo_ids.chunks(MAX_ROWS_PER_INSERT) {
78            let candidate_sql = chunk
79                .iter()
80                .enumerate()
81                .map(|(idx, _)| {
82                    if idx == 0 {
83                        "SELECT ? AS photo_id"
84                    } else {
85                        "UNION ALL SELECT ?"
86                    }
87                })
88                .collect::<Vec<_>>()
89                .join(" ");
90            let sql = format!(
91                r#"
92                INSERT OR IGNORE INTO album_photos (album_id, photo_id)
93                SELECT ?, candidate.photo_id
94                  FROM ({candidate_sql}) AS candidate
95                  JOIN photos p ON p.id = candidate.photo_id
96                 WHERE p.is_trashed = FALSE
97                "#
98            );
99            let mut values: Vec<Box<dyn ToSql>> = Vec::with_capacity(chunk.len() + 1);
100            values.push(Box::new(album_id));
101            for pid in chunk {
102                values.push(Box::new(*pid));
103            }
104            let refs: Vec<&dyn ToSql> = values.iter().map(|v| v.as_ref()).collect();
105            added += tx.execute(&sql, refs.as_slice())?;
106        }
107        update_album_stats_in_conn(&tx, album_id)?;
108        tx.commit()?;
109        Ok(added)
110    }
111
112    /// Remove photos from an album. Returns the number actually removed.
113    pub fn remove_photos(&self, album_id: i64, photo_ids: &[i64]) -> SqliteResult<usize> {
114        if photo_ids.is_empty() {
115            return Ok(0);
116        }
117        let tx = self.conn.unchecked_transaction()?;
118        let mut removed = 0usize;
119        for chunk in photo_ids.chunks(MAX_ROWS_PER_INSERT) {
120            let placeholders = (0..chunk.len()).map(|_| "?").collect::<Vec<_>>().join(", ");
121            let sql = format!(
122                "DELETE FROM album_photos WHERE album_id = ? AND photo_id IN ({placeholders})"
123            );
124            let mut values: Vec<Box<dyn ToSql>> = Vec::with_capacity(chunk.len() + 1);
125            values.push(Box::new(album_id));
126            for pid in chunk {
127                values.push(Box::new(*pid));
128            }
129            let refs: Vec<&dyn ToSql> = values.iter().map(|v| v.as_ref()).collect();
130            removed += tx.execute(&sql, refs.as_slice())?;
131        }
132        update_album_stats_in_conn(&tx, album_id)?;
133        tx.commit()?;
134        Ok(removed)
135    }
136
137    /// Get all albums ordered by most recently updated.
138    pub fn get_all(&self) -> SqliteResult<Vec<AlbumRecord>> {
139        let mut stmt = self.conn.prepare(
140            r#"
141            SELECT a.id, a.name, a.cover_photo_id, a.cover_auto_picked,
142                   COUNT(p.id) AS live_photo_count, a.created_at, a.updated_at,
143                   MIN(p.date_taken) AS date_range_start,
144                   MAX(p.date_taken) AS date_range_end,
145                   pcov.id AS live_cover_photo_id,
146                   COALESCE(a.created_by, 'user') AS created_by,
147                   pcov.thumbnail_path AS cover_thumbnail_path
148            FROM albums a
149            LEFT JOIN album_photos ap ON a.id = ap.album_id
150            LEFT JOIN photos p ON ap.photo_id = p.id AND p.is_trashed = FALSE
151            LEFT JOIN photos pcov ON pcov.id = a.cover_photo_id AND pcov.is_trashed = FALSE
152            GROUP BY a.id
153            ORDER BY a.updated_at DESC
154            "#,
155        )?;
156
157        let rows = stmt.query_map([], |row| {
158            Ok(AlbumRecord {
159                id: row.get(0)?,
160                name: row.get(1)?,
161                cover_photo_id: row.get(9)?,
162                cover_auto_picked: row.get::<_, bool>(3).unwrap_or(true),
163                photo_count: row.get(4)?,
164                created_at: row.get(5)?,
165                updated_at: row.get(6)?,
166                date_range_start: row.get(7)?,
167                date_range_end: row.get(8)?,
168                cover_thumbnail_path: row.get(11)?,
169                created_by: row.get(10)?,
170            })
171        })?;
172
173        let mut albums = Vec::new();
174        for r in rows {
175            albums.push(r?);
176        }
177        Ok(albums)
178    }
179
180    /// Get ordered photo IDs for an album.
181    pub fn get_album_photo_ids(&self, album_id: i64) -> SqliteResult<Vec<i64>> {
182        let mut stmt = self.conn.prepare(
183            r#"
184            SELECT ap.photo_id FROM album_photos ap
185            JOIN photos p ON ap.photo_id = p.id
186            WHERE ap.album_id = ?1
187              AND p.is_trashed = FALSE
188            ORDER BY p.date_taken IS NULL ASC, p.date_taken DESC, p.id DESC
189            "#,
190        )?;
191
192        let rows = stmt.query_map(params![album_id], |row| row.get(0))?;
193        let mut ids = Vec::new();
194        for r in rows {
195            ids.push(r?);
196        }
197        Ok(ids)
198    }
199
200    /// Get album memberships for a photo: (album_id, album_name) pairs.
201    pub fn get_albums_for_photo(&self, photo_id: i64) -> SqliteResult<Vec<(i64, String)>> {
202        let mut stmt = self.conn.prepare(
203            r#"
204            SELECT a.id, a.name FROM albums a
205            JOIN album_photos ap ON a.id = ap.album_id
206            JOIN photos p ON p.id = ap.photo_id
207            WHERE ap.photo_id = ?1
208              AND p.is_trashed = FALSE
209            ORDER BY a.name ASC
210            "#,
211        )?;
212
213        let rows = stmt.query_map(params![photo_id], |row| Ok((row.get(0)?, row.get(1)?)))?;
214
215        let mut result = Vec::new();
216        for r in rows {
217            result.push(r?);
218        }
219        Ok(result)
220    }
221
222    /// Auto-pick a cover photo for the album (prefers faces, landscape, newest).
223    pub fn auto_pick_cover(&self, album_id: i64) -> SqliteResult<()> {
224        auto_pick_cover_in_conn(self.conn, album_id)
225    }
226
227    /// Recompute persisted album counters and refresh auto-picked cover.
228    pub fn refresh_stats(&self, album_id: i64) -> SqliteResult<()> {
229        self.update_album_stats(album_id)
230    }
231
232    /// Update photo_count and optionally auto-pick cover.
233    fn update_album_stats(&self, album_id: i64) -> SqliteResult<()> {
234        update_album_stats_in_conn(self.conn, album_id)
235    }
236}
237
238fn update_album_stats_in_conn(conn: &Connection, album_id: i64) -> SqliteResult<()> {
239    conn.execute(
240        r#"
241        UPDATE albums SET
242          photo_count = (
243            SELECT COUNT(*)
244              FROM album_photos ap
245              JOIN photos p ON p.id = ap.photo_id
246             WHERE ap.album_id = ?1
247               AND p.is_trashed = FALSE
248          ),
249          updated_at = CURRENT_TIMESTAMP
250        WHERE id = ?1
251        "#,
252        params![album_id],
253    )?;
254
255    // Check if cover is auto-picked
256    let auto_picked: bool = conn
257        .query_row(
258            "SELECT cover_auto_picked FROM albums WHERE id = ?1",
259            params![album_id],
260            |row| row.get(0),
261        )
262        .unwrap_or(true);
263
264    if auto_picked {
265        auto_pick_cover_in_conn(conn, album_id)?;
266    }
267
268    Ok(())
269}
270
271fn auto_pick_cover_in_conn(conn: &Connection, album_id: i64) -> SqliteResult<()> {
272    let cover_id: Option<i64> = conn
273        .query_row(
274            r#"
275        SELECT ap.photo_id FROM album_photos ap
276        JOIN photos p ON ap.photo_id = p.id
277        LEFT JOIN faces f ON p.id = f.photo_id
278        WHERE ap.album_id = ?1 AND p.is_trashed = FALSE
279        GROUP BY ap.photo_id
280        ORDER BY
281          COUNT(f.id) > 0 DESC,
282          p.width > p.height DESC,
283          p.date_taken DESC
284        LIMIT 1
285        "#,
286            params![album_id],
287            |row| row.get(0),
288        )
289        .ok();
290
291    conn.execute(
292        "UPDATE albums SET cover_photo_id = ?1, cover_auto_picked = TRUE, updated_at = CURRENT_TIMESTAMP WHERE id = ?2",
293        params![cover_id, album_id],
294    )?;
295    Ok(())
296}
297
298#[cfg(test)]
299mod tests {
300    use super::*;
301
302    fn setup() -> Connection {
303        let conn = Connection::open_in_memory().expect("open in-memory db");
304        crate::db::create_schema(&conn).expect("create schema");
305        conn
306    }
307
308    fn insert_photo(conn: &Connection, id: i64, trashed: bool) {
309        conn.execute(
310            r#"
311            INSERT INTO photos
312                (id, file_path, file_name, file_hash, file_size, date_taken, is_trashed)
313            VALUES
314                (?1, ?2, ?3, ?4, ?5, ?6, ?7)
315            "#,
316            params![
317                id,
318                format!("IMG_{id:04}.jpg"),
319                format!("IMG_{id:04}.jpg"),
320                format!("hash-{id}"),
321                1000 + id,
322                format!("2024-01-01T12:0{id}:00Z"),
323                trashed,
324            ],
325        )
326        .expect("insert photo");
327    }
328
329    #[test]
330    fn add_photos_batches_live_rows_and_ignores_duplicates_or_trashed() {
331        let conn = setup();
332        for id in 1..=3 {
333            insert_photo(&conn, id, id == 3);
334        }
335        let repo = AlbumRepo::new(&conn);
336        let album_id = repo.create("Trip").unwrap();
337
338        let added = repo.add_photos(album_id, &[1, 2, 2, 3]).unwrap();
339
340        assert_eq!(added, 2);
341        let album = repo.get_all().unwrap().pop().unwrap();
342        assert_eq!(album.photo_count, 2);
343        assert!(album.cover_photo_id.is_some());
344    }
345
346    #[test]
347    fn remove_photos_reports_actual_removed_count_and_refreshes_stats() {
348        let conn = setup();
349        for id in 1..=3 {
350            insert_photo(&conn, id, false);
351        }
352        let repo = AlbumRepo::new(&conn);
353        let album_id = repo.create("Trip").unwrap();
354        repo.add_photos(album_id, &[1, 2, 3]).unwrap();
355
356        let removed = repo.remove_photos(album_id, &[2, 999]).unwrap();
357
358        assert_eq!(removed, 1);
359        let album = repo.get_all().unwrap().pop().unwrap();
360        assert_eq!(album.photo_count, 2);
361        assert_eq!(repo.get_album_photo_ids(album_id).unwrap(), vec![3, 1]);
362    }
363}
smriti/db/album_repo.rs

smriti/db/
album_repo.rs
