From 9ba634c68ed46199ca12f8589b2958d20294f27c Mon Sep 17 00:00:00 2001 From: Dennis Kobert Date: Tue, 11 Feb 2020 16:19:57 +0000 Subject: Use postgres database --- Cargo.toml | 5 +- Rocket.toml | 4 +- dist/button.html | 16 ++++++ src/main.rs | 150 ++++++++++++++++++++----------------------------------- src/serve.rs | 58 ++++++++++++++++++++- 5 files changed, 130 insertions(+), 103 deletions(-) create mode 100644 dist/button.html diff --git a/Cargo.toml b/Cargo.toml index 9ebe69f..b2e6b41 100644 --- a/Cargo.toml +++ b/Cargo.toml @@ -10,8 +10,5 @@ edition = "2018" rspotify = "0.7" rocket = "0.4" lazy_static = { version = "1.4"} -#noria = { git = "https://github.com/mit-pdos/noria" } -noria = { path = "noria/noria" } -noria-server = { path = "noria/noria-server" } futures = "0.3" -tokio = { version = "0.2", features = ["full"] } +postgres = "0.17" diff --git a/Rocket.toml b/Rocket.toml index 9037d62..f4b1577 100644 --- a/Rocket.toml +++ b/Rocket.toml @@ -1,9 +1,9 @@ [development] address = "localhost" -port = 8008 +port = 8000 #workers = [number of cpus * 2] keep_alive = 5 -log = "debug" +log = "normal" #secret_key = [randomly generated at launch] limits = { forms = 32768 } diff --git a/dist/button.html b/dist/button.html new file mode 100644 index 0000000..da99833 --- /dev/null +++ b/dist/button.html @@ -0,0 +1,16 @@ + + + +

Spotify Intersect

