Skip to main content

max / goingson

9.8 KB · 311 lines History Blame Raw
1 //! SQLite implementation of the SavedViewRepository.
2 //!
3 //! Manages saved views (custom filtered/sorted task lists) that users can
4 //! create, pin to the sidebar, and reorder.
5
6 use async_trait::async_trait;
7 use chrono::Utc;
8 use sqlx::SqlitePool;
9 use goingson_core::{
10 CoreError, DbValue, NewSavedView, ParseableEnum, Result, SavedView, SavedViewId,
11 SavedViewRepository, SortDirection, UserId, ViewFilters, ViewType,
12 };
13
14 use crate::utils::parse_uuid;
15
16 #[derive(sqlx::FromRow)]
17 struct ViewRow {
18 id: String,
19 user_id: String,
20 name: String,
21 view_type: String,
22 filters: String,
23 sort_by: Option<String>,
24 sort_order: String,
25 is_pinned: i32,
26 position: i32,
27 created_at: String,
28 updated_at: String,
29 }
30
31 /// SQLite-backed implementation of [`SavedViewRepository`].
32 ///
33 /// Stores view configurations as JSON-serialized filters with support
34 /// for pinning and custom ordering.
35 pub struct SqliteSavedViewRepository {
36 pool: SqlitePool,
37 }
38
39 impl SqliteSavedViewRepository {
40 /// Creates a new repository instance with the given connection pool.
41 #[tracing::instrument(skip_all)]
42 pub fn new(pool: SqlitePool) -> Self {
43 Self { pool }
44 }
45 }
46
47 #[async_trait]
48 impl SavedViewRepository for SqliteSavedViewRepository {
49 #[tracing::instrument(skip_all)]
50 async fn list_all(&self, user_id: UserId) -> Result<Vec<SavedView>> {
51 let user_id_str = user_id.to_string();
52
53 let rows: Vec<ViewRow> = sqlx::query_as(
54 r#"
55 SELECT id, user_id, name, view_type, filters, sort_by, sort_order,
56 is_pinned, position, created_at, updated_at
57 FROM saved_views
58 WHERE user_id = ?
59 ORDER BY position ASC, name ASC
60 "#,
61 )
62 .bind(&user_id_str)
63 .fetch_all(&self.pool)
64 .await
65 .map_err(CoreError::database)?;
66
67 rows.into_iter().map(row_to_saved_view).collect()
68 }
69
70 #[tracing::instrument(skip_all)]
71 async fn list_pinned(&self, user_id: UserId) -> Result<Vec<SavedView>> {
72 let user_id_str = user_id.to_string();
73
74 let rows: Vec<ViewRow> = sqlx::query_as(
75 r#"
76 SELECT id, user_id, name, view_type, filters, sort_by, sort_order,
77 is_pinned, position, created_at, updated_at
78 FROM saved_views
79 WHERE user_id = ? AND is_pinned = 1
80 ORDER BY position ASC, name ASC
81 "#,
82 )
83 .bind(&user_id_str)
84 .fetch_all(&self.pool)
85 .await
86 .map_err(CoreError::database)?;
87
88 rows.into_iter().map(row_to_saved_view).collect()
89 }
90
91 #[tracing::instrument(skip_all)]
92 async fn get_by_id(&self, id: SavedViewId, user_id: UserId) -> Result<Option<SavedView>> {
93 let id_str = id.to_string();
94 let user_id_str = user_id.to_string();
95
96 let row: Option<ViewRow> = sqlx::query_as(
97 r#"
98 SELECT id, user_id, name, view_type, filters, sort_by, sort_order,
99 is_pinned, position, created_at, updated_at
100 FROM saved_views
101 WHERE id = ? AND user_id = ?
102 "#,
103 )
104 .bind(&id_str)
105 .bind(&user_id_str)
106 .fetch_optional(&self.pool)
107 .await
108 .map_err(CoreError::database)?;
109
110 match row {
111 Some(r) => Ok(Some(row_to_saved_view(r)?)),
112 None => Ok(None),
113 }
114 }
115
116 #[tracing::instrument(skip_all)]
117 async fn create(&self, user_id: UserId, view: NewSavedView) -> Result<SavedView> {
118 let id = SavedViewId::new();
119 let now = Utc::now();
120 let id_str = id.to_string();
121 let user_id_str = user_id.to_string();
122 let now_str = now.to_rfc3339();
123 let filters_json = serde_json::to_string(&view.filters)
124 .map_err(|e| CoreError::database_msg(e.to_string()))?;
125
126 sqlx::query(
127 r#"
128 INSERT INTO saved_views (id, user_id, name, view_type, filters, sort_by, sort_order,
129 is_pinned, position, created_at, updated_at)
130 VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?)
131 "#,
132 )
133 .bind(&id_str)
134 .bind(&user_id_str)
135 .bind(&view.name)
136 .bind(view.view_type.db_value())
137 .bind(&filters_json)
138 .bind(view.sort_by.map(|sf| {
139 serde_json::to_value(sf).ok()
140 .and_then(|v| v.as_str().map(String::from))
141 .unwrap_or_default()
142 }))
143 .bind(match view.sort_order {
144 Some(SortDirection::Desc) => "desc",
145 _ => "asc",
146 })
147 .bind(if view.is_pinned.unwrap_or(false) { 1 } else { 0 })
148 .bind(&now_str)
149 .bind(&now_str)
150 .execute(&self.pool)
151 .await
152 .map_err(CoreError::database)?;
153
154 Ok(SavedView {
155 id,
156 user_id,
157 name: view.name,
158 view_type: view.view_type,
159 filters: view.filters,
160 sort_by: view.sort_by,
161 sort_order: view.sort_order.unwrap_or(SortDirection::Asc),
162 is_pinned: view.is_pinned.unwrap_or(false),
163 position: 0,
164 created_at: now,
165 updated_at: now,
166 })
167 }
168
169 #[tracing::instrument(skip_all)]
170 async fn update(&self, id: SavedViewId, user_id: UserId, view: NewSavedView) -> Result<Option<SavedView>> {
171 let id_str = id.to_string();
172 let user_id_str = user_id.to_string();
173 let now = Utc::now();
174 let now_str = now.to_rfc3339();
175 let filters_json = serde_json::to_string(&view.filters)
176 .map_err(|e| CoreError::database_msg(e.to_string()))?;
177
178 let result = sqlx::query(
179 r#"
180 UPDATE saved_views
181 SET name = ?, view_type = ?, filters = ?, sort_by = ?, sort_order = ?,
182 is_pinned = ?, updated_at = ?
183 WHERE id = ? AND user_id = ?
184 "#,
185 )
186 .bind(&view.name)
187 .bind(view.view_type.db_value())
188 .bind(&filters_json)
189 .bind(view.sort_by.map(|sf| {
190 serde_json::to_value(sf).ok()
191 .and_then(|v| v.as_str().map(String::from))
192 .unwrap_or_default()
193 }))
194 .bind(match view.sort_order {
195 Some(SortDirection::Desc) => "desc",
196 _ => "asc",
197 })
198 .bind(if view.is_pinned.unwrap_or(false) { 1 } else { 0 })
199 .bind(&now_str)
200 .bind(&id_str)
201 .bind(&user_id_str)
202 .execute(&self.pool)
203 .await
204 .map_err(CoreError::database)?;
205
206 if result.rows_affected() == 0 {
207 return Ok(None);
208 }
209
210 self.get_by_id(id, user_id).await
211 }
212
213 #[tracing::instrument(skip_all)]
214 async fn delete(&self, id: SavedViewId, user_id: UserId) -> Result<bool> {
215 let id_str = id.to_string();
216 let user_id_str = user_id.to_string();
217
218 let result = sqlx::query("DELETE FROM saved_views WHERE id = ? AND user_id = ?")
219 .bind(&id_str)
220 .bind(&user_id_str)
221 .execute(&self.pool)
222 .await
223 .map_err(CoreError::database)?;
224
225 Ok(result.rows_affected() > 0)
226 }
227
228 #[tracing::instrument(skip_all)]
229 async fn toggle_pinned(&self, id: SavedViewId, user_id: UserId) -> Result<Option<SavedView>> {
230 let id_str = id.to_string();
231 let user_id_str = user_id.to_string();
232 let now_str = Utc::now().to_rfc3339();
233
234 let result = sqlx::query(
235 r#"
236 UPDATE saved_views
237 SET is_pinned = CASE WHEN is_pinned = 1 THEN 0 ELSE 1 END, updated_at = ?
238 WHERE id = ? AND user_id = ?
239 "#,
240 )
241 .bind(&now_str)
242 .bind(&id_str)
243 .bind(&user_id_str)
244 .execute(&self.pool)
245 .await
246 .map_err(CoreError::database)?;
247
248 if result.rows_affected() == 0 {
249 return Ok(None);
250 }
251
252 self.get_by_id(id, user_id).await
253 }
254
255 #[tracing::instrument(skip_all)]
256 async fn update_position(&self, id: SavedViewId, user_id: UserId, position: i32) -> Result<Option<SavedView>> {
257 let id_str = id.to_string();
258 let user_id_str = user_id.to_string();
259 let now_str = Utc::now().to_rfc3339();
260
261 let result = sqlx::query(
262 "UPDATE saved_views SET position = ?, updated_at = ? WHERE id = ? AND user_id = ?",
263 )
264 .bind(position)
265 .bind(&now_str)
266 .bind(&id_str)
267 .bind(&user_id_str)
268 .execute(&self.pool)
269 .await
270 .map_err(CoreError::database)?;
271
272 if result.rows_affected() == 0 {
273 return Ok(None);
274 }
275
276 self.get_by_id(id, user_id).await
277 }
278 }
279
280 /// Converts a database row to a [`SavedView`] domain object.
281 fn row_to_saved_view(row: ViewRow) -> Result<SavedView> {
282 let view_type = ViewType::from_str_or_default(&row.view_type);
283
284 let filters: ViewFilters = serde_json::from_str(&row.filters)
285 .unwrap_or_else(|e| {
286 tracing::warn!(view_id = %row.id, error = %e, "Malformed saved view filters, using defaults");
287 ViewFilters::default()
288 });
289
290 Ok(SavedView {
291 id: parse_uuid(&row.id)?.into(),
292 user_id: parse_uuid(&row.user_id)?.into(),
293 name: row.name,
294 view_type,
295 filters,
296 sort_by: row.sort_by.as_deref().and_then(|s| {
297 let json = serde_json::Value::String(s.to_string());
298 serde_json::from_value(json).ok()
299 }),
300 sort_order: SortDirection::from_str_or_default(&row.sort_order),
301 is_pinned: row.is_pinned == 1,
302 position: row.position,
303 created_at: chrono::DateTime::parse_from_rfc3339(&row.created_at)
304 .map(|dt| dt.with_timezone(&Utc))
305 .unwrap_or_else(|_| Utc::now()),
306 updated_at: chrono::DateTime::parse_from_rfc3339(&row.updated_at)
307 .map(|dt| dt.with_timezone(&Utc))
308 .unwrap_or_else(|_| Utc::now()),
309 })
310 }
311