use std::path::PathBuf;
use std::sync::Arc;
use parking_lot::Mutex;
use rusqlite::Connection;
use crate::config::SqliteConfig;
use crate::error::{SqliteError, SqliteResult};
struct Inner {
config: SqliteConfig,
writer: Mutex<Connection>,
readers: Mutex<Vec<Connection>>,
}
#[derive(Clone)]
pub struct SqliteDatabase {
inner: Arc<Inner>,
}
impl SqliteDatabase {
pub fn open(config: SqliteConfig) -> SqliteResult<Self> {
if let Some(path) = &config.path {
if let Some(parent) = path.parent() {
if !parent.as_os_str().is_empty() {
std::fs::create_dir_all(parent).map_err(|error| {
SqliteError::Config(format!(
"could not create {}: {error}",
parent.display()
))
})?;
}
}
}
let writer = open_connection(&config, false)?;
Ok(Self {
inner: Arc::new(Inner {
config,
writer: Mutex::new(writer),
readers: Mutex::new(Vec::new()),
}),
})
}
pub fn from_env() -> SqliteResult<Self> {
Self::open(SqliteConfig::from_env()?)
}
pub fn in_memory() -> SqliteResult<Self> {
Self::open(SqliteConfig::in_memory())
}
pub fn arc(self) -> Arc<Self> {
Arc::new(self)
}
pub fn path(&self) -> Option<&PathBuf> {
self.inner.config.path.as_ref()
}
pub async fn write<F, T>(&self, work: F) -> SqliteResult<T>
where
F: FnOnce(&Connection) -> rusqlite::Result<T> + Send + 'static,
T: Send + 'static,
{
let inner = self.inner.clone();
blocking(move || {
let connection = inner.writer.lock();
work(&connection).map_err(SqliteError::from)
})
.await
}
pub async fn read<F, T>(&self, work: F) -> SqliteResult<T>
where
F: FnOnce(&Connection) -> rusqlite::Result<T> + Send + 'static,
T: Send + 'static,
{
let inner = self.inner.clone();
blocking(move || {
let Some(connection) = checkout(&inner)? else {
let connection = inner.writer.lock();
return work(&connection).map_err(SqliteError::from);
};
let result = work(&connection).map_err(SqliteError::from);
inner.readers.lock().push(connection);
result
})
.await
}
pub async fn transaction<F, T>(&self, work: F) -> SqliteResult<T>
where
F: FnOnce(&rusqlite::Transaction<'_>) -> rusqlite::Result<T> + Send + 'static,
T: Send + 'static,
{
let inner = self.inner.clone();
blocking(move || {
let mut connection = inner.writer.lock();
let transaction =
connection.transaction_with_behavior(rusqlite::TransactionBehavior::Immediate)?;
let value = work(&transaction)?;
transaction.commit()?;
Ok(value)
})
.await
}
pub async fn ping(&self) -> SqliteResult<()> {
self.read(|connection| connection.query_row("SELECT 1", [], |_| Ok(())))
.await
}
pub async fn close(&self) -> SqliteResult<()> {
self.inner.readers.lock().clear();
self.write(|connection| {
connection.pragma_update(None, "wal_checkpoint", "TRUNCATE")?;
Ok(())
})
.await
}
}
fn checkout(inner: &Inner) -> SqliteResult<Option<Connection>> {
if inner.config.max_readers == 0 {
return Ok(None);
}
if let Some(connection) = inner.readers.lock().pop() {
return Ok(Some(connection));
}
Ok(Some(open_connection(&inner.config, true)?))
}
fn open_connection(config: &SqliteConfig, read_only: bool) -> SqliteResult<Connection> {
use rusqlite::OpenFlags;
let connection = match &config.path {
None => Connection::open_in_memory()?,
Some(path) => {
let mut flags = OpenFlags::SQLITE_OPEN_URI | OpenFlags::SQLITE_OPEN_NO_MUTEX;
if read_only {
flags |= OpenFlags::SQLITE_OPEN_READ_ONLY;
} else {
flags |= OpenFlags::SQLITE_OPEN_READ_WRITE | OpenFlags::SQLITE_OPEN_CREATE;
}
Connection::open_with_flags(path, flags)?
}
};
connection.busy_timeout(config.busy_timeout)?;
connection.pragma_update(None, "cache_size", -(config.cache_kib as i64))?;
connection.pragma_update(None, "foreign_keys", "ON")?;
connection.pragma_update(None, "case_sensitive_like", "ON")?;
if !read_only {
if !config.is_in_memory() {
connection.pragma_update(None, "journal_mode", "WAL")?;
}
connection.pragma_update(
None,
"synchronous",
if config.synchronous_full {
"FULL"
} else {
"NORMAL"
},
)?;
}
Ok(connection)
}
async fn blocking<F, T>(work: F) -> SqliteResult<T>
where
F: FnOnce() -> SqliteResult<T> + Send + 'static,
T: Send + 'static,
{
tokio::task::spawn_blocking(work)
.await
.map_err(|error| SqliteError::Worker(error.to_string()))?
}
#[cfg(test)]
mod tests {
use super::*;
#[tokio::test]
async fn it_opens_and_answers() {
let database = SqliteDatabase::in_memory().expect("open");
database.ping().await.expect("ping");
}
#[tokio::test]
async fn a_write_is_visible_to_a_read() {
let database = SqliteDatabase::in_memory().expect("open");
database
.write(|c| c.execute("CREATE TABLE t (v TEXT)", []).map(|_| ()))
.await
.expect("create");
database
.write(|c| c.execute("INSERT INTO t VALUES ('x')", []).map(|_| ()))
.await
.expect("insert");
let value: String = database
.read(|c| c.query_row("SELECT v FROM t", [], |row| row.get(0)))
.await
.expect("read");
assert_eq!(value, "x");
}
#[tokio::test]
async fn a_file_database_serves_reads_from_the_pool() {
let directory = std::env::temp_dir().join(format!("wabot-sqlite-{}", std::process::id()));
let path = directory.join("nested").join("test.db");
let _ = std::fs::remove_dir_all(&directory);
let database = SqliteDatabase::open(SqliteConfig::at(&path)).expect("open");
assert!(path.exists(), "the parent directories were created");
database
.write(|c| {
c.execute("CREATE TABLE t (v INTEGER)", [])?;
c.execute("INSERT INTO t VALUES (7)", [])
})
.await
.expect("write");
let reads = (0..8).map(|_| {
let database = database.clone();
tokio::spawn(async move {
database
.read(|c| c.query_row("SELECT v FROM t", [], |row| row.get::<_, i64>(0)))
.await
})
});
for read in reads {
assert_eq!(read.await.expect("task").expect("read"), 7);
}
database.close().await.expect("close");
let _ = std::fs::remove_dir_all(&directory);
}
#[tokio::test]
async fn like_is_case_sensitive() {
let database = SqliteDatabase::in_memory().expect("open");
database
.write(|c| {
c.execute("CREATE TABLE t (v TEXT)", [])?;
c.execute("INSERT INTO t VALUES ('Ada')", [])
})
.await
.expect("setup");
let hits: i64 = database
.read(|c| {
c.query_row("SELECT COUNT(*) FROM t WHERE v LIKE 'ada%'", [], |r| {
r.get(0)
})
})
.await
.expect("query");
assert_eq!(hits, 0, "'ada%' must not match 'Ada'");
let hits: i64 = database
.read(|c| {
c.query_row("SELECT COUNT(*) FROM t WHERE v LIKE 'Ada%'", [], |r| {
r.get(0)
})
})
.await
.expect("query");
assert_eq!(hits, 1);
}
#[tokio::test]
async fn a_failed_transaction_rolls_back() {
let database = SqliteDatabase::in_memory().expect("open");
database
.write(|c| c.execute("CREATE TABLE t (v INTEGER)", []).map(|_| ()))
.await
.expect("create");
let result = database
.transaction(|t| {
t.execute("INSERT INTO t VALUES (1)", [])?;
Err::<(), _>(rusqlite::Error::InvalidQuery)
})
.await;
assert!(result.is_err());
let count: i64 = database
.read(|c| c.query_row("SELECT COUNT(*) FROM t", [], |row| row.get(0)))
.await
.expect("count");
assert_eq!(count, 0, "the insert was rolled back with the transaction");
}
}