erl_mysql_tests.erl

-module(erl_mysql_tests).

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

all_test_() ->
    [insert(),
     update(),
     delete(),
     select()].

insert() ->
    [?_assertEqual(<<"INSERT INTO `project`(`foo`, `bar`, `baz`) VALUES ('a', 'b', 'c'), ('d', 'e', 'f')">>,  
                   iolist_to_binary(erl_mysql:insert(project,{[foo,bar,baz],[["a","b","c"],["d","e","f"]]}))),
     ?_assertEqual(<<"INSERT INTO `project`(`foo`, `baz`) VALUES (5, 'bob')">>, 
                   iolist_to_binary(erl_mysql:insert(project,[{foo,5},{baz,"bob"}]))),
     ?_assertEqual(<<"INSERT INTO `project`(`foo`, `baz`) VALUES (5, 'bob')">>, 
                   iolist_to_binary(erl_mysql:insert(project,[{foo,5},{baz,<<"bob">>}])))
].

update() ->
   [?_assertEqual(<<"UPDATE `project` SET `foo` = 5, `bar` = 6, `baz` = 'hello'">>,
                  iolist_to_binary(erl_mysql:update(project,[{foo,5},{bar,6},{baz,"hello"}]))),
    ?_assertEqual(<<"UPDATE `project` SET `foo` = 'quo\\'ted', `baz` = 'blub' WHERE NOT (`a` = 5)">>,
                  iolist_to_binary(erl_mysql:update(project,[{foo,"quo'ted"},{baz,"blub"}],{'not',{a,'=',5}})))].


delete() ->
    [?_assertEqual(<<"DELETE FROM `project`">>,
                   iolist_to_binary(erl_mysql:delete(project))),
     ?_assertEqual(<<"DELETE FROM `project` LIMIT 50">>,
                   iolist_to_binary(erl_mysql:delete(project, undefined, [{limit, 50}]))),     
     ?_assertEqual(<<"DELETE FROM `project` WHERE (`a` = 5)">>,
                   iolist_to_binary(erl_mysql:delete(project,{a,'=',5}))),
     ?_assertEqual(<<"DELETE FROM `project` WHERE (`a` = 5)">>,
                   iolist_to_binary(erl_mysql:delete(project,{a,'=',5}))),
     ?_assertEqual(<<"DELETE FROM `developer` WHERE NOT ((`name` LIKE '%Paul%') OR (`name` LIKE '%Gerber%'))">>,
                   iolist_to_binary(erl_mysql:delete(developer,
                                                     {'not',{{name,like,"%Paul%"},
                                                             'or',
                                                             {name,like,"%Gerber%"}}})))].


