cctg: MySQL driver for Erlang

MySQL/OTP 的作者提供了一份 MySQL Driver for Erlang 的比較表：Comparison of MySQL clients，除了使用最原始的 ODBC 連接 MySQL 之外，要在 erlang 連接 MySQL 目前有四個 driver：MySQL/OTP、Emysql、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
  ),