koan_core/db/queries/
albums.rs

1use rusqlite::{Connection, params};
2
3use crate::db::connection::DbError;
4
5use super::AlbumRow;
6
7/// The album column list, read the same way by every query that selects it.
8fn album_row(row: &rusqlite::Row) -> rusqlite::Result<AlbumRow> {
9    Ok(AlbumRow {
10        id: row.get(0)?,
11        title: row.get(1)?,
12        artist_id: row.get(2)?,
13        artist_name: row.get::<_, Option<String>>(3)?.unwrap_or_default(),
14        date: row.get(4)?,
15        total_discs: row.get(5)?,
16        total_tracks: row.get(6)?,
17        codec: row.get(7)?,
18        label: row.get(8)?,
19        remote_id: row.get(9)?,
20        added_at: row.get(10)?,
21    })
22}
23
24/// Get or create an album by title + artist. Returns the album ID.
25#[allow(clippy::too_many_arguments)]
26pub fn get_or_create_album(
27    conn: &Connection,
28    title: &str,
29    artist_id: i64,
30    date: Option<&str>,
31    total_discs: Option<i32>,
32    total_tracks: Option<i32>,
33    codec: Option<&str>,
34    label: Option<&str>,
35    // `added_at`: remote sync passes the server's `created`, a local scan the
36    // earliest mtime among the album's files. Both ISO 8601 UTC, so the two
37    // sources sort against each other.
38    remote_id: Option<&str>,
39    added_at: Option<&str>,
40) -> Result<i64, DbError> {
41    let existing: Option<i64> = conn
42        .query_row(
43            "SELECT id FROM albums WHERE title = ?1 AND artist_id = ?2",
44            params![title, artist_id],
45            |row| row.get(0),
46        )
47        .ok();
48
49    if let Some(id) = existing {
50        // Update mutable fields so rescans pick up format upgrades (e.g. MP3→FLAC),
51        // corrected dates, or newly-added remote IDs.
52        conn.execute(
53            "UPDATE albums SET
54                codec      = COALESCE(?1, codec),
55                date       = COALESCE(?2, date),
56                label      = COALESCE(?3, label),
57                remote_id  = COALESCE(?4, remote_id),
58                -- Earliest wins. A record acquired over months should date
59                -- from its first file, not its last, and filling only would
60                -- freeze whichever file the first scan happened to reach.
61                added_at   = MIN(COALESCE(added_at, ?5), COALESCE(?5, added_at))
62             WHERE id = ?6",
63            params![codec, date, label, remote_id, added_at, id],
64        )?;
65        return Ok(id);
66    }
67
68    conn.execute(
69        "INSERT INTO albums (title, artist_id, date, total_discs, total_tracks, codec, label, remote_id, added_at)
70         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)",
71        params![title, artist_id, date, total_discs, total_tracks, codec, label, remote_id, added_at],
72    )?;
73    Ok(conn.last_insert_rowid())
74}
75
76/// How a listing of albums is ordered.
77///
78/// In SQL rather than over the returned rows, because a listing that is read a
79/// page at a time has to be ordered before it is cut.
80#[derive(Debug, Clone, Copy, Default, PartialEq, Eq)]
81pub enum AlbumOrder {
82    /// Artist, then release date, then title — how a shelf reads.
83    #[default]
84    ArtistThenDate,
85    /// Release date, then title. A discography, in the order it happened.
86    Date,
87    /// Newest acquisition first. What a browser should open on: the record you
88    /// just added is the one you were looking for.
89    RecentlyAdded,
90    Title,
91    /// Newest release first.
92    YearDesc,
93    /// Seeded, so every page of one shuffle belongs to the same shuffle. A new
94    /// seed is a new order — that is what the reshuffle button asks for.
95    Random(i64),
96}
97
98impl AlbumOrder {
99    fn clause(self) -> &'static str {
100        match self {
101            Self::ArtistThenDate => "a.name COLLATE LIBRARY, al.date, al.title COLLATE LIBRARY",
102            Self::Date => "al.date, al.title COLLATE LIBRARY",
103            // Albums predating the added_at column sort last rather than first,
104            // which is what a NULL would do.
105            Self::RecentlyAdded => {
106                "COALESCE(al.added_at, '') DESC, a.name COLLATE LIBRARY, al.title COLLATE LIBRARY"
107            }
108            Self::Title => "al.title COLLATE LIBRARY, a.name COLLATE LIBRARY, al.date",
109            Self::YearDesc => {
110                "COALESCE(CAST(substr(al.date, 1, 4) AS INTEGER), 0) DESC, \
111                               a.name COLLATE LIBRARY, al.title COLLATE LIBRARY"
112            }
113            Self::Random(_) => "koan_shuffle(al.id, ?)",
114        }
115    }
116}
117
118/// What to list. Everything optional, so one query answers the browser, the
119/// search field, an artist's discography and the favourites page.
120#[derive(Debug, Clone, Copy, Default)]
121pub struct AlbumQuery<'a> {
122    pub artist_id: Option<i64>,
123    /// Case-insensitive substring over the album title and the artist name.
124    pub search: Option<&'a str>,
125    pub order: AlbumOrder,
126    /// Favourited records only.
127    pub favourites_only: bool,
128    /// `None` for the whole listing. A client that scrolls should page.
129    pub limit: Option<u32>,
130    pub offset: u32,
131}
132
133/// Albums, narrowed, ordered and paged by the database.
134///
135/// The narrowing belongs here rather than in each client: every front end wants
136/// the same answer, and the ones that filtered a fully-loaded list in their own
137/// language paid for reading the whole table to throw most of it away. Matching
138/// is ASCII case-insensitive, like `find_artists` — SQLite's `NOCASE` does not
139/// fold accented letters, so `MOTLEY` finds `Motley` but `MÖTLEY` does not find
140/// `Mötley`.
141pub fn list_albums(conn: &Connection, q: &AlbumQuery) -> Result<Vec<AlbumRow>, DbError> {
142    let mut sql = String::from(
143        "SELECT al.id, al.title, al.artist_id, a.name, al.date,
144                al.total_discs, al.total_tracks, al.codec, al.label, al.remote_id,
145                al.added_at
146         FROM albums al
147         LEFT JOIN artists a ON al.artist_id = a.id",
148    );
149    if q.favourites_only {
150        sql.push_str(
151            " JOIN favourite_albums f
152                ON f.artist_name = a.name AND f.album_title = al.title",
153        );
154    }
155
156    let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
157    let mut wheres: Vec<&str> = Vec::new();
158    if let Some(id) = q.artist_id {
159        params.push(Box::new(id));
160        wheres.push("al.artist_id = ?");
161    }
162    if let Some(query) = q.search {
163        let pattern = format!("%{}%", super::artists::escape_like(query));
164        // Bound twice rather than once: positional parameters are cheaper to
165        // keep straight than named ones across an assembled query.
166        params.push(Box::new(pattern.clone()));
167        params.push(Box::new(pattern));
168        wheres.push(
169            "(al.title LIKE ? COLLATE NOCASE ESCAPE '\\'
170              OR a.name LIKE ? COLLATE NOCASE ESCAPE '\\')",
171        );
172    }
173    if !wheres.is_empty() {
174        sql.push_str(" WHERE ");
175        sql.push_str(&wheres.join(" AND "));
176    }
177
178    sql.push_str(" ORDER BY ");
179    if let AlbumOrder::Random(seed) = q.order {
180        params.push(Box::new(seed));
181    }
182    sql.push_str(q.order.clause());
183
184    if let Some(limit) = q.limit {
185        params.push(Box::new(limit as i64));
186        params.push(Box::new(q.offset as i64));
187        sql.push_str(" LIMIT ? OFFSET ?");
188    }
189
190    let mut stmt = conn.prepare(&sql)?;
191    let rows = stmt
192        .query_map(rusqlite::params_from_iter(params.iter()), album_row)?
193        .collect::<Result<Vec<_>, _>>()?;
194    Ok(rows)
195}
196
197/// Get albums for a specific artist, ordered chronologically.
198pub fn albums_for_artist(conn: &Connection, artist_id: i64) -> Result<Vec<AlbumRow>, DbError> {
199    list_albums(
200        conn,
201        &AlbumQuery {
202            artist_id: Some(artist_id),
203            order: AlbumOrder::Date,
204            ..Default::default()
205        },
206    )
207}
208
209/// Get a single album by ID.
210pub fn get_album(conn: &Connection, album_id: i64) -> Result<Option<AlbumRow>, DbError> {
211    let result = conn
212        .query_row(
213            "SELECT al.id, al.title, al.artist_id, a.name, al.date,
214                    al.total_discs, al.total_tracks, al.codec, al.label, al.remote_id,
215                al.added_at
216             FROM albums al
217             LEFT JOIN artists a ON al.artist_id = a.id
218             WHERE al.id = ?1",
219            params![album_id],
220            album_row,
221        )
222        .ok();
223    Ok(result)
224}
225
226/// Get the date string for an album by ID.
227pub fn album_date(conn: &Connection, album_id: i64) -> Result<Option<String>, DbError> {
228    Ok(conn
229        .query_row(
230            "SELECT date FROM albums WHERE id = ?1",
231            params![album_id],
232            |row| row.get(0),
233        )
234        .ok()
235        .flatten())
236}
237
238/// Albums whose title or artist matches, case-insensitive substring.
239pub fn find_albums(conn: &Connection, query: &str) -> Result<Vec<AlbumRow>, DbError> {
240    list_albums(
241        conn,
242        &AlbumQuery {
243            search: Some(query),
244            ..Default::default()
245        },
246    )
247}
248
249/// Get all albums with their artist name, sorted.
250pub fn all_albums(conn: &Connection) -> Result<Vec<AlbumRow>, DbError> {
251    list_albums(conn, &AlbumQuery::default())
252}
253
254/// Record what the server knows about an album beyond what a track carries.
255///
256/// `get_or_create_album` is reached through a track and only ever sees what a
257/// file's tags say. Track totals, the record label and the MusicBrainz id are
258/// properties of the release, and the server hands all three over in the same
259/// response the sync already paged through.
260///
261/// Fills blanks rather than overwriting, so a locally-scanned album keeps what
262/// its tags said.
263pub fn enrich_remote_album(
264    conn: &Connection,
265    remote_id: &str,
266    mbid: Option<&str>,
267    sort_name: Option<&str>,
268    total_tracks: Option<i32>,
269    label: Option<&str>,
270) -> Result<(), DbError> {
271    conn.execute(
272        "UPDATE albums SET
273             mbid         = COALESCE(mbid, ?2),
274             sort_name    = COALESCE(sort_name, ?3),
275             total_tracks = COALESCE(total_tracks, ?4),
276             label        = COALESCE(label, ?5)
277         WHERE remote_id = ?1",
278        params![remote_id, mbid, sort_name, total_tracks, label],
279    )?;
280    Ok(())
281}
282
283#[cfg(test)]
284mod tests {
285    use super::*;
286    use crate::db::connection::Database;
287    use crate::db::queries::get_or_create_artist;
288
289    fn test_db() -> Database {
290        let conn = rusqlite::Connection::open_in_memory().unwrap();
291        conn.pragma_update(None, "foreign_keys", "on").unwrap();
292        crate::db::schema::create_tables(&conn).unwrap();
293        Database { conn }
294    }
295
296    #[test]
297    fn test_album_create_and_dedup() {
298        let db = test_db();
299        let artist = get_or_create_artist(&db.conn, "Boards of Canada", None).unwrap();
300        let a1 = get_or_create_album(
301            &db.conn,
302            "Music Has the Right to Children",
303            artist,
304            Some("1998"),
305            None,
306            None,
307            Some("FLAC"),
308            Some("Warp"),
309            None,
310            None,
311        )
312        .unwrap();
313        let a2 = get_or_create_album(
314            &db.conn,
315            "Music Has the Right to Children",
316            artist,
317            Some("1998"),
318            None,
319            None,
320            Some("FLAC"),
321            Some("Warp"),
322            None,
323            None,
324        )
325        .unwrap();
326        assert_eq!(a1, a2);
327    }
328
329    #[test]
330    fn test_album_codec_updated_on_format_upgrade() {
331        let db = test_db();
332        let artist = get_or_create_artist(&db.conn, "WAGDUG FUTURISTIC UNITY", None).unwrap();
333
334        // First scan: album indexed as MP3.
335        let id1 = get_or_create_album(
336            &db.conn,
337            "HAKAI",
338            artist,
339            Some("2008"),
340            None,
341            None,
342            Some("MP3"),
343            None,
344            None,
345            None,
346        )
347        .unwrap();
348
349        let codec: Option<String> = db
350            .conn
351            .query_row(
352                "SELECT codec FROM albums WHERE id = ?1",
353                params![id1],
354                |r| r.get(0),
355            )
356            .unwrap();
357        assert_eq!(codec.as_deref(), Some("MP3"));
358
359        // Re-scan after upgrading MP3→FLAC: same album, new codec.
360        let id2 = get_or_create_album(
361            &db.conn,
362            "HAKAI",
363            artist,
364            Some("2008"),
365            None,
366            None,
367            Some("FLAC"),
368            None,
369            None,
370            None,
371        )
372        .unwrap();
373
374        assert_eq!(id1, id2, "should return the same album ID");
375
376        let codec: Option<String> = db
377            .conn
378            .query_row(
379                "SELECT codec FROM albums WHERE id = ?1",
380                params![id1],
381                |r| r.get(0),
382            )
383            .unwrap();
384        assert_eq!(
385            codec.as_deref(),
386            Some("FLAC"),
387            "album codec should be updated after format upgrade"
388        );
389    }
390
391    #[test]
392    fn test_album_codec_not_nulled_by_missing_codec() {
393        let db = test_db();
394        let artist = get_or_create_artist(&db.conn, "Boards of Canada", None).unwrap();
395
396        // First scan with codec.
397        let id = get_or_create_album(
398            &db.conn,
399            "MHTRTC",
400            artist,
401            Some("1998"),
402            None,
403            None,
404            Some("FLAC"),
405            Some("Warp"),
406            None,
407            None,
408        )
409        .unwrap();
410
411        // Re-encounter with no codec (e.g. remote sync without codec info).
412        get_or_create_album(
413            &db.conn,
414            "MHTRTC",
415            artist,
416            Some("1998"),
417            None,
418            None,
419            None, // no codec
420            None, // no label
421            None,
422            None,
423        )
424        .unwrap();
425
426        let (codec, label): (Option<String>, Option<String>) = db
427            .conn
428            .query_row(
429                "SELECT codec, label FROM albums WHERE id = ?1",
430                params![id],
431                |r| Ok((r.get(0)?, r.get(1)?)),
432            )
433            .unwrap();
434        assert_eq!(
435            codec.as_deref(),
436            Some("FLAC"),
437            "codec should not be nulled by a None value"
438        );
439        assert_eq!(
440            label.as_deref(),
441            Some("Warp"),
442            "label should not be nulled by a None value"
443        );
444    }
445
446    /// Six albums across two artists, so a page is smaller than the listing.
447    fn stocked_db() -> Database {
448        use crate::db::queries::{sample_meta, upsert_track};
449        let db = test_db();
450        for (i, (artist, album)) in [
451            ("Autechre", "Amber"),
452            ("Autechre", "Tri Repetae"),
453            ("Autechre", "Confield"),
454            ("Boards of Canada", "Geogaddi"),
455            ("Boards of Canada", "Twoism"),
456            ("Coil", "Horse Rotorvator"),
457        ]
458        .iter()
459        .enumerate()
460        {
461            let mut m = sample_meta("t", artist, album);
462            m.path = Some(format!("/music/{album}/t.flac"));
463            m.date = Some(format!("199{i}"));
464            upsert_track(&db.conn, &m).unwrap();
465        }
466        db
467    }
468
469    #[test]
470    fn paging_walks_the_listing_without_repeating() {
471        let db = stocked_db();
472        let page = |offset| {
473            list_albums(
474                &db.conn,
475                &AlbumQuery {
476                    limit: Some(2),
477                    offset,
478                    ..Default::default()
479                },
480            )
481            .unwrap()
482            .into_iter()
483            .map(|a| a.title)
484            .collect::<Vec<_>>()
485        };
486        let whole = all_albums(&db.conn)
487            .unwrap()
488            .into_iter()
489            .map(|a| a.title)
490            .collect::<Vec<_>>();
491        assert_eq!([page(0), page(2), page(4)].concat(), whole);
492        assert!(
493            page(6).is_empty(),
494            "a page past the end is empty, not wrapped"
495        );
496    }
497
498    #[test]
499    fn search_narrows_on_title_or_artist() {
500        let db = stocked_db();
501        let titles = |q| {
502            find_albums(&db.conn, q)
503                .unwrap()
504                .into_iter()
505                .map(|a| a.title)
506                .collect::<Vec<_>>()
507        };
508        assert_eq!(titles("geogaddi"), ["Geogaddi"]);
509        assert_eq!(titles("autechre").len(), 3, "matched on the artist name");
510    }
511
512    /// The reason the seed exists: page two has to belong to the same shuffle
513    /// as page one, or scrolling repeats and drops records.
514    #[test]
515    fn a_seeded_shuffle_pages_consistently() {
516        let db = stocked_db();
517        let shuffled = |seed, limit, offset| {
518            list_albums(
519                &db.conn,
520                &AlbumQuery {
521                    order: AlbumOrder::Random(seed),
522                    limit,
523                    offset,
524                    ..Default::default()
525                },
526            )
527            .unwrap()
528            .into_iter()
529            .map(|a| a.id)
530            .collect::<Vec<_>>()
531        };
532
533        let whole = shuffled(42, None, 0);
534        assert_eq!(
535            [shuffled(42, Some(4), 0), shuffled(42, Some(4), 4)].concat(),
536            whole
537        );
538        assert_ne!(shuffled(43, None, 0), whole, "a new seed is a new order");
539        assert_eq!(whole.len(), 6, "a shuffle drops nothing");
540    }
541
542    #[test]
543    fn favourites_only_lists_what_was_hearted() {
544        use crate::db::queries::toggle_favourite_album;
545        let db = stocked_db();
546        toggle_favourite_album(&db.conn, "Coil", "Horse Rotorvator").unwrap();
547        let rows = list_albums(
548            &db.conn,
549            &AlbumQuery {
550                favourites_only: true,
551                ..Default::default()
552            },
553        )
554        .unwrap();
555        assert_eq!(
556            rows.iter().map(|a| a.title.as_str()).collect::<Vec<_>>(),
557            ["Horse Rotorvator"]
558        );
559    }
560
561    #[test]
562    fn test_all_albums_and_tracks() {
563        use crate::db::queries::{sample_meta, tracks_for_album, upsert_track};
564
565        let db = test_db();
566        let mut m1 = sample_meta("Track1", "Artist1", "Album1");
567        m1.track_number = Some(1);
568        let mut m2 = sample_meta("Track2", "Artist1", "Album1");
569        m2.track_number = Some(2);
570        m2.path = Some("/music/Album1/Track2.flac".into());
571        upsert_track(&db.conn, &m1).unwrap();
572        upsert_track(&db.conn, &m2).unwrap();
573
574        let albums = all_albums(&db.conn).unwrap();
575        assert_eq!(albums.len(), 1);
576        assert_eq!(albums[0].title, "Album1");
577
578        let tracks = tracks_for_album(&db.conn, albums[0].id).unwrap();
579        assert_eq!(tracks.len(), 2);
580        assert_eq!(tracks[0].track_number, Some(1));
581        assert_eq!(tracks[1].track_number, Some(2));
582    }
583}
koan_core/db/queries/albums.rs

koan_core/db/queries/
albums.rs
