sql_cli/data/
type_inference.rs

1//! Shared type inference logic for data loaders
2//!
3//! This module provides centralized type detection logic to ensure
4//! consistent behavior across CSV, JSON, and other data sources.
5
6use regex::Regex;
7use std::sync::LazyLock;
8
9/// Static compiled regex patterns for date detection
10/// Using `LazyLock` for thread-safe initialization
11static DATE_PATTERNS: LazyLock<Vec<Regex>> = LazyLock::new(|| {
12    vec![
13        // YYYY-MM-DD (year must be 19xx or 20xx, month 01-12, day 01-31)
14        Regex::new(r"^(19|20)\d{2}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])$").unwrap(),
15        // YYYY-MM-DD HH:MM:SS (with space separator)
16        Regex::new(r"^(19|20)\d{2}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])\s+\d{2}:\d{2}:\d{2}$").unwrap(),
17        // YYYY-MM-DD HH:MM:SS.mmm (with milliseconds)
18        Regex::new(r"^(19|20)\d{2}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])\s+\d{2}:\d{2}:\d{2}\.\d{1,3}$").unwrap(),
19        // MM/DD/YYYY
20        Regex::new(r"^(0[1-9]|1[0-2])/(0[1-9]|[12]\d|3[01])/(19|20)\d{2}$").unwrap(),
21        // DD/MM/YYYY
22        Regex::new(r"^(0[1-9]|[12]\d|3[01])/(0[1-9]|1[0-2])/(19|20)\d{2}$").unwrap(),
23        // DD/MM/YYYY HH:MM:SS (UK format with time)
24        Regex::new(r"^(0[1-9]|[12]\d|3[01])/(0[1-9]|1[0-2])/(19|20)\d{2}\s+\d{2}:\d{2}:\d{2}$").unwrap(),
25        // DD/MM/YYYY HH:MM:SS.mmm (UK format with milliseconds)
26        Regex::new(r"^(0[1-9]|[12]\d|3[01])/(0[1-9]|1[0-2])/(19|20)\d{2}\s+\d{2}:\d{2}:\d{2}\.\d{1,3}$").unwrap(),
27        // DD-MM-YYYY
28        Regex::new(r"^(0[1-9]|[12]\d|3[01])-(0[1-9]|1[0-2])-(19|20)\d{2}$").unwrap(),
29        // YYYY/MM/DD
30        Regex::new(r"^(19|20)\d{2}/(0[1-9]|1[0-2])/(0[1-9]|[12]\d|3[01])$").unwrap(),
31        // YYYY/MM/DD HH:MM:SS
32        Regex::new(r"^(19|20)\d{2}/(0[1-9]|1[0-2])/(0[1-9]|[12]\d|3[01])\s+\d{2}:\d{2}:\d{2}$").unwrap(),
33        // ISO 8601 with time: YYYY-MM-DDTHH:MM:SS
34        Regex::new(r"^(19|20)\d{2}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])T\d{2}:\d{2}:\d{2}")
35            .unwrap(),
36        // ISO 8601 with timezone: YYYY-MM-DDTHH:MM:SS+/-HH:MM or Z
37        Regex::new(
38            r"^(19|20)\d{2}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])T\d{2}:\d{2}:\d{2}(\.\d+)?(Z|[+-]\d{2}:\d{2})$",
39        )
40        .unwrap(),
41    ]
42});
43
44/// Detected data type for a value or column
45#[derive(Debug, Clone, Copy, PartialEq, Eq)]
46pub enum InferredType {
47    Boolean,
48    Integer,
49    Float,
50    DateTime,
51    String,
52    Null,
53}
54
55/// Type inference utilities
56pub struct TypeInference;
57
58impl TypeInference {
59    /// Infer the type of a single string value
60    ///
61    /// This is the main entry point for type detection.
62    /// Order of checks is important for performance and accuracy.
63    #[must_use]
64    pub fn infer_from_string(value: &str) -> InferredType {
65        // Empty values are null
66        if value.is_empty() {
67            return InferredType::Null;
68        }
69
70        // Column-aligned CSVs pad their fields (` 1732`, `\t22`), and a number or
71        // a boolean has no meaningful surrounding whitespace — so classify past
72        // the padding. Only the *classification* looks through it; a value that
73        // turns out to be a string still keeps its spaces verbatim.
74        let value = value.trim();
75
76        // Check boolean first (fast string comparison)
77        if value.eq_ignore_ascii_case("true") || value.eq_ignore_ascii_case("false") {
78            return InferredType::Boolean;
79        }
80
81        // Try integer (common case, relatively fast)
82        if value.parse::<i64>().is_ok() {
83            return InferredType::Integer;
84        }
85
86        // Try float (includes scientific notation)
87        if value.parse::<f64>().is_ok() {
88            return InferredType::Float;
89        }
90
91        // Check if it looks like a datetime
92        // This is the most expensive check, so we do it last
93        if Self::looks_like_datetime(value) {
94            return InferredType::DateTime;
95        }
96
97        // Default to string
98        InferredType::String
99    }
100
101    /// Check if a string looks like a datetime value
102    ///
103    /// Uses strict regex patterns to avoid false positives with ID strings
104    /// like "BQ-123456" or "ORDER-2024-001"
105    pub fn looks_like_datetime(value: &str) -> bool {
106        // Quick length check - dates are typically 8-30 chars
107        if value.len() < 8 || value.len() > 35 {
108            return false;
109        }
110
111        // Check against our compiled patterns
112        DATE_PATTERNS.iter().any(|pattern| pattern.is_match(value))
113    }
114
115    /// Merge two types when a column has mixed types
116    ///
117    /// Rules:
118    /// - Same type -> keep it
119    /// - Null with anything -> the other type
120    /// - Integer + Float -> Float
121    /// - Any numeric + String -> String
122    /// - `DateTime` + String -> String
123    /// - Everything else -> String
124    #[must_use]
125    pub fn merge_types(type1: InferredType, type2: InferredType) -> InferredType {
126        use InferredType::{Boolean, DateTime, Float, Integer, Null, String};
127
128        match (type1, type2) {
129            // Same type
130            (t1, t2) if t1 == t2 => t1,
131
132            // Null merges to the other type
133            (Null, t) | (t, Null) => t,
134
135            // Integer and Float -> Float
136            (Integer, Float) | (Float, Integer) => Float,
137
138            // Boolean stays boolean only with itself or null
139            (Boolean, _) | (_, Boolean) => String,
140
141            // DateTime only compatible with itself or null
142            (DateTime, _) | (_, DateTime) => String,
143
144            // Default to String for mixed types
145            _ => String,
146        }
147    }
148
149    /// Infer type from multiple sample values
150    ///
151    /// Useful for determining column type from a sample of rows.
152    /// Returns the most specific type that fits all non-null values.
153    pub fn infer_from_samples<'a, I>(values: I) -> InferredType
154    where
155        I: Iterator<Item = &'a str>,
156    {
157        let mut result_type = InferredType::Null;
158
159        for value in values {
160            let value_type = Self::infer_from_string(value);
161            result_type = Self::merge_types(result_type, value_type);
162
163            // Early exit if we've degraded to String
164            if result_type == InferredType::String {
165                break;
166            }
167        }
168
169        result_type
170    }
171
172    /// Check if a value can be coerced to a specific type
173    #[must_use]
174    pub fn can_coerce_to(value: &str, target_type: InferredType) -> bool {
175        match target_type {
176            InferredType::Boolean => {
177                value.eq_ignore_ascii_case("true")
178                    || value.eq_ignore_ascii_case("false")
179                    || value == "0"
180                    || value == "1"
181            }
182            InferredType::Integer => value.parse::<i64>().is_ok(),
183            InferredType::Float => value.parse::<f64>().is_ok(),
184            InferredType::DateTime => Self::looks_like_datetime(value),
185            InferredType::String => true, // Everything can be a string
186            InferredType::Null => value.is_empty(),
187        }
188    }
189}
190
191#[cfg(test)]
192mod tests {
193    use super::*;
194
195    #[test]
196    fn test_basic_type_inference() {
197        assert_eq!(
198            TypeInference::infer_from_string("123"),
199            InferredType::Integer
200        );
201        assert_eq!(
202            TypeInference::infer_from_string("123.45"),
203            InferredType::Float
204        );
205        assert_eq!(
206            TypeInference::infer_from_string("true"),
207            InferredType::Boolean
208        );
209        assert_eq!(
210            TypeInference::infer_from_string("FALSE"),
211            InferredType::Boolean
212        );
213        assert_eq!(
214            TypeInference::infer_from_string("hello"),
215            InferredType::String
216        );
217        assert_eq!(TypeInference::infer_from_string(""), InferredType::Null);
218    }
219
220    #[test]
221    fn test_datetime_detection() {
222        // Valid dates should be detected
223        assert_eq!(
224            TypeInference::infer_from_string("2024-01-15"),
225            InferredType::DateTime
226        );
227        assert_eq!(
228            TypeInference::infer_from_string("01/15/2024"),
229            InferredType::DateTime
230        );
231        assert_eq!(
232            TypeInference::infer_from_string("15-01-2024"),
233            InferredType::DateTime
234        );
235        assert_eq!(
236            TypeInference::infer_from_string("2024-01-15T10:30:00"),
237            InferredType::DateTime
238        );
239        assert_eq!(
240            TypeInference::infer_from_string("2024-01-15T10:30:00Z"),
241            InferredType::DateTime
242        );
243    }
244
245    #[test]
246    fn test_id_strings_not_detected_as_datetime() {
247        // These should be detected as String, not DateTime
248        assert_eq!(
249            TypeInference::infer_from_string("BQ-81198596"),
250            InferredType::String
251        );
252        assert_eq!(
253            TypeInference::infer_from_string("ORDER-2024-001"),
254            InferredType::String
255        );
256        assert_eq!(
257            TypeInference::infer_from_string("ID-123-456"),
258            InferredType::String
259        );
260        assert_eq!(
261            TypeInference::infer_from_string("ABC-DEF-GHI"),
262            InferredType::String
263        );
264        assert_eq!(
265            TypeInference::infer_from_string("2024-ABC-123"),
266            InferredType::String
267        );
268    }
269
270    #[test]
271    fn test_invalid_dates_not_detected() {
272        // Invalid month/day combinations
273        assert_eq!(
274            TypeInference::infer_from_string("2024-13-01"), // Month 13
275            InferredType::String
276        );
277        assert_eq!(
278            TypeInference::infer_from_string("2024-00-15"), // Month 00
279            InferredType::String
280        );
281        assert_eq!(
282            TypeInference::infer_from_string("2024-01-32"), // Day 32
283            InferredType::String
284        );
285        assert_eq!(
286            TypeInference::infer_from_string("2024-01-00"), // Day 00
287            InferredType::String
288        );
289    }
290
291    #[test]
292    fn test_type_merging() {
293        use InferredType::*;
294
295        // Same type
296        assert_eq!(TypeInference::merge_types(Integer, Integer), Integer);
297        assert_eq!(TypeInference::merge_types(String, String), String);
298
299        // Null with anything
300        assert_eq!(TypeInference::merge_types(Null, Integer), Integer);
301        assert_eq!(TypeInference::merge_types(Float, Null), Float);
302
303        // Integer and Float
304        assert_eq!(TypeInference::merge_types(Integer, Float), Float);
305        assert_eq!(TypeInference::merge_types(Float, Integer), Float);
306
307        // Mixed types degrade to String
308        assert_eq!(TypeInference::merge_types(Integer, String), String);
309        assert_eq!(TypeInference::merge_types(DateTime, Integer), String);
310        assert_eq!(TypeInference::merge_types(Boolean, Float), String);
311    }
312
313    #[test]
314    fn test_infer_from_samples() {
315        // All integers
316        let samples = vec!["1", "2", "3", "4", "5"];
317        assert_eq!(
318            TypeInference::infer_from_samples(samples.into_iter()),
319            InferredType::Integer
320        );
321
322        // Mixed integer and float
323        let samples = vec!["1", "2.5", "3", "4.0"];
324        assert_eq!(
325            TypeInference::infer_from_samples(samples.into_iter()),
326            InferredType::Float
327        );
328
329        // Mixed types degrade to string
330        let samples = vec!["1", "hello", "3"];
331        assert_eq!(
332            TypeInference::infer_from_samples(samples.into_iter()),
333            InferredType::String
334        );
335
336        // With nulls (empty strings)
337        let samples = vec!["", "1", "", "2", "3"];
338        assert_eq!(
339            TypeInference::infer_from_samples(samples.into_iter()),
340            InferredType::Integer
341        );
342    }
343
344    #[test]
345    fn test_can_coerce() {
346        // Boolean coercion
347        assert!(TypeInference::can_coerce_to("true", InferredType::Boolean));
348        assert!(TypeInference::can_coerce_to("1", InferredType::Boolean));
349        assert!(TypeInference::can_coerce_to("0", InferredType::Boolean));
350        assert!(!TypeInference::can_coerce_to(
351            "hello",
352            InferredType::Boolean
353        ));
354
355        // Integer coercion
356        assert!(TypeInference::can_coerce_to("123", InferredType::Integer));
357        assert!(!TypeInference::can_coerce_to(
358            "123.45",
359            InferredType::Integer
360        ));
361        assert!(!TypeInference::can_coerce_to(
362            "hello",
363            InferredType::Integer
364        ));
365
366        // Everything can be a string
367        assert!(TypeInference::can_coerce_to("123", InferredType::String));
368        assert!(TypeInference::can_coerce_to("hello", InferredType::String));
369        assert!(TypeInference::can_coerce_to("", InferredType::String));
370    }
371}
sql_cli/data/type_inference.rs

sql_cli/data/
type_inference.rs
