fraiseql_db/

projection_generator.rs

//! SQL Projection Query Generator
//!
//! Generates database-specific SQL for field projection optimization.
//!
//! # Overview
//!
//! When a schema type has a `SqlProjectionHint`, this module generates the actual SQL
//! to project only requested fields at the database level, reducing network payload
//! and JSON deserialization overhead.
//!
//! # Supported Databases
//!
//! - PostgreSQL: Uses `jsonb_build_object()` for efficient field selection
//! - MySQL, SQLite, SQL Server: Multi-database support
//!
//! # Example
//!
//! ```rust
//! use fraiseql_db::projection_generator::PostgresProjectionGenerator;
//! # use fraiseql_error::Result;
//! # fn example() -> Result<()> {
//! let generator = PostgresProjectionGenerator::new();
//! let fields = vec!["id".to_string(), "name".to_string(), "email".to_string()];
//! let sql = generator.generate_projection_sql(&fields)?;
//! assert!(sql.contains("jsonb_build_object"));
//! # Ok(())
//! # }
//! ```

use fraiseql_error::{FraiseQLError, Result};

/// The semantic kind of a projection field, determining which JSONB extraction
/// operator to use in generated SQL.
///
/// - `Text` → `->>` (extracts as text — for String and ID scalars)
/// - `Native` → `->` (preserves native JSON type — Int, Float, Boolean, DateTime, etc.)
/// - `Composite` → `->` (preserves full JSONB structure)
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum FieldKind {
    /// Text scalar — extracted with `->>` (String, ID).
    Text,
    /// Native JSON scalar — extracted with `->` to preserve type (Int, Float, Boolean, DateTime,
    /// etc.).
    Native,
    /// Object or list — extracted with `->` to preserve JSONB structure.
    Composite,
}

/// A SQL expression a projection may emit verbatim, in place of reading a key
/// out of the JSONB column (#959).
///
/// The inner string is private and there is **no public constructor**: the only
/// way to obtain one is
/// [`vector_distance_expr`](crate::order_by::vector_distance_expr), which builds
/// it from a validated identifier, an operator drawn from a fixed set and a
/// literal whose character set it re-checks. A projection is SQL the caller does
/// not get to write, and this type is what enforces that across the crate
/// boundary — `fraiseql-core` can carry one into a `ProjectionField` but cannot
/// invent one.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ComputedExpr(String);

impl ComputedExpr {
    /// Wrap an expression assembled from validated parts.
    pub(crate) const fn from_validated_parts(sql: String) -> Self {
        Self(sql)
    }

    /// The expression as it will appear in the SELECT list.
    #[must_use]
    pub fn as_sql(&self) -> &str {
        &self.0
    }
}

/// A field in a SQL projection with type information.
///
/// Used by typed projection generators to choose the correct JSONB extraction
/// operator based on [`FieldKind`]: `->` (preserves JSONB) for composites and
/// native scalars, `->>` (text) for text scalars (String, ID).
///
/// When `sub_fields` is populated on a composite field, `generate_typed_projection_sql`
/// will recurse and emit a nested `jsonb_build_object(...)` instead of returning the full
/// composite blob.  Leave `sub_fields` as `None` to get the existing `data->'field'`
/// behaviour (full blob, no sub-selection).
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ProjectionField {
    /// Output (response) key — the GraphQL alias if present, otherwise the field
    /// name. Used verbatim as the key in the generated `jsonb_build_object`.
    pub name: String,

    /// Source GraphQL field name (camelCase) used to derive the JSONB column via
    /// `to_snake_case`. Equals [`name`](Self::name) for unaliased fields, but
    /// differs when the field is aliased: `myName: fullName` yields
    /// `name = "myName"`, `source = "fullName"`, so the projection reads
    /// `data->>'full_name'` while emitting it under the `myName` key (#418).
    pub source: String,

    /// Semantic kind of the field, controlling the JSONB extraction operator.
    pub kind: FieldKind,

    /// Sub-fields to project for composite (Object) types.
    ///
    /// When `Some` and non-empty, the generator recurses and produces a nested
    /// `jsonb_build_object` instead of returning the entire composite blob.
    /// Set to `None` (or `Some([])`) to fall back to `data->'field'`.
    /// List fields should always use `None` — sub-projection inside aggregated
    /// JSONB arrays is out of scope for this first iteration.
    pub sub_fields: Option<Vec<ProjectionField>>,

    /// A SQL expression this field's value comes from, instead of the JSONB
    /// column (#959) — the vector distance a `nearest` query ordered by.
    ///
    /// When set it wins over [`kind`](Self::kind) and
    /// [`source`](Self::source): the field is not stored, so there is no key to
    /// read. See [`ComputedExpr`] for why an arbitrary string cannot get here.
    pub computed: Option<ComputedExpr>,
}

