day-persistence 0.3.1

SQLite persistence for the Day observable model.
Documentation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
// Copyright © The Daybrite Project
// SPDX-License-Identifier: MPL-2.0

//! The built-in driver. On native targets the engine is rusqlite, chosen by cargo feature:
//! `bundled` (the default), `system` (link the OS's libsqlite3), or `cipher` (SQLCipher with
//! vendored crypto). On web-dom the engine is day-sqlite-worker: `:memory:` databases run
//! in-process, and file databases are proxied synchronously to the day-sql worker, which
//! holds them on real OPFS (see the web module at the end of this file). One driver type,
//! recompiled; not a crate per engine.

use std::rc::Rc;

#[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
use rusqlite::Connection;

#[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
type InitHook = Rc<dyn Fn(&Connection)>;

use crate::{
    Capabilities, DbError, DbErrorKind, Location, OpenOptions, Row, Secret, SqliteConnection,
    SqliteDriver, Value,
};

/// The built-in SQLite driver. `Sqlite::at(path)` or `Sqlite::memory()`, then builder options.
/// A statement-trace sink (`Sqlite::trace_sql`).
type TraceFn = Rc<dyn Fn(&str)>;

pub struct Sqlite {
    opts: OpenOptions,
    /// Runs against the raw rusqlite connection right after open, to register a custom SQL
    /// function, load an extension, or set a PRAGMA. The framework neither knows nor cares.
    /// Native only: the web engine lives in the day-sql worker, out of closure reach.
    #[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
    init: Option<InitHook>,
    /// The statement-trace sink (`trace_sql`).
    trace: Option<TraceFn>,
}

impl Sqlite {
    fn from_opts(opts: OpenOptions) -> Self {
        Sqlite {
            opts,
            #[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
            init: None,
            trace: None,
        }
    }
    pub fn at(path: impl Into<std::path::PathBuf>) -> Self {
        Sqlite::from_opts(OpenOptions::new(Location::File(path.into())))
    }
    pub fn memory() -> Self {
        Sqlite::from_opts(OpenOptions::new(Location::Memory))
    }
    #[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
    /// The database named `name` (`"notes.db"`: `.db` by convention) in the app's data
    /// directory, `day_spec::storage::data_dir()`, created if missing: a folder named for the app
    /// in the platform's place for app data (Application Support, `%LOCALAPPDATA%`,
    /// `$XDG_DATA_HOME`, the Android and HarmonyOS files directory; Day's docs/fs.md "Where files
    /// live"), or `DAY_DATA_DIR` when a host or a test sets it. `name` is a file name, not a path.
    /// Errs where no such directory exists (web has no
    /// filesystem; keep a wasm build on [`Sqlite::memory`] or its own driver).
    pub fn app_data(name: impl AsRef<str>) -> Result<Self, DbError> {
        let name = name.as_ref();
        if name.is_empty() || name.contains(['/', '\\']) || name.contains("..") {
            return Err(DbError::new(
                DbErrorKind::Unsupported,
                format!("app_data takes a file name, not a path: {name:?}"),
            ));
        }
        let dir = Self::app_data_dir()?;
        std::fs::create_dir_all(&dir)
            .map_err(|e| DbError::driver(format!("creating {}: {e}", dir.display())))?;
        Ok(Sqlite::at(dir.join(name)))
    }
    /// The database key (feature `cipher`; other builds refuse it at open, loudly).
    pub fn key(mut self, key: Secret) -> Self {
        self.opts.key = Some(key);
        self
    }
    /// Accept a file written by an older SQLCipher generation (`PRAGMA cipher_migrate`).
    pub fn cipher_migrate(mut self) -> Self {
        self.opts.cipher_migrate = true;
        self
    }
    pub fn wal(mut self, on: bool) -> Self {
        self.opts.wal = on;
        self
    }
    /// The directory [`Sqlite::app_data`] resolves into, for apps that manage their document
    /// files there (numbering "Drawing 2", importing a picked file). Created lazily by
    /// `app_data`; this accessor only resolves it.
    #[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
    pub fn app_data_dir() -> Result<std::path::PathBuf, DbError> {
        app_data_root()
    }

