fraiseql_db/dialect/

trait_def.rs

//! `SqlDialect` trait — dialect-specific SQL rendering primitives.

use std::borrow::Cow;

use crate::types::sql_hints::ScalarFieldType;

/// Column type used by row-shaped view (`vr_*`) DDL generation.
///
/// Maps GraphQL scalar types to their SQL equivalents for typed column
/// extraction from JSON/JSONB data columns.
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum RowViewColumnType {
    /// Text / varchar column.
    Text,
    /// 32-bit integer.
    Int32,
    /// 64-bit integer.
    Int64,
    /// 64-bit floating point.
    Float64,
    /// Boolean.
    Boolean,
    /// UUID.
    Uuid,
    /// Timestamp with timezone.
    Timestamptz,
    /// JSON / JSONB.
    Json,
    /// Date (without time).
    Date,
}

/// Error returned when an operator is not supported by a dialect.
#[derive(Debug)]
pub struct UnsupportedOperator {
    /// Dialect name (e.g., "MySQL").
    pub dialect:  &'static str,
    /// Operator name (e.g., "ArrayContainedBy").
    pub operator: &'static str,
}

impl std::fmt::Display for UnsupportedOperator {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        write!(
            f,
            "operator `{}` is not supported by the {} dialect",
            self.operator, self.dialect
        )
    }
}

impl std::error::Error for UnsupportedOperator {}

