A schema is a collection of table definitions that describe your database. It covers which of your tables are stored on RAM, disk, or both, alongside their configuration characteristics and the format of the data they will contain. These characteristics may differ from node to node, as you might want your table to have its disk copies on the operation and maintenance node but have RAM-only copies on the transaction nodes. In Erlang, the schema is stored in a persistent Mnesia table. When configuring your database, you create an empty schema table which, over time, you populate with your table definitions.

To create the schema,^[32] start your distributed Erlang nodes and connect them. If you do not want to distribute Mnesia, just start a non-distributed Erlang node. Before doing this it is important to make sure no old schemas exist, as well as ensuring that Mnesia is not started.

In our example, we will be starting the database on two nodes, switch and om:

(om@Vaio)1> net_adm:ping(switch@Vaio).
pong
(om@Vaio)2> nodes().
[switch@Vaio]
(om@Vaio)3> mnesia:create_schema([node()|nodes()]).
ok
(om@Vaio)4> ls().
Mnesia.om@Vaio         Mnesia.switch@Vaio     include
lib
ok

The mnesia:create_schema(Nodes) command has to be executed on one of the connected nodes only. By creating the list [node()|nodes()], we get all the connected Erlang nodes, which incidentally are the same nodes on which we want to create the schema tables. You might recall from Chapter 11, where we covered distributed Erlang, that the node() BIF returns the local node, whereas nodes() returns all other connected nodes. The mnesia:create_schema/1 command will propagate to the other nodes automatically.

When creating the schema, each node will create a directory. In our case, as both distributed Erlang nodes share the same root directory, their schema directories Mnesia.om@Vaio and Mnesia.switch@Vaio will appear in the same location, where Vaio is the hostname of the computer on which we executed the command. Other contents of the directory will depend on what you’ve previously done with Erlang, but will not affect your schema.

Had your nodes been on computers that were not connected, only the schema directory for the node running on that computer would have been created. If you do not plan to run Mnesia in a distributed environment, the schema directory name will be Mnesia.nonode@nohost. Just pass [node()] as an argument to the create_schema/1 call.

You can override the location of the root directory by starting Erlang with this directive:

erl -mnesia dir Dir

replacing Dir with the directory where you want to store your schema.

Once your schema has been created, you start the application by calling the following:

application:start(mnesia).

If you are using boot scripts as described in Chapter 12, you should include the Mnesia application in your release file. In a test environment where you are not using OTP behaviors, you can also use mnesia:start(). In an industrial project with release handling, separating the applications and starting them individually is considered a best practice.

If you start Mnesia without a schema, a memory-only database will be created. This will not survive restarts, however, so the RAM-only tables will have to be created every time you restart the system. To start Mnesia with a RAM schema, all you need to do is ensure that there is no schema directory for that particular node, and then you can start Mnesia.

You can stop Mnesia by calling either application:stop(mnesia) or mnesia:stop().

Mnesia tables contain Erlang records. By default, the name of the record type becomes the table name. You create a table by using the following function call:

mnesia:create_table(Name, Options)

In this function call, Name is the record type and Options is a list of tuples of the format {Item, Value}. The following Items and Values are most commonly used:

The key position is, by default, the first element of the record. Each instance of a record in a Mnesia table is called an object. The key of this object, together with the table name, give you the object identifier.

Having created the schema, we want to start Mnesia on all nodes and do the one-off operation of creating the table. This is usually done when installing and configuring the Erlang nodes. For redundancy and performance reasons, we want to run the database on two remote nodes called om and switch. On the om node, we want to store the data in RAM and on disk, and on the switch node, we want to maintain only a RAM copy. When looking at the example, remember the rr/1 shell command that reads a file and extracts its record definitions, making them available in the shell:

(om@Vaio)5> rr(usr).
[usr]
(om@Vaio)6> Fields = record_info(fields, usr).
[msisdn,id,status,plan,services]
(om@Vaio)7> application:start(mnesia).
 ok
(om@Vaio)8> mnesia:create_table(usr, [{disc_copies, [node()]}, 
  {ram_copies, nodes()}, {type, set}, {attributes, Fields}, {index, [id]}]).
{atomic,ok}

Within this Mnesia example, notice how we create only one table, stating that it has to be indexed and that it must have a RAM copy and file backup. A simplistic explanation of what is happening behind the scenes is evident in our mobile subscriber database backend module using ETS and Dets tables, where we created three tables: one for the disk copy, one for the RAM copy, and one for the index.

There is no need to open Mnesia tables. When starting the Mnesia application, all tables configured in the schema are created or opened. This is a relatively fast, nonblocking operation, done in parallel with the startup of other applications.

