2016年6月13日

MySQL driver for Erlang

MySQL/OTP 的作者提供了一份 MySQL Driver for Erlang 的比較表:Comparison of MySQL clients,除了使用最原始的 ODBC 連接 MySQL 之外,要在 erlang 連接 MySQL 目前有四個 driver:MySQL/OTPEmysql、erlang-mysql-driver、YXA。

Emysql#History 有說明後面三個 driver 的演進的過程,從最早的 YXA,接手的是 erlang-mysql-driver,再來是 Emysql,MySQL/OTP 的作者原本也是 Emysql 的貢獻者,但覺得這個 driver 為了跟舊版 MySQL 相容,而有一些限制,因此他又做了一個 MySQL/OTP 的 driver,另外該作者又利用 PoolBoy 製作了 Connection Pool 的功能,就是 mysql-otp-poolboy

MySQL-OTP

要直接使用 MySQL-OTP 並不困難,基本上依照 專案首頁 的方式,用 mysql:start_link 就可以連接資料庫,mysql:query 取得資料。

(web@cmbp)8> {ok, Pid} = mysql:start_link([{host, "localhost"}, {user, "root"},{password, "pwd"}, {database, "test"}]).
{ok,<0.146.0>}
(web@cmbp)9> {ok, ColumnNames, Rows} = mysql:query(Pid, <<"SELECT * FROM dbversion WHERE versiontxt = ?">>, ["test"]).
{ok,[<<"dbversionseq">>,<<"versionno">>,<<"versiontxt">>,
     <<"updatedate">>,<<"updateby">>,<<"createdate">>,
     <<"createby">>],
    [[1,1,<<"test">>,
      {{2016,3,25},{15,35,47}},
      0,
      {{2016,3,25},{15,35,47}},
      0]]}

MySQL-OTP Poolboy

MySQL/OTP Poolboy 的部分,依照 網頁 的說明,有兩種啟動 supervisor 的方式:一種是放在自己的 supervisor 裡面,一種是使用專案中內建的 supervisor。

兩種方式都試過可以用,以下紀錄怎麼用專案中內建的 supervisor。

首先要製作 OTP appcation config 檔案,通常是放在 sys.config 裡面。

[
  {mysql_poolboy, [
    {pool1,
      {
        [
          {size, 10}, {max_overflow, 20}
        ],
        [
          {host, "localhost"},
          {port, 3306},
          {user, "root"},
          {password, "password"},
          {database, "test"},
          {keep_alive, true},
          {prepare, [{foo, "SELECT sysdate()"}]}
        ]
      }
    }
  ]}
].

撰寫一個 otp server,在啟動 mysql_poolboy 以前,要先啟動 mysql 跟 poolboy。

-module(server).

-behaviour(application).

%% Application callbacks
-export([start/2, stop/1]).

%% ===================================================================
%% Application callbacks
%% ===================================================================

start(_StartType, _StartArgs) ->
  init_mysql()
  ok.
stop(_State) ->
  ok.

init_mysql() ->
  application:start(mysql),
  application:start(poolboy),
  application:start(mysql_poolboy),

  ok.

啟動 MySQL/OTP Pooboy 之後,就是要使用這個 mysql connection pool,用以下的方式,可以查詢出 table 的結果

{ok, L1, L2} = mysql_poolboy:query(pool1, <<"SELECT * FROM dbversion">>)

用以下的指令啟動 otp server

