improv_storage_sql/
backend.rs

1//! Backend abstraction over SQL drivers.
2//!
3//! Both drivers produce a common [`Table`] of [`Cell`]s, so the column→model
4//! mapping in `lib.rs` is backend-agnostic. Data values always cross the
5//! boundary as typed cells; only validated identifiers ever reach a query
6//! string (see `ident`/`sanitize_ident`).
7
8use crate::{Result, SqlError};
9use improv_core_model::Value;
10
11/// A single result-cell value, normalized across drivers.
12#[derive(Debug, Clone, PartialEq)]
13pub enum Cell {
14    Null,
15    Int(i64),
16    Float(f64),
17    Text(String),
18    Bool(bool),
19}
20
21impl Cell {
22    /// Stringify a dimension cell (used to intern items by name).
23    pub fn as_text(&self) -> String {
24        match self {
25            Cell::Null => String::new(),
26            Cell::Int(i) => i.to_string(),
27            Cell::Float(f) => f.to_string(),
28            Cell::Text(t) => t.clone(),
29            Cell::Bool(b) => b.to_string(),
30        }
31    }
32
33    /// Coerce a value cell to the numeric measure value.
34    pub fn as_number(&self) -> Result<f64> {
35        match self {
36            Cell::Int(i) => Ok(*i as f64),
37            Cell::Float(f) => Ok(*f),
38            Cell::Bool(b) => Ok(if *b { 1.0 } else { 0.0 }),
39            Cell::Text(t) => t
40                .parse::<f64>()
41                .map_err(|_| SqlError::Import(format!("value {t:?} is not numeric"))),
42            Cell::Null => Err(SqlError::Import("value column is NULL".into())),
43        }
44    }
45
46    /// Map a cell to a model [`Value`] (Number/Bool/Text), shared across
47    /// backends. Ints/Floats → Number, Bool → Boolean, Text → Text, Null → Text("").
48    pub fn to_value(&self) -> Value {
49        match self {
50            Cell::Null => Value::Text(String::new()),
51            Cell::Int(i) => Value::Number(*i as f64),
52            Cell::Float(f) => Value::Number(*f),
53            Cell::Text(t) => Value::Text(t.clone()),
54            Cell::Bool(b) => Value::Boolean(*b),
55        }
56    }
57}
58
59/// A materialized query result: column names + row-major cells.
60pub struct Table {
61    pub columns: Vec<String>,
62    pub rows: Vec<Vec<Cell>>,
63}
64
65impl Table {
66    /// Index of a named column, or a clear import error if absent.
67    pub fn column_index(&self, name: &str) -> Result<usize> {
68        self.columns
69            .iter()
70            .position(|c| c == name)
71            .ok_or_else(|| SqlError::Import(format!("no column '{name}' in query result")))
72    }
73}
74
75/// The operations `import_query`/`export_measure`/`refresh_sql_measure` need
76/// from a connection. Implemented for `&mut Backend` (SQLite or Postgres) and,
77/// for backward compatibility with SQLite-only callers, `&rusqlite::Connection`.
78pub trait SqlConn {
79    fn query(&mut self, sql: &str) -> Result<Table>;
80    fn execute(&mut self, sql: &str, params: &[Cell]) -> Result<()>;
81    /// Positional placeholder for the `n`-th (1-based) parameter.
82    fn placeholder(&self, n: usize) -> String;
83    /// SQL type keyword for a floating value column in `CREATE TABLE`.
84    fn real_type(&self) -> &'static str;
85}
86
87impl SqlConn for &mut Backend {
88    fn query(&mut self, sql: &str) -> Result<Table> {
89        Backend::query(self, sql)
90    }
91    fn execute(&mut self, sql: &str, params: &[Cell]) -> Result<()> {
92        Backend::execute(self, sql, params)
93    }
94    fn placeholder(&self, n: usize) -> String {
95        Backend::placeholder(self, n)
96    }
97    fn real_type(&self) -> &'static str {
98        Backend::real_type(self)
99    }
100}
101
102impl SqlConn for &rusqlite::Connection {
103    fn query(&mut self, sql: &str) -> Result<Table> {
104        sqlite_query(self, sql)
105    }
106    fn execute(&mut self, sql: &str, params: &[Cell]) -> Result<()> {
107        let bound: Vec<rusqlite::types::Value> = params.iter().map(cell_to_rusqlite).collect();
108        rusqlite::Connection::execute(self, sql, rusqlite::params_from_iter(bound))?;
109        Ok(())
110    }
111    fn placeholder(&self, _n: usize) -> String {
112        "?".to_string()
113    }
114    fn real_type(&self) -> &'static str {
115        "REAL"
116    }
117}
118
119/// An open connection to one of the supported SQL backends. Data values are
120/// always passed to `execute` as [`Cell`] parameters (never interpolated).
121pub enum Backend {
122    Sqlite(rusqlite::Connection),
123    Postgres(postgres::Client),
124    Duckdb(duckdb::Connection),
125}
126
127impl Backend {
128    /// Run a `SELECT` and materialize its rows.
129    pub fn query(&mut self, sql: &str) -> Result<Table> {
130        match self {
131            Backend::Sqlite(conn) => sqlite_query(conn, sql),
132            Backend::Postgres(client) => crate::pg::pg_query(client, sql),
133            Backend::Duckdb(conn) => crate::duck::duck_query(conn, sql),
134        }
135    }
136
137    /// Run a statement, binding `params` positionally as data.
138    pub fn execute(&mut self, sql: &str, params: &[Cell]) -> Result<()> {
139        match self {
140            Backend::Sqlite(conn) => {
141                let bound: Vec<rusqlite::types::Value> =
142                    params.iter().map(cell_to_rusqlite).collect();
143                conn.execute(sql, rusqlite::params_from_iter(bound))?;
144                Ok(())
145            }
146            Backend::Postgres(client) => crate::pg::pg_execute(client, sql, params),
147            Backend::Duckdb(conn) => crate::duck::duck_execute(conn, sql, params),
148        }
149    }
150
151    /// Positional placeholder for the `n`-th (1-based) parameter, per driver:
152    /// `?` for SQLite, `$n` for Postgres.
153    pub fn placeholder(&self, n: usize) -> String {
154        match self {
155            Backend::Sqlite(_) => "?".to_string(),
156            Backend::Postgres(_) => format!("${n}"),
157            Backend::Duckdb(_) => "?".to_string(),
158        }
159    }
160
161    /// SQL type keyword for a floating value column in a `CREATE TABLE`.
162    pub fn real_type(&self) -> &'static str {
163        match self {
164            Backend::Sqlite(_) => "REAL",
165            Backend::Postgres(_) => "DOUBLE PRECISION",
166            Backend::Duckdb(_) => "DOUBLE",
167        }
168    }
169}
170
171fn cell_to_rusqlite(c: &Cell) -> rusqlite::types::Value {
172    use rusqlite::types::Value as V;
173    match c {
174        Cell::Null => V::Null,
175        Cell::Int(i) => V::Integer(*i),
176        Cell::Float(f) => V::Real(*f),
177        Cell::Text(t) => V::Text(t.clone()),
178        Cell::Bool(b) => V::Integer(*b as i64),
179    }
180}
181
182fn sqlite_query(conn: &rusqlite::Connection, sql: &str) -> Result<Table> {
183    use rusqlite::types::ValueRef;
184    let mut stmt = conn.prepare(sql)?;
185    // SQLite's own verdict on whether the statement can write: refuse it.
186    if !stmt.readonly() {
187        return Err(SqlError::Import(
188            "refusing SQL query: statement is not read-only".into(),
189        ));
190    }
191    let columns: Vec<String> = stmt.column_names().iter().map(|s| s.to_string()).collect();
192    let ncols = columns.len();
193    let mut rows_iter = stmt.query([])?;
194    let mut rows = Vec::new();
195    while let Some(row) = rows_iter.next()? {
196        let mut cells = Vec::with_capacity(ncols);
197        for i in 0..ncols {
198            cells.push(match row.get_ref(i)? {
199                ValueRef::Null => Cell::Null,
200                ValueRef::Integer(v) => Cell::Int(v),
201                ValueRef::Real(v) => Cell::Float(v),
202                ValueRef::Text(t) => Cell::Text(String::from_utf8_lossy(t).into_owned()),
203                ValueRef::Blob(_) => return Err(SqlError::Import("blob cell value".into())),
204            });
205        }
206        rows.push(cells);
207    }
208    Ok(Table { columns, rows })
209}
210
211#[cfg(test)]
212#[cfg_attr(coverage_nightly, coverage(off))]
213mod tests {
214    use super::*;
215
216    #[test]
217    fn cell_to_value_maps_types() {
218        assert_eq!(Cell::Int(3).to_value(), Value::Number(3.0));
219        assert_eq!(Cell::Float(2.5).to_value(), Value::Number(2.5));
220        assert_eq!(Cell::Bool(true).to_value(), Value::Boolean(true));
221        assert_eq!(Cell::Text("x".into()).to_value(), Value::Text("x".into()));
222        assert_eq!(Cell::Null.to_value(), Value::Text(String::new()));
223    }
224
225    #[test]
226    fn cell_as_number_coerces() {
227        assert_eq!(Cell::Int(7).as_number().unwrap(), 7.0);
228        assert_eq!(Cell::Text("1.5".into()).as_number().unwrap(), 1.5);
229        assert!(Cell::Text("nope".into()).as_number().is_err());
230        assert!(Cell::Null.as_number().is_err());
231    }
232
233    #[test]
234    fn cell_text_and_number_coercions_cover_every_variant() {
235        assert_eq!(Cell::Null.as_text(), "");
236        assert_eq!(Cell::Int(-4).as_text(), "-4");
237        assert_eq!(Cell::Float(1.5).as_text(), "1.5");
238        assert_eq!(Cell::Bool(true).as_text(), "true");
239        assert_eq!(Cell::Text("t".into()).as_text(), "t");
240        assert_eq!(Cell::Float(0.5).as_number().unwrap(), 0.5);
241        assert_eq!(Cell::Bool(true).as_number().unwrap(), 1.0);
242        assert_eq!(Cell::Bool(false).as_number().unwrap(), 0.0);
243    }
244
245    /// The bare `&rusqlite::Connection` impl binds every `Cell` kind and reads
246    /// NULL/INTEGER/REAL/TEXT back; a BLOB cell is a clean error.
247    #[test]
248    fn raw_sqlite_conn_binds_and_reads_all_cell_kinds() {
249        let raw = rusqlite::Connection::open_in_memory().unwrap();
250        let mut c = &raw;
251        assert_eq!((c.placeholder(3), c.real_type()), ("?".into(), "REAL"));
252        SqlConn::execute(&mut c, "CREATE TABLE t (a, b, c, d, e)", &[]).unwrap();
253        SqlConn::execute(
254            &mut c,
255            "INSERT INTO t VALUES (?, ?, ?, ?, ?)",
256            &[
257                Cell::Null,
258                Cell::Int(7),
259                Cell::Float(2.5),
260                Cell::Text("x".into()),
261                Cell::Bool(true),
262            ],
263        )
264        .unwrap();
265        let t = SqlConn::query(&mut c, "SELECT a, b, c, d, e FROM t").unwrap();
266        assert_eq!(t.columns, ["a", "b", "c", "d", "e"]);
267        assert_eq!(
268            t.rows,
269            vec![vec![
270                Cell::Null,
271                Cell::Int(7),
272                Cell::Float(2.5),
273                Cell::Text("x".into()),
274                Cell::Int(1),
275            ]]
276        );
277        let err = SqlConn::query(&mut c, "SELECT x'00'")
278            .err()
279            .unwrap()
280            .to_string();
281        assert!(err.contains("blob"), "{err}");
282        assert!(t.column_index("zz").is_err());
283    }
284
285    #[test]
286    fn backend_dialect_per_driver() {
287        let s = Backend::Sqlite(rusqlite::Connection::open_in_memory().unwrap());
288        let d = Backend::Duckdb(duckdb::Connection::open_in_memory().unwrap());
289        assert_eq!((s.placeholder(2), s.real_type()), ("?".into(), "REAL"));
290        assert_eq!((d.placeholder(2), d.real_type()), ("?".into(), "DOUBLE"));
291    }
292}
improv_storage_sql/backend.rs

improv_storage_sql/
backend.rs
