1 -module(test_mnesia). 2 -compile(export_all). 3 4 -include_lib("stdlib/include/qlc.hrl"). 5 6 %% 定义记录结构 7 -record(shop,{item,quantity,cost}). 8 -record(cost,{name,price}). 9 -record(design,{id,plan}). 10 11 start() -> 12 mnesia:start(), 13 %% 等待表的加载 14 mnesia:wait_for_tables([shop,cost,design],20000). 15 16 %% 初始化mnesia表结构 17 init() -> 18 mnesia:create_schema([node()]), 19 mnesia:start(), 20 %% 表创建 mnesia:create_talbe(TableName,[Args]) 21 %% {type,Type} set,ordered_set,bag 表类型 22 %% {ram_copies,NodeList} NodeList每个节点都有内存备份 默认为这个{ram_copies,[node()]} 23 %% {disc_copies,NodeList} NodeList每个节点都有内存备份和磁盘备份 24 %% {disc_only_copies,NodeList} NodeList每个节点有磁盘备份 25 %% {attributes,AtomList} 要保存的列名称 一般和record有关 record_info(fields,RecordName) 26 mnesia:create_table(shop,[{attributes,record_info(fields,shop)}]), %% 创建shop表 27 mnesia:create_table(cost,[{attributes,record_info(fields,cost)}]), 28 mnesia:create_table(design,[{attributes,record_info(fields,design)}]), 29 mnesia:stop(). 30 31 %% 加载测试数据 32 reset_tables() -> 33 mnesia:clear_table(shop), 34 mnesia:clear_table(cost), 35 F = fun() -> 36 lists:foreach(fun mnesia:write/1,example_tables()) 37 end, 38 mnesia:transaction(F). 39 40 41 %% 测试数据 42 example_tables() -> 43 [ 44 %% shop table 45 {shop,apple,20,2.3}, 46 {shop,orange,100,3.8}, 47 {shop,pear,200,3.6}, 48 {shop,banana,420,4.5}, 49 {shop,potato,2456,1.2}, 50 %% cost table 51 {cost,apple,1.5}, 52 {cost,orange,2.4}, 53 {cost,pear,2.2}, 54 {cost,banana,1.6}, 55 {cost,potato,0.6} 56 ]. 57 58 %%== 查询 ============================================================= 59 60 do(Q) -> 61 F = fun() -> qlc:e(Q) end, 62 {atomic,Val} = mnesia:transaction(F), 63 Val. 64 65 %% SELECT * FROM shop 66 %% 选取所有列 67 demo(select_shop) -> 68 do(qlc:q([X || X <- mnesia:table(shop)])); 69 70 %% SELECT item,quantity FROM shop 71 %% 选取指定列 72 demo(select_some) -> 73 do(qlc:q([{X#shop.item, X#shop.quantity} || X <- mnesia:table(shop)])); 74 75 %% SELECT * FROM shop WHERE shop.quantity < 250 76 %% 选取指定条件的数据 77 demo(where) -> 78 do(qlc:q([X || X <- mnesia:table(shop), 79 X#shop.quantity < 250 80 ])); 81 82 %% 关联查询 83 %% SELECT shop.* FROM shop,cost wHERE shop.item = cost.name AND cost.price < 2 AND shop.quantity < 250 84 demo(join) -> 85 do(qlc:q([X || X <- mnesia:table(shop), 86 X#shop.quantity < 250, 87 Y <- mnesia:table(cost), 88 X#shop.item =:= Y#cost.name, 89 Y#cost.price < 2 90 ])). 91 92 %% == 数据操作 =============================================== 93 94 %% 增加一行 95 add_shop_item(Name,Quantity,Cost) -> 96 Row = #shop{item = Name,quantity = Quantity, cost = Cost}, 97 F = fun() -> 98 mnesia:write(Row) 99 end, 100 mnesia:transaction(F). 101 102 %% 删除一行 103 remove_shop_item(Item) -> 104 Oid = {shop,Item}, 105 F = fun() -> 106 mnesia:delete(Oid) 107 end, 108 mnesia:transaction(F). 109 110 %% 取消一个事务 111 former(Nwant) -> 112 F = fun() -> 113 %% find the num of apples 114 [Apple] = mnesia:read({shop,apple}), 115 Napples = Apple#shop.quantity, 116 %% update the database 117 NewApple = Apple#shop{quantity = Napples + 2 * Nwant}, 118 mnesia:write(NewApple), 119 %% find the num of oranges 120 [Orange] = mnesia:read({shop,orange}), 121 Noranges = Orange#shop.quantity, 122 if 123 Noranges >= Nwant -> 124 %% update the database 125 Num = Noranges - Nwant, 126 NewOrange = Orange#shop{quantity = Num}, 127 mnesia:write(NewOrange); 128 true -> 129 %% no enough oranges 取消事务 130 mnesia:abort(oranges) 131 end 132 end, 133 mnesia:transaction(F). 134 135 %% 保存复杂数据 136 add_plans() -> 137 D1 = #design{ 138 id = {joe,1}, 139 plan = {circle,10} 140 }, 141 D2 = #design{ 142 id = fred, 143 plan = {rectangle,[10,5]} 144 }, 145 F = fun() -> 146 mnesia:write(D1), 147 mnesia:write(D2) 148 end, 149 mnesia:transaction(F). 150 151 %% 获复杂数据 152 get_plans(PlanId) -> 153 F = fun() -> mnesia:read({design,PlanId}) end, 154 mnesia:transaction(F).
运行结果
Eshell V5.8.4 (abort with ^G) 1> c(test_mnesia). {ok,test_mnesia} 2> test_mnesia:init(). stopped =INFO REPORT==== 16-May-2012::17:24:29 === application: mnesia exited: stopped type: temporary 3> test_mnesia:start(). ok 4> test_mnesia:reset_tables(). {atomic,ok} 5> test_mnesia:demo(select_shop). [{shop,potato,2456,1.2}, {shop,orange,100,3.8}, {shop,apple,20,2.3}, {shop,pear,200,3.6}, {shop,banana,420,4.5}] 6> test_mnesia:demo(select_some). [{potato,2456}, {orange,100}, {apple,20}, {pear,200}, {banana,420}] 7> test_mnesia:demo(where). [{shop,orange,100,3.8}, {shop,apple,20,2.3}, {shop,pear,200,3.6}] 8> test_mnesia:demo(join). [{shop,apple,20,2.3}] 9> test_mnesia:add_shop_item(apple,236,2.8). {atomic,ok} 10> test_mnesia:demo(select_shop). [{shop,potato,2456,1.2}, {shop,orange,100,3.8}, {shop,apple,236,2.8}, {shop,pear,200,3.6}, {shop,banana,420,4.5}] 11> test_mnesia:add_shop_item(egg,236,2.8). {atomic,ok} 12> test_mnesia:demo(select_shop). [{shop,potato,2456,1.2}, {shop,egg,236,2.8}, {shop,orange,100,3.8}, {shop,apple,236,2.8}, {shop,pear,200,3.6}, {shop,banana,420,4.5}] 13> test_mnesia:delete_shop_item(egg). ** exception error: undefined function test_mnesia:delete_shop_item/1 14> test_mnesia:remove_shop_item(egg). {atomic,ok} 15> test_mnesia:demo(select_shop). [{shop,potato,2456,1.2}, {shop,orange,100,3.8}, {shop,apple,236,2.8}, {shop,pear,200,3.6}, {shop,banana,420,4.5}] 16> test_mnesia:former(50). {atomic,ok} 17> test_mnesia:demo(select_shop). [{shop,potato,2456,1.2}, {shop,orange,50,3.8}, {shop,apple,336,2.8}, {shop,pear,200,3.6}, {shop,banana,420,4.5}] 18> test_mnesia:former(100). {aborted,oranges} 19> test_mnesia:demo(select_shop). [{shop,potato,2456,1.2}, {shop,orange,50,3.8}, {shop,apple,336,2.8}, {shop,pear,200,3.6}, {shop,banana,420,4.5}] 20> test_mnesia:add_plans(). {atomic,ok} 21> test_mnesia:get_plans(a). {atomic,[]} 22> test_mnesia:get_plans(fred). {atomic,[{design,fred,{rectangle,[10,5]}}]} 23> test_mnesia:get_plans(joe). {atomic,[]} 24>