emysql

%%%-------------------------------------------------------------------
%%% File     : Emysql/test/basics_SUITE.erl
%%% Descr    : Suite #2: Tests of basic SQL statements,
%%%            prepared statements, stored procedures 
%%% Author   : H. Diedrich
%%% Created  : 12/13/2011 hd
%%% Requires : Erlang 14B (prior may not have ct_run)
%%%-------------------------------------------------------------------
%%%
%%% Run from Emysql/: 
%%%     make test
%%%
%%% Results see:
%%%     test/index.html
%%%
%%%-------------------------------------------------------------------

-module(basics_SUITE).
-compile(export_all).
-include_lib("common_test/include/ct.hrl").

-record(hello_record, {hello_text}).

-include("../include/emysql.hrl").

%% Optional suite settings
%%--------------------------------------------------------------------
%% Function: suite() -> Info
%% Info = [tuple()]
%%--------------------------------------------------------------------

suite() ->
    [{timetrap,{seconds,30}}].

%% Mandatory list of test cases and test groups, and skip orders. 
%%--------------------------------------------------------------------
%% Function: all() -> GroupsAndTestCases | {skip,Reason}
%% GroupsAndTestCases = [{group,GroupName} | TestCase]
%% GroupName = atom()
%% TestCase = atom()
%% Reason = term()
%%--------------------------------------------------------------------

all() -> 
    [delete_all,
     encode_atoms,
     insert_only,
     insert_and_read_back,
     insert_and_read_back_as_recs,
     insert_and_read_back_as_json,
     select_by_prepared_statement,
	 delete_non_existant_procedure,
	 select_by_stored_procedure,
     multiple_select,
     encode_floating_point_data].


%% Optional suite pre test initialization
%%--------------------------------------------------------------------
%% Function: init_per_suite(Config0) ->
%%               Config1 | {skip,Reason} | {skip_and_save,Reason,Config1}
%% Config0 = Config1 = [tuple()]
%% Reason = term()
%%--------------------------------------------------------------------

init_per_suite(Config) ->

	% if this fails, focus on environment_SUITE to fix test setup.
    crypto:start(),
    application:start(emysql),
    emysql:add_pool(test_pool, 1,
        test_helper:test_u(), test_helper:test_p(), "localhost", 3306,
        "hello_database", utf8),

    Config.

%% Optional suite post test wind down
%%--------------------------------------------------------------------
%% Function: end_per_suite(Config0) -> void() | {save_config,Config1}
%% Config0 = Config1 = [tuple()]
%%--------------------------------------------------------------------

end_per_suite(_Config) ->
	emysql:remove_pool(test_pool),
    ok.

%% A test case. The ok is irrelevant. What matters is, if it returns.
%%--------------------------------------------------------------------
%% Function: TestCase(Config0) ->
%%               ok | exit() | {skip,Reason} | {comment,Comment} |
%%               {save_config,Config1} | {skip_and_save,Reason,Config1}
%% Config0 = Config1 = [tuple()]
%% Reason = term()
%% Comment = term()
%%--------------------------------------------------------------------

%% Test Case: Delete all records in the test database
%%--------------------------------------------------------------------
delete_all(_) ->
	
    emysql:execute(test_pool, <<"DELETE FROM hello_table">>),
    ok.


%% Test Case: Make an Insert
%%--------------------------------------------------------------------
insert_only(_) ->
	
    emysql:execute(test_pool,
        <<"INSERT INTO hello_table SET hello_text = 'Hello World!'">>),

    ok.

%% Test Case: Allow insertion of atom values through the encoder
%%--------------------------------------------------------------------
encode_atoms(_Config) ->
    emysql:execute(test_pool, <<"DROP TABLE encode_atoms_test">>),
    emysql:execute(test_pool, <<"CREATE TABLE encode_atoms_test (x VARCHAR(32))">>),

    emysql:prepare(encode_atoms, <<"INSERT INTO encode_atoms_test (x) VALUES (?)">>),
    Result = emysql:execute(test_pool, encode_atoms, [foo]),
    ct:log("Result: ~p", [Result]),

    ok_packet = element(1, Result),
    ok.

