Use postgres database

2 files changed, 111 insertions, 97 deletions

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<Mutex<Client>> = 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::<ApiError>() {
@@ -85,6 +140,7 @@ pub fn get_tracks(name: String, url: String) -> String {
     }
     name
 }
+
 #[get("/token/<name>")]
 pub fn token(name: String) -> Result<Redirect, status::Custom<String>> {
     let state = rspotify::spotify::util::generate_random_string(16);