koan_core/db/queries/
artists.rs

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

koan_core/db/queries/
artists.rs
