toolu-orm-query 0.2.0

Type-safe query builders for toolu-orm
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

use toolu_orm_query::select::RelationalSelectBuilder;

#[test]
fn sqlite_single_has_many_generates_correlated_subquery() {
  let builder = RelationalSelectBuilder::new("users", &["id", "name"]).with_many(
    "posts",
    "posts",
    "id",
    "author_id",
    &["id", "title"],
  );
  let sql = builder.to_sql_sqlite();
  assert!(
    sql.contains("json_group_array"),
    "should contain json_group_array, got: {sql}"
  );
  assert!(
    sql.contains("json_array"),
    "should contain json_array, got: {sql}"
  );
  assert!(
    sql.contains(r#""posts"."author_id" = "users"."id""#),
    "should contain join condition, got: {sql}"
  );
  assert!(
    !sql.contains("LATERAL"),
    "SQLite should not use LATERAL, got: {sql}"
  );
}

#[test]
fn sqlite_has_one_generates_subquery_without_aggregation() {
  let builder = RelationalSelectBuilder::new("posts", &["id", "title"]).with_one(
    "author",
    "users",
    "author_id",
    "id",
    &["id", "name"],
  );
  let sql = builder.to_sql_sqlite();
  assert!(
    sql.contains("json_array"),
    "should contain json_array, got: {sql}"
  );
  assert!(
    sql.contains("LIMIT 1"),
    "has-one should contain LIMIT 1, got: {sql}"
  );
}

#[test]
fn sqlite_multiple_relations_generate_multiple_subqueries() {
  let builder = RelationalSelectBuilder::new("users", &["id", "name"])
    .with_many("posts", "posts", "id", "author_id", &["id", "title"])
    .with_many("comments", "comments", "id", "user_id", &["id", "body"]);
  let sql = builder.to_sql_sqlite();
  let subquery_count = sql.matches("json_group_array").count();
  assert_eq!(
    subquery_count, 2,
    "should have 2 subqueries with json_group_array, got {subquery_count} in: {sql}"
  );
}

#[test]
fn sqlite_relation_column_is_aliased() {
  let builder = RelationalSelectBuilder::new("users", &["id"]).with_many(
    "posts",
    "posts",
    "id",
    "author_id",
    &["id", "title"],
  );
  let sql = builder.to_sql_sqlite();
  assert!(
    sql.contains(r#"AS "posts""#),
    "subquery should be aliased as the relation field name, got: {sql}"
  );
}

#[test]
fn sqlite_coalesce_wraps_json_group_array() {
  let builder = RelationalSelectBuilder::new("users", &["id"]).with_many(
    "posts",
    "posts",
    "id",
    "author_id",
    &["id"],
  );
  let sql = builder.to_sql_sqlite();
  assert!(
    sql.contains("coalesce"),
    "should wrap json_group_array with coalesce, got: {sql}"
  );
  assert!(
    sql.contains("json_array()"),
    "coalesce fallback should be empty json_array(), got: {sql}"
  );
}

#[test]
fn sqlite_source_columns_are_qualified() {
  let builder = RelationalSelectBuilder::new("users", &["id", "name"]).with_many(
    "posts",
    "posts",
    "id",
    "author_id",
    &["id"],
  );
  let sql = builder.to_sql_sqlite();
  assert!(
    sql.contains(r#""users"."id""#),
    "should qualify source columns, got: {sql}"
  );
  assert!(
    sql.contains(r#""users"."name""#),
    "should qualify source columns, got: {sql}"
  );
}