termusiclib/new_database/
artist_ops.rs

1use anyhow::{Result, bail};
2use indoc::{formatdoc, indoc};
3use rusqlite::{Connection, OptionalExtension, Row, named_params};
4
5use crate::new_database::Integer;
6
7/// Count all rows currently in the `artists` database
8pub fn count_all_artists(conn: &Connection) -> Result<Integer> {
9    let count = conn.query_row("SELECT COUNT(id) FROM artists;", [], |v| v.get(0))?;
10
11    Ok(count)
12}
13
14/// The most common data required for a artist read from the database
15#[derive(Debug, Clone, PartialEq)]
16pub struct ArtistRead {
17    pub id: Integer,
18
19    pub name: String,
20}
21
22#[derive(Debug, Clone, Copy, PartialEq, Eq)]
23pub enum RowOrdering {
24    IdAsc,
25    IdDesc,
26    AddedAsc,
27    AddedDesc,
28}
29
30impl RowOrdering {
31    /// Represent it as the data for a `ORDER BY` clause.
32    fn as_sql(self) -> &'static str {
33        match self {
34            RowOrdering::IdAsc => "artists.id ASC",
35            RowOrdering::IdDesc => "artists.id DESC",
36            RowOrdering::AddedAsc => "artists.added_at ASC",
37            RowOrdering::AddedDesc => "artists.added_at DESC",
38        }
39    }
40}
41
42/// Get all the Artists currently stored in the database with all the important data.
43///
44/// # Panics
45///
46/// If the database schema does not match what is expected.
47pub fn get_all_artists(conn: &Connection, order: RowOrdering) -> Result<Vec<ArtistRead>> {
48    let stmt = formatdoc! {"
49        SELECT artists.id AS artist_id, artists.artist
50        FROM artists
51        ORDER BY {};
52        ",
53        order.as_sql()
54    };
55    let mut stmt = conn.prepare(&stmt)?;
56
57    let result: Vec<ArtistRead> = stmt
58        .query_map(named_params! {}, |row| {
59            let artist_read = common_row_to_artistread(row);
60
61            Ok(artist_read)
62        })?
63        .collect::<Result<Vec<_>, rusqlite::Error>>()?;
64
65    Ok(result)
66}
67
68/// Get all the Artists that match `like`.
69///
70/// # Panics
71///
72/// If the database schema does not match what is expected.
73pub fn get_all_artists_like(
74    conn: &Connection,
75    like: &str,
76    order: RowOrdering,
77) -> Result<Vec<ArtistRead>> {
78    let stmt = formatdoc! {"
79        SELECT artists.id AS artist_id, artists.artist
80        FROM artists
81        WHERE artists.artist LIKE :like
82        ORDER BY {};
83        ",
84        order.as_sql()
85    };
86    let mut stmt = conn.prepare(&stmt)?;
87
88    let result: Vec<ArtistRead> = stmt
89        .query_map(named_params! {":like": like}, |row| {
90            let artist_read = common_row_to_artistread(row);
91
92            Ok(artist_read)
93        })?
94        .collect::<Result<Vec<_>, rusqlite::Error>>()?;
95
96    Ok(result)
97}
98
99/// Get a specific artist.
100///
101/// # Panics
102///
103/// If the database schema does not match what is expected.
104pub fn get_artist(conn: &Connection, artist: &str) -> Result<ArtistRead> {
105    if artist.trim().is_empty() {
106        bail!("Given artist is empty!");
107    }
108
109    let mut stmt = conn.prepare(indoc! {"
110        SELECT artists.id AS artist_id, artists.artist
111        FROM artists
112        WHERE artists.artist=:artist_name;
113    "})?;
114
115    let result: ArtistRead = stmt.query_row(named_params! {":artist_name": artist}, |row| {
116        let artist_read = common_row_to_artistread(row);
117
118        Ok(artist_read)
119    })?;
120
121    Ok(result)
122}
123
124/// Check if a entry for the given `artist` exists.
125///
126/// # Panics
127///
128/// If sqlite somehow does not return what is expected.
129pub fn artist_exists(conn: &Connection, artist: &str) -> Result<bool> {
130    if artist.trim().is_empty() {
131        bail!("Given artist is empty!");
132    }
133
134    let mut stmt = conn.prepare(indoc! {"
135        SELECT artists.id
136        FROM artists
137        WHERE artists.artist=:artist_name;
138    "})?;
139
140    let exists = stmt.exists(named_params! {":artist_name": artist})?;
141
142    Ok(exists)
143}
144
145/// Common function that converts a well-known named row to a [`ArtistRead`].
146///
147/// For row names look at [`get_all_artists`].
148pub(super) fn common_row_to_artistread(row: &Row<'_>) -> ArtistRead {
149    let id = row.get("artist_id").unwrap();
150    let artist_title = row.get("artist").unwrap();
151
152    ArtistRead {
153        id,
154        name: artist_title,
155    }
156}
157
158/// Remove all artists that are unreferenced.
159///
160/// Returns the number of deleted rows. Will return `Ok(0)` if the query did not do anything.
161///
162/// # Panics
163///
164/// If the database schema does not match what is expected.
165pub fn delete_all_unreferenced_artists(conn: &Connection) -> Result<usize> {
166    // the following query is likely very slow compared to other queries
167    let mut stmt = conn.prepare_cached(indoc! {"
168        DELETE FROM artists
169        WHERE artists.id NOT IN (
170            SELECT tracks_artists.artist FROM tracks_artists
171            UNION
172            SELECT albums_artists.artist FROM albums_artists
173        );
174    "})?;
175
176    let affected = stmt
177        .execute(named_params! {})
178        .optional()?
179        .unwrap_or_default();
180
181    Ok(affected)
182}
183
184#[cfg(test)]
185mod tests {
186    use std::{path::Path, time::Duration};
187
188    use pretty_assertions::assert_eq;
189
190    use crate::{
191        new_database::{
192            album_ops::delete_all_unreferenced_albums,
193            artist_insert::ArtistInsertable,
194            artist_ops::{
195                RowOrdering, artist_exists, count_all_artists, delete_all_unreferenced_artists,
196                get_all_artists, get_all_artists_like, get_artist,
197            },
198            test_utils::{gen_database, test_path},
199            track_insert::TrackInsertable,
200        },
201        track::TrackMetadata,
202    };
203
204    #[test]
205    fn all_artists() {
206        let db = gen_database();
207
208        let artist = ArtistInsertable { artist: "ArtistA" };
209        let _artist_id = artist.try_insert_or_update(&db.get_connection()).unwrap();
210
211        let artist = ArtistInsertable { artist: "ArtistB" };
212        let _artist_id = artist.try_insert_or_update(&db.get_connection()).unwrap();
213
214        let artists: Vec<String> = get_all_artists(&db.get_connection(), RowOrdering::IdAsc)
215            .unwrap()
216            .into_iter()
217            .map(|v| v.name)
218            .collect();
219
220        assert_eq!(artists, &["ArtistA", "ArtistB"]);
221    }
222
223    #[test]
224    fn all_artists_like() {
225        let db = gen_database();
226
227        let artist = ArtistInsertable { artist: "ArtistA" };
228        let _artist_id = artist.try_insert_or_update(&db.get_connection()).unwrap();
229
230        let artist = ArtistInsertable { artist: "ArtistB" };
231        let _artist_id = artist.try_insert_or_update(&db.get_connection()).unwrap();
232
233        let artists: Vec<String> =
234            get_all_artists_like(&db.get_connection(), "%artista%", RowOrdering::IdAsc)
235                .unwrap()
236                .into_iter()
237                .map(|v| v.name)
238                .collect();
239
240        assert_eq!(artists, &["ArtistA"]);
241    }
242
243    #[test]
244    fn single_artist() {
245        let db = gen_database();
246
247        let artist = ArtistInsertable { artist: "ArtistA" };
248        let _artist_id = artist.try_insert_or_update(&db.get_connection()).unwrap();
249
250        let artist = ArtistInsertable { artist: "ArtistB" };
251        let _artist_id = artist.try_insert_or_update(&db.get_connection()).unwrap();
252
253        let artist_a = get_artist(&db.get_connection(), "ArtistA").unwrap();
254        assert_eq!(artist_a.name, "ArtistA");
255
256        let artist_b = get_artist(&db.get_connection(), "ArtistB").unwrap();
257        assert_eq!(artist_b.name, "ArtistB");
258
259        let err = get_artist(&db.get_connection(), "ArtistC").unwrap_err();
260        let err = err.downcast::<rusqlite::Error>().unwrap();
261        assert_eq!(err, rusqlite::Error::QueryReturnedNoRows);
262    }
263
264    #[test]
265    fn exists() {
266        let db = gen_database();
267
268        let artist = ArtistInsertable { artist: "ArtistA" };
269        let _artist_id = artist.try_insert_or_update(&db.get_connection()).unwrap();
270
271        let res = artist_exists(&db.get_connection(), "ArtistA").unwrap();
272
273        assert!(res);
274
275        let res = artist_exists(&db.get_connection(), "ArtistB").unwrap();
276
277        assert!(!res);
278    }
279
280    #[test]
281    fn delete_unreferenced_albums() {
282        let db = gen_database();
283
284        let metadata = TrackMetadata {
285            album: Some("AlbumA".to_string()),
286            album_artist: Some("ArtistA".to_string()),
287            album_artists: Some(vec!["ArtistA".to_string()]),
288            artist: Some("ArtistA".to_string()),
289            artists: Some(vec!["ArtistA".to_string()]),
290            title: Some("FileA1".to_string()),
291            duration: Some(Duration::from_secs(10)),
292            ..Default::default()
293        };
294        let path = &test_path(Path::new("/somewhere/fileA1.ext"));
295        let insertable = TrackInsertable::try_from_track(path, &metadata).unwrap();
296        let _ = insertable
297            .try_insert_or_update(&db.get_connection())
298            .unwrap();
299
300        let metadata = TrackMetadata {
301            album: Some("AlbumB".to_string()),
302            album_artist: Some("ArtistB".to_string()),
303            album_artists: Some(vec!["ArtistB".to_string()]),
304            artist: Some("ArtistB".to_string()),
305            artists: Some(vec!["ArtistB".to_string()]),
306            title: Some("FileB1".to_string()),
307            duration: Some(Duration::from_secs(10)),
308            ..Default::default()
309        };
310        let path = &test_path(Path::new("/somewhere/fileB1.ext"));
311        let insertable = TrackInsertable::try_from_track(path, &metadata).unwrap();
312        let _ = insertable
313            .try_insert_or_update(&db.get_connection())
314            .unwrap();
315
316        let artists = count_all_artists(&db.get_connection()).unwrap();
317
318        assert_eq!(artists, 2);
319
320        let affected = delete_all_unreferenced_artists(&db.get_connection()).unwrap();
321
322        assert_eq!(affected, 0);
323
324        let metadata = TrackMetadata {
325            album: Some("AlbumA".to_string()),
326            album_artist: Some("ArtistA".to_string()),
327            album_artists: Some(vec!["ArtistA".to_string()]),
328            artist: Some("ArtistA".to_string()),
329            artists: Some(vec!["ArtistA".to_string()]),
330            title: Some("FileB1".to_string()),
331            duration: Some(Duration::from_secs(10)),
332            ..Default::default()
333        };
334        let path = &test_path(Path::new("/somewhere/fileB1.ext"));
335        let insertable = TrackInsertable::try_from_track(path, &metadata).unwrap();
336        let _ = insertable
337            .try_insert_or_update(&db.get_connection())
338            .unwrap();
339
340        let artists = count_all_artists(&db.get_connection()).unwrap();
341
342        assert_eq!(artists, 2);
343
344        let affected = delete_all_unreferenced_artists(&db.get_connection()).unwrap();
345
346        assert_eq!(affected, 0);
347
348        // albums are unique based on title+artist_display, which changed, so a new album is made and the old one still referenced the artist
349        // and so would otherwise not be deleted, as can be seen by the assert just above
350        let _ = delete_all_unreferenced_albums(&db.get_connection()).unwrap();
351
352        let affected = delete_all_unreferenced_artists(&db.get_connection()).unwrap();
353
354        assert_eq!(affected, 1);
355
356        let artists = count_all_artists(&db.get_connection()).unwrap();
357
358        assert_eq!(artists, 1);
359    }
360}
termusiclib/new_database/artist_ops.rs

termusiclib/new_database/
artist_ops.rs
