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// =============================================================================
19// Table SQL Generation
20// =============================================================================
21
22/// A complete table definition with all related entities for SQL generation
23#[derive(Clone, Debug)]
24pub struct TableSql<'a> {
25    pub table: &'a Table,
26    pub columns: &'a [Column],
27    pub primary_key: Option<&'a PrimaryKey>,
28    pub foreign_keys: &'a [ForeignKey],
29    pub unique_constraints: &'a [UniqueConstraint],
30    pub check_constraints: &'a [CheckConstraint],
31}
32
33impl<'a> TableSql<'a> {
34    /// Create a new `TableSql` for SQL generation
35    #[must_use]
36    pub const fn new(table: &'a Table) -> Self {
37        Self {
38            table,
39            columns: &[],
40            primary_key: None,
41            foreign_keys: &[],
42            unique_constraints: &[],
43            check_constraints: &[],
44        }
45    }
46
47    /// Set columns
48    #[must_use]
49    pub const fn columns(mut self, columns: &'a [Column]) -> Self {
50        self.columns = columns;
51        self
52    }
53
54    /// Set primary key
55    #[must_use]
56    pub const fn primary_key(mut self, pk: Option<&'a PrimaryKey>) -> Self {
57        self.primary_key = pk;
58        self
59    }
60
61    /// Set foreign keys
62    #[must_use]
63    pub const fn foreign_keys(mut self, fks: &'a [ForeignKey]) -> Self {
64        self.foreign_keys = fks;
65        self
66    }
67
68    /// Set unique constraints
69    #[must_use]
70    pub const fn unique_constraints(mut self, uniques: &'a [UniqueConstraint]) -> Self {
71        self.unique_constraints = uniques;
72        self
73    }
74
75    /// Set check constraints
76    #[must_use]
77    pub const fn check_constraints(mut self, checks: &'a [CheckConstraint]) -> Self {
78        self.check_constraints = checks;
79        self
80    }
81
82    /// Generate CREATE TABLE SQL
83    #[must_use]
84    pub fn create_table_sql(&self) -> String {
85        let mut sql = format!("CREATE TABLE {} (\n", quote_ident(self.table.name()));
86
87        let mut lines = Vec::new();
88
89        // Column definitions
90        for column in self.columns {
91            let is_inline_pk = self.primary_key.as_ref().is_some_and(|pk| {
92                pk.columns.len() == 1
93                    && pk.columns.iter().any(|c| *c == column.name())
94                    && !pk.name_explicit
95            });
96
97            let is_inline_unique = self.unique_constraints.iter().any(|u| {
98                u.columns.len() == 1
99                    && u.columns.iter().any(|c| *c == column.name())
100                    && !u.name_explicit
101            });
102
103            lines.push(format!(
104                "\t{}",
105                column.to_column_sql(is_inline_pk, is_inline_unique)
106            ));
107        }
108
109        // Composite or named primary key
110        if let Some(pk) = &self.primary_key
111            && (pk.columns.len() > 1 || pk.name_explicit)
112        {
113            let cols = pk
114                .columns
115                .iter()
116                .map(|c| quote_ident(c))
117                .collect::<Vec<_>>()
118                .join(", ");
119            lines.push(format!(
120                "\tCONSTRAINT {} PRIMARY KEY({})",
121                quote_ident(pk.name()),
122                cols
123            ));
124        }
125
126        // Foreign keys
127        for fk in self.foreign_keys {
128            lines.push(format!("\t{}", fk.to_constraint_sql()));
129        }
130
131        // Multi-column unique constraints
132        for unique in self
133            .unique_constraints
134            .iter()
135            .filter(|u| u.columns.len() > 1 || u.name_explicit)
136        {
137            let cols = unique
138                .columns
139                .iter()
140                .map(|c| quote_ident(c))
141                .collect::<Vec<_>>()
142                .join(", ");
143            lines.push(format!(
144                "\tCONSTRAINT {} UNIQUE({})",
145                quote_ident(unique.name()),
146                cols
147            ));
148        }
149
150        // Check constraints
151        for check in self.check_constraints {
152            lines.push(format!(
153                "\tCONSTRAINT {} CHECK({})",
154                quote_ident(check.name()),
155                check.value
156            ));
157        }
158
159        sql.push_str(&lines.join(",\n"));
160        sql.push_str("\n)");
161
162        // Table options
163        let mut options = Vec::new();
164        if self.table.without_rowid {
165            options.push("WITHOUT ROWID");
166        }
167        if self.table.strict {
168            options.push("STRICT");
169        }
170        if !options.is_empty() {
171            let _ = write!(sql, " {}", options.join(", "));
172        }
173
174        sql.push(';');
175        sql
176    }
177
178    /// Generate DROP TABLE SQL
179    #[must_use]
180    pub fn drop_table_sql(&self) -> String {
181        format!("DROP TABLE {};", quote_ident(self.table.name()))
182    }
183}
184
185// =============================================================================
186// Column SQL Generation
187// =============================================================================
188
189impl Column {
190    /// Generate the column definition SQL (without leading/trailing punctuation)
191    #[must_use]
192    pub fn to_column_sql(&self, inline_pk: bool, inline_unique: bool) -> String {
193        let mut sql = format!(
194            "{} {}",
195            quote_ident(self.name()),
196            self.sql_type().to_uppercase()
197        );
198
199        if inline_pk {
200            sql.push_str(" PRIMARY KEY");
201            if self.autoincrement.unwrap_or(false) {
202                sql.push_str(" AUTOINCREMENT");
203            }
204        } else if self.autoincrement.unwrap_or(false) {
205            sql.push_str(" AUTOINCREMENT");
206        }
207
208        if let Some(default) = self.default.as_ref() {
209            let _ = write!(sql, " DEFAULT {default}");
210        }
211
212        if let Some(generated) = &self.generated {
213            sql.push_str(&generated.to_sql());
214        }
215
216        // NOT NULL - skip for INTEGER PRIMARY KEY (allows NULL by default in SQLite)
217        if self.not_null && !(inline_pk && self.sql_type().to_lowercase().starts_with("int")) {
218            sql.push_str(" NOT NULL");
219        }
220
221        if inline_unique && !inline_pk {
222            sql.push_str(" UNIQUE");
223        }
224
225        // COLLATE applies to comparisons on this column. SQLite parses it as a
226        // column-constraint, so it follows other inline constraints.
227        if let Some(collate) = self.collate.as_ref() {
228            let _ = write!(sql, " COLLATE {collate}");
229        }
230
231        sql
232    }
233
234    /// Generate ADD COLUMN SQL
235    #[must_use]
236    pub fn add_column_sql(&self) -> String {
237        format!(
238            "ALTER TABLE {} ADD COLUMN {};",
239            quote_ident(self.table()),
240            self.to_column_sql(false, false)
241        )
242    }
243
244    /// Generate DROP COLUMN SQL
245    #[must_use]
246    pub fn drop_column_sql(&self) -> String {
247        format!(
248            "ALTER TABLE {} DROP COLUMN {};",
249            quote_ident(self.table()),
250            quote_ident(self.name())
251        )
252    }
253}
254
255// =============================================================================
256// Generated Column SQL
257// =============================================================================
258
259impl Generated {
260    /// Generate the GENERATED clause SQL
261    #[must_use]
262    pub fn to_sql(&self) -> String {
263        let gen_type = match self.gen_type {
264            GeneratedType::Stored => "STORED",
265            GeneratedType::Virtual => "VIRTUAL",
266        };
267        format!(" GENERATED ALWAYS AS {} {}", self.expression, gen_type)
268    }
269}
270
271// =============================================================================
272// Foreign Key SQL Generation
273// =============================================================================
274
275impl ForeignKey {
276    /// Generate the CONSTRAINT ... FOREIGN KEY clause SQL
277    #[must_use]
278    pub fn to_constraint_sql(&self) -> String {
279        let from_cols = self
280            .columns
281            .iter()
282            .map(|c| quote_ident(c))
283            .collect::<Vec<_>>()
284            .join(", ");
285
286        let to_cols = self
287            .columns_to
288            .iter()
289            .map(|c| quote_ident(c))
290            .collect::<Vec<_>>()
291            .join(", ");
292
293        let mut sql = format!(
294            "CONSTRAINT {} FOREIGN KEY ({}) REFERENCES {}({})",
295            quote_ident(self.name()),
296            from_cols,
297            quote_ident(&self.table_to),
298            to_cols
299        );
300
301        if let Some(on_update) = self.on_update.as_ref()
302            && on_update != "NO ACTION"
303        {
304            let _ = write!(sql, " ON UPDATE {on_update}");
305        }
306
307        if let Some(on_delete) = self.on_delete.as_ref()
308            && on_delete != "NO ACTION"
309        {
310            let _ = write!(sql, " ON DELETE {on_delete}");
311        }
312
313        sql
314    }
315
316    /// Generate ADD FOREIGN KEY SQL (via new table constraint)
317    #[must_use]
318    pub fn add_fk_sql(&self) -> String {
319        // SQLite doesn't support ADD CONSTRAINT for foreign keys directly
320        // This would require table recreation
321        format!(
322            "-- SQLite requires table recreation to add foreign keys\n-- FK: {} on {}",
323            self.name(),
324            quote_ident(self.table())
325        )
326    }
327
328    /// Generate DROP FOREIGN KEY SQL (comment since `SQLite` doesn't support it)
329    #[must_use]
330    pub fn drop_fk_sql(&self) -> String {
331        format!(
332            "-- SQLite requires table recreation to drop foreign keys\n-- FK: {} on {}",
333            self.name(),
334            quote_ident(self.table())
335        )
336    }
337}
338
339// =============================================================================
340// Index SQL Generation
341// =============================================================================
342
343impl Index {
344    /// Generate CREATE INDEX SQL
345    #[must_use]
346    pub fn create_index_sql(&self) -> String {
347        let unique = if self.is_unique { "UNIQUE " } else { "" };
348
349        let columns = self
350            .columns
351            .iter()
352            .map(super::index::IndexColumn::to_sql)
353            .collect::<Vec<_>>()
354            .join(", ");
355
356        let mut sql = format!(
357            "CREATE {}INDEX {} ON {}({});",
358            unique,
359            quote_ident(self.name()),
360            quote_ident(self.table()),
361            columns
362        );
363
364        if let Some(where_clause) = self.where_clause.as_ref() {
365            // Remove trailing semicolon to add WHERE
366            sql.pop();
367            let _ = write!(sql, " WHERE {where_clause};");
368        }
369
370        sql
371    }
372
373    /// Generate DROP INDEX SQL
374    #[must_use]
375    pub fn drop_index_sql(&self) -> String {
376        format!("DROP INDEX {};", quote_ident(self.name()))
377    }
378}
379
380impl IndexColumnDef {
381    /// Generate the column reference for an index
382    #[must_use]
383    pub fn to_sql(&self) -> String {
384        if self.is_expression {
385            self.value.to_string()
386        } else {
387            quote_ident(self.value)
388        }
389    }
390}
391
392// =============================================================================
393// View SQL Generation
394// =============================================================================
395
396impl View {
397    /// Generate CREATE VIEW SQL
398    #[must_use]
399    pub fn create_view_sql(&self) -> String {
400        self.definition.as_ref().map_or_else(
401            || format!("-- View {} has no definition", quote_ident(self.name())),
402            |def| format!("CREATE VIEW {} AS {};", quote_ident(self.name()), def),
403        )
404    }
405
406    /// Generate DROP VIEW SQL
407    #[must_use]
408    pub fn drop_view_sql(&self) -> String {
409        format!("DROP VIEW {};", quote_ident(self.name()))
410    }
411}
412
413// =============================================================================
414// Table-level utilities
415// =============================================================================
416
417impl Table {
418    /// Generate DROP TABLE SQL
419    #[must_use]
420    pub fn drop_table_sql(&self) -> String {
421        format!("DROP TABLE {};", quote_ident(self.name()))
422    }
423
424    /// Generate RENAME TABLE SQL
425    #[must_use]
426    pub fn rename_table_sql(&self, new_name: &str) -> String {
427        format!(
428            "ALTER TABLE {} RENAME TO {};",
429            quote_ident(self.name()),
430            quote_ident(new_name)
431        )
432    }
433}
434
435// =============================================================================
436// Primary Key SQL Generation
437// =============================================================================
438
439impl PrimaryKey {
440    /// Generate the PRIMARY KEY constraint clause
441    #[must_use]
442    pub fn to_constraint_sql(&self) -> String {
443        let cols = self
444            .columns
445            .iter()
446            .map(|c| quote_ident(c))
447            .collect::<Vec<_>>()
448            .join(", ");
449
450        format!(
451            "CONSTRAINT {} PRIMARY KEY({})",
452            quote_ident(self.name()),
453            cols
454        )
455    }
456}
457
458// =============================================================================
459// Unique Constraint SQL Generation
460// =============================================================================
461
462impl UniqueConstraint {
463    /// Generate the UNIQUE constraint clause
464    #[must_use]
465    pub fn to_constraint_sql(&self) -> String {
466        let cols = self
467            .columns
468            .iter()
469            .map(|c| quote_ident(c))
470            .collect::<Vec<_>>()
471            .join(", ");
472
473        format!("CONSTRAINT {} UNIQUE({})", quote_ident(self.name()), cols)
474    }
475}
476
477// =============================================================================
478// Check Constraint SQL Generation
479// =============================================================================
480
481impl CheckConstraint {
482    /// Generate the CHECK constraint clause
483    #[must_use]
484    pub fn to_constraint_sql(&self) -> String {
485        format!(
486            "CONSTRAINT {} CHECK({})",
487            quote_ident(self.name()),
488            self.value
489        )
490    }
491}
492
493#[cfg(test)]
494mod tests {
495    use super::*;
496    use crate::sqlite::ddl::{
497        ColumnDef, ForeignKeyDef, IndexColumnDef, IndexDef, PrimaryKeyDef, ReferentialAction,
498        TableDef,
499    };
500    use std::borrow::Cow;
501
502    #[test]
503    fn test_simple_create_table() {
504        let table = TableDef::new("users").into_table();
505        let columns = [
506            ColumnDef::new("users", "id", "INTEGER")
507                .primary_key()
508                .autoincrement()
509                .into_column(),
510            ColumnDef::new("users", "name", "TEXT")
511                .not_null()
512                .into_column(),
513            ColumnDef::new("users", "email", "TEXT").into_column(),
514        ];
515        const PK_COLS: &[Cow<'static, str>] = &[Cow::Borrowed("id")];
516        let pk = PrimaryKeyDef::new("users", "users_pk")
517            .columns(PK_COLS)
518            .into_primary_key();
519
520        let sql = TableSql::new(&table)
521            .columns(&columns)
522            .primary_key(Some(&pk))
523            .create_table_sql();
524
525        assert!(sql.contains("CREATE TABLE `users`"));
526        assert!(sql.contains("`id` INTEGER PRIMARY KEY AUTOINCREMENT"));
527        assert!(sql.contains("`name` TEXT NOT NULL"));
528        assert!(sql.contains("`email` TEXT"));
529    }
530
531    #[test]
532    fn test_table_with_foreign_key() {
533        let table = TableDef::new("posts").into_table();
534        let columns = [
535            ColumnDef::new("posts", "id", "INTEGER")
536                .primary_key()
537                .into_column(),
538            ColumnDef::new("posts", "user_id", "INTEGER")
539                .not_null()
540                .into_column(),
541        ];
542        const PK_COLS: &[Cow<'static, str>] = &[Cow::Borrowed("id")];
543        let pk = PrimaryKeyDef::new("posts", "posts_pk")
544            .columns(PK_COLS)
545            .into_primary_key();
546        const FK_COLS: &[Cow<'static, str>] = &[Cow::Borrowed("user_id")];
547        const FK_REFS: &[Cow<'static, str>] = &[Cow::Borrowed("id")];
548        let fks = [ForeignKeyDef::new("posts", "posts_user_id_fk")
549            .columns(FK_COLS)
550            .references("users", FK_REFS)
551            .on_delete(ReferentialAction::Cascade)
552            .into_foreign_key()];
553
554        let sql = TableSql::new(&table)
555            .columns(&columns)
556            .primary_key(Some(&pk))
557            .foreign_keys(&fks)
558            .create_table_sql();
559
560        assert!(sql.contains("FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)"));
561        assert!(sql.contains("ON DELETE CASCADE"));
562    }
563
564    #[test]
565    fn test_create_index() {
566        const COLS: &[IndexColumnDef] = &[IndexColumnDef::new("email")];
567        let index = IndexDef::new("users", "users_email_idx")
568            .columns(COLS)
569            .unique()
570            .into_index();
571
572        let sql = index.create_index_sql();
573        assert_eq!(
574            sql,
575            "CREATE UNIQUE INDEX `users_email_idx` ON `users`(`email`);"
576        );
577    }
578
579    #[test]
580    fn test_strict_without_rowid() {
581        let table = TableDef::new("data").strict().without_rowid().into_table();
582        let columns = [ColumnDef::new("data", "key", "TEXT")
583            .primary_key()
584            .not_null()
585            .into_column()];
586        const PK_COLS: &[Cow<'static, str>] = &[Cow::Borrowed("key")];
587        let pk = PrimaryKeyDef::new("data", "data_pk")
588            .columns(PK_COLS)
589            .into_primary_key();
590
591        let sql = TableSql::new(&table)
592            .columns(&columns)
593            .primary_key(Some(&pk))
594            .create_table_sql();
595
596        assert!(sql.ends_with("WITHOUT ROWID, STRICT;"));
597    }
598}
drizzle_types/sqlite/ddl/sql.rs

drizzle_types/sqlite/ddl/
sql.rs
