fraiseql_core/runtime/
aggregation.rs

1//! Runtime Aggregation SQL Generation Module
2//!
3//! This module generates database-specific SQL from aggregation execution plans.
4//!
5//! # Database-Specific SQL
6//!
7//! ## PostgreSQL
8//! ```sql
9//! SELECT
10//!   data->>'category' AS category,
11//!   DATE_TRUNC('day', occurred_at) AS occurred_at_day,
12//!   COUNT(*) AS count,
13//!   SUM(revenue) AS revenue_sum
14//! FROM tf_sales
15//! WHERE customer_id = $1
16//! GROUP BY data->>'category', DATE_TRUNC('day', occurred_at)
17//! HAVING SUM(revenue) > $2
18//! ORDER BY revenue_sum DESC
19//! LIMIT 10
20//! ```
21//!
22//! ## MySQL
23//! ```sql
24//! SELECT
25//!   JSON_UNQUOTE(JSON_EXTRACT(data, '$.category')) AS category,
26//!   DATE_FORMAT(occurred_at, '%Y-%m-%d') AS occurred_at_day,
27//!   COUNT(*) AS count,
28//!   SUM(revenue) AS revenue_sum
29//! FROM tf_sales
30//! WHERE customer_id = ?
31//! GROUP BY JSON_UNQUOTE(JSON_EXTRACT(data, '$.category')), DATE_FORMAT(occurred_at, '%Y-%m-%d')
32//! HAVING SUM(revenue) > ?
33//! ORDER BY revenue_sum DESC
34//! LIMIT 10
35//! ```
36//!
37//! ## SQLite
38//! ```sql
39//! SELECT
40//!   json_extract(data, '$.category') AS category,
41//!   strftime('%Y-%m-%d', occurred_at) AS occurred_at_day,
42//!   COUNT(*) AS count,
43//!   SUM(revenue) AS revenue_sum
44//! FROM tf_sales
45//! WHERE customer_id = ?
46//! GROUP BY json_extract(data, '$.category'), strftime('%Y-%m-%d', occurred_at)
47//! HAVING SUM(revenue) > ?
48//! ORDER BY revenue_sum DESC
49//! LIMIT 10
50//! ```
51
52use crate::{
53    compiler::{
54        aggregate_types::{AggregateFunction, TemporalBucket},
55        aggregation::{
56            AggregateExpression, AggregationPlan, GroupByExpression, OrderByClause, OrderDirection,
57            ValidatedHavingCondition,
58        },
59        fact_table::FactTableMetadata,
60    },
61    db::{
62        types::DatabaseType,
63        where_clause::{WhereClause, WhereOperator},
64    },
65    error::{FraiseQLError, Result},
66    utils::casing::to_snake_case,
67};
68
69/// SQL query components
70#[derive(Debug, Clone)]
71pub struct AggregationSql {
72    /// SELECT clause
73    pub select:       String,
74    /// FROM clause
75    pub from:         String,
76    /// WHERE clause (if present)
77    pub where_clause: Option<String>,
78    /// GROUP BY clause (if present)
79    pub group_by:     Option<String>,
80    /// HAVING clause (if present)
81    pub having:       Option<String>,
82    /// ORDER BY clause (if present)
83    pub order_by:     Option<String>,
84    /// LIMIT clause (if present)
85    pub limit:        Option<u32>,
86    /// OFFSET clause (if present)
87    pub offset:       Option<u32>,
88    /// Complete SQL query
89    pub complete_sql: String,
90}
91
92/// Aggregation SQL generator
93pub struct AggregationSqlGenerator {
94    database_type: DatabaseType,
95}
96
97impl AggregationSqlGenerator {
98    /// Create new SQL generator for specific database
99    #[must_use]
100    pub const fn new(database_type: DatabaseType) -> Self {
101        Self { database_type }
102    }
103
104    /// Generate SQL from aggregation plan
105    ///
106    /// # Errors
107    ///
108    /// Returns error if SQL generation fails
109    pub fn generate(&self, plan: &AggregationPlan) -> Result<AggregationSql> {
110        // Build SELECT clause
111        let select =
112            self.build_select_clause(&plan.group_by_expressions, &plan.aggregate_expressions)?;
113
114        // Build FROM clause
115        let from = format!("FROM {}", plan.request.table_name);
116
117        // Build WHERE clause (if present)
118        let where_clause = if let Some(ref where_clause) = plan.request.where_clause {
119            Some(self.build_where_clause(where_clause, &plan.metadata)?)
120        } else {
121            None
122        };
123
124        // Build GROUP BY clause (if present)
125        let group_by = if !plan.group_by_expressions.is_empty() {
126            Some(self.build_group_by_clause(&plan.group_by_expressions)?)
127        } else {
128            None
129        };
130
131        // Build HAVING clause (if present)
132        let having = if !plan.having_conditions.is_empty() {
133            Some(self.build_having_clause(&plan.having_conditions)?)
134        } else {
135            None
136        };
137
138        // Build ORDER BY clause (if present)
139        let order_by = if !plan.request.order_by.is_empty() {
140            Some(self.build_order_by_clause(&plan.request.order_by)?)
141        } else {
142            None
143        };
144
145        // Build complete SQL
146        let complete_sql = self.assemble_sql(
147            &select,
148            &from,
149            where_clause.as_deref(),
150            group_by.as_deref(),
151            having.as_deref(),
152            order_by.as_deref(),
153            plan.request.limit,
154            plan.request.offset,
155        );
156
157        Ok(AggregationSql {
158            select,
159            from,
160            where_clause,
161            group_by,
162            having,
163            order_by,
164            limit: plan.request.limit,
165            offset: plan.request.offset,
166            complete_sql,
167        })
168    }
169
170    /// Build SELECT clause
171    fn build_select_clause(
172        &self,
173        group_by_expressions: &[GroupByExpression],
174        aggregate_expressions: &[AggregateExpression],
175    ) -> Result<String> {
176        let mut columns = Vec::new();
177
178        // Add GROUP BY columns to SELECT
179        for expr in group_by_expressions {
180            let column = self.group_by_expression_to_sql(expr)?;
181            let alias = match expr {
182                GroupByExpression::JsonbPath { alias, .. }
183                | GroupByExpression::TemporalBucket { alias, .. }
184                | GroupByExpression::CalendarPath { alias, .. } => alias,
185            };
186            columns.push(format!("{} AS {}", column, alias));
187        }
188
189        // Add aggregate columns to SELECT
190        for expr in aggregate_expressions {
191            let column = self.aggregate_expression_to_sql(expr)?;
192            let alias = match expr {
193                AggregateExpression::Count { alias }
194                | AggregateExpression::CountDistinct { alias, .. }
195                | AggregateExpression::MeasureAggregate { alias, .. }
196                | AggregateExpression::AdvancedAggregate { alias, .. }
197                | AggregateExpression::BoolAggregate { alias, .. } => alias,
198            };
199            columns.push(format!("{} AS {}", column, alias));
200        }
201
202        Ok(format!("SELECT\n  {}", columns.join(",\n  ")))
203    }
204
205    /// Convert GROUP BY expression to SQL
206    fn group_by_expression_to_sql(&self, expr: &GroupByExpression) -> Result<String> {
207        match expr {
208            GroupByExpression::JsonbPath {
209                jsonb_column, path, ..
210            } => Ok(self.jsonb_extract_sql(jsonb_column, path)),
211            GroupByExpression::TemporalBucket { column, bucket, .. } => {
212                Ok(self.temporal_bucket_sql(column, *bucket))
213            },
214            GroupByExpression::CalendarPath {
215                calendar_column,
216                json_key,
217                ..
218            } => {
219                // Calendar dimension: reuse JSONB extraction for all 4 databases
220                Ok(self.jsonb_extract_sql(calendar_column, json_key))
221            },
222        }
223    }
224
225    /// Generate JSONB extraction SQL
226    fn jsonb_extract_sql(&self, jsonb_column: &str, path: &str) -> String {
227        match self.database_type {
228            DatabaseType::PostgreSQL => {
229                format!("{}->>'{}' ", jsonb_column, path)
230            },
231            DatabaseType::MySQL => {
232                format!("JSON_UNQUOTE(JSON_EXTRACT({}, '$.{}'))", jsonb_column, path)
233            },
234            DatabaseType::SQLite => {
235                format!("json_extract({}, '$.{}')", jsonb_column, path)
236            },
237            DatabaseType::SQLServer => {
238                format!("JSON_VALUE({}, '$.{}')", jsonb_column, path)
239            },
240        }
241    }
242
243    /// Generate temporal bucket SQL
244    fn temporal_bucket_sql(&self, column: &str, bucket: TemporalBucket) -> String {
245        match self.database_type {
246            DatabaseType::PostgreSQL => {
247                format!("DATE_TRUNC('{}', {})", bucket.postgres_arg(), column)
248            },
249            DatabaseType::MySQL => {
250                let format = match bucket {
251                    TemporalBucket::Second => "%Y-%m-%d %H:%i:%s",
252                    TemporalBucket::Minute => "%Y-%m-%d %H:%i:00",
253                    TemporalBucket::Hour => "%Y-%m-%d %H:00:00",
254                    TemporalBucket::Day => "%Y-%m-%d",
255                    TemporalBucket::Week => "%Y-%u",
256                    TemporalBucket::Month => "%Y-%m",
257                    TemporalBucket::Quarter => "%Y-Q%q",
258                    TemporalBucket::Year => "%Y",
259                };
260                format!("DATE_FORMAT({}, '{}')", column, format)
261            },
262            DatabaseType::SQLite => {
263                let format = match bucket {
264                    TemporalBucket::Second => "%Y-%m-%d %H:%M:%S",
265                    TemporalBucket::Minute => "%Y-%m-%d %H:%M:00",
266                    TemporalBucket::Hour => "%Y-%m-%d %H:00:00",
267                    TemporalBucket::Day => "%Y-%m-%d",
268                    TemporalBucket::Week => "%Y-W%W",
269                    TemporalBucket::Month => "%Y-%m",
270                    TemporalBucket::Quarter => "%Y-Q",
271                    TemporalBucket::Year => "%Y",
272                };
273                format!("strftime('{}', {})", format, column)
274            },
275            DatabaseType::SQLServer => {
276                let datepart = match bucket {
277                    TemporalBucket::Second => "second",
278                    TemporalBucket::Minute => "minute",
279                    TemporalBucket::Hour => "hour",
280                    TemporalBucket::Day => "day",
281                    TemporalBucket::Week => "week",
282                    TemporalBucket::Month => "month",
283                    TemporalBucket::Quarter => "quarter",
284                    TemporalBucket::Year => "year",
285                };
286                // SQL Server doesn't have DATE_TRUNC, use DATEADD/DATEDIFF pattern
287                match bucket {
288                    TemporalBucket::Day => format!("CAST({} AS DATE)", column),
289                    TemporalBucket::Month => {
290                        format!("DATEADD(month, DATEDIFF(month, 0, {}), 0)", column)
291                    },
292                    TemporalBucket::Year => {
293                        format!("DATEADD(year, DATEDIFF(year, 0, {}), 0)", column)
294                    },
295                    _ => format!("DATEPART({}, {})", datepart, column),
296                }
297            },
298        }
299    }
300
301    /// Convert aggregate expression to SQL
302    fn aggregate_expression_to_sql(&self, expr: &AggregateExpression) -> Result<String> {
303        match expr {
304            AggregateExpression::Count { .. } => Ok("COUNT(*)".to_string()),
305            AggregateExpression::CountDistinct { column, .. } => {
306                Ok(format!("COUNT(DISTINCT {})", column))
307            },
308            AggregateExpression::MeasureAggregate {
309                column, function, ..
310            } => {
311                // Handle statistical functions with database-specific SQL
312                use AggregateFunction::*;
313                match function {
314                    Stddev => Ok(self.generate_stddev_sql(column)),
315                    Variance => Ok(self.generate_variance_sql(column)),
316                    _ => Ok(format!("{}({})", function.sql_name(), column)),
317                }
318            },
319            AggregateExpression::AdvancedAggregate {
320                column,
321                function,
322                delimiter,
323                order_by,
324                ..
325            } => self.advanced_aggregate_to_sql(
326                column,
327                *function,
328                delimiter.as_deref(),
329                order_by.as_ref(),
330            ),
331            AggregateExpression::BoolAggregate {
332                column, function, ..
333            } => Ok(self.generate_bool_agg_sql(column, *function)),
334        }
335    }
336
337    /// Generate SQL for advanced aggregates
338    fn advanced_aggregate_to_sql(
339        &self,
340        column: &str,
341        function: AggregateFunction,
342        delimiter: Option<&str>,
343        order_by: Option<&Vec<OrderByClause>>,
344    ) -> Result<String> {
345        use AggregateFunction::*;
346
347        match function {
348            ArrayAgg => Ok(self.generate_array_agg_sql(column, order_by)),
349            JsonAgg => Ok(self.generate_json_agg_sql(column, order_by)),
350            JsonbAgg => Ok(self.generate_jsonb_agg_sql(column, order_by)),
351            StringAgg => {
352                Ok(self.generate_string_agg_sql(column, delimiter.unwrap_or(","), order_by))
353            },
354            _ => Ok(format!("{}({})", function.sql_name(), column)),
355        }
356    }
357
358    /// Generate ARRAY_AGG SQL
359    fn generate_array_agg_sql(
360        &self,
361        column: &str,
362        order_by: Option<&Vec<OrderByClause>>,
363    ) -> String {
364        match self.database_type {
365            DatabaseType::PostgreSQL => {
366                if let Some(order) = order_by {
367                    format!("ARRAY_AGG({} ORDER BY {})", column, self.order_by_to_sql(order))
368                } else {
369                    format!("ARRAY_AGG({})", column)
370                }
371            },
372            DatabaseType::MySQL => {
373                // MySQL doesn't have ARRAY_AGG, use JSON_ARRAYAGG
374                format!("JSON_ARRAYAGG({})", column)
375            },
376            DatabaseType::SQLite => {
377                // SQLite: emulate with GROUP_CONCAT, wrap in JSON array syntax
378                format!("'[' || GROUP_CONCAT('\"' || {} || '\"', ',') || ']'", column)
379            },
380            DatabaseType::SQLServer => {
381                // SQL Server: use STRING_AGG and wrap in JSON array
382                format!(
383                    "'[' + STRING_AGG('\"' + CAST({} AS NVARCHAR(MAX)) + '\"', ',') + ']'",
384                    column
385                )
386            },
387        }
388    }
389
390    /// Generate JSON_AGG SQL
391    fn generate_json_agg_sql(&self, column: &str, order_by: Option<&Vec<OrderByClause>>) -> String {
392        match self.database_type {
393            DatabaseType::PostgreSQL => {
394                if let Some(order) = order_by {
395                    format!("JSON_AGG({} ORDER BY {})", column, self.order_by_to_sql(order))
396                } else {
397                    format!("JSON_AGG({})", column)
398                }
399            },
400            DatabaseType::MySQL => {
401                // MySQL: JSON_ARRAYAGG for arrays
402                format!("JSON_ARRAYAGG({})", column)
403            },
404            DatabaseType::SQLite => {
405                // SQLite: limited JSON support
406                format!("JSON_ARRAY({})", column)
407            },
408            DatabaseType::SQLServer => {
409                // SQL Server: FOR JSON PATH
410                format!("(SELECT {} FOR JSON PATH)", column)
411            },
412        }
413    }
414
415    /// Generate JSONB_AGG SQL (PostgreSQL-specific)
416    fn generate_jsonb_agg_sql(
417        &self,
418        column: &str,
419        order_by: Option<&Vec<OrderByClause>>,
420    ) -> String {
421        match self.database_type {
422            DatabaseType::PostgreSQL => {
423                if let Some(order) = order_by {
424                    format!("JSONB_AGG({} ORDER BY {})", column, self.order_by_to_sql(order))
425                } else {
426                    format!("JSONB_AGG({})", column)
427                }
428            },
429            // Fall back to JSON_AGG for other databases
430            _ => self.generate_json_agg_sql(column, order_by),
431        }
432    }
433
434    /// Generate STRING_AGG SQL
435    fn generate_string_agg_sql(
436        &self,
437        column: &str,
438        delimiter: &str,
439        order_by: Option<&Vec<OrderByClause>>,
440    ) -> String {
441        match self.database_type {
442            DatabaseType::PostgreSQL => {
443                if let Some(order) = order_by {
444                    format!(
445                        "STRING_AGG({}, '{}' ORDER BY {})",
446                        column,
447                        delimiter,
448                        self.order_by_to_sql(order)
449                    )
450                } else {
451                    format!("STRING_AGG({}, '{}')", column, delimiter)
452                }
453            },
454            DatabaseType::MySQL => {
455                let mut sql = format!("GROUP_CONCAT({}", column);
456                if let Some(order) = order_by {
457                    sql.push_str(&format!(" ORDER BY {}", self.order_by_to_sql(order)));
458                }
459                sql.push_str(&format!(" SEPARATOR '{}')", delimiter));
460                sql
461            },
462            DatabaseType::SQLite => {
463                // SQLite GROUP_CONCAT doesn't support ORDER BY in older versions
464                format!("GROUP_CONCAT({}, '{}')", column, delimiter)
465            },
466            DatabaseType::SQLServer => {
467                let mut sql =
468                    format!("STRING_AGG(CAST({} AS NVARCHAR(MAX)), '{}')", column, delimiter);
469                if let Some(order) = order_by {
470                    sql.push_str(&format!(
471                        " WITHIN GROUP (ORDER BY {})",
472                        self.order_by_to_sql(order)
473                    ));
474                }
475                sql
476            },
477        }
478    }
479
480    /// Convert ORDER BY clauses to SQL
481    fn order_by_to_sql(&self, order_by: &[OrderByClause]) -> String {
482        order_by
483            .iter()
484            .map(|clause| {
485                let direction = match clause.direction {
486                    OrderDirection::Asc => "ASC",
487                    OrderDirection::Desc => "DESC",
488                };
489                format!("{} {}", clause.field, direction)
490            })
491            .collect::<Vec<_>>()
492            .join(", ")
493    }
494
495    /// Generate STDDEV SQL (database-specific)
496    ///
497    /// Database support:
498    /// - PostgreSQL: STDDEV_SAMP() (default), STDDEV_POP() also available
499    /// - MySQL: STDDEV_SAMP() or STD()
500    /// - SQLite: Not natively supported (returns NULL or use custom function)
501    /// - SQL Server: STDEV()
502    fn generate_stddev_sql(&self, column: &str) -> String {
503        match self.database_type {
504            DatabaseType::PostgreSQL => format!("STDDEV_SAMP({})", column),
505            DatabaseType::MySQL => format!("STDDEV_SAMP({})", column),
506            DatabaseType::SQLite => {
507                // SQLite doesn't have built-in STDDEV
508                // Return NULL to indicate unavailable
509                format!("NULL /* STDDEV not supported in SQLite */")
510            },
511            DatabaseType::SQLServer => format!("STDEV({})", column),
512        }
513    }
514
515    /// Generate VARIANCE SQL (database-specific)
516    ///
517    /// Database support:
518    /// - PostgreSQL: VAR_SAMP() (default), VAR_POP() also available
519    /// - MySQL: VAR_SAMP() or VARIANCE()
520    /// - SQLite: Not natively supported (returns NULL or use custom function)
521    /// - SQL Server: VAR()
522    fn generate_variance_sql(&self, column: &str) -> String {
523        match self.database_type {
524            DatabaseType::PostgreSQL => format!("VAR_SAMP({})", column),
525            DatabaseType::MySQL => format!("VAR_SAMP({})", column),
526            DatabaseType::SQLite => {
527                // SQLite doesn't have built-in VARIANCE
528                // Return NULL to indicate unavailable
529                format!("NULL /* VARIANCE not supported in SQLite */")
530            },
531            DatabaseType::SQLServer => format!("VAR({})", column),
532        }
533    }
534
535    /// Generate BOOL_AND/BOOL_OR SQL
536    fn generate_bool_agg_sql(
537        &self,
538        column: &str,
539        function: crate::compiler::aggregate_types::BoolAggregateFunction,
540    ) -> String {
541        use crate::compiler::aggregate_types::BoolAggregateFunction;
542
543        match self.database_type {
544            DatabaseType::PostgreSQL => {
545                // PostgreSQL has native BOOL_AND/BOOL_OR
546                format!("{}({})", function.sql_name(), column)
547            },
548            DatabaseType::MySQL | DatabaseType::SQLite => {
549                // MySQL/SQLite: emulate with MIN/MAX on boolean as integer (0/1)
550                match function {
551                    BoolAggregateFunction::And => format!("MIN({}) = 1", column),
552                    BoolAggregateFunction::Or => format!("MAX({}) = 1", column),
553                }
554            },
555            DatabaseType::SQLServer => {
556                // SQL Server: emulate with MIN/MAX on CAST to BIT
557                match function {
558                    BoolAggregateFunction::And => format!("MIN(CAST({} AS BIT)) = 1", column),
559                    BoolAggregateFunction::Or => format!("MAX(CAST({} AS BIT)) = 1", column),
560                }
561            },
562        }
563    }
564
565    /// Build WHERE clause SQL
566    ///
567    /// Handles two types of filterable fields:
568    /// 1. Denormalized filters (direct columns): WHERE customer_id = $1
569    /// 2. Dimensions (JSONB paths): WHERE data->>'category' = $1
570    pub fn build_where_clause(
571        &self,
572        where_clause: &WhereClause,
573        metadata: &FactTableMetadata,
574    ) -> Result<String> {
575        if where_clause.is_empty() {
576            return Ok(String::new());
577        }
578
579        let condition_sql = self.where_clause_to_sql(where_clause, metadata)?;
580        Ok(format!("WHERE {}", condition_sql))
581    }
582
583    /// Convert WhereClause AST to SQL
584    fn where_clause_to_sql(
585        &self,
586        clause: &WhereClause,
587        metadata: &FactTableMetadata,
588    ) -> Result<String> {
589        match clause {
590            WhereClause::Field {
591                path,
592                operator,
593                value,
594            } => {
595                let field_name = &path[0];
596
597                // Check if field is a denormalized filter (direct column)
598                let is_denormalized =
599                    metadata.denormalized_filters.iter().any(|f| f.name == *field_name);
600
601                if is_denormalized {
602                    // Direct column: WHERE customer_id = $1
603                    self.generate_direct_column_where(field_name, operator, value)
604                } else {
605                    // JSONB dimension: WHERE data->>'category' = $1
606                    let jsonb_column = &metadata.dimensions.name; // "data"
607                    self.generate_jsonb_where(jsonb_column, path, operator, value)
608                }
609            },
610            WhereClause::And(clauses) => {
611                let conditions: Vec<String> = clauses
612                    .iter()
613                    .map(|c| self.where_clause_to_sql(c, metadata))
614                    .collect::<Result<Vec<_>>>()?;
615                Ok(format!("({})", conditions.join(" AND ")))
616            },
617            WhereClause::Or(clauses) => {
618                let conditions: Vec<String> = clauses
619                    .iter()
620                    .map(|c| self.where_clause_to_sql(c, metadata))
621                    .collect::<Result<Vec<_>>>()?;
622                Ok(format!("({})", conditions.join(" OR ")))
623            },
624            WhereClause::Not(clause) => {
625                let inner = self.where_clause_to_sql(clause, metadata)?;
626                Ok(format!("NOT ({})", inner))
627            },
628        }
629    }
630
631    /// Generate WHERE for direct column (denormalized filter)
632    fn generate_direct_column_where(
633        &self,
634        field: &str,
635        operator: &WhereOperator,
636        value: &serde_json::Value,
637    ) -> Result<String> {
638        let op_sql = self.operator_to_sql(operator);
639
640        // Handle NULL checks (no value needed)
641        if matches!(operator, WhereOperator::IsNull) {
642            return Ok(format!("{} IS NULL", field));
643        }
644
645        // Handle IN/NOT IN (array values)
646        if matches!(operator, WhereOperator::In | WhereOperator::Nin) {
647            let values = self.format_array_values(value)?;
648            return Ok(format!("{} {} ({})", field, op_sql, values));
649        }
650
651        // Regular comparison
652        let value_sql = self.format_sql_value(value);
653        Ok(format!("{} {} {}", field, op_sql, value_sql))
654    }
655
656    /// Generate WHERE for JSONB dimension field
657    fn generate_jsonb_where(
658        &self,
659        jsonb_column: &str,
660        path: &[String],
661        operator: &WhereOperator,
662        value: &serde_json::Value,
663    ) -> Result<String> {
664        let field_path = &path[0]; // Simple path for now (no nested paths)
665        // Convert GraphQL field name (camelCase) to database column name (snake_case)
666        let db_field_path = to_snake_case(field_path);
667        let jsonb_extract = self.jsonb_extract_sql(jsonb_column, &db_field_path);
668        let op_sql = self.operator_to_sql(operator);
669
670        // Handle NULL checks
671        if matches!(operator, WhereOperator::IsNull) {
672            return Ok(format!("{} IS NULL", jsonb_extract));
673        }
674
675        // Handle case-insensitive operators
676        if operator.is_case_insensitive() {
677            return self.generate_case_insensitive_where(&jsonb_extract, operator, value);
678        }
679
680        // Handle IN/NOT IN for JSONB
681        if matches!(operator, WhereOperator::In | WhereOperator::Nin) {
682            let values = self.format_array_values(value)?;
683            return Ok(format!("{} {} ({})", jsonb_extract, op_sql, values));
684        }
685
686        // Handle LIKE pattern operators (Contains, Startswith, Endswith)
687        if matches!(
688            operator,
689            WhereOperator::Contains | WhereOperator::Startswith | WhereOperator::Endswith
690        ) {
691            let value_str = value
692                .as_str()
693                .ok_or_else(|| FraiseQLError::validation("LIKE operators require string values"))?;
694            let pattern = self.format_like_pattern(operator, value_str);
695            return Ok(format!("{} {} {}", jsonb_extract, op_sql, pattern));
696        }
697
698        // Regular comparison
699        let value_sql = self.format_sql_value(value);
700        Ok(format!("{} {} {}", jsonb_extract, op_sql, value_sql))
701    }
702
703    /// Convert WhereOperator to SQL operator
704    fn operator_to_sql(&self, operator: &WhereOperator) -> &'static str {
705        match operator {
706            WhereOperator::Eq => "=",
707            WhereOperator::Neq => "!=",
708            WhereOperator::Gt => ">",
709            WhereOperator::Gte => ">=",
710            WhereOperator::Lt => "<",
711            WhereOperator::Lte => "<=",
712            WhereOperator::In => "IN",
713            WhereOperator::Nin => "NOT IN",
714            WhereOperator::Like | WhereOperator::Contains => "LIKE",
715            WhereOperator::Ilike | WhereOperator::Icontains => {
716                match self.database_type {
717                    DatabaseType::PostgreSQL => "ILIKE",
718                    _ => "LIKE", // Other databases use LIKE with UPPER/LOWER
719                }
720            },
721            WhereOperator::Startswith => "LIKE",
722            WhereOperator::Istartswith => match self.database_type {
723                DatabaseType::PostgreSQL => "ILIKE",
724                _ => "LIKE",
725            },
726            WhereOperator::Endswith => "LIKE",
727            WhereOperator::Iendswith => match self.database_type {
728                DatabaseType::PostgreSQL => "ILIKE",
729                _ => "LIKE",
730            },
731            _ => "=", // Safe default for other operators
732        }
733    }
734
735    /// Generate case-insensitive WHERE clause
736    fn generate_case_insensitive_where(
737        &self,
738        column: &str,
739        operator: &WhereOperator,
740        value: &serde_json::Value,
741    ) -> Result<String> {
742        let value_str = value.as_str().ok_or_else(|| {
743            FraiseQLError::validation("Case-insensitive operators require string values")
744        })?;
745
746        match self.database_type {
747            DatabaseType::PostgreSQL => {
748                // PostgreSQL has ILIKE
749                let op = self.operator_to_sql(operator);
750                let pattern = self.format_like_pattern(operator, value_str);
751                Ok(format!("{} {} {}", column, op, pattern))
752            },
753            _ => {
754                // Other databases: use UPPER() for case-insensitive comparison
755                let op = "LIKE";
756                let pattern = self.format_like_pattern(operator, &value_str.to_uppercase());
757                Ok(format!("UPPER({}) {} {}", column, op, pattern))
758            },
759        }
760    }
761
762    /// Format LIKE pattern based on operator
763    fn format_like_pattern(&self, operator: &WhereOperator, value: &str) -> String {
764        match operator {
765            WhereOperator::Contains | WhereOperator::Icontains => {
766                format!("'%{}%'", value.replace('\'', "''"))
767            },
768            WhereOperator::Startswith | WhereOperator::Istartswith => {
769                format!("'{}%'", value.replace('\'', "''"))
770            },
771            WhereOperator::Endswith | WhereOperator::Iendswith => {
772                format!("'%{}'", value.replace('\'', "''"))
773            },
774            _ => format!("'{}'", value.replace('\'', "''")),
775        }
776    }
777
778    /// Format array values for IN/NOT IN clauses
779    fn format_array_values(&self, value: &serde_json::Value) -> Result<String> {
780        let array = value
781            .as_array()
782            .ok_or_else(|| FraiseQLError::validation("IN/NOT IN operators require array values"))?;
783
784        let formatted: Vec<String> = array.iter().map(|v| self.format_sql_value(v)).collect();
785
786        Ok(formatted.join(", "))
787    }
788
789    /// Format a single SQL value
790    fn format_sql_value(&self, value: &serde_json::Value) -> String {
791        match value {
792            serde_json::Value::Null => "NULL".to_string(),
793            serde_json::Value::Bool(b) => b.to_string(),
794            serde_json::Value::Number(n) => n.to_string(),
795            serde_json::Value::String(s) => format!("'{}'", s.replace('\'', "''")),
796            _ => "NULL".to_string(), // Fallback for arrays/objects
797        }
798    }
799
800    /// Build GROUP BY clause
801    fn build_group_by_clause(&self, group_by_expressions: &[GroupByExpression]) -> Result<String> {
802        let mut columns = Vec::new();
803
804        for expr in group_by_expressions {
805            let column = self.group_by_expression_to_sql(expr)?;
806            columns.push(column);
807        }
808
809        Ok(format!("GROUP BY {}", columns.join(", ")))
810    }
811
812    /// Build HAVING clause
813    fn build_having_clause(
814        &self,
815        having_conditions: &[ValidatedHavingCondition],
816    ) -> Result<String> {
817        let mut conditions = Vec::new();
818
819        for condition in having_conditions {
820            let aggregate_sql = self.aggregate_expression_to_sql(&condition.aggregate)?;
821            let operator_sql = condition.operator.sql_operator();
822
823            // Format value based on type
824            let value_sql = match &condition.value {
825                serde_json::Value::Number(n) => n.to_string(),
826                serde_json::Value::String(s) => format!("'{}'", s),
827                serde_json::Value::Bool(b) => b.to_string(),
828                _ => {
829                    return Err(FraiseQLError::Validation {
830                        message: "Invalid HAVING value type".to_string(),
831                        path:    None,
832                    });
833                },
834            };
835
836            conditions.push(format!("{} {} {}", aggregate_sql, operator_sql, value_sql));
837        }
838
839        Ok(format!("HAVING {}", conditions.join(" AND ")))
840    }
841
842    /// Build ORDER BY clause
843    fn build_order_by_clause(&self, order_by: &[OrderByClause]) -> Result<String> {
844        let clauses: Vec<String> = order_by
845            .iter()
846            .map(|clause| {
847                let direction = match clause.direction {
848                    OrderDirection::Asc => "ASC",
849                    OrderDirection::Desc => "DESC",
850                };
851                format!("{} {}", clause.field, direction)
852            })
853            .collect();
854
855        Ok(format!("ORDER BY {}", clauses.join(", ")))
856    }
857
858    /// Assemble complete SQL query
859    fn assemble_sql(
860        &self,
861        select: &str,
862        from: &str,
863        where_clause: Option<&str>,
864        group_by: Option<&str>,
865        having: Option<&str>,
866        order_by: Option<&str>,
867        limit: Option<u32>,
868        offset: Option<u32>,
869    ) -> String {
870        let mut sql = String::new();
871
872        sql.push_str(select);
873        sql.push('\n');
874        sql.push_str(from);
875
876        if let Some(where_sql) = where_clause {
877            sql.push('\n');
878            sql.push_str(where_sql);
879        }
880
881        if let Some(group_by_sql) = group_by {
882            sql.push('\n');
883            sql.push_str(group_by_sql);
884        }
885
886        if let Some(having_sql) = having {
887            sql.push('\n');
888            sql.push_str(having_sql);
889        }
890
891        if let Some(order_by_sql) = order_by {
892            sql.push('\n');
893            sql.push_str(order_by_sql);
894        }
895
896        if let Some(limit_val) = limit {
897            sql.push('\n');
898            sql.push_str(&format!("LIMIT {}", limit_val));
899        }
900
901        if let Some(offset_val) = offset {
902            sql.push('\n');
903            sql.push_str(&format!("OFFSET {}", offset_val));
904        }
905
906        sql
907    }
908}
909
910#[cfg(test)]
911mod tests {
912    use super::*;
913    use crate::compiler::{
914        aggregate_types::HavingOperator,
915        aggregation::{AggregateSelection, AggregationRequest, GroupBySelection},
916        fact_table::{DimensionColumn, FactTableMetadata, FilterColumn, MeasureColumn, SqlType},
917    };
918
919    fn create_test_plan() -> AggregationPlan {
920        let metadata = FactTableMetadata {
921            table_name:           "tf_sales".to_string(),
922            measures:             vec![MeasureColumn {
923                name:     "revenue".to_string(),
924                sql_type: SqlType::Decimal,
925                nullable: false,
926            }],
927            dimensions:           DimensionColumn {
928                name:  "dimensions".to_string(),
929                paths: vec![],
930            },
931            denormalized_filters: vec![FilterColumn {
932                name:     "occurred_at".to_string(),
933                sql_type: SqlType::Timestamp,
934                indexed:  true,
935            }],
936            calendar_dimensions:  vec![],
937        };
938
939        let request = AggregationRequest {
940            table_name:   "tf_sales".to_string(),
941            where_clause: None,
942            group_by:     vec![
943                GroupBySelection::Dimension {
944                    path:  "category".to_string(),
945                    alias: "category".to_string(),
946                },
947                GroupBySelection::TemporalBucket {
948                    column: "occurred_at".to_string(),
949                    bucket: TemporalBucket::Day,
950                    alias:  "day".to_string(),
951                },
952            ],
953            aggregates:   vec![
954                AggregateSelection::Count {
955                    alias: "count".to_string(),
956                },
957                AggregateSelection::MeasureAggregate {
958                    measure:  "revenue".to_string(),
959                    function: AggregateFunction::Sum,
960                    alias:    "revenue_sum".to_string(),
961                },
962            ],
963            having:       vec![],
964            order_by:     vec![],
965            limit:        Some(10),
966            offset:       None,
967        };
968
969        crate::compiler::aggregation::AggregationPlanner::plan(request, metadata).unwrap()
970    }
971
972    #[test]
973    fn test_postgres_sql_generation() {
974        let plan = create_test_plan();
975        let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
976        let sql = generator.generate(&plan).unwrap();
977
978        assert!(sql.complete_sql.contains("dimensions->>'category'"));
979        assert!(sql.complete_sql.contains("DATE_TRUNC('day', occurred_at)"));
980        assert!(sql.complete_sql.contains("COUNT(*)"));
981        assert!(sql.complete_sql.contains("SUM(revenue)"));
982        assert!(sql.complete_sql.contains("GROUP BY"));
983        assert!(sql.complete_sql.contains("LIMIT 10"));
984    }
985
986    #[test]
987    fn test_mysql_sql_generation() {
988        let plan = create_test_plan();
989        let generator = AggregationSqlGenerator::new(DatabaseType::MySQL);
990        let sql = generator.generate(&plan).unwrap();
991
992        assert!(
993            sql.complete_sql
994                .contains("JSON_UNQUOTE(JSON_EXTRACT(dimensions, '$.category'))")
995        );
996        assert!(sql.complete_sql.contains("DATE_FORMAT(occurred_at"));
997        assert!(sql.complete_sql.contains("COUNT(*)"));
998        assert!(sql.complete_sql.contains("SUM(revenue)"));
999    }
1000
1001    #[test]
1002    fn test_sqlite_sql_generation() {
1003        let plan = create_test_plan();
1004        let generator = AggregationSqlGenerator::new(DatabaseType::SQLite);
1005        let sql = generator.generate(&plan).unwrap();
1006
1007        assert!(sql.complete_sql.contains("json_extract(dimensions, '$.category')"));
1008        assert!(sql.complete_sql.contains("strftime"));
1009        assert!(sql.complete_sql.contains("COUNT(*)"));
1010        assert!(sql.complete_sql.contains("SUM(revenue)"));
1011    }
1012
1013    #[test]
1014    fn test_sqlserver_sql_generation() {
1015        let plan = create_test_plan();
1016        let generator = AggregationSqlGenerator::new(DatabaseType::SQLServer);
1017        let sql = generator.generate(&plan).unwrap();
1018
1019        assert!(sql.complete_sql.contains("JSON_VALUE(dimensions, '$.category')"));
1020        assert!(sql.complete_sql.contains("CAST(occurred_at AS DATE)"));
1021        assert!(sql.complete_sql.contains("COUNT(*)"));
1022        assert!(sql.complete_sql.contains("SUM(revenue)"));
1023    }
1024
1025    #[test]
1026    fn test_having_clause() {
1027        let mut plan = create_test_plan();
1028        plan.having_conditions = vec![ValidatedHavingCondition {
1029            aggregate: AggregateExpression::MeasureAggregate {
1030                column:   "revenue".to_string(),
1031                function: AggregateFunction::Sum,
1032                alias:    "revenue_sum".to_string(),
1033            },
1034            operator:  HavingOperator::Gt,
1035            value:     serde_json::json!(1000),
1036        }];
1037
1038        let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
1039        let sql = generator.generate(&plan).unwrap();
1040
1041        assert!(sql.having.is_some());
1042        assert!(sql.having.as_ref().unwrap().contains("HAVING SUM(revenue) > 1000"));
1043    }
1044
1045    #[test]
1046    fn test_order_by_clause() {
1047        use crate::compiler::aggregation::OrderByClause;
1048
1049        let mut plan = create_test_plan();
1050        plan.request.order_by = vec![OrderByClause {
1051            field:     "revenue_sum".to_string(),
1052            direction: OrderDirection::Desc,
1053        }];
1054
1055        let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
1056        let sql = generator.generate(&plan).unwrap();
1057
1058        assert!(sql.order_by.is_some());
1059        assert!(sql.order_by.as_ref().unwrap().contains("ORDER BY revenue_sum DESC"));
1060    }
1061
1062    // ========================================
1063    // Advanced Aggregates Tests
1064    // ========================================
1065
1066    #[test]
1067    fn test_array_agg_postgres() {
1068        let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
1069
1070        // Test without ORDER BY
1071        let sql = generator.generate_array_agg_sql("product_id", None);
1072        assert_eq!(sql, "ARRAY_AGG(product_id)");
1073
1074        // Test with ORDER BY
1075        let order_by = vec![OrderByClause {
1076            field:     "revenue".to_string(),
1077            direction: OrderDirection::Desc,
1078        }];
1079        let sql = generator.generate_array_agg_sql("product_id", Some(&order_by));
1080        assert_eq!(sql, "ARRAY_AGG(product_id ORDER BY revenue DESC)");
1081    }
1082
1083    #[test]
1084    fn test_array_agg_mysql() {
1085        let generator = AggregationSqlGenerator::new(DatabaseType::MySQL);
1086        let sql = generator.generate_array_agg_sql("product_id", None);
1087        assert_eq!(sql, "JSON_ARRAYAGG(product_id)");
1088    }
1089
1090    #[test]
1091    fn test_array_agg_sqlite() {
1092        let generator = AggregationSqlGenerator::new(DatabaseType::SQLite);
1093        let sql = generator.generate_array_agg_sql("product_id", None);
1094        assert!(sql.contains("GROUP_CONCAT"));
1095        assert!(sql.contains("'[' ||"));
1096        assert!(sql.contains("|| ']'"));
1097    }
1098
1099    #[test]
1100    fn test_string_agg_postgres() {
1101        let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
1102
1103        // Test without ORDER BY
1104        let sql = generator.generate_string_agg_sql("product_name", ", ", None);
1105        assert_eq!(sql, "STRING_AGG(product_name, ', ')");
1106
1107        // Test with ORDER BY
1108        let order_by = vec![OrderByClause {
1109            field:     "revenue".to_string(),
1110            direction: OrderDirection::Desc,
1111        }];
1112        let sql = generator.generate_string_agg_sql("product_name", ", ", Some(&order_by));
1113        assert_eq!(sql, "STRING_AGG(product_name, ', ' ORDER BY revenue DESC)");
1114    }
1115
1116    #[test]
1117    fn test_string_agg_mysql() {
1118        let generator = AggregationSqlGenerator::new(DatabaseType::MySQL);
1119
1120        let order_by = vec![OrderByClause {
1121            field:     "revenue".to_string(),
1122            direction: OrderDirection::Desc,
1123        }];
1124        let sql = generator.generate_string_agg_sql("product_name", ", ", Some(&order_by));
1125        assert_eq!(sql, "GROUP_CONCAT(product_name ORDER BY revenue DESC SEPARATOR ', ')");
1126    }
1127
1128    #[test]
1129    fn test_string_agg_sqlserver() {
1130        let generator = AggregationSqlGenerator::new(DatabaseType::SQLServer);
1131
1132        let order_by = vec![OrderByClause {
1133            field:     "revenue".to_string(),
1134            direction: OrderDirection::Desc,
1135        }];
1136        let sql = generator.generate_string_agg_sql("product_name", ", ", Some(&order_by));
1137        assert!(sql.contains("STRING_AGG(CAST(product_name AS NVARCHAR(MAX)), ', ')"));
1138        assert!(sql.contains("WITHIN GROUP (ORDER BY revenue DESC)"));
1139    }
1140
1141    #[test]
1142    fn test_json_agg_postgres() {
1143        let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
1144        let sql = generator.generate_json_agg_sql("data", None);
1145        assert_eq!(sql, "JSON_AGG(data)");
1146
1147        let order_by = vec![OrderByClause {
1148            field:     "created_at".to_string(),
1149            direction: OrderDirection::Asc,
1150        }];
1151        let sql = generator.generate_json_agg_sql("data", Some(&order_by));
1152        assert_eq!(sql, "JSON_AGG(data ORDER BY created_at ASC)");
1153    }
1154
1155    #[test]
1156    fn test_jsonb_agg_postgres() {
1157        let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
1158        let sql = generator.generate_jsonb_agg_sql("data", None);
1159        assert_eq!(sql, "JSONB_AGG(data)");
1160    }
1161
1162    #[test]
1163    fn test_bool_and_postgres() {
1164        use crate::compiler::aggregate_types::BoolAggregateFunction;
1165
1166        let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
1167        let sql = generator.generate_bool_agg_sql("is_active", BoolAggregateFunction::And);
1168        assert_eq!(sql, "BOOL_AND(is_active)");
1169
1170        let sql = generator.generate_bool_agg_sql("has_discount", BoolAggregateFunction::Or);
1171        assert_eq!(sql, "BOOL_OR(has_discount)");
1172    }
1173
1174    #[test]
1175    fn test_bool_and_mysql() {
1176        use crate::compiler::aggregate_types::BoolAggregateFunction;
1177
1178        let generator = AggregationSqlGenerator::new(DatabaseType::MySQL);
1179        let sql = generator.generate_bool_agg_sql("is_active", BoolAggregateFunction::And);
1180        assert_eq!(sql, "MIN(is_active) = 1");
1181
1182        let sql = generator.generate_bool_agg_sql("has_discount", BoolAggregateFunction::Or);
1183        assert_eq!(sql, "MAX(has_discount) = 1");
1184    }
1185
1186    #[test]
1187    fn test_bool_and_sqlserver() {
1188        use crate::compiler::aggregate_types::BoolAggregateFunction;
1189
1190        let generator = AggregationSqlGenerator::new(DatabaseType::SQLServer);
1191        let sql = generator.generate_bool_agg_sql("is_active", BoolAggregateFunction::And);
1192        assert_eq!(sql, "MIN(CAST(is_active AS BIT)) = 1");
1193
1194        let sql = generator.generate_bool_agg_sql("has_discount", BoolAggregateFunction::Or);
1195        assert_eq!(sql, "MAX(CAST(has_discount AS BIT)) = 1");
1196    }
1197
1198    #[test]
1199    fn test_advanced_aggregate_full_query() {
1200        // Create a plan with advanced aggregates
1201        let mut plan = create_test_plan();
1202
1203        // Add an ARRAY_AGG aggregate
1204        plan.aggregate_expressions.push(AggregateExpression::AdvancedAggregate {
1205            column:    "product_id".to_string(),
1206            function:  AggregateFunction::ArrayAgg,
1207            alias:     "products".to_string(),
1208            delimiter: None,
1209            order_by:  Some(vec![OrderByClause {
1210                field:     "revenue".to_string(),
1211                direction: OrderDirection::Desc,
1212            }]),
1213        });
1214
1215        // Add a STRING_AGG aggregate
1216        plan.aggregate_expressions.push(AggregateExpression::AdvancedAggregate {
1217            column:    "product_name".to_string(),
1218            function:  AggregateFunction::StringAgg,
1219            alias:     "product_names".to_string(),
1220            delimiter: Some(", ".to_string()),
1221            order_by:  None,
1222        });
1223
1224        let generator = AggregationSqlGenerator::new(DatabaseType::PostgreSQL);
1225        let sql = generator.generate(&plan).unwrap();
1226
1227        assert!(sql.complete_sql.contains("ARRAY_AGG(product_id ORDER BY revenue DESC)"));
1228        assert!(sql.complete_sql.contains("STRING_AGG(product_name, ', ')"));
1229    }
1230}
fraiseql_core/runtime/aggregation.rs

fraiseql_core/runtime/
aggregation.rs
