cadmus_core/dictionary/monolingual/
db.rs

1//! Database access layer for monolingual dictionary metadata.
2//!
3//! Manages the `reader_dict_monolingual_metadata` table, which caches the
4//! API response from `https://www.reader-dict.com/api/v1/dictionaries`.
5//! Only monolingual entries (source language == target language) are stored.
6//!
7//! Also manages the `reader_dict_monolingual_installed` table, which tracks which version
8//! of each dictionary is currently installed on the device.
9
10use super::metadata::DictionaryEntry;
11use crate::db::runtime::RUNTIME;
12use crate::db::types::UnixTimestamp;
13use crate::db::Database;
14use anyhow::Error;
15use sqlx::SqlitePool;
16
17/// Database handle for monolingual dictionary tables.
18#[derive(Clone, Debug)]
19pub(super) struct Db {
20    pool: SqlitePool,
21}
22
23impl Db {
24    pub(super) fn new(database: &Database) -> Self {
25        Self {
26            pool: database.pool().clone(),
27        }
28    }
29
30    /// Inserts or replaces a single monolingual metadata entry.
31    ///
32    /// The `updated` date is stored as a Unix epoch integer (midnight UTC).
33    ///
34    /// # Errors
35    ///
36    /// Returns an error if the database write fails.
37    #[cfg_attr(feature = "tracing", tracing::instrument(skip(self, entry), fields(lang = %lang)))]
38    pub(super) fn upsert_entry(&self, lang: &str, entry: &DictionaryEntry) -> Result<(), Error> {
39        let updated: UnixTimestamp = entry.updated.into();
40        let cached_at = UnixTimestamp::now();
41        let formats = &entry.formats;
42        let words = entry.words as i64;
43
44        RUNTIME.block_on(async {
45            sqlx::query!(
46                r#"INSERT INTO reader_dict_monolingual_metadata
47                       (lang, formats, updated, words, cached_at)
48                   VALUES (?, ?, ?, ?, ?)
49                   ON CONFLICT(lang) DO UPDATE SET
50                       formats   = excluded.formats,
51                       updated   = excluded.updated,
52                       words     = excluded.words,
53                       cached_at = excluded.cached_at"#,
54                lang,
55                formats,
56                updated,
57                words,
58                cached_at,
59            )
60            .execute(&self.pool)
61            .await?;
62
63            tracing::debug!(lang, "upserted monolingual metadata entry");
64            Ok(())
65        })
66    }
67
68    /// Retrieves the cached metadata entry for a single language.
69    ///
70    /// Returns `None` if no entry for `lang` has been cached yet.
71    ///
72    /// # Errors
73    ///
74    /// Returns an error if the database query fails.
75    #[cfg_attr(feature = "tracing", tracing::instrument(skip(self), fields(lang = %lang)))]
76    pub(super) fn get_entry(&self, lang: &str) -> Result<Option<DictionaryEntry>, Error> {
77        RUNTIME.block_on(async {
78            let row = sqlx::query!(
79                r#"SELECT formats, updated as "updated: UnixTimestamp", words
80                   FROM reader_dict_monolingual_metadata
81                   WHERE lang = ?"#,
82                lang,
83            )
84            .fetch_optional(&self.pool)
85            .await?;
86
87            Ok(row.map(|r| DictionaryEntry {
88                formats: r.formats,
89                updated: r.updated.into(),
90                words: r.words as u64,
91            }))
92        })
93    }
94
95    /// Retrieves all cached monolingual metadata entries.
96    ///
97    /// Returns an empty `Vec` if no entries have been cached yet.
98    ///
99    /// # Errors
100    ///
101    /// Returns an error if the database query fails or any stored `updated`
102    /// timestamp cannot be converted to a `NaiveDate`.
103    #[cfg_attr(feature = "tracing", tracing::instrument(skip(self)))]
104    pub(super) fn get_all_entries(&self) -> Result<Vec<(String, DictionaryEntry)>, Error> {
105        RUNTIME.block_on(async {
106            let rows = sqlx::query!(
107                r#"SELECT lang, formats, updated as "updated: UnixTimestamp", words
108                   FROM reader_dict_monolingual_metadata"#,
109            )
110            .fetch_all(&self.pool)
111            .await?;
112
113            rows.into_iter()
114                .map(|r| {
115                    Ok((
116                        r.lang,
117                        DictionaryEntry {
118                            formats: r.formats,
119                            updated: r.updated.into(),
120                            words: r.words as u64,
121                        },
122                    ))
123                })
124                .collect()
125        })
126    }
127
128    /// Returns the most recent `cached_at` value across all metadata entries.
129    ///
130    /// Used to determine whether the metadata cache is stale relative to
131    /// the API's `Last-Modified` header.
132    ///
133    /// # Errors
134    ///
135    /// Returns an error if the database query fails.
136    #[cfg_attr(feature = "tracing", tracing::instrument(skip(self)))]
137    pub(super) fn get_most_recent_cached_at(&self) -> Result<Option<UnixTimestamp>, Error> {
138        RUNTIME.block_on(async {
139            let result = sqlx::query_scalar!(
140                r#"SELECT MAX(cached_at) as "cached_at: UnixTimestamp"
141                   FROM reader_dict_monolingual_metadata"#
142            )
143            .fetch_one(&self.pool)
144            .await?;
145
146            Ok(result)
147        })
148    }
149
150    /// Records that a dictionary was installed with the given version.
151    ///
152    /// If a record already exists for `lang`, it is updated in place.
153    ///
154    /// # Errors
155    ///
156    /// Returns an error if the database write fails.
157    #[cfg_attr(feature = "tracing", tracing::instrument(skip(self), fields(lang = %lang)))]
158    pub(super) fn record_install(
159        &self,
160        lang: &str,
161        installed_version: UnixTimestamp,
162    ) -> Result<(), Error> {
163        let installed_at = UnixTimestamp::now();
164
165        RUNTIME.block_on(async {
166            sqlx::query!(
167                r#"INSERT INTO reader_dict_monolingual_installed (lang, installed_at, installed_version)
168                   VALUES (?, ?, ?)
169                   ON CONFLICT(lang) DO UPDATE SET
170                       installed_at      = excluded.installed_at,
171                       installed_version = excluded.installed_version"#,
172                lang,
173                installed_at,
174                installed_version,
175            )
176            .execute(&self.pool)
177            .await?;
178
179            tracing::debug!(lang, "recorded dictionary install");
180            Ok(())
181        })
182    }
183
184    /// Removes the installed record for a language.
185    ///
186    /// Called when a dictionary is deleted from the device.
187    ///
188    /// # Errors
189    ///
190    /// Returns an error if the database write fails.
191    #[cfg_attr(feature = "tracing", tracing::instrument(skip(self), fields(lang = %lang)))]
192    pub(super) fn remove_installed(&self, lang: &str) -> Result<(), Error> {
193        RUNTIME.block_on(async {
194            sqlx::query!(
195                r#"DELETE FROM reader_dict_monolingual_installed WHERE lang = ?"#,
196                lang
197            )
198            .execute(&self.pool)
199            .await?;
200
201            tracing::debug!(lang, "removed dictionary installed record");
202            Ok(())
203        })
204    }
205
206    /// Returns `true` if a newer version of the dictionary is available.
207    ///
208    /// Compares `updated` from `reader_dict_monolingual_metadata` against
209    /// `installed_version` from `reader_dict_monolingual_installed` via a single SQL JOIN.
210    /// Returns `false` if the language is not installed or has no metadata.
211    ///
212    /// # Errors
213    ///
214    /// Returns an error if the database query fails.
215    #[cfg_attr(feature = "tracing", tracing::instrument(skip(self), fields(lang = %lang), ret(level=tracing::Level::TRACE)))]
216    pub(super) fn is_update_available(&self, lang: &str) -> Result<bool, Error> {
217        RUNTIME.block_on(async {
218            let result = sqlx::query_scalar!(
219                r#"SELECT EXISTS(
220                    SELECT 1
221                    FROM reader_dict_monolingual_metadata m
222                    JOIN reader_dict_monolingual_installed i ON m.lang = i.lang
223                    WHERE m.lang = ? AND m.updated > i.installed_version
224                ) as "exists: bool""#,
225                lang
226            )
227            .fetch_one(&self.pool)
228            .await?;
229
230            Ok(result)
231        })
232    }
233}
234
235#[cfg(test)]
236mod tests {
237    use chrono::NaiveDate;
238
239    use super::*;
240
241    fn create_test_db() -> (Database, Db) {
242        let database = Database::new(":memory:").expect("failed to create in-memory database");
243        database.migrate().expect("failed to run migrations");
244        let db = Db::new(&database);
245        (database, db)
246    }
247
248    fn make_entry(year: i32, month: u32, day: u32, words: u64) -> DictionaryEntry {
249        DictionaryEntry {
250            formats: "df,dic,dictorg,kobo,mobi,stardict".to_string(),
251            updated: NaiveDate::from_ymd_opt(year, month, day).unwrap(),
252            words,
253        }
254    }
255
256    #[test]
257    fn test_upsert_and_get_roundtrip() {
258        let (_database, db) = create_test_db();
259        let entry = make_entry(2026, 4, 1, 1_381_375);
260
261        db.upsert_entry("en", &entry)
262            .expect("upsert should succeed");
263
264        let all = db.get_all_entries().expect("get_all should not fail");
265        assert_eq!(all.len(), 1);
266        let (lang, fetched) = &all[0];
267        assert_eq!(lang, "en");
268        assert_eq!(fetched.formats, entry.formats);
269        assert_eq!(
270            fetched.updated,
271            NaiveDate::from_ymd_opt(2026, 4, 1).unwrap()
272        );
273        assert_eq!(fetched.words, 1_381_375);
274    }
275
276    #[test]
277    fn test_upsert_overwrites_existing_entry() {
278        let (_database, db) = create_test_db();
279
280        db.upsert_entry("en", &make_entry(2026, 1, 1, 100))
281            .expect("upsert should succeed");
282        db.upsert_entry("en", &make_entry(2026, 4, 1, 1_381_375))
283            .expect("upsert should succeed");
284
285        let all = db.get_all_entries().expect("get_all should not fail");
286        assert_eq!(all.len(), 1);
287        let (_, fetched) = &all[0];
288        assert_eq!(
289            fetched.updated,
290            NaiveDate::from_ymd_opt(2026, 4, 1).unwrap()
291        );
292        assert_eq!(fetched.words, 1_381_375);
293    }
294
295    #[test]
296    fn test_get_all_entries_returns_all() {
297        let (_database, db) = create_test_db();
298
299        db.upsert_entry("en", &make_entry(2026, 4, 1, 1_381_375))
300            .expect("upsert should succeed");
301        db.upsert_entry("fr", &make_entry(2026, 3, 1, 2_050_655))
302            .expect("upsert should succeed");
303
304        let all = db.get_all_entries().expect("get_all should not fail");
305        assert_eq!(all.len(), 2);
306
307        let langs: Vec<&str> = all.iter().map(|(l, _)| l.as_str()).collect();
308        assert!(langs.contains(&"en"));
309        assert!(langs.contains(&"fr"));
310    }
311
312    #[test]
313    fn test_get_all_entries_empty() {
314        let (_database, db) = create_test_db();
315        let all = db.get_all_entries().expect("get_all should not fail");
316        assert!(all.is_empty());
317    }
318
319    #[test]
320    fn test_get_most_recent_cached_at_empty() {
321        let (_database, db) = create_test_db();
322        let result = db
323            .get_most_recent_cached_at()
324            .expect("should not error on empty table");
325        assert!(result.is_none());
326    }
327
328    #[test]
329    fn test_get_most_recent_cached_at_returns_max() {
330        let (_database, db) = create_test_db();
331
332        db.upsert_entry("en", &make_entry(2026, 4, 1, 1_381_375))
333            .expect("upsert should succeed");
334        db.upsert_entry("fr", &make_entry(2026, 3, 1, 2_050_655))
335            .expect("upsert should succeed");
336
337        let result = db.get_most_recent_cached_at().expect("should not error");
338        assert!(result.is_some());
339    }
340
341    #[test]
342    fn test_remove_installed() {
343        let (_database, db) = create_test_db();
344        let version = UnixTimestamp::now();
345
346        db.record_install("en", version)
347            .expect("record_install should succeed");
348        db.remove_installed("en")
349            .expect("remove_installed should succeed");
350
351        let result = db.is_update_available("en").expect("should not error");
352        assert!(!result);
353    }
354
355    #[test]
356    fn test_is_update_available_no_update() {
357        let (_database, db) = create_test_db();
358
359        let date = NaiveDate::from_ymd_opt(2026, 4, 1).unwrap();
360        let version: UnixTimestamp = date.into();
361
362        db.upsert_entry("en", &make_entry(2026, 4, 1, 1_381_375))
363            .expect("upsert should succeed");
364        db.record_install("en", version)
365            .expect("record_install should succeed");
366
367        let result = db.is_update_available("en").expect("should not error");
368        assert!(!result);
369    }
370
371    #[test]
372    fn test_is_update_available_with_update() {
373        let (_database, db) = create_test_db();
374
375        let old_version: UnixTimestamp = NaiveDate::from_ymd_opt(2026, 1, 1).unwrap().into();
376
377        db.upsert_entry("en", &make_entry(2026, 4, 1, 1_381_375))
378            .expect("upsert should succeed");
379        db.record_install("en", old_version)
380            .expect("record_install should succeed");
381
382        let result = db.is_update_available("en").expect("should not error");
383        assert!(result);
384    }
385
386    #[test]
387    fn test_is_update_available_not_installed() {
388        let (_database, db) = create_test_db();
389
390        db.upsert_entry("en", &make_entry(2026, 4, 1, 1_381_375))
391            .expect("upsert should succeed");
392
393        let result = db.is_update_available("en").expect("should not error");
394        assert!(!result);
395    }
396}
cadmus_core/dictionary/monolingual/db.rs

cadmus_core/dictionary/monolingual/
db.rs