    /// A per-connection hook over the raw rusqlite connection: the escape valve for loadable
    /// extensions, custom functions, and PRAGMAs this crate does not model. Native only.
    #[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
    pub fn with_init(mut self, f: impl Fn(&Connection) + 'static) -> Self {
        self.init = Some(Rc::new(f));
        self
    }

    /// Per-statement SQL tracing through the engine's own facility (`sqlite3_trace_v2` with
    /// `SQLITE_TRACE_STMT`): `f` sees every statement this connection executes (migrations,
    /// autosave flushes, live-query `SELECT`s, maintenance) with bound parameters expanded by
    /// the engine itself. Wire it to a logger in debug builds:
    ///
    /// ```ignore
    /// let driver = Sqlite::app_data("trips.db")?;
    /// let driver = if cfg!(debug_assertions) {
    ///     driver.trace_sql(|sql| log::trace!("sql: {sql}"))
    /// } else {
    ///     driver
    /// };
    /// ```
    ///
    /// The trace installs after any `PRAGMA key`, so a cipher key never reaches the sink. On
    /// web-dom a file database's engine runs in the day-sql worker, out of closure reach:
    /// there the statements log to the browser console (`[day-sql]` lines in devtools) and
    /// `f` is not called. `:memory:` databases call `f` on every target.
    pub fn trace_sql(mut self, f: impl Fn(&str) + 'static) -> Self {
        self.trace = Some(Rc::new(f));
        self
    }
}

impl SqliteDriver for Sqlite {
    type Connection = RusqliteConn;

    /// The proxy open: `:memory:` runs in-process (no file I/O exists in that engine shape);
    /// a file database opens inside the day-sql worker over the synchronous channel.
    #[cfg(all(target_family = "wasm", target_os = "unknown"))]
    fn open(self) -> Result<RusqliteConn, DbError> {
        use day_sqlite_worker::protocol::{Reply as WReply, Req as WReq};
        if self.opts.key.is_some() {
            return Err(DbError::new(
                DbErrorKind::Unsupported,
                "this build has no encryption — the web engine does not include SQLCipher",
            ));
        }
        let conn = match &self.opts.location {
            Location::Memory => {
                let mut conn = day_sqlite_worker::Connection::open_memory()
                    .map_err(|e| DbError::driver(e.to_string()))?;
                if let Some(f) = &self.trace {
                    let f = f.clone();
                    conn.trace_stmt(Box::new(move |sql| f(sql)));
                }
                WebConn::Memory(conn)
            }
            Location::File(p) => {
                let name = p.to_string_lossy().into_owned();
                match web::channel::call(&WReq::Open {
                    name,
                    trace: self.trace.is_some(),
                })? {
                    WReply::Conn(id) => WebConn::Remote(id),
                    other => return Err(web::unexpected(other)),
                }
            }
        };
        let mut conn = RusqliteConn { conn };
        if self.opts.foreign_keys {
            conn.execute("PRAGMA foreign_keys = ON", &[])?;
        }
        Ok(conn)
    }

