implement artwork uploadHEADmain

4 files changed, 63 insertions, 19 deletions

diff --git a/src/db/artists.rs b/src/db/artists.rs
index 043f0bd..08a5968 100644
--- a/src/db/artists.rs
+++ b/src/db/artists.rs
@@ -13,7 +13,7 @@ impl Artists {
 
     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",
+            "insert into artists (handle, name, bio, site) values ($1, $2, $3, $4) returning artist_id",
             artist.handle,
             artist.name,
             artist.bio,
@@ -21,7 +21,7 @@ impl Artists {
         )
         .fetch_one(&self.0)
         .await?
-        .id;
+        .artist_id;
         Ok(artist_id)
     }
 
diff --git a/src/db/artworks.rs b/src/db/artworks.rs
index 619f42d..0b62d1d 100644
--- a/src/db/artworks.rs
+++ b/src/db/artworks.rs
@@ -1,13 +1,49 @@
 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;
 
+use time::{OffsetDateTime, PrimitiveDateTime};
+
+use crate::{artist::Artist, comment::Comment, file::File};
+
+#[derive(sqlx::FromRow)]
+pub struct Artwork {
+    /// artwork id
+    artwork_id: Option<i32>,
+    /// name of the artwork
+    pub title: Option<String>,
+    /// description of the artwork
+    pub description: Option<String>,
+    /// source url of the artwork
+    pub url_source: Option<String>,
+    /// artwork creation time
+    created_at: Option<PrimitiveDateTime>,
+    /// id of the artist
+    pub artist: Artist,
+    /// ids of files
+    pub files: Vec<File>,
+    // /// TODO: comments in thread,
+    // #[sqlx(Flatten)]
+    // comments: Vec<Comment>,
+}
+
+impl Artwork {
+    pub fn new(title: Option<String>, description: Option<String>, url_source: Option<String>, artist: Artist, files: Vec<File>) -> Self {
+        Self {
+            artwork_id: None,
+            title,
+            description,
+            url_source,
+            created_at: None,
+            artist,
+            files,
+        }
+    }
+}
+
 #[derive(Clone)]
 pub struct Artworks(Pool<Postgres>);
 
@@ -21,16 +57,17 @@ impl Artworks {
     }
 
     pub async fn create(&self, artwork: Artwork) -> Result<i32> {
-        let artist_id = if let Some(artist_id) = artwork.artist.id() {
+        // TODO: efficiency?
+        let artist_id = if let Some(artist_id) = self.downcast().artists().read_handle(&artwork.artist.handle).await.map(|artist| artist.artist_id()).unwrap_or(artwork.artist.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;
+        let artwork_id = sqlx::query!("insert into artworks (title, description, url_source, artist_id) values ($1, $2, $3, $4) returning artwork_id", artwork.title, artwork.description, artwork.url_source, artist_id).fetch_one(&self.0).await?.artwork_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(),
+                "insert into artwork_files (file_id, alt_text, extension, artwork_id) values ($1, $2, $3, $4)",
+                file.file_id(),
                 file.alt_text,
                 file.extension(),
                 artwork_id
@@ -43,8 +80,12 @@ impl Artworks {
 
     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",
+        Ok(sqlx::query_as!(Artwork, 
+            r#"select artworks.artwork_id, artworks.title, artworks.description, artworks.url_source, artworks.created_at, coalesce(artists.*) as "artist!: Artist", coalesce(array_agg((artwork_files.file_id, artwork_files.alt_text, artwork_files.extension, artwork_files.artwork_id)) filter (where artwork_files.file_id is not null), '{}') as "files!: Vec<File>" 
+            from artworks
+            left join artists on artworks.artist_id = artists.artist_id 
+            left join artwork_files on artworks.artwork_id = artwork_files.artwork_id 
+            group by artworks.artwork_id, artists.artist_id"#,
         )
         .fetch_all(&self.0)
         .await?)
diff --git a/src/db/comments.rs b/src/db/comments.rs
index ec07aa0..3c14852 100644
--- a/src/db/comments.rs
+++ b/src/db/comments.rs
@@ -13,13 +13,13 @@ impl Comments {
 
     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"#,
+            r#"insert into comments (text, artwork_id) values ($1, $2) returning comment_id"#,
             comment.text,
             comment.artwork_id
         )
         .fetch_one(&self.0)
         .await?
-        .id;
+        .comment_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?;
         }
@@ -35,8 +35,11 @@ impl 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?)
+        Ok(
+            sqlx::query_as("select * from comments where artwork_id = $1")
+                .bind(artwork_id)
+                .fetch_all(&self.0)
+                .await?,
+        )
     }
 }
diff --git a/src/db/mod.rs b/src/db/mod.rs
index 79e8717..6f794a7 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -3,9 +3,9 @@ use artworks::Artworks;
 use comments::Comments;
 use sqlx::{postgres::PgPoolOptions, Pool, Postgres};
 
-mod artists;
-mod artworks;
-mod comments;
+pub mod artists;
+pub mod artworks;
+pub mod comments;
 
 #[derive(Clone)]
 pub struct Database(Pool<Postgres>);