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