    #[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
    fn open(self) -> Result<RusqliteConn, DbError> {
        let conn = match &self.opts.location {
            Location::Memory => Connection::open_in_memory(),
            Location::File(p) => Connection::open(p),
        }
        .map_err(|e| DbError::driver(e.to_string()))?;

        if let Some(key) = &self.opts.key {
            #[cfg(feature = "cipher")]
            {
                // PRAGMA key takes no bound parameter; single-quote-escape the literal.
                let quoted = key.reveal().replace('\'', "''");
                conn.execute_batch(&format!("PRAGMA key = '{quoted}';"))
                    .map_err(|e| DbError::driver(e.to_string()))?;
                if self.opts.cipher_migrate {
                    let _ = conn.execute_batch("PRAGMA cipher_migrate;");
                }
            }
            #[cfg(not(feature = "cipher"))]
            {
                let _ = key;
                return Err(DbError::new(
                    DbErrorKind::Unsupported,
                    "this build has no encryption — enable the `cipher` feature",
                ));
            }
        }

        // A wrong (or missing) key surfaces on the first real read, not at PRAGMA time. Probe
        // now so the caller gets BadKey at open instead of a raw engine error later. Only the
        // cipher build maps the failure to BadKey; a plaintext build's unreadable file is
        // corruption, not a key problem.
        #[cfg(feature = "cipher")]
        if conn
            .prepare("SELECT count(*) FROM sqlite_master")
            .and_then(|mut s| s.query_row([], |_| Ok(())))
            .is_err()
        {
            return Err(DbError::new(
                DbErrorKind::BadKey,
                "the database would not open with this key",
            ));
        }

        // Trace after the key PRAGMA above (a cipher key must never reach the sink) and
        // before everything else, so the remaining setup PRAGMAs log too.
        let trace = self
            .trace
            .as_ref()
            .map(|f| install_sql_trace(&conn, f.clone()));

        // `day_fold`: Rust's full-Unicode `to_lowercase` as a scalar SQL function, so
        // case-insensitive predicates compile to SQL that selects exactly the rows the
        // in-memory fold would (SQLite's own `lower()` folds ASCII only). Deterministic, so
        // the planner may hoist and index it.
        {
            use rusqlite::functions::FunctionFlags;
            conn.create_scalar_function(
                "day_fold",
                1,
                FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
                |ctx| {
                    let folded = match ctx.get_raw(0) {
                        rusqlite::types::ValueRef::Text(t) => {
                            Some(String::from_utf8_lossy(t).to_lowercase())
                        }
                        _ => None,
                    };
                    Ok(folded)
                },
            )
            .map_err(|e| DbError::driver(format!("registering day_fold: {e}")))?;
        }

        conn.busy_timeout(std::time::Duration::from_millis(
            self.opts.busy_timeout_ms as u64,
        ))
        .map_err(|e| DbError::driver(e.to_string()))?;
        if self.opts.foreign_keys {
            conn.execute_batch("PRAGMA foreign_keys = ON;")
                .map_err(|e| DbError::driver(e.to_string()))?;
        }
        #[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
        if self.opts.wal && matches!(self.opts.location, Location::File(_)) {
            conn.execute_batch("PRAGMA journal_mode = WAL;")
                .map_err(|e| DbError::driver(e.to_string()))?;
        }
        if let Some(init) = &self.init {
            init(&conn);
        }
        Ok(RusqliteConn {
            conn,
            _trace: trace,
        })
    }

    fn capabilities(&self) -> Capabilities {
        #[cfg(all(target_family = "wasm", target_os = "unknown"))]
        {
            Capabilities {
                // A file database is durable when the day-sql worker holds it on OPFS: the
                // worker fsyncs before each reply, so a commit that returned has landed. No
                // channel (a host serving without cross-origin isolation) = no file databases
                // at all: reported here, refused loudly at open.
                durable: matches!(self.opts.location, Location::File(_)) && web::channel::ready(),
                encryption: false,
                wal: false,
                // day-sqlite-worker pins FTS5 and R*Tree into its build.
                full_text_search: true,
                rtree: true,
                external_changes: false,
                // The web engine runs in the day-sql worker, out of reach of a Rust closure
                // registration, so folded predicates take the exact fallback path instead.
                unicode_fold: false,
            }
        }
        #[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
        {
            Capabilities {
                durable: matches!(self.opts.location, Location::File(_)),
                encryption: cfg!(feature = "cipher"),
                wal: true,
                // The bundled builds compile FTS5/R*Tree in; a system libsqlite3 may or may
                // not, and claiming what we have not verified would be a lie the schema layer
                // acts on.
                full_text_search: cfg!(any(feature = "bundled", feature = "cipher")),
                rtree: cfg!(any(feature = "bundled", feature = "cipher")),
                // `PRAGMA data_version` is core SQLite: another connection's committed writes
                // are detectable on any file database. A memory database has no second
                // connection to detect.
                external_changes: matches!(self.opts.location, Location::File(_)),
                // `day_fold` registers at open on every native connection.
                unicode_fold: true,
            }
        }
    }
}

