summaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
authorDennis Kobert <dennis@kobert.dev>2020-03-02 16:21:26 +0000
committerDennis Kobert <dennis@kobert.dev>2020-03-02 16:21:26 +0000
commit1a95bd4e610770bed1571de6ddf572d5590561d9 (patch)
treebfd5b41bf0ee9ec9b49a2b31e212185ddb888aaa /src
parent17a305d420c4fd88fcaabb479164e6095303f7fc (diff)
Rework table structure
add sql queries
Diffstat (limited to 'src')
-rw-r--r--src/main.rs21
1 files changed, 15 insertions, 6 deletions
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(())
}