select() ->
    SelectFun = fun(A) ->
                        iolist_to_binary(apply(erl_mysql, select, A))                   
                end,

    [?_assertEqual(<<"SELECT 'foo'">>,
                   SelectFun([["foo"]])),
     ?_assertEqual(<<"SELECT 'foo', 'bar'">>,
                   SelectFun([["foo","bar"]])),           
     ?_assertEqual(<<"SELECT (1 + 1)">>,
                   SelectFun([{1,'+',1}])),
     ?_assertEqual(<<"SELECT `foo` AS `bar` FROM `baz` AS `blub`">>,
                   SelectFun([{foo,as,bar},{baz,as,blub}])),
     ?_assertEqual(<<"SELECT `name` FROM `developer` WHERE (`country` = 'quoted \\' \\\" string')">>,
                   SelectFun([name, developer, {country,'=',"quoted ' \" string"}])),
     ?_assertEqual(<<"SELECT `p`.`name` AS `name`, `p`.`age` AS `age`, `project`.* FROM `person` AS `p`, `project`">>,
                   SelectFun([[{{p,name},as,name},{{p,age},as,age},{project,'*'}],
                              [{person,as,p}, project]])),
     ?_assertEqual(<<"SELECT count(`name`) FROM `developer`">>,
                   SelectFun([{call,count,[name]},developer])),
     ?_assertEqual(<<"SELECT last_insert_id()">>,
                   SelectFun([{call,last_insert_id,[]}])),
     ?_assertEqual(<<"SELECT DISTINCT `name` FROM `person` LIMIT 5">>,
                SelectFun([distinct, name, person, undefined, {limit,5}])),
     ?_assertEqual(<<"SELECT `name`, `age` FROM `person` ORDER BY `name` DESC, `age`">>,
                SelectFun([[name,age], person, undefined, {order_by, [{name,desc}, age]}])),
     ?_assertEqual(<<"SELECT count(`name`), `age` FROM `developer` GROUP BY `age`">>,
                   SelectFun([[{call,count,[name]},age],
                              developer,
                              undefined,
                              {group_by,age}])),  
     ?_assertEqual(<<"SELECT count(`name`), `age`, `country` FROM `developer` GROUP BY `age`, `country` HAVING (`age` > 20)">>,
                SelectFun([[{call,count,[name]},age,country],
                           developer,
                           undefined,
                           {group_by,[age,country],having,{age,'>',20}}])),

     ?_assertEqual(<<"SELECT * FROM `developer` WHERE `name` IN ('Paul', 'Frank')">>,
                SelectFun(['*',
                           developer,
                           {name,in,["Paul","Frank"]}])),
     
     ?_assertEqual(<<"SELECT * FROM `developer` WHERE (`name` = 'weiwei')">>,
                   SelectFun(['*',
                              developer,
                              {name,'=',"weiwei"}])),

     ?_assertEqual(<<"SELECT * FROM `foo` WHERE (`a` = (1 + 2 + 3))">>,
                   SelectFun(['*',
                              foo,
                              {a,'=',{'+',[1,2,3]}}])),
     
     ?_assertEqual(<<"SELECT * FROM `foo` WHERE ((`a` + `b` + `c`) = (`d` + `e` + `f`))">>,
                   SelectFun(['*',
                              foo,
                              {'=',[{'+',[a,b,c]},{'+',[d,e,f]}]}])),
     ?_assertEqual(<<"SELECT * FROM `foo` WHERE ((`a` = `b`) AND (`c` = `d`) AND (`e` = `f`))">>,
                   SelectFun(['*',
                              foo,
                              {'and',[{a,'=',b},{c,'=',d},{e,'=',f}]}])),
     ?_assertEqual(<<"SELECT (1 + 2 + 3 + 4)">>,
                   SelectFun([{'+',[1,2,3,4]}])),
     ?_assertEqual(<<"SELECT * FROM `blah`">>,
                   SelectFun(['*', 
                              blah])),
     ?_assertEqual(<<"SELECT `name` FROM search_people(age := 18)">>,
                   SelectFun([name,
                              {call,search_people,[{age, 18}]}])),

     ?_assertEqual(<<"SELECT * FROM `foo` WHERE a = b">>,
                   SelectFun(['*',
                              foo,
                              "a = b"])),
     ?_assertEqual(<<"SELECT * FROM `foo` WHERE a = 'foo'">>,
                   SelectFun(['*',
                              foo,
                              "a = 'foo'"])),
     ?_assertEqual(<<"SELECT * FROM `foo` WHERE a = 'i'm an evil query'">>,
                SelectFun(['*',
                           foo,
                           <<"a = 'i'm an evil query'">>])),
     ?_assertEqual(<<"SELECT * FROM `foo` WHERE a = b">>,
                   SelectFun(['*',
                              foo,
                              <<"a = b">>])),
     ?_assertEqual(<<"SELECT * FROM `foo` WHERE a = b">>,
                   SelectFun(['*',
                              foo,
                              "a = b"])),

     ?_assertEqual(<<"SELECT * FROM `foo` WHERE a = b">>,
                   SelectFun(['*',
                              foo,
                              <<"a = b">>])),
     ?_assertEqual(<<"SELECT * FROM `foo` WHERE a = b LIMIT 5">>,
                   SelectFun(['*',
                              foo,
                              <<"a = b">>,
                              <<"LIMIT 5">>])),
     ?_assertEqual(<<"SELECT (`a` OR (foo))">>,
                   SelectFun([{a,'or',"foo"}])),

     ?_assertEqual(<<"SELECT ((bar) OR `b`)">>,
                   SelectFun([{"bar",'or',b}])),

     ?_assertEqual(<<"SELECT ((foo) AND (bar))">>,
                   SelectFun([{"foo",'and',<<"bar">>}])),

     ?_assertEqual(<<"SELECT NOT (foo = bar)">>,
                   SelectFun([{'not',"foo = bar"}])),
     ?_assertEqual(<<"SELECT NOT (foo = bar)">>,
                   SelectFun([{'!',"foo = bar"}]))].