sql-cli 1.73.1

SQL query tool for CSV/JSON with both interactive TUI and non-interactive CLI modes - perfect for exploration and automation
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262

use sql_cli::data::recursive_where_evaluator::RecursiveWhereEvaluator;
use sql_cli::datatable::{DataColumn, DataRow, DataTable, DataValue};
use sql_cli::recursive_parser::{Parser, WhereClause};

fn create_test_table() -> DataTable {
    let mut table = DataTable::new("test");

    // Add columns
    table.add_column(DataColumn::new("id"));
    table.add_column(DataColumn::new("name"));
    table.add_column(DataColumn::new("description"));
    table.add_column(DataColumn::new("price"));
    table.add_column(DataColumn::new("category"));

    // Add test data
    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(1),
            DataValue::String("Widget".to_string()),
            DataValue::String("A useful widget for various tasks".to_string()),
            DataValue::Float(19.99),
            DataValue::String("Tools".to_string()),
        ]))
        .unwrap();

    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(2),
            DataValue::String("Gadget".to_string()),
            DataValue::String("An innovative gadget device".to_string()),
            DataValue::Float(29.99),
            DataValue::String("Electronics".to_string()),
        ]))
        .unwrap();

    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(3),
            DataValue::String("Gizmo".to_string()),
            DataValue::String("A clever gizmo for entertainment".to_string()),
            DataValue::Float(9.99),
            DataValue::String("Toys".to_string()),
        ]))
        .unwrap();

    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(4),
            DataValue::String("Device".to_string()),
            DataValue::String("Professional device for work".to_string()),
            DataValue::Float(99.99),
            DataValue::String("Electronics".to_string()),
        ]))
        .unwrap();

    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(5),
            DataValue::String("Tool".to_string()),
            DataValue::String("Essential tool for projects".to_string()),
            DataValue::Float(49.99),
            DataValue::String("Tools".to_string()),
        ]))
        .unwrap();

    table
}

fn extract_where_clause(query: &str) -> WhereClause {
    let mut parser = Parser::new(query);
    let statement = parser.parse().expect("Failed to parse query");

    statement.where_clause.expect("Expected WHERE clause")
}

#[test]
fn test_contains_method() {
    let table = create_test_table();
    let mut evaluator = RecursiveWhereEvaluator::new(&table);

    // Test case-insensitive contains
    let where_clause = extract_where_clause("SELECT * FROM test WHERE name.Contains('get')");

    // Should match "Widget" (true - contains 'get'), "Gadget" (true - contains 'get'), "Gizmo" (false), "Device" (false), "Tool" (false)
    assert!(evaluator.evaluate(&where_clause, 0).unwrap()); // Widget - contains 'get' at the end
    assert!(evaluator.evaluate(&where_clause, 1).unwrap()); // Gadget - contains 'get' in the middle
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // Gizmo - does not contain 'get'
    assert!(!evaluator.evaluate(&where_clause, 3).unwrap()); // Device - does not contain 'get'
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // Tool - does not contain 'get'
}

#[test]
fn test_startswith_method() {
    let table = create_test_table();
    let mut evaluator = RecursiveWhereEvaluator::new(&table);

    let where_clause = extract_where_clause("SELECT * FROM test WHERE name.StartsWith('G')");

    // Should match "Widget" (false), "Gadget" (true), "Gizmo" (true), "Device" (false), "Tool" (false)
    assert!(!evaluator.evaluate(&where_clause, 0).unwrap()); // Widget
    assert!(evaluator.evaluate(&where_clause, 1).unwrap()); // Gadget
    assert!(evaluator.evaluate(&where_clause, 2).unwrap()); // Gizmo
    assert!(!evaluator.evaluate(&where_clause, 3).unwrap()); // Device
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // Tool
}

#[test]
fn test_endswith_method() {
    let table = create_test_table();
    let mut evaluator = RecursiveWhereEvaluator::new(&table);

    let where_clause = extract_where_clause("SELECT * FROM test WHERE name.EndsWith('et')");

    // Should match "Widget" (true), "Gadget" (true), "Gizmo" (false), "Device" (false), "Tool" (false)
    assert!(evaluator.evaluate(&where_clause, 0).unwrap()); // Widget
    assert!(evaluator.evaluate(&where_clause, 1).unwrap()); // Gadget
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // Gizmo
    assert!(!evaluator.evaluate(&where_clause, 3).unwrap()); // Device
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // Tool
}

#[test]
fn test_length_method() {
    let table = create_test_table();
    let mut evaluator = RecursiveWhereEvaluator::new(&table);

    let where_clause = extract_where_clause("SELECT * FROM test WHERE name.Length() > 5");

    // Should match "Widget" (6, true), "Gadget" (6, true), "Gizmo" (5, false), "Device" (6, true), "Tool" (4, false)
    assert!(evaluator.evaluate(&where_clause, 0).unwrap()); // Widget (6 chars)
    assert!(evaluator.evaluate(&where_clause, 1).unwrap()); // Gadget (6 chars)
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // Gizmo (5 chars)
    assert!(evaluator.evaluate(&where_clause, 3).unwrap()); // Device (6 chars)
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // Tool (4 chars)
}

#[test]
fn test_indexof_method_found() {
    let table = create_test_table();
    let mut evaluator = RecursiveWhereEvaluator::new(&table);

    // Test IndexOf when substring is found
    let where_clause =
        extract_where_clause("SELECT * FROM test WHERE description.IndexOf('device') > 0");

    // "A useful widget for various tasks" - no "device" (-1)
    // "An innovative gadget device" - "device" at position 21
    // "A clever gizmo for entertainment" - no "device" (-1)
    // "Professional device for work" - "device" at position 13
    // "Essential tool for projects" - no "device" (-1)

    assert!(!evaluator.evaluate(&where_clause, 0).unwrap()); // Widget: -1 > 0 = false
    assert!(evaluator.evaluate(&where_clause, 1).unwrap()); // Gadget: 21 > 0 = true
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // Gizmo: -1 > 0 = false
    assert!(evaluator.evaluate(&where_clause, 3).unwrap()); // Device: 13 > 0 = true
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // Tool: -1 > 0 = false
}

