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

sql_splitter/differ/output/
sql.rs
