erlang-mnesia操作

-module(test_mnesia).
-import(lists, [foreach/2]).
-include_lib("stdlib/include/qlc.hrl").
-compile(export_all).
-record(shop, {id, item, quantity, cost}).%定义shop表的表结构
-record(items, {id, item_info}).%定义shop表的表结构
-ifdef(debug).
-define(LOG(X), io:format("pid:~p , {~p,~p}: ~p~n", [self(), ?MODULE, ?LINE, X])).
-else.
-define(LOG(X), io:format("pid:~p , {~p,~p}: ~p~n", [self(), ?MODULE, ?LINE, X])).
-endif.

do_this_once() ->
    mnesia:create_schema([node()]),
    mnesia:start(),
    %{type,set} 表示id作为主键，不允许id重复，如果改为 {type,bag}，id可以重复，但整条记录不能重复
    mnesia:create_table(shop, [{attributes, record_info(fields, shop)}, {type,set}, {disc_copies, [node()]} ]),
    mnesia:create_table(items, [{attributes, record_info(fields, items)}, {type,set}, {disc_copies, [node()]} ]),
    mnesia:stop().

start() ->
    mnesia:stop(),
    mnesia:start(),
    mnesia:wait_for_tables([shop], 2000),%等待应用程序需要的表初始化完成，0.002秒超时
    init_table(shop_info()).%写入数据

delete_table(TabelName) ->
    mnesia:delete_table(TabelName).

%方法1 获取所有数据
get_all1(TabelName) ->
    ?LOG(TabelName),
    Qh =qlc:q([X || X <- mnesia:table(TabelName)]),%select * from TableName
    F = fun() -> qlc:e(Qh) end,%将qlc：q/1编译的查询匹配给F
    {atomic, Val} = mnesia:transaction(F),%将F拿去走事务流程
    Val. %消除atomic，只取值
  %本来结果为：
  % {atomic,[{shop,apple,20,2.3},
  %        {shop,orange,100,3.8},
  %        {shop,pear,200,3.6},
  %        {shop,banana,420,4.5}]}
  % 处理后
  %  [{shop,apple,20,2.3},
  % {shop,orange,100,3.8},
  % {shop,pear,200,3.6},
  % {shop,banana,420,4.5}]
%方法2 简化获取所有数据
get_all2(TabelName) ->
    F = fun() ->
        Q = qlc:q([E || E <- mnesia:table(TabelName)]),
        qlc:e(Q)
        end,
    mnesia:transaction(F).
%方法3 获取所有数据
get_all3(TabelName) ->
    F = fun() ->
        MatchHead = #shop{ _ = '_' },
        Guard = [],
        Result = ['$_'],
        mnesia:select(TabelName, [{MatchHead, Guard, Result}])
    end,
    mnesia:transaction(F).

%start开始时插入的数据
shop_info() ->
    [%% The shop table
        {shop, 1, apple, 20, 2.3},
        {shop, 2, orange, 100, 3.8},
        {shop, 3, pear, 200, 3.6},
        {shop, 4, banana, 420, 4.5}
    ].
%执行start时自动操作的脏写操作，foreach从列表TableData中取出一条条执行
init_table(TableData) ->
    F = fun(Model) -> mnesia:dirty_write(Model) end,
    lists:foreach(F, TableData).

%where 方式1
where1() ->
    F = fun() ->
        MatchHead = #shop{id = '$1', item = '$2', _ = '_' },
        Guard = [{'>', '$1', 2}],
        Result = ['$2'],
        mnesia:select(shop, [{MatchHead, Guard, Result}])
    end,
    mnesia:transaction(F).
