diff options
Diffstat (limited to 'src/database.rs')
| -rw-r--r-- | src/database.rs | 164 |
1 files changed, 164 insertions, 0 deletions
diff --git a/src/database.rs b/src/database.rs new file mode 100644 index 0000000..326c1e0 --- /dev/null +++ b/src/database.rs @@ -0,0 +1,164 @@ +use crate::errors::Error; +use postgres::{Client, NoTls}; +use std::sync::{Arc, Mutex}; + +lazy_static! { + static ref CLIENT: Arc<Mutex<Client>> = Arc::new(Mutex::new(Client::connect( + "host=track_db user=postgres password=example", + NoTls + )?)); +} + +fn initialize_db() -> Result<(), Error> { + let mut client = CLIENT.lock()?; + client.batch_execute( + r#" + CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS suser ( + user_id SERIAL PRIMARY KEY, + user_name TEXT NOT NULL UNIQUE + ); + CREATE TABLE IF NOT EXISTS user_track_raw ( + 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) + ); + + CREATE OR REPLACE VIEW user_track AS ( + SELECT *, "count" / ( + SELECT SUM("count") + FROM user_track ut + WHERE ut.user_id = user_id + )::decimal AS score + FROM user_track + ); + "#, + )?; + Ok(()) +} + +use rspotify::spotify::model::track::FullTrack; +fn insert_track(user_id: i32, track: FullTrack) -> Result<(), Error> { + let mut client = crate::CLIENT.lock()?; + + if track.id.is_none() { + println!("{:#?}", track); + return Err("failed to load get track information".into()); + } + print!(" {} ", track.id.clone()?); + client.execute( + "INSERT INTO track (track_code, name, artist, popularity) + VALUES ($1, $2, $3, $4) + ON CONFLICT DO NOTHING + ", + &[ + &(track.id.clone()?), + &track.name, + &track.artists[0].name, + &(track.popularity as i32), + ], + )?; + let track_id: i32 = client.query( + "SELECT track_id FROM track where track_code = $1;", + &[&(track.id?)], + )?[0] + .get(0); + println!("uid: {} tid: {}", user_id, track_id); + client.batch_execute( + " + INSERT INTO user_track_raw (track_id, user_id, count) + VALUES ($1, $2, $3) + ON CONFLICT + ON CONSTRAINT track_user_pkey + DO NOTHING; + UPDATE user_track SET count = count + 1 WHERE track_id = $1 AND user_id = $2; + ", + &[&track_id, &user_id, &0], + )?; + Ok(()) +} + +fn insert_user(name: &str) -> Result<(i32, String), Error> { + let mut client = crate::CLIENT.lock()?; + client.batch_execute( + " + INSERT INTO suser (user_name) VALUES ($1) ON CONFLICT (user_name) DO NOTHING; + DELETE FROM user_track WHERE user_name = $1; + ", + &[&user_id], + )?; + let user_id: i32 = client + .query_one( + "SELECT user_id FROM suser WHERE user_name = $1;", + &[&user_id], + )? + .get(0); + //reset user_track relation + Ok((user_id, name)) +} + +fn get_uid(name: &str, client: &mut postgres::Client) -> Result<i32, Error> { + let x: i32 = client + .query_one("SELECT user_id FROM suser where user_name = $1;", &[&name])? + .get(0); + Ok(x) +} + +lazy_static! { + static ref CACHE: Arc<Mutex<HashMap<String, SpotifyOAuth>>> = + Arc::new(Mutex::new(HashMap::new())); +} + +pub fn match_users(name1: String, name2: String) -> Result<String, Error> { + let mut client = crate::CLIENT.lock()?; + let mut songs = String::new(); + for row in client.query( + " + WITH users AS ( + SELECT * + FROM ( VALUES + + ($1), ($2) + + ) AS _ (user_id) + ) + 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) + ; + ", + &[&name1.as_str(), &name2.as_str()], + )? { + let name: String = row.get(1); + let artist: String = row.get(2); + songs = format!("{}{} by {}\n", songs, name, artist); + } + Ok(songs) +} + +#[get("/user")] +pub fn get_users() -> Result<String, Error> { + let mut client = crate::CLIENT.lock()?; + let mut users = String::new(); + for row in client.query("SELECT user_name FROM suser", &[])? { + let user: String = row.get(0); + users = format!("{}{}\n", users, user); + } + Ok(users) +} |