/// The app's data directory (`day_spec::storage::data_dir()`), shared with day-part-filesystem
/// (whose files keep to its `day-fs/` leaf) and everything else of the app's.
#[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
fn app_data_root() -> Result<std::path::PathBuf, DbError> {
    day_spec::storage::data_dir().ok_or_else(|| {
        DbError::new(
            DbErrorKind::Unsupported,
            "no per-app data directory on this target",
        )
    })
}

#[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
pub struct RusqliteConn {
    conn: Connection,
    /// Keeps the installed trace closure alive as long as the engine may call it (the box's
    /// heap address is what `sqlite3_trace_v2` holds). Dropped after `conn` closes.
    _trace: Option<Box<TraceFn>>,
}

/// Install the engine's per-statement trace on a raw connection handle. rusqlite's own
/// `trace_v2` takes a plain fn pointer, so the closure rides the C-level context instead.
#[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
fn install_sql_trace(conn: &Connection, f: TraceFn) -> Box<TraceFn> {
    use rusqlite::ffi;
    unsafe extern "C" fn cb(
        ev: std::ffi::c_uint,
        ctx: *mut std::ffi::c_void,
        p: *mut std::ffi::c_void,
        x: *mut std::ffi::c_void,
    ) -> std::ffi::c_int {
        if ev != ffi::SQLITE_TRACE_STMT || ctx.is_null() {
            return 0;
        }
        // SAFETY: ctx is the connection's boxed trace closure (returned below and stored on
        // RusqliteConn), alive until after the connection closes; day's connections are
        // single-threaded, so the callback never races the closure.
        let f = unsafe { &*(ctx as *const TraceFn) };
        unsafe {
            let expanded = ffi::sqlite3_expanded_sql(p.cast());
            if !expanded.is_null() {
                if let Ok(s) = std::ffi::CStr::from_ptr(expanded).to_str() {
                    f(s);
                }
                ffi::sqlite3_free(expanded.cast());
            } else if !x.is_null() {
                // The engine could not expand (OOM, or a trigger frame): unexpanded text.
                if let Ok(s) = std::ffi::CStr::from_ptr(x.cast()).to_str() {
                    f(s);
                }
            }
        }
        0
    }
    let boxed = Box::new(f);
    let ctx = &*boxed as *const TraceFn as *mut std::ffi::c_void;
    // SAFETY: the handle is this connection's own; ctx points into `boxed`, whose heap slot
    // outlives the connection via RusqliteConn::_trace.
    unsafe {
        ffi::sqlite3_trace_v2(conn.handle(), ffi::SQLITE_TRACE_STMT, Some(cb), ctx);
    }
    boxed
}

/// The web connection: in-process for `:memory:`, a worker connection id for files.
#[cfg(all(target_family = "wasm", target_os = "unknown"))]
enum WebConn {
    Memory(day_sqlite_worker::Connection),
    Remote(u32),
}

#[cfg(all(target_family = "wasm", target_os = "unknown"))]
pub struct RusqliteConn {
    conn: WebConn,
}

#[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
fn to_sql(v: &Value) -> rusqlite::types::Value {
    use rusqlite::types::Value as R;
    match v {
        Value::Null => R::Null,
        Value::Int(i) => R::Integer(*i),
        Value::Real(f) => R::Real(*f),
        Value::Text(t) => R::Text(t.clone()),
        Value::Blob(b) => R::Blob(b.clone()),
    }
}

#[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
fn from_sql(v: rusqlite::types::ValueRef<'_>) -> Value {
    use rusqlite::types::ValueRef as R;
    match v {
        R::Null => Value::Null,
        R::Integer(i) => Value::Int(i),
        R::Real(f) => Value::Real(f),
        R::Text(t) => Value::Text(String::from_utf8_lossy(t).into_owned()),
        R::Blob(b) => Value::Blob(b.to_vec()),
    }
}

