Mnesia用户手册:二，Mnesia快速上手

本章介绍了Mnesia：
1）启动一个Erlang session并制定Mnesia数据库的目录
2）初始化数据库结构
3）启动Mnesia并创建必要的表

1，初次启动Mnesia
以下是Mnesia系统启动的一个简单展示：

unix> erl -mnesia dir '"/tmp/funky"'
Erlang (BEAM) emulator version 4.9

Eshell V4.9 (abort with ^G)
1>
1> mnesia:create_schema([node()]).
ok
2> mnesia:start().
ok
3> mnesia:create_table(funky, []).
{atomic,ok}
4> mnesia:info().
---> Processes holding locks <---
---> Processes waiting for locks <---
---> Pending (remote) transactions <---
---> Active (local) transactions <---
---> Uncertain transactions <---
---> Active tables <---
funky : with 0 records occupying 269 words of mem
schema : with 2 records occupying 353 words of mem
===> System info in version "1.0", debug level = none <===
opt disc. Directory "/tmp/funky" is used.
use fall-back at restart = false
running db nodes = [nonode@nohost]
stopped db nodes = []
remote = []
ram copies = [funky]
disc copies = [schema]
disc only copies = []
[fnonode@nohost,disc copiesg] = [schema]
[fnonode@nohost,ram copiesg] = [funky]
1 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

上面的例子里：
1）启动erl时参数-mnesia dir '"/tmp/funky"'指定了Mnesia存储数据的目录，windows下可以是erl -mnesia dir 'D:/erl/code'
2）mnesia:create_schema([node()])在本地节点上初始化一个空的schema
3）DBMS通过mnesia:start()启动
4）通过mnesia:create_table(funky, [])来创建表funky
5）mnesia:info()根据数据库的状态来显示信息

2，一个例子
Mnesia数据库组织为一个表的集合，一个表也包含一些属性，如location和persistence
在这个例子中：
1）启动一个Erlang系统，指定数据库位置目录
2）初始化一个新的schema，使用一个属性来指定数据库在那些节点上操作
3）启动Mnesia本身
4）创建数据库表

例子数据库
在这个数据库例子里，我们将创建如下数据库和关系，称为Company数据库
Company ER图：

id name   emp_no name salary (s e x) phone room_no         name number\    /               \        /                         \       / Dept   - Manager -   Employee        - In_proj -        Project     \              /         At_dep

数据库模型如下：
1）有三个实体：employee，project，department
2）这些实体间有三个关系：
  i）一个department由一个employee管理，manager关系
  ii）一个employee在一个department工作，at_dep关系
  iii）每个employee对多个project工作，in_proj关系

定义结构和内容
我们首先将record定义输入到一个company.hrl文件，该文件定义了如下结构：

-record(employee, {emp_no,
                   name,
                   salary,
                   ***,
                   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}).

该结构在我们的数据库定义了6个表
在Mnesia里，mnesia:create_table(Name, ArgList)用来创建表
Name是表名，当前的Mnesia不要求表名和record名一样
例如，employee表的创建为mnesia:create_table(employee, [{attributes, record_info(fields, employee)}])
record_info(fields, RecordName)表达式由Erlang预处理程序处理为一个包含一个record的不同fields的list

程序
以下启动一个Mnesia并初始化company数据库的schema

% erl -mnesia dir '"/ldisc/scratch/Mnesia.Company"'
Erlang (BEAM) emulator version 4.9

Eshell V4.9 (abort with ^G)
1> mnesia:create_schema([node()]).
ok
2) mnesia:start().
ok

以下程序模块创建前面定义的表：

-include_lib("stdlib/include/qlc.hrl").
-include("company.hrl").

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, [{type, set}, {attributes, record_info(fields, at_dep)}]),
  mnesia:create_table(in_proj, [{type, bag}, {attributes, record_info(fields, in_proj)}]),

程序的解释
以下命令用来初始化Company数据库：
1）% erl -mnesia dir '"/ldisc/scratch/Mnesia.Company"'，-mnesia dir Dir指定了数据库目录的位置
2）mnesia:create_schema([nocde()])初始化一个新的schema
3）mnesia:start()启动Mnesia
4）mnesia:create_table(Name, ArgList)用来创建必要的数据库表
下面继续：

