Skip to main content

schema_reverse_engineer/sqlserver/
mod.rs

1//! SQL Server introspection (via `tiberius`), the counterpart of the `postgres` module. The rows
2//! it collects are assembled into a `DatabaseModel` by the same code as the Postgres reader.
3//!
4//! Every numeric catalog column is `CAST` to `int` in SQL, because tiberius does not coerce
5//! between integer widths when reading a row.
6
7mod columns;
8mod constraints;
9mod keys;
10mod relations;
11mod routines;
12mod tables;
13mod triggers;
14mod views;
15
16use crate::error::SchemaReverseEngineerError;
17use crate::reader::{SchemaParts, assemble_schema};
18use schema_model::model::database_model::DatabaseModel;
19use tiberius::{Client, Row};
20use tokio::net::TcpStream;
21use tokio_util::compat::{Compat, TokioAsyncWriteCompatExt};
22
23pub type SqlServerClient = Client<Compat<TcpStream>>;
24
25/// Opens a connection from an ADO-style connection string
26/// (`Server=tcp:host,1433;Database=db;User Id=sa;Password=...`). TLS is required unless the
27/// string says otherwise with `Encrypt=`.
28pub async fn connect(
29    connection_string: &str,
30    trust_server_certificate: bool,
31) -> Result<SqlServerClient, SchemaReverseEngineerError> {
32    let mut config = tiberius::Config::from_ado_string(connection_string).map_err(|e| {
33        SchemaReverseEngineerError::Connection(format!("Invalid SQL Server connection string: {e}"))
34    })?;
35    // `from_ado_string` maps an absent `Encrypt` key to "login only"; keep TLS on by default.
36    if !specifies_encrypt(connection_string) {
37        config.encryption(tiberius::EncryptionLevel::Required);
38    }
39    if trust_server_certificate {
40        config.trust_cert();
41    }
42    let tcp = TcpStream::connect(config.get_addr())
43        .await
44        .map_err(|e| SchemaReverseEngineerError::Connection(format!("Failed to connect: {e}")))?;
45    tcp.set_nodelay(true)
46        .map_err(|e| SchemaReverseEngineerError::Connection(e.to_string()))?;
47    Client::connect(config, tcp.compat_write())
48        .await
49        .map_err(|e| SchemaReverseEngineerError::Connection(format!("Failed to authenticate: {e}")))
50}
51
52fn specifies_encrypt(connection_string: &str) -> bool {
53    connection_string.split(';').any(|part| {
54        part.split('=')
55            .next()
56            .is_some_and(|key| key.trim().eq_ignore_ascii_case("encrypt"))
57    })
58}
59
60/// Introspects the given SQL Server schema (`db_schema`, e.g. `"dbo"`) of the connected
61/// database and builds a `DatabaseModel` describing its tables, columns, keys, foreign keys,
62/// check constraints, views, functions, procedures, and triggers (as raw `other_sql`).
63pub async fn read_sqlserver_schema(
64    client: &mut SqlServerClient,
65    db_schema: &str,
66) -> Result<DatabaseModel, SchemaReverseEngineerError> {
67    let table_names = tables::list_tables(client, db_schema).await?;
68    let all_columns = columns::list_columns(client, db_schema).await?;
69    let table_keys = keys::list_keys(client, db_schema).await?;
70    let table_relations = relations::list_foreign_keys(client, db_schema).await?;
71    let table_constraints = constraints::list_constraints(client, db_schema).await?;
72    let table_views = views::list_views(client, db_schema).await?;
73    let (functions, procedures) = routines::list_routines(client, db_schema).await?;
74    let other_sql = triggers::list_triggers(client, db_schema).await?;
75
76    Ok(assemble_schema(
77        db_schema,
78        SchemaParts {
79            table_names,
80            enum_types: Vec::new(),
81            columns: all_columns,
82            keys: table_keys,
83            relations: table_relations,
84            constraints: table_constraints,
85            views: table_views,
86            functions,
87            procedures,
88            other_sql,
89        },
90    ))
91}
92
93/// Runs `sql` with `db_schema` bound as `@P1` and returns the first result set.
94async fn query(
95    client: &mut SqlServerClient,
96    sql: &str,
97    db_schema: &str,
98) -> Result<Vec<Row>, SchemaReverseEngineerError> {
99    client
100        .query(sql, &[&db_schema])
101        .await
102        .map_err(introspection_error)?
103        .into_first_result()
104        .await
105        .map_err(introspection_error)
106}
107
108fn introspection_error(e: tiberius::error::Error) -> SchemaReverseEngineerError {
109    SchemaReverseEngineerError::Introspection(e.to_string())
110}
111
112fn string(row: &Row, column: &str) -> Result<String, SchemaReverseEngineerError> {
113    opt_string(row, column)?.ok_or_else(|| {
114        SchemaReverseEngineerError::Introspection(format!("unexpected NULL in column {column}"))
115    })
116}
117
118fn opt_string(row: &Row, column: &str) -> Result<Option<String>, SchemaReverseEngineerError> {
119    row.try_get::<&str, _>(column)
120        .map(|v| v.map(str::to_string))
121        .map_err(introspection_error)
122}
123
124fn int(row: &Row, column: &str) -> Result<i32, SchemaReverseEngineerError> {
125    row.try_get::<i32, _>(column)
126        .map_err(introspection_error)?
127        .ok_or_else(|| {
128            SchemaReverseEngineerError::Introspection(format!("unexpected NULL in column {column}"))
129        })
130}
131
132/// Strips every pair of parentheses that wraps the whole expression. SQL Server stores column
133/// defaults and computed-column / check expressions wrapped (`((0))`, `(getdate())`,
134/// `([a]*[b])`); the generators add their own punctuation, so the bare expression is kept.
135pub(crate) fn strip_outer_parens(expression: &str) -> &str {
136    let mut current = expression.trim();
137    while current.starts_with('(') && current.ends_with(')') && wraps_whole(current) {
138        current = current[1..current.len() - 1].trim();
139    }
140    current
141}
142
143/// True when the `(` at the start matches the `)` at the very end (so `(a)+(b)` is false).
144fn wraps_whole(expression: &str) -> bool {
145    let mut depth = 0i32;
146    let mut in_string = false;
147    let mut chars = expression.char_indices().peekable();
148    while let Some((i, ch)) = chars.next() {
149        match ch {
150            '\'' if in_string => {
151                if matches!(chars.peek(), Some((_, '\''))) {
152                    chars.next();
153                } else {
154                    in_string = false;
155                }
156            }
157            '\'' => in_string = true,
158            '(' if !in_string => depth += 1,
159            ')' if !in_string => {
160                depth -= 1;
161                if depth == 0 && i != expression.len() - 1 {
162                    return false;
163                }
164            }
165            _ => {}
166        }
167    }
168    depth == 0
169}
170
171#[cfg(test)]
172mod tests {
173    use super::*;
174
175    #[test]
176    fn strips_all_wrapping_layers() {
177        assert_eq!(strip_outer_parens("((0))"), "0");
178        assert_eq!(strip_outer_parens("(getdate())"), "getdate()");
179        assert_eq!(strip_outer_parens("('A')"), "'A'");
180    }
181
182    #[test]
183    fn keeps_parens_that_do_not_wrap_the_whole_expression() {
184        assert_eq!(strip_outer_parens("(a)+(b)"), "(a)+(b)");
185        assert_eq!(strip_outer_parens("((a)+(b))"), "(a)+(b)");
186    }
187
188    #[test]
189    fn parens_inside_string_literals_are_ignored() {
190        assert_eq!(strip_outer_parens("(')(')"), "')('");
191        assert_eq!(strip_outer_parens("('it''s (x)')"), "'it''s (x)'");
192    }
193
194    #[test]
195    fn detects_explicit_encrypt_key() {
196        assert!(specifies_encrypt("Server=x;Encrypt=false;User Id=sa"));
197        assert!(!specifies_encrypt("Server=x;TrustServerCertificate=true"));
198    }
199}