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

drizzle_types/sqlite/ddl/
sql.rs
