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

use serde_json::json;
use sql_cli::csv_datasource::CsvApiClient;

fn test_where_with_order_by() {
    // Create test data
    let test_data = vec![
        json!({"Index": 1, "Name": "Product A", "Availability": "in_stock", "Price": 20.0}),
        json!({"Index": 2, "Name": "Product B", "Availability": "pre_order", "Price": 15.0}),
        json!({"Index": 3, "Name": "Product C", "Availability": "out_of_stock", "Price": 25.0}),
        json!({"Index": 4, "Name": "Product D", "Availability": "in_stock", "Price": 10.0}),
        json!({"Index": 5, "Name": "Product E", "Availability": "out_of_stock", "Price": 30.0}),
    ];

    // Test 1: WHERE clause alone
    let mut client = CsvApiClient::new();
    client
        .load_from_json(test_data.clone(), "products")
        .unwrap();

    let result = client
        .query_csv(r#"SELECT * FROM products WHERE Availability NOT IN ("pre_order")"#)
        .unwrap();
    println!("Test 1 - WHERE only: {} rows", result.count);
    assert_eq!(result.count, 4); // Should exclude the pre_order item

    // Test 2: ORDER BY clause alone
    let mut client2 = CsvApiClient::new();
    client2
        .load_from_json(test_data.clone(), "products")
        .unwrap();

    let result2 = client2
        .query_csv(r#"SELECT * FROM products ORDER BY Availability"#)
        .unwrap();
    println!("Test 2 - ORDER BY only: {} rows", result2.count);

    // Check ordering
    let first_availability = result2.data[0]["Availability"].as_str().unwrap();
    let last_availability = result2.data[4]["Availability"].as_str().unwrap();
    println!(
        "  First: {}, Last: {}",
        first_availability, last_availability
    );
    assert!(first_availability <= last_availability);

    // Test 3: WHERE + ORDER BY together (the problematic case)
    let mut client3 = CsvApiClient::new();
    client3
        .load_from_json(test_data.clone(), "products")
        .unwrap();

    let query =
        r#"SELECT * FROM products WHERE Availability NOT IN ("pre_order") ORDER BY Availability"#;
    println!("Test 3 Query: {}", query);
    let result3 = client3.query_csv(query).unwrap();
    println!("Test 3 - WHERE + ORDER BY: {} rows", result3.count);
    assert_eq!(result3.count, 4); // Should still exclude the pre_order item

    // Check that results are ordered
    let availabilities: Vec<String> = result3
        .data
        .iter()
        .map(|row| row["Availability"].as_str().unwrap().to_string())
        .collect();

    println!("  Availabilities in order: {:?}", availabilities);

    // Verify ordering
    for i in 1..availabilities.len() {
        assert!(
            availabilities[i - 1] <= availabilities[i],
            "Results not ordered: {} > {}",
            availabilities[i - 1],
            availabilities[i]
        );
    }

    // Test 4: ORDER BY with price to verify numeric sorting works with WHERE
    let mut client4 = CsvApiClient::new();
    client4
        .load_from_json(test_data.clone(), "products")
        .unwrap();

    let result4 = client4
        .query_csv(
            r#"SELECT * FROM products WHERE Availability NOT IN ("pre_order") ORDER BY Price"#,
        )
        .unwrap();
    let prices: Vec<f64> = result4
        .data
        .iter()
        .map(|row| row["Price"].as_f64().unwrap())
        .collect();

    println!("Test 4 - WHERE + ORDER BY Price: {:?}", prices);

    // Verify numeric ordering
    for i in 1..prices.len() {
        assert!(
            prices[i - 1] <= prices[i],
            "Prices not ordered: {} > {}",
            prices[i - 1],
            prices[i]
        );
    }
}

fn main() {
    test_where_with_order_by();
    println!("All tests passed!");
}