use std::marker::PhantomData;
use std::sync::atomic::{AtomicBool, Ordering};
use rusqlite::types::Value as SqlValue;
use serde::de::DeserializeOwned;
use serde::Serialize;
use wabot_core::entity::{Entity, EntityFields};
use crate::database::SqliteDatabase;
use crate::error::{SqliteError, SqliteResult};
pub struct PromotedColumn<D> {
pub name: &'static str,
pub ddl_type: &'static str,
pub value: fn(&D) -> Option<String>,
}
impl<D> Clone for PromotedColumn<D> {
fn clone(&self) -> Self {
*self
}
}
impl<D> Copy for PromotedColumn<D> {}
pub type IndexDdl = &'static str;
pub struct SqliteJsonbStore<D>
where
D: EntityFields + Serialize + DeserializeOwned + Send + Sync + 'static,
{
database: SqliteDatabase,
table_name: String,
qualified: String,
promoted: Vec<PromotedColumn<D>>,
indexes: Vec<IndexDdl>,
ready: AtomicBool,
_marker: PhantomData<fn() -> D>,
}
impl<D> SqliteJsonbStore<D>
where
D: EntityFields + Serialize + DeserializeOwned + Send + Sync + 'static,
{
pub fn new(database: SqliteDatabase, table: impl Into<String>) -> Self {
let table_name = table.into();
let qualified = format!("\"{}\"", table_name.replace('"', "\"\""));
Self {
database,
table_name,
qualified,
promoted: Vec::new(),
indexes: Vec::new(),
ready: AtomicBool::new(false),
_marker: PhantomData,
}
}
pub fn with_promoted_column(mut self, column: PromotedColumn<D>) -> Self {
self.promoted.push(column);
self
}
pub fn with_index(mut self, ddl: IndexDdl) -> Self {
self.indexes.push(ddl);
self
}
pub fn placement(&self) -> crate::query_sql::FieldPlacement {
crate::query_sql::FieldPlacement::promoted(
self.promoted.iter().map(|column| column.name.to_string()),
)
}
pub fn database(&self) -> &SqliteDatabase {
&self.database
}
pub fn table(&self) -> &str {
&self.qualified
}
pub async fn ensure_ready(&self) -> SqliteResult<()> {
if self.ready.load(Ordering::Relaxed) {
return Ok(());
}
let ddl = self.ddl_statements();
let indexes = self.indexes.clone();
self.database
.write(move |connection| {
for statement in ddl {
connection.execute(&statement, [])?;
}
for index in indexes {
if let Err(error) = connection.execute(index, []) {
tracing::warn!(ddl = %index, %error, "index creation failed");
}
}
Ok(())
})
.await?;
self.ready.store(true, Ordering::Relaxed);
Ok(())
}
fn ddl_statements(&self) -> Vec<String> {
let mut columns = vec![
"\"id\" TEXT PRIMARY KEY".to_string(),
"\"created_at\" INTEGER".to_string(),
"\"data\" TEXT".to_string(),
];
for column in &self.promoted {
columns.push(format!("\"{}\" {}", column.name, column.ddl_type));
}
let mut statements = vec![format!(
"CREATE TABLE IF NOT EXISTS {} ({})",
self.qualified,
columns.join(", ")
)];
statements.push(format!(
"CREATE INDEX IF NOT EXISTS \"{}_created_at_id\" ON {} (\"created_at\" DESC, \"id\" DESC)",
self.table_name.replace('"', ""),
self.qualified
));
statements
}
pub async fn migrate_promoted_columns(&self) -> SqliteResult<()> {
self.ensure_ready().await?;
let table = self.qualified.clone();
let wanted: Vec<(String, String)> = self
.promoted
.iter()
.map(|column| (column.name.to_string(), column.ddl_type.to_string()))
.collect();
self.database
.write(move |connection| {
let existing: std::collections::HashSet<String> = connection
.prepare(&format!(
"SELECT name FROM pragma_table_info('{}')",
table.trim_matches('"')
))?
.query_map([], |row| row.get::<_, String>(0))?
.collect::<rusqlite::Result<_>>()?;
for (name, ddl_type) in wanted {
if !existing.contains(&name) {
connection.execute(
&format!("ALTER TABLE {table} ADD COLUMN \"{name}\" {ddl_type}"),
[],
)?;
}
}
Ok(())
})
.await
}
pub async fn create(&self, entity: &Entity<D>) -> SqliteResult<()> {
self.ensure_ready().await?;
let sql = self.insert_sql(false);
let values = self.write_values(entity)?;
self.database
.write(move |connection| connection.execute(&sql, rusqlite::params_from_iter(values)))
.await?;
Ok(())
}
pub async fn save(&self, entity: &Entity<D>) -> SqliteResult<()> {
self.ensure_ready().await?;
let sql = self.insert_sql(true);
let values = self.write_values(entity)?;
self.database
.write(move |connection| connection.execute(&sql, rusqlite::params_from_iter(values)))
.await?;
Ok(())
}
pub async fn update(&self, entity: &Entity<D>) -> SqliteResult<bool> {
self.ensure_ready().await?;
let mut sets = vec![
"\"created_at\" = ?2".to_string(),
"\"data\" = ?3".to_string(),
];
for (index, column) in self.promoted.iter().enumerate() {
sets.push(format!("\"{}\" = ?{}", column.name, index + 4));
}
let sql = format!(
"UPDATE {} SET {} WHERE \"id\" = ?1",
self.qualified,
sets.join(", ")
);
let values = self.write_values(entity)?;
let affected = self
.database
.write(move |connection| connection.execute(&sql, rusqlite::params_from_iter(values)))
.await?;
Ok(affected > 0)
}
pub async fn delete(&self, id: &str) -> SqliteResult<()> {
self.ensure_ready().await?;
let sql = format!("DELETE FROM {} WHERE \"id\" = ?1", self.qualified);
let id = id.to_string();
self.database
.write(move |connection| connection.execute(&sql, [id]))
.await?;
Ok(())
}
pub async fn find_by_id(&self, id: &str) -> SqliteResult<Option<Entity<D>>> {
let sql = format!(
"SELECT \"data\" FROM {} WHERE \"id\" = ?1 LIMIT 1",
self.qualified
);
self.query_opt(&sql, vec![SqlValue::Text(id.to_string())])
.await
}
pub async fn query(&self, sql: &str, values: Vec<SqlValue>) -> SqliteResult<Vec<Entity<D>>> {
self.ensure_ready().await?;
let sql = sql.to_string();
let documents: Vec<String> = self
.database
.read(move |connection| {
connection
.prepare(&sql)?
.query_map(rusqlite::params_from_iter(values), |row| {
row.get::<_, String>(0)
})?
.collect()
})
.await?;
documents.iter().map(|json| decode::<D>(json)).collect()
}
pub async fn query_opt(
&self,
sql: &str,
values: Vec<SqlValue>,
) -> SqliteResult<Option<Entity<D>>> {
Ok(self.query(sql, values).await?.into_iter().next())
}
pub async fn count(&self, sql: &str, values: Vec<SqlValue>) -> SqliteResult<i64> {
self.ensure_ready().await?;
let sql = sql.to_string();
self.database
.read(move |connection| {
connection.query_row(&sql, rusqlite::params_from_iter(values), |row| row.get(0))
})
.await
}
pub async fn find_by_query(
&self,
query: &wabot_core::repository::Query,
) -> SqliteResult<Vec<Entity<D>>> {
let built = crate::query_sql::build_query_sql(query, &self.placement(), None)
.map_err(|error| SqliteError::Other(error.to_string()))?;
let values = crate::query_sql::bind_values(query)
.map_err(|error| SqliteError::Other(error.to_string()))?;
let sql = format!("SELECT \"data\" FROM {}{}", self.qualified, built.sql);
self.query(&sql, values.iter().map(to_sql_value).collect())
.await
}
fn insert_sql(&self, upsert: bool) -> String {
let mut columns = vec![
"id".to_string(),
"created_at".to_string(),
"data".to_string(),
];
for column in &self.promoted {
columns.push(column.name.to_string());
}
let quoted: Vec<String> = columns.iter().map(|c| format!("\"{c}\"")).collect();
let placeholders: Vec<String> = (1..=columns.len()).map(|i| format!("?{i}")).collect();
let mut sql = format!(
"INSERT INTO {} ({}) VALUES ({})",
self.qualified,
quoted.join(", "),
placeholders.join(", ")
);
if upsert {
let updates: Vec<String> = quoted
.iter()
.skip(1)
.map(|column| format!("{column} = excluded.{column}"))
.collect();
sql.push_str(&format!(
" ON CONFLICT (\"id\") DO UPDATE SET {}",
updates.join(", ")
));
}
sql
}
fn write_values(&self, entity: &Entity<D>) -> SqliteResult<Vec<SqlValue>> {
let data = entity.data();
let base = data.entity();
let id = base
.id
.clone()
.ok_or_else(|| SqliteError::Other("entity has no id".into()))?;
let created_at = base
.created_at
.unwrap_or_else(|| chrono::Utc::now().timestamp_millis());
let json = serde_json::to_string(data)?;
let mut values = vec![
SqlValue::Text(id),
SqlValue::Integer(created_at),
SqlValue::Text(json),
];
for column in &self.promoted {
values.push(match (column.value)(data) {
Some(value) => SqlValue::Text(value),
None => SqlValue::Null,
});
}
Ok(values)
}
}
pub fn to_sql_value(value: &serde_json::Value) -> SqlValue {
match value {
serde_json::Value::Null => SqlValue::Null,
serde_json::Value::Bool(flag) => SqlValue::Integer(i64::from(*flag)),
serde_json::Value::Number(number) => match (number.as_i64(), number.as_f64()) {
(Some(int), _) => SqlValue::Integer(int),
(None, Some(float)) => SqlValue::Real(float),
_ => SqlValue::Text(number.to_string()),
},
serde_json::Value::String(text) => SqlValue::Text(text.clone()),
other => SqlValue::Text(other.to_string()),
}
}
fn decode<D>(json: &str) -> SqliteResult<Entity<D>>
where
D: EntityFields + Serialize + DeserializeOwned + Send + Sync + 'static,
{
Ok(Entity::new(serde_json::from_str::<D>(json)?))
}
#[cfg(test)]
mod tests {
use super::*;
use serde::{Deserialize, Serialize};
use wabot_core::entity::EntityData;
#[derive(Debug, Clone, Serialize, Deserialize)]
struct ThingData {
#[serde(flatten)]
base: EntityData,
name: String,
total: i64,
}
impl EntityFields for ThingData {
fn entity(&self) -> &EntityData {
&self.base
}
fn entity_mut(&mut self) -> &mut EntityData {
&mut self.base
}
}
fn thing(name: &str, total: i64) -> Entity<ThingData> {
Entity::create(ThingData {
base: EntityData::default(),
name: name.to_string(),
total,
})
}
async fn store() -> SqliteJsonbStore<ThingData> {
SqliteJsonbStore::new(SqliteDatabase::in_memory().expect("open"), "things")
.with_promoted_column(PromotedColumn {
name: "owner_id",
ddl_type: "TEXT",
value: |d| Some(d.name.clone()),
})
}
#[tokio::test]
async fn insert_sql_covers_promoted_columns() {
let sql = store().await.insert_sql(false);
assert_eq!(
sql,
"INSERT INTO \"things\" (\"id\", \"created_at\", \"data\", \"owner_id\") \
VALUES (?1, ?2, ?3, ?4)"
);
}
#[tokio::test]
async fn upsert_sql_updates_every_column_but_the_key() {
let sql = store().await.insert_sql(true);
assert!(sql.contains("ON CONFLICT (\"id\") DO UPDATE SET"));
assert!(sql.contains("\"data\" = excluded.\"data\""));
assert!(
!sql.contains("\"id\" = excluded.\"id\""),
"the conflict key must not be in the SET list"
);
}
#[tokio::test]
async fn an_entity_round_trips() {
let store = store().await;
let entity = thing("Ada", 10);
store.create(&entity).await.expect("create");
let found = store
.find_by_id(entity.id())
.await
.expect("find")
.expect("present");
assert_eq!(found.data().name, "Ada");
assert_eq!(found.data().total, 10);
assert_eq!(found.id(), entity.id());
}
#[tokio::test]
async fn create_refuses_a_duplicate_but_save_upserts() {
let store = store().await;
let entity = thing("Ada", 1);
store.create(&entity).await.expect("create");
assert!(store.create(&entity).await.is_err(), "the id is taken");
let mut updated = entity.clone();
updated.update(|data| data.total = 2);
store.save(&updated).await.expect("save");
let found = store.find_by_id(entity.id()).await.expect("find").unwrap();
assert_eq!(found.data().total, 2);
}
#[tokio::test]
async fn update_reports_whether_a_row_matched() {
let store = store().await;
let entity = thing("Ada", 1);
assert!(
!store.update(&entity).await.expect("update"),
"nothing to update yet"
);
store.create(&entity).await.expect("create");
assert!(store.update(&entity).await.expect("update"));
}
#[tokio::test]
async fn a_promoted_column_is_written_and_indexable() {
let store = store().await;
store.create(&thing("Ada", 1)).await.expect("create");
let count = store
.count(
"SELECT COUNT(*) FROM \"things\" WHERE \"owner_id\" = ?1",
vec![SqlValue::Text("Ada".into())],
)
.await
.expect("count");
assert_eq!(count, 1);
}
#[tokio::test]
async fn a_document_field_compares_numerically() {
let store = store().await;
for total in [9, 10, 100] {
store.create(&thing("x", total)).await.expect("create");
}
let found = store
.find_by_query(&wabot_core::repository::Query::new().gt("total", 9))
.await
.expect("query");
let mut totals: Vec<i64> = found.iter().map(|e| e.data().total).collect();
totals.sort();
assert_eq!(
totals,
vec![10, 100],
"lexicographic comparison would have dropped 100 and kept nothing above 9"
);
}
#[tokio::test]
async fn membership_and_ordering_survive_a_real_query() {
let store = store().await;
for (name, total) in [("a", 1), ("b", 2), ("c", 3)] {
store.create(&thing(name, total)).await.expect("create");
}
let found = store
.find_by_query(
&wabot_core::repository::Query::new()
.is_in("name", serde_json::json!(["a", "c"]))
.order_desc("total"),
)
.await
.expect("query");
assert_eq!(
found
.iter()
.map(|e| e.data().name.as_str())
.collect::<Vec<_>>(),
vec!["c", "a"]
);
}
#[tokio::test]
async fn a_new_promoted_column_is_added_to_an_existing_table() {
let database = SqliteDatabase::in_memory().expect("open");
let first: SqliteJsonbStore<ThingData> = SqliteJsonbStore::new(database.clone(), "things");
first.ensure_ready().await.expect("ready");
let second = SqliteJsonbStore::<ThingData>::new(database.clone(), "things")
.with_promoted_column(PromotedColumn {
name: "total_col",
ddl_type: "INTEGER",
value: |d| Some(d.total.to_string()),
});
second.migrate_promoted_columns().await.expect("migrate");
second
.migrate_promoted_columns()
.await
.expect("migrating again is a no-op");
second.create(&thing("Ada", 42)).await.expect("create");
let count = second
.count(
"SELECT COUNT(*) FROM \"things\" WHERE \"total_col\" = ?1",
vec![SqlValue::Text("42".into())],
)
.await
.expect("count");
assert_eq!(count, 1);
}
#[tokio::test]
async fn json_numbers_bind_as_numbers() {
assert_eq!(
to_sql_value(&serde_json::json!(7)),
SqlValue::Integer(7),
"an integer must not become text, or ordering comparisons go lexicographic"
);
assert_eq!(to_sql_value(&serde_json::json!(1.5)), SqlValue::Real(1.5));
assert_eq!(to_sql_value(&serde_json::json!(true)), SqlValue::Integer(1));
assert_eq!(to_sql_value(&serde_json::json!(null)), SqlValue::Null);
}
}