rocksky.app
A decentralized music tracking and discovery platform built on AT Protocol 馃幍
rocksky.app
spotify
atproto
lastfm
musicbrainz
scrobbling
listenbrainz
1use anyhow::Error;
2use sqlx::{Pool, Postgres};
3
4use crate::xata::artist::{ArtistRow, ArtistWithStats};
5
6pub async fn get_all_artists(
7 pool: &Pool<Postgres>,
8 user_id: &str,
9) -> Result<Vec<ArtistWithStats>, Error> {
10 let rows: Vec<ArtistWithStats> = sqlx::query_as(
11 r#"
12 SELECT
13 artists.xata_id,
14 artists.name,
15 artists.picture,
16 COUNT(DISTINCT artist_albums.album_id) AS album_count
17 FROM artists
18 JOIN artist_tracks ON artists.xata_id = artist_tracks.artist_id
19 JOIN user_uploads ON artist_tracks.track_id = user_uploads.track_id
20 LEFT JOIN artist_albums ON artists.xata_id = artist_albums.artist_id
21 WHERE user_uploads.user_id = $1
22 GROUP BY artists.xata_id, artists.name, artists.picture
23 ORDER BY artists.name ASC
24 "#,
25 )
26 .bind(user_id)
27 .fetch_all(pool)
28 .await?;
29
30 Ok(rows)
31}
32
33pub async fn get_artist_by_id(
34 pool: &Pool<Postgres>,
35 artist_id: &str,
36 user_id: &str,
37) -> Result<Option<ArtistRow>, Error> {
38 let row: Option<ArtistRow> = sqlx::query_as(
39 r#"
40 SELECT DISTINCT artists.xata_id, artists.name, artists.picture, artists.xata_createdat
41 FROM artists
42 JOIN artist_tracks ON artists.xata_id = artist_tracks.artist_id
43 JOIN user_uploads ON artist_tracks.track_id = user_uploads.track_id
44 WHERE artists.xata_id = $1 AND user_uploads.user_id = $2
45 LIMIT 1
46 "#,
47 )
48 .bind(artist_id)
49 .bind(user_id)
50 .fetch_optional(pool)
51 .await?;
52
53 Ok(row)
54}
55
56pub async fn search_artists(
57 pool: &Pool<Postgres>,
58 user_id: &str,
59 query: &str,
60 count: i64,
61 offset: i64,
62) -> Result<Vec<ArtistWithStats>, Error> {
63 let pattern = format!("%{}%", query);
64 let rows: Vec<ArtistWithStats> = sqlx::query_as(
65 r#"
66 SELECT
67 artists.xata_id,
68 artists.name,
69 artists.picture,
70 COUNT(DISTINCT artist_albums.album_id) AS album_count
71 FROM artists
72 JOIN artist_tracks ON artists.xata_id = artist_tracks.artist_id
73 JOIN user_uploads ON artist_tracks.track_id = user_uploads.track_id
74 LEFT JOIN artist_albums ON artists.xata_id = artist_albums.artist_id
75 WHERE user_uploads.user_id = $1
76 AND LOWER(artists.name) LIKE LOWER($2)
77 GROUP BY artists.xata_id, artists.name, artists.picture
78 ORDER BY artists.name ASC
79 LIMIT $3 OFFSET $4
80 "#,
81 )
82 .bind(user_id)
83 .bind(&pattern)
84 .bind(count)
85 .bind(offset)
86 .fetch_all(pool)
87 .await?;
88
89 Ok(rows)
90}
91
92/// Fetch artists matching names returned by Typesense.
93pub async fn get_artists_by_names(
94 pool: &Pool<Postgres>,
95 user_id: &str,
96 names: &[String],
97) -> Result<Vec<ArtistWithStats>, Error> {
98 if names.is_empty() {
99 return Ok(vec![]);
100 }
101 let name_strs: Vec<&str> = names.iter().map(|s| s.as_str()).collect();
102 let rows: Vec<ArtistWithStats> = sqlx::query_as(
103 r#"
104 SELECT
105 artists.xata_id,
106 artists.name,
107 artists.picture,
108 COUNT(DISTINCT artist_albums.album_id) AS album_count
109 FROM artists
110 JOIN artist_tracks ON artists.xata_id = artist_tracks.artist_id
111 JOIN user_uploads ON artist_tracks.track_id = user_uploads.track_id
112 LEFT JOIN artist_albums ON artists.xata_id = artist_albums.artist_id
113 WHERE user_uploads.user_id = $1
114 AND artists.name = ANY($2)
115 GROUP BY artists.xata_id, artists.name, artists.picture
116 ORDER BY artists.name ASC
117 "#,
118 )
119 .bind(user_id)
120 .bind(&name_strs[..])
121 .fetch_all(pool)
122 .await?;
123
124 Ok(rows)
125}
126
127pub async fn get_picture_by_artist_id(
128 pool: &Pool<Postgres>,
129 artist_id: &str,
130) -> Result<Option<String>, Error> {
131 let row: Option<(Option<String>,)> =
132 sqlx::query_as(r#"SELECT picture FROM artists WHERE xata_id = $1"#)
133 .bind(artist_id)
134 .fetch_optional(pool)
135 .await?;
136
137 Ok(row.and_then(|(p,)| p))
138}