+ + + + diff --git a/src/main.rs b/src/main.rs index bfd53b8..c6b7d86 100644 --- a/src/main.rs +++ b/src/main.rs @@ -2,112 +2,70 @@ #[macro_use] extern crate rocket; -use rspotify::spotify::client::Spotify; -use rspotify::spotify::oauth2::{SpotifyClientCredentials, SpotifyOAuth}; -use rspotify::spotify::util::get_token; - +#[macro_use] +extern crate lazy_static; mod serve; +use postgres::{Client, NoTls}; +use std::sync::{Arc, Mutex}; -//#[tokio::main] -fn main() { - // Set client_id and client_secret in .env file or - // export CLIENT_ID="your client_id" - // export CLIENT_SECRET="secret" - // export REDIRECT_URI=your-direct-uri - - // Or set client_id, client_secret,redirect_uri explictly - // let oauth = SpotifyOAuth::default() - // .client_id("this-is-my-client-id") - // .client_secret("this-is-my-client-secret") - // .redirect_uri("http://localhost:8888/callback") - // .build(); - // - // looking up article 42 should yield the article we inserted with a vote count of 1 - let mut rt = tokio::runtime::Builder::new() - .enable_all() - .threaded_scheduler() - .thread_name("voter") - .build() - .unwrap(); - - let mut db = rt - .block_on(noria::ControllerHandle::from_zk("127.0.0.1:2181")) - .unwrap(); - println!("test"); - rt.block_on(db.install_recipe( - " - CREATE TABLE Article (aid int, title varchar(255), url text, PRIMARY KEY(aid)); - CREATE TABLE Vote (aid int, uid int); -", - )) - .unwrap(); - println!("staring_init"); - let init = init(); +lazy_static! { + static ref CLIENT: Arc> = Arc::new(Mutex::new( + Client::connect("host=localhost user=postgres password=example", NoTls).unwrap() + )); +} +fn main() { + //initialize_db().unwrap(); + //setup_db().unwrap(); + println!("connected with db"); rocket::ignite() .mount("/", routes![serve::token, serve::get_tracks]) .launch(); - rt.block_on(init); } -async fn init() { - let mut db = noria::ControllerHandle::from_zk("127.0.0.1:2181") - .await - .unwrap(); - - //db.ready(); - //println!("test {:?}", db.url()); - db.install_recipe( - " - CREATE TABLE Article (aid int, title varchar(255), url text, PRIMARY KEY(aid)); - CREATE TABLE Vote (aid int, uid int); -", - ) - .await - .unwrap(); - println!("test"); - // we can then get handles that let us insert into the new tables - let mut article = db.table("Article").await.unwrap(); - let mut vote = db.table("Vote").await.unwrap(); +fn setup_db() -> Result<(), postgres::Error> { + let name = "Ferris"; + let data = None::<&[u8]>; + let mut client = CLIENT.lock().unwrap(); + client.execute( + "INSERT INTO person (name, data) VALUES ($1, $2)", + &[&name, &data], + )?; - // let's make a new article - let aid = 42; - let title = "I love Soup"; - let url = "https://pdos.csail.mit.edu"; - article - .insert(vec![aid.into(), title.into(), url.into()]) - .await - .unwrap(); + for row in client.query("SELECT id, name, data FROM person", &[])? { + let id: i32 = row.get(0); + let name: &str = row.get(1); + let data: Option<&[u8]> = row.get(2); - // and then vote for it - vote.insert(vec![aid.into(), 1.into()]).await.unwrap(); - println!("test"); + println!("found person: {} {} {:?}", id, name, data); + } + Ok(()) +} - // we can also declare views that we want want to query - db.extend_recipe( +fn initialize_db() -> Result<(), postgres::Error> { + let mut client = CLIENT.lock().unwrap(); + client.batch_execute( " - VoteCount: \ - SELECT Vote.aid, COUNT(uid) AS votes \ - FROM Vote GROUP BY Vote.aid; - QUERY ArticleWithVoteCount: \ - SELECT Article.aid, title, url, VoteCount.votes AS votes \ - FROM Article LEFT JOIN VoteCount ON (Article.aid = VoteCount.aid) \ - WHERE Article.aid = ?;", - ) - .await - .unwrap(); - - // and then get handles that let us execute those queries to fetch their results - let mut awvc = db.view("ArticleWithVoteCount").await.unwrap(); - - assert_eq!( - awvc.lookup(&[aid.into()], true).await.unwrap(), - vec![vec![ - noria::DataType::from(aid), - title.into(), - url.into(), - 1.into() - ]] - ); - println!("init done"); + DROP TABLE user_track; DROP TABLE suser; DROP TABLE track; + CREATE TABLE track ( + track_id SERIAL PRIMARY KEY, + track_code TEXT NOT NULL UNIQUE, + name TEXT NOT NULL, + artist TEXT NOT NULL, + popularity int DEFAULT 50 + ); + CREATE TABLE suser ( + user_id SERIAL PRIMARY KEY, + user_name TEXT NOT NULL UNIQUE + ); + CREATE TABLE user_track ( + track_id int REFERENCES track (track_id) ON UPDATE CASCADE ON DELETE CASCADE, + user_id int REFERENCES suser (user_id) ON UPDATE CASCADE ON DELETE CASCADE, + count int NOT NULL DEFAULT 1, + CONSTRAINT track_user_pkey PRIMARY KEY (track_id, user_id) + ); + ", + )?; + Ok(()) } + diff --git a/src/serve.rs b/src/serve.rs index 0dff5da..5696f12 100644 --- a/src/serve.rs +++ b/src/serve.rs @@ -26,6 +26,21 @@ pub fn get_tracks(name: String, url: String) -> String { .client_credentials_manager(client_credential) .build(); let user_id = spotify.current_user().unwrap().id; + let mut client = crate::CLIENT.lock().unwrap(); + client + .execute( + "INSERT INTO suser (user_name) VALUES ($1) ON CONFLICT (user_name) DO NOTHING;", + &[&user_id], + ) + .unwrap(); + let uid: i32 = client + .query( + "SELECT user_id FROM suser where user_name = $1;", + &[&user_id], + ) + .unwrap()[0] + .get(0); + drop(client); let chunk_size = 50; let mut playlist_index = 0; loop { @@ -54,7 +69,47 @@ pub fn get_tracks(name: String, url: String) -> String { break; } for track in tracks.items { - println!("{:?}", track.track.name); + //println!("{:?}", track.track.name); + let mut client = crate::CLIENT.lock().unwrap(); + + 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) + ON CONFLICT DO NOTHING + ", + &[&(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) + VALUES ($1, $2, $3) + ON CONFLICT + ON CONSTRAINT track_user_pkey + DO UPDATE;", + &[&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::() { @@ -85,6 +140,7 @@ pub fn get_tracks(name: String, url: String) -> String { } name } + #[get("/token/")] pub fn token(name: String) -> Result> { let state = rspotify::spotify::util::generate_random_string(16); -- cgit v1.2.3-54-g00ecf