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
118
119
120
121
122
123
124
125

use super::support::*;
use super::targets_and_projections::NO_ALIAS_META;

// ---------------------------------------------------------------------------
// DISTINCT rendering
// ---------------------------------------------------------------------------

#[test]
fn distinct_lists_render_the_distinct_keyword() {
    let rendered = PgRenderer
        .render_list(&plan::<DistinctUser>("", QueryTarget::TypedPage))
        .unwrap();
    assert!(rendered.sql.starts_with("SELECT DISTINCT u.id AS \"id\""));
}

#[test]
fn distinct_clusters_project_the_summarized_field_inside_the_subquery() {
    let id = FieldRef::<DistinctUser, i64, ScalarKind>::new(FieldId(0));
    let options = SearchOptions::<DistinctUser>::builder()
        .sum(id)
        .build()
        .unwrap();
    let rendered = PgRenderer
        .render_cluster(
            &plan::<DistinctUser>("", QueryTarget::Count),
            Some(&options),
        )
        .unwrap();
    assert_eq!(
        rendered.sql.as_ref(),
        "SELECT count(*) AS \"sq_count\", sum(sq_0) AS \"sq_0\" \
         FROM (SELECT DISTINCT u.id, u.name, u.age, u.id AS \"sq_0\" FROM app_user u \
         WHERE u.deleted = false) sq_cluster"
    );
}

#[test]
fn distinct_summaries_require_the_field_in_the_final_projection() {
    let name = FieldRef::<DistinctUser, String, ScalarKind>::new(FieldId(1));
    let options = SearchOptions::<DistinctUser>::builder()
        .min(name)
        .build()
        .unwrap();
    let mut broken = plan::<DistinctUser>("", QueryTarget::Count);
    broken.projection = vec![FieldId(0)];
    assert_eq!(
        PgRenderer
            .render_cluster(&broken, Some(&options))
            .unwrap_err(),
        RenderError::InvalidCondition(
            "DISTINCT summaries require the summarized field in the final projection".to_owned()
        )
    );
}

#[test]
fn sum_over_distinct_wraps_the_distinct_projection_in_a_subquery() {
    let rendered = PgRenderer
        .render_sum(&plan::<DistinctUser>("", QueryTarget::Sum), FieldId(0))
        .unwrap();
    assert_eq!(
        rendered.sql.as_ref(),
        "SELECT sum(\"id\") AS \"sq_sum\" FROM (SELECT DISTINCT u.id AS \"id\", u.name AS \"name\", \
         u.age AS \"age\" FROM app_user u WHERE u.deleted = false) sq_sum_source"
    );
}

#[test]
fn sum_over_distinct_requires_the_field_in_the_final_projection() {
    let mut broken = plan::<DistinctUser>("", QueryTarget::Sum);
    broken.projection = vec![FieldId(1)];
    assert_eq!(
        PgRenderer.render_sum(&broken, FieldId(0)).unwrap_err(),
        RenderError::InvalidCondition(
            "SUM over DISTINCT entities requires the field in the final projection".to_owned()
        )
    );
}

static NO_ALIAS_DISTINCT_META: EntityMeta = EntityMeta {
    distinct: true,
    schema_fingerprint: 36,
    ..NO_ALIAS_META
};
struct NoAliasDistinct;
impl SearchEntity for NoAliasDistinct {
    const META: &'static EntityMeta = &NO_ALIAS_DISTINCT_META;
}

#[test]
fn sum_over_distinct_requires_a_result_alias() {
    assert_eq!(
        PgRenderer
            .render_sum(&plan::<NoAliasDistinct>("", QueryTarget::Sum), FieldId(0))
            .unwrap_err(),
        RenderError::MissingAlias("id".to_owned())
    );
}

#[test]
fn sum_over_grouped_and_plain_entities_renders_each_shape() {
    let plain = PgRenderer
        .render_sum(&plan::<User>("", QueryTarget::Sum), FieldId(2))
        .unwrap();
    assert_eq!(
        plain.sql.as_ref(),
        "SELECT sum(u.age) AS \"sq_sum\" FROM app_user u WHERE u.deleted = false"
    );

    let grouped = PgRenderer
        .render_sum(&plan::<Score>("", QueryTarget::Sum), FieldId(1))
        .unwrap();
    assert!(grouped.sql.contains(
        "SELECT sum(\"sq_value\") AS \"sq_sum\" FROM (SELECT sum(s.score) AS \"sq_value\""
    ));
    assert!(grouped.sql.contains("GROUP BY s.course_id"));
    assert!(grouped.sql.contains("HAVING count(*) > 0"));

    assert_eq!(
        PgRenderer
            .render_sum(&plan::<User>("", QueryTarget::Sum), FieldId(99))
            .unwrap_err(),
        RenderError::UnknownField(99)
    );
}