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

koan_core/db/queries/
artists.rs
