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

drizzle_types/sqlite/ddl/
sql.rs
