koan_core/db/queries/
artists.rs

1use rusqlite::{Connection, OptionalExtension, params};
2
3use crate::db::connection::DbError;
4
5use super::ArtistRow;
6
7/// Escape SQL LIKE wildcard characters in user input.
8pub(super) fn escape_like(s: &str) -> String {
9    s.replace('\\', "\\\\")
10        .replace('%', "\\%")
11        .replace('_', "\\_")
12}
13
14/// Get or create an artist by name. Returns the artist ID.
15///
16/// Names that differ only in letter case are one artist: tags spell the same
17/// act "The Squire Of Gothos" on one record and "of" on the next, and two rows
18/// split its albums from its tracks. An exact match wins over a case-folded one.
19pub fn get_or_create_artist(
20    conn: &Connection,
21    name: &str,
22    remote_id: Option<&str>,
23) -> Result<i64, DbError> {
24    let key = super::sources::fold(name);
25    let existing: Option<(i64, Option<String>)> = conn
26        .prepare_cached("SELECT id, remote_id FROM artists WHERE name_key = ?1")?
27        .query_row(params![key], |row| Ok((row.get(0)?, row.get(1)?)))
28        .optional()?;
29
30    if let Some((id, stored)) = existing {
31        // The server's current id wins: one that renumbers its library would
32        // otherwise leave the artist under an id it no longer answers to.
33        if let Some(rid) = remote_id {
34            if stored.as_deref() != Some(rid) {
35                conn.prepare_cached("UPDATE artists SET remote_id = ?1 WHERE id = ?2")?
36                    .execute(params![rid, id])?;
37            }
38            super::adopt_uid(conn, super::UidKind::Artist, id, rid)?;
39        }
40        return Ok(id);
41    }
42
43    let uid = super::free_uid(conn, super::UidKind::Artist, remote_id)?;
44    conn.prepare_cached(
45        "INSERT INTO artists (name, name_key, remote_id, uid) VALUES (?1, ?2, ?3, ?4)",
46    )?
47    .execute(params![name, key, remote_id, uid])?;
48    let id = conn.last_insert_rowid();
49    if let Some(rid) = remote_id {
50        super::adopt_uid(conn, super::UidKind::Artist, id, rid)?;
51    }
52    Ok(id)
53}
54
55/// How to order the artist listing.
56#[derive(Debug, Clone, Copy, Default, PartialEq, Eq)]
57pub enum ArtistOrder {
58    /// By name, as it reads. Sort names from tags are too erratic to order by.
59    #[default]
60    Name,
61    /// Most albums first.
62    AlbumCount,
63    /// The artist whose newest album arrived most recently first.
64    RecentlyAdded,
65    /// Insertion order, for an offset walk that must not skip or repeat.
66    Id,
67}
68
69impl ArtistOrder {
70    fn clause(self) -> &'static str {
71        match self {
72            Self::Name => "a.name COLLATE LIBRARY",
73            Self::AlbumCount => "COUNT(DISTINCT al.id) DESC, a.name COLLATE LIBRARY",
74            Self::RecentlyAdded => "COALESCE(MAX(al.added_at), '') DESC, a.name COLLATE LIBRARY",
75            Self::Id => "a.id",
76        }
77    }
78}
79
80/// What to list. Artists are always album artists — a track-only credit (a
81/// featured guest) appears inline in the queue, not as a shelf of its own.
82#[derive(Debug, Clone, Copy, Default)]
83pub struct ArtistQuery<'a> {
84    /// Only these artists.
85    pub ids: Option<&'a [i64]>,
86    /// Case-insensitive substring over the name.
87    pub search: Option<&'a str>,
88    /// Only artists this user has favourited.
89    pub favourites_of: Option<i64>,
90    /// Albums that count; an artist with none left is not listed, and the
91    /// counts are of what is left.
92    pub filter: super::albums::AlbumFilter<'a>,
93    pub order: ArtistOrder,
94    /// Leave `track_count` at zero rather than read every track in the library
95    /// to count them.
96    pub without_track_counts: bool,
97    /// `None` for the whole listing. A client that scrolls should page.
98    pub limit: Option<u32>,
99    pub offset: u32,
100}
101
102/// Artists with their album and track counts, narrowed, ordered and paged by
103/// the database.
104pub fn list_artists(conn: &Connection, q: &ArtistQuery) -> Result<Vec<ArtistRow>, DbError> {
105    let mut sql = String::from(if q.without_track_counts {
106        "SELECT a.id, a.name, a.sort_name, a.remote_id, COUNT(al.id), 0
107         FROM artists a
108         INNER JOIN albums al ON al.artist_id = a.id"
109    } else {
110        "SELECT a.id, a.name, a.sort_name, a.remote_id,
111                COUNT(DISTINCT al.id), COUNT(t.id)
112         FROM artists a
113         INNER JOIN albums al ON al.artist_id = a.id
114         LEFT JOIN tracks t ON t.album_id = al.id"
115    });
116    let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
117    if let Some(user) = q.favourites_of {
118        params.push(Box::new(super::auth::resolve_user(conn, user)?));
119        sql.push_str(" JOIN favourite_artists f ON f.artist_id = a.id AND f.user_id = ?");
120    }
121    let mut wheres: Vec<String> = Vec::new();
122    if let Some(ids) = q.ids {
123        params.push(Box::new(super::json_list(ids)));
124        wheres.push("a.id IN (SELECT value FROM json_each(?))".into());
125    }
126    if let Some(query) = q.search {
127        params.push(Box::new(format!("%{}%", escape_like(query))));
128        wheres.push("a.name LIKE ? COLLATE NOCASE ESCAPE '\\'".into());
129    }
130    q.filter.push(&mut wheres, &mut params);
131    if !wheres.is_empty() {
132        sql.push_str(" WHERE ");
133        sql.push_str(&wheres.join(" AND "));
134    }
135    sql.push_str(" GROUP BY a.id ORDER BY ");
136    sql.push_str(q.order.clause());
137    if let Some(limit) = q.limit {
138        params.push(Box::new(limit as i64));
139        params.push(Box::new(q.offset as i64));
140        sql.push_str(" LIMIT ? OFFSET ?");
141    }
142
143    let mut stmt = conn.prepare(&sql)?;
144    let rows = stmt
145        .query_map(rusqlite::params_from_iter(params.iter()), artist_row)?
146        .collect::<Result<Vec<_>, _>>()?;
147    Ok(rows)
148}
149
150fn artist_row(row: &rusqlite::Row) -> rusqlite::Result<ArtistRow> {
151    Ok(ArtistRow {
152        id: row.get(0)?,
153        name: row.get(1)?,
154        sort_name: row.get(2)?,
155        remote_id: row.get(3)?,
156        album_count: row.get(4)?,
157        track_count: row.get(5)?,
158    })
159}
160
161/// One artist, with its counts. An artist credited only on other people's
162/// albums (a feature, a compilation track) owns none, and is still an artist:
163/// its track count is every track credited to it or on its albums.
164pub fn get_artist(conn: &Connection, artist_id: i64) -> Result<Option<ArtistRow>, DbError> {
165    Ok(conn
166        .query_row(
167            "SELECT a.id, a.name, a.sort_name, a.remote_id,
168                    (SELECT COUNT(*) FROM albums WHERE artist_id = a.id),
169                    (SELECT COUNT(*) FROM tracks
170                      WHERE artist_id = a.id
171                         OR album_id IN (SELECT id FROM albums WHERE artist_id = a.id))
172             FROM artists a
173             WHERE a.id = ?1",
174            params![artist_id],
175            artist_row,
176        )
177        .ok())
178}
179
180/// Find artists by name (case-insensitive substring match).
181pub fn find_artists(conn: &Connection, query: &str) -> Result<Vec<ArtistRow>, DbError> {
182    list_artists(
183        conn,
184        &ArtistQuery {
185            search: Some(query),
186            ..Default::default()
187        },
188    )
189}
190
191/// Every album artist, sorted by name.
192pub fn all_artists(conn: &Connection) -> Result<Vec<ArtistRow>, DbError> {
193    list_artists(conn, &ArtistQuery::default())
194}
195
196/// Record what the server knows about an artist beyond its name.
197///
198/// Fills blanks rather than overwriting: a local scan may have set a sort name
199/// from tags, and the server's should not clobber it. Matched on `remote_id`,
200/// which the artist already has from the track upserts.
201pub fn enrich_remote_artist(
202    conn: &Connection,
203    remote_id: &str,
204    mbid: Option<&str>,
205    sort_name: Option<&str>,
206) -> Result<(), DbError> {
207    conn.execute(
208        "UPDATE artists SET
209             mbid      = COALESCE(mbid, ?2),
210             sort_name = COALESCE(sort_name, ?3)
211         WHERE remote_id = ?1",
212        params![remote_id, mbid, sort_name],
213    )?;
214    Ok(())
215}
216
217#[cfg(test)]
218mod tests {
219    use super::*;
220    use crate::db::connection::Database;
221
222    fn test_db() -> Database {
223        let conn = rusqlite::Connection::open_in_memory().unwrap();
224        conn.pragma_update(None, "foreign_keys", "on").unwrap();
225        crate::db::schema::create_tables(&conn).unwrap();
226        Database { conn }
227    }
228
229    /// Artists only appear once they own an album, so the fixture goes in
230    /// through a track.
231    fn stocked_db() -> Database {
232        use crate::db::queries::{sample_meta, upsert_track};
233        let db = test_db();
234        for (i, artist) in ["Autechre", "Boards of Canada", "Coil", "Dopplereffekt"]
235            .iter()
236            .enumerate()
237        {
238            let mut m = sample_meta("t", artist, "Album");
239            m.path = Some(format!("/music/{i}/t.flac"));
240            upsert_track(&db.conn, &m).unwrap();
241        }
242        db
243    }
244
245    #[test]
246    fn paging_walks_the_listing_without_repeating() {
247        let db = stocked_db();
248        let page = |offset| {
249            list_artists(
250                &db.conn,
251                &ArtistQuery {
252                    limit: Some(2),
253                    offset,
254                    ..Default::default()
255                },
256            )
257            .unwrap()
258            .into_iter()
259            .map(|a| a.name)
260            .collect::<Vec<_>>()
261        };
262        assert_eq!(page(0), ["Autechre", "Boards of Canada"]);
263        assert_eq!(page(2), ["Coil", "Dopplereffekt"]);
264        assert!(page(4).is_empty());
265    }
266
267    #[test]
268    fn favourites_only_lists_what_was_hearted() {
269        use crate::db::queries::toggle_favourite_artist;
270        let db = stocked_db();
271        let coil: i64 = db
272            .conn
273            .query_row("SELECT id FROM artists WHERE name = 'Coil'", [], |r| {
274                r.get(0)
275            })
276            .unwrap();
277        toggle_favourite_artist(&db.conn, crate::db::queries::LOCAL_USER, coil).unwrap();
278        let rows = list_artists(
279            &db.conn,
280            &ArtistQuery {
281                favourites_of: Some(crate::db::queries::LOCAL_USER),
282                ..Default::default()
283            },
284        )
285        .unwrap();
286        assert_eq!(
287            rows.iter().map(|a| a.name.as_str()).collect::<Vec<_>>(),
288            ["Coil"]
289        );
290    }
291
292    #[test]
293    fn spellings_that_differ_only_in_case_are_one_artist() {
294        let db = stocked_db();
295        let a = get_or_create_artist(&db.conn, "The Squire of Gothos", None).unwrap();
296        let b = get_or_create_artist(&db.conn, "The Squire Of Gothos", None).unwrap();
297        assert_eq!(a, b);
298    }
299
300    #[test]
301    fn an_artist_with_tracks_but_no_albums_is_still_an_artist() {
302        let db = stocked_db();
303        let guest = get_or_create_artist(&db.conn, "A Guest", None).unwrap();
304        let album: i64 = db
305            .conn
306            .query_row("SELECT id FROM albums LIMIT 1", [], |r| r.get(0))
307            .unwrap();
308        db.conn
309            .execute(
310                "INSERT INTO tracks (title, album_id, artist_id, path) VALUES ('Feature', ?1, ?2, '/f.flac')",
311                params![album, guest],
312            )
313            .unwrap();
314        let artist = get_artist(&db.conn, guest)
315            .unwrap()
316            .expect("credited on a track");
317        assert_eq!((artist.album_count, artist.track_count), (0, 1));
318    }
319
320    #[test]
321    fn one_artist_carries_its_counts() {
322        let db = stocked_db();
323        let id = find_artists(&db.conn, "Coil").unwrap()[0].id;
324        let artist = get_artist(&db.conn, id)
325            .unwrap()
326            .expect("Coil owns an album");
327        assert_eq!(artist.name, "Coil");
328        assert_eq!(artist.album_count, 1);
329        assert_eq!(artist.track_count, 1);
330        assert!(get_artist(&db.conn, 9999).unwrap().is_none());
331    }
332
333    #[test]
334    fn test_artist_create_and_dedup() {
335        let db = test_db();
336        let id1 = get_or_create_artist(&db.conn, "Aphex Twin", None).unwrap();
337        let id2 = get_or_create_artist(&db.conn, "Aphex Twin", None).unwrap();
338        assert_eq!(id1, id2);
339
340        let id3 = get_or_create_artist(&db.conn, "Squarepusher", None).unwrap();
341        assert_ne!(id1, id3);
342    }
343}
koan_core/db/queries/artists.rs

koan_core/db/queries/
artists.rs
