rocksky.app
A decentralized music tracking and discovery platform built on AT Protocol 馃幍
rocksky.app
spotify
atproto
lastfm
musicbrainz
scrobbling
listenbrainz
tsiry-sandratraina.com
1use std::sync::{Arc, Mutex};
2
3use crate::types::track::{GetLovedTracksParams, GetTopTracksParams, GetTracksParams, Track};
4use actix_web::{web, HttpRequest, HttpResponse};
5use anyhow::Error;
6use duckdb::Connection;
7use tokio_stream::StreamExt;
8
9use crate::read_payload;
10
11pub async fn get_tracks(
12 payload: &mut web::Payload,
13 _req: &HttpRequest,
14 conn: Arc<Mutex<Connection>>,
15) -> Result<HttpResponse, Error> {
16 let body = read_payload!(payload);
17 let params = serde_json::from_slice::<GetTracksParams>(&body)?;
18 let pagination = params.pagination.unwrap_or_default();
19 let offset = pagination.skip.unwrap_or(0);
20 let limit = pagination.take.unwrap_or(20);
21 let did = params.user_did;
22 let genre = params.genre;
23 tracing::info!(limit, offset, user_did = ?did, genre = ?genre, "Get tracks");
24
25 let conn = conn.lock().unwrap();
26 match did {
27 Some(did) => {
28 let genre_filter = if genre.is_some() {
29 " AND list_contains(a.genres, ?)"
30 } else {
31 ""
32 };
33 let query = format!(
34 r#"
35 SELECT
36 t.id,
37 t.title,
38 t.artist,
39 t.album_artist,
40 t.album_art,
41 t.album,
42 t.track_number,
43 t.duration,
44 t.mb_id,
45 t.youtube_link,
46 t.spotify_link,
47 t.tidal_link,
48 t.apple_music_link,
49 t.sha256,
50 t.composer,
51 t.genre,
52 t.disc_number,
53 t.label,
54 t.uri,
55 t.copyright_message,
56 t.artist_uri,
57 t.album_uri,
58 t.created_at,
59 COUNT(*) AS play_count,
60 COUNT(DISTINCT s.user_id) AS unique_listeners
61 FROM tracks t
62 LEFT JOIN user_tracks ut ON t.id = ut.track_id
63 LEFT JOIN users u ON ut.user_id = u.id
64 LEFT JOIN scrobbles s ON s.track_id = t.id
65 LEFT JOIN artists a ON t.artist_uri = a.uri
66 WHERE (u.did = ? OR u.handle = ?){}
67 GROUP BY t.id, t.title, t.artist, t.album_artist, t.album_art, t.album, t.track_number, t.duration, t.mb_id, t.youtube_link, t.spotify_link, t.tidal_link, t.apple_music_link, t.sha256, t.composer, t.genre, t.disc_number, t.label, t.copyright_message, t.uri, t.created_at, t.artist_uri, t.album_uri
68 ORDER BY play_count DESC
69 LIMIT ?
70 OFFSET ?;
71 "#,
72 genre_filter
73 );
74
75 let mut stmt = conn.prepare(&query)?;
76 let params: Vec<&dyn duckdb::ToSql> = if let Some(g) = &genre {
77 vec![&did, &did, g, &limit, &offset]
78 } else {
79 vec![&did, &did, &limit, &offset]
80 };
81
82 let tracks = stmt.query_map(duckdb::params_from_iter(params), |row| {
83 Ok(Track {
84 id: row.get(0)?,
85 title: row.get(1)?,
86 artist: row.get(2)?,
87 album_artist: row.get(3)?,
88 album_art: row.get(4)?,
89 album: row.get(5)?,
90 track_number: row.get(6)?,
91 duration: row.get(7)?,
92 mb_id: row.get(8)?,
93 youtube_link: row.get(9)?,
94 spotify_link: row.get(10)?,
95 tidal_link: row.get(11)?,
96 apple_music_link: row.get(12)?,
97 sha256: row.get(13)?,
98 composer: row.get(14)?,
99 genre: row.get(15)?,
100 disc_number: row.get(16)?,
101 label: row.get(17)?,
102 uri: row.get(18)?,
103 copyright_message: row.get(19)?,
104 artist_uri: row.get(20)?,
105 album_uri: row.get(21)?,
106 created_at: row.get(22)?,
107 play_count: row.get(23)?,
108 unique_listeners: row.get(24)?,
109 ..Default::default()
110 })
111 })?;
112 let tracks: Result<Vec<_>, _> = tracks.collect();
113 Ok(HttpResponse::Ok().json(tracks?))
114 }
115 None => {
116 let genre_filter = if genre.is_some() {
117 " WHERE list_contains(a.genres, ?)"
118 } else {
119 ""
120 };
121 let query = format!(
122 r#"
123 SELECT
124 t.id,
125 t.title,
126 t.artist,
127 t.album_artist,
128 t.album_art,
129 t.album,
130 t.track_number,
131 t.duration,
132 t.mb_id,
133 t.youtube_link,
134 t.spotify_link,
135 t.tidal_link,
136 t.apple_music_link,
137 t.sha256,
138 t.composer,
139 t.genre,
140 t.disc_number,
141 t.label,
142 t.uri,
143 t.copyright_message,
144 t.artist_uri,
145 t.album_uri,
146 t.created_at,
147 COUNT(*) AS play_count,
148 COUNT(DISTINCT s.user_id) AS unique_listeners
149 FROM tracks t
150 LEFT JOIN scrobbles s ON s.track_id = t.id
151 LEFT JOIN artists a ON t.artist_uri = a.uri{}
152 GROUP BY t.id, t.title, t.artist, t.album_artist, t.album_art, t.album, t.track_number, t.duration, t.mb_id, t.youtube_link, t.spotify_link, t.tidal_link, t.apple_music_link, t.sha256, t.composer, t.genre, t.disc_number, t.label, t.copyright_message, t.uri, t.created_at, t.artist_uri, t.album_uri
153 ORDER BY play_count DESC
154 LIMIT ?
155 OFFSET ?;
156 "#,
157 genre_filter
158 );
159
160 let mut stmt = conn.prepare(&query)?;
161 let params: Vec<&dyn duckdb::ToSql> = if let Some(g) = &genre {
162 vec![g, &limit, &offset]
163 } else {
164 vec![&limit, &offset]
165 };
166
167 let tracks = stmt.query_map(duckdb::params_from_iter(params), |row| {
168 Ok(Track {
169 id: row.get(0)?,
170 title: row.get(1)?,
171 artist: row.get(2)?,
172 album_artist: row.get(3)?,
173 album_art: row.get(4)?,
174 album: row.get(5)?,
175 track_number: row.get(6)?,
176 duration: row.get(7)?,
177 mb_id: row.get(8)?,
178 youtube_link: row.get(9)?,
179 spotify_link: row.get(10)?,
180 tidal_link: row.get(11)?,
181 apple_music_link: row.get(12)?,
182 sha256: row.get(13)?,
183 composer: row.get(14)?,
184 genre: row.get(15)?,
185 disc_number: row.get(16)?,
186 label: row.get(17)?,
187 uri: row.get(18)?,
188 copyright_message: row.get(19)?,
189 artist_uri: row.get(20)?,
190 album_uri: row.get(21)?,
191 created_at: row.get(22)?,
192 play_count: row.get(23)?,
193 unique_listeners: row.get(24)?,
194 ..Default::default()
195 })
196 })?;
197 let tracks: Result<Vec<_>, _> = tracks.collect();
198 Ok(HttpResponse::Ok().json(tracks?))
199 }
200 }
201}
202
203pub async fn get_loved_tracks(
204 payload: &mut web::Payload,
205 _req: &HttpRequest,
206 conn: Arc<Mutex<Connection>>,
207) -> Result<HttpResponse, Error> {
208 let body = read_payload!(payload);
209 let params = serde_json::from_slice::<GetLovedTracksParams>(&body)?;
210 let pagination = params.pagination.unwrap_or_default();
211 let offset = pagination.skip.unwrap_or(0);
212 let limit = pagination.take.unwrap_or(20);
213 let did = params.user_did;
214 tracing::info!(limit, offset, user_did = ?did, "Get loved tracks");
215
216 let conn = conn.lock().unwrap();
217 let mut stmt = conn.prepare(
218 r#"
219 SELECT
220 t.id,
221 t.title,
222 t.artist,
223 t.album,
224 t.album_artist,
225 t.album_art,
226 t.album_uri,
227 t.artist_uri,
228 t.composer,
229 t.copyright_message,
230 t.disc_number,
231 t.duration,
232 t.track_number,
233 t.label,
234 t.spotify_link,
235 t.tidal_link,
236 t.youtube_link,
237 t.apple_music_link,
238 t.sha256,
239 t.uri,
240 u.handle,
241 u.did,
242 l.created_at
243 FROM loved_tracks l
244 LEFT JOIN users u ON l.user_id = u.id
245 LEFT JOIN tracks t ON l.track_id = t.id
246 WHERE u.did = ? OR u.handle = ?
247 ORDER BY l.created_at DESC
248 OFFSET ?
249 LIMIT ?;
250 "#,
251 )?;
252 let loved_tracks = stmt.query_map(
253 [&did, &did, &limit.to_string(), &offset.to_string()],
254 |row| {
255 Ok(Track {
256 id: row.get(0)?,
257 title: row.get(1)?,
258 artist: row.get(2)?,
259 album: row.get(3)?,
260 album_artist: row.get(4)?,
261 album_art: row.get(5)?,
262 album_uri: row.get(6)?,
263 artist_uri: row.get(7)?,
264 composer: row.get(8)?,
265 copyright_message: row.get(9)?,
266 disc_number: row.get(10)?,
267 duration: row.get(11)?,
268 track_number: row.get(12)?,
269 label: row.get(13)?,
270 spotify_link: row.get(14)?,
271 tidal_link: row.get(15)?,
272 youtube_link: row.get(16)?,
273 apple_music_link: row.get(17)?,
274 sha256: row.get(18)?,
275 uri: row.get(19)?,
276 handle: row.get(20)?,
277 did: row.get(21)?,
278 created_at: row.get(22)?,
279 ..Default::default()
280 })
281 },
282 )?;
283 let loved_tracks: Result<Vec<_>, _> = loved_tracks.collect();
284 Ok(HttpResponse::Ok().json(loved_tracks?))
285}
286
287pub async fn get_top_tracks(
288 payload: &mut web::Payload,
289 _req: &HttpRequest,
290 conn: Arc<Mutex<Connection>>,
291) -> Result<HttpResponse, Error> {
292 let body = read_payload!(payload);
293 let params = serde_json::from_slice::<GetTopTracksParams>(&body)?;
294
295 let pagination = params.pagination.clone().unwrap_or_default();
296 let offset: i64 = pagination.skip.unwrap_or(0) as i64;
297 let limit: i64 = pagination.take.unwrap_or(20) as i64;
298
299 let did = params.user_did.clone();
300
301 let start_date: Option<&str> = params.start_date.as_deref();
302 let end_date: Option<&str> = params.end_date.as_deref();
303
304 tracing::info!(
305 limit,
306 offset,
307 user_did = ?did,
308 start_date = ?params.start_date,
309 end_date = ?params.end_date,
310 "Get top tracks"
311 );
312
313 let conn = conn.lock().unwrap();
314
315 match did {
316 Some(did) => {
317 let mut stmt = conn.prepare(
318 r#"
319 SELECT
320 t.id,
321 t.title,
322 t.artist,
323 t.album_artist,
324 t.album,
325 t.uri,
326 t.album_art,
327 t.duration,
328 t.disc_number,
329 t.track_number,
330 t.artist_uri,
331 t.album_uri,
332 t.sha256,
333 t.created_at,
334 COUNT(DISTINCT s.created_at) AS play_count,
335 COUNT(DISTINCT s.user_id) AS unique_listeners
336 FROM scrobbles s
337 LEFT JOIN tracks t ON s.track_id = t.id
338 LEFT JOIN artists ar ON s.artist_id = ar.id
339 LEFT JOIN albums a ON s.album_id = a.id
340 LEFT JOIN users u ON s.user_id = u.id
341 WHERE
342 (u.did = ? OR u.handle = ?)
343 AND (? IS NULL OR s.created_at >= CAST(? AS TIMESTAMP))
344 AND (? IS NULL OR s.created_at <= CAST(? AS TIMESTAMP))
345 GROUP BY
346 t.id, s.track_id, t.title, ar.name, a.title, t.artist, t.uri,
347 t.album_art, t.duration, t.disc_number, t.track_number,
348 t.artist_uri, t.album_uri, t.created_at, t.sha256,
349 t.album_artist, t.album
350 ORDER BY play_count DESC
351 LIMIT ?
352 OFFSET ?;
353 "#,
354 )?;
355
356 let rows = stmt.query_map(
357 duckdb::params![
358 did, // u.did = ?
359 did, // u.handle = ?
360 start_date, // ? IS NULL
361 start_date, // CAST(? AS TIMESTAMP)
362 end_date, // ? IS NULL
363 end_date, // CAST(? AS TIMESTAMP)
364 limit, // LIMIT ?
365 offset // OFFSET ?
366 ],
367 |row| {
368 Ok(Track {
369 id: row.get(0)?,
370 title: row.get(1)?,
371 artist: row.get(2)?,
372 album_artist: row.get(3)?,
373 album: row.get(4)?,
374 uri: row.get(5)?,
375 album_art: row.get(6)?,
376 duration: row.get(7)?,
377 disc_number: row.get(8)?,
378 track_number: row.get(9)?,
379 artist_uri: row.get(10)?,
380 album_uri: row.get(11)?,
381 sha256: row.get(12)?,
382 created_at: row.get(13)?,
383 play_count: row.get(14)?,
384 unique_listeners: row.get(15)?,
385 ..Default::default()
386 })
387 },
388 )?;
389
390 let top_tracks: Result<Vec<_>, _> = rows.collect();
391 Ok(HttpResponse::Ok().json(top_tracks?))
392 }
393
394 None => {
395 let mut stmt = conn.prepare(
396 r#"
397 SELECT
398 t.id,
399 t.title,
400 t.artist,
401 t.album_artist,
402 t.album,
403 t.uri,
404 t.album_art,
405 t.duration,
406 t.disc_number,
407 t.track_number,
408 t.artist_uri,
409 t.album_uri,
410 t.sha256,
411 t.created_at,
412 COUNT(*) AS play_count,
413 COUNT(DISTINCT s.user_id) AS unique_listeners
414 FROM scrobbles s
415 LEFT JOIN tracks t ON s.track_id = t.id
416 LEFT JOIN artists ar ON s.artist_id = ar.id
417 LEFT JOIN albums a ON s.album_id = a.id
418 WHERE
419 s.track_id IS NOT NULL
420 AND s.artist_id IS NOT NULL
421 AND s.album_id IS NOT NULL
422 AND (? IS NULL OR s.created_at >= CAST(? AS TIMESTAMP))
423 AND (? IS NULL OR s.created_at <= CAST(? AS TIMESTAMP))
424 GROUP BY
425 t.id, s.track_id, t.title, ar.name, a.title, t.artist, t.uri,
426 t.album_art, t.duration, t.disc_number, t.track_number,
427 t.artist_uri, t.album_uri, t.created_at, t.sha256,
428 t.album_artist, t.album
429 ORDER BY play_count DESC
430 LIMIT ?
431 OFFSET ?;
432 "#,
433 )?;
434
435 let rows = stmt.query_map(
436 duckdb::params![start_date, start_date, end_date, end_date, limit, offset],
437 |row| {
438 Ok(Track {
439 id: row.get(0)?,
440 title: row.get(1)?,
441 artist: row.get(2)?,
442 album_artist: row.get(3)?,
443 album: row.get(4)?,
444 uri: row.get(5)?,
445 album_art: row.get(6)?,
446 duration: row.get(7)?,
447 disc_number: row.get(8)?,
448 track_number: row.get(9)?,
449 artist_uri: row.get(10)?,
450 album_uri: row.get(11)?,
451 sha256: row.get(12)?,
452 created_at: row.get(13)?,
453 play_count: row.get(14)?,
454 unique_listeners: row.get(15)?,
455 ..Default::default()
456 })
457 },
458 )?;
459
460 let top_tracks: Result<Vec<_>, _> = rows.collect();
461 Ok(HttpResponse::Ok().json(top_tracks?))
462 }
463 }
464}