1use async_trait::async_trait;
2use sqlx::{Pool, Sqlite};
3
4use crate::{
5 error::DatabaseError,
6 model::zone::Zone,
7 repository::{RepositoryTx, RepositoryTxKind, ZoneFilter, ZoneRepository},
8};
9
10pub struct SqliteZoneRepository {
11 pool: Pool<Sqlite>,
12}
13
14impl SqliteZoneRepository {
15 pub fn new(pool: Pool<Sqlite>) -> Self {
16 Self { pool }
17 }
18}
19
20#[async_trait]
21impl ZoneRepository for SqliteZoneRepository {
22 async fn create(&self, mut zone: Zone) -> Result<Zone, DatabaseError> {
23 let mut conn = self.pool.acquire().await?;
24
25 let result = sqlx::query(
26 r#"
27 INSERT INTO zones (name, primary_ns, admin_email, ttl, serial, refresh, retry, expire, minimum_ttl)
28 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
29 "#,
30 )
31 .bind(&zone.name)
32 .bind(&zone.primary_ns)
33 .bind(&zone.admin_email)
34 .bind(zone.ttl)
35 .bind(zone.serial)
36 .bind(zone.refresh)
37 .bind(zone.retry)
38 .bind(zone.expire)
39 .bind(zone.minimum_ttl)
40 .execute(&mut *conn)
41 .await?;
42
43 zone.id = result.last_insert_rowid() as i32;
44 Ok(zone)
45 }
46
47 async fn create_tx(
48 &self,
49 tx: &mut RepositoryTx<'_>,
50 mut zone: Zone,
51 ) -> Result<Zone, DatabaseError> {
52 let sqlite_tx = match &mut tx.0 {
53 RepositoryTxKind::SQLite(tx) => tx,
54 _ => {
55 return Err(DatabaseError::TransactionFailed(
56 "transaction kind mismatch (expected SQLite)".to_string(),
57 ));
58 }
59 };
60
61 let result = sqlx::query(
62 r#"
63 INSERT INTO zones (name, primary_ns, admin_email, ttl, serial, refresh, retry, expire, minimum_ttl)
64 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
65 "#,
66 )
67 .bind(&zone.name)
68 .bind(&zone.primary_ns)
69 .bind(&zone.admin_email)
70 .bind(zone.ttl)
71 .bind(zone.serial)
72 .bind(zone.refresh)
73 .bind(zone.retry)
74 .bind(zone.expire)
75 .bind(zone.minimum_ttl)
76 .execute(&mut **sqlite_tx)
77 .await?;
78
79 zone.id = result.last_insert_rowid() as i32;
80 Ok(zone)
81 }
82
83 async fn get_by_id(&self, id: i32) -> Result<Option<Zone>, DatabaseError> {
84 let mut conn = self.pool.acquire().await?;
85
86 let zone = sqlx::query_as::<_, Zone>("SELECT id, name, primary_ns, admin_email, ttl, serial, refresh, retry, expire, minimum_ttl, created_at FROM zones WHERE id = ?")
87 .bind(id)
88 .fetch_optional(&mut *conn)
89 .await?;
90
91 Ok(zone)
92 }
93
94 async fn get_by_id_tx(
95 &self,
96 tx: &mut RepositoryTx<'_>,
97 id: i32,
98 ) -> Result<Option<Zone>, DatabaseError> {
99 let sqlite_tx = match &mut tx.0 {
100 RepositoryTxKind::SQLite(tx) => tx,
101 _ => {
102 return Err(DatabaseError::TransactionFailed(
103 "transaction kind mismatch (expected SQLite)".to_string(),
104 ));
105 }
106 };
107
108 let zone = sqlx::query_as::<_, Zone>("SELECT id, name, primary_ns, admin_email, ttl, serial, refresh, retry, expire, minimum_ttl, created_at FROM zones WHERE id = ?")
109 .bind(id)
110 .fetch_optional(&mut **sqlite_tx)
111 .await?;
112
113 Ok(zone)
114 }
115
116 async fn get_by_name(&self, name: &str) -> Result<Option<Zone>, DatabaseError> {
117 let mut conn = self.pool.acquire().await?;
118
119 let zone = sqlx::query_as::<_, Zone>("SELECT id, name, primary_ns, admin_email, ttl, serial, refresh, retry, expire, minimum_ttl, created_at FROM zones WHERE name = ?")
120 .bind(name)
121 .fetch_optional(&mut *conn)
122 .await?;
123
124 Ok(zone)
125 }
126
127 async fn get_by_name_tx(
128 &self,
129 tx: &mut RepositoryTx<'_>,
130 name: &str,
131 ) -> Result<Option<Zone>, DatabaseError> {
132 let sqlite_tx = match &mut tx.0 {
133 RepositoryTxKind::SQLite(tx) => tx,
134 _ => {
135 return Err(DatabaseError::TransactionFailed(
136 "transaction kind mismatch (expected SQLite)".to_string(),
137 ));
138 }
139 };
140
141 let zone = sqlx::query_as::<_, Zone>("SELECT id, name, primary_ns, admin_email, ttl, serial, refresh, retry, expire, minimum_ttl, created_at FROM zones WHERE name = ?")
142 .bind(name)
143 .fetch_optional(&mut **sqlite_tx)
144 .await?;
145
146 Ok(zone)
147 }
148
149 async fn get_all(&self) -> Result<Vec<Zone>, DatabaseError> {
150 let mut conn = self.pool.acquire().await?;
151
152 let zones = sqlx::query_as::<_, Zone>("SELECT id, name, primary_ns, admin_email, ttl, serial, refresh, retry, expire, minimum_ttl, created_at FROM zones ORDER BY name")
153 .fetch_all(&mut *conn)
154 .await?;
155
156 Ok(zones)
157 }
158
159 async fn get_by_filter(&self, filter: ZoneFilter) -> Result<Vec<Zone>, DatabaseError> {
160 let mut conn = self.pool.acquire().await?;
161 let search = like_pattern(filter.search.as_deref());
162
163 let zones = sqlx::query_as::<_, Zone>(
164 r#"
165 SELECT id, name, primary_ns, admin_email, ttl, serial, refresh, retry, expire, minimum_ttl, created_at
166 FROM zones
167 WHERE (? IS NULL OR LOWER(name) = LOWER(?))
168 AND (? IS NULL OR id = ?)
169 AND (? IS NULL OR LOWER(primary_ns) = LOWER(?))
170 AND (? IS NULL OR LOWER(admin_email) = LOWER(?))
171 AND (? IS NULL OR ttl = ?)
172 AND (? IS NULL OR ttl >= ?)
173 AND (? IS NULL OR ttl <= ?)
174 AND (? IS NULL OR serial = ?)
175 AND (
176 ? IS NULL
177 OR LOWER(name) LIKE LOWER(?)
178 OR LOWER(primary_ns) LIKE LOWER(?)
179 OR LOWER(admin_email) LIKE LOWER(?)
180 )
181 ORDER BY name
182 LIMIT ? OFFSET ?
183 "#,
184 )
185 .bind(&filter.name)
186 .bind(&filter.name)
187 .bind(filter.id)
188 .bind(filter.id)
189 .bind(&filter.primary_ns)
190 .bind(&filter.primary_ns)
191 .bind(&filter.admin_email)
192 .bind(&filter.admin_email)
193 .bind(filter.ttl)
194 .bind(filter.ttl)
195 .bind(filter.min_ttl)
196 .bind(filter.min_ttl)
197 .bind(filter.max_ttl)
198 .bind(filter.max_ttl)
199 .bind(filter.serial)
200 .bind(filter.serial)
201 .bind(&search)
202 .bind(&search)
203 .bind(&search)
204 .bind(&search)
205 .bind(filter.limit.map(i64::from).unwrap_or(i64::MAX))
206 .bind(
207 filter
208 .offset
209 .map(|offset| i64::try_from(offset).unwrap_or(i64::MAX))
210 .unwrap_or(0),
211 )
212 .fetch_all(&mut *conn)
213 .await?;
214
215 Ok(zones)
216 }
217
218 async fn count_by_filter(&self, filter: ZoneFilter) -> Result<u64, DatabaseError> {
219 let mut conn = self.pool.acquire().await?;
220 let search = like_pattern(filter.search.as_deref());
221
222 let count = sqlx::query_scalar::<_, i64>(
223 r#"
224 SELECT COUNT(*)
225 FROM zones
226 WHERE (? IS NULL OR LOWER(name) = LOWER(?))
227 AND (? IS NULL OR id = ?)
228 AND (? IS NULL OR LOWER(primary_ns) = LOWER(?))
229 AND (? IS NULL OR LOWER(admin_email) = LOWER(?))
230 AND (? IS NULL OR ttl = ?)
231 AND (? IS NULL OR ttl >= ?)
232 AND (? IS NULL OR ttl <= ?)
233 AND (? IS NULL OR serial = ?)
234 AND (
235 ? IS NULL
236 OR LOWER(name) LIKE LOWER(?)
237 OR LOWER(primary_ns) LIKE LOWER(?)
238 OR LOWER(admin_email) LIKE LOWER(?)
239 )
240 "#,
241 )
242 .bind(&filter.name)
243 .bind(&filter.name)
244 .bind(filter.id)
245 .bind(filter.id)
246 .bind(&filter.primary_ns)
247 .bind(&filter.primary_ns)
248 .bind(&filter.admin_email)
249 .bind(&filter.admin_email)
250 .bind(filter.ttl)
251 .bind(filter.ttl)
252 .bind(filter.min_ttl)
253 .bind(filter.min_ttl)
254 .bind(filter.max_ttl)
255 .bind(filter.max_ttl)
256 .bind(filter.serial)
257 .bind(filter.serial)
258 .bind(&search)
259 .bind(&search)
260 .bind(&search)
261 .bind(&search)
262 .fetch_one(&mut *conn)
263 .await?;
264
265 Ok(count as u64)
266 }
267
268 async fn update(&self, zone: Zone) -> Result<Zone, DatabaseError> {
269 let mut conn = self.pool.acquire().await?;
270
271 sqlx::query(
272 r#"
273 UPDATE zones
274 SET name = ?, primary_ns = ?, admin_email = ?,
275 ttl = ?, serial = ?, refresh = ?, retry = ?, expire = ?, minimum_ttl = ?
276 WHERE id = ?
277 "#,
278 )
279 .bind(&zone.name)
280 .bind(&zone.primary_ns)
281 .bind(&zone.admin_email)
282 .bind(zone.ttl)
283 .bind(zone.serial)
284 .bind(zone.refresh)
285 .bind(zone.retry)
286 .bind(zone.expire)
287 .bind(zone.minimum_ttl)
288 .bind(zone.id)
289 .execute(&mut *conn)
290 .await?;
291
292 Ok(zone)
293 }
294
295 async fn update_tx(
296 &self,
297 tx: &mut RepositoryTx<'_>,
298 zone: Zone,
299 ) -> Result<Zone, DatabaseError> {
300 let sqlite_tx = match &mut tx.0 {
301 RepositoryTxKind::SQLite(tx) => tx,
302 _ => {
303 return Err(DatabaseError::TransactionFailed(
304 "transaction kind mismatch (expected SQLite)".to_string(),
305 ));
306 }
307 };
308
309 sqlx::query(
310 r#"
311 UPDATE zones
312 SET name = ?, primary_ns = ?, admin_email = ?,
313 ttl = ?, serial = ?, refresh = ?, retry = ?, expire = ?, minimum_ttl = ?
314 WHERE id = ?
315 "#,
316 )
317 .bind(&zone.name)
318 .bind(&zone.primary_ns)
319 .bind(&zone.admin_email)
320 .bind(zone.ttl)
321 .bind(zone.serial)
322 .bind(zone.refresh)
323 .bind(zone.retry)
324 .bind(zone.expire)
325 .bind(zone.minimum_ttl)
326 .bind(zone.id)
327 .execute(&mut **sqlite_tx)
328 .await?;
329
330 Ok(zone)
331 }
332
333 async fn delete(&self, id: i32) -> Result<(), DatabaseError> {
334 let mut conn = self.pool.acquire().await?;
335
336 sqlx::query("DELETE FROM zones WHERE id = ?")
337 .bind(id)
338 .execute(&mut *conn)
339 .await?;
340
341 Ok(())
342 }
343
344 async fn delete_tx(&self, tx: &mut RepositoryTx<'_>, id: i32) -> Result<(), DatabaseError> {
345 let sqlite_tx = match &mut tx.0 {
346 RepositoryTxKind::SQLite(tx) => tx,
347 _ => {
348 return Err(DatabaseError::TransactionFailed(
349 "transaction kind mismatch (expected SQLite)".to_string(),
350 ));
351 }
352 };
353
354 sqlx::query("DELETE FROM zones WHERE id = ?")
355 .bind(id)
356 .execute(&mut **sqlite_tx)
357 .await?;
358 Ok(())
359 }
360}
361
362fn like_pattern(value: Option<&str>) -> Option<String> {
363 value
364 .map(str::trim)
365 .filter(|value| !value.is_empty())
366 .map(|value| format!("%{}%", value))
367}