./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 174 escape_binary(server_type(), Bin).
37
38 escape_string(Iolist) ->
39 114 ServerType = server_type(),
40 114 escape_text(ServerType, iolist_to_binary(Iolist)).
41
42 -spec unescape_binary(binary()) -> binary().
43 unescape_binary(Bin) when is_binary(Bin) ->
44 7073 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 1846 query(Connection, [Query], Timeout);
78 query(Connection, Query, Timeout) ->
79 40540 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 4181 TabCols = fields_to_tabcol(Fields, Table),
86 4181 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 4181 Tables = tabcols_to_tables(TabCols),
96 4181 TableDesc = describe_tables(Connection, Tables),
97 4181 ServerType = server_type(),
98 4181 ParamMappers = [tabcol_to_mapper(ServerType, TableDesc, TabCol) || TabCol <- TabCols],
99 4181 {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 160084 ODBCParams = map_params(Params, ParamMapper),
107 160084 case eodbc:param_query(Connection, Query, ODBCParams, Timeout) of
108 {error, Reason} ->
109 362 Map = #{reason => Reason,
110 odbc_query => Query,
111 odbc_params => ODBCParams},
112 362 {error, Map};
113 Result ->
114 159722 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 93368 FieldsInfo = fields_to_parse_info(FieldTypeNames),
134 93368 {selected, parse_rows(Rows, FieldsInfo)};
135 parse({error, Reason}) when is_atom(Reason) ->
136 
:-(
 {error, atom_to_list(Reason)};
137 parse({error, Reason}) ->
138 
:-(
 ErrorStr = unicode:characters_to_list(list_to_binary(Reason)),
139 
:-(
 case re:run(ErrorStr, "duplicate key") of
140 
:-(
 nomatch -> {error, ErrorStr};
141 
:-(
 {match, _} -> {error, duplicate_key}
142 end;
143 parse(Other) ->
144 106894 Other.
145
146 fields_to_parse_info(FieldTypeNames) ->
147 93368 [field_to_parse_info(FieldTypeName) || FieldTypeName <- FieldTypeNames].
148
149 field_to_parse_info({{sql_wvarchar,_}, _Name}) ->
150 153146 utf16;
151 field_to_parse_info(_) ->
152 82977 generic.
153
154 parse_rows(Rows, FieldsInfo) ->
155 93368 [list_to_tuple(parse_row(tuple_to_list(Row), FieldsInfo)) || Row <- Rows].
156
157 parse_row([null|Row], [_|FieldsInfo]) ->
158 2440 [null|parse_row(Row, FieldsInfo)];
159 parse_row([FieldValue|Row], [utf16|FieldsInfo]) ->
160 %% Transorms UTF16 encoded NVARCHAR-s into utf8
161 69858 Decoded = unicode_characters_to_binary(FieldValue, {utf16, little}, utf8),
162 69858 [Decoded|parse_row(Row, FieldsInfo)];
163 parse_row([FieldValue|Row], [generic|FieldsInfo]) ->
164 51292 [FieldValue|parse_row(Row, FieldsInfo)];
165 parse_row([], []) ->
166 58324 [].
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 456 fun(P) -> {sql_integer, [P]} end;
173 tabcol_to_mapper(_ServerType, _TableDesc, {_, <<"offset">>}) ->
174 13 fun(P) -> {sql_integer, [P]} end;
175 tabcol_to_mapper(_ServerType, TableDesc, TabCol) ->
176 13309 ODBCType = tabcol_to_odbc_type(TabCol, TableDesc),
177 13309 case simple_type(just_type(ODBCType)) of
178 binary ->
179 586 fun(P) -> binary_mapper(P) end;
180 unicode ->
181 9170 fun(P) -> unicode_mapper(P) end;
182 bigint ->
183 2661 fun(P) -> bigint_mapper(P) end;
184 _ ->
185 892 fun(P) -> generic_mapper(ODBCType, P) end
186 end.
187
188 tabcol_to_odbc_type(TabCol = {Table, Column}, TableDesc) ->
189 13309 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 13309 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 10369 {{sql_wlongvarchar, 0}, [null]};
204 unicode_mapper(P) ->
205 351201 Utf16 = unicode_characters_to_binary(iolist_to_binary(P), utf8, {utf16, little}),
206 351201 Len = byte_size(Utf16) div 2,
207 351201 {{sql_wlongvarchar, Len}, [Utf16]}.
208
209 bigint_mapper(null) ->
210 208 Type = {'sql_varchar', 0},
211 208 {Type, [null]};
212 bigint_mapper(P) when is_integer(P) ->
213 47574 B = integer_to_binary(P),
214 47574 Type = {'sql_varchar', byte_size(B)},
215 47574 {Type, [B]}.
216
217 binary_mapper(null) ->
218 18 Type = {'sql_longvarbinary', 0},
219 18 {Type, [null]};
220 binary_mapper(P) ->
221 13318 Type = {'sql_longvarbinary', byte_size(P)},
222 13318 {Type, [P]}.
223
224 generic_mapper(ODBCType, null) ->
225 2 {ODBCType, [null]};
226 generic_mapper(ODBCType, P) ->
227 17626 {ODBCType, [P]}.
228
229
230 
:-(
 simple_type('SQL_BINARY') -> binary;
231 586 simple_type('SQL_VARBINARY') -> binary;
232 
:-(
 simple_type('SQL_LONGVARBINARY') -> binary;
233 13 simple_type('SQL_LONGVARCHAR') -> unicode;
234 8597 simple_type('sql_wvarchar') -> unicode; %% nvarchar type in MSSQL
235 560 simple_type('sql_varchar') -> unicode; %% encode ascii as unicode
236 2661 simple_type('SQL_BIGINT') -> bigint;
237 892 simple_type(_) -> generic.
238
239 %% Ignore type length
240 just_type({Type, _Len}) ->
241 9295 Type;
242 just_type(Type) ->
243 4014 Type.
244
245 map_params([Param|Params], [Mapper|Mappers]) ->
246 442758 [map_param(Param, Mapper)|map_params(Params, Mappers)];
247 map_params([], []) ->
248 160084 [].
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 442744 Mapper(Param).
258
259 -spec server_type() -> atom().
260 server_type() ->
261 4469 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 174 [<<"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 114 Utf16 = unicode_characters_to_binary(Bin, utf8, {utf16, little}),
278 114 [<<"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 421173 case unicode:characters_to_binary(Input, FromEncoding, ToEncoding) of
284 Result when is_binary(Result) ->
285 421173 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 4181 [field_to_tabcol(Field, DefaultTable) || Field <- Fields].
303
304 field_to_tabcol(Field, DefaultTable) ->
305 13778 case binary:split(Field, <<".">>) of
306 [Column] ->
307 13749 {DefaultTable, Column};
308 [Table, Column] ->
309 29 {Table, Column}
310 end.
311
312 tabcols_to_tables(TabCols) ->
313 4181 lists:usort([Table || {Table, _Column} <- TabCols]).
314
315 describe_tables(Connection, Tables) ->
316 4181 lists:append([describe_table(Connection, Table) || Table <- Tables]).
317
318 describe_table(Connection, Table) ->
319 3679 {ok, TableDesc} = eodbc:describe_table(Connection, unicode:characters_to_list(Table)),
320 3679 [{{Table, unicode:characters_to_binary(Column)}, ODBCType}
321 3679 || {Column, ODBCType} <- TableDesc].
Line Hits Source