drizzle_types/sqlite/ddl/
sql.rs

1//! SQL generation for `SQLite` DDL types
2//!
3//! This module provides SQL generation methods for DDL types, enabling
4//! unified SQL output from both compile-time and runtime schema definitions.
5
6use crate::alloc_prelude::*;
7use core::fmt::Write;
8
9use super::{
10    CheckConstraint, Column, ForeignKey, Generated, GeneratedType, Index, IndexColumnDef,
11    PrimaryKey, Table, UniqueConstraint, View,
12};
13
14fn quote_ident(ident: &str) -> String {
15    format!("`{}`", ident.replace('`', "``"))
16}
17
18/// Returns `true` when `expr` is fully wrapped in a single pair of balanced
19/// parentheses, e.g. `(a + b)` but not `(a) + (b)`.
20///
21/// This is a tolerant scanner (it does not understand string literals), which
22/// matches the tolerance level of the rest of this module.
23fn is_wrapped_in_parens(expr: &str) -> bool {
24    let bytes = expr.as_bytes();
25    if bytes.len() < 2 || bytes[0] != b'(' || bytes[bytes.len() - 1] != b')' {
26        return false;
27    }
28    let mut depth = 0i32;
29    for (i, ch) in expr.char_indices() {
30        match ch {
31            '(' => depth += 1,
32            ')' => {
33                depth -= 1;
34                if depth == 0 {
35                    return i == expr.len() - 1;
36                }
37            }
38            _ => {}
39        }
40    }
41    false
42}
43
44// =============================================================================
45// Table SQL Generation
46// =============================================================================
47
48/// A complete table definition with all related entities for SQL generation
49#[derive(Clone, Debug)]
50pub struct TableSql<'a> {
51    pub table: &'a Table,
52    pub columns: &'a [Column],
53    pub primary_key: Option<&'a PrimaryKey>,
54    pub foreign_keys: &'a [ForeignKey],
55    pub unique_constraints: &'a [UniqueConstraint],
56    pub check_constraints: &'a [CheckConstraint],
57}
58
59impl<'a> TableSql<'a> {
60    /// Create a new `TableSql` for SQL generation
61    #[must_use]
62    pub const fn new(table: &'a Table) -> Self {
63        Self {
64            table,
65            columns: &[],
66            primary_key: None,
67            foreign_keys: &[],
68            unique_constraints: &[],
69            check_constraints: &[],
70        }
71    }
72
73    /// Set columns
74    #[must_use]
75    pub const fn columns(mut self, columns: &'a [Column]) -> Self {
76        self.columns = columns;
77        self
78    }
79
80    /// Set primary key
81    #[must_use]
82    pub const fn primary_key(mut self, pk: Option<&'a PrimaryKey>) -> Self {
83        self.primary_key = pk;
84        self
85    }
86
87    /// Set foreign keys
88    #[must_use]
89    pub const fn foreign_keys(mut self, fks: &'a [ForeignKey]) -> Self {
90        self.foreign_keys = fks;
91        self
92    }
93
94    /// Set unique constraints
95    #[must_use]
96    pub const fn unique_constraints(mut self, uniques: &'a [UniqueConstraint]) -> Self {
97        self.unique_constraints = uniques;
98        self
99    }
100
101    /// Set check constraints
102    #[must_use]
103    pub const fn check_constraints(mut self, checks: &'a [CheckConstraint]) -> Self {
104        self.check_constraints = checks;
105        self
106    }
107
108    /// Generate CREATE TABLE SQL
109    #[must_use]
110    pub fn create_table_sql(&self) -> String {
111        let mut sql = format!("CREATE TABLE {} (\n", quote_ident(self.table.name()));
112
113        let mut lines = Vec::new();
114
115        // Columns whose `primary_key` flag is set but which no PrimaryKey
116        // entity covers still need a PRIMARY KEY clause (e.g. columns built via
117        // `ColumnDef::new(..).primary_key()` without a PrimaryKey entity).
118        let flag_pk_columns: Vec<&str> = self
119            .columns
120            .iter()
121            .filter(|c| {
122                c.is_primary_key()
123                    && !self
124                        .primary_key
125                        .as_ref()
126                        .is_some_and(|pk| pk.columns.iter().any(|pc| *pc == c.name()))
127            })
128            .map(Column::name)
129            .collect();
130
131        // Column definitions
132        for column in self.columns {
133            let is_entity_inline_pk = self.primary_key.as_ref().is_some_and(|pk| {
134                pk.columns.len() == 1
135                    && pk.columns.iter().any(|c| *c == column.name())
136                    && !pk.name_explicit
137            });
138            let is_flag_inline_pk =
139                flag_pk_columns.len() == 1 && flag_pk_columns[0] == column.name();
140            let is_inline_pk = is_entity_inline_pk || is_flag_inline_pk;
141
142            let is_inline_unique = self.unique_constraints.iter().any(|u| {
143                u.columns.len() == 1
144                    && u.columns.iter().any(|c| *c == column.name())
145                    && !u.name_explicit
146            });
147
148            lines.push(format!(
149                "\t{}",
150                column.to_column_sql(is_inline_pk, is_inline_unique)
151            ));
152        }
153
154        // Composite or named primary key
155        if let Some(pk) = &self.primary_key
156            && (pk.columns.len() > 1 || pk.name_explicit)
157        {
158            let cols = pk
159                .columns
160                .iter()
161                .map(|c| quote_ident(c))
162                .collect::<Vec<_>>()
163                .join(", ");
164            lines.push(format!(
165                "\tCONSTRAINT {} PRIMARY KEY({})",
166                quote_ident(pk.name()),
167                cols
168            ));
169        }
170
171        // Composite primary key declared only through column flags (no entity):
172        // rendering each column with an inline PRIMARY KEY would be invalid SQL,
173        // so emit a single table-level PRIMARY KEY clause instead.
174        if self.primary_key.is_none() && flag_pk_columns.len() > 1 {
175            let cols = flag_pk_columns
176                .iter()
177                .map(|c| quote_ident(c))
178                .collect::<Vec<_>>()
179                .join(", ");
180            lines.push(format!("\tPRIMARY KEY({cols})"));
181        }
182
183        // Foreign keys
184        for fk in self.foreign_keys {
185            lines.push(format!("\t{}", fk.to_constraint_sql()));
186        }
187
188        // Multi-column unique constraints
189        for unique in self
190            .unique_constraints
191            .iter()
192            .filter(|u| u.columns.len() > 1 || u.name_explicit)
193        {
194            let cols = unique
195                .columns
196                .iter()
197                .map(|c| quote_ident(c))
198                .collect::<Vec<_>>()
199                .join(", ");
200            lines.push(format!(
201                "\tCONSTRAINT {} UNIQUE({})",
202                quote_ident(unique.name()),
203                cols
204            ));
205        }
206
207        // Check constraints
208        for check in self.check_constraints {
209            lines.push(format!(
210                "\tCONSTRAINT {} CHECK({})",
211                quote_ident(check.name()),
212                check.value
213            ));
214        }
215
216        sql.push_str(&lines.join(",\n"));
217        sql.push_str("\n)");
218
219        // Table options
220        let mut options = Vec::new();
221        if self.table.without_rowid {
222            options.push("WITHOUT ROWID");
223        }
224        if self.table.strict {
225            options.push("STRICT");
226        }
227        if !options.is_empty() {
228            let _ = write!(sql, " {}", options.join(", "));
229        }
230
231        sql.push(';');
232        sql
233    }
234
235    /// Generate DROP TABLE SQL
236    #[must_use]
237    pub fn drop_table_sql(&self) -> String {
238        format!("DROP TABLE {};", quote_ident(self.table.name()))
239    }
240}
241
242// =============================================================================
243// Column SQL Generation
244// =============================================================================
245
246impl Column {
247    /// Generate the column definition SQL (without leading/trailing punctuation)
248    #[must_use]
249    pub fn to_column_sql(&self, inline_pk: bool, inline_unique: bool) -> String {
250        let mut sql = format!(
251            "{} {}",
252            quote_ident(self.name()),
253            self.sql_type().to_uppercase()
254        );
255
256        if inline_pk {
257            sql.push_str(" PRIMARY KEY");
258            // AUTOINCREMENT is only valid immediately after an inline
259            // `PRIMARY KEY`; emitting it anywhere else is a syntax error, so it
260            // is intentionally dropped when this column is not the inline PK.
261            if self.autoincrement.unwrap_or(false) {
262                sql.push_str(" AUTOINCREMENT");
263            }
264        }
265
266        if let Some(default) = self.default.as_ref() {
267            let _ = write!(sql, " DEFAULT {default}");
268        }
269
270        if let Some(generated) = &self.generated {
271            sql.push_str(&generated.to_sql());
272        }
273
274        // NOT NULL - skip for INTEGER PRIMARY KEY (allows NULL by default in SQLite)
275        if self.not_null && !(inline_pk && self.sql_type().to_lowercase().starts_with("int")) {
276            sql.push_str(" NOT NULL");
277        }
278
279        if inline_unique && !inline_pk {
280            sql.push_str(" UNIQUE");
281        }
282
283        // COLLATE applies to comparisons on this column. SQLite parses it as a
284        // column-constraint, so it follows other inline constraints.
285        if let Some(collate) = self.collate.as_ref() {
286            let _ = write!(sql, " COLLATE {collate}");
287        }
288
289        sql
290    }
291
292    /// Generate ADD COLUMN SQL
293    #[must_use]
294    pub fn add_column_sql(&self) -> String {
295        format!(
296            "ALTER TABLE {} ADD COLUMN {};",
297            quote_ident(self.table()),
298            self.to_column_sql(false, false)
299        )
300    }
301
302    /// Generate DROP COLUMN SQL
303    #[must_use]
304    pub fn drop_column_sql(&self) -> String {
305        format!(
306            "ALTER TABLE {} DROP COLUMN {};",
307            quote_ident(self.table()),
308            quote_ident(self.name())
309        )
310    }
311}
312
313// =============================================================================
314// Generated Column SQL
315// =============================================================================
316
317impl Generated {
318    /// Generate the GENERATED clause SQL
319    ///
320    /// `SQLite` requires the generation expression to be parenthesized
321    /// (`GENERATED ALWAYS AS (expr)`), so the expression is wrapped in parens
322    /// unless it is already fully parenthesized (the table macros store
323    /// pre-parenthesized expressions; introspection stores bare expressions).
324    #[must_use]
325    pub fn to_sql(&self) -> String {
326        let gen_type = match self.gen_type {
327            GeneratedType::Stored => "STORED",
328            GeneratedType::Virtual => "VIRTUAL",
329        };
330        let expression = self.expression.trim();
331        if is_wrapped_in_parens(expression) {
332            format!(" GENERATED ALWAYS AS {expression} {gen_type}")
333        } else {
334            format!(" GENERATED ALWAYS AS ({expression}) {gen_type}")
335        }
336    }
337}
338
339// =============================================================================
340// Foreign Key SQL Generation
341// =============================================================================
342
343impl ForeignKey {
344    /// Generate the CONSTRAINT ... FOREIGN KEY clause SQL
345    #[must_use]
346    pub fn to_constraint_sql(&self) -> String {
347        let from_cols = self
348            .columns
349            .iter()
350            .map(|c| quote_ident(c))
351            .collect::<Vec<_>>()
352            .join(", ");
353
354        let to_cols = self
355            .columns_to
356            .iter()
357            .map(|c| quote_ident(c))
358            .collect::<Vec<_>>()
359            .join(", ");
360
361        let mut sql = format!(
362            "CONSTRAINT {} FOREIGN KEY ({}) REFERENCES {}({})",
363            quote_ident(self.name()),
364            from_cols,
365            quote_ident(&self.table_to),
366            to_cols
367        );
368
369        if let Some(on_update) = self.on_update.as_ref()
370            && on_update != "NO ACTION"
371        {
372            let _ = write!(sql, " ON UPDATE {on_update}");
373        }
374
375        if let Some(on_delete) = self.on_delete.as_ref()
376            && on_delete != "NO ACTION"
377        {
378            let _ = write!(sql, " ON DELETE {on_delete}");
379        }
380
381        sql
382    }
383
384    /// Generate ADD FOREIGN KEY SQL (via new table constraint)
385    #[must_use]
386    pub fn add_fk_sql(&self) -> String {
387        // SQLite doesn't support ADD CONSTRAINT for foreign keys directly
388        // This would require table recreation
389        format!(
390            "-- SQLite requires table recreation to add foreign keys\n-- FK: {} on {}",
391            self.name(),
392            quote_ident(self.table())
393        )
394    }
395
396    /// Generate DROP FOREIGN KEY SQL (comment since `SQLite` doesn't support it)
397    #[must_use]
398    pub fn drop_fk_sql(&self) -> String {
399        format!(
400            "-- SQLite requires table recreation to drop foreign keys\n-- FK: {} on {}",
401            self.name(),
402            quote_ident(self.table())
403        )
404    }
405}
406
407// =============================================================================
408// Index SQL Generation
409// =============================================================================
410
411impl Index {
412    /// Generate CREATE INDEX SQL
413    #[must_use]
414    pub fn create_index_sql(&self) -> String {
415        let unique = if self.is_unique { "UNIQUE " } else { "" };
416
417        let columns = self
418            .columns
419            .iter()
420            .map(super::index::IndexColumn::to_sql)
421            .collect::<Vec<_>>()
422            .join(", ");
423
424        let mut sql = format!(
425            "CREATE {}INDEX {} ON {}({});",
426            unique,
427            quote_ident(self.name()),
428            quote_ident(self.table()),
429            columns
430        );
431
432        if let Some(where_clause) = self.where_clause.as_ref() {
433            // Remove trailing semicolon to add WHERE
434            sql.pop();
435            let _ = write!(sql, " WHERE {where_clause};");
436        }
437
438        sql
439    }
440
441    /// Generate DROP INDEX SQL
442    #[must_use]
443    pub fn drop_index_sql(&self) -> String {
444        format!("DROP INDEX {};", quote_ident(self.name()))
445    }
446}
447
448impl IndexColumnDef {
449    /// Generate the column reference for an index
450    #[must_use]
451    pub fn to_sql(&self) -> String {
452        if self.is_expression {
453            self.value.to_string()
454        } else {
455            quote_ident(self.value)
456        }
457    }
458}
459
460// =============================================================================
461// View SQL Generation
462// =============================================================================
463
464impl View {
465    /// Generate CREATE VIEW SQL
466    #[must_use]
467    pub fn create_view_sql(&self) -> String {
468        self.definition.as_ref().map_or_else(
469            || format!("-- View {} has no definition", quote_ident(self.name())),
470            |def| format!("CREATE VIEW {} AS {};", quote_ident(self.name()), def),
471        )
472    }
473
474    /// Generate DROP VIEW SQL
475    #[must_use]
476    pub fn drop_view_sql(&self) -> String {
477        format!("DROP VIEW {};", quote_ident(self.name()))
478    }
479}
480
481// =============================================================================
482// Table-level utilities
483// =============================================================================
484
485impl Table {
486    /// Generate DROP TABLE SQL
487    #[must_use]
488    pub fn drop_table_sql(&self) -> String {
489        format!("DROP TABLE {};", quote_ident(self.name()))
490    }
491
492    /// Generate RENAME TABLE SQL
493    #[must_use]
494    pub fn rename_table_sql(&self, new_name: &str) -> String {
495        format!(
496            "ALTER TABLE {} RENAME TO {};",
497            quote_ident(self.name()),
498            quote_ident(new_name)
499        )
500    }
501}
502
503// =============================================================================
504// Primary Key SQL Generation
505// =============================================================================
506
507impl PrimaryKey {
508    /// Generate the PRIMARY KEY constraint clause
509    #[must_use]
510    pub fn to_constraint_sql(&self) -> String {
511        let cols = self
512            .columns
513            .iter()
514            .map(|c| quote_ident(c))
515            .collect::<Vec<_>>()
516            .join(", ");
517
518        format!(
519            "CONSTRAINT {} PRIMARY KEY({})",
520            quote_ident(self.name()),
521            cols
522        )
523    }
524}
525
526// =============================================================================
527// Unique Constraint SQL Generation
528// =============================================================================
529
530impl UniqueConstraint {
531    /// Generate the UNIQUE constraint clause
532    #[must_use]
533    pub fn to_constraint_sql(&self) -> String {
534        let cols = self
535            .columns
536            .iter()
537            .map(|c| quote_ident(c))
538            .collect::<Vec<_>>()
539            .join(", ");
540
541        format!("CONSTRAINT {} UNIQUE({})", quote_ident(self.name()), cols)
542    }
543}
544
545// =============================================================================
546// Check Constraint SQL Generation
547// =============================================================================
548
549impl CheckConstraint {
550    /// Generate the CHECK constraint clause
551    #[must_use]
552    pub fn to_constraint_sql(&self) -> String {
553        format!(
554            "CONSTRAINT {} CHECK({})",
555            quote_ident(self.name()),
556            self.value
557        )
558    }
559}
560
561#[cfg(test)]
562mod tests {
563    use super::*;
564    use crate::sqlite::ddl::{
565        ColumnDef, ForeignKeyDef, IndexColumnDef, IndexDef, PrimaryKeyDef, ReferentialAction,
566        TableDef,
567    };
568    use std::borrow::Cow;
569
570    #[test]
571    fn test_simple_create_table() {
572        let table = TableDef::new("users").into_table();
573        let columns = [
574            ColumnDef::new("users", "id", "INTEGER")
575                .primary_key()
576                .autoincrement()
577                .into_column(),
578            ColumnDef::new("users", "name", "TEXT")
579                .not_null()
580                .into_column(),
581            ColumnDef::new("users", "email", "TEXT").into_column(),
582        ];
583        const PK_COLS: &[Cow<'static, str>] = &[Cow::Borrowed("id")];
584        let pk = PrimaryKeyDef::new("users", "users_pk")
585            .columns(PK_COLS)
586            .into_primary_key();
587
588        let sql = TableSql::new(&table)
589            .columns(&columns)
590            .primary_key(Some(&pk))
591            .create_table_sql();
592
593        assert!(sql.contains("CREATE TABLE `users`"));
594        assert!(sql.contains("`id` INTEGER PRIMARY KEY AUTOINCREMENT"));
595        assert!(sql.contains("`name` TEXT NOT NULL"));
596        assert!(sql.contains("`email` TEXT"));
597    }
598
599    #[test]
600    fn test_table_with_foreign_key() {
601        let table = TableDef::new("posts").into_table();
602        let columns = [
603            ColumnDef::new("posts", "id", "INTEGER")
604                .primary_key()
605                .into_column(),
606            ColumnDef::new("posts", "user_id", "INTEGER")
607                .not_null()
608                .into_column(),
609        ];
610        const PK_COLS: &[Cow<'static, str>] = &[Cow::Borrowed("id")];
611        let pk = PrimaryKeyDef::new("posts", "posts_pk")
612            .columns(PK_COLS)
613            .into_primary_key();
614        const FK_COLS: &[Cow<'static, str>] = &[Cow::Borrowed("user_id")];
615        const FK_REFS: &[Cow<'static, str>] = &[Cow::Borrowed("id")];
616        let fks = [ForeignKeyDef::new("posts", "posts_user_id_fk")
617            .columns(FK_COLS)
618            .references("users", FK_REFS)
619            .on_delete(ReferentialAction::Cascade)
620            .into_foreign_key()];
621
622        let sql = TableSql::new(&table)
623            .columns(&columns)
624            .primary_key(Some(&pk))
625            .foreign_keys(&fks)
626            .create_table_sql();
627
628        assert!(sql.contains("FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)"));
629        assert!(sql.contains("ON DELETE CASCADE"));
630    }
631
632    #[test]
633    fn test_create_index() {
634        const COLS: &[IndexColumnDef] = &[IndexColumnDef::new("email")];
635        let index = IndexDef::new("users", "users_email_idx")
636            .columns(COLS)
637            .unique()
638            .into_index();
639
640        let sql = index.create_index_sql();
641        assert_eq!(
642            sql,
643            "CREATE UNIQUE INDEX `users_email_idx` ON `users`(`email`);"
644        );
645    }
646
647    #[test]
648    fn test_column_flag_primary_key_renders_inline_without_entity() {
649        // A column built via ColumnDef::primary_key().autoincrement() must
650        // render an inline PRIMARY KEY AUTOINCREMENT even when no PrimaryKey
651        // entity exists.
652        let table = TableDef::new("users").into_table();
653        let columns = [
654            ColumnDef::new("users", "id", "INTEGER")
655                .primary_key()
656                .autoincrement()
657                .into_column(),
658            ColumnDef::new("users", "name", "TEXT")
659                .not_null()
660                .into_column(),
661        ];
662
663        let sql = TableSql::new(&table).columns(&columns).create_table_sql();
664
665        assert!(
666            sql.contains("`id` INTEGER PRIMARY KEY AUTOINCREMENT"),
667            "expected inline PRIMARY KEY AUTOINCREMENT, got: {sql}"
668        );
669        assert!(
670            !sql.contains("INTEGER AUTOINCREMENT"),
671            "orphan AUTOINCREMENT without PRIMARY KEY: {sql}"
672        );
673    }
674
675    #[test]
676    fn test_column_flag_composite_primary_key_renders_table_constraint() {
677        let table = TableDef::new("pair").into_table();
678        let columns = [
679            ColumnDef::new("pair", "a", "INTEGER")
680                .primary_key()
681                .into_column(),
682            ColumnDef::new("pair", "b", "INTEGER")
683                .primary_key()
684                .into_column(),
685        ];
686
687        let sql = TableSql::new(&table).columns(&columns).create_table_sql();
688
689        assert!(
690            sql.contains("PRIMARY KEY(`a`, `b`)"),
691            "expected composite PRIMARY KEY clause, got: {sql}"
692        );
693        assert_eq!(
694            sql.matches("PRIMARY KEY").count(),
695            1,
696            "composite flag PK must render exactly one PRIMARY KEY clause: {sql}"
697        );
698    }
699
700    #[test]
701    fn test_generated_expression_is_parenthesized() {
702        use crate::sqlite::ddl::{Generated, GeneratedType};
703
704        let bare = Generated {
705            expression: Cow::Borrowed("length(name)"),
706            gen_type: GeneratedType::Stored,
707        };
708        assert_eq!(bare.to_sql(), " GENERATED ALWAYS AS (length(name)) STORED");
709
710        // Already-parenthesized expressions (macro canonical form) must not be
711        // double-wrapped.
712        let wrapped = Generated {
713            expression: Cow::Borrowed("(length(name))"),
714            gen_type: GeneratedType::Virtual,
715        };
716        assert_eq!(
717            wrapped.to_sql(),
718            " GENERATED ALWAYS AS (length(name)) VIRTUAL"
719        );
720
721        // `(a) + (b)` starts and ends with parens but is NOT fully wrapped.
722        let tricky = Generated {
723            expression: Cow::Borrowed("(a) + (b)"),
724            gen_type: GeneratedType::Virtual,
725        };
726        assert_eq!(tricky.to_sql(), " GENERATED ALWAYS AS ((a) + (b)) VIRTUAL");
727    }
728
729    #[test]
730    fn test_strict_without_rowid() {
731        let table = TableDef::new("data").strict().without_rowid().into_table();
732        let columns = [ColumnDef::new("data", "key", "TEXT")
733            .primary_key()
734            .not_null()
735            .into_column()];
736        const PK_COLS: &[Cow<'static, str>] = &[Cow::Borrowed("key")];
737        let pk = PrimaryKeyDef::new("data", "data_pk")
738            .columns(PK_COLS)
739            .into_primary_key();
740
741        let sql = TableSql::new(&table)
742            .columns(&columns)
743            .primary_key(Some(&pk))
744            .create_table_sql();
745
746        assert!(sql.ends_with("WITHOUT ROWID, STRICT;"));
747    }
748}
drizzle_types/sqlite/ddl/sql.rs

drizzle_types/sqlite/ddl/
sql.rs
