Skip to main content

schema_sql_generator/sqlserver/
sqlserver_table_generator.rs

1use crate::common::generator_context::GeneratorContext;
2use crate::common::sql_string::escape_sql_literal;
3use crate::common::table_generator::{DefaultTableGenerator, TableGenerator};
4use crate::sql_println;
5use crate::sqlserver::sqlserver_column_constraint_generator::SqlServerColumnConstraintGenerator;
6use crate::sqlserver::sqlserver_column_generator::SqlServerColumnGenerator;
7use crate::sqlserver::sqlserver_index_generator::SqlServerIndexGenerator;
8use crate::sqlserver::sqlserver_key_generator::SqlServerKeyGenerator;
9use crate::sqlserver::sqlserver_table_constraint_generator::SqlServerTableConstraintGenerator;
10use schema_model::model::table::Table;
11use schema_model::model::types::LockEscalation;
12
13pub struct SqlServerTableGenerator {
14    context: GeneratorContext,
15    table_generator: DefaultTableGenerator,
16}
17
18impl SqlServerTableGenerator {
19    pub fn new(context: GeneratorContext) -> Self {
20        Self {
21            table_generator: DefaultTableGenerator::new(
22                context.clone(),
23                Box::new(SqlServerColumnGenerator::new(context.clone())),
24                Box::new(SqlServerKeyGenerator::new(context.clone())),
25                Box::new(SqlServerColumnConstraintGenerator::new(context.clone())),
26                Box::new(SqlServerTableConstraintGenerator::new(context.clone())),
27                Box::new(SqlServerIndexGenerator::new(context.clone())),
28            ),
29            context,
30        }
31    }
32}
33
34impl TableGenerator for SqlServerTableGenerator {
35    fn output_tables(&self) {
36        let database_model = self.context.settings().database_model();
37
38        // All drops up front, in reverse-dependency order, rather than interleaved with
39        // each table's own create (H17): SQL Server has no `cascade` on `drop table`, so
40        // dropping in declaration order can fail on the first table that's still an FK
41        // target of a later one. See `tables_in_drop_order`.
42        for table in crate::common::table_generator::tables_in_drop_order(database_model) {
43            self.output_table_drop(table);
44        }
45
46        for schema in database_model.schemas() {
47            for table in schema.tables() {
48                self.output_table_header(table);
49                self.output_table_definition(table);
50                self.output_table_footer(table);
51                self.output_indexes(table);
52                self.output_initial_data(table);
53            }
54        }
55    }
56
57    fn output_table(&self, table: &Table) {
58        self.output_table_drop(table);
59        self.output_table_header(table);
60        self.output_table_definition(table);
61        self.output_table_footer(table);
62        self.output_indexes(table);
63        self.output_initial_data(table);
64    }
65
66    fn output_table_drop(&self, table: &Table) {
67        // The legacy Java codegen tool's SQL Server output uses an existence-checked drop
68        // (`if object_id(...) is not null`) rather than the `drop table if exists ...;` form
69        // the other dialects share, so this is a full override rather than a delegation to
70        // `DefaultTableGenerator::output_table_drop`.
71        // The guard is schema-qualified (H15): querying `object_id('{schema}.{table}', 'U')`
72        // rather than `dbo.sysobjects` on name alone, since the latter ignores schema and
73        // would drop/skip the wrong object when the same table name exists in two schemas.
74        let database_type = self.context.settings().database_type();
75        let separator = self.context.settings().statement_separator().to_string();
76        let fully_qualified_table_name = table.fully_qualified_table_name(database_type);
77        let table_name = table.name();
78
79        self.context.with_writer(|writer| {
80            sql_println!(writer, "/* {} */", table_name);
81            sql_println!(
82                writer,
83                "if object_id('{}', 'U') is not null",
84                escape_sql_literal(&fully_qualified_table_name)
85            );
86            sql_println!(
87                writer,
88                "drop table {}{}",
89                fully_qualified_table_name,
90                separator
91            );
92            sql_println!(writer, "");
93        });
94    }
95
96    fn output_table_header(&self, table: &Table) {
97        let fully_qualified_table_name =
98            table.fully_qualified_table_name(self.context.settings().database_type());
99
100        self.context.with_writer(|writer| {
101            sql_println!(writer, "create table {}", fully_qualified_table_name);
102            sql_println!(writer, "(");
103        });
104    }
105
106    fn output_table_definition(&self, table: &Table) {
107        self.table_generator.output_table_definition(table);
108    }
109
110    fn output_table_footer(&self, table: &Table) {
111        self.table_generator.output_table_footer(table);
112
113        // `Auto` is SQL Server's own default for LOCK_ESCALATION, so emitting the ALTER for it
114        // would be a no-op; only emit it when it actually changes behavior.
115        let lock_escalation_value = match table.lock_escalation() {
116            LockEscalation::Auto => return,
117            LockEscalation::Disable => "disable",
118            LockEscalation::Table => "table",
119        };
120        let separator = self.context.settings().statement_separator();
121        let fully_qualified_table_name =
122            table.fully_qualified_table_name(self.context.settings().database_type());
123        self.context.with_writer(|writer| {
124            sql_println!(
125                writer,
126                "alter table {} set (lock_escalation = {}){}",
127                fully_qualified_table_name,
128                lock_escalation_value,
129                separator
130            );
131            sql_println!(writer, "");
132        });
133    }
134
135    fn output_indexes(&self, table: &Table) {
136        self.table_generator.output_indexes(table);
137    }
138
139    fn output_initial_data(&self, table: &Table) {
140        self.table_generator.output_initial_data(table);
141    }
142}
143
144#[cfg(test)]
145mod tests {
146    use super::*;
147    use crate::common::test_support::make_context;
148    use schema_model::builder::{ColumnBuilder, SchemaBuilder, TableBuilder};
149    use schema_model::model::column_type::ColumnType;
150    use schema_model::model::database_model::DatabaseModel;
151    use schema_model::model::types::{BooleanMode, DatabaseType, ForeignKeyMode};
152
153    #[test]
154    fn output_table_renders_header_and_columns() {
155        let table = TableBuilder::new(None::<&str>, "users")
156            .add_column(
157                ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence)
158                    .required(true)
159                    .build(),
160            )
161            .add_column(
162                ColumnBuilder::new(None::<&str>, "name", ColumnType::Varchar)
163                    .length(50)
164                    .required(true)
165                    .build(),
166            )
167            .build();
168        let schema = SchemaBuilder::new(None::<&str>)
169            .add_table(table.clone())
170            .build();
171        let model =
172            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
173        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
174
175        let generator = SqlServerTableGenerator::new(ctx);
176        generator.output_table_drop(&table);
177        generator.output_table_header(&table);
178        generator.output_table_definition(&table);
179        generator.output_table_footer(&table);
180
181        let output = buffer.contents();
182        assert!(output.contains("/* users */"));
183        assert!(output.contains("if object_id('dbo.users', 'U') is not null"));
184        assert!(output.contains("drop table dbo.users\ngo"));
185        assert!(output.contains("create table dbo.users"));
186        assert!(output.contains("id integer identity(1,1)"));
187        assert!(output.contains("name nvarchar(50)"));
188    }
189
190    #[test]
191    fn output_table_header_escapes_single_quote_in_table_name() {
192        // Regression test: an unescaped embedded quote would break the generated
193        // object_id existence-check SQL string literal.
194        let table = TableBuilder::new(None::<&str>, "o'brien").build();
195        let schema = SchemaBuilder::new(None::<&str>)
196            .add_table(table.clone())
197            .build();
198        let model =
199            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
200        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
201
202        let generator = SqlServerTableGenerator::new(ctx);
203        generator.output_table_drop(&table);
204
205        let output = buffer.contents();
206        assert!(output.contains("if object_id('dbo.o''brien', 'U') is not null"));
207    }
208
209    #[test]
210    fn output_tables_drops_child_before_parent_and_before_any_create() {
211        // Regression test for H17: dropping in declaration order (parent first, since
212        // it must be declared before the child that references it) fails on a rerun
213        // against a populated database - SQL Server has no `cascade` on `drop table`,
214        // so `drop table parent` errors while `child`'s FK constraint still references
215        // it. All drops must be emitted up front, in reverse-dependency order (child
216        // before parent), ahead of every create.
217        let parent = TableBuilder::new(None::<&str>, "parent")
218            .add_column(
219                ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence)
220                    .required(true)
221                    .build(),
222            )
223            .build();
224        let child = TableBuilder::new(None::<&str>, "child")
225            .add_column(
226                ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int)
227                    .required(true)
228                    .build(),
229            )
230            .add_relation(schema_model::model::relation::Relation::new(
231                "parent",
232                "id",
233                "child",
234                "parent_id",
235                schema_model::model::types::RelationType::Cascade,
236                false,
237            ))
238            .build();
239        let schema = SchemaBuilder::new(None::<&str>)
240            .add_table(parent)
241            .add_table(child)
242            .build();
243        let model =
244            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
245        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
246
247        let generator = SqlServerTableGenerator::new(ctx);
248        generator.output_tables();
249
250        let output = buffer.contents();
251        let drop_child = output.find("drop table dbo.child").unwrap();
252        let drop_parent = output.find("drop table dbo.parent").unwrap();
253        let create_child = output.find("create table dbo.child").unwrap();
254        let create_parent = output.find("create table dbo.parent").unwrap();
255
256        assert!(
257            drop_child < drop_parent,
258            "child must be dropped before the parent it references"
259        );
260        assert!(
261            drop_parent < create_child,
262            "every drop must precede every create"
263        );
264        assert!(
265            drop_parent < create_parent,
266            "every drop must precede every create"
267        );
268    }
269
270    #[test]
271    fn output_table_footer_emits_lock_escalation_when_table() {
272        let table = TableBuilder::new(None::<&str>, "users")
273            .lock_escalation(LockEscalation::Table)
274            .build();
275        let schema = SchemaBuilder::new(None::<&str>)
276            .add_table(table.clone())
277            .build();
278        let model =
279            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
280        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
281
282        let generator = SqlServerTableGenerator::new(ctx);
283        generator.output_table_footer(&table);
284
285        assert!(
286            buffer
287                .contents()
288                .contains("alter table dbo.users set (lock_escalation = table)\ngo")
289        );
290    }
291
292    #[test]
293    fn output_table_footer_omits_lock_escalation_when_auto() {
294        let table = TableBuilder::new(None::<&str>, "users")
295            .lock_escalation(LockEscalation::Auto)
296            .build();
297        let schema = SchemaBuilder::new(None::<&str>)
298            .add_table(table.clone())
299            .build();
300        let model =
301            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
302        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
303
304        let generator = SqlServerTableGenerator::new(ctx);
305        generator.output_table_footer(&table);
306
307        assert!(!buffer.contents().contains("lock_escalation"));
308    }
309
310    #[test]
311    fn output_table_footer_omits_lock_escalation_when_unset() {
312        let table = TableBuilder::new(None::<&str>, "users").build();
313        let schema = SchemaBuilder::new(None::<&str>)
314            .add_table(table.clone())
315            .build();
316        let model =
317            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
318        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
319
320        let generator = SqlServerTableGenerator::new(ctx);
321        generator.output_table_footer(&table);
322
323        assert!(!buffer.contents().contains("lock_escalation"));
324    }
325
326    #[test]
327    fn output_table_footer_emits_lock_escalation_when_disable() {
328        let table = TableBuilder::new(None::<&str>, "users")
329            .lock_escalation(LockEscalation::Disable)
330            .build();
331        let schema = SchemaBuilder::new(None::<&str>)
332            .add_table(table.clone())
333            .build();
334        let model =
335            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
336        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
337
338        let generator = SqlServerTableGenerator::new(ctx);
339        generator.output_table_footer(&table);
340
341        assert!(
342            buffer
343                .contents()
344                .contains("alter table dbo.users set (lock_escalation = disable)\ngo")
345        );
346    }
347
348    #[test]
349    fn output_table_footer_qualifies_lock_escalation_with_non_default_schema() {
350        // Regression test: the lock_escalation ALTER used the bare table name while every
351        // other statement for the table (create/drop/object_id check) used the fully
352        // qualified name - for a non-default schema this would alter the wrong object
353        // (SQL Server resolves an unqualified name via the connection's default schema,
354        // not necessarily the table's declared schema).
355        let table = TableBuilder::new(Some("app"), "orders")
356            .lock_escalation(LockEscalation::Table)
357            .build();
358        let schema = SchemaBuilder::new(Some("app"))
359            .add_table(table.clone())
360            .build();
361        let model =
362            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
363        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
364
365        let generator = SqlServerTableGenerator::new(ctx);
366        generator.output_table_footer(&table);
367
368        assert!(
369            buffer
370                .contents()
371                .contains("alter table app.orders set (lock_escalation = table)\ngo")
372        );
373    }
374}