Skip to main content

max / balanced_breakfast

6.7 KB · 209 lines History Blame Raw
1 use super::{
2 BookmarkId, CreateBookmark, DbBookmark, SqlitePool, TIMESTAMP_FMT, UpdateBookmark, Utc,
3 };
4
5 #[derive(Clone)]
6 /// Repository for bookmark (reading list) CRUD
7 pub struct BookmarksRepository {
8 pool: SqlitePool,
9 }
10
11 impl BookmarksRepository {
12 pub fn new(pool: SqlitePool) -> Self {
13 Self { pool }
14 }
15
16 /// Insert a new bookmark and return the created row.
17 #[tracing::instrument(skip_all)]
18 pub async fn create(&self, input: CreateBookmark) -> Result<DbBookmark, sqlx::Error> {
19 let id = BookmarkId::new();
20 let now = Utc::now().format(TIMESTAMP_FMT).to_string();
21
22 let mut tx = self.pool.begin().await?;
23
24 let bookmark: DbBookmark = sqlx::query_as(
25 r"
26 INSERT INTO bookmarks (id, url, title, description, author, source_name,
27 feed_item_id, notes, is_pinned, created_at, updated_at)
28 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, 0, ?9, ?9)
29 RETURNING *
30 ",
31 )
32 .bind(id)
33 .bind(&input.url)
34 .bind(&input.title)
35 .bind(&input.description)
36 .bind(&input.author)
37 .bind(&input.source_name)
38 .bind(&input.feed_item_id)
39 .bind(&input.notes)
40 .bind(&now)
41 .fetch_one(&mut *tx)
42 .await?;
43
44 // Insert tags
45 for tag in &input.tags {
46 let tag = tag.trim();
47 if tag.is_empty() {
48 continue;
49 }
50 sqlx::query("INSERT OR IGNORE INTO bookmark_tags (bookmark_id, tag) VALUES (?1, ?2)")
51 .bind(id)
52 .bind(tag)
53 .execute(&mut *tx)
54 .await?;
55 }
56
57 tx.commit().await?;
58 Ok(bookmark)
59 }
60
61 /// Look up a single bookmark by ID. Returns `None` if not found.
62 #[tracing::instrument(skip_all)]
63 pub async fn get(&self, id: BookmarkId) -> Result<Option<DbBookmark>, sqlx::Error> {
64 sqlx::query_as("SELECT * FROM bookmarks WHERE id = ?1")
65 .bind(id)
66 .fetch_optional(&self.pool)
67 .await
68 }
69
70 /// Look up a bookmark by URL. Returns `None` if not found.
71 #[tracing::instrument(skip_all)]
72 pub async fn get_by_url(&self, url: &str) -> Result<Option<DbBookmark>, sqlx::Error> {
73 sqlx::query_as("SELECT * FROM bookmarks WHERE url = ?1")
74 .bind(url)
75 .fetch_optional(&self.pool)
76 .await
77 }
78
79 /// Look up a bookmark by its linked feed item ID. Returns `None` if not found.
80 #[tracing::instrument(skip_all)]
81 pub async fn get_by_feed_item(
82 &self,
83 feed_item_id: &str,
84 ) -> Result<Option<DbBookmark>, sqlx::Error> {
85 sqlx::query_as("SELECT * FROM bookmarks WHERE feed_item_id = ?1")
86 .bind(feed_item_id)
87 .fetch_optional(&self.pool)
88 .await
89 }
90
91 /// List bookmarks, optionally filtered by tag. Ordered by pinned first, then newest.
92 #[tracing::instrument(skip_all)]
93 pub async fn list(&self, tag: Option<&str>) -> Result<Vec<DbBookmark>, sqlx::Error> {
94 match tag {
95 Some(tag) => {
96 sqlx::query_as(
97 r"
98 SELECT b.* FROM bookmarks b
99 INNER JOIN bookmark_tags bt ON bt.bookmark_id = b.id
100 WHERE bt.tag = ?1
101 ORDER BY b.is_pinned DESC, b.created_at DESC
102 ",
103 )
104 .bind(tag)
105 .fetch_all(&self.pool)
106 .await
107 }
108 None => {
109 sqlx::query_as("SELECT * FROM bookmarks ORDER BY is_pinned DESC, created_at DESC")
110 .fetch_all(&self.pool)
111 .await
112 }
113 }
114 }
115
116 /// Update a bookmark's mutable fields.
117 #[tracing::instrument(skip_all)]
118 pub async fn update(&self, id: BookmarkId, input: UpdateBookmark) -> Result<(), sqlx::Error> {
119 let now = Utc::now().format(TIMESTAMP_FMT).to_string();
120
121 sqlx::query(
122 r"
123 UPDATE bookmarks SET
124 title = COALESCE(?2, title),
125 description = COALESCE(?3, description),
126 notes = COALESCE(?4, notes),
127 is_pinned = COALESCE(?5, is_pinned),
128 updated_at = ?1
129 WHERE id = ?6
130 ",
131 )
132 .bind(&now)
133 .bind(&input.title)
134 .bind(&input.description)
135 .bind(&input.notes)
136 .bind(input.is_pinned.map(|b| b as i32))
137 .bind(id)
138 .execute(&self.pool)
139 .await?;
140 Ok(())
141 }
142
143 /// Delete a bookmark by ID (cascade deletes its tags).
144 #[tracing::instrument(skip_all)]
145 pub async fn delete(&self, id: BookmarkId) -> Result<(), sqlx::Error> {
146 sqlx::query("DELETE FROM bookmarks WHERE id = ?1")
147 .bind(id)
148 .execute(&self.pool)
149 .await?;
150 Ok(())
151 }
152
153 /// Replace all tags for a bookmark (delete-all-then-insert).
154 #[tracing::instrument(skip_all)]
155 pub async fn set_tags(&self, id: BookmarkId, tags: &[String]) -> Result<(), sqlx::Error> {
156 let mut tx = self.pool.begin().await?;
157
158 sqlx::query("DELETE FROM bookmark_tags WHERE bookmark_id = ?1")
159 .bind(id)
160 .execute(&mut *tx)
161 .await?;
162
163 for tag in tags {
164 let tag = tag.trim();
165 if tag.is_empty() {
166 continue;
167 }
168 sqlx::query("INSERT OR IGNORE INTO bookmark_tags (bookmark_id, tag) VALUES (?1, ?2)")
169 .bind(id)
170 .bind(tag)
171 .execute(&mut *tx)
172 .await?;
173 }
174
175 tx.commit().await?;
176 Ok(())
177 }
178
179 /// Get all tags for a bookmark.
180 #[tracing::instrument(skip_all)]
181 pub async fn get_tags(&self, id: BookmarkId) -> Result<Vec<String>, sqlx::Error> {
182 let rows: Vec<(String,)> =
183 sqlx::query_as("SELECT tag FROM bookmark_tags WHERE bookmark_id = ?1 ORDER BY tag")
184 .bind(id)
185 .fetch_all(&self.pool)
186 .await?;
187 Ok(rows.into_iter().map(|r| r.0).collect())
188 }
189
190 /// Get all distinct tags across all bookmarks.
191 #[tracing::instrument(skip_all)]
192 pub async fn list_all_tags(&self) -> Result<Vec<String>, sqlx::Error> {
193 let rows: Vec<(String,)> =
194 sqlx::query_as("SELECT DISTINCT tag FROM bookmark_tags ORDER BY tag")
195 .fetch_all(&self.pool)
196 .await?;
197 Ok(rows.into_iter().map(|r| r.0).collect())
198 }
199
200 /// Count total bookmarks.
201 #[tracing::instrument(skip_all)]
202 pub async fn count(&self) -> Result<i64, sqlx::Error> {
203 let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM bookmarks")
204 .fetch_one(&self.pool)
205 .await?;
206 Ok(row.0)
207 }
208 }
209