vespertide_query/sql/
create_table.rs

1use sea_query::{Alias, ForeignKey, Index, Table, TableCreateStatement};
2
3use vespertide_core::{ColumnDef, ColumnType, ComplexColumnType, TableConstraint};
4
5use super::helpers::{
6    build_create_enum_type_sql, build_schema_statement, build_sea_column_def_with_table,
7    collect_sqlite_enum_check_clauses, to_sea_fk_action,
8};
9use super::types::{BuiltQuery, DatabaseBackend, RawSql};
10use crate::error::QueryError;
11
12pub(crate) fn build_create_table_for_backend(
13    backend: DatabaseBackend,
14    table: &str,
15    columns: &[ColumnDef],
16    constraints: &[TableConstraint],
17) -> TableCreateStatement {
18    let mut stmt = Table::create().table(Alias::new(table)).to_owned();
19    add_create_table_columns(&mut stmt, backend, table, columns, constraints);
20    add_create_table_constraints(&mut stmt, backend, table, columns, constraints);
21    stmt
22}
23
24fn add_create_table_columns(
25    stmt: &mut TableCreateStatement,
26    backend: DatabaseBackend,
27    table: &str,
28    columns: &[ColumnDef],
29    constraints: &[TableConstraint],
30) {
31    let has_table_primary_key = constraints
32        .iter()
33        .any(|c| matches!(c, TableConstraint::PrimaryKey { .. }));
34    let auto_increment_columns = collect_auto_increment_columns(constraints);
35
36    for column in columns {
37        let mut col = build_sea_column_def_with_table(backend, table, column);
38        if column.primary_key.is_some() && !has_table_primary_key {
39            col.primary_key();
40        }
41        if auto_increment_columns.contains(column.name.as_str())
42            && column.r#type.supports_auto_increment()
43        {
44            if matches!(backend, DatabaseBackend::Sqlite) {
45                col.primary_key();
46            }
47            col.auto_increment();
48        }
49        *stmt = stmt.col(col).to_owned();
50    }
51}
52
53fn collect_auto_increment_columns(
54    constraints: &[TableConstraint],
55) -> std::collections::HashSet<&str> {
56    constraints
57        .iter()
58        .filter_map(|c| {
59            if let TableConstraint::PrimaryKey {
60                columns: pk_cols,
61                auto_increment: true,
62                ..
63            } = c
64            {
65                Some(pk_cols.iter().map(AsRef::as_ref).collect::<Vec<_>>())
66            } else {
67                None
68            }
69        })
70        .flatten()
71        .collect()
72}
73
74fn add_create_table_constraints(
75    stmt: &mut TableCreateStatement,
76    backend: DatabaseBackend,
77    table: &str,
78    columns: &[ColumnDef],
79    constraints: &[TableConstraint],
80) {
81    for constraint in constraints {
82        match constraint {
83            TableConstraint::PrimaryKey {
84                columns: pk_cols,
85                auto_increment,
86                ..
87            } => add_primary_key_constraint(stmt, backend, columns, pk_cols, *auto_increment),
88            TableConstraint::Unique {
89                name,
90                columns: unique_cols,
91                ..
92            } => add_mysql_unique_constraint(stmt, backend, table, unique_cols, name.as_deref()),
93            TableConstraint::ForeignKey { .. } => {
94                add_foreign_key_constraint(stmt, table, constraint);
95            }
96            TableConstraint::Check { name, expr, .. } => {
97                let _ = (name, expr);
98            }
99            TableConstraint::Index { .. } => {}
100            _ => {
101                unreachable!("TableConstraint is #[non_exhaustive]; all variants are matched above")
102            }
103        }
104    }
105}
106
107fn add_primary_key_constraint(
108    stmt: &mut TableCreateStatement,
109    backend: DatabaseBackend,
110    columns: &[ColumnDef],
111    pk_cols: &[impl AsRef<str>],
112    auto_increment: bool,
113) {
114    if should_skip_sqlite_auto_increment_pk(backend, columns, pk_cols, auto_increment) {
115        return;
116    }
117
118    let mut pk_idx = Index::create();
119    for c in pk_cols {
120        pk_idx.col(Alias::new(c.as_ref()));
121    }
122    *stmt = stmt.primary_key(&mut pk_idx).to_owned();
123}
124
125fn should_skip_sqlite_auto_increment_pk(
126    backend: DatabaseBackend,
127    columns: &[ColumnDef],
128    pk_cols: &[impl AsRef<str>],
129    auto_increment: bool,
130) -> bool {
131    matches!(backend, DatabaseBackend::Sqlite)
132        && auto_increment
133        && pk_cols.iter().all(|col_name| {
134            columns
135                .iter()
136                .find(|c| c.name == col_name.as_ref())
137                .is_some_and(|c| c.r#type.supports_auto_increment())
138        })
139}
140
141fn add_mysql_unique_constraint(
142    stmt: &mut TableCreateStatement,
143    backend: DatabaseBackend,
144    table: &str,
145    unique_cols: &[impl AsRef<str>],
146    name: Option<&str>,
147) {
148    if !matches!(backend, DatabaseBackend::MySql) {
149        return;
150    }
151
152    let index_name = super::helpers::build_unique_constraint_name(table, unique_cols, name);
153    let mut idx = Index::create().name(&index_name).unique().to_owned();
154    for col in unique_cols {
155        idx.col(Alias::new(col.as_ref()));
156    }
157    *stmt = stmt.index(&mut idx).to_owned();
158}
159
160fn add_foreign_key_constraint(
161    stmt: &mut TableCreateStatement,
162    table: &str,
163    constraint: &TableConstraint,
164) {
165    if let TableConstraint::ForeignKey {
166        name,
167        columns: fk_cols,
168        ref_table,
169        ref_columns,
170        on_delete,
171        on_update,
172        ..
173    } = constraint
174    {
175        let fk_name = super::helpers::build_foreign_key_name(table, fk_cols, name.as_deref());
176        let mut fk = ForeignKey::create().name(&fk_name).to_owned();
177        fk.from_tbl(Alias::new(table));
178        for col in fk_cols {
179            fk.from_col(Alias::new(col));
180        }
181        fk.to_tbl(Alias::new(ref_table));
182        for col in ref_columns {
183            fk.to_col(Alias::new(col));
184        }
185        if let Some(action) = on_delete {
186            fk.on_delete(to_sea_fk_action(action));
187        }
188        if let Some(action) = on_update {
189            fk.on_update(to_sea_fk_action(action));
190        }
191        *stmt = stmt.foreign_key(&mut fk).to_owned();
192    }
193}
194
195pub fn build_create_table(
196    backend: DatabaseBackend,
197    table: &str,
198    columns: &[ColumnDef],
199    constraints: &[TableConstraint],
200) -> Result<Vec<BuiltQuery>, QueryError> {
201    // Normalize the table to convert inline constraints to table-level
202    // This ensures we don't have duplicate constraints if both inline and table-level are defined
203    let table_def = vespertide_core::TableDef {
204        description: None,
205        name: table.into(),
206        columns: columns.to_vec(),
207        constraints: constraints.to_vec(),
208    };
209    let normalized = table_def.normalize().map_err(|e| {
210        QueryError::SchemaError(format!("Failed to normalize table '{table}': {e}"))
211    })?;
212
213    // Use normalized columns and constraints for SQL generation
214    let columns = &normalized.columns;
215    let constraints = &normalized.constraints;
216
217    let mut queries = Vec::new();
218
219    // Create enum types first (PostgreSQL only)
220    // Collect unique enum types to avoid duplicates
221    let mut created_enums = std::collections::HashSet::new();
222    for column in columns {
223        if let ColumnType::Complex(ComplexColumnType::Enum { name, .. }) = &column.r#type
224            && created_enums.insert(name.clone())
225            && let Some(create_type_sql) = build_create_enum_type_sql(table, &column.r#type)
226        {
227            queries.push(BuiltQuery::Raw(create_type_sql));
228        }
229    }
230
231    // Separate unique constraints for Postgres and SQLite (they need separate CREATE INDEX statements)
232    // For MySQL, unique constraints are added directly in CREATE TABLE via build_create_table_for_backend
233    let (table_constraints, unique_constraints): (Vec<&TableConstraint>, Vec<&TableConstraint>) =
234        constraints
235            .iter()
236            .partition(|c| !matches!(c, TableConstraint::Unique { .. }));
237
238    // Build CREATE TABLE
239    // For MySQL, include unique constraints in CREATE TABLE
240    // For Postgres and SQLite, exclude them (will be added as separate CREATE INDEX statements)
241    let create_table_stmt = if matches!(backend, DatabaseBackend::MySql) {
242        build_create_table_for_backend(backend, table, columns, constraints)
243    } else {
244        // Convert references to owned values for build_create_table_for_backend
245        let table_constraints_owned: Vec<TableConstraint> =
246            table_constraints.iter().copied().cloned().collect();
247        build_create_table_for_backend(backend, table, columns, &table_constraints_owned)
248    };
249
250    // For SQLite, add CHECK constraints for enum columns
251    if matches!(backend, DatabaseBackend::Sqlite) {
252        let enum_check_clauses = collect_sqlite_enum_check_clauses(table, columns);
253        if enum_check_clauses.is_empty() {
254            queries.push(BuiltQuery::CreateTable(Box::new(create_table_stmt)));
255        } else {
256            // Embed CHECK constraints into CREATE TABLE statement
257            let base_sql = build_schema_statement(&create_table_stmt, backend);
258            let mut modified_sql = base_sql;
259            if let Some(pos) = modified_sql.rfind(')') {
260                let check_sql = enum_check_clauses.join(", ");
261                modified_sql.insert_str(pos, &format!(", {check_sql}"));
262            }
263            queries.push(BuiltQuery::Raw(RawSql::per_backend(
264                modified_sql.clone(),
265                modified_sql.clone(),
266                modified_sql,
267            )));
268        }
269    } else {
270        queries.push(BuiltQuery::CreateTable(Box::new(create_table_stmt)));
271    }
272
273    // For Postgres and SQLite, add unique constraints as separate CREATE UNIQUE INDEX statements
274    if matches!(backend, DatabaseBackend::Postgres | DatabaseBackend::Sqlite) {
275        for constraint in unique_constraints {
276            if let TableConstraint::Unique {
277                name,
278                columns: unique_cols,
279                ..
280            } = constraint
281            {
282                // Always generate a proper name: uq_{table}_{key} or uq_{table}_{columns}
283                let index_name = super::helpers::build_unique_constraint_name(
284                    table,
285                    unique_cols,
286                    name.as_deref(),
287                );
288                let mut idx = Index::create()
289                    .table(Alias::new(table))
290                    .name(&index_name)
291                    .unique()
292                    .to_owned();
293                for col in unique_cols {
294                    idx.col(Alias::new(col));
295                }
296                queries.push(BuiltQuery::CreateIndex(Box::new(idx)));
297            }
298        }
299    }
300
301    // Add Index constraints as CREATE INDEX statements (for all backends)
302    for constraint in constraints {
303        if let TableConstraint::Index {
304            name,
305            columns: index_cols,
306        } = constraint
307        {
308            // Always generate a proper name: ix_{table}_{key} or ix_{table}_{columns}
309            let index_name = super::helpers::build_index_name(table, index_cols, name.as_deref());
310            let mut idx = Index::create()
311                .table(Alias::new(table))
312                .name(&index_name)
313                .to_owned();
314            for col in index_cols {
315                idx.col(Alias::new(col));
316            }
317            queries.push(BuiltQuery::CreateIndex(Box::new(idx)));
318        }
319    }
320
321    Ok(queries)
322}
323
324#[cfg(test)]
325mod tests {
326    use super::*;
327    use crate::test_support::col;
328    use insta::{assert_snapshot, with_settings};
329    use rstest::rstest;
330    use vespertide_core::{ColumnType, EnumValues, SimpleColumnType};
331
332    fn join_queries(queries: &[BuiltQuery], backend: DatabaseBackend, separator: &str) -> String {
333        let mut sql = String::new();
334        for (index, query) in queries.iter().enumerate() {
335            if index > 0 {
336                sql.push_str(separator);
337            }
338            sql.push_str(&query.build(backend));
339        }
340        sql
341    }
342
343    #[rstest]
344    #[case::create_table_postgres(
345        "create_table_postgres",
346        DatabaseBackend::Postgres,
347        &["CREATE TABLE \"users\" ( \"id\" integer )"]
348    )]
349    #[case::create_table_mysql(
350        "create_table_mysql",
351        DatabaseBackend::MySql,
352        &["CREATE TABLE `users` ( `id` int )"]
353    )]
354    #[case::create_table_sqlite(
355        "create_table_sqlite",
356        DatabaseBackend::Sqlite,
357        &["CREATE TABLE \"users\" ( \"id\" integer )"]
358    )]
359    fn test_create_table(
360        #[case] title: &str,
361        #[case] backend: DatabaseBackend,
362        #[case] expected: &[&str],
363    ) {
364        let result = build_create_table(
365            backend,
366            "users",
367            &[col("id", ColumnType::Simple(SimpleColumnType::Integer))],
368            &[],
369        )
370        .unwrap();
371        let sql = join_queries(&result, backend, "\n");
372        for exp in expected {
373            assert!(
374                sql.contains(exp),
375                "Expected SQL to contain '{exp}', got: {sql}"
376            );
377        }
378
379        with_settings!({ snapshot_suffix => format!("create_table_{}", title) }, {
380            assert_snapshot!(sql);
381        });
382    }
383
384    #[rstest]
385    #[case::inline_unique_postgres(DatabaseBackend::Postgres)]
386    #[case::inline_unique_mysql(DatabaseBackend::MySql)]
387    #[case::inline_unique_sqlite(DatabaseBackend::Sqlite)]
388    fn test_create_table_with_inline_unique(#[case] backend: DatabaseBackend) {
389        // Test that inline unique constraint is converted to table-level during normalization.
390        // build_create_table now normalizes the table, so inline unique becomes a CREATE UNIQUE INDEX.
391        use vespertide_core::schema::str_or_bool::StrOrBoolOrArray;
392
393        let mut email_col = col("email", ColumnType::Simple(SimpleColumnType::Text));
394        email_col.unique = Some(StrOrBoolOrArray::Bool(true));
395
396        let result = build_create_table(
397            backend,
398            "users",
399            &[
400                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
401                email_col,
402            ],
403            // No explicit table-level unique constraint passed, but normalize will create one from inline
404            &[],
405        )
406        .unwrap();
407        let sql = join_queries(&result, backend, "\n");
408
409        // After normalization, inline unique should produce UNIQUE constraint in SQL
410        assert!(
411            sql.contains("UNIQUE") || sql.to_uppercase().contains("UNIQUE"),
412            "Normalized unique constraint should be in SQL, but not found: {sql}"
413        );
414        with_settings!({ snapshot_suffix => format!("create_table_with_inline_unique_{:?}", backend) }, {
415            assert_snapshot!(sql);
416        });
417    }
418
419    #[rstest]
420    #[case::table_level_unique_postgres(DatabaseBackend::Postgres)]
421    #[case::table_level_unique_mysql(DatabaseBackend::MySql)]
422    #[case::table_level_unique_sqlite(DatabaseBackend::Sqlite)]
423    fn test_create_table_with_table_level_unique(#[case] backend: DatabaseBackend) {
424        // Test table-level unique constraint (lines 53-54, 56-58, 60-61)
425        let result = build_create_table(
426            backend,
427            "users",
428            &[
429                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
430                col("email", ColumnType::Simple(SimpleColumnType::Text)),
431            ],
432            &[TableConstraint::Unique {
433                name: Some("uq_email".into()),
434                columns: vec!["email".into()],
435                strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
436                    keep: vespertide_core::KeepPolicy::First,
437                },
438            }],
439        )
440        .unwrap();
441        let sql = join_queries(&result, backend, "\n");
442        assert!(sql.contains("CREATE TABLE"));
443        // Verify unique constraint is present
444        match backend {
445            DatabaseBackend::MySql => {
446                assert!(
447                    sql.contains("UNIQUE"),
448                    "MySQL should have UNIQUE in CREATE TABLE: {sql}"
449                );
450            }
451            _ => {
452                // For Postgres and SQLite, unique constraint should be in a separate CREATE UNIQUE INDEX statement
453                assert!(
454                    sql.contains("CREATE UNIQUE INDEX"),
455                    "Postgres/SQLite should have CREATE UNIQUE INDEX: {sql}"
456                );
457            }
458        }
459        with_settings!({ snapshot_suffix => format!("create_table_with_table_level_unique_{:?}", backend) }, {
460            assert_snapshot!(sql);
461        });
462    }
463
464    #[rstest]
465    #[case::table_level_unique_no_name_postgres(DatabaseBackend::Postgres)]
466    #[case::table_level_unique_no_name_mysql(DatabaseBackend::MySql)]
467    #[case::table_level_unique_no_name_sqlite(DatabaseBackend::Sqlite)]
468    fn test_create_table_with_table_level_unique_no_name(#[case] backend: DatabaseBackend) {
469        // Test table-level unique constraint without name (lines 53-54, 56-58, 60-61)
470        let result = build_create_table(
471            backend,
472            "users",
473            &[
474                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
475                col("email", ColumnType::Simple(SimpleColumnType::Text)),
476            ],
477            &[TableConstraint::Unique {
478                name: None,
479                columns: vec!["email".into()],
480                strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
481                    keep: vespertide_core::KeepPolicy::First,
482                },
483            }],
484        )
485        .unwrap();
486        let sql = join_queries(&result, backend, "\n");
487        assert!(sql.contains("CREATE TABLE"));
488        // Verify unique constraint is present
489        match backend {
490            DatabaseBackend::MySql => {
491                assert!(
492                    sql.contains("UNIQUE"),
493                    "MySQL should have UNIQUE in CREATE TABLE: {sql}"
494                );
495            }
496            _ => {
497                // For Postgres and SQLite, unique constraint should be in a separate CREATE UNIQUE INDEX statement
498                assert!(
499                    sql.contains("CREATE UNIQUE INDEX"),
500                    "Postgres/SQLite should have CREATE UNIQUE INDEX: {sql}"
501                );
502            }
503        }
504        with_settings!({ snapshot_suffix => format!("create_table_with_table_level_unique_no_name_{:?}", backend) }, {
505            assert_snapshot!(sql);
506        });
507    }
508
509    #[rstest]
510    #[case::postgres(DatabaseBackend::Postgres)]
511    #[case::mysql(DatabaseBackend::MySql)]
512    #[case::sqlite(DatabaseBackend::Sqlite)]
513    fn test_create_table_with_enum_column(#[case] backend: DatabaseBackend) {
514        // Test creating a table with an enum column (should create enum type first for PostgreSQL)
515        let columns = vec![
516            ColumnDef {
517                name: "id".into(),
518                r#type: ColumnType::Simple(SimpleColumnType::Integer),
519                nullable: false,
520                default: None,
521                comment: None,
522                primary_key: None,
523                unique: None,
524                index: None,
525                foreign_key: None,
526            },
527            ColumnDef {
528                name: "status".into(),
529                r#type: ColumnType::Complex(ComplexColumnType::Enum {
530                    name: "user_status".into(),
531                    values: EnumValues::String(vec![
532                        "active".into(),
533                        "inactive".into(),
534                        "pending".into(),
535                    ]),
536                }),
537                nullable: false,
538                default: Some("'active'".into()),
539                comment: None,
540                primary_key: None,
541                unique: None,
542                index: None,
543                foreign_key: None,
544            },
545        ];
546        let constraints = vec![TableConstraint::PrimaryKey {
547            auto_increment: false,
548            columns: vec!["id".into()],
549            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
550        }];
551
552        let result = build_create_table(backend, "users", &columns, &constraints);
553        assert!(result.is_ok());
554        let queries = result.unwrap();
555        let sql = join_queries(&queries, backend, ";\n");
556
557        with_settings!({ snapshot_suffix => format!("create_table_with_enum_column_{:?}", backend) }, {
558            assert_snapshot!(sql);
559        });
560    }
561
562    #[rstest]
563    #[case::auto_increment_postgres(DatabaseBackend::Postgres)]
564    #[case::auto_increment_mysql(DatabaseBackend::MySql)]
565    #[case::auto_increment_sqlite(DatabaseBackend::Sqlite)]
566    fn test_create_table_with_auto_increment_primary_key(#[case] backend: DatabaseBackend) {
567        // Test that auto_increment on primary key generates SERIAL/AUTO_INCREMENT/AUTOINCREMENT
568        let columns = vec![ColumnDef {
569            name: "id".into(),
570            r#type: ColumnType::Simple(SimpleColumnType::Integer),
571            nullable: false,
572            default: None,
573            comment: None,
574            primary_key: None,
575            unique: None,
576            index: None,
577            foreign_key: None,
578        }];
579        let constraints = vec![TableConstraint::PrimaryKey {
580            auto_increment: true,
581            columns: vec!["id".into()],
582            strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
583        }];
584
585        let result = build_create_table(backend, "users", &columns, &constraints);
586        assert!(result.is_ok());
587        let queries = result.unwrap();
588        let sql = join_queries(&queries, backend, ";\n");
589
590        // Verify auto_increment is applied correctly for each backend
591        match backend {
592            DatabaseBackend::Postgres => {
593                // sea-query 1.0.0-rc.33 renders PG auto-increment as the
594                // SQL-standard `GENERATED BY DEFAULT AS IDENTITY` (PG 10+),
595                // whereas the 1.0.0 final line used `SERIAL`. Both express an
596                // auto-incrementing PK; accept either form.
597                assert!(
598                    sql.contains("SERIAL")
599                        || sql.contains("serial")
600                        || sql.contains("IDENTITY")
601                        || sql.contains("identity"),
602                    "PostgreSQL should use SERIAL or IDENTITY for auto_increment, got: {sql}"
603                );
604            }
605            DatabaseBackend::MySql => {
606                assert!(
607                    sql.contains("AUTO_INCREMENT") || sql.contains("auto_increment"),
608                    "MySQL should use AUTO_INCREMENT for auto_increment, got: {sql}"
609                );
610            }
611            DatabaseBackend::Sqlite => {
612                assert!(
613                    sql.contains("AUTOINCREMENT") || sql.contains("autoincrement"),
614                    "SQLite should use AUTOINCREMENT for auto_increment, got: {sql}"
615                );
616            }
617        }
618
619        with_settings!({ snapshot_suffix => format!("create_table_with_auto_increment_{:?}", backend) }, {
620            assert_snapshot!(sql);
621        });
622    }
623
624    #[rstest]
625    #[case::inline_auto_increment_postgres(DatabaseBackend::Postgres)]
626    #[case::inline_auto_increment_mysql(DatabaseBackend::MySql)]
627    #[case::inline_auto_increment_sqlite(DatabaseBackend::Sqlite)]
628    fn test_create_table_with_inline_auto_increment_primary_key(#[case] backend: DatabaseBackend) {
629        // Test that inline primary_key with auto_increment generates correct SQL
630        use vespertide_core::schema::primary_key::{PrimaryKeyDef, PrimaryKeySyntax};
631
632        let columns = vec![ColumnDef {
633            name: "id".into(),
634            r#type: ColumnType::Simple(SimpleColumnType::Integer),
635            nullable: false,
636            default: None,
637            comment: None,
638            primary_key: Some(PrimaryKeySyntax::Object(PrimaryKeyDef {
639                auto_increment: true,
640            })),
641            unique: None,
642            index: None,
643            foreign_key: None,
644        }];
645
646        let result = build_create_table(backend, "users", &columns, &[]);
647        assert!(result.is_ok());
648        let queries = result.unwrap();
649        let sql = join_queries(&queries, backend, ";\n");
650
651        // Verify auto_increment is applied correctly for each backend
652        match backend {
653            DatabaseBackend::Postgres => {
654                // sea-query 1.0.0-rc.33 renders PG auto-increment as the
655                // SQL-standard `GENERATED BY DEFAULT AS IDENTITY` (PG 10+),
656                // whereas the 1.0.0 final line used `SERIAL`. Both express an
657                // auto-incrementing PK; accept either form.
658                assert!(
659                    sql.contains("SERIAL")
660                        || sql.contains("serial")
661                        || sql.contains("IDENTITY")
662                        || sql.contains("identity"),
663                    "PostgreSQL should use SERIAL or IDENTITY for auto_increment, got: {sql}"
664                );
665            }
666            DatabaseBackend::MySql => {
667                assert!(
668                    sql.contains("AUTO_INCREMENT") || sql.contains("auto_increment"),
669                    "MySQL should use AUTO_INCREMENT for auto_increment, got: {sql}"
670                );
671            }
672            DatabaseBackend::Sqlite => {
673                assert!(
674                    sql.contains("AUTOINCREMENT") || sql.contains("autoincrement"),
675                    "SQLite should use AUTOINCREMENT for auto_increment, got: {sql}"
676                );
677            }
678        }
679
680        with_settings!({ snapshot_suffix => format!("create_table_with_inline_auto_increment_{:?}", backend) }, {
681            assert_snapshot!(sql);
682        });
683    }
684
685    /// Test creating a table with timestamp column and `NOW()` default
686    /// `SQLite` should convert `NOW()` to `CURRENT_TIMESTAMP`
687    #[rstest]
688    #[case::timestamp_now_default_postgres(DatabaseBackend::Postgres)]
689    #[case::timestamp_now_default_mysql(DatabaseBackend::MySql)]
690    #[case::timestamp_now_default_sqlite(DatabaseBackend::Sqlite)]
691    /// CREATE TABLE with `Check` + `Index` constraints exercises the
692    /// `TableConstraint::Check` and `TableConstraint::Index` arms of
693    /// `add_create_table_constraints` (both silently skip - the
694    /// constraints are emitted in separate ALTER statements outside
695    /// this builder, but the match arms still execute on every call).
696    #[rstest]
697    #[case::postgres(DatabaseBackend::Postgres)]
698    #[case::mysql(DatabaseBackend::MySql)]
699    #[case::sqlite(DatabaseBackend::Sqlite)]
700    fn test_create_table_check_and_index_constraints_are_skipped(#[case] backend: DatabaseBackend) {
701        // Mix CHECK + INDEX + PrimaryKey so the match dispatcher walks the
702        // Check and Index arms (currently no-op) alongside a real PK arm.
703        let constraints = vec![
704            TableConstraint::PrimaryKey {
705                columns: vec!["id".into()],
706                auto_increment: false,
707                strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
708            },
709            TableConstraint::Check {
710                name: "chk_age".into(),
711                expr: "age > 0".into(),
712                strategy: vespertide_core::CheckViolationStrategy::default(),
713            },
714            TableConstraint::Index {
715                name: Some("idx_age".into()),
716                columns: vec!["age".into()],
717            },
718        ];
719        let columns = vec![
720            ColumnDef {
721                name: "id".into(),
722                r#type: ColumnType::Simple(SimpleColumnType::Integer),
723                nullable: false,
724                default: None,
725                comment: None,
726                primary_key: None,
727                unique: None,
728                index: None,
729                foreign_key: None,
730            },
731            ColumnDef {
732                name: "age".into(),
733                r#type: ColumnType::Simple(SimpleColumnType::Integer),
734                nullable: false,
735                default: None,
736                comment: None,
737                primary_key: None,
738                unique: None,
739                index: None,
740                foreign_key: None,
741            },
742        ];
743        let queries = build_create_table(backend, "users", &columns, &constraints).unwrap();
744        let sql = queries
745            .iter()
746            .map(|q| q.build(backend))
747            .collect::<Vec<_>>()
748            .join("\n");
749        // The CHECK arm in `add_create_table_constraints` is a documented
750        // no-op (CHECK appears in a separate ALTER ADD CONSTRAINT statement,
751        // not in CREATE TABLE itself). We assert the dispatch ran (PK lands)
752        // and the CHECK predicate is NOT inlined in the CREATE TABLE.
753        assert!(sql.contains("PRIMARY KEY"));
754        assert!(!sql.contains("CHECK (age > 0)"));
755        // Index constraints fan out as separate CREATE INDEX statements,
756        // which is the documented behaviour of build_create_table.
757        assert!(sql.contains("CREATE INDEX") || sql.contains("idx_age"));
758    }
759
760    #[rstest]
761    #[case::postgres(DatabaseBackend::Postgres)]
762    #[case::mysql(DatabaseBackend::MySql)]
763    #[case::sqlite(DatabaseBackend::Sqlite)]
764    fn test_create_table_with_timestamp_now_default(#[case] backend: DatabaseBackend) {
765        let columns = vec![
766            ColumnDef {
767                name: "id".into(),
768                r#type: ColumnType::Simple(SimpleColumnType::BigInt),
769                nullable: false,
770                default: None,
771                comment: None,
772                primary_key: None,
773                unique: None,
774                index: None,
775                foreign_key: None,
776            },
777            ColumnDef {
778                name: "created_at".into(),
779                r#type: ColumnType::Simple(SimpleColumnType::Timestamptz),
780                nullable: false,
781                default: Some("NOW()".into()), // uppercase NOW()
782                comment: None,
783                primary_key: None,
784                unique: None,
785                index: None,
786                foreign_key: None,
787            },
788        ];
789
790        let result = build_create_table(backend, "events", &columns, &[]);
791        assert!(result.is_ok(), "build_create_table failed: {result:?}");
792        let queries = result.unwrap();
793        let sql = join_queries(&queries, backend, "\n");
794
795        // SQLite should NOT have NOW() - it should be converted to CURRENT_TIMESTAMP
796        if matches!(backend, DatabaseBackend::Sqlite) {
797            assert!(
798                !sql.contains("NOW()"),
799                "SQLite should not contain NOW(), got: {sql}"
800            );
801            assert!(
802                sql.contains("CURRENT_TIMESTAMP"),
803                "SQLite should use CURRENT_TIMESTAMP, got: {sql}"
804            );
805        }
806
807        with_settings!({ snapshot_suffix => format!("create_table_with_timestamp_now_default_{:?}", backend) }, {
808            assert_snapshot!(sql);
809        });
810    }
811}
vespertide_query/sql/create_table.rs

vespertide_query/sql/
create_table.rs
