use std::collections::HashMap;
use sqlshield::schema::TablesAndColumns;
#[derive(Debug, thiserror::Error)]
pub enum IntrospectError {
#[error("invalid database URL: {0}")]
InvalidUrl(String),
#[error("unsupported database URL scheme `{0}` (expected postgres / sqlite)")]
UnsupportedScheme(String),
#[error("driver `{0}` not compiled in (rebuild with the `{0}` feature)")]
DriverDisabled(&'static str),
#[error("postgres error: {0}")]
#[cfg(feature = "postgres")]
Postgres(#[from] postgres::Error),
#[error("sqlite error: {0}")]
#[cfg(feature = "sqlite")]
Sqlite(#[from] rusqlite::Error),
}
pub fn introspect(db_url: &str) -> Result<TablesAndColumns, IntrospectError> {
let scheme = url_scheme(db_url);
match scheme.as_deref() {
Some("postgres") | Some("postgresql") => introspect_postgres(db_url),
Some("mysql") => Err(IntrospectError::DriverDisabled("mysql")),
Some("sqlite") | Some("file") => introspect_sqlite_url(db_url),
None => introspect_sqlite_path(db_url),
Some(other) => Err(IntrospectError::UnsupportedScheme(other.to_string())),
}
}
fn url_scheme(s: &str) -> Option<String> {
let bytes = s.as_bytes();
if bytes.len() >= 2 && bytes[1] == b':' && bytes[0].is_ascii_alphabetic() {
return None;
}
url::Url::parse(s)
.ok()
.map(|u| u.scheme().to_ascii_lowercase())
}
#[cfg(feature = "postgres")]
fn introspect_postgres(db_url: &str) -> Result<TablesAndColumns, IntrospectError> {
use postgres::{Client, NoTls};
let mut client = Client::connect(db_url, NoTls)?;
let rows = client.query(
"SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')",
&[],
)?;
let mut tables: TablesAndColumns = HashMap::new();
for row in rows {
let schema: String = row.get(0);
let table: String = row.get(1);
let column: String = row.get(2);
let qualified = format!("{schema}.{table}");
tables.entry(table).or_default().insert(column.clone());
tables.entry(qualified).or_default().insert(column);
}
Ok(tables)
}
#[cfg(not(feature = "postgres"))]
fn introspect_postgres(_db_url: &str) -> Result<TablesAndColumns, IntrospectError> {
Err(IntrospectError::DriverDisabled("postgres"))
}
#[cfg(feature = "sqlite")]
fn introspect_sqlite_url(db_url: &str) -> Result<TablesAndColumns, IntrospectError> {
let parsed = url::Url::parse(db_url).map_err(|e| IntrospectError::InvalidUrl(e.to_string()))?;
let path = parsed.path();
let path = path.strip_prefix('/').unwrap_or(path);
introspect_sqlite_path(path)
}
#[cfg(feature = "sqlite")]
fn introspect_sqlite_path(path: &str) -> Result<TablesAndColumns, IntrospectError> {
let conn = rusqlite::Connection::open(path)?;
let mut tables: TablesAndColumns = HashMap::new();
let names: Vec<String> = {
let mut stmt =
conn.prepare("SELECT name FROM sqlite_master WHERE type IN ('table','view')")?;
let iter = stmt.query_map([], |row| row.get::<_, String>(0))?;
let mut out = Vec::new();
for r in iter {
out.push(r?);
}
out
};
for table in names {
let mut col_stmt = conn.prepare(&format!("PRAGMA table_info(\"{}\")", escape(&table)))?;
let cols_iter = col_stmt.query_map([], |row| row.get::<_, String>(1))?;
let mut cols = std::collections::HashSet::new();
for c in cols_iter {
cols.insert(c?);
}
tables.insert(table, cols);
}
Ok(tables)
}
#[cfg(not(feature = "sqlite"))]
fn introspect_sqlite_url(_db_url: &str) -> Result<TablesAndColumns, IntrospectError> {
Err(IntrospectError::DriverDisabled("sqlite"))
}
#[cfg(not(feature = "sqlite"))]
fn introspect_sqlite_path(_path: &str) -> Result<TablesAndColumns, IntrospectError> {
Err(IntrospectError::DriverDisabled("sqlite"))
}
#[cfg(feature = "sqlite")]
fn escape(s: &str) -> String {
s.replace('"', "\"\"")
}
#[cfg(all(test, feature = "sqlite"))]
mod tests {
use super::*;
#[test]
fn sqlite_in_memory_roundtrip() {
let dir = tempfile::tempdir().unwrap();
let path = dir.path().join("t.db");
let conn = rusqlite::Connection::open(&path).unwrap();
conn.execute_batch(
"CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER);",
)
.unwrap();
drop(conn);
let tables = introspect(path.to_str().unwrap()).unwrap();
assert!(tables.contains_key("users"), "got: {tables:?}");
assert!(tables.contains_key("orders"));
assert!(tables["users"].contains("name"));
assert!(tables["orders"].contains("user_id"));
}
#[test]
fn windows_drive_letter_is_not_treated_as_url_scheme() {
assert_eq!(url_scheme("C:\\foo\\bar.db"), None);
assert_eq!(url_scheme("d:/relative.sqlite"), None);
assert_eq!(url_scheme("postgres://x/y").as_deref(), Some("postgres"));
assert_eq!(url_scheme("/abs/path.db"), None);
}
}