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 existing: Option<(i64, Option<String>)> = conn
25        .prepare_cached(
26            "SELECT id, remote_id FROM artists WHERE name = ?1 COLLATE NOCASE
27             ORDER BY name = ?1 DESC, id LIMIT 1",
28        )?
29        .query_row(params![name], |row| Ok((row.get(0)?, row.get(1)?)))
30        .optional()?;
31
32    if let Some((id, stored)) = existing {
33        // The server's current id wins: one that renumbers its library would
34        // otherwise leave the artist under an id it no longer answers to.
35        if let Some(rid) = remote_id {
36            if stored.as_deref() != Some(rid) {
37                conn.prepare_cached("UPDATE artists SET remote_id = ?1 WHERE id = ?2")?
38                    .execute(params![rid, id])?;
39            }
40            super::adopt_uid(conn, super::UidKind::Artist, id, rid)?;
41        }
42        return Ok(id);
43    }
44
45    let uid = super::free_uid(conn, super::UidKind::Artist, remote_id)?;
46    conn.prepare_cached("INSERT INTO artists (name, remote_id, uid) VALUES (?1, ?2, ?3)")?
47        .execute(params![name, 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_name = a.name 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        toggle_favourite_artist(&db.conn, crate::db::queries::LOCAL_USER, "Coil").unwrap();
272        let rows = list_artists(
273            &db.conn,
274            &ArtistQuery {
275                favourites_of: Some(crate::db::queries::LOCAL_USER),
276                ..Default::default()
277            },
278        )
279        .unwrap();
280        assert_eq!(
281            rows.iter().map(|a| a.name.as_str()).collect::<Vec<_>>(),
282            ["Coil"]
283        );
284    }
285
286    #[test]
287    fn spellings_that_differ_only_in_case_are_one_artist() {
288        let db = stocked_db();
289        let a = get_or_create_artist(&db.conn, "The Squire of Gothos", None).unwrap();
290        let b = get_or_create_artist(&db.conn, "The Squire Of Gothos", None).unwrap();
291        assert_eq!(a, b);
292    }
293
294    #[test]
295    fn an_artist_with_tracks_but_no_albums_is_still_an_artist() {
296        let db = stocked_db();
297        let guest = get_or_create_artist(&db.conn, "A Guest", None).unwrap();
298        let album: i64 = db
299            .conn
300            .query_row("SELECT id FROM albums LIMIT 1", [], |r| r.get(0))
301            .unwrap();
302        db.conn
303            .execute(
304                "INSERT INTO tracks (title, album_id, artist_id, path) VALUES ('Feature', ?1, ?2, '/f.flac')",
305                params![album, guest],
306            )
307            .unwrap();
308        let artist = get_artist(&db.conn, guest)
309            .unwrap()
310            .expect("credited on a track");
311        assert_eq!((artist.album_count, artist.track_count), (0, 1));
312    }
313
314    #[test]
315    fn one_artist_carries_its_counts() {
316        let db = stocked_db();
317        let id = find_artists(&db.conn, "Coil").unwrap()[0].id;
318        let artist = get_artist(&db.conn, id)
319            .unwrap()
320            .expect("Coil owns an album");
321        assert_eq!(artist.name, "Coil");
322        assert_eq!(artist.album_count, 1);
323        assert_eq!(artist.track_count, 1);
324        assert!(get_artist(&db.conn, 9999).unwrap().is_none());
325    }
326
327    #[test]
328    fn test_artist_create_and_dedup() {
329        let db = test_db();
330        let id1 = get_or_create_artist(&db.conn, "Aphex Twin", None).unwrap();
331        let id2 = get_or_create_artist(&db.conn, "Aphex Twin", None).unwrap();
332        assert_eq!(id1, id2);
333
334        let id3 = get_or_create_artist(&db.conn, "Squarepusher", None).unwrap();
335        assert_ne!(id1, id3);
336    }
337}
koan_core/db/queries/artists.rs

koan_core/db/queries/
artists.rs
