graphitesql 0.0.14

A pure, safe, no_std Rust re-implementation of SQLite, compatible with the SQLite 3 file format.
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

//! RANGE window frames with numeric value offsets (`RANGE n PRECEDING/FOLLOWING`),
//! which bound the frame by the ORDER BY value, checked against sqlite3.

#![cfg(feature = "std")]

use graphitesql::{Connection, Value};
use std::process::Command;

fn render(result: &graphitesql::QueryResult) -> String {
    result
        .rows
        .iter()
        .map(|row| {
            row.iter()
                .map(|v| match v {
                    Value::Null => String::new(),
                    Value::Integer(i) => i.to_string(),
                    Value::Text(s) => s.clone(),
                    Value::Real(r) => graphitesql::exec::eval::format_real(*r),
                    Value::Blob(b) => b.iter().map(|x| format!("{x:02x}")).collect(),
                })
                .collect::<Vec<_>>()
                .join("|")
        })
        .collect::<Vec<_>>()
        .join("\n")
}

#[test]
fn range_value_frames_match_sqlite3() {
    if Command::new("sqlite3").arg("--version").output().is_err() {
        eprintln!("sqlite3 not found; skipping");
        return;
    }
    let path = std::env::temp_dir().join(format!("gsql-wr-{}.db", std::process::id()));
    let path = path.to_string_lossy().into_owned();
    let _ = std::fs::remove_file(&path);
    let setup = "CREATE TABLE t(id INTEGER PRIMARY KEY, g INT, v INT);\
        INSERT INTO t(g,v) VALUES (1,10),(1,20),(1,20),(1,30),(2,5),(2,15),(2,100);";
    {
        let o = Command::new("sqlite3")
            .arg(&path)
            .arg(setup)
            .output()
            .unwrap();
        assert!(o.status.success());
    }
    let mut g = Connection::open_memory().unwrap();
    for s in setup.split(';') {
        if !s.trim().is_empty() {
            g.execute(s).unwrap();
        }
    }
    let q = |frame: &str| format!("SELECT id, sum(v) OVER (ORDER BY v {frame}) FROM t ORDER BY id");
    let mut queries: Vec<String> = vec![
        q("RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING"),
        q("RANGE BETWEEN 10 PRECEDING AND CURRENT ROW"),
        q("RANGE BETWEEN CURRENT ROW AND 10 FOLLOWING"),
        q("RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING"),
        q("RANGE BETWEEN UNBOUNDED PRECEDING AND 5 FOLLOWING"),
        q("RANGE 15 PRECEDING"),
    ];
    queries.push("SELECT id, sum(v) OVER (ORDER BY v DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING) FROM t ORDER BY id".into());
    queries.push("SELECT id, count(*) OVER (PARTITION BY g ORDER BY v RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING) FROM t ORDER BY id".into());
    queries.push("SELECT id, avg(v) OVER (ORDER BY v RANGE BETWEEN 20 PRECEDING AND 20 FOLLOWING) FROM t ORDER BY id".into());
    // EXCLUDE clauses (CURRENT ROW / GROUP / TIES) over aggregates and first_value.
    queries.push("SELECT id, sum(v) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) FROM t ORDER BY id".into());
    queries.push("SELECT id, sum(v) OVER (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP) FROM t ORDER BY id".into());
    queries.push("SELECT id, sum(v) OVER (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES) FROM t ORDER BY id".into());
    queries.push("SELECT id, count(*) OVER (ORDER BY id GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) FROM t ORDER BY id".into());
    queries.push("SELECT id, first_value(v) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) FROM t ORDER BY id".into());
    // FILTER (WHERE …) on window aggregates.
    queries.push(
        "SELECT id, count(*) FILTER (WHERE v > 10) OVER (ORDER BY id) FROM t ORDER BY id".into(),
    );
    queries.push("SELECT id, sum(v) FILTER (WHERE g = 1) OVER () FROM t ORDER BY id".into());
    queries.push(
        "SELECT id, avg(v) FILTER (WHERE v <> 10) OVER (PARTITION BY g) FROM t ORDER BY id".into(),
    );
    for qq in &queries {
        let want = {
            let o = Command::new("sqlite3")
                .arg(&path)
                .arg(format!("{qq};"))
                .output()
                .unwrap();
            String::from_utf8_lossy(&o.stdout).trim_end().to_string()
        };
        let got = render(&g.query(qq).unwrap());
        assert_eq!(got, want, "RANGE frame diverged: {qq}");
    }
    let _ = std::fs::remove_file(&path);
}

/// A RANGE frame with a numeric offset and NULL ORDER BY values: NULLs form their
/// own peer group, so a NULL current row's frame is exactly the NULL peers (the
/// offset does not span into the value groups), and a value row's frame never
/// includes the NULLs (which sort first under ASC, last under DESC). Checked
/// against sqlite3.
#[test]
fn range_frames_with_nulls_match_sqlite3() {
    if Command::new("sqlite3").arg("--version").output().is_err() {
        eprintln!("sqlite3 not found; skipping");
        return;
    }
    let path = std::env::temp_dir().join(format!("gsql-wrn-{}.db", std::process::id()));
    let path = path.to_string_lossy().into_owned();
    let _ = std::fs::remove_file(&path);
    let setup = "CREATE TABLE t(id INTEGER PRIMARY KEY, v INT);\
        INSERT INTO t(v) VALUES (NULL),(1),(2),(NULL),(5);";
    {
        let o = Command::new("sqlite3")
            .arg(&path)
            .arg(setup)
            .output()
            .unwrap();
        assert!(o.status.success());
    }
    let mut g = Connection::open_memory().unwrap();
    for s in setup.split(';') {
        if !s.trim().is_empty() {
            g.execute(s).unwrap();
        }
    }
    let queries = [
        "SELECT id, count(*) OVER (ORDER BY v RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t ORDER BY id",
        "SELECT id, sum(v) OVER (ORDER BY v RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t ORDER BY id",
        "SELECT id, count(*) OVER (ORDER BY v DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t ORDER BY id",
        "SELECT id, sum(v) OVER (ORDER BY v DESC RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) FROM t ORDER BY id",
        "SELECT id, count(*) OVER (ORDER BY v RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) FROM t ORDER BY id",
        "SELECT id, count(*) OVER (ORDER BY v RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM t ORDER BY id",
    ];
    for qq in queries {
        let want = {
            let o = Command::new("sqlite3")
                .arg(&path)
                .arg(format!("{qq};"))
                .output()
                .unwrap();
            String::from_utf8_lossy(&o.stdout).trim_end().to_string()
        };
        let got = render(&g.query(qq).unwrap());
        assert_eq!(got, want, "RANGE+NULL frame diverged: {qq}");
    }
    let _ = std::fs::remove_file(&path);
}