prax_migrate/
sql.rs

1//! SQL generation for migrations.
2
3use crate::diff::{
4    EnumAlterDiff, EnumDiff, ExtensionDiff, FieldAlterDiff, FieldDiff, IndexDiff, ModelAlterDiff,
5    ModelDiff, SchemaDiff, ViewDiff,
6};
7
8/// SQL generator for PostgreSQL.
9pub struct PostgresSqlGenerator;
10
11impl PostgresSqlGenerator {
12    /// Generate SQL for a schema diff.
13    pub fn generate(&self, diff: &SchemaDiff) -> MigrationSql {
14        let mut up = Vec::new();
15        let mut down = Vec::new();
16
17        // Create extensions first (they provide types used by tables)
18        for ext in &diff.create_extensions {
19            up.push(self.create_extension(ext));
20            down.push(self.drop_extension(&ext.name));
21        }
22
23        // Drop extensions (in reverse order)
24        for name in &diff.drop_extensions {
25            up.push(self.drop_extension(name));
26            // Can't easily recreate dropped extensions without knowing schema/version
27        }
28
29        // Create enums (they might be used in tables)
30        for enum_diff in &diff.create_enums {
31            up.push(self.create_enum(enum_diff));
32            down.push(self.drop_enum(&enum_diff.name));
33        }
34
35        // Drop enums (in reverse order)
36        for name in &diff.drop_enums {
37            up.push(self.drop_enum(name));
38            // Can't easily recreate dropped enums without knowing values
39        }
40
41        // Alter enums
42        for alter in &diff.alter_enums {
43            up.extend(self.alter_enum(alter));
44            // Reversing enum alterations is complex
45        }
46
47        // Create models
48        for model in &diff.create_models {
49            up.push(self.create_table(model));
50            down.push(self.drop_table(&model.table_name));
51        }
52
53        // Drop models
54        for name in &diff.drop_models {
55            up.push(self.drop_table(name));
56            // Can't easily recreate dropped tables
57        }
58
59        // Alter models
60        for alter in &diff.alter_models {
61            up.extend(self.alter_table(alter));
62            // Reverse alterations could be generated but complex
63        }
64
65        // Create indexes
66        for index in &diff.create_indexes {
67            up.push(self.create_index(index));
68            down.push(self.drop_index(&index.name, &index.table_name));
69        }
70
71        // Drop indexes
72        for index in &diff.drop_indexes {
73            up.push(self.drop_index(&index.name, &index.table_name));
74        }
75
76        // Create views (after tables they depend on)
77        for view in &diff.create_views {
78            up.push(self.create_view(view));
79            down.push(self.drop_view(&view.view_name, view.is_materialized));
80        }
81
82        // Drop views
83        for name in &diff.drop_views {
84            // We don't know if it was materialized, so try both
85            up.push(self.drop_view(name, false));
86        }
87
88        // Alter views (drop and recreate)
89        for view in &diff.alter_views {
90            // Drop the old view first
91            up.push(self.drop_view(&view.view_name, view.is_materialized));
92            // Then create the new one
93            up.push(self.create_view(view));
94        }
95
96        MigrationSql {
97            up: up.join("\n\n"),
98            down: down.join("\n\n"),
99        }
100    }
101
102    /// Generate CREATE EXTENSION statement.
103    fn create_extension(&self, ext: &ExtensionDiff) -> String {
104        let mut sql = format!("CREATE EXTENSION IF NOT EXISTS \"{}\"", ext.name);
105        if let Some(schema) = &ext.schema {
106            sql.push_str(&format!(" SCHEMA \"{}\"", schema));
107        }
108        if let Some(version) = &ext.version {
109            sql.push_str(&format!(" VERSION '{}'", version));
110        }
111        sql.push(';');
112        sql
113    }
114
115    /// Generate DROP EXTENSION statement.
116    fn drop_extension(&self, name: &str) -> String {
117        format!("DROP EXTENSION IF EXISTS \"{}\" CASCADE;", name)
118    }
119
120    /// Generate CREATE TYPE for enum.
121    fn create_enum(&self, enum_diff: &EnumDiff) -> String {
122        let values: Vec<String> = enum_diff
123            .values
124            .iter()
125            .map(|v| format!("'{}'", v))
126            .collect();
127        format!(
128            "CREATE TYPE \"{}\" AS ENUM ({});",
129            enum_diff.name,
130            values.join(", ")
131        )
132    }
133
134    /// Generate DROP TYPE.
135    fn drop_enum(&self, name: &str) -> String {
136        format!("DROP TYPE IF EXISTS \"{}\";", name)
137    }
138
139    /// Generate ALTER TYPE statements.
140    fn alter_enum(&self, alter: &EnumAlterDiff) -> Vec<String> {
141        let mut stmts = Vec::new();
142
143        for value in &alter.add_values {
144            stmts.push(format!(
145                "ALTER TYPE \"{}\" ADD VALUE IF NOT EXISTS '{}';",
146                alter.name, value
147            ));
148        }
149
150        // Note: PostgreSQL doesn't support removing enum values directly
151        // This would require recreating the type
152
153        stmts
154    }
155
156    /// Generate CREATE TABLE statement.
157    fn create_table(&self, model: &ModelDiff) -> String {
158        let mut columns = Vec::new();
159
160        for field in &model.fields {
161            columns.push(self.column_definition(field));
162        }
163
164        // Add primary key constraint
165        if !model.primary_key.is_empty() {
166            let pk_cols: Vec<String> = model
167                .primary_key
168                .iter()
169                .map(|c| format!("\"{}\"", c))
170                .collect();
171            columns.push(format!("PRIMARY KEY ({})", pk_cols.join(", ")));
172        }
173
174        // Add unique constraints
175        for uc in &model.unique_constraints {
176            let cols: Vec<String> = uc.columns.iter().map(|c| format!("\"{}\"", c)).collect();
177            let constraint = if let Some(name) = &uc.name {
178                format!("CONSTRAINT \"{}\" UNIQUE ({})", name, cols.join(", "))
179            } else {
180                format!("UNIQUE ({})", cols.join(", "))
181            };
182            columns.push(constraint);
183        }
184
185        format!(
186            "CREATE TABLE \"{}\" (\n    {}\n);",
187            model.table_name,
188            columns.join(",\n    ")
189        )
190    }
191
192    /// Generate column definition.
193    fn column_definition(&self, field: &FieldDiff) -> String {
194        let mut parts = vec![format!("\"{}\"", field.column_name), field.sql_type.clone()];
195
196        if field.is_auto_increment {
197            // Replace type with SERIAL variants
198            if field.sql_type == "INTEGER" {
199                parts[1] = "SERIAL".to_string();
200            } else if field.sql_type == "BIGINT" {
201                parts[1] = "BIGSERIAL".to_string();
202            }
203        }
204
205        if !field.nullable && !field.is_primary_key {
206            parts.push("NOT NULL".to_string());
207        }
208
209        if field.is_unique && !field.is_primary_key {
210            parts.push("UNIQUE".to_string());
211        }
212
213        if let Some(default) = &field.default {
214            parts.push(format!("DEFAULT {}", default));
215        }
216
217        parts.join(" ")
218    }
219
220    /// Generate DROP TABLE statement.
221    fn drop_table(&self, name: &str) -> String {
222        format!("DROP TABLE IF EXISTS \"{}\" CASCADE;", name)
223    }
224
225    /// Generate ALTER TABLE statements.
226    fn alter_table(&self, alter: &ModelAlterDiff) -> Vec<String> {
227        let mut stmts = Vec::new();
228
229        // Add columns
230        for field in &alter.add_fields {
231            stmts.push(format!(
232                "ALTER TABLE \"{}\" ADD COLUMN {};",
233                alter.table_name,
234                self.column_definition(field)
235            ));
236        }
237
238        // Drop columns
239        for name in &alter.drop_fields {
240            stmts.push(format!(
241                "ALTER TABLE \"{}\" DROP COLUMN IF EXISTS \"{}\";",
242                alter.table_name, name
243            ));
244        }
245
246        // Alter columns
247        for field in &alter.alter_fields {
248            stmts.extend(self.alter_column(&alter.table_name, field));
249        }
250
251        // Add indexes
252        for index in &alter.add_indexes {
253            stmts.push(self.create_index(index));
254        }
255
256        // Drop indexes
257        for name in &alter.drop_indexes {
258            stmts.push(format!("DROP INDEX IF EXISTS \"{}\";", name));
259        }
260
261        stmts
262    }
263
264    /// Generate ALTER COLUMN statements.
265    fn alter_column(&self, table: &str, field: &FieldAlterDiff) -> Vec<String> {
266        let mut stmts = Vec::new();
267
268        if let Some(new_type) = &field.new_type {
269            stmts.push(format!(
270                "ALTER TABLE \"{}\" ALTER COLUMN \"{}\" TYPE {} USING \"{}\"::{};",
271                table, field.column_name, new_type, field.column_name, new_type
272            ));
273        }
274
275        if let Some(new_nullable) = field.new_nullable {
276            if new_nullable {
277                stmts.push(format!(
278                    "ALTER TABLE \"{}\" ALTER COLUMN \"{}\" DROP NOT NULL;",
279                    table, field.column_name
280                ));
281            } else {
282                stmts.push(format!(
283                    "ALTER TABLE \"{}\" ALTER COLUMN \"{}\" SET NOT NULL;",
284                    table, field.column_name
285                ));
286            }
287        }
288
289        if let Some(new_default) = &field.new_default {
290            stmts.push(format!(
291                "ALTER TABLE \"{}\" ALTER COLUMN \"{}\" SET DEFAULT {};",
292                table, field.column_name, new_default
293            ));
294        }
295
296        stmts
297    }
298
299    /// Generate CREATE INDEX statement.
300    fn create_index(&self, index: &IndexDiff) -> String {
301        let unique = if index.unique { "UNIQUE " } else { "" };
302
303        // Handle vector indexes (HNSW, IVFFlat)
304        if index.is_vector_index() {
305            return self.create_vector_index(index);
306        }
307
308        // Standard index with optional type
309        let using_clause = match &index.index_type {
310            Some(idx_type) => format!(" USING {}", idx_type.as_sql()),
311            None => String::new(),
312        };
313
314        let cols: Vec<String> = index.columns.iter().map(|c| format!("\"{}\"", c)).collect();
315        format!(
316            "CREATE {}INDEX \"{}\" ON \"{}\"{}({});",
317            unique,
318            index.name,
319            index.table_name,
320            using_clause,
321            cols.join(", ")
322        )
323    }
324
325    /// Generate CREATE INDEX for vector indexes (HNSW/IVFFlat).
326    fn create_vector_index(&self, index: &IndexDiff) -> String {
327        let index_type = index.index_type.as_ref().unwrap();
328        let ops_class = index
329            .vector_ops
330            .as_ref()
331            .map(|o| o.as_ops_class())
332            .unwrap_or("vector_cosine_ops");
333
334        // Build column expression with operator class
335        let col_expr = if index.columns.len() == 1 {
336            format!("\"{}\" {}", index.columns[0], ops_class)
337        } else {
338            // Multi-column vector index (rare but possible)
339            index
340                .columns
341                .iter()
342                .map(|c| format!("\"{}\" {}", c, ops_class))
343                .collect::<Vec<_>>()
344                .join(", ")
345        };
346
347        // Build WITH clause for index parameters
348        let with_clause = match index_type {
349            prax_schema::ast::IndexType::Hnsw => {
350                let mut params = Vec::new();
351                if let Some(m) = index.hnsw_m {
352                    params.push(format!("m = {}", m));
353                }
354                if let Some(ef) = index.hnsw_ef_construction {
355                    params.push(format!("ef_construction = {}", ef));
356                }
357                if params.is_empty() {
358                    String::new()
359                } else {
360                    format!(" WITH ({})", params.join(", "))
361                }
362            }
363            prax_schema::ast::IndexType::IvfFlat => {
364                if let Some(lists) = index.ivfflat_lists {
365                    format!(" WITH (lists = {})", lists)
366                } else {
367                    String::new()
368                }
369            }
370            _ => String::new(),
371        };
372
373        format!(
374            "CREATE INDEX \"{}\" ON \"{}\" USING {} ({}){};",
375            index.name,
376            index.table_name,
377            index_type.as_sql(),
378            col_expr,
379            with_clause
380        )
381    }
382
383    /// Generate DROP INDEX statement.
384    fn drop_index(&self, name: &str, _table: &str) -> String {
385        format!("DROP INDEX IF EXISTS \"{}\";", name)
386    }
387
388    /// Generate CREATE VIEW statement.
389    fn create_view(&self, view: &ViewDiff) -> String {
390        if view.is_materialized {
391            format!(
392                "CREATE MATERIALIZED VIEW \"{}\" AS\n{};",
393                view.view_name, view.sql_query
394            )
395        } else {
396            format!(
397                "CREATE OR REPLACE VIEW \"{}\" AS\n{};",
398                view.view_name, view.sql_query
399            )
400        }
401    }
402
403    /// Generate DROP VIEW statement.
404    fn drop_view(&self, name: &str, is_materialized: bool) -> String {
405        if is_materialized {
406            format!("DROP MATERIALIZED VIEW IF EXISTS \"{}\" CASCADE;", name)
407        } else {
408            format!("DROP VIEW IF EXISTS \"{}\" CASCADE;", name)
409        }
410    }
411
412    /// Generate REFRESH MATERIALIZED VIEW statement.
413    #[allow(dead_code)]
414    fn refresh_materialized_view(&self, name: &str, concurrently: bool) -> String {
415        if concurrently {
416            format!("REFRESH MATERIALIZED VIEW CONCURRENTLY \"{}\";", name)
417        } else {
418            format!("REFRESH MATERIALIZED VIEW \"{}\";", name)
419        }
420    }
421}
422
423/// Generated SQL for a migration.
424#[derive(Debug, Clone)]
425pub struct MigrationSql {
426    /// SQL to apply the migration.
427    pub up: String,
428    /// SQL to rollback the migration.
429    pub down: String,
430}
431
432impl MigrationSql {
433    /// Check if the migration is empty.
434    pub fn is_empty(&self) -> bool {
435        self.up.trim().is_empty()
436    }
437}
438
439/// SQL generator for MySQL.
440pub struct MySqlGenerator;
441
442impl MySqlGenerator {
443    /// Generate SQL for a schema diff.
444    pub fn generate(&self, diff: &SchemaDiff) -> MigrationSql {
445        let mut up = Vec::new();
446        let mut down = Vec::new();
447
448        // Create enums (MySQL uses ENUM type in column definitions)
449        // Enums in MySQL are defined per-column, not as separate types
450
451        // Create models
452        for model in &diff.create_models {
453            up.push(self.create_table(model));
454            down.push(self.drop_table(&model.table_name));
455        }
456
457        // Drop models
458        for name in &diff.drop_models {
459            up.push(self.drop_table(name));
460        }
461
462        // Alter models
463        for alter in &diff.alter_models {
464            up.extend(self.alter_table(alter));
465        }
466
467        // Create indexes
468        for index in &diff.create_indexes {
469            up.push(self.create_index(index));
470            down.push(self.drop_index(&index.name, &index.table_name));
471        }
472
473        // Drop indexes
474        for index in &diff.drop_indexes {
475            up.push(self.drop_index(&index.name, &index.table_name));
476        }
477
478        // Create views (after tables they depend on)
479        for view in &diff.create_views {
480            up.push(self.create_view(view));
481            down.push(self.drop_view(&view.view_name));
482        }
483
484        // Drop views
485        for name in &diff.drop_views {
486            up.push(self.drop_view(name));
487        }
488
489        // Alter views (drop and recreate)
490        for view in &diff.alter_views {
491            up.push(self.drop_view(&view.view_name));
492            up.push(self.create_view(view));
493        }
494
495        MigrationSql {
496            up: up.join("\n\n"),
497            down: down.join("\n\n"),
498        }
499    }
500
501    /// Generate CREATE TABLE statement.
502    fn create_table(&self, model: &ModelDiff) -> String {
503        let mut columns = Vec::new();
504
505        for field in &model.fields {
506            columns.push(self.column_definition(field));
507        }
508
509        // Add primary key constraint
510        if !model.primary_key.is_empty() {
511            let pk_cols: Vec<String> = model
512                .primary_key
513                .iter()
514                .map(|c| format!("`{}`", c))
515                .collect();
516            columns.push(format!("PRIMARY KEY ({})", pk_cols.join(", ")));
517        }
518
519        // Add unique constraints
520        for uc in &model.unique_constraints {
521            let cols: Vec<String> = uc.columns.iter().map(|c| format!("`{}`", c)).collect();
522            let constraint = if let Some(name) = &uc.name {
523                format!("CONSTRAINT `{}` UNIQUE ({})", name, cols.join(", "))
524            } else {
525                format!("UNIQUE ({})", cols.join(", "))
526            };
527            columns.push(constraint);
528        }
529
530        format!(
531            "CREATE TABLE `{}` (\n    {}\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
532            model.table_name,
533            columns.join(",\n    ")
534        )
535    }
536
537    /// Generate column definition for MySQL.
538    fn column_definition(&self, field: &FieldDiff) -> String {
539        let mut parts = vec![format!("`{}`", field.column_name)];
540
541        // MySQL type mapping
542        let sql_type = match field.sql_type.as_str() {
543            "INTEGER" if field.is_auto_increment => "INT AUTO_INCREMENT".to_string(),
544            "INTEGER" => "INT".to_string(),
545            "BIGINT" if field.is_auto_increment => "BIGINT AUTO_INCREMENT".to_string(),
546            "TEXT" => "VARCHAR(255)".to_string(), // Default length for VARCHAR
547            "DOUBLE PRECISION" => "DOUBLE".to_string(),
548            "TIMESTAMP WITH TIME ZONE" => "DATETIME".to_string(),
549            "BOOLEAN" => "TINYINT(1)".to_string(),
550            "BYTEA" => "BLOB".to_string(),
551            "JSONB" | "JSON" => "JSON".to_string(),
552            other => other.to_string(),
553        };
554        parts.push(sql_type);
555
556        if !field.nullable && !field.is_primary_key {
557            parts.push("NOT NULL".to_string());
558        }
559
560        if field.is_unique && !field.is_primary_key {
561            parts.push("UNIQUE".to_string());
562        }
563
564        if let Some(default) = &field.default {
565            parts.push(format!("DEFAULT {}", default));
566        }
567
568        parts.join(" ")
569    }
570
571    /// Generate DROP TABLE statement.
572    fn drop_table(&self, name: &str) -> String {
573        format!("DROP TABLE IF EXISTS `{}`;", name)
574    }
575
576    /// Generate ALTER TABLE statements.
577    fn alter_table(&self, alter: &ModelAlterDiff) -> Vec<String> {
578        let mut stmts = Vec::new();
579
580        // Add columns
581        for field in &alter.add_fields {
582            stmts.push(format!(
583                "ALTER TABLE `{}` ADD COLUMN {};",
584                alter.table_name,
585                self.column_definition(field)
586            ));
587        }
588
589        // Drop columns
590        for name in &alter.drop_fields {
591            stmts.push(format!(
592                "ALTER TABLE `{}` DROP COLUMN `{}`;",
593                alter.table_name, name
594            ));
595        }
596
597        // Alter columns
598        for field in &alter.alter_fields {
599            stmts.extend(self.alter_column(&alter.table_name, field));
600        }
601
602        stmts
603    }
604
605    /// Generate ALTER COLUMN statements.
606    fn alter_column(&self, table: &str, field: &FieldAlterDiff) -> Vec<String> {
607        let mut stmts = Vec::new();
608
609        if let Some(new_type) = &field.new_type {
610            stmts.push(format!(
611                "ALTER TABLE `{}` MODIFY COLUMN `{}` {};",
612                table, field.column_name, new_type
613            ));
614        }
615
616        stmts
617    }
618
619    /// Generate CREATE INDEX statement.
620    fn create_index(&self, index: &IndexDiff) -> String {
621        let unique = if index.unique { "UNIQUE " } else { "" };
622
623        // Handle FULLTEXT index for MySQL
624        let index_type = match &index.index_type {
625            Some(prax_schema::ast::IndexType::FullText) => "FULLTEXT ",
626            _ => "",
627        };
628
629        let cols: Vec<String> = index.columns.iter().map(|c| format!("`{}`", c)).collect();
630        format!(
631            "CREATE {}{}INDEX `{}` ON `{}`({});",
632            unique,
633            index_type,
634            index.name,
635            index.table_name,
636            cols.join(", ")
637        )
638    }
639
640    /// Generate DROP INDEX statement.
641    fn drop_index(&self, name: &str, table: &str) -> String {
642        format!("DROP INDEX `{}` ON `{}`;", name, table)
643    }
644
645    /// Generate CREATE VIEW statement.
646    fn create_view(&self, view: &ViewDiff) -> String {
647        // MySQL doesn't support materialized views natively
648        // but we can create a regular view
649        format!(
650            "CREATE OR REPLACE VIEW `{}` AS\n{};",
651            view.view_name, view.sql_query
652        )
653    }
654
655    /// Generate DROP VIEW statement.
656    fn drop_view(&self, name: &str) -> String {
657        format!("DROP VIEW IF EXISTS `{}`;", name)
658    }
659}
660
661/// SQL generator for SQLite.
662pub struct SqliteGenerator;
663
664impl SqliteGenerator {
665    /// Generate SQL for a schema diff.
666    pub fn generate(&self, diff: &SchemaDiff) -> MigrationSql {
667        let mut up = Vec::new();
668        let mut down = Vec::new();
669
670        // Create models
671        for model in &diff.create_models {
672            up.push(self.create_table(model));
673            down.push(self.drop_table(&model.table_name));
674        }
675
676        // Drop models
677        for name in &diff.drop_models {
678            up.push(self.drop_table(name));
679        }
680
681        // Create indexes
682        for index in &diff.create_indexes {
683            up.push(self.create_index(index));
684            down.push(self.drop_index(&index.name));
685        }
686
687        // Drop indexes
688        for index in &diff.drop_indexes {
689            up.push(self.drop_index(&index.name));
690        }
691
692        // Create views (after tables they depend on)
693        for view in &diff.create_views {
694            up.push(self.create_view(view));
695            down.push(self.drop_view(&view.view_name));
696        }
697
698        // Drop views
699        for name in &diff.drop_views {
700            up.push(self.drop_view(name));
701        }
702
703        // Alter views (drop and recreate)
704        for view in &diff.alter_views {
705            up.push(self.drop_view(&view.view_name));
706            up.push(self.create_view(view));
707        }
708
709        MigrationSql {
710            up: up.join("\n\n"),
711            down: down.join("\n\n"),
712        }
713    }
714
715    /// Generate CREATE TABLE statement.
716    fn create_table(&self, model: &ModelDiff) -> String {
717        let mut columns = Vec::new();
718
719        for field in &model.fields {
720            columns.push(self.column_definition(field));
721        }
722
723        // SQLite handles primary key in column definition for INTEGER PRIMARY KEY
724        let has_integer_pk = model
725            .fields
726            .iter()
727            .any(|f| f.is_primary_key && f.sql_type == "INTEGER" && f.is_auto_increment);
728
729        // Add primary key constraint only if not using INTEGER PRIMARY KEY
730        if !model.primary_key.is_empty() && !has_integer_pk {
731            let pk_cols: Vec<String> = model
732                .primary_key
733                .iter()
734                .map(|c| format!("\"{}\"", c))
735                .collect();
736            columns.push(format!("PRIMARY KEY ({})", pk_cols.join(", ")));
737        }
738
739        // Add unique constraints
740        for uc in &model.unique_constraints {
741            let cols: Vec<String> = uc.columns.iter().map(|c| format!("\"{}\"", c)).collect();
742            let constraint = if let Some(name) = &uc.name {
743                format!("CONSTRAINT \"{}\" UNIQUE ({})", name, cols.join(", "))
744            } else {
745                format!("UNIQUE ({})", cols.join(", "))
746            };
747            columns.push(constraint);
748        }
749
750        format!(
751            "CREATE TABLE \"{}\" (\n    {}\n);",
752            model.table_name,
753            columns.join(",\n    ")
754        )
755    }
756
757    /// Generate column definition for SQLite.
758    fn column_definition(&self, field: &FieldDiff) -> String {
759        let mut parts = vec![format!("\"{}\"", field.column_name)];
760
761        // SQLite type mapping
762        let sql_type = match field.sql_type.as_str() {
763            "INTEGER" if field.is_primary_key && field.is_auto_increment => {
764                // INTEGER PRIMARY KEY is auto-increment in SQLite
765                parts.push("INTEGER PRIMARY KEY".to_string());
766                return parts.join(" ");
767            }
768            "BIGINT" => "INTEGER".to_string(),
769            "DOUBLE PRECISION" => "REAL".to_string(),
770            "TIMESTAMP WITH TIME ZONE" | "DATETIME" => "TEXT".to_string(), // SQLite stores dates as TEXT
771            "BOOLEAN" => "INTEGER".to_string(),
772            "BYTEA" | "BLOB" => "BLOB".to_string(),
773            "JSONB" | "JSON" => "TEXT".to_string(), // SQLite stores JSON as TEXT
774            other => other.to_string(),
775        };
776        parts.push(sql_type);
777
778        if !field.nullable && !field.is_primary_key {
779            parts.push("NOT NULL".to_string());
780        }
781
782        if field.is_unique && !field.is_primary_key {
783            parts.push("UNIQUE".to_string());
784        }
785
786        if let Some(default) = &field.default {
787            parts.push(format!("DEFAULT {}", default));
788        }
789
790        parts.join(" ")
791    }
792
793    /// Generate DROP TABLE statement.
794    fn drop_table(&self, name: &str) -> String {
795        format!("DROP TABLE IF EXISTS \"{}\";", name)
796    }
797
798    /// Generate CREATE INDEX statement.
799    fn create_index(&self, index: &IndexDiff) -> String {
800        let unique = if index.unique { "UNIQUE " } else { "" };
801
802        let cols: Vec<String> = index.columns.iter().map(|c| format!("\"{}\"", c)).collect();
803        format!(
804            "CREATE {}INDEX \"{}\" ON \"{}\"({});",
805            unique,
806            index.name,
807            index.table_name,
808            cols.join(", ")
809        )
810    }
811
812    /// Generate DROP INDEX statement.
813    fn drop_index(&self, name: &str) -> String {
814        format!("DROP INDEX IF EXISTS \"{}\";", name)
815    }
816
817    /// Generate CREATE VIEW statement.
818    fn create_view(&self, view: &ViewDiff) -> String {
819        // SQLite doesn't support materialized views
820        // but we can create a regular view
821        format!(
822            "CREATE VIEW IF NOT EXISTS \"{}\" AS\n{};",
823            view.view_name, view.sql_query
824        )
825    }
826
827    /// Generate DROP VIEW statement.
828    fn drop_view(&self, name: &str) -> String {
829        format!("DROP VIEW IF EXISTS \"{}\";", name)
830    }
831}
832
833/// SQL generator for Microsoft SQL Server.
834pub struct MssqlGenerator;
835
836impl MssqlGenerator {
837    /// Generate SQL for a schema diff.
838    pub fn generate(&self, diff: &SchemaDiff) -> MigrationSql {
839        let mut up = Vec::new();
840        let mut down = Vec::new();
841
842        // Create models
843        for model in &diff.create_models {
844            up.push(self.create_table(model));
845            down.push(self.drop_table(&model.table_name));
846        }
847
848        // Drop models
849        for name in &diff.drop_models {
850            up.push(self.drop_table(name));
851        }
852
853        // Alter models
854        for alter in &diff.alter_models {
855            up.extend(self.alter_table(alter));
856        }
857
858        // Create indexes
859        for index in &diff.create_indexes {
860            up.push(self.create_index(index));
861            down.push(self.drop_index(&index.name, &index.table_name));
862        }
863
864        // Drop indexes
865        for index in &diff.drop_indexes {
866            up.push(self.drop_index(&index.name, &index.table_name));
867        }
868
869        // Create views (after tables they depend on)
870        for view in &diff.create_views {
871            up.push(self.create_view(view));
872            down.push(self.drop_view(&view.view_name, view.is_materialized));
873        }
874
875        // Drop views
876        for name in &diff.drop_views {
877            up.push(self.drop_view(name, false));
878        }
879
880        // Alter views (drop and recreate)
881        for view in &diff.alter_views {
882            up.push(self.drop_view(&view.view_name, view.is_materialized));
883            up.push(self.create_view(view));
884        }
885
886        MigrationSql {
887            up: up.join("\n\nGO\n\n"),
888            down: down.join("\n\nGO\n\n"),
889        }
890    }
891
892    /// Generate CREATE TABLE statement.
893    fn create_table(&self, model: &ModelDiff) -> String {
894        let mut columns = Vec::new();
895
896        for field in &model.fields {
897            columns.push(self.column_definition(field));
898        }
899
900        // Add primary key constraint
901        if !model.primary_key.is_empty() {
902            let pk_cols: Vec<String> = model
903                .primary_key
904                .iter()
905                .map(|c| format!("[{}]", c))
906                .collect();
907            columns.push(format!(
908                "CONSTRAINT [PK_{}] PRIMARY KEY ({})",
909                model.table_name,
910                pk_cols.join(", ")
911            ));
912        }
913
914        // Add unique constraints
915        for uc in &model.unique_constraints {
916            let cols: Vec<String> = uc.columns.iter().map(|c| format!("[{}]", c)).collect();
917            let name = uc
918                .name
919                .clone()
920                .unwrap_or_else(|| format!("UQ_{}_{}", model.table_name, uc.columns.join("_")));
921            columns.push(format!(
922                "CONSTRAINT [{}] UNIQUE ({})",
923                name,
924                cols.join(", ")
925            ));
926        }
927
928        format!(
929            "CREATE TABLE [{}] (\n    {}\n);",
930            model.table_name,
931            columns.join(",\n    ")
932        )
933    }
934
935    /// Generate column definition for MSSQL.
936    fn column_definition(&self, field: &FieldDiff) -> String {
937        let mut parts = vec![format!("[{}]", field.column_name)];
938
939        // MSSQL type mapping
940        let sql_type = match field.sql_type.as_str() {
941            "INTEGER" => "INT".to_string(),
942            "BIGINT" => "BIGINT".to_string(),
943            "TEXT" => "NVARCHAR(MAX)".to_string(),
944            "DOUBLE PRECISION" => "FLOAT".to_string(),
945            "TIMESTAMP WITH TIME ZONE" => "DATETIMEOFFSET".to_string(),
946            "BOOLEAN" => "BIT".to_string(),
947            "BYTEA" => "VARBINARY(MAX)".to_string(),
948            "JSONB" | "JSON" => "NVARCHAR(MAX)".to_string(), // MSSQL 2016+ has JSON support
949            "UUID" => "UNIQUEIDENTIFIER".to_string(),
950            "DECIMAL" => "DECIMAL(18,2)".to_string(),
951            other => other.to_string(),
952        };
953        parts.push(sql_type);
954
955        if field.is_auto_increment {
956            parts.push("IDENTITY(1,1)".to_string());
957        }
958
959        if !field.nullable && !field.is_primary_key {
960            parts.push("NOT NULL".to_string());
961        }
962
963        if field.is_unique && !field.is_primary_key {
964            // Unique constraint will be added at table level in MSSQL
965        }
966
967        if let Some(default) = &field.default {
968            parts.push(format!("DEFAULT {}", default));
969        }
970
971        parts.join(" ")
972    }
973
974    /// Generate DROP TABLE statement.
975    fn drop_table(&self, name: &str) -> String {
976        format!("DROP TABLE IF EXISTS [{}];", name)
977    }
978
979    /// Generate ALTER TABLE statements.
980    fn alter_table(&self, alter: &ModelAlterDiff) -> Vec<String> {
981        let mut stmts = Vec::new();
982
983        // Add columns
984        for field in &alter.add_fields {
985            stmts.push(format!(
986                "ALTER TABLE [{}] ADD {};",
987                alter.table_name,
988                self.column_definition(field)
989            ));
990        }
991
992        // Drop columns
993        for name in &alter.drop_fields {
994            stmts.push(format!(
995                "ALTER TABLE [{}] DROP COLUMN [{}];",
996                alter.table_name, name
997            ));
998        }
999
1000        // Alter columns
1001        for field in &alter.alter_fields {
1002            stmts.extend(self.alter_column(&alter.table_name, field));
1003        }
1004
1005        stmts
1006    }
1007
1008    /// Generate ALTER COLUMN statements.
1009    fn alter_column(&self, table: &str, field: &FieldAlterDiff) -> Vec<String> {
1010        let mut stmts = Vec::new();
1011
1012        if let Some(new_type) = &field.new_type {
1013            stmts.push(format!(
1014                "ALTER TABLE [{}] ALTER COLUMN [{}] {};",
1015                table, field.column_name, new_type
1016            ));
1017        }
1018
1019        stmts
1020    }
1021
1022    /// Generate CREATE INDEX statement.
1023    fn create_index(&self, index: &IndexDiff) -> String {
1024        let unique = if index.unique { "UNIQUE " } else { "" };
1025
1026        let cols: Vec<String> = index.columns.iter().map(|c| format!("[{}]", c)).collect();
1027        format!(
1028            "CREATE {}INDEX [{}] ON [{}]({});",
1029            unique,
1030            index.name,
1031            index.table_name,
1032            cols.join(", ")
1033        )
1034    }
1035
1036    /// Generate DROP INDEX statement.
1037    fn drop_index(&self, name: &str, table: &str) -> String {
1038        format!("DROP INDEX [{}] ON [{}];", name, table)
1039    }
1040
1041    /// Generate CREATE VIEW statement.
1042    ///
1043    /// MSSQL supports indexed views (similar to materialized views) with:
1044    /// - SCHEMABINDING option
1045    /// - Unique clustered index on the view
1046    fn create_view(&self, view: &ViewDiff) -> String {
1047        if view.is_materialized {
1048            // Create an indexed view (MSSQL's equivalent of materialized views)
1049            // Note: This requires additional setup like creating a clustered index
1050            format!(
1051                "CREATE VIEW [{}] WITH SCHEMABINDING AS\n{};\n\n-- Create unique clustered index for indexed view\n-- CREATE UNIQUE CLUSTERED INDEX [IX_{}_Clustered] ON [{}] ([id]);",
1052                view.view_name, view.sql_query, view.view_name, view.view_name
1053            )
1054        } else {
1055            format!(
1056                "CREATE OR ALTER VIEW [{}] AS\n{};",
1057                view.view_name, view.sql_query
1058            )
1059        }
1060    }
1061
1062    /// Generate DROP VIEW statement.
1063    fn drop_view(&self, name: &str, _is_materialized: bool) -> String {
1064        // MSSQL uses the same syntax for regular and indexed views
1065        format!("DROP VIEW IF EXISTS [{}];", name)
1066    }
1067
1068    /// Generate sp_refreshview for refreshing view metadata.
1069    #[allow(dead_code)]
1070    fn refresh_view(&self, name: &str) -> String {
1071        format!("EXEC sp_refreshview N'{}';", name)
1072    }
1073}
1074
1075#[cfg(test)]
1076mod tests {
1077    use super::*;
1078
1079    #[test]
1080    fn test_create_enum() {
1081        let generator = PostgresSqlGenerator;
1082        let enum_diff = EnumDiff {
1083            name: "Status".to_string(),
1084            values: vec!["PENDING".to_string(), "ACTIVE".to_string()],
1085        };
1086
1087        let sql = generator.create_enum(&enum_diff);
1088        assert!(sql.contains("CREATE TYPE"));
1089        assert!(sql.contains("Status"));
1090        assert!(sql.contains("PENDING"));
1091        assert!(sql.contains("ACTIVE"));
1092    }
1093
1094    #[test]
1095    fn test_create_table() {
1096        let generator = PostgresSqlGenerator;
1097        let model = ModelDiff {
1098            name: "User".to_string(),
1099            table_name: "users".to_string(),
1100            fields: vec![
1101                FieldDiff {
1102                    name: "id".to_string(),
1103                    column_name: "id".to_string(),
1104                    sql_type: "INTEGER".to_string(),
1105                    nullable: false,
1106                    default: None,
1107                    is_primary_key: true,
1108                    is_auto_increment: true,
1109                    is_unique: false,
1110                },
1111                FieldDiff {
1112                    name: "email".to_string(),
1113                    column_name: "email".to_string(),
1114                    sql_type: "TEXT".to_string(),
1115                    nullable: false,
1116                    default: None,
1117                    is_primary_key: false,
1118                    is_auto_increment: false,
1119                    is_unique: true,
1120                },
1121            ],
1122            primary_key: vec!["id".to_string()],
1123            indexes: Vec::new(),
1124            unique_constraints: Vec::new(),
1125        };
1126
1127        let sql = generator.create_table(&model);
1128        assert!(sql.contains("CREATE TABLE"));
1129        assert!(sql.contains("users"));
1130        assert!(sql.contains("SERIAL"));
1131        assert!(sql.contains("email"));
1132        assert!(sql.contains("UNIQUE"));
1133        assert!(sql.contains("PRIMARY KEY"));
1134    }
1135
1136    #[test]
1137    fn test_create_index() {
1138        let generator = PostgresSqlGenerator;
1139        let index = IndexDiff::new("idx_users_email", "users", vec!["email".to_string()]).unique();
1140
1141        let sql = generator.create_index(&index);
1142        assert!(sql.contains("CREATE UNIQUE INDEX"));
1143        assert!(sql.contains("idx_users_email"));
1144        assert!(sql.contains("users"));
1145    }
1146
1147    #[test]
1148    fn test_create_hnsw_index() {
1149        use prax_schema::ast::{IndexType, VectorOps};
1150
1151        let generator = PostgresSqlGenerator;
1152        let index = IndexDiff::new("idx_embedding", "documents", vec!["embedding".to_string()])
1153            .with_type(IndexType::Hnsw)
1154            .with_vector_ops(VectorOps::Cosine)
1155            .with_hnsw_m(16)
1156            .with_hnsw_ef_construction(64);
1157
1158        let sql = generator.create_index(&index);
1159        assert!(sql.contains("CREATE INDEX"));
1160        assert!(sql.contains("USING hnsw"));
1161        assert!(sql.contains("vector_cosine_ops"));
1162        assert!(sql.contains("m = 16"));
1163        assert!(sql.contains("ef_construction = 64"));
1164    }
1165
1166    #[test]
1167    fn test_create_ivfflat_index() {
1168        use prax_schema::ast::{IndexType, VectorOps};
1169
1170        let generator = PostgresSqlGenerator;
1171        let index = IndexDiff::new(
1172            "idx_embedding_l2",
1173            "documents",
1174            vec!["embedding".to_string()],
1175        )
1176        .with_type(IndexType::IvfFlat)
1177        .with_vector_ops(VectorOps::L2)
1178        .with_ivfflat_lists(100);
1179
1180        let sql = generator.create_index(&index);
1181        assert!(sql.contains("CREATE INDEX"));
1182        assert!(sql.contains("USING ivfflat"));
1183        assert!(sql.contains("vector_l2_ops"));
1184        assert!(sql.contains("lists = 100"));
1185    }
1186
1187    #[test]
1188    fn test_create_gin_index() {
1189        use prax_schema::ast::IndexType;
1190
1191        let generator = PostgresSqlGenerator;
1192        let index =
1193            IndexDiff::new("idx_tags", "posts", vec!["tags".to_string()]).with_type(IndexType::Gin);
1194
1195        let sql = generator.create_index(&index);
1196        assert!(sql.contains("CREATE INDEX"));
1197        assert!(sql.contains("USING GIN"));
1198        assert!(sql.contains("idx_tags"));
1199    }
1200
1201    #[test]
1202    fn test_alter_table_add_column() {
1203        let generator = PostgresSqlGenerator;
1204        let alter = ModelAlterDiff {
1205            name: "User".to_string(),
1206            table_name: "users".to_string(),
1207            add_fields: vec![FieldDiff {
1208                name: "age".to_string(),
1209                column_name: "age".to_string(),
1210                sql_type: "INTEGER".to_string(),
1211                nullable: true,
1212                default: None,
1213                is_primary_key: false,
1214                is_auto_increment: false,
1215                is_unique: false,
1216            }],
1217            drop_fields: Vec::new(),
1218            alter_fields: Vec::new(),
1219            add_indexes: Vec::new(),
1220            drop_indexes: Vec::new(),
1221        };
1222
1223        let stmts = generator.alter_table(&alter);
1224        assert_eq!(stmts.len(), 1);
1225        assert!(stmts[0].contains("ADD COLUMN"));
1226        assert!(stmts[0].contains("age"));
1227    }
1228
1229    #[test]
1230    fn test_create_view() {
1231        let generator = PostgresSqlGenerator;
1232        let view = ViewDiff {
1233            name: "UserStats".to_string(),
1234            view_name: "user_stats".to_string(),
1235            sql_query: "SELECT id, COUNT(*) as post_count FROM users GROUP BY id".to_string(),
1236            is_materialized: false,
1237            refresh_interval: None,
1238            fields: vec![],
1239        };
1240
1241        let sql = generator.create_view(&view);
1242        assert!(sql.contains("CREATE OR REPLACE VIEW"));
1243        assert!(sql.contains("user_stats"));
1244        assert!(sql.contains("SELECT id"));
1245        assert!(sql.contains("post_count"));
1246    }
1247
1248    #[test]
1249    fn test_create_materialized_view() {
1250        let generator = PostgresSqlGenerator;
1251        let view = ViewDiff {
1252            name: "UserStats".to_string(),
1253            view_name: "user_stats".to_string(),
1254            sql_query: "SELECT id, COUNT(*) as post_count FROM users GROUP BY id".to_string(),
1255            is_materialized: true,
1256            refresh_interval: Some("1h".to_string()),
1257            fields: vec![],
1258        };
1259
1260        let sql = generator.create_view(&view);
1261        assert!(sql.contains("CREATE MATERIALIZED VIEW"));
1262        assert!(sql.contains("user_stats"));
1263        assert!(!sql.contains("OR REPLACE")); // Materialized views don't support OR REPLACE
1264    }
1265
1266    #[test]
1267    fn test_drop_view() {
1268        let generator = PostgresSqlGenerator;
1269
1270        let sql = generator.drop_view("user_stats", false);
1271        assert!(sql.contains("DROP VIEW"));
1272        assert!(sql.contains("user_stats"));
1273        assert!(sql.contains("CASCADE"));
1274
1275        let sql_mat = generator.drop_view("user_stats", true);
1276        assert!(sql_mat.contains("DROP MATERIALIZED VIEW"));
1277        assert!(sql_mat.contains("user_stats"));
1278    }
1279
1280    #[test]
1281    fn test_refresh_materialized_view() {
1282        let generator = PostgresSqlGenerator;
1283
1284        let sql = generator.refresh_materialized_view("user_stats", false);
1285        assert!(sql.contains("REFRESH MATERIALIZED VIEW"));
1286        assert!(sql.contains("user_stats"));
1287        assert!(!sql.contains("CONCURRENTLY"));
1288
1289        let sql_concurrent = generator.refresh_materialized_view("user_stats", true);
1290        assert!(sql_concurrent.contains("CONCURRENTLY"));
1291    }
1292
1293    #[test]
1294    fn test_generate_with_views() {
1295        use crate::diff::SchemaDiff;
1296
1297        let generator = PostgresSqlGenerator;
1298        let mut diff = SchemaDiff::default();
1299        diff.create_views.push(ViewDiff {
1300            name: "ActiveUsers".to_string(),
1301            view_name: "active_users".to_string(),
1302            sql_query: "SELECT * FROM users WHERE active = true".to_string(),
1303            is_materialized: false,
1304            refresh_interval: None,
1305            fields: vec![],
1306        });
1307
1308        let sql = generator.generate(&diff);
1309        assert!(!sql.is_empty());
1310        assert!(sql.up.contains("CREATE OR REPLACE VIEW"));
1311        assert!(sql.up.contains("active_users"));
1312        assert!(sql.down.contains("DROP VIEW"));
1313    }
1314
1315    // ==================== MySQL Generator Tests ====================
1316
1317    #[test]
1318    fn test_mysql_create_view() {
1319        let generator = MySqlGenerator;
1320        let view = ViewDiff {
1321            name: "UserStats".to_string(),
1322            view_name: "user_stats".to_string(),
1323            sql_query: "SELECT id, COUNT(*) as post_count FROM users GROUP BY id".to_string(),
1324            is_materialized: false,
1325            refresh_interval: None,
1326            fields: vec![],
1327        };
1328
1329        let sql = generator.create_view(&view);
1330        assert!(sql.contains("CREATE OR REPLACE VIEW"));
1331        assert!(sql.contains("`user_stats`"));
1332        assert!(sql.contains("SELECT id"));
1333    }
1334
1335    #[test]
1336    fn test_mysql_drop_view() {
1337        let generator = MySqlGenerator;
1338        let sql = generator.drop_view("user_stats");
1339        assert!(sql.contains("DROP VIEW IF EXISTS"));
1340        assert!(sql.contains("`user_stats`"));
1341    }
1342
1343    #[test]
1344    fn test_mysql_generate_with_views() {
1345        use crate::diff::SchemaDiff;
1346
1347        let generator = MySqlGenerator;
1348        let mut diff = SchemaDiff::default();
1349        diff.create_views.push(ViewDiff {
1350            name: "ActiveUsers".to_string(),
1351            view_name: "active_users".to_string(),
1352            sql_query: "SELECT * FROM users WHERE active = 1".to_string(),
1353            is_materialized: false,
1354            refresh_interval: None,
1355            fields: vec![],
1356        });
1357
1358        let sql = generator.generate(&diff);
1359        assert!(!sql.is_empty());
1360        assert!(sql.up.contains("CREATE OR REPLACE VIEW"));
1361        assert!(sql.up.contains("`active_users`"));
1362        assert!(sql.down.contains("DROP VIEW"));
1363    }
1364
1365    #[test]
1366    fn test_mysql_create_table() {
1367        let generator = MySqlGenerator;
1368        let model = ModelDiff {
1369            name: "User".to_string(),
1370            table_name: "users".to_string(),
1371            fields: vec![FieldDiff {
1372                name: "id".to_string(),
1373                column_name: "id".to_string(),
1374                sql_type: "INTEGER".to_string(),
1375                nullable: false,
1376                default: None,
1377                is_primary_key: true,
1378                is_auto_increment: true,
1379                is_unique: false,
1380            }],
1381            primary_key: vec!["id".to_string()],
1382            indexes: Vec::new(),
1383            unique_constraints: Vec::new(),
1384        };
1385
1386        let sql = generator.create_table(&model);
1387        assert!(sql.contains("CREATE TABLE `users`"));
1388        assert!(sql.contains("AUTO_INCREMENT"));
1389        assert!(sql.contains("ENGINE=InnoDB"));
1390    }
1391
1392    // ==================== SQLite Generator Tests ====================
1393
1394    #[test]
1395    fn test_sqlite_create_view() {
1396        let generator = SqliteGenerator;
1397        let view = ViewDiff {
1398            name: "UserStats".to_string(),
1399            view_name: "user_stats".to_string(),
1400            sql_query: "SELECT id, COUNT(*) as post_count FROM users GROUP BY id".to_string(),
1401            is_materialized: false,
1402            refresh_interval: None,
1403            fields: vec![],
1404        };
1405
1406        let sql = generator.create_view(&view);
1407        assert!(sql.contains("CREATE VIEW IF NOT EXISTS"));
1408        assert!(sql.contains("\"user_stats\""));
1409        assert!(sql.contains("SELECT id"));
1410    }
1411
1412    #[test]
1413    fn test_sqlite_drop_view() {
1414        let generator = SqliteGenerator;
1415        let sql = generator.drop_view("user_stats");
1416        assert!(sql.contains("DROP VIEW IF EXISTS"));
1417        assert!(sql.contains("\"user_stats\""));
1418    }
1419
1420    #[test]
1421    fn test_sqlite_generate_with_views() {
1422        use crate::diff::SchemaDiff;
1423
1424        let generator = SqliteGenerator;
1425        let mut diff = SchemaDiff::default();
1426        diff.create_views.push(ViewDiff {
1427            name: "ActiveUsers".to_string(),
1428            view_name: "active_users".to_string(),
1429            sql_query: "SELECT * FROM users WHERE active = 1".to_string(),
1430            is_materialized: false,
1431            refresh_interval: None,
1432            fields: vec![],
1433        });
1434
1435        let sql = generator.generate(&diff);
1436        assert!(!sql.is_empty());
1437        assert!(sql.up.contains("CREATE VIEW IF NOT EXISTS"));
1438        assert!(sql.up.contains("\"active_users\""));
1439        assert!(sql.down.contains("DROP VIEW"));
1440    }
1441
1442    #[test]
1443    fn test_sqlite_create_table_with_autoincrement() {
1444        let generator = SqliteGenerator;
1445        let model = ModelDiff {
1446            name: "User".to_string(),
1447            table_name: "users".to_string(),
1448            fields: vec![FieldDiff {
1449                name: "id".to_string(),
1450                column_name: "id".to_string(),
1451                sql_type: "INTEGER".to_string(),
1452                nullable: false,
1453                default: None,
1454                is_primary_key: true,
1455                is_auto_increment: true,
1456                is_unique: false,
1457            }],
1458            primary_key: vec!["id".to_string()],
1459            indexes: Vec::new(),
1460            unique_constraints: Vec::new(),
1461        };
1462
1463        let sql = generator.create_table(&model);
1464        assert!(sql.contains("CREATE TABLE \"users\""));
1465        assert!(sql.contains("INTEGER PRIMARY KEY"));
1466    }
1467
1468    // ==================== MSSQL Generator Tests ====================
1469
1470    #[test]
1471    fn test_mssql_create_view() {
1472        let generator = MssqlGenerator;
1473        let view = ViewDiff {
1474            name: "UserStats".to_string(),
1475            view_name: "user_stats".to_string(),
1476            sql_query: "SELECT id, COUNT(*) as post_count FROM users GROUP BY id".to_string(),
1477            is_materialized: false,
1478            refresh_interval: None,
1479            fields: vec![],
1480        };
1481
1482        let sql = generator.create_view(&view);
1483        assert!(sql.contains("CREATE OR ALTER VIEW"));
1484        assert!(sql.contains("[user_stats]"));
1485        assert!(sql.contains("SELECT id"));
1486    }
1487
1488    #[test]
1489    fn test_mssql_create_indexed_view() {
1490        let generator = MssqlGenerator;
1491        let view = ViewDiff {
1492            name: "UserStats".to_string(),
1493            view_name: "user_stats".to_string(),
1494            sql_query: "SELECT id, COUNT(*) as post_count FROM users GROUP BY id".to_string(),
1495            is_materialized: true,
1496            refresh_interval: None,
1497            fields: vec![],
1498        };
1499
1500        let sql = generator.create_view(&view);
1501        assert!(sql.contains("WITH SCHEMABINDING"));
1502        assert!(sql.contains("[user_stats]"));
1503        // Should include comment about clustered index
1504        assert!(sql.contains("CLUSTERED INDEX"));
1505    }
1506
1507    #[test]
1508    fn test_mssql_drop_view() {
1509        let generator = MssqlGenerator;
1510        let sql = generator.drop_view("user_stats", false);
1511        assert!(sql.contains("DROP VIEW IF EXISTS"));
1512        assert!(sql.contains("[user_stats]"));
1513    }
1514
1515    #[test]
1516    fn test_mssql_generate_with_views() {
1517        use crate::diff::SchemaDiff;
1518
1519        let generator = MssqlGenerator;
1520        let mut diff = SchemaDiff::default();
1521        diff.create_views.push(ViewDiff {
1522            name: "ActiveUsers".to_string(),
1523            view_name: "active_users".to_string(),
1524            sql_query: "SELECT * FROM users WHERE active = 1".to_string(),
1525            is_materialized: false,
1526            refresh_interval: None,
1527            fields: vec![],
1528        });
1529
1530        let sql = generator.generate(&diff);
1531        assert!(!sql.is_empty());
1532        assert!(sql.up.contains("CREATE OR ALTER VIEW"));
1533        assert!(sql.up.contains("[active_users]"));
1534        assert!(sql.down.contains("DROP VIEW"));
1535    }
1536
1537    #[test]
1538    fn test_mssql_create_table() {
1539        let generator = MssqlGenerator;
1540        let model = ModelDiff {
1541            name: "User".to_string(),
1542            table_name: "users".to_string(),
1543            fields: vec![FieldDiff {
1544                name: "id".to_string(),
1545                column_name: "id".to_string(),
1546                sql_type: "INTEGER".to_string(),
1547                nullable: false,
1548                default: None,
1549                is_primary_key: true,
1550                is_auto_increment: true,
1551                is_unique: false,
1552            }],
1553            primary_key: vec!["id".to_string()],
1554            indexes: Vec::new(),
1555            unique_constraints: Vec::new(),
1556        };
1557
1558        let sql = generator.create_table(&model);
1559        assert!(sql.contains("CREATE TABLE [users]"));
1560        assert!(sql.contains("IDENTITY(1,1)"));
1561        assert!(sql.contains("[PK_users]"));
1562    }
1563}
prax_migrate/sql.rs

prax_migrate/
sql.rs
