./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 %% API
29
30 -spec escape_binary(binary()) -> iodata().
31 escape_binary(Bin) when is_binary(Bin) ->
32 87 escape_binary(server_type(), Bin).
33
34 escape_string(Iolist) ->
35 57 ServerType = server_type(),
36 57 escape_text(ServerType, iolist_to_binary(Iolist)).
37
38 -spec unescape_binary(binary()) -> binary().
39 unescape_binary(Bin) when is_binary(Bin) ->
40 5942 base16:decode(Bin).
41
42 -spec connect(Args :: any(), QueryTimeout :: non_neg_integer()) ->
43 {ok, Connection :: term()} | {error, Reason :: any()}.
44 connect(Settings, _QueryTimeout) when is_list(Settings) ->
45 %% We need binary_strings=off to distinguish between:
46 %% - UTF-16 encoded NVARCHARs - encoded as binaries.
47 %% - Binaries/regular strings - encoded as list of small integers.
48 %%
49 %% It's not as efficient, as using binaries everywhere.
50 %% But otherwise we should propose one of two patches to OTP's odbc driver:
51 %% - Return UTF-16 strings as UTF-8
52 %% - Return type information from sql_query
53 %%
54 %% More info:
55 %% http://erlang.org/~raimo/doc-8.0.3/lib/odbc-2.11.2/doc/html/databases.html
56 365 case eodbc:connect(Settings, [{scrollable_cursors, off},
57 {binary_strings, on},
58 {return_types, on}]) of
59 {ok, Pid} ->
60 365 link(Pid),
61 365 {ok, Pid};
62 Error ->
63 
:-(
 Error
64 end.
65
66 -spec disconnect(Connection :: term()) -> any().
67 disconnect(Connection) ->
68 350 eodbc:disconnect(Connection).
69
70 -spec query(Connection :: term(), Query :: any(),
71 Timeout :: infinity | non_neg_integer()) -> mongoose_rdbms:query_result().
72 query(Connection, Query, Timeout) when is_binary(Query) ->
73 827 query(Connection, [Query], Timeout);
74 query(Connection, Query, Timeout) ->
75 21084 parse(eodbc:sql_query(Connection, Query, Timeout)).
76
77 -spec prepare(Connection :: term(), Name :: atom(), Table :: binary(),
78 Fields :: [binary()], Statement :: iodata()) ->
79 {ok, {binary(), [fun((term()) -> tuple())]}}.
80 prepare(Connection, Name, Table, Fields, Statement) ->
81 1253 TabCols = fields_to_tabcol(Fields, Table),
82 1253 try prepare2(Connection, TabCols, Statement)
83 catch Class:Reason:Stacktrace ->
84 
:-(
 ?LOG_ERROR(#{what => prepare_failed,
85 statement_name => Name, sql_query => Statement,
86 
:-(
 class => Class, reason => Reason, stacktrace => Stacktrace}),
87 
:-(
 erlang:raise(Class, Reason, Stacktrace)
88 end.
89
90 prepare2(Connection, TabCols, Statement) ->
91 1253 Tables = tabcols_to_tables(TabCols),
92 1253 TableDesc = describe_tables(Connection, Tables),
93 1253 ServerType = server_type(),
94 1253 ParamMappers = [tabcol_to_mapper(ServerType, TableDesc, TabCol) || TabCol <- TabCols],
95 1253 {ok, {iolist_to_binary(Statement), ParamMappers}}.
96
97 -spec execute(Connection :: term(), Statement :: {binary(), [fun((term()) -> tuple())]},
98 Params :: [term()], Timeout :: infinity | non_neg_integer()) ->
99 mongoose_rdbms:query_result().
100 execute(Connection, {Query, ParamMapper}, Params, Timeout)
101 when length(ParamMapper) =:= length(Params) ->
102 84461 ODBCParams = map_params(Params, ParamMapper),
103 84461 case eodbc:param_query(Connection, Query, ODBCParams, Timeout) of
104 {error, Reason} ->
105 189 Map = #{reason => Reason,
106 odbc_query => Query,
107 odbc_params => ODBCParams},
108 189 {error, Map};
109 Result ->
110 84272 parse(Result)
111 end;
112 execute(Connection, {Query, ParamMapper}, Params, Timeout) ->
113 
:-(
 ?LOG_ERROR(#{what => odbc_execute_failed,
114 params_length => length(Params),
115 mapped_length => length(ParamMapper),
116 connection => Connection,
117 sql_query => Query,
118 query_params => Params,
119 
:-(
 param_mapper => ParamMapper}),
120 
:-(
 erlang:error({badarg, [Connection, {Query, ParamMapper}, Params, Timeout]}).
121
122 %% Helpers
123
124 -spec parse(eodbc:result_tuple() | [eodbc:result_tuple()] | {error, string()}) ->
125 mongoose_rdbms:query_result().
126 parse(Items) when is_list(Items) ->
127 
:-(
 [parse(Item) || Item <- Items];
128 parse({selected, FieldTypeNames, Rows}) ->
129 46862 FieldsInfo = fields_to_parse_info(FieldTypeNames),
130 46862 {selected, parse_rows(Rows, FieldsInfo)};
131 parse({error, Reason}) when is_atom(Reason) ->
132 
:-(
 {error, atom_to_list(Reason)};
133 parse({error, Reason}) ->
134 
:-(
 ErrorStr = unicode:characters_to_list(list_to_binary(Reason)),
135 
:-(
 case re:run(ErrorStr, "duplicate key") of
136 
:-(
 nomatch -> {error, ErrorStr};
137 
:-(
 {match, _} -> {error, duplicate_key}
138 end;
139 parse(Other) ->
140 58494 Other.
141
142 fields_to_parse_info(FieldTypeNames) ->
143 46862 [field_to_parse_info(FieldTypeName) || FieldTypeName <- FieldTypeNames].
144
145 field_to_parse_info({{sql_wvarchar,_}, _Name}) ->
146 84065 utf16;
147 field_to_parse_info(_) ->
148 48705 generic.
149
150 parse_rows(Rows, FieldsInfo) ->
151 46862 [list_to_tuple(parse_row(tuple_to_list(Row), FieldsInfo)) || Row <- Rows].
152
153 parse_row([null|Row], [_|FieldsInfo]) ->
154 1645 [null|parse_row(Row, FieldsInfo)];
155 parse_row([FieldValue|Row], [utf16|FieldsInfo]) ->
156 %% Transorms UTF16 encoded NVARCHAR-s into utf8
157 25393 Decoded = unicode_characters_to_binary(FieldValue, {utf16, little}, utf8),
158 25393 [Decoded|parse_row(Row, FieldsInfo)];
159 parse_row([FieldValue|Row], [generic|FieldsInfo]) ->
160 30671 [FieldValue|parse_row(Row, FieldsInfo)];
161 parse_row([], []) ->
162 28132 [].
163
164 -spec tabcol_to_mapper(ServerType :: atom(),
165 TableDesc :: proplists:proplist(),
166 TabCol :: tabcol()) -> fun((term()) -> tuple()).
167 tabcol_to_mapper(_ServerType, _TableDesc, {_, <<"limit">>}) ->
168 166 fun(P) -> {sql_integer, [P]} end;
169 tabcol_to_mapper(_ServerType, _TableDesc, {_, <<"offset">>}) ->
170 8 fun(P) -> {sql_integer, [P]} end;
171 tabcol_to_mapper(_ServerType, TableDesc, TabCol) ->
172 5129 ODBCType = tabcol_to_odbc_type(TabCol, TableDesc),
173 5129 case simple_type(just_type(ODBCType)) of
174 binary ->
175 305 fun(P) -> binary_mapper(P) end;
176 unicode ->
177 3429 fun(P) -> unicode_mapper(P) end;
178 bigint ->
179 1209 fun(P) -> bigint_mapper(P) end;
180 _ ->
181 186 fun(P) -> generic_mapper(ODBCType, P) end
182 end.
183
184 tabcol_to_odbc_type(TabCol = {Table, Column}, TableDesc) ->
185 5129 case lists:keyfind(TabCol, 1, TableDesc) of
186 false ->
187 
:-(
 ?LOG_ERROR(#{what => field_to_odbc_type_failed, table => Table,
188 
:-(
 column => Column, table_desc => TableDesc}),
189 
:-(
 error(field_to_odbc_type_failed);
190 {_, ODBCType} ->
191 5129 ODBCType
192 end.
193
194 %% Null should be encoded with the correct type. Otherwise when inserting two records,
195 %% where one value is null and the other is not, would cause:
196 %% > [FreeTDS][SQL Server]Conversion failed when converting the nvarchar value
197 %% 'orig_id' to data type int. SQLSTATE IS: 22018
198 unicode_mapper(null) ->
199 6699 {{sql_wlongvarchar, 0}, [null]};
200 unicode_mapper(P) ->
201 183303 Utf16 = unicode_characters_to_binary(iolist_to_binary(P), utf8, {utf16, little}),
202 183303 Len = byte_size(Utf16) div 2,
203 183303 {{sql_wlongvarchar, Len}, [Utf16]}.
204
205 bigint_mapper(null) ->
206 115 Type = {'sql_varchar', 0},
207 115 {Type, [null]};
208 bigint_mapper(P) when is_integer(P) ->
209 28141 B = integer_to_binary(P),
210 28141 Type = {'sql_varchar', byte_size(B)},
211 28141 {Type, [B]}.
212
213 binary_mapper(null) ->
214 9 Type = {'sql_longvarbinary', 0},
215 9 {Type, [null]};
216 binary_mapper(P) ->
217 7913 Type = {'sql_longvarbinary', byte_size(P)},
218 7913 {Type, [P]}.
219
220 generic_mapper(ODBCType, null) ->
221 1 {ODBCType, [null]};
222 generic_mapper(ODBCType, P) ->
223 4046 {ODBCType, [P]}.
224
225
226 
:-(
 simple_type('SQL_BINARY') -> binary;
227 305 simple_type('SQL_VARBINARY') -> binary;
228 
:-(
 simple_type('SQL_LONGVARBINARY') -> binary;
229 2 simple_type('SQL_LONGVARCHAR') -> unicode;
230 3256 simple_type('sql_wvarchar') -> unicode; %% nvarchar type in MSSQL
231 171 simple_type('sql_varchar') -> unicode; %% encode ascii as unicode
232 1209 simple_type('SQL_BIGINT') -> bigint;
233 186 simple_type(_) -> generic.
234
235 %% Ignore type length
236 just_type({Type, _Len}) ->
237 3493 Type;
238 just_type(Type) ->
239 1636 Type.
240
241 map_params([Param|Params], [Mapper|Mappers]) ->
242 232358 [map_param(Param, Mapper)|map_params(Params, Mappers)];
243 map_params([], []) ->
244 84461 [].
245
246 map_param(undefined, Mapper) ->
247 
:-(
 map_param(null, Mapper);
248 map_param(true, _Mapper) ->
249 25 {sql_integer, [1]};
250 map_param(false, _Mapper) ->
251 306 {sql_integer, [0]};
252 map_param(Param, Mapper) ->
253 232027 Mapper(Param).
254
255 -spec server_type() -> atom().
256 server_type() ->
257 1397 mongoose_config:get_opt(rdbms_server_type).
258
259 -spec escape_binary(ServerType :: atom(), binary()) -> iodata().
260 escape_binary(pgsql, Bin) ->
261 
:-(
 mongoose_rdbms_pgsql:escape_binary(Bin);
262 escape_binary(mysql, Bin) ->
263 
:-(
 mongoose_rdbms_mysql:escape_binary(Bin);
264 escape_binary(mssql, Bin) ->
265 87 [<<"0x">>, base16:encode(Bin)];
266 escape_binary(_ServerType, Bin) ->
267 
:-(
 [$', base16:encode(Bin), $'].
268
269 -spec escape_text(ServerType :: atom(), binary()) -> iodata().
270 escape_text(pgsql, Bin) ->
271 
:-(
 escape_pgsql_string(Bin);
272 escape_text(mssql, Bin) ->
273 57 Utf16 = unicode_characters_to_binary(Bin, utf8, {utf16, little}),
274 57 [<<"CAST(0x">>, base16:encode(Utf16), <<" AS NVARCHAR(max))">>];
275 escape_text(ServerType, Bin) ->
276 
:-(
 escape_binary(ServerType, Bin).
277
278 unicode_characters_to_binary(Input, FromEncoding, ToEncoding) ->
279 208753 case unicode:characters_to_binary(Input, FromEncoding, ToEncoding) of
280 Result when is_binary(Result) ->
281 208753 Result;
282 Other ->
283 
:-(
 erlang:error(#{what => parse_value_failed,
284 from_encoding => FromEncoding,
285 to_encoding => ToEncoding,
286 input_binary => Input,
287 output_result => Other})
288 end.
289
290 escape_pgsql_string(Bin) ->
291 
:-(
 [$', escape_pgsql_characters(Bin), $'].
292
293 %% Duplicate each single quaote
294 escape_pgsql_characters(Bin) when is_binary(Bin) ->
295 
:-(
 binary:replace(Bin, <<"'">>, <<"''">>, [global]).
296
297 fields_to_tabcol(Fields, DefaultTable) ->
298 1253 [field_to_tabcol(Field, DefaultTable) || Field <- Fields].
299
300 field_to_tabcol(Field, DefaultTable) ->
301 5303 case binary:split(Field, <<".">>) of
302 [Column] ->
303 5296 {DefaultTable, Column};
304 [Table, Column] ->
305 7 {Table, Column}
306 end.
307
308 tabcols_to_tables(TabCols) ->
309 1253 lists:usort([Table || {Table, _Column} <- TabCols]).
310
311 describe_tables(Connection, Tables) ->
312 1253 lists:append([describe_table(Connection, Table) || Table <- Tables]).
313
314 describe_table(Connection, Table) ->
315 1098 {ok, TableDesc} = eodbc:describe_table(Connection, unicode:characters_to_list(Table)),
316 1098 [{{Table, unicode:characters_to_binary(Column)}, ODBCType}
317 1098 || {Column, ODBCType} <- TableDesc].
Line Hits Source