Skip to main content

sqlshield_introspect/
lib.rs

1//! Live database introspection for sqlshield.
2//!
3//! Connects to a running database and builds a [`TablesAndColumns`] map
4//! directly from system catalogs — no SQL dump required. Driver is selected
5//! by URL scheme:
6//!
7//! * `postgres://…` / `postgresql://…` — Postgres via the `postgres` crate.
8//! * `sqlite:///path/to/file.db` (or a bare path) — SQLite via `rusqlite`.
9//!
10//! MySQL support is planned but currently blocked on the `mysql_common`
11//! crate's use of unstable Rust features that haven't reached the project's
12//! pinned toolchain yet.
13//!
14//! Each driver is optional behind a Cargo feature; the default build pulls
15//! both currently-supported drivers.
16
17use std::collections::HashMap;
18
19use sqlshield::schema::TablesAndColumns;
20
21#[derive(Debug, thiserror::Error)]
22pub enum IntrospectError {
23    #[error("invalid database URL: {0}")]
24    InvalidUrl(String),
25
26    #[error("unsupported database URL scheme `{0}` (expected postgres / sqlite)")]
27    UnsupportedScheme(String),
28
29    #[error("driver `{0}` not compiled in (rebuild with the `{0}` feature)")]
30    DriverDisabled(&'static str),
31
32    #[error("postgres error: {0}")]
33    #[cfg(feature = "postgres")]
34    Postgres(#[from] postgres::Error),
35
36    #[error("sqlite error: {0}")]
37    #[cfg(feature = "sqlite")]
38    Sqlite(#[from] rusqlite::Error),
39}
40
41/// Connect to `db_url` and return the live schema. Caller is expected to
42/// pass a URL the running process can actually reach; the function returns
43/// any driver error directly so callers can show a useful message.
44pub fn introspect(db_url: &str) -> Result<TablesAndColumns, IntrospectError> {
45    let scheme = url_scheme(db_url);
46    match scheme.as_deref() {
47        Some("postgres") | Some("postgresql") => introspect_postgres(db_url),
48        Some("mysql") => Err(IntrospectError::DriverDisabled("mysql")),
49        Some("sqlite") | Some("file") => introspect_sqlite_url(db_url),
50        // Bare path → treat as sqlite for convenience.
51        None => introspect_sqlite_path(db_url),
52        Some(other) => Err(IntrospectError::UnsupportedScheme(other.to_string())),
53    }
54}
55
56fn url_scheme(s: &str) -> Option<String> {
57    // Windows drive letters (`C:\…`) parse as a single-character URL
58    // scheme, which would otherwise route a bare path through the
59    // unsupported-scheme branch. Treat any single-letter "scheme" as a
60    // bare filesystem path.
61    let bytes = s.as_bytes();
62    if bytes.len() >= 2 && bytes[1] == b':' && bytes[0].is_ascii_alphabetic() {
63        return None;
64    }
65    url::Url::parse(s)
66        .ok()
67        .map(|u| u.scheme().to_ascii_lowercase())
68}
69
70#[cfg(feature = "postgres")]
71fn introspect_postgres(db_url: &str) -> Result<TablesAndColumns, IntrospectError> {
72    use postgres::{Client, NoTls};
73
74    let mut client = Client::connect(db_url, NoTls)?;
75    // information_schema.columns covers user tables plus views, which is
76    // what the validator wants. Skip catalog schemas to keep the map lean.
77    let rows = client.query(
78        "SELECT table_schema, table_name, column_name
79         FROM information_schema.columns
80         WHERE table_schema NOT IN ('pg_catalog', 'information_schema')",
81        &[],
82    )?;
83
84    let mut tables: TablesAndColumns = HashMap::new();
85    for row in rows {
86        let schema: String = row.get(0);
87        let table: String = row.get(1);
88        let column: String = row.get(2);
89        // Postgres returns identifiers already lowercased for unquoted
90        // tables. Quoted identifiers come back case-preserved. The
91        // validator's PG-mode folding will read both keys correctly.
92        let qualified = format!("{schema}.{table}");
93        tables.entry(table).or_default().insert(column.clone());
94        tables.entry(qualified).or_default().insert(column);
95    }
96    Ok(tables)
97}
98
99#[cfg(not(feature = "postgres"))]
100fn introspect_postgres(_db_url: &str) -> Result<TablesAndColumns, IntrospectError> {
101    Err(IntrospectError::DriverDisabled("postgres"))
102}
103
104#[cfg(feature = "sqlite")]
105fn introspect_sqlite_url(db_url: &str) -> Result<TablesAndColumns, IntrospectError> {
106    // sqlite:///abs/path or sqlite://relative. Strip scheme+`//` to get
107    // the filesystem path; rusqlite opens by path.
108    let parsed = url::Url::parse(db_url).map_err(|e| IntrospectError::InvalidUrl(e.to_string()))?;
109    let path = parsed.path();
110    let path = path.strip_prefix('/').unwrap_or(path);
111    introspect_sqlite_path(path)
112}
113
114#[cfg(feature = "sqlite")]
115fn introspect_sqlite_path(path: &str) -> Result<TablesAndColumns, IntrospectError> {
116    let conn = rusqlite::Connection::open(path)?;
117    let mut tables: TablesAndColumns = HashMap::new();
118
119    let names: Vec<String> = {
120        let mut stmt =
121            conn.prepare("SELECT name FROM sqlite_master WHERE type IN ('table','view')")?;
122        let iter = stmt.query_map([], |row| row.get::<_, String>(0))?;
123        let mut out = Vec::new();
124        for r in iter {
125            out.push(r?);
126        }
127        out
128    };
129
130    for table in names {
131        let mut col_stmt = conn.prepare(&format!("PRAGMA table_info(\"{}\")", escape(&table)))?;
132        let cols_iter = col_stmt.query_map([], |row| row.get::<_, String>(1))?;
133        let mut cols = std::collections::HashSet::new();
134        for c in cols_iter {
135            cols.insert(c?);
136        }
137        tables.insert(table, cols);
138    }
139    Ok(tables)
140}
141
142#[cfg(not(feature = "sqlite"))]
143fn introspect_sqlite_url(_db_url: &str) -> Result<TablesAndColumns, IntrospectError> {
144    Err(IntrospectError::DriverDisabled("sqlite"))
145}
146
147#[cfg(not(feature = "sqlite"))]
148fn introspect_sqlite_path(_path: &str) -> Result<TablesAndColumns, IntrospectError> {
149    Err(IntrospectError::DriverDisabled("sqlite"))
150}
151
152#[cfg(feature = "sqlite")]
153fn escape(s: &str) -> String {
154    // PRAGMA table_info() takes an identifier; escape embedded quotes to
155    // keep arbitrary table names safe.
156    s.replace('"', "\"\"")
157}
158
159#[cfg(all(test, feature = "sqlite"))]
160mod tests {
161    use super::*;
162
163    #[test]
164    fn sqlite_in_memory_roundtrip() {
165        let dir = tempfile::tempdir().unwrap();
166        let path = dir.path().join("t.db");
167        let conn = rusqlite::Connection::open(&path).unwrap();
168        conn.execute_batch(
169            "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
170             CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER);",
171        )
172        .unwrap();
173        drop(conn);
174
175        let tables = introspect(path.to_str().unwrap()).unwrap();
176        assert!(tables.contains_key("users"), "got: {tables:?}");
177        assert!(tables.contains_key("orders"));
178        assert!(tables["users"].contains("name"));
179        assert!(tables["orders"].contains("user_id"));
180    }
181
182    #[test]
183    fn windows_drive_letter_is_not_treated_as_url_scheme() {
184        assert_eq!(url_scheme("C:\\foo\\bar.db"), None);
185        assert_eq!(url_scheme("d:/relative.sqlite"), None);
186        assert_eq!(url_scheme("postgres://x/y").as_deref(), Some("postgres"));
187        assert_eq!(url_scheme("/abs/path.db"), None);
188    }
189}