grabcli_sqlite/
db.rs

1//! Opening the cache database: file permissions, pragmas, ordered schema
2//! migrations, and cheap housekeeping on open.
3
4use std::fs;
5use std::path::Path;
6use std::time::Duration;
7
8use rusqlite::{Connection, OptionalExtension};
9
10use grabcli_core::error::Error;
11
12use crate::err::{DbError, Result};
13
14/// The database file name inside the cache dir.
15pub const DB_FILE: &str = "cache.sqlite";
16
17/// How many expired response rows to delete when the database is opened. Keeps
18/// opening cheap; `grab cache prune` removes the rest.
19const PRUNE_ON_OPEN_LIMIT: i64 = 200;
20
21/// Ordered schema migrations. Index `i` upgrades version `i` to `i + 1`. Never
22/// edit a released migration; append a new one.
23pub(crate) const MIGRATIONS: &[&str] = &[SCHEMA_V1];
24
25/// Version 1: raw responses, the normalized catalog with FTS5 indexes, and the
26/// image store index.
27const SCHEMA_V1: &str = r#"
28-- Raw upstream responses for read-only browse calls. `key` is the SHA-256 of
29-- `identity` (namespace, kind, endpoint, params incl. the rounded location).
30CREATE TABLE entries (
31    key        TEXT PRIMARY KEY,
32    namespace  TEXT NOT NULL,
33    kind       TEXT NOT NULL,
34    identity   TEXT NOT NULL,
35    body       TEXT NOT NULL,
36    fetched_at INTEGER NOT NULL,
37    expires_at INTEGER NOT NULL,
38    etag       TEXT
39);
40CREATE INDEX entries_expires ON entries (expires_at);
41CREATE INDEX entries_kind ON entries (namespace, kind);
42
43-- Restaurants and stores seen in any list/search/menu response. `rid` is a
44-- stable integer rowid for the external-content FTS tables.
45CREATE TABLE restaurants (
46    rid                INTEGER PRIMARY KEY,
47    id                 TEXT NOT NULL UNIQUE,
48    namespace          TEXT NOT NULL,
49    name               TEXT NOT NULL,
50    chain_name         TEXT,
51    branch_name        TEXT,
52    business_type      TEXT,
53    cuisines           TEXT,
54    rating             REAL,
55    vote_count         INTEGER,
56    price_level        INTEGER,
57    lat                REAL,
58    lon                REAL,
59    delivery_fee_minor INTEGER,
60    delivery_eta_min   INTEGER,
61    has_promo          INTEGER,
62    promo_text         TEXT,
63    free_delivery      INTEGER,
64    open_now           INTEGER,
65    opening_hours      TEXT,
66    distance_km        REAL,
67    photo_url          TEXT,
68    fetched_at         INTEGER NOT NULL,
69    raw_json           TEXT
70);
71CREATE INDEX restaurants_latlon ON restaurants (lat, lon);
72CREATE INDEX restaurants_fetched ON restaurants (fetched_at);
73
74-- Where a restaurant was seen from: distance/ETA/fee depend on the delivery
75-- location, so they are kept per searched location bucket (~110 m).
76CREATE TABLE restaurant_locations (
77    restaurant_id      TEXT NOT NULL,
78    bucket             TEXT NOT NULL,
79    search_lat         REAL NOT NULL,
80    search_lon         REAL NOT NULL,
81    distance_km        REAL,
82    delivery_eta_min   INTEGER,
83    delivery_fee_minor INTEGER,
84    free_delivery      INTEGER,
85    fetched_at         INTEGER NOT NULL,
86    PRIMARY KEY (restaurant_id, bucket)
87);
88CREATE INDEX restaurant_locations_pos ON restaurant_locations (search_lat, search_lon);
89
90CREATE TABLE menu_categories (
91    restaurant_id TEXT NOT NULL,
92    id            TEXT NOT NULL,
93    name          TEXT NOT NULL,
94    sort          INTEGER NOT NULL,
95    fetched_at    INTEGER NOT NULL,
96    raw_json      TEXT,
97    PRIMARY KEY (restaurant_id, id)
98);
99
100CREATE TABLE menu_items (
101    rid                    INTEGER PRIMARY KEY,
102    id                     TEXT NOT NULL UNIQUE,
103    restaurant_id          TEXT NOT NULL,
104    category_id            TEXT,
105    name                   TEXT NOT NULL,
106    description            TEXT,
107    price_minor            INTEGER,
108    discounted_price_minor INTEGER,
109    available              INTEGER,
110    image_url              TEXT,
111    image_hash             TEXT,
112    fetched_at             INTEGER NOT NULL,
113    raw_json               TEXT
114);
115CREATE INDEX menu_items_restaurant ON menu_items (restaurant_id);
116CREATE INDEX menu_items_price ON menu_items (price_minor);
117CREATE INDEX menu_items_fetched ON menu_items (fetched_at);
118
119-- Word index (diacritic-insensitive, prefix queries) and trigram index
120-- (substring matches such as "isig" in "sisig"), both external-content over
121-- the tables above and kept in sync by triggers.
122CREATE VIRTUAL TABLE restaurants_fts USING fts5(
123    name, cuisines, content='restaurants', content_rowid='rid',
124    tokenize='unicode61 remove_diacritics 2'
125);
126CREATE VIRTUAL TABLE restaurants_tri USING fts5(
127    name, cuisines, content='restaurants', content_rowid='rid',
128    tokenize='trigram remove_diacritics 1'
129);
130CREATE TRIGGER restaurants_ai AFTER INSERT ON restaurants BEGIN
131    INSERT INTO restaurants_fts (rowid, name, cuisines) VALUES (new.rid, new.name, new.cuisines);
132    INSERT INTO restaurants_tri (rowid, name, cuisines) VALUES (new.rid, new.name, new.cuisines);
133END;
134CREATE TRIGGER restaurants_ad AFTER DELETE ON restaurants BEGIN
135    INSERT INTO restaurants_fts (restaurants_fts, rowid, name, cuisines) VALUES ('delete', old.rid, old.name, old.cuisines);
136    INSERT INTO restaurants_tri (restaurants_tri, rowid, name, cuisines) VALUES ('delete', old.rid, old.name, old.cuisines);
137END;
138CREATE TRIGGER restaurants_au AFTER UPDATE OF name, cuisines ON restaurants BEGIN
139    INSERT INTO restaurants_fts (restaurants_fts, rowid, name, cuisines) VALUES ('delete', old.rid, old.name, old.cuisines);
140    INSERT INTO restaurants_tri (restaurants_tri, rowid, name, cuisines) VALUES ('delete', old.rid, old.name, old.cuisines);
141    INSERT INTO restaurants_fts (rowid, name, cuisines) VALUES (new.rid, new.name, new.cuisines);
142    INSERT INTO restaurants_tri (rowid, name, cuisines) VALUES (new.rid, new.name, new.cuisines);
143END;
144
145CREATE VIRTUAL TABLE menu_items_fts USING fts5(
146    name, description, content='menu_items', content_rowid='rid',
147    tokenize='unicode61 remove_diacritics 2'
148);
149CREATE VIRTUAL TABLE menu_items_tri USING fts5(
150    name, description, content='menu_items', content_rowid='rid',
151    tokenize='trigram remove_diacritics 1'
152);
153CREATE TRIGGER menu_items_ai AFTER INSERT ON menu_items BEGIN
154    INSERT INTO menu_items_fts (rowid, name, description) VALUES (new.rid, new.name, new.description);
155    INSERT INTO menu_items_tri (rowid, name, description) VALUES (new.rid, new.name, new.description);
156END;
157CREATE TRIGGER menu_items_ad AFTER DELETE ON menu_items BEGIN
158    INSERT INTO menu_items_fts (menu_items_fts, rowid, name, description) VALUES ('delete', old.rid, old.name, old.description);
159    INSERT INTO menu_items_tri (menu_items_tri, rowid, name, description) VALUES ('delete', old.rid, old.name, old.description);
160END;
161CREATE TRIGGER menu_items_au AFTER UPDATE OF name, description ON menu_items BEGIN
162    INSERT INTO menu_items_fts (menu_items_fts, rowid, name, description) VALUES ('delete', old.rid, old.name, old.description);
163    INSERT INTO menu_items_tri (menu_items_tri, rowid, name, description) VALUES ('delete', old.rid, old.name, old.description);
164    INSERT INTO menu_items_fts (rowid, name, description) VALUES (new.rid, new.name, new.description);
165    INSERT INTO menu_items_tri (rowid, name, description) VALUES (new.rid, new.name, new.description);
166END;
167
168-- Content-addressed image files: <cache>/images/ab/cd/<sha256>.<ext>. Several
169-- URLs may share one file (same hash).
170CREATE TABLE images (
171    url          TEXT PRIMARY KEY,
172    hash         TEXT NOT NULL,
173    bytes        INTEGER NOT NULL,
174    width        INTEGER,
175    height       INTEGER,
176    content_type TEXT NOT NULL,
177    last_access  INTEGER NOT NULL,
178    fetched_at   INTEGER NOT NULL
179);
180CREATE INDEX images_hash ON images (hash);
181CREATE INDEX images_access ON images (last_access);
182"#;
183
184/// Create `dir` (and parents) with every created directory 0700.
185pub(crate) fn create_private_dirs(dir: &Path) -> Result<()> {
186    let mut b = fs::DirBuilder::new();
187    b.recursive(true);
188    #[cfg(unix)]
189    {
190        use std::os::unix::fs::DirBuilderExt;
191        b.mode(0o700);
192    }
193    b.create(dir)?;
194    Ok(())
195}
196
197/// Create an empty file with mode 0600 if it doesn't exist yet, so SQLite (and
198/// its -wal/-shm files, which copy the database's mode) never start out
199/// world-readable.
200fn touch_private(path: &Path) -> Result<()> {
201    let mut opts = fs::OpenOptions::new();
202    opts.write(true).create_new(true);
203    #[cfg(unix)]
204    {
205        use std::os::unix::fs::OpenOptionsExt;
206        opts.mode(0o600);
207    }
208    match opts.open(path) {
209        Ok(_) => Ok(()),
210        Err(e) if e.kind() == std::io::ErrorKind::AlreadyExists => Ok(()),
211        Err(e) => Err(e.into()),
212    }
213}
214
215/// The current schema version (0 for a new database).
216pub(crate) fn schema_version(conn: &Connection) -> Result<usize> {
217    conn.execute_batch("CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL)")?;
218    let v: Option<i64> = conn
219        .query_row("SELECT MAX(version) FROM schema_version", [], |r| r.get(0))
220        .optional()?
221        .flatten();
222    Ok(usize::try_from(v.unwrap_or(0)).unwrap_or(0))
223}
224
225/// Apply every pending migration, each in its own transaction. Returns
226/// `(from, to)` versions. A database written by a newer grabcli is an error
227/// (the caller then runs without a cache rather than guessing).
228pub(crate) fn migrate(conn: &mut Connection, migrations: &[&str]) -> Result<(usize, usize)> {
229    let from = schema_version(conn)?;
230    if from > migrations.len() {
231        return Err(DbError(Error::invalid(format!(
232            "cache database schema v{from} is newer than this grabcli (v{}); \
233             run `grab cache clear --all` or upgrade",
234            migrations.len()
235        ))));
236    }
237    for (i, sql) in migrations.iter().enumerate().skip(from) {
238        let tx = conn.transaction()?;
239        tx.execute_batch(sql)?;
240        tx.execute("DELETE FROM schema_version", [])?;
241        tx.execute(
242            "INSERT INTO schema_version (version) VALUES (?1)",
243            [i64::try_from(i + 1).unwrap_or(i64::MAX)],
244        )?;
245        tx.commit()?;
246    }
247    Ok((from, migrations.len()))
248}
249
250fn configure(conn: &Connection) -> Result<()> {
251    conn.busy_timeout(Duration::from_secs(5))?;
252    // journal_mode returns a row, so query it rather than execute.
253    let _: String = conn.query_row("PRAGMA journal_mode = WAL", [], |r| r.get(0))?;
254    conn.execute_batch("PRAGMA synchronous = NORMAL; PRAGMA foreign_keys = ON;")?;
255    Ok(())
256}
257
258/// The pre-SQLite cache stored one JSON file per entry under
259/// `<root>/<namespace>/<kind>/`. It was never released; remove those
260/// directories (only ones whose children are all kind names).
261fn remove_legacy_json_dirs(root: &Path) {
262    const KINDS: [&str; 4] = ["list", "menu", "details", "categories"];
263    let Ok(dirs) = fs::read_dir(root) else {
264        return;
265    };
266    for ns in dirs.flatten() {
267        let path = ns.path();
268        if !path.is_dir() || ns.file_name() == "images" {
269            continue;
270        }
271        let Ok(children) = fs::read_dir(&path) else {
272            continue;
273        };
274        let names: Vec<String> = children
275            .flatten()
276            .map(|c| c.file_name().to_string_lossy().into_owned())
277            .collect();
278        if !names.is_empty() && names.iter().all(|n| KINDS.contains(&n.as_str())) {
279            let _ = fs::remove_dir_all(&path);
280        }
281    }
282}
283
284fn open_once(path: &Path, now: i64) -> Result<Connection> {
285    touch_private(path)?;
286    let mut conn = Connection::open(path)?;
287    configure(&conn)?;
288    let (from, _) = migrate(&mut conn, MIGRATIONS)?;
289    if from == 0
290        && let Some(root) = path.parent()
291    {
292        remove_legacy_json_dirs(root);
293    }
294    conn.execute(
295        "DELETE FROM entries WHERE rowid IN \
296         (SELECT rowid FROM entries WHERE expires_at <= ?1 LIMIT ?2)",
297        rusqlite::params![now, PRUNE_ON_OPEN_LIMIT],
298    )?;
299    Ok(conn)
300}
301
302fn is_corrupt(e: &DbError) -> bool {
303    let m = e.0.message.to_ascii_lowercase();
304    m.contains("not a database") || m.contains("malformed")
305}
306
307/// Open (creating if needed) the cache database at `path`: parent dir 0700,
308/// file 0600, WAL, busy timeout, migrations, and a bounded prune of expired
309/// responses. A corrupt file is deleted and recreated — it's only a cache.
310pub(crate) fn open(path: &Path, now: i64) -> Result<Connection> {
311    if let Some(dir) = path.parent() {
312        create_private_dirs(dir)?;
313    }
314    match open_once(path, now) {
315        Err(e) if is_corrupt(&e) => {
316            remove_db_files(path);
317            open_once(path, now)
318        }
319        other => other,
320    }
321}
322
323/// Delete the database and its WAL/SHM side files.
324pub(crate) fn remove_db_files(path: &Path) {
325    let _ = fs::remove_file(path);
326    for suffix in ["-wal", "-shm"] {
327        let mut p = path.as_os_str().to_owned();
328        p.push(suffix);
329        let _ = fs::remove_file(Path::new(&p));
330    }
331}
332
333/// Size of the database plus its WAL file.
334pub(crate) fn db_size(path: &Path) -> u64 {
335    let mut total = fs::metadata(path).map(|m| m.len()).unwrap_or(0);
336    let mut wal = path.as_os_str().to_owned();
337    wal.push("-wal");
338    total += fs::metadata(Path::new(&wal)).map(|m| m.len()).unwrap_or(0);
339    total
340}
341
342#[cfg(test)]
343mod tests {
344    use super::*;
345
346    #[test]
347    fn migrations_apply_once_in_order() -> Result<()> {
348        let dir = tempfile::tempdir()?;
349        let path = dir.path().join(DB_FILE);
350        let mut conn = open(&path, 0)?;
351        assert_eq!(schema_version(&conn)?, MIGRATIONS.len());
352        // Re-running is a no-op.
353        assert_eq!(
354            migrate(&mut conn, MIGRATIONS)?,
355            (MIGRATIONS.len(), MIGRATIONS.len())
356        );
357        // A later migration applies on top, exactly once.
358        let more = [MIGRATIONS[0], "CREATE TABLE extra (x INTEGER);"];
359        assert_eq!(migrate(&mut conn, &more)?, (1, 2));
360        assert_eq!(schema_version(&conn)?, 2);
361        assert_eq!(migrate(&mut conn, &more)?, (2, 2));
362        // The database is now "newer" than this binary's migrations.
363        assert!(migrate(&mut conn, MIGRATIONS).is_err());
364        Ok(())
365    }
366
367    #[test]
368    fn corrupt_database_is_recreated() -> Result<()> {
369        let dir = tempfile::tempdir()?;
370        let path = dir.path().join(DB_FILE);
371        fs::write(
372            &path,
373            b"this is not sqlite, just some bytes padding it out......",
374        )?;
375        let conn = open(&path, 0)?;
376        assert_eq!(schema_version(&conn)?, MIGRATIONS.len());
377        Ok(())
378    }
379
380    #[test]
381    fn legacy_json_dirs_are_removed() -> Result<()> {
382        let dir = tempfile::tempdir()?;
383        let legacy = dir.path().join("food").join("list");
384        fs::create_dir_all(&legacy)?;
385        fs::write(legacy.join("abc.json"), b"{}")?;
386        let keep = dir.path().join("other");
387        fs::create_dir_all(keep.join("stuff"))?;
388        open(&dir.path().join(DB_FILE), 0)?;
389        assert!(!dir.path().join("food").exists());
390        assert!(keep.exists());
391        Ok(())
392    }
393
394    #[test]
395    fn fts5_trigram_available() -> Result<()> {
396        let conn = Connection::open_in_memory()?;
397        conn.execute_batch(
398            "CREATE VIRTUAL TABLE t USING fts5(x, tokenize='trigram remove_diacritics 1');
399             INSERT INTO t (x) VALUES ('Café Sisig');",
400        )?;
401        let n: i64 =
402            conn.query_row("SELECT COUNT(*) FROM t WHERE t MATCH '\"cafe\"'", [], |r| {
403                r.get(0)
404            })?;
405        assert_eq!(n, 1);
406        Ok(())
407    }
408}
grabcli_sqlite/db.rs

grabcli_sqlite/
db.rs