#[test]
fn test_indexof_method_not_found() {
    let table = create_test_table();
    let mut evaluator = RecursiveWhereEvaluator::new(&table);

    // Test IndexOf when substring is not found (returns -1)
    let where_clause = extract_where_clause("SELECT * FROM test WHERE name.IndexOf('xyz') = -1");

    // None of the names contain "xyz", so all should return -1
    assert!(evaluator.evaluate(&where_clause, 0).unwrap()); // Widget: -1 = -1
    assert!(evaluator.evaluate(&where_clause, 1).unwrap()); // Gadget: -1 = -1
    assert!(evaluator.evaluate(&where_clause, 2).unwrap()); // Gizmo: -1 = -1
    assert!(evaluator.evaluate(&where_clause, 3).unwrap()); // Device: -1 = -1
    assert!(evaluator.evaluate(&where_clause, 4).unwrap()); // Tool: -1 = -1
}

#[test]
fn test_indexof_at_beginning() {
    let table = create_test_table();
    let mut evaluator = RecursiveWhereEvaluator::new(&table);

    // Test IndexOf when substring is at the beginning (returns 0)
    let where_clause = extract_where_clause("SELECT * FROM test WHERE name.IndexOf('Wid') = 0");

    assert!(evaluator.evaluate(&where_clause, 0).unwrap()); // Widget starts with "Wid"
    assert!(!evaluator.evaluate(&where_clause, 1).unwrap()); // Gadget doesn't
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // Gizmo doesn't
    assert!(!evaluator.evaluate(&where_clause, 3).unwrap()); // Device doesn't
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // Tool doesn't
}

#[test]
fn test_numeric_column_with_string_methods() {
    let table = create_test_table();
    let mut evaluator = RecursiveWhereEvaluator::new(&table);

    // Test string methods on numeric columns (should convert to string)
    let where_clause = extract_where_clause("SELECT * FROM test WHERE price.Contains('9.99')");

    // Prices: 19.99, 29.99, 9.99, 99.99, 49.99
    assert!(evaluator.evaluate(&where_clause, 0).unwrap()); // 19.99 contains "9.99"
    assert!(evaluator.evaluate(&where_clause, 1).unwrap()); // 29.99 contains "9.99"
    assert!(evaluator.evaluate(&where_clause, 2).unwrap()); // 9.99 contains "9.99"
    assert!(evaluator.evaluate(&where_clause, 3).unwrap()); // 99.99 contains "9.99"
    assert!(evaluator.evaluate(&where_clause, 4).unwrap()); // 49.99 contains "9.99"
}

#[test]
fn test_complex_expressions_with_methods() {
    let table = create_test_table();
    let mut evaluator = RecursiveWhereEvaluator::new(&table);

    // Test complex expression with multiple conditions
    let where_clause = extract_where_clause(
        "SELECT * FROM test WHERE name.Length() > 4 AND category.Contains('tron')",
    );

    // name.Length() > 4: Widget(6), Gadget(6), Gizmo(5), Device(6), Tool(4)
    // category.Contains('tron'): Tools(false), Electronics(true), Toys(false), Electronics(true), Tools(false)
    // Combined: false, true, false, true, false
    assert!(!evaluator.evaluate(&where_clause, 0).unwrap()); // Widget & Tools
    assert!(evaluator.evaluate(&where_clause, 1).unwrap()); // Gadget & Electronics
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // Gizmo & Toys
    assert!(evaluator.evaluate(&where_clause, 3).unwrap()); // Device & Electronics
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // Tool & Tools
}

#[test]
fn test_indexof_with_greater_than() {
    let table = create_test_table();
    let mut evaluator = RecursiveWhereEvaluator::new(&table);

    // Test the specific case from the user's debug output
    let where_clause =
        extract_where_clause("SELECT * FROM test WHERE description.IndexOf('ful') > 2");

    // "A useful widget for various tasks" - "ful" at position 4 (case-insensitive)
    // "An innovative gadget device" - no "ful" (-1)
    // "A clever gizmo for entertainment" - no "ful" (-1)
    // "Professional device for work" - no "ful" (-1)
    // "Essential tool for projects" - no "ful" (-1)

    assert!(evaluator.evaluate(&where_clause, 0).unwrap()); // 4 > 2 = true
    assert!(!evaluator.evaluate(&where_clause, 1).unwrap()); // -1 > 2 = false
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // -1 > 2 = false
    assert!(!evaluator.evaluate(&where_clause, 3).unwrap()); // -1 > 2 = false
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // -1 > 2 = false
}

#[test]
fn test_case_sensitivity() {
    let table = create_test_table();
    let mut evaluator = RecursiveWhereEvaluator::new(&table);

    // Test that methods are case-insensitive for search strings
    let where_clause1 = extract_where_clause("SELECT * FROM test WHERE name.Contains('WIDGET')");
    let where_clause2 = extract_where_clause("SELECT * FROM test WHERE name.Contains('widget')");
    let where_clause3 = extract_where_clause("SELECT * FROM test WHERE name.Contains('WiDgEt')");

    // All should match the same row (Widget)
    assert!(evaluator.evaluate(&where_clause1, 0).unwrap());
    assert!(evaluator.evaluate(&where_clause2, 0).unwrap());
    assert!(evaluator.evaluate(&where_clause3, 0).unwrap());
}