sql_cli/sql/functions/
utility.rs

1use crate::data::datatable::DataValue;
2use crate::sql::functions::{ArgCount, FunctionCategory, FunctionSignature, SqlFunction};
3use anyhow::Result;
4use base64::{engine::general_purpose::STANDARD as BASE64, Engine};
5
6/// ASCII() - Get ASCII/Unicode code point of first character
7pub struct AsciiFunction;
8
9impl SqlFunction for AsciiFunction {
10    fn signature(&self) -> FunctionSignature {
11        FunctionSignature {
12            name: "ASCII",
13            category: FunctionCategory::String,
14            arg_count: ArgCount::Fixed(1),
15            description: "Get ASCII/Unicode code point of first character",
16            returns: "Integer code point of the first character",
17            examples: vec![
18                "SELECT ASCII('A')  -- Returns 65",
19                "SELECT ASCII('ABC')  -- Returns 65 (first char only)",
20                "SELECT ASCII('€')  -- Returns 8364 (Euro symbol)",
21            ],
22        }
23    }
24
25    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
26        if args.len() != 1 {
27            return Err(anyhow::anyhow!(
28                "ASCII expects 1 argument, got {}",
29                args.len()
30            ));
31        }
32
33        let text = args[0].to_string();
34        if text.is_empty() {
35            return Ok(DataValue::Null);
36        }
37
38        // Get the first character and its code point
39        if let Some(first_char) = text.chars().next() {
40            Ok(DataValue::Integer(first_char as i64))
41        } else {
42            Ok(DataValue::Null)
43        }
44    }
45}
46
47/// ORD() - Alias for ASCII (common in some SQL dialects)
48pub struct OrdFunction;
49
50impl SqlFunction for OrdFunction {
51    fn signature(&self) -> FunctionSignature {
52        FunctionSignature {
53            name: "ORD",
54            category: FunctionCategory::String,
55            arg_count: ArgCount::Fixed(1),
56            description: "Get Unicode code point of first character (alias for ASCII)",
57            returns: "Integer code point of the first character",
58            examples: vec![
59                "SELECT ORD('A')  -- Returns 65",
60                "SELECT ORD('€')  -- Returns 8364",
61            ],
62        }
63    }
64
65    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
66        AsciiFunction.evaluate(args)
67    }
68}
69
70/// CHAR() - Alias for CHR() for SQL compatibility
71pub struct CharFunction;
72
73impl SqlFunction for CharFunction {
74    fn signature(&self) -> FunctionSignature {
75        FunctionSignature {
76            name: "CHAR",
77            category: FunctionCategory::String,
78            arg_count: ArgCount::Fixed(1),
79            description: "Convert ASCII/Unicode code to character (alias for CHR)",
80            returns: "Character corresponding to the code",
81            examples: vec![
82                "SELECT CHAR(65)  -- Returns 'A'",
83                "SELECT CHAR(8364)  -- Returns '€' (Euro symbol)",
84            ],
85        }
86    }
87
88    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
89        if args.len() != 1 {
90            return Err(anyhow::anyhow!(
91                "CHAR expects 1 argument, got {}",
92                args.len()
93            ));
94        }
95
96        let code = match &args[0] {
97            DataValue::Integer(i) => *i as u32,
98            DataValue::Float(f) => f.trunc() as u32,
99            DataValue::String(s) => s
100                .parse::<u32>()
101                .map_err(|_| anyhow::anyhow!("CHAR expects a number, got '{}'", s))?,
102            DataValue::Null => return Ok(DataValue::Null),
103            _ => return Err(anyhow::anyhow!("CHAR expects a number as argument")),
104        };
105
106        // Convert Unicode code point to character
107        match char::from_u32(code) {
108            Some(c) => Ok(DataValue::String(c.to_string())),
109            None => Err(anyhow::anyhow!("Invalid Unicode code point: {}", code)),
110        }
111    }
112}
113
114/// TO_INT() - Convert string to integer
115pub struct ToIntFunction;
116
117impl SqlFunction for ToIntFunction {
118    fn signature(&self) -> FunctionSignature {
119        FunctionSignature {
120            name: "TO_INT",
121            category: FunctionCategory::Conversion,
122            arg_count: ArgCount::Fixed(1),
123            description: "Convert string to integer",
124            returns: "Integer value or NULL if conversion fails",
125            examples: vec![
126                "SELECT TO_INT('123')  -- Returns 123",
127                "SELECT TO_INT('45.67')  -- Returns 45 (truncates)",
128                "SELECT TO_INT('abc')  -- Returns NULL",
129            ],
130        }
131    }
132
133    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
134        if args.len() != 1 {
135            return Err(anyhow::anyhow!(
136                "TO_INT expects 1 argument, got {}",
137                args.len()
138            ));
139        }
140
141        match &args[0] {
142            DataValue::Integer(i) => Ok(DataValue::Integer(*i)),
143            DataValue::Float(f) => Ok(DataValue::Integer(f.trunc() as i64)),
144            DataValue::String(s) => {
145                // Try to parse as float first (handles "123.45")
146                if let Ok(f) = s.parse::<f64>() {
147                    Ok(DataValue::Integer(f.trunc() as i64))
148                } else if let Ok(i) = s.parse::<i64>() {
149                    Ok(DataValue::Integer(i))
150                } else {
151                    Ok(DataValue::Null)
152                }
153            }
154            DataValue::Null => Ok(DataValue::Null),
155            _ => Ok(DataValue::Null),
156        }
157    }
158}
159
160/// TO_DECIMAL() - Convert string to decimal/float
161pub struct ToDecimalFunction;
162
163impl SqlFunction for ToDecimalFunction {
164    fn signature(&self) -> FunctionSignature {
165        FunctionSignature {
166            name: "TO_DECIMAL",
167            category: FunctionCategory::Conversion,
168            arg_count: ArgCount::Fixed(1),
169            description: "Convert string to decimal/float",
170            returns: "Float value or NULL if conversion fails",
171            examples: vec![
172                "SELECT TO_DECIMAL('123.45')  -- Returns 123.45",
173                "SELECT TO_DECIMAL('123')  -- Returns 123.0",
174                "SELECT TO_DECIMAL('abc')  -- Returns NULL",
175            ],
176        }
177    }
178
179    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
180        if args.len() != 1 {
181            return Err(anyhow::anyhow!(
182                "TO_DECIMAL expects 1 argument, got {}",
183                args.len()
184            ));
185        }
186
187        match &args[0] {
188            DataValue::Integer(i) => Ok(DataValue::Float(*i as f64)),
189            DataValue::Float(f) => Ok(DataValue::Float(*f)),
190            DataValue::String(s) => match s.parse::<f64>() {
191                Ok(f) => Ok(DataValue::Float(f)),
192                Err(_) => Ok(DataValue::Null),
193            },
194            DataValue::Null => Ok(DataValue::Null),
195            _ => Ok(DataValue::Null),
196        }
197    }
198}
199
200/// TO_STRING() - Convert any value to string
201pub struct ToStringFunction;
202
203impl SqlFunction for ToStringFunction {
204    fn signature(&self) -> FunctionSignature {
205        FunctionSignature {
206            name: "TO_STRING",
207            category: FunctionCategory::Conversion,
208            arg_count: ArgCount::Fixed(1),
209            description: "Convert any value to string representation",
210            returns: "String representation of the value",
211            examples: vec![
212                "SELECT TO_STRING(123)  -- Returns '123'",
213                "SELECT TO_STRING(45.67)  -- Returns '45.67'",
214                "SELECT TO_STRING(NULL)  -- Returns NULL",
215            ],
216        }
217    }
218
219    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
220        if args.len() != 1 {
221            return Err(anyhow::anyhow!(
222                "TO_STRING expects 1 argument, got {}",
223                args.len()
224            ));
225        }
226
227        match &args[0] {
228            DataValue::Null => Ok(DataValue::Null),
229            value => Ok(DataValue::String(value.to_string())),
230        }
231    }
232}
233
234/// ENCODE() - Encode string to base64 or hex
235pub struct EncodeFunction;
236
237impl SqlFunction for EncodeFunction {
238    fn signature(&self) -> FunctionSignature {
239        FunctionSignature {
240            name: "ENCODE",
241            category: FunctionCategory::String,
242            arg_count: ArgCount::Fixed(2),
243            description: "Encode string to base64 or hex",
244            returns: "Encoded string",
245            examples: vec![
246                "SELECT ENCODE('Hello', 'base64')  -- Returns 'SGVsbG8='",
247                "SELECT ENCODE('Hello', 'hex')  -- Returns '48656c6c6f'",
248            ],
249        }
250    }
251
252    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
253        if args.len() != 2 {
254            return Err(anyhow::anyhow!(
255                "ENCODE expects 2 arguments, got {}",
256                args.len()
257            ));
258        }
259
260        let text = args[0].to_string();
261        let encoding = args[1].to_string().to_lowercase();
262
263        match encoding.as_str() {
264            "base64" => {
265                let encoded = BASE64.encode(text.as_bytes());
266                Ok(DataValue::String(encoded))
267            }
268            "hex" => {
269                let hex = hex::encode(text.as_bytes());
270                Ok(DataValue::String(hex))
271            }
272            _ => Err(anyhow::anyhow!(
273                "Unsupported encoding '{}'. Use 'base64' or 'hex'",
274                encoding
275            )),
276        }
277    }
278}
279
280/// DECODE() - Decode base64 or hex string
281pub struct DecodeFunction;
282
283impl SqlFunction for DecodeFunction {
284    fn signature(&self) -> FunctionSignature {
285        FunctionSignature {
286            name: "DECODE",
287            category: FunctionCategory::String,
288            arg_count: ArgCount::Fixed(2),
289            description: "Decode base64 or hex encoded string",
290            returns: "Decoded string",
291            examples: vec![
292                "SELECT DECODE('SGVsbG8=', 'base64')  -- Returns 'Hello'",
293                "SELECT DECODE('48656c6c6f', 'hex')  -- Returns 'Hello'",
294            ],
295        }
296    }
297
298    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
299        if args.len() != 2 {
300            return Err(anyhow::anyhow!(
301                "DECODE expects 2 arguments, got {}",
302                args.len()
303            ));
304        }
305
306        let encoded = args[0].to_string();
307        let encoding = args[1].to_string().to_lowercase();
308
309        match encoding.as_str() {
310            "base64" => match BASE64.decode(encoded.as_bytes()) {
311                Ok(bytes) => match String::from_utf8(bytes) {
312                    Ok(decoded) => Ok(DataValue::String(decoded)),
313                    Err(_) => Err(anyhow::anyhow!("Invalid UTF-8 in decoded data")),
314                },
315                Err(e) => Err(anyhow::anyhow!("Base64 decode error: {}", e)),
316            },
317            "hex" => match hex::decode(encoded.as_bytes()) {
318                Ok(bytes) => match String::from_utf8(bytes) {
319                    Ok(decoded) => Ok(DataValue::String(decoded)),
320                    Err(_) => Err(anyhow::anyhow!("Invalid UTF-8 in decoded data")),
321                },
322                Err(e) => Err(anyhow::anyhow!("Hex decode error: {}", e)),
323            },
324            _ => Err(anyhow::anyhow!(
325                "Unsupported encoding '{}'. Use 'base64' or 'hex'",
326                encoding
327            )),
328        }
329    }
330}
331
332/// UNICODE() - Get all Unicode code points in a string
333pub struct UnicodeFunction;
334
335impl SqlFunction for UnicodeFunction {
336    fn signature(&self) -> FunctionSignature {
337        FunctionSignature {
338            name: "UNICODE",
339            category: FunctionCategory::String,
340            arg_count: ArgCount::Fixed(1),
341            description: "Get Unicode code points of all characters as comma-separated list",
342            returns: "Comma-separated list of Unicode code points",
343            examples: vec![
344                "SELECT UNICODE('ABC')  -- Returns '65,66,67'",
345                "SELECT UNICODE('€$')  -- Returns '8364,36'",
346                "SELECT UNICODE('Hello')  -- Returns '72,101,108,108,111'",
347            ],
348        }
349    }
350
351    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
352        if args.len() != 1 {
353            return Err(anyhow::anyhow!(
354                "UNICODE expects 1 argument, got {}",
355                args.len()
356            ));
357        }
358
359        let text = args[0].to_string();
360        if text.is_empty() {
361            return Ok(DataValue::Null);
362        }
363
364        let codes: Vec<String> = text.chars().map(|c| (c as u32).to_string()).collect();
365        Ok(DataValue::String(codes.join(",")))
366    }
367}
sql_cli/sql/functions/utility.rs

sql_cli/sql/functions/
utility.rs
