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