From Rails to Erlyweb - Part IV

IV. Support Mysql Spatial Extensions via erlydb_mysql

ErlyDB supports most database operations, and it can also be extended to support more complex operations. Here is an example, where I need to support Mysql spatial extensions.

Assume we have a table: places, which has a mysql point field: location, here is the code:

-module(places).

-export([get_location_as_tuple/1,
         set_location_by_tuple/2
         ]).

%% @spec Id::integer() -> {float(), float()}
get_location_as_tuple(Id) ->
    ESQL = {esql, {select, 'AsText(location)', {from, ?MODULE}, {where, {'id', '=', Id}}}},
    case erlydb_mysql:q(ESQL) of
        %% Example:
        %% {data, {mysql_result, [{<<>>, <<"AsText(location)">>, 8192, 'VAR_STRING'}],
        %%                       [[<<"POINT(-122.292 37.8341)">>]],
        %%                       0,
        %%                       []}}
        {data, {mysql_result, [{_, <<"AsText(location)">>, _, _}],
                              [[FirstResult]|_Rest],
                              _,
                              _}} ->
            case io_lib:fread("POINT(~f ~f)", binary_to_list(FirstResult)) of
                {ok, [X, Y], _} -> {X, Y};
                _Else -> undefined
            end;        
        _Error ->
            undefined
    end.

%% @spec Id::integer, {X::float(), Y::float()} -> ok | error    
set_location_by_tuple(Id, {X, Y}) ->
    %% "UPDATE places SET location = PointFromText(\'POINT(1 1)\') WHERE (id = 1)"
    PointFromText = point_tuple_to_point_from_text({X, Y}),
    ESQL = {esql, {update, ?MODULE, [{location, PointFromText}], {'id', '=', Id}}},
    Options = [{allow_unsafe_statements, true}],
    case erlydb_mysql:q(ESQL, Options) of
        {updated, {mysql_result, [], [], _UpdatedNum, []}} -> ok;                              
        _Error -> error
    end.
   
point_tuple_to_point_from_text({X, Y}) ->
    %% as mysql support float in format of someting like 1.20002300000000005298e+02, 
    %% we can just apply float_to_list/1 to X and Y
    PointFromText = lists:flatten(io_lib:fwrite("PointFromText('POINT(~f ~f)')", [X, Y])),
    list_to_atom(PointFromText).             %% 'PointFromText(\'POINT(X Y)\')'       
   

Now we can:

> places:set_location_by_tuple(6, {-11.11, 88.88}).
> places:get_location_as_tuple(6).
{-11.11, 88.88}