fraiseql_core/compiler/fact_table/detector.rs
1use super::{
2 CalendarBucket, CalendarDimension, CalendarGranularity, DatabaseIntrospector, DatabaseType,
3 DimensionColumn, DimensionPath, FactTableMetadata, FilterColumn, MeasureColumn, SqlType,
4};
5use crate::error::{FraiseQLError, Result};
6
7/// Detects and introspects fact tables
8pub struct FactTableDetector;
9
10impl FactTableDetector {
11 /// Detect if a table name follows the fact table pattern.
12 ///
13 /// Fact tables must follow the naming convention: `tf_<table_name>`
14 /// where the table name contains only lowercase letters, numbers, and underscores.
15 ///
16 /// # Arguments
17 ///
18 /// * `table_name` - Table name to check
19 ///
20 /// # Returns
21 ///
22 /// `true` if the table name starts with `tf_` and follows naming conventions,
23 /// `false` otherwise.
24 ///
25 /// # Notes
26 ///
27 /// - The check is strict: `tf_` is required as a prefix
28 /// - Table names like `TF_sales` (uppercase prefix) are NOT recognized as fact tables
29 /// - Empty strings and tables named just `tf_` without additional suffix are not valid
30 ///
31 /// # Examples
32 ///
33 /// ```
34 /// use fraiseql_core::compiler::fact_table::FactTableDetector;
35 ///
36 /// assert!(FactTableDetector::is_fact_table("tf_sales"));
37 /// assert!(FactTableDetector::is_fact_table("tf_events"));
38 /// assert!(FactTableDetector::is_fact_table("tf_page_views_daily"));
39 /// assert!(!FactTableDetector::is_fact_table("ta_sales_by_day"));
40 /// assert!(!FactTableDetector::is_fact_table("v_user"));
41 /// assert!(!FactTableDetector::is_fact_table("TF_sales")); // uppercase prefix not recognized
42 /// assert!(!FactTableDetector::is_fact_table("tf_")); // incomplete name
43 /// ```
44 #[must_use]
45 pub fn is_fact_table(table_name: &str) -> bool {
46 // Must start with "tf_" and have at least one more character
47 table_name.len() > 3 && table_name.starts_with("tf_")
48 }
49
50 /// Introspect a fact table from the database
51 ///
52 /// Queries the database schema to extract:
53 /// - Measures (numeric columns)
54 /// - Dimensions (JSONB/JSON columns)
55 /// - Denormalized filters (indexed columns)
56 ///
57 /// # Arguments
58 ///
59 /// * `introspector` - Database introspection implementation
60 /// * `table_name` - Fact table name (must start with "tf_")
61 ///
62 /// # Errors
63 ///
64 /// Returns error if:
65 /// - Table is not a fact table (doesn't start with "tf_")
66 /// - Database query fails
67 /// - Table structure is invalid
68 ///
69 /// # Example
70 ///
71 /// ```rust,no_run
72 /// use fraiseql_core::compiler::fact_table::{FactTableDetector, DatabaseIntrospector};
73 ///
74 /// # async fn example(db: impl DatabaseIntrospector) -> Result<(), Box<dyn std::error::Error>> {
75 /// let metadata = FactTableDetector::introspect(&db, "tf_sales").await?;
76 /// println!("Found {} measures", metadata.measures.len());
77 /// # Ok(())
78 /// # }
79 /// ```
80 pub async fn introspect(
81 introspector: &impl DatabaseIntrospector,
82 table_name: &str,
83 ) -> Result<FactTableMetadata> {
84 // Validate table name follows fact table pattern
85 if !Self::is_fact_table(table_name) {
86 return Err(FraiseQLError::Validation {
87 message: format!(
88 "Table '{}' is not a fact table (must start with 'tf_')",
89 table_name
90 ),
91 path: None,
92 });
93 }
94
95 // Query column information
96 let columns = introspector.get_columns(table_name).await?;
97 if columns.is_empty() {
98 return Err(FraiseQLError::Validation {
99 message: format!("Table '{}' not found or has no columns", table_name),
100 path: None,
101 });
102 }
103
104 // Query indexed columns
105 let indexed_columns = introspector.get_indexed_columns(table_name).await?;
106 let indexed_set: std::collections::HashSet<String> = indexed_columns.into_iter().collect();
107
108 // Parse SQL types based on database
109 let db_type = introspector.database_type();
110
111 let mut measures = Vec::new();
112 let mut dimension_column: Option<DimensionColumn> = None;
113 let mut filters = Vec::new();
114
115 for (name, data_type, is_nullable) in &columns {
116 let sql_type = Self::parse_sql_type(data_type, db_type);
117
118 match sql_type {
119 SqlType::Jsonb | SqlType::Json => {
120 // This is the dimension column - try to extract paths from sample data
121 let paths = if let Ok(Some(sample)) =
122 introspector.get_sample_jsonb(table_name, name).await
123 {
124 Self::extract_dimension_paths(&sample, name, db_type)
125 } else {
126 Vec::new()
127 };
128 dimension_column = Some(DimensionColumn {
129 name: name.clone(),
130 paths,
131 });
132 },
133 SqlType::Int | SqlType::BigInt | SqlType::Decimal | SqlType::Float => {
134 // Skip common non-measure columns
135 if name != "id" && !name.ends_with("_id") {
136 measures.push(MeasureColumn {
137 name: name.clone(),
138 sql_type: sql_type.clone(),
139 nullable: *is_nullable,
140 });
141 }
142
143 // Check if it's a denormalized filter
144 if name.ends_with("_id") && indexed_set.contains(name.as_str()) {
145 filters.push(FilterColumn {
146 name: name.clone(),
147 sql_type: sql_type.clone(),
148 indexed: true,
149 });
150 }
151 },
152 _ => {
153 // Other types might be denormalized filters
154 if name != "id"
155 && name != "created_at"
156 && name != "updated_at"
157 && name != "occurred_at"
158 {
159 filters.push(FilterColumn {
160 name: name.clone(),
161 sql_type,
162 indexed: indexed_set.contains(name.as_str()),
163 });
164 } else if (name == "occurred_at" || name == "created_at")
165 && indexed_set.contains(name.as_str())
166 {
167 // Timestamp columns are important filters if indexed
168 filters.push(FilterColumn {
169 name: name.clone(),
170 sql_type,
171 indexed: true,
172 });
173 }
174 },
175 }
176 }
177
178 // Detect calendar dimensions
179 let calendar_dimensions = Self::detect_calendar_dimensions(&columns, &indexed_set)?;
180
181 let metadata = FactTableMetadata {
182 table_name: table_name.to_string(),
183 measures,
184 dimensions: dimension_column.unwrap_or(DimensionColumn {
185 name: "dimensions".to_string(),
186 paths: Vec::new(),
187 }),
188 denormalized_filters: filters,
189 calendar_dimensions,
190 partial_period: None,
191 native_measures: std::collections::HashMap::new(),
192 native_dimension_mapping: std::collections::HashMap::new(),
193 };
194
195 Self::validate(&metadata)?;
196 Ok(metadata)
197 }
198
199 /// Parse SQL type string to `SqlType` enum
200 fn parse_sql_type(type_name: &str, db_type: DatabaseType) -> SqlType {
201 match db_type {
202 DatabaseType::PostgreSQL => SqlType::from_str_postgres(type_name),
203 DatabaseType::MySQL => SqlType::from_str_mysql(type_name),
204 DatabaseType::SQLite => SqlType::from_str_sqlite(type_name),
205 DatabaseType::SQLServer => SqlType::from_str_sqlserver(type_name),
206 }
207 }
208
209 /// Validate fact table structure
210 ///
211 /// # Errors
212 ///
213 /// Returns error if:
214 /// - No measures found
215 /// - No dimension column found
216 /// - Measures are not numeric types
217 pub fn validate(metadata: &FactTableMetadata) -> Result<()> {
218 // Must have at least one measure
219 if metadata.measures.is_empty() {
220 return Err(FraiseQLError::Validation {
221 message: format!(
222 "Fact table '{}' must have at least one measure column",
223 metadata.table_name
224 ),
225 path: None,
226 });
227 }
228
229 // Validate all measures are numeric
230 for measure in &metadata.measures {
231 if !Self::is_numeric_type(&measure.sql_type) {
232 return Err(FraiseQLError::Validation {
233 message: format!(
234 "Measure column '{}' must be numeric type, found {:?}",
235 measure.name, measure.sql_type
236 ),
237 path: None,
238 });
239 }
240 }
241
242 // Must have dimension column
243 if metadata.dimensions.name.is_empty() {
244 return Err(FraiseQLError::Validation {
245 message: format!(
246 "Fact table '{}' must have a dimension column (JSONB)",
247 metadata.table_name
248 ),
249 path: None,
250 });
251 }
252
253 Ok(())
254 }
255
256 /// Check if SQL type is numeric (suitable for aggregation)
257 pub(super) const fn is_numeric_type(sql_type: &SqlType) -> bool {
258 matches!(sql_type, SqlType::Int | SqlType::BigInt | SqlType::Decimal | SqlType::Float)
259 }
260
261 /// Extract dimension paths from a sample JSON value
262 ///
263 /// Walks through the JSON structure and extracts top-level keys as dimension paths.
264 /// Nested objects are represented with dot notation (e.g., "customer.region").
265 ///
266 /// # Arguments
267 ///
268 /// * `sample` - Sample JSON value from the dimension column
269 /// * `column_name` - Name of the JSONB column (e.g., "dimensions")
270 /// * `db_type` - Database type for generating correct JSON path syntax
271 ///
272 /// # Returns
273 ///
274 /// Vec of `DimensionPath` extracted from the sample data
275 #[must_use]
276 pub fn extract_dimension_paths(
277 sample: &serde_json::Value,
278 column_name: &str,
279 db_type: DatabaseType,
280 ) -> Vec<DimensionPath> {
281 let mut paths = Vec::new();
282 Self::extract_paths_recursive(sample, column_name, "", &mut paths, db_type, 0);
283 paths
284 }
285
286 /// Recursively extract paths from JSON structure
287 fn extract_paths_recursive(
288 value: &serde_json::Value,
289 column_name: &str,
290 prefix: &str,
291 paths: &mut Vec<DimensionPath>,
292 db_type: DatabaseType,
293 depth: usize,
294 ) {
295 // Limit depth to avoid infinite recursion on deeply nested structures
296 if depth > 3 {
297 return;
298 }
299
300 if let Some(obj) = value.as_object() {
301 for (key, val) in obj {
302 let full_path = if prefix.is_empty() {
303 key.clone()
304 } else {
305 format!("{}.{}", prefix, key)
306 };
307
308 // Determine data type from the value
309 let data_type = Self::infer_json_type(val);
310
311 // Generate database-specific JSON path syntax
312 let json_path = Self::generate_json_path(column_name, &full_path, db_type);
313
314 paths.push(DimensionPath {
315 name: full_path.replace('.', "_"), /* Convert dots to underscores for field
316 * names */
317 json_path,
318 data_type,
319 });
320
321 // Recurse into nested objects
322 if val.is_object() {
323 Self::extract_paths_recursive(
324 val,
325 column_name,
326 &full_path,
327 paths,
328 db_type,
329 depth + 1,
330 );
331 }
332 }
333 }
334 }
335
336 /// Infer JSON data type from a value
337 pub(super) fn infer_json_type(value: &serde_json::Value) -> String {
338 match value {
339 serde_json::Value::Bool(_) => "boolean".to_string(),
340 serde_json::Value::Number(n) => {
341 if n.is_i64() || n.is_u64() {
342 "integer".to_string()
343 } else {
344 "float".to_string()
345 }
346 },
347 serde_json::Value::Array(_) => "array".to_string(),
348 serde_json::Value::Object(_) => "object".to_string(),
349 // Null and String both infer as "string" type
350 serde_json::Value::Null | serde_json::Value::String(_) => "string".to_string(),
351 }
352 }
353
354 /// Generate database-specific JSON path syntax
355 pub(super) fn generate_json_path(
356 column_name: &str,
357 path: &str,
358 db_type: DatabaseType,
359 ) -> String {
360 let parts: Vec<&str> = path.split('.').collect();
361
362 match db_type {
363 DatabaseType::PostgreSQL => {
364 // PostgreSQL: column->>'key' for top-level, column->'nested'->>'key' for nested
365 if parts.is_empty() {
366 // Safety: handle empty path by returning raw column name
367 column_name.to_string()
368 } else if parts.len() == 1 {
369 format!("{}->>'{}'", column_name, parts[0])
370 } else {
371 // Safe: parts.len() >= 2 is guaranteed here
372 if let Some(last) = parts.last() {
373 let rest = &parts[..parts.len() - 1];
374 let nav = rest.iter().fold(String::new(), |mut acc, p| {
375 use std::fmt::Write;
376 let _ = write!(acc, "->'{}'", p);
377 acc
378 });
379 format!("{}{}->>'{}'", column_name, nav, last)
380 } else {
381 // This branch is unreachable due to length check, but safe fallback
382 column_name.to_string()
383 }
384 }
385 },
386 DatabaseType::MySQL => {
387 // MySQL: JSON_EXTRACT(column, '$.path.to.key')
388 format!("JSON_UNQUOTE(JSON_EXTRACT({}, '$.{}')", column_name, path)
389 },
390 DatabaseType::SQLite => {
391 // SQLite: json_extract(column, '$.path.to.key')
392 format!("json_extract({}, '$.{}')", column_name, path)
393 },
394 DatabaseType::SQLServer => {
395 // SQL Server: JSON_VALUE(column, '$.path.to.key')
396 format!("JSON_VALUE({}, '$.{}')", column_name, path)
397 },
398 }
399 }
400
401 /// Detect calendar dimension columns (`date_info`, `week_info`, etc.)
402 ///
403 /// Looks for `*_info` JSONB/JSON columns following the calendar dimension pattern.
404 /// Returns calendar dimension metadata if calendar columns are found.
405 ///
406 /// # Arguments
407 ///
408 /// * `columns` - List of (name, `data_type`, nullable) tuples
409 /// * `_indexed_set` - Set of indexed columns (unused, for future optimization detection)
410 ///
411 /// # Returns
412 ///
413 /// Vec of calendar dimensions (empty if none found)
414 pub(super) fn detect_calendar_dimensions(
415 columns: &[(String, String, bool)],
416 _indexed_set: &std::collections::HashSet<String>,
417 ) -> Result<Vec<CalendarDimension>> {
418 // Look for *_info columns with JSONB/JSON type
419 let calendar_columns: Vec<String> = columns
420 .iter()
421 .filter(|(name, data_type, _)| {
422 name.ends_with("_info")
423 && (data_type.to_lowercase().contains("json")
424 || data_type.to_lowercase().contains("jsonb"))
425 })
426 .map(|(name, _, _)| name.clone())
427 .collect();
428
429 if calendar_columns.is_empty() {
430 return Ok(Vec::new());
431 }
432
433 // Build granularities based on calendar dimension pattern
434 let mut granularities = Vec::new();
435 for col_name in calendar_columns {
436 let buckets = Self::infer_calendar_buckets(&col_name);
437 if !buckets.is_empty() {
438 granularities.push(CalendarGranularity {
439 column_name: col_name,
440 buckets,
441 });
442 }
443 }
444
445 if granularities.is_empty() {
446 return Ok(Vec::new());
447 }
448
449 // Assume single source column "occurred_at"
450 // (could be enhanced to detect from schema later)
451 Ok(vec![CalendarDimension {
452 source_column: "occurred_at".to_string(),
453 granularities,
454 }])
455 }
456
457 /// Map calendar column names to available buckets (standard pattern)
458 ///
459 /// # Arguments
460 ///
461 /// * `column_name` - Name of the calendar column (e.g., "`date_info`", "`month_info`")
462 ///
463 /// # Returns
464 ///
465 /// Vec of calendar buckets available in this column
466 pub(super) fn infer_calendar_buckets(column_name: &str) -> Vec<CalendarBucket> {
467 use crate::compiler::aggregate_types::TemporalBucket;
468
469 match column_name {
470 "date_info" => vec![
471 CalendarBucket {
472 json_key: "date".to_string(),
473 bucket_type: TemporalBucket::Day,
474 data_type: "date".to_string(),
475 },
476 CalendarBucket {
477 json_key: "week".to_string(),
478 bucket_type: TemporalBucket::Week,
479 data_type: "integer".to_string(),
480 },
481 CalendarBucket {
482 json_key: "month".to_string(),
483 bucket_type: TemporalBucket::Month,
484 data_type: "integer".to_string(),
485 },
486 CalendarBucket {
487 json_key: "quarter".to_string(),
488 bucket_type: TemporalBucket::Quarter,
489 data_type: "integer".to_string(),
490 },
491 CalendarBucket {
492 json_key: "year".to_string(),
493 bucket_type: TemporalBucket::Year,
494 data_type: "integer".to_string(),
495 },
496 ],
497 "week_info" => vec![
498 CalendarBucket {
499 json_key: "week".to_string(),
500 bucket_type: TemporalBucket::Week,
501 data_type: "integer".to_string(),
502 },
503 CalendarBucket {
504 json_key: "month".to_string(),
505 bucket_type: TemporalBucket::Month,
506 data_type: "integer".to_string(),
507 },
508 CalendarBucket {
509 json_key: "quarter".to_string(),
510 bucket_type: TemporalBucket::Quarter,
511 data_type: "integer".to_string(),
512 },
513 CalendarBucket {
514 json_key: "year".to_string(),
515 bucket_type: TemporalBucket::Year,
516 data_type: "integer".to_string(),
517 },
518 ],
519 "month_info" => vec![
520 CalendarBucket {
521 json_key: "month".to_string(),
522 bucket_type: TemporalBucket::Month,
523 data_type: "integer".to_string(),
524 },
525 CalendarBucket {
526 json_key: "quarter".to_string(),
527 bucket_type: TemporalBucket::Quarter,
528 data_type: "integer".to_string(),
529 },
530 CalendarBucket {
531 json_key: "year".to_string(),
532 bucket_type: TemporalBucket::Year,
533 data_type: "integer".to_string(),
534 },
535 ],
536 "quarter_info" => vec![
537 CalendarBucket {
538 json_key: "quarter".to_string(),
539 bucket_type: TemporalBucket::Quarter,
540 data_type: "integer".to_string(),
541 },
542 CalendarBucket {
543 json_key: "year".to_string(),
544 bucket_type: TemporalBucket::Year,
545 data_type: "integer".to_string(),
546 },
547 ],
548 "semester_info" => vec![
549 CalendarBucket {
550 json_key: "semester".to_string(),
551 bucket_type: TemporalBucket::Quarter, // Map to Quarter for now
552 data_type: "integer".to_string(),
553 },
554 CalendarBucket {
555 json_key: "year".to_string(),
556 bucket_type: TemporalBucket::Year,
557 data_type: "integer".to_string(),
558 },
559 ],
560 "year_info" => vec![CalendarBucket {
561 json_key: "year".to_string(),
562 bucket_type: TemporalBucket::Year,
563 data_type: "integer".to_string(),
564 }],
565 _ => Vec::new(),
566 }
567 }
568
569 /// Create metadata from column definitions (for testing)
570 ///
571 /// # Errors
572 ///
573 /// Returns `FraiseQLError::Validation` if no time dimension column is found.
574 pub fn from_columns(
575 table_name: String,
576 columns: Vec<(&str, SqlType, bool)>,
577 ) -> Result<FactTableMetadata> {
578 let mut measures = Vec::new();
579 let mut dimension_column: Option<DimensionColumn> = None;
580 let mut filters = Vec::new();
581
582 for (name, sql_type, nullable) in columns {
583 match sql_type {
584 SqlType::Jsonb | SqlType::Json => {
585 // This is the dimension column
586 dimension_column = Some(DimensionColumn {
587 name: name.to_string(),
588 paths: Vec::new(),
589 });
590 },
591 SqlType::Int | SqlType::BigInt | SqlType::Decimal | SqlType::Float => {
592 // Skip id column
593 if name != "id" && !name.ends_with("_id") {
594 // This is a measure
595 measures.push(MeasureColumn {
596 name: name.to_string(),
597 sql_type,
598 nullable,
599 });
600 } else if name != "id" {
601 // This is a filter (_id columns)
602 filters.push(FilterColumn {
603 name: name.to_string(),
604 sql_type,
605 indexed: false,
606 });
607 }
608 },
609 _ => {
610 // This might be a filter column (if not id/created_at/updated_at)
611 if name != "id" && name != "created_at" && name != "updated_at" {
612 filters.push(FilterColumn {
613 name: name.to_string(),
614 sql_type,
615 indexed: false, // Would need to query indexes to determine
616 });
617 }
618 },
619 }
620 }
621
622 let metadata = FactTableMetadata {
623 table_name,
624 measures,
625 dimensions: dimension_column.unwrap_or(DimensionColumn {
626 name: "dimensions".to_string(),
627 paths: Vec::new(),
628 }),
629 denormalized_filters: filters,
630 calendar_dimensions: Vec::new(), // No calendar detection in test helper
631 partial_period: None,
632 native_measures: std::collections::HashMap::new(),
633 native_dimension_mapping: std::collections::HashMap::new(),
634 };
635
636 Self::validate(&metadata)?;
637 Ok(metadata)
638 }
639}