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    /// Most recently played first. Only with `ArtistQuery::played`, which is
68    /// what knows when; without it, `Name`.
69    LastPlayed,
70    /// The closest match to `ArtistQuery::search` first (see
71    /// `search::match_rank`), then by name. Without a search, in the order of
72    /// `ArtistQuery::ids`; without either, `Name`.
73    Relevance,
74}
75
76impl ArtistOrder {
77    fn clause(self) -> &'static str {
78        match self {
79            Self::Name | Self::Relevance => "a.name COLLATE LIBRARY",
80            Self::AlbumCount => "COUNT(DISTINCT al.id) DESC, a.name COLLATE LIBRARY",
81            Self::RecentlyAdded => "COALESCE(MAX(al.added_at), '') DESC, a.name COLLATE LIBRARY",
82            Self::Id => "a.id",
83            Self::LastPlayed => "MAX(p.last) DESC, MAX(p.last_id) DESC",
84        }
85    }
86}
87
88/// What to list. Artists are always album artists — a track-only credit (a
89/// featured guest) appears inline in the queue, not as a shelf of its own.
90#[derive(Debug, Clone, Copy, Default)]
91pub struct ArtistQuery<'a> {
92    /// Only these artists.
93    pub ids: Option<&'a [i64]>,
94    /// Case-insensitive substring over the name. When no artist's name holds
95    /// it, the closest fuzzy matches instead (`search::fuzzy_ids`).
96    pub search: Option<&'a str>,
97    /// Only artists this user has favourited.
98    pub favourites_of: Option<i64>,
99    /// Only artists with a record played since then, by its album artist.
100    pub played: Option<super::history::PlayedSince>,
101    /// Albums that count; an artist with none left is not listed, and the
102    /// counts are of what is left.
103    pub filter: super::albums::AlbumFilter<'a>,
104    pub order: ArtistOrder,
105    /// Leave `track_count` at zero rather than read every track in the library
106    /// to count them.
107    pub without_track_counts: bool,
108    /// `None` for the whole listing. A client that scrolls should page.
109    pub limit: Option<u32>,
110    pub offset: u32,
111}
112
113/// Artists with their album and track counts, narrowed, ordered and paged by
114/// the database.
115pub fn list_artists(conn: &Connection, q: &ArtistQuery) -> Result<Vec<ArtistRow>, DbError> {
116    if let Some(ids) = fuzzy_fallback(conn, q)? {
117        return list_artists(
118            conn,
119            &ArtistQuery {
120                search: None,
121                ids: Some(&ids),
122                ..*q
123            },
124        );
125    }
126    let columns = if q.without_track_counts {
127        "a.id, a.name, a.sort_name, a.remote_id, COUNT(al.id), 0"
128    } else {
129        "a.id, a.name, a.sort_name, a.remote_id, COUNT(DISTINCT al.id), COUNT(t.id)"
130    };
131    let (body, mut params) = artist_body(conn, q)?;
132    let order = match q.order {
133        ArtistOrder::LastPlayed if q.played.is_none() => ArtistOrder::Name,
134        order => order,
135    };
136    let order_by = match (order, q.search, q.ids) {
137        (ArtistOrder::Relevance, Some(query), _) => {
138            params.extend(
139                super::search::match_rank_binds(query)
140                    .map(|b| Box::new(b) as Box<dyn rusqlite::ToSql>),
141            );
142            format!(
143                "{}, a.name COLLATE LIBRARY",
144                super::search::match_rank("a.name")
145            )
146        }
147        (ArtistOrder::Relevance, None, Some(_)) => "MIN(r.key)".into(),
148        (order, ..) => order.clause().into(),
149    };
150    let mut sql = format!("SELECT {columns} {body} ORDER BY {order_by}");
151    if let Some(limit) = q.limit {
152        params.push(Box::new(limit as i64));
153        params.push(Box::new(q.offset as i64));
154        sql.push_str(" LIMIT ? OFFSET ?");
155    }
156
157    let mut stmt = conn.prepare(&sql)?;
158    let rows = stmt
159        .query_map(rusqlite::params_from_iter(params.iter()), artist_row)?
160        .collect::<Result<Vec<_>, _>>()?;
161    Ok(rows)
162}
163
164/// How many artists `list_artists` would list for `q`, ignoring its paging.
165pub fn count_artists(conn: &Connection, q: &ArtistQuery) -> Result<u64, DbError> {
166    if let Some(ids) = fuzzy_fallback(conn, q)? {
167        return count_artists(
168            conn,
169            &ArtistQuery {
170                search: None,
171                ids: Some(&ids),
172                ..*q
173            },
174        );
175    }
176    let (body, params) = artist_body(conn, q)?;
177    let n: i64 = conn.query_row(
178        &format!("SELECT COUNT(*) FROM (SELECT a.id {body})"),
179        rusqlite::params_from_iter(params.iter()),
180        |r| r.get(0),
181    )?;
182    Ok(n as u64)
183}
184
185/// Whether `q` lists its ids in their own order: `Relevance` with no search.
186fn ranked_by_ids(q: &ArtistQuery) -> bool {
187    q.order == ArtistOrder::Relevance && q.search.is_none()
188}
189
190/// What `q`'s search lists when no artist's name holds it: the fuzzy matches,
191/// best first. `None` when it has no search or the search finds something.
192fn fuzzy_fallback(conn: &Connection, q: &ArtistQuery) -> Result<Option<Vec<i64>>, DbError> {
193    let Some(query) = q.search else {
194        return Ok(None);
195    };
196    let (body, params) = artist_body(conn, q)?;
197    let found: bool = conn.query_row(
198        &format!("SELECT EXISTS (SELECT 1 {body})"),
199        rusqlite::params_from_iter(params.iter()),
200        |r| r.get(0),
201    )?;
202    if found {
203        return Ok(None);
204    }
205    let mut ids = super::search::fuzzy_ids(conn, super::search::CorpusKind::Artist, query)?;
206    if let Some(only) = q.ids {
207        ids.retain(|id| only.contains(id));
208    }
209    Ok(Some(ids))
210}
211
212/// The FROM, WHERE and GROUP BY that `list_artists` and `count_artists`
213/// share, and their parameters, in order.
214fn artist_body(
215    conn: &Connection,
216    q: &ArtistQuery,
217) -> Result<(String, Vec<Box<dyn rusqlite::ToSql>>), DbError> {
218    let mut sql = String::from(if q.without_track_counts {
219        "FROM artists a
220         INNER JOIN albums al ON al.artist_id = a.id"
221    } else {
222        "FROM artists a
223         INNER JOIN albums al ON al.artist_id = a.id
224         LEFT JOIN tracks t ON t.album_id = al.id"
225    });
226    let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
227    if let Some(user) = q.favourites_of {
228        params.push(Box::new(super::auth::resolve_user(conn, user)?));
229        sql.push_str(" JOIN favourite_artists f ON f.artist_id = a.id AND f.user_id = ?");
230    }
231    if let Some(played) = q.played {
232        params.push(Box::new(super::auth::resolve_user(conn, played.user)?));
233        params.push(Box::new(played.since));
234        sql.push_str(
235            " JOIN (SELECT pal.artist_id AS id, MAX(h.played_at) AS last, MAX(h.id) AS last_id
236                      FROM play_history h
237                      JOIN tracks pt ON pt.id = h.track_id
238                      JOIN albums pal ON pal.id = pt.album_id
239                     WHERE h.user_id = ? AND h.played_at >= ?
240                     GROUP BY pal.artist_id) p ON p.id = a.id",
241        );
242    }
243    let mut wheres: Vec<String> = Vec::new();
244    match q.ids {
245        // Joined once rather than looked up per row, so the order of the ids
246        // is `r.key`.
247        Some(ids) if ranked_by_ids(q) => {
248            params.push(Box::new(super::json_list(ids)));
249            sql.push_str(" JOIN json_each(?) r ON r.value = a.id");
250        }
251        Some(ids) => {
252            params.push(Box::new(super::json_list(ids)));
253            wheres.push("a.id IN (SELECT value FROM json_each(?))".into());
254        }
255        None => {}
256    }
257    if let Some(query) = q.search {
258        params.push(Box::new(format!("%{}%", escape_like(query))));
259        wheres.push("a.name LIKE ? COLLATE NOCASE ESCAPE '\\'".into());
260    }
261    q.filter.push(&mut wheres, &mut params);
262    if !wheres.is_empty() {
263        sql.push_str(" WHERE ");
264        sql.push_str(&wheres.join(" AND "));
265    }
266    sql.push_str(" GROUP BY a.id");
267    Ok((sql, params))
268}
269
270fn artist_row(row: &rusqlite::Row) -> rusqlite::Result<ArtistRow> {
271    Ok(ArtistRow {
272        id: row.get(0)?,
273        name: row.get(1)?,
274        sort_name: row.get(2)?,
275        remote_id: row.get(3)?,
276        album_count: row.get(4)?,
277        track_count: row.get(5)?,
278    })
279}
280
281/// One artist, with its counts. An artist credited only on other people's
282/// albums (a feature, a compilation track) owns none, and is still an artist:
283/// its track count is every track credited to it or on its albums.
284pub fn get_artist(conn: &Connection, artist_id: i64) -> Result<Option<ArtistRow>, DbError> {
285    Ok(conn
286        .query_row(
287            "SELECT a.id, a.name, a.sort_name, a.remote_id,
288                    (SELECT COUNT(*) FROM albums WHERE artist_id = a.id),
289                    (SELECT COUNT(*) FROM tracks
290                      WHERE artist_id = a.id
291                         OR album_id IN (SELECT id FROM albums WHERE artist_id = a.id))
292             FROM artists a
293             WHERE a.id = ?1",
294            params![artist_id],
295            artist_row,
296        )
297        .ok())
298}
299
300/// Find artists by name (case-insensitive substring match).
301pub fn find_artists(conn: &Connection, query: &str) -> Result<Vec<ArtistRow>, DbError> {
302    list_artists(
303        conn,
304        &ArtistQuery {
305            search: Some(query),
306            ..Default::default()
307        },
308    )
309}
310
311/// Every album artist, sorted by name.
312pub fn all_artists(conn: &Connection) -> Result<Vec<ArtistRow>, DbError> {
313    list_artists(conn, &ArtistQuery::default())
314}
315
316/// Record what the server knows about an artist beyond its name.
317///
318/// Fills blanks rather than overwriting: a local scan may have set a sort name
319/// from tags, and the server's should not clobber it. Matched on `remote_id`,
320/// which the artist already has from the track upserts.
321pub fn enrich_remote_artist(
322    conn: &Connection,
323    remote_id: &str,
324    mbid: Option<&str>,
325    sort_name: Option<&str>,
326) -> Result<(), DbError> {
327    conn.execute(
328        "UPDATE artists SET
329             mbid      = COALESCE(mbid, ?2),
330             sort_name = COALESCE(sort_name, ?3)
331         WHERE remote_id = ?1",
332        params![remote_id, mbid, sort_name],
333    )?;
334    Ok(())
335}
336
337#[cfg(test)]
338mod tests {
339    use super::*;
340    use crate::db::connection::Database;
341
342    fn test_db() -> Database {
343        let conn = rusqlite::Connection::open_in_memory().unwrap();
344        conn.pragma_update(None, "foreign_keys", "on").unwrap();
345        crate::db::schema::create_tables(&conn).unwrap();
346        Database { conn }
347    }
348
349    /// Artists only appear once they own an album, so the fixture goes in
350    /// through a track.
351    fn stocked_db() -> Database {
352        use crate::db::queries::{sample_meta, upsert_track};
353        let db = test_db();
354        for (i, artist) in ["Autechre", "Boards of Canada", "Coil", "Dopplereffekt"]
355            .iter()
356            .enumerate()
357        {
358            let mut m = sample_meta("t", artist, "Album");
359            m.path = Some(format!("/music/{i}/t.flac"));
360            upsert_track(&db.conn, &m).unwrap();
361        }
362        db
363    }
364
365    #[test]
366    fn paging_walks_the_listing_without_repeating() {
367        let db = stocked_db();
368        let page = |offset| {
369            list_artists(
370                &db.conn,
371                &ArtistQuery {
372                    limit: Some(2),
373                    offset,
374                    ..Default::default()
375                },
376            )
377            .unwrap()
378            .into_iter()
379            .map(|a| a.name)
380            .collect::<Vec<_>>()
381        };
382        assert_eq!(page(0), ["Autechre", "Boards of Canada"]);
383        assert_eq!(page(2), ["Coil", "Dopplereffekt"]);
384        assert!(page(4).is_empty());
385    }
386
387    #[test]
388    fn favourites_only_lists_what_was_hearted() {
389        use crate::db::queries::toggle_favourite_artist;
390        let db = stocked_db();
391        let coil: i64 = db
392            .conn
393            .query_row("SELECT id FROM artists WHERE name = 'Coil'", [], |r| {
394                r.get(0)
395            })
396            .unwrap();
397        toggle_favourite_artist(&db.conn, crate::db::queries::LOCAL_USER, coil).unwrap();
398        let rows = list_artists(
399            &db.conn,
400            &ArtistQuery {
401                favourites_of: Some(crate::db::queries::LOCAL_USER),
402                ..Default::default()
403            },
404        )
405        .unwrap();
406        assert_eq!(
407            rows.iter().map(|a| a.name.as_str()).collect::<Vec<_>>(),
408            ["Coil"]
409        );
410    }
411
412    #[test]
413    fn spellings_that_differ_only_in_case_are_one_artist() {
414        let db = stocked_db();
415        let a = get_or_create_artist(&db.conn, "The Squire of Gothos", None).unwrap();
416        let b = get_or_create_artist(&db.conn, "The Squire Of Gothos", None).unwrap();
417        assert_eq!(a, b);
418    }
419
420    #[test]
421    fn an_artist_with_tracks_but_no_albums_is_still_an_artist() {
422        let db = stocked_db();
423        let guest = get_or_create_artist(&db.conn, "A Guest", None).unwrap();
424        let album: i64 = db
425            .conn
426            .query_row("SELECT id FROM albums LIMIT 1", [], |r| r.get(0))
427            .unwrap();
428        db.conn
429            .execute(
430                "INSERT INTO tracks (title, album_id, artist_id, path) VALUES ('Feature', ?1, ?2, '/f.flac')",
431                params![album, guest],
432            )
433            .unwrap();
434        let artist = get_artist(&db.conn, guest)
435            .unwrap()
436            .expect("credited on a track");
437        assert_eq!((artist.album_count, artist.track_count), (0, 1));
438    }
439
440    #[test]
441    fn one_artist_carries_its_counts() {
442        let db = stocked_db();
443        let id = find_artists(&db.conn, "Coil").unwrap()[0].id;
444        let artist = get_artist(&db.conn, id)
445            .unwrap()
446            .expect("Coil owns an album");
447        assert_eq!(artist.name, "Coil");
448        assert_eq!(artist.album_count, 1);
449        assert_eq!(artist.track_count, 1);
450        assert!(get_artist(&db.conn, 9999).unwrap().is_none());
451    }
452
453    #[test]
454    fn test_artist_create_and_dedup() {
455        let db = test_db();
456        let id1 = get_or_create_artist(&db.conn, "Aphex Twin", None).unwrap();
457        let id2 = get_or_create_artist(&db.conn, "Aphex Twin", None).unwrap();
458        assert_eq!(id1, id2);
459
460        let id3 = get_or_create_artist(&db.conn, "Squarepusher", None).unwrap();
461        assert_ne!(id1, id3);
462    }
463}
koan_core/db/queries/artists.rs

koan_core/db/queries/
artists.rs
