fraiseql_db/types/sql_hints.rs
1//! Shared SQL types used across the compiler, schema, and database layers.
2//!
3//! These types are defined here (rather than in `compiler::aggregation` or
4//! `schema`) so that `db/` can import them without creating a dependency on
5//! the compilation or schema layers — a prerequisite for eventually extracting
6//! `db/` into its own crate (`fraiseql-db`).
7
8use fraiseql_error::{FraiseQLError, Result};
9use serde::{Deserialize, Serialize};
10
11use crate::{types::db_types::DatabaseType, utils::to_snake_case};
12
13/// SQL sort type for ORDER BY cast generation.
14///
15/// Determines whether the SQL generator wraps the extracted JSONB text in a
16/// type cast (e.g., `(data->>'amount')::numeric`) to ensure correct sort order.
17/// Without a cast, all JSONB extractions are `text` and sort lexicographically,
18/// which is wrong for numeric and date/time fields (`"9" > "10"`).
19#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
20#[non_exhaustive]
21pub enum OrderByFieldType {
22 /// No cast — text/string sort (correct for strings, UUIDs, enum values).
23 #[default]
24 Text,
25 /// Cast to integer type (`::bigint` / `CAST(... AS BIGINT)`).
26 Integer,
27 /// Cast to floating-point/numeric type (`::numeric` / `CAST(... AS DECIMAL(38,12))`).
28 Numeric,
29 /// Cast to boolean (`::boolean` / `CAST(... AS UNSIGNED)`).
30 Boolean,
31 /// Cast to timestamp (`::timestamptz` / `CAST(... AS DATETIME)`).
32 /// Also used for ISO-8601 date-time strings which sort correctly as text,
33 /// but the cast ensures the database optimizer can use typed comparisons.
34 DateTime,
35 /// Cast to date (`::date` / `CAST(... AS DATE)`).
36 Date,
37 /// Cast to time (`::time` / `CAST(... AS TIME)`).
38 Time,
39}
40
41/// ORDER BY clause with optional type and native column information.
42///
43/// The SQL generator uses `field_type` to emit the correct type cast for
44/// JSONB-extracted values, and `native_column` to bypass JSONB extraction
45/// entirely when the view exposes a dedicated typed column.
46///
47/// # Sort correctness by source
48///
49/// | Source | Text fields | Numeric fields | Date fields |
50/// |--------|------------|----------------|-------------|
51/// | JSONB (no cast) | Correct | **Wrong** (lexicographic) | Correct (ISO-8601) |
52/// | JSONB (with cast) | Correct | Correct | Correct |
53/// | Native column | Correct | Correct | Correct + indexable |
54#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
55#[non_exhaustive]
56pub struct OrderByClause {
57 /// Field to order by (GraphQL camelCase name).
58 pub field: String,
59 /// Sort direction.
60 pub direction: OrderDirection,
61 /// Field type for SQL cast generation. `Text` (default) means no cast.
62 #[serde(default)]
63 pub field_type: OrderByFieldType,
64 /// Native column name if the view exposes this field as a typed column.
65 /// When set, ORDER BY uses this column directly instead of JSONB extraction,
66 /// enabling index support and correct typing without casts.
67 #[serde(default, skip_serializing_if = "Option::is_none")]
68 pub native_column: Option<String>,
69}
70
71/// Sort direction
72#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
73#[non_exhaustive]
74pub enum OrderDirection {
75 /// Ascending (A-Z, 0-9)
76 Asc,
77 /// Descending (Z-A, 9-0)
78 Desc,
79}
80
81impl OrderDirection {
82 /// Return the SQL keyword for this direction.
83 #[must_use]
84 pub const fn as_sql(self) -> &'static str {
85 match self {
86 Self::Asc => "ASC",
87 Self::Desc => "DESC",
88 }
89 }
90}
91
92impl OrderByClause {
93 /// Create a new `OrderByClause` with default field type (text) and no native column.
94 #[must_use]
95 pub fn new(field: String, direction: OrderDirection) -> Self {
96 Self {
97 field,
98 direction,
99 field_type: OrderByFieldType::default(),
100 native_column: None,
101 }
102 }
103
104 /// Convert the GraphQL camelCase field name to the JSONB snake_case storage key.
105 ///
106 /// # Examples
107 ///
108 /// ```
109 /// use fraiseql_db::OrderByClause;
110 /// use fraiseql_db::OrderDirection;
111 ///
112 /// let clause = OrderByClause::new("createdAt".to_string(), OrderDirection::Asc);
113 /// assert_eq!(clause.storage_key(), "created_at");
114 /// ```
115 #[must_use]
116 pub fn storage_key(&self) -> String {
117 to_snake_case(&self.field)
118 }
119
120 /// Validate that a field name matches the GraphQL identifier pattern `[_A-Za-z][_0-9A-Za-z]*`.
121 ///
122 /// This is a security boundary: field names are interpolated into SQL `data->>'field'`
123 /// expressions. Any character outside the GraphQL identifier set must be rejected before
124 /// the `OrderByClause` is constructed.
125 ///
126 /// # Errors
127 ///
128 /// Returns `FraiseQLError::Validation` if the field contains invalid characters.
129 pub fn validate_field_name(field: &str) -> Result<()> {
130 let mut chars = field.chars();
131 let first_ok = chars.next().is_some_and(|c| c.is_ascii_alphabetic() || c == '_');
132 let rest_ok = chars.all(|c| c.is_ascii_alphanumeric() || c == '_');
133 if first_ok && rest_ok {
134 Ok(())
135 } else {
136 Err(FraiseQLError::Validation {
137 message: format!(
138 "orderBy field name '{field}' contains invalid characters; \
139 only [_A-Za-z][_0-9A-Za-z]* is allowed"
140 ),
141 path: None,
142 })
143 }
144 }
145
146 /// Parse `orderBy` from a GraphQL variables JSON value.
147 ///
148 /// Accepts two formats:
149 /// - Object: `{ "name": "DESC", "created_at": "ASC" }`
150 /// - Array: `[{ "field": "name", "direction": "DESC" }]`
151 ///
152 /// Direction strings are case-insensitive.
153 ///
154 /// # Errors
155 ///
156 /// Returns `FraiseQLError::Validation` for invalid structure or direction values.
157 pub fn from_graphql_json(value: &serde_json::Value) -> Result<Vec<Self>> {
158 if let Some(obj) = value.as_object() {
159 // Object format: { "name": "DESC", "created_at": "ASC" }
160 obj.iter()
161 .map(|(field, dir_val)| {
162 let dir_str = dir_val.as_str().ok_or_else(|| FraiseQLError::Validation {
163 message: format!("orderBy direction for '{field}' must be a string"),
164 path: None,
165 })?;
166 let direction = match dir_str.to_ascii_uppercase().as_str() {
167 "ASC" => OrderDirection::Asc,
168 "DESC" => OrderDirection::Desc,
169 _ => {
170 return Err(FraiseQLError::Validation {
171 message: format!(
172 "orderBy direction '{dir_str}' must be ASC or DESC"
173 ),
174 path: None,
175 });
176 },
177 };
178 Self::validate_field_name(field)?;
179 Ok(Self::new(field.clone(), direction))
180 })
181 .collect()
182 } else if let Some(arr) = value.as_array() {
183 // Array format: [{ "field": "name", "direction": "DESC" }]
184 arr.iter()
185 .map(|item| {
186 let obj = item.as_object().ok_or_else(|| FraiseQLError::Validation {
187 message: "orderBy array items must be objects".to_string(),
188 path: None,
189 })?;
190 let field = obj
191 .get("field")
192 .and_then(|v| v.as_str())
193 .ok_or_else(|| FraiseQLError::Validation {
194 message: "orderBy item missing 'field' string".to_string(),
195 path: None,
196 })?
197 .to_string();
198 let dir_str = obj.get("direction").and_then(|v| v.as_str()).unwrap_or("ASC");
199 let direction = match dir_str.to_ascii_uppercase().as_str() {
200 "ASC" => OrderDirection::Asc,
201 "DESC" => OrderDirection::Desc,
202 _ => {
203 return Err(FraiseQLError::Validation {
204 message: format!(
205 "orderBy direction '{dir_str}' must be ASC or DESC"
206 ),
207 path: None,
208 });
209 },
210 };
211 Self::validate_field_name(&field)?;
212 Ok(Self::new(field, direction))
213 })
214 .collect()
215 } else {
216 Err(FraiseQLError::Validation {
217 message: "orderBy must be an object or array".to_string(),
218 path: None,
219 })
220 }
221 }
222}
223
224/// SQL projection hint for database-specific field projection optimization.
225///
226/// When a type has a large JSONB payload, the compiler can generate
227/// SQL that projects only the requested fields, reducing network payload
228/// and JSON deserialization overhead.
229#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
230#[non_exhaustive]
231pub struct SqlProjectionHint {
232 /// Database type — typed to prevent silent typos (e.g. `"postgresq"`) that
233 /// would cause adapters to silently ignore the hint.
234 pub database: DatabaseType,
235
236 /// The projection SQL template.
237 /// Example for PostgreSQL:
238 /// `jsonb_build_object('id', data->>'id', 'email', data->>'email')`
239 pub projection_template: String,
240
241 /// Estimated reduction in payload size (percentage 0-100).
242 pub estimated_reduction_percent: u32,
243}
244
245impl SqlProjectionHint {
246 /// Creates a new `SqlProjectionHint`.
247 #[must_use]
248 pub const fn new(
249 database: DatabaseType,
250 projection_template: String,
251 estimated_reduction_percent: u32,
252 ) -> Self {
253 Self {
254 database,
255 projection_template,
256 estimated_reduction_percent,
257 }
258 }
259}
260
261#[cfg(test)]
262mod tests;