Adds DatabaseClient & Postgres Database Client

6 files changed, 274 insertions, 5 deletions

diff --git a/rust/proto/src/lib.rs b/rust/proto/src/lib.rs
index 1a8199b..3733b99 100644
--- a/rust/proto/src/lib.rs
+++ b/rust/proto/src/lib.rs
@@ -1,4 +1,4 @@
-use chrono::{DateTime, Utc};
+use chrono::{DateTime, NaiveDateTime, Utc};
 use std::ops::{Deref, DerefMut};
 
 include!("gen/mod.rs");
@@ -7,9 +7,15 @@ include!("gen/mod.rs");
 
 pub struct ProtoTimestamp(pbjson_types::Timestamp);
 
-impl Into<pbjson_types::Timestamp> for ProtoTimestamp {
-    fn into(self) -> pbjson_types::Timestamp {
-        self.0
+impl From<ProtoTimestamp> for pbjson_types::Timestamp {
+    fn from(value: ProtoTimestamp) -> Self {
+        value.0
+    }
+}
+
+impl From<pbjson_types::Timestamp> for ProtoTimestamp {
+    fn from(value: pbjson_types::Timestamp) -> Self {
+        ProtoTimestamp(value)
     }
 }
 
@@ -19,6 +25,24 @@ impl From<DateTime<Utc>> for ProtoTimestamp {
     }
 }
 
+impl From<ProtoTimestamp> for DateTime<Utc> {
+    // Will silently underflow if nanos is less than 0, but should panic anyway since the
+    // nanos will be out of range. TryInto by pbjson_types is useless.
+    fn from(value: ProtoTimestamp) -> Self {
+        Self::from_utc(value.into(), Utc)
+    }
+}
+
+impl From<ProtoTimestamp> for NaiveDateTime {
+    // Will silently underflow if nanos is less than 0, but should panic anyway since the
+    // nanos will be out of range. TryInto by pbjson_types is useless.
+    fn from(value: ProtoTimestamp) -> Self {
+        let pbjson_types::Timestamp { seconds, nanos } = value.0;
+
+        NaiveDateTime::from_timestamp(seconds, nanos as u32)
+    }
+}
+
 impl Deref for ProtoTimestamp {
     type Target = pbjson_types::Timestamp;
 
@@ -34,3 +58,25 @@ impl DerefMut for ProtoTimestamp {
 }
 
 //endregion
+
+impl touchpad::common::v1::Gender {
+    /// Converts ISO/IEC 5129 integer into gender
+    /// Reference: https://en.wikipedia.org/wiki/ISO/IEC_5218
+    pub fn from_iso_5218(value: u8) -> Self {
+        match value {
+            0 => Self::Unspecified,
+            1 => Self::Male,
+            2 => Self::Female,
+            9 => Self::Unspecified,
+            _ => Self::Unspecified,
+        }
+    }
+
+    pub fn to_iso_5218(self) -> u8 {
+        match self {
+            Self::Unspecified => 0,
+            Self::Male => 1,
+            Self::Female => 2,
+        }
+    }
+}
\ No newline at end of file
diff --git a/rust/scraper/Cargo.toml b/rust/scraper/Cargo.toml
index 0e0ab40..4a13f01 100644
--- a/rust/scraper/Cargo.toml
+++ b/rust/scraper/Cargo.toml
@@ -6,6 +6,7 @@ edition = "2021"
 # See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html
 
 [dependencies]
+async-trait = "0.1.57"
 chrono = "0.4.21"
 futures = "0.3.21"
 parking_lot = "0.12.1"
@@ -13,8 +14,9 @@ pbjson-types = "0.4.0"
 proto = { path = "../proto" }
 reqwest = { version = "0.11.11", features = ["json", "rustls-tls"] }
 serde = { version = "1.0.143", features = ["derive"] }
+sqlx = { version = "0.6.1", features = ["runtime-tokio-rustls", "postgres", "chrono"] }
 thiserror = "1.0.32"
 tokio = { version = "1.20.1", features = ["full"] }
 
 [dev-dependencies]
-mockito = "0.31.0"
\ No newline at end of file
+mockito = "0.31.0"
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]