sbol_db_sqlite/
sql_console.rs

1//! The SQL console capability over SQLite: arbitrary read/write SQL with a
2//! per-statement timeout, a server-side row cap, untyped row→JSON conversion,
3//! and parse-only validation.
4//!
5//! SQLite is dynamically typed, so each cell's JSON shape is decided by what
6//! decodes (integer, real, text, blob) rather than by a declared column type.
7//! Errors the SQLite engine reports (syntax, constraint, missing table) are
8//! almost always user SQL faults, so they map to [`DomainError::InvalidInput`];
9//! pool/connection faults stay [`DomainError::Database`].
10
11use std::time::{Duration, Instant};
12
13use async_trait::async_trait;
14use sbol_db_core::DomainError;
15use sbol_db_storage::{
16    SqlConsole, SqlConsoleColumn, SqlExecuteRequest, SqlExecuteResult, SqlValidateError,
17};
18use serde_json::Value;
19use sqlx::sqlite::SqliteRow;
20use sqlx::{Column, Executor, Row, TypeInfo};
21
22use crate::SqlitePool;
23
24/// The SQL console for a SQLite backend. Cloneable; clones share the pool.
25#[derive(Clone)]
26pub struct SqliteSqlConsole {
27    pool: SqlitePool,
28}
29
30impl SqliteSqlConsole {
31    pub fn new(pool: SqlitePool) -> Self {
32        Self { pool }
33    }
34}
35
36/// Decode one cell to JSON by trying SQLite's storage classes in order:
37/// integer, real, text, blob (rendered as hex). A NULL cell yields
38/// [`Value::Null`]; the first decode that succeeds and is `Some` wins.
39fn value_to_json(row: &SqliteRow, idx: usize) -> Value {
40    if let Ok(Some(n)) = row.try_get::<Option<i64>, _>(idx) {
41        return Value::from(n);
42    }
43    if let Ok(Some(f)) = row.try_get::<Option<f64>, _>(idx) {
44        return serde_json::Number::from_f64(f)
45            .map(Value::Number)
46            .unwrap_or(Value::Null);
47    }
48    if let Ok(Some(s)) = row.try_get::<Option<String>, _>(idx) {
49        return Value::String(s);
50    }
51    if let Ok(Some(b)) = row.try_get::<Option<Vec<u8>>, _>(idx) {
52        return Value::String(hex::encode(b));
53    }
54    Value::Null
55}
56
57/// Map a sqlx error from `execute`. A `Database` error is a SQLite engine
58/// complaint (syntax, constraint, no such table/column), which is a user SQL
59/// fault → [`DomainError::InvalidInput`]. Everything else (pool timeout, I/O,
60/// lost connection) is infrastructure → [`DomainError::Database`].
61fn classify_execute_error(err: sqlx::Error) -> DomainError {
62    match err {
63        sqlx::Error::Database(db) => DomainError::InvalidInput(db.message().to_string()),
64        other => DomainError::Database(other.to_string()),
65    }
66}
67
68#[async_trait]
69impl SqlConsole for SqliteSqlConsole {
70    async fn execute(&self, req: SqlExecuteRequest) -> Result<SqlExecuteResult, DomainError> {
71        let started = Instant::now();
72        let fut = sqlx::query(&req.query).fetch_all(&self.pool);
73        let rows = match tokio::time::timeout(Duration::from_millis(req.timeout_ms), fut).await {
74            Ok(result) => result.map_err(classify_execute_error)?,
75            Err(_) => {
76                return Err(DomainError::InvalidInput(
77                    "statement exceeded the time limit".to_string(),
78                ))
79            }
80        };
81        let elapsed_ms = started.elapsed().as_millis() as u64;
82
83        let total = rows.len() as u64;
84        let row_limit = req.row_limit as usize;
85        let truncated = total > req.row_limit as u64;
86
87        let columns: Vec<SqlConsoleColumn> = rows
88            .first()
89            .map(|row| {
90                row.columns()
91                    .iter()
92                    .map(|c| SqlConsoleColumn {
93                        name: c.name().to_string(),
94                        column_type: c.type_info().name().to_string(),
95                    })
96                    .collect()
97            })
98            .unwrap_or_default();
99
100        let json_rows: Vec<Vec<Value>> = rows
101            .iter()
102            .take(row_limit)
103            .map(|row| {
104                (0..row.columns().len())
105                    .map(|i| value_to_json(row, i))
106                    .collect()
107            })
108            .collect();
109
110        Ok(SqlExecuteResult {
111            columns,
112            rows: json_rows,
113            row_count: total,
114            truncated,
115            elapsed_ms,
116            backend_pid: None,
117        })
118    }
119
120    async fn validate(&self, query: &str) -> Result<Option<SqlValidateError>, DomainError> {
121        if query.trim().is_empty() {
122            return Ok(None);
123        }
124        let mut conn = self.pool.acquire().await.map_err(crate::pool::db_err)?;
125        match conn.prepare(query).await {
126            Ok(_) => Ok(None),
127            Err(err) => Ok(Some(SqlValidateError {
128                message: err.to_string(),
129                line: 1,
130                column: 1,
131                end_line: None,
132                end_column: None,
133            })),
134        }
135    }
136
137    fn dialect(&self) -> &'static str {
138        "sql"
139    }
140}
141
142#[cfg(test)]
143mod tests {
144    use super::*;
145    use crate::{connect, run_migrations};
146    use sbol_db_storage::SqlConsole;
147
148    async fn console() -> SqliteSqlConsole {
149        let pool = connect("sqlite::memory:").await.unwrap();
150        run_migrations(&pool).await.unwrap();
151        SqliteSqlConsole::new(pool)
152    }
153
154    fn req(query: &str, row_limit: u32) -> SqlExecuteRequest {
155        SqlExecuteRequest {
156            query: query.to_string(),
157            timeout_ms: 5_000,
158            row_limit,
159        }
160    }
161
162    #[tokio::test]
163    async fn execute_select_literal() {
164        let console = console().await;
165        let result = console.execute(req("SELECT 1 AS one", 100)).await.unwrap();
166        assert_eq!(result.columns.len(), 1);
167        assert_eq!(result.columns[0].name, "one");
168        assert_eq!(result.rows.len(), 1);
169        assert_eq!(result.rows[0][0], Value::from(1));
170        assert_eq!(result.row_count, 1);
171        assert!(!result.truncated);
172        assert!(result.backend_pid.is_none());
173    }
174
175    #[tokio::test]
176    async fn execute_applies_row_limit() {
177        let console = console().await;
178        let result = console
179            .execute(req("SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3", 2))
180            .await
181            .unwrap();
182        assert_eq!(result.rows.len(), 2);
183        assert_eq!(result.row_count, 3);
184        assert!(result.truncated);
185    }
186
187    #[tokio::test]
188    async fn execute_bad_sql_is_invalid_input() {
189        let console = console().await;
190        let err = console.execute(req("SELCT 1", 100)).await.unwrap_err();
191        assert!(matches!(err, DomainError::InvalidInput(_)));
192    }
193
194    #[tokio::test]
195    async fn validate_reports_syntax_error() {
196        let console = console().await;
197        let err = console.validate("SELCT 1").await.unwrap();
198        assert!(err.is_some());
199        let err = err.unwrap();
200        assert_eq!(err.line, 1);
201        assert_eq!(err.column, 1);
202    }
203
204    #[tokio::test]
205    async fn validate_accepts_valid_sql() {
206        let console = console().await;
207        assert!(console.validate("SELECT 1").await.unwrap().is_none());
208        assert!(console.validate("   ").await.unwrap().is_none());
209    }
210
211    #[tokio::test]
212    async fn dialect_is_sql() {
213        let console = console().await;
214        assert_eq!(console.dialect(), "sql");
215    }
216}
sbol_db_sqlite/sql_console.rs

sbol_db_sqlite/
sql_console.rs
