sqlite_diff_rs/builders/
sql_output.rs

1//! SQL statement generation from changesets and patchsets.
2//!
3//! Converts changeset and patchset operations back into SQL statements.
4//! Useful for debugging, logging, or applying changes to non-SQLite databases.
5//!
6//! # Example
7//!
8//! ```rust
9//! use sqlite_diff_rs::{SimpleTable, PatchSet, DiffOps, Insert};
10//!
11//! let table = SimpleTable::new("users", &["id", "name"], &[0]);
12//! let insert = Insert::from(table.clone())
13//!     .set(0, 1i64).unwrap()
14//!     .set(1, "Alice").unwrap();
15//!
16//! let patchset = PatchSet::<SimpleTable, String, Vec<u8>>::new().insert(insert);
17//!
18//! for sql in patchset.sql_statements() {
19//!     println!("{}", sql);
20//!     // Prints: INSERT INTO "users" ("id", "name") VALUES (1, 'Alice')
21//! }
22//! ```
23
24use alloc::string::String;
25use alloc::vec::Vec;
26use core::fmt::{Debug, Write};
27use core::hash::Hash;
28
29use crate::builders::operation::Operation;
30use crate::builders::{ChangesetFormat, DiffSetBuilder, PatchsetFormat};
31use crate::encoding::{MaybeValue, Value};
32use crate::schema::NamedColumns;
33
34/// Type alias for changeset update pairs: (old_value, new_value) for each column.
35type ChangesetUpdatePairs<S, B> = [(MaybeValue<S, B>, MaybeValue<S, B>)];
36
37/// Quote a SQL identifier (table or column name) with double quotes.
38///
39/// Escapes any embedded double quotes by doubling them.
40fn quote_identifier(name: &str) -> String {
41    let mut out = String::with_capacity(name.len() + 2);
42    out.push('"');
43    for c in name.chars() {
44        if c == '"' {
45            out.push_str("\"\"");
46        } else {
47            out.push(c);
48        }
49    }
50    out.push('"');
51    out
52}
53
54/// Trait for tables that can provide column names by index.
55///
56/// This extends [`NamedColumns`] to support SQL generation.
57pub trait ColumnNames: NamedColumns {
58    /// Get the column name for a given index.
59    ///
60    /// Returns `None` if the index is out of bounds.
61    fn column_name(&self, index: usize) -> Option<&str>;
62
63    /// Get the column indices that are part of the primary key, in PK order.
64    fn pk_indices(&self) -> Vec<usize> {
65        // Build a list of (pk_ordinal, col_idx) pairs, then sort by pk_ordinal
66        let mut pk_cols: Vec<(usize, usize)> = Vec::new();
67        for col_idx in 0..self.number_of_columns() {
68            if let Some(pk_ordinal) = self.primary_key_index(col_idx) {
69                pk_cols.push((pk_ordinal, col_idx));
70            }
71        }
72        pk_cols.sort_by_key(|(ordinal, _)| *ordinal);
73        pk_cols.into_iter().map(|(_, col_idx)| col_idx).collect()
74    }
75}
76
77impl ColumnNames for crate::SimpleTable {
78    fn column_name(&self, index: usize) -> Option<&str> {
79        self.column_name(index)
80    }
81
82    fn pk_indices(&self) -> Vec<usize> {
83        // SimpleTable already has this method
84        crate::SimpleTable::pk_indices(self)
85    }
86}
87
88/// Format an INSERT statement with column names.
89fn format_insert<T: ColumnNames, S: AsRef<str>, B: AsRef<[u8]>>(
90    table: &T,
91    values: &[Value<S, B>],
92) -> String {
93    let mut sql = String::new();
94    write!(sql, "INSERT INTO {}", quote_identifier(table.name())).unwrap();
95
96    // Column names
97    sql.push_str(" (");
98    for i in 0..table.number_of_columns() {
99        if i > 0 {
100            sql.push_str(", ");
101        }
102        if let Some(name) = table.column_name(i) {
103            sql.push_str(&quote_identifier(name));
104        } else {
105            write!(sql, "\"col{i}\"").unwrap();
106        }
107    }
108    sql.push_str(") VALUES (");
109
110    // Values
111    for (i, val) in values.iter().enumerate() {
112        if i > 0 {
113            sql.push_str(", ");
114        }
115        write!(sql, "{val}").unwrap();
116    }
117    sql.push(')');
118    sql
119}
120
121/// Format a DELETE statement (changeset format - has all old values).
122fn format_delete_changeset<T: ColumnNames, S: AsRef<str>, B: AsRef<[u8]>>(
123    table: &T,
124    values: &[Value<S, B>],
125) -> String {
126    let mut sql = String::new();
127    write!(sql, "DELETE FROM {}", quote_identifier(table.name())).unwrap();
128    sql.push_str(" WHERE ");
129
130    // Use PK columns for the WHERE clause
131    let mut first = true;
132    for (col_idx, value) in values.iter().enumerate() {
133        if table.primary_key_index(col_idx).is_some() {
134            if !first {
135                sql.push_str(" AND ");
136            }
137            first = false;
138            if let Some(name) = table.column_name(col_idx) {
139                sql.push_str(&quote_identifier(name));
140            } else {
141                write!(sql, "\"col{col_idx}\"").unwrap();
142            }
143            sql.push_str(" = ");
144            write!(sql, "{value}").unwrap();
145        }
146    }
147    sql
148}
149
150/// Format a DELETE statement (patchset format - PK only).
151fn format_delete_patchset<T: ColumnNames, S: AsRef<str>, B: AsRef<[u8]>>(
152    table: &T,
153    pk: &[Value<S, B>],
154) -> String {
155    let mut sql = String::new();
156    write!(sql, "DELETE FROM {}", quote_identifier(table.name())).unwrap();
157    sql.push_str(" WHERE ");
158
159    // Get PK column indices in order
160    let pk_indices = table.pk_indices();
161
162    let mut first = true;
163    for (pk_ordinal, &col_idx) in pk_indices.iter().enumerate() {
164        if !first {
165            sql.push_str(" AND ");
166        }
167        first = false;
168        if let Some(name) = table.column_name(col_idx) {
169            sql.push_str(&quote_identifier(name));
170        } else {
171            write!(sql, "\"col{col_idx}\"").unwrap();
172        }
173        sql.push_str(" = ");
174        write!(sql, "{}", pk[pk_ordinal]).unwrap();
175    }
176    sql
177}
178
179/// Format an UPDATE statement (changeset format).
180fn format_update_changeset<T: ColumnNames, S: AsRef<str>, B: AsRef<[u8]>>(
181    table: &T,
182    pairs: &ChangesetUpdatePairs<S, B>,
183) -> String {
184    let mut sql = String::new();
185    write!(sql, "UPDATE {}", quote_identifier(table.name())).unwrap();
186    sql.push_str(" SET ");
187
188    // SET clause: columns that changed (have new values)
189    let mut first_set = true;
190    for (col_idx, (_old, new)) in pairs.iter().enumerate() {
191        if let Some(new_val) = new {
192            // Skip PK columns in SET (they go in WHERE)
193            if table.primary_key_index(col_idx).is_some() {
194                continue;
195            }
196            if !first_set {
197                sql.push_str(", ");
198            }
199            first_set = false;
200            if let Some(name) = table.column_name(col_idx) {
201                sql.push_str(&quote_identifier(name));
202            } else {
203                write!(sql, "\"col{col_idx}\"").unwrap();
204            }
205            sql.push_str(" = ");
206            write!(sql, "{new_val}").unwrap();
207        }
208    }
209
210    // WHERE clause: use old PK values
211    sql.push_str(" WHERE ");
212    let mut first_where = true;
213    for (col_idx, (old, _new)) in pairs.iter().enumerate() {
214        if table.primary_key_index(col_idx).is_some() {
215            if let Some(old_val) = old {
216                if !first_where {
217                    sql.push_str(" AND ");
218                }
219                first_where = false;
220                if let Some(name) = table.column_name(col_idx) {
221                    sql.push_str(&quote_identifier(name));
222                } else {
223                    write!(sql, "\"col{col_idx}\"").unwrap();
224                }
225                sql.push_str(" = ");
226                write!(sql, "{old_val}").unwrap();
227            }
228        }
229    }
230    sql
231}
232
233/// Format an UPDATE statement (patchset format).
234fn format_update_patchset<T: ColumnNames, S: AsRef<str>, B: AsRef<[u8]>>(
235    table: &T,
236    pk: &[Value<S, B>],
237    pairs: &[((), MaybeValue<S, B>)],
238) -> String {
239    let mut sql = String::new();
240    write!(sql, "UPDATE {}", quote_identifier(table.name())).unwrap();
241    sql.push_str(" SET ");
242
243    // SET clause: columns that changed (have new values), excluding PK
244    let mut first_set = true;
245    for (col_idx, ((), new)) in pairs.iter().enumerate() {
246        if let Some(new_val) = new {
247            // Skip PK columns in SET
248            if table.primary_key_index(col_idx).is_some() {
249                continue;
250            }
251            if !first_set {
252                sql.push_str(", ");
253            }
254            first_set = false;
255            if let Some(name) = table.column_name(col_idx) {
256                sql.push_str(&quote_identifier(name));
257            } else {
258                write!(sql, "\"col{col_idx}\"").unwrap();
259            }
260            sql.push_str(" = ");
261            write!(sql, "{new_val}").unwrap();
262        }
263    }
264
265    // WHERE clause: use PK values
266    sql.push_str(" WHERE ");
267    let pk_indices = table.pk_indices();
268
269    let mut first_where = true;
270    for (pk_ordinal, &col_idx) in pk_indices.iter().enumerate() {
271        if !first_where {
272            sql.push_str(" AND ");
273        }
274        first_where = false;
275        if let Some(name) = table.column_name(col_idx) {
276            sql.push_str(&quote_identifier(name));
277        } else {
278            write!(sql, "\"col{col_idx}\"").unwrap();
279        }
280        sql.push_str(" = ");
281        write!(sql, "{}", pk[pk_ordinal]).unwrap();
282    }
283    sql
284}
285
286// ============================================================================
287// DiffSetBuilder::sql_statements() implementations
288// ============================================================================
289
290impl<
291    T: ColumnNames,
292    S: AsRef<str> + Clone + Debug + Hash + Eq,
293    B: AsRef<[u8]> + Clone + Debug + Hash + Eq,
294> DiffSetBuilder<ChangesetFormat, T, S, B>
295{
296    /// Iterate over operations as SQL statements.
297    ///
298    /// Each operation is converted to an INSERT, UPDATE, or DELETE statement.
299    /// Identifiers (table and column names) are quoted with double quotes.
300    /// Statements do not include trailing semicolons.
301    ///
302    /// # Example
303    ///
304    /// ```rust
305    /// use sqlite_diff_rs::{SimpleTable, ChangeSet, DiffOps, Insert};
306    ///
307    /// let table = SimpleTable::new("users", &["id", "name"], &[0]);
308    /// let insert = Insert::from(table.clone())
309    ///     .set(0, 1i64).unwrap()
310    ///     .set(1, "Alice").unwrap();
311    ///
312    /// let changeset = ChangeSet::<SimpleTable, String, Vec<u8>>::new().insert(insert);
313    ///
314    /// for sql in changeset.sql_statements() {
315    ///     assert_eq!(sql, r#"INSERT INTO "users" ("id", "name") VALUES (1, 'Alice')"#);
316    /// }
317    /// ```
318    pub fn sql_statements(&self) -> impl Iterator<Item = String> + '_ {
319        self.tables.iter().flat_map(|(table, rows)| {
320            rows.values().map(move |op| match op {
321                Operation::Insert { values, .. } => format_insert(table, values),
322                Operation::Delete { data: values, .. } => format_delete_changeset(table, values),
323                Operation::Update { values, .. } => format_update_changeset(table, values),
324            })
325        })
326    }
327}
328
329impl<T: ColumnNames, S: AsRef<str> + Clone + Hash + Eq, B: AsRef<[u8]> + Clone + Hash + Eq>
330    DiffSetBuilder<PatchsetFormat, T, S, B>
331{
332    /// Iterate over operations as SQL statements.
333    ///
334    /// Each operation is converted to an INSERT, UPDATE, or DELETE statement.
335    /// Identifiers (table and column names) are quoted with double quotes.
336    /// Statements do not include trailing semicolons.
337    ///
338    /// # Example
339    ///
340    /// ```rust
341    /// use sqlite_diff_rs::{SimpleTable, PatchSet, DiffOps, Insert};
342    ///
343    /// let table = SimpleTable::new("users", &["id", "name"], &[0]);
344    /// let insert = Insert::from(table.clone())
345    ///     .set(0, 1i64).unwrap()
346    ///     .set(1, "Alice").unwrap();
347    ///
348    /// let patchset = PatchSet::<SimpleTable, String, Vec<u8>>::new().insert(insert);
349    ///
350    /// for sql in patchset.sql_statements() {
351    ///     assert_eq!(sql, r#"INSERT INTO "users" ("id", "name") VALUES (1, 'Alice')"#);
352    /// }
353    /// ```
354    pub fn sql_statements(&self) -> impl Iterator<Item = String> + '_ {
355        self.tables.iter().flat_map(|(table, rows)| {
356            rows.iter().map(move |(pk, op)| match op {
357                Operation::Insert { values, .. } => format_insert(table, values),
358                Operation::Delete { data: (), .. } => format_delete_patchset(table, pk),
359                Operation::Update { values, .. } => format_update_patchset(table, pk, values),
360            })
361        })
362    }
363}
364
365#[cfg(test)]
366mod tests {
367    use super::*;
368    use crate::{
369        ChangeDelete, ChangeSet, DiffOps, Insert, PatchDelete, PatchSet, SimpleTable, Update,
370    };
371
372    #[test]
373    fn test_quote_identifier_simple() {
374        assert_eq!(quote_identifier("users"), r#""users""#);
375    }
376
377    #[test]
378    fn test_quote_identifier_with_quotes() {
379        assert_eq!(quote_identifier(r#"user"name"#), r#""user""name""#);
380    }
381
382    #[test]
383    fn test_changeset_insert_sql() {
384        let table = SimpleTable::new("users", &["id", "name"], &[0]);
385        let insert = Insert::from(table.clone())
386            .set(0, 1i64)
387            .unwrap()
388            .set(1, "Alice")
389            .unwrap();
390
391        let cs = ChangeSet::<SimpleTable, String, Vec<u8>>::new().insert(insert);
392        let stmts: Vec<_> = cs.sql_statements().collect();
393
394        assert_eq!(stmts.len(), 1);
395        assert_eq!(
396            stmts[0],
397            r#"INSERT INTO "users" ("id", "name") VALUES (1, 'Alice')"#
398        );
399    }
400
401    #[test]
402    fn test_patchset_insert_sql() {
403        let table = SimpleTable::new("users", &["id", "name"], &[0]);
404        let insert = Insert::from(table.clone())
405            .set(0, 1i64)
406            .unwrap()
407            .set(1, "Alice")
408            .unwrap();
409
410        let ps = PatchSet::<SimpleTable, String, Vec<u8>>::new().insert(insert);
411        let stmts: Vec<_> = ps.sql_statements().collect();
412
413        assert_eq!(stmts.len(), 1);
414        assert_eq!(
415            stmts[0],
416            r#"INSERT INTO "users" ("id", "name") VALUES (1, 'Alice')"#
417        );
418    }
419
420    #[test]
421    fn test_changeset_delete_sql() {
422        let table = SimpleTable::new("users", &["id", "name"], &[0]);
423        let delete = ChangeDelete::from(table.clone())
424            .set(0, 42i64)
425            .unwrap()
426            .set(1, "Bob")
427            .unwrap();
428
429        let cs = ChangeSet::<SimpleTable, String, Vec<u8>>::new().delete(delete);
430        let stmts: Vec<_> = cs.sql_statements().collect();
431
432        assert_eq!(stmts.len(), 1);
433        assert_eq!(stmts[0], r#"DELETE FROM "users" WHERE "id" = 42"#);
434    }
435
436    #[test]
437    fn test_patchset_delete_sql() {
438        let table = SimpleTable::new("users", &["id", "name"], &[0]);
439        let delete = PatchDelete::new(table.clone(), alloc::vec![Value::Integer(42)]);
440
441        let ps = PatchSet::<SimpleTable, String, Vec<u8>>::new().delete(delete);
442        let stmts: Vec<_> = ps.sql_statements().collect();
443
444        assert_eq!(stmts.len(), 1);
445        assert_eq!(stmts[0], r#"DELETE FROM "users" WHERE "id" = 42"#);
446    }
447
448    #[test]
449    fn test_changeset_update_sql() {
450        let table = SimpleTable::new("users", &["id", "name"], &[0]);
451        let update = Update::<SimpleTable, ChangesetFormat, String, Vec<u8>>::from(table.clone())
452            .set(0, 1i64, 1i64)
453            .unwrap()
454            .set(1, "Alice", "Alicia")
455            .unwrap();
456
457        let cs = ChangeSet::<SimpleTable, String, Vec<u8>>::new().update(update);
458        let stmts: Vec<_> = cs.sql_statements().collect();
459
460        assert_eq!(stmts.len(), 1);
461        assert_eq!(
462            stmts[0],
463            r#"UPDATE "users" SET "name" = 'Alicia' WHERE "id" = 1"#
464        );
465    }
466
467    #[test]
468    fn test_patchset_update_sql() {
469        let table = SimpleTable::new("users", &["id", "name"], &[0]);
470        let update = Update::<SimpleTable, PatchsetFormat, String, Vec<u8>>::from(table.clone())
471            .set(0, 1i64)
472            .unwrap()
473            .set(1, "Alicia")
474            .unwrap();
475
476        let ps = PatchSet::<SimpleTable, String, Vec<u8>>::new().update(update);
477        let stmts: Vec<_> = ps.sql_statements().collect();
478
479        assert_eq!(stmts.len(), 1);
480        assert_eq!(
481            stmts[0],
482            r#"UPDATE "users" SET "name" = 'Alicia' WHERE "id" = 1"#
483        );
484    }
485
486    #[test]
487    fn test_sql_escapes_quotes_in_strings() {
488        let table = SimpleTable::new("users", &["id", "name"], &[0]);
489        let insert = Insert::from(table.clone())
490            .set(0, 1i64)
491            .unwrap()
492            .set(1, "O'Brien")
493            .unwrap();
494
495        let cs = ChangeSet::<SimpleTable, String, Vec<u8>>::new().insert(insert);
496        let stmts: Vec<_> = cs.sql_statements().collect();
497
498        assert_eq!(
499            stmts[0],
500            r#"INSERT INTO "users" ("id", "name") VALUES (1, 'O''Brien')"#
501        );
502    }
503
504    #[test]
505    fn test_sql_escapes_quotes_in_identifiers() {
506        let table = SimpleTable::new(r#"user"table"#, &["id", r#"user"name"#], &[0]);
507        let insert = Insert::from(table.clone())
508            .set(0, 1i64)
509            .unwrap()
510            .set(1, "Alice")
511            .unwrap();
512
513        let cs = ChangeSet::<SimpleTable, String, Vec<u8>>::new().insert(insert);
514        let stmts: Vec<_> = cs.sql_statements().collect();
515
516        assert_eq!(
517            stmts[0],
518            r#"INSERT INTO "user""table" ("id", "user""name") VALUES (1, 'Alice')"#
519        );
520    }
521
522    #[test]
523    fn test_multiple_operations() {
524        let table = SimpleTable::new("users", &["id", "name"], &[0]);
525
526        let insert1 = Insert::from(table.clone())
527            .set(0, 1i64)
528            .unwrap()
529            .set(1, "Alice")
530            .unwrap();
531
532        let insert2 = Insert::from(table.clone())
533            .set(0, 2i64)
534            .unwrap()
535            .set(1, "Bob")
536            .unwrap();
537
538        let cs = ChangeSet::<SimpleTable, String, Vec<u8>>::new()
539            .insert(insert1)
540            .insert(insert2);
541
542        let stmts: Vec<_> = cs.sql_statements().collect();
543        assert_eq!(stmts.len(), 2);
544    }
545
546    #[test]
547    fn test_composite_pk_delete() {
548        let table = SimpleTable::new("order_items", &["order_id", "item_id", "qty"], &[0, 1]);
549        let delete = ChangeDelete::from(table.clone())
550            .set(0, 100i64)
551            .unwrap()
552            .set(1, 5i64)
553            .unwrap()
554            .set(2, 10i64)
555            .unwrap();
556
557        let cs = ChangeSet::<SimpleTable, String, Vec<u8>>::new().delete(delete);
558        let stmts: Vec<_> = cs.sql_statements().collect();
559
560        assert_eq!(
561            stmts[0],
562            r#"DELETE FROM "order_items" WHERE "order_id" = 100 AND "item_id" = 5"#
563        );
564    }
565
566    /// Test schema whose ColumnNames impl returns `None` for non-PK columns.
567    /// Drives the `"\"col{col_idx}\""` fallback path in INSERT/UPDATE SET
568    /// and DELETE WHERE rendering.
569    #[derive(Debug, Clone, PartialEq, Eq, Hash)]
570    struct AnonColsTable {
571        name: alloc::string::String,
572        num_columns: usize,
573        pk_column: usize,
574    }
575
576    impl crate::DynTable for AnonColsTable {
577        fn name(&self) -> &str {
578            &self.name
579        }
580        fn number_of_columns(&self) -> usize {
581            self.num_columns
582        }
583        fn write_pk_flags(&self, buf: &mut [u8]) {
584            for (i, b) in buf.iter_mut().enumerate() {
585                *b = u8::from(i == self.pk_column);
586            }
587        }
588    }
589
590    impl crate::SchemaWithPK for AnonColsTable {
591        fn number_of_primary_keys(&self) -> usize {
592            1
593        }
594        fn primary_key_index(&self, col_idx: usize) -> Option<usize> {
595            (col_idx == self.pk_column).then_some(0)
596        }
597        fn extract_pk<S: Clone, B: Clone>(
598            &self,
599            values: &impl crate::IndexableValues<Text = S, Binary = B>,
600        ) -> Vec<Value<S, B>> {
601            alloc::vec![values.get(self.pk_column).unwrap()]
602        }
603    }
604
605    impl crate::schema::NamedColumns for AnonColsTable {
606        fn column_index(&self, _column_name: &str) -> Option<usize> {
607            None
608        }
609    }
610
611    impl crate::ColumnNames for AnonColsTable {
612        // Always return None: every column rendered should use the
613        // \"col{col_idx}\" fallback.
614        fn column_name(&self, _index: usize) -> Option<&str> {
615            None
616        }
617    }
618
619    #[test]
620    fn test_sql_output_uses_col_index_fallback() {
621        let table = AnonColsTable {
622            name: "t".into(),
623            num_columns: 2,
624            pk_column: 0,
625        };
626        let insert = Insert::from(table.clone())
627            .set(0, 42i64)
628            .unwrap()
629            .set(1, "hello")
630            .unwrap();
631        let cs = ChangeSet::<AnonColsTable, String, Vec<u8>>::new().insert(insert);
632        let stmts: Vec<_> = cs.sql_statements().collect();
633        assert_eq!(
634            stmts[0],
635            r#"INSERT INTO "t" ("col0", "col1") VALUES (42, 'hello')"#
636        );
637    }
638
639    #[test]
640    fn test_sql_output_col_index_fallback_in_patchset_delete() {
641        let table = AnonColsTable {
642            name: "t".into(),
643            num_columns: 2,
644            pk_column: 0,
645        };
646        let delete: PatchDelete<_, String, Vec<u8>> =
647            PatchDelete::new(table.clone(), alloc::vec![Value::Integer(7)]);
648        let ps = PatchSet::<AnonColsTable, String, Vec<u8>>::new().delete(delete);
649        let stmts: Vec<_> = ps.sql_statements().collect();
650        assert_eq!(stmts[0], r#"DELETE FROM "t" WHERE "col0" = 7"#);
651    }
652}
sqlite_diff_rs/builders/sql_output.rs

sqlite_diff_rs/builders/
sql_output.rs