#[cfg(all(target_family = "wasm", target_os = "unknown"))]
impl SqliteConnection for RusqliteConn {
    fn execute_batch(&mut self, sql: &str) -> Result<(), DbError> {
        use day_sqlite_worker::protocol::{Reply as WReply, Req as WReq};
        match &self.conn {
            WebConn::Memory(c) => c
                .execute_batch(sql)
                .map_err(|e| DbError::driver(format!("{e} in `{sql}`"))),
            WebConn::Remote(id) => match web::channel::call(&WReq::Batch {
                conn: *id,
                sql: sql.to_owned(),
            })? {
                WReply::Ok => Ok(()),
                other => Err(web::unexpected(other)),
            },
        }
    }

    fn execute(&mut self, sql: &str, params: &[Value]) -> Result<u64, DbError> {
        use day_sqlite_worker::protocol::{Reply as WReply, Req as WReq};
        match &self.conn {
            WebConn::Memory(c) => c
                .execute(sql, &web::to_wire(params))
                .map_err(|e| DbError::driver(format!("{e} in `{sql}`"))),
            WebConn::Remote(id) => match web::channel::call(&WReq::Exec {
                conn: *id,
                sql: sql.to_string(),
                params: web::to_wire(params),
            })? {
                WReply::Changes(n) => Ok(n),
                other => Err(web::unexpected(other)),
            },
        }
    }

    fn query(
        &mut self,
        sql: &str,
        params: &[Value],
        row: &mut dyn FnMut(&dyn Row),
    ) -> Result<(), DbError> {
        use day_sqlite_worker::protocol::{Reply as WReply, Req as WReq};
        match &self.conn {
            WebConn::Memory(c) => c
                .query(sql, &web::to_wire(params), &mut |r| {
                    let vals: Vec<Value> = r.into_iter().map(web::from_wire).collect();
                    row(&vals);
                })
                .map_err(|e| DbError::driver(format!("{e} in `{sql}`"))),
            WebConn::Remote(id) => match web::channel::call(&WReq::Query {
                conn: *id,
                sql: sql.to_string(),
                params: web::to_wire(params),
            })? {
                WReply::Rows(rows) => {
                    for r in rows {
                        let vals: Vec<Value> = r.into_iter().map(web::from_wire).collect();
                        row(&vals);
                    }
                    Ok(())
                }
                other => Err(web::unexpected(other)),
            },
        }
    }
    fn query_named(
        &mut self,
        sql: &str,
        params: &[Value],
        row: &mut dyn FnMut(&[String], &dyn Row),
    ) -> Result<(), DbError> {
        use day_sqlite_worker::protocol::{Reply as WReply, Req as WReq};
        match &self.conn {
            WebConn::Memory(c) => c
                .query_named(sql, &web::to_wire(params), &mut |names, r| {
                    let vals: Vec<Value> = r.into_iter().map(web::from_wire).collect();
                    row(names, &vals);
                })
                .map(|_| ())
                .map_err(|e| DbError::driver(format!("{e} in `{sql}`"))),
            WebConn::Remote(id) => match web::channel::call(&WReq::QueryNamed {
                conn: *id,
                sql: sql.to_owned(),
                params: web::to_wire(params),
            })? {
                WReply::NamedRows { names, rows } => {
                    for r in rows {
                        let vals: Vec<Value> = r.into_iter().map(web::from_wire).collect();
                        row(&names, &vals);
                    }
                    Ok(())
                }
                other => Err(web::unexpected(other)),
            },
        }
    }
}

/// A dropped remote connection closes its worker side, best effort, like a file handle.
#[cfg(all(target_family = "wasm", target_os = "unknown"))]
impl Drop for RusqliteConn {
    fn drop(&mut self) {
        if let WebConn::Remote(id) = self.conn {
            let _ = web::channel::call(&day_sqlite_worker::protocol::Req::Close { conn: id });
        }
    }
}

