database work

4 files changed, 166 insertions, 0 deletions

diff --git a/src/db/artists.rs b/src/db/artists.rs
new file mode 100644
index 0000000..043f0bd
--- /dev/null
+++ b/src/db/artists.rs
@@ -0,0 +1,56 @@
+use sqlx::{Pool, Postgres};
+
+use crate::artist::Artist;
+use crate::Result;
+
+#[derive(Clone)]
+pub struct Artists(Pool<Postgres>);
+
+impl Artists {
+    pub fn new(pool: Pool<Postgres>) -> Self {
+        Self(pool)
+    }
+
+    pub async fn create(&self, artist: Artist) -> Result<i32> {
+        let artist_id = sqlx::query!(
+            "insert into artists (handle, name, bio, site) values ($1, $2, $3, $4) returning id",
+            artist.handle,
+            artist.name,
+            artist.bio,
+            artist.site
+        )
+        .fetch_one(&self.0)
+        .await?
+        .id;
+        Ok(artist_id)
+    }
+
+    pub async fn read(&self, id: i32) -> Result<Artist> {
+        Ok(sqlx::query_as("select * from artists where id = $1")
+            .bind(id)
+            .fetch_one(&self.0)
+            .await?)
+    }
+
+    pub async fn read_handle(&self, handle: &str) -> Result<Artist> {
+        Ok(sqlx::query_as("select * from artists where handle = $1")
+            .bind(handle)
+            .fetch_one(&self.0)
+            .await?)
+    }
+
+    pub async fn read_all(&self) -> Result<Vec<Artist>> {
+        Ok(sqlx::query_as("select * from artists")
+            .fetch_all(&self.0)
+            .await?)
+    }
+
+    pub async fn search(&self, query: &str) -> Result<Vec<Artist>> {
+        Ok(
+            sqlx::query_as("select * from artists where handle + name like '%$1%'")
+                .bind(query)
+                .fetch_all(&self.0)
+                .await?,
+        )
+    }
+}
diff --git a/src/db/artworks.rs b/src/db/artworks.rs
index 8b13789..619f42d 100644
--- a/src/db/artworks.rs
+++ b/src/db/artworks.rs
@@ -1 +1,52 @@
+use sqlx::{Pool, Postgres};
+use uuid::Uuid;
 
+use crate::artist::Artist;
+use crate::artwork::Artwork;
+use crate::file::File;
+use crate::Result;
+
+use super::Database;
+
+#[derive(Clone)]
+pub struct Artworks(Pool<Postgres>);
+
+impl Artworks {
+    pub fn new(pool: Pool<Postgres>) -> Self {
+        Self(pool)
+    }
+
+    pub fn downcast(&self) -> Database {
+        Database(self.0.clone())
+    }
+
+    pub async fn create(&self, artwork: Artwork) -> Result<i32> {
+        let artist_id = if let Some(artist_id) = artwork.artist.id() {
+            artist_id
+        } else {
+            self.downcast().artists().create(artwork.artist).await?
+        };
+        let artwork_id = sqlx::query!("insert into artworks (title, description, url_source, artist_id) values ($1, $2, $3, $4) returning id", artwork.title, artwork.description, artwork.url_source, artist_id).fetch_one(&self.0).await?.id;
+        for file in artwork.files {
+            sqlx::query!(
+                "insert into artwork_files (id, alt_text, extension, artwork_id) values ($1, $2, $3, $4)",
+                file.id(),
+                file.alt_text,
+                file.extension(),
+                artwork_id
+            )
+            .execute(&self.0)
+            .await?;
+        }
+        Ok(artwork_id)
+    }
+
+    pub async fn read_all(&self) -> Result<Vec<Artwork>> {
+        // TODO: join comments and files
+        Ok(sqlx::query_as(
+            "select * from artworks left join artists on artworks.artist_id = artists.id left join artwork_files on artworks.id = artwork_files.artwork_id group by artworks.id, artists.id, artwork_files.id",
+        )
+        .fetch_all(&self.0)
+        .await?)
+    }
+}
diff --git a/src/db/comments.rs b/src/db/comments.rs
new file mode 100644
index 0000000..ec07aa0
--- /dev/null
+++ b/src/db/comments.rs
@@ -0,0 +1,42 @@
+use sqlx::{Pool, Postgres};
+
+use crate::comment::Comment;
+use crate::Result;
+
+#[derive(Clone)]
+pub struct Comments(Pool<Postgres>);
+
+impl Comments {
+    pub fn new(pool: Pool<Postgres>) -> Self {
+        Self(pool)
+    }
+
+    pub async fn create(&self, comment: Comment) -> Result<i32> {
+        let comment_id = sqlx::query!(
+            r#"insert into comments (text, artwork_id) values ($1, $2) returning id"#,
+            comment.text,
+            comment.artwork_id
+        )
+        .fetch_one(&self.0)
+        .await?
+        .id;
+        for in_reply_to_id in comment.in_reply_to_ids {
+            sqlx::query!("insert into comment_relations (artwork_id, in_reply_to_id, comment_id) values ($1, $2, $3)", comment.artwork_id, in_reply_to_id, comment_id).execute(&self.0).await?;
+        }
+        Ok(comment_id)
+    }
+
+    pub async fn read_all(&self) -> Result<Vec<Comment>> {
+        // TODO: joins to get in_reply_to_ids and mentioned_by_ids
+        let comments: Vec<Comment> = sqlx::query_as("select * from comments")
+            .fetch_all(&self.0)
+            .await?;
+        Ok(comments)
+    }
+
+    pub async fn read_thread(&self, artwork_id: i32) -> Result<Vec<Comment>> {
+        Ok(sqlx::query_as("select * from comments")
+            .fetch_all(&self.0)
+            .await?)
+    }
+}
diff --git a/src/db/mod.rs b/src/db/mod.rs
index 97a5b25..79e8717 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -1,6 +1,11 @@
+use artists::Artists;
+use artworks::Artworks;
+use comments::Comments;
 use sqlx::{postgres::PgPoolOptions, Pool, Postgres};
 
+mod artists;
 mod artworks;
+mod comments;
 
 #[derive(Clone)]
 pub struct Database(Pool<Postgres>);
@@ -17,4 +22,16 @@ impl Database {
 
         Self(pool)
     }
+
+    pub fn artists(&self) -> Artists {
+        Artists::new(self.0.clone())
+    }
+
+    pub fn artworks(&self) -> Artworks {
+        Artworks::new(self.0.clone())
+    }
+
+    pub fn comments(&self) -> Comments {
+        Comments::new(self.0.clone())
+    }
 }