前提:
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)