%where 方式2
where2() ->
    F = fun() ->
        Q = qlc:q([E#shop.item || E <- mnesia:table(shop), E#shop.id>2]),
        qlc:e(Q)
    end,
    mnesia:transaction(F).
% 如果查找主键 key=X 的记录 也可如下执行
where3() ->
    F = fun() ->
        mnesia:read({shop,5})
    end,
    mnesia:transaction(F).
% 查找非主键字段的记录 也可如下执行
where4() ->
    F = fun() ->
        MatchHead = #shop{ id = '_', item = banana, quantity = '_', cost = '_' },
        Guard = [],
        Result = ['$_'],
        mnesia:select(shop, [{MatchHead, Guard, Result}])
    end,
    mnesia:transaction(F).

% 查询部分字段 方式1
select_some1() ->
    F = fun() ->
        MatchHead = #shop{item = '$1', quantity = '$2', _ = '_' },
        Guard = [],
        Result = ['$$'],
        mnesia:select(shop, [{MatchHead, Guard, Result}])
    end,
    mnesia:transaction(F).
% 查询部分字段 方式2
select_some2() ->
    F = fun() ->
        Q = qlc:q([[E#shop.item, E#shop.quantity] || E <- mnesia:table(shop)]),
        qlc:e(Q)
    end,
    mnesia:transaction(F).

%===============================================
%   select * from shop order by id asc
%===============================================
% order by查询 方式1
order_by1() ->
    F = fun() ->
        Q = qlc:q([E || E <- mnesia:table(shop)]),
        % keysort(KeyPos, QH1, SortOptions) -> QH2 返回一个查询句柄。在评估查询句柄时QH2，查询句柄的答案根据选项QH1进行排序file_sorter:keysort/4。
        % keysort(KeyPos, QH1)相当于keysort(KeyPos, QH1, [])。 KeyPos元组的位置 id位于的二个位置 第一个是表名
        qlc:e(qlc:keysort(2, Q, [{order, ascending}]))
    end,
    mnesia:transaction(F).
% order by查询 方式2
order_by2() ->  
    F = fun() ->  
        Q = qlc:q([E || E <- mnesia:table(shop)]), 
        Order = fun(A, B) ->
            B#shop.id > A#shop.id
        end,
        qlc:e(qlc:sort(Q, [{order, Order}]))
    end,  
    mnesia:transaction(F).

% join 查询
%===============================================
%   select items.* from shop join items on (shop.id = items.id)
%      where shop.item = 'apple'
%===============================================
select_join() ->
F = fun() ->
    Q = qlc:q([Y || X <- mnesia:table(shop),
        X#shop.item =:= apple,
        Y <- mnesia:table(items),
        X#shop.id =:= Y#items.id
    ]),
    qlc:e(Q)
end,
mnesia:transaction(F).

%===============================================
%   select * from shop limit 2
%===============================================
% Limit 查询
select_limit2() -> 
    F = fun() ->
        Q = qlc:q([E || E <- mnesia:table(shop)]),
        QC = qlc:cursor(Q),
        qlc:next_answers(QC, 2)
    end,
    mnesia:transaction(F).

% Select count(*) 查询
select_count() ->
F = fun() ->
    mnesia:table_info(shop, size)
end,
mnesia:transaction(F).

%删除操作
delete(Item) ->
    Oid = {shop, Item},
    F = fun() ->
        mnesia:delete(Oid)
    end,
    mnesia:transaction(F).
%脏删除操作
dirty_delete(Item) ->
    Oid = {shop, Item},
    F = fun() ->
        mnesia:dirty_delete(Oid)
    end,
    mnesia:transaction(F).

%写操作
write() ->
    Oid =  {shop, 5, potato, 2456, 1.3},
    F = fun() ->
        mnesia:write(Oid)
    end,
    mnesia:transaction(F).
%脏写操作
dirty_write() ->
    Oid =  {shop, 5, potato, 2456, 1.2},
    F = fun() ->
        mnesia:dirty_write(Oid)
    end,
    mnesia:transaction(F).
% Insert / Update 操作  mnesia是根据主键去更新记录的，如果主键不存在则插入
write_test(Args) ->
    [Id, Item, Quantity, Cost] = Args,
    F = fun() ->
        Q = #shop{id = Id, item = Item, quantity = Quantity, cost = Cost},
        mnesia:write(Q)
    end,
    mnesia:transaction(F).


% 封装的执行增加，删除的方法，测试脏操作不支持事务  
% 脏操作指在事务的范围以外而且没有设置任何锁来执行的操作
% apply 执行Function传入参数为Pram  true = ok必定出错，不匹配  
% 执行test_mnesia:execute(fun test_mnesia:delete/1,[apple]).与test_mnesia:execute(fun test_mnesia:dirty_delete/1,[apple]). 
% 不是脏删除时，因为支持事务，执行失败  是脏删除时，因为不支持事务，执行成功 
execute(Function,Pram) when is_function(Function),is_list(Pram) ->
    F = fun() ->
        apply(Function,Pram)
        ,true = ok  %在事物里制造问题，验证事物的有效性
    end,
    mnesia:transaction(F).

参考：
https://www.jianshu.com/p/2d43992c829c
https://blog.csdn.net/mycwq/article/details/12158967