Match boss_db column names and values for use with ErlyDTL

boss_db_sql.erl

-module(boss_db_sql).
-compile(export_all).

%% Much more elaborate version -- and with comments!
%% Drop in src/lib/ to be able to use it from anywhere in CB.

%% Return just the values from an executed SQL query.
sql(S) ->
    {ok, ColNames, Values} = boss_db:execute(S),
    Values.

%% Return values from an SQL query, paired with their column names,
%% e.g. [[{id,<<23>>},{name,<<"Rune">>],
%% [{id,<<24>>},{name,<<"Ronja">>}], ...].
sql(S, pair) ->
    {ok, Columns, Values} = boss_db:execute(S),
    %% Extract column names.
    ColumnNames = 
        [list_to_atom(binary_to_list(Name)) || {column,Name,_,_,_,_} <- Columns],
    %% Pair column names and values
    Pairings = lists:map(
                 fun(Value) ->
                         lists:zip(ColumnNames, tuple_to_list(Value))
                 end,
                 Values),
    %% As timestamps are returned as a string, e.g. "2012-08-16
    %% 21:02:02" or even "2012-08-16 21:02:02.123456" (PostgreSQL does
    %% this), we need to convert it into a CB datetime tuple.
    Pairings2 = lists:map(
                  fun(L) -> 
                          lists:map(
                            fun(X) ->
                                    timestamp_to_tuple(X)
                            end, 
                            L)
                  end,
                  Pairings
                 ),
    Pairings2.

%% Converts a string timestapm to a CB datetime tuple of format
%% {{Y,M,D},{H,Mm,S}} for ErlyDTL to be able to figure it out.  It
%% might be better to patch ErlyDTL to handle timestamps as strings,
%% but that's another discussion.
timestamp_to_tuple({timestamp, T}) ->
    %% The first five parts are integers, the last part may be either
    %% integer or float.
    {Ints, PossibleFloat} = lists:split(5, string:tokens(binary_to_list(T), " :-")),
    Seconds = case string:to_float(hd(PossibleFloat)) of
                  {S, []} ->
                      erlang:round(S);
                  {error, no_float} ->
                      {S2, []} = string:to_integer(hd(PossibleFloat)),
                      S2
              end,
    [Y, M, D, H, Mm] =
        lists:map(fun(X) -> {Int, []} = string:to_integer(X), Int end, Ints),
    {timestamp, {{Y, M, D}, {H, Mm, Seconds}}};
timestamp_to_tuple(Other) ->
    Other.