impl ProjectionField {
    /// Create a text scalar projection field (uses `->>` text extraction).
    ///
    /// Use for String and ID fields only. Other scalars (Int, Float, Boolean,
    /// DateTime, etc.) should use [`Self::native`].
    #[must_use]
    pub fn scalar(name: impl Into<String>) -> Self {
        let name = name.into();
        Self {
            source: name.clone(),
            name,
            kind: FieldKind::Text,
            sub_fields: None,
            computed: None,
        }
    }

    /// Create a native JSON scalar projection field (uses `->` to preserve type).
    ///
    /// Use for Int, Float, Boolean, DateTime, Date, Time, Decimal, Vector, and
    /// other non-text scalars. `->>` would coerce these to strings inside
    /// `jsonb_build_object`, losing type information.
    #[must_use]
    pub fn native(name: impl Into<String>) -> Self {
        let name = name.into();
        Self {
            source: name.clone(),
            name,
            kind: FieldKind::Native,
            sub_fields: None,
            computed: None,
        }
    }

    /// Create a composite (object/list) projection field (uses `->` JSONB extraction).
    #[must_use]
    pub fn composite(name: impl Into<String>) -> Self {
        let name = name.into();
        Self {
            source: name.clone(),
            name,
            kind: FieldKind::Composite,
            sub_fields: None,
            computed: None,
        }
    }

    /// Create a composite projection field with known sub-fields.
    ///
    /// The generator will recurse into `sub_fields` and emit a nested
    /// `jsonb_build_object(...)` rather than returning the full composite blob.
    #[must_use]
    pub fn composite_with_sub_fields(name: impl Into<String>, sub_fields: Vec<Self>) -> Self {
        let name = name.into();
        Self {
            source: name.clone(),
            name,
            kind: FieldKind::Composite,
            sub_fields: Some(sub_fields),
            computed: None,
        }
    }

    /// Create a field whose value is computed in SQL rather than read from the
    /// JSONB column (#959).
    ///
    /// `kind` is [`FieldKind::Native`] because the expression yields a JSON
    /// number, but the projection never consults it — a computed field has no
    /// stored key to extract.
    #[must_use]
    pub fn computed(name: impl Into<String>, expr: ComputedExpr) -> Self {
        let name = name.into();
        Self {
            source: name.clone(),
            name,
            kind: FieldKind::Native,
            sub_fields: None,
            computed: Some(expr),
        }
    }

    /// Whether this field is a composite type (Object or List).
    #[must_use]
    pub const fn is_composite(&self) -> bool {
        matches!(self.kind, FieldKind::Composite)
    }
}

impl From<String> for ProjectionField {
    fn from(name: String) -> Self {
        Self::scalar(name)
    }
}

/// Validate that a GraphQL field name contains only characters that are safe
/// for use in SQL projections (alphanumeric characters and underscores only).
///
/// GraphQL field names in FraiseQL are either snake_case (schema definitions)
/// or camelCase (after the compiler's automatic conversion). Both forms are
/// subsets of `[a-zA-Z_][a-zA-Z0-9_]*`, so this function rejects any name
/// that falls outside that alphabet.
///
/// # Errors
///
/// Returns `FraiseQLError::Validation` if `field` contains a character outside
/// `[a-zA-Z0-9_]`.
fn validate_field_name(field: &str) -> Result<()> {
    if field.chars().all(|c| c.is_ascii_alphanumeric() || c == '_') {
        Ok(())
    } else {
        Err(FraiseQLError::Validation {
            message: format!(
                "field name '{}' contains characters that cannot be safely projected; \
                 only ASCII alphanumeric characters and underscores are allowed",
                field
            ),
            path:    None,
        })
    }
}

use crate::utils::to_snake_case;

/// PostgreSQL SQL projection generator using jsonb_build_object.
///
/// Generates efficient PostgreSQL SQL that projects only requested JSONB fields,
/// reducing payload size and JSON deserialization time.
pub struct PostgresProjectionGenerator {
    /// JSONB column name (typically "data")
    jsonb_column: String,
}

impl PostgresProjectionGenerator {
    /// Create new PostgreSQL projection generator with default JSONB column name.
    ///
    /// Default JSONB column: "data"
    #[must_use]
    pub fn new() -> Self {
        Self::with_column("data")
    }

    /// Create projection generator with custom JSONB column name.
    ///
    /// # Arguments
    ///
    /// * `jsonb_column` - Name of the JSONB column in the database table
    #[must_use]
    pub fn with_column(jsonb_column: &str) -> Self {
        Self {
            jsonb_column: jsonb_column.to_string(),
        }
    }

