sql_bridge

%% vim: ts=4 sw=4 et sts=4
-module(sql_bridge_test).
-include_lib("eunit/include/eunit.hrl").
-compile(nowarn_export_all).
-compile(export_all).

%% This is just a shortcut to prevent dialyzer throwing errors for the
%% dynamically generated `db` module.
-define(DB, sql_bridge).

-define(P(X), (sql_bridge_utils:create_placeholder(X))).
-define(P1, ?P(1)).
-define(P2, ?P(2)).
-define(P3, ?P(3)).
-define(P4, ?P(4)).
-define(P5, ?P(5)).
-define(P6, ?P(6)).
-define(P7, ?P(7)).
-define(P8, ?P(8)).
-define(P9, ?P(9)).

-define(MYSQL_HOST, os:getenv("MYSQLHOST", "localhost")).
-define(PG_HOST, os:getenv("PGHOST", "localhost")).

mysql_otp_test_() ->
    {setup,
     %% We are intentionally testing postgres-style token replacements on mysql
     %% (so 'postgres' is not a typo here)
     fun() -> gen_setup(sql_bridge_mysql_otp, postgres, ?MYSQL_HOST, 3306) end,
     fun mysql_otp_cleanup/1,
     fun main_tests/1
    }.

mysql_otp_trans_test_() ->
    {setup,
     fun() -> gen_setup(sql_bridge_mysql_otp, mysql, ?MYSQL_HOST, 3306) end,
     fun mysql_otp_cleanup/1,
     fun trans_tests/1
    }.

epgsql_trans_test_() ->
    {setup,
     fun() -> gen_setup(sql_bridge_epgsql, postgres, ?PG_HOST, 5432) end,
     fun epgsql_cleanup/1,
     fun main_tests/1
    }.

epgsql_test_() ->
    {setup,
     %% We are intentionally testing mysql-style token replacements on pgsql
     %% (so 'mysql' is not a typo here)
     fun() -> gen_setup(sql_bridge_epgsql, mysql, ?PG_HOST, 5432) end,
     fun epgsql_cleanup/1,
     fun trans_tests/1
    }.


gen_setup(Adapter, ReplacementType, Host, Port) ->
    error_logger:info_msg("Starting Adapter: ~p~n",[Adapter]),
    application:load(sql_bridge),
    application:set_env(sql_bridge, adapter, Adapter),
    application:set_env(sql_bridge, port, Port),
    application:set_env(sql_bridge, user, "sql_bridge_user"),
    application:set_env(sql_bridge, pass, "sql_bridge_test_password"),
    application:set_env(sql_bridge, host, Host),
    application:set_env(sql_bridge, lookup, sql_bridge_test),
    application:set_env(sql_bridge, replacement_token_style, ReplacementType),
    application:set_env(sql_bridge, stringify_binaries, true),
    sql_bridge:start(),
    case Adapter of
        sql_bridge_epgsql -> set_feature(uuid, true);
        sql_bridge_mysql_otp -> check_mysql_feature(uuid)
    end,
    ?DB:q("delete from fruit").

check_mysql_feature(uuid) ->
    Vsn = ?DB:fffr("select @@version"),
    io:format("MySQL Version: ~p", [Vsn]),

    Enabled = case re:run(Vsn, "^(\\d+)\\.(\\d+)\\.(\\d+)-mariadb", [caseless, {capture, all_but_first, list}]) of
        {match, [Maj, Min, Pat]} ->
            Major = list_to_integer(Maj),
            Minor = list_to_integer(Min),
            Patch = list_to_integer(Pat),
            case {Major, Minor, Patch} >= {10, 7, 0} of
                true ->
                    io:format("UUID Support Enabled~n"),
                    true;
                false ->
                    io:format("UUID Support Disabled (10.7+ Required~n"),
                    false
            end;
        nomatch ->
            io:format("UUID Tests Disabled~n"),
            false
    end,
    set_feature(uuid, Enabled).

set_feature(Feature, TF) ->
    persistent_term:put({sql_bridge_feature, Feature}, TF).

get_feature(Feature) ->
    persistent_term:get({sql_bridge_feature, Feature}).

get_feature_uuid() ->
    get_feature(uuid).

epgsql_cleanup(_) ->
    application:stop(epgsql),
    application:stop(sql_bridge),
    ok.

