Skip to main content

max / goingson

6.2 KB · 181 lines History Blame Raw
1 //! SQLite implementation of the AttachmentRepository.
2
3 use async_trait::async_trait;
4 use sqlx::SqlitePool;
5 use goingson_core::{
6 Attachment, AttachmentId, CoreError, EmailId, NewAttachment, ProjectId, Result, TaskId, UserId,
7 AttachmentRepository,
8 };
9
10 use crate::utils::{parse_datetime, parse_uuid, parse_uuid_opt, format_datetime_now};
11
12 /// Database row struct for Attachment.
13 #[derive(Debug, Clone, sqlx::FromRow)]
14 struct AttachmentRow {
15 pub id: String,
16 pub user_id: String,
17 pub task_id: Option<String>,
18 pub project_id: Option<String>,
19 pub filename: String,
20 pub file_size: i64,
21 pub mime_type: String,
22 pub blob_hash: String,
23 pub source_email_id: Option<String>,
24 pub created_at: String,
25 }
26
27 impl TryFrom<AttachmentRow> for Attachment {
28 type Error = CoreError;
29
30 fn try_from(row: AttachmentRow) -> std::result::Result<Self, Self::Error> {
31 Ok(Attachment {
32 id: parse_uuid(&row.id)?.into(),
33 user_id: parse_uuid(&row.user_id)?.into(),
34 task_id: parse_uuid_opt(row.task_id.as_deref())?.map(TaskId::from),
35 project_id: parse_uuid_opt(row.project_id.as_deref())?.map(ProjectId::from),
36 filename: row.filename,
37 file_size: row.file_size,
38 mime_type: row.mime_type,
39 blob_hash: row.blob_hash,
40 source_email_id: parse_uuid_opt(row.source_email_id.as_deref())?.map(EmailId::from),
41 created_at: parse_datetime(&row.created_at)?,
42 })
43 }
44 }
45
46 /// SQLite-backed implementation of [`AttachmentRepository`].
47 pub struct SqliteAttachmentRepository {
48 pool: SqlitePool,
49 }
50
51 impl SqliteAttachmentRepository {
52 #[tracing::instrument(skip_all)]
53 pub fn new(pool: SqlitePool) -> Self {
54 Self { pool }
55 }
56 }
57
58 const SELECT_COLS: &str = "id, user_id, task_id, project_id, filename, file_size, mime_type, blob_hash, source_email_id, created_at";
59
60 #[async_trait]
61 impl AttachmentRepository for SqliteAttachmentRepository {
62 #[tracing::instrument(skip_all)]
63 async fn create(&self, user_id: UserId, attachment: NewAttachment) -> Result<Attachment> {
64 let id = AttachmentId::new();
65 let now = format_datetime_now();
66
67 sqlx::query(
68 r#"
69 INSERT INTO attachments (id, user_id, task_id, project_id, filename, file_size, mime_type, blob_hash, source_email_id, created_at)
70 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
71 "#,
72 )
73 .bind(id.to_string())
74 .bind(user_id.to_string())
75 .bind(attachment.task_id.map(|t| t.to_string()))
76 .bind(attachment.project_id.map(|p| p.to_string()))
77 .bind(&attachment.filename)
78 .bind(attachment.file_size)
79 .bind(&attachment.mime_type)
80 .bind(&attachment.blob_hash)
81 .bind(attachment.source_email_id.map(|e| e.to_string()))
82 .bind(&now)
83 .execute(&self.pool)
84 .await
85 .map_err(CoreError::database)?;
86
87 self.get_by_id(id, user_id)
88 .await?
89 .ok_or_else(|| CoreError::internal("Failed to retrieve created attachment"))
90 }
91
92 #[tracing::instrument(skip_all)]
93 async fn list_for_task(&self, task_id: TaskId, user_id: UserId) -> Result<Vec<Attachment>> {
94 let sql = format!(
95 "SELECT {} FROM attachments WHERE task_id = ? AND user_id = ? ORDER BY created_at ASC",
96 SELECT_COLS
97 );
98 let rows = sqlx::query_as::<_, AttachmentRow>(&sql)
99 .bind(task_id.to_string())
100 .bind(user_id.to_string())
101 .fetch_all(&self.pool)
102 .await
103 .map_err(CoreError::database)?;
104
105 rows.into_iter().map(Attachment::try_from).collect()
106 }
107
108 #[tracing::instrument(skip_all)]
109 async fn list_for_project(&self, project_id: ProjectId, user_id: UserId) -> Result<Vec<Attachment>> {
110 let sql = format!(
111 "SELECT {} FROM attachments WHERE project_id = ? AND user_id = ? ORDER BY created_at ASC",
112 SELECT_COLS
113 );
114 let rows = sqlx::query_as::<_, AttachmentRow>(&sql)
115 .bind(project_id.to_string())
116 .bind(user_id.to_string())
117 .fetch_all(&self.pool)
118 .await
119 .map_err(CoreError::database)?;
120
121 rows.into_iter().map(Attachment::try_from).collect()
122 }
123
124 #[tracing::instrument(skip_all)]
125 async fn get_by_id(&self, id: AttachmentId, user_id: UserId) -> Result<Option<Attachment>> {
126 let sql = format!(
127 "SELECT {} FROM attachments WHERE id = ? AND user_id = ?",
128 SELECT_COLS
129 );
130 let row = sqlx::query_as::<_, AttachmentRow>(&sql)
131 .bind(id.to_string())
132 .bind(user_id.to_string())
133 .fetch_optional(&self.pool)
134 .await
135 .map_err(CoreError::database)?;
136
137 row.map(Attachment::try_from).transpose()
138 }
139
140 #[tracing::instrument(skip_all)]
141 async fn delete(&self, id: AttachmentId, user_id: UserId) -> Result<bool> {
142 let result = sqlx::query("DELETE FROM attachments WHERE id = ? AND user_id = ?")
143 .bind(id.to_string())
144 .bind(user_id.to_string())
145 .execute(&self.pool)
146 .await
147 .map_err(CoreError::database)?;
148
149 Ok(result.rows_affected() > 0)
150 }
151
152 #[tracing::instrument(skip_all)]
153 async fn list_by_blob_hash(&self, blob_hash: &str, user_id: UserId) -> Result<Vec<Attachment>> {
154 let sql = format!(
155 "SELECT {} FROM attachments WHERE blob_hash = ? AND user_id = ? ORDER BY created_at ASC",
156 SELECT_COLS
157 );
158 let rows = sqlx::query_as::<_, AttachmentRow>(&sql)
159 .bind(blob_hash)
160 .bind(user_id.to_string())
161 .fetch_all(&self.pool)
162 .await
163 .map_err(CoreError::database)?;
164
165 rows.into_iter().map(Attachment::try_from).collect()
166 }
167
168 #[tracing::instrument(skip_all)]
169 async fn list_all_blob_hashes(&self, user_id: UserId) -> Result<Vec<String>> {
170 let rows: Vec<(String,)> = sqlx::query_as(
171 "SELECT DISTINCT blob_hash FROM attachments WHERE user_id = ?"
172 )
173 .bind(user_id.to_string())
174 .fetch_all(&self.pool)
175 .await
176 .map_err(CoreError::database)?;
177
178 Ok(rows.into_iter().map(|(h,)| h).collect())
179 }
180 }
181