mysql-otp/test/mysql_tests.erl

%% MySQL/OTP – a MySQL driver for Erlang/OTP
%% Copyright (C) 2014 Viktor Söderqvist
%%
%% This program is free software: you can redistribute it and/or modify
%% it under the terms of the GNU General Public License as published by
%% the Free Software Foundation, either version 3 of the License, or
%% (at your option) any later version.
%%
%% This program is distributed in the hope that it will be useful,
%% but WITHOUT ANY WARRANTY; without even the implied warranty of
%% MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
%% GNU General Public License for more details.
%%
%% You should have received a copy of the GNU General Public License
%% along with this program. If not, see <https://www.gnu.org/licenses/>.

%% @doc This module performs test to an actual database.
-module(mysql_tests).

-include_lib("eunit/include/eunit.hrl").

-define(user,     "otptest").
-define(password, "otptest").

-define(create_table_t, <<"CREATE TABLE t ("
                          "  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,"
                          "  bl BLOB,"
                          "  tx TEXT NOT NULL," %% No default value
                          "  f FLOAT,"
                          "  dc DECIMAL(5,3),"
                          "  ti TIME,"
                          "  ts TIMESTAMP,"
                          "  da DATE,"
                          "  c CHAR(2)"
                          ") ENGINE=InnoDB">>).

connect_test() ->
    {ok, Pid} = mysql:start_link([{user, ?user}, {password, ?password}]),
    exit(Pid, normal).

query_test_() ->
    {setup,
     fun () ->
         {ok, Pid} = mysql:start_link([{user, ?user}, {password, ?password}]),
         ok = mysql:query(Pid, <<"DROP DATABASE IF EXISTS otptest">>),
         ok = mysql:query(Pid, <<"CREATE DATABASE otptest">>),
         ok = mysql:query(Pid, <<"USE otptest">>),
         Pid
     end,
     fun (Pid) ->
         ok = mysql:query(Pid, <<"DROP DATABASE otptest">>),
         exit(Pid, normal)
     end,
     {with, [fun basic_queries/1,
             fun text_protocol/1,
             fun binary_protocol/1,
             fun float_rounding/1,
             fun time/1,
             fun microseconds/1]}}.

basic_queries(Pid) ->

    %% warning count
    ?assertEqual(ok, mysql:query(Pid, <<"DROP TABLE IF EXISTS foo">>)),
    ?assertEqual(1, mysql:warning_count(Pid)),

    %% SQL parse error
    ?assertMatch({error, {1064, <<"42000">>, <<"You have an erro", _/binary>>}},
                 mysql:query(Pid, <<"FOO">>)),

    %% Simple resultset with various types
    ?assertEqual({ok, [<<"i">>, <<"s">>], [[42, <<"foo">>]]},
                 mysql:query(Pid, <<"SELECT 42 AS i, 'foo' AS s;">>)),

    ok.

text_protocol(Pid) ->
    ok = mysql:query(Pid, ?create_table_t),
    ok = mysql:query(Pid, <<"INSERT INTO t (bl, f, dc, ti, ts, da, c)"
                            " VALUES ('blob', 3.14, 3.14, '00:22:11',"
                            " '2014-11-03 00:22:24', '2014-11-03',"
                            " NULL)">>),
    ?assertEqual(1, mysql:warning_count(Pid)), %% tx has no default value
    ?assertEqual(1, mysql:insert_id(Pid)),     %% auto_increment starts from 1
    ?assertEqual(1, mysql:affected_rows(Pid)),

    %% select
    {ok, Columns, Rows} = mysql:query(Pid, <<"SELECT * FROM t">>),
    ?assertEqual([<<"id">>, <<"bl">>, <<"tx">>, <<"f">>, <<"dc">>, <<"ti">>,
                  <<"ts">>, <<"da">>, <<"c">>], Columns),
    ?assertEqual([[1, <<"blob">>, <<>>, 3.14, <<"3.140">>, {0, {0, 22, 11}},
                   {{2014, 11, 03}, {00, 22, 24}}, {2014, 11, 03}, null]],
                 Rows),

    %% TODO:
    %% * More types: BIT, SET, ENUM, GEOMETRY
    %% * TIME with negative hours
    %% * TIME with more than 2 digits in hour.
    %% * TIME with microseconds
    %% * Negative TIME

    ok = mysql:query(Pid, <<"DROP TABLE t">>).

