sqlmodel_schema/
create.rs

1//! CREATE TABLE statement builder.
2
3use sqlmodel_core::{FieldInfo, InheritanceStrategy, Model, quote_ident};
4use std::marker::PhantomData;
5
6/// Builder for CREATE TABLE statements.
7#[derive(Debug)]
8pub struct CreateTable<M: Model> {
9    if_not_exists: bool,
10    _marker: PhantomData<M>,
11}
12
13impl<M: Model> CreateTable<M> {
14    /// Create a new CREATE TABLE builder.
15    pub fn new() -> Self {
16        Self {
17            if_not_exists: false,
18            _marker: PhantomData,
19        }
20    }
21
22    /// Add IF NOT EXISTS clause.
23    pub fn if_not_exists(mut self) -> Self {
24        self.if_not_exists = true;
25        self
26    }
27
28    /// Build the CREATE TABLE SQL.
29    ///
30    /// # Inheritance Handling
31    ///
32    /// - **Single Table Inheritance (child)**: Returns empty string (child uses parent's table)
33    /// - **Joined Table Inheritance (child)**: Adds FK constraint to parent table
34    /// - **Concrete Table Inheritance**: Each model gets independent table (normal behavior)
35    pub fn build(&self) -> String {
36        let inheritance = M::inheritance();
37
38        // Single table inheritance: child models don't create their own table
39        // They share the parent's table and are distinguished by the discriminator column
40        if inheritance.strategy == InheritanceStrategy::None
41            && inheritance.parent.is_some()
42            && inheritance.discriminator_value.is_some()
43        {
44            // This is a single table inheritance child - no table to create
45            // Child-specific columns are handled by higher-level schema planning (e.g. SchemaBuilder)
46            return String::new();
47        }
48
49        let mut sql = String::from("CREATE TABLE ");
50
51        if self.if_not_exists {
52            sql.push_str("IF NOT EXISTS ");
53        }
54
55        sql.push_str(&quote_ident(M::TABLE_NAME));
56        sql.push_str(" (\n");
57
58        let fields = M::fields();
59        let mut column_defs = Vec::new();
60        let mut constraints = Vec::new();
61
62        // SQLite auto-increment requires `INTEGER PRIMARY KEY` on the column itself.
63        // When we detect a single-column PK marked `auto_increment`, we embed the PK
64        // constraint in the column definition and skip the table-level PK clause.
65        let embedded_autoinc_pk: Option<&str> = {
66            let pk_cols = M::PRIMARY_KEY;
67            if pk_cols.len() == 1 {
68                let pk = pk_cols[0];
69                let has_autoinc_pk = fields
70                    .iter()
71                    .any(|f| f.column_name == pk && f.primary_key && f.auto_increment);
72                if has_autoinc_pk { Some(pk) } else { None }
73            } else {
74                None
75            }
76        };
77
78        for field in fields {
79            let embed_pk = embedded_autoinc_pk.is_some_and(|col| {
80                col == field.column_name && field.primary_key && field.auto_increment
81            });
82            column_defs.push(self.column_definition(field, embed_pk));
83
84            // Collect constraints
85            if field.unique && !field.primary_key {
86                let constraint_name = format!("uk_{}_{}", M::TABLE_NAME, field.column_name);
87                let constraint = format!(
88                    "CONSTRAINT {} UNIQUE ({})",
89                    quote_ident(&constraint_name),
90                    quote_ident(field.column_name)
91                );
92                constraints.push(constraint);
93            }
94
95            if let Some(fk) = field.foreign_key {
96                let parts: Vec<&str> = fk.split('.').collect();
97                if parts.len() == 2 {
98                    let constraint_name = format!("fk_{}_{}", M::TABLE_NAME, field.column_name);
99                    let mut fk_sql = format!(
100                        "CONSTRAINT {} FOREIGN KEY ({}) REFERENCES {}({})",
101                        quote_ident(&constraint_name),
102                        quote_ident(field.column_name),
103                        quote_ident(parts[0]),
104                        quote_ident(parts[1])
105                    );
106
107                    // Add ON DELETE action if specified
108                    if let Some(on_delete) = field.on_delete {
109                        fk_sql.push_str(" ON DELETE ");
110                        fk_sql.push_str(on_delete.as_sql());
111                    }
112
113                    // Add ON UPDATE action if specified
114                    if let Some(on_update) = field.on_update {
115                        fk_sql.push_str(" ON UPDATE ");
116                        fk_sql.push_str(on_update.as_sql());
117                    }
118
119                    constraints.push(fk_sql);
120                }
121            }
122        }
123
124        // For joined table inheritance child models, add FK to parent table
125        if inheritance.strategy == InheritanceStrategy::Joined {
126            if let Some(parent_table) = inheritance.parent {
127                // In joined inheritance, the child's primary key columns are also a foreign key
128                // to the parent table's primary key columns (same column names).
129                let pk_cols = M::PRIMARY_KEY;
130                if !pk_cols.is_empty() {
131                    let quoted_child_cols: Vec<String> =
132                        pk_cols.iter().map(|c| quote_ident(c)).collect();
133                    let quoted_parent_cols = quoted_child_cols.clone();
134                    let constraint_name = format!("fk_{}_parent", M::TABLE_NAME);
135                    let fk_sql = format!(
136                        "CONSTRAINT {} FOREIGN KEY ({}) REFERENCES {}({}) ON DELETE CASCADE",
137                        quote_ident(&constraint_name),
138                        quoted_child_cols.join(", "),
139                        quote_ident(parent_table),
140                        quoted_parent_cols.join(", ")
141                    );
142                    constraints.push(fk_sql);
143                }
144            }
145        }
146
147        // Add primary key constraint (unless embedded for SQLite-style auto-increment single PK).
148        let pk_cols = M::PRIMARY_KEY;
149        if !pk_cols.is_empty() {
150            let embedded = embedded_autoinc_pk.is_some_and(|pk| pk_cols == [pk]);
151            if !embedded {
152                let quoted_pk: Vec<String> = pk_cols.iter().map(|c| quote_ident(c)).collect();
153                let mut constraint = String::new();
154                constraint.push_str("PRIMARY KEY (");
155                constraint.push_str(&quoted_pk.join(", "));
156                constraint.push(')');
157                constraints.insert(0, constraint);
158            }
159        }
160
161        // Combine column definitions and constraints
162        let all_parts: Vec<_> = column_defs.into_iter().chain(constraints).collect();
163
164        sql.push_str(&all_parts.join(",\n  "));
165        sql.push_str("\n)");
166
167        sql
168    }
169
170    /// Check if this model should skip table creation.
171    ///
172    /// Returns true for single table inheritance child models, which
173    /// share their parent's table rather than having their own.
174    pub fn should_skip_table_creation() -> bool {
175        let inheritance = M::inheritance();
176        // Single table inheritance child: has parent + discriminator_value but no explicit strategy
177        inheritance.strategy == InheritanceStrategy::None
178            && inheritance.parent.is_some()
179            && inheritance.discriminator_value.is_some()
180    }
181
182    fn column_definition(&self, field: &FieldInfo, embed_primary_key: bool) -> String {
183        let sql_type = if embed_primary_key {
184            // Required by SQLite for rowid-backed autoincrement behavior.
185            "INTEGER".to_string()
186        } else {
187            field.effective_sql_type()
188        };
189        let mut def = String::from("  ");
190        def.push_str(&quote_ident(field.column_name));
191        def.push(' ');
192        def.push_str(&sql_type);
193
194        if embed_primary_key {
195            def.push_str(" PRIMARY KEY");
196        } else if !field.nullable && !field.auto_increment {
197            def.push_str(" NOT NULL");
198        }
199
200        if let Some(default) = field.default {
201            def.push_str(" DEFAULT ");
202            def.push_str(default);
203        }
204
205        def
206    }
207}
208
209impl<M: Model> Default for CreateTable<M> {
210    fn default() -> Self {
211        Self::new()
212    }
213}
214
215#[cfg(test)]
216mod tests {
217    use super::*;
218    use sqlmodel_core::{FieldInfo, Row, SqlType, Value};
219
220    // Test model for CREATE TABLE generation
221    struct TestHero;
222
223    impl Model for TestHero {
224        const TABLE_NAME: &'static str = "heroes";
225        const PRIMARY_KEY: &'static [&'static str] = &["id"];
226
227        fn fields() -> &'static [FieldInfo] {
228            static FIELDS: &[FieldInfo] = &[
229                FieldInfo::new("id", "id", SqlType::BigInt)
230                    .nullable(true)
231                    .primary_key(true)
232                    .auto_increment(true),
233                FieldInfo::new("name", "name", SqlType::Text).unique(true),
234                FieldInfo::new("age", "age", SqlType::Integer).nullable(true),
235                FieldInfo::new("team_id", "team_id", SqlType::BigInt)
236                    .nullable(true)
237                    .foreign_key("teams.id"),
238            ];
239            FIELDS
240        }
241
242        fn to_row(&self) -> Vec<(&'static str, Value)> {
243            vec![]
244        }
245
246        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
247            Ok(TestHero)
248        }
249
250        fn primary_key_value(&self) -> Vec<Value> {
251            vec![]
252        }
253
254        fn is_new(&self) -> bool {
255            true
256        }
257    }
258
259    #[test]
260    fn test_create_table_basic() {
261        let sql = CreateTable::<TestHero>::new().build();
262        assert!(sql.starts_with("CREATE TABLE \"heroes\""));
263        assert!(sql.contains("\"id\" INTEGER PRIMARY KEY"));
264        assert!(sql.contains("\"name\" TEXT NOT NULL"));
265        assert!(sql.contains("\"age\" INTEGER"));
266        assert!(sql.contains("\"team_id\" BIGINT"));
267    }
268
269    #[test]
270    fn test_create_table_if_not_exists() {
271        let sql = CreateTable::<TestHero>::new().if_not_exists().build();
272        assert!(sql.starts_with("CREATE TABLE IF NOT EXISTS \"heroes\""));
273    }
274
275    #[test]
276    fn test_create_table_primary_key() {
277        let sql = CreateTable::<TestHero>::new().build();
278        assert!(sql.contains("\"id\" INTEGER PRIMARY KEY"));
279    }
280
281    #[test]
282    fn test_create_table_unique_constraint() {
283        let sql = CreateTable::<TestHero>::new().build();
284        assert!(sql.contains("CONSTRAINT \"uk_heroes_name\" UNIQUE (\"name\")"));
285    }
286
287    #[test]
288    fn test_create_table_foreign_key() {
289        let sql = CreateTable::<TestHero>::new().build();
290        assert!(sql.contains("FOREIGN KEY (\"team_id\") REFERENCES \"teams\"(\"id\")"));
291    }
292
293    #[test]
294    fn test_create_table_auto_increment() {
295        let sql = CreateTable::<TestHero>::new().build();
296        assert!(sql.contains("\"id\" INTEGER PRIMARY KEY"));
297    }
298
299    #[test]
300    fn test_schema_builder_single_table() {
301        let statements = SchemaBuilder::new().create_table::<TestHero>().build();
302        assert_eq!(statements.len(), 1);
303        assert!(statements[0].contains("CREATE TABLE IF NOT EXISTS \"heroes\""));
304    }
305
306    #[test]
307    fn test_schema_builder_with_index() {
308        let statements = SchemaBuilder::new()
309            .create_table::<TestHero>()
310            .create_index("idx_hero_name", "heroes", &["name"], false)
311            .build();
312        assert_eq!(statements.len(), 2);
313        assert!(
314            statements[1]
315                .contains("CREATE INDEX IF NOT EXISTS \"idx_hero_name\" ON \"heroes\" (\"name\")")
316        );
317    }
318
319    #[test]
320    fn test_schema_builder_unique_index() {
321        let statements = SchemaBuilder::new()
322            .create_index("idx_hero_email", "heroes", &["email"], true)
323            .build();
324        assert!(statements[0].contains("CREATE UNIQUE INDEX"));
325    }
326
327    #[test]
328    fn test_schema_builder_raw_sql() {
329        let statements = SchemaBuilder::new()
330            .raw("ALTER TABLE heroes ADD COLUMN power TEXT")
331            .build();
332        assert_eq!(statements.len(), 1);
333        assert_eq!(statements[0], "ALTER TABLE heroes ADD COLUMN power TEXT");
334    }
335
336    #[test]
337    fn test_schema_builder_multi_column_index() {
338        let statements = SchemaBuilder::new()
339            .create_index("idx_hero_name_age", "heroes", &["name", "age"], false)
340            .build();
341        assert!(statements[0].contains("ON \"heroes\" (\"name\", \"age\")"));
342    }
343
344    // Test model with default values
345    struct TestWithDefault;
346
347    impl Model for TestWithDefault {
348        const TABLE_NAME: &'static str = "settings";
349        const PRIMARY_KEY: &'static [&'static str] = &["id"];
350
351        fn fields() -> &'static [FieldInfo] {
352            static FIELDS: &[FieldInfo] = &[
353                FieldInfo::new("id", "id", SqlType::Integer).primary_key(true),
354                FieldInfo::new("is_active", "is_active", SqlType::Boolean).default("true"),
355            ];
356            FIELDS
357        }
358
359        fn to_row(&self) -> Vec<(&'static str, Value)> {
360            vec![]
361        }
362
363        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
364            Ok(TestWithDefault)
365        }
366
367        fn primary_key_value(&self) -> Vec<Value> {
368            vec![]
369        }
370
371        fn is_new(&self) -> bool {
372            true
373        }
374    }
375
376    #[test]
377    fn test_create_table_default_value() {
378        let sql = CreateTable::<TestWithDefault>::new().build();
379        assert!(sql.contains("\"is_active\" BOOLEAN NOT NULL DEFAULT true"));
380    }
381
382    // Test model with ON DELETE CASCADE
383    struct TestWithOnDelete;
384
385    impl Model for TestWithOnDelete {
386        const TABLE_NAME: &'static str = "comments";
387        const PRIMARY_KEY: &'static [&'static str] = &["id"];
388
389        fn fields() -> &'static [FieldInfo] {
390            use sqlmodel_core::ReferentialAction;
391            static FIELDS: &[FieldInfo] = &[
392                FieldInfo::new("id", "id", SqlType::BigInt)
393                    .nullable(true)
394                    .primary_key(true)
395                    .auto_increment(true),
396                FieldInfo::new("post_id", "post_id", SqlType::BigInt)
397                    .foreign_key("posts.id")
398                    .on_delete(ReferentialAction::Cascade)
399                    .on_update(ReferentialAction::NoAction),
400            ];
401            FIELDS
402        }
403
404        fn to_row(&self) -> Vec<(&'static str, Value)> {
405            vec![]
406        }
407
408        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
409            Ok(TestWithOnDelete)
410        }
411
412        fn primary_key_value(&self) -> Vec<Value> {
413            vec![]
414        }
415
416        fn is_new(&self) -> bool {
417            true
418        }
419    }
420
421    #[test]
422    fn test_create_table_on_delete_cascade() {
423        let sql = CreateTable::<TestWithOnDelete>::new().build();
424        assert!(sql.contains("FOREIGN KEY (\"post_id\") REFERENCES \"posts\"(\"id\") ON DELETE CASCADE ON UPDATE NO ACTION"));
425    }
426
427    #[test]
428    fn test_referential_action_as_sql() {
429        use sqlmodel_core::ReferentialAction;
430        assert_eq!(ReferentialAction::NoAction.as_sql(), "NO ACTION");
431        assert_eq!(ReferentialAction::Restrict.as_sql(), "RESTRICT");
432        assert_eq!(ReferentialAction::Cascade.as_sql(), "CASCADE");
433        assert_eq!(ReferentialAction::SetNull.as_sql(), "SET NULL");
434        assert_eq!(ReferentialAction::SetDefault.as_sql(), "SET DEFAULT");
435    }
436
437    #[test]
438    fn test_referential_action_from_str() {
439        use sqlmodel_core::ReferentialAction;
440        assert_eq!(
441            ReferentialAction::from_str("CASCADE"),
442            Some(ReferentialAction::Cascade)
443        );
444        assert_eq!(
445            ReferentialAction::from_str("cascade"),
446            Some(ReferentialAction::Cascade)
447        );
448        assert_eq!(
449            ReferentialAction::from_str("SET NULL"),
450            Some(ReferentialAction::SetNull)
451        );
452        assert_eq!(
453            ReferentialAction::from_str("SETNULL"),
454            Some(ReferentialAction::SetNull)
455        );
456        assert_eq!(ReferentialAction::from_str("invalid"), None);
457    }
458
459    #[derive(sqlmodel_macros::Model)]
460    struct TestDerivedSqlTypeOverride {
461        #[sqlmodel(primary_key)]
462        id: i64,
463
464        #[sqlmodel(sql_type = "TIMESTAMP WITH TIME ZONE")]
465        created_at: String,
466    }
467
468    #[test]
469    fn test_create_table_sql_type_attribute_preserves_raw_string() {
470        let sql = CreateTable::<TestDerivedSqlTypeOverride>::new().build();
471        assert!(sql.contains("\"created_at\" TIMESTAMP WITH TIME ZONE NOT NULL"));
472    }
473
474    // Test model with sql_type_override
475    struct TestWithSqlTypeOverride;
476
477    impl Model for TestWithSqlTypeOverride {
478        const TABLE_NAME: &'static str = "products";
479        const PRIMARY_KEY: &'static [&'static str] = &["id"];
480
481        fn fields() -> &'static [FieldInfo] {
482            static FIELDS: &[FieldInfo] = &[
483                FieldInfo::new("id", "id", SqlType::BigInt)
484                    .nullable(true)
485                    .primary_key(true)
486                    .auto_increment(true),
487                FieldInfo::new("price", "price", SqlType::Real).sql_type_override("DECIMAL(10,2)"),
488                FieldInfo::new("sku", "sku", SqlType::Text)
489                    .sql_type_override("VARCHAR(50)")
490                    .unique(true),
491            ];
492            FIELDS
493        }
494
495        fn to_row(&self) -> Vec<(&'static str, Value)> {
496            vec![]
497        }
498
499        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
500            Ok(TestWithSqlTypeOverride)
501        }
502
503        fn primary_key_value(&self) -> Vec<Value> {
504            vec![]
505        }
506
507        fn is_new(&self) -> bool {
508            true
509        }
510    }
511
512    #[test]
513    fn test_create_table_sql_type_override() {
514        let sql = CreateTable::<TestWithSqlTypeOverride>::new().build();
515        // Override types should be used instead of base types
516        assert!(sql.contains("\"price\" DECIMAL(10,2) NOT NULL"));
517        assert!(sql.contains("\"sku\" VARCHAR(50) NOT NULL"));
518        // Non-overridden types use sql_type.sql_name()
519        assert!(sql.contains("\"id\" BIGINT"));
520    }
521
522    #[test]
523    fn test_field_info_effective_sql_type() {
524        let field_no_override = FieldInfo::new("col", "col", SqlType::Integer);
525        assert_eq!(field_no_override.effective_sql_type(), "INTEGER");
526
527        let field_with_override =
528            FieldInfo::new("col", "col", SqlType::Text).sql_type_override("VARCHAR(255)");
529        assert_eq!(field_with_override.effective_sql_type(), "VARCHAR(255)");
530    }
531
532    #[test]
533    fn test_quote_ident_escapes_embedded_quotes() {
534        // Simple identifier - no escaping needed
535        assert_eq!(quote_ident("simple"), "\"simple\"");
536
537        // Identifier with embedded quote - must be doubled
538        assert_eq!(quote_ident("with\"quote"), "\"with\"\"quote\"");
539
540        // Identifier with multiple quotes
541        assert_eq!(quote_ident("a\"b\"c"), "\"a\"\"b\"\"c\"");
542
543        // Already-doubled quotes stay doubled-doubled
544        assert_eq!(quote_ident("test\"\"name"), "\"test\"\"\"\"name\"");
545    }
546
547    #[test]
548    fn test_schema_builder_index_with_special_chars() {
549        let statements = SchemaBuilder::new()
550            .create_index("idx\"test", "my\"table", &["col\"name"], false)
551            .build();
552        // Verify quotes are escaped (doubled)
553        assert!(statements[0].contains("\"idx\"\"test\""));
554        assert!(statements[0].contains("\"my\"\"table\""));
555        assert!(statements[0].contains("\"col\"\"name\""));
556    }
557
558    // ================================================================================
559    // DDL Identifier Quoting Integration Tests
560    // ================================================================================
561
562    // Test model with SQL keyword table name
563    struct TestOrderTable;
564
565    impl Model for TestOrderTable {
566        const TABLE_NAME: &'static str = "order"; // SQL keyword!
567        const PRIMARY_KEY: &'static [&'static str] = &["id"];
568
569        fn fields() -> &'static [FieldInfo] {
570            static FIELDS: &[FieldInfo] = &[
571                FieldInfo::new("id", "id", SqlType::BigInt)
572                    .nullable(true)
573                    .primary_key(true),
574                FieldInfo::new("select", "select", SqlType::Text), // SQL keyword column!
575                FieldInfo::new("from", "from", SqlType::Text),     // SQL keyword column!
576            ];
577            FIELDS
578        }
579
580        fn to_row(&self) -> Vec<(&'static str, Value)> {
581            vec![]
582        }
583
584        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
585            Ok(TestOrderTable)
586        }
587
588        fn primary_key_value(&self) -> Vec<Value> {
589            vec![]
590        }
591
592        fn is_new(&self) -> bool {
593            true
594        }
595    }
596
597    #[test]
598    fn test_create_table_with_keyword_table_name() {
599        let sql = CreateTable::<TestOrderTable>::new().build();
600        // Table name "order" must be quoted
601        assert!(sql.contains("CREATE TABLE \"order\""));
602        // Column names that are keywords must be quoted
603        assert!(sql.contains("\"select\" TEXT NOT NULL"));
604        assert!(sql.contains("\"from\" TEXT NOT NULL"));
605        assert!(sql.contains("\"id\" BIGINT"));
606        assert!(sql.contains("PRIMARY KEY (\"id\")"));
607    }
608
609    #[test]
610    fn test_schema_builder_with_keyword_table_name() {
611        let statements = SchemaBuilder::new()
612            .create_table::<TestOrderTable>()
613            .build();
614        assert!(statements[0].contains("CREATE TABLE IF NOT EXISTS \"order\""));
615        assert!(statements[0].contains("\"select\" TEXT NOT NULL"));
616    }
617
618    #[test]
619    fn test_create_index_with_keyword_names() {
620        let statements = SchemaBuilder::new()
621            .create_index("idx_order_select", "order", &["select", "from"], false)
622            .build();
623        // All identifiers must be quoted
624        assert!(statements[0].contains("\"idx_order_select\""));
625        assert!(statements[0].contains("ON \"order\""));
626        assert!(statements[0].contains("(\"select\", \"from\")"));
627    }
628
629    // Test model with embedded quotes in table/column names
630    struct TestQuotedNames;
631
632    impl Model for TestQuotedNames {
633        const TABLE_NAME: &'static str = "my\"table"; // Embedded quote!
634        const PRIMARY_KEY: &'static [&'static str] = &["pk"];
635
636        fn fields() -> &'static [FieldInfo] {
637            static FIELDS: &[FieldInfo] = &[
638                FieldInfo::new("pk", "pk", SqlType::BigInt).primary_key(true),
639                FieldInfo::new("data\"col", "data\"col", SqlType::Text), // Embedded quote!
640            ];
641            FIELDS
642        }
643
644        fn to_row(&self) -> Vec<(&'static str, Value)> {
645            vec![]
646        }
647
648        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
649            Ok(TestQuotedNames)
650        }
651
652        fn primary_key_value(&self) -> Vec<Value> {
653            vec![]
654        }
655
656        fn is_new(&self) -> bool {
657            true
658        }
659    }
660
661    #[test]
662    fn test_create_table_with_embedded_quotes() {
663        let sql = CreateTable::<TestQuotedNames>::new().build();
664        // Embedded quotes must be doubled
665        assert!(sql.contains("CREATE TABLE \"my\"\"table\""));
666        assert!(sql.contains("\"data\"\"col\" TEXT NOT NULL"));
667        // Primary key also needs quote escaping
668        assert!(sql.contains("PRIMARY KEY (\"pk\")"));
669    }
670
671    // Test model with unicode characters
672    struct TestUnicodeTable;
673
674    impl Model for TestUnicodeTable {
675        const TABLE_NAME: &'static str = "用户表"; // Chinese "user table"
676        const PRIMARY_KEY: &'static [&'static str] = &["id"];
677
678        fn fields() -> &'static [FieldInfo] {
679            static FIELDS: &[FieldInfo] = &[
680                FieldInfo::new("id", "id", SqlType::BigInt).primary_key(true),
681                FieldInfo::new("名前", "名前", SqlType::Text), // Japanese "name"
682                FieldInfo::new("émoji_🦀", "émoji_🦀", SqlType::Text), // Emoji in column name
683            ];
684            FIELDS
685        }
686
687        fn to_row(&self) -> Vec<(&'static str, Value)> {
688            vec![]
689        }
690
691        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
692            Ok(TestUnicodeTable)
693        }
694
695        fn primary_key_value(&self) -> Vec<Value> {
696            vec![]
697        }
698
699        fn is_new(&self) -> bool {
700            true
701        }
702    }
703
704    #[test]
705    fn test_create_table_with_unicode_names() {
706        let sql = CreateTable::<TestUnicodeTable>::new().build();
707        // Unicode should be preserved and quoted
708        assert!(sql.contains("CREATE TABLE \"用户表\""));
709        assert!(sql.contains("\"名前\" TEXT NOT NULL"));
710        assert!(sql.contains("\"émoji_🦀\" TEXT NOT NULL"));
711    }
712
713    // Test model with spaces in names
714    struct TestSpacedNames;
715
716    impl Model for TestSpacedNames {
717        const TABLE_NAME: &'static str = "my table";
718        const PRIMARY_KEY: &'static [&'static str] = &["my id"];
719
720        fn fields() -> &'static [FieldInfo] {
721            static FIELDS: &[FieldInfo] = &[
722                FieldInfo::new("my id", "my id", SqlType::BigInt).primary_key(true),
723                FieldInfo::new("full name", "full name", SqlType::Text),
724            ];
725            FIELDS
726        }
727
728        fn to_row(&self) -> Vec<(&'static str, Value)> {
729            vec![]
730        }
731
732        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
733            Ok(TestSpacedNames)
734        }
735
736        fn primary_key_value(&self) -> Vec<Value> {
737            vec![]
738        }
739
740        fn is_new(&self) -> bool {
741            true
742        }
743    }
744
745    #[test]
746    fn test_create_table_with_spaces_in_names() {
747        let sql = CreateTable::<TestSpacedNames>::new().build();
748        // Spaces must be preserved within quotes
749        assert!(sql.contains("CREATE TABLE \"my table\""));
750        assert!(sql.contains("\"my id\" BIGINT"));
751        assert!(sql.contains("\"full name\" TEXT NOT NULL"));
752        assert!(sql.contains("PRIMARY KEY (\"my id\")"));
753    }
754
755    // Test foreign key with keyword table reference
756    struct TestFkToKeyword;
757
758    impl Model for TestFkToKeyword {
759        const TABLE_NAME: &'static str = "user_orders";
760        const PRIMARY_KEY: &'static [&'static str] = &["id"];
761
762        fn fields() -> &'static [FieldInfo] {
763            static FIELDS: &[FieldInfo] = &[
764                FieldInfo::new("id", "id", SqlType::BigInt)
765                    .nullable(true)
766                    .primary_key(true),
767                FieldInfo::new("order_id", "order_id", SqlType::BigInt)
768                    .nullable(true)
769                    .foreign_key("order.id"), // FK to keyword table!
770            ];
771            FIELDS
772        }
773
774        fn to_row(&self) -> Vec<(&'static str, Value)> {
775            vec![]
776        }
777
778        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
779            Ok(TestFkToKeyword)
780        }
781
782        fn primary_key_value(&self) -> Vec<Value> {
783            vec![]
784        }
785
786        fn is_new(&self) -> bool {
787            true
788        }
789    }
790
791    #[test]
792    fn test_foreign_key_to_keyword_table() {
793        let sql = CreateTable::<TestFkToKeyword>::new().build();
794        // FK reference must quote the keyword table name
795        assert!(sql.contains("FOREIGN KEY (\"order_id\") REFERENCES \"order\"(\"id\")"));
796    }
797
798    // Test unique constraint with keyword column name
799    struct TestUniqueKeyword;
800
801    impl Model for TestUniqueKeyword {
802        const TABLE_NAME: &'static str = "items";
803        const PRIMARY_KEY: &'static [&'static str] = &["id"];
804
805        fn fields() -> &'static [FieldInfo] {
806            static FIELDS: &[FieldInfo] = &[
807                FieldInfo::new("id", "id", SqlType::BigInt).primary_key(true),
808                FieldInfo::new("index", "index", SqlType::Integer).unique(true), // keyword!
809            ];
810            FIELDS
811        }
812
813        fn to_row(&self) -> Vec<(&'static str, Value)> {
814            vec![]
815        }
816
817        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
818            Ok(TestUniqueKeyword)
819        }
820
821        fn primary_key_value(&self) -> Vec<Value> {
822            vec![]
823        }
824
825        fn is_new(&self) -> bool {
826            true
827        }
828    }
829
830    #[test]
831    fn test_unique_constraint_with_keyword_column() {
832        let sql = CreateTable::<TestUniqueKeyword>::new().build();
833        // Unique constraint with keyword column name
834        assert!(sql.contains("CONSTRAINT \"uk_items_index\" UNIQUE (\"index\")"));
835        assert!(sql.contains("\"index\" INTEGER NOT NULL"));
836    }
837
838    // Edge cases: empty string, single quote, backslash
839    #[test]
840    fn test_quote_ident_edge_cases() {
841        // Empty string
842        assert_eq!(quote_ident(""), "\"\"");
843
844        // Single character
845        assert_eq!(quote_ident("x"), "\"x\"");
846
847        // Just a quote
848        assert_eq!(quote_ident("\""), "\"\"\"\"");
849
850        // Backslash (should pass through)
851        assert_eq!(quote_ident("back\\slash"), "\"back\\slash\"");
852
853        // Multiple consecutive quotes
854        assert_eq!(quote_ident("\"\"\""), "\"\"\"\"\"\"\"\"");
855
856        // Mixed quotes and other chars
857        assert_eq!(quote_ident("a\"b\"c\"d"), "\"a\"\"b\"\"c\"\"d\"");
858    }
859
860    // Test that all SQL keywords are properly quoted
861    #[test]
862    fn test_various_sql_keywords_as_identifiers() {
863        // All these are SQL reserved words
864        let keywords = [
865            "select",
866            "from",
867            "where",
868            "order",
869            "group",
870            "by",
871            "having",
872            "insert",
873            "update",
874            "delete",
875            "create",
876            "drop",
877            "table",
878            "index",
879            "primary",
880            "foreign",
881            "key",
882            "references",
883            "constraint",
884            "unique",
885            "not",
886            "null",
887            "default",
888            "and",
889            "or",
890            "in",
891            "between",
892            "like",
893            "is",
894            "as",
895            "join",
896            "inner",
897            "outer",
898            "left",
899            "right",
900            "on",
901            "into",
902            "values",
903            "set",
904            "limit",
905            "offset",
906            "asc",
907            "desc",
908            "user",
909            "database",
910        ];
911
912        for keyword in keywords {
913            let quoted = quote_ident(keyword);
914            // Must be quoted with double quotes
915            assert!(
916                quoted.starts_with('"') && quoted.ends_with('"'),
917                "Keyword '{}' not properly quoted: {}",
918                keyword,
919                quoted
920            );
921            // Content should be the keyword itself
922            assert_eq!(
923                &quoted[1..quoted.len() - 1],
924                keyword,
925                "Keyword '{}' mangled in quoting",
926                keyword
927            );
928        }
929    }
930
931    // SchemaBuilder edge cases
932    #[test]
933    fn test_schema_builder_create_index_with_keywords() {
934        let stmts = SchemaBuilder::new()
935            .create_index("idx_user_select", "user", &["select"], true)
936            .build();
937        assert!(stmts[0].contains("CREATE UNIQUE INDEX IF NOT EXISTS \"idx_user_select\""));
938        assert!(stmts[0].contains("ON \"user\" (\"select\")"));
939    }
940
941    #[test]
942    fn test_schema_builder_multi_column_index_with_quotes() {
943        let stmts = SchemaBuilder::new()
944            .create_index("idx\"multi", "tbl\"name", &["col\"a", "col\"b"], false)
945            .build();
946        assert!(stmts[0].contains("\"idx\"\"multi\""));
947        assert!(stmts[0].contains("ON \"tbl\"\"name\""));
948        assert!(stmts[0].contains("(\"col\"\"a\", \"col\"\"b\")"));
949    }
950
951    // ================================================================================
952    // Table Inheritance Schema Generation Tests
953    // ================================================================================
954
955    use sqlmodel_core::InheritanceInfo;
956
957    // Single Table Inheritance Base Model
958    struct SingleTableBase;
959
960    impl Model for SingleTableBase {
961        const TABLE_NAME: &'static str = "employees";
962        const PRIMARY_KEY: &'static [&'static str] = &["id"];
963
964        fn fields() -> &'static [FieldInfo] {
965            static FIELDS: &[FieldInfo] = &[
966                FieldInfo::new("id", "id", SqlType::BigInt).primary_key(true),
967                FieldInfo::new("name", "name", SqlType::Text),
968                FieldInfo::new("type_", "type_", SqlType::Text), // Discriminator column
969            ];
970            FIELDS
971        }
972
973        fn to_row(&self) -> Vec<(&'static str, Value)> {
974            vec![]
975        }
976
977        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
978            Ok(SingleTableBase)
979        }
980
981        fn primary_key_value(&self) -> Vec<Value> {
982            vec![]
983        }
984
985        fn is_new(&self) -> bool {
986            true
987        }
988
989        fn inheritance() -> InheritanceInfo {
990            InheritanceInfo {
991                strategy: sqlmodel_core::InheritanceStrategy::Single,
992                parent: None,
993                parent_fields_fn: None,
994                discriminator_column: Some("type_"),
995                discriminator_value: None,
996            }
997        }
998    }
999
1000    // Single Table Inheritance Child Model (should not create table)
1001    struct SingleTableChild;
1002
1003    impl Model for SingleTableChild {
1004        // Single-table inheritance child shares the parent's physical table.
1005        const TABLE_NAME: &'static str = "employees";
1006        const PRIMARY_KEY: &'static [&'static str] = &["id"];
1007
1008        fn fields() -> &'static [FieldInfo] {
1009            static FIELDS: &[FieldInfo] = &[
1010                FieldInfo::new("id", "id", SqlType::BigInt).primary_key(true),
1011                FieldInfo::new("department", "department", SqlType::Text),
1012            ];
1013            FIELDS
1014        }
1015
1016        fn to_row(&self) -> Vec<(&'static str, Value)> {
1017            vec![]
1018        }
1019
1020        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
1021            Ok(SingleTableChild)
1022        }
1023
1024        fn primary_key_value(&self) -> Vec<Value> {
1025            vec![]
1026        }
1027
1028        fn is_new(&self) -> bool {
1029            true
1030        }
1031
1032        fn inheritance() -> InheritanceInfo {
1033            // Child model: has parent and discriminator_value but strategy is None
1034            // (inherits from parent's strategy implicitly)
1035            InheritanceInfo {
1036                strategy: sqlmodel_core::InheritanceStrategy::None,
1037                parent: Some("employees"),
1038                parent_fields_fn: None,
1039                discriminator_column: Some("type_"),
1040                discriminator_value: Some("manager"),
1041            }
1042        }
1043    }
1044
1045    // Joined Table Inheritance Base Model
1046    struct JoinedTableBase;
1047
1048    impl Model for JoinedTableBase {
1049        const TABLE_NAME: &'static str = "persons";
1050        const PRIMARY_KEY: &'static [&'static str] = &["id"];
1051
1052        fn fields() -> &'static [FieldInfo] {
1053            static FIELDS: &[FieldInfo] = &[
1054                FieldInfo::new("id", "id", SqlType::BigInt).primary_key(true),
1055                FieldInfo::new("name", "name", SqlType::Text),
1056            ];
1057            FIELDS
1058        }
1059
1060        fn to_row(&self) -> Vec<(&'static str, Value)> {
1061            vec![]
1062        }
1063
1064        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
1065            Ok(JoinedTableBase)
1066        }
1067
1068        fn primary_key_value(&self) -> Vec<Value> {
1069            vec![]
1070        }
1071
1072        fn is_new(&self) -> bool {
1073            true
1074        }
1075
1076        fn inheritance() -> InheritanceInfo {
1077            InheritanceInfo {
1078                strategy: sqlmodel_core::InheritanceStrategy::Joined,
1079                parent: None,
1080                parent_fields_fn: None,
1081                discriminator_column: None,
1082                discriminator_value: None,
1083            }
1084        }
1085    }
1086
1087    // Joined Table Inheritance Child Model (has FK to parent)
1088    struct JoinedTableChild;
1089
1090    impl Model for JoinedTableChild {
1091        const TABLE_NAME: &'static str = "students";
1092        const PRIMARY_KEY: &'static [&'static str] = &["id"];
1093
1094        fn fields() -> &'static [FieldInfo] {
1095            static FIELDS: &[FieldInfo] = &[
1096                FieldInfo::new("id", "id", SqlType::BigInt).primary_key(true),
1097                FieldInfo::new("grade", "grade", SqlType::Text),
1098            ];
1099            FIELDS
1100        }
1101
1102        fn to_row(&self) -> Vec<(&'static str, Value)> {
1103            vec![]
1104        }
1105
1106        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
1107            Ok(JoinedTableChild)
1108        }
1109
1110        fn primary_key_value(&self) -> Vec<Value> {
1111            vec![]
1112        }
1113
1114        fn is_new(&self) -> bool {
1115            true
1116        }
1117
1118        fn inheritance() -> InheritanceInfo {
1119            InheritanceInfo {
1120                strategy: sqlmodel_core::InheritanceStrategy::Joined,
1121                parent: Some("persons"),
1122                parent_fields_fn: None,
1123                discriminator_column: None,
1124                discriminator_value: None,
1125            }
1126        }
1127    }
1128
1129    #[test]
1130    fn test_single_table_inheritance_base_creates_table() {
1131        let sql = CreateTable::<SingleTableBase>::new().build();
1132        assert!(sql.contains("CREATE TABLE \"employees\""));
1133        assert!(sql.contains("\"type_\" TEXT NOT NULL")); // Discriminator column
1134    }
1135
1136    #[test]
1137    fn test_single_table_inheritance_child_skips_table_creation() {
1138        // Child model should not create its own table
1139        let sql = CreateTable::<SingleTableChild>::new().build();
1140        assert!(
1141            sql.is_empty(),
1142            "Single table inheritance child should not create a table"
1143        );
1144    }
1145
1146    #[test]
1147    fn test_single_table_inheritance_child_should_skip() {
1148        assert!(
1149            CreateTable::<SingleTableChild>::should_skip_table_creation(),
1150            "should_skip_table_creation should return true for STI child"
1151        );
1152        assert!(
1153            !CreateTable::<SingleTableBase>::should_skip_table_creation(),
1154            "should_skip_table_creation should return false for STI base"
1155        );
1156    }
1157
1158    #[test]
1159    fn test_joined_table_inheritance_base_creates_table() {
1160        let sql = CreateTable::<JoinedTableBase>::new().build();
1161        assert!(sql.contains("CREATE TABLE \"persons\""));
1162        assert!(sql.contains("\"id\" BIGINT"));
1163        assert!(sql.contains("\"name\" TEXT NOT NULL"));
1164    }
1165
1166    #[test]
1167    fn test_joined_table_inheritance_child_creates_table_with_fk() {
1168        let sql = CreateTable::<JoinedTableChild>::new().build();
1169        assert!(sql.contains("CREATE TABLE \"students\""));
1170        assert!(sql.contains("\"id\" BIGINT"));
1171        assert!(sql.contains("\"grade\" TEXT NOT NULL"));
1172        // Should have FK to parent table
1173        assert!(
1174            sql.contains("FOREIGN KEY (\"id\") REFERENCES \"persons\"(\"id\") ON DELETE CASCADE"),
1175            "Joined table child should have FK to parent: {}",
1176            sql
1177        );
1178    }
1179
1180    #[test]
1181    fn test_schema_builder_applies_sti_child_columns() {
1182        let statements = SchemaBuilder::new()
1183            .create_table::<SingleTableBase>()
1184            .create_table::<SingleTableChild>() // Adds child-specific columns via ALTER TABLE
1185            .build();
1186
1187        // Base creates the table, child adds the extra column(s).
1188        assert_eq!(
1189            statements.len(),
1190            2,
1191            "STI child should contribute ALTER TABLE statements"
1192        );
1193        assert!(statements[0].contains("CREATE TABLE IF NOT EXISTS \"employees\""));
1194        assert!(statements[1].contains("ALTER TABLE \"employees\" ADD COLUMN \"department\""));
1195    }
1196
1197    #[test]
1198    fn test_schema_builder_creates_both_joined_tables() {
1199        let statements = SchemaBuilder::new()
1200            .create_table::<JoinedTableBase>()
1201            .create_table::<JoinedTableChild>()
1202            .build();
1203
1204        // Both tables should be created
1205        assert_eq!(
1206            statements.len(),
1207            2,
1208            "Both joined inheritance tables should be created"
1209        );
1210        assert!(statements[0].contains("CREATE TABLE IF NOT EXISTS \"persons\""));
1211        assert!(statements[1].contains("CREATE TABLE IF NOT EXISTS \"students\""));
1212        assert!(statements[1].contains("FOREIGN KEY"));
1213    }
1214}
1215
1216/// Builder for multiple schema operations.
1217#[derive(Debug, Default)]
1218pub struct SchemaBuilder {
1219    statements: Vec<String>,
1220}
1221
1222impl SchemaBuilder {
1223    /// Create a new schema builder.
1224    pub fn new() -> Self {
1225        Self::default()
1226    }
1227
1228    /// Add a CREATE TABLE statement.
1229    ///
1230    /// For single table inheritance child models, this emits `ALTER TABLE .. ADD COLUMN ..`
1231    /// statements for the child-specific fields, since the child's logical table is the
1232    /// parent's physical table.
1233    pub fn create_table<M: Model>(mut self) -> Self {
1234        if CreateTable::<M>::should_skip_table_creation() {
1235            let inheritance = M::inheritance();
1236            let Some(parent_table) = inheritance.parent else {
1237                return self;
1238            };
1239
1240            let pk_cols = M::PRIMARY_KEY;
1241            for field in M::fields() {
1242                // Avoid trying to re-add PK columns that are expected to be on the base table.
1243                if field.primary_key || pk_cols.contains(&field.column_name) {
1244                    continue;
1245                }
1246                self.statements
1247                    .push(alter_table_add_column(parent_table, field));
1248            }
1249            return self;
1250        }
1251
1252        self.statements
1253            .push(CreateTable::<M>::new().if_not_exists().build());
1254        self
1255    }
1256
1257    /// Add a raw SQL statement.
1258    pub fn raw(mut self, sql: impl Into<String>) -> Self {
1259        self.statements.push(sql.into());
1260        self
1261    }
1262
1263    /// Add an index creation statement.
1264    pub fn create_index(mut self, name: &str, table: &str, columns: &[&str], unique: bool) -> Self {
1265        let unique_str = if unique { "UNIQUE " } else { "" };
1266        let quoted_cols: Vec<String> = columns.iter().map(|c| quote_ident(c)).collect();
1267        let stmt = format!(
1268            "CREATE {}INDEX IF NOT EXISTS {} ON {} ({})",
1269            unique_str,
1270            quote_ident(name),
1271            quote_ident(table),
1272            quoted_cols.join(", ")
1273        );
1274        self.statements.push(stmt);
1275        self
1276    }
1277
1278    /// Get all SQL statements.
1279    pub fn build(self) -> Vec<String> {
1280        self.statements
1281    }
1282}
1283
1284fn alter_table_add_column(table: &str, field: &FieldInfo) -> String {
1285    let sql_type = field.effective_sql_type();
1286    let mut stmt = format!(
1287        "ALTER TABLE {} ADD COLUMN {} {}",
1288        quote_ident(table),
1289        quote_ident(field.column_name),
1290        sql_type
1291    );
1292
1293    if !field.nullable && !field.auto_increment {
1294        stmt.push_str(" NOT NULL");
1295    }
1296
1297    if let Some(default) = field.default {
1298        stmt.push_str(" DEFAULT ");
1299        stmt.push_str(default);
1300    }
1301
1302    stmt
1303}
sqlmodel_schema/create.rs

sqlmodel_schema/
create.rs
