Introduction

A Lispy wrapper for Erlang Mnesia and QLC

Credit

This tutorial is adapted (massively copied) from the LFE Mnesia Tutorial, which in turn was borrowed from the Erlang/OTP Mnesia Book on the Erlang docs site.

About Mnesia

Mnesia is a distributed Database Management System, appropriate for telecommunications applications and other Erlang applications which require continuous operation and soft real-time properties.

Mnesia is designed with requirements like the following in mind:

  • Fast real-time key/value lookup
  • Complicated non real-time queries mainly for operation and maintenance
  • Distributed data due to distributed applications
  • High fault tolerance
  • Dynamic re-configuration
  • Complex objects

Due to its focus on telecommunications applications, Mnesia combines many concepts found in traditional databases, such as transactions and queries with concepts such as very fast real-time operations, configurable degree of fault tolerance (by means of replication) and the ability to reconfigure the system without stopping or suspending it.

Mnesia is also interesting due to its tight coupling to the programming language Erlang/LFE, thus almost turning Erlang/LFE into a database programming language.

Add-on Applications

The following add-ons can be used in conjunction with Mnesia to produce specialized functions which enhance the operational ability of Mnesia:

  • QLC has the ability to optimize the query compiler for the Mnesia Database Management System, essentially making the DBMS more efficient.
  • QLC, can be used as a database programming language for Mnesia. It includes a notation called "list comprehensions" and can be used to make complex database queries over a set of tables.
  • Mnesia Session is an interface for the Mnesia Database Management System
  • Mnesia Session enables access to the Mnesia DBMS from foreign programming languages (i.e. other languages than Erlang/LFE).

When to Use Mnesia

Use Mnesia with the following types of applications:

  • Applications that need to replicate data.
  • Applications that perform complicated searches on data.
  • Applications that need to use atomic transactions to update several records simultaneously.
  • Applications that use soft real-time characteristics.

On the other hand, Mnesia may not be appropriate with the following types of applications:

  • Programs that process plain text or binary data files
  • Applications that merely need a look-up dictionary which can be stored to disc can utilize the standard library module dets, which is a disc based version of the module ets.
  • Applications which need disc logging facilities can utilize the module disc_log by preference.
  • Not suitable for hard real time systems.

Prerequisites

In order to work through this tutorial, you will need the following:

  • git
  • make
  • rebar3
  • Erlang installed on your system

Using Moneta

The real differences are the modules and the additional utility functions. Module changes from the mnesia and qlc Erlang modules are as follows:

  • mnta - holds alll the mnesia functions besides the dirty ones
  • mnta-drty - split dirty functions into their own module ("dirty" operations are short-cuts that bypass much of the processing and increase the speed of the transaction)
  • mnta-qry - qlc alias; longer to type than qlc, but provides some nice visual context when scanning code

The utility functions that have been added are as follows:

TBD

Getting Started

Download Moneta

Download the project source code:

$ git clone https://github.com/lfex/moneta
$ cd moneta

Next, get all the dependencies downloaded and built:

$ rebar3 compile

The moneta project offers some convenience wrappers for the Erlang/OTP mnesia and qlc modules. To walk through the tutorial, you'll need to download the project source code.

Once you have the code built, you're ready to go :-) Now you can start the LFE REPL, letting it know where to save your data.

Start LFE and let it know where you data will be saved:

$ rebar3 lfe repl

Once in the REPL, you're ready to start dataing:

Erlang/OTP 26 [erts-14.0.2] [source] [64-bit] [smp:10:10] ...

   ..-~.~_~---..
  (      \\     )    |   A Lisp-2+ on the Erlang VM
  |`-.._/_\\_.-':    |   Type (help) for usage info.
  |         g |_ \   |
  |        n    | |  |   Docs: http://docs.lfe.io/
  |       a    / /   |   Source: http://github.com/lfe/lfe
   \     l    |_/    |
    \   r     /      |   LFE v2.1.2 (abort with ^G)
     `-E___.-'

lfe>

First Run

We're going to be using macros in the REPL, so let's include them:

