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        if let Some(rid) = remote_id {
66            super::adopt_uid(conn, super::UidKind::Album, id, rid)?;
67        }
68        return Ok(id);
69    }
70
71    conn.execute(
72        "INSERT INTO albums (title, artist_id, date, total_discs, total_tracks, codec, label, remote_id, added_at)
73         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)",
74        params![title, artist_id, date, total_discs, total_tracks, codec, label, remote_id, added_at],
75    )?;
76    let id = conn.last_insert_rowid();
77    if let Some(rid) = remote_id {
78        super::adopt_uid(conn, super::UidKind::Album, id, rid)?;
79    }
80    Ok(id)
81}
82
83/// How a listing of albums is ordered.
84///
85/// In SQL rather than over the returned rows, because a listing that is read a
86/// page at a time has to be ordered before it is cut.
87#[derive(Debug, Clone, Copy, Default, PartialEq, Eq)]
88pub enum AlbumOrder {
89    /// Artist, then release date, then title — how a shelf reads.
90    #[default]
91    ArtistThenDate,
92    /// Release date, then title. A discography, in the order it happened.
93    Date,
94    /// Newest acquisition first. What a browser should open on: the record you
95    /// just added is the one you were looking for.
96    RecentlyAdded,
97    Title,
98    /// Newest release first.
99    YearDesc,
100    /// Seeded, so every page of one shuffle belongs to the same shuffle. A new
101    /// seed is a new order — that is what the reshuffle button asks for.
102    Random(i64),
103    /// Insertion order. The one order a new album cannot land in the middle
104    /// of, which is what makes an offset walk over the whole list exact.
105    Id,
106}
107
108impl AlbumOrder {
109    fn clause(self) -> &'static str {
110        match self {
111            Self::ArtistThenDate => "a.name COLLATE LIBRARY, al.date, al.title COLLATE LIBRARY",
112            Self::Date => "al.date, al.title COLLATE LIBRARY",
113            // Albums predating the added_at column sort last rather than first,
114            // which is what a NULL would do.
115            Self::RecentlyAdded => {
116                "COALESCE(al.added_at, '') DESC, a.name COLLATE LIBRARY, al.title COLLATE LIBRARY"
117            }
118            Self::Title => "al.title COLLATE LIBRARY, a.name COLLATE LIBRARY, al.date",
119            Self::YearDesc => {
120                "COALESCE(CAST(substr(al.date, 1, 4) AS INTEGER), 0) DESC, \
121                               a.name COLLATE LIBRARY, al.title COLLATE LIBRARY"
122            }
123            Self::Random(_) => "koan_shuffle(al.id, ?)",
124            Self::Id => "al.id",
125        }
126    }
127}
128
129/// Codecs that lose nothing, as the indexer names them.
130pub const LOSSLESS_CODECS: [&str; 5] = ["FLAC", "ALAC", "WAV", "AIFF", "PCM"];
131
132/// Narrowing by what the records are, shared by the album and artist listings:
133/// an artist passes when any of their albums does.
134#[derive(Debug, Clone, Copy, Default)]
135pub struct AlbumFilter<'a> {
136    /// Only records in a codec from `LOSSLESS_CODECS`.
137    pub lossless: bool,
138    /// Only records in this codec, matched as the indexer names it.
139    pub codec: Option<&'a str>,
140    /// Release year bounds, inclusive. Records without a date are left out
141    /// when either is set.
142    pub year_from: Option<i32>,
143    pub year_to: Option<i32>,
144    /// Records with at least one track tagged with this genre.
145    pub genre: Option<&'a str>,
146}
147
148impl AlbumFilter<'_> {
149    /// Conditions on the album aliased `al`.
150    pub(crate) fn push(
151        &self,
152        wheres: &mut Vec<String>,
153        params: &mut Vec<Box<dyn rusqlite::ToSql>>,
154    ) {
155        if self.lossless {
156            wheres.push(format!(
157                "al.codec IN ({})",
158                vec!["?"; LOSSLESS_CODECS.len()].join(",")
159            ));
160            params.extend(
161                LOSSLESS_CODECS
162                    .iter()
163                    .map(|c| Box::new(*c) as Box<dyn rusqlite::ToSql>),
164            );
165        }
166        if let Some(codec) = self.codec {
167            wheres.push("al.codec = ? COLLATE NOCASE".into());
168            params.push(Box::new(codec.to_owned()));
169        }
170        let year = "CAST(substr(al.date, 1, 4) AS INTEGER)";
171        if let Some(from) = self.year_from {
172            wheres.push(format!("{year} >= ?"));
173            params.push(Box::new(from));
174        }
175        if let Some(to) = self.year_to {
176            wheres.push(format!("al.date IS NOT NULL AND {year} <= ?"));
177            params.push(Box::new(to));
178        }
179        if let Some(genre) = self.genre {
180            wheres.push(
181                "EXISTS (SELECT 1 FROM tracks g WHERE g.album_id = al.id AND g.genre = ? COLLATE NOCASE)"
182                    .into(),
183            );
184            params.push(Box::new(genre.to_owned()));
185        }
186    }
187}
188
189/// What to list. Everything optional, so one query answers the browser, the
190/// search field, an artist's discography and the favourites page.
191#[derive(Debug, Clone, Copy, Default)]
192pub struct AlbumQuery<'a> {
193    pub artist_id: Option<i64>,
194    /// Case-insensitive substring over the album title and the artist name.
195    pub search: Option<&'a str>,
196    pub order: AlbumOrder,
197    /// Only records this user has favourited.
198    pub favourites_of: Option<i64>,
199    pub filter: AlbumFilter<'a>,
200    /// `None` for the whole listing. A client that scrolls should page.
201    pub limit: Option<u32>,
202    pub offset: u32,
203}
204
205/// Albums, narrowed, ordered and paged by the database.
206///
207/// The narrowing belongs here rather than in each client: every front end wants
208/// the same answer, and one filtering a fully-loaded list in its own language
209/// pays for reading the whole table to throw most of it away. Matching
210/// is ASCII case-insensitive, like `find_artists` — SQLite's `NOCASE` does not
211/// fold accented letters, so `MOTLEY` finds `Motley` but `MÖTLEY` does not find
212/// `Mötley`.
213pub fn list_albums(conn: &Connection, q: &AlbumQuery) -> Result<Vec<AlbumRow>, DbError> {
214    let mut sql = String::from(
215        "SELECT al.id, al.title, al.artist_id, a.name, al.date,
216                al.total_discs, al.total_tracks, al.codec, al.label, al.remote_id,
217                al.added_at
218         FROM albums al
219         LEFT JOIN artists a ON al.artist_id = a.id",
220    );
221    let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
222    if let Some(user) = q.favourites_of {
223        params.push(Box::new(super::auth::resolve_user(conn, user)?));
224        sql.push_str(
225            " JOIN favourite_albums f
226                ON f.artist_name = a.name AND f.album_title = al.title AND f.user_id = ?",
227        );
228    }
229    let mut wheres: Vec<String> = Vec::new();
230    if let Some(id) = q.artist_id {
231        params.push(Box::new(id));
232        wheres.push("al.artist_id = ?".into());
233    }
234    if let Some(query) = q.search {
235        let pattern = format!("%{}%", super::artists::escape_like(query));
236        // Bound twice rather than once: positional parameters are cheaper to
237        // keep straight than named ones across an assembled query.
238        params.push(Box::new(pattern.clone()));
239        params.push(Box::new(pattern));
240        wheres.push(
241            "(al.title LIKE ? COLLATE NOCASE ESCAPE '\\'
242              OR a.name LIKE ? COLLATE NOCASE ESCAPE '\\')"
243                .into(),
244        );
245    }
246    q.filter.push(&mut wheres, &mut params);
247    if !wheres.is_empty() {
248        sql.push_str(" WHERE ");
249        sql.push_str(&wheres.join(" AND "));
250    }
251
252    sql.push_str(" ORDER BY ");
253    if let AlbumOrder::Random(seed) = q.order {
254        params.push(Box::new(seed));
255    }
256    sql.push_str(q.order.clause());
257
258    if let Some(limit) = q.limit {
259        params.push(Box::new(limit as i64));
260        params.push(Box::new(q.offset as i64));
261        sql.push_str(" LIMIT ? OFFSET ?");
262    }
263
264    let mut stmt = conn.prepare(&sql)?;
265    let rows = stmt
266        .query_map(rusqlite::params_from_iter(params.iter()), album_row)?
267        .collect::<Result<Vec<_>, _>>()?;
268    Ok(rows)
269}
270
271/// How `played_albums` orders a user's listening.
272#[derive(Debug, Clone, Copy, PartialEq, Eq)]
273pub enum PlayedOrder {
274    /// Most recently played first.
275    Recent,
276    /// Most plays first, ties by most recent.
277    Frequent,
278}
279
280/// Albums `user` has played, from play history, paged by the database.
281/// Albums never played are not listed.
282pub fn played_albums(
283    conn: &Connection,
284    user: i64,
285    order: PlayedOrder,
286    limit: u32,
287    offset: u32,
288) -> Result<Vec<AlbumRow>, DbError> {
289    let order = match order {
290        PlayedOrder::Recent => "p.last DESC",
291        PlayedOrder::Frequent => "p.plays DESC, p.last DESC",
292    };
293    let sql = format!(
294        "SELECT al.id, al.title, al.artist_id, a.name, al.date,
295                al.total_discs, al.total_tracks, al.codec, al.label, al.remote_id,
296                al.added_at
297         FROM (SELECT t.album_id, MAX(h.played_at) AS last, COUNT(*) AS plays
298                 FROM play_history h JOIN tracks t ON t.id = h.track_id
299                WHERE h.user_id = ?1 AND t.album_id IS NOT NULL
300                GROUP BY t.album_id) p
301         JOIN albums al ON al.id = p.album_id
302         LEFT JOIN artists a ON al.artist_id = a.id
303         ORDER BY {order}, al.id
304         LIMIT ?2 OFFSET ?3"
305    );
306    let mut stmt = conn.prepare(&sql)?;
307    let rows = stmt
308        .query_map(
309            params![super::auth::resolve_user(conn, user)?, limit, offset],
310            album_row,
311        )?
312        .collect::<Result<Vec<_>, _>>()?;
313    Ok(rows)
314}
315
316/// Genres by how many records carry them, most first: what a genre filter
317/// offers. Blank tags are left out.
318pub fn genres(conn: &Connection, limit: u32) -> Result<Vec<String>, DbError> {
319    let mut stmt = conn.prepare(
320        "SELECT genre FROM tracks
321         WHERE genre IS NOT NULL AND TRIM(genre) != '' AND album_id IS NOT NULL
322         GROUP BY genre COLLATE NOCASE
323         ORDER BY COUNT(DISTINCT album_id) DESC, genre COLLATE NOCASE
324         LIMIT ?1",
325    )?;
326    let rows = stmt.query_map([limit], |r| r.get(0))?;
327    Ok(rows.collect::<Result<_, _>>()?)
328}
329
330/// The codecs records are in, most common first.
331pub fn album_codecs(conn: &Connection) -> Result<Vec<String>, DbError> {
332    let mut stmt = conn.prepare(
333        "SELECT codec FROM albums WHERE codec IS NOT NULL AND codec != ''
334         GROUP BY codec ORDER BY COUNT(*) DESC, codec",
335    )?;
336    let rows = stmt.query_map([], |r| r.get(0))?;
337    Ok(rows.collect::<Result<_, _>>()?)
338}
339
340/// Get albums for a specific artist, ordered chronologically.
341pub fn albums_for_artist(conn: &Connection, artist_id: i64) -> Result<Vec<AlbumRow>, DbError> {
342    list_albums(
343        conn,
344        &AlbumQuery {
345            artist_id: Some(artist_id),
346            order: AlbumOrder::Date,
347            ..Default::default()
348        },
349    )
350}
351
352/// Get a single album by ID.
353pub fn get_album(conn: &Connection, album_id: i64) -> Result<Option<AlbumRow>, DbError> {
354    let result = conn
355        .query_row(
356            "SELECT al.id, al.title, al.artist_id, a.name, al.date,
357                    al.total_discs, al.total_tracks, al.codec, al.label, al.remote_id,
358                al.added_at
359             FROM albums al
360             LEFT JOIN artists a ON al.artist_id = a.id
361             WHERE al.id = ?1",
362            params![album_id],
363            album_row,
364        )
365        .ok();
366    Ok(result)
367}
368
369/// Get the date string for an album by ID.
370pub fn album_date(conn: &Connection, album_id: i64) -> Result<Option<String>, DbError> {
371    Ok(conn
372        .query_row(
373            "SELECT date FROM albums WHERE id = ?1",
374            params![album_id],
375            |row| row.get(0),
376        )
377        .ok()
378        .flatten())
379}
380
381/// Albums whose title or artist matches, case-insensitive substring.
382pub fn find_albums(conn: &Connection, query: &str) -> Result<Vec<AlbumRow>, DbError> {
383    list_albums(
384        conn,
385        &AlbumQuery {
386            search: Some(query),
387            ..Default::default()
388        },
389    )
390}
391
392/// Get all albums with their artist name, sorted.
393pub fn all_albums(conn: &Connection) -> Result<Vec<AlbumRow>, DbError> {
394    list_albums(conn, &AlbumQuery::default())
395}
396
397/// Record what the server knows about an album beyond what a track carries.
398///
399/// `get_or_create_album` is reached through a track and only ever sees what a
400/// file's tags say. Track totals, the record label and the MusicBrainz id are
401/// properties of the release, and the server hands all three over in the same
402/// response the sync already paged through.
403///
404/// Fills blanks rather than overwriting, so a locally-scanned album keeps what
405/// its tags said.
406pub fn enrich_remote_album(
407    conn: &Connection,
408    remote_id: &str,
409    mbid: Option<&str>,
410    sort_name: Option<&str>,
411    total_tracks: Option<i32>,
412    label: Option<&str>,
413) -> Result<(), DbError> {
414    conn.execute(
415        "UPDATE albums SET
416             mbid         = COALESCE(mbid, ?2),
417             sort_name    = COALESCE(sort_name, ?3),
418             total_tracks = COALESCE(total_tracks, ?4),
419             label        = COALESCE(label, ?5)
420         WHERE remote_id = ?1",
421        params![remote_id, mbid, sort_name, total_tracks, label],
422    )?;
423    Ok(())
424}
425
426#[cfg(test)]
427mod tests {
428    use super::*;
429    use crate::db::connection::Database;
430    use crate::db::queries::get_or_create_artist;
431
432    fn test_db() -> Database {
433        let conn = rusqlite::Connection::open_in_memory().unwrap();
434        conn.pragma_update(None, "foreign_keys", "on").unwrap();
435        crate::db::schema::create_tables(&conn).unwrap();
436        Database { conn }
437    }
438
439    /// Three records by two artists: a FLAC techno one from 1995, an MP3 rock
440    /// one from 2005 and an ALAC techno one from 2010.
441    fn filter_library() -> Database {
442        let db = test_db();
443        for (title, artist, album, codec, date, genre) in [
444            ("A", "Rrose", "Early", "FLAC", "1995", "Techno"),
445            ("B", "Band", "Middle", "MP3", "2005", "Rock"),
446            ("C", "Rrose", "Late", "ALAC", "2010-03-01", "techno"),
447        ] {
448            let mut meta = crate::db::queries::sample_meta(title, artist, album);
449            meta.codec = Some(codec.into());
450            meta.date = Some(date.into());
451            meta.genre = Some(genre.into());
452            crate::db::queries::upsert_track(&db.conn, &meta).unwrap();
453        }
454        db
455    }
456
457    fn titles(db: &Database, filter: AlbumFilter) -> Vec<String> {
458        list_albums(
459            &db.conn,
460            &AlbumQuery {
461                filter,
462                order: AlbumOrder::Date,
463                ..Default::default()
464            },
465        )
466        .unwrap()
467        .into_iter()
468        .map(|a| a.title)
469        .collect()
470    }
471
472    #[test]
473    fn albums_filter_by_codec_year_and_genre_in_sql() {
474        let db = filter_library();
475        let lossless = AlbumFilter {
476            lossless: true,
477            ..Default::default()
478        };
479        assert_eq!(titles(&db, lossless), ["Early", "Late"]);
480        let mp3 = AlbumFilter {
481            codec: Some("mp3"),
482            ..Default::default()
483        };
484        assert_eq!(titles(&db, mp3), ["Middle"]);
485        let years = AlbumFilter {
486            year_from: Some(2000),
487            year_to: Some(2010),
488            ..Default::default()
489        };
490        assert_eq!(titles(&db, years), ["Middle", "Late"]);
491        let techno = AlbumFilter {
492            genre: Some("TECHNO"),
493            ..Default::default()
494        };
495        assert_eq!(titles(&db, techno), ["Early", "Late"]);
496        let all = AlbumFilter {
497            lossless: true,
498            year_from: Some(2000),
499            genre: Some("techno"),
500            ..Default::default()
501        };
502        assert_eq!(titles(&db, all), ["Late"]);
503        assert_eq!(
504            genres(&db.conn, 10).unwrap().len(),
505            2,
506            "techno counted once"
507        );
508        assert_eq!(album_codecs(&db.conn).unwrap().len(), 3);
509    }
510
511    #[test]
512    fn played_albums_follow_one_users_history() {
513        use crate::db::queries::{LOCAL_USER, SOURCE_LOCAL, record_play_at};
514        let db = filter_library();
515        let track = |title: &str| -> i64 {
516            db.conn
517                .query_row("SELECT id FROM tracks WHERE title = ?1", [title], |r| {
518                    r.get(0)
519                })
520                .unwrap()
521        };
522        for (title, at) in [("A", 10), ("C", 30), ("A", 20)] {
523            record_play_at(&db.conn, LOCAL_USER, track(title), at, None, SOURCE_LOCAL).unwrap();
524        }
525        let played = |order, limit, offset| {
526            played_albums(&db.conn, LOCAL_USER, order, limit, offset)
527                .unwrap()
528                .into_iter()
529                .map(|a| a.title)
530                .collect::<Vec<_>>()
531        };
532        assert_eq!(played(PlayedOrder::Recent, 10, 0), ["Late", "Early"]);
533        assert_eq!(played(PlayedOrder::Frequent, 10, 0), ["Early", "Late"]);
534        assert_eq!(played(PlayedOrder::Frequent, 1, 1), ["Late"]);
535    }
536
537    #[test]
538    fn random_draws_narrow_in_sql() {
539        use crate::db::queries::{RandomFilter, random_tracks_where};
540        let db = filter_library();
541        let draw = |filter: RandomFilter, count| {
542            let mut titles: Vec<String> = random_tracks_where(&db.conn, count, &filter)
543                .unwrap()
544                .into_iter()
545                .map(|t| t.title)
546                .collect();
547            titles.sort();
548            titles
549        };
550        assert_eq!(draw(RandomFilter::default(), 10), ["A", "B", "C"]);
551        assert_eq!(draw(RandomFilter::default(), 2).len(), 2);
552        let techno = RandomFilter {
553            genre: Some("TECHNO"),
554            ..Default::default()
555        };
556        assert_eq!(draw(techno, 10), ["A", "C"]);
557        let nineties = RandomFilter {
558            year_from: Some(1990),
559            year_to: Some(1999),
560            ..Default::default()
561        };
562        assert_eq!(draw(nineties, 10), ["A"]);
563    }
564
565    #[test]
566    fn artists_sort_and_count_what_the_filter_leaves() {
567        use crate::db::queries::{ArtistOrder, ArtistQuery, list_artists};
568        let db = filter_library();
569        let names = |q: ArtistQuery| {
570            list_artists(&db.conn, &q)
571                .unwrap()
572                .into_iter()
573                .map(|a| (a.name, a.album_count))
574                .collect::<Vec<_>>()
575        };
576        assert_eq!(
577            names(ArtistQuery {
578                order: ArtistOrder::AlbumCount,
579                ..Default::default()
580            }),
581            [("Rrose".to_string(), 2), ("Band".to_string(), 1)]
582        );
583        assert_eq!(
584            names(ArtistQuery {
585                filter: AlbumFilter {
586                    year_from: Some(2000),
587                    ..Default::default()
588                },
589                ..Default::default()
590            }),
591            [("Band".to_string(), 1), ("Rrose".to_string(), 1)]
592        );
593    }
594
595    #[test]
596    fn test_album_create_and_dedup() {
597        let db = test_db();
598        let artist = get_or_create_artist(&db.conn, "Boards of Canada", None).unwrap();
599        let a1 = get_or_create_album(
600            &db.conn,
601            "Music Has the Right to Children",
602            artist,
603            Some("1998"),
604            None,
605            None,
606            Some("FLAC"),
607            Some("Warp"),
608            None,
609            None,
610        )
611        .unwrap();
612        let a2 = get_or_create_album(
613            &db.conn,
614            "Music Has the Right to Children",
615            artist,
616            Some("1998"),
617            None,
618            None,
619            Some("FLAC"),
620            Some("Warp"),
621            None,
622            None,
623        )
624        .unwrap();
625        assert_eq!(a1, a2);
626    }
627
628    #[test]
629    fn test_album_codec_updated_on_format_upgrade() {
630        let db = test_db();
631        let artist = get_or_create_artist(&db.conn, "WAGDUG FUTURISTIC UNITY", None).unwrap();
632
633        // First scan: album indexed as MP3.
634        let id1 = get_or_create_album(
635            &db.conn,
636            "HAKAI",
637            artist,
638            Some("2008"),
639            None,
640            None,
641            Some("MP3"),
642            None,
643            None,
644            None,
645        )
646        .unwrap();
647
648        let codec: Option<String> = db
649            .conn
650            .query_row(
651                "SELECT codec FROM albums WHERE id = ?1",
652                params![id1],
653                |r| r.get(0),
654            )
655            .unwrap();
656        assert_eq!(codec.as_deref(), Some("MP3"));
657
658        // Re-scan after upgrading MP3→FLAC: same album, new codec.
659        let id2 = get_or_create_album(
660            &db.conn,
661            "HAKAI",
662            artist,
663            Some("2008"),
664            None,
665            None,
666            Some("FLAC"),
667            None,
668            None,
669            None,
670        )
671        .unwrap();
672
673        assert_eq!(id1, id2, "should return the same album ID");
674
675        let codec: Option<String> = db
676            .conn
677            .query_row(
678                "SELECT codec FROM albums WHERE id = ?1",
679                params![id1],
680                |r| r.get(0),
681            )
682            .unwrap();
683        assert_eq!(
684            codec.as_deref(),
685            Some("FLAC"),
686            "album codec should be updated after format upgrade"
687        );
688    }
689
690    #[test]
691    fn test_album_codec_not_nulled_by_missing_codec() {
692        let db = test_db();
693        let artist = get_or_create_artist(&db.conn, "Boards of Canada", None).unwrap();
694
695        // First scan with codec.
696        let id = get_or_create_album(
697            &db.conn,
698            "MHTRTC",
699            artist,
700            Some("1998"),
701            None,
702            None,
703            Some("FLAC"),
704            Some("Warp"),
705            None,
706            None,
707        )
708        .unwrap();
709
710        // Re-encounter with no codec (e.g. remote sync without codec info).
711        get_or_create_album(
712            &db.conn,
713            "MHTRTC",
714            artist,
715            Some("1998"),
716            None,
717            None,
718            None, // no codec
719            None, // no label
720            None,
721            None,
722        )
723        .unwrap();
724
725        let (codec, label): (Option<String>, Option<String>) = db
726            .conn
727            .query_row(
728                "SELECT codec, label FROM albums WHERE id = ?1",
729                params![id],
730                |r| Ok((r.get(0)?, r.get(1)?)),
731            )
732            .unwrap();
733        assert_eq!(
734            codec.as_deref(),
735            Some("FLAC"),
736            "codec should not be nulled by a None value"
737        );
738        assert_eq!(
739            label.as_deref(),
740            Some("Warp"),
741            "label should not be nulled by a None value"
742        );
743    }
744
745    /// Six albums across two artists, so a page is smaller than the listing.
746    fn stocked_db() -> Database {
747        use crate::db::queries::{sample_meta, upsert_track};
748        let db = test_db();
749        for (i, (artist, album)) in [
750            ("Autechre", "Amber"),
751            ("Autechre", "Tri Repetae"),
752            ("Autechre", "Confield"),
753            ("Boards of Canada", "Geogaddi"),
754            ("Boards of Canada", "Twoism"),
755            ("Coil", "Horse Rotorvator"),
756        ]
757        .iter()
758        .enumerate()
759        {
760            let mut m = sample_meta("t", artist, album);
761            m.path = Some(format!("/music/{album}/t.flac"));
762            m.date = Some(format!("199{i}"));
763            upsert_track(&db.conn, &m).unwrap();
764        }
765        db
766    }
767
768    #[test]
769    fn paging_walks_the_listing_without_repeating() {
770        let db = stocked_db();
771        let page = |offset| {
772            list_albums(
773                &db.conn,
774                &AlbumQuery {
775                    limit: Some(2),
776                    offset,
777                    ..Default::default()
778                },
779            )
780            .unwrap()
781            .into_iter()
782            .map(|a| a.title)
783            .collect::<Vec<_>>()
784        };
785        let whole = all_albums(&db.conn)
786            .unwrap()
787            .into_iter()
788            .map(|a| a.title)
789            .collect::<Vec<_>>();
790        assert_eq!([page(0), page(2), page(4)].concat(), whole);
791        assert!(
792            page(6).is_empty(),
793            "a page past the end is empty, not wrapped"
794        );
795    }
796
797    #[test]
798    fn search_narrows_on_title_or_artist() {
799        let db = stocked_db();
800        let titles = |q| {
801            find_albums(&db.conn, q)
802                .unwrap()
803                .into_iter()
804                .map(|a| a.title)
805                .collect::<Vec<_>>()
806        };
807        assert_eq!(titles("geogaddi"), ["Geogaddi"]);
808        assert_eq!(titles("autechre").len(), 3, "matched on the artist name");
809    }
810
811    /// The reason the seed exists: page two has to belong to the same shuffle
812    /// as page one, or scrolling repeats and drops records.
813    #[test]
814    fn a_seeded_shuffle_pages_consistently() {
815        let db = stocked_db();
816        let shuffled = |seed, limit, offset| {
817            list_albums(
818                &db.conn,
819                &AlbumQuery {
820                    order: AlbumOrder::Random(seed),
821                    limit,
822                    offset,
823                    ..Default::default()
824                },
825            )
826            .unwrap()
827            .into_iter()
828            .map(|a| a.id)
829            .collect::<Vec<_>>()
830        };
831
832        let whole = shuffled(42, None, 0);
833        assert_eq!(
834            [shuffled(42, Some(4), 0), shuffled(42, Some(4), 4)].concat(),
835            whole
836        );
837        assert_ne!(shuffled(43, None, 0), whole, "a new seed is a new order");
838        assert_eq!(whole.len(), 6, "a shuffle drops nothing");
839    }
840
841    #[test]
842    fn favourites_only_lists_what_was_hearted() {
843        use crate::db::queries::toggle_favourite_album;
844        let db = stocked_db();
845        toggle_favourite_album(
846            &db.conn,
847            crate::db::queries::LOCAL_USER,
848            "Coil",
849            "Horse Rotorvator",
850        )
851        .unwrap();
852        let rows = list_albums(
853            &db.conn,
854            &AlbumQuery {
855                favourites_of: Some(crate::db::queries::LOCAL_USER),
856                ..Default::default()
857            },
858        )
859        .unwrap();
860        assert_eq!(
861            rows.iter().map(|a| a.title.as_str()).collect::<Vec<_>>(),
862            ["Horse Rotorvator"]
863        );
864    }
865
866    #[test]
867    fn test_all_albums_and_tracks() {
868        use crate::db::queries::{sample_meta, tracks_for_album, upsert_track};
869
870        let db = test_db();
871        let mut m1 = sample_meta("Track1", "Artist1", "Album1");
872        m1.track_number = Some(1);
873        let mut m2 = sample_meta("Track2", "Artist1", "Album1");
874        m2.track_number = Some(2);
875        m2.path = Some("/music/Album1/Track2.flac".into());
876        upsert_track(&db.conn, &m1).unwrap();
877        upsert_track(&db.conn, &m2).unwrap();
878
879        let albums = all_albums(&db.conn).unwrap();
880        assert_eq!(albums.len(), 1);
881        assert_eq!(albums[0].title, "Album1");
882
883        let tracks = tracks_for_album(&db.conn, albums[0].id).unwrap();
884        assert_eq!(tracks.len(), 2);
885        assert_eq!(tracks[0].track_number, Some(1));
886        assert_eq!(tracks[1].track_number, Some(2));
887    }
888}
koan_core/db/queries/albums.rs

koan_core/db/queries/
albums.rs