3> company:init().
{atomic,ok}
4> mnesia:info().
nsactions <---
---> Active tables <---
in proj : with 0 records occuping 269 words of mem
at dep : with 0 records occuping 269 words of mem
manager : with 0 records occuping 269 words of mem
project : with 0 records occuping 269 words of mem
dept : with 0 records occuping 269 words of mem
employee : with 0 records occuping 269 words of mem
schema : with 7 records occuping 571 words of mem
===> System info in version "1.0", debug level = none <===
opt disc. Directory "/ldisc/scratch/Mnesia.Company" is used.
use fall-back at restart = false
running db nodes = [nonode@nohost]
stopped db nodes = []
remote = []
ram copies =
[at dep,dept,employee,in proj,manager,project]
disc copies = [schema]
disc only copies = []
[fnonode@nohost,disc copiesg] = [schema]
[fnonode@nohost,ram copiesg] =
[employee,dept,project,manager,at dep,in proj]
6 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

company:init/0创建我们的表，有两个表类型是bag
bag表示one-to-many，set表示one-to-one
mnesia:info()现在显示数据库有7个本地表，其中6个我们定义的表，另一个是schema
6个事务被提交，因为创建表时6个事务被成功运行

插入一个employee record到数据库、一个at_dep record和一些in_proj record的例子：

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.

insert_emp/3的参数为：
1）Emp是employee record
2）DeptId是department的id
3）ProjNames是project的name的list
insert_emp/3 -> function创建一个函数式对象，函数式对象由Fun来表示
mnesia:transaction(Fun)表示Fun位于一个事务中：
1）Fun要么完全成功要么完全失败
2）操作同样数据record的代码可以并行运行，不同的进程不会相互干扰
该方法可以这样使用：

Me = #employee{emp_no= 104732,
                name = klacke,
                salary = 7,
                *** = male,
                phone = 98108,
                room_no = {221, 015}},
insert_emp(Me, 'B/SFR', [erlang, mnesia, otp]).

初始化数据库内容
employee表的数据对应的tuple展现: {employee, 104732, klacke, 7, male, 98108, {221, 015}}
at_dep表的数据对应的tuple展现: {at_dep, klacke, 'B/SFR'}
in_proj表的数据对应的tuple展现: {in_proj, klacke, erlang, klacke, otp, klacke, mnesia}
Mnesia表由Mnesia record展现，如tuple{boss, klacke, bjarne}是一个record，第一个元素为表名，第二个为key
object identifier(oid)是指{Tab, Key}这个tuple，第一个元素为表名，第二个为key
一个oid可以对应0、1或多个record，这取决于表类型是set或bag
我们可以插入{boss, klacke, bjarne}record到数据库，Mnesia不强迫非得有klacke和bjarne这两个employeee的数据

添加记录和关系到数据库
employee

{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"}.

project

{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}.

写查询语句
从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).

由于我们希望在增加salary之后使用mnesia:write/1来更新record，所以我们在从table读数据时获得一个写lock(read方法的第三个参数)
有时候我们需要搜索多个表才能获取数据，这种查询比直接的mnesia:read开销要大很多
有两种方式来写数据库查询:
1) Mnesia方法
2) QLC

Mnesia方法
从数据库获取女性employee的名字：

mnesia:select(employee, [{#employee{*** = female, name = '$1', _ = '_'}, [], ['$1']}]).

select必须运行在transaction等activity里面，所有我们需要构造一个方法：

all_females() ->
  F = fun() ->
    Female = #employee{*** = female, name = '$1', _ = '_'},
    mnesia:select(employee, [{Female, [], ['$1']}])
  end,
  mnesia:transaction(F).

select表达式匹配employee表里所有的记录中***为female的记录
该方法可以从shell里直接调用：

1> company:all_females().
{atomic, ["Carlsson Tuula", "Fedoriw Anna"]}

使用QLC
使用QLC可能比使用Mnesia方法开销更大，但是它提供了一个很好的语法

Q = qlc:q([E#employee.name || E <- mnesia:table(employee), E#employee.*** == female]),
qlc:e(Q).

使用QLC list comprehension来访问Mnesia表时必须运行在一个transaction里：

females() ->
  F = fun() ->
    Q = qlc:q([E#employee.name || E <- mnesia:table(employee), E#employee.*** == female]),
    qlc:e(Q)
  end,
  mnesia:transaction(F).

调用QLC写的方法：

company:females().

list comprehension表达式：
1）[括号表示“构建list”
2）||表示“例如”，<-表示“从哪里取”
上面的list comprehension表示：构建list E#employee.name，E来自employee表，并且***属性等于female
raise female salary的方法：

raise_females(Amount) ->
  F = fun() ->
    Q = qlc:q([E || E <- mnesia:table(employee),
    E#employee.*** == 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.