lfe> (include-lib "moneta/include/macros.lfe")
--loaded-moneta-macros--

Create a schema, asking Moneta to start the Mnesia database for you automatically:

lfe> (mnta:create-schema #(start true))
ok

Define a record for storing data:

lfe> (defrecord funky a b c)

Now create a defaul table (with no provided table definition):

lfe> (mnta:create-table 'funky $ENV)
#(atomic ok)

From the LFE REPL you can create a new database on disk, start Mnesia up, and then create a table. After creating the table, you can examine the table's metadata.

We can take a look at our creation with this command:

> (mnta:info)

Which should give you something like this:

---> Processes holding locks <---
---> Processes waiting for locks <---
---> Participant transactions <---
---> Coordinator transactions <---
---> Uncertain transactions <---
---> Active tables <---
in-project     : with 11       records occupying 399      words of mem
project        : with 7        records occupying 367      words of mem
manager        : with 3        records occupying 335      words of mem
employee       : with 7        records occupying 601      words of mem
in-department  : with 7        records occupying 367      words of mem
department     : with 3        records occupying 481      words of mem
schema         : with 7        records occupying 1138     words of mem
===> System info in version "4.22", debug level = none <===
opt_disc. Directory "/Users/oubiwann/lab/lfe/moneta/Mnesia.nonode@nohost" is used.
use fallback at restart = false
running db nodes   = [nonode@nohost]
stopped db nodes   = []
master node tables = []
remote             = []
ram_copies         = []
disc_copies        = [department,employee,'in-department','in-project',
                      manager,project,schema]
disc_only_copies   = []
[{nonode@nohost,disc_copies}] = [schema,department,'in-department',employee,
                                 manager,project,'in-project']
2 transactions committed, 0 aborted, 0 restarted, 0 logged to disc
0 held locks, 0 in queue; 0 local transactions, 0 remote
0 transactions waits for other nodes: []
ok

You can quit the REPL now, as we'll restart it in the next section.

DBs and Tables

Starting Again

Restart the LFE REPL using a new data directory:

$ rebar3 lfe repl

Create a default database schema:

lfe> (mnta:create-schema #(start true))
ok

We've had a quick taste of Mnesia, and what some of the calls look like in LFE. Next we're going to tackle a bit more heady stuff: tables and relationships.

After you have quit from your previous LFE REPL, restart using the Company.DB database name and then create a default schema, passing the auto-start option to Mnesia.

Records as Tables

The following records are defined in examples/tables.lfe:

(defrecord employee
  id
  name
  department-id
  projects)

(defrecord department
  id
  name)

(defrecord project
  name
  number)

(defrecord manager
  employee-id
  department-id)

(defrecord in-department
  employee-id
  department-id)

(defrecord in-project
  employee-id
  project-name)

Pull in these table definitions:

lfe> (include-file "examples/tables.lfe")
loaded-example-tables

Define your tables:

lfe> (set set-tables '(employee department project in-department))
(employee department project in-department)
lfe> (set bag-tables '(manager in-project))
(manager in-project)

The tables.lfe example include defines LFE records that act as our table definitions (and thus all the convenient record macros that come with those). These are only definitions, though -- representing a table schema -- not the actual tables themselves. We need to create those. as well as a macro that lets us create Mnesia tables with almost no boilerplate.

These records (tables) are taken from the example given in the Erlang Mnesia tutorial which also gives this entity diagram for their proposed "Company" database:

Creating Our Tables

Create the tables with the appropriate table specs:

lfe> (mnta:create-tables set-tables '(#(type set)) $ENV)
(#(atomic ok) #(atomic ok) #(atomic ok) #(atomic ok))
lfe> (mnta:create-tables bag-tables '(#(type bag)) $ENV)
(#(atomic ok) #(atomic ok))

This just created all our Mnesia tables for us. If we run it again, we'll see errors indicating that the tables have already been created:

lfe> (mnta:create-tables set-tables '(#(type set)))
(#(aborted #(already_exists employee))
 #(aborted #(already_exists department))
 #(aborted #(already_exists project))
 #(aborted #(already_exists in-department)))
lfe> (mnta:create-tables bag-tables '(#(type bag)))
(#(aborted #(already_exists manager))
 #(aborted #(already_exists in-project)))

When using Mnesia directly, there is a great deal of boilerplate code that developers need to write in order to create tables. Fortunately, Moneta provides several macros and functions that does this for you, making table-creation as intuitive as possible: all you need to do is provide table names and table specs.

Table Metadata

Next, let's re-run the info function we saw in the previous section:

lfe> (mnta:info)

...
---> Active tables <---
in-project     : with 0        records occupying 305      words of mem
in-department  : with 0        records occupying 305      words of mem
manager        : with 0        records occupying 305      words of mem
project        : with 0        records occupying 305      words of mem
department     : with 0        records occupying 305      words of mem
employee       : with 0        records occupying 305      words of mem
...

Here's how you find what backend type is being used for any given table:

lfe> (mnta:table-info 'employee 'type)
set
lfe> (mnta:table-info 'in-project 'type)
bag

You can also get table metadata for several tables at once:

lfe> (mnta:tables-info (++ set-tables bag-tables) 'type)
(set set set set bag bag)

If you're interested in seeing all the details of any given table, you can do so with the 'all parameter:

lfe> (mnta:table-info 'employee 'all)
(#(access_mode read_write)
#(active_replicas (nonode@nohost))
#(all_nodes (nonode@nohost))
#(arity 7)
#(attributes (id name salary gender phone room-number))
#(checkpoints ())
#(commit_work ())
#(cookie #(#(1396 680215 616649) nonode@nohost))
#(cstruct
 #(cstruct
   employee
   set
   (nonode@nohost)
   ()
   ()
   0
   read_write
   false
   ()
   ()
   false
   employee
   (id name salary gender ...)
   ()
   ()
   ()
   #(...)...))
#(disc_copies ())
#(disc_only_copies ())
#(frag_properties ())
#(index ())
#(load_by_force false)
#(load_node nonode@nohost)
#(load_order 0)
#(load_reason #(dumper create_table))
#(local_content false)
#(majority false)
#(master_nodes ())
#(memory 317)
#(ram_copies (nonode@nohost))
#(record_name employee)
#(record_validation #(employee 7 set))
#(type set)
#(size 0)
#(snmp ())
#(storage_properties ...)
#(...)...)
>

The output of the info function will be very similar to what we saw in the previous section. However, do note that our new tables are reported in the "Active tables" section:

If you would like to check up on the tables created above, you can use the table-info function to pull out certain data.

Next up, we'll start inserting some data.

Content

We've created tables and we know how to inspect their metadata; now let's give them data. From the previous chapter, you should have the example records available to after including them:

lfe> (include-file "examples/tables.lfe")

Department Data

With those present (and the macros automatically created for us when those records were included), let's create a list of departments:

(set depts
  (list
    (make-department id 'B/SF name "Open Telecom Platform")
    (make-department id 'B/SFP name "OTP - Product Development")
    (make-department id 'B/SFR name "Computer Science Laboratory")))

And then we can insert this data with the following:

lfe> (mnta-qry:insert depts)
ok

To see everything we just inserted, we can use a moneta convenience function:

lfe> (set `#(atomic ,results) (mnta-qry:select-all 'department))

These results are department records, so we can iterate over them and use their record macros. For example:

lfe> (lists:map (lambda (r) (department-name r)) results)

which gives:

("Open Telecom Platform"
 "OTP - Product Development"
 "Computer Science Laboratory")

Project Data

We can repeat this process for the following projects:

(set projs
  (list
    (make-project name 'erlang number 1)
    (make-project name 'otp number 2)
    (make-project name 'beam number 3)
    (make-project name 'mnesia number 5)
    (make-project name 'wolf number 6)
    (make-project name 'documentation number 7)
    (make-project name 'www number 8)))

lfe> (mnta-qry:insert projs)
ok
lfe> (mnta-qry:select-all 'project)
#(atomic
  (#(project wolf 6)
   #(project erlang 1)
   #(project beam 3)
   #(project otp 2)
   #(project documentation 7)
   #(project www 8)
   #(project mnesia 5)))

Relationships

Next we'll explore the relational aspect of our data. First, let's create some employee records:

(set empls
  (list
    (make-employee id 1 name "Johnson Torbjorn" department-id 'B/SF
                   projects '(otp))
    (make-employee id 2 name "Carlsson Tuula" department-id 'B/SF
                   projects '(otp))
    (make-employee id 3 name "Dacker Bjarne" department-id 'B/SFR
                   projects '(otp))
    (make-employee id 4 name "Nilsson Hans" department-id 'B/SFR
                   projects '(mnesia otp))
    (make-employee id 5 name "Tornkvist Torbjorn" department-id 'B/SFR
                   projects '(otp wolf))
    (make-employee id 6 name "Fedoriw Anna" department-id 'B/SFR
                   projects '(documentation otp))
    (make-employee id 7 name "Mattsson Hakan" department-id 'B/SFR
                   projects '(mnesia otp))))

Since we have relationships with other tables, we'll need to create a custom insert for employee records:

(defun add-projects
  ((_ '())
   'ok)
  ((emp-id `(,proj . ,tail))
   (mnesia:write (make-in-project employee-id emp-id project-name proj))
   (add-projects emp-id tail)))

(defun insert-employees
  (('())
   'ok)
  ((`(,(= (match-employee id id department-id dept-id projects projs) record) . ,tail))
   (mnesia:transaction
    (lambda ()
      (mnesia:write record)
      (mnesia:write (make-in-department employee-id id department-id dept-id))
      (add-projects id projs)))
   (insert-employees tail)))

We can then insert the employee data and create all the relations with this:

lfe> (insert-employees empls)

And then use what we did before to check the inserts:

lfe> (mnta-qry:select-all 'employee)
lfe> (mnta-qry:select-all 'in-department)
lfe> (mnta-qry:select-all 'in-project)

Finally, we'll round out the relationships with employee managers, starting with the list of records:

(set mgrs
  (list
    (make-manager employee-id 1 department-id 'B/SF)
    (make-manager employee-id 1 department-id 'B/SFP)
    (make-manager employee-id 3 department-id 'B/SFR)))

Now for the insert:

lfe> (mnta-qry:insert mgrs)
ok

With the data in place, we're ready to dive into querying it ...

Querying

We've already used some of the querying techniques for inserting and selecting all records. Here we'll explore some specifics of other types of queries we can make against the database.

Select and Match Specs

Mnesia supports a form of querying data using a "match specification" with various level of detail provided at the following:

On the LFE side, a brief description of using match specifications is provided here:

Selects in mnesia have to be wrapped in a transaction, so moneta provides a convenience function for this. We can use it and the LFE ets-ms macro to query our employee table, finding all who belong to the B/SFR department:

lfe> (set `#(atomic ,results)
       (mnta-qry:select 'employee 
         (ets-ms
          (((match-employee department-id 'B/SFR name '$1)) '() '($1)))))

Which gives us the following:

lfe> results
(("Dacker Bjarne")
 ("Fedoriw Anna")
 ("Nilsson Hans")
 ("Tornkvist Torbjorn")
 ("Mattsson Hakan"))

We can also use guards in the match specs, providing a similar capability as the SQL WHERE clause. Here we get the three lowest-numbered employee IDs:

lfe> (set `#(atomic ,results)
       (mnta-qry:select 'employee 
         (ets-ms
          (((match-employee id id name '$2)) (when (=< id 3)) '($2)))))

giving us the following:

lfe> results
(("Dacker Bjarne")
 ("Carlsson Tuula")
 ("Johnson Torbjorn"))

QLC

The other way to query Mnesia is to use "query list comprehensions" which you can read about in detail at the following:

Let's rewrite our queries above using QLCs:

TBD

Previous Versions

Documentation is available for all previous releases:

License

Copyright © 2016-2023 Duncan McGreggor

Distributed under the Apache License, Version 2.0.

Original Mnesia documentation upon which the Moneta tutorial is based:

Copyright © 1997-2016 Ericsson AB. All Rights Reserved.