sift-query-sqlx 0.1.0

PostgreSQL renderer and sqlx executor for SiftQuery
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

use super::support::*;

// ---------------------------------------------------------------------------
// Grouped rendering
// ---------------------------------------------------------------------------

#[test]
fn grouped_lists_render_group_by_and_having() {
    let rendered = PgRenderer
        .render_list(&plan::<Score>(
            "courseId=1&total=10&total-op=ge",
            QueryTarget::TypedPage,
        ))
        .unwrap();
    assert!(rendered.sql.contains("GROUP BY s.course_id"));
    assert!(
        rendered
            .sql
            .contains("HAVING count(*) > 0 AND sum(s.score) >= $2")
    );
}

#[test]
fn grouped_counts_use_a_subquery_with_the_fixed_having_clause() {
    let rendered = PgRenderer
        .render_count(&plan::<Score>("", QueryTarget::Count))
        .unwrap();
    assert_eq!(
        rendered.sql.as_ref(),
        "SELECT count(*) AS \"sq_count\" FROM (SELECT 1 FROM student_score s \
         GROUP BY s.course_id HAVING count(*) > 0) sq_cluster"
    );
}

#[test]
fn grouped_clusters_with_summaries_project_each_requested_field() {
    let age = FieldRef::<Score, i64, ScalarKind>::new(FieldId(1));
    let options = SearchOptions::<Score>::builder()
        .sum(age)
        .max(age)
        .build()
        .unwrap();
    let rendered = PgRenderer
        .render_cluster(&plan::<Score>("", QueryTarget::Count), Some(&options))
        .unwrap();
    assert_eq!(
        rendered.sql.as_ref(),
        "SELECT count(*) AS \"sq_count\", sum(sq_0) AS \"sq_0\", max(sq_1) AS \"sq_1\" \
         FROM (SELECT sum(s.score) AS \"sq_0\", sum(s.score) AS \"sq_1\" FROM student_score s \
         GROUP BY s.course_id HAVING count(*) > 0) sq_cluster"
    );
}

#[test]
fn clustered_summaries_without_a_grouping_select_count_only() {
    let rendered = PgRenderer
        .render_cluster(&plan::<User>("", QueryTarget::Count), None)
        .unwrap();
    assert_eq!(
        rendered.sql.as_ref(),
        "SELECT count(*) AS \"sq_count\" FROM app_user u WHERE u.deleted = false"
    );
}

#[test]
fn unsupported_summary_types_are_rejected_before_rendering() {
    // `sum` over text has no PostgreSQL aggregate.
    let title = FieldRef::<Typed, String, ScalarKind>::new(FieldId(5));
    let options = SearchOptions::<Typed>::builder()
        .sum(title)
        .build()
        .unwrap();
    assert_eq!(
        PgRenderer
            .render_cluster(&plan::<Typed>("", QueryTarget::Count), Some(&options))
            .unwrap_err(),
        RenderError::InvalidCondition(
            "summary field type does not support the PostgreSQL aggregate".to_owned()
        )
    );
}

#[test]
fn every_summary_function_renders_its_own_aggregate() {
    let custom =
        CustomAggregate::new("spread", "max({field}) - min({field})", ValueType::I32).unwrap();
    let age = FieldRef::<User, i32, ScalarKind>::new(FieldId(2));
    let options = SearchOptions::<User>::builder()
        .sum(age)
        .avg(age)
        .min(age)
        .max(age)
        .custom(age, custom)
        .build()
        .unwrap();
    let rendered = PgRenderer
        .render_cluster(&plan::<User>("", QueryTarget::Count), Some(&options))
        .unwrap();
    for (index, fragment) in [
        "sum(u.age)",
        "avg(u.age)",
        "min(u.age)",
        "max(u.age)",
        "max(u.age) - min(u.age)",
    ]
    .iter()
    .enumerate()
    {
        assert!(
            rendered
                .sql
                .contains(&format!("{fragment} AS \"sq_{index}\"")),
            "missing {fragment} at {index}: {}",
            rendered.sql
        );
    }
}