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;