3 Getting Started

This section introduces Mnesia with an example database. This example is referenced in the following sections, where the example is modified to illustrate various program constructs. This section illustrates the following mandatory procedures through examples:

• Starting the Erlang session.
• Specifying the Mnesia directory where the database is to be stored.
• Initializing a new database schema with an attribute that specifies on which node, or nodes, that database is to operate.
• Starting Mnesia.
• Creating and populating the database tables.

3.1  Starting Mnesia for the First Time

This section provides a simplified demonstration of a Mnesia system startup. The dialogue from the Erlang shell is as follows:

        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 = []
        [{nonode@nohost,disc_copies}] = [schema]
        [{nonode@nohost,ram_copies}] = [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      
    

In this example, the following actions are performed:

• Step 1: The Erlang system is started from the UNIX prompt with a flag -mnesia dir '"/tmp/funky"', which indicates in which directory to store the data.
• Step 2: A new empty schema is initialized on the local node by evaluating mnesia:create_schema([node()]). The schema contains information about the database in general. This is explained in detail later.
• Step 3: The DBMS is started by evaluating mnesia:start().
• Step 4: A first table is created, called funky, by evaluating the expression mnesia:create_table(funky, []). The table is given default properties.
• Step 5: mnesia:info() is evaluated to display information on the terminal about the status of the database.

3.2  Example

A Mnesia database is organized as a set of tables. Each table is populated with instances (Erlang records). A table has also a number of properties, such as location and persistence.

Database

This example shows how to create a database called Company and the relationships shown in the following diagram:

Figure 3.1:   Company Entity-Relation Diagram

The database model is as follows:

• There are three entities: department, employee, and project.

• There are three relationships between these entities:

  1. A department is managed by an employee, hence the manager relationship.
  2. An employee works at a department, hence the at_dep relationship.
  3. Each employee works on a number of projects, hence the in_proj relationship.

Defining Structure and Content

First the record definitions are entered into a text file named company.hrl. This file defines the following structure for the example 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}).

The structure defines six tables in the database. In Mnesia, the function mnesia:create_table(Name, ArgList) creates tables. Name is the table name.

For example, the table for employees is created with the function mnesia:create_table(employee, [{attributes, record_info(fields, employee)}]). The table name employee matches the name for records specified in ArgList. The expression record_info(fields, RecordName) is processed by the Erlang preprocessor and evaluates to a list containing the names of the different fields for a record.

Program

The following shell interaction starts Mnesia and initializes the schema for the Company database:

        % 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

The following program module creates and populates previously defined tables:

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

Program Explained

The following commands and functions are used to initiate the Company database:

• % erl -mnesia dir '"/ldisc/scratch/Mnesia.Company"'. This is a UNIX command-line entry that starts the Erlang system. The flag -mnesia dir Dir specifies the location of the database directory. The system responds and waits for further input with the prompt 1>.
• mnesia:create_schema([node()]). This function has the format mnesia:create_schema(DiscNodeList) and initiates a new schema. In this example, a non-distributed system using only one node is created. Schemas are fully explained in Define a Schema.
• mnesia:start(). This function starts Mnesia and is fully explained in Start Mnesia.

