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

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"));
    table.add_column(DataColumn::new("description"));

    // Add test data with various whitespace patterns
    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(1),
            DataValue::String("  derivatives  ".to_string()), // spaces both sides
            DataValue::String("with spaces".to_string()),
        ]))
        .unwrap();

    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(2),
            DataValue::String("  equity trading".to_string()), // leading spaces only
            DataValue::String("no trim needed".to_string()),
        ]))
        .unwrap();

    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(3),
            DataValue::String("FX  ".to_string()), // trailing spaces only
            DataValue::String("clean".to_string()),
        ]))
        .unwrap();

    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(4),
            DataValue::String("bonds".to_string()), // no spaces
            DataValue::String("already clean".to_string()),
        ]))
        .unwrap();

    table
        .add_row(DataRow::new(vec![
            DataValue::Integer(5),
            DataValue::String("   ".to_string()), // only spaces
            DataValue::String("empty after trim".to_string()),
        ]))
        .unwrap();

    table
}

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

    // Test Trim() = 'derivatives' - should match row 0 after trimming
    let mut parser = Parser::new("SELECT * FROM test WHERE book.Trim() = 'derivatives'");
    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  " -> "derivatives"
    assert!(!evaluator.evaluate(&where_clause, 1).unwrap()); // "  equity trading" -> "equity trading"
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // "FX  " -> "FX"
    assert!(!evaluator.evaluate(&where_clause, 3).unwrap()); // "bonds" -> "bonds"
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // "   " -> ""
}

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

    // Test TrimStart() = 'equity trading' - should match row 1 after trimming start
    let mut parser = Parser::new("SELECT * FROM test WHERE book.TrimStart() = 'equity trading'");
    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  " -> "derivatives  "
    assert!(evaluator.evaluate(&where_clause, 1).unwrap()); // "  equity trading" -> "equity trading"
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // "FX  " -> "FX  "
    assert!(!evaluator.evaluate(&where_clause, 3).unwrap()); // "bonds" -> "bonds"
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // "   " -> ""
}

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

    // Test TrimEnd() = 'FX' - should match row 2 after trimming end
    let mut parser = Parser::new("SELECT * FROM test WHERE book.TrimEnd() = 'FX'");
    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  " -> "  derivatives"
    assert!(!evaluator.evaluate(&where_clause, 1).unwrap()); // "  equity trading" -> "  equity trading"
    assert!(evaluator.evaluate(&where_clause, 2).unwrap()); // "FX  " -> "FX"
    assert!(!evaluator.evaluate(&where_clause, 3).unwrap()); // "bonds" -> "bonds"
    assert!(!evaluator.evaluate(&where_clause, 4).unwrap()); // "   " -> "   " (TrimEnd leaves leading spaces)
}

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

    // Test Trim() = '' - should match row 4 which has only spaces
    let mut parser = Parser::new("SELECT * FROM test WHERE book.Trim() = ''");
    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  " -> "derivatives"
    assert!(!evaluator.evaluate(&where_clause, 1).unwrap()); // "  equity trading" -> "equity trading"
    assert!(!evaluator.evaluate(&where_clause, 2).unwrap()); // "FX  " -> "FX"
    assert!(!evaluator.evaluate(&where_clause, 3).unwrap()); // "bonds" -> "bonds"
    assert!(evaluator.evaluate(&where_clause, 4).unwrap()); // "   " -> ""
}

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

    // Test combining Trim() with StartsWith
    let mut parser = Parser::new("SELECT * FROM test WHERE book.Trim().StartsWith('equity')");
    let statement = parser.parse().expect("Failed to parse");
    let where_clause = statement.where_clause.expect("Expected WHERE clause");

    // This should fail for now as we don't support chained methods yet
    // But it demonstrates the intention for future enhancement

    // For now, we'd need to use: book.Trim() = 'equity trading' AND book.StartsWith('equity')
}

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

    // Verify that Trim only removes leading/trailing spaces, not internal ones
    let mut parser = Parser::new("SELECT * FROM test WHERE book.TrimStart() = 'equity trading'");
    let statement = parser.parse().expect("Failed to parse");
    let where_clause = statement.where_clause.expect("Expected WHERE clause");

    // "  equity trading" should become "equity trading" (with space preserved between words)
    assert!(evaluator.evaluate(&where_clause, 1).unwrap());
}