1use async_trait::async_trait;
2use sqlx::{Pool, Postgres, Row};
3
4use crate::{
5 error::DatabaseError,
6 model::zone::Zone,
7 repository::{RepositoryTx, RepositoryTxKind, ZoneFilter, ZoneRepository},
8};
9
10pub struct PostgresZoneRepository {
11 pool: Pool<Postgres>,
12}
13
14impl PostgresZoneRepository {
15 pub fn new(pool: Pool<Postgres>) -> Self {
16 Self { pool }
17 }
18}
19
20#[async_trait]
21impl ZoneRepository for PostgresZoneRepository {
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 ($1, $2, $3, $4, $5, $6, $7, $8, $9)
29 RETURNING id
30 "#,
31 )
32 .bind(&zone.name)
33 .bind(&zone.primary_ns)
34 .bind(&zone.admin_email)
35 .bind(zone.ttl)
36 .bind(zone.serial)
37 .bind(zone.refresh)
38 .bind(zone.retry)
39 .bind(zone.expire)
40 .bind(zone.minimum_ttl)
41 .fetch_one(&mut *conn)
42 .await?;
43
44 zone.id = result.get::<i32, _>(0);
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 postgres_tx = match &mut tx.0 {
55 RepositoryTxKind::PostgreSQL(tx) => tx,
56 _ => {
57 return Err(DatabaseError::TransactionFailed(
58 "transaction kind mismatch (expected PostgreSQL)".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 ($1, $2, $3, $4, $5, $6, $7, $8, $9)
67 RETURNING id
68 "#,
69 )
70 .bind(&zone.name)
71 .bind(&zone.primary_ns)
72 .bind(&zone.admin_email)
73 .bind(zone.ttl)
74 .bind(zone.serial)
75 .bind(zone.refresh)
76 .bind(zone.retry)
77 .bind(zone.expire)
78 .bind(zone.minimum_ttl)
79 .fetch_one(&mut **postgres_tx)
80 .await?;
81
82 zone.id = result.get::<i32, _>(0);
83 Ok(zone)
84 }
85
86 async fn get_by_id(&self, id: i32) -> Result<Option<Zone>, DatabaseError> {
87 let mut conn = self.pool.acquire().await?;
88
89 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 = $1")
90 .bind(id)
91 .fetch_optional(&mut *conn)
92 .await?;
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 postgres_tx = match &mut tx.0 {
103 RepositoryTxKind::PostgreSQL(tx) => tx,
104 _ => {
105 return Err(DatabaseError::TransactionFailed(
106 "transaction kind mismatch (expected PostgreSQL)".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 = $1 FOR UPDATE")
112 .bind(id)
113 .fetch_optional(&mut **postgres_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 = $1")
123 .bind(name)
124 .fetch_optional(&mut *conn)
125 .await?;
126
127 Ok(zone)
128 }
129
130 async fn get_by_name_tx(
131 &self,
132 tx: &mut RepositoryTx<'_>,
133 name: &str,
134 ) -> Result<Option<Zone>, DatabaseError> {
135 let postgres_tx = match &mut tx.0 {
136 RepositoryTxKind::PostgreSQL(tx) => tx,
137 _ => {
138 return Err(DatabaseError::TransactionFailed(
139 "transaction kind mismatch (expected PostgreSQL)".to_string(),
140 ));
141 }
142 };
143
144 let zone = sqlx::query_as::<_, Zone>(
145 "SELECT id, name, primary_ns, admin_email, ttl, serial, refresh, retry, expire, minimum_ttl, created_at FROM zones WHERE name = $1 FOR UPDATE",
146 )
147 .bind(name)
148 .fetch_optional(&mut **postgres_tx)
149 .await?;
150
151 Ok(zone)
152 }
153
154 async fn get_all(&self) -> Result<Vec<Zone>, DatabaseError> {
155 let mut conn = self.pool.acquire().await?;
156
157 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")
158 .fetch_all(&mut *conn)
159 .await?;
160
161 Ok(zones)
162 }
163
164 async fn get_by_filter(&self, filter: ZoneFilter) -> Result<Vec<Zone>, DatabaseError> {
165 let mut conn = self.pool.acquire().await?;
166 let search = like_pattern(filter.search.as_deref());
167
168 let zones = sqlx::query_as::<_, Zone>(
169 r#"
170 SELECT id, name, primary_ns, admin_email, ttl, serial, refresh, retry, expire, minimum_ttl, created_at
171 FROM zones
172 WHERE ($1::TEXT IS NULL OR LOWER(name) = LOWER($2))
173 AND ($3::INT4 IS NULL OR id = $4)
174 AND ($5::TEXT IS NULL OR LOWER(primary_ns) = LOWER($6))
175 AND ($7::TEXT IS NULL OR LOWER(admin_email) = LOWER($8))
176 AND ($9::INT4 IS NULL OR ttl = $10)
177 AND ($11::INT4 IS NULL OR ttl >= $12)
178 AND ($13::INT4 IS NULL OR ttl <= $14)
179 AND ($15::INT4 IS NULL OR serial = $16)
180 AND (
181 $17::TEXT IS NULL
182 OR LOWER(name) LIKE LOWER($18)
183 OR LOWER(primary_ns) LIKE LOWER($19)
184 OR LOWER(admin_email) LIKE LOWER($20)
185 )
186 ORDER BY name
187 LIMIT $21 OFFSET $22
188 "#,
189 )
190 .bind(&filter.name)
191 .bind(&filter.name)
192 .bind(filter.id)
193 .bind(filter.id)
194 .bind(&filter.primary_ns)
195 .bind(&filter.primary_ns)
196 .bind(&filter.admin_email)
197 .bind(&filter.admin_email)
198 .bind(filter.ttl)
199 .bind(filter.ttl)
200 .bind(filter.min_ttl)
201 .bind(filter.min_ttl)
202 .bind(filter.max_ttl)
203 .bind(filter.max_ttl)
204 .bind(filter.serial)
205 .bind(filter.serial)
206 .bind(&search)
207 .bind(&search)
208 .bind(&search)
209 .bind(&search)
210 .bind(filter.limit.map(i64::from).unwrap_or(i64::MAX))
211 .bind(
212 filter
213 .offset
214 .map(|offset| i64::try_from(offset).unwrap_or(i64::MAX))
215 .unwrap_or(0),
216 )
217 .fetch_all(&mut *conn)
218 .await?;
219
220 Ok(zones)
221 }
222
223 async fn count_by_filter(&self, filter: ZoneFilter) -> Result<u64, DatabaseError> {
224 let mut conn = self.pool.acquire().await?;
225 let search = like_pattern(filter.search.as_deref());
226
227 let count = sqlx::query_scalar::<_, i64>(
228 r#"
229 SELECT COUNT(*)
230 FROM zones
231 WHERE ($1::TEXT IS NULL OR LOWER(name) = LOWER($2))
232 AND ($3::INT4 IS NULL OR id = $4)
233 AND ($5::TEXT IS NULL OR LOWER(primary_ns) = LOWER($6))
234 AND ($7::TEXT IS NULL OR LOWER(admin_email) = LOWER($8))
235 AND ($9::INT4 IS NULL OR ttl = $10)
236 AND ($11::INT4 IS NULL OR ttl >= $12)
237 AND ($13::INT4 IS NULL OR ttl <= $14)
238 AND ($15::INT4 IS NULL OR serial = $16)
239 AND (
240 $17::TEXT IS NULL
241 OR LOWER(name) LIKE LOWER($18)
242 OR LOWER(primary_ns) LIKE LOWER($19)
243 OR LOWER(admin_email) LIKE LOWER($20)
244 )
245 "#,
246 )
247 .bind(&filter.name)
248 .bind(&filter.name)
249 .bind(filter.id)
250 .bind(filter.id)
251 .bind(&filter.primary_ns)
252 .bind(&filter.primary_ns)
253 .bind(&filter.admin_email)
254 .bind(&filter.admin_email)
255 .bind(filter.ttl)
256 .bind(filter.ttl)
257 .bind(filter.min_ttl)
258 .bind(filter.min_ttl)
259 .bind(filter.max_ttl)
260 .bind(filter.max_ttl)
261 .bind(filter.serial)
262 .bind(filter.serial)
263 .bind(&search)
264 .bind(&search)
265 .bind(&search)
266 .bind(&search)
267 .fetch_one(&mut *conn)
268 .await?;
269
270 Ok(count as u64)
271 }
272
273 async fn update(&self, zone: Zone) -> Result<Zone, DatabaseError> {
274 let mut conn = self.pool.acquire().await?;
275
276 sqlx::query(
277 r#"
278 UPDATE zones
279 SET name = $1, primary_ns = $2, admin_email = $3,
280 ttl = $4, serial = $5, refresh = $6, retry = $7, expire = $8, minimum_ttl = $9
281 WHERE id = $10
282 "#,
283 )
284 .bind(&zone.name)
285 .bind(&zone.primary_ns)
286 .bind(&zone.admin_email)
287 .bind(zone.ttl)
288 .bind(zone.serial)
289 .bind(zone.refresh)
290 .bind(zone.retry)
291 .bind(zone.expire)
292 .bind(zone.minimum_ttl)
293 .bind(zone.id)
294 .execute(&mut *conn)
295 .await?;
296
297 Ok(zone)
298 }
299
300 async fn update_tx(
301 &self,
302 tx: &mut RepositoryTx<'_>,
303 zone: Zone,
304 ) -> Result<Zone, DatabaseError> {
305 let postgres_tx = match &mut tx.0 {
306 RepositoryTxKind::PostgreSQL(tx) => tx,
307 _ => {
308 return Err(DatabaseError::TransactionFailed(
309 "transaction kind mismatch (expected PostgreSQL)".to_string(),
310 ));
311 }
312 };
313
314 sqlx::query(
315 r#"
316 UPDATE zones
317 SET name = $1, primary_ns = $2, admin_email = $3,
318 ttl = $4, serial = $5, refresh = $6, retry = $7, expire = $8, minimum_ttl = $9
319 WHERE id = $10
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 **postgres_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 = $1")
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 postgres_tx = match &mut tx.0 {
351 RepositoryTxKind::PostgreSQL(tx) => tx,
352 _ => {
353 return Err(DatabaseError::TransactionFailed(
354 "transaction kind mismatch (expected PostgreSQL)".to_string(),
355 ));
356 }
357 };
358
359 sqlx::query("DELETE FROM zones WHERE id = $1")
360 .bind(id)
361 .execute(&mut **postgres_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}