Skip to main content

sqlive/
sqlite_settings.rs

1//! The SQLite settings an app may choose for a database's connections, the
2//! ones sqlive keeps fixed, and the WAL commands an app may run (§4, api.md
3//! E1).
4
5use crate::authorization::HostError;
6use crate::authorization::Policy;
7use crate::authorization::PolicyError;
8use std::num::NonZeroU32;
9use std::time::Duration;
10
11use crate::connection::HostSql;
12
13/// SQLite settings for every connection sqlive opens to a database: the
14/// writer and the readers of a writable handle, and every connection of a
15/// read-only one. A setting left `None` keeps SQLite's own default, except
16/// `checkpoint_fullfsync` on macOS and iOS, which sqlive turns on, and
17/// `auto_vacuum`, which is `Incremental`. sqlive applies them as each
18/// connection opens, before any of its own statements.
19///
20/// The settings sqlive's guarantees rest on have no field here: the journal
21/// mode, `synchronous`, the locking mode, foreign keys and the rest stay
22/// fixed (§4).
23///
24/// ```compile_fail
25/// let _ = sqlive::SqliteSettings {
26///     journal_mode: None,
27///     ..Default::default()
28/// };
29/// ```
30#[derive(Clone, Debug, Default, PartialEq, Eq)]
31pub struct SqliteSettings {
32    /// How long a connection waits for a lock another connection holds before
33    /// failing with `SQLITE_BUSY` (`PRAGMA busy_timeout`), at most
34    /// `i32::MAX` milliseconds. Unset keeps the 5 seconds rusqlite sets when
35    /// it opens a connection. RESTART and TRUNCATE checkpoints wait this long
36    /// for readers.
37    pub busy_timeout: Option<Duration>,
38    /// The size of each connection's page cache (`PRAGMA cache_size`).
39    pub cache_size: Option<CacheSize>,
40    /// Whether, and past how many cached pages, a transaction writes changed
41    /// pages to the file before it commits (`PRAGMA cache_spill`).
42    pub cache_spill: Option<CacheSpill>,
43    /// Bytes of the database read through memory-mapped I/O (`PRAGMA
44    /// mmap_size`), at most `i64::MAX`. SQLite lowers it to the largest size
45    /// it was built to map.
46    pub mmap_size: Option<u64>,
47    /// Where temporary tables and indices are kept (`PRAGMA temp_store`).
48    /// On Android, sqlive's SQLite is built to keep them in memory whatever
49    /// this says.
50    pub temp_store: Option<TempStore>,
51    /// Helper threads one statement may use to sort (`PRAGMA threads`), at
52    /// most `i32::MAX`. SQLite lowers it to the most it was built to allow.
53    pub threads: Option<u32>,
54    /// The size SQLite truncates the WAL file to at the first commit after
55    /// a checkpoint lets the WAL start over (`PRAGMA journal_size_limit`).
56    /// Unset is sqlive's 4 MiB.
57    pub journal_size_limit: Option<JournalSizeLimit>,
58    /// The WAL size at which sqlive checkpoints it, off the commit path; unset
59    /// is SQLite's 1000 pages (§4). None of a writable handle's connections
60    /// runs a checkpoint as it commits. A read-only handle's own commits, which
61    /// only its cache makes, checkpoint as SQLite's
62    /// `PRAGMA wal_autocheckpoint` does.
63    pub wal_autocheckpoint: Option<WalAutocheckpoint>,
64    /// Whether the query planner may build a temporary index for one query
65    /// (`PRAGMA automatic_index`).
66    pub automatic_index: Option<bool>,
67    /// Whether SQLite checks each page's cells for damage as it reads them
68    /// (`PRAGMA cell_size_check`).
69    pub cell_size_check: Option<bool>,
70    /// Whether deleted content is overwritten in the file (`PRAGMA
71    /// secure_delete`).
72    pub secure_delete: Option<SecureDelete>,
73    /// Whether a commit's sync on macOS and iOS also flushes the drive's own
74    /// cache, `F_FULLFSYNC` (`PRAGMA fullfsync`). Other platforms ignore it.
75    /// Unset keeps SQLite's default, off, so a power loss can lose the latest
76    /// commits.
77    pub fullfsync: Option<bool>,
78    /// The same for checkpoints (`PRAGMA checkpoint_fullfsync`). Unset is on
79    /// for macOS and iOS, so a power loss can't leave the database file
80    /// holding only part of a checkpoint after the WAL has started over.
81    pub checkpoint_fullfsync: Option<bool>,
82    /// How many rows of each index `ANALYZE` reads (`PRAGMA analysis_limit`).
83    pub analysis_limit: Option<AnalysisLimit>,
84    /// The page size SQLite creates a database with (`PRAGMA page_size`).
85    /// SQLite fixes the size once the file has its first page, so an existing
86    /// database keeps the size SQLite created it with.
87    pub page_size: Option<PageSize>,
88    /// The auto-vacuum mode a database is created with (`PRAGMA
89    /// auto_vacuum`). Like the page size, an existing database keeps its
90    /// own. Unset is `Incremental`, and a host gives back free pages past
91    /// 4 MiB through `give_back_free_pages`, as coven does each time a claim
92    /// lands (§4).
93    pub auto_vacuum: Option<AutoVacuum>,
94}
95
96/// The SQLite settings one connection to a database has in effect,
97/// as SQLite reports them. Every connection has the same, except that a
98/// writable handle's writer and statistics connection never checkpoint as
99/// they commit.
100#[derive(Clone, Debug, PartialEq, Eq)]
101pub struct SqliteSettingsInEffect {
102    /// `PRAGMA busy_timeout`, to the millisecond.
103    pub busy_timeout: Duration,
104    /// `PRAGMA cache_size`.
105    pub cache_size: CacheSize,
106    /// `PRAGMA cache_spill`: when on, the page count past which a
107    /// transaction spills, which is never below the cache's own size.
108    pub cache_spill: CacheSpill,
109    /// `PRAGMA mmap_size`.
110    pub mmap_size: u64,
111    /// `PRAGMA temp_store`.
112    pub temp_store: TempStore,
113    /// `PRAGMA threads`.
114    pub threads: u32,
115    /// `PRAGMA journal_size_limit`.
116    pub journal_size_limit: JournalSizeLimit,
117    /// `PRAGMA wal_autocheckpoint`, which on a writable handle's readers is
118    /// the size at which sqlive checkpoints. Its writer's and its statistics
119    /// connection's own are off: neither checkpoints as it commits.
120    pub wal_autocheckpoint: WalAutocheckpoint,
121    /// `PRAGMA automatic_index`.
122    pub automatic_index: bool,
123    /// `PRAGMA cell_size_check`.
124    pub cell_size_check: bool,
125    /// `PRAGMA secure_delete`.
126    pub secure_delete: SecureDelete,
127    /// `PRAGMA fullfsync`.
128    pub fullfsync: bool,
129    /// `PRAGMA checkpoint_fullfsync`.
130    pub checkpoint_fullfsync: bool,
131    /// `PRAGMA analysis_limit`.
132    pub analysis_limit: AnalysisLimit,
133    /// `PRAGMA page_size`.
134    pub page_size: PageSize,
135    /// `PRAGMA auto_vacuum`.
136    pub auto_vacuum: AutoVacuum,
137}
138
139/// A page cache's size, which SQLite counts in pages or in kibibytes.
140#[derive(Clone, Copy, Debug, PartialEq, Eq)]
141pub enum CacheSize {
142    /// This many pages, at most `i32::MAX`.
143    Pages(u32),
144    /// As many pages as fit in this many kibibytes, at most `i32::MAX`.
145    Kibibytes(u32),
146}
147
148/// Whether a transaction may spill changed pages before it commits.
149#[derive(Clone, Copy, Debug, PartialEq, Eq)]
150pub enum CacheSpill {
151    /// Changed pages stay in memory until commit.
152    Off,
153    /// Spill once the cache holds this many pages, at most `i32::MAX`.
154    Pages(NonZeroU32),
155}
156
157/// Where temporary tables and indices are kept.
158#[derive(Clone, Copy, Debug, PartialEq, Eq)]
159pub enum TempStore {
160    /// Where SQLite was built to keep them.
161    Default,
162    /// In a temporary file.
163    File,
164    /// In memory.
165    Memory,
166}
167
168/// How large the WAL file may stay after a checkpoint resets it.
169#[derive(Clone, Copy, Debug, PartialEq, Eq)]
170pub enum JournalSizeLimit {
171    /// The file keeps its size.
172    Unlimited,
173    /// The file is truncated to this many bytes, at most `i64::MAX`.
174    Bytes(u64),
175}
176
177/// When sqlive checkpoints the WAL by itself.
178#[derive(Clone, Copy, Debug, PartialEq, Eq)]
179pub enum WalAutocheckpoint {
180    /// Never: the WAL grows until the app checkpoints it.
181    Off,
182    /// Once the WAL holds this many pages, at most `i32::MAX`.
183    Pages(NonZeroU32),
184}
185
186/// Whether deleted content is overwritten.
187#[derive(Clone, Copy, Debug, PartialEq, Eq)]
188pub enum SecureDelete {
189    /// Deleted content stays in free pages until they are reused.
190    Off,
191    /// Deleted content is overwritten with zeros.
192    On,
193    /// Overwritten only where that adds no I/O.
194    Fast,
195}
196
197/// How many rows of each index `ANALYZE` reads.
198#[derive(Clone, Copy, Debug, PartialEq, Eq)]
199pub enum AnalysisLimit {
200    /// Every row.
201    Unlimited,
202    /// About this many rows, at most `i32::MAX`.
203    Rows(NonZeroU32),
204}
205
206/// A database page size: a power of two from 512 bytes to 64 KiB.
207#[derive(Clone, Copy, Debug, PartialEq, Eq)]
208pub enum PageSize {
209    /// 512 bytes.
210    Bytes512,
211    /// 1 KiB.
212    Bytes1024,
213    /// 2 KiB.
214    Bytes2048,
215    /// 4 KiB, SQLite's default.
216    Bytes4096,
217    /// 8 KiB.
218    Bytes8192,
219    /// 16 KiB.
220    Bytes16384,
221    /// 32 KiB.
222    Bytes32768,
223    /// 64 KiB.
224    Bytes65536,
225}
226
227/// What happens to the pages deleted content frees.
228#[derive(Clone, Copy, Debug, PartialEq, Eq)]
229pub enum AutoVacuum {
230    /// They stay in the file for reuse.
231    Off,
232    /// Each commit moves them to the end of the file and truncates it.
233    Full,
234    /// They are given back only when the app runs an incremental vacuum.
235    Incremental,
236}
237
238/// SQLite's checkpoint modes (`PRAGMA wal_checkpoint`).
239#[derive(Clone, Copy, Debug, PartialEq, Eq)]
240pub enum CheckpointMode {
241    /// Copy nothing: only report how many frames the WAL holds, and how
242    /// many of them are in the database file.
243    Noop,
244    /// Copy what no reader still needs, without waiting.
245    Passive,
246    /// Wait for the writer lock and for readers, then copy the whole WAL.
247    Full,
248    /// As `Full`, then wait for readers to leave the WAL, so the next commit
249    /// writes the WAL from its start.
250    Restart,
251    /// As `Restart`, then truncate the WAL file to zero bytes.
252    Truncate,
253}
254
255/// What a checkpoint did, as SQLite reports it.
256#[derive(Clone, Copy, Debug, PartialEq, Eq)]
257pub enum WalCheckpoint {
258    /// SQLite read nothing of the WAL, so it reports no frames: another
259    /// connection was checkpointing the WAL, or was committing to it as
260    /// SQLite read where it ends. SQLite gives up on this at once, without
261    /// waiting out the busy timeout.
262    NotStarted,
263    /// SQLite read the WAL and copied what it could.
264    Ran {
265        /// A `Full`, `Restart` or `Truncate` checkpoint could not finish
266        /// within the busy timeout, because a reader or another writer held
267        /// on.
268        busy: bool,
269        /// Frames in the WAL.
270        log_frames: u32,
271        /// Frames copied into the database, this time or before.
272        checkpointed_frames: u32,
273    },
274}
275
276/// How many free pages an incremental vacuum gives back.
277#[derive(Clone, Copy, Debug, PartialEq, Eq)]
278pub enum IncrementalVacuum {
279    /// Every free page.
280    All,
281    /// At most this many, at most `i32::MAX`.
282    Pages(NonZeroU32),
283}
284
285impl PageSize {
286    fn bytes(self) -> u32 {
287        match self {
288            PageSize::Bytes512 => 512,
289            PageSize::Bytes1024 => 1024,
290            PageSize::Bytes2048 => 2048,
291            PageSize::Bytes4096 => 4096,
292            PageSize::Bytes8192 => 8192,
293            PageSize::Bytes16384 => 16384,
294            PageSize::Bytes32768 => 32768,
295            PageSize::Bytes65536 => 65536,
296        }
297    }
298
299    fn from_bytes<F: HostError, R: HostError>(bytes: i64) -> Result<Self, crate::Error<F, R>> {
300        Ok(match bytes {
301            512 => PageSize::Bytes512,
302            1024 => PageSize::Bytes1024,
303            2048 => PageSize::Bytes2048,
304            4096 => PageSize::Bytes4096,
305            8192 => PageSize::Bytes8192,
306            16384 => PageSize::Bytes16384,
307            32768 => PageSize::Bytes32768,
308            65536 => PageSize::Bytes65536,
309            _ => return Err(unreported()),
310        })
311    }
312}
313
314/// sqlive's `checkpoint_fullfsync` where the app leaves it unset. On macOS and
315/// iOS a plain `fsync` can leave writes in the drive's cache, in any order. A
316/// checkpoint copies the WAL into the database file and lets the next commit
317/// write the WAL over from its start, so each of its syncs flushes the drive's
318/// cache with `F_FULLFSYNC`: the database file holds the whole checkpoint
319/// before any of the WAL is overwritten (§3). Commits keep a plain `fsync`.
320#[cfg(any(target_os = "macos", target_os = "ios"))]
321const CHECKPOINT_FULLFSYNC: Option<bool> = Some(true);
322/// Elsewhere SQLite's own default stands.
323#[cfg(not(any(target_os = "macos", target_os = "ios")))]
324const CHECKPOINT_FULLFSYNC: Option<bool> = None;
325
326/// sqlive's `journal_size_limit` where the app leaves it unset: once a
327/// checkpoint lets the WAL start over, the file shrinks back to 4 MiB, about
328/// the 1000 pages of 4 KiB at which sqlive checkpoints by default, instead of
329/// keeping the largest size it ever grew to.
330const JOURNAL_SIZE_LIMIT: JournalSizeLimit = JournalSizeLimit::Bytes(4 << 20);
331
332/// sqlive's auto-vacuum mode where the app leaves it unset, so a host can
333/// give back the pages its own writes free.
334const AUTO_VACUUM: AutoVacuum = AutoVacuum::Incremental;
335
336/// The free pages sqlive leaves in the file for later writes to reuse, the
337/// same 4 MiB it lets the WAL file keep (`JOURNAL_SIZE_LIMIT`). A coven
338/// store's outgoing queue refills freed pages with its next writes, so
339/// vacuuming them at every landing would only move pages back and forth; the
340/// problem is the peak left behind.
341const KEPT_FREE_BYTES: u64 = 4 << 20;
342
343/// The WAL size at which sqlive checkpoints where the app leaves it unset:
344/// SQLite's own automatic checkpoint size.
345pub const CHECKPOINT_PAGES: NonZeroU32 = NonZeroU32::new(1000).expect("nonzero");
346
347impl SqliteSettings {
348    /// The WAL size at which sqlive checkpoints it, or `None` if it never does.
349    pub fn checkpoint_pages(&self) -> Option<NonZeroU32> {
350        match self.wal_autocheckpoint {
351            None => Some(CHECKPOINT_PAGES),
352            Some(WalAutocheckpoint::Pages(pages)) => Some(pages),
353            Some(WalAutocheckpoint::Off) => None,
354        }
355    }
356
357    /// The PRAGMAs that set these choices on any connection.
358    fn connection_pragmas<F: HostError, R: HostError>(
359        &self,
360    ) -> Result<Vec<String>, crate::Error<F, R>> {
361        let mut pragmas = Vec::new();
362        if let Some(timeout) = self.busy_timeout {
363            let milliseconds = int("busy_timeout", timeout.as_millis())?;
364            pragmas.push(format!("busy_timeout = {milliseconds}"));
365        }
366        if let Some(size) = self.cache_size {
367            pragmas.push(match size {
368                CacheSize::Pages(pages) => format!("cache_size = {}", int("cache_size", pages)?),
369                CacheSize::Kibibytes(kib) => format!("cache_size = -{}", int("cache_size", kib)?),
370            });
371        }
372        if let Some(spill) = self.cache_spill {
373            pragmas.push(match spill {
374                CacheSpill::Off => "cache_spill = OFF".into(),
375                CacheSpill::Pages(pages) => {
376                    format!("cache_spill = {}", int("cache_spill", pages.get())?)
377                }
378            });
379        }
380        if let Some(bytes) = self.mmap_size {
381            pragmas.push(format!("mmap_size = {}", int64("mmap_size", bytes)?));
382        }
383        if let Some(store) = self.temp_store {
384            let store = match store {
385                TempStore::Default => "DEFAULT",
386                TempStore::File => "FILE",
387                TempStore::Memory => "MEMORY",
388            };
389            pragmas.push(format!("temp_store = {store}"));
390        }
391        if let Some(threads) = self.threads {
392            pragmas.push(format!("threads = {}", int("threads", threads)?));
393        }
394        let bytes = match self.journal_size_limit.unwrap_or(JOURNAL_SIZE_LIMIT) {
395            JournalSizeLimit::Unlimited => -1,
396            JournalSizeLimit::Bytes(bytes) => int64("journal_size_limit", bytes)?,
397        };
398        pragmas.push(format!("journal_size_limit = {bytes}"));
399        if let Some(checkpoint) = self.wal_autocheckpoint {
400            let pages = match checkpoint {
401                WalAutocheckpoint::Off => 0,
402                WalAutocheckpoint::Pages(pages) => int("wal_autocheckpoint", pages.get())?,
403            };
404            pragmas.push(format!("wal_autocheckpoint = {pages}"));
405        }
406        for (name, value) in [
407            ("automatic_index", self.automatic_index),
408            ("cell_size_check", self.cell_size_check),
409            ("fullfsync", self.fullfsync),
410            ("checkpoint_fullfsync", self.checkpoint_fullfsync.or(CHECKPOINT_FULLFSYNC)),
411        ] {
412            if let Some(value) = value {
413                pragmas.push(format!("{name} = {}", if value { "ON" } else { "OFF" }));
414            }
415        }
416        if let Some(delete) = self.secure_delete {
417            let delete = match delete {
418                SecureDelete::Off => "OFF",
419                SecureDelete::On => "ON",
420                SecureDelete::Fast => "FAST",
421            };
422            pragmas.push(format!("secure_delete = {delete}"));
423        }
424        if let Some(limit) = self.analysis_limit {
425            let rows = match limit {
426                AnalysisLimit::Unlimited => 0,
427                AnalysisLimit::Rows(rows) => int("analysis_limit", rows.get())?,
428            };
429            pragmas.push(format!("analysis_limit = {rows}"));
430        }
431        Ok(pragmas)
432    }
433
434    /// The PRAGMAs that shape a new database file. The page size comes first:
435    /// setting the auto-vacuum mode writes the file's first page, which fixes it.
436    fn creation_pragmas(&self) -> Vec<String> {
437        let mut pragmas = Vec::new();
438        if let Some(size) = self.page_size {
439            pragmas.push(format!("page_size = {}", size.bytes()));
440        }
441        let vacuum = match self.auto_vacuum.unwrap_or(AUTO_VACUUM) {
442            AutoVacuum::Off => "NONE",
443            AutoVacuum::Full => "FULL",
444            AutoVacuum::Incremental => "INCREMENTAL",
445        };
446        pragmas.push(format!("auto_vacuum = {vacuum}"));
447        pragmas
448    }
449}
450
451/// The fewest rows a table keeps the planner's statistics for (§4). SQLite
452/// 3.53 planned a full scan over a rowid range lookup for a table it counted
453/// at 2 rows, over an index lookup at 2, and over an index range at 8 to 50.
454const SMALL_TABLE: i64 = 100;
455
456impl<P: Policy> HostSql<P> {
457    /// Set the settings sqlive fixes, then the app's choices, on a connection
458    /// that has run no statement yet. Changing `temp_store` drops temporary
459    /// objects, so this precedes the host's temporary triggers. A writable
460    /// connection to an empty file also gets its creation settings, before
461    /// anything writes the file.
462    pub(crate) fn configure(
463        &self,
464        settings: &SqliteSettings,
465        read_only: bool,
466    ) -> Result<(), PolicyError<P>> {
467        self.batch(
468            "PRAGMA foreign_keys = ON; PRAGMA recursive_triggers = ON; PRAGMA trusted_schema = OFF;",
469        )?;
470        let mut pragmas = settings.connection_pragmas()?;
471        if !read_only {
472            let pages: i64 = self.query_row("PRAGMA page_count", [], |r| r.get(0))?;
473            if pages == 0 {
474                pragmas.extend(settings.creation_pragmas());
475            }
476        }
477        if pragmas.is_empty() {
478            return Ok(());
479        }
480        self.batch(&format!("PRAGMA {};", pragmas.join("; PRAGMA ")))
481    }
482
483    /// Put the database in WAL mode and make every commit durable (§4).
484    pub(crate) fn enable_wal(&self) -> Result<(), PolicyError<P>> {
485        let mode: String = self.query_row("PRAGMA journal_mode = WAL", [], |row| row.get(0))?;
486        if !mode.eq_ignore_ascii_case("wal") {
487            return Err(crate::Error::WalUnavailable { mode });
488        }
489        self.batch("PRAGMA synchronous = FULL;")
490    }
491
492    /// The SQLite settings in effect on this connection.
493    pub fn sqlite_settings(&self) -> Result<SqliteSettingsInEffect, PolicyError<P>> {
494        let read = |pragma: &str| -> Result<i64, PolicyError<P>> {
495            self.query_row(&format!("PRAGMA {pragma}"), [], |r| r.get(0))
496        };
497        let positive = |value: i64| u32::try_from(value).ok().and_then(NonZeroU32::new);
498        let cache_size = read("cache_size")?;
499        Ok(SqliteSettingsInEffect {
500            busy_timeout: Duration::from_millis(unsigned(read("busy_timeout")?)?),
501            cache_size: if cache_size < 0 {
502                CacheSize::Kibibytes(unsigned(-cache_size)?)
503            } else {
504                CacheSize::Pages(unsigned(cache_size)?)
505            },
506            cache_spill: match read("cache_spill")? {
507                0 => CacheSpill::Off,
508                spill => CacheSpill::Pages(positive(spill).ok_or_else(unreported)?),
509            },
510            mmap_size: unsigned(read("mmap_size")?)?,
511            temp_store: match read("temp_store")? {
512                0 => TempStore::Default,
513                1 => TempStore::File,
514                2 => TempStore::Memory,
515                _ => return Err(unreported()),
516            },
517            threads: unsigned(read("threads")?)?,
518            journal_size_limit: match read("journal_size_limit")? {
519                -1 => JournalSizeLimit::Unlimited,
520                bytes => JournalSizeLimit::Bytes(unsigned(bytes)?),
521            },
522            wal_autocheckpoint: match read("wal_autocheckpoint")? {
523                0 => WalAutocheckpoint::Off,
524                checkpoint => {
525                    WalAutocheckpoint::Pages(positive(checkpoint).ok_or_else(unreported)?)
526                }
527            },
528            automatic_index: read("automatic_index")? != 0,
529            cell_size_check: read("cell_size_check")? != 0,
530            secure_delete: match read("secure_delete")? {
531                0 => SecureDelete::Off,
532                1 => SecureDelete::On,
533                2 => SecureDelete::Fast,
534                _ => return Err(unreported()),
535            },
536            fullfsync: read("fullfsync")? != 0,
537            checkpoint_fullfsync: read("checkpoint_fullfsync")? != 0,
538            analysis_limit: match read("analysis_limit")? {
539                0 => AnalysisLimit::Unlimited,
540                rows => AnalysisLimit::Rows(positive(rows).ok_or_else(unreported)?),
541            },
542            page_size: PageSize::from_bytes(read("page_size")?)?,
543            auto_vacuum: match read("auto_vacuum")? {
544                0 => AutoVacuum::Off,
545                1 => AutoVacuum::Full,
546                2 => AutoVacuum::Incremental,
547                _ => return Err(unreported()),
548            },
549        })
550    }
551
552    /// Checkpoint the WAL outside any transaction, on a connection in WAL
553    /// mode: the writer, or a checkpointer's own. SQLite reports the frames
554    /// unless it read nothing of the WAL, which it reports as busy with no
555    /// frames. Every checkpoint sqlive runs on a database, its checkpointer's
556    /// or the host's, goes through here. After each one that read the WAL,
557    /// except a `Noop`, which copies nothing, this connection sends the main
558    /// file's size to the database's watch, when it holds the watch
559    /// (`Database::checkpointed_size`).
560    pub fn wal_checkpoint(&self, mode: CheckpointMode) -> Result<WalCheckpoint, PolicyError<P>> {
561        let pragma = match mode {
562            CheckpointMode::Noop => "NOOP",
563            CheckpointMode::Passive => "PASSIVE",
564            CheckpointMode::Full => "FULL",
565            CheckpointMode::Restart => "RESTART",
566            CheckpointMode::Truncate => "TRUNCATE",
567        };
568        let (busy, log, checkpointed): (i64, i64, i64) =
569            self.query_row(&format!("PRAGMA main.wal_checkpoint({pragma})"), [], |r| {
570                Ok((r.get(0)?, r.get(1)?, r.get(2)?))
571            })?;
572        if (busy, log, checkpointed) == (1, -1, -1) {
573            return Ok(WalCheckpoint::NotStarted);
574        }
575        let frames = |count: i64| {
576            u32::try_from(count).map_err(|_overflow| crate::Error::Invariant {
577                what: "a checkpoint that read the WAL reports its frames",
578            })
579        };
580        let ran = WalCheckpoint::Ran {
581            busy: busy != 0,
582            log_frames: frames(log)?,
583            checkpointed_frames: frames(checkpointed)?,
584        };
585        if mode != CheckpointMode::Noop {
586            self.send_checkpointed_size()?;
587        }
588        Ok(ran)
589    }
590
591    /// Gather the query planner's statistics where they are missing or stale,
592    /// outside any captured transaction, so none records them (§4).
593    /// `0x10000` checks every table, not only those this connection read;
594    /// `0x10` reads at most SQLite's 2,000 rows of each index, or the app's
595    /// own lower `analysis_limit`, and fewer in a schema of over 100 tables
596    /// and indexes; `0x02` analyzes a table never analyzed, or one whose size
597    /// changed tenfold since. Tables whose statistics hold cost no reads.
598    ///
599    /// Then it forgets the statistics of each table under [`SMALL_TABLE`]
600    /// rows, which SQLite may plan to read whole: once such a table grows,
601    /// every statement would read all of it. Without statistics SQLite
602    /// plans each table as a large one, which a small table answers as
603    /// quickly. The connection keeps planning with the counts it found, so a
604    /// writable open runs this on a connection of its own.
605    pub(crate) fn optimize(&self) -> Result<(), PolicyError<P>> {
606        self.batch("PRAGMA optimize=0x10012")?;
607        let analyzed: bool = self.query_row(
608            "SELECT EXISTS(SELECT 1 FROM sqlite_schema WHERE name='sqlite_stat1')",
609            [],
610            |r| r.get(0),
611        )?;
612        if !analyzed {
613            return Ok(());
614        }
615        self.execute(
616            "DELETE FROM sqlite_stat1 WHERE tbl IN (SELECT tbl FROM sqlite_stat1
617             GROUP BY tbl HAVING max(CAST(stat AS INTEGER)) < ?1)",
618            [SMALL_TABLE],
619        )?;
620        Ok(())
621    }
622
623    /// Whether a run of [`Self::optimize`] would change statistics a table
624    /// keeps: `PRAGMA optimize`, told to name its work without doing it,
625    /// names each table whose size changed tenfold since its statistics,
626    /// and each one with rows and an index that has none: one never
627    /// analyzed, and every one under [`SMALL_TABLE`] rows. Reading at most
628    /// that many rows of each tells which a run would keep statistics for.
629    pub(crate) fn statistics_wanted(&self) -> Result<bool, PolicyError<P>> {
630        let named = self.query("PRAGMA optimize=0x10013", [], |r| r.get::<_, String>(0))?;
631        for analyze in named {
632            let table = analyze.strip_prefix("ANALYZE ").ok_or(crate::Error::Invariant {
633                what: "PRAGMA optimize names its work as ANALYZE statements",
634            })?;
635            let rows: i64 = self.query_row(
636                &format!("SELECT count(*) FROM (SELECT 1 FROM {table} LIMIT ?1)"),
637                [SMALL_TABLE],
638                |r| r.get(0),
639            )?;
640            if rows >= SMALL_TABLE {
641                return Ok(true);
642            }
643        }
644        Ok(false)
645    }
646
647    /// Gather the statistics as [`Self::optimize`] does, on a connection of
648    /// their own while the database stays open, and change the schema in the
649    /// same transaction: SQLite reads the statistics again only as it reads
650    /// the schema, so every connection, the read-only readers among them,
651    /// plans with the new ones from its next statement, this one included,
652    /// which so loads no count of a table whose statistics it forgot.
653    pub(crate) fn gather_statistics_for_every_connection(&mut self) -> Result<(), PolicyError<P>> {
654        self.transaction(|connection| {
655            connection.optimize()?;
656            connection.batch(
657                "CREATE TABLE _coven_statistics_gathered(gathered);
658                 DROP TABLE _coven_statistics_gathered",
659            )
660        })
661    }
662
663    /// Gather the statistics on this connection of their own, then close it.
664    pub(crate) fn gather_statistics(self) -> Result<(), PolicyError<P>> {
665        self.optimize()?;
666        self.close()
667    }
668
669    /// Give back free pages of an incrementally vacuumed database. SQLite
670    /// frees one page per result row, so every row is stepped.
671    pub fn incremental_vacuum(&self, pages: IncrementalVacuum) -> Result<(), PolicyError<P>> {
672        let sql = match pages {
673            IncrementalVacuum::All => "PRAGMA main.incremental_vacuum".to_owned(),
674            IncrementalVacuum::Pages(pages) => format!(
675                "PRAGMA main.incremental_vacuum({})",
676                int("incremental_vacuum", pages.get())?
677            ),
678        };
679        self.query(&sql, [], |_| Ok(()))?;
680        Ok(())
681    }
682
683    /// Give back the free pages of an incrementally vacuumed database past
684    /// the 4 MiB sqlive leaves for later writes to reuse; any other
685    /// auto-vacuum mode is the app's choice and keeps its meaning.
686    pub fn give_back_free_pages(&self) -> Result<(), PolicyError<P>> {
687        let pragma = |name: &str| -> Result<u64, PolicyError<P>> {
688            let value: i64 = self.query_row(&format!("PRAGMA main.{name}"), [], |r| r.get(0))?;
689            unsigned(value)
690        };
691        if pragma("auto_vacuum")? != 2 {
692            return Ok(());
693        }
694        let free = pragma("freelist_count")?;
695        let page_size = pragma("page_size")?;
696        let surplus = free.saturating_sub(KEPT_FREE_BYTES / page_size);
697        if let Some(pages) = u32::try_from(surplus).ok().and_then(NonZeroU32::new) {
698            self.incremental_vacuum(IncrementalVacuum::Pages(pages))?;
699        }
700        Ok(())
701    }
702}
703
704/// `value` as one of SQLite's 32-bit integer settings.
705fn int<F: HostError, R: HostError>(
706    setting: &'static str,
707    value: impl Into<u128>,
708) -> Result<i32, crate::Error<F, R>> {
709    i32::try_from(value.into()).map_err(|_overflow| crate::Error::SqliteSettingOutOfRange {
710        setting,
711        maximum: i32::MAX as u64,
712    })
713}
714
715/// `value` as one of SQLite's 64-bit integer settings.
716fn int64<F: HostError, R: HostError>(
717    setting: &'static str,
718    value: u64,
719) -> Result<i64, crate::Error<F, R>> {
720    i64::try_from(value).map_err(|_overflow| crate::Error::SqliteSettingOutOfRange {
721        setting,
722        maximum: i64::MAX as u64,
723    })
724}
725
726fn unsigned<T: TryFrom<i64>, F: HostError, R: HostError>(
727    value: i64,
728) -> Result<T, crate::Error<F, R>> {
729    T::try_from(value).map_err(|_overflow| unreported())
730}
731
732/// SQLite reported a setting outside the values it documents.
733fn unreported<F: HostError, R: HostError>() -> crate::Error<F, R> {
734    crate::Error::Invariant { what: "SQLite reports a setting within its documented values" }
735}
736
737#[cfg(test)]
738#[path = "sqlite_settings_tests.rs"]
739mod tests;