mysql_otp_cleanup(_) ->
    application:stop(mysql),
    application:stop(sql_bridge),
    ok.

trans_tests(_) ->
    LookupPid = erlang:spawn(fun lookup_loop/0),
    [
        {inorder, [
            % Extending the timeout from 15000 to 20000, in case github actions was just running slowly?
            {timeout, 20000, [
                {inparallel, [
                    ?_assert(test_trans(LookupPid, 1)),
                    ?_assert(test_trans(LookupPid, 2)),
                    ?_assert(test_trans(LookupPid, 3)),
                    ?_assert(test_trans(LookupPid, 4)),
                    ?_assert(test_trans(LookupPid, 5)),
                    ?_assert(test_trans(LookupPid, 6)),
                    ?_assert(test_trans(LookupPid, 7)),
                    ?_assert(test_trans(LookupPid, 8)),
                    ?_assert(test_trans(LookupPid, 9)),
                    ?_assert(test_trans(LookupPid, 10)),
                    ?_assert(test_trans(LookupPid, 11)),
                    ?_assert(test_trans(LookupPid, 12)),
                    ?_assertNot(test_trans(LookupPid, 50, rollback)),
                    ?_assertNot(test_trans(LookupPid, 60, rollback)),
                    ?_assertNot(test_trans(LookupPid, 70, rollback))
                ]}
            ]},
            ?_assertEqual(12, ?DB:fffr("select count(*) from fruit")),
            ?_assertEqual(12, ?DB:fffr(["select count(*) from fruit where quantity in (",?DB:encode_list([1,2,3,4,5,6,7,8,9,10,11,12]),")"]))
        ]}
    ].

lookup_loop() ->
    lookup_loop([]).

