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.fields.iter().any(|f| {
725            f.is_primary_key && f.sql_type == "INTEGER" && f.is_auto_increment
726        });
727
728        // Add primary key constraint only if not using INTEGER PRIMARY KEY
729        if !model.primary_key.is_empty() && !has_integer_pk {
730            let pk_cols: Vec<String> = model
731                .primary_key
732                .iter()
733                .map(|c| format!("\"{}\"", c))
734                .collect();
735            columns.push(format!("PRIMARY KEY ({})", pk_cols.join(", ")));
736        }
737
738        // Add unique constraints
739        for uc in &model.unique_constraints {
740            let cols: Vec<String> = uc.columns.iter().map(|c| format!("\"{}\"", c)).collect();
741            let constraint = if let Some(name) = &uc.name {
742                format!("CONSTRAINT \"{}\" UNIQUE ({})", name, cols.join(", "))
743            } else {
744                format!("UNIQUE ({})", cols.join(", "))
745            };
746            columns.push(constraint);
747        }
748
749        format!(
750            "CREATE TABLE \"{}\" (\n    {}\n);",
751            model.table_name,
752            columns.join(",\n    ")
753        )
754    }
755
756    /// Generate column definition for SQLite.
757    fn column_definition(&self, field: &FieldDiff) -> String {
758        let mut parts = vec![format!("\"{}\"", field.column_name)];
759
760        // SQLite type mapping
761        let sql_type = match field.sql_type.as_str() {
762            "INTEGER" if field.is_primary_key && field.is_auto_increment => {
763                // INTEGER PRIMARY KEY is auto-increment in SQLite
764                parts.push("INTEGER PRIMARY KEY".to_string());
765                return parts.join(" ");
766            }
767            "BIGINT" => "INTEGER".to_string(),
768            "DOUBLE PRECISION" => "REAL".to_string(),
769            "TIMESTAMP WITH TIME ZONE" | "DATETIME" => "TEXT".to_string(), // SQLite stores dates as TEXT
770            "BOOLEAN" => "INTEGER".to_string(),
771            "BYTEA" | "BLOB" => "BLOB".to_string(),
772            "JSONB" | "JSON" => "TEXT".to_string(), // SQLite stores JSON as TEXT
773            other => other.to_string(),
774        };
775        parts.push(sql_type);
776
777        if !field.nullable && !field.is_primary_key {
778            parts.push("NOT NULL".to_string());
779        }
780
781        if field.is_unique && !field.is_primary_key {
782            parts.push("UNIQUE".to_string());
783        }
784
785        if let Some(default) = &field.default {
786            parts.push(format!("DEFAULT {}", default));
787        }
788
789        parts.join(" ")
790    }
791
792    /// Generate DROP TABLE statement.
793    fn drop_table(&self, name: &str) -> String {
794        format!("DROP TABLE IF EXISTS \"{}\";", name)
795    }
796
797    /// Generate CREATE INDEX statement.
798    fn create_index(&self, index: &IndexDiff) -> String {
799        let unique = if index.unique { "UNIQUE " } else { "" };
800
801        let cols: Vec<String> = index.columns.iter().map(|c| format!("\"{}\"", c)).collect();
802        format!(
803            "CREATE {}INDEX \"{}\" ON \"{}\"({});",
804            unique, index.name, index.table_name, cols.join(", ")
805        )
806    }
807
808    /// Generate DROP INDEX statement.
809    fn drop_index(&self, name: &str) -> String {
810        format!("DROP INDEX IF EXISTS \"{}\";", name)
811    }
812
813    /// Generate CREATE VIEW statement.
814    fn create_view(&self, view: &ViewDiff) -> String {
815        // SQLite doesn't support materialized views
816        // but we can create a regular view
817        format!(
818            "CREATE VIEW IF NOT EXISTS \"{}\" AS\n{};",
819            view.view_name, view.sql_query
820        )
821    }
822
823    /// Generate DROP VIEW statement.
824    fn drop_view(&self, name: &str) -> String {
825        format!("DROP VIEW IF EXISTS \"{}\";", name)
826    }
827}
828
829/// SQL generator for Microsoft SQL Server.
830pub struct MssqlGenerator;
831
832impl MssqlGenerator {
833    /// Generate SQL for a schema diff.
834    pub fn generate(&self, diff: &SchemaDiff) -> MigrationSql {
835        let mut up = Vec::new();
836        let mut down = Vec::new();
837
838        // Create models
839        for model in &diff.create_models {
840            up.push(self.create_table(model));
841            down.push(self.drop_table(&model.table_name));
842        }
843
844        // Drop models
845        for name in &diff.drop_models {
846            up.push(self.drop_table(name));
847        }
848
849        // Alter models
850        for alter in &diff.alter_models {
851            up.extend(self.alter_table(alter));
852        }
853
854        // Create indexes
855        for index in &diff.create_indexes {
856            up.push(self.create_index(index));
857            down.push(self.drop_index(&index.name, &index.table_name));
858        }
859
860        // Drop indexes
861        for index in &diff.drop_indexes {
862            up.push(self.drop_index(&index.name, &index.table_name));
863        }
864
865        // Create views (after tables they depend on)
866        for view in &diff.create_views {
867            up.push(self.create_view(view));
868            down.push(self.drop_view(&view.view_name, view.is_materialized));
869        }
870
871        // Drop views
872        for name in &diff.drop_views {
873            up.push(self.drop_view(name, false));
874        }
875
876        // Alter views (drop and recreate)
877        for view in &diff.alter_views {
878            up.push(self.drop_view(&view.view_name, view.is_materialized));
879            up.push(self.create_view(view));
880        }
881
882        MigrationSql {
883            up: up.join("\n\nGO\n\n"),
884            down: down.join("\n\nGO\n\n"),
885        }
886    }
887
888    /// Generate CREATE TABLE statement.
889    fn create_table(&self, model: &ModelDiff) -> String {
890        let mut columns = Vec::new();
891
892        for field in &model.fields {
893            columns.push(self.column_definition(field));
894        }
895
896        // Add primary key constraint
897        if !model.primary_key.is_empty() {
898            let pk_cols: Vec<String> = model
899                .primary_key
900                .iter()
901                .map(|c| format!("[{}]", c))
902                .collect();
903            columns.push(format!("CONSTRAINT [PK_{}] PRIMARY KEY ({})", model.table_name, pk_cols.join(", ")));
904        }
905
906        // Add unique constraints
907        for uc in &model.unique_constraints {
908            let cols: Vec<String> = uc.columns.iter().map(|c| format!("[{}]", c)).collect();
909            let name = uc.name.clone().unwrap_or_else(|| {
910                format!("UQ_{}_{}", model.table_name, uc.columns.join("_"))
911            });
912            columns.push(format!("CONSTRAINT [{}] UNIQUE ({})", name, cols.join(", ")));
913        }
914
915        format!(
916            "CREATE TABLE [{}] (\n    {}\n);",
917            model.table_name,
918            columns.join(",\n    ")
919        )
920    }
921
922    /// Generate column definition for MSSQL.
923    fn column_definition(&self, field: &FieldDiff) -> String {
924        let mut parts = vec![format!("[{}]", field.column_name)];
925
926        // MSSQL type mapping
927        let sql_type = match field.sql_type.as_str() {
928            "INTEGER" => "INT".to_string(),
929            "BIGINT" => "BIGINT".to_string(),
930            "TEXT" => "NVARCHAR(MAX)".to_string(),
931            "DOUBLE PRECISION" => "FLOAT".to_string(),
932            "TIMESTAMP WITH TIME ZONE" => "DATETIMEOFFSET".to_string(),
933            "BOOLEAN" => "BIT".to_string(),
934            "BYTEA" => "VARBINARY(MAX)".to_string(),
935            "JSONB" | "JSON" => "NVARCHAR(MAX)".to_string(), // MSSQL 2016+ has JSON support
936            "UUID" => "UNIQUEIDENTIFIER".to_string(),
937            "DECIMAL" => "DECIMAL(18,2)".to_string(),
938            other => other.to_string(),
939        };
940        parts.push(sql_type);
941
942        if field.is_auto_increment {
943            parts.push("IDENTITY(1,1)".to_string());
944        }
945
946        if !field.nullable && !field.is_primary_key {
947            parts.push("NOT NULL".to_string());
948        }
949
950        if field.is_unique && !field.is_primary_key {
951            // Unique constraint will be added at table level in MSSQL
952        }
953
954        if let Some(default) = &field.default {
955            parts.push(format!("DEFAULT {}", default));
956        }
957
958        parts.join(" ")
959    }
960
961    /// Generate DROP TABLE statement.
962    fn drop_table(&self, name: &str) -> String {
963        format!("DROP TABLE IF EXISTS [{}];", name)
964    }
965
966    /// Generate ALTER TABLE statements.
967    fn alter_table(&self, alter: &ModelAlterDiff) -> Vec<String> {
968        let mut stmts = Vec::new();
969
970        // Add columns
971        for field in &alter.add_fields {
972            stmts.push(format!(
973                "ALTER TABLE [{}] ADD {};",
974                alter.table_name,
975                self.column_definition(field)
976            ));
977        }
978
979        // Drop columns
980        for name in &alter.drop_fields {
981            stmts.push(format!(
982                "ALTER TABLE [{}] DROP COLUMN [{}];",
983                alter.table_name, name
984            ));
985        }
986
987        // Alter columns
988        for field in &alter.alter_fields {
989            stmts.extend(self.alter_column(&alter.table_name, field));
990        }
991
992        stmts
993    }
994
995    /// Generate ALTER COLUMN statements.
996    fn alter_column(&self, table: &str, field: &FieldAlterDiff) -> Vec<String> {
997        let mut stmts = Vec::new();
998
999        if let Some(new_type) = &field.new_type {
1000            stmts.push(format!(
1001                "ALTER TABLE [{}] ALTER COLUMN [{}] {};",
1002                table, field.column_name, new_type
1003            ));
1004        }
1005
1006        stmts
1007    }
1008
1009    /// Generate CREATE INDEX statement.
1010    fn create_index(&self, index: &IndexDiff) -> String {
1011        let unique = if index.unique { "UNIQUE " } else { "" };
1012
1013        let cols: Vec<String> = index.columns.iter().map(|c| format!("[{}]", c)).collect();
1014        format!(
1015            "CREATE {}INDEX [{}] ON [{}]({});",
1016            unique, index.name, index.table_name, cols.join(", ")
1017        )
1018    }
1019
1020    /// Generate DROP INDEX statement.
1021    fn drop_index(&self, name: &str, table: &str) -> String {
1022        format!("DROP INDEX [{}] ON [{}];", name, table)
1023    }
1024
1025    /// Generate CREATE VIEW statement.
1026    ///
1027    /// MSSQL supports indexed views (similar to materialized views) with:
1028    /// - SCHEMABINDING option
1029    /// - Unique clustered index on the view
1030    fn create_view(&self, view: &ViewDiff) -> String {
1031        if view.is_materialized {
1032            // Create an indexed view (MSSQL's equivalent of materialized views)
1033            // Note: This requires additional setup like creating a clustered index
1034            format!(
1035                "CREATE VIEW [{}] WITH SCHEMABINDING AS\n{};\n\n-- Create unique clustered index for indexed view\n-- CREATE UNIQUE CLUSTERED INDEX [IX_{}_Clustered] ON [{}] ([id]);",
1036                view.view_name, view.sql_query, view.view_name, view.view_name
1037            )
1038        } else {
1039            format!(
1040                "CREATE OR ALTER VIEW [{}] AS\n{};",
1041                view.view_name, view.sql_query
1042            )
1043        }
1044    }
1045
1046    /// Generate DROP VIEW statement.
1047    fn drop_view(&self, name: &str, _is_materialized: bool) -> String {
1048        // MSSQL uses the same syntax for regular and indexed views
1049        format!("DROP VIEW IF EXISTS [{}];", name)
1050    }
1051
1052    /// Generate sp_refreshview for refreshing view metadata.
1053    #[allow(dead_code)]
1054    fn refresh_view(&self, name: &str) -> String {
1055        format!("EXEC sp_refreshview N'{}';", name)
1056    }
1057}
1058
1059#[cfg(test)]
1060mod tests {
1061    use super::*;
1062
1063    #[test]
1064    fn test_create_enum() {
1065        let generator = PostgresSqlGenerator;
1066        let enum_diff = EnumDiff {
1067            name: "Status".to_string(),
1068            values: vec!["PENDING".to_string(), "ACTIVE".to_string()],
1069        };
1070
1071        let sql = generator.create_enum(&enum_diff);
1072        assert!(sql.contains("CREATE TYPE"));
1073        assert!(sql.contains("Status"));
1074        assert!(sql.contains("PENDING"));
1075        assert!(sql.contains("ACTIVE"));
1076    }
1077
1078    #[test]
1079    fn test_create_table() {
1080        let generator = PostgresSqlGenerator;
1081        let model = ModelDiff {
1082            name: "User".to_string(),
1083            table_name: "users".to_string(),
1084            fields: vec![
1085                FieldDiff {
1086                    name: "id".to_string(),
1087                    column_name: "id".to_string(),
1088                    sql_type: "INTEGER".to_string(),
1089                    nullable: false,
1090                    default: None,
1091                    is_primary_key: true,
1092                    is_auto_increment: true,
1093                    is_unique: false,
1094                },
1095                FieldDiff {
1096                    name: "email".to_string(),
1097                    column_name: "email".to_string(),
1098                    sql_type: "TEXT".to_string(),
1099                    nullable: false,
1100                    default: None,
1101                    is_primary_key: false,
1102                    is_auto_increment: false,
1103                    is_unique: true,
1104                },
1105            ],
1106            primary_key: vec!["id".to_string()],
1107            indexes: Vec::new(),
1108            unique_constraints: Vec::new(),
1109        };
1110
1111        let sql = generator.create_table(&model);
1112        assert!(sql.contains("CREATE TABLE"));
1113        assert!(sql.contains("users"));
1114        assert!(sql.contains("SERIAL"));
1115        assert!(sql.contains("email"));
1116        assert!(sql.contains("UNIQUE"));
1117        assert!(sql.contains("PRIMARY KEY"));
1118    }
1119
1120    #[test]
1121    fn test_create_index() {
1122        let generator = PostgresSqlGenerator;
1123        let index = IndexDiff::new("idx_users_email", "users", vec!["email".to_string()]).unique();
1124
1125        let sql = generator.create_index(&index);
1126        assert!(sql.contains("CREATE UNIQUE INDEX"));
1127        assert!(sql.contains("idx_users_email"));
1128        assert!(sql.contains("users"));
1129    }
1130
1131    #[test]
1132    fn test_create_hnsw_index() {
1133        use prax_schema::ast::{IndexType, VectorOps};
1134
1135        let generator = PostgresSqlGenerator;
1136        let index = IndexDiff::new("idx_embedding", "documents", vec!["embedding".to_string()])
1137            .with_type(IndexType::Hnsw)
1138            .with_vector_ops(VectorOps::Cosine)
1139            .with_hnsw_m(16)
1140            .with_hnsw_ef_construction(64);
1141
1142        let sql = generator.create_index(&index);
1143        assert!(sql.contains("CREATE INDEX"));
1144        assert!(sql.contains("USING hnsw"));
1145        assert!(sql.contains("vector_cosine_ops"));
1146        assert!(sql.contains("m = 16"));
1147        assert!(sql.contains("ef_construction = 64"));
1148    }
1149
1150    #[test]
1151    fn test_create_ivfflat_index() {
1152        use prax_schema::ast::{IndexType, VectorOps};
1153
1154        let generator = PostgresSqlGenerator;
1155        let index = IndexDiff::new(
1156            "idx_embedding_l2",
1157            "documents",
1158            vec!["embedding".to_string()],
1159        )
1160        .with_type(IndexType::IvfFlat)
1161        .with_vector_ops(VectorOps::L2)
1162        .with_ivfflat_lists(100);
1163
1164        let sql = generator.create_index(&index);
1165        assert!(sql.contains("CREATE INDEX"));
1166        assert!(sql.contains("USING ivfflat"));
1167        assert!(sql.contains("vector_l2_ops"));
1168        assert!(sql.contains("lists = 100"));
1169    }
1170
1171    #[test]
1172    fn test_create_gin_index() {
1173        use prax_schema::ast::IndexType;
1174
1175        let generator = PostgresSqlGenerator;
1176        let index =
1177            IndexDiff::new("idx_tags", "posts", vec!["tags".to_string()]).with_type(IndexType::Gin);
1178
1179        let sql = generator.create_index(&index);
1180        assert!(sql.contains("CREATE INDEX"));
1181        assert!(sql.contains("USING GIN"));
1182        assert!(sql.contains("idx_tags"));
1183    }
1184
1185    #[test]
1186    fn test_alter_table_add_column() {
1187        let generator = PostgresSqlGenerator;
1188        let alter = ModelAlterDiff {
1189            name: "User".to_string(),
1190            table_name: "users".to_string(),
1191            add_fields: vec![FieldDiff {
1192                name: "age".to_string(),
1193                column_name: "age".to_string(),
1194                sql_type: "INTEGER".to_string(),
1195                nullable: true,
1196                default: None,
1197                is_primary_key: false,
1198                is_auto_increment: false,
1199                is_unique: false,
1200            }],
1201            drop_fields: Vec::new(),
1202            alter_fields: Vec::new(),
1203            add_indexes: Vec::new(),
1204            drop_indexes: Vec::new(),
1205        };
1206
1207        let stmts = generator.alter_table(&alter);
1208        assert_eq!(stmts.len(), 1);
1209        assert!(stmts[0].contains("ADD COLUMN"));
1210        assert!(stmts[0].contains("age"));
1211    }
1212
1213    #[test]
1214    fn test_create_view() {
1215        let generator = PostgresSqlGenerator;
1216        let view = ViewDiff {
1217            name: "UserStats".to_string(),
1218            view_name: "user_stats".to_string(),
1219            sql_query: "SELECT id, COUNT(*) as post_count FROM users GROUP BY id".to_string(),
1220            is_materialized: false,
1221            refresh_interval: None,
1222            fields: vec![],
1223        };
1224
1225        let sql = generator.create_view(&view);
1226        assert!(sql.contains("CREATE OR REPLACE VIEW"));
1227        assert!(sql.contains("user_stats"));
1228        assert!(sql.contains("SELECT id"));
1229        assert!(sql.contains("post_count"));
1230    }
1231
1232    #[test]
1233    fn test_create_materialized_view() {
1234        let generator = PostgresSqlGenerator;
1235        let view = ViewDiff {
1236            name: "UserStats".to_string(),
1237            view_name: "user_stats".to_string(),
1238            sql_query: "SELECT id, COUNT(*) as post_count FROM users GROUP BY id".to_string(),
1239            is_materialized: true,
1240            refresh_interval: Some("1h".to_string()),
1241            fields: vec![],
1242        };
1243
1244        let sql = generator.create_view(&view);
1245        assert!(sql.contains("CREATE MATERIALIZED VIEW"));
1246        assert!(sql.contains("user_stats"));
1247        assert!(!sql.contains("OR REPLACE")); // Materialized views don't support OR REPLACE
1248    }
1249
1250    #[test]
1251    fn test_drop_view() {
1252        let generator = PostgresSqlGenerator;
1253
1254        let sql = generator.drop_view("user_stats", false);
1255        assert!(sql.contains("DROP VIEW"));
1256        assert!(sql.contains("user_stats"));
1257        assert!(sql.contains("CASCADE"));
1258
1259        let sql_mat = generator.drop_view("user_stats", true);
1260        assert!(sql_mat.contains("DROP MATERIALIZED VIEW"));
1261        assert!(sql_mat.contains("user_stats"));
1262    }
1263
1264    #[test]
1265    fn test_refresh_materialized_view() {
1266        let generator = PostgresSqlGenerator;
1267
1268        let sql = generator.refresh_materialized_view("user_stats", false);
1269        assert!(sql.contains("REFRESH MATERIALIZED VIEW"));
1270        assert!(sql.contains("user_stats"));
1271        assert!(!sql.contains("CONCURRENTLY"));
1272
1273        let sql_concurrent = generator.refresh_materialized_view("user_stats", true);
1274        assert!(sql_concurrent.contains("CONCURRENTLY"));
1275    }
1276
1277    #[test]
1278    fn test_generate_with_views() {
1279        use crate::diff::SchemaDiff;
1280
1281        let generator = PostgresSqlGenerator;
1282        let mut diff = SchemaDiff::default();
1283        diff.create_views.push(ViewDiff {
1284            name: "ActiveUsers".to_string(),
1285            view_name: "active_users".to_string(),
1286            sql_query: "SELECT * FROM users WHERE active = true".to_string(),
1287            is_materialized: false,
1288            refresh_interval: None,
1289            fields: vec![],
1290        });
1291
1292        let sql = generator.generate(&diff);
1293        assert!(!sql.is_empty());
1294        assert!(sql.up.contains("CREATE OR REPLACE VIEW"));
1295        assert!(sql.up.contains("active_users"));
1296        assert!(sql.down.contains("DROP VIEW"));
1297    }
1298
1299    // ==================== MySQL Generator Tests ====================
1300
1301    #[test]
1302    fn test_mysql_create_view() {
1303        let generator = MySqlGenerator;
1304        let view = ViewDiff {
1305            name: "UserStats".to_string(),
1306            view_name: "user_stats".to_string(),
1307            sql_query: "SELECT id, COUNT(*) as post_count FROM users GROUP BY id".to_string(),
1308            is_materialized: false,
1309            refresh_interval: None,
1310            fields: vec![],
1311        };
1312
1313        let sql = generator.create_view(&view);
1314        assert!(sql.contains("CREATE OR REPLACE VIEW"));
1315        assert!(sql.contains("`user_stats`"));
1316        assert!(sql.contains("SELECT id"));
1317    }
1318
1319    #[test]
1320    fn test_mysql_drop_view() {
1321        let generator = MySqlGenerator;
1322        let sql = generator.drop_view("user_stats");
1323        assert!(sql.contains("DROP VIEW IF EXISTS"));
1324        assert!(sql.contains("`user_stats`"));
1325    }
1326
1327    #[test]
1328    fn test_mysql_generate_with_views() {
1329        use crate::diff::SchemaDiff;
1330
1331        let generator = MySqlGenerator;
1332        let mut diff = SchemaDiff::default();
1333        diff.create_views.push(ViewDiff {
1334            name: "ActiveUsers".to_string(),
1335            view_name: "active_users".to_string(),
1336            sql_query: "SELECT * FROM users WHERE active = 1".to_string(),
1337            is_materialized: false,
1338            refresh_interval: None,
1339            fields: vec![],
1340        });
1341
1342        let sql = generator.generate(&diff);
1343        assert!(!sql.is_empty());
1344        assert!(sql.up.contains("CREATE OR REPLACE VIEW"));
1345        assert!(sql.up.contains("`active_users`"));
1346        assert!(sql.down.contains("DROP VIEW"));
1347    }
1348
1349    #[test]
1350    fn test_mysql_create_table() {
1351        let generator = MySqlGenerator;
1352        let model = ModelDiff {
1353            name: "User".to_string(),
1354            table_name: "users".to_string(),
1355            fields: vec![
1356                FieldDiff {
1357                    name: "id".to_string(),
1358                    column_name: "id".to_string(),
1359                    sql_type: "INTEGER".to_string(),
1360                    nullable: false,
1361                    default: None,
1362                    is_primary_key: true,
1363                    is_auto_increment: true,
1364                    is_unique: false,
1365                },
1366            ],
1367            primary_key: vec!["id".to_string()],
1368            indexes: Vec::new(),
1369            unique_constraints: Vec::new(),
1370        };
1371
1372        let sql = generator.create_table(&model);
1373        assert!(sql.contains("CREATE TABLE `users`"));
1374        assert!(sql.contains("AUTO_INCREMENT"));
1375        assert!(sql.contains("ENGINE=InnoDB"));
1376    }
1377
1378    // ==================== SQLite Generator Tests ====================
1379
1380    #[test]
1381    fn test_sqlite_create_view() {
1382        let generator = SqliteGenerator;
1383        let view = ViewDiff {
1384            name: "UserStats".to_string(),
1385            view_name: "user_stats".to_string(),
1386            sql_query: "SELECT id, COUNT(*) as post_count FROM users GROUP BY id".to_string(),
1387            is_materialized: false,
1388            refresh_interval: None,
1389            fields: vec![],
1390        };
1391
1392        let sql = generator.create_view(&view);
1393        assert!(sql.contains("CREATE VIEW IF NOT EXISTS"));
1394        assert!(sql.contains("\"user_stats\""));
1395        assert!(sql.contains("SELECT id"));
1396    }
1397
1398    #[test]
1399    fn test_sqlite_drop_view() {
1400        let generator = SqliteGenerator;
1401        let sql = generator.drop_view("user_stats");
1402        assert!(sql.contains("DROP VIEW IF EXISTS"));
1403        assert!(sql.contains("\"user_stats\""));
1404    }
1405
1406    #[test]
1407    fn test_sqlite_generate_with_views() {
1408        use crate::diff::SchemaDiff;
1409
1410        let generator = SqliteGenerator;
1411        let mut diff = SchemaDiff::default();
1412        diff.create_views.push(ViewDiff {
1413            name: "ActiveUsers".to_string(),
1414            view_name: "active_users".to_string(),
1415            sql_query: "SELECT * FROM users WHERE active = 1".to_string(),
1416            is_materialized: false,
1417            refresh_interval: None,
1418            fields: vec![],
1419        });
1420
1421        let sql = generator.generate(&diff);
1422        assert!(!sql.is_empty());
1423        assert!(sql.up.contains("CREATE VIEW IF NOT EXISTS"));
1424        assert!(sql.up.contains("\"active_users\""));
1425        assert!(sql.down.contains("DROP VIEW"));
1426    }
1427
1428    #[test]
1429    fn test_sqlite_create_table_with_autoincrement() {
1430        let generator = SqliteGenerator;
1431        let model = ModelDiff {
1432            name: "User".to_string(),
1433            table_name: "users".to_string(),
1434            fields: vec![
1435                FieldDiff {
1436                    name: "id".to_string(),
1437                    column_name: "id".to_string(),
1438                    sql_type: "INTEGER".to_string(),
1439                    nullable: false,
1440                    default: None,
1441                    is_primary_key: true,
1442                    is_auto_increment: true,
1443                    is_unique: false,
1444                },
1445            ],
1446            primary_key: vec!["id".to_string()],
1447            indexes: Vec::new(),
1448            unique_constraints: Vec::new(),
1449        };
1450
1451        let sql = generator.create_table(&model);
1452        assert!(sql.contains("CREATE TABLE \"users\""));
1453        assert!(sql.contains("INTEGER PRIMARY KEY"));
1454    }
1455
1456    // ==================== MSSQL Generator Tests ====================
1457
1458    #[test]
1459    fn test_mssql_create_view() {
1460        let generator = MssqlGenerator;
1461        let view = ViewDiff {
1462            name: "UserStats".to_string(),
1463            view_name: "user_stats".to_string(),
1464            sql_query: "SELECT id, COUNT(*) as post_count FROM users GROUP BY id".to_string(),
1465            is_materialized: false,
1466            refresh_interval: None,
1467            fields: vec![],
1468        };
1469
1470        let sql = generator.create_view(&view);
1471        assert!(sql.contains("CREATE OR ALTER VIEW"));
1472        assert!(sql.contains("[user_stats]"));
1473        assert!(sql.contains("SELECT id"));
1474    }
1475
1476    #[test]
1477    fn test_mssql_create_indexed_view() {
1478        let generator = MssqlGenerator;
1479        let view = ViewDiff {
1480            name: "UserStats".to_string(),
1481            view_name: "user_stats".to_string(),
1482            sql_query: "SELECT id, COUNT(*) as post_count FROM users GROUP BY id".to_string(),
1483            is_materialized: true,
1484            refresh_interval: None,
1485            fields: vec![],
1486        };
1487
1488        let sql = generator.create_view(&view);
1489        assert!(sql.contains("WITH SCHEMABINDING"));
1490        assert!(sql.contains("[user_stats]"));
1491        // Should include comment about clustered index
1492        assert!(sql.contains("CLUSTERED INDEX"));
1493    }
1494
1495    #[test]
1496    fn test_mssql_drop_view() {
1497        let generator = MssqlGenerator;
1498        let sql = generator.drop_view("user_stats", false);
1499        assert!(sql.contains("DROP VIEW IF EXISTS"));
1500        assert!(sql.contains("[user_stats]"));
1501    }
1502
1503    #[test]
1504    fn test_mssql_generate_with_views() {
1505        use crate::diff::SchemaDiff;
1506
1507        let generator = MssqlGenerator;
1508        let mut diff = SchemaDiff::default();
1509        diff.create_views.push(ViewDiff {
1510            name: "ActiveUsers".to_string(),
1511            view_name: "active_users".to_string(),
1512            sql_query: "SELECT * FROM users WHERE active = 1".to_string(),
1513            is_materialized: false,
1514            refresh_interval: None,
1515            fields: vec![],
1516        });
1517
1518        let sql = generator.generate(&diff);
1519        assert!(!sql.is_empty());
1520        assert!(sql.up.contains("CREATE OR ALTER VIEW"));
1521        assert!(sql.up.contains("[active_users]"));
1522        assert!(sql.down.contains("DROP VIEW"));
1523    }
1524
1525    #[test]
1526    fn test_mssql_create_table() {
1527        let generator = MssqlGenerator;
1528        let model = ModelDiff {
1529            name: "User".to_string(),
1530            table_name: "users".to_string(),
1531            fields: vec![
1532                FieldDiff {
1533                    name: "id".to_string(),
1534                    column_name: "id".to_string(),
1535                    sql_type: "INTEGER".to_string(),
1536                    nullable: false,
1537                    default: None,
1538                    is_primary_key: true,
1539                    is_auto_increment: true,
1540                    is_unique: false,
1541                },
1542            ],
1543            primary_key: vec!["id".to_string()],
1544            indexes: Vec::new(),
1545            unique_constraints: Vec::new(),
1546        };
1547
1548        let sql = generator.create_table(&model);
1549        assert!(sql.contains("CREATE TABLE [users]"));
1550        assert!(sql.contains("IDENTITY(1,1)"));
1551        assert!(sql.contains("[PK_users]"));
1552    }
1553}
prax_migrate/sql.rs

prax_migrate/
sql.rs