For large persistent tables, or tables that were incorrectly closed and whose backup files need repair, other applications might try to access the table even if it has not been properly loaded. Should this happen, the process crashes with the error no_exists. To avoid this, you should call:

mnesia:wait_for_tables(TableList, TimeOut)

in the initialization phase of your process or OTP behavior, where TableList is a list of Mnesia table names (both persistent and volatile) used by that process, and TimeOut is either the atom infinity or an integer in milliseconds.

When dealing with large tables containing millions of rows, if you are not using infinity as a timeout, you must ensure that the TimeOut value is at least a few minutes, if not hours, for extremely large, fragmented, disk-based tables. The call wait_for_tables/2 is called independently of starting Mnesia by the process which needs the tables. By pattern matching on the return value, you ensure that the table is loaded. If a timeout occurs, the return value will result in a bad match error, and that is logged. The last thing you want is for the wait_for_tables/2 call to return {timeout, TableList}, ignore this value, and continue assuming that the tables have been properly loaded.

In our mobile subscriber example, we originally needed a process that created and owned the ETS and Dets tables and serialized all destructive (write and delete) operations. That is no longer the case. Mnesia will take care of this for us. We would thus scrap the usr_db.erl module, and instead place all of our functionality in the usr.erl module. We would also remove all the process-related calls, such as start, spawn, init, and stop, or gen_server calls and callbacks if we are using the behavior example. We would instead add the calls create_tables/0 and ensure_loaded/0 and keep the entire client API:

-module(usr).
-export([create_tables/0, ensure_loaded/0]).
-export([add_usr/3, delete_usr/1, set_service/3, set_status/2,
         delete_disabled/0, lookup_id/1]).
-export([lookup_msisdn/1, service_flag/2]).

-include("usr.hrl").

%% Mnesia API

create_tables() ->
  mnesia:create_table(usr, [{disc_copies, [node()]}, {ram_copies, nodes()},
                      {type, set}, {attributes,record_info(fields, usr)},
                      {index, [id]}]).

ensure_loaded() ->
  ok = mnesia:wait_for_tables([usr], 60000).

To write an object in a table, you use the function mnesia:write(Record), encapsulating it in a fun and executing it in a transaction. The call returns the atom ok. Mnesia will put a write lock on all of the copies of this object (including those on remote nodes). Attempts to put a lock on an already locked object will fail, prompting the transaction to release all of its locks and start again.

Trying out the functions directly in the shell will give you a better feel for how it all works:

(om@Vaio)9> Rec = #usr{msisdn=700000003, id=3, status=enabled,
(om@Vaio)9>            plan=prepay, services=[data,sms,lbs]}.
#usr{msisdn=700000003, id=3, status=enabled,
     plan=prepay, services=[data,sms,lbs]}
(om@Vaio)10> mnesia:transaction(fun() -> mnesia:write(Rec) end).
{atomic,ok}

Remember how in the ETS and Dets variants of this example, which first appeared in Chapter 10, you had to create three table entries for every user you inserted in the database? And what if you wanted to distribute this data across multiple nodes? As the usr Mnesia table contains distributed RAM and disk-based copies as well as an index, you only need to do one write. Behind the scenes, Mnesia takes care of the rest for you.

To write or update a record in the mobile subscriber example, you would encapsulate the write operation in the usr.erl module as follows:

add_usr(PhoneNo, CustId, Plan) when Plan==prepay; Plan==postpay ->
  Rec = #usr{msisdn = PhoneNo,
             id     = CustId,
             plan   = Plan},
  Fun = fun() -> mnesia:write(Rec) end,
  {atomic, Res} = mnesia:transaction(Fun),
  Res.

As add_usr/1 in all of the previous ETS and OTP behavior examples returned ok, you would make the new function backward-compatible.

To read objects, you use the function mnesia:read(OId), where OId is an object identifier of the format {TableName, Key}. This function call will return the empty list if the object does not exist or a list of one or more records if the object exists and the table is a set or bag. You need to execute the function within the scope of a transaction; failing to do so will cause a runtime error.

Note from which node we are now reading the record. It does not make a difference. We just need to make sure Mnesia is started on this node as well, something we did when creating the table.

To delete an object, you can use the mnesia:delete(OId) call from within a transaction. The call returns the atom ok, regardless of whether the object exists.

Our schema was distributed across two nodes. Let’s start Mnesia on the second node and look up the entry we wrote on the om node in the previous example:

