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

sqlmodel_schema/
create.rs
