From 1a95bd4e610770bed1571de6ddf572d5590561d9 Mon Sep 17 00:00:00 2001 From: Dennis Kobert Date: Mon, 2 Mar 2020 16:21:26 +0000 Subject: Rework table structure add sql queries --- src/main.rs | 21 +++++++++++++++------ 1 file changed, 15 insertions(+), 6 deletions(-) (limited to 'src') diff --git a/src/main.rs b/src/main.rs index 99ad195..d1ebe7b 100644 --- a/src/main.rs +++ b/src/main.rs @@ -32,27 +32,36 @@ fn main() { fn initialize_db() -> Result<(), postgres::Error> { let mut client = CLIENT.lock().unwrap(); - let _ = client.batch_execute("DROP TABLE user_track; DROP TABLE suser; DROP TABLE track;"); + //let _ = client.batch_execute("DROP TABLE user_track; DROP TABLE suser; DROP TABLE track;"); client.batch_execute( - " - CREATE TABLE track ( + r#" + CREATE TABLE track IF NOT EXISTS ( 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 ( + CREATE TABLE suser IF NOT EXISTS ( user_id SERIAL PRIMARY KEY, user_name TEXT NOT NULL UNIQUE ); - CREATE TABLE user_track ( + CREATE TABLE user_track_raw IF NOT EXISTS ( 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(()) } -- cgit v1.2.3-54-g00ecf