mnesia加载mysql表数据(修改ejabberd privacy list)

前提：

mnesia 一张表 privacy

mysql 三张表privacy_list privacy_list_data privacy_default_list

目标：

服务start加载mysql 三个表的数据到mnesia privacy表中

分析分现 ,mysql 的三个表是通过username name id 相互关联起来的，mnesia就是一个结构表

可能习惯了关系数据库我首先想到，关联查询。但发现操mnesia似乎不容易，组合数存在重复的操作。早晚都要遍历干脆 分别从mysql中把表读出来放在内存中，通过erlang的list操作得了，还能顺便写进mnesia中。

代码片段

onload_table(Host) ->
    ?DEBUG("In ~p module ~p onload data from mysql Host ~p ~n", [?MODULE, ?LINE, Host]),

    odbc_queries:sql_get_privacy_list_default(Host),
    PrivacyListDefault =
    case catch odbc_queries:sql_get_privacy_list_default(Host) of
          {selected, ["username", "name"], []} ->[];
          {selected, ["username", "name"], PrivacyListDefault1} -> PrivacyListDefault1
    end,

    PrivacyListData =
    case catch odbc_queries:sql_get_privacy_list_data(Host) of
          {selected, ["id", "t", "value", "action", "ord", "match_all",
                            "match_iq", "match_message",
                            "match_presence_in", "match_presence_out"],
                 []} ->[];
          {selected, ["id", "t", "value", "action", "ord", "match_all",
                            "match_iq", "match_message",
                            "match_presence_in", "match_presence_out"],
                 RItems} ->
                    Items = lists:map(fun raw_to_item_with_id/1, RItems),
                    Items
    end,

    case catch odbc_queries:sql_get_privacy_list(Host) of
         {selected, ["username", "name", "id"], []} -> [];
         {selected, ["username", "name", "id"], PrivacyList} ->
              assemble_data(PrivacyListDefault, PrivacyList, PrivacyListData, [],Host)
    end.


assemble_data(DList, [P | PList], PLData, Res,Host)->
    case P of
       {UserName, ListName, Id} ->
           Default =
           case [{K, V} || {K, V} <- DList, K =:= UserName] of
                [] -> none;
                [{UserName, ListName}] -> ListName;
                _ -> none
           end,
          Test =  [{listitem, Type, Value, Action, Order, All, Iq, Message, In, Out}
                 ||{Kid, {listitem, Type, Value, Action, Order, All, Iq, Message, In, Out}}
                  <- PLData, Kid =:= Id],

          ListItems =
          %case [{listitem, Type, Value, Action, Order, All, Iq, Message, In, Out}
          %       || {listitem, Type, Value, Action, Order, All, Iq, Message, In, Out}
          %        <- PLData, Order =:= Id] of
          case [ListItem
                 || {Kid, #listitem{}=ListItem}
                  <- PLData, Kid =:= Id] of
                ListItems1 when is_list(ListItems1)->
                      ListItems1;
                [] -> [];
                _ -> []
           end,
           Privacy = #privacy{us = {UserName, Host}, default = Default, lists = ListItems},
           mnesia:dirty_write(Privacy),
           assemble_data(DList, PList, PLData, Res,Host);
       _ ->
           assemble_data(DList, PList, PLData, Res,Host)
    end;
assemble_data(_DList, [], _PLData, Res,_Host)->
    Res.

 

mnesia 一张表

-record(privacy, {us,

                  default = none,

                  lists = []}).

Mysql  三张表..

mysql> describe privacy_list_data;

+--------------------+---------------+------+-----+---------+-------+

| Field              | Type          | Null | Key | Default | Extra |

+--------------------+---------------+------+-----+---------+-------+

| id                 | bigint(20)    | YES  | MUL | NULL    |       |

| t                  | char(1)       | NO   |     | NULL    |       |

| value              | text          | NO   |     | NULL    |       |

| action             | char(1)       | NO   |     | NULL    |       |

| ord                | decimal(10,0) | NO   |     | NULL    |       |

| match_all          | tinyint(1)    | NO   |     | NULL    |       |

| match_iq           | tinyint(1)    | NO   |     | NULL    |       |

| match_message      | tinyint(1)    | NO   |     | NULL    |       |

| match_presence_in  | tinyint(1)    | NO   |     | NULL    |       |

| match_presence_out | tinyint(1)    | NO   |     | NULL    |       |

+--------------------+---------------+------+-----+---------+-------+

10 rows in set (0.52 sec)

mysql> select * from privacy_list;

+--------------------+---------+----+---------------------+

| username           | name    | id | created_at          |

+--------------------+---------+----+---------------------+

| server_test_user_2 | newlist |  1 | 2011-03-23 10:53:51 |

| server_test_user_3 | newlist |  2 | 2011-03-23 11:07:57 |

| server_test_user_1 | newlist |  3 | 2011-03-23 11:08:29 |

+--------------------+---------+----+---------------------+

3 rows in set (0.10 sec)

mysql> select * from privacy_default_list;

+--------------------+---------+

| username           | name    |

+--------------------+---------+

| server_test_user_1 | newlist |

| server_test_user_2 | newlist |

| server_test_user_3 | newlist |

+--------------------+---------+

3 rows in set (0.00 sec)