binary_protocol(Pid) ->
    ok = mysql:query(Pid, ?create_table_t),
    %% The same queries as in the text protocol. Expect the same results.
    {ok, Ins} = mysql:prepare(Pid, <<"INSERT INTO t (bl, f, dc, ti, ts, da, c)"
                                     " VALUES (?, ?, ?, ?, ?, ?, ?)">>),

    ok = mysql:execute(Pid, Ins, [<<"blob">>, 3.14, <<"3.14">>,
                                  {0, {0, 22, 11}}, 
                                  {{2014, 11, 03}, {0, 22, 24}},
                                  {2014, 11, 03}, null]),

    %% TODO: Put the expected result in a macro to make sure they are identical
    %% for the text and the binary protocol tests.

    {ok, Stmt} = mysql:prepare(Pid, <<"SELECT * FROM t WHERE id=?">>),
    {ok, Columns, Rows} = mysql:execute(Pid, Stmt, [1]),
    ?assertEqual([<<"id">>, <<"bl">>, <<"tx">>, <<"f">>, <<"dc">>, <<"ti">>,
                  <<"ts">>, <<"da">>, <<"c">>], Columns),
    ?assertEqual([[1, <<"blob">>, <<>>, 3.14, <<"3.140">>,
                   {0, {0, 22, 11}},
                   {{2014, 11, 03}, {00, 22, 24}}, {2014, 11, 03}, null]],
                 Rows),

    %% TODO: Both send and receive the following values:
    %% * Values for all types
    %% * Negative numbers for all integer types
    %% * Integer overflow
    %% * TIME with more than 2 digits in hour.
    %% * TIME with microseconds
    %% * Negative TIME

    ok = mysql:query(Pid, <<"DROP TABLE t">>).

float_rounding(Pid) ->
    %% This is to make sure we get the same values for 32-bit FLOATs in the text
    %% and binary protocols for ordinary queries and prepared statements
    %% respectively.
    %%
    %% MySQL rounds to 6 significant digits when "printing" floats over the
    %% text protocol. When we receive a float on the binary protocol, we round
    %% it in the same way to match what MySQL does on the text protocol. This
    %% way we should to get the same values regardless of which protocol is
    %% used.

    %% Table for testing floats
    ok = mysql:query(Pid, "CREATE TABLE f (f FLOAT)"),

    %% Prepared statements
    {ok, Insert} = mysql:prepare(Pid, "INSERT INTO f (f) VALUES (?)"),
    {ok, Select} = mysql:prepare(Pid, "SELECT f FROM f"),

    %% [{Input, Expected}]
    TestData = [{1.0, 1.0}, {3.14, 3.14}, {0.2, 0.2},
                {0.20082111, 0.200821}, {0.000123456789, 0.000123457},
                {33.3333333, 33.3333}, {-33.2233443322, -33.2233},
                {400.0123, 400.012}, {1000.1234, 1000.12},
                {999.00009, 999.0},
                {1234.5678, 1234.57}, {68888.8888, 68888.9},
                {123456.789, 123457.0}, {7654321.0, 7654320.0},
                {80001111.1, 80001100.0}, {987654321.0, 987654000.0},
                {-123456789.0, -123457000.0},
                {2.12345111e-23, 2.12345e-23}, {-2.12345111e-23, -2.12345e-23},
                {2.12345111e23, 2.12345e23}, {-2.12345111e23, -2.12345e23}],
    lists:foreach(fun ({Input, Expected}) ->
                      %% Insert using binary protocol (sending it as a double)
                      ok = mysql:execute(Pid, Insert, [Input]),

                      %% Text (plain query)
                      {ok, _, [[Value]]} = mysql:query(Pid, "SELECT f FROM f"),
                      ?assertEqual(Expected, Value),

                      %% Binary (prepared statement)
                      {ok, _, [[BinValue]]} = mysql:execute(Pid, Select, []),
                      ?assertEqual(Expected, BinValue),

                      %% cleanup before the next test
                      ok = mysql:query(Pid, "DELETE FROM f")
                end,
                TestData),
    ok = mysql:query(Pid, "DROP TABLE f").

