From a7d56ba0f54273920743109fe1a6541030f3c003 Mon Sep 17 00:00:00 2001
From: Ilion Beyst <ilion.beyst@gmail.com>
Date: Mon, 9 May 2022 19:41:33 +0200
Subject: add ratings table

---
 .../2022-04-28-171349_create_rating/down.sql       |  2 ++
 .../2022-04-28-171349_create_rating/up.sql         |  7 ++++++
 planetwars-server/src/db/mod.rs                    |  1 +
 planetwars-server/src/db/ratings.rs                | 27 ++++++++++++++++++++++
 planetwars-server/src/schema.rs                    | 21 ++++++++++++++++-
 5 files changed, 57 insertions(+), 1 deletion(-)
 create mode 100644 planetwars-server/migrations/2022-04-28-171349_create_rating/down.sql
 create mode 100644 planetwars-server/migrations/2022-04-28-171349_create_rating/up.sql
 create mode 100644 planetwars-server/src/db/ratings.rs

diff --git a/planetwars-server/migrations/2022-04-28-171349_create_rating/down.sql b/planetwars-server/migrations/2022-04-28-171349_create_rating/down.sql
new file mode 100644
index 0000000..8267412
--- /dev/null
+++ b/planetwars-server/migrations/2022-04-28-171349_create_rating/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+drop table ratings;
\ No newline at end of file
diff --git a/planetwars-server/migrations/2022-04-28-171349_create_rating/up.sql b/planetwars-server/migrations/2022-04-28-171349_create_rating/up.sql
new file mode 100644
index 0000000..39fe6fe
--- /dev/null
+++ b/planetwars-server/migrations/2022-04-28-171349_create_rating/up.sql
@@ -0,0 +1,7 @@
+-- Your SQL goes here
+-- this table could later be expanded to include more information,
+-- such as rating state (eg. number of matches played) or scope (eg. map)
+create table ratings (
+    bot_id integer PRIMARY KEY REFERENCES bots(id),
+    rating float NOT NULL
+)
\ No newline at end of file
diff --git a/planetwars-server/src/db/mod.rs b/planetwars-server/src/db/mod.rs
index 7a950c6..84ed2a6 100644
--- a/planetwars-server/src/db/mod.rs
+++ b/planetwars-server/src/db/mod.rs
@@ -1,4 +1,5 @@
 pub mod bots;
 pub mod matches;
+pub mod ratings;
 pub mod sessions;
 pub mod users;
diff --git a/planetwars-server/src/db/ratings.rs b/planetwars-server/src/db/ratings.rs
new file mode 100644
index 0000000..bee8548
--- /dev/null
+++ b/planetwars-server/src/db/ratings.rs
@@ -0,0 +1,27 @@
+use diesel::{prelude::*, PgConnection, QueryResult};
+use serde::{Deserialize, Serialize};
+
+use crate::schema::{bots, ratings};
+
+#[derive(Queryable, Debug, Insertable, PartialEq, Serialize, Deserialize)]
+pub struct Rating {
+    pub bot_id: i32,
+    pub rating: f64,
+}
+
+pub fn get_rating(bot_id: i32, db_conn: &PgConnection) -> QueryResult<Option<f64>> {
+    ratings::table
+        .filter(ratings::bot_id.eq(bot_id))
+        .select(ratings::rating)
+        .first(db_conn)
+        .optional()
+}
+
+pub fn set_rating(bot_id: i32, rating: f64, db_conn: &PgConnection) -> QueryResult<usize> {
+    diesel::insert_into(ratings::table)
+        .values(Rating { bot_id, rating })
+        .on_conflict(ratings::bot_id)
+        .do_update()
+        .set(ratings::rating.eq(rating))
+        .execute(db_conn)
+}
diff --git a/planetwars-server/src/schema.rs b/planetwars-server/src/schema.rs
index ae2e60c..812e05e 100644
--- a/planetwars-server/src/schema.rs
+++ b/planetwars-server/src/schema.rs
@@ -47,6 +47,16 @@ table! {
     }
 }
 
+table! {
+    use diesel::sql_types::*;
+    use crate::db_types::*;
+
+    ratings (bot_id) {
+        bot_id -> Int4,
+        rating -> Float8,
+    }
+}
+
 table! {
     use diesel::sql_types::*;
     use crate::db_types::*;
@@ -74,6 +84,15 @@ joinable!(bots -> users (owner_id));
 joinable!(code_bundles -> bots (bot_id));
 joinable!(match_players -> code_bundles (code_bundle_id));
 joinable!(match_players -> matches (match_id));
+joinable!(ratings -> bots (bot_id));
 joinable!(sessions -> users (user_id));
 
-allow_tables_to_appear_in_same_query!(bots, code_bundles, match_players, matches, sessions, users,);
+allow_tables_to_appear_in_same_query!(
+    bots,
+    code_bundles,
+    match_players,
+    matches,
+    ratings,
+    sessions,
+    users,
+);
-- 
cgit v1.2.3