1use std::sync::Arc;
33
34use async_trait::async_trait;
35use chrono::{DateTime, Utc};
36use rusqlite::types::Value as SqlValue;
37
38use wabot_feature_async::cron_job::{CronJob, CronJobData};
39use wabot_feature_async::job::{Job, JobData};
40use wabot_feature_async::repository::{CronJobRepository, JobRepository};
41use wabot_feature_async::types::AsyncError;
42use wabot_feature_sqlite::{SqliteDatabase, SqliteError, SqliteJsonbStore};
43
44pub const JOBS_TABLE: &str = "jobs";
47pub const CRON_JOBS_TABLE: &str = "cron_jobs";
48
49macro_rules! field {
56 ($name:literal) => {
57 concat!("\"data\"->>'$.\"", $name, "\"'")
58 };
59}
60
61pub struct SqliteJobRepository {
64 store: SqliteJsonbStore<JobData>,
65}
66
67impl SqliteJobRepository {
68 pub fn new(database: SqliteDatabase) -> Self {
69 Self::with_table(database, JOBS_TABLE)
70 }
71
72 pub fn with_table(database: SqliteDatabase, table: &str) -> Self {
73 let store = SqliteJsonbStore::new(database, table)
74 .with_index(concat!(
78 "CREATE INDEX IF NOT EXISTS jobs_pending_idx ON \"jobs\" (",
79 field!("scheduledAt"),
80 ") WHERE ",
81 field!("startedAt"),
82 " IS NULL AND ",
83 field!("successAt"),
84 " IS NULL AND ",
85 field!("failedAt"),
86 " IS NULL"
87 ))
88 .with_index(concat!(
89 "CREATE INDEX IF NOT EXISTS jobs_dedup_idx ON \"jobs\" (",
90 field!("commandName"),
91 ", ",
92 field!("dedupKey"),
93 ")"
94 ))
95 .with_index(concat!(
96 "CREATE INDEX IF NOT EXISTS jobs_running_idx ON \"jobs\" (",
97 field!("commandName"),
98 ") WHERE ",
99 field!("startedAt"),
100 " IS NOT NULL AND ",
101 field!("successAt"),
102 " IS NULL AND ",
103 field!("failedAt"),
104 " IS NULL"
105 ));
106 Self { store }
107 }
108
109 pub fn store(&self) -> &SqliteJsonbStore<JobData> {
112 &self.store
113 }
114
115 pub async fn ensure_ready(&self) -> Result<(), AsyncError> {
117 self.store.ensure_ready().await.map_err(to_async_error)
118 }
119}
120
121#[async_trait]
122impl JobRepository for SqliteJobRepository {
123 async fn find(&self, id: &str) -> Result<Option<Job>, AsyncError> {
124 self.store.find_by_id(id).await.map_err(to_async_error)
125 }
126
127 async fn create(&self, job: &Job) -> Result<(), AsyncError> {
128 self.store.save(job).await.map_err(to_async_error)
132 }
133
134 async fn update(&self, job: &Job) -> Result<(), AsyncError> {
135 self.store.save(job).await.map_err(to_async_error)
136 }
137
138 async fn delete(&self, job: &Job) -> Result<(), AsyncError> {
139 self.store.delete(job.id()).await.map_err(to_async_error)
140 }
141
142 async fn find_pending_for_run_from(
143 &self,
144 as_of: DateTime<Utc>,
145 limit: usize,
146 ) -> Result<Vec<Job>, AsyncError> {
147 let sql = format!(
152 "SELECT \"data\" FROM {t} \
153 WHERE {scheduled} IS NOT NULL \
154 AND {scheduled} <= ?1 \
155 AND {started} IS NULL \
156 AND {success} IS NULL \
157 AND {failed} IS NULL \
158 ORDER BY {scheduled} ASC \
159 LIMIT ?2",
160 t = self.store.table(),
161 scheduled = field!("scheduledAt"),
162 started = field!("startedAt"),
163 success = field!("successAt"),
164 failed = field!("failedAt"),
165 );
166 self.store
167 .query(
168 &sql,
169 vec![
170 SqlValue::Integer(as_of.timestamp_millis()),
171 SqlValue::Integer(limit as i64),
172 ],
173 )
174 .await
175 .map_err(to_async_error)
176 }
177
178 async fn find_running(&self) -> Result<Vec<Job>, AsyncError> {
179 let sql = format!(
180 "SELECT \"data\" FROM {t} \
181 WHERE {started} IS NOT NULL \
182 AND {success} IS NULL \
183 AND {failed} IS NULL",
184 t = self.store.table(),
185 started = field!("startedAt"),
186 success = field!("successAt"),
187 failed = field!("failedAt"),
188 );
189 self.store
190 .query(&sql, Vec::new())
191 .await
192 .map_err(to_async_error)
193 }
194
195 async fn count_running_by_command(&self, command_name: &str) -> Result<usize, AsyncError> {
196 let sql = format!(
197 "SELECT COUNT(*) FROM {t} \
198 WHERE {started} IS NOT NULL \
199 AND {success} IS NULL \
200 AND {failed} IS NULL \
201 AND {command} = ?1",
202 t = self.store.table(),
203 started = field!("startedAt"),
204 success = field!("successAt"),
205 failed = field!("failedAt"),
206 command = field!("commandName"),
207 );
208 let count = self
209 .store
210 .count(&sql, vec![SqlValue::Text(command_name.to_string())])
211 .await
212 .map_err(to_async_error)?;
213 Ok(count.max(0) as usize)
214 }
215
216 async fn find_active_by_dedup_key(
217 &self,
218 command_name: &str,
219 dedup_key: &str,
220 succeeded_since_ms: i64,
221 ) -> Result<Option<Job>, AsyncError> {
222 let sql = format!(
223 "SELECT \"data\" FROM {t} \
224 WHERE {command} = ?1 \
225 AND {dedup} = ?2 \
226 AND {failed} IS NULL \
227 AND ({success} IS NULL OR {success} >= ?3) \
228 ORDER BY \"created_at\" DESC \
229 LIMIT 1",
230 t = self.store.table(),
231 command = field!("commandName"),
232 dedup = field!("dedupKey"),
233 failed = field!("failedAt"),
234 success = field!("successAt"),
235 );
236 self.store
237 .query_opt(
238 &sql,
239 vec![
240 SqlValue::Text(command_name.to_string()),
241 SqlValue::Text(dedup_key.to_string()),
242 SqlValue::Integer(succeeded_since_ms),
243 ],
244 )
245 .await
246 .map_err(to_async_error)
247 }
248}
249
250impl wabot_core::injection::IsSingleton for SqliteJobRepository {
251 fn register_in(c: &wabot_core::injection::Container) {
252 c.register_singleton_factory::<SqliteJobRepository, _>(|c2| {
253 let database: Arc<SqliteDatabase> = c2.resolve();
254 Arc::new(SqliteJobRepository::new(database.as_ref().clone()))
255 });
256 }
257}
258
259pub struct SqliteCronJobRepository {
262 store: SqliteJsonbStore<CronJobData>,
263}
264
265impl SqliteCronJobRepository {
266 pub fn new(database: SqliteDatabase) -> Self {
267 Self::with_table(database, CRON_JOBS_TABLE)
268 }
269
270 pub fn with_table(database: SqliteDatabase, table: &str) -> Self {
271 let store = SqliteJsonbStore::new(database, table)
272 .with_index(concat!(
273 "CREATE INDEX IF NOT EXISTS cron_jobs_next_run_idx ON \"cron_jobs\" (",
274 field!("nextRunAt"),
275 ")"
276 ))
277 .with_index(concat!(
282 "CREATE UNIQUE INDEX IF NOT EXISTS cron_jobs_name_idx ON \"cron_jobs\" (",
283 field!("name"),
284 ")"
285 ));
286 Self { store }
287 }
288
289 pub fn store(&self) -> &SqliteJsonbStore<CronJobData> {
290 &self.store
291 }
292
293 pub async fn ensure_ready(&self) -> Result<(), AsyncError> {
294 self.store.ensure_ready().await.map_err(to_async_error)
295 }
296}
297
298#[async_trait]
299impl CronJobRepository for SqliteCronJobRepository {
300 async fn find(&self, id: &str) -> Result<Option<CronJob>, AsyncError> {
301 self.store.find_by_id(id).await.map_err(to_async_error)
302 }
303
304 async fn find_by_name(&self, name: &str) -> Result<Option<CronJob>, AsyncError> {
305 let sql = format!(
306 "SELECT \"data\" FROM {t} WHERE {name} = ?1 LIMIT 1",
307 t = self.store.table(),
308 name = field!("name"),
309 );
310 self.store
311 .query_opt(&sql, vec![SqlValue::Text(name.to_string())])
312 .await
313 .map_err(to_async_error)
314 }
315
316 async fn create(&self, cron: &CronJob) -> Result<(), AsyncError> {
317 self.store.save(cron).await.map_err(to_async_error)
318 }
319
320 async fn update(&self, cron: &CronJob) -> Result<(), AsyncError> {
321 self.store.save(cron).await.map_err(to_async_error)
322 }
323
324 async fn find_due(&self, now: DateTime<Utc>) -> Result<Vec<CronJob>, AsyncError> {
325 let sql = format!(
329 "SELECT \"data\" FROM {t} \
330 WHERE {next} IS NOT NULL \
331 AND {next} <= ?1 \
332 AND {enabled} = 1 \
333 ORDER BY {next} ASC",
334 t = self.store.table(),
335 next = field!("nextRunAt"),
336 enabled = field!("enabled"),
337 );
338 self.store
339 .query(&sql, vec![SqlValue::Integer(now.timestamp_millis())])
340 .await
341 .map_err(to_async_error)
342 }
343}
344
345impl wabot_core::injection::IsSingleton for SqliteCronJobRepository {
346 fn register_in(c: &wabot_core::injection::Container) {
347 c.register_singleton_factory::<SqliteCronJobRepository, _>(|c2| {
348 let database: Arc<SqliteDatabase> = c2.resolve();
349 Arc::new(SqliteCronJobRepository::new(database.as_ref().clone()))
350 });
351 }
352}
353
354fn to_async_error(error: SqliteError) -> AsyncError {
358 AsyncError::Storage(error.to_string())
359}
360
361#[cfg(test)]
362mod tests {
363 use super::*;
364 use serde_json::json;
365 use wabot_core::entity::EntityData;
366 use wabot_feature_async::cron_job::{self, CronJobData};
367 use wabot_feature_async::job::new_job;
368 use wabot_feature_async::types::MisfirePolicy;
369
370 fn ts(ms: i64) -> DateTime<Utc> {
371 DateTime::<Utc>::from_timestamp_millis(ms).unwrap()
372 }
373
374 fn job(command: &str, scheduled_at: Option<i64>, dedup_key: Option<&str>) -> Job {
375 new_job(JobData {
376 base: EntityData::default(),
377 command_name: command.into(),
378 command_data: json!({}),
379 scheduled_at,
380 started_at: None,
381 success_at: None,
382 failed_at: None,
383 retry_delays_seconds: None,
384 intent_number: None,
385 error: None,
386 acceptable_running_time_seconds: None,
387 stuck_retry_attempts: None,
388 dedup_key: dedup_key.map(str::to_string),
389 actor: None,
390 request_id: None,
391 })
392 }
393
394 async fn jobs() -> SqliteJobRepository {
395 let repository = SqliteJobRepository::new(SqliteDatabase::in_memory().expect("open"));
396 repository.ensure_ready().await.expect("ready");
397 repository
398 }
399
400 #[tokio::test]
401 async fn a_job_round_trips() {
402 let repository = jobs().await;
403 let job = job("ping", Some(1_000), None);
404 repository.create(&job).await.expect("create");
405
406 let found = repository
407 .find(job.id())
408 .await
409 .expect("find")
410 .expect("present");
411 assert_eq!(found.data().command_name, "ping");
412
413 repository.delete(&job).await.expect("delete");
414 assert!(repository.find(job.id()).await.expect("find").is_none());
415 }
416
417 #[tokio::test]
420 async fn creating_the_same_job_twice_is_not_an_error() {
421 let repository = jobs().await;
422 let job = job("ping", Some(1), None);
423 repository.create(&job).await.expect("create");
424 repository.create(&job).await.expect("create again");
425 }
426
427 #[tokio::test]
430 async fn pending_finds_only_unstarted_jobs_that_are_due() {
431 let repository = jobs().await;
432
433 repository
434 .create(&job("due", Some(1_000), None))
435 .await
436 .unwrap();
437 repository
438 .create(&job("later", Some(9_000), None))
439 .await
440 .unwrap();
441 repository
442 .create(&job("unscheduled", None, None))
443 .await
444 .unwrap();
445
446 let mut started = job("started", Some(1_000), None);
447 started.update(|data| data.started_at = Some(1_500));
448 repository.create(&started).await.unwrap();
449
450 let mut done = job("done", Some(1_000), None);
451 done.update(|data| data.success_at = Some(1_500));
452 repository.create(&done).await.unwrap();
453
454 let pending = repository
455 .find_pending_for_run_from(ts(2_000), 10)
456 .await
457 .expect("pending");
458 assert_eq!(
459 pending
460 .iter()
461 .map(|j| j.data().command_name.as_str())
462 .collect::<Vec<_>>(),
463 vec!["due"]
464 );
465 }
466
467 #[tokio::test]
471 async fn pending_orders_numerically_not_lexicographically() {
472 let repository = jobs().await;
473 for at in [9_000, 10_000, 800] {
474 repository
475 .create(&job(&format!("j{at}"), Some(at), None))
476 .await
477 .unwrap();
478 }
479
480 let pending = repository
481 .find_pending_for_run_from(ts(20_000), 2)
482 .await
483 .expect("pending");
484 assert_eq!(
485 pending
486 .iter()
487 .map(|j| j.data().scheduled_at.unwrap())
488 .collect::<Vec<_>>(),
489 vec![800, 9_000],
490 "lexicographic ordering would have put 10000 before 800 and 9000"
491 );
492 }
493
494 #[tokio::test]
495 async fn running_jobs_are_counted_per_command() {
496 let repository = jobs().await;
497
498 for command in ["build", "build", "deploy"] {
499 let mut running = job(command, Some(1), None);
500 running.update(|data| data.started_at = Some(2));
501 repository.create(&running).await.unwrap();
502 }
503 let mut finished = job("build", Some(1), None);
505 finished.update(|data| {
506 data.started_at = Some(2);
507 data.success_at = Some(3);
508 });
509 repository.create(&finished).await.unwrap();
510
511 assert_eq!(repository.find_running().await.unwrap().len(), 3);
512 assert_eq!(
513 repository.count_running_by_command("build").await.unwrap(),
514 2
515 );
516 assert_eq!(
517 repository.count_running_by_command("absent").await.unwrap(),
518 0
519 );
520 }
521
522 #[tokio::test]
523 async fn dedup_finds_an_active_job_and_respects_the_success_window() {
524 let repository = jobs().await;
525
526 repository
527 .create(&job("send", Some(1), Some("key-1")))
528 .await
529 .unwrap();
530 assert!(repository
531 .find_active_by_dedup_key("send", "key-1", 0)
532 .await
533 .unwrap()
534 .is_some());
535 assert!(
536 repository
537 .find_active_by_dedup_key("send", "other", 0)
538 .await
539 .unwrap()
540 .is_none(),
541 "a different key is a different job"
542 );
543
544 let mut old = job("send", Some(1), Some("key-2"));
546 old.update(|data| data.success_at = Some(1_000));
547 repository.create(&old).await.unwrap();
548 assert!(repository
549 .find_active_by_dedup_key("send", "key-2", 5_000)
550 .await
551 .unwrap()
552 .is_none());
553 assert!(repository
554 .find_active_by_dedup_key("send", "key-2", 500)
555 .await
556 .unwrap()
557 .is_some());
558
559 let mut failed = job("send", Some(1), Some("key-3"));
561 failed.update(|data| data.failed_at = Some(1_000));
562 repository.create(&failed).await.unwrap();
563 assert!(repository
564 .find_active_by_dedup_key("send", "key-3", 0)
565 .await
566 .unwrap()
567 .is_none());
568 }
569
570 fn cron(name: &str, next_run_at: Option<i64>, enabled: bool) -> CronJob {
571 cron_job::new_cron_job(CronJobData {
572 base: EntityData::default(),
573 name: name.into(),
574 command_name: "ping".into(),
575 command_data: None,
576 cron: "0 0 * * * *".into(),
577 enabled,
578 last_run_at: None,
579 next_run_at,
580 max_running_jobs: Some(1),
581 misfire_policy: Some(MisfirePolicy::RunOnce),
582 retry_delays_seconds: None,
583 acceptable_running_time_seconds: None,
584 stuck_retry_attempts: None,
585 })
586 .unwrap()
587 }
588
589 async fn crons() -> SqliteCronJobRepository {
590 let repository = SqliteCronJobRepository::new(SqliteDatabase::in_memory().expect("open"));
591 repository.ensure_ready().await.expect("ready");
592 repository
593 }
594
595 #[tokio::test]
596 async fn a_cron_round_trips_and_is_findable_by_name() {
597 let repository = crons().await;
598 let cron = cron("nightly", Some(1_000), true);
599 repository.create(&cron).await.expect("create");
600
601 assert_eq!(
602 repository
603 .find_by_name("nightly")
604 .await
605 .expect("find")
606 .expect("present")
607 .id(),
608 cron.id()
609 );
610 assert!(repository.find_by_name("absent").await.unwrap().is_none());
611 }
612
613 #[tokio::test]
614 async fn due_crons_exclude_disabled_and_future_ones() {
615 let repository = crons().await;
616 repository
617 .create(&cron("due", Some(1_000), true))
618 .await
619 .unwrap();
620 repository
621 .create(&cron("later", Some(9_000), true))
622 .await
623 .unwrap();
624 repository
625 .create(&cron("off", Some(1_000), false))
626 .await
627 .unwrap();
628 repository.create(&cron("never", None, true)).await.unwrap();
629
630 let due = repository.find_due(ts(2_000)).await.expect("due");
631 assert_eq!(
632 due.iter()
633 .map(|c| c.data().name.as_str())
634 .collect::<Vec<_>>(),
635 vec!["due"],
636 "a JSON `true` reads back as 1, and a disabled cron must not fire"
637 );
638 }
639
640 #[tokio::test]
643 async fn two_crons_cannot_share_a_name() {
644 let repository = crons().await;
645 repository
646 .create(&cron("nightly", Some(1), true))
647 .await
648 .unwrap();
649 assert!(
650 repository
651 .create(&cron("nightly", Some(1), true))
652 .await
653 .is_err(),
654 "the unique index refused the duplicate"
655 );
656 }
657}