| 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 |
:-( |
host_type(#{host_type := HostType}) -> HostType. |
| 25 |
|
|
| 26 |
|
-spec table(env_vars()) -> atom(). |
| 27 |
:-( |
table(#{table := Table}) -> Table. |
| 28 |
|
|
| 29 |
|
-spec index_hint_sql(env_vars()) -> sql_part(). |
| 30 |
:-( |
index_hint_sql(Env = #{index_hint_fn := F}) -> F(Env). |
| 31 |
|
|
| 32 |
|
-spec columns_sql(env_vars(), query_type()) -> sql_part(). |
| 33 |
:-( |
columns_sql(#{columns_sql_fn := F}, QueryType) -> F(QueryType). |
| 34 |
|
|
| 35 |
|
-spec column_to_id(env_vars(), column()) -> string(). |
| 36 |
:-( |
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 |
:-( |
Table = table(Env), |
| 52 |
:-( |
HostType = host_type(Env), |
| 53 |
:-( |
StmtName = filters_to_statement_name(Env, QueryType, Table, Filters, Order, OffsetLimit), |
| 54 |
:-( |
case mongoose_rdbms:prepared(StmtName) of |
| 55 |
|
false -> |
| 56 |
|
%% Create a new type of a query |
| 57 |
:-( |
SQL = lookup_sql_binary(QueryType, Table, Env, Filters, Order, OffsetLimit), |
| 58 |
:-( |
Columns = filters_to_columns(Filters, OffsetLimit), |
| 59 |
:-( |
mongoose_rdbms:prepare(StmtName, Table, Columns, SQL); |
| 60 |
|
true -> |
| 61 |
:-( |
ok |
| 62 |
|
end, |
| 63 |
:-( |
Args = filters_to_args(Filters, OffsetLimit), |
| 64 |
:-( |
mongoose_rdbms:execute_successfully(HostType, StmtName, Args). |
| 65 |
|
|
| 66 |
|
lookup_sql_binary(QueryType, Table, Env, Filters, Order, OffsetLimit) -> |
| 67 |
:-( |
iolist_to_binary(lookup_sql(QueryType, Table, Env, Filters, Order, OffsetLimit)). |
| 68 |
|
|
| 69 |
|
lookup_sql(QueryType, Table, Env, Filters, Order, OffsetLimit) -> |
| 70 |
:-( |
IndexHintSQL = index_hint_sql(Env), |
| 71 |
:-( |
FilterSQL = filters_to_sql(Filters), |
| 72 |
:-( |
OrderSQL = order_to_sql(Order), |
| 73 |
:-( |
{LimitSQL, TopSQL} = limit_sql(OffsetLimit), |
| 74 |
:-( |
["SELECT ", TopSQL, " ", columns_sql(Env, QueryType), |
| 75 |
|
" FROM ", atom_to_list(Table), " ", |
| 76 |
|
IndexHintSQL, FilterSQL, OrderSQL, LimitSQL]. |
| 77 |
|
|
| 78 |
:-( |
limit_sql(all) -> {"", ""}; |
| 79 |
:-( |
limit_sql({0, _Limit}) -> rdbms_queries:get_db_specific_limits(); |
| 80 |
:-( |
limit_sql({_Offset, _Limit}) -> {rdbms_queries:limit_offset_sql(), ""}. |
| 81 |
|
|
| 82 |
|
filters_to_columns(Filters, OffsetLimit) -> |
| 83 |
:-( |
offset_limit_to_columns(OffsetLimit, [Column || {_Op, Column, _Value} <- Filters]). |
| 84 |
|
|
| 85 |
|
filters_to_args(Filters, OffsetLimit) -> |
| 86 |
:-( |
offset_limit_to_args(OffsetLimit, [Value || {_Op, _Column, Value} <- Filters]). |
| 87 |
|
|
| 88 |
|
offset_limit_to_args(all, Args) -> |
| 89 |
:-( |
Args; |
| 90 |
|
offset_limit_to_args({0, Limit}, Args) -> |
| 91 |
:-( |
rdbms_queries:add_limit_arg(Limit, Args); |
| 92 |
|
offset_limit_to_args({Offset, Limit}, Args) -> |
| 93 |
:-( |
Args ++ rdbms_queries:limit_offset_args(Limit, Offset). |
| 94 |
|
|
| 95 |
|
offset_limit_to_columns(all, Columns) -> |
| 96 |
:-( |
Columns; |
| 97 |
|
offset_limit_to_columns({0, _Limit}, Columns) -> |
| 98 |
:-( |
rdbms_queries:add_limit_arg(limit, Columns); |
| 99 |
|
offset_limit_to_columns({_Offset, _Limit}, Columns) -> |
| 100 |
:-( |
Columns ++ rdbms_queries:limit_offset_args(limit, offset). |
| 101 |
|
|
| 102 |
|
filters_to_statement_name(Env, QueryType, Table, Filters, Order, OffsetLimit) -> |
| 103 |
:-( |
QueryId = query_type_to_id(QueryType), |
| 104 |
:-( |
Ids = [op_to_id(Op) ++ column_to_id(Env, Col) || {Op, Col, _Val} <- Filters], |
| 105 |
:-( |
OrderId = order_type_to_id(Order), |
| 106 |
:-( |
LimitId = offset_limit_to_id(OffsetLimit), |
| 107 |
:-( |
list_to_atom(atom_to_list(Table) ++ "_" ++ QueryId ++ "_" ++ OrderId ++ "_" ++ lists:append(Ids) ++ "_" ++ LimitId). |
| 108 |
|
|
| 109 |
:-( |
query_type_to_id(QueryType) -> atom_to_list(QueryType). |
| 110 |
|
|
| 111 |
:-( |
order_type_to_id(desc) -> "d"; |
| 112 |
:-( |
order_type_to_id(asc) -> "a"; |
| 113 |
:-( |
order_type_to_id(unordered) -> "u". |
| 114 |
|
|
| 115 |
:-( |
order_to_sql(asc) -> " ORDER BY id "; |
| 116 |
:-( |
order_to_sql(desc) -> " ORDER BY id DESC "; |
| 117 |
:-( |
order_to_sql(unordered) -> " ". |
| 118 |
|
|
| 119 |
:-( |
offset_limit_to_id({0, _Limit}) -> "limit"; |
| 120 |
:-( |
offset_limit_to_id({_Offset, _Limit}) -> "offlim"; |
| 121 |
:-( |
offset_limit_to_id(all) -> "all". |
| 122 |
|
|
| 123 |
|
filters_to_sql(Filters) -> |
| 124 |
:-( |
SQLs = [filter_to_sql(Filter) || Filter <- Filters], |
| 125 |
:-( |
case SQLs of |
| 126 |
:-( |
[] -> ""; |
| 127 |
:-( |
Defined -> [" WHERE ", rdbms_queries:join(Defined, " AND ")] |
| 128 |
|
end. |
| 129 |
|
|
| 130 |
|
-spec filter_to_sql(mam_filter:filter_field()) -> sql_part(). |
| 131 |
:-( |
filter_to_sql({Op, Column, _Value}) -> filter_to_sql(atom_to_list(Column), Op). |
| 132 |
|
|
| 133 |
:-( |
op_to_id(equal) -> "eq"; |
| 134 |
:-( |
op_to_id(less) -> "lt"; %% less than |
| 135 |
:-( |
op_to_id(greater) -> "gt"; %% greater than |
| 136 |
:-( |
op_to_id(le) -> "le"; %% less or equal |
| 137 |
:-( |
op_to_id(ge) -> "ge"; %% greater or equal |
| 138 |
:-( |
op_to_id(like) -> "lk". |
| 139 |
|
|
| 140 |
:-( |
filter_to_sql(Column, equal) -> Column ++ " = ?"; |
| 141 |
:-( |
filter_to_sql(Column, less) -> Column ++ " < ?"; |
| 142 |
:-( |
filter_to_sql(Column, greater) -> Column ++ " > ?"; |
| 143 |
:-( |
filter_to_sql(Column, le) -> Column ++ " <= ?"; |
| 144 |
:-( |
filter_to_sql(Column, ge) -> Column ++ " >= ?"; |
| 145 |
:-( |
filter_to_sql(Column, like) -> Column ++ " LIKE ?". |