/// Dialect-specific SQL rendering primitives for WHERE clause generation.
///
/// Implement this trait to add a new database backend.  All methods that are
/// identical across dialects have default implementations — override only
/// what your dialect requires.
///
/// # Security contract
///
/// Implementations MUST:
/// - Never interpolate user-supplied values into the returned SQL string. Use [`Self::placeholder`]
///   and append values to the `params` vector instead.
/// - Escape field / column names via the `path_escape` module.
/// - Escape literal SQL identifiers (not values) by doubling the delimiter.
pub trait SqlDialect: Send + Sync + 'static {
    // ── Core primitives (must implement) ──────────────────────────────────────

    /// Dialect name for error messages (e.g., `"PostgreSQL"`, `"MySQL"`).
    fn name(&self) -> &'static str;

    /// Quote a database identifier (table or column name).
    ///
    /// # Examples
    /// - PostgreSQL: `v_user` → `"v_user"`,  `evil"name` → `"evil""name"`
    /// - MySQL:      `v_user` → `` `v_user` ``, `` evil`name `` → `` `evil``name` ``
    /// - SQL Server: `v_user` → `[v_user]`,   `evil]name` → `[evil]]name]`
    fn quote_identifier(&self, name: &str) -> String;

    /// Generate SQL to extract a scalar string from a JSON/JSONB column.
    ///
    /// `column` is the unquoted column name (typically `"data"`).
    /// `path` is the slice of field-name segments (pre-escaped by caller if needed).
    ///
    /// # Examples
    /// - PostgreSQL (1 segment): `data->>'field'`
    /// - MySQL: `JSON_UNQUOTE(JSON_EXTRACT(data, '$.outer.inner'))`
    /// - SQLite: `json_extract(data, '$.outer.inner')`
    /// - SQL Server: `JSON_VALUE(data, '$.outer.inner')`
    fn json_extract_scalar(&self, column: &str, path: &[String]) -> String;

    /// Next parameter placeholder.  Called with the current 1-based index.
    ///
    /// - PostgreSQL: `$1`, `$2`, …
    /// - SQL Server: `@p1`, `@p2`, …
    /// - MySQL / SQLite: `?`
    fn placeholder(&self, n: usize) -> String;

    // ── Scalar type casts ──────────────────────────────────────────────────────

    /// SQL type name this dialect casts a JSON-extracted value to for `ty`,
    /// or `None` when the raw `text` extraction is already the right type.
    ///
    /// This is **the** type → SQL-type table for the dialect. Both the ORDER BY
    /// renderer ([`DatabaseType::typed_json_field_expr`]) and the WHERE
    /// generator read it, so a sort and a filter on the same field can never
    /// disagree about how that field is typed.
    ///
    /// [`DatabaseType::typed_json_field_expr`]: crate::types::DatabaseType::typed_json_field_expr
    fn cast_type_name(&self, ty: ScalarFieldType) -> Option<&'static str>;

    /// Wrap a JSON-extracted scalar expression so it compares as `ty`.
    ///
    /// Default renders ANSI `CAST(expr AS type)`; PostgreSQL overrides with
    /// `(expr)::type`. `ScalarFieldType::Text` — and any type the dialect has no
    /// cast for — returns the expression unchanged.
    fn cast_expr_as<'a>(&self, expr: &'a str, ty: ScalarFieldType) -> Cow<'a, str> {
        match self.cast_type_name(ty) {
            Some(name) => Cow::Owned(format!("CAST({expr} AS {name})")),
            None => Cow::Borrowed(expr),
        }
    }

    /// Wrap a parameter placeholder so it is compared as `ty`.
    ///
    /// PostgreSQL overrides this with `({p}::text)::type`: tokio-postgres binds
    /// JSON scalars as text, and without forcing the server to resolve the
    /// parameter as `text` first it expects the binary encoding of the target
    /// type. sqlx (MySQL, SQLite) and tiberius (SQL Server) send string
    /// parameters as text regardless, so the default passes the placeholder
    /// through and lets the database coerce it.
    fn cast_param_as<'a>(&self, placeholder: &'a str, _ty: ScalarFieldType) -> Cow<'a, str> {
        Cow::Borrowed(placeholder)
    }

    /// Wrap a parameter placeholder for a native-column equality condition.
    ///
    /// `native_type` is the PostgreSQL canonical type name stored in
    /// `native_columns` (e.g. `"uuid"`, `"int4"`, `"timestamp"`).
    ///
    /// **Only PostgreSQL needs to override this.**  tokio-postgres uses a binary
    /// wire protocol: when the query contains `$1::uuid`, the server resolves `$1`
    /// as OID 2950 and expects 16-byte binary UUID encoding, but `QueryParam::Text`
    /// sends UTF-8 bytes, causing "incorrect binary data format".  The two-step
    /// cast `$1::text::uuid` forces the server to resolve `$1` as `text` first.
    ///
    /// sqlx (MySQL, SQLite) and tiberius (SQL Server) send string parameters as
    /// text by default regardless of column type, so no cast is needed — the
    /// database coerces the text value at comparison time.
    ///
    /// Default: return the placeholder unchanged (correct for MySQL, SQLite,
    /// SQL Server).
    fn cast_native_param(&self, placeholder: &str, _native_type: &str) -> String {
        placeholder.to_string()
    }

    // ── LIKE / pattern matching ────────────────────────────────────────────────

    /// Trailing `ESCAPE` clause required to make `escape_like_literal`'s
    /// backslash escaping effective on this dialect, or `""` when the dialect
    /// already treats `\` as the escape character.
    ///
    /// PostgreSQL and MySQL default to `\`; SQLite and SQL Server have **no**
    /// default escape character, so without this clause an escaped `\%` matches
    /// a literal backslash followed by the wildcard — the pattern over-matches
    /// instead of being neutralised (#722).
    fn like_escape_clause(&self) -> &'static str {
        ""
    }

    /// SQL fragment for case-sensitive LIKE: `lhs LIKE rhs`.
    fn like_sql(&self, lhs: &str, rhs: &str) -> String {
        format!("{lhs} LIKE {rhs}{}", self.like_escape_clause())
    }

    /// SQL fragment for case-insensitive LIKE.
    ///
    /// Default: `LOWER(lhs) LIKE LOWER(rhs)` (MySQL / SQLite compatible).
    /// PostgreSQL overrides with `lhs ILIKE rhs`.
    /// SQL Server overrides with `lhs LIKE rhs COLLATE Latin1_General_CI_AI`.
    fn ilike_sql(&self, lhs: &str, rhs: &str) -> String {
        format!("LOWER({lhs}) LIKE LOWER({rhs}){}", self.like_escape_clause())
    }

    /// String concatenation operator / function for building LIKE patterns.
    ///
    /// Default: `||` (ANSI SQL — works for PostgreSQL and SQLite).
    /// MySQL overrides with `CONCAT(…)`.
    /// SQL Server overrides with `+`.
    fn concat_sql(&self, parts: &[&str]) -> String {
        parts.join(" || ")
    }

    // ── Empty clause sentinels ─────────────────────────────────────────────────

    /// SQL literal for "always false" (used for empty IN clauses, empty OR).
    ///
    /// Default: `FALSE`. SQLite and SQL Server use `1=0`.
    fn always_false(&self) -> &'static str {
        "FALSE"
    }

    /// SQL literal for "always true" (used for empty AND).
    ///
    /// Default: `TRUE`. SQLite and SQL Server use `1=1`.
    fn always_true(&self) -> &'static str {
        "TRUE"
    }

    // ── Inequality operator ────────────────────────────────────────────────────

    /// SQL inequality operator.  Default `!=`.  SQL Server uses `<>`.
    fn neq_operator(&self) -> &'static str {
        "!="
    }

    // ── Array length function ──────────────────────────────────────────────────

    /// SQL expression for the length of a JSON array stored in `expr`.
    fn json_array_length(&self, expr: &str) -> String;

    // ── Array containment (returns Err if not supported) ──────────────────────

    /// SQL for "array contains this element".
    ///
    /// Default: returns `Err(UnsupportedOperator)`.
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support array containment.
    fn array_contains_sql(&self, _lhs: &str, _rhs: &str) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "ArrayContains",
        })
    }

    /// SQL for "element is contained by array".
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support array containment.
    fn array_contained_by_sql(
        &self,
        _lhs: &str,
        _rhs: &str,
    ) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "ArrayContainedBy",
        })
    }

    /// SQL for "arrays overlap".
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support array overlap.
    fn array_overlaps_sql(&self, _lhs: &str, _rhs: &str) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "ArrayOverlaps",
        })
    }

    // ── Full-text search (returns Err if not supported) ────────────────────────

    /// SQL for `to_tsvector(expr) @@ to_tsquery(param)`.
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support full-text search.
    fn fts_matches_sql(&self, _expr: &str, _param: &str) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "Matches",
        })
    }

    /// SQL for plain-text full-text search.
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support plain-text FTS.
    fn fts_plain_query_sql(
        &self,
        _expr: &str,
        _param: &str,
    ) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "PlainQuery",
        })
    }

    /// SQL for phrase full-text search.
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support phrase FTS.
    fn fts_phrase_query_sql(
        &self,
        _expr: &str,
        _param: &str,
    ) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "PhraseQuery",
        })
    }

    /// SQL for web-search full-text search.
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support web-search FTS.
    fn fts_websearch_query_sql(
        &self,
        _expr: &str,
        _param: &str,
    ) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "WebsearchQuery",
        })
    }

    /// SQL for the full-text *relevance* of a document against a web-search
    /// query — the ordering half of `?search=` (#1284).
    ///
    /// `document_expr` is a text expression; `query_param` is a placeholder for
    /// the search text, which is arbitrary client input and is therefore bound
    /// rather than interpolated. Lives beside
    /// [`fts_websearch_query_sql`](Self::fts_websearch_query_sql) so the
    /// text-search configuration the two resolve against cannot drift: a rank
    /// computed under a different configuration from the predicate that matched
    /// would order rows by a document nothing searched.
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support
    /// full-text ranking.
    fn fts_rank_sql(
        &self,
        _document_expr: &str,
        _query_param: &str,
    ) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "Relevance",
        })
    }

    // ── Regex (returns Err if not supported) ───────────────────────────────────

    /// SQL for POSIX-style regex match.
    ///
    /// Default: returns `Err(UnsupportedOperator)`.
    /// PostgreSQL overrides with `~`, `~*`, `!~`, `!~*`.
    /// MySQL overrides with `REGEXP` / `NOT REGEXP`.
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support regex matching.
    fn regex_sql(
        &self,
        _lhs: &str,
        _rhs: &str,
        _case_insensitive: bool,
        _negate: bool,
    ) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "Regex",
        })
    }

    // ── PostgreSQL-only operators (Vector, Network, LTree) ────────────────────
    // These methods have default `Err` implementations; only `PostgresDialect`
    // overrides them.  Callers push parameter values before calling these methods
    // and pass the already-generated placeholder strings.

    /// Generate SQL for an INET unary check (IsIPv4, IsIPv6, IsPrivate, IsPublic, IsLoopback,
    /// IsNotLoopback).
    ///
    /// `check_name` identifies the operator (passed to `UnsupportedOperator` on failure).
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support INET checks.
    fn inet_check_sql(&self, _lhs: &str, _check_name: &str) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "InetCheck",
        })
    }

    /// Generate SQL for an INET binary operation (InSubnet, ContainsSubnet, ContainsIP, Overlaps).
    ///
    /// `pg_op` is one of `<<`, `>>`, `&&`.
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support INET binary operations.
    fn inet_binary_sql(
        &self,
        _pg_op: &str,
        _lhs: &str,
        _rhs: &str,
    ) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "InetBinaryOp",
        })
    }

    /// Generate SQL for an LTree binary operator.
    ///
    /// `pg_op` is one of `@>`, `<@`, `~`, `@`.
    /// `rhs_type` is the cast type for `rhs` (e.g., `"ltree"`, `"lquery"`, `"ltxtquery"`).
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support LTree operations.
    fn ltree_binary_sql(
        &self,
        _pg_op: &str,
        _lhs: &str,
        _rhs: &str,
        _rhs_type: &str,
    ) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "LTreeBinaryOp",
        })
    }

    /// Generate SQL for `ltree ? ARRAY[...]` (MatchesAnyLquery).
    ///
    /// `placeholders` contains pre-formatted placeholder strings
    /// (e.g., `["$1::lquery", "$2::lquery"]`).
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support LTree lquery arrays.
    fn ltree_any_lquery_sql(
        &self,
        _lhs: &str,
        _placeholders: &[String],
    ) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "MatchesAnyLquery",
        })
    }

    /// Generate SQL for `nlevel(ltree) OP param` (depth comparison operators).
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support LTree depth comparisons.
    fn ltree_depth_sql(
        &self,
        _op: &str,
        _lhs: &str,
        _rhs: &str,
    ) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "LTreeDepth",
        })
    }

    /// Generate SQL for ID-based ltree operators (`descendantOfId`, `ancestorOfId`).
    ///
    /// Generates a subquery that resolves the ltree path from an entity UUID:
    /// - Self-referencing: `field_expr <@ (SELECT path FROM t WHERE id = $1)`
    /// - Cross-table: `fk IN (SELECT id FROM t WHERE path <@ (SELECT path FROM t WHERE id = $1))`
    ///
    /// `pg_op` is `"<@"` for `descendantOfId` or `"@>"` for `ancestorOfId`.
    /// `field_expr` is the resolved LHS expression (e.g., `data->>'category_path'`).
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support LTree operations.
    fn ltree_id_subquery_sql(
        &self,
        _pg_op: &str,
        _field_expr: &str,
        _table: &str,
        _path_column: &str,
        _fk_column: Option<&str>,
        _param: &str,
    ) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "LTreeIdSubquery",
        })
    }

    /// Generate SQL for `ltree = lca(ARRAY[...])` (Lca operator).
    ///
    /// `placeholders` contains pre-formatted placeholder strings
    /// (e.g., `["$1::ltree", "$2::ltree"]`).
    ///
    /// # Errors
    ///
    /// Returns [`UnsupportedOperator`] if this dialect does not support LTree LCA.
    fn ltree_lca_sql(
        &self,
        _lhs: &str,
        _placeholders: &[String],
    ) -> Result<String, UnsupportedOperator> {
        Err(UnsupportedOperator {
            dialect:  self.name(),
            operator: "Lca",
        })
    }

    // ── Row-view DDL helpers (for gRPC transport) ──────────────────────────────

    /// Generate a SQL expression that extracts a scalar field from a JSON column
    /// and casts it to the given [`RowViewColumnType`].
    ///
    /// `json_column` is the unquoted column name (e.g., `"data"`).
    /// `field_name` is the JSON key to extract.
    /// `col_type` selects the target SQL type.
    ///
    /// Default: panics — each dialect must override.
    fn row_view_column_expr(
        &self,
        json_column: &str,
        field_name: &str,
        col_type: &RowViewColumnType,
    ) -> String {
        let _ = (json_column, field_name, col_type);
        // Reason: this is a default-impl panic that fires only if a dialect
        // forgets to override; that's a compile-time-discoverable programmer
        // error, not user input, so a panic on first call is the right
        // failure mode.
        #[allow(clippy::panic)]
        {
            panic!("{} dialect has not implemented row_view_column_expr", self.name())
        }
    }

    /// Generate the full DDL statement(s) to create a row-shaped view.
    ///
    /// `view_name` is the unquoted view name (e.g., `"vr_user"`).
    /// `source_table` is the unquoted source table (e.g., `"tb_user"`).
    /// `columns` are `(alias, expression)` pairs from [`Self::row_view_column_expr`].
    ///
    /// Default: `CREATE OR REPLACE VIEW "view" AS SELECT ... FROM "table"`.
    fn create_row_view_ddl(
        &self,
        view_name: &str,
        source_table: &str,
        columns: &[(String, String)],
    ) -> String {
        let quoted_view = self.quote_identifier(view_name);
        let quoted_table = self.quote_identifier(source_table);
        let col_list: Vec<String> = columns
            .iter()
            .map(|(alias, expr)| format!("{expr} AS {}", self.quote_identifier(alias)))
            .collect();
        format!(
            "CREATE OR REPLACE VIEW {quoted_view} AS\nSELECT\n  {}\nFROM {quoted_table};",
            col_list.join(",\n  ")
        )
    }
}