mnesia数据库操作

%% @author ping
%% @doc @todo Add description to mnesiaTest.


-module(mnesiaTest).

%% ====================================================================
%% API functions
%% ====================================================================
-export([]).
-import(lists,[foreach/2]).
-compile(export_all).
%%导入qlc工具
-include_lib("stdlib/include/qlc.hrl").

-record(shop,{item,quantity,cost}).
-record(cost,{name,price}).
-record(design,{id,plan}).

%% ====================================================================
%% Internal functions
%% ====================================================================

do_this_once() ->
	mnesia:create_schema([node()]),
	mnesia:start(),
	mnesia:create_table(shop,   [{attributes,record_info(fields,shop)}]),
	mnesia:create_table(cost,   [{attributes,record_info(fields,cost)}]),
	mnesia:create_table(design, [{attributes,record_info(fields,design)}]),
	mnesia:stop().
start() -> 
	mnesia:start(),
%%等待加载表
	mnesia:wait_for_tables([shop,cost,design], 2000).
%% 查询数据

%% select * from shop;
demo(select_shop) ->
	do(qlc:q([X || X <- mnesia:table(shop)]));

%% select item,quantity from shop;
demo(select_some) ->
	do(qlc:q([{X#shop.item,X#shop.quantity} || X <- mnesia:table(shop)]));

%% select shop.item from shop
%% where shop.quantity < 250 ;
demo(reorder) ->
	do(qlc:q([X#shop.item || X <- mnesia:table(shop),
							 X#shop.quantity<250]
								));

%% select shop.item, shop.quantity, cost.name, cost.price
%% form shop,cost
%% where shop.item = cost.name
%%   and cost.price < 2
%%   and shop.quantity < 250
demo(join) ->
	do(qlc:q([X#shop.item || X <- mnesia:table(shop),
							 X#shop.quantity < 250,
							 Y <- mnesia:table(cost),
							 Y#cost.price < 2,
							 X#shop.item =:= Y#cost.name
							 	])).

do(Q) -> 
	F = fun() -> 
				qlc:e(Q) 
		end,
	{atomic,Val} = mnesia:transaction(F),
	Val.

%% 手动导入数据
example_tables() ->
	[%% the shop table
	 {shop, apple,  20,   2.3},
	 {shop, orange, 100,  3.8},
	 {shop, pear,   200,  3.6},
	 {shop, banana, 420,  4.5},
	 {shop, potato, 2425, 1.2},
	 %% the cost table
	 {cost, apple,  1.5},
	 {cost, orange, 2.4},
	 {cost, pear,   2.2},
	 {cost, banana, 1.5},
	 {cost, potato, 0.6}
	].

%% 增加shop数据
add_shop_item(Name,Quantity,Cost) ->
	Row = #shop{item=Name, quantity = Quantity, cost = Cost},
	F = fun() ->
				mnesia:write(Row)
		end,
	mnesia:transaction(F).

%% 删除shop数据
remove_shop_item(Item) ->
	Oid = {shop,Item},
	F = fun() -> 
				mnesia:delete(Oid)
		end,
	mnesia:transaction(F).

%% 更改数据
farmer(Nwant) ->
	%%
	F = fun() ->
				[Apple] = mnesia:read({shop,apple}),
				Napples  = Apple#shop.quantity,
				Apple1  = Apple#shop{quantity = Napples + 2*Nwant},
				mnesia:write(Apple1),
				[Orange] = mnesia:read({shop,orange}),
				NOranges = Orange#shop.quantity,
				if
					NOranges >= Nwant ->
						N1 = NOranges - Nwant,
						Orange1 = Orange#shop{quantity = N1},
						%% 更改数据库中的数据
						mnesia:write(Orange1);
					true ->
						mnesia:abort(oranges)
				end
		end,
	mnesia:transaction(F).

%% 初始化数据库，导入example_tables()中的数据
reset_tables() ->
	mnesia:clear_table(shop),
	mnesia:clear_table(cost),
	F = fun() ->
				foreach(fun mnesia:write/1,example_tables())
		end,
	mnesia:transaction(F).
%% 增加数据
add_plans() ->
	D1 = #design{id  = {joe,1},
				plan = {circle,10}},
	D2 = #design{id  = fred,
				plan = {rectangle,10,5}},
	F = fun() -> 
				mnesia:write(D1),
				mnesia:write(D2)
		end,
	mnesia:transaction(F).
%% 读取数据
get_plan(PlanId) ->
	F = fun() -> 
				mnesia:read({design,PlanId}) 
		end,
	mnesia:transaction(F).