vespertide_query/sql/
helpers.rs

1use sea_query::{
2    Alias, ColumnDef as SeaColumnDef, ForeignKeyAction, MysqlQueryBuilder, PostgresQueryBuilder,
3    Query, QueryStatementWriter, SchemaStatementBuilder, SimpleExpr, SqliteQueryBuilder, Table,
4};
5
6use vespertide_core::{
7    ColumnDef, ColumnType, ComplexColumnType, EnumValues, ReferenceAction, SimpleColumnType,
8    TableConstraint, TableDef,
9};
10
11use super::create_table::build_create_table_for_backend;
12use super::types::{BuiltQuery, DatabaseBackend, RawSql};
13
14/// Normalize `fill_with` value - empty string becomes `''` (SQL empty-string
15/// literal). Returns a borrowed `&str` because both arms are static or borrowed
16/// from the caller — no allocation ever happens, so the `Cow` wrapper was
17/// purely ceremonial.
18#[must_use]
19pub(crate) fn normalize_fill_with(fill_with: Option<&str>) -> Option<&str> {
20    fill_with.map(|s| if s.is_empty() { "''" } else { s })
21}
22
23/// Helper function to convert a schema statement to SQL for a specific backend
24pub(crate) fn build_schema_statement<T: SchemaStatementBuilder>(
25    stmt: &T,
26    backend: DatabaseBackend,
27) -> String {
28    match backend {
29        DatabaseBackend::Postgres => stmt.to_string(PostgresQueryBuilder),
30        DatabaseBackend::MySql => stmt.to_string(MysqlQueryBuilder),
31        DatabaseBackend::Sqlite => stmt.to_string(SqliteQueryBuilder),
32    }
33}
34
35/// Helper function to convert a query statement (INSERT, SELECT, etc.) to SQL for a specific backend
36pub(crate) fn build_query_statement<T: QueryStatementWriter>(
37    stmt: &T,
38    backend: DatabaseBackend,
39) -> String {
40    match backend {
41        DatabaseBackend::Postgres => stmt.to_string(PostgresQueryBuilder),
42        DatabaseBackend::MySql => stmt.to_string(MysqlQueryBuilder),
43        DatabaseBackend::Sqlite => stmt.to_string(SqliteQueryBuilder),
44    }
45}
46
47/// Apply vespertide `ColumnType` to `sea_query` `ColumnDef` with table-aware enum type naming
48pub(crate) fn apply_column_type_with_table(
49    col: &mut SeaColumnDef,
50    ty: &ColumnType,
51    table: &str,
52    backend: DatabaseBackend,
53) {
54    match ty {
55        ColumnType::Simple(simple) => apply_simple_column_type(col, *simple, backend),
56        ColumnType::Complex(complex) => apply_complex_column_type(col, complex, table, backend),
57    }
58}
59
60fn apply_simple_column_type(
61    col: &mut SeaColumnDef,
62    simple: SimpleColumnType,
63    backend: DatabaseBackend,
64) {
65    match simple {
66        SimpleColumnType::SmallInt => {
67            col.small_integer();
68        }
69        SimpleColumnType::Integer => {
70            col.integer();
71        }
72        SimpleColumnType::BigInt => {
73            col.big_integer();
74        }
75        SimpleColumnType::Real => {
76            col.float();
77        }
78        SimpleColumnType::DoublePrecision => {
79            col.double();
80        }
81        SimpleColumnType::Text => {
82            col.text();
83        }
84        SimpleColumnType::Boolean => {
85            col.boolean();
86        }
87        SimpleColumnType::Date => {
88            col.date();
89        }
90        SimpleColumnType::Time => {
91            col.time();
92        }
93        SimpleColumnType::Timestamp => {
94            col.timestamp();
95        }
96        SimpleColumnType::Timestamptz => apply_timestamptz_type(col, backend),
97        SimpleColumnType::Interval => apply_interval_type(col, backend),
98        SimpleColumnType::Bytea => {
99            col.binary();
100        }
101        SimpleColumnType::Uuid => {
102            col.uuid();
103        }
104        SimpleColumnType::Json => {
105            col.json();
106        }
107        SimpleColumnType::Inet => apply_postgres_text_fallback_type(col, backend, "INET"),
108        SimpleColumnType::Cidr => apply_postgres_text_fallback_type(col, backend, "CIDR"),
109        SimpleColumnType::Macaddr => apply_postgres_text_fallback_type(col, backend, "MACADDR"),
110        SimpleColumnType::Xml => apply_postgres_text_fallback_type(col, backend, "XML"),
111        _ => unreachable!("SimpleColumnType is #[non_exhaustive]; all variants are matched above"),
112    }
113}
114
115fn apply_timestamptz_type(col: &mut SeaColumnDef, backend: DatabaseBackend) {
116    match backend {
117        DatabaseBackend::Postgres => {
118            col.timestamp_with_time_zone();
119        }
120        DatabaseBackend::MySql | DatabaseBackend::Sqlite => {
121            col.timestamp();
122        }
123    }
124}
125
126fn apply_interval_type(col: &mut SeaColumnDef, backend: DatabaseBackend) {
127    match backend {
128        DatabaseBackend::Postgres => {
129            col.interval(None, None);
130        }
131        DatabaseBackend::MySql | DatabaseBackend::Sqlite => {
132            col.text();
133        }
134    }
135}
136
137fn apply_postgres_text_fallback_type(
138    col: &mut SeaColumnDef,
139    backend: DatabaseBackend,
140    postgres_type: &str,
141) {
142    match backend {
143        DatabaseBackend::Postgres => {
144            col.custom(Alias::new(postgres_type));
145        }
146        DatabaseBackend::MySql | DatabaseBackend::Sqlite => {
147            col.text();
148        }
149    }
150}
151
152fn apply_complex_column_type(
153    col: &mut SeaColumnDef,
154    complex: &ComplexColumnType,
155    table: &str,
156    backend: DatabaseBackend,
157) {
158    match complex {
159        ComplexColumnType::Varchar { length } => {
160            col.string_len(*length);
161        }
162        ComplexColumnType::Numeric { precision, scale } => {
163            apply_numeric_type(col, *precision, *scale, backend);
164        }
165        ComplexColumnType::Char { length } => {
166            col.char_len(*length);
167        }
168        ComplexColumnType::Custom { custom_type } => {
169            col.custom(Alias::new(custom_type));
170        }
171        // Matched on the variant rather than `values.is_integer()`: the former
172        // `enum_variant_aliases` helper carried an `EnumValues::Integer` arm
173        // that the `is_integer()` guard made unreachable.
174        ComplexColumnType::Enum { name, values } => match values {
175            // Integer enums are stored as INTEGER; no native enum type is emitted.
176            EnumValues::Integer(_) => {
177                col.integer();
178            }
179            EnumValues::String(variants) => {
180                // Use table-prefixed enum type name to avoid conflicts
181                let type_name = build_enum_type_name(table, name);
182                // Map each variant name straight into `Alias::new`, skipping the
183                // intermediate `Vec<&str>` that `variant_names()` would allocate.
184                let aliases: Vec<Alias> = variants.iter().map(Alias::new).collect();
185                col.enumeration(Alias::new(&type_name), aliases);
186            }
187        },
188        _ => unreachable!("ComplexColumnType is #[non_exhaustive]; all variants are matched above"),
189    }
190}
191
192fn apply_numeric_type(
193    col: &mut SeaColumnDef,
194    precision: u32,
195    scale: u32,
196    backend: DatabaseBackend,
197) {
198    debug_assert!(
199        scale <= precision,
200        "numeric scale ({scale}) must be <= precision ({precision}); schema validation should reject this before SQL generation"
201    );
202    let safe_precision = precision.min(28);
203    let safe_scale = scale.min(safe_precision);
204    match backend {
205        DatabaseBackend::Postgres | DatabaseBackend::MySql => {
206            col.decimal_len(safe_precision, safe_scale);
207        }
208        DatabaseBackend::Sqlite => {
209            col.double();
210        }
211    }
212}
213
214/// Convert vespertide `ReferenceAction` to `sea_query` `ForeignKeyAction`
215pub(crate) fn to_sea_fk_action(action: &ReferenceAction) -> ForeignKeyAction {
216    match action {
217        ReferenceAction::Cascade => ForeignKeyAction::Cascade,
218        ReferenceAction::Restrict => ForeignKeyAction::Restrict,
219        ReferenceAction::SetNull => ForeignKeyAction::SetNull,
220        ReferenceAction::SetDefault => ForeignKeyAction::SetDefault,
221        ReferenceAction::NoAction => ForeignKeyAction::NoAction,
222        _ => unreachable!("ReferenceAction is #[non_exhaustive]; all variants are matched above"),
223    }
224}
225
226/// Function spellings meaning "generate a UUID". Matched against the **whole**
227/// input, case-insensitively, so they can never rewrite part of a larger
228/// expression.
229pub(super) const UUID_FUNCTION_SPELLINGS: [&str; 3] =
230    ["gen_random_uuid()", "uuid()", "lower(hex(randomblob(16)))"];
231
232/// Function spellings meaning "current timestamp". Same whole-input matching
233/// rule as [`UUID_FUNCTION_SPELLINGS`].
234pub(super) const TIMESTAMP_FUNCTION_SPELLINGS: [&str; 4] = [
235    "current_timestamp()",
236    "now()",
237    "current_timestamp",
238    "getdate()",
239];
240
241/// Whole-string, case-insensitive membership test.
242///
243/// Uses `eq_ignore_ascii_case` rather than `to_lowercase()` so no `String` is
244/// allocated per call, mirroring the convention `needs_quoting` uses below.
245pub(super) fn matches_any_spelling(value: &str, spellings: &[&str]) -> bool {
246    spellings.iter().any(|s| value.eq_ignore_ascii_case(s))
247}
248
249/// Convert a default value string to the appropriate backend-specific expression
250///
251/// This is for a column **DEFAULT** — a single literal or function call the
252/// generator is free to canonicalise. It is *not* safe for a raw SQL
253/// expression slot such as `fill_with`; see
254/// [`super::fill_with::convert_fill_with_for_backend`].
255pub(crate) fn convert_default_for_backend(default: &str, backend: DatabaseBackend) -> String {
256    if matches_any_spelling(default, &UUID_FUNCTION_SPELLINGS) {
257        return match backend {
258            DatabaseBackend::Postgres => "gen_random_uuid()".to_string(),
259            DatabaseBackend::MySql => "(UUID())".to_string(),
260            DatabaseBackend::Sqlite => "lower(hex(randomblob(16)))".to_string(),
261        };
262    }
263
264    if matches_any_spelling(default, &TIMESTAMP_FUNCTION_SPELLINGS) {
265        return "CURRENT_TIMESTAMP".to_string();
266    }
267
268    // PostgreSQL-style type casts: 'value'::type or expr::type
269    if let Some((value, cast_type)) = parse_pg_type_cast(default) {
270        return convert_cast_chain(value, &cast_type, backend);
271    }
272
273    default.to_string()
274}
275
276/// Byte offset of the **last top-level** `::` cast operator in `expr`.
277///
278/// Top-level means outside every single-quoted string literal *and* outside
279/// every parenthesised group. Both properties matter:
280///
281/// * Taking the **last** operator makes a cast chain (`'x'::text::json`) peel
282///   from the outside in, instead of treating `text::json` as one type name.
283/// * Skipping quoted and nested occurrences stops
284///   `CASE WHEN tag = 'a::b' THEN 1 ELSE 2 END::integer` from being split
285///   inside its own string literal — the defect that let a `fill_with`
286///   expression be silently truncated and case-folded.
287///
288/// Returns `None` when there is no top-level cast, or when a string literal is
289/// left unterminated (at that point syntax cannot be told from data).
290///
291/// Toggling `in_quote` on every `'` also handles the SQL `''` escape for free:
292/// the pair closes and immediately reopens the literal, so its content stays
293/// quoted. Driving the scan from `bytes().enumerate()` keeps the cursor
294/// monotonic by construction — there is no hand-rolled index arithmetic that
295/// could stall the loop. Comparing bytes is sound because every byte matched
296/// here is ASCII, which never occurs inside a multi-byte UTF-8 sequence, so a
297/// returned index is always a `char` boundary.
298pub(super) fn find_last_top_level_cast(expr: &str) -> Option<usize> {
299    let mut in_quote = false;
300    let mut depth: usize = 0;
301    let mut pending_colon: Option<usize> = None;
302    let mut last = None;
303
304    for (index, byte) in expr.bytes().enumerate() {
305        if in_quote {
306            if byte == b'\'' {
307                in_quote = false;
308            }
309            pending_colon = None;
310            continue;
311        }
312        match byte {
313            b'\'' => {
314                in_quote = true;
315                pending_colon = None;
316            }
317            b'(' => {
318                depth += 1;
319                pending_colon = None;
320            }
321            b')' => {
322                depth = depth.saturating_sub(1);
323                pending_colon = None;
324            }
325            b':' => match pending_colon.take() {
326                Some(start) if depth == 0 => last = Some(start),
327                Some(_) => {}
328                None => pending_colon = Some(index),
329            },
330            _ => pending_colon = None,
331        }
332    }
333
334    if in_quote { None } else { last }
335}
336
337/// Parse a PostgreSQL-style type cast expression (e.g., `'[]'::json`, `0::boolean`)
338/// Returns `(value, type)` if parsed, or None if not a type cast.
339///
340/// The split happens at the last top-level `::` (see
341/// [`find_last_top_level_cast`]), so `'x'::text::json` yields
342/// `("'x'::text", "json")` and a `::` that only appears inside a string
343/// literal is not a split point at all.
344///
345/// The value borrows `expr` (a contiguous slice of the input, quotes
346/// included); only `cast_type` is owned because of the `to_lowercase()`
347/// normalisation. **Only the type name is lower-cased — the value is returned
348/// byte-for-byte**, so no caller can mangle user SQL through this function.
349pub(super) fn parse_pg_type_cast(expr: &str) -> Option<(&str, String)> {
350    let trimmed = expr.trim();
351    let split = find_last_top_level_cast(trimmed)?;
352    // `split` and `split + 2` index the two ASCII `:` bytes, so both slices
353    // land on `char` boundaries.
354    let value = trimmed[..split].trim();
355    let cast_type = trimmed[split + 2..].trim().to_lowercase();
356    if value.is_empty() || cast_type.is_empty() {
357        return None;
358    }
359    Some((value, cast_type))
360}
361
362/// Convert a possibly *chained* `PostgreSQL` cast to backend syntax.
363///
364/// Recurses so `'x'::text::json` nests properly: MySQL emits
365/// `CAST(CAST('x' AS CHAR) AS JSON)` and SQLite strips every level rather than
366/// leaving a stray `::text` behind.
367fn convert_cast_chain(value: &str, cast_type: &str, backend: DatabaseBackend) -> String {
368    let inner = match parse_pg_type_cast(value) {
369        Some((inner_value, inner_cast)) => convert_cast_chain(inner_value, &inner_cast, backend),
370        None => value.to_string(),
371    };
372    convert_type_cast(&inner, cast_type, backend)
373}
374
375/// Map `PostgreSQL` type name to `MySQL` CAST target type
376fn pg_type_to_mysql_cast(pg_type: &str) -> &'static str {
377    match pg_type {
378        "json" | "jsonb" => "JSON",
379        "integer" | "int" | "int4" | "smallint" | "int2" | "bigint" | "int8" => "SIGNED",
380        "real" | "float4" | "double precision" | "float8" | "numeric" | "decimal" => "DECIMAL",
381        "boolean" | "bool" => "UNSIGNED",
382        "date" => "DATE",
383        "time" => "TIME",
384        "timestamp"
385        | "timestamptz"
386        | "timestamp with time zone"
387        | "timestamp without time zone" => "DATETIME",
388        "bytea" => "BINARY",
389        _ => "CHAR",
390    }
391}
392
393/// Convert a type cast expression to the appropriate backend syntax
394fn convert_type_cast(value: &str, cast_type: &str, backend: DatabaseBackend) -> String {
395    match backend {
396        // PostgreSQL: keep native :: syntax
397        DatabaseBackend::Postgres => format!("{value}::{cast_type}"),
398        // MySQL: CAST(value AS type)
399        DatabaseBackend::MySql => {
400            let mysql_type = pg_type_to_mysql_cast(cast_type);
401            format!("CAST({value} AS {mysql_type})")
402        }
403        // SQLite: strip the cast, use raw value (SQLite is dynamically typed)
404        DatabaseBackend::Sqlite => value.to_string(),
405    }
406}
407
408/// Check if the column type is an enum type
409pub(super) fn is_enum_type(column_type: &ColumnType) -> bool {
410    matches!(
411        column_type,
412        ColumnType::Complex(ComplexColumnType::Enum { .. })
413    )
414}
415
416/// Normalize a default value for enum columns - add quotes if needed
417/// This is used for SQL expressions (INSERT, UPDATE) where enum values need quoting
418pub(crate) fn normalize_enum_default(column_type: &ColumnType, value: &str) -> String {
419    if is_enum_type(column_type) && needs_quoting(value) {
420        format!("'{value}'")
421    } else {
422        value.to_string()
423    }
424}
425
426/// Check if a string default value needs quoting (is a plain string literal without quotes/parens)
427pub(super) fn needs_quoting(default_str: &str) -> bool {
428    let trimmed = default_str.trim();
429    // Empty string always needs quoting to become ''
430    if trimmed.is_empty() {
431        return true;
432    }
433    // Don't quote if already quoted
434    if trimmed.starts_with('\'') || trimmed.starts_with('"') {
435        return false;
436    }
437    // Don't quote if it's a function call
438    if trimmed.contains('(') || trimmed.contains(')') {
439        return false;
440    }
441    // Don't quote NULL
442    if trimmed.eq_ignore_ascii_case("null") {
443        return false;
444    }
445    // Don't quote special SQL keywords
446    if trimmed.eq_ignore_ascii_case("current_timestamp")
447        || trimmed.eq_ignore_ascii_case("current_date")
448        || trimmed.eq_ignore_ascii_case("current_time")
449    {
450        return false;
451    }
452    true
453}
454
455/// Build `sea_query` `ColumnDef` from vespertide `ColumnDef` for a specific backend with table-aware enum naming
456pub(crate) fn build_sea_column_def_with_table(
457    backend: DatabaseBackend,
458    table: &str,
459    column: &ColumnDef,
460) -> SeaColumnDef {
461    let mut col = SeaColumnDef::new(Alias::new(&column.name));
462    apply_column_type_with_table(&mut col, &column.r#type, table, backend);
463
464    if !column.nullable {
465        col.not_null();
466    }
467
468    if let Some(default) = &column.default {
469        let default_str = default.to_sql();
470        let converted = convert_default_for_backend(&default_str, backend);
471
472        // Auto-quote enum default values if the value is a string and needs quoting
473        let final_default =
474            if is_enum_type(&column.r#type) && default.is_string() && needs_quoting(&converted) {
475                format!("'{converted}'")
476            } else {
477                converted
478            };
479
480        // SQLite requires DEFAULT (expr) for expressions containing function calls.
481        // Wrapping in parentheses is always safe for all backends.
482        let final_default = if backend == DatabaseBackend::Sqlite
483            && final_default.contains('(')
484            && !final_default.starts_with('(')
485        {
486            format!("({final_default})")
487        } else {
488            final_default
489        };
490
491        col.default(Into::<SimpleExpr>::into(sea_query::Expr::cust(
492            final_default,
493        )));
494    }
495
496    col
497}
498
499/// Generate CREATE TYPE SQL for an enum type (`PostgreSQL` only)
500/// Returns None for non-PostgreSQL backends or non-enum types
501///
502/// The enum type name will be prefixed with the table name to avoid conflicts
503/// across tables using the same enum name (e.g., "status", "gender").
504pub(crate) fn build_create_enum_type_sql(
505    table: &str,
506    column_type: &ColumnType,
507) -> Option<super::types::RawSql> {
508    if let ColumnType::Complex(ComplexColumnType::Enum { name, values }) = column_type {
509        // Integer enums don't need CREATE TYPE - they use INTEGER column
510        if values.is_integer() {
511            return None;
512        }
513
514        let values_sql = values.sql_values_joined(", ");
515
516        // Generate unique type name with table prefix
517        let type_name = build_enum_type_name(table, name);
518
519        // PostgreSQL: CREATE TYPE {table}_{name} AS ENUM (...)
520        let type_name = quote_ident(&type_name, DatabaseBackend::Postgres);
521        let pg_sql = format!("CREATE TYPE {type_name} AS ENUM ({values_sql})");
522
523        // MySQL: ENUMs are inline, no CREATE TYPE needed
524        // SQLite: Uses TEXT, no CREATE TYPE needed
525        Some(super::types::RawSql::postgres_only(pg_sql))
526    } else {
527        None
528    }
529}
530
531/// Generate DROP TYPE SQL for an enum type (`PostgreSQL` only)
532/// Returns None for non-PostgreSQL backends or non-enum types
533///
534/// The enum type name will be prefixed with the table name to match the CREATE TYPE.
535pub(crate) fn build_drop_enum_type_sql(
536    table: &str,
537    column_type: &ColumnType,
538) -> Option<super::types::RawSql> {
539    if let ColumnType::Complex(ComplexColumnType::Enum { name, .. }) = column_type {
540        // Generate the same unique type name used in CREATE TYPE
541        let type_name = build_enum_type_name(table, name);
542
543        // PostgreSQL: DROP TYPE {table}_{name}
544        let type_name = quote_ident(&type_name, DatabaseBackend::Postgres);
545        let pg_sql = format!("DROP TYPE {type_name}");
546
547        // MySQL/SQLite: No action needed
548        Some(super::types::RawSql::postgres_only(pg_sql))
549    } else {
550        None
551    }
552}
553
554// Re-export naming functions from vespertide-naming
555pub(crate) use vespertide_naming::{
556    build_check_constraint_name, build_enum_type_name, build_foreign_key_name, build_index_name,
557    build_unique_constraint_name,
558};
559
560/// Generate CHECK constraint expression for `SQLite` enum column
561/// Returns the constraint clause like: CONSTRAINT "`chk_table_col`" CHECK (col IN ('val1', 'val2'))
562pub(crate) fn build_sqlite_enum_check_clause(
563    table: &str,
564    column: &str,
565    column_type: &ColumnType,
566) -> Option<String> {
567    if let ColumnType::Complex(ComplexColumnType::Enum { values, .. }) = column_type {
568        let name = build_check_constraint_name(table, column);
569        let values_sql = values.sql_values_joined(", ");
570        let name = quote_ident(&name, DatabaseBackend::Sqlite);
571        let column = quote_ident(column, DatabaseBackend::Sqlite);
572        Some(format!(
573            "CONSTRAINT {name} CHECK ({column} IN ({values_sql}))"
574        ))
575    } else {
576        None
577    }
578}
579
580/// Collect all CHECK constraints for enum columns in a table (for `SQLite`)
581pub(crate) fn collect_sqlite_enum_check_clauses(table: &str, columns: &[ColumnDef]) -> Vec<String> {
582    columns
583        .iter()
584        .filter_map(|col| build_sqlite_enum_check_clause(table, &col.name, &col.r#type))
585        .collect()
586}
587
588/// Extract CHECK constraint clauses from a list of table constraints.
589/// Returns SQL fragments like: `CONSTRAINT "chk_name" CHECK (expr)`
590pub(crate) fn extract_check_clauses(constraints: &[TableConstraint]) -> Vec<String> {
591    constraints
592        .iter()
593        .filter_map(|c| {
594            if let TableConstraint::Check { name, expr, .. } = c {
595                let name = quote_ident(name, DatabaseBackend::Sqlite);
596                Some(format!("CONSTRAINT {name} CHECK ({expr})"))
597            } else {
598                None
599            }
600        })
601        .collect()
602}
603
604/// Collect ALL CHECK constraint clauses for a `SQLite` temp table.
605/// Combines both:
606/// - Enum-based CHECK constraints (from column types)
607/// - Explicit CHECK constraints (from `TableConstraint::Check`)
608///
609/// Returns deduplicated union of both.
610pub(crate) fn collect_all_check_clauses(
611    table: &str,
612    columns: &[ColumnDef],
613    constraints: &[TableConstraint],
614) -> Vec<String> {
615    let mut clauses = collect_sqlite_enum_check_clauses(table, columns);
616    // Enum clauses are already unique among themselves (one per column), so the
617    // membership set only needs to reject explicit clauses that collide with an
618    // enum clause or with an earlier-kept explicit clause. Build the dedup set
619    // from borrowed `&str` views of the enum clauses (no per-enum-clause `String`
620    // clone) plus each kept explicit clause's `&str`, filtering the explicit list
621    // into `retained` BEFORE touching `clauses` so no borrow into `clauses`
622    // overlaps its later mutation. Insertion order is preserved: enum clauses
623    // first, then explicit clauses in source order.
624    let explicit = extract_check_clauses(constraints);
625    let mut retained: Vec<String> = Vec::with_capacity(explicit.len());
626    {
627        // `seen` borrows the enum clauses out of `clauses`, so keep it in an
628        // inner scope whose borrows end before `clauses` is mutated below. This
629        // seeds the dedup set from borrowed `&str` — no per-enum-clause `String`
630        // clone (the win) — and interns each kept explicit clause's `&str` so
631        // explicit-vs-explicit dedup stays a single O(log n) lookup. Only the
632        // explicit clauses actually kept are moved into `retained`.
633        let mut seen: std::collections::BTreeSet<&str> =
634            clauses.iter().map(String::as_str).collect();
635        for clause in &explicit {
636            if seen.insert(clause.as_str()) {
637                retained.push(clause.clone());
638            }
639        }
640    }
641    clauses.extend(retained);
642    clauses
643}
644
645/// Build CREATE TABLE query with CHECK constraints properly embedded.
646/// sea-query doesn't support CHECK constraints natively, so we inject them
647/// by modifying the generated SQL string.
648pub(crate) fn build_create_with_checks(
649    backend: DatabaseBackend,
650    create_stmt: &sea_query::TableCreateStatement,
651    check_clauses: &[String],
652) -> BuiltQuery {
653    if check_clauses.is_empty() {
654        BuiltQuery::CreateTable(Box::new(create_stmt.clone()))
655    } else {
656        let base_sql = build_schema_statement(create_stmt, backend);
657        let mut modified_sql = base_sql;
658        if let Some(pos) = modified_sql.rfind(')') {
659            let check_sql = check_clauses.join(", ");
660            // Insert `", " + check_sql` at `pos` without an extra `format!`
661            // allocation: the joined text goes in first, then the separator is
662            // inserted BEFORE it at the same `pos`, yielding `", <check_sql>`.
663            modified_sql.insert_str(pos, &check_sql);
664            modified_sql.insert_str(pos, ", ");
665        }
666        BuiltQuery::Raw(RawSql::uniform(modified_sql))
667    }
668}
669
670/// Build the CREATE TABLE statement for a `SQLite` temp table, including all CHECK constraints.
671/// This combines `build_create_table_for_backend` with CHECK constraint injection.
672///
673/// `table` is the ORIGINAL table name (used for constraint naming).
674/// `temp_table` is the temporary table name.
675pub(crate) fn build_sqlite_temp_table_create(
676    backend: DatabaseBackend,
677    temp_table: &str,
678    table: &str,
679    columns: &[ColumnDef],
680    constraints: &[TableConstraint],
681) -> BuiltQuery {
682    let create_stmt = build_create_table_for_backend(backend, temp_table, columns, constraints);
683    let check_clauses = collect_all_check_clauses(table, columns, constraints);
684    build_create_with_checks(backend, &create_stmt, &check_clauses)
685}
686
687/// Canonical `INSERT INTO temp_table (cols) SELECT cols FROM table` builder used
688/// by every `SQLite` temp-table rebuild path.
689///
690/// Six call sites previously open-coded this identical sequence (delete column,
691/// modify column type, modify column default, modify column nullable, add
692/// constraint, replace constraint, remove constraint). Centralising it here
693/// removes the drift risk that comes with keeping six copies in lock-step.
694///
695/// `columns` is the column slice to copy — pass `&table_def.columns` for the
696/// "copy everything" rebuilds and a filtered slice (without the dropped column)
697/// for the `DELETE COLUMN` path.
698pub(super) fn build_copy_into_temp_table(
699    table: &str,
700    temp_table: &str,
701    columns: &[ColumnDef],
702) -> BuiltQuery {
703    let column_aliases: Vec<Alias> = columns
704        .iter()
705        .map(|column| Alias::new(&column.name))
706        .collect();
707
708    let mut select_query = Query::select();
709    for column_alias in &column_aliases {
710        select_query.column(column_alias.clone());
711    }
712    select_query.from(Alias::new(table));
713
714    let insert_stmt = Query::insert()
715        .into_table(Alias::new(temp_table))
716        .columns(column_aliases)
717        .select_from(select_query)
718        .expect("SQLite temp table copy SELECT should be valid")
719        .to_owned();
720
721    BuiltQuery::Insert(Box::new(insert_stmt))
722}
723
724/// Recreate all indexes (both regular and UNIQUE) after a `SQLite` temp table rebuild.
725/// After DROP TABLE + RENAME, all original indexes are gone, so plain CREATE INDEX is correct.
726///
727/// `pending_constraints` are constraints that exist in the logical schema but haven't been
728/// physically created yet (e.g., promoted from inline column definitions by `AddColumn` normalization).
729/// These will be created by separate `AddConstraint` actions later, so we must NOT recreate them here.
730pub(crate) fn recreate_indexes_after_rebuild(
731    table: &str,
732    constraints: &[TableConstraint],
733    pending_constraints: &[TableConstraint],
734) -> Vec<BuiltQuery> {
735    // perf: capacity follows the upper bound of emitted index queries, avoiding reallocations.
736    let mut queries = Vec::with_capacity(constraints.len());
737    // perf: BTreeSet membership avoids nested Vec::contains scans during SQLite rebuilds.
738    let pending_constraints: std::collections::BTreeSet<_> = pending_constraints.iter().collect();
739    // perf: `table` is loop-invariant — quote it once instead of per surviving constraint.
740    let quoted_table = quote_ident(table, DatabaseBackend::Sqlite);
741    // dedup: both Index and Unique arms differ only by name builder + `UNIQUE` keyword.
742    let mut push_index =
743        |index_name: String, columns: &[vespertide_core::ColumnName], unique: bool| {
744            let cols_sql = quote_idents(columns, DatabaseBackend::Sqlite);
745            let quoted_index = quote_ident(&index_name, DatabaseBackend::Sqlite);
746            let keyword = if unique {
747                "CREATE UNIQUE INDEX"
748            } else {
749                "CREATE INDEX"
750            };
751            let sql = format!("{keyword} {quoted_index} ON {quoted_table} ({cols_sql})");
752            queries.push(BuiltQuery::Raw(RawSql::uniform(sql)));
753        };
754    for constraint in constraints {
755        // Skip constraints that will be created by future AddConstraint actions
756        if pending_constraints.contains(constraint) {
757            continue;
758        }
759        match constraint {
760            TableConstraint::Index { name, columns } => {
761                push_index(
762                    build_index_name(table, columns, name.as_deref()),
763                    columns,
764                    false,
765                );
766            }
767            TableConstraint::Unique { name, columns, .. } => {
768                push_index(
769                    build_unique_constraint_name(table, columns, name.as_deref()),
770                    columns,
771                    true,
772                );
773            }
774            _ => {}
775        }
776    }
777    queries
778}
779
780/// Build the canonical 5-step `SQLite` temp-table rebuild sequence:
781///
782/// 1. `CREATE TABLE {table}_temp(...)` from `create_columns` + `create_constraints`
783/// 2. `INSERT INTO {table}_temp ... SELECT FROM {table}` over `copy_columns`
784/// 3. `DROP TABLE {table}`
785/// 4. `ALTER TABLE {table}_temp RENAME TO {table}`
786/// 5. Recreate indexes / UNIQUE indexes from `recreate_constraints`
787///    minus anything already in `pending_constraints`
788///
789/// Centralises the seven open-coded call sites
790/// (`add_constraint::rebuild_sqlite_table_with_added_constraint`,
791/// `remove_constraint::sqlite::rebuild_table_without_constraint`,
792/// the `SQLite` arms of `modify_column_default` / `modify_column_nullable`,
793/// `modify_column_type::sqlite_rebuild::build_modify_column_type_sqlite_temp_table`,
794/// `replace_constraint::build_sqlite_constraint_replace`, and
795/// `delete_column::sqlite_rebuild::build_delete_column_sqlite_temp_table`)
796/// that previously re-emitted the same fixed four-statement vec + index
797/// extension by hand. Each call site keeps its surrounding context
798/// (`fill_with` UPDATEs, enum DROP TYPE, ...) OUTSIDE this helper — the
799/// helper covers only the invariant rebuild contract, so emitted SQL
800/// stays byte-identical to the previous open-coded sequences (every
801/// existing snapshot must continue to match without regeneration).
802///
803/// The seven slices ARE the rebuild contract; bundling them into a
804/// struct hides which slice plays which role at each call site, so
805/// the parameters stay flat (and sit exactly at clippy's default
806/// 7-arg threshold for `too_many_arguments`).
807pub(super) fn build_sqlite_table_rebuild(
808    backend: DatabaseBackend,
809    table: &str,
810    create_columns: &[ColumnDef],
811    create_constraints: &[TableConstraint],
812    copy_columns: &[ColumnDef],
813    recreate_constraints: &[TableConstraint],
814    pending_constraints: &[TableConstraint],
815) -> Vec<BuiltQuery> {
816    let temp_table = format!("{table}_temp");
817    let create_query = build_sqlite_temp_table_create(
818        backend,
819        &temp_table,
820        table,
821        create_columns,
822        create_constraints,
823    );
824    let insert_query = build_copy_into_temp_table(table, &temp_table, copy_columns);
825    let drop_query = super::delete_table::build_delete_table(table);
826    let rename_query = super::rename_table::build_rename_table(&temp_table, table);
827    let index_queries =
828        recreate_indexes_after_rebuild(table, recreate_constraints, pending_constraints);
829    let mut queries = Vec::with_capacity(4 + index_queries.len());
830    queries.push(create_query);
831    queries.push(insert_query);
832    queries.push(drop_query);
833    queries.push(rename_query);
834    queries.extend(index_queries);
835    queries
836}
837
838/// Quote an identifier (table name, column name, constraint name) for the given backend.
839///
840/// Escapes any quote characters within the identifier to prevent SQL injection
841/// via malicious model names (defense-in-depth; identifier validation upstream
842/// is the primary defense).
843///
844/// - `PostgreSQL` / `SQLite`: `"identifier"` (double quotes; embedded `"` escaped as `""`)
845/// - `MySQL`: `` `identifier` `` (backticks; embedded `` ` `` escaped as ` `` `)
846#[must_use]
847pub fn quote_ident(name: &str, backend: DatabaseBackend) -> String {
848    let mut out = String::with_capacity(name.len() + 2);
849    quote_ident_into(&mut out, name, backend);
850    out
851}
852
853/// Append the quoted form of `name` for `backend` directly to `out`.
854///
855/// Core of [`quote_ident`], exposed as an append-style helper so
856/// multi-identifier emitters ([`quote_idents`]) can write every identifier
857/// straight into their single output buffer without a per-identifier
858/// `String` round-trip (one allocation + one memcpy saved per element).
859fn quote_ident_into(out: &mut String, name: &str, backend: DatabaseBackend) {
860    let delim = match backend {
861        DatabaseBackend::Postgres | DatabaseBackend::Sqlite => '"',
862        DatabaseBackend::MySql => '`',
863    };
864    // Hot path: every valid identifier produced by the codebase carries no
865    // embedded quote char, so one exact-size reservation suffices and the
866    // `str::replace` + `format!` double-allocation is unnecessary. Mirrors
867    // the borrowed-fast-path / owned-slow-path pattern already used by
868    // `vespertide_core::sql_escape::escape_sql_string_literal`.
869    if !name.contains(delim) {
870        out.reserve(name.len() + 2);
871        out.push(delim);
872        out.push_str(name);
873        out.push(delim);
874        return;
875    }
876    // Slow path (defense-in-depth): identifier embeds the quote char and
877    // must be escaped by doubling it. Byte-identical output to the
878    // pre-fast-path implementation.
879    let escaped = name.replace(delim, &format!("{delim}{delim}"));
880    out.reserve(escaped.len() + 2);
881    out.push(delim);
882    out.push_str(&escaped);
883    out.push(delim);
884}
885
886/// Quote a list of identifiers and join them with comma.
887#[must_use]
888pub fn quote_idents<T: AsRef<str>>(names: &[T], backend: DatabaseBackend) -> String {
889    // Write straight into one buffer instead of collecting an intermediate
890    // `Vec<String>` and joining — same output, one fewer allocation per call.
891    let mut out = String::new();
892    for (i, n) in names.iter().enumerate() {
893        if i > 0 {
894            out.push_str(", ");
895        }
896        quote_ident_into(&mut out, n.as_ref(), backend);
897    }
898    out
899}
900
901/// Build a PostgreSQL `ALTER TABLE "t" ALTER COLUMN "c" <suffix>` statement.
902///
903/// Single source of truth for the seven open-coded PG-only emit sites
904/// across [`crate::sql::modify_column_default`],
905/// [`crate::sql::modify_column_nullable`], and
906/// [`crate::sql::modify_column_type::direct`]. Each call site used to
907/// repeat the same `quote_ident(table, …) + quote_ident(column, …) +
908/// format!("ALTER TABLE {qt} ALTER COLUMN {qc} <suffix>")` skeleton; this
909/// helper folds them into one call so the next PG ALTER variant only has
910/// to choose its suffix. SQL output is byte-identical (every existing
911/// snapshot must continue to match).
912#[must_use]
913pub(super) fn build_pg_alter_column_sql(table: &str, column: &str, suffix: &str) -> String {
914    let quoted_table = quote_ident(table, DatabaseBackend::Postgres);
915    let quoted_column = quote_ident(column, DatabaseBackend::Postgres);
916    format!("ALTER TABLE {quoted_table} ALTER COLUMN {quoted_column} {suffix}")
917}
918
919/// Look up `table` in `current_schema` and return a reference to its
920/// [`TableDef`], or a uniform `QueryError::SchemaError` describing why the
921/// lookup is mandatory for the calling backend.
922///
923/// Centralises the six SQL-builder call sites that previously open-coded
924/// the same `current_schema.iter().find(...).ok_or_else(...)` chain. The
925/// emitted message is `"Table '{table}' not found in current schema.
926/// {context}."` — pass `context` WITHOUT a trailing period so the helper
927/// is the single source of truth for the sentence terminator.
928pub(crate) fn require_table_in_schema<'a>(
929    schema: &'a [TableDef],
930    table: &str,
931    context: &str,
932) -> Result<&'a TableDef, crate::QueryError> {
933    schema.iter().find(|t| t.name == table).ok_or_else(|| {
934        crate::QueryError::SchemaError(format!(
935            "Table '{table}' not found in current schema. {context}."
936        ))
937    })
938}
939
940/// Build a `DROP INDEX` query for the given table-qualified `index_name`.
941///
942/// Single source of truth for the
943/// `sea_query::Index::drop().table(...).name(...)` shape. Centralises the
944/// four call sites that previously open-coded it (the SQLite-rebuild
945/// fallback in [`crate::sql::remove_constraint`], the Postgres / MySQL
946/// `Index` arms there, and the two inline constraint-drop arms in
947/// [`crate::sql::delete_column`]).
948#[must_use]
949pub(crate) fn build_drop_index_query(table: &str, index_name: &str) -> BuiltQuery {
950    let idx_drop = sea_query::Index::drop()
951        .table(Alias::new(table))
952        .name(index_name)
953        .to_owned();
954    BuiltQuery::DropIndex(Box::new(idx_drop))
955}
956
957/// Look up `column` in `table_def.columns` and return a reference to its
958/// [`ColumnDef`], or a uniform `QueryError::SchemaError` describing the
959/// miss. Mirrors [`require_table_in_schema`] one level down: centralises
960/// the call sites that previously open-coded
961/// `table_def.columns.iter().find(|c| c.name == column).ok_or_else(...)`
962/// with the canonical `"Column '{column}' not found in table '{table}'."`
963/// message (trailing period included so callers stay byte-identical to
964/// existing string-match assertions).
965pub(crate) fn require_column_in_table<'a>(
966    table_def: &'a TableDef,
967    column: &str,
968) -> Result<&'a ColumnDef, crate::QueryError> {
969    table_def
970        .columns
971        .iter()
972        .find(|c| c.name == column)
973        .ok_or_else(|| {
974            crate::QueryError::SchemaError(format!(
975                "Column '{column}' not found in table '{table}'.",
976                table = table_def.name,
977            ))
978        })
979}
980
981/// Build the `MySQL ALTER TABLE ... MODIFY COLUMN ...` query produced by
982/// every "modify one ColumnDef field" builder (nullability, default, …).
983///
984/// Folds the byte-identical six-line MySQL dispatch — look up the table,
985/// look up the column, clone & mutate one field, hand the result to
986/// `build_sea_column_def_with_table`, wrap as an `AlterTable` — into a
987/// single helper. `context` is the descriptor appended to the canonical
988/// `require_table_in_schema` error message ("MySQL requires …"); pass it
989/// WITHOUT a trailing period (the helper adds it).
990///
991/// `mutator` rewrites whichever single field the caller cares about
992/// (`|c| c.nullable = …`, `|c| c.default = …`, …) on a fresh clone, so
993/// the original `current_schema` is never mutated.
994///
995/// `modify_column_comment` deliberately does NOT use this helper: its
996/// MySQL emit hand-appends a `COMMENT '…'` suffix outside sea-query and
997/// produces `BuiltQuery::Raw` rather than `BuiltQuery::AlterTable`.
998pub(crate) fn build_mysql_modify_column_with<F>(
999    table: &str,
1000    column: &str,
1001    current_schema: &[TableDef],
1002    context: &str,
1003    mutator: F,
1004) -> Result<BuiltQuery, crate::QueryError>
1005where
1006    F: FnOnce(&mut ColumnDef),
1007{
1008    let table_def = require_table_in_schema(current_schema, table, context)?;
1009    let column_def = require_column_in_table(table_def, column)?;
1010    let mut modified_col_def = column_def.clone();
1011    mutator(&mut modified_col_def);
1012    let sea_col = build_sea_column_def_with_table(DatabaseBackend::MySql, table, &modified_col_def);
1013    let stmt = Table::alter()
1014        .table(Alias::new(table))
1015        .modify_column(sea_col)
1016        .to_owned();
1017    Ok(BuiltQuery::AlterTable(Box::new(stmt)))
1018}
1019
1020/// Build the canonical `SQLite` temp-table rebuild for every "modify one
1021/// ColumnDef field" builder (nullability, default, …). Every other column
1022/// stays untouched; `mutator` rewrites only the single column the action
1023/// targets — matching the existing
1024/// `new_columns.iter_mut().find(|c| c.name == column)` shape, including
1025/// the silent no-op when the column is absent (which is the documented
1026/// behaviour: see the `column_not_found` test in `modify_column_nullable`).
1027///
1028/// Folds the byte-identical nine-line SQLite dispatch — look up the
1029/// table, clone the column list, rewrite one column, forward the rebuild
1030/// contract to `build_sqlite_table_rebuild` — into a single helper.
1031/// `context` is the descriptor appended to the canonical
1032/// `require_table_in_schema` error message.
1033pub(crate) fn build_sqlite_modify_column_with<F>(
1034    table: &str,
1035    column: &str,
1036    current_schema: &[TableDef],
1037    pending_constraints: &[TableConstraint],
1038    context: &str,
1039    mutator: F,
1040) -> Result<Vec<BuiltQuery>, crate::QueryError>
1041where
1042    F: FnOnce(&mut ColumnDef),
1043{
1044    let table_def = require_table_in_schema(current_schema, table, context)?;
1045    let mut new_columns = table_def.columns.clone();
1046    if let Some(col) = new_columns.iter_mut().find(|c| c.name == column) {
1047        mutator(col);
1048    }
1049    Ok(build_sqlite_table_rebuild(
1050        DatabaseBackend::Sqlite,
1051        table,
1052        &new_columns,
1053        &table_def.constraints,
1054        &table_def.columns,
1055        &table_def.constraints,
1056        pending_constraints,
1057    ))
1058}
1059
1060/// Look up `column` in the table named `table` inside `schema`, returning
1061/// `None` when either the table or the column is absent.
1062///
1063/// Centralises the `schema.iter().find(|t| t.name == table).and_then(|t|
1064/// t.columns.iter().find(|c| c.name == column))` chain used by the five
1065/// non-error-returning column lookups across the SQL builders (the
1066/// `DeleteColumn` dispatcher, the Postgres `ModifyColumnDefault` path,
1067/// and the three direct `ModifyColumnType` helpers). The error-returning
1068/// twin lives one helper up as [`require_column_in_table`].
1069#[must_use]
1070pub(crate) fn find_column_in_schema<'a>(
1071    schema: &'a [TableDef],
1072    table: &str,
1073    column: &str,
1074) -> Option<&'a ColumnDef> {
1075    schema
1076        .iter()
1077        .find(|t| t.name == table)
1078        .and_then(|t| t.columns.iter().find(|c| c.name == column))
1079}
1080
1081#[cfg(test)]
1082mod tests {
1083    use super::*;
1084    use sea_query::{Alias, ColumnDef as SeaColDef, Table};
1085
1086    /// `build_create_with_checks` early-returns a plain `CreateTable` query
1087    /// when `check_clauses` is empty (no string-injection round-trip).
1088    /// Covers the `if check_clauses.is_empty() { ... }` true-branch.
1089    #[test]
1090    fn build_create_with_checks_empty_clauses_returns_plain_create_table() {
1091        let mut stmt = Table::create();
1092        stmt.table(Alias::new("users"))
1093            .col(SeaColDef::new(Alias::new("id")).integer().not_null());
1094        let query = build_create_with_checks(DatabaseBackend::Postgres, &stmt, &[]);
1095        let sql = query.build(DatabaseBackend::Postgres);
1096        assert!(
1097            sql.contains("CREATE TABLE"),
1098            "expected CREATE TABLE in: {sql}"
1099        );
1100        // No CHECK clauses appended.
1101        assert!(
1102            !sql.contains("CHECK ("),
1103            "no CHECK should be injected: {sql}"
1104        );
1105        // The empty-branch path returns a `CreateTable` variant (not `Raw`).
1106        assert!(
1107            matches!(query, BuiltQuery::CreateTable(_)),
1108            "empty-checks branch must return BuiltQuery::CreateTable"
1109        );
1110    }
1111
1112    /// `require_table_in_schema` returns the matching table when present.
1113    #[test]
1114    fn require_table_in_schema_hit_returns_table_ref() {
1115        let schema = vec![TableDef {
1116            name: "users".into(),
1117            description: None,
1118            columns: vec![],
1119            constraints: vec![],
1120        }];
1121        let table_def = require_table_in_schema(&schema, "users", "test context")
1122            .expect("table 'users' should be found in the schema");
1123        assert_eq!(table_def.name.as_str(), "users");
1124    }
1125
1126    /// `require_table_in_schema` emits the canonical `SchemaError`
1127    /// message — same template as every replaced call site, so wire-error
1128    /// output stays byte-identical for callers and downstream tests.
1129    #[test]
1130    fn require_table_in_schema_miss_emits_canonical_message() {
1131        let schema: Vec<TableDef> = vec![];
1132        let err = require_table_in_schema(&schema, "missing", "SQLite requires test context")
1133            .expect_err("missing table must error");
1134        let msg = err.to_string();
1135        assert!(
1136            msg.contains(
1137                "Table 'missing' not found in current schema. SQLite requires test context."
1138            ),
1139            "expected canonical message with trailing period, got: {msg}"
1140        );
1141    }
1142}
vespertide_query/sql/helpers.rs

vespertide_query/sql/
helpers.rs