erl -pa ./deps/*/ebin ./ebin -config sys.config -eval "application:start(server)"

處理 Result Set

利用 query 查詢的結果,資料結構就像下面這樣,第一個是 ok,第二個是所有欄位名稱的 list,第三個部分是所有結果的 list of list。

mysql_poolboy:query(pool1, <<"SELECT * FROM dateserial">>).

{ok,[<<"dateserialseq">>,<<"datestr">>,<<"datenumber">>,
     <<"updatedate">>,<<"updateby">>,<<"createdate">>,
     <<"createby">>],
    [[8,<<"20160330">>,9,
      {{2016,3,30},{18,26,35}},
      0,
      {{2016,3,30},{18,26,28}},
      0],
     [9,<<"20160331">>,10,
      {{2016,3,31},{9,56,32}},
      0,
      {{2016,3,31},{9,53,29}},
      0]]}

處理日期

erlang 的日期格式是以下這個樣子,但對網頁資料處理來說,這個結構是沒辦法處理的。我們可以利用 erlware_commons 的 ec_date 作為日期轉換的 library。

{{2016,3,31},{9,56,32}}

利用 ec_date 就可以將日期轉換成字串 2016-03-31 09:56:32

ec_date:format("Y-m-d H:i:s", {{2016,3,31},{9,56,32}})

但剛剛的 MySQL Result Set 裡面的日期,並沒有固定的欄位位置,我們寫一個 function,用來將 list 的 Index 位置的元素,轉換成日期字串。

convertDateString(L,Index) ->
  {L1,[OldValue|L2]} = lists:split(Index-1,L),
  L1++[ list_to_binary(ec_date:format(?DATE_FORMATE, OldValue)) |L2].

以剛剛的 sql 查詢,就是第 4 以及第 6 個欄位位置是需要轉換的日期。

  %% convert date to bit string
        L3 = lists:map( fun(L2Map) -> convertDateString(L2Map, 4) end, L2 ),
        L4 = lists:map( fun(L2Map) -> convertDateString(L2Map, 6) end, L3 )

轉換 JSON

通常,我們會直接將資料庫取得資料的結果,以 JSON 的方式傳給前端網頁的 javascript 程式處理,所以需要設法將這些資料轉換為 JSON。

JSON 會以 jsx 這個套件來輔助處理,我們先看 encode 跟 decode 的部分,Erlang 在 17 版之後提供了 maps 的資料結構,在這裡我們有看到 jsx 有支援 maps,所以目標是先將 MySQL Result Set 轉換成 maps,再透過 jsx 轉換成 JSON。

因為是 Result Set 是 list of maps,對於 JSON 來說,前面需要再加上一個 key,才是一個完整的 JSON。

#{<<"res">> => L5 }

透過 lists:map 以及 lists:zip 就可以完成這項工作,最後再用 jsx:encode 轉換成 JSON。

%% convert two lists to  list of maps
        L5 = lists:map( fun(L4Map) -> maps:from_list(lists:zip(L1, L4Map)) end, L4 ),
%% add key
        L6= #{<<"res">> => L5 },
%% convert to json
        Json= jsx:encode(L6)

完整的範例

以下是處理 MySQL Result Set 的完整範例程式。

  Result = mysql_poolboy:transaction(pool1,
      fun(Pid) ->
        {ok, L1, L2} = mysql_poolboy:query(pool1, <<"SELECT * FROM dateserial">>),
        lager:debug("~n L1=~p.~n", [L1]),
        lager:debug("~n L2=~p.~n", [L2]),

        %% convert date to bit string
        L3 = lists:map( fun(L2Map) -> convertDateString(L2Map, 4) end, L2 ),
        L4 = lists:map( fun(L2Map) -> convertDateString(L2Map, 6) end, L3 ),

        %% convert two lists to  list of maps
        L5 = lists:map( fun(L4Map) -> maps:from_list(lists:zip(L1, L4Map)) end, L4 ),
        lager:debug("~n L5=~p.~n", [L5]),

        %% add key
        L6= #{<<"res">> => L5 },
        lager:debug("~n L6=~p.~n", [L6]),

        %% convert to json
        Json= jsx:encode(L6),

        lager:debug("~n Json=~p.~n", [Json]),

        %% decode json, json -> maps
        % JMap=jsx:decode(<<"{\"res\":[{\"dateserialseq\":8,\"datestr\":\"20160330\",\"datenumber\":10},{\"dateserialseq\":9,\"datestr\":\"20160331\",\"datenumber\":8}] }">>, [return_maps]).

        ok
      end
  ),

References

add transaction for emysql

emysql與erlang-mysql-driver的pool模型

Erlang Mysql: How to prevent SQL Injections

使用 prepared statements + escape characters

使用 Haproxy 搭建高可用與負載平衡叢集(一)MySQL DB 應用

MaxScale

High availability with asynchronous replication… and transparent R/W split

MySQL高可用性方案

Erlang pool management -- Emysql pool

Erlang pool management -- Emysql pool optimize

關於emysql的若干問題