summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorDennis Kobert <dennis@kobert.dev>2020-03-02 17:30:47 +0100
committerDennis Kobert <dennis@kobert.dev>2020-03-02 17:30:47 +0100
commitb839a88544aab96234caac3374ad34a9b364d85a (patch)
treeb85f9b94446f443614620d47e11fbb72652728f7
parent1a95bd4e610770bed1571de6ddf572d5590561d9 (diff)
Move query logic to database
-rw-r--r--src/serve.rs375
1 files changed, 190 insertions, 185 deletions
diff --git a/src/serve.rs b/src/serve.rs
index edd0826..8aaf1ec 100644
--- a/src/serve.rs
+++ b/src/serve.rs
@@ -8,87 +8,87 @@ use std::collections::HashMap;
use std::sync::{Arc, Mutex};
lazy_static! {
- static ref CACHE: Arc<Mutex<HashMap<String, SpotifyOAuth>>> =
- Arc::new(Mutex::new(HashMap::new()));
+ static ref CACHE: Arc<Mutex<HashMap<String, SpotifyOAuth>>> =
+ Arc::new(Mutex::new(HashMap::new()));
}
#[get("/callback/<name>/<url>")]
pub fn get_tracks(name: String, url: String) -> Result<(), status::Custom<String>> {
- let mut guard = CACHE.lock().or(Err(status::Custom(
- Status::InternalServerError,
- String::from("failed to lock cache mutex"),
- )))?;
- let mut oauth = guard.remove(&name).ok_or(status::Custom(
- Status::NotFound,
- String::from("uuid not found in cache"),
- ))?;
- println!("auth: {:?} url: {}", oauth, url);
- let token_info = process_token(&mut oauth, &mut ("?code=".to_owned() + url.as_ref()));
- let client_credential = SpotifyClientCredentials::default()
- .token_info(token_info.unwrap())
- .build();
+ let mut guard = CACHE.lock().or(Err(status::Custom(
+ Status::InternalServerError,
+ String::from("failed to lock cache mutex"),
+ )))?;
+ let mut oauth = guard.remove(&name).ok_or(status::Custom(
+ Status::NotFound,
+ String::from("uuid not found in cache"),
+ ))?;
+ println!("auth: {:?} url: {}", oauth, url);
+ let token_info = process_token(&mut oauth, &mut ("?code=".to_owned() + url.as_ref()));
+ let client_credential = SpotifyClientCredentials::default()
+ .token_info(token_info.unwrap())
+ .build();
- let spotify = Spotify::default()
- .client_credentials_manager(client_credential)
- .build();
- let user_id = spotify.current_user().unwrap().id;
- let mut client = crate::CLIENT.lock().or(Err(status::Custom(
- Status::InternalServerError,
- String::from("failed to lock cache mutex"),
- )))?;
- client
- .execute(
- "INSERT INTO suser (user_name) VALUES ($1) ON CONFLICT (user_name) DO NOTHING;",
- &[&user_id],
- )
- .unwrap();
- let uid = get_uid(user_id.as_ref(), &mut client).ok_or(status::Custom(
- Status::NotFound,
- format!("username {} not found", user_id),
- ))?;
- client
- .execute("DELETE FROM user_track WHERE user_id = $1;", &[&uid])
- .unwrap();
- drop(client);
- let chunk_size = 50;
- let mut playlist_index = 0;
- loop {
- match spotify.user_playlists(user_id.as_ref(), Some(chunk_size), Some(playlist_index)) {
- Ok(playlists) => {
- playlist_index += chunk_size;
- if playlists.items.is_empty() {
- break;
- }
- for playlist in playlists.items {
- println!("playlist: {:?}", playlist.name);
- let mut track_index = 0;
+ let spotify = Spotify::default()
+ .client_credentials_manager(client_credential)
+ .build();
+ let user_id = spotify.current_user().unwrap().id;
+ let mut client = crate::CLIENT.lock().or(Err(status::Custom(
+ Status::InternalServerError,
+ String::from("failed to lock cache mutex"),
+ )))?;
+ client
+ .execute(
+ "INSERT INTO suser (user_name) VALUES ($1) ON CONFLICT (user_name) DO NOTHING;",
+ &[&user_id],
+ )
+ .unwrap();
+ let uid = get_uid(user_id.as_ref(), &mut client).ok_or(status::Custom(
+ Status::NotFound,
+ format!("username {} not found", user_id),
+ ))?;
+ client
+ .execute("DELETE FROM user_track WHERE user_id = $1;", &[&uid])
+ .unwrap();
+ drop(client);
+ let chunk_size = 50;
+ let mut playlist_index = 0;
+ loop {
+ match spotify.user_playlists(user_id.as_ref(), Some(chunk_size), Some(playlist_index)) {
+ Ok(playlists) => {
+ playlist_index += chunk_size;
+ if playlists.items.is_empty() {
+ break;
+ }
+ for playlist in playlists.items {
+ println!("playlist: {:?}", playlist.name);
+ let mut track_index = 0;
- loop {
- match spotify.user_playlist_tracks(
- user_id.as_ref(),
- &playlist.id,
- None,
- Some(chunk_size),
- Some(track_index),
- None,
- ) {
- Ok(tracks) => {
- track_index += chunk_size;
- if tracks.items.is_empty() {
- break;
- }
- for track in tracks.items {
- //println!("{:?}", track.track.name);
- let mut client = crate::CLIENT.lock().or(Err(
- status::Custom(Status::InternalServerError, String::new()),
- ))?;
+ loop {
+ match spotify.user_playlist_tracks(
+ user_id.as_ref(),
+ &playlist.id,
+ None,
+ Some(chunk_size),
+ Some(track_index),
+ None,
+ ) {
+ Ok(tracks) => {
+ track_index += chunk_size;
+ if tracks.items.is_empty() {
+ break;
+ }
+ for track in tracks.items {
+ //println!("{:?}", track.track.name);
+ let mut client = crate::CLIENT.lock().or(Err(
+ status::Custom(Status::InternalServerError, String::new()),
+ ))?;
- if track.track.id.is_none() {
- println!("{:#?}", track);
- continue;
- }
- print!(" {} ", track.track.id.clone().unwrap());
- client
+ if track.track.id.is_none() {
+ println!("{:#?}", track);
+ continue;
+ }
+ print!(" {} ", track.track.id.clone().unwrap());
+ client
.execute(
"INSERT INTO track (track_code, name, artist, popularity)
VALUES ($1, $2, $3, $4)
@@ -97,145 +97,150 @@ pub fn get_tracks(name: String, url: String) -> Result<(), status::Custom<String
&[&(track.track.id.clone().unwrap()), &track.track.name, &track.track.artists[0].name, &(track.track.popularity as i32)],
)
.unwrap();
- let tid: i32 = client
- .query(
- "SELECT track_id FROM track where track_code = $1;",
- &[&(track.track.id.clone().unwrap())],
- )
- .unwrap()[0]
- .get(0);
- println!("uid: {} tid: {}", uid, tid);
- client
- .execute(
- "INSERT INTO user_track (track_id, user_id, count)
+ let tid: i32 = client
+ .query(
+ "SELECT track_id FROM track where track_code = $1;",
+ &[&(track.track.id.clone().unwrap())],
+ )
+ .unwrap()[0]
+ .get(0);
+ println!("uid: {} tid: {}", uid, tid);
+ client
+ .execute(
+ "INSERT INTO user_track (track_id, user_id, count)
VALUES ($1, $2, $3)
ON CONFLICT
ON CONSTRAINT track_user_pkey
DO NOTHING;",
- &[&tid, &uid, &0],
- )
- .unwrap();
- client
+ &[&tid, &uid, &0],
+ )
+ .unwrap();
+ client
.execute(
"UPDATE user_track SET count = count + 1 WHERE track_id = $1 AND user_id = $2;",
&[&tid, &uid],
)
.unwrap();
- }
- }
- Err(e) => match e.downcast::<ApiError>() {
- Ok(ApiError::RateLimited(x)) => std::thread::sleep(
- std::time::Duration::from_secs(x.unwrap_or(5) as u64),
- ),
+ }
+ }
+ Err(e) => match e.downcast::<ApiError>() {
+ Ok(ApiError::RateLimited(x)) => std::thread::sleep(
+ std::time::Duration::from_secs(x.unwrap_or(5) as u64),
+ ),
- cause => {
- println!("Error: {:?}", cause);
- break;
- }
- },
- }
- }
- }
- }
- Err(e) => match e.downcast::<ApiError>() {
- Ok(ApiError::RateLimited(x)) => {
- std::thread::sleep(std::time::Duration::from_secs(x.unwrap_or(5) as u64))
- }
+ cause => {
+ println!("Error: {:?}", cause);
+ break;
+ }
+ },
+ }
+ }
+ }
+ }
+ Err(e) => match e.downcast::<ApiError>() {
+ Ok(ApiError::RateLimited(x)) => {
+ std::thread::sleep(std::time::Duration::from_secs(x.unwrap_or(5) as u64))
+ }
- cause => {
- println!("Error: {:?}", cause);
- break;
- }
- },
- }
- }
- Ok(())
+ cause => {
+ println!("Error: {:?}", cause);
+ break;
+ }
+ },
+ }
+ }
+ Ok(())
}
#[get("/token/<name>")]
pub fn token(name: String) -> Result<Redirect, status::Custom<String>> {
- let state = rspotify::spotify::util::generate_random_string(16);
- let oauth = SpotifyOAuth::default();
- //let callback = oauth.redirect_uri.clone();
- let oauth = oauth
+ let state = rspotify::spotify::util::generate_random_string(16);
+ let oauth = SpotifyOAuth::default();
+ //let callback = oauth.redirect_uri.clone();
+ let oauth = oauth
.scope("playlist-read-private, playlist-read-collaborative, user-read-private, user-follow-read, user-library-read")
//.redirect_uri(format!("{}/{}", callback, &state).as_ref())
.build();
- let auth_url = oauth.get_authorize_url(Some(&state), None);
- match CACHE.lock() {
- Ok(mut guard) => {
- guard.insert(name, oauth);
- Ok(Redirect::to(auth_url))
- }
- Err(_) => Err(status::Custom(
- Status::ImATeapot,
- "Internal Server Error".to_owned(),
- )),
- }
+ let auth_url = oauth.get_authorize_url(Some(&state), None);
+ match CACHE.lock() {
+ Ok(mut guard) => {
+ guard.insert(name, oauth);
+ Ok(Redirect::to(auth_url))
+ }
+ Err(_) => Err(status::Custom(
+ Status::ImATeapot,
+ "Internal Server Error".to_owned(),
+ )),
+ }
}
fn get_uid(name: &str, client: &mut postgres::Client) -> Option<i32> {
- match client.query("SELECT user_id FROM suser where user_name = $1;", &[&name]) {
- Ok(rows) => match rows.len() {
- 0 => None,
- x => {
- let x: i32 = rows[0].get(0);
- Some(x)
- }
- },
- _ => None,
- }
+ match client.query("SELECT user_id FROM suser where user_name = $1;", &[&name]) {
+ Ok(rows) => match rows.len() {
+ 0 => None,
+ x => {
+ let x: i32 = rows[0].get(0);
+ Some(x)
+ }
+ },
+ _ => None,
+ }
}
#[get("/match/<name1>/<name2>")]
pub fn match_users(name1: String, name2: String) -> Result<String, status::NotFound<String>> {
- let mut client = crate::CLIENT.lock().unwrap();
- let uid1 = get_uid(name1.as_ref(), &mut client)
- .ok_or(status::NotFound(format!("username {} not found", name1)))?;
- let uid2 = get_uid(name2.as_ref(), &mut client)
- .ok_or(status::NotFound(format!("username {} not found", name2)))?;
- let mut songs = String::new();
- for row in client
- .query(
- "
- SELECT track_id, SUM(score)/2
- FROM user_track
- WHERE user_id IN (
- SELECT user_id
- FROM suser
- WHERE user_name = $1
- OR user_name = $2
- )
- GROUP BY track_id
- HAVING COUNT(track_id) = 2
- ORDER BY SUM(score) DESC;
+ let mut client = crate::CLIENT.lock().unwrap();
+ let uid1 = get_uid(name1.as_ref(), &mut client)
+ .ok_or(status::NotFound(format!("username {} not found", name1)))?;
+ let uid2 = get_uid(name2.as_ref(), &mut client)
+ .ok_or(status::NotFound(format!("username {} not found", name2)))?;
+ let mut songs = String::new();
+ for row in client
+ .query(
+ "
+ WITH users AS (
+ SELECT *
+ FROM ( VALUES
+
+ ($1), ($2)
+
+ ) AS _ (user_name)
+ )
+ --SELECT track_id, SUM(score) / (SELECT COUNT(*) FROM users) AS score
+ SELECT track_id, name, artist
+ FROM track
+ JOIN (
+ SELECT track_id
+ FROM user_track
+ JOIN suser USING (user_id)
+ JOIN track USING (track_id)
+ WHERE suser.user_name IN (SELECT * FROM users)
+ GROUP BY track_id
+ HAVING COUNT(track_id) = (SELECT COUNT(*) FROM users)
+ ORDER BY SUM(score) DESC
+ ) AS _ USING (track_id)
+ ;
",
- &[&uid1, &uid2],
- )
- .unwrap()
- {
- let tid: i32 = row.get(0);
- let song = &client
- .query(
- "SELECT name, artist FROM track where track_id = $1",
- &[&tid],
- )
- .unwrap()[0];
- println!("{:?}", song.columns());
- let name: String = song.get(0);
- let artist: String = song.get(1);
- songs = format!("{}{} by {}\n", songs, name, artist);
- }
- Ok(songs)
+ &[&uid1, &uid2],
+ )
+ .unwrap()
+ {
+ //let song = row.get(0);
+ println!("{:?}", song.columns());
+ let name: String = song.get(1);
+ let artist: String = song.get(2);
+ songs = format!("{}{} by {}\n", songs, name, artist);
+ }
+ Ok(songs)
}
#[get("/user")]
pub fn get_users() -> Result<String, status::NotFound<String>> {
- let mut client = crate::CLIENT.lock().unwrap();
- let mut users = String::new();
- for row in client.query("SELECT user_name FROM suser", &[]).unwrap() {
- let user: String = row.get(0);
- users = format!("{}{}\n", users, user);
- }
- Ok(users)
+ let mut client = crate::CLIENT.lock().unwrap();
+ let mut users = String::new();
+ for row in client.query("SELECT user_name FROM suser", &[]).unwrap() {
+ let user: String = row.get(0);
+ users = format!("{}{}\n", users, user);
+ }
+ Ok(users)
}