Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita74aff1

Browse files
committed
Add optional float-as-decimal encoding
* Possibility to send numbers as decimals using {decimal, Value}.* New option {float_as_decimal, boolean() | non_neg_integer()} to do this automatically for floats, optionally rounded to a given number of decimals.
1 parent552dce4 commita74aff1

File tree

7 files changed

+158
-16
lines changed

7 files changed

+158
-16
lines changed

‎doc/overview.edoc‎

Lines changed: 13 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -69,12 +69,14 @@ For the reference manual see the <a href="mysql.html">mysql</a> module.
6969
<td>DECIMAL(P, S)</td>
7070
<td>`integer()' when S == 0<br />
7171
`float()' when P =&lt; 15 and S &gt; 0<br />
72-
`binary()' when P &gt;= 16 and S &gt; 0 [<a href="#vn2">2</a>]</td>
73-
<td>`42'<br />`3.14'<br />`<<"3.14159265358979323846">>'</td>
72+
`binary()' when P &gt;= 16 and S &gt; 0 [<a href="#vn2">2</a>]<br />
73+
`{decimal, Value}' [<a href="#vn3">3</a>] (parameter only)</td>
74+
<td>`42'<br />`3.14'<br />`<<"3.14159265358979323846">>'<br />
75+
`{decimal, 10.2}'</td>
7476
</tr>
7577
<tr>
7678
<td>DATETIME, TIMESTAMP</td>
77-
<td>`calendar:datetime()' [<a href="#vn3">3</a>]</td>
79+
<td>`calendar:datetime()' [<a href="#vn4">4</a>]</td>
7880
<td>`{{2014, 11, 18}, {10, 22, 36}}'</td>
7981
</tr>
8082
<tr>
@@ -84,8 +86,8 @@ For the reference manual see the <a href="mysql.html">mysql</a> module.
8486
</tr>
8587
<tr>
8688
<td>TIME</td>
87-
<td>`{Days, calendar:time()}' [<a href="#vn3">3</a>,
88-
<a href="#vn4">4</a>]</td>
89+
<td>`{Days, calendar:time()}' [<a href="#vn4">4</a>,
90+
<a href="#vn5">5</a>]</td>
8991
<td>`{0, {10, 22, 36}}'</td>
9092
</tr>
9193
<tr>
@@ -111,17 +113,20 @@ Notes:
111113
can be represented without precision loss and as `binary()' for high
112114
precision DECIMAL values. This is similar to how the `odbc' OTP application
113115
treats DECIMALs.</li>
114-
<li id="vn3">For `DATETIME', `TIMESTAMP' and `TIME' values with fractions of
116+
<li id="vn3">DECIMALs can be sent as `{decimal, Value}' (where Value is a
117+
number, string or binary) but values received from the database are
118+
never returned in this format.</li>
119+
<li id="vn4">For `DATETIME', `TIMESTAMP' and `TIME' values with fractions of
115120
seconds, we use a float for the seconds part. (These are unusual and were
116121
added to MySQL in version 5.6.4.)</li>
117-
<li id="vn4">Since `TIME' can be outside the `calendar:time()' interval, we use
122+
<li id="vn5">Since `TIME' can be outside the `calendar:time()' interval, we use
118123
the format as returned by `calendar:seconds_to_daystime/1' for `TIME'
119124
values.</li>
120125
</ol>
121126

122127
<h2>Copying</h2>
123128

124-
Copyright 2014-2019 The authors of MySQL/OTP. See the project page at
129+
Copyright 2014-2021 The authors of MySQL/OTP. See the project page at
125130
<a href="https://github.com/mysql-otp/mysql-otp"
126131
target="_top">https://github.com/mysql-otp/mysql-otp</a>.
127132

