spg-engine 7.38.17

Execution engine for SPG: glues spg-sql parsing to spg-storage. Foreign keys, joins, vectors, cold tier.
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

//! PG's ordinary spelling for a full-text index puts an expression in
//! the key. SPG refused the DDL, so a schema using it did not load.
use spg_engine::{Engine, QueryResult};

fn n(e: &mut Engine, q: &str) -> String {
    match e.execute(q) {
        Ok(QueryResult::Rows { rows, .. }) => format!("{} row(s)", rows.len()),
        Ok(_) => "cmd".into(),
        Err(err) => format!(
            "ERR {}",
            format!("{err:?}").chars().take(70).collect::<String>()
        ),
    }
}

fn main() {
    let cases: [(&str, &str, &str); 4] = [
        (
            "concatenation",
            "CREATE INDEX g ON d USING gin (to_tsvector('english', title || ' ' || body))",
            "SELECT id FROM d WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english','fox')",
        ),
        (
            "coalesce",
            "CREATE INDEX g ON d USING gin (to_tsvector('english', coalesce(title,'')))",
            "SELECT id FROM d WHERE to_tsvector('english', coalesce(title,'')) @@ to_tsquery('english','quick')",
        ),
        (
            "jsonb path",
            "CREATE INDEX g ON d USING gin ((meta -> 'tags'))",
            "SELECT id FROM d WHERE meta -> 'tags' @> '[\"a\"]'",
        ),
        (
            "plain column (was already ok)",
            "CREATE INDEX g ON d USING gin (to_tsvector('english', body))",
            "SELECT id FROM d WHERE to_tsvector('english', body) @@ to_tsquery('english','lazy')",
        ),
    ];
    for (label, ddl, q) in cases {
        let mut e = Engine::new();
        e.execute("CREATE TABLE d (id INT, title TEXT, body TEXT, meta JSONB)")
            .unwrap();
        e.execute(
            "INSERT INTO d VALUES (1,'quick fox','jumps over the lazy dog','{\"tags\":[\"a\"]}')",
        )
        .unwrap();
        e.execute("INSERT INTO d VALUES (2,'slow turtle','sits','{\"tags\":[\"b\"]}')")
            .unwrap();
        let ddl_r = match e.execute(ddl) {
            Ok(_) => "built".to_string(),
            Err(err) => format!(
                "ERR {}",
                format!("{err:?}").chars().take(70).collect::<String>()
            ),
        };
        // A row inserted after the index was built must be findable too.
        let post = e
            .execute("INSERT INTO d VALUES (3,'quick hare','naps','{\"tags\":[\"a\"]}')")
            .map_or_else(|e| format!("{e:?}"), |_| "ok".into());
        println!(
            "{label:<30} {ddl_r:<12} insert {post:<4} query {}",
            n(&mut e, q)
        );
    }
}