use crate::errors::Error; use postgres::{Client, NoTls}; use std::sync::{Arc, Mutex}; lazy_static! { static ref CLIENT: Arc> = Arc::new(Mutex::new( Client::connect("host=track_db user=postgres password=example", NoTls) .expect("failed to connect to database") )); } pub 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_raw AS ut WHERE ut.user_id = user_id )::decimal AS score FROM user_track_raw ); "#, )?; Ok(()) } use rspotify::spotify::model::track::FullTrack; pub fn insert_track(user_id: i32, track: FullTrack, weight: i32) -> Result<(), Error> { let mut client = 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.execute( " INSERT INTO user_track_raw (track_id, user_id, count) VALUES ($1, $2, $3) ON CONFLICT ON CONSTRAINT track_user_pkey DO NOTHING; ", &[&track_id, &user_id, &0], )?; client.execute( "UPDATE user_track SET count = count + $3 WHERE track_id = $1 AND user_id = $2;", &[&track_id, &user_id, &weight], )?; Ok(()) } pub fn insert_user(name: &str) -> Result { let mut client = CLIENT.lock()?; client.execute( "INSERT INTO suser (user_name) VALUES ($1) ON CONFLICT (user_name) DO NOTHING;", &[&name], )?; let db_user_id = get_uid(name, &mut *client)?; client.execute("DELETE FROM user_track WHERE user_id = $1;", &[&db_user_id])?; Ok(db_user_id) } fn get_uid(name: &str, client: &mut postgres::Client) -> Result { let x: i32 = client .query_one("SELECT user_id FROM suser where user_name = $1;", &[&name])? .get(0); Ok(x) } pub fn match_users(name1: String, name2: String) -> Result { let mut client = 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) } pub fn get_users() -> Result { let mut client = 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) }