    /// Generate PostgreSQL projection SQL for specified fields.
    ///
    /// Generates a `jsonb_build_object()` call that selects only the requested fields
    /// from the JSONB column, drastically reducing payload size.
    ///
    /// # Arguments
    ///
    /// * `fields` - GraphQL field names to project from JSONB
    ///
    /// # Returns
    ///
    /// SQL fragment that can be used in a SELECT clause, e.g.:
    /// `jsonb_build_object('id', data->>'id', 'email', data->>'email')`
    ///
    /// # Example
    ///
    /// ```rust
    /// use fraiseql_db::projection_generator::PostgresProjectionGenerator;
    /// # use fraiseql_error::Result;
    /// # fn example() -> Result<()> {
    /// let generator = PostgresProjectionGenerator::new();
    /// let fields = vec!["id".to_string(), "email".to_string()];
    /// let sql = generator.generate_projection_sql(&fields)?;
    /// // Returns:
    /// // jsonb_build_object('id', data->>'id', 'email', data->>'email')
    /// assert!(sql.contains("jsonb_build_object"));
    /// # Ok(())
    /// # }
    /// ```
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Validation` if any field name contains characters
    /// that cannot be safely included in a SQL projection.
    pub fn generate_projection_sql(&self, fields: &[String]) -> Result<String> {
        if fields.is_empty() {
            // No fields to project, return pass-through
            return Ok(format!("\"{}\"", self.jsonb_column));
        }

        // Validate all field names before generating any SQL.
        for field in fields {
            validate_field_name(field)?;
        }

        // Build the jsonb_build_object() call with all requested fields
        let field_pairs: Vec<String> = fields
            .iter()
            .map(|field| {
                // Response key uses the GraphQL field name (camelCase).
                // Used as a SQL *string literal* key (inside single-quotes): escape ' → ''.
                let safe_field = Self::escape_sql_string(field);
                // JSONB key uses the original schema field name (snake_case).
                let jsonb_key = to_snake_case(field);
                let safe_jsonb_key = Self::escape_sql_string(&jsonb_key);
                format!("'{}', \"{}\"->>'{}' ", safe_field, self.jsonb_column, safe_jsonb_key)
            })
            .collect();

        // Format: jsonb_build_object('field1', data->>'field1', 'field2', data->>'field2', ...)
        Ok(format!("jsonb_build_object({})", field_pairs.join(",")))
    }

    /// Generate type-aware PostgreSQL projection SQL.
    ///
    /// Uses `->` (JSONB extraction) for composite fields (objects, lists) and
    /// `->>` (text extraction) for scalar fields. This avoids the unnecessary
    /// text→JSON round-trip that occurs when `->>` is used for nested objects.
    ///
    /// When a composite field carries `sub_fields`, the generator recurses and
    /// emits a nested `jsonb_build_object(...)` that selects only the requested
    /// sub-fields rather than returning the entire blob, at every depth. `sub_fields` of
    /// `Some(vec![])` selects nothing of the object and renders `jsonb_build_object()`.
    /// There is no depth cap: the fields follow a selection, which the caller bounds
    /// (`max_query_depth`), and a cap that fell back to `data->'field'` served every
    /// stored key of the object below it. PostgreSQL 16's parser refuses a nest of 2 044
    /// `jsonb_build_object` levels (`memory exhausted`), two orders of magnitude above
    /// any bound a caller sets.
    ///
    /// # Arguments
    ///
    /// * `fields` - Projection fields with type information
    ///
    /// An empty field list projects an empty object, **not** the JSONB column.
    /// "Nothing was requested" and "everything was requested" are opposite
    /// answers, and returning the whole column for the first is how a `node(id:)`
    /// lookup whose selection resolved to nothing served every column in the view
    /// (#827).
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Validation` if any field name contains characters
    /// that cannot be safely included in a SQL projection.
    pub fn generate_typed_projection_sql(&self, fields: &[ProjectionField]) -> Result<String> {
        if fields.is_empty() {
            return Ok("jsonb_build_object()".to_string());
        }

        let path = format!("\"{}\"", self.jsonb_column);
        let field_pairs = fields
            .iter()
            .map(|field| Self::render_field(field, &path))
            .collect::<Result<Vec<_>>>()?;

        Ok(format!("jsonb_build_object({})", field_pairs.join(",")))
    }

