blitz_query_cpp

1

2
#include <gtest/gtest.h>
3
#include <gmock/gmock.h>
4
#include "data/sql/sql_expr.hpp"
5
#include "data/sql/postgresql_renderer.hpp"
6

7
using namespace blitz_query_cpp::sql;
8

9
std::string render(const sql_expr_t &expr)
10
{
11
    postgresql_renderer renderer;
12
    renderer.render(expr);
13
    std::string result { renderer.get_string()};
14
    return result;
15
}
16

17
TEST(Sql, SelectFromTable)
18
{
19
    EXPECT_EQ(render(
20
                  select({"id", "name", "age"}) | from(table("users"))),
21
              "SELECT id, name, age FROM users");
22

23
    auto query2 = select({"id", "name"});
24
    query2 = query2 | from(table("users"));
25
    EXPECT_EQ(render(query2), "SELECT id, name FROM users");
26
}
27

28
TEST(Sql, SelectColumnsExpr)
29
{
30
    EXPECT_EQ(render(
31
                  select() | alias(column("security", "users", "id"), "user_id") | alias(column("security", "users", "name"), "user_name") | alias(column("security", "users", "age"), "user_age") | from(table("security", "users"))),
32
              "SELECT security.users.id as user_id, security.users.name as user_name, security.users.age as user_age FROM security.users");
33
}
34

35
TEST(Sql, SelectFromSubQuery)
36
{
37
    auto query =
38
        select({"id", "name"}) | from(
39
                                     select({"id", "name", "age"}) | from(table("users")));
40

41
    EXPECT_EQ(render(query), "SELECT id, name FROM (SELECT id, name, age FROM users)");
42

43
    auto subquery = select({"id", "name", "age"}) | from(table("users"));
44
    auto query2 =
45
        select({"id", "name"}) | from(subquery);
46

47
    EXPECT_EQ(render(query2), "SELECT id, name FROM (SELECT id, name, age FROM users)");
48
}
49

50
TEST(Sql, SelectWhere)
51
{
52
    auto query1 = select({"id", "name"}) | from(table("users")) | where(binary_operation("age", binary_op::Gt, 18));
53
    EXPECT_EQ(render(query1), "SELECT id, name FROM users WHERE age > 18");
54

55
    auto query2 = select({"id", "name"}) | from(table("users")) | where(binary_operation("name", binary_op::Like, "M%"));
56
    EXPECT_EQ(render(query2), "SELECT id, name FROM users WHERE name LIKE 'M%'");
57
}
58

59
TEST(Sql, SelectOffsetLimit)
60
{
61
    auto query1 = select({"id", "name"}) | from(table("users")) | offset(10) | limit(20);
62

63
    EXPECT_EQ(render(query1), "SELECT id, name FROM users OFFSET 10 LIMIT 20");
64
}
65

66
TEST(Sql, SelectOrderBy)
67
{
68
    auto query1 = select({"id", "name"}) | from(table("users")) | order_by(column("users", "name"), false);
69

70
    EXPECT_EQ(render(query1), "SELECT id, name FROM users ORDER BY users.name DESC");
71
}
72

73
TEST(Sql, SelectCombined)
74
{
75
    auto query1 = select({"id", "name"}) | from(table("security", "users")) | where(binary_operation("age", binary_op::Gt, 18)) | or_else(binary_operation("name", binary_op::Like, "M%")) | offset(10) | limit(20) | order_by(column("users", "name"), false) | order_by(column("users", "age"), true);
76

77
    EXPECT_EQ(render(query1), "SELECT id, name FROM security.users WHERE (age > 18 OR name LIKE 'M%') ORDER BY users.name DESC, users.age ASC OFFSET 10 LIMIT 20");
78
}
79

80
TEST(Sql, SimpleJoin)
81
{
82
    auto query1 = select() | column("user", "id") | column("user", "name") | from(table("user")) | join(table("order"), join_kind::InnerJoin, column("user", "id"), column("oder", "user_id"));
83
    EXPECT_EQ(render(query1), "SELECT user.id, user.name FROM user INNER JOIN order ON user.id = oder.user_id");
84
}