sql-cli 1.73.0

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

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

fn create_test_table() -> DataTable {
    let mut table = DataTable::new("test");
    table.add_column(DataColumn::new("id"));
    table.add_column(DataColumn::new("book"));

    // Add test data
    // Row 0: "derivatives" - no space
    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(1),
            DataValue::String("derivatives".to_string()),
        ]))
        .unwrap();

    // Row 1: "equity trading" - space at position 6
    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(2),
            DataValue::String("equity trading".to_string()),
        ]))
        .unwrap();

    // Row 2: " leading" - space at position 0
    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(3),
            DataValue::String(" leading".to_string()),
        ]))
        .unwrap();

    // Row 3: "trailing " - space at position 8
    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(4),
            DataValue::String("trailing ".to_string()),
        ]))
        .unwrap();

    // Row 4: "FX" - no space
    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(5),
            DataValue::String("FX".to_string()),
        ]))
        .unwrap();

    table
}

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

    // Test IndexOf(' ') = 0 - should only match strings with space at position 0
    let mut parser = Parser::new("SELECT * FROM test WHERE book.IndexOf(' ') = 0");
    let statement = parser.parse().expect("Failed to parse");
    let where_clause = statement.where_clause.expect("Expected WHERE clause");

    assert!(!evaluator.evaluate(&where_clause, 0).unwrap()); // "derivatives" has no space
    assert!(!evaluator.evaluate(&where_clause, 1).unwrap()); // "equity trading" has space at position 6
    assert!(evaluator.evaluate(&where_clause, 2).unwrap()); // " leading" has space at position 0
    assert!(!evaluator.evaluate(&where_clause, 3).unwrap()); // "trailing " has space at position 8
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // "FX" has no space
}

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

    // Test IndexOf(' ') = -1 - should only match strings without spaces
    let mut parser = Parser::new("SELECT * FROM test WHERE book.IndexOf(' ') = -1");
    let statement = parser.parse().expect("Failed to parse");
    let where_clause = statement.where_clause.expect("Expected WHERE clause");

    assert!(evaluator.evaluate(&where_clause, 0).unwrap()); // "derivatives" has no space
    assert!(!evaluator.evaluate(&where_clause, 1).unwrap()); // "equity trading" has space
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // " leading" has space
    assert!(!evaluator.evaluate(&where_clause, 3).unwrap()); // "trailing " has space
    assert!(evaluator.evaluate(&where_clause, 4).unwrap()); // "FX" has no space
}

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

    // Test IndexOf(' ') > 0 - should match strings with space not at beginning
    let mut parser = Parser::new("SELECT * FROM test WHERE book.IndexOf(' ') > 0");
    let statement = parser.parse().expect("Failed to parse");
    let where_clause = statement.where_clause.expect("Expected WHERE clause");

    assert!(!evaluator.evaluate(&where_clause, 0).unwrap()); // "derivatives" has no space (-1 > 0 = false)
    assert!(evaluator.evaluate(&where_clause, 1).unwrap()); // "equity trading" has space at position 6 (6 > 0 = true)
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // " leading" has space at position 0 (0 > 0 = false)
    assert!(evaluator.evaluate(&where_clause, 3).unwrap()); // "trailing " has space at position 8 (8 > 0 = true)
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // "FX" has no space (-1 > 0 = false)
}

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

    // Test IndexOf(' ') >= 0 - should match all strings WITH spaces
    let mut parser = Parser::new("SELECT * FROM test WHERE book.IndexOf(' ') >= 0");
    let statement = parser.parse().expect("Failed to parse");
    let where_clause = statement.where_clause.expect("Expected WHERE clause");

    assert!(!evaluator.evaluate(&where_clause, 0).unwrap()); // "derivatives" has no space (-1 >= 0 = false)
    assert!(evaluator.evaluate(&where_clause, 1).unwrap()); // "equity trading" has space at position 6 (6 >= 0 = true)
    assert!(evaluator.evaluate(&where_clause, 2).unwrap()); // " leading" has space at position 0 (0 >= 0 = true)
    assert!(evaluator.evaluate(&where_clause, 3).unwrap()); // "trailing " has space at position 8 (8 >= 0 = true)
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // "FX" has no space (-1 >= 0 = false)
}

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

    // Test specific positions
    // "equity trading" has space at position 6
    let mut parser = Parser::new("SELECT * FROM test WHERE book.IndexOf(' ') = 6");
    let statement = parser.parse().expect("Failed to parse");
    let where_clause = statement.where_clause.expect("Expected WHERE clause");

    assert!(!evaluator.evaluate(&where_clause, 0).unwrap()); // "derivatives"
    assert!(evaluator.evaluate(&where_clause, 1).unwrap()); // "equity trading" - space at 6
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // " leading" - space at 0
    assert!(!evaluator.evaluate(&where_clause, 3).unwrap()); // "trailing " - space at 8
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // "FX"
}