Mnesia Study Notes (2)

 

2. Getting started with mnesia

A demo database is used. This example will be used later and some modifications will be made.

First, do the following:

● Start the Erlang session and specify the location of the mnesia Database

● Initialize the database structure

● Start mnesia to create the required data table

2.1 start mnesia for the first time

Open the doscommand Console window and run the following command:

D:/program files/erl5.6.5/bin> Erl-mnesia dir '"D:/Erlang/learn/DB /"'

Go to the Erlang shell and run the following statement:

Eshell v5.6.5 (abort with ^ g)

1> mnesia: create_schema ([node ()]).

You can see that the sub-directory dB is added under path D:/Erlang/learn /.

In addition, the database has a file fallback. Bup.

2> mnesia: Start ().

OK

3> mnesia: create_table (funky, []).

{Atomic, OK}

4> mnesia: Info ().

---> Processes holding locks <---

---> Processes waiting for locks <---

---> Maid <---

---> 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 "Blind Guide" Example

This example is for you, me, and other mnesia "blind. :-)

The mnesia database consists of a group of tables. The data member of each table is an Erlang record ). The table also has some attributes, such as location and Storage Period (persistence ).

In this example, the task is:

● Start the Erlang system and specify the database location;

● Initialize the database and register and record the nodes involved in database operations;

● Start mnesia.

● Create and fill in the form.

2.2.1 database example

The database model is as follows:

● Three entities: employee Employee, project, and department.

● There are three relationships between entities:

(1) departments are managed by employees to form a Manager relationship;

(2) employees work in the Department to form the at_dep relationship;

(3) Employees are forming in_proj relationships for project work.

2.2.2 definition structure and content

First, define records in the text file company. HRL to form a simple structure of our database:

-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 Application

% 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)}]).

Roughly repeat the previous example process:

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 <---

---> Maid <---

---> 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>

A group of tables is created:

Mnesia: create_table (name, Arglist ).

The specific value of the Arglist parameter will be discussed later.

To write a function and insert employee records into the database, you must have an at_dep record and a group of in_proj records. The following code completes this process:

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.

This function can be used as follows:

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 add records and relationships to the database

The result of appending a record to the database company is:

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

The above table is a real record, and the following is a link.

Manager

{Manager, 104465, 'B/SF '}.

{Manager, 104465, 'B/sfp '}.

{Manager, 114872, 'B/sfr '}.

At_dep

{At_dep, 104465, 'B/fs '}.

{At_dep, 107912, 'B/fs '}.

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

The database company now has initialization data.

2.2.7 write query statements

To retrieve data from a DBMS, use the mnesia: Read/3 or mnesia: Read/1 function. The following function is about "increase salary:

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 ).

There are two ways to query a database:

● Mnesia Functions

● Qlc

2.2.7.1 mnesia Function

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 ).

Operations in Erlang shell:

(Klacke @ gin) 1> company: all_females ().

{Atomic, ["Carlsson tuula", "fedoriw Anna"]}

2.2.7.2 use qlc

It is very troublesome to use qlc. It is not as straightforward as using the mnesia function, but the syntax of qlc is beautiful.

Select a collection of female employees from the database:

Q = qlc: Q ([E # employee. Name | E <-mnesia: Table (employee ),

E # employee. Sex = female]),

Qlc: E (Q ),

The qlc table meta selection function must be performed in transactions:

Females ()->

F = fun ()->

Q = qlc: Q ([E # employee. Name | E <-mnesia: Table (employee ),

E # employee. Sex = female]),

Qlc: E (q)

End,

Mnesia: transaction (f ).

The shell calls the function with the same results:

(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:

The table meta selection operation can be combined with the mnesia lower-level function call in the same transaction.

For example, raise the salary of all female employees:

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.