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::track::TrackWithUpload;
5
6const TRACK_SELECT: &str = r#"
7 SELECT
8 tracks.xata_id,
9 tracks.title,
10 tracks.artist,
11 tracks.album_artist,
12 tracks.album_art,
13 tracks.album,
14 tracks.track_number,
15 tracks.disc_number,
16 tracks.duration,
17 tracks.mb_id,
18 tracks.genre,
19 tracks.xata_createdat,
20 user_uploads.r2_key,
21 user_uploads.mime_type,
22 user_uploads.file_size
23 FROM tracks
24 JOIN user_uploads ON tracks.xata_id = user_uploads.track_id
25"#;
26
27pub async fn get_tracks_by_album(
28 pool: &Pool<Postgres>,
29 album_id: &str,
30 user_id: &str,
31) -> Result<Vec<TrackWithUpload>, Error> {
32 let rows: Vec<TrackWithUpload> = sqlx::query_as(&format!(
33 r#"
34 {}
35 JOIN album_tracks ON tracks.xata_id = album_tracks.track_id
36 WHERE album_tracks.album_id = $1
37 AND user_uploads.user_id = $2
38 ORDER BY tracks.disc_number ASC NULLS FIRST, tracks.track_number ASC NULLS FIRST
39 "#,
40 TRACK_SELECT
41 ))
42 .bind(album_id)
43 .bind(user_id)
44 .fetch_all(pool)
45 .await?;
46
47 Ok(rows)
48}
49
50pub async fn get_track_by_id(
51 pool: &Pool<Postgres>,
52 track_id: &str,
53 user_id: &str,
54) -> Result<Option<TrackWithUpload>, Error> {
55 let row: Option<TrackWithUpload> = sqlx::query_as(&format!(
56 r#"
57 {}
58 WHERE tracks.xata_id = $1
59 AND user_uploads.user_id = $2
60 "#,
61 TRACK_SELECT
62 ))
63 .bind(track_id)
64 .bind(user_id)
65 .fetch_optional(pool)
66 .await?;
67
68 Ok(row)
69}
70
71pub async fn get_random_songs(
72 pool: &Pool<Postgres>,
73 user_id: &str,
74 count: i64,
75 genre: Option<&str>,
76 from_year: Option<i32>,
77 to_year: Option<i32>,
78) -> Result<Vec<TrackWithUpload>, Error> {
79 let mut filters = vec!["user_uploads.user_id = $1".to_string()];
80
81 if let Some(g) = genre {
82 filters.push(format!(
83 "LOWER(tracks.genre) = LOWER('{}')",
84 g.replace('\'', "''")
85 ));
86 }
87 if let Some(from) = from_year {
88 if let Some(to) = to_year {
89 filters.push(format!(
90 "EXTRACT(YEAR FROM tracks.xata_createdat) BETWEEN {} AND {}",
91 from.min(to),
92 from.max(to)
93 ));
94 }
95 }
96
97 let where_clause = filters.join(" AND ");
98
99 let sql = format!(
100 r#"
101 {}
102 WHERE {}
103 ORDER BY RANDOM()
104 LIMIT $2
105 "#,
106 TRACK_SELECT, where_clause
107 );
108
109 let rows: Vec<TrackWithUpload> = sqlx::query_as(&sql)
110 .bind(user_id)
111 .bind(count)
112 .fetch_all(pool)
113 .await?;
114
115 Ok(rows)
116}
117
118pub async fn search_tracks(
119 pool: &Pool<Postgres>,
120 user_id: &str,
121 query: &str,
122 count: i64,
123 offset: i64,
124) -> Result<Vec<TrackWithUpload>, Error> {
125 let pattern = format!("%{}%", query);
126 let rows: Vec<TrackWithUpload> = sqlx::query_as(&format!(
127 r#"
128 {}
129 WHERE user_uploads.user_id = $1
130 AND LOWER(tracks.title) LIKE LOWER($2)
131 ORDER BY tracks.title ASC
132 LIMIT $3 OFFSET $4
133 "#,
134 TRACK_SELECT
135 ))
136 .bind(user_id)
137 .bind(&pattern)
138 .bind(count)
139 .bind(offset)
140 .fetch_all(pool)
141 .await?;
142
143 Ok(rows)
144}
145
146pub async fn get_album_art_by_track_id(
147 pool: &Pool<Postgres>,
148 track_id: &str,
149) -> Result<Option<String>, Error> {
150 let row: Option<(Option<String>,)> =
151 sqlx::query_as(r#"SELECT album_art FROM tracks WHERE xata_id = $1"#)
152 .bind(track_id)
153 .fetch_optional(pool)
154 .await?;
155
156 Ok(row.and_then(|(art,)| art))
157}
158
159pub async fn get_album_id_for_track(
160 pool: &Pool<Postgres>,
161 track_id: &str,
162) -> Result<Option<String>, Error> {
163 let row: Option<(String,)> =
164 sqlx::query_as(r#"SELECT album_id FROM album_tracks WHERE track_id = $1 LIMIT 1"#)
165 .bind(track_id)
166 .fetch_optional(pool)
167 .await?;
168
169 Ok(row.map(|(id,)| id))
170}
171
172pub async fn get_artist_id_for_track(
173 pool: &Pool<Postgres>,
174 track_id: &str,
175) -> Result<Option<String>, Error> {
176 let row: Option<(String,)> =
177 sqlx::query_as(r#"SELECT artist_id FROM artist_tracks WHERE track_id = $1 LIMIT 1"#)
178 .bind(track_id)
179 .fetch_optional(pool)
180 .await?;
181
182 Ok(row.map(|(id,)| id))
183}