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/// What to list. Artists are always album artists — a track-only credit (a
48/// featured guest) appears inline in the queue, not as a shelf of its own.
49#[derive(Debug, Clone, Copy, Default)]
50pub struct ArtistQuery<'a> {
51    /// Case-insensitive substring over the name.
52    pub search: Option<&'a str>,
53    /// Favourited artists only.
54    pub favourites_only: bool,
55    /// `None` for the whole listing. A client that scrolls should page.
56    pub limit: Option<u32>,
57    pub offset: u32,
58}
59
60/// Artists with their album and track counts, narrowed and paged by the
61/// database. Ordered by sort name, falling back to the name.
62pub fn list_artists(conn: &Connection, q: &ArtistQuery) -> Result<Vec<ArtistRow>, DbError> {
63    let mut sql = String::from(
64        "SELECT a.id, a.name, a.sort_name, a.remote_id,
65                COUNT(DISTINCT al.id), COUNT(t.id)
66         FROM artists a
67         INNER JOIN albums al ON al.artist_id = a.id
68         LEFT JOIN tracks t ON t.album_id = al.id",
69    );
70    if q.favourites_only {
71        sql.push_str(" JOIN favourite_artists f ON f.artist_name = a.name");
72    }
73
74    let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
75    if let Some(query) = q.search {
76        params.push(Box::new(format!("%{}%", escape_like(query))));
77        sql.push_str(" WHERE a.name LIKE ? COLLATE NOCASE ESCAPE '\\'");
78    }
79    sql.push_str(" GROUP BY a.id ORDER BY COALESCE(a.sort_name, a.name) COLLATE LIBRARY");
80    if let Some(limit) = q.limit {
81        params.push(Box::new(limit as i64));
82        params.push(Box::new(q.offset as i64));
83        sql.push_str(" LIMIT ? OFFSET ?");
84    }
85
86    let mut stmt = conn.prepare(&sql)?;
87    let rows = stmt
88        .query_map(rusqlite::params_from_iter(params.iter()), artist_row)?
89        .collect::<Result<Vec<_>, _>>()?;
90    Ok(rows)
91}
92
93fn artist_row(row: &rusqlite::Row) -> rusqlite::Result<ArtistRow> {
94    Ok(ArtistRow {
95        id: row.get(0)?,
96        name: row.get(1)?,
97        sort_name: row.get(2)?,
98        remote_id: row.get(3)?,
99        album_count: row.get(4)?,
100        track_count: row.get(5)?,
101    })
102}
103
104/// One artist, with its counts. `None` if it owns no albums.
105pub fn get_artist(conn: &Connection, artist_id: i64) -> Result<Option<ArtistRow>, DbError> {
106    Ok(conn
107        .query_row(
108            "SELECT a.id, a.name, a.sort_name, a.remote_id,
109                    COUNT(DISTINCT al.id), COUNT(t.id)
110             FROM artists a
111             INNER JOIN albums al ON al.artist_id = a.id
112             LEFT JOIN tracks t ON t.album_id = al.id
113             WHERE a.id = ?1
114             GROUP BY a.id",
115            params![artist_id],
116            artist_row,
117        )
118        .ok())
119}
120
121/// Find artists by name (case-insensitive substring match).
122pub fn find_artists(conn: &Connection, query: &str) -> Result<Vec<ArtistRow>, DbError> {
123    list_artists(
124        conn,
125        &ArtistQuery {
126            search: Some(query),
127            ..Default::default()
128        },
129    )
130}
131
132/// Every album artist, sorted by name.
133pub fn all_artists(conn: &Connection) -> Result<Vec<ArtistRow>, DbError> {
134    list_artists(conn, &ArtistQuery::default())
135}
136
137/// Record what the server knows about an artist beyond its name.
138///
139/// Fills blanks rather than overwriting: a local scan may have set a sort name
140/// from tags, and the server's should not clobber it. Matched on `remote_id`,
141/// which the artist already has from the track upserts.
142pub fn enrich_remote_artist(
143    conn: &Connection,
144    remote_id: &str,
145    mbid: Option<&str>,
146    sort_name: Option<&str>,
147) -> Result<(), DbError> {
148    conn.execute(
149        "UPDATE artists SET
150             mbid      = COALESCE(mbid, ?2),
151             sort_name = COALESCE(sort_name, ?3)
152         WHERE remote_id = ?1",
153        params![remote_id, mbid, sort_name],
154    )?;
155    Ok(())
156}
157
158#[cfg(test)]
159mod tests {
160    use super::*;
161    use crate::db::connection::Database;
162
163    fn test_db() -> Database {
164        let conn = rusqlite::Connection::open_in_memory().unwrap();
165        conn.pragma_update(None, "foreign_keys", "on").unwrap();
166        crate::db::schema::create_tables(&conn).unwrap();
167        Database { conn }
168    }
169
170    /// Artists only appear once they own an album, so the fixture goes in
171    /// through a track.
172    fn stocked_db() -> Database {
173        use crate::db::queries::{sample_meta, upsert_track};
174        let db = test_db();
175        for (i, artist) in ["Autechre", "Boards of Canada", "Coil", "Dopplereffekt"]
176            .iter()
177            .enumerate()
178        {
179            let mut m = sample_meta("t", artist, "Album");
180            m.path = Some(format!("/music/{i}/t.flac"));
181            upsert_track(&db.conn, &m).unwrap();
182        }
183        db
184    }
185
186    #[test]
187    fn paging_walks_the_listing_without_repeating() {
188        let db = stocked_db();
189        let page = |offset| {
190            list_artists(
191                &db.conn,
192                &ArtistQuery {
193                    limit: Some(2),
194                    offset,
195                    ..Default::default()
196                },
197            )
198            .unwrap()
199            .into_iter()
200            .map(|a| a.name)
201            .collect::<Vec<_>>()
202        };
203        assert_eq!(page(0), ["Autechre", "Boards of Canada"]);
204        assert_eq!(page(2), ["Coil", "Dopplereffekt"]);
205        assert!(page(4).is_empty());
206    }
207
208    #[test]
209    fn favourites_only_lists_what_was_hearted() {
210        use crate::db::queries::toggle_favourite_artist;
211        let db = stocked_db();
212        toggle_favourite_artist(&db.conn, "Coil").unwrap();
213        let rows = list_artists(
214            &db.conn,
215            &ArtistQuery {
216                favourites_only: true,
217                ..Default::default()
218            },
219        )
220        .unwrap();
221        assert_eq!(
222            rows.iter().map(|a| a.name.as_str()).collect::<Vec<_>>(),
223            ["Coil"]
224        );
225    }
226
227    #[test]
228    fn one_artist_carries_its_counts() {
229        let db = stocked_db();
230        let id = find_artists(&db.conn, "Coil").unwrap()[0].id;
231        let artist = get_artist(&db.conn, id)
232            .unwrap()
233            .expect("Coil owns an album");
234        assert_eq!(artist.name, "Coil");
235        assert_eq!(artist.album_count, 1);
236        assert_eq!(artist.track_count, 1);
237        assert!(get_artist(&db.conn, 9999).unwrap().is_none());
238    }
239
240    #[test]
241    fn test_artist_create_and_dedup() {
242        let db = test_db();
243        let id1 = get_or_create_artist(&db.conn, "Aphex Twin", None).unwrap();
244        let id2 = get_or_create_artist(&db.conn, "Aphex Twin", None).unwrap();
245        assert_eq!(id1, id2);
246
247        let id3 = get_or_create_artist(&db.conn, "Squarepusher", None).unwrap();
248        assert_ne!(id1, id3);
249    }
250}
koan_core/db/queries/artists.rs

koan_core/db/queries/
artists.rs
