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

mnesia加载mysql表数据(修改ejabberd privacy list)
前提：mnesia 一张表 privacymysql 三张表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)

查看更多 下一篇