%% Test Case: Make an Insert and Select it back
%%--------------------------------------------------------------------
insert_and_read_back(_) ->
	
    emysql:execute(test_pool, <<"DELETE FROM hello_table">>),

    emysql:execute(test_pool,
        <<"INSERT INTO hello_table SET hello_text = 'Hello World!'">>),

    Result = emysql:execute(test_pool,
        <<"select hello_text from hello_table">>),
	
	% find this output by clicking on the test name, then case name in test/index.html
	ct:log("~p~n", [Result]),

	% the test
	{result_packet,5,
               [#field{seq_num=2, 
                       catalog= <<"def">>, 
                       db= <<"hello_database">>,
                       table= <<"hello_table">>,
                       org_table= <<"hello_table">>,
                       name= <<"hello_text">>,
                       org_name= <<"hello_text">>,
                       type=254,
                       default= <<>>,
                       charset_nr=33,
                       length=_,
                       flags=_,
                       decimals=0,
                       decoder = _ }],
               [[<<"Hello World!">>]],
               <<>>} = Result,
    
    ok.

%% Test Case: Encode floating point data into a test table (Issue 57)
encode_floating_point_data(_Config) ->
    emysql:execute(test_pool, <<"DROP TABLE float_test">>),
    emysql:execute(test_pool, <<"CREATE TABLE float_test ( x FLOAT )">>),
    emysql:prepare(encode_float_stmt, <<"INSERT INTO float_test (x) VALUES (?)">>),
    
    Result = emysql:execute(test_pool, encode_float_stmt, [3.14]),

    ct:log("Result: ~p", [Result]),
    ok_packet = element(1, Result),
    ok.

%% Test Case: Make an Insert and Select it back, reading out as Record
%%--------------------------------------------------------------------
insert_and_read_back_as_recs(_) ->
	
    emysql:execute(test_pool, <<"DELETE FROM hello_table">>),

	emysql:execute(test_pool,
		<<"INSERT INTO hello_table SET hello_text = 'Hello World!'">>),

	Result = emysql:execute(test_pool, <<"SELECT * from hello_table">>),

	Recs = emysql_util:as_record(
		Result, hello_record, record_info(fields, hello_record)),

	% find this output by clicking on the test name, then case name in test/index.html
	ct:log("~p~n", [Recs]),

	% the test
	Recs = [{hello_record,<<"Hello World!">>}],

	ok.
	
%% Test Case: Make an Insert and Select it back, reading out as JSON
%%--------------------------------------------------------------------
insert_and_read_back_as_json(_) ->

    emysql:execute(test_pool, <<"DELETE FROM hello_table">>),

    emysql:execute(test_pool,
        <<"INSERT INTO hello_table SET hello_text = 'Hello World!'">>),

    Result = emysql:execute(test_pool, <<"SELECT * from hello_table">>),

    Recs = emysql_util:as_json(Result),

    % find this output by clicking on the test name, then case name in test/index.html
    io:format("~p~n", [Recs]),

    % the test
    Recs = [[{<<"hello_text">>,<<"Hello World!">>}]],

    ok.

%% Test Case: Create a Prepared Statement and make a Select with it
%%--------------------------------------------------------------------
select_by_prepared_statement(_) ->

    emysql:execute(test_pool, <<"DELETE FROM hello_table">>),

	emysql:execute(test_pool,
		<<"INSERT INTO hello_table SET hello_text = 'Hello World!'">>),

	emysql:prepare(test_stmt, 
		<<"SELECT * from hello_table WHERE hello_text like ?">>),

	Result = emysql:execute(test_pool, test_stmt, ["Hello%"]),

	% find this output by clicking on the test name, then case name in test/index.html
	ct:log("Result: ~p~n", [Result]),

	% the test
	{result_packet,5,  
                    [#field{
                            seq_num=2,
                            catalog= <<"def">>,
                            db= <<"hello_database">>,
                            table= <<"hello_table">>,
                            org_table= <<"hello_table">>,
                            name= <<"hello_text">>,
                            org_name= <<"hello_text">>,
                            type=254,
                            default = <<>>,
                            charset_nr = 33,
                            length= _Length,
                            flags = _Flags,
                            decimals = 0,
                            decoder = _Decoder}],
                       [[<<"Hello World!">>]],
                       <<>>} = Result,
    ok.