lookup_loop(Fruitids) ->
    receive
        {register, Fruitid} ->
            lookup_loop(Fruitids ++ [Fruitid]);
        {lookup, Pid} ->
            %% Get the first fruit from thee list
            [Fruitid|Rest] = Fruitids,
            %% send it back to the user
            Pid ! Fruitid,
            %% Put the found fruitid to the end of the list (so we don't grab it again right away)
            NewFruitids = Rest ++ [Fruitid],
            %% then loop again
            lookup_loop(NewFruitids)
    after
        %% if no messages received for 10 seconds, we can safely die1
        10000 -> die
    end.

register_fruitid(LookupPid, Fruitid) ->
    LookupPid ! {register, Fruitid}.

lookup_fruitid(LookupPid, NotFruitid) ->
    LookupPid ! {lookup, self()},
    receive
        NotFruitid ->
            %% We happened to retrieve ourselves. We don't want that. Try again (the queue will change with each request)
            lookup_fruitid(LookupPid, NotFruitid);
        Fruitid ->
            Fruitid
    after
        10000 -> throw(not_received)
    end.

test_trans(LookupPid, Quantity) ->
    test_trans(LookupPid, Quantity, commit).

-ifdef(SHOW_TRANS).
-define(TRANS_STATUS(Msg, Args), trans_status(StartTime, FruitName, Msg, Args)).
-define(TRANS_STATUS(Msg), ?TRANS_STATUS(Msg, [])).
-else.
-define(TRANS_STATUS(Msg, Args), ok).
-define(TRANS_STATUS(Msg), ok).
-endif.

test_trans(LookupPid, Quantity, CommitOrRollback) ->
    FruitName = "Fruit-" ++ integer_to_list(Quantity),
    SleepModifier = Quantity * 10,
    _AddedFruitid = ?DB:trans(fun() ->
        FirstSleep = 1000 - SleepModifier,
        SecondSleep = 1500 + SleepModifier,
        ThirdSleep = 2000,
        %StartTime = os:timestamp(),
        ?TRANS_STATUS("Transaction Started. Sleeping for ~pms", [FirstSleep]),
        timer:sleep(FirstSleep),
        ?TRANS_STATUS("Woke up. Verifying fruit table is empty"),
        0=?DB:fffr("select count(*) from fruit"),
        ?TRANS_STATUS("Inserting ~s", [FruitName]),
        Fruitid = ?DB:qi(["insert into fruit(fruit, quantity) values(",?P1,",",?P2,")"], [FruitName, Quantity]),
        ?TRANS_STATUS("Inserted (fruitid=~p). Registering with tracker process",[Fruitid]),
        register_fruitid(LookupPid, Fruitid),
        ?TRANS_STATUS("Registered. Verifying that Friutid=~p exists in transaction.", [Fruitid]),
        true=?DB:exists(fruit, Fruitid),
        ?TRANS_STATUS("fruitid=~p exists. Counting records in table (should only be 1)", [Fruitid]),
        1=?DB:fffr("select count(*) from fruit"),
        ?TRANS_STATUS("Verified. Sleeping for ~pms", [SecondSleep]),
        timer:sleep(SecondSleep),
        ?TRANS_STATUS("Woke up. Getting a random other fruit that was inserted in another transaction."),
        OtherTranFruitid = lookup_fruitid(LookupPid, Fruitid),
        ?TRANS_STATUS("Retrieved other fruitid=~p. Verifying its validity.",[OtherTranFruitid]),
        true=is_integer(OtherTranFruitid),
        true=(Fruitid=/=OtherTranFruitid),
        ?TRANS_STATUS("Verified. Verifying that fruitid=~p does not yet exists in this transaction.",[OtherTranFruitid]),
        false=?DB:exists(fruit, OtherTranFruitid),
        ?TRANS_STATUS("Verified. Sleeping for ~pms.",[ThirdSleep]),
        timer:sleep(ThirdSleep),
        ?TRANS_STATUS("Woke up. Now checking if we should crash or return."),
        %% this will crash if CommitOrRollback=rollback, causing the
        %% transaction to be rolled back completely (or it should be, anyway)
        commit=CommitOrRollback,
        Fruitid
    end),
    ?DB:qexists(["select * from fruit where quantity=",?P1], [Quantity]).

%sleep_random(Min, Max) ->
%    Time = crypto:rand_uniform(Min, Max),
%    timer:sleep(Time).

trans_status(StartTime, Tag, Msg, Args) ->
    Now = os:timestamp(),
    Microsec = timer:now_diff(Now, StartTime),
    ElapsedMS = Microsec div 1000,
    Args2 = [self(), Tag, ElapsedMS] ++ Args,
    Msg2 = "(~p) Trans Update [Tag = ~p] (~pms Elapsed): " ++ Msg ++ "\n",
    logger:notice(Msg2, Args2).


main_tests(_) ->
    [
     ?_assertEqual([], ?DB:q("select * from fruit")),
     ?_assertEqual([], ?DB:tq("select * from fruit")),
     ?_assertEqual([], ?DB:dq("select * from fruit")),
     ?_assertEqual([], ?DB:mq("select * from fruit")),
     ?_assertEqual([], ?DB:plq("select * from fruit")),
     ?_assertEqual(not_found, ?DB:fr("select * from fruit")),
     ?_assertEqual(not_found, ?DB:tfr("select * from fruit")),
     ?_assertEqual(not_found, ?DB:mfr("select * from fruit")),
     ?_assertEqual(not_found, ?DB:plfr("select * from fruit")),
     ?_assertEqual(not_found, ?DB:dfr("select * from fruit")),

     ?_assertMatch([fruitid, fruit, description, quantity, picture, some_float], ?DB:table_fields(fruit)),
     ?_assert(is_nonzero_integer(?DB:qi(["insert into fruit(fruit, quantity, some_float) values(", ?P1, ",", ?P2, ",", ?P3,")"], ["apple", 5, 10.1]))),
     ?_assertEqual(undefined, ?DB:fffr("select description from fruit where fruit='apple'")),
     ?_assertEqual(5, ?DB:fffr(["select quantity from fruit where fruit=",?P1 ], [apple])),
     ?_assertEqual("apple", ?DB:fffr(["select fruit from fruit where quantity=", ?P1], [5])),
     ?_assertEqual("apple", ?DB:fffr(["select fruit from fruit where quantity=", ?P1], ["5"])),
     ?_assertEqual("apple", ?DB:fffr(["select fruit from fruit where quantity=", ?P1], [<<"5">>])),
     ?_assert(is_nonzero_integer(?DB:pl(fruit, [{fruitid, 0}, {fruit, <<"banana">>}, {quantity, 100}, {description, "long and yellow"}, {some_float, 6.1}]))),
     ?_assert(is_float(?DB:fffr("select sum(some_float) from fruit"))),
     ?_assertMatch("long and yellow", ?DB:field(fruit, description, fruit, "banana")),

     ?_assertEqual([["apple", 5], ["banana", 100]], ?DB:q("select fruit, quantity from fruit order by fruit")),
     ?_assertEqual(["apple", 5], ?DB:fr("select fruit, quantity from fruit order by fruit")),

     ?_assertEqual([{"apple", 5}, {"banana", 100}], ?DB:tq("select fruit, quantity from fruit order by fruit")),
     ?_assertEqual({"apple", 5}, ?DB:tfr("select fruit, quantity from fruit order by fruit")),

     ?_assertEqual([[{fruit, "apple"}, {quantity, 5}], [{fruit, "banana"}, {quantity, 100}]], ?DB:plq("select fruit, quantity from fruit order by fruit")),
     ?_assertEqual([{fruit, "apple"}, {quantity, 5}], ?DB:plfr("select fruit, quantity from fruit order by fruit")),

     ?_assertEqual("apple", dict:fetch(fruit, ?DB:dfr("select fruit, quantity from fruit order by fruit"))),

     ?_assertEqual([#{fruit=>"apple", quantity=>5}, #{fruit=>"banana", quantity=>100}], ?DB:mq("select fruit, quantity from fruit order by fruit")),
     ?_assertEqual(#{fruit=>"apple", quantity=>5},  ?DB:mfr("select fruit, quantity from fruit order by fruit")),
     ?_assertEqual(["apple", "banana"], ?DB:ffl("select fruit from fruit order by fruit")),
     ?_assert(?DB:qexists(["select * from fruit where fruit=",?P1], [banana])),
     ?_assert(?DB:qexists(["select * from fruit where fruit=",?P1], ["apple"])),
     ?_assertNot(?DB:qexists(["select * from fruit where fruit=",?P1], [<<"watermelon">>])),
     ?_assertEqual(#{fruit=>"orange", quantity=>5, description=>"oranges are orange"}, update_apple_to_orange()),
     ?_assertEqual("berry", test_insert_id()),
     ?_assertEqual(1, ?DB:delete(fruit, fruit, "orange")),
     ?_assert(test_exists("banana")),
     ?_assertNot(?DB:exists(fruit, fruit, "banana-fake")),
     ?_assertNot(test_id_delete()),
     ?_assert(test_string("ﻦﺤﻧ ﺫﺎﻬﺑﻮﻧ ﻒﻳ ﺡﺎﺟﺓ ﺈﻟﻯ ﻕﺍﺮﺑ ﺄﻜﺑﺭ")),
     ?_assert(test_string("我们将需要更大的船")),
     ?_assert(test_string("Budeme potřebovat větší loď")),
     ?_assert(test_string("ჩვენ ვაპირებთ, რომ უნდა დიდი ნავი")),
     ?_assert(test_string("Мы собираемся нуждаться в большей лодку")),
     ?_assert(test_string("testy'pants")),
     ?_assert(test_string("'+\"!@#$%^&*()\\//\\//';[]<>./-=-=+")),
     ?_assert(test_encode_list(["'+\"", "!@#$'^&%", "//\\//\\", "blah","123","-=--=-!+'\"'''''''''''''''''"])),
     ?_assertEqual({some, crazy,"term"}, ?DB:decode64(?DB:encode64({some, crazy,"term"}))),
     ?_assertMatch([_, _], ?DB:q("select * from fruit " ++ ?DB:limit_clause(2, 1))),
     ?_assertMatch([_, _], ?DB:q("select * from fruit " ++ ?DB:limit_clause(2, -1))),
     ?_assertMatch([_], ?DB:q("select * from fruit " ++ ?DB:limit_clause(-123, 5))),
     ?_assertEqual(1.1, test_float(1.1)),
     ?_assertEqual(12345.5, test_float(12345.5)),
     ?_assertEqual(12.5, test_decimal(12.5)),
     ?_assertEqual(undefined, test_null()),
     ?_assertEqual("2016-12-31", test_date("2016-12-31")),
     ?_assertEqual("23:00:00", test_time("23:00:00")),
     ?_assertEqual("2016-12-31 23:00:00", test_datetime("2016-12-31 23:00:00")),
     ?_assertEqual(fruitid, ?DB:primary_key(fruit)),
     ?_assert(?DB:is_auto_increment(fruit, fruitid)),
     ?_assertNot(?DB:is_auto_increment(fruit, fruit)),
     ?_assert(test_key_variety(other_auto, fun is_nonzero_integer/1)),
     ?_assert(test_key_variety(other_int, fun is_nonzero_integer/1)),
     ?_assert(test_key_variety(other_string, fun is_string/1)),
     ?_assert(maybe_test(fun get_feature_uuid/0, fun() -> test_key_variety(other_uuid, fun is_uuid/1) end))
    ].



is_nonzero_integer(X) ->
    is_integer(X) andalso X=/=0.

is_string(X) ->
    case is_string_(X) of
        true -> true;
        false ->
            io:format("Term is not a string: ~p~n",[X]),
            false
    end.

is_string_(X) when is_list(X) ->
    lists:all(fun(C) -> is_alphanumeric(C) end, X);
is_string_(_) ->
    false.

is_alphanumeric(C) ->
    (C >= $0 andalso C =< $9) orelse
    (C >= $a andalso C =< $z) orelse
    (C >= $A andalso C =< $Z).

is_uuid(X) when is_list(X); is_binary(X) ->
    U = uuid:string_to_uuid(X),
    case uuid:is_uuid(U) of
        true -> true;
        false ->
            io:format("Term is not a valid UUID: ~p~n", [X]),
            false
    end.

test_decimal(V) ->
    test_in_out_other(my_decimal, V).

test_date(V) ->
    test_in_out_other(my_date, V).

test_time(V) ->
    test_in_out_other(my_time, V).

test_datetime(V) ->
    test_in_out_other(my_datetime, V).

test_null() ->
    ID = ?DB:pl(other, [{otherid, 0}, {my_decimal, 123.5}]),
    ?DB:field(other, my_date, ID).

maybe_test(CondFun, TestFun) ->
    case CondFun() of
        true -> TestFun();
        false -> true
    end.

test_key_variety(Table, TypeFun) ->
    ID = ?DB:save(Table, [{some_text, "random_text"}]),
    true=TypeFun(ID),
    ?DB:exists(Table, ID).

test_in_out_other(Field, V) ->
    ID = ?DB:pl(other, [{Field, V}]),
    ?DB:field(other, Field, ID).

test_float(Val) ->
    Fruitid = ?DB:pl(fruit, [{some_float, Val}]),
    ?DB:field(fruit, some_float, Fruitid).

update_apple_to_orange() ->
    Fruitid = ?DB:fffr(["Select fruitid from fruit where fruit=",?P1], ["apple"]),
    New = [
        {fruitid, Fruitid},
        {fruit, "orange"},
        {description, "oranges are orange"}
    ],
    ?DB:pl(fruit, New),
    ?DB:mfr(["Select fruit, quantity, description from fruit where fruitid=", ?P1], [Fruitid]).

test_insert_id() ->
    Fruitid = ?DB:qi("insert into fruit(fruit, quantity) values('berry', 200)"),
    ?DB:field(fruit, fruit, Fruitid).

test_exists(Fruit) ->
    Fruitid = ?DB:fffr(["select fruitid from fruit where fruit=",?P1], [Fruit]),
    ?DB:exists(fruit, Fruitid).

test_id_delete() ->
    Fruitid = ?DB:fffr(["select fruitid from fruit where fruit=",?P1], ["banana"]),
    ?DB:delete(fruit, Fruitid),
    ?DB:exists(fruit, Fruitid).

test_string(Str) ->
    Fruitid = ?DB:pl(fruit, [{fruitid, 0}, {fruit, "new"}, {description, Str}]),
    Str == ?DB:field(fruit, description, Fruitid).

test_encode_list(List) ->
    Fruitids = lists:map(fun(Fruit) ->
        ?DB:pl(fruit, [{fruit, Fruit}])
    end, List),
    Fruitids = ?DB:ffl(["select fruitid from fruit where fruitid in (",?DB:encode_list(Fruitids),") order by fruitid"]),
    Fruitids = ?DB:ffl(["select fruitid from fruit where fruit in (", ?DB:encode_list(List), ") order by fruitid"]),
    true.