sql_cli/sql/functions/
format.rs

1use anyhow::{anyhow, Result};
2use chrono::{DateTime, NaiveDate, NaiveDateTime, Utc};
3
4use crate::data::datatable::DataValue;
5use crate::sql::functions::{ArgCount, FunctionCategory, FunctionSignature, SqlFunction};
6
7/// FORMAT_NUMBER function - Format numbers with specified decimal places and separators
8pub struct FormatNumberFunction;
9
10impl SqlFunction for FormatNumberFunction {
11    fn signature(&self) -> FunctionSignature {
12        FunctionSignature {
13            name: "FORMAT_NUMBER",
14            category: FunctionCategory::String,
15            arg_count: ArgCount::Range(1, 3),
16            description: "Format a number with decimal places and thousand separators",
17            returns: "STRING",
18            examples: vec![
19                "SELECT FORMAT_NUMBER(1234567.89, 2)",    // "1,234,567.89"
20                "SELECT FORMAT_NUMBER(1234.5, 2, false)", // "1234.50" (no thousands sep)
21                "SELECT FORMAT_NUMBER(1234567)",          // "1,234,567"
22            ],
23        }
24    }
25
26    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
27        self.validate_args(args)?;
28
29        let value = match &args[0] {
30            DataValue::Integer(n) => *n as f64,
31            DataValue::Float(f) => *f,
32            DataValue::Null => return Ok(DataValue::Null),
33            _ => return Err(anyhow!("FORMAT_NUMBER requires a numeric argument")),
34        };
35
36        let decimals = if args.len() >= 2 {
37            match &args[1] {
38                DataValue::Integer(n) => *n as usize,
39                DataValue::Float(f) => *f as usize,
40                _ => 2,
41            }
42        } else {
43            0
44        };
45
46        let use_separator = if args.len() >= 3 {
47            match &args[2] {
48                DataValue::Boolean(b) => *b,
49                DataValue::Integer(n) => *n != 0,
50                _ => true,
51            }
52        } else {
53            true
54        };
55
56        // Format with decimal places
57        let formatted = if decimals > 0 {
58            format!("{:.prec$}", value, prec = decimals)
59        } else {
60            format!("{:.0}", value)
61        };
62
63        // Add thousand separators if requested
64        let result = if use_separator {
65            add_thousand_separators(&formatted)
66        } else {
67            formatted
68        };
69
70        Ok(DataValue::String(result))
71    }
72}
73
74fn add_thousand_separators(s: &str) -> String {
75    let parts: Vec<&str> = s.split('.').collect();
76    let integer_part = parts[0];
77    let decimal_part = parts.get(1);
78
79    let mut result = String::new();
80    let mut count = 0;
81
82    for ch in integer_part.chars().rev() {
83        if count > 0 && count % 3 == 0 && ch != '-' {
84            result.push(',');
85        }
86        result.push(ch);
87        if ch != '-' {
88            count += 1;
89        }
90    }
91
92    let formatted_integer: String = result.chars().rev().collect();
93
94    if let Some(dec) = decimal_part {
95        format!("{}.{}", formatted_integer, dec)
96    } else {
97        formatted_integer
98    }
99}
100
101/// LPAD function - Left pad a string to a specified length
102pub struct LPadFunction;
103
104impl SqlFunction for LPadFunction {
105    fn signature(&self) -> FunctionSignature {
106        FunctionSignature {
107            name: "LPAD",
108            category: FunctionCategory::String,
109            arg_count: ArgCount::Range(2, 3),
110            description: "Left pad a string to a specified length with a fill character",
111            returns: "STRING",
112            examples: vec![
113                "SELECT LPAD('123', 5)",         // "  123"
114                "SELECT LPAD('123', 5, '0')",    // "00123"
115                "SELECT LPAD('hello', 10, '.')", // ".....hello"
116            ],
117        }
118    }
119
120    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
121        self.validate_args(args)?;
122
123        let text = match &args[0] {
124            DataValue::String(s) => s.clone(),
125            DataValue::Integer(n) => n.to_string(),
126            DataValue::Float(f) => f.to_string(),
127            DataValue::Null => return Ok(DataValue::Null),
128            _ => return Err(anyhow!("LPAD requires a string or numeric argument")),
129        };
130
131        let length = match &args[1] {
132            DataValue::Integer(n) if *n >= 0 => *n as usize,
133            _ => return Err(anyhow!("LPAD length must be a non-negative integer")),
134        };
135
136        let pad_char = if args.len() >= 3 {
137            match &args[2] {
138                DataValue::String(s) if !s.is_empty() => s.chars().next().unwrap(),
139                _ => ' ',
140            }
141        } else {
142            ' '
143        };
144
145        if text.len() >= length {
146            Ok(DataValue::String(text[..length].to_string()))
147        } else {
148            let padding = pad_char.to_string().repeat(length - text.len());
149            Ok(DataValue::String(format!("{}{}", padding, text)))
150        }
151    }
152}
153
154/// RPAD function - Right pad a string to a specified length
155pub struct RPadFunction;
156
157impl SqlFunction for RPadFunction {
158    fn signature(&self) -> FunctionSignature {
159        FunctionSignature {
160            name: "RPAD",
161            category: FunctionCategory::String,
162            arg_count: ArgCount::Range(2, 3),
163            description: "Right pad a string to a specified length with a fill character",
164            returns: "STRING",
165            examples: vec![
166                "SELECT RPAD('123', 5)",         // "123  "
167                "SELECT RPAD('123', 5, '0')",    // "12300"
168                "SELECT RPAD('hello', 10, '.')", // "hello....."
169            ],
170        }
171    }
172
173    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
174        self.validate_args(args)?;
175
176        let text = match &args[0] {
177            DataValue::String(s) => s.clone(),
178            DataValue::Integer(n) => n.to_string(),
179            DataValue::Float(f) => f.to_string(),
180            DataValue::Null => return Ok(DataValue::Null),
181            _ => return Err(anyhow!("RPAD requires a string or numeric argument")),
182        };
183
184        let length = match &args[1] {
185            DataValue::Integer(n) if *n >= 0 => *n as usize,
186            _ => return Err(anyhow!("RPAD length must be a non-negative integer")),
187        };
188
189        let pad_char = if args.len() >= 3 {
190            match &args[2] {
191                DataValue::String(s) if !s.is_empty() => s.chars().next().unwrap(),
192                _ => ' ',
193            }
194        } else {
195            ' '
196        };
197
198        if text.len() >= length {
199            Ok(DataValue::String(text[..length].to_string()))
200        } else {
201            let padding = pad_char.to_string().repeat(length - text.len());
202            Ok(DataValue::String(format!("{}{}", text, padding)))
203        }
204    }
205}
206
207/// CENTER function - Center a string within a specified width
208pub struct CenterFunction;
209
210impl SqlFunction for CenterFunction {
211    fn signature(&self) -> FunctionSignature {
212        FunctionSignature {
213            name: "CENTER",
214            category: FunctionCategory::String,
215            arg_count: ArgCount::Range(2, 3),
216            description: "Center a string within a specified width",
217            returns: "STRING",
218            examples: vec![
219                "SELECT CENTER('hello', 11)",     // "   hello   "
220                "SELECT CENTER('test', 10, '.')", // "...test..."
221                "SELECT CENTER('SQL', 7, '-')",   // "--SQL--"
222            ],
223        }
224    }
225
226    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
227        self.validate_args(args)?;
228
229        let text = match &args[0] {
230            DataValue::String(s) => s.clone(),
231            DataValue::Integer(n) => n.to_string(),
232            DataValue::Float(f) => f.to_string(),
233            DataValue::Null => return Ok(DataValue::Null),
234            _ => return Err(anyhow!("CENTER requires a string or numeric argument")),
235        };
236
237        let width = match &args[1] {
238            DataValue::Integer(n) if *n >= 0 => *n as usize,
239            _ => return Err(anyhow!("CENTER width must be a non-negative integer")),
240        };
241
242        let pad_char = if args.len() >= 3 {
243            match &args[2] {
244                DataValue::String(s) if !s.is_empty() => s.chars().next().unwrap(),
245                _ => ' ',
246            }
247        } else {
248            ' '
249        };
250
251        if text.len() >= width {
252            Ok(DataValue::String(text[..width].to_string()))
253        } else {
254            let total_padding = width - text.len();
255            let left_padding = total_padding / 2;
256            let right_padding = total_padding - left_padding;
257
258            let left = pad_char.to_string().repeat(left_padding);
259            let right = pad_char.to_string().repeat(right_padding);
260
261            Ok(DataValue::String(format!("{}{}{}", left, text, right)))
262        }
263    }
264}
265
266/// FORMAT_DATE function - Format dates using format strings
267pub struct FormatDateFunction;
268
269impl SqlFunction for FormatDateFunction {
270    fn signature(&self) -> FunctionSignature {
271        FunctionSignature {
272            name: "FORMAT_DATE",
273            category: FunctionCategory::Date,
274            arg_count: ArgCount::Fixed(2),
275            description: "Format a date using a format string",
276            returns: "STRING",
277            examples: vec![
278                "SELECT FORMAT_DATE(NOW(), '%Y-%m-%d')",      // "2024-03-15"
279                "SELECT FORMAT_DATE(NOW(), '%B %d, %Y')",     // "March 15, 2024"
280                "SELECT FORMAT_DATE(NOW(), '%Y%m%d_%H%M%S')", // "20240315_143022"
281            ],
282        }
283    }
284
285    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
286        self.validate_args(args)?;
287
288        let datetime_str = match &args[0] {
289            DataValue::DateTime(dt) => dt,
290            DataValue::String(s) => s, // Also accept string dates
291            DataValue::Null => return Ok(DataValue::Null),
292            _ => {
293                return Err(anyhow!(
294                    "FORMAT_DATE requires a datetime or string argument"
295                ))
296            }
297        };
298
299        let format_str = match &args[1] {
300            DataValue::String(s) => s,
301            _ => return Err(anyhow!("FORMAT_DATE format must be a string")),
302        };
303
304        // Try to parse as ISO 8601 or common date formats
305
306        let formatted = if let Ok(dt) = datetime_str.parse::<DateTime<Utc>>() {
307            dt.format(format_str).to_string()
308        } else if let Ok(dt) = NaiveDateTime::parse_from_str(datetime_str, "%Y-%m-%d %H:%M:%S") {
309            dt.format(format_str).to_string()
310        } else if let Ok(dt) = NaiveDate::parse_from_str(datetime_str, "%Y-%m-%d") {
311            dt.format(format_str).to_string()
312        } else if let Ok(dt) = NaiveDateTime::parse_from_str(datetime_str, "%Y-%m-%dT%H:%M:%S") {
313            dt.format(format_str).to_string()
314        } else {
315            return Err(anyhow!("Cannot parse datetime: {}", datetime_str));
316        };
317
318        Ok(DataValue::String(formatted))
319    }
320}
sql_cli/sql/functions/format.rs

sql_cli/sql/functions/
format.rs
