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