From 1a95bd4e610770bed1571de6ddf572d5590561d9 Mon Sep 17 00:00:00 2001
From: Dennis Kobert <dennis@kobert.dev>
Date: Mon, 2 Mar 2020 16:21:26 +0000
Subject: Rework table structure

add sql queries
---
 sql/si.sql       | 37 +++++++++++++++++++++++++++++++++++++
 sql/weighted.sql |  8 ++++++++
 src/main.rs      | 21 +++++++++++++++------
 3 files changed, 60 insertions(+), 6 deletions(-)
 create mode 100644 sql/si.sql
 create mode 100644 sql/weighted.sql

diff --git a/sql/si.sql b/sql/si.sql
new file mode 100644
index 0000000..0b2cb22
--- /dev/null
+++ b/sql/si.sql
@@ -0,0 +1,37 @@
+/*
+SELECT track_id, SUM(score)/2
+FROM user_track
+WHERE user_id IN (
+	SELECT user_id
+	FROM suser
+	WHERE user_name = 'd-kobert'
+	OR user_name = 'thilo'
+)
+GROUP BY track_id
+HAVING COUNT(track_id) = 2
+ORDER BY SUM(score) DESC;
+*/
+
+-- alternative
+WITH users AS (
+	SELECT *
+	FROM ( VALUES
+
+		('d-kobert'), ('thilo')
+
+	) AS _ (user_name)
+)
+--SELECT track_id, SUM(score) / (SELECT COUNT(*) FROM users) AS score
+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)
+;
diff --git a/sql/weighted.sql b/sql/weighted.sql
new file mode 100644
index 0000000..dfa23c1
--- /dev/null
+++ b/sql/weighted.sql
@@ -0,0 +1,8 @@
+CREATE OR REPLACE VIEW user_track_weighted AS (
+    SELECT *, "count" / (
+        SELECT SUM("count")
+        FROM user_track ut
+        WHERE ut.user_id = user_id
+    )::decimal AS score
+    FROM user_track
+);
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