#[cfg(not(all(target_family = "wasm", target_os = "unknown")))]
impl SqliteConnection for RusqliteConn {
    fn execute(&mut self, sql: &str, params: &[Value]) -> Result<u64, DbError> {
        // Cached, not re-prepared: the fold emits RUNS of identical SQL (one INSERT shape per
        // table, one DELETE, one UPDATE per changed column set), so a bulk flush would
        // otherwise re-parse the same statement thousands of times.
        let mut stmt = self
            .conn
            .prepare_cached(sql)
            .map_err(|e| DbError::driver(format!("{e} in `{sql}`")))?;
        let bound = rusqlite::params_from_iter(params.iter().map(to_sql));
        stmt.execute(bound)
            .map(|n| n as u64)
            .map_err(|e| DbError::driver(format!("{e} in `{sql}`")))
    }

    fn query(
        &mut self,
        sql: &str,
        params: &[Value],
        row: &mut dyn FnMut(&dyn Row),
    ) -> Result<(), DbError> {
        let mut stmt = self
            .conn
            .prepare_cached(sql)
            .map_err(|e| DbError::driver(format!("{e} in `{sql}`")))?;
        let bound = rusqlite::params_from_iter(params.iter().map(to_sql));
        let mut rows = stmt
            .query(bound)
            .map_err(|e| DbError::driver(format!("{e} in `{sql}`")))?;
        loop {
            match rows.next() {
                Ok(Some(r)) => {
                    let n = r.as_ref().column_count();
                    let vals: Vec<Value> = (0..n)
                        .map(|i| r.get_ref(i).map(from_sql).unwrap_or(Value::Null))
                        .collect();
                    row(&vals);
                }
                Ok(None) => break,
                Err(e) => return Err(DbError::driver(e.to_string())),
            }
        }
        Ok(())
    }

    fn execute_batch(&mut self, sql: &str) -> Result<(), DbError> {
        self.conn
            .execute_batch(sql)
            .map_err(|e| DbError::driver(format!("{e} in batch")))
    }

    fn query_named(
        &mut self,
        sql: &str,
        params: &[Value],
        row: &mut dyn FnMut(&[String], &dyn Row),
    ) -> Result<(), DbError> {
        let mut stmt = self
            .conn
            .prepare(sql)
            .map_err(|e| DbError::driver(format!("{e} in `{sql}`")))?;
        let names: Vec<String> = stmt.column_names().iter().map(|s| s.to_string()).collect();
        let bound = rusqlite::params_from_iter(params.iter().map(to_sql));
        let mut rows = stmt
            .query(bound)
            .map_err(|e| DbError::driver(format!("{e} in `{sql}`")))?;
        loop {
            match rows.next() {
                Ok(Some(r)) => {
                    let n = r.as_ref().column_count();
                    let vals: Vec<Value> = (0..n)
                        .map(|i| r.get_ref(i).map(from_sql).unwrap_or(Value::Null))
                        .collect();
                    row(&names, &vals);
                }
                Ok(None) => break,
                Err(e) => return Err(DbError::driver(e.to_string())),
            }
        }
        Ok(())
    }
}

// ---------------------------------------------------------------------------
// The web leg (wasm32-unknown-unknown): the synchronous channel to the day-sql worker.
//
// OPFS's only random-access synchronous API (`createSyncAccessHandle`) exists solely in
// dedicated workers, so the engine runs in day-sqlite-worker's day-sql worker (the same wasm
// module, instantiated there) and this side is a proxy. Each call encodes one request in
// day-sqlite-worker's protocol, hands it to that crate's channel, and the main thread blocks
// the few microseconds until the worker's reply lands back through the SharedArrayBuffer.
// Fully synchronous from Rust's point of view, fully durable at commit, one code path with
// every other platform. The channel needs cross-origin isolation (COOP/COEP; `day launch`
// serves it, see docs/web.md); without it File opens fail loudly and capabilities say
// durable: false.
// ---------------------------------------------------------------------------

#[cfg(all(target_family = "wasm", target_os = "unknown"))]
mod web {
    use super::*;
    use day_sqlite_worker::protocol::{self, Reply as WReply, Req as WReq, Value as WValue};

    pub(super) mod channel {
        use super::*;

        pub(crate) fn ready() -> bool {
            day_sqlite_worker::channel::ready()
        }

