use fv_mac::{where_sql, Bindings, Taxonomy};
use fv_value::Value;
use rusqlite::types::ValueRef;
use rusqlite::Connection;
use crate::{ConnectError, Row};
fn ident(name: &str) -> String {
format!("\"{}\"", name.replace('"', "\"\""))
}
fn cell(v: ValueRef<'_>) -> Value {
match v {
ValueRef::Null => Value::Null,
ValueRef::Integer(i) => Value::Num(i as f64),
ValueRef::Real(f) => Value::Num(f),
ValueRef::Text(t) => Value::Str(String::from_utf8_lossy(t).to_string()),
ValueRef::Blob(b) => Value::Str(String::from_utf8_lossy(b).to_string()),
}
}
fn to_sql(v: &Value) -> rusqlite::types::Value {
match v {
Value::Null => rusqlite::types::Value::Null,
Value::Bool(b) => rusqlite::types::Value::Integer(*b as i64),
Value::Num(n) if n.fract() == 0.0 && n.abs() < i64::MAX as f64 => rusqlite::types::Value::Integer(*n as i64),
Value::Num(n) => rusqlite::types::Value::Real(*n),
Value::Str(s) => rusqlite::types::Value::Text(s.clone()),
other => {
rusqlite::types::Value::Text(serde_json::to_string(&fv_value::value_to_json(other)).unwrap_or_default())
}
}
}
pub fn read_sqlite_with_mac_sync(
path: &str,
table: &str,
taxonomy: &Taxonomy,
grants: &Bindings,
label_column: impl Fn(&str) -> String,
limit: Option<usize>,
) -> Result<Vec<Row>, ConnectError> {
let conn = Connection::open(path).map_err(|e| ConnectError(format!("open {path}: {e}")))?;
let predicate = where_sql(taxonomy, grants, &label_column);
let mut sql = format!("SELECT * FROM {} WHERE {predicate}", ident(table));
if let Some(n) = limit {
sql.push_str(&format!(" LIMIT {n}"));
}
let mut stmt = conn
.prepare(&sql)
.map_err(|e| ConnectError(format!("query `{sql}`: {e}")))?;
let names: Vec<String> = stmt.column_names().iter().map(|s| s.to_string()).collect();
let mut rows = stmt
.query([])
.map_err(|e| ConnectError(format!("query `{sql}`: {e}")))?;
let mut out = Vec::new();
while let Some(r) = rows.next().map_err(|e| ConnectError(format!("row: {e}")))? {
let mut row: Row = Vec::with_capacity(names.len());
for (i, name) in names.iter().enumerate() {
row.push((
name.clone(),
cell(r.get_ref(i).map_err(|e| ConnectError(format!("cell {name}: {e}")))?),
));
}
out.push(row);
}
Ok(out)
}
pub fn write_and_promote_sqlite_sync(
path: &str,
table: &str,
columns: &[String],
rows: &[Row],
) -> Result<(), ConnectError> {
let mut conn = Connection::open(path).map_err(|e| ConnectError(format!("open {path}: {e}")))?;
let cols: Vec<String> = if columns.is_empty() {
let mut seen = Vec::new();
for r in rows {
for (k, _) in r {
if !seen.contains(k) {
seen.push(k.clone());
}
}
}
seen
} else {
columns.to_vec()
};
if cols.is_empty() {
return Err(ConnectError("write_and_promote: no columns".into()));
}
let stg = format!("{table}__stg");
let tx = conn.transaction().map_err(|e| ConnectError(format!("tx: {e}")))?;
tx.execute_batch(&format!(
"DROP TABLE IF EXISTS {stg_i}; CREATE TABLE {stg_i} ({cols_i});",
stg_i = ident(&stg),
cols_i = cols.iter().map(|c| ident(c)).collect::<Vec<_>>().join(", "),
))
.map_err(|e| ConnectError(format!("stage {stg}: {e}")))?;
{
let placeholders = vec!["?"; cols.len()].join(", ");
let mut ins = tx
.prepare(&format!("INSERT INTO {} VALUES ({placeholders})", ident(&stg)))
.map_err(|e| ConnectError(format!("insert prep: {e}")))?;
for r in rows {
let by_name: std::collections::HashMap<&str, &Value> = r.iter().map(|(k, v)| (k.as_str(), v)).collect();
let params: Vec<rusqlite::types::Value> = cols
.iter()
.map(|c| {
by_name
.get(c.as_str())
.map(|v| to_sql(v))
.unwrap_or(rusqlite::types::Value::Null)
})
.collect();
ins.execute(rusqlite::params_from_iter(params))
.map_err(|e| ConnectError(format!("insert: {e}")))?;
}
}
tx.execute_batch(&format!(
"DROP TABLE IF EXISTS {table_i}; ALTER TABLE {stg_i} RENAME TO {table_i};",
table_i = ident(table),
stg_i = ident(&stg),
))
.map_err(|e| ConnectError(format!("promote {table}: {e}")))?;
tx.commit().map_err(|e| ConnectError(format!("commit: {e}")))
}
#[cfg(test)]
mod tests {
use super::*;
use fv_mac::{Axis, Operator};
fn taxonomy() -> Taxonomy {
Taxonomy {
axes: vec![Axis {
name: "classification".into(),
operator: Operator::Dominance,
}],
}
}
fn row(rid: &str, level: f64) -> Row {
vec![
("rid".into(), Value::Str(rid.into())),
("weightKg".into(), Value::Num(12.5)),
("classification_level".into(), Value::Num(level)),
]
}
#[test]
fn write_promote_read_roundtrip_with_mac_pushdown() {
let dir = std::env::temp_dir().join(format!("fv-sqlite-test-{}", std::process::id()));
std::fs::create_dir_all(&dir).unwrap();
let path = dir.join("t.db");
let path = path.to_str().unwrap();
let table = "curated.things";
write_and_promote_sqlite_sync(path, table, &[], &[row("r-1", 0.0), row("r-2", 40.0)]).unwrap();
let all = |grants: &Bindings| {
read_sqlite_with_mac_sync(path, table, &taxonomy(), grants, |a| format!("{a}_level"), None).unwrap()
};
let mut cleared = Bindings::new();
cleared.insert("classification".into(), fv_mac::Val::Level(40.0));
assert_eq!(all(&cleared).len(), 2);
let uncleared = Bindings::new();
let visible = all(&uncleared);
assert_eq!(visible.len(), 1);
assert!(matches!(&visible[0][0].1, Value::Str(s) if s == "r-1"));
write_and_promote_sqlite_sync(path, table, &[], &[row("r-9", 0.0)]).unwrap();
assert_eq!(all(&cleared).len(), 1);
std::fs::remove_dir_all(&dir).ok();
}
}