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::album::AlbumWithStats;
5
6pub async fn get_albums_by_artist(
7 pool: &Pool<Postgres>,
8 artist_id: &str,
9 user_id: &str,
10) -> Result<Vec<AlbumWithStats>, Error> {
11 let rows: Vec<AlbumWithStats> = sqlx::query_as(
12 r#"
13 SELECT
14 albums.xata_id,
15 albums.title,
16 albums.artist,
17 albums.year,
18 albums.album_art,
19 COUNT(DISTINCT album_tracks.track_id) AS song_count,
20 SUM(tracks.duration)::bigint AS total_duration,
21 MIN(user_uploads.uploaded_at)::timestamptz AS created_at,
22 $2::text AS artist_id
23 FROM albums
24 JOIN artist_albums ON albums.xata_id = artist_albums.album_id
25 JOIN album_tracks ON albums.xata_id = album_tracks.album_id
26 JOIN tracks ON album_tracks.track_id = tracks.xata_id
27 JOIN user_uploads ON tracks.xata_id = user_uploads.track_id
28 WHERE artist_albums.artist_id = $2
29 AND user_uploads.user_id = $1
30 GROUP BY albums.xata_id, albums.title, albums.artist, albums.year, albums.album_art
31 ORDER BY albums.year DESC NULLS LAST
32 "#,
33 )
34 .bind(user_id)
35 .bind(artist_id)
36 .fetch_all(pool)
37 .await?;
38
39 Ok(rows)
40}
41
42pub async fn get_album_by_id(
43 pool: &Pool<Postgres>,
44 album_id: &str,
45 user_id: &str,
46) -> Result<Option<AlbumWithStats>, Error> {
47 let row: Option<AlbumWithStats> = sqlx::query_as(
48 r#"
49 SELECT
50 albums.xata_id,
51 albums.title,
52 albums.artist,
53 albums.year,
54 albums.album_art,
55 COUNT(DISTINCT album_tracks.track_id) AS song_count,
56 SUM(tracks.duration)::bigint AS total_duration,
57 MIN(user_uploads.uploaded_at)::timestamptz AS created_at,
58 (SELECT aa.artist_id FROM artist_albums aa WHERE aa.album_id = albums.xata_id LIMIT 1) AS artist_id
59 FROM albums
60 JOIN album_tracks ON albums.xata_id = album_tracks.album_id
61 JOIN tracks ON album_tracks.track_id = tracks.xata_id
62 JOIN user_uploads ON tracks.xata_id = user_uploads.track_id
63 WHERE user_uploads.user_id = $1
64 AND albums.xata_id = $2
65 GROUP BY albums.xata_id, albums.title, albums.artist, albums.year, albums.album_art
66 "#,
67 )
68 .bind(user_id)
69 .bind(album_id)
70 .fetch_optional(pool)
71 .await?;
72
73 Ok(row)
74}
75
76pub async fn get_album_list(
77 pool: &Pool<Postgres>,
78 user_id: &str,
79 list_type: &str,
80 count: i64,
81 offset: i64,
82 from_year: Option<i32>,
83 to_year: Option<i32>,
84 genre: Option<&str>,
85) -> Result<Vec<AlbumWithStats>, Error> {
86 let order_clause = match list_type {
87 "newest" => "ORDER BY created_at DESC NULLS LAST",
88 "alphabeticalByName" => "ORDER BY albums.title ASC",
89 "alphabeticalByArtist" => "ORDER BY albums.artist ASC",
90 "random" => "ORDER BY RANDOM()",
91 "recent" => "ORDER BY created_at DESC NULLS LAST",
92 "byYear" => {
93 if from_year.unwrap_or(0) > to_year.unwrap_or(9999) {
94 "ORDER BY albums.year DESC NULLS LAST"
95 } else {
96 "ORDER BY albums.year ASC NULLS LAST"
97 }
98 }
99 _ => "ORDER BY created_at DESC NULLS LAST",
100 };
101
102 let year_filter = if list_type == "byYear" {
103 let from = from_year.unwrap_or(0);
104 let to = to_year.unwrap_or(9999);
105 format!(
106 " AND albums.year BETWEEN {} AND {}",
107 from.min(to),
108 from.max(to)
109 )
110 } else {
111 String::new()
112 };
113
114 // byGenre needs an extra join through artist_albums → artists to check genres array
115 let (genre_join, genre_filter) = if list_type == "byGenre" {
116 if let Some(_g) = genre {
117 (
118 "JOIN artist_albums ag ON albums.xata_id = ag.album_id JOIN artists ON ag.artist_id = artists.xata_id".to_string(),
119 format!(" AND $4::text = ANY(artists.genres)"),
120 )
121 } else {
122 (String::new(), String::new())
123 }
124 } else {
125 (String::new(), String::new())
126 };
127
128 let sql = format!(
129 r#"
130 SELECT
131 albums.xata_id,
132 albums.title,
133 albums.artist,
134 albums.year,
135 albums.album_art,
136 COUNT(DISTINCT album_tracks.track_id) AS song_count,
137 SUM(tracks.duration)::bigint AS total_duration,
138 MIN(user_uploads.uploaded_at)::timestamptz AS created_at,
139 (SELECT aa.artist_id FROM artist_albums aa WHERE aa.album_id = albums.xata_id LIMIT 1) AS artist_id
140 FROM albums
141 JOIN album_tracks ON albums.xata_id = album_tracks.album_id
142 JOIN tracks ON album_tracks.track_id = tracks.xata_id
143 JOIN user_uploads ON tracks.xata_id = user_uploads.track_id
144 {}
145 WHERE user_uploads.user_id = $1
146 {}{}
147 GROUP BY albums.xata_id, albums.title, albums.artist, albums.year, albums.album_art
148 {}
149 LIMIT $2 OFFSET $3
150 "#,
151 genre_join, year_filter, genre_filter, order_clause
152 );
153
154 let mut q = sqlx::query_as::<_, AlbumWithStats>(&sql)
155 .bind(user_id) // $1
156 .bind(count) // $2
157 .bind(offset); // $3
158 if list_type == "byGenre" {
159 q = q.bind(genre.unwrap_or("")); // $4
160 }
161 let rows: Vec<AlbumWithStats> = q.fetch_all(pool).await?;
162
163 Ok(rows)
164}
165
166pub async fn search_albums(
167 pool: &Pool<Postgres>,
168 user_id: &str,
169 query: &str,
170 count: i64,
171 offset: i64,
172) -> Result<Vec<AlbumWithStats>, Error> {
173 let pattern = format!("%{}%", query);
174 let rows: Vec<AlbumWithStats> = sqlx::query_as(
175 r#"
176 SELECT
177 albums.xata_id,
178 albums.title,
179 albums.artist,
180 albums.year,
181 albums.album_art,
182 COUNT(DISTINCT album_tracks.track_id) AS song_count,
183 SUM(tracks.duration)::bigint AS total_duration,
184 MIN(user_uploads.uploaded_at)::timestamptz AS created_at,
185 (SELECT aa.artist_id FROM artist_albums aa WHERE aa.album_id = albums.xata_id LIMIT 1) AS artist_id
186 FROM albums
187 JOIN album_tracks ON albums.xata_id = album_tracks.album_id
188 JOIN tracks ON album_tracks.track_id = tracks.xata_id
189 JOIN user_uploads ON tracks.xata_id = user_uploads.track_id
190 WHERE user_uploads.user_id = $1
191 AND LOWER(albums.title) LIKE LOWER($2)
192 GROUP BY albums.xata_id, albums.title, albums.artist, albums.year, albums.album_art
193 ORDER BY albums.title ASC
194 LIMIT $3 OFFSET $4
195 "#,
196 )
197 .bind(user_id)
198 .bind(&pattern)
199 .bind(count)
200 .bind(offset)
201 .fetch_all(pool)
202 .await?;
203
204 Ok(rows)
205}
206
207/// Fetch albums matching a list of (title, artist) pairs returned by Typesense.
208pub async fn get_albums_by_names(
209 pool: &Pool<Postgres>,
210 user_id: &str,
211 pairs: &[(String, String)],
212) -> Result<Vec<AlbumWithStats>, Error> {
213 if pairs.is_empty() {
214 return Ok(vec![]);
215 }
216 let titles: Vec<&str> = pairs.iter().map(|(t, _)| t.as_str()).collect();
217 let artists: Vec<&str> = pairs.iter().map(|(_, a)| a.as_str()).collect();
218 let rows: Vec<AlbumWithStats> = sqlx::query_as(
219 r#"
220 SELECT
221 albums.xata_id,
222 albums.title,
223 albums.artist,
224 albums.year,
225 albums.album_art,
226 COUNT(DISTINCT album_tracks.track_id) AS song_count,
227 SUM(tracks.duration)::bigint AS total_duration,
228 MIN(user_uploads.uploaded_at)::timestamptz AS created_at,
229 (SELECT aa.artist_id FROM artist_albums aa WHERE aa.album_id = albums.xata_id LIMIT 1) AS artist_id
230 FROM albums
231 JOIN album_tracks ON albums.xata_id = album_tracks.album_id
232 JOIN tracks ON album_tracks.track_id = tracks.xata_id
233 JOIN user_uploads ON tracks.xata_id = user_uploads.track_id
234 WHERE user_uploads.user_id = $1
235 AND albums.title = ANY($2)
236 AND albums.artist = ANY($3)
237 GROUP BY albums.xata_id, albums.title, albums.artist, albums.year, albums.album_art
238 ORDER BY albums.title ASC
239 "#,
240 )
241 .bind(user_id)
242 .bind(&titles[..])
243 .bind(&artists[..])
244 .fetch_all(pool)
245 .await?;
246
247 Ok(rows)
248}
249
250pub async fn get_album_art(pool: &Pool<Postgres>, album_id: &str) -> Result<Option<String>, Error> {
251 let row: Option<(Option<String>,)> =
252 sqlx::query_as(r#"SELECT album_art FROM albums WHERE xata_id = $1"#)
253 .bind(album_id)
254 .fetch_optional(pool)
255 .await?;
256
257 Ok(row.and_then(|(art,)| art))
258}