    /// Project the whole JSONB column **plus** a set of computed fields (#959).
    ///
    /// `"data" || jsonb_build_object('similarity', (…))`. Used where the row has
    /// to come back whole — a policy-gated field needs the full parent to decide
    /// on, and the stream strategy returns the stored blob — but a computed
    /// value still has to reach the response. Concatenation, so a computed key
    /// that collides with a stored one wins, which is the only reading that
    /// matches what the caller asked for.
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Validation` if a field name cannot be safely
    /// projected.
    pub fn generate_merged_projection_sql(&self, computed: &[ProjectionField]) -> Result<String> {
        if computed.is_empty() {
            return Ok(format!("\"{}\"", self.jsonb_column));
        }
        let path = format!("\"{}\"", self.jsonb_column);
        let pairs = computed
            .iter()
            .map(|field| Self::render_field(field, &path))
            .collect::<Result<Vec<_>>>()?;
        Ok(format!("{path} || jsonb_build_object({})", pairs.join(",")))
    }

    /// Recursively render one projection field as a `'key', <expr>` pair for
    /// `jsonb_build_object`.
    ///
    /// * `field` — field to render
    /// * `path`  — JSONB path prefix built so far (e.g. `"data"` at depth 0, `"data"->'author'` at
    ///   depth 1)
    fn render_field(field: &ProjectionField, path: &str) -> Result<String> {
        // Output key is the (possibly aliased) response key; the JSONB column is
        // derived from the *source* field name. For unaliased fields these are
        // the same, but an aliased field (`myName: fullName`) must read
        // `data->>'full_name'`, not `data->>'my_name'` (#418).
        let resp_key = Self::escape_sql_string(&field.name);
        // A computed field has no stored key: the expression is the value, at
        // whatever depth the field appears (#959).
        if let Some(expr) = &field.computed {
            return Ok(format!("'{}', ({})", resp_key, expr.as_sql()));
        }
        let jsonb_key = to_snake_case(&field.source);
        let safe_jsonb_key = Self::escape_sql_string(&jsonb_key);

        // An object's sub-fields, at any depth — never the stored object in their place.
        //
        // Built only when the stored value IS an object (#1364): a JSON `null` or a missing
        // key reads every sub-field as NULL, and an unconditional `jsonb_build_object`
        // turned that into `{"id": null, ...}` — a nullable object that could never be
        // `null`, and a non-null sub-field answered with `null`. The `CASE` with no `ELSE`
        // yields NULL, and recursion applies the guard at every depth.
        if let Some(subs) = &field.sub_fields {
            let nested_path = format!("{}->'{}'", path, safe_jsonb_key);
            let inner = subs
                .iter()
                .map(|sf| Self::render_field(sf, &nested_path))
                .collect::<Result<Vec<_>>>()?;
            return Ok(format!(
                "'{}', CASE WHEN jsonb_typeof({}) = 'object' THEN jsonb_build_object({}) END",
                resp_key,
                nested_path,
                inner.join(",")
            ));
        }

        // Text: ->> (text cast, for String/ID).
        // Native / Composite: -> (preserves native JSONB type).
        let op = if field.kind == FieldKind::Text {
            "->>"
        } else {
            "->"
        };
        Ok(format!("'{}', {}{}'{}'", resp_key, path, op, safe_jsonb_key))
    }

    /// Generate complete SELECT clause with projection for a table.
    ///
    /// # Arguments
    ///
    /// * `table_alias` - Table alias or name in the FROM clause
    /// * `fields` - Fields to project
    ///
    /// # Returns
    ///
    /// Complete SELECT clause, e.g.: `SELECT jsonb_build_object(...) as data`
    ///
    /// # Example
    ///
    /// ```rust
    /// use fraiseql_db::projection_generator::PostgresProjectionGenerator;
    ///
    /// let generator = PostgresProjectionGenerator::new();
    /// let fields = vec!["id".to_string(), "name".to_string()];
    /// let sql = generator.generate_select_clause("t", &fields).unwrap();
    /// assert!(sql.contains("SELECT"));
    /// ```
    ///
    /// # Errors
    ///
    /// Propagates any error from [`Self::generate_projection_sql`].
    pub fn generate_select_clause(&self, table_alias: &str, fields: &[String]) -> Result<String> {
        let projection = self.generate_projection_sql(fields)?;
        Ok(format!(
            "SELECT {} as \"{}\" FROM \"{}\" ",
            projection, self.jsonb_column, table_alias
        ))
    }

    /// Escape a value for use as a SQL *string literal* (inside single quotes).
    ///
    /// Doubles any embedded single-quote (`'` → `''`) to prevent SQL injection
    /// when the field name is embedded as a string literal key, e.g. in
    /// `jsonb_build_object('key', ...)` or `data->>'key'`.
    fn escape_sql_string(s: &str) -> String {
        s.replace('\'', "''")
    }

    /// Escape a SQL identifier using PostgreSQL double-quote quoting.
    ///
    /// Double-quote delimiters prevent identifier injection: any `"` within
    /// the identifier is doubled (`""`), and the whole name is wrapped in `"`.
    /// Use this when the name appears in an *identifier* position (column name,
    /// table alias) rather than as a string literal.
    #[allow(dead_code)] // Reason: available for callers embedding names as SQL identifiers
    fn escape_identifier(field: &str) -> String {
        format!("\"{}\"", field.replace('"', "\"\""))
    }
}

impl Default for PostgresProjectionGenerator {
    fn default() -> Self {
        Self::new()
    }
}

#[cfg(test)]
mod tests;