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!(writer, "if object_id('{}', 'U') is not null", escape_sql_literal(&fully_qualified_table_name));
82            sql_println!(writer, "drop table {}{}", fully_qualified_table_name, separator);
83            sql_println!(writer, "");
84        });
85    }
86
87    fn output_table_header(&self, table: &Table) {
88        let fully_qualified_table_name = table.fully_qualified_table_name(self.context.settings().database_type());
89
90        self.context.with_writer(|writer| {
91            sql_println!(writer, "create table {}", fully_qualified_table_name);
92            sql_println!(writer, "(");
93        });
94    }
95
96    fn output_table_definition(&self, table: &Table) {
97        self.table_generator.output_table_definition(table);
98    }
99
100    fn output_table_footer(&self, table: &Table) {
101        self.table_generator.output_table_footer(table);
102
103        // `Auto` is SQL Server's own default for LOCK_ESCALATION, so emitting the ALTER for it
104        // would be a no-op; only emit it when it actually changes behavior.
105        let lock_escalation_value = match table.lock_escalation() {
106            LockEscalation::Auto => return,
107            LockEscalation::Disable => "disable",
108            LockEscalation::Table => "table",
109        };
110        let separator = self.context.settings().statement_separator();
111        let fully_qualified_table_name = table.fully_qualified_table_name(self.context.settings().database_type());
112        self.context.with_writer(|writer| {
113            sql_println!(
114                writer,
115                "alter table {} set (lock_escalation = {}){}",
116                fully_qualified_table_name,
117                lock_escalation_value,
118                separator
119            );
120            sql_println!(writer, "");
121        });
122    }
123
124    fn output_indexes(&self, table: &Table) {
125        self.table_generator.output_indexes(table);
126    }
127
128    fn output_initial_data(&self, table: &Table) {
129        self.table_generator.output_initial_data(table);
130    }
131}
132
133#[cfg(test)]
134mod tests {
135    use super::*;
136    use crate::common::test_support::make_context;
137    use schema_model::builder::{ColumnBuilder, SchemaBuilder, TableBuilder};
138    use schema_model::model::column_type::ColumnType;
139    use schema_model::model::database_model::DatabaseModel;
140    use schema_model::model::types::{BooleanMode, DatabaseType, ForeignKeyMode};
141
142    #[test]
143    fn output_table_renders_header_and_columns() {
144        let table = TableBuilder::new(None::<&str>, "users")
145            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
146            .add_column(ColumnBuilder::new(None::<&str>, "name", ColumnType::Varchar).length(50).required(true).build())
147            .build();
148        let schema = SchemaBuilder::new(None::<&str>).add_table(table.clone()).build();
149        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
150        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
151
152        let generator = SqlServerTableGenerator::new(ctx);
153        generator.output_table_drop(&table);
154        generator.output_table_header(&table);
155        generator.output_table_definition(&table);
156        generator.output_table_footer(&table);
157
158        let output = buffer.contents();
159        assert!(output.contains("/* users */"));
160        assert!(output.contains("if object_id('dbo.users', 'U') is not null"));
161        assert!(output.contains("drop table dbo.users\ngo"));
162        assert!(output.contains("create table dbo.users"));
163        assert!(output.contains("id integer identity(1,1)"));
164        assert!(output.contains("name nvarchar(50)"));
165    }
166
167    #[test]
168    fn output_table_header_escapes_single_quote_in_table_name() {
169        // Regression test: an unescaped embedded quote would break the generated
170        // object_id existence-check SQL string literal.
171        let table = TableBuilder::new(None::<&str>, "o'brien").build();
172        let schema = SchemaBuilder::new(None::<&str>).add_table(table.clone()).build();
173        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
174        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
175
176        let generator = SqlServerTableGenerator::new(ctx);
177        generator.output_table_drop(&table);
178
179        let output = buffer.contents();
180        assert!(output.contains("if object_id('dbo.o''brien', 'U') is not null"));
181    }
182
183    #[test]
184    fn output_tables_drops_child_before_parent_and_before_any_create() {
185        // Regression test for H17: dropping in declaration order (parent first, since
186        // it must be declared before the child that references it) fails on a rerun
187        // against a populated database - SQL Server has no `cascade` on `drop table`,
188        // so `drop table parent` errors while `child`'s FK constraint still references
189        // it. All drops must be emitted up front, in reverse-dependency order (child
190        // before parent), ahead of every create.
191        let parent = TableBuilder::new(None::<&str>, "parent")
192            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
193            .build();
194        let child = TableBuilder::new(None::<&str>, "child")
195            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).required(true).build())
196            .add_relation(schema_model::model::relation::Relation::new(
197                "parent", "id", "child", "parent_id", schema_model::model::types::RelationType::Cascade, false,
198            ))
199            .build();
200        let schema = SchemaBuilder::new(None::<&str>)
201            .add_table(parent)
202            .add_table(child)
203            .build();
204        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
205        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
206
207        let generator = SqlServerTableGenerator::new(ctx);
208        generator.output_tables();
209
210        let output = buffer.contents();
211        let drop_child = output.find("drop table dbo.child").unwrap();
212        let drop_parent = output.find("drop table dbo.parent").unwrap();
213        let create_child = output.find("create table dbo.child").unwrap();
214        let create_parent = output.find("create table dbo.parent").unwrap();
215
216        assert!(drop_child < drop_parent, "child must be dropped before the parent it references");
217        assert!(drop_parent < create_child, "every drop must precede every create");
218        assert!(drop_parent < create_parent, "every drop must precede every create");
219    }
220
221    #[test]
222    fn output_table_footer_emits_lock_escalation_when_table() {
223        let table = TableBuilder::new(None::<&str>, "users")
224            .lock_escalation(LockEscalation::Table)
225            .build();
226        let schema = SchemaBuilder::new(None::<&str>).add_table(table.clone()).build();
227        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
228        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
229
230        let generator = SqlServerTableGenerator::new(ctx);
231        generator.output_table_footer(&table);
232
233        assert!(buffer.contents().contains("alter table dbo.users set (lock_escalation = table)\ngo"));
234    }
235
236    #[test]
237    fn output_table_footer_omits_lock_escalation_when_auto() {
238        let table = TableBuilder::new(None::<&str>, "users")
239            .lock_escalation(LockEscalation::Auto)
240            .build();
241        let schema = SchemaBuilder::new(None::<&str>).add_table(table.clone()).build();
242        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
243        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
244
245        let generator = SqlServerTableGenerator::new(ctx);
246        generator.output_table_footer(&table);
247
248        assert!(!buffer.contents().contains("lock_escalation"));
249    }
250
251    #[test]
252    fn output_table_footer_omits_lock_escalation_when_unset() {
253        let table = TableBuilder::new(None::<&str>, "users").build();
254        let schema = SchemaBuilder::new(None::<&str>).add_table(table.clone()).build();
255        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
256        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
257
258        let generator = SqlServerTableGenerator::new(ctx);
259        generator.output_table_footer(&table);
260
261        assert!(!buffer.contents().contains("lock_escalation"));
262    }
263
264    #[test]
265    fn output_table_footer_emits_lock_escalation_when_disable() {
266        let table = TableBuilder::new(None::<&str>, "users")
267            .lock_escalation(LockEscalation::Disable)
268            .build();
269        let schema = SchemaBuilder::new(None::<&str>).add_table(table.clone()).build();
270        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
271        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
272
273        let generator = SqlServerTableGenerator::new(ctx);
274        generator.output_table_footer(&table);
275
276        assert!(buffer.contents().contains("alter table dbo.users set (lock_escalation = disable)\ngo"));
277    }
278
279    #[test]
280    fn output_table_footer_qualifies_lock_escalation_with_non_default_schema() {
281        // Regression test: the lock_escalation ALTER used the bare table name while every
282        // other statement for the table (create/drop/object_id check) used the fully
283        // qualified name - for a non-default schema this would alter the wrong object
284        // (SQL Server resolves an unqualified name via the connection's default schema,
285        // not necessarily the table's declared schema).
286        let table = TableBuilder::new(Some("app"), "orders")
287            .lock_escalation(LockEscalation::Table)
288            .build();
289        let schema = SchemaBuilder::new(Some("app")).add_table(table.clone()).build();
290        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
291        let (ctx, buffer) = make_context(model, DatabaseType::SqlServer);
292
293        let generator = SqlServerTableGenerator::new(ctx);
294        generator.output_table_footer(&table);
295
296        assert!(buffer.contents().contains("alter table app.orders set (lock_escalation = table)\ngo"));
297    }
298}