-module(epgsql_pool_SUITE).
%% test needs connection to database
%% and database should be inited with ./testdb_schema.sql
-include("epgsql_pool.hrl").
-include_lib("epgsql/include/epgsql.hrl").
-include_lib("common_test/include/ct.hrl").
-include_lib("eunit/include/eunit.hrl").
-export([all/0,
init_per_suite/1, end_per_suite/1, init_per_testcase/2, end_per_testcase/2,
query_test/1, squery_test/1, transaction_test/1, reconnect_test/1, timeout_test/1,
validate_connection_params_test/1
]).
-define(SELECT_ITEMS_QUERY, "SELECT id, category_id, title, num FROM item ORDER by id ASC").
all() ->
[query_test,
squery_test,
transaction_test,
reconnect_test,
timeout_test,
validate_connection_params_test
].
init_per_suite(Config) ->
application:ensure_all_started(epgsql_pool),
Config.
end_per_suite(Config) ->
application:stop(epgsql_pool),
Config.
init_per_testcase(_, Config) ->
Params = #epgsql_connection_params{host = "localhost", port = 5432, username = "test", password = "test", database = "testdb"},
{ok, _} = epgsql_pool:start(my_pool, 5, 10, Params),
epgsql_pool:query(my_pool, "TRUNCATE TABLE item"),
epgsql_pool:query(my_pool, "TRUNCATE TABLE category CASCADE"),
Config.
end_per_testcase(_, Config) ->
ok = epgsql_pool:stop(my_pool),
Config.
query_test(Config) ->
{ok, 3, _, Ids} = epgsql_pool:query(my_pool,
"INSERT INTO category (title) "
"VALUES ('cat 1'), ('cat 2'), ('cat 3') "
"RETURNING id"),
WaitForRows = lists:map(fun({{Id}, Title}) -> {Id, Title} end,
lists:zip(Ids, [<<"cat 1">>, <<"cat 2">>, <<"cat 3">>])),
{ok, _, Rows} = epgsql_pool:query(my_pool, "SELECT id, title FROM category ORDER by id ASC"),
ct:log("Rows ~p", [Rows]),
?assertEqual(WaitForRows, Rows),
[{Id1}, {Id2} | _] = Ids,
{ok, _, Rows2} = epgsql_pool:query(my_pool, "SELECT id, title FROM category WHERE id = $1 OR id = $2 ORDER BY id ASC", [Id1, Id2]),
ct:log("Rows2 ~p", [Rows2]),
?assertEqual([{Id1, <<"cat 1">>}, {Id2, <<"cat 2">>}], Rows2),
{error, Error} = epgsql_pool:query(my_pool, "SELECT id, title FROM some_table"),
ct:log("Error:~p", [Error]),
?assertMatch(#error{severity = error, message = <<"relation \"some_table\" does not exist">>}, Error),
ok.
squery_test(Config) ->
{ok, 3, _, Ids} = epgsql_pool:squery(my_pool,
"INSERT INTO category (title) "
"VALUES ('cat 1'), ('cat 2'), ('cat 3') "
"RETURNING id"),
WaitForRows = lists:map(fun({{Id}, Title}) -> {Id, Title} end,
lists:zip(Ids, [<<"cat 1">>, <<"cat 2">>, <<"cat 3">>])),
{ok, _, Rows} = epgsql_pool:squery(my_pool, "SELECT id, title FROM category ORDER by id ASC"),
ct:log("Rows ~p", [Rows]),
?assertEqual(WaitForRows, Rows),
?assertMatch([{ok, _, [{<<"1">>}]}, {ok, _, [{<<"2">>}]}],
epgsql_pool:squery(my_pool, "SELECT 1; SELECT 2;")),
?assertMatch({error, #error{severity = error, message = <<"relation \"some_table\" does not exist">>}},
epgsql_pool:squery(my_pool, "SELECT * FROM some_table")),
Config.
transaction_test(Config) ->
{FirstCatId, CatIds2, ItemIds2} =
epgsql_pool:transaction(my_pool,
fun(Worker) ->
ct:log("worker:~p", [Worker]),
{ok, 3, _, CatIds0} =
epgsql_pool:query(Worker,
"INSERT INTO category (title) "
"VALUES ('cat 4'), ('cat 5'), ('cat 6') "
"RETURNING id"),
CatIds1 = lists:map(fun({Cid}) -> Cid end, CatIds0),
CatId = hd(CatIds1),
{ok, 2, _, ItemIds0} =
epgsql_pool:query(Worker,
"INSERT INTO item (category_id, title, num) "
"VALUES ($1, 'item 1', 5), ($1, 'item 2', 7) "
"RETURNING id", [CatId]),
ItemIds1 = lists:map(fun({Iid}) -> Iid end, ItemIds0),
{CatId, CatIds1, ItemIds1}
end),
WaitForCats = lists:zip(CatIds2, [<<"cat 4">>, <<"cat 5">>, <<"cat 6">>]),
{ok, _, CatRows} = epgsql_pool:query(my_pool, "SELECT id, title FROM category ORDER by id ASC"),
ct:log("CatRows ~p", [CatRows]),
?assertEqual(WaitForCats, CatRows),
WaitForItems = lists:map(fun({ItemId, {Title, Num}}) -> {ItemId, FirstCatId, Title, Num} end,
lists:zip(ItemIds2, [{<<"item 1">>, 5}, {<<"item 2">>, 7}])),
{ok, _, ItemRows} = epgsql_pool:query(my_pool, ?SELECT_ITEMS_QUERY),
ct:log("ItemRows ~p", [ItemRows]),
?assertEqual(WaitForItems, ItemRows),
try
epgsql_pool:transaction(my_pool,
fun(Worker) ->
ct:log("worker:~p", [Worker]),
{ok, 2} =
epgsql_pool:query(Worker,
"INSERT INTO item (category_id, title, num) "
"VALUES ($1, 'item 3', 55), ($1, 'item 4', 77) ",
[FirstCatId]),
{ok, _, ItemRows2} = epgsql_pool:query(Worker, ?SELECT_ITEMS_QUERY),
ct:log("ItemRows2 ~p", [ItemRows2]),
?assertMatch([{_, FirstCatId, <<"item 1">>, 5},
{_, FirstCatId, <<"item 2">>, 7},
{_, FirstCatId, <<"item 3">>, 55},
{_, FirstCatId, <<"item 4">>, 77}],
ItemRows2),
throw(cancel_transaction)
end)
catch
throw:cancel_transaction -> ok
end,
%% items not changes after calcelled transaction
{ok, _, ItemRows} = epgsql_pool:query(my_pool, ?SELECT_ITEMS_QUERY),
ok.
reconnect_test(Config) ->
Worker = pooler:take_member(my_pool, 1000),
[state, my_pool, #epgsql_connection{sock = Sock1} | _]= tuple_to_list(sys:get_state(Worker)),
ct:log("Worker: ~p, sock: ~p", [Worker, Sock1]),
R1 = epgsql_pool:query(Worker, ?SELECT_ITEMS_QUERY),
ct:log("first query ~p", [R1]),
{ok, _, []} = R1,
ct:log("~p close_connection", [Sock1]),
exit(Sock1, close_connection),
R2 = epgsql_pool:query(Worker, "select * from item"),
ct:log("second query goes immediatelly ~p", [R2]),
{error, no_connection} = R2,
timer:sleep(50),
R3 = epgsql_pool:query(Worker, "select * from item"),
ct:log("third query goes after 50 ms ~p", [R3]),
{error, no_connection} = R3,
timer:sleep(150),
R4 = epgsql_pool:query(Worker, "select * from item"),
ct:log("fouth query goes after 200 ms ~p", [R4]),
{ok, _, []} = R4,
[state, my_pool, #epgsql_connection{sock = Sock2} | _]= tuple_to_list(sys:get_state(Worker)),
ct:log("Worker: ~p, sock: ~p", [Worker, Sock2]),
?assertNotEqual(Sock1, Sock2),
ok.
timeout_test(_Config) ->
Res1 = epgsql_pool:query(my_pool, "SELECT pg_sleep(1)", [], [{timeout, 2000}]),
ct:log("Res1:~p", [Res1]),
?assertMatch({ok, _, _}, Res1),
Res2 = epgsql_pool:query(my_pool, "SELECT pg_sleep(1)", [], [{timeout, 500}]),
ct:log("Res2:~p", [Res2]),
?assertEqual({error, timeout}, Res2),
Worker = pooler:take_member(my_pool, 1000),
Res3 = epgsql_pool:query(Worker, "SELECT pg_sleep(100)", [], [{timeout, 500}]),
ct:log("Res3:~p", [Res3]),
?assertEqual({error, timeout}, Res3),
%% check worker and connection able to perform query
Res4 = epgsql_pool:query(Worker, "SELECT * FROM item", [], [{timeout, 500}]),
ct:log("Res4:~p", [Res4]),
?assertMatch({ok, _, _}, Res4),
ok.
validate_connection_params_test(_Config) ->
Params1 = #epgsql_connection_params{host = "localhost", port = 5432,
username = "test", password = "test", database = "testdb"},
Res1 = epgsql_pool:validate_connection_params(Params1),
ct:log("Res1: ~p", [Res1]),
?assertEqual(ok, Res1),
Params2 = #epgsql_connection_params{host = "localhost", port = 5432,
username = "test", password = "some", database = "testdb"},
Res2 = epgsql_pool:validate_connection_params(Params2),
ct:log("Res2: ~p", [Res2]),
?assertEqual({error,invalid_password}, Res2),
Params3 = #epgsql_connection_params{host = "localhost", port = 5432,
username = "test", password = "test", database = "some"},
{error, Res3} = epgsql_pool:validate_connection_params(Params3),
ct:log("Res3: ~p", [Res3]),
?assertMatch(#error{
severity = fatal,
code = <<"3D000">>,
codename = invalid_catalog_name,
message = <<"database \"some\" does not exist">>
}, Res3),
ok.