        /// One round trip. Worker-reported SQL errors come back as [`DbError`]s here, so
        /// callers only ever match the success variants.
        pub(crate) fn call(req: &WReq) -> Result<WReply, DbError> {
            let Some(reply) = day_sqlite_worker::channel::call(&protocol::encode_req(req)) else {
                return Err(DbError::new(
                    DbErrorKind::Unsupported,
                    "no SQL worker — file databases on the web need the day-sql worker, \
                     which needs cross-origin isolation (COOP/COEP headers; docs/web.md)",
                ));
            };
            match protocol::decode_reply(&reply) {
                Ok(WReply::Err(m)) => Err(DbError::driver(m)),
                Ok(r) => Ok(r),
                Err(_) => Err(DbError::driver("malformed reply from the SQL worker")),
            }
        }
    }

    pub(super) fn unexpected(r: WReply) -> DbError {
        DbError::driver(format!("unexpected SQL worker reply: {r:?}"))
    }

    pub(super) fn to_wire(params: &[Value]) -> Vec<WValue> {
        params
            .iter()
            .map(|v| match v {
                Value::Null => WValue::Null,
                Value::Int(i) => WValue::Int(*i),
                Value::Real(r) => WValue::Real(*r),
                Value::Text(t) => WValue::Text(t.clone()),
                Value::Blob(b) => WValue::Blob(b.clone()),
            })
            .collect()
    }

    pub(super) fn from_wire(v: WValue) -> Value {
        match v {
            WValue::Null => Value::Null,
            WValue::Int(i) => Value::Int(i),
            WValue::Real(r) => Value::Real(r),
            WValue::Text(t) => Value::Text(t),
            WValue::Blob(b) => Value::Blob(b),
        }
    }

    /// The origin's database pool: names in, bytes out, OPFS underneath. The document
    /// surface a file-per-document app needs on the web: numbering a fresh drawing,
    /// importing an Open… pick, exporting a download.
    #[derive(Clone)]
    pub struct WebStorage;

    impl WebStorage {
        pub fn exists(&self, name: &str) -> bool {
            matches!(
                channel::call(&WReq::Exists {
                    name: name.to_string()
                }),
                Ok(WReply::Bool(true))
            )
        }

        /// Every database name in the pool.
        pub fn list(&self) -> Vec<String> {
            match channel::call(&WReq::List) {
                Ok(WReply::Names(names)) => names,
                _ => Vec::new(),
            }
        }

        /// The database's bytes: a plain SQLite file image, downloadable as a file. Flush
        /// (`ModelContainer::save`) first for an image that includes this turn's edits.
        pub fn export_db(&self, name: &str) -> Result<Vec<u8>, DbError> {
            match channel::call(&WReq::Export {
                name: name.to_string(),
            })? {
                WReply::Bytes(b) => Ok(b),
                other => Err(unexpected(other)),
            }
        }

        /// Write a SQLite file image under `name` (an Open… flow's landing). A connection
        /// still open on that name is stale afterwards; close (drop) it before importing.
        pub fn import_db(&self, name: &str, bytes: &[u8]) -> Result<(), DbError> {
            match channel::call(&WReq::Import {
                name: name.to_string(),
                bytes: bytes.to_vec(),
            })? {
                WReply::Ok => Ok(()),
                other => Err(unexpected(other)),
            }
        }

        pub fn delete_db(&self, name: &str) {
            let _ = channel::call(&WReq::Delete {
                name: name.to_string(),
            });
        }
    }

    impl Sqlite {
        /// The pool surface, when the worker channel is up.
        pub fn web_storage() -> Result<WebStorage, DbError> {
            if channel::ready() {
                Ok(WebStorage)
            } else {
                Err(DbError::new(
                    DbErrorKind::Unsupported,
                    "no SQL worker — file databases on the web need cross-origin isolation \
                     (COOP/COEP headers; docs/web.md)",
                ))
            }
        }
    }
}

#[cfg(all(target_family = "wasm", target_os = "unknown"))]
pub use web::WebStorage;