%% Test TIME value representation. There are a few things to check.
time(Pid) ->
    ok = mysql:query(Pid, "CREATE TABLE tm (tm TIME)"),
    {ok, Insert} = mysql:prepare(Pid, "INSERT INTO tm VALUES (?)"),
    {ok, Select} = mysql:prepare(Pid, "SELECT tm FROM tm"),
    lists:foreach(
        fun ({Value, Text}) ->
            %% --- Insert using text query ---
            ok = mysql:query(Pid, ["INSERT INTO tm VALUES ('", Text, "')"]),
            %% Select using prepared statement
            ?assertEqual({ok, [<<"tm">>], [[Value]]},
                         mysql:execute(Pid, Select, [])),
            %% Select using plain query
            ?assertEqual({ok, [<<"tm">>], [[Value]]},
                         mysql:query(Pid, "SELECT tm FROM tm")),
            %% Empty table
            ok = mysql:query(Pid, "DELETE FROM tm"),
            %% --- Insert using prepared statement ---
            ok = mysql:execute(Pid, Insert, [Value]),
            %% Select using prepared statement
            ?assertEqual({ok, [<<"tm">>], [[Value]]},
                         mysql:execute(Pid, Select, [])),
            %% Select using plain query
            ?assertEqual({ok, [<<"tm">>], [[Value]]},
                         mysql:query(Pid, "SELECT tm FROM tm")),
            %% Empty table
            ok = mysql:query(Pid, "DELETE FROM tm"),
            ok
        end,
        [{{0, {10, 11, 12}},   "10:11:12"},
         {{5, {0, 0, 1}},     "120:00:01"},
         {{-1, {23, 59, 59}}, "-00:00:01"},
         {{-1, {23, 59, 0}},  "-00:01:00"},
         {{-1, {23, 0, 0}},   "-01:00:00"},
         {{-1, {0, 0, 0}},    "-24:00:00"},
         {{-5, {10, 0, 0}},  "-110:00:00"}]
    ),
    ok = mysql:query(Pid, "DROP TABLE tm").

microseconds(Pid) ->
    %% Check whether we have the required version for this testcase.
    {ok, _, [[VersionBin]]} = mysql:query(Pid, <<"SELECT @@version">>),
    Version = lists:map(fun binary_to_integer/1,
                        binary:split(VersionBin, <<".">>, [global])),
    if
        Version >= [5, 6, 4] ->
            ok = mysql:query(Pid, "CREATE TABLE m (t TIME(6))"),
            SelectTime = "SELECT t FROM m",
            {ok, SelectStmt} = mysql:prepare(Pid, SelectTime),
            {ok, InsertStmt} = mysql:prepare(Pid, "INSERT INTO m VALUES (?)"),
            %% Positive time, insert using plain query
            E1 = {0, {23, 59, 57.654321}},
            ok = mysql:query(Pid,
                             <<"INSERT INTO m VALUES ('23:59:57.654321')">>),
            ?assertEqual({ok, [<<"t">>], [[E1]]},
                         mysql:query(Pid, SelectTime)),
            ?assertEqual({ok, [<<"t">>], [[E1]]},
                         mysql:execute(Pid, SelectStmt, [])),
            ok = mysql:query(Pid, "DELETE FROM m"),
            %% The same, but insert using prepared stmt
            ok = mysql:execute(Pid, InsertStmt, [E1]),
            ?assertEqual({ok, [<<"t">>], [[E1]]},
                         mysql:query(Pid, SelectTime)),
            ?assertEqual({ok, [<<"t">>], [[E1]]},
                         mysql:execute(Pid, SelectStmt, [])),
            ok = mysql:query(Pid, "DELETE FROM m"),
            %% Negative time
            E2 = {-1, {23, 59, 57.654321}},
            ok = mysql:query(Pid,
                             <<"INSERT INTO m VALUES ('-00:00:02.345679')">>),
            ?assertEqual({ok, [<<"t">>], [[E2]]},
                         mysql:query(Pid, SelectTime)),
            ?assertEqual({ok, [<<"t">>], [[E2]]},
                         mysql:execute(Pid, SelectStmt, [])),
            ok = mysql:query(Pid, "DELETE FROM m"),
            %% The same, but insert using prepared stmt
            ok = mysql:execute(Pid, InsertStmt, [E2]),
            ?assertEqual({ok, [<<"t">>], [[E2]]},
                         mysql:query(Pid, SelectTime)),
            ?assertEqual({ok, [<<"t">>], [[E2]]},
                         mysql:execute(Pid, SelectStmt, [])),
            ok = mysql:query(Pid, "DROP TABLE m"),
            %% Datetime
            Q3 = <<"SELECT TIMESTAMP '2014-11-23 23:59:57.654321' AS t">>,
            E3 = [[{{2014, 11, 23}, {23, 59, 57.654321}}]],
            ?assertEqual({ok, [<<"t">>], E3}, mysql:query(Pid, Q3)),
            {ok, S3} = mysql:prepare(Pid, Q3),
            ?assertEqual({ok, [<<"t">>], E3}, mysql:execute(Pid, S3, [])),
            ok;
        true ->
            error_logger:info_msg("Skipping microseconds test. Microseconds are"
                                  " not available on MySQL version ~s. Required"
                                  " version is >= 5.6.4.",
                                  [VersionBin])
    end.