Adds DatabaseClient & Postgres Database Client

4 files changed, 221 insertions, 0 deletions

diff --git a/rust/scraper/src/database/error.rs b/rust/scraper/src/database/error.rs
new file mode 100644
index 0000000..f530768
--- /dev/null
+++ b/rust/scraper/src/database/error.rs
@@ -0,0 +1,12 @@
+use std::borrow::Cow;
+use thiserror::Error;
+
+#[derive(Error, Debug)]
+pub enum DatabaseError {
+    #[error(transparent)]
+    SQLError(#[from] sqlx::Error),
+    #[error("bad input: {0}")]
+    BadInput(Cow<'static, str>),
+    #[error("not found")]
+    NotFound,
+}
diff --git a/rust/scraper/src/database/mod.rs b/rust/scraper/src/database/mod.rs
new file mode 100644
index 0000000..232f42f
--- /dev/null
+++ b/rust/scraper/src/database/mod.rs
@@ -0,0 +1,20 @@
+pub mod postgres;
+pub mod error;
+
+pub use error::DatabaseError;
+
+use proto::touchpad::common::v1;
+
+type Result<T> = std::result::Result<T, DatabaseError>;
+
+#[async_trait::async_trait]
+pub trait DatabaseClient {
+    async fn get_swimmer(&self, id: u32) -> Result<v1::Swimmer>;
+    async fn add_swimmer(&self, swimmer: &v1::Swimmer) -> Result<()>;
+
+    async fn get_team(&self, id: u32) -> Result<v1::Team>;
+    async fn add_team(&self, team: &v1::Team) -> Result<()>;
+
+    async fn get_meet(&self, id: u32) -> Result<v1::SwimMeet>;
+    async fn add_meet(&self, meet: &v1::SwimMeet) -> Result<()>;
+}
diff --git a/rust/scraper/src/database/postgres.rs b/rust/scraper/src/database/postgres.rs
new file mode 100644
index 0000000..3361308
--- /dev/null
+++ b/rust/scraper/src/database/postgres.rs
@@ -0,0 +1,188 @@
+use std::mem;
+use crate::database::error::DatabaseError;
+use chrono::{DateTime, NaiveDate, TimeZone, Utc};
+use proto::touchpad::common::v1::{Gender, SwimMeet, Swimmer, Team};
+use sqlx::postgres::{PgPool, PgPoolOptions};
+use proto::ProtoTimestamp;
+
+pub struct PostgresClient {
+    pool: PgPool,
+}
+
+impl PostgresClient {
+    pub async fn new<S: AsRef<str>>(url: S) -> Result<PostgresClient, sqlx::Error> {
+        Ok(PostgresClient {
+            pool: PgPoolOptions::new().connect(url.as_ref()).await?,
+        })
+    }
+}
+
+#[async_trait::async_trait]
+impl super::DatabaseClient for PostgresClient {
+    async fn get_swimmer(&self, id: u32) -> Result<Swimmer, DatabaseError> {
+        let swimmer: SwimmersTableSchema = sqlx::query_as!(
+            SwimmersTableSchema,
+            "SELECT * FROM swimmers WHERE id = $1;",
+            id as i32
+        )
+            .fetch_one(&self.pool)
+            .await?;
+
+        Ok(Swimmer {
+            id,
+            name: swimmer.name,
+            gender: Gender::from_iso_5218(swimmer.gender as u8) as i32,
+        })
+    }
+
+    async fn add_swimmer(&self, swimmer: &Swimmer) -> Result<(), DatabaseError> {
+        let gender = Gender::from_i32(swimmer.gender)
+            .ok_or(DatabaseError::BadInput("Invalid gender".into()))?
+            .to_iso_5218();
+
+        sqlx::query!(
+            "INSERT INTO swimmers VALUES ($1, $2, $3);",
+            swimmer.id as i32,
+            swimmer.name,
+            gender as i16
+        )
+            .execute(&self.pool)
+            .await?;
+
+        Ok(())
+    }
+
+    async fn get_team(&self, id: u32) -> crate::database::Result<Team> {
+        let team: TeamsTableSchema = sqlx::query_as!(
+            TeamsTableSchema,
+            "SELECT * FROM teams WHERE id = $1;",
+            id as i32
+        )
+            .fetch_one(&self.pool)
+            .await?;
+
+        Ok(Team {
+            id,
+            name: team.name,
+        })
+    }
+
+    async fn add_team(&self, team: &Team) -> crate::database::Result<()> {
+        sqlx::query!(
+            "INSERT INTO teams VALUES ($1, $2);",
+            team.id as i32,
+            team.name
+        )
+            .execute(&self.pool)
+            .await?;
+
+        Ok(())
+    }
+
+    async fn get_meet(&self, id: u32) -> crate::database::Result<SwimMeet> {
+        let mut meet: Vec<GetMeetSchema> = sqlx::query_as!(
+            GetMeetSchema,
+            "
+SELECT meets.name as meet_name, start_date, end_date, team_id, t.name as team_name
+FROM meets
+INNER JOIN meets_teams mt on meets.id = mt.meet_id
+INNER JOIN teams t on mt.team_id = t.id
+WHERE meets.id = $1;
+",
+            id as i32
+        )
+            .fetch_all(&self.pool)
+            .await?;
+
+        let meet_name = mem::take(&mut meet.get_mut(0).ok_or(DatabaseError::NotFound)?.meet_name);
+        let start = meet.get(0).ok_or(DatabaseError::NotFound)?.start_date.and_hms(0, 0, 0);
+        let end = meet.get(0).ok_or(DatabaseError::NotFound)?.end_date.and_hms(0, 0, 0);
+
+        let teams = meet.into_iter()
+            .map(|m| Team {
+                id: m.team_id as u32,
+                name: m.team_name,
+            })
+            .collect();
+
+        Ok(SwimMeet {
+            id,
+            meet_name,
+            start: Some(Utc.from_utc_datetime(&start).into()),
+            end: Some(Utc.from_utc_datetime(&end).into()),
+            teams,
+            points: Default::default(),
+        })
+    }
+
+    async fn add_meet(&self, meet: &SwimMeet) -> crate::database::Result<()> {
+        let mut tx = self.pool.begin().await?;
+
+        // Convert protobuf timestamp into chrono Datetime
+        let start_date: DateTime<Utc> = {
+            let proto_timestamp: ProtoTimestamp = meet.start.clone()
+                .ok_or(DatabaseError::BadInput("empty start date".into()))?
+                .into();
+            proto_timestamp.into()
+        };
+
+        let end_date: DateTime<Utc> = {
+            let proto_timestamp: ProtoTimestamp = meet.end.clone()
+                .ok_or(DatabaseError::BadInput("empty end date".into()))?
+                .into();
+            proto_timestamp.into()
+        };
+
+        // Insert into meets table
+        sqlx::query!(
+            "INSERT INTO meets VALUES ($1, $2, $3, $4)",
+            meet.id as i32,
+            meet.meet_name,
+            start_date.date_naive(),
+            end_date.date_naive(),
+        )
+            .execute(&mut tx)
+            .await?;
+
+        for team in &meet.teams {
+            sqlx::query!(
+                "INSERT INTO teams VALUES ($1, $2) ON CONFLICT DO NOTHING",
+                team.id as i32,
+                team.name,
+            )
+                .execute(&mut tx)
+                .await?;
+
+            sqlx::query!(
+                "INSERT INTO meets_teams VALUES ($1, $2)",
+                meet.id as i32,
+                team.id as i32,
+            )
+                .execute(&mut tx)
+                .await?;
+        }
+
+        tx.commit().await?;
+
+        Ok(())
+    }
+}
+
+struct SwimmersTableSchema {
+    id: i32,
+    name: String,
+    gender: i16,
+}
+
+struct TeamsTableSchema {
+    id: i32,
+    name: String,
+}
+
+struct GetMeetSchema {
+    meet_name: String,
+    start_date: NaiveDate,
+    end_date: NaiveDate,
+    team_id: i32,
+    team_name: String,
+}
diff --git a/rust/scraper/src/main.rs b/rust/scraper/src/main.rs
index 7d6a4a2..901a833 100644
--- a/rust/scraper/src/main.rs
+++ b/rust/scraper/src/main.rs
@@ -1,5 +1,6 @@
 use touchpad::TouchpadLiveClient;
 
+mod database;
 mod touchpad;
 
 #[tokio::main]