Mnesia 学习笔记
2、Mnesia 入门
一个演示用的数据库例子。这个例子以后还会用到，也会做些修改。
首先要做以下事情：
● 启动Erlang会话，指定Mnesia数据库的位置
● 初始化数据库结构
● 启动Mnesia，创建所需数据表格
2.1 第一次启动 Mnesia
打开DOS命令控制台窗口，执行这句：
D:\Program Files\erl5.6.5\bin > erl -mnesia dir '"d:/erlang/learn/db/"'
进入Erlang的shell，执行语句：
Eshell V5.6.5 (abort with ^G)
1> mnesia:create_schema([node()]).
这时，你可看到，在路径 d:/erlang/learn/ 下新增子目录 db
并且，db 中有一文件 FALLBACK.BUP
2> mnesia:start().
ok
3> mnesia:create_table(funky, []).
{atomic,ok}
4> mnesia:info().
---> Processes holding locks <---
---> Processes waiting for locks <---
---> Participant transactions <---
---> Coordinator transactions <---
---> Uncertain transactions <---
---> Active tables <---
funky : with 0 records occupying 279 words of mem
schema : with 2 records occupying 499 words of mem
===> System info in version "4.4.7", debug level = none <===
opt_disc. Directory "d:/erlang/learn/db" is used.
use fallback at restart = false
running db nodes = [nonode@nohost]
stopped db nodes = []
master node tables = []
remote = []
ram_copies = [funky]
disc_copies = [schema]
disc_only_copies = []
[{nonode@nohost,disc_copies}] = [schema]
[{nonode@nohost,ram_copies}] = [funky]
3 transactions committed, 0 aborted, 0 restarted, 1 logged to disc
0 held locks, 0 in queue; 0 local transactions, 0 remote
0 transactions waits for other nodes: []
ok
5>
2.2 “导盲”示例
这个例子是为你、我、他这些Mnesia“盲”预备的。:-)
Mnesia 数据库由一组表格构成。每个表格的数据成员是Erlang的记录（record）。表格还有些属性，譬如位置和存期（persistence）。
本例的任务是：
● 启动 Erlang 系统，指定数据库所处位置；
● 对数据库进行初始化，将数据库操作涉及的节点，登记备案；
● 启动 Mnesia。
● 创建并填写表格。
2.2.1 数据库例子
数据库模型如下：
● 三个实体：员工employee，项目project，部门department。
● 实体之间有三种关系：
（1）部门由员工管理，形成manager关系；
（2）员工在部门中工作，形成at_dep关系；
（3）员工都在为项目工作形成in_proj关系。
2.2.2 定义结构和内容
首先在文本文件company.hrl中定义记录，形成我们数据库的简单结构：
-record(employee, {emp_no,
name,
salary,
sex,
phone,
room_no}).
-record(dept, {id,
name}).
-record(project, {name,
number}).
-record(manager, {emp,
dept}).
-record(at_dep, {emp,
dept_id}).
-record(in_proj, {emp,
proj_name}).
2.2.3 应用程序
%% company.erl
-module(company).
-include_lib("stdlib/include/qlc.hrl").
-include("d:/erlang/learn/db/company.hrl").
-export([init/0]).
init() ->
mnesia:create_table(employee,
[{attributes, record_info(fields, employee)}]),
mnesia:create_table(dept,
[{attributes, record_info(fields, dept)}]),
mnesia:create_table(project,
[{attributes, record_info(fields, project)}]),
mnesia:create_table(manager, [{type, bag},
{attributes, record_info(fields, manager)}]),
mnesia:create_table(at_dep,
[{attributes, record_info(fields, at_dep)}]),
mnesia:create_table(in_proj, [{type, bag},
{attributes, record_info(fields, in_proj)}]).
大致重复上例过程：
D:\Program Files\erl5.6.5\bin>erl -mnesia dir '"d:/erlang/learn/db/Mnesia.Company"'
Eshell V5.6.5 (abort with ^G)
1> mnesia:create_schema([node()]).
ok
2> mnesia:start().
ok
3> c("d:/erlang/learn/db/company").
{ok,company}
4> company:init().
{atomic,ok}
4> mnesia:info().
---> Processes holding locks <---
---> Processes waiting for locks <---
---> Participant transactions <---
---> Coordinator transactions <---
---> Uncertain transactions <---
---> Active tables <---
in_proj : with 0 records occupying 279 words of mem
at_dep : with 0 records occupying 279 words of mem
manager : with 0 records occupying 279 words of mem
project : with 0 records occupying 279 words of mem
dept : with 0 records occupying 279 words of mem
employee : with 0 records occupying 279 words of mem
schema : with 7 records occupying 1057 words of mem
===> System info in version "4.4.7", debug level = none <===
opt_disc. Directory "d:/erlang/learn/db/Mnesia.Company" is used.
use fallback at restart = false
running db nodes = [nonode@nohost]
stopped db nodes = []
master node tables = []
remote = []
ram_copies = [at_dep,dept,employee,in_proj,manager,project]
disc_copies = [schema]
disc_only_copies = []
[{nonode@nohost,disc_copies}] = [schema]
[{nonode@nohost,ram_copies}] = [employee,dept,project,manager,at_dep,in_proj]
8 transactions committed, 0 aborted, 0 restarted, 6 logged to disc
0 held locks, 0 in queue; 0 local transactions, 0 remote
0 transactions waits for other nodes: []
ok
5>
创建了一组表格：
mnesia:create_table(Name,ArgList).
参数 ArgList 的具体取值以后会讲。
要写个函数，把员工记录插入数据库中，就必须有个at_dep记录和一组in_proj记录。下面的代码完成此事：
insert_emp(Emp, DeptId, ProjNames) ->
Ename = Emp#employee.name,
Fun = fun() ->
mnesia:write(Emp),
AtDep = #at_dep{emp = Ename, dept_id = DeptId},
mnesia:write(AtDep),
mk_projs(Ename, ProjNames)
end,
mnesia:transaction(Fun).
mk_projs(Ename, [ProjName|Tail]) ->
mnesia:write(#in_proj{emp = Ename, proj_name = ProjName}),
mk_projs(Ename, Tail);
mk_projs(_, []) -> ok.
这个函数可以这样用：
Emp = #employee{emp_no= 104732,
name = klacke,
salary = 7,
sex = male,
phone = 98108,
room_no = {221, 015}},
insert_emp(Me, 'B/SFR', [Erlang, mnesia, otp]).
2.2.6 向数据库增加记录和关系
向数据库Company追加记录的结果是：
Employees
{employee, 104465, "Johnson Torbjorn", 1, male, 99184, {242,038}}.
{employee, 107912, "Carlsson Tuula", 2, female,94556, {242,056}}.
{employee, 114872, "Dacker Bjarne", 3, male, 99415, {221,035}}.
{employee, 104531, "Nilsson Hans", 3, male, 99495, {222,026}}.
{employee, 104659, "Tornkvist Torbjorn", 2, male, 99514, {222,022}}.
{employee, 104732, "Wikstrom Claes", 2, male, 99586, {221,015}}.
{employee, 117716, "Fedoriw Anna", 1, female,99143, {221,031}}.
{employee, 115018, "Mattsson Hakan", 3, male, 99251, {203,348}}.
Dept
{dept, 'B/SF', "Open Telecom Platform"}.
{dept, 'B/SFP', "OTP - Product Development"}.
{dept, 'B/SFR', "Computer Science Laboratory"}.
Projects
%% projects
{project, erlang, 1}.
{project, otp, 2}.
{project, beam, 3}.
{project, mnesia, 5}.
{project, wolf, 6}.
{project, documentation, 7}.
{project, www, 8}.
上面的表格是真实的记录，下面的是建立关系的。
Manager
{manager, 104465, 'B/SF'}.
{manager, 104465, 'B/SFP'}.
{manager, 114872, 'B/SFR'}.
At_dep
{at_dep, 104465, 'B/SF'}.
{at_dep, 107912, 'B/SF'}.
{at_dep, 114872, 'B/SFR'}.
{at_dep, 104531, 'B/SFR'}.
{at_dep, 104659, 'B/SFR'}.
{at_dep, 104732, 'B/SFR'}.
{at_dep, 117716, 'B/SFP'}.
{at_dep, 115018, 'B/SFP'}.
In_proj
{in_proj, 104465, otp}.
{in_proj, 107912, otp}.
{in_proj, 114872, otp}.
{in_proj, 104531, otp}.
{in_proj, 104531, mnesia}.
{in_proj, 104545, wolf}.
{in_proj, 104659, otp}.
{in_proj, 104659, wolf}.
{in_proj, 104732, otp}.
{in_proj, 104732, mnesia}.
{in_proj, 104732, erlang}.
{in_proj, 117716, otp}.
{in_proj, 117716, documentation}.
{in_proj, 115018, otp}.
{in_proj, 115018, mnesia}.
数据库Company现在有了初始化数据。
2.2.7 写查询语句
从DBMS检索数据，通常应当使用函数 mnesia:read/3 或 mnesia:read/1。下面的函数是关于“增加工资”的：
raise(Eno, Raise) ->
F = fun() ->
[E] = mnesia:read(employee, Eno, write),
Salary = E#employee.salary + Raise,
New = E#employee{salary = Salary},
mnesia:write(New)
end,
mnesia:transaction(F).
进行数据库查询，有2个办法：
● Mnesia functions
● QLC
2.2.7.1 Mnesia 函数
mnesia:select(employee, [{#employee{sex = female, name = '$1', _ = '_'},[], ['$1']}]).
all_females() ->
F = fun() ->
Female = #employee{sex = female, name = '$1', _ = '_'},
mnesia:select(employee, [{Female, [], ['$1']}])
end,
mnesia:transaction(F).
在Erlang shell中的操作：
(klacke@gin)1> company:all_females().
{atomic, ["Carlsson Tuula", "Fedoriw Anna"]}
2.2.7.2 使用 QLC
使用QLC很费事，不如用Mnesia函数来得直白，但QLC的语法美观。
从数据库中选出女员工的集合：
Q = qlc:q([E#employee.name || E <- mnesia:table(employee),
E#employee.sex == female]),
qlc:e(Q),
用QLC表元选择功能必须在事务中进行：
females() ->
F = fun() ->
Q = qlc:q([E#employee.name || E <- mnesia:table(employee),
E#employee.sex == female]),
qlc:e(Q)
end,
mnesia:transaction(F).
下面是结果相同的shell调用函数：
(klacke@gin)1> company:females().
{atomic, ["Carlsson Tuula", "Fedoriw Anna"]}
It is possible to combine list comprehensions with low level Mnesia functions in the same transaction. If we want to raise the salary of all female employees we execute:
可以把表元选择的操作，与Mnesia低层函数调用，结合在同一事务里。
如提高全体女员工的工资：
raise_females(Amount) ->
F = fun() ->
Q = qlc:q([E || E <- mnesia:table(employee),
E#employee.sex == female]),
Fs = qlc:e(Q),
over_write(Fs, Amount)
end,
mnesia:transaction(F).
over_write([E|Tail], Amount) ->
Salary = E#employee.salary + Amount,
New = E#employee{salary = Salary},
mnesia:write(New),
1 + over_write(Tail, Amount);
over_write([], _) ->
0.