Continuing the dialogue with the Erlang shell produces the following:

        3> company:init().
        {atomic,ok}
        4> mnesia:info().
        ---> Processes holding locks <--- 
        ---> Processes waiting for locks <--- 
        ---> Pending (remote) transactions <--- 
        ---> Active (local) transactions <---
        ---> Uncertain transactions <--- 
        ---> 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 = []
        [{nonode@nohost,disc_copies}] = [schema]
        [{nonode@nohost,ram_copies}] =
            [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
      

A set of tables is created. The function mnesia:create_table(Name, ArgList) creates the required database tables. The options available with ArgList are explained in Create New Tables.

The function company:init/0 creates the tables. Two tables are of type bag. This is the manager relation as well the in_proj relation. This is interpreted as: an employee can be manager over several departments, and an employee can participate in several projects. However, the at_dep relation is set, as an employee can only work in one department. In this data model, there are examples of relations that are 1-to-1 (set) and 1-to-many (bag).

mnesia:info() now indicates that a database has seven local tables, where six are the user-defined tables and one is the schema. Six transactions have been committed, as six successful transactions were run when creating the tables.

To write a function that inserts an employee record into the database, there must be an at_dep record and a set of in_proj records inserted. Examine the following code used to complete this action:

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.

• The insert_emp/3 arguments are as follows:

  1. Emp is an employee record.
  2. DeptId is the identity of the department where the employee works.
  3. ProjNames is a list of the names of the projects where the employee works.

The function insert_emp/3 creates a Functional Object (Fun). Fun is passed as a single argument to the function mnesia:transaction(Fun). This means that Fun is run as a transaction with the following properties:

• A Fun either succeeds or fails.
• Code that manipulates the same data records can be run concurrently without the different processes interfering with each other.

The 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(Emp, 'B/SFR', [Erlang, mnesia, otp]).

Initial Database Content

After the insertion of the employee named klacke, the database has the following records:

This employee record has the Erlang record/tuple representation {employee, 104732, klacke, 7, male, 98108, {221, 015}}.

This at_dep record has the Erlang tuple representation {at_dep, klacke, 'B/SFR'}.

This in_proj record has the Erlang tuple representation {in_proj, klacke, 'Erlang', klacke, 'otp', klacke, 'mnesia'}.

There is no difference between rows in a table and Mnesia records. Both concepts are the same and are used interchangeably throughout this User's Guide.

A Mnesia table is populated by Mnesia records. For example, the tuple {boss, klacke, bjarne} is a record. The second element in this tuple is the key. To identify a table uniquely, both the key and the table name is needed. The term Object Identifier (OID) is sometimes used for the arity two tuple {Tab, Key}. The OID for the record {boss, klacke, bjarne} is the arity two tuple {boss, klacke}. The first element of the tuple is the type of the record and the second element is the key. An OID can lead to zero, one, or more records depending on whether the table type is set or bag.

The record {boss, klacke, bjarne} can also be inserted. This record contains an implicit reference to another employee that does not yet exist in the database. Mnesia does not enforce this.

Adding Records and Relationships to Database

After adding more records to the Company database, the result can be the following records:

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

These three tables, employees, dept, and projects, are made up of real records. The following database content is stored in the tables and is built on relationships. These tables are manager, at_dep, and in_proj.

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

The room number is an attribute of the employee record. This is a structured attribute that consists of a tuple. The first element of the tuple identifies a corridor, and the second element identifies the room in that corridor. An alternative is to represent this as a record -record(room, {corr, no}). instead of an anonymous tuple representation.

The Company database is now initialized and contains data.

Writing Queries

Retrieving data from DBMS is usually to be done with the functions mnesia:read/3 or mnesia:read/1. The following function raises the 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).

Since it is desired to update the record using the function mnesia:write/1 after the salary has been increased, a write lock (third argument to read) is acquired when the record from the table is read.

To read the values from the table directly is not always possible. It can be needed to search one or more tables to get the wanted data, and this is done by writing database queries. Queries are always more expensive operations than direct lookups done with mnesia:read. Therefore, avoid queries in performance-critical code.

Two methods are available for writing database queries:

• Mnesia functions
• QLC

Using Mnesia Functions

The following function extracts the names of the female employees stored in the database:

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

select must always run within an activity, such as a transaction. The following function can be constructed to call from the shell:

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

The select expression matches all entries in table employee with the field sex set to female.

This function can be called from the shell as follows:

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

For a description of select and its syntax, see Pattern Matching.

Using QLC

This section contains simple introductory examples only. For a full description of the QLC query language, see the qlc manual page in STDLIB.

Using QLC can be more expensive than using Mnesia functions directly but offers a nice syntax.

The following function extracts a list of female employees from the database:

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

Accessing Mnesia tables from a QLC list comprehension must always be done within a transaction. Consider the following function:

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

This function can be called from the shell as follows:

          (klacke@gin)1> company:females().
          {atomic, ["Carlsson Tuula", "Fedoriw Anna"]}

In traditional relational database terminology, this operation is called a selection, followed by a projection.

The previous list comprehension expression contains a number of syntactical elements:

• The first [ bracket is read as "build the list".
• The || "such that" and the arrow <- is read as "taken from".

Hence, the previous list comprehension demonstrates the formation of the list E#employee.name such that E is taken from the table of employees, and attribute sex of each record is equal to the atom female.

The whole list comprehension must be given to the function qlc:q/1.

List comprehensions with low-level Mnesia functions can be combined in the same transaction. To raise the salary of all female employees, execute the following:

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.

The function raise_females/1 returns the tuple {atomic, Number}, where Number is the number of female employees who received a salary increase. If an error occurs, the value {aborted, Reason} is returned, and Mnesia guarantees that the salary is not raised for any employee.

Example:

          33>company:raise_females(33).
          {atomic,2}