./ct_report/coverage/mam_lookup_sql.COVER.html

1 %% Makes a SELECT SQL query
2 -module(mam_lookup_sql).
3 -export([lookup_query/5]).
4
5 -include("mongoose_logger.hrl").
6 -include("mongoose_mam.hrl").
7
8 -type offset_limit() :: all | {Offset :: non_neg_integer(), Limit :: non_neg_integer()}.
9 -type sql_part() :: iolist() | binary().
10 -type env_vars() :: mod_mam_rdbms_arch:env_vars().
11 -type query_type() :: atom().
12 -type column() :: atom().
13 -type lookup_query_fn() :: fun((QueryType :: atom(), Env :: map(), Filters :: list(),
14 Order :: atom(), OffsetLimit :: offset_limit()) -> term()).
15
16 -export_type([sql_part/0]).
17 -export_type([query_type/0]).
18 -export_type([column/0]).
19 -export_type([lookup_query_fn/0]).
20
21 %% The ONLY usage of Env is in these functions:
22 %% The rest of code should treat Env as opaque (i.e. the code just passes Env around).
23 -spec host_type(env_vars()) -> mongooseim:host_type().
24 4345 host_type(#{host_type := HostType}) -> HostType.
25
26 -spec table(env_vars()) -> atom().
27 4345 table(#{table := Table}) -> Table.
28
29 -spec index_hint_sql(env_vars()) -> sql_part().
30 61 index_hint_sql(Env = #{index_hint_fn := F}) -> F(Env).
31
32 -spec columns_sql(env_vars(), query_type()) -> sql_part().
33 61 columns_sql(#{columns_sql_fn := F}, QueryType) -> F(QueryType).
34
35 -spec column_to_id(env_vars(), column()) -> string().
36 5734 column_to_id(#{column_to_id_fn := F}, Col) -> F(Col).
37
38
39 %% This function uses some fields from Env:
40 %% - host_type
41 %% - table
42 %% - index_hint_fn
43 %% - columns_sql_fn
44 %% - column_to_id_fn
45 %%
46 %% Filters are in format {Op, Column, Value}
47 %% QueryType should be an atom, that we pass into the columns_sql_fn function.
48 -spec lookup_query(QueryType :: atom(), Env :: map(), Filters :: list(),
49 Order :: atom(), OffsetLimit :: offset_limit()) -> term().
50 lookup_query(QueryType, Env, Filters, Order, OffsetLimit) ->
51 4345 Table = table(Env),
52 4345 HostType = host_type(Env),
53 4345 StmtName = filters_to_statement_name(Env, QueryType, Table, Filters, Order, OffsetLimit),
54 4345 case mongoose_rdbms:prepared(StmtName) of
55 false ->
56 %% Create a new type of a query
57 61 SQL = lookup_sql_binary(QueryType, Table, Env, Filters, Order, OffsetLimit),
58 61 Columns = filters_to_columns(Filters, OffsetLimit),
59 61 mongoose_rdbms:prepare(StmtName, Table, Columns, SQL);
60 true ->
61 4284 ok
62 end,
63 4345 Args = filters_to_args(Filters, OffsetLimit),
64 4345 mongoose_rdbms:execute_successfully(HostType, StmtName, Args).
65
66 lookup_sql_binary(QueryType, Table, Env, Filters, Order, OffsetLimit) ->
67 61 iolist_to_binary(lookup_sql(QueryType, Table, Env, Filters, Order, OffsetLimit)).
68
69 lookup_sql(QueryType, Table, Env, Filters, Order, OffsetLimit) ->
70 61 IndexHintSQL = index_hint_sql(Env),
71 61 FilterSQL = filters_to_sql(Filters),
72 61 OrderSQL = order_to_sql(Order),
73 61 {LimitSQL, TopSQL} = limit_sql(OffsetLimit),
74 61 ["SELECT ", TopSQL, " ", columns_sql(Env, QueryType),
75 " FROM ", atom_to_list(Table), " ",
76 IndexHintSQL, FilterSQL, OrderSQL, LimitSQL].
77
78 21 limit_sql(all) -> {"", ""};
79 38 limit_sql({0, _Limit}) -> rdbms_queries:get_db_specific_limits();
80 2 limit_sql({_Offset, _Limit}) -> {rdbms_queries:limit_offset_sql(), ""}.
81
82 filters_to_columns(Filters, OffsetLimit) ->
83 61 offset_limit_to_columns(OffsetLimit, [Column || {_Op, Column, _Value} <- Filters]).
84
85 filters_to_args(Filters, OffsetLimit) ->
86 4345 offset_limit_to_args(OffsetLimit, [Value || {_Op, _Column, Value} <- Filters]).
87
88 offset_limit_to_args(all, Args) ->
89 2881 Args;
90 offset_limit_to_args({0, Limit}, Args) ->
91 1443 rdbms_queries:add_limit_arg(Limit, Args);
92 offset_limit_to_args({Offset, Limit}, Args) ->
93 21 Args ++ rdbms_queries:limit_offset_args(Limit, Offset).
94
95 offset_limit_to_columns(all, Columns) ->
96 21 Columns;
97 offset_limit_to_columns({0, _Limit}, Columns) ->
98 38 rdbms_queries:add_limit_arg(limit, Columns);
99 offset_limit_to_columns({_Offset, _Limit}, Columns) ->
100 2 Columns ++ rdbms_queries:limit_offset_args(limit, offset).
101
102 filters_to_statement_name(Env, QueryType, Table, Filters, Order, OffsetLimit) ->
103 4345 QueryId = query_type_to_id(QueryType),
104 4345 Ids = [op_to_id(Op) ++ column_to_id(Env, Col) || {Op, Col, _Val} <- Filters],
105 4345 OrderId = order_type_to_id(Order),
106 4345 LimitId = offset_limit_to_id(OffsetLimit),
107 4345 list_to_atom(atom_to_list(Table) ++ "_" ++ QueryId ++ "_" ++ OrderId ++ "_" ++ lists:append(Ids) ++ "_" ++ LimitId).
108
109 4345 query_type_to_id(QueryType) -> atom_to_list(QueryType).
110
111 385 order_type_to_id(desc) -> "d";
112 1130 order_type_to_id(asc) -> "a";
113 2830 order_type_to_id(unordered) -> "u".
114
115 23 order_to_sql(asc) -> " ORDER BY id ";
116 18 order_to_sql(desc) -> " ORDER BY id DESC ";
117 20 order_to_sql(unordered) -> " ".
118
119 1443 offset_limit_to_id({0, _Limit}) -> "limit";
120 21 offset_limit_to_id({_Offset, _Limit}) -> "offlim";
121 2881 offset_limit_to_id(all) -> "all".
122
123 filters_to_sql(Filters) ->
124 61 SQLs = [filter_to_sql(Filter) || Filter <- Filters],
125 61 case SQLs of
126 
:-(
 [] -> "";
127 61 Defined -> [" WHERE ", rdbms_queries:join(Defined, " AND ")]
128 end.
129
130 -spec filter_to_sql(mam_filter:filter_field()) -> sql_part().
131 122 filter_to_sql({Op, Column, _Value}) -> filter_to_sql(atom_to_list(Column), Op).
132
133 4504 op_to_id(equal) -> "eq";
134 287 op_to_id(less) -> "lt"; %% less than
135 196 op_to_id(greater) -> "gt"; %% greater than
136 313 op_to_id(le) -> "le"; %% less or equal
137 364 op_to_id(ge) -> "ge"; %% greater or equal
138 70 op_to_id(like) -> "lk".
139
140 71 filter_to_sql(Column, equal) -> Column ++ " = ?";
141 8 filter_to_sql(Column, less) -> Column ++ " < ?";
142 8 filter_to_sql(Column, greater) -> Column ++ " > ?";
143 13 filter_to_sql(Column, le) -> Column ++ " <= ?";
144 15 filter_to_sql(Column, ge) -> Column ++ " >= ?";
145 7 filter_to_sql(Column, like) -> Column ++ " LIKE ?".
Line Hits Source