use rusqlite::Connection;
use crate::config::SqlitePragmas;
use nitr_core::{Error, Result};
const JOURNAL_MODES: &[&str] = &["delete", "truncate", "persist", "memory", "wal", "off"];
const PREPARED_STATEMENT_CACHE: usize = 64;
const SYNCHRONOUS_LEVELS: &[&str] = &["off", "normal", "full", "extra"];
impl SqlitePragmas {
pub fn apply(&self, conn: &Connection, path: &std::path::Path) -> Result {
let context = |what: &str, err: rusqlite::Error| {
Error::Config(format!(
"failed to set {what} on database {}: {err}",
path.display()
))
};
conn.busy_timeout(std::time::Duration::from_millis(self.busy_timeout))
.map_err(|err| context("the busy timeout", err))?;
let journal = self.journal_mode.to_ascii_lowercase();
if journal != "keep" {
if !JOURNAL_MODES.contains(&journal.as_str()) {
return Err(Error::Config(format!(
"unknown [database] journal_mode `{}`: expected one of {} or \"keep\"",
self.journal_mode,
JOURNAL_MODES.join(", ")
)));
}
let applied: String = conn
.pragma_update_and_check(None, "journal_mode", &journal, |row| row.get(0))
.map_err(|err| context("the journal mode", err))?;
if !applied.eq_ignore_ascii_case(&journal) {
tracing::warn!(
"database {} runs in `{applied}` journal mode, not the configured `{journal}`",
path.display()
);
}
}
let synchronous = self.synchronous.to_ascii_lowercase();
if !SYNCHRONOUS_LEVELS.contains(&synchronous.as_str()) {
return Err(Error::Config(format!(
"unknown [database] synchronous `{}`: expected one of {}",
self.synchronous,
SYNCHRONOUS_LEVELS.join(", ")
)));
}
conn.pragma_update(None, "synchronous", &synchronous)
.map_err(|err| context("the synchronous level", err))?;
conn.pragma_update(None, "foreign_keys", self.foreign_keys)
.map_err(|err| context("the foreign_keys pragma", err))?;
conn.pragma_update(None, "cache_size", self.cache_size)
.map_err(|err| context("the cache size", err))?;
Ok(())
}
}
pub fn open(path: &std::path::Path, pragmas: &SqlitePragmas) -> Result<Connection> {
let conn = Connection::open(path).map_err(|err| {
Error::Config(format!(
"failed to open database at {}: {err}",
path.display()
))
})?;
pragmas.apply(&conn, path)?;
conn.set_prepared_statement_cache_capacity(PREPARED_STATEMENT_CACHE);
conn.authorizer(Some(authorize))
.map_err(|err| Error::Config(format!("failed to install the SQL authorizer: {err}")))?;
Ok(conn)
}
fn authorize(ctx: rusqlite::hooks::AuthContext<'_>) -> rusqlite::hooks::Authorization {
use rusqlite::hooks::{AuthAction, Authorization};
match ctx.action {
AuthAction::Attach { filename: "" } => Authorization::Allow,
AuthAction::Attach { .. } | AuthAction::Detach { .. } => Authorization::Deny,
AuthAction::Unknown { code, .. }
if code == rusqlite::ffi::SQLITE_ATTACH || code == rusqlite::ffi::SQLITE_DETACH =>
{
Authorization::Deny
}
_ => Authorization::Allow,
}
}
#[cfg(test)]
mod tests {
use super::*;
fn temp_db(name: &str) -> std::path::PathBuf {
static NEXT: std::sync::atomic::AtomicU32 = std::sync::atomic::AtomicU32::new(0);
let id = NEXT.fetch_add(1, std::sync::atomic::Ordering::Relaxed);
let dir = std::env::temp_dir().join(format!("nitr-pragma-{}-{id}", std::process::id()));
std::fs::create_dir_all(&dir).expect("temp dir");
dir.join(name)
}
#[test]
fn sql_cannot_attach_other_database_files() {
let path = temp_db("attach.db");
let other = path.with_file_name("other.db");
let conn = open(&path, &SqlitePragmas::default()).expect("open");
conn.execute_batch("CREATE TABLE t (x)").expect("create");
for sql in [
format!("ATTACH DATABASE '{}' AS x", other.display()),
"ATTACH DATABASE ':memory:' AS m".to_string(),
format!("VACUUM INTO '{}'", other.display()),
format!("ATTACH DATABASE ('{}' || '') AS y", other.display()),
format!("ATTACH DATABASE (SELECT '{}') AS z", other.display()),
] {
let err = conn.execute_batch(&sql).expect_err(&sql);
assert!(
err.to_string().contains("authoriz"),
"`{sql}` must be refused by the authorizer, got: {err}"
);
}
let err = conn
.execute(
"ATTACH DATABASE ?1 AS p",
[other.to_string_lossy().as_ref()],
)
.expect_err("bound attach");
assert!(err.to_string().contains("authoriz"), "bound: {err}");
assert!(!other.exists(), "no file may be created through SQL");
conn.execute_batch("VACUUM")
.expect("plain VACUUM stays available");
drop(conn);
let _ = std::fs::remove_dir_all(path.parent().expect("dir"));
}
#[test]
fn the_defaults_land_on_the_connection() {
let path = temp_db("defaults.db");
let _ = std::fs::remove_file(&path);
let conn = open(&path, &SqlitePragmas::default()).expect("open");
let journal: String = conn
.pragma_query_value(None, "journal_mode", |row| row.get(0))
.expect("journal_mode");
assert_eq!(journal.to_ascii_lowercase(), "wal");
let foreign_keys: i64 = conn
.pragma_query_value(None, "foreign_keys", |row| row.get(0))
.expect("foreign_keys");
assert_eq!(foreign_keys, 1, "foreign keys must be enforced");
let synchronous: i64 = conn
.pragma_query_value(None, "synchronous", |row| row.get(0))
.expect("synchronous");
assert_eq!(synchronous, 1, "NORMAL");
drop(conn);
let _ = std::fs::remove_file(&path);
}
#[test]
fn keep_leaves_the_existing_mode_alone() {
let path = temp_db("keep.db");
let _ = std::fs::remove_file(&path);
drop(open(&path, &SqlitePragmas::default()).expect("open wal"));
let conn = open(
&path,
&SqlitePragmas {
journal_mode: "keep".into(),
..Default::default()
},
)
.expect("reopen");
let journal: String = conn
.pragma_query_value(None, "journal_mode", |row| row.get(0))
.expect("journal_mode");
assert_eq!(journal.to_ascii_lowercase(), "wal");
drop(conn);
let _ = std::fs::remove_file(&path);
}
#[test]
fn a_bogus_pragma_value_is_refused_rather_than_interpolated() {
let path = temp_db("bogus.db");
let _ = std::fs::remove_file(&path);
let err = open(
&path,
&SqlitePragmas {
journal_mode: "wal; DROP TABLE users".into(),
..Default::default()
},
)
.expect_err("must be refused");
assert!(err.to_string().contains("journal_mode"), "{err}");
let _ = std::fs::remove_file(&path);
}
}