‎src/mysql.erl‎

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
%% MySQL/OTP – MySQL client library for Erlang/OTP
2-
%% Copyright (C) 2014-2015, 2018 Viktor Söderqvist,
2+
%% Copyright (C) 2014-2015, 2018, 2021 Viktor Söderqvist,
33
%% 2016 Johan Lövdahl
44
%% 2017 Piotr Nosek, Michal Slaski
55
%%
@@ -92,7 +92,8 @@
9292
| {found_rows,boolean()}
9393
| {query_cache_time,non_neg_integer()}
9494
| {tcp_options, [gen_tcp:connect_option()]}
95-
| {ssl,term()}.
95+
| {ssl,term()}
96+
| {float_as_decimal,boolean() |non_neg_integer()}.
9697

9798
-include("exception.hrl").
9899

@@ -192,6 +193,12 @@
192193
%% The `server_name_indication' option, if omitted, defaults to the value
193194
%% of the `host' option if it is a hostname string, otherwise no default
194195
%% value is set.</dd>
196+
%% <dt>`{float_as_decimal, boolean() | non_neg_integer()}'</dt>
197+
%% <dd>Encode floats as decimals when sending parameters for parametrized
198+
%% queries and prepared statements to the server. This prevents float
199+
%% rounding and truncation errors from happening on the server side. If a
200+
%% number is specified, the float is rounded to this number of
201+
%% decimals. This is off (false) by default.</dd>
195202
%% </dl>
196203
-specstart_link(Options:: [option()])-> {ok,pid()} |ignore | {error,term()}.
197204
start_link(Options)->

‎src/mysql_conn.erl‎

Lines changed: 23 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
%% MySQL/OTP – MySQL client library for Erlang/OTP
2-
%% Copyright (C) 2014-2018 Viktor Söderqvist
2+
%% Copyright (C) 2014-2021 Viktor Söderqvist
33
%%
44
%% This file is part of MySQL/OTP.
55
%%
@@ -57,7 +57,8 @@
5757
connect_timeout,ping_timeout,query_timeout,query_cache_time,
5858
affected_rows=0,status=0,warning_count=0,insert_id=0,
5959
transaction_levels= [],ping_ref=undefined,
60-
stmts=dict:new(),query_cache=empty,cap_found_rows=false}).
60+
stmts=dict:new(),query_cache=empty,cap_found_rows=false,
61+
float_as_decimal=false}).
6162

6263
%% @private
6364
init(Opts)->
@@ -89,6 +90,7 @@ init(Opts) ->
8990

9091
Queries=proplists:get_value(queries,Opts, []),
9192
Prepares=proplists:get_value(prepare,Opts, []),
93+
FloatAsDecimal=proplists:get_value(float_as_decimal,Opts,false),
9294

9395
true=lists:all(funmysql_protocol:valid_path/1,AllowedLocalPaths),
9496

@@ -111,7 +113,8 @@ init(Opts) ->
111113
ping_timeout=PingTimeout,
112114
query_timeout=QueryTimeout,
113115
query_cache_time=QueryCacheTime,
114-
cap_found_rows= (SetFoundRows=:=true)
116+
cap_found_rows= (SetFoundRows=:=true),
117+
float_as_decimal=FloatAsDecimal
115118
},
116119

117120
caseproplists:get_value(connect_mode,Opts,synchronous)of
@@ -554,9 +557,16 @@ code_change(_OldVsn, _State, _Extra) ->
554557
%% @doc Executes a prepared statement and returns {Reply, NewState}.
555558
execute_stmt(Stmt,Args,FilterMap,Timeout,State)->
556559
#state{socket=Socket,sockmod=SockMod,
557-
allowed_local_paths=AllowedPaths}=State,
560+
allowed_local_paths=AllowedPaths,
561+
float_as_decimal=FloatAsDecimal}=State,
562+
Args1=caseFloatAsDecimalof
563+
false ->
564+
Args;
565+
_ ->
566+
[float_to_decimal(Arg,FloatAsDecimal) ||Arg<-Args]
567+
end,
558568
setopts(SockMod,Socket, [{active,false}]),
559-
{ok,Recs}=casemysql_protocol:execute(Stmt,Args,SockMod,Socket,
569+
{ok,Recs}=casemysql_protocol:execute(Stmt,Args1,SockMod,Socket,
560570
AllowedPaths,FilterMap,
561571
Timeout)of
562572
{error,timeout}whenState#state.server_version>= [5,0,0] ->
@@ -576,6 +586,14 @@ execute_stmt(Stmt, Args, FilterMap, Timeout, State) ->
576586
andalsolog_warnings(State1,Stmt#prepared.orig_query),
577587
handle_query_call_result(Recs,Stmt#prepared.orig_query,State1).
578588

589+
%% @doc Formats floats as decimals, optionally with a given number of decimals.
590+
float_to_decimal(Arg,true)whenis_float(Arg)->
591+
{decimal,list_to_binary(io_lib:format("~w", [Arg]))};
592+
float_to_decimal(Arg,N)whenis_float(Arg),is_integer(N)->
593+
{decimal,float_to_binary(Arg, [{decimals,N},compact])};
594+
float_to_decimal(Arg,_)->
595+
Arg.
596+
579597
%% @doc Produces a tuple to return as an error reason.
580598
-specerror_to_reason(#error{})->mysql:server_reason().
581599
error_to_reason(#error{code=Code,state=State,msg=Msg})->

‎src/mysql_encode.erl‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,12 @@ encode(String) when is_list(String) ->
2323
encode(unicode:characters_to_binary(String));
2424
encode(Bitstring)whenis_bitstring(Bitstring)->
2525
["b'", [caseBof0 ->$0;1 ->$1end || <<B:1>><=Bitstring ],$'];
26+
encode({decimal,Num})whenis_float(Num);is_integer(Num)->
27+
encode(Num);
28+
encode({decimal,Str})whenis_binary(Str);is_list(Str)->
29+
%% Simple injection block
30+
nomatch=re:run(Str, <<"[^0-9.+\\-eE]">>),
31+
Str;
2632
encode({Y,M,D})->
2733
io_lib:format("'~4..0b-~2..0b-~2..0b'", [Y,M,D]);
2834
encode({{Y,M,D}, {H,Mi,S}})whenis_integer(S)->

‎src/mysql_protocol.erl‎

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
%% MySQL/OTP – MySQL client library for Erlang/OTP
2-
%% Copyright (C) 2014 Viktor Söderqvist
2+
%% Copyright (C) 2014-2021 Viktor Söderqvist
33
%% 2017 Piotr Nosek, Michal Slaski
44
%%
55
%% This file is part of MySQL/OTP.
@@ -1078,6 +1078,14 @@ encode_param(Value) when is_integer(Value), Value < 0 ->
10781078
end;
10791079
encode_param(Value)whenis_float(Value)->
10801080
{<<?TYPE_DOUBLE,0>>, <<Value:64/float-little>>};
1081+
encode_param({decimal,Value})->
1082+
Bin=ifis_binary(Value) ->Value;
1083+
is_list(Value) ->list_to_binary(Value);
1084+
is_integer(Value) ->integer_to_binary(Value);
1085+
is_float(Value) ->list_to_binary(io_lib:format("~w", [Value]))
1086+
end,
1087+
EncLength=lenenc_int_encode(byte_size(Bin)),
1088+
{<<?TYPE_DECIMAL,0>>, <<EncLength/binary,Bin/binary>>};
10811089
encode_param(Value)whenis_bitstring(Value)->
10821090
Binary=encode_bitstring(Value),
10831091
EncLength=lenenc_int_encode(byte_size(Binary)),
@@ -1147,6 +1155,9 @@ is_valid_param(Value) when is_list(Value) ->
11471155
end;
11481156
is_valid_param(Value)whenis_number(Value)->
11491157
true;
1158+
is_valid_param({decimal,Value})whenis_binary(Value);is_list(Value);
1159+
is_float(Value);is_integer(Value)->
1160+
true;
11501161
is_valid_param(Value)whenis_bitstring(Value)->
11511162
true;
11521163
is_valid_param({Y,M,D})->

‎test/mysql_encode_tests.erl‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,9 @@ encode_test() ->
2121
{<<255,0,255,0>>, <<"'",255,0,255,0,"'">>},
2222
%% BIT(N)
2323
{<<255,2:3>>,"b'11111111010'"},
24+
%% Explicit decimal
25+
{{decimal,10.2},"10.2"},
26+
{{decimal,"10.2"},"10.2"},
2427
%% DATE
2528
{{2014,11,03},"'2014-11-03'"},
2629
{{0,0,0},"'0000-00-00'"},

‎test/mysql_tests.erl‎

Lines changed: 92 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -328,6 +328,9 @@ query_test_() ->
328328
{"Binary protocol",fun ()->binary_protocol(Pid)end},
329329
{"FLOAT rounding",fun ()->float_rounding(Pid)end},
330330
{"DECIMAL",fun ()->decimal(Pid)end},
331+
{"DECIMAL truncated",fun ()->decimal_trunc(Pid)end},
332+
{"Float as decimal",fun ()->float_as_decimal(Pid)end},
333+
{"Float as decimal(2)",fun ()->float_as_decimal_2(Pid)end},
331334
{"INT",fun ()->int(Pid)end},
332335
{"BIT(N)",fun ()->bit(Pid)end},
333336
{"DATE",fun ()->date(Pid)end},
@@ -709,6 +712,95 @@ decimal(Pid) ->
709712
<<"dec16">>, <<"d">>),
710713
ok=mysql:query(Pid,"DROP TABLE dec16").
711714

715+
decimal_trunc(_Pid)->
716+
%% Create another connection with log_warnings enabled.
717+
{ok,Pid}=mysql:start_link([{user,?user}, {password,?password},
718+
{log_warnings,true}]),
719+
ok=mysql:query(Pid, <<"USE otptest">>),
720+
ok=mysql:query(Pid, <<"SET autocommit = 1">>),
721+
ok=mysql:query(Pid, <<"SET SESSION sql_mode = ?">>, [?SQL_MODE]),
722+
ok=mysql:query(Pid, <<"CREATE TABLE `test_decimals` ("
723+
" `id` bigint(20) unsigned NOT NULL,"
724+
" `balance` decimal(13,4) NOT NULL,"
725+
" PRIMARY KEY (`id`)"
726+
") ENGINE=InnoDB;">>),
727+
ok=mysql:query(Pid, <<"INSERT INTO test_decimals (id, balance)"
728+
" VALUES (1, 5000), (2, 5000), (3, 5000);">>),
729+
{ok,decr}=mysql:prepare(Pid,decr, <<"UPDATE test_decimals"
730+
" SET balance = balance - ?"
731+
" WHERE id = ?">>),
732+
%% Decimal sent as float gives truncation warning.
733+
{ok,ok, [{_,LoggedWarning1}|_]}=error_logger_acc:capture(fun ()->
734+
ok=mysql:execute(Pid,decr, [10.2,1]),
735+
ok=mysql:execute(Pid,decr, [10.2,1]),
736+
ok=mysql:execute(Pid,decr, [10.2,1]),
737+
ok=mysql:execute(Pid,decr, [10.2,1])
738+
end),
739+
?assertMatch("Note 1265: Data truncated for column 'balance'"++_,
740+
LoggedWarning1),
741+
%% Decimal sent as binary gives truncation warning.
742+
{ok,ok, [{_,LoggedWarning2}|_]}=error_logger_acc:capture(fun ()->
743+
ok=mysql:execute(Pid,decr, [<<"10.2">>,2]),
744+
ok=mysql:execute(Pid,decr, [<<"10.2">>,2]),
745+
ok=mysql:execute(Pid,decr, [<<"10.2">>,2]),
746+
ok=mysql:execute(Pid,decr, [<<"10.2">>,2])
747+
end),
748+
?assertMatch("Note 1265: Data truncated for column 'balance'"++_,
749+
LoggedWarning2),
750+
%% Decimal sent as DECIMAL => no warning
751+
{ok,ok, []}=error_logger_acc:capture(fun ()->
752+
ok=mysql:execute(Pid,decr, [{decimal, <<"10.2">>},3]),
753+
ok=mysql:execute(Pid,decr, [{decimal,"10.2"},3]),
754+
ok=mysql:execute(Pid,decr, [{decimal,10.2},3]),
755+
ok=mysql:execute(Pid,decr, [{decimal,10.2},3]),
756+
ok=mysql:execute(Pid,decr, [{decimal,0},3])% <- integer coverage
757+
end),
758+
?assertMatch({ok,_, [[1,4959.2], [2,4959.2], [3,4959.2]]},
759+
mysql:query(Pid, <<"SELECT id, balance FROM test_decimals">>)),
760+
ok=mysql:query(Pid,"DROP TABLE test_decimals"),
761+
ok=mysql:stop(Pid).
762+
763+
float_as_decimal(_Pid) ->
764+
%% Create another connection with {float_as_decimal, true}
765+
{ok,Pid}=mysql:start_link([{user,?user}, {password,?password},
766+
{log_warnings,true},
767+
{float_as_decimal,true}]),
768+
ok=mysql:query(Pid, <<"USE otptest">>),
769+
ok=mysql:query(Pid, <<"SET autocommit = 1">>),
770+
ok=mysql:query(Pid, <<"SET SESSION sql_mode = ?">>, [?SQL_MODE]),
771+
ok=mysql:query(Pid, <<"CREATE TABLE float_as_decimal ("
772+
" balance decimal(13,4) NOT NULL"
773+
") ENGINE=InnoDB;">>),
774+
ok=mysql:query(Pid, <<"INSERT INTO float_as_decimal (balance)"
775+
" VALUES (5000);">>),
776+
{ok,decr}=mysql:prepare(Pid,decr, <<"UPDATE float_as_decimal"
777+
" SET balance = balance - ?">>),
778+
%% Floats sent as decimal => no truncation warning.
779+
{ok,ok, []}=error_logger_acc:capture(fun ()->
780+
ok=mysql:execute(Pid,decr, [10.2]),
781+
ok=mysql:execute(Pid,decr, [10.2]),
782+
ok=mysql:execute(Pid,decr, [10.2]),
783+
ok=mysql:execute(Pid,decr, [10.2])
784+
end),
785+
ok=mysql:query(Pid,"DROP TABLE float_as_decimal;"),
786+
ok=mysql:stop(Pid).
787+
788+
float_as_decimal_2(_Pid)->
789+
%% Create another connection with {float_as_decimal, 2}.
790+
%% Check that floats are sent as DECIMAL with 2 decimals.
791+
{ok,Pid}=mysql:start_link([{user,?user}, {password,?password},
792+
{log_warnings,true},
793+
{float_as_decimal,2}]),
794+
ok=mysql:query(Pid, <<"USE otptest">>),
795+
ok=mysql:query(Pid, <<"SET autocommit = 1">>),
796+
ok=mysql:query(Pid, <<"SET SESSION sql_mode = ?">>, [?SQL_MODE]),
797+
ok=mysql:query(Pid, <<"CREATE TABLE dec13_4 (d DECIMAL(13,4))">>),
798+
ok=mysql:query(Pid, <<"INSERT INTO dec13_4 (d) VALUES (?)">>, [3.14159]),
799+
{ok,_, [[Value]]}=mysql:query(Pid, <<"SELECT d FROM dec13_4">>),
800+
?assertEqual(3.14,Value),
801+
ok=mysql:query(Pid, <<"DROP TABLE dec13_4">>),
802+
ok=mysql:stop(Pid).
803+
712804
int(Pid) ->
713805
ok=mysql:query(Pid,"CREATE TABLE ints (i INT)"),
714806
write_read_text_binary(Pid,42, <<"42">>, <<"ints">>, <<"i">>),

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp