sqlite3_lib.erl

%%%-------------------------------------------------------------------
%%% File    : sqlite3_lib.erl
%%% @author Tee Teoh
%%% @copyright 21 Jun 2008 by Tee Teoh 
%%% @version 1.0.0
%%% @doc Library module for sqlite3
%%% @end
%%%-------------------------------------------------------------------
-module(sqlite3_lib).
-include("sqlite3.hrl").

%%--------------------------------------------------------------------
%% @spec value_to_sql(Value :: sql_value()) -> iolist()
%% @doc 
%%    Converts an Erlang term to an SQL string.
%%    Currently supports integers, floats, 'null' atom, and iodata 
%%    (binaries and iolists) which are treated as SQL strings.
%%
%%    All single quotes (') will be replaced with ''.
%% @end
%%--------------------------------------------------------------------
-spec value_to_sql(sql_value()) -> iolist().
value_to_sql(X) ->
    if
        is_integer(X)   -> integer_to_list(X);
        is_float(X)     -> float_to_list(X);
        X == ?NULL_ATOM -> "NULL";
        true            -> [$', escape(X), $']
    end.

%%--------------------------------------------------------------------
%% @spec write_value_sql(Value :: [sql_value()]) -> iolist()
%% @doc 
%%    Creates the values portion of the sql stmt.
%% @end
%%--------------------------------------------------------------------
-spec write_value_sql(sql_value()) -> iolist().
write_value_sql(Values) ->
    map_intersperse(fun value_to_sql/1, Values, ", ").

    
%%--------------------------------------------------------------------
%% @spec write_col_sql([atom()]) -> iolist()
%% @doc Creates the column/data stmt for SQL.
%% @end
%%--------------------------------------------------------------------
-spec write_col_sql([atom()]) -> iolist().
write_col_sql(Cols) ->
    map_intersperse(fun atom_to_list/1, Cols, ", ").

%%--------------------------------------------------------------------
%% @spec escape(IoData :: iodata()) -> iodata()
%% 
%% @doc Returns copy of IoData with all ' replaced by ''
%% @end
%%--------------------------------------------------------------------
-spec escape(iodata()) -> iodata().
escape(IoData) -> re:replace(IoData, "'", "''", [global]).

%%--------------------------------------------------------------------
%% @spec write_sql(Tbl, Data) -> iolist()
%%       Tbl = atom()
%%       Data = [{ColName :: atom(), Value :: sql_value()}]
%% @doc Taking Data as list of column names and values pairs it creates the
%%      proper insertion SQL stmt.
%% @end
%%--------------------------------------------------------------------
-spec write_sql(atom(), [{atom(), sql_value()}]) -> iolist().
write_sql(Tbl, Data) ->
    {Cols, Values} = lists:unzip(Data),
    io_lib:format("INSERT INTO ~p (~s) VALUES (~s);", 
                  [Tbl, write_col_sql(Cols), write_value_sql(Values)]).
%%     ["INSERT INTO ", atom_to_list(Tbl), " (", sqlite3_lib:write_col_sql(Cols), 
%%      ") values (", sqlite3_lib:write_value_sql(Values), ");"].

%%====================================================================
%% Internal functions
%%====================================================================

%% @doc Works like string:join for iolists

-spec map_intersperse(fun((X) -> iolist()), [X], [iolist() | integer()]) -> iolist().
map_intersperse(_Fun, [], _Sep) -> [];
map_intersperse(Fun, [Elem], _Sep) -> [Fun(Elem)];
map_intersperse(Fun, [Head | Tail], Sep) -> [Fun(Head), Sep | map_intersperse(Fun, Tail, Sep)].

%%--------------------------------------------------------------------
%% @type sql_value() = number() | 'null' | iodata().
%% 
%% Values accepted in SQL statements include numbers, atom 'null',
%% and io:iolist().
%% @end
%%--------------------------------------------------------------------

%%--------------------------------------------------------------------
%% Tests
%%--------------------------------------------------------------------
-ifdef(TEST).
-include_lib("eunit/include/eunit.hrl").

-define(FLAT(X), iolist_to_binary(X)).
-define(assertFlat(Expected, Value), ?assertEqual(iolist_to_binary(Expected), iolist_to_binary(Value))).

write_sql_test() ->
    ?debugVal(iolist_to_binary(write_sql(user, [{id, 1}, {name, "a"}]))),
    ?assertFlat(
        "INSERT INTO user (id, name) values (1, 'a');",
        write_sql(user, [{id, 1}, {name, "a"}])).

-endif.

test output

======================== EUnit ========================
module 'sqlite3_test'
module 'sqlite3'
src/sqlite3_lib.erl:105: iolist_to_binary ( write_sql ( user , [ { id , 1 } , { name , "a" } ] ) ) = <<"INSERT INTO user (id, name) VALUES (1, 'a');">>
sqlite3_lib: write_sql_test (module 'sqlite3_lib')...*failed*
::error:{assertEqual_failed,
          [{module,sqlite3_lib},
           {line,108},
           {expression,
               "iolist_to_binary ( write_sql ( user , [ { id , 1 } , { name , \"a\" } ] ) )"},
           {expected,<<"INSERT INTO user (id, name) values (1, 'a');">>},
           {value,<<"INSERT INTO user (id, name) VALUES (1, '"...>>}]}
  in function sqlite3_lib:'-write_sql_test/0-fun-1-'/1


=======================================================
  Failed: 1.  Skipped: 0.  Passed: 0.

I don't understand how it's possible for this test to fail, given the value printed by ?debugVal.

n expected, u need to change the word values, VALUES

Ah yes, thanks!

no problem :)