collective
Browse / github.com/storytold/gridcraft / patch
Add a standalone typed ODBC connector
Add a standalone typed ODBC connector ## Summary ```text connection + SQL -> gridcraft-odbc -> bounded typed DataSet (no engine, UI or workbook persistence) ``` Split the reusable connector out of #1717 as suggested in review. Supports Windows ODBC drivers for PostgreSQL, SQL Server/Azure SQL, MySQL, MariaDB, Oracle and SQLite 3. Keeps driver profiles, read-only transactions/guards, precision protection, cancellation and credential redaction together. Dates, times and timestamps now use Chrono types instead of spreadsheet strings; explicit offsets and fractional seconds are retained. Arrow rec
- Status
- open
- Score
- 0.00
- Adopters
- 0 other operators
- Author
- PietroSoaresss (imported from pull request #2774, open)
- Base
11bb67386c9bb19b0717e21e98adc608162ae2e5- Size
- +1410 −1 in 13 files
- Review
- pending
- Shared
- 2026-10-28
- Id
c7d408f767ca6aa91c636b76
How it went
No other operator has reported using it yet.
Diff
raw · Cargo.lock Cargo.toml crates/odbc/Cargo.toml crates/odbc/README.md crates/odbc/src/lib.rs crates/odbc/src/native.rs crates/odbc/src/native/profile.rs crates/odbc/src/tests.rs crates/odbc/tests/databases.rs crates/odbc/tests/postgres.rs docs/architecture.md docs/superpowers/plans/2026-10-27-odbc-split.md xtask/src/layers.rs
@@ -377,6 +377,15 @@ dependencies = [ "syn 3.0.6", ] +[[package]]+name = "atoi"+version = "3.1.1"+source = "registry+https://github.com/rust-lang/crates.io-index"+checksum = "d12c37257553c19e9aee6cba77814fdc9e07844abfc8b3a2c377cd920ee5a993"+dependencies = [+ "num-traits",+]+ [[package]] name = "atomic-waker" version = "1.1.2"@@ -2072,6 +2081,18 @@ dependencies = [ "gridcraft-core", ] +[[package]]+name = "gridcraft-odbc"+version = "0.12.1"+dependencies = [+ "chrono",+ "odbc-api",+ "serde",+ "serde_json",+ "sqlparser",+ "thiserror 2.0.21",+]+ [[package]] name = "gridcraft-pdf" version = "0.12.1"@@ -3400,6 +3421,25 @@ dependencies = [ "objc2-foundation 0.2.2", ] +[[package]]+name = "odbc-api"+version = "29.2.0"+source = "registry+https://github.com/rust-lang/crates.io-index"+checksum = "b8532ca21ccfef7f60246ce4f4d25d4a46d2aa501832dfaab17e2a27e621fb52"+dependencies = [+ "atoi",+ "log",+ "odbc-sys",+ "thiserror 2.0.21",+ "widestring",+]++[[package]]+name = "odbc-sys"+version = "0.31.0"+source = "registry+https://github.com/rust-lang/crates.io-index"+checksum = "245cb4fe8236df4fd352ba96075d754233c6509d654d9f1c1482158b7d6c083d"+ [[package]] name = "once_cell" version = "1.21.4"@@ -4830,6 +4870,15 @@ dependencies = [ "der", ] +[[package]]+name = "sqlparser"+version = "0.63.0"+source = "registry+https://github.com/rust-lang/crates.io-index"+checksum = "3679862809bd1f92e563cf6fd820e28f39135303668001c10331d5027183e8b4"+dependencies = [+ "log",+]+ [[package]] name = "stable_deref_trait" version = "1.2.1"@@ -5928,6 +5977,12 @@ dependencies = [ "web-sys", ] +[[package]]+name = "widestring"+version = "1.2.1"+source = "registry+https://github.com/rust-lang/crates.io-index"+checksum = "72069c3113ab32ab29e5584db3c6ec55d416895e60715417b5b883a357c3e471"+ [[package]] name = "winapi-util" version = "0.1.11"@@ -24,6 +24,7 @@ gridcraft-engine = { path = "crates/engine" } gridcraft-ui-egui = { path = "crates/ui-egui" } gridcraft-mcp = { path = "crates/mcp" } gridcraft-pdf = { path = "crates/pdf" }+gridcraft-odbc = { path = "crates/odbc" } # Every craft-libs crate (craft-update and craft-window here, craft-ui in ui-egui, craft-print in # the app) comes from one storytold/craft-libs revision. craft-update = { git = "https://github.com/storytold/craft-libs", rev = "92d392cff132eaf53e13afff77f38b5c11b73aa0", version = "=0.1.0", features = ["egui"] }new file mode 100644@@ -0,0 +1,21 @@+[package]+name = "gridcraft-odbc"+version.workspace = true+edition.workspace = true+license.workspace = true+description = "Bounded, toolkit-independent ODBC data imports"++[lints]+workspace = true++[dependencies]+chrono = { workspace = true, features = ["serde"] }+serde = { workspace = true }+thiserror = { workspace = true }++[target.'cfg(target_os = "windows")'.dependencies]+odbc-api = { version = "29.2", default-features = false, features = ["odbc_version_3_80", "wide"] }+sqlparser = { version = "0.63", default-features = false, features = ["std"] }++[dev-dependencies]+serde_json = { workspace = true }new file mode 100644@@ -0,0 +1,84 @@+# gridcraft-odbc++Bounded, read-only ODBC queries independent of the spreadsheet engine and UI.+The current native implementation supports Windows drivers for PostgreSQL,+SQL Server/Azure SQL, MySQL, MariaDB, Oracle and SQLite 3. Other targets build+the same API and return `OdbcError::Unavailable` for native operations.++```rust,no_run+use gridcraft_odbc::{Backend, Connection, Limits, NativeBackend};+use std::sync::atomic::AtomicBool;++# fn main() -> Result<(), gridcraft_odbc::OdbcError> {+let connection = Connection { dsn: Some("Reporting".into()), ..Default::default() };+let data = NativeBackend.query(+ &connection, "SELECT * FROM customers", Limits::default(), false,+ &AtomicBool::new(false),+)?;+for column in &data.columns {+ println!("{}: {:?}", column.name, column.kind);+}+# Ok(())+# }+```++## Contract++`Backend::query` returns a `DataSet` containing column names/types, row values and+a preview truncation flag. NULL is `Value::Empty`. Numbers above spreadsheet+precision remain exact text. Text is never interpreted as a formula. Dates,+times and timestamps use Chrono types, retaining nanoseconds and explicit+offsets when supplied by ODBC metadata. No workbook epoch is chosen here.+Timestamp columns whose driver omits timezone information remain timezone-free.+Nonstandard temporal strings (including infinite dates, zero dates and duration+values outside a time of day) return `InvalidData`; explicitly cast them to text.+SQLite expressions with unknown metadata remain text rather than guessed dates.++The result is materialized and bounded: 100,000 rows, 256 columns, 128 MiB+including container overhead, and 1 MiB per text cell. Preview reads at most+100 rows and may return `truncated`; full queries fail on overflow. Cancellation+is checked between native reads. A blocking driver call may finish first.+Arrow batches and unixODBC are follow-ups; this crate does not define saved+queries, refresh or an XLSX extension. See+[#431](https://github.com/storytold/gridcraft/issues/431) for that model.++## Drivers and safety++Install a matching 64-bit ODBC driver and configure a DSN, or supply a registered+driver connection string. Use separate user/password fields. Driver detection,+connection options, query guards and transaction behavior live in+`src/native/profile.rs`; values and bounded fetching live in `src/native.rs`.+Credentials and driver diagnostics are redacted from public errors and Debug.+Disable ODBC manager/vendor tracing separately.++Queries accept a single SELECT/WITH/VALUES/TABLE statement. Write/control+keywords, executable comments and internal semicolons are rejected. Connections+roll back after reading. PostgreSQL/Oracle use a server read-only transaction;+MySQL/MariaDB start a read-only transaction; SQLite enables `query_only`.+SQL Server read intent is a routing hint: a SELECT-only account is required.+Query guards do not sandbox database functions or replace server permissions.++## Validation and contributions++Run `cargo test -p gridcraft-odbc`. Synthetic integration tests are ignored by+default and need dedicated local databases plus installed drivers:++```powershell+$env:GRIDCRAFT_TEST_ODBC_DSN = 'GridCraftTest'+$env:GRIDCRAFT_TEST_ODBC_USER = 'reader'+$env:GRIDCRAFT_TEST_ODBC_PASSWORD = '<local test password>'+cargo test -p gridcraft-odbc --test postgres -- --ignored+```++`GRIDCRAFT_TEST_ODBC_CONNECTION_STRING` replaces the DSN. Other databases use+`GRIDCRAFT_TEST_ODBC_{SQLSERVER,MYSQL,MARIADB,ORACLE,SQLITE}_{DSN,CONNECTION_STRING,USER,PASSWORD}`+and `cargo test -p gridcraft-odbc --test databases -- --ignored` (or a test-name+filter). SQLite requires an existing synthetic file. These tests create no+server objects. Add a driver profile and synthetic integration case when adding+a database family. Keep vendor settings out of engine/UI code and keep native+dependencies out of WASM.++On 2026-10-27, all 17 unit/profile tests passed and the revised native tests+passed with local PostgreSQL and SQLite drivers. SQL Server, MySQL, MariaDB and+Oracle servers were unavailable for this revision; earlier validation of the+full implementation does not certify these revised temporal mappings.new file mode 100644@@ -0,0 +1,306 @@+//! Bounded ODBC reads for spreadsheet imports.+#![forbid(unsafe_code)]+#![deny(clippy::unwrap_used, clippy::expect_used, clippy::panic)]+#![cfg_attr(test, allow(clippy::unwrap_used, clippy::expect_used, clippy::panic))]++use std::{fmt, mem::size_of, sync::atomic::AtomicBool};++pub use chrono::{DateTime, Datelike, FixedOffset, NaiveDate, NaiveDateTime, NaiveTime, Timelike};+use serde::{Deserialize, Serialize};++#[cfg(target_os = "windows")]+mod native;+#[cfg(test)]+mod tests;++pub const MAX_ROWS: usize = 100_000;+pub const MAX_COLUMNS: usize = 256;+pub const MAX_BYTES: usize = 128 * 1024 * 1024;+pub const MAX_CELL_BYTES: usize = 1024 * 1024;++#[derive(Clone, Default)]+pub struct Connection {+ pub dsn: Option<String>,+ pub connection_string: Option<String>,+ pub user: String,+ pub password: String,+}++impl fmt::Debug for Connection {+ fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {+ f.debug_struct("Connection")+ .field("dsn", &self.dsn)+ .field("connection_string", &"[redacted]")+ .field("user", &self.user)+ .field("password", &"[redacted]")+ .finish()+ }+}++impl Connection {+ pub fn validate(&self) -> Result<(), OdbcError> {+ match (&self.dsn, &self.connection_string) {+ (Some(dsn), None)+ if !dsn.trim().is_empty()+ && dsn.trim() == dsn+ && dsn.encode_utf16().count() <= 32+ && !dsn.chars().any(|c| c.is_control() || "[]{}(),;?*=!@\\".contains(c)) => {}+ (None, Some(cs))+ if !cs.trim().is_empty()+ && cs.len() <= 16_384+ && !cs.contains('\0')+ && !cs.split(';').any(|part| part.split_once('=').is_some_and(|(key, _)| key.trim().eq_ignore_ascii_case("SAVEFILE"))) => {}+ _ => return Err(OdbcError::InvalidConnection),+ }+ if self.user.len() > 512 || self.password.len() > 16_384 || self.user.contains('\0') || self.password.contains('\0') {+ return Err(OdbcError::InvalidConnection);+ }+ Ok(())+ }+}++#[derive(Clone, Copy, Debug, PartialEq, Eq, Serialize, Deserialize)]+#[serde(rename_all = "camelCase")]+pub struct Limits {+ pub rows: usize,+ pub columns: usize,+ pub bytes: usize,+ pub cell_bytes: usize,+}++impl Default for Limits {+ fn default() -> Self {+ Self { rows: MAX_ROWS, columns: MAX_COLUMNS, bytes: MAX_BYTES, cell_bytes: MAX_CELL_BYTES }+ }+}++impl Limits {+ pub fn validate(self) -> Result<Self, OdbcError> {+ if self.rows == 0+ || self.rows > MAX_ROWS+ || self.columns == 0+ || self.columns > MAX_COLUMNS+ || self.bytes == 0+ || self.bytes > MAX_BYTES+ || self.cell_bytes == 0+ || self.cell_bytes > MAX_CELL_BYTES+ {+ return Err(OdbcError::Limit("Requested limits are outside supported bounds."));+ }+ Ok(self)+ }+}++#[derive(Clone, Copy, Debug, PartialEq, Eq, Serialize, Deserialize)]+#[serde(rename_all = "camelCase")]+pub enum ColumnKind {+ Text,+ Integer,+ Decimal,+ Float,+ Boolean,+ Date,+ Time,+ Timestamp,+ TimestampWithOffset,+}++/// Connector values are independent of workbook epochs, formats and formula semantics.+#[derive(Clone, Debug, PartialEq, Serialize, Deserialize)]+pub enum Value {+ Empty,+ Bool(bool),+ Number(f64),+ Text(String),+ Date(NaiveDate),+ Time(NaiveTime),+ Timestamp(NaiveDateTime),+ TimestampWithOffset(DateTime<FixedOffset>),+}++impl Value {+ pub fn text(value: impl Into<String>) -> Self {+ Self::Text(value.into())+ }+}++#[derive(Clone, Debug, PartialEq, Eq, Serialize, Deserialize)]+#[serde(rename_all = "camelCase")]+pub struct Column {+ pub name: String,+ pub kind: ColumnKind,+}++#[derive(Clone, Debug, PartialEq, Serialize, Deserialize)]+#[serde(rename_all = "camelCase")]+pub struct DataSet {+ pub columns: Vec<Column>,+ pub rows: Vec<Vec<Value>>,+ pub truncated: bool,+}++impl DataSet {+ /// Validates the result and returns its bounded storage estimate.+ pub fn validate(&self, limits: Limits) -> Result<usize, OdbcError> {+ let limits = limits.validate()?;+ if self.columns.is_empty()+ || self.columns.len() > limits.columns+ || self.rows.len() > limits.rows+ || self.columns.iter().any(|c| c.name.len() > 1024 || c.name.contains('\0'))+ {+ return Err(OdbcError::Limit("The result exceeds the supported shape."));+ }+ let mut bytes = self+ .columns+ .iter()+ .try_fold(0usize, |n, c| n.checked_add(c.name.len() + size_of::<Column>()))+ .ok_or(OdbcError::Limit("The result exceeds the supported size."))?;+ for row in &self.rows {+ if row.len() != self.columns.len() {+ return Err(OdbcError::InvalidData);+ }+ bytes = bytes.checked_add(size_of::<Vec<Value>>()).ok_or(OdbcError::Limit("The result exceeds the supported size."))?;+ for value in row {+ let size = match value {+ Value::Empty | Value::Bool(_) | Value::Date(_) | Value::Time(_) | Value::Timestamp(_) | Value::TimestampWithOffset(_) => 0,+ Value::Number(n) if n.is_finite() => 0,+ Value::Text(s) if s.len() <= limits.cell_bytes => s.len() + 2 * size_of::<usize>(),+ Value::Text(_) => return Err(OdbcError::Limit("A result cell exceeds the supported size.")),+ _ => return Err(OdbcError::InvalidData),+ };+ bytes = bytes.checked_add(size + size_of::<Value>()).ok_or(OdbcError::Limit("The result exceeds the supported size."))?;+ if bytes > limits.bytes {+ return Err(OdbcError::Limit("The result exceeds the supported size."));+ }+ }+ }+ if bytes > limits.bytes {+ return Err(OdbcError::Limit("The result exceeds the supported size."));+ }+ Ok(bytes)+ }+}++#[derive(Clone, Debug, PartialEq, Eq, Serialize, Deserialize)]+#[serde(rename_all = "camelCase")]+pub struct Sources {+ pub data_sources: Vec<String>,+ pub drivers: Vec<String>,+}++#[derive(Clone, Debug, PartialEq, Eq, thiserror::Error)]+pub enum OdbcError {+ #[error("ODBC is available only in the Windows desktop build.")]+ Unavailable,+ #[error("The connection settings are invalid.")]+ InvalidConnection,+ #[error("The ODBC connection could not be completed.")]+ ConnectionFailed,+ #[error("The ODBC query failed. Check the SQL and database permissions.")]+ QueryFailed,+ #[error("ODBC operation failed (SQLSTATE {0}). Check the driver, connection and query.")]+ DriverState(String),+ #[error("Use a supported PostgreSQL, SQL Server, MySQL, MariaDB, Oracle or SQLite3 ODBC driver.")]+ UnsupportedDatabase,+ #[error("Use one SELECT, WITH, VALUES or TABLE query without internal semicolons, write/control keywords or executable comments.")]+ InvalidQuery,+ #[error("{0}")]+ Limit(&'static str),+ #[error("The driver returned unsupported or malformed data.")]+ InvalidData,+ #[error("The operation was cancelled.")]+ Cancelled,+}++pub trait Backend: Send + Sync + 'static {+ fn sources(&self) -> Result<Sources, OdbcError>;+ fn test(&self, connection: &Connection) -> Result<(), OdbcError>;+ fn query(&self, connection: &Connection, sql: &str, limits: Limits, preview: bool, cancelled: &AtomicBool) -> Result<DataSet, OdbcError>;+}++#[derive(Default)]+pub struct NativeBackend;++impl Backend for NativeBackend {+ fn sources(&self) -> Result<Sources, OdbcError> {+ native::sources()+ }+ fn test(&self, connection: &Connection) -> Result<(), OdbcError> {+ native::test(connection)+ }+ fn query(&self, connection: &Connection, sql: &str, limits: Limits, preview: bool, cancelled: &AtomicBool) -> Result<DataSet, OdbcError> {+ native::query(connection, sql, limits, preview, cancelled)+ }+}++pub const fn available() -> bool {+ cfg!(target_os = "windows")+}++pub fn validate_query(sql: &str) -> Result<(), OdbcError> {+ let body = sql.trim().strip_suffix(';').unwrap_or(sql.trim()).trim();+ // shortcut: semicolons inside literals are refused; use a SQL parser if multi-statement-aware editing is added.+ if body.is_empty()+ || body.len() > 1_000_000+ || body.contains(';')+ || body.chars().any(|c| c.is_control() && !matches!(c, '\r' | '\n' | '\t'))+ || !body+ .split(|c: char| !c.is_ascii_alphabetic() && c != '_')+ .next()+ .is_some_and(|word| ["select", "with", "values", "table"].iter().any(|keyword| word.eq_ignore_ascii_case(keyword)))+ {+ return Err(OdbcError::InvalidQuery);+ }+ Ok(())+}++pub fn decode(kind: ColumnKind, value: &str) -> Result<Value, OdbcError> {+ let exact = || Value::text(value);+ Ok(match kind {+ ColumnKind::Text => exact(),+ ColumnKind::Date => Value::Date(value.parse().map_err(|_| OdbcError::InvalidData)?),+ ColumnKind::Time => Value::Time(value.parse().map_err(|_| OdbcError::InvalidData)?),+ ColumnKind::Timestamp => Value::Timestamp(NaiveDateTime::parse_from_str(value, "%Y-%m-%d %H:%M:%S%.f").map_err(|_| OdbcError::InvalidData)?),+ ColumnKind::TimestampWithOffset => Value::TimestampWithOffset(+ DateTime::parse_from_str(value, "%Y-%m-%d %H:%M:%S%.f %:z")+ .or_else(|_| DateTime::parse_from_str(value, "%Y-%m-%d %H:%M:%S%.f%:z"))+ .map_err(|_| OdbcError::InvalidData)?,+ ),+ ColumnKind::Integer => {+ let number = value.parse::<i128>().map_err(|_| OdbcError::InvalidData)?;+ if number.unsigned_abs() < 1_000_000_000_000_000 { Value::Number(number as f64) } else { exact() }+ }+ ColumnKind::Decimal => {+ let significant = value.trim_start_matches(['+', '-']).split(['e', 'E']).next().unwrap_or(value);+ let digits = significant.chars().filter(|c| c.is_ascii_digit()).skip_while(|c| *c == '0').collect::<String>().trim_end_matches('0').len();+ match value.parse::<f64>() {+ Ok(n) if n.is_finite() && digits <= 15 && (n != 0.0 || digits == 0) => Value::Number(n),+ Ok(_) => exact(),+ Err(_) => return Err(OdbcError::InvalidData),+ }+ }+ ColumnKind::Float => {+ let number = value.parse::<f64>().map_err(|_| OdbcError::InvalidData)?;+ if number.is_finite() { Value::Number(number) } else { exact() }+ }+ ColumnKind::Boolean => match value.to_ascii_lowercase().as_str() {+ "1" | "-1" | "true" | "t" | "yes" => Value::Bool(true),+ "0" | "false" | "f" | "no" => Value::Bool(false),+ _ => return Err(OdbcError::InvalidData),+ },+ })+}++#[cfg(not(target_os = "windows"))]+mod native {+ use super::*;+ pub fn sources() -> Result<Sources, OdbcError> {+ Err(OdbcError::Unavailable)+ }+ pub fn test(_: &Connection) -> Result<(), OdbcError> {+ Err(OdbcError::Unavailable)+ }+ pub fn query(_: &Connection, _: &str, _: Limits, _: bool, _: &AtomicBool) -> Result<DataSet, OdbcError> {+ Err(OdbcError::Unavailable)+ }+}new file mode 100644@@ -0,0 +1,221 @@+use std::{+ mem::size_of,+ sync::{+ OnceLock,+ atomic::{AtomicBool, Ordering},+ },+};++use odbc_api::{+ ConnectionOptions, Cursor, DataType, Environment, ResultSetMetadata,+ buffers::Indicator,+ parameter::{VarCell, WideText},+};++use crate::{Column, ColumnKind, Connection as Settings, DataSet, Limits, OdbcError, Sources, Value, decode};++mod profile;+use profile::Driver;++fn env() -> Result<&'static Environment, OdbcError> {+ static ENV: OnceLock<Result<Environment, OdbcError>> = OnceLock::new();+ ENV.get_or_init(|| Environment::new().map_err(|_| OdbcError::Unavailable)).as_ref().map_err(Clone::clone)+}++pub fn sources() -> Result<Sources, OdbcError> {+ let env = env()?;+ let data_sources = env.data_sources().map_err(|_| OdbcError::Unavailable)?.into_iter().map(|s| s.server_name).collect();+ let drivers = env.drivers().map_err(|_| OdbcError::Unavailable)?.into_iter().map(|d| d.description).collect();+ Ok(Sources { data_sources, drivers })+}++fn connect<'a>(env: &'a Environment, settings: &Settings) -> Result<(odbc_api::Connection<'a>, Driver), OdbcError> {+ let attrs = profile::attributes(settings)?;+ let (key, name) = profile::selector(&attrs)?;+ let description = if key == "DSN" {+ env.data_sources()+ .map_err(|_| OdbcError::Unavailable)?+ .into_iter()+ .find(|source| source.server_name.eq_ignore_ascii_case(name))+ .ok_or(OdbcError::ConnectionFailed)?+ .driver+ } else {+ env.drivers()+ .map_err(|_| OdbcError::Unavailable)?+ .into_iter()+ .find(|driver| driver.description.eq_ignore_ascii_case(name))+ .ok_or(OdbcError::ConnectionFailed)?+ .description+ };+ let driver = Driver::from_name(&description)?;+ let options = ConnectionOptions { login_timeout_sec: Some(15), ..Default::default() };+ let connection_string = profile::connection_string(settings, &attrs, driver)?;+ let connection = env.connect_with_connection_string(&connection_string, options).map_err(driver_error)?;+ if !driver.accepts(&connection.database_management_system_name().map_err(driver_error)?) {+ return Err(OdbcError::UnsupportedDatabase);+ }+ Ok((connection, driver))+}++fn driver_error(error: odbc_api::Error) -> OdbcError {+ if let odbc_api::Error::Diagnostics { record, .. } = error+ && record.state.0.iter().all(u8::is_ascii_alphanumeric)+ && let Ok(state) = std::str::from_utf8(&record.state.0)+ {+ return OdbcError::DriverState(state.into());+ }+ OdbcError::QueryFailed+}++fn begin(connection: &odbc_api::Connection<'_>, driver: Driver) -> Result<(), OdbcError> {+ connection.set_autocommit(false).map_err(driver_error)?;+ if let Some(sql) = driver.begin_sql() {+ connection.execute(sql, (), Some(60)).map_err(driver_error)?;+ }+ Ok(())+}++pub fn test(settings: &Settings) -> Result<(), OdbcError> {+ let (connection, driver) = connect(env()?, settings)?;+ begin(&connection, driver)?;+ drop(connection.execute(driver.test_sql(), (), Some(15)).map_err(driver_error)?);+ connection.rollback().map_err(driver_error)+}++fn column_kind(driver: Driver, data_type: DataType) -> Result<ColumnKind, OdbcError> {+ Ok(match data_type {+ DataType::Integer | DataType::SmallInt | DataType::BigInt | DataType::TinyInt => ColumnKind::Integer,+ DataType::Numeric { .. } | DataType::Decimal { .. } => ColumnKind::Decimal,+ DataType::Float { .. } | DataType::Real | DataType::Double => ColumnKind::Float,+ DataType::Bit => ColumnKind::Boolean,+ DataType::Date => ColumnKind::Date,+ DataType::Time { .. } => ColumnKind::Time,+ DataType::Timestamp { .. } => ColumnKind::Timestamp,+ DataType::Char { .. }+ | DataType::WChar { .. }+ | DataType::Varchar { .. }+ | DataType::WVarchar { .. }+ | DataType::LongVarchar { .. }+ | DataType::WLongVarchar { .. } => ColumnKind::Text,+ DataType::Other { data_type, .. } if data_type.0 == -11 => ColumnKind::Text, // SQL_GUID+ DataType::Other { data_type, .. } if driver == Driver::SqlServer && data_type.0 == -154 => ColumnKind::Time,+ DataType::Other { data_type, .. } if driver == Driver::SqlServer && data_type.0 == -155 => ColumnKind::TimestampWithOffset,+ DataType::Other { data_type, .. } if driver == Driver::SqlServer && data_type.0 == -152 => ColumnKind::Text, // SQL_SS_XML+ DataType::Unknown if driver == Driver::Sqlite => ColumnKind::Text,+ _ => return Err(OdbcError::InvalidData),+ })+}++pub fn query(settings: &Settings, sql: &str, limits: Limits, preview: bool, cancelled: &AtomicBool) -> Result<DataSet, OdbcError> {+ let limits = limits.validate()?;+ crate::validate_query(sql)?;+ if cancelled.load(Ordering::Relaxed) {+ return Err(OdbcError::Cancelled);+ }+ let (connection, driver) = connect(env()?, settings)?;+ let sql = driver.query_sql(sql)?;+ begin(&connection, driver)?;+ let mut cursor = connection.execute(&sql, (), Some(60)).map_err(driver_error)?.ok_or(OdbcError::InvalidData)?;+ let count = cursor.num_result_cols().map_err(|_| OdbcError::InvalidData)?;+ if count <= 0 || count as usize > limits.columns {+ return Err(OdbcError::Limit("The result has too many columns."));+ }+ let mut columns = Vec::with_capacity(count as usize);+ for i in 1..=count as u16 {+ let kind = column_kind(driver, cursor.col_data_type(i).map_err(|_| OdbcError::InvalidData)?)?;+ let name = cursor.col_name(i).map_err(|_| OdbcError::InvalidData)?;+ if name.len() > 1024 || name.contains('\0') {+ return Err(OdbcError::InvalidData);+ }+ columns.push(Column { name, kind });+ }+ let mut rows = Vec::new();+ let max_rows = if preview { limits.rows.min(100) } else { limits.rows };+ let mut bytes = columns.iter().map(|c| c.name.len() + size_of::<Column>()).sum::<usize>();+ let mut truncated = false;+ let mut buffer = vec![0u16; limits.cell_bytes.saturating_add(1)];+ while let Some(mut row) = cursor.next_row().map_err(|_| OdbcError::InvalidData)? {+ if cancelled.load(Ordering::Relaxed) {+ return Err(OdbcError::Cancelled);+ }+ if rows.len() == max_rows {+ if !preview {+ return Err(OdbcError::Limit("The result exceeds the row limit. Restrict the query or increase maxRows."));+ }+ truncated = true;+ break;+ }+ bytes = bytes+ .checked_add(size_of::<Vec<Value>>() + columns.len() * size_of::<Value>())+ .ok_or(OdbcError::Limit("The result exceeds the supported size."))?;+ if bytes > limits.bytes {+ return Err(OdbcError::Limit("The result exceeds the supported size."));+ }+ let mut values = Vec::with_capacity(columns.len());+ for (i, column) in columns.iter().enumerate() {+ let mut cell = VarCell::<&mut [u16], WideText>::from_buffer(buffer.as_mut_slice(), Indicator::Null);+ row.get_data((i + 1) as u16, &mut cell).map_err(|_| OdbcError::InvalidData)?;+ if !cell.is_complete() {+ return Err(OdbcError::Limit("A result cell exceeds the supported size."));+ }+ let value = match cell.as_slice() {+ None => Value::Empty,+ Some(raw) => {+ let text = String::from_utf16(raw).map_err(|_| OdbcError::InvalidData)?;+ if text.len() > limits.cell_bytes {+ return Err(OdbcError::Limit("A result cell exceeds the supported size."));+ }+ decode(column.kind, &text)?+ }+ };+ if let Value::Text(text) = &value {+ bytes = bytes.checked_add(text.len() + 2 * size_of::<usize>()).ok_or(OdbcError::Limit("The result exceeds the supported size."))?;+ }+ if bytes > limits.bytes {+ return Err(OdbcError::Limit("The result exceeds the supported size."));+ }+ values.push(value);+ }+ rows.push(values);+ }+ let dataset = DataSet { columns, rows, truncated };+ drop(cursor);+ connection.rollback().map_err(|_| OdbcError::QueryFailed)?;+ dataset.validate(limits)?;+ Ok(dataset)+}++#[cfg(test)]+mod tests {+ use super::*;+ #[test]+ fn vendor_types_preserve_identifiers_offsets_and_binary_rejection() {+ let custom = |code| DataType::Other { data_type: odbc_api::sys::SqlDataType(code), column_size: None, decimal_digits: 0 };+ assert_eq!(column_kind(Driver::SqlServer, custom(-11)).unwrap(), ColumnKind::Text);+ assert_eq!(column_kind(Driver::SqlServer, custom(-154)).unwrap(), ColumnKind::Time);+ assert_eq!(column_kind(Driver::SqlServer, custom(-155)).unwrap(), ColumnKind::TimestampWithOffset);+ assert_eq!(column_kind(Driver::SqlServer, custom(-152)).unwrap(), ColumnKind::Text);+ assert!(column_kind(Driver::Oracle, custom(-155)).is_err());+ assert_eq!(column_kind(Driver::Sqlite, DataType::Unknown).unwrap(), ColumnKind::Text);+ assert!(column_kind(Driver::SqlServer, DataType::Unknown).is_err());+ for driver in [Driver::Postgres, Driver::SqlServer, Driver::MySql, Driver::MariaDb, Driver::Oracle, Driver::Sqlite] {+ assert!(column_kind(driver, DataType::Binary { length: None }).is_err());+ assert_eq!(column_kind(driver, DataType::Numeric { precision: 38, scale: 20 }).unwrap(), ColumnKind::Decimal);+ }+ }++ #[test]+ fn credentials_escape_braces_and_separators_without_quoting_the_dsn() {+ let settings = Settings { dsn: Some("Postgres".into()), user: "reader".into(), password: "{pass};+".into(), connection_string: None };+ let value = profile::connection_string(&settings, &profile::attributes(&settings).unwrap(), Driver::Postgres).unwrap();+ assert!(value.contains("UID=reader;PWD={{pass}};+};DSN=Postgres;"));+ assert!(value.starts_with("BoolsAsChar=0;ReadOnly=0;CommLog=0;Debug=0;ConnSettings={};"));+ let unsafe_settings = Settings {+ connection_string: Some("Driver={PostgreSQL Unicode};CX=ff;A0=0;B2=1;B3=1;A6={DELETE FROM x};B6=0;BI=8;D6=8".into()),+ ..Default::default()+ };+ let value = profile::connection_string(&unsafe_settings, &profile::attributes(&unsafe_settings).unwrap(), Driver::Postgres).unwrap();+ assert!(value.contains("D6=8;BoolsAsChar=0;ReadOnly=0;CommLog=0;Debug=0;ConnSettings={};"));+ assert!(value.ends_with("UseDeclareFetch=1;Fetch=100;BI=-5;NumericAS=2;LFConversion=0;IgnoreTimeout=0;"));+ }+}new file mode 100644@@ -0,0 +1,405 @@+use crate::{Connection as Settings, OdbcError};+use sqlparser::{+ dialect::{Dialect, MsSqlDialect, MySqlDialect, OracleDialect, PostgreSqlDialect, SQLiteDialect},+ tokenizer::{Token, Tokenizer},+};++#[derive(Clone, Copy, Debug, PartialEq, Eq)]+pub(super) enum Driver {+ Postgres,+ SqlServer,+ MySql,+ MariaDb,+ Oracle,+ Sqlite,+}++impl Driver {+ pub fn from_name(name: &str) -> Result<Self, OdbcError> {+ let name = name.to_ascii_lowercase();+ if name.contains("postgresql") {+ Ok(Self::Postgres)+ } else if name.contains("sql server") {+ Ok(Self::SqlServer)+ } else if name.contains("mariadb") {+ Ok(Self::MariaDb)+ } else if name.contains("mysql") {+ Ok(Self::MySql)+ } else if name.contains("oracle") {+ Ok(Self::Oracle)+ } else if name.contains("sqlite3") || name == "sqlite" {+ Ok(Self::Sqlite)+ } else {+ Err(OdbcError::UnsupportedDatabase)+ }+ }++ pub fn accepts(self, dbms: &str) -> bool {+ let dbms = dbms.to_ascii_lowercase();+ match self {+ Self::Postgres => dbms.contains("postgresql"),+ Self::SqlServer => dbms.contains("sql server"),+ Self::MySql | Self::MariaDb => dbms.contains("mysql") || dbms.contains("mariadb"),+ Self::Oracle => dbms.contains("oracle"),+ Self::Sqlite => dbms.contains("sqlite"),+ }+ }++ fn options(self) -> &'static str {+ match self {+ Self::Postgres => {+ // psqlODBC's read-only flag blocks SET TRANSACTION too; the server enforces our read-only transaction.+ "BoolsAsChar=0;ReadOnly=0;CommLog=0;Debug=0;ConnSettings={};UseDeclareFetch=1;Fetch=100;BI=-5;NumericAS=2;LFConversion=0;IgnoreTimeout=0;"+ }+ Self::SqlServer => "ApplicationIntent=ReadOnly;MARS_Connection=No;",+ Self::MySql => {+ "OPTION=3145744;INITSTMT={};STMT={};NO_CACHE=1;FORWARD_CURSOR=1;MULTI_STATEMENTS=0;NO_TRANSACTIONS=0;AUTO_RECONNECT=0;LOG_QUERY=0;USE_MYCNF=0;CHARSET=utf8mb4;"+ }+ Self::MariaDb => "OPTION=3145744;OPTIONS=3145744;INITSTMT={};NO_CACHE=1;FORWARDONLY=1;AUTO_RECONNECT=0;USE_MYCNF=0;CHARSET=utf8mb4;",+ // DBA=R starts a second read-only transaction in the driver, conflicting with SET TRANSACTION.+ Self::Oracle => "DBA=W;QTO=T;FBS=65536;BNF=F;BTD=F;FEN=F;EXC=F;MTS=T;",+ Self::Sqlite => "NoCreat=1;StepAPI=1;NoTXN=0;NoWCHAR=0;LoadExt=;Timeout=15000;",+ }+ }++ pub fn test_sql(self) -> &'static str {+ if self == Self::Oracle { "SELECT 1 FROM DUAL" } else { "SELECT 1" }+ }++ pub fn begin_sql(self) -> Option<&'static str> {+ match self {+ Self::Postgres | Self::Oracle => Some("SET TRANSACTION READ ONLY"),+ Self::MySql | Self::MariaDb => Some("START TRANSACTION READ ONLY"),+ Self::Sqlite => Some("PRAGMA query_only=ON"),+ // SQL Server has no read-only transaction mode; server permissions must restrict writes.+ Self::SqlServer => None,+ }+ }++ pub fn query_sql(self, sql: &str) -> Result<String, OdbcError> {+ crate::validate_query(sql)?;+ let sql = sql.trim().strip_suffix(';').unwrap_or(sql.trim());+ if matches!(self, Self::MySql | Self::MariaDb) && (sql.contains("/*!") || sql.to_ascii_uppercase().contains("/*M!")) {+ return Err(OdbcError::InvalidQuery);+ }+ let dialect: &dyn Dialect = match self {+ Self::Postgres => &PostgreSqlDialect {},+ Self::SqlServer => &MsSqlDialect {},+ Self::MySql | Self::MariaDb => &MySqlDialect {},+ Self::Oracle => &OracleDialect {},+ Self::Sqlite => &SQLiteDialect {},+ };+ // A conservative token guard, not a SQL sandbox. Database permissions remain authoritative.+ for token in Tokenizer::new(dialect, sql).tokenize().map_err(|_| OdbcError::InvalidQuery)? {+ if let Token::Word(word) = token+ && word.quote_style.is_none()+ && matches!(+ word.value.to_ascii_uppercase().as_str(),+ "INSERT"+ | "UPDATE"+ | "DELETE"+ | "MERGE"+ | "INTO"+ | "CREATE"+ | "ALTER"+ | "DROP"+ | "TRUNCATE"+ | "EXEC"+ | "EXECUTE"+ | "CALL"+ | "GRANT"+ | "REVOKE"+ | "DENY"+ | "SET"+ | "RESET"+ | "COMMIT"+ | "ROLLBACK"+ | "SAVEPOINT"+ | "RELEASE"+ | "BEGIN"+ | "USE"+ | "GO"+ | "DBCC"+ | "BACKUP"+ | "RESTORE"+ | "RECONFIGURE"+ | "SHUTDOWN"+ | "KILL"+ | "WAITFOR"+ | "ATTACH"+ | "DETACH"+ | "VACUUM"+ | "PRAGMA"+ | "COPY"+ | "LOAD"+ | "LOCK"+ | "UNLOCK"+ )+ {+ return Err(OdbcError::InvalidQuery);+ }+ }+ // psqlODBC bypasses cursor fetching for bare TABLE/VALUES; other dialects retain their syntax.+ Ok(if self == Self::Postgres { format!("SELECT * FROM ({sql}\n) AS gridcraft_odbc_result") } else { sql.to_string() })+ }+}++pub(super) fn attributes(settings: &Settings) -> Result<Vec<(String, String)>, OdbcError> {+ settings.validate()?;+ if let Some(dsn) = &settings.dsn {+ return Ok(vec![("DSN".into(), dsn.clone())]);+ }+ let mut chars = settings.connection_string.as_deref().ok_or(OdbcError::InvalidConnection)?.chars().peekable();+ let mut result = Vec::new();+ while chars.peek().is_some() {+ while chars.peek().is_some_and(|c| c.is_whitespace() || *c == ';') {+ chars.next();+ }+ if chars.peek().is_none() {+ break;+ }+ let mut key = String::new();+ loop {+ match chars.next() {+ Some('=') => break,+ Some(c) if c.is_ascii_alphanumeric() || matches!(c, '_' | '-' | ' ') => key.push(c),+ _ => return Err(OdbcError::InvalidConnection),+ }+ }+ let key = key.trim().to_ascii_uppercase();+ if key.is_empty() || key.len() > 128 || matches!(key.as_str(), "SAVEFILE" | "FILEDSN") || result.len() == 128 {+ return Err(OdbcError::InvalidConnection);+ }+ while chars.peek().is_some_and(|c| c.is_whitespace()) {+ chars.next();+ }+ let value = if chars.peek() == Some(&'{') {+ chars.next();+ let mut value = String::new();+ loop {+ match chars.next() {+ Some('}') if chars.peek() == Some(&'}') => {+ chars.next();+ value.push('}');+ }+ Some('}') => break,+ Some(c) => value.push(c),+ None => return Err(OdbcError::InvalidConnection),+ }+ }+ while chars.peek().is_some_and(|c| c.is_whitespace()) {+ chars.next();+ }+ if chars.peek().is_some_and(|c| *c != ';') {+ return Err(OdbcError::InvalidConnection);+ }+ value+ } else {+ chars.by_ref().take_while(|c| *c != ';').collect::<String>().trim().to_string()+ };+ result.push((key, value));+ }+ Ok(result)+}++pub(super) fn selector(attrs: &[(String, String)]) -> Result<(&str, &str), OdbcError> {+ let mut selectors = attrs.iter().filter(|(key, _)| matches!(key.as_str(), "DSN" | "DRIVER"));+ let (key, value) = selectors.next().ok_or(OdbcError::InvalidConnection)?;+ if value.is_empty() || selectors.next().is_some() {+ return Err(OdbcError::InvalidConnection);+ }+ if key == "DSN" {+ Settings { dsn: Some(value.clone()), ..Default::default() }.validate()?;+ }+ Ok((key, value))+}++pub(super) fn connection_string(settings: &Settings, attrs: &[(String, String)], driver: Driver) -> Result<String, OdbcError> {+ let escaped = |value: &str| {+ if value.starts_with('{') || value.trim() != value {+ format!("{{{}}}", value.replace('}', "}}"))+ } else {+ odbc_api::escape_attribute_value(value).into_owned()+ }+ };+ let safety = driver.options();+ let mut credentials = String::new();+ if !settings.user.is_empty() {+ credentials.push_str(&format!("UID={};", escaped(&settings.user)));+ }+ if !settings.password.is_empty() {+ credentials.push_str(&format!("PWD={};", escaped(&settings.password)));+ }+ // Cover both first- and last-occurrence resolution, including options read from a DSN.+ let mut value = format!("{safety}{credentials}");+ for (key, entry) in attrs {+ let overridden = safety.split(';').filter_map(|s| s.split_once('=')).any(|(option, _)| option.eq_ignore_ascii_case(key));+ if overridden+ || (!settings.user.is_empty() && matches!(key.as_str(), "UID" | "USER"))+ || (!settings.password.is_empty() && matches!(key.as_str(), "PWD" | "PASSWORD"))+ {+ continue;+ }+ if key == "DSN" {+ value.push_str(&format!("DSN={entry};"));+ } else if key == "DRIVER" {+ value.push_str(&format!("DRIVER={{{}}};", entry.replace('}', "}}")));+ } else {+ value.push_str(&format!("{key}={};", escaped(entry)));+ }+ }+ value.push_str(&credentials);+ value.push_str(safety);+ // SQLDriverConnectW takes a signed 16-bit length; odbc-api panics if it cannot convert it.+ if value.encode_utf16().count() > i16::MAX as usize {+ return Err(OdbcError::InvalidConnection);+ }+ Ok(value)+}++#[cfg(test)]+mod tests {+ use super::*;++ #[test]+ fn escaped_connections_fit_the_odbc_smallint_length() {+ for password in ["x".repeat(16_384), "};".repeat(8192)] {+ let settings = Settings { dsn: Some("Test".into()), password, ..Default::default() };+ assert!(connection_string(&settings, &attributes(&settings).unwrap(), Driver::Postgres).is_err());+ }+ let settings = Settings { dsn: Some("Test".into()), password: "🦊".repeat(4096), ..Default::default() };+ let value = connection_string(&settings, &attributes(&settings).unwrap(), Driver::Postgres).unwrap();+ assert!(value.len() > i16::MAX as usize);+ assert!(value.encode_utf16().count() <= i16::MAX as usize);+ }++ #[test]+ fn credentials_keep_leading_braces_and_edge_spaces() {+ for password in ["{pass}", " pass ", "{odbc};+test"] {+ let settings = Settings { dsn: Some("Test".into()), password: password.into(), ..Default::default() };+ let raw = connection_string(&settings, &attributes(&settings).unwrap(), Driver::Postgres).unwrap();+ let parsed = attributes(&Settings { connection_string: Some(raw), ..Default::default() }).unwrap();+ let passwords: Vec<_> = parsed.iter().filter(|(key, _)| key == "PWD").map(|(_, value)| value.as_str()).collect();+ assert_eq!(passwords, [password, password]);+ }+ }++ #[test]+ fn ordinary_attributes_are_not_changed_into_braced_literals() {+ let settings = Settings {+ connection_string: Some("Driver={PostgreSQL Unicode(x64)};Servername=127.0.0.1;Port=55435;Database=postgres;SSLmode=disable".into()),+ user: "gridcraft_reader".into(),+ password: "test".into(),+ ..Default::default()+ };+ let value = connection_string(&settings, &attributes(&settings).unwrap(), Driver::Postgres).unwrap();+ for expected in ["UID=gridcraft_reader;", "PWD=test;", "SERVERNAME=127.0.0.1;", "PORT=55435;", "DATABASE=postgres;", "SSLMODE=disable;"] {+ assert!(value.contains(expected), "missing {expected}");+ }+ assert!(value.contains("DRIVER={PostgreSQL Unicode(x64)};"));+ }++ #[test]+ fn sqlite_disables_extensions_with_an_unquoted_empty_value() {+ assert!(Driver::Sqlite.options().contains("LoadExt=;"));+ }++ #[test]+ fn oracle_starts_read_only_transactions_without_a_conflicting_driver_mode() {+ let settings = Settings { connection_string: Some("Driver={Oracle in instantclient_23_26};DBA=R".into()), ..Default::default() };+ let value = connection_string(&settings, &attributes(&settings).unwrap(), Driver::Oracle).unwrap();+ assert!(!value.contains("DBA=R;"));+ assert!(value.starts_with("DBA=W;") && value.ends_with(Driver::Oracle.options()));+ assert_eq!(Driver::Oracle.begin_sql(), Some("SET TRANSACTION READ ONLY"));+ }++ #[test]+ fn supported_drivers_have_database_specific_read_settings() {+ for (name, driver, test_sql, begin_sql) in [+ ("PostgreSQL Unicode(x64)", Driver::Postgres, "SELECT 1", Some("SET TRANSACTION READ ONLY")),+ ("ODBC Driver 18 for SQL Server", Driver::SqlServer, "SELECT 1", None),+ ("MySQL ODBC 9.0 Unicode Driver", Driver::MySql, "SELECT 1", Some("START TRANSACTION READ ONLY")),+ ("MariaDB ODBC 3.2 Driver", Driver::MariaDb, "SELECT 1", Some("START TRANSACTION READ ONLY")),+ ("Oracle in instantclient_23_9", Driver::Oracle, "SELECT 1 FROM DUAL", Some("SET TRANSACTION READ ONLY")),+ ("SQLite3 ODBC Driver", Driver::Sqlite, "SELECT 1", Some("PRAGMA query_only=ON")),+ ] {+ assert_eq!(Driver::from_name(name).unwrap(), driver);+ assert_eq!(driver.test_sql(), test_sql);+ assert_eq!(driver.begin_sql(), begin_sql);+ }+ assert!(Driver::from_name("Unknown ODBC Driver").is_err());+ assert!(Driver::MySql.accepts("MariaDB"));+ assert!(Driver::MariaDb.accepts("MySQL"));+ assert!(!Driver::Oracle.accepts("PostgreSQL"));+ }++ #[test]+ fn driver_options_are_isolated_and_credentials_override_aliases() {+ let connection = Settings {+ connection_string: Some(+ "Driver={MySQL ODBC 9.0 Unicode Driver};PASSWORD=old;USER=old;INITSTMT={DELETE FROM t};OPTION=67108864;SSLCA={C:\\certs\\a;b.pem}"+ .into(),+ ),+ user: "reader".into(),+ password: "{pass};+".into(),+ ..Default::default()+ };+ let attrs = attributes(&connection).unwrap();+ assert_eq!(selector(&attrs).unwrap(), ("DRIVER", "MySQL ODBC 9.0 Unicode Driver"));+ let value = connection_string(&connection, &attrs, Driver::MySql).unwrap();+ assert!(!value.contains("DELETE FROM t"));+ assert!(!value.contains("PASSWORD={old}"));+ assert!(value.contains("SSLCA={C:\\certs\\a;b.pem};"));+ assert!(value.contains("PWD={{pass}};+};"));+ assert!(value.contains("NO_CACHE=1;"));+ assert!(!value.contains("BoolsAsChar"));+ for driver in [Driver::SqlServer, Driver::Oracle, Driver::Sqlite, Driver::MariaDb] {+ assert!(!connection_string(&connection, &attrs, driver).unwrap().contains("BoolsAsChar"));+ }+ }++ #[test]+ fn ambiguous_or_malformed_connection_selectors_are_rejected() {+ for raw in [+ "Driver={SQLite3 ODBC Driver};DSN=Other",+ "DSN=One;dsn=Two",+ "FILEDSN=hidden.dsn",+ "Driver={unclosed",+ "Driver={x}junk",+ "Driver=x;SAVEFILE={hidden.dsn}",+ ] {+ let settings = Settings { connection_string: Some(raw.into()), ..Default::default() };+ assert!(attributes(&settings).and_then(|attrs| selector(&attrs).map(|_| ())).is_err(), "{raw}");+ }+ let attrs = attributes(&Settings { dsn: Some("Reporting".into()), ..Default::default() }).unwrap();+ assert_eq!(selector(&attrs).unwrap(), ("DSN", "Reporting"));+ }++ #[test]+ fn query_guards_preserve_dialects_and_reject_writes() {+ for (driver, sql) in [+ (Driver::SqlServer, "WITH t AS (SELECT 1 AS [value]) SELECT [value] FROM t ORDER BY [value]"),+ (Driver::SqlServer, "SELECT CASE WHEN 1=1 THEN 'DELETE' ELSE 'INTO' END AS [update] /* DELETE */"),+ (Driver::MySql, "SELECT `value`, 'delete' FROM example LIMIT 2"),+ (Driver::Oracle, "SELECT q'[delete]' AS value FROM DUAL"),+ (Driver::Sqlite, "WITH t AS (SELECT 1 AS value) SELECT value FROM t"),+ (Driver::Postgres, "VALUES (1)"),+ ] {+ let prepared = driver.query_sql(sql).unwrap();+ if driver != Driver::Postgres {+ assert_eq!(prepared, sql);+ }+ }+ for (driver, sql) in [+ (Driver::SqlServer, "WITH t AS (SELECT 1 AS n) DELETE FROM example"),+ (Driver::SqlServer, "SELECT 1 INTO copied"),+ (Driver::SqlServer, "SELECT 1 EXEC('DELETE FROM example')"),+ (Driver::MySql, "SELECT 1 INTO OUTFILE '/tmp/result'"),+ (Driver::MariaDb, "SELECT 1 /*!50000 INTO OUTFILE '/tmp/result' */"),+ (Driver::MySql, "SELECT 1 /*M! INTO OUTFILE '/tmp/result' */"),+ (Driver::Sqlite, "WITH t AS (SELECT 1) UPDATE example SET n=1"),+ ] {+ assert!(driver.query_sql(sql).is_err(), "{sql}");+ }+ }+}new file mode 100644@@ -0,0 +1,80 @@+use super::*;++#[test]+fn odbc_values_keep_text_null_boolean_and_numeric_types() {+ assert_eq!(decode(ColumnKind::Text, "=SUM(A1:A2)").unwrap(), Value::text("=SUM(A1:A2)"));+ assert_eq!(decode(ColumnKind::Text, "0012").unwrap(), Value::text("0012"));+ assert_eq!(decode(ColumnKind::Integer, "42").unwrap(), Value::Number(42.0));+ assert_eq!(decode(ColumnKind::Boolean, "1").unwrap(), Value::Bool(true));+ assert_eq!(decode(ColumnKind::Boolean, "false").unwrap(), Value::Bool(false));+ assert!(decode(ColumnKind::Boolean, "not a boolean").is_err());+ for kind in [ColumnKind::Integer, ColumnKind::Decimal, ColumnKind::Float] {+ assert!(decode(kind, "not a number").is_err());+ }+}++#[test]+fn temporal_values_are_typed_and_preserve_fractional_seconds_and_offsets() {+ let date = NaiveDate::from_ymd_opt(2024, 2, 29).unwrap();+ let time = NaiveTime::from_hms_nano_opt(12, 34, 56, 123_456_789).unwrap();+ assert_eq!(decode(ColumnKind::Date, "2024-02-29").unwrap(), Value::Date(date));+ assert_eq!(decode(ColumnKind::Time, "12:34:56.123456789").unwrap(), Value::Time(time));+ assert_eq!(decode(ColumnKind::Timestamp, "2024-02-29 12:34:56.123456789").unwrap(), Value::Timestamp(date.and_time(time)));+ for text in ["2024-02-29 12:34:56.123456789 +03:00", "2024-02-29 12:34:56.123456789+03:00"] {+ let Value::TimestampWithOffset(value) = decode(ColumnKind::TimestampWithOffset, text).unwrap() else { panic!("expected typed timestamp") };+ assert_eq!(value.naive_local(), date.and_time(time));+ assert_eq!(value.offset().local_minus_utc(), 10800);+ }+ for (kind, text) in [(ColumnKind::Date, "2023-02-29"), (ColumnKind::Time, "25:00:00"), (ColumnKind::Timestamp, "invalid")] {+ assert!(decode(kind, text).is_err());+ }+ let value = Value::Timestamp(date.and_time(time));+ assert_eq!(serde_json::from_str::<Value>(&serde_json::to_string(&value).unwrap()).unwrap(), value);+}++#[test]+fn odbc_precision_is_not_silently_lost() {+ for text in ["9007199254740993", "9223372036854775807"] {+ assert_eq!(decode(ColumnKind::Integer, text).unwrap(), Value::text(text));+ }+ assert_eq!(decode(ColumnKind::Decimal, "0.12345678901234567890").unwrap(), Value::text("0.12345678901234567890"));+ assert_eq!(decode(ColumnKind::Decimal, "12.34000").unwrap(), Value::Number(12.34));+ assert_eq!(decode(ColumnKind::Float, "NaN").unwrap(), Value::text("NaN"));+ assert_eq!(decode(ColumnKind::Decimal, "1e-400").unwrap(), Value::text("1e-400"));+}++#[test]+fn queries_are_single_read_operations_and_result_memory_is_bounded() {+ for sql in ["SELECT 1;", "WITH sample AS (SELECT 1) SELECT * FROM sample", "VALUES (1)"] {+ assert!(validate_query(sql).is_ok());+ }+ for sql in ["DELETE FROM sales", "SELECT 1; DELETE FROM sales", "COMMIT", "SELECT 1;;"] {+ assert!(validate_query(sql).is_err());+ }+ let dataset =+ DataSet { columns: vec![Column { name: "n".into(), kind: ColumnKind::Integer }], rows: vec![vec![Value::Number(1.0)]], truncated: false };+ assert!(dataset.validate(Limits { bytes: 1, ..Default::default() }).is_err());+}++#[test]+fn odbc_limits_reject_invalid_and_oversized_data() {+ assert!(Limits { rows: 0, ..Default::default() }.validate().is_err());+ assert!(Limits { rows: 100_001, ..Default::default() }.validate().is_err());+ let data =+ DataSet { columns: vec![Column { name: "text".into(), kind: ColumnKind::Text }], rows: vec![vec![Value::text("=1")]], truncated: false };+ assert!(data.validate(Limits::default()).is_ok());+ assert!(data.validate(Limits { cell_bytes: 1, ..Default::default() }).is_err());+ let ragged = DataSet { rows: vec![vec![]], ..data };+ assert!(ragged.validate(Limits::default()).is_err());+}++#[test]+fn odbc_connections_validate_without_printing_credentials() {+ let connection =+ Connection { dsn: Some("Postgres".into()), connection_string: Some("PWD=hidden".into()), user: "reader".into(), password: "hidden".into() };+ assert!(connection.validate().is_err());+ assert!(!format!("{connection:?}").contains("hidden"));+ assert!(Connection { dsn: Some("Postgres;PWD=hidden".into()), ..Default::default() }.validate().is_err());+ assert!(Connection { dsn: Some("a".repeat(33)), ..Default::default() }.validate().is_err());+ assert!(Connection { connection_string: Some("DSN=Postgres;SAVEFILE=credentials.dsn".into()), ..Default::default() }.validate().is_err());+}new file mode 100644@@ -0,0 +1,114 @@+#![cfg(target_os = "windows")]+//! Each ignored test needs its own dedicated database/driver; no server objects are created.+use gridcraft_odbc::{Backend, ColumnKind, Connection, Limits, NativeBackend, OdbcError, Value, decode};+use std::sync::atomic::AtomicBool;++fn read_database(name: &str, sql: &str, oracle: bool) {+ let prefix = format!("GRIDCRAFT_TEST_ODBC_{name}");+ let dsn = std::env::var(format!("{prefix}_DSN")).ok();+ let connection = Connection {+ connection_string: if dsn.is_some() {+ None+ } else {+ Some(std::env::var(format!("{prefix}_CONNECTION_STRING")).expect("dedicated test connection string"))+ },+ dsn,+ user: std::env::var(format!("{prefix}_USER")).unwrap_or_default(),+ password: std::env::var(format!("{prefix}_PASSWORD")).unwrap_or_default(),+ };+ let backend = NativeBackend;+ backend.test(&connection).unwrap();+ let cancel = AtomicBool::new(false);+ let data = backend.query(&connection, sql, Limits::default(), false, &cancel).unwrap();+ assert_eq!(data.rows.len(), 1);+ let expected_number =+ if name == "SQLITE" && data.columns[0].kind == gridcraft_odbc::ColumnKind::Text { Value::text("42") } else { Value::Number(42.0) };+ assert_eq!(data.rows[0][0], expected_number);+ assert_eq!(data.rows[0][1], Value::text("=1"));+ assert_eq!(data.rows[0][2], Value::text("ação 🦊"));+ assert_eq!(data.rows[0][3], Value::text("9007199254740993"));+ assert_eq!(data.rows[0][4], Value::Empty);+ let temporal_sql = match name {+ "SQLSERVER" => Some(+ "SELECT CAST('2024-02-29' AS date), CAST('12:34:56.123456' AS time(6)), CAST('2024-02-29 12:34:56.123456' AS datetime2(6)), CAST('2024-02-29 12:34:56.123456 +03:00' AS datetimeoffset(6))",+ ),+ "MYSQL" | "MARIADB" => {+ Some("SELECT CAST('2024-02-29' AS DATE), CAST('12:34:56.123456' AS TIME(6)), CAST('2024-02-29 12:34:56.123456' AS DATETIME(6))")+ }+ "ORACLE" => Some("SELECT TIMESTAMP '2024-02-29 12:34:56.123456' FROM DUAL"),+ _ => None,+ };+ if let Some(sql) = temporal_sql {+ let dates = backend.query(&connection, sql, Limits::default(), false, &cancel).unwrap();+ let timestamp = decode(ColumnKind::Timestamp, "2024-02-29 12:34:56.123456").unwrap();+ if oracle {+ assert_eq!(dates.rows[0][0], timestamp);+ } else {+ assert_eq!(dates.rows[0][0], decode(ColumnKind::Date, "2024-02-29").unwrap());+ assert_eq!(dates.rows[0][1], decode(ColumnKind::Time, "12:34:56.123456").unwrap());+ assert_eq!(dates.rows[0][2], timestamp);+ if name == "SQLSERVER" {+ assert_eq!(dates.rows[0][3], decode(ColumnKind::TimestampWithOffset, "2024-02-29 12:34:56.123456 +03:00").unwrap());+ }+ }+ }+ let from = if oracle { " FROM DUAL" } else { "" };+ let two_rows = format!("SELECT 1 AS value{from} UNION ALL SELECT 2 AS value{from}");+ let preview = backend.query(&connection, &two_rows, Limits { rows: 1, ..Default::default() }, true, &cancel).unwrap();+ assert_eq!(preview.rows.len(), 1);+ assert!(preview.truncated);+ assert!(matches!(backend.query(&connection, &two_rows, Limits { rows: 1, ..Default::default() }, false, &cancel), Err(OdbcError::Limit(_))));+ let empty = format!("SELECT 1 AS value{from} WHERE 1=0");+ assert!(backend.query(&connection, &empty, Limits::default(), false, &cancel).unwrap().rows.is_empty());+ assert!(matches!(backend.query(&connection, sql, Limits::default(), false, &AtomicBool::new(true)), Err(OdbcError::Cancelled)));+}++#[test]+#[ignore = "requires a dedicated SQLSERVER ODBC test connection"]+fn sql_server_reads_unicode_literals_and_exact_bigints() {+ read_database(+ "SQLSERVER",+ "SELECT CAST(42 AS int) AS n, CAST('=1' AS nvarchar(10)) AS literal, N'ação 🦊' AS unicode_text, CAST(9007199254740993 AS bigint) AS identifier, CAST(NULL AS nvarchar(10)) AS optional_text",+ false,+ );+}++#[test]+#[ignore = "requires a dedicated MYSQL ODBC test connection"]+fn mysql_reads_unicode_literals_and_exact_bigints() {+ read_database(+ "MYSQL",+ "SELECT CAST(42 AS SIGNED) AS n, '=1' AS literal, 'ação 🦊' AS unicode_text, CAST(9007199254740993 AS SIGNED) AS identifier, NULL AS optional_text",+ false,+ );+}++#[test]+#[ignore = "requires a dedicated MARIADB ODBC test connection"]+fn mariadb_reads_unicode_literals_and_exact_bigints() {+ read_database(+ "MARIADB",+ "SELECT CAST(42 AS SIGNED) AS n, '=1' AS literal, 'ação 🦊' AS unicode_text, CAST(9007199254740993 AS SIGNED) AS identifier, NULL AS optional_text",+ false,+ );+}++#[test]+#[ignore = "requires a dedicated ORACLE ODBC test connection"]+fn oracle_reads_unicode_literals_and_exact_numbers() {+ read_database(+ "ORACLE",+ "SELECT CAST(42 AS NUMBER(10)) AS n, '=1' AS literal, N'ação 🦊' AS unicode_text, CAST(9007199254740993 AS NUMBER(19)) AS identifier, CAST(NULL AS VARCHAR2(10)) AS optional_text FROM DUAL",+ true,+ );+}++#[test]+#[ignore = "requires a dedicated SQLITE ODBC test connection and existing database"]+fn sqlite_reads_unicode_literals_and_exact_integers() {+ read_database(+ "SQLITE",+ "SELECT CAST(42 AS INTEGER) AS n, '=1' AS literal, 'ação 🦊' AS unicode_text, CAST(9007199254740993 AS INTEGER) AS identifier, CAST(NULL AS TEXT) AS optional_text",+ false,+ );+}new file mode 100644@@ -0,0 +1,60 @@+#![cfg(target_os = "windows")]+//! Opt in with a dedicated test connection; all queries use synthetic expressions.+use gridcraft_odbc::{Backend, ColumnKind, Connection, Limits, NativeBackend, OdbcError, Value, decode};+use std::sync::atomic::AtomicBool;++#[test]+#[ignore = "requires GRIDCRAFT_TEST_ODBC_DSN or GRIDCRAFT_TEST_ODBC_CONNECTION_STRING"]+fn postgres_psqlodbc_reads_types_unicode_precision_and_read_only_transactions() {+ let dsn = std::env::var("GRIDCRAFT_TEST_ODBC_DSN").ok();+ let connection = Connection {+ connection_string: if dsn.is_some() {+ None+ } else {+ Some(std::env::var("GRIDCRAFT_TEST_ODBC_CONNECTION_STRING").expect("test connection string"))+ },+ dsn,+ user: std::env::var("GRIDCRAFT_TEST_ODBC_USER").unwrap_or_default(),+ password: std::env::var("GRIDCRAFT_TEST_ODBC_PASSWORD").unwrap_or_default(),+ };+ let backend = NativeBackend;+ backend.test(&connection).unwrap();+ if let Ok(connection_string) = std::env::var("GRIDCRAFT_TEST_ODBC_CONNECTION_STRING") {+ let mut alternate = connection.clone();+ alternate.dsn = None;+ alternate.connection_string = Some(connection_string);+ backend.test(&alternate).unwrap();+ }+ let cancel = AtomicBool::new(false);+ let sql = "SELECT 9007199254740993::bigint AS identifier, 0.12345678901234567890::numeric(30,20) AS exact_amount, 12.34::numeric(10,2) AS amount, true AS active, '=1'::text AS literal, 'ação 🦊'::text AS unicode, timestamp '2026-10-14 12:34:56.123456' AS recorded, NULL::text AS optional_text, 42::integer AS count";+ let data = backend.query(&connection, sql, Limits::default(), false, &cancel).unwrap();+ let row = &data.rows[0];+ assert_eq!(row[0], Value::text("9007199254740993"));+ assert_eq!(row[1], Value::text("0.12345678901234567890"));+ assert_eq!(row[2], Value::Number(12.34));+ assert_eq!(row[3], Value::Bool(true));+ assert_eq!(row[4], Value::text("=1"));+ assert_eq!(row[5], Value::text("ação 🦊"));+ assert_eq!(row[6], decode(ColumnKind::Timestamp, "2026-10-14 12:34:56.123456").unwrap());+ assert_eq!(row[7], Value::Empty);+ assert_eq!(row[8], Value::Number(42.0));+ let dates = backend.query(&connection, "SELECT date '2024-02-29', time '12:34:56.123456'", Limits::default(), false, &cancel).unwrap();+ assert_eq!(dates.rows[0][0], decode(ColumnKind::Date, "2024-02-29").unwrap());+ assert_eq!(dates.rows[0][1], decode(ColumnKind::Time, "12:34:56.123456").unwrap());+ let read_only = backend.query(&connection, "SELECT current_setting('transaction_read_only') AS mode", Limits::default(), false, &cancel).unwrap();+ assert_eq!(read_only.rows[0][0], Value::text("on"));+ let preview = backend.query(&connection, "SELECT generate_series(1,101) AS value", Limits::default(), true, &cancel).unwrap();+ assert_eq!(preview.rows.len(), 100);+ assert!(preview.truncated);+ assert!(matches!(+ backend.query(&connection, "SELECT generate_series(1,2) AS value", Limits { rows: 1, ..Default::default() }, false, &cancel),+ Err(OdbcError::Limit(_))+ ));+ let empty = backend.query(&connection, "SELECT 1 AS value WHERE false", Limits::default(), false, &cancel).unwrap();+ assert_eq!(empty.columns.len(), 1);+ assert!(empty.rows.is_empty());+ assert!(backend.query(&connection, "SELECT decode('ff','hex') AS binary", Limits::default(), false, &cancel).is_err());+ assert!(matches!(backend.query(&connection, "SELECT 1; SELECT 2", Limits::default(), false, &cancel), Err(OdbcError::InvalidQuery)));+ let cancelled = AtomicBool::new(true);+ assert!(matches!(backend.query(&connection, "SELECT 1", Limits::default(), false, &cancelled), Err(OdbcError::Cancelled)));+}@@ -1,6 +1,12 @@ # Architecture -> **Last reviewed:** 2026-10-10 · ** Last updated:** 2026-10-24 · **Change:** minor (engine building blocks for multi-command actions) · **Target:** Microsoft Excel (Microsoft 365)+`gridcraft-odbc` is an isolated L2 connector: connection settings and SQL produce+bounded typed tables, including calendar dates/times. It owns driver safety and+does not depend on the engine, UI or workbook persistence. See+[`crates/odbc/README.md`](../crates/odbc/README.md). Saved query/refresh design is+deferred to [#431](https://github.com/storytold/gridcraft/issues/431).++> **Last reviewed:** 2026-10-10 · ** Last updated:** 2026-10-27 · **Change:** minor (standalone typed ODBC connector) · **Target:** Microsoft Excel (Microsoft 365) How GridCraft is built today. Rules for contributors are in [`AGENTS.md`](../AGENTS.md). @@ -148,6 +154,8 @@ rpm, tarball), FreeBSD, and web (WASM; WebGPU or WebGL2). Release workflows in ## Revision history +- 2026-10-27: Separate the typed ODBC connector from engine/UI and saved-query persistence pending #431.+ | Date | Change | Summary | |---|---|---| | 2026-10-24 | minor | Engine building blocks for multi-command actions: undo groups, recording buffer, document trust, chunked hidden-name storage |new file mode 100644@@ -0,0 +1,53 @@+# ODBC review split++The complete implementation is preserved at `parked/odbc-saved-queries`+(`2b3d5c890f3d076a4f4b0cfba6cd6071ea4046bb`). This plan implements the PR+feedback without choosing the saved-query contract being discussed in #431.++## 1. Standalone connector++- Branch `feat/odbc-connector`, based on upstream main.+- Keep `crates/odbc`, workspace registration and layer registration only, with+ contributor documentation. No engine, UI or workbook changes.+- Contract: `Backend::query(connection, sql, limits, preview, cancelled)` returns+ a bounded typed `DataSet`. Replace spreadsheet cell values with connector values;+ return dates, times and timestamps as Chrono types. Preserve precision, nulls,+ Unicode, credentials redaction, vendor guards and rollback.+- Cover typed temporal results and existing six-driver behavior. Run connector+ tests and repository CI before the English commit.++## 2. One-shot import++- Keep PR #1717 on `feat/odbc-postgresql`; merge the connector branch normally.+- Remove QuerySource, query profiles, refresh jobs, fingerprint/replace logic,+ XLSX extensions and their tests. Restore existing pivot refresh behavior.+- Retain Test, Preview, Load, async jobs, cancellation, undo, CLI and MCP commands.+- Convert connector dates to the workbook date system and apply number formats.+ Preserve offsets as text and dates outside spreadsheet limits as text.+- Update command docs, user docs and tests. Prove saved files contain ordinary+ tables without SQL, connection settings or private query metadata.+- Run affected tests, format/layer/parity checks and repository CI, then commit+ in English and push without rewriting published history.++## 3. Review delivery++- Open a connector-only upstream PR and describe the dependency in PR #1717.+- Link the preserved branch and #431 in the PR descriptions.+- Request a fresh local review of the combined change; fix material findings and+ report actual validation, including unavailable environments.++## Deferred++Arrow record batches and unixODBC are optional follow-ups. Do not introduce a+Polars pipeline, saved transformations, refresh contract or workbook extension.++## Execution notes++- Connector: 17 unit/profile tests passed; revised PostgreSQL and SQLite native+ integration tests passed on 2026-10-27. Other four local servers are absent.+- Connector CI: format and connector Clippy passed. Full CI is blocked by unchanged+ upstream Windows issues: `engine/src/io/tests.rs:32` uses `std::os::unix`, and+ `apps/gridcraft-cli/src/check.rs:281` triggers `only_used_in_recursion` with+ Rust 1.99. Assets and layers passed when run separately.+- Fresh review found historical years must be checked before Excel DATE conversion;+ importer now preserves pre-epoch dates/timestamps as text, with regression cases.@@ -34,6 +34,7 @@ pub const TABLE: &[(&str, Class)] = &[ ("model", Class::Layer(2)), ("calc", Class::Layer(2)), ("xlsx", Class::Layer(2)),+ ("odbc", Class::Layer(2)), ("chart", Class::Layer(3)), ("engine", Class::Layer(5)), ("ui-egui", Class::Layer(6)),