sql_splitter/differ/output/
sql.rs

1//! SQL migration output formatter for diff results.
2//!
3//! Generates DDL statements for schema changes.
4
5use crate::differ::DiffResult;
6use crate::parser::SqlDialect;
7
8/// Format diff result as SQL migration script
9pub fn format_sql(result: &DiffResult, dialect: SqlDialect) -> String {
10    let mut output = String::new();
11
12    output.push_str("-- SQL Migration Script\n");
13    output.push_str(&format!(
14        "-- Generated by sql-splitter diff (dialect: {})\n",
15        dialect
16    ));
17    output.push_str("--\n\n");
18
19    let Some(ref schema) = result.schema else {
20        output.push_str("-- No schema changes detected\n");
21        return output;
22    };
23
24    if !schema.has_changes() {
25        output.push_str("-- No schema changes detected\n");
26        return output;
27    }
28
29    // Quote identifier based on dialect
30    let quote = |name: &str| -> String {
31        match dialect {
32            SqlDialect::MySql => format!("`{}`", name),
33            SqlDialect::Postgres | SqlDialect::Sqlite => format!("\"{}\"", name),
34            SqlDialect::Mssql => format!("[{}]", name),
35        }
36    };
37
38    // New tables - use CREATE TABLE statement if available
39    for table in &schema.tables_added {
40        output.push_str(&format!("-- New table: {}\n", table.name));
41        if let Some(ref stmt) = table.create_statement {
42            output.push_str(stmt.trim());
43            if !stmt.trim().ends_with(';') {
44                output.push(';');
45            }
46            output.push_str("\n\n");
47        } else {
48            // Generate CREATE TABLE from column info
49            output.push_str(&format!("CREATE TABLE {} (\n", quote(&table.name)));
50
51            let col_defs: Vec<String> = table
52                .columns
53                .iter()
54                .map(|col| {
55                    let nullable = if col.is_nullable { "" } else { " NOT NULL" };
56                    format!("  {} {}{}", quote(&col.name), col.col_type, nullable)
57                })
58                .collect();
59
60            output.push_str(&col_defs.join(",\n"));
61
62            if !table.primary_key.is_empty() {
63                let pk_cols: Vec<String> = table.primary_key.iter().map(|c| quote(c)).collect();
64                output.push_str(&format!(",\n  PRIMARY KEY ({})", pk_cols.join(", ")));
65            }
66
67            output.push_str("\n);\n\n");
68        }
69    }
70
71    // Removed tables
72    for table_name in &schema.tables_removed {
73        output.push_str(&format!("-- Removed table: {}\n", table_name));
74        output.push_str(&format!("DROP TABLE IF EXISTS {};\n\n", quote(table_name)));
75    }
76
77    // Modified tables
78    for modification in &schema.tables_modified {
79        let has_changes = !modification.columns_added.is_empty()
80            || !modification.columns_removed.is_empty()
81            || !modification.columns_modified.is_empty()
82            || !modification.fks_added.is_empty()
83            || !modification.fks_removed.is_empty()
84            || !modification.indexes_added.is_empty()
85            || !modification.indexes_removed.is_empty();
86
87        if !has_changes && !modification.pk_changed {
88            continue;
89        }
90
91        output.push_str(&format!("-- Modified table: {}\n", modification.table_name));
92
93        // Add columns
94        for col in &modification.columns_added {
95            let nullable = if col.is_nullable { "" } else { " NOT NULL" };
96            output.push_str(&format!(
97                "ALTER TABLE {} ADD COLUMN {} {}{};\n",
98                quote(&modification.table_name),
99                quote(&col.name),
100                col.col_type,
101                nullable
102            ));
103        }
104
105        // Drop columns
106        for col in &modification.columns_removed {
107            output.push_str(&format!(
108                "ALTER TABLE {} DROP COLUMN {};\n",
109                quote(&modification.table_name),
110                quote(&col.name)
111            ));
112        }
113
114        // Modify columns
115        for change in &modification.columns_modified {
116            let new_type = change.new_type.as_deref().unwrap_or("-- unknown type");
117            let nullable = match change.new_nullable {
118                Some(true) => "",
119                Some(false) => " NOT NULL",
120                None => "",
121            };
122
123            match dialect {
124                SqlDialect::MySql => {
125                    output.push_str(&format!(
126                        "ALTER TABLE {} MODIFY COLUMN {} {}{};\n",
127                        quote(&modification.table_name),
128                        quote(&change.name),
129                        new_type,
130                        nullable
131                    ));
132                }
133                SqlDialect::Postgres => {
134                    // PostgreSQL uses separate commands for type and nullability
135                    if change.new_type.is_some() {
136                        output.push_str(&format!(
137                            "ALTER TABLE {} ALTER COLUMN {} TYPE {};\n",
138                            quote(&modification.table_name),
139                            quote(&change.name),
140                            new_type
141                        ));
142                    }
143                    if let Some(is_nullable) = change.new_nullable {
144                        let constraint = if is_nullable {
145                            "DROP NOT NULL"
146                        } else {
147                            "SET NOT NULL"
148                        };
149                        output.push_str(&format!(
150                            "ALTER TABLE {} ALTER COLUMN {} {};\n",
151                            quote(&modification.table_name),
152                            quote(&change.name),
153                            constraint
154                        ));
155                    }
156                }
157                SqlDialect::Sqlite => {
158                    output.push_str(&format!(
159                        "-- SQLite does not support ALTER COLUMN; manual migration required for {}.{}\n",
160                        modification.table_name, change.name
161                    ));
162                }
163                SqlDialect::Mssql => {
164                    output.push_str(&format!(
165                        "ALTER TABLE {} ALTER COLUMN {} {}{};\n",
166                        quote(&modification.table_name),
167                        quote(&change.name),
168                        new_type,
169                        nullable
170                    ));
171                }
172            }
173        }
174
175        // Add foreign keys
176        for fk in &modification.fks_added {
177            let fk_cols: Vec<String> = fk.columns.iter().map(|c| quote(c)).collect();
178            let ref_cols: Vec<String> = fk.referenced_columns.iter().map(|c| quote(c)).collect();
179
180            let constraint_name = fk
181                .name
182                .as_ref()
183                .map(|n| format!("CONSTRAINT {} ", quote(n)))
184                .unwrap_or_default();
185
186            output.push_str(&format!(
187                "ALTER TABLE {} ADD {}FOREIGN KEY ({}) REFERENCES {}({});\n",
188                quote(&modification.table_name),
189                constraint_name,
190                fk_cols.join(", "),
191                quote(&fk.referenced_table),
192                ref_cols.join(", ")
193            ));
194        }
195
196        // Drop foreign keys
197        for fk in &modification.fks_removed {
198            if let Some(ref name) = fk.name {
199                match dialect {
200                    SqlDialect::MySql => {
201                        output.push_str(&format!(
202                            "ALTER TABLE {} DROP FOREIGN KEY {};\n",
203                            quote(&modification.table_name),
204                            quote(name)
205                        ));
206                    }
207                    SqlDialect::Postgres | SqlDialect::Mssql => {
208                        output.push_str(&format!(
209                            "ALTER TABLE {} DROP CONSTRAINT {};\n",
210                            quote(&modification.table_name),
211                            quote(name)
212                        ));
213                    }
214                    SqlDialect::Sqlite => {
215                        output.push_str(&format!(
216                            "-- SQLite does not support DROP CONSTRAINT; manual migration required for FK {}\n",
217                            name
218                        ));
219                    }
220                }
221            } else {
222                output.push_str(&format!(
223                    "-- Cannot drop unnamed FK ({}) -> {}.({}) without constraint name\n",
224                    fk.columns.join(", "),
225                    fk.referenced_table,
226                    fk.referenced_columns.join(", ")
227                ));
228            }
229        }
230
231        // Add indexes
232        for idx in &modification.indexes_added {
233            let unique = if idx.is_unique { "UNIQUE " } else { "" };
234            let idx_cols: Vec<String> = idx.columns.iter().map(|c| quote(c)).collect();
235
236            match dialect {
237                SqlDialect::Postgres => {
238                    let using = idx
239                        .index_type
240                        .as_ref()
241                        .map(|t| format!(" USING {}", t.to_lowercase()))
242                        .unwrap_or_default();
243                    output.push_str(&format!(
244                        "CREATE {}INDEX {} ON {}{}({});\n",
245                        unique,
246                        quote(&idx.name),
247                        quote(&modification.table_name),
248                        using,
249                        idx_cols.join(", ")
250                    ));
251                }
252                _ => {
253                    output.push_str(&format!(
254                        "CREATE {}INDEX {} ON {}({});\n",
255                        unique,
256                        quote(&idx.name),
257                        quote(&modification.table_name),
258                        idx_cols.join(", ")
259                    ));
260                }
261            }
262        }
263
264        // Drop indexes
265        for idx in &modification.indexes_removed {
266            match dialect {
267                SqlDialect::MySql => {
268                    output.push_str(&format!(
269                        "DROP INDEX {} ON {};\n",
270                        quote(&idx.name),
271                        quote(&modification.table_name)
272                    ));
273                }
274                SqlDialect::Postgres | SqlDialect::Sqlite => {
275                    output.push_str(&format!("DROP INDEX IF EXISTS {};\n", quote(&idx.name)));
276                }
277                SqlDialect::Mssql => {
278                    output.push_str(&format!(
279                        "DROP INDEX {} ON {};\n",
280                        quote(&idx.name),
281                        quote(&modification.table_name)
282                    ));
283                }
284            }
285        }
286
287        // PK changes (complex - often requires table recreation)
288        if modification.pk_changed {
289            let old_pk = modification
290                .old_pk
291                .as_ref()
292                .map(|pk| pk.join(", "))
293                .unwrap_or_else(|| "(none)".to_string());
294            let new_pk = modification
295                .new_pk
296                .as_ref()
297                .map(|pk| pk.join(", "))
298                .unwrap_or_else(|| "(none)".to_string());
299
300            output.push_str(&format!(
301                "-- WARNING: PRIMARY KEY change ({}) -> ({}) may require table recreation\n",
302                old_pk, new_pk
303            ));
304
305            match dialect {
306                SqlDialect::MySql => {
307                    output.push_str(&format!(
308                        "ALTER TABLE {} DROP PRIMARY KEY;\n",
309                        quote(&modification.table_name)
310                    ));
311                    if let Some(ref new_pk_cols) = modification.new_pk {
312                        if !new_pk_cols.is_empty() {
313                            let pk_cols: Vec<String> =
314                                new_pk_cols.iter().map(|c| quote(c)).collect();
315                            output.push_str(&format!(
316                                "ALTER TABLE {} ADD PRIMARY KEY ({});\n",
317                                quote(&modification.table_name),
318                                pk_cols.join(", ")
319                            ));
320                        }
321                    }
322                }
323                SqlDialect::Postgres | SqlDialect::Mssql => {
324                    output.push_str(&format!(
325                        "ALTER TABLE {} DROP CONSTRAINT IF EXISTS {}_pkey;\n",
326                        quote(&modification.table_name),
327                        modification.table_name
328                    ));
329                    if let Some(ref new_pk_cols) = modification.new_pk {
330                        if !new_pk_cols.is_empty() {
331                            let pk_cols: Vec<String> =
332                                new_pk_cols.iter().map(|c| quote(c)).collect();
333                            output.push_str(&format!(
334                                "ALTER TABLE {} ADD PRIMARY KEY ({});\n",
335                                quote(&modification.table_name),
336                                pk_cols.join(", ")
337                            ));
338                        }
339                    }
340                }
341                SqlDialect::Sqlite => {
342                    output.push_str(
343                        "-- SQLite does not support ALTER PRIMARY KEY; table recreation required\n",
344                    );
345                }
346            }
347        }
348
349        output.push('\n');
350    }
351
352    // Note about data changes
353    if let Some(ref data) = result.data {
354        let has_data_changes = data
355            .tables
356            .values()
357            .any(|t| t.added_count > 0 || t.removed_count > 0 || t.modified_count > 0);
358
359        if has_data_changes {
360            output.push_str("-- Data changes detected but not included in migration script\n");
361            output.push_str("-- Use text or JSON output format for data diff details\n");
362        }
363    }
364
365    output
366}
sql_splitter/differ/output/sql.rs

sql_splitter/differ/output/
sql.rs