multiple_select(_) ->
    emysql:execute(test_pool, <<"DELETE FROM hello_table">>),

	emysql:execute(test_pool,
		<<"INSERT INTO hello_table SET hello_text = 'Hello World!'">>),

	[Result1, Result2] = emysql:execute(test_pool, <<"SELECT * from hello_table; SELECT * from hello_table">>),

	% find this output by clicking on the test name, then case name in test/index.html
	ct:log("Result1: ~p~n", [Result1]),
	ct:log("Result2: ~p~n", [Result2]),

	% the test
	{result_packet,5,  
                    [#field{
                            seq_num=_,
                            catalog= <<"def">>,
                            db= <<"hello_database">>,
                            table= <<"hello_table">>,
                            org_table= <<"hello_table">>,
                            name= <<"hello_text">>,
                            org_name= <<"hello_text">>,
                            type=254,
                            default = <<>>,
                            charset_nr = 33,
                            length= _Length,
                            flags = _Flags,
                            decimals = 0,
                            decoder = _Decoder}],
                       [[<<"Hello World!">>]],
                       <<>>} = Result1,
	{result_packet,_,  
                    [#field{
                            seq_num=_,
                            catalog= <<"def">>,
                            db= <<"hello_database">>,
                            table= <<"hello_table">>,
                            org_table= <<"hello_table">>,
                            name= <<"hello_text">>,
                            org_name= <<"hello_text">>,
                            type=254,
                            default = <<>>,
                            charset_nr = 33,
                            length= _Length,
                            flags = _Flags,
                            decimals = 0,
                            decoder = _Decoder}],
                       [[<<"Hello World!">>]],
                       <<>>} = Result2,
    ok.

%% Test Case: Delete a non-existant Stored Procedure
%%--------------------------------------------------------------------
delete_non_existant_procedure(_) ->

	Result1 = emysql:execute(test_pool,
	  	<<"drop procedure sp_me_no_exist">>),
	  	% note: returns ok even if sp_hello does not exist

	% find this output by clicking on the test name, then case name in test/index.html
	ct:log("~p~n", [Result1]),

	% test
	Result1 = {error_packet,1,1305,<<"42000">>,
              "PROCEDURE hello_database.sp_me_no_exist does not exist"},

	ok.

%% Test Case: Create a Stored Procedure and make a Select with it
%%--------------------------------------------------------------------
select_by_stored_procedure(_) ->

    emysql:execute(test_pool, <<"DELETE FROM hello_table">>),

	emysql:execute(test_pool,
		<<"INSERT INTO hello_table SET hello_text = 'Hello World!'">>),

	Result1 = emysql:execute(test_pool,
	  	<<"drop procedure sp_hello">>),
	  	% note: returns ok even if sp_hello does not exist

	% find this output by clicking on the test name, then case name in test/index.html
	ct:log("~p~n", [Result1]),

	% first test
	case Result1 of
		{ok_packet,1,0,0,_,0,[]} -> ok;
		{error_packet,1,1305,<<"42000">>,
              "PROCEDURE hello_database.sp_hello does not exist"} -> ok
    end,

	Result2 = emysql:execute(test_pool,
	  	<<"create procedure sp_hello() begin select * from hello_table limit 2; end">>),

	% find this output by clicking on the test name, then case name in test/index.html
	ct:log("~p~n", [Result2]),

	% second test
	{ok_packet,1,0,0,_,0,[]} = Result2,

	Result3 = emysql:execute(test_pool,	<<"call sp_hello();">>),

	% find this output by clicking on the test name, then case name in test/index.html
	ct:log("~p~n", [Result3]),
	
	% third, main test
	[{result_packet,5, _Fields, [[<<"Hello World!">>]], <<>>}, {ok_packet,6,0,0,_,0,[]}] = Result3,
	
	ok.

fields() ->
    [{field,2,<<"def">>,<<"hello_database">>,<<"hello_table">>,
                        <<"hello_table">>,<<"hello_text">>,<<"hello_text">>,
                        254,<<>>,33,60,0,0}].