(switch@Vaio)1> application:start(mnesia).
ok
(switch@Vaio)2> usr:ensure_loaded().
ok
(switch@Vaio)3> rr(usr).
[usr]
(switch@Vaio)4> mnesia:transaction(fun() -> mnesia:read({usr, 700000003}) end).
{atomic,[#usr{msisdn = 700000003,id = 3,status = enabled,
              plan = prepay,
              services = [data,sms,lbs]}]}
(switch@Vaio)5> mnesia:read({usr, 700000003}).
** exception exit: {aborted,no_transaction}
     in function  mnesia:abort/1
(switch@Vaio)6> mnesia:transaction(fun() -> mnesia:abort(no_user) end).
{aborted,no_user}
(switch@Vaio)7> mnesia:transaction(fun() -> mnesia:delete({usr, 700000003}) end).
{atomic,ok}
(switch@Vaio)8> mnesia:transaction(fun() -> mnesia:read({usr, 700000003}) end).
{atomic,[]}

As you can see, executing a destructive operation such as write or delete will duplicate the operation across all nodes. Pay attention to the error in command 5, where we execute a read outside the scope of a transaction. Also, look at the return value for command 6, where we abort a transaction.

When creating the usr table, one of the options we passed into the call was the tuple {index, AttributeList}. This will index the table, allowing us to look up and manipulate objects using any of the secondary fields (or keys) listed in the AttributeList. To use indexes, you have to execute the following call:

index_read(TableName, SecondaryKey, Attribute).

All of the functions used to provision customer data in our example use the CustomerId attribute. If you want to delete a subscriber record, you would have to check its existence, as the function delete_usr/1 returns {error, instance} if the field does not exist. If the record does exist, you find its primary key and use it to delete the field:

delete_usr(CustId) ->
  F = fun() -> case mnesia:index_read(usr, CustId, id) of
                 []    -> {error, instance};
                 [Usr] -> mnesia:delete({usr, Usr#usr.msisdn})
               end
      end,
  {atomic, Result} = mnesia:transaction(F),
  Result.

In a similar fashion, if you wanted to add or remove a service a subscriber is entitled to use, you would look up the usr record and, if the entry exists, update the status using the msisdn:

set_service(CustId, Service, Flag) when Flag==true; Flag==false ->
  F = fun() ->
        case mnesia:index_read(usr, CustId, id) of
          []    -> {error, instance};
          [Usr] ->
            Services = lists:delete(Service, Usr#usr.services),
            NewServices = case Flag of
                            true  -> [Service|Services];
                            false -> Services
                          end,
            mnesia:write(Usr#usr{services=NewServices})
        end
      end,
  {atomic, Result} = mnesia:transaction(F),
  Result.

The same principle applies to enabling and disabling a particular subscriber:

set_status(CustId, Status) when Status==enabled; Status==disabled->
  F = fun() ->
        case mnesia:index_read(usr, CustId, id) of
          []    -> {error, instance};
          [Usr] -> mnesia:write(Usr#usr{status=Status})
        end
    end,
  {atomic, Result} = mnesia:transaction(F),
  Result.

Note how all of these functions first look up an object, and if it exists, they either delete or manipulate it. When changing the subscriber status or services, there is no risk of any other process deleting the entry between the index_read/3 and the write/1 function calls. This is because both operations are running in a transaction, setting locks on the objects they are manipulating and ensuring that other transactions attempting to access them are kept on hold. As a result, any two transactions on the same object cannot interfere with each other. Keep in mind that the race conditions could occur among processes in different nodes. Let’s try the functions we’ve just defined in the shell and see whether they work:

(switch@Vaio)9> usr:add_usr(700000001, 1, prepay).
ok
(switch@Vaio)10> usr:add_usr(700000002, 2, prepay).
ok
(switch@Vaio)11> usr:add_usr(700000003, 3, postpay).
ok
(switch@Vaio)12> usr:delete_usr(3).
ok
(switch@Vaio)13> usr:delete_usr(3).
{error,instance}
(switch@Vaio)14> usr:set_status(1, disabled).
ok
(switch@Vaio)15> usr:set_service(2, premiumsms, true).
ok
(switch@Vaio)16> mnesia:transaction(fun() -> mnesia:index_read(usr, 2, id) end).
{atomic,[#usr{msisdn = 700000002,id = 2,status = enabled,
              plan = prepay,
              services = [premiumsms]}]}

If you create a table and want to add or remove indexes during runtime, you can use the schema manipulation functions add_table_index(Tab, Attribute) and del_table_index(Tab, Attribute).

Sometimes it is acceptable to execute an operation outside the scope of a transaction without setting any locks. Such operations are known as dirty operations. In Mnesia, dirty operations are about 10 times faster than their counterparts that are executed in transactions, making them a very viable option for soft real-time systems. If you can guarantee the consistency, isolation, durability, and distribution properties of your tables, dirty operations will significantly enhance the performance of your program.

Some of the most common dirty Mnesia operations are:

dirty_read(Oid)
dirty_write(Object)
dirty_delete(ObjectId)
dirty_index_read(Table, SecondaryKey, Attribute)

All of these operations will return the same values as their counterparts executed within a transaction. If you need to implement soft real-time systems with requirements on throughput, transactions quickly become a major bottleneck. In our mobile subscriber example, the time-critical functions are those that are service-related. If you need to send 100,000 SMS messages, where every SMS requires a lookup to ensure that the subscriber not only exists and is enabled, but also is allowed to receive premium-rated SMSs, speed becomes critical. If the subscriber data is changed before or after the dirty read, it would not impact the sending of the SMS, since the functions contain only one nondestructive read operation:

lookup_id(CustId) ->
  case mnesia:dirty_index_read(usr, CustId, id) of
    [Usr] -> {ok, Usr};
    []    -> {error, instance}
  end.

%% Service API

lookup_msisdn(PhoneNo) ->
  case mnesia:dirty_read({usr, PhoneNo}) of
    [Usr] -> {ok, Usr};
    []    -> {error, instance}
  end.

service_flag(PhoneNo, Service) ->
  case lookup_msisdn(PhoneNo) of
    {ok,#usr{services=Services, status=enabled}} ->
      lists:member(Service, Services);
    {ok, #usr{status=disabled}} ->
      {error, disabled};
    {error, Reason} ->
      {error, Reason}
  end.

A common way to use dirty operations while ensuring data consistency is to serialize all destructive operations in a single process. Although another process might be allowed to execute a dirty read outside the scope of this process, all operations that involve both writing and deleting elements are serialized by sending the request to the process that executes them in the order they are received.

In our Mnesia version of the usr example, we got rid of our central process altogether and used transactions. If we had kept the process, we could have replaced all of the ETS and Dets read, write, and delete operations with Mnesia dirty operations. If we distributed the table across the OM and Switch nodes, however, we would have had to redirect all destructive operations to one of the nodes, as we would otherwise have run the risk of simultaneously updating the same object in two locations.

If you need to use dirty operations in a distributed environment, the trick is to ensure that updates to a certain key subset are serialized through a process on a single node. If your keys are in the range of 1 to 1,000, you could potentially update all even keys on one node and all odd ones on the other, solving the race condition we just described.

In this step-by-step exercise, you will create a distributed Mnesia database of Muppets. First, start two nodes:

erl -sname foo
erl -sname bar

In the first node, declare the Muppet data structure:

foo@localhost 1> rd(muppet, {name, callsign, salary}).

Next, create a schema so that you can make the tables persistent:

foo@localhost2> mnesia:create_schema([foo@localhost, bar@localhost]).

Now,you need to start Mnesia on both nodes:

foo@localhost 3> application:start(mnesia).
bar@localhost 1> application:start(mnesia).

The database is running! Create a distributed table:

foo@localhost 4> mnesia:create_table(muppet, [
                            {attributes, record_info(fields, muppet)},
                            {disc_copies [foo@localhost, bar@localhost}]).

Note how the disc_copies attribute specifies the nodes on which you want to keep a persistent copy. Check that everything looks all right:

foo@localhost 5> mnesia:info().

Now, look around you and type in your current cast of Muppets:

foo@localhost 6> mnesia:dirty_write(#muppet
{name = "Francesco" callsign="HuluHuluHulu", salary = 0}).

See how many Muppets you have so far:

foo@localhost 7> mnesia:table_info(muppet, size).

List their names with a function we have not covered in this chapter, but which you should have picked up when reading the Mnesia manual pages:

foo@localhost 8> mnesia:dirty_all_keys(muppet).

Excellent; now go to the other node and look up a Muppet:

bar@localhost 2> mnesia:dirty_read({muppet, "Francesco"}).

Write a function that reads a Muppet’s salary and increases it by 10%. Use a transaction to guarantee that there are no race conditions.

Implement the usr_db.erl module from Chapter 10 with dirty Mnesia operations. The module should be completely backward compatible with the ETS- and Dets-based solution. Test it from the shell, and when it works, serialize the operations in a process, using the usr.erl module. Your create_tables/1 and close_tables/0 calls should start and stop the Mnesia application, and the restore_backup/0 call should implement a wait_for_tables/2 call. All other functions should return the same values returned in the original example.