./ct_report/coverage/mongoose_rdbms_odbc.COVER.html

1 %%==============================================================================
2 %% Copyright 2016 Erlang Solutions Ltd.
3 %%
4 %% Licensed under the Apache License, Version 2.0 (the "License");
5 %% you may not use this file except in compliance with the License.
6 %% You may obtain a copy of the License at
7 %%
8 %% http://www.apache.org/licenses/LICENSE-2.0
9 %%
10 %% Unless required by applicable law or agreed to in writing, software
11 %% distributed under the License is distributed on an "AS IS" BASIS,
12 %% WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 %% See the License for the specific language governing permissions and
14 %% limitations under the License.
15 %%==============================================================================
16
17 -module(mongoose_rdbms_odbc).
18 -author('konrad.zemek@erlang-solutions.com').
19 -behaviour(mongoose_rdbms_backend).
20 -include("mongoose_logger.hrl").
21
22 -export([escape_binary/1, escape_string/1,
23 unescape_binary/1, connect/2, disconnect/1,
24 query/3, prepare/5, execute/4]).
25
26 -type tabcol() :: {binary(), binary()}.
27
28 -type options() :: #{settings := string(), atom() => any()}.
29
30 -type result_tuple() :: tuple().
31
32 %% API
33
34 -spec escape_binary(binary()) -> iodata().
35 escape_binary(Bin) when is_binary(Bin) ->
36 87 escape_binary(server_type(), Bin).
37
38 escape_string(Iolist) ->
39 57 ServerType = server_type(),
40 57 escape_text(ServerType, iolist_to_binary(Iolist)).
41
42 -spec unescape_binary(binary()) -> binary().
43 unescape_binary(Bin) when is_binary(Bin) ->
44 6538 base16:decode(Bin).
45
46 -spec connect(options(), QueryTimeout :: non_neg_integer()) ->
47 {ok, Connection :: term()} | {error, Reason :: any()}.
48 connect(#{settings := Settings}, _QueryTimeout) when is_list(Settings) ->
49 %% We need binary_strings=off to distinguish between:
50 %% - UTF-16 encoded NVARCHARs - encoded as binaries.
51 %% - Binaries/regular strings - encoded as list of small integers.
52 %%
53 %% It's not as efficient, as using binaries everywhere.
54 %% But otherwise we should propose one of two patches to OTP's odbc driver:
55 %% - Return UTF-16 strings as UTF-8
56 %% - Return type information from sql_query
57 %%
58 %% More info:
59 %% http://erlang.org/~raimo/doc-8.0.3/lib/odbc-2.11.2/doc/html/databases.html
60 530 case eodbc:connect(Settings, [{scrollable_cursors, off},
61 {binary_strings, on},
62 {return_types, on}]) of
63 {ok, Pid} ->
64 530 link(Pid),
65 530 {ok, Pid};
66 Error ->
67 
:-(
 Error
68 end.
69
70 -spec disconnect(Connection :: term()) -> any().
71 disconnect(Connection) ->
72 520 eodbc:disconnect(Connection).
73
74 -spec query(Connection :: term(), Query :: any(),
75 Timeout :: infinity | non_neg_integer()) -> mongoose_rdbms:query_result().
76 query(Connection, Query, Timeout) when is_binary(Query) ->
77 1168 query(Connection, [Query], Timeout);
78 query(Connection, Query, Timeout) ->
79 39139 parse(eodbc:sql_query(Connection, Query, Timeout)).
80
81 -spec prepare(Connection :: term(), Name :: atom(), Table :: binary(),
82 Fields :: [binary()], Statement :: iodata()) ->
83 {ok, {binary(), [fun((term()) -> tuple())]}}.
84 prepare(Connection, Name, Table, Fields, Statement) ->
85 3985 TabCols = fields_to_tabcol(Fields, Table),
86 3985 try prepare2(Connection, TabCols, Statement)
87 catch Class:Reason:Stacktrace ->
88 
:-(
 ?LOG_ERROR(#{what => prepare_failed,
89 statement_name => Name, sql_query => Statement,
90 
:-(
 class => Class, reason => Reason, stacktrace => Stacktrace}),
91 
:-(
 erlang:raise(Class, Reason, Stacktrace)
92 end.
93
94 prepare2(Connection, TabCols, Statement) ->
95 3985 Tables = tabcols_to_tables(TabCols),
96 3985 TableDesc = describe_tables(Connection, Tables),
97 3985 ServerType = server_type(),
98 3985 ParamMappers = [tabcol_to_mapper(ServerType, TableDesc, TabCol) || TabCol <- TabCols],
99 3985 {ok, {iolist_to_binary(Statement), ParamMappers}}.
100
101 -spec execute(Connection :: term(), Statement :: {binary(), [fun((term()) -> tuple())]},
102 Params :: [term()], Timeout :: infinity | non_neg_integer()) ->
103 mongoose_rdbms:query_result().
104 execute(Connection, {Query, ParamMapper}, Params, Timeout)
105 when length(ParamMapper) =:= length(Params) ->
106 153676 ODBCParams = map_params(Params, ParamMapper),
107 153676 case eodbc:param_query(Connection, Query, ODBCParams, Timeout) of
108 {error, Reason} ->
109 402 Map = #{reason => Reason,
110 odbc_query => Query,
111 odbc_params => ODBCParams},
112 402 {error, Map};
113 Result ->
114 153264 parse(Result)
115 end;
116 execute(Connection, {Query, ParamMapper}, Params, Timeout) ->
117 
:-(
 ?LOG_ERROR(#{what => odbc_execute_failed,
118 params_length => length(Params),
119 mapped_length => length(ParamMapper),
120 connection => Connection,
121 sql_query => Query,
122 query_params => Params,
123 
:-(
 param_mapper => ParamMapper}),
124 
:-(
 erlang:error({badarg, [Connection, {Query, ParamMapper}, Params, Timeout]}).
125
126 %% Helpers
127
128 -spec parse(result_tuple() | [result_tuple()] | {error, string()}) ->
129 mongoose_rdbms:query_result().
130 parse(Items) when is_list(Items) ->
131 
:-(
 [parse(Item) || Item <- Items];
132 parse({selected, FieldTypeNames, Rows}) ->
133 90037 FieldsInfo = fields_to_parse_info(FieldTypeNames),
134 90037 {selected, parse_rows(Rows, FieldsInfo)};
135 parse({error, Reason}) when is_atom(Reason) ->
136 
:-(
 {error, atom_to_list(Reason)};
137 parse({error, Reason}) ->
138 9 ErrorStr = unicode:characters_to_list(list_to_binary(Reason)),
139 9 case re:run(ErrorStr, "duplicate key") of
140 9 nomatch -> {error, ErrorStr};
141 
:-(
 {match, _} -> {error, duplicate_key}
142 end;
143 parse(Other) ->
144 102357 Other.
145
146 fields_to_parse_info(FieldTypeNames) ->
147 90037 [field_to_parse_info(FieldTypeName) || FieldTypeName <- FieldTypeNames].
148
149 field_to_parse_info({{sql_wvarchar,_}, _Name}) ->
150 150975 utf16;
151 field_to_parse_info(_) ->
152 80181 generic.
153
154 parse_rows(Rows, FieldsInfo) ->
155 90037 [list_to_tuple(parse_row(tuple_to_list(Row), FieldsInfo)) || Row <- Rows].
156
157 parse_row([null|Row], [_|FieldsInfo]) ->
158 2405 [null|parse_row(Row, FieldsInfo)];
159 parse_row([FieldValue|Row], [utf16|FieldsInfo]) ->
160 %% Transorms UTF16 encoded NVARCHAR-s into utf8
161 69555 Decoded = unicode_characters_to_binary(FieldValue, {utf16, little}, utf8),
162 69555 [Decoded|parse_row(Row, FieldsInfo)];
163 parse_row([FieldValue|Row], [generic|FieldsInfo]) ->
164 49178 [FieldValue|parse_row(Row, FieldsInfo)];
165 parse_row([], []) ->
166 55945 [].
167
168 -spec tabcol_to_mapper(ServerType :: atom(),
169 TableDesc :: proplists:proplist(),
170 TabCol :: tabcol()) -> fun((term()) -> tuple()).
171 tabcol_to_mapper(_ServerType, _TableDesc, {_, <<"limit">>}) ->
172 382 fun(P) -> {sql_integer, [P]} end;
173 tabcol_to_mapper(_ServerType, _TableDesc, {_, <<"offset">>}) ->
174 11 fun(P) -> {sql_integer, [P]} end;
175 tabcol_to_mapper(_ServerType, TableDesc, TabCol) ->
176 12791 ODBCType = tabcol_to_odbc_type(TabCol, TableDesc),
177 12791 case simple_type(just_type(ODBCType)) of
178 binary ->
179 510 fun(P) -> binary_mapper(P) end;
180 unicode ->
181 8918 fun(P) -> unicode_mapper(P) end;
182 bigint ->
183 2512 fun(P) -> bigint_mapper(P) end;
184 _ ->
185 851 fun(P) -> generic_mapper(ODBCType, P) end
186 end.
187
188 tabcol_to_odbc_type(TabCol = {Table, Column}, TableDesc) ->
189 12791 case lists:keyfind(TabCol, 1, TableDesc) of
190 false ->
191 
:-(
 ?LOG_ERROR(#{what => field_to_odbc_type_failed, table => Table,
192 
:-(
 column => Column, table_desc => TableDesc}),
193 
:-(
 error(field_to_odbc_type_failed);
194 {_, ODBCType} ->
195 12791 ODBCType
196 end.
197
198 %% Null should be encoded with the correct type. Otherwise when inserting two records,
199 %% where one value is null and the other is not, would cause:
200 %% > [FreeTDS][SQL Server]Conversion failed when converting the nvarchar value
201 %% 'orig_id' to data type int. SQLSTATE IS: 22018
202 unicode_mapper(null) ->
203 8096 {{sql_wlongvarchar, 0}, [null]};
204 unicode_mapper(P) ->
205 334928 Utf16 = unicode_characters_to_binary(iolist_to_binary(P), utf8, {utf16, little}),
206 334928 Len = byte_size(Utf16) div 2,
207 334928 {{sql_wlongvarchar, Len}, [Utf16]}.
208
209 bigint_mapper(null) ->
210 206 Type = {'sql_varchar', 0},
211 206 {Type, [null]};
212 bigint_mapper(P) when is_integer(P) ->
213 41850 B = integer_to_binary(P),
214 41850 Type = {'sql_varchar', byte_size(B)},
215 41850 {Type, [B]}.
216
217 binary_mapper(null) ->
218 9 Type = {'sql_longvarbinary', 0},
219 9 {Type, [null]};
220 binary_mapper(P) ->
221 10758 Type = {'sql_longvarbinary', byte_size(P)},
222 10758 {Type, [P]}.
223
224 generic_mapper(ODBCType, null) ->
225 1 {ODBCType, [null]};
226 generic_mapper(ODBCType, P) ->
227 15964 {ODBCType, [P]}.
228
229
230 
:-(
 simple_type('SQL_BINARY') -> binary;
231 510 simple_type('SQL_VARBINARY') -> binary;
232 
:-(
 simple_type('SQL_LONGVARBINARY') -> binary;
233 13 simple_type('SQL_LONGVARCHAR') -> unicode;
234 8371 simple_type('sql_wvarchar') -> unicode; %% nvarchar type in MSSQL
235 534 simple_type('sql_varchar') -> unicode; %% encode ascii as unicode
236 2512 simple_type('SQL_BIGINT') -> bigint;
237 851 simple_type(_) -> generic.
238
239 %% Ignore type length
240 just_type({Type, _Len}) ->
241 9047 Type;
242 just_type(Type) ->
243 3744 Type.
244
245 map_params([Param|Params], [Mapper|Mappers]) ->
246 413965 [map_param(Param, Mapper)|map_params(Params, Mappers)];
247 map_params([], []) ->
248 153676 [].
249
250 map_param(undefined, Mapper) ->
251 
:-(
 map_param(null, Mapper);
252 map_param(true, _Mapper) ->
253 
:-(
 {sql_integer, [1]};
254 map_param(false, _Mapper) ->
255 14 {sql_integer, [0]};
256 map_param(Param, Mapper) ->
257 413951 Mapper(Param).
258
259 -spec server_type() -> atom().
260 server_type() ->
261 4129 mongoose_config:get_opt(rdbms_server_type).
262
263 -spec escape_binary(ServerType :: atom(), binary()) -> iodata().
264 escape_binary(pgsql, Bin) ->
265 
:-(
 mongoose_rdbms_pgsql:escape_binary(Bin);
266 escape_binary(mysql, Bin) ->
267 
:-(
 mongoose_rdbms_mysql:escape_binary(Bin);
268 escape_binary(mssql, Bin) ->
269 87 [<<"0x">>, base16:encode(Bin)];
270 escape_binary(_ServerType, Bin) ->
271 
:-(
 [$', base16:encode(Bin), $'].
272
273 -spec escape_text(ServerType :: atom(), binary()) -> iodata().
274 escape_text(pgsql, Bin) ->
275 
:-(
 escape_pgsql_string(Bin);
276 escape_text(mssql, Bin) ->
277 57 Utf16 = unicode_characters_to_binary(Bin, utf8, {utf16, little}),
278 57 [<<"CAST(0x">>, base16:encode(Utf16), <<" AS NVARCHAR(max))">>];
279 escape_text(ServerType, Bin) ->
280 
:-(
 escape_binary(ServerType, Bin).
281
282 unicode_characters_to_binary(Input, FromEncoding, ToEncoding) ->
283 404540 case unicode:characters_to_binary(Input, FromEncoding, ToEncoding) of
284 Result when is_binary(Result) ->
285 404540 Result;
286 Other ->
287 
:-(
 erlang:error(#{what => parse_value_failed,
288 from_encoding => FromEncoding,
289 to_encoding => ToEncoding,
290 input_binary => Input,
291 output_result => Other})
292 end.
293
294 escape_pgsql_string(Bin) ->
295 
:-(
 [$', escape_pgsql_characters(Bin), $'].
296
297 %% Duplicate each single quaote
298 escape_pgsql_characters(Bin) when is_binary(Bin) ->
299 
:-(
 binary:replace(Bin, <<"'">>, <<"''">>, [global]).
300
301 fields_to_tabcol(Fields, DefaultTable) ->
302 3985 [field_to_tabcol(Field, DefaultTable) || Field <- Fields].
303
304 field_to_tabcol(Field, DefaultTable) ->
305 13184 case binary:split(Field, <<".">>) of
306 [Column] ->
307 13156 {DefaultTable, Column};
308 [Table, Column] ->
309 28 {Table, Column}
310 end.
311
312 tabcols_to_tables(TabCols) ->
313 3985 lists:usort([Table || {Table, _Column} <- TabCols]).
314
315 describe_tables(Connection, Tables) ->
316 3985 lists:append([describe_table(Connection, Table) || Table <- Tables]).
317
318 describe_table(Connection, Table) ->
319 3489 {ok, TableDesc} = eodbc:describe_table(Connection, unicode:characters_to_list(Table)),
320 3489 [{{Table, unicode:characters_to_binary(Column)}, ODBCType}
321 3489 || {Column, ODBCType} <- TableDesc].
Line Hits Source