Tutorial: Mnesia

4 Content

4.1 Inserting Data

We've created tables and we know how to inspect their metadata; now let's give them data. Again, from the REPL, we'll pull in our source code:

> (slurp '"src/content.lfe")
#(ok content)
>

4.1.1 Inserting Department Data

One of the functions now available to us in the REPL is insert-departments. It is defined like so:

(defun insert-departments
  (('())
   'ok)
  (((cons (list id name) tail))
   (let ((department (make-department id id name name)))
     (mnesia:transaction (lambda () (mnesia:write department)))
     (insert-departments tail))))

Which means that we can create a simple list of lists that it will iterate over and insert for us. Let's try it.

Here's our list of departments:

> (set depts '((B/SF "Open Telecom Platform")
               (B/SFP "OTP - Product Development")
               (B/SFR "Computer Science Laboratory")))
>

Let's do the insert:

> (insert-departments depts)
ok
>

To make sure that everything is where we planned, we can use mnesia:foldl:

> (mnesia:transaction
    (lambda ()
      (mnesia:foldl (lambda (x _) (lfe_io:format '"~p~n" (list x)))
                    0
                    'department)))
#(department B/SFP "OTP - Product Development")
#(department B/SFR "Computer Science Laboratory")
#(department B/SF "Open Telecom Platform")
#(atomic ok)
>

We've defined this as a function you can call from now on, without all that typing:

> (querying:show-data 'department)
#(department B/SFP "OTP - Product Development")
#(department B/SFR "Computer Science Laboratory")
#(department B/SF "Open Telecom Platform")
#(atomic ok)
>

4.1.2 Inserting Project Data

We can do something similar for populating our project table. There is another utility function defined for inserting projects, just like the one above for our company's departments:

> (set projs '((erlang 1)
               (otp 2)
               (beam 3)
               (mnesia 5)
               (wolf 6)
               (documentation 7)
               (www 8))))
> (insert-projects projs)
ok
>

Let's give this a check, too:

(querying:show-data 'project)
#(project erlang 1)
#(project wolf 6)
#(project www 8)
#(project otp 2)
#(project mnesia 5)
#(project beam 3)
#(project documentation 7)
#(atomic ok)
>

We've inserted all the data we can that doesn't depend upon relationships. All the remainder of our data will require dependent insertions.

4.2 Building Relationships

We've going to take advantage of another utility function for setting up relationships between employees and their departments and projects:

(defun insert-employee (employee department-id project-names)
  (mnesia:transaction
    (lambda ()
      (mnesia:write employee)
      (insert-department-relation (employee-id employee) department-id)
      (insert-project-relations (employee-id employee) project-names))))

The functions called by our function are available for your viewing pleasure in the LFE Mnesia tutorial repo. They are similar in nature to the other utility functions we have already defined.

Let's define our first employee (we will refer to the employee record we defined in include/tables.lfe, while doing so):

> (set emp (make-employee
             id 104732
             name '"Wikstrom Claes"
             salary 2
             gender 'M
             phone 98108
             room-number #(221 015)))
#(employee 104732 "Wikstrom Claes" 7 M 98108 #(221 15))
>

Now let's use our insert utility function:

> (insert-employee emp 'B/SFR '(erlang mnesia otp))
#(atomic ok)
>

We've got three tables to check; let's do that all at once:

> (querying:show-data 'employee)
#(employee 104732 "Wikstrom Claes" 7 M 98108 #(221 15))
#(atomic ok)
> (querying:show-data 'in-project)
#(in-project 104732 erlang)
#(in-project 104732 mnesia)
#(in-project 104732 otp)
#(atomic ok)
> (querying:show-data 'in-department)
#(in-department 104732 B/SFR)
#(atomic ok)
>

Fantastic! It's all there :-)

Let's add some more:

> (set employees
    '((104465 "Johnson Torbjorn" 1 M 99184 #(242 038) B/SF (otp))
      (107912 "Carlsson Tuula" 2 F 94556 #(242 056) B/SF (otp))
      (114872 "Dacker Bjarne" 3 M 99415 #(221 035) B/SFR (otp))
      (104531 "Nilsson Hans" 3 M 99495 #(222 026) B/SFR (mnesia otp))
      (104659 "Tornkvist Torbjorn" 2 M 99514 #(222 022) B/SFR (otp wolf))
      (117716 "Fedoriw Anna" 1 F 99143 #(221 031) B/SFP (documentation otp))
      (115018 "Mattsson Hakan" 3 M 99251 #(203 348) B/SFP (mnesia otp))))
...
> (insert-employees employees)
ok
>

Let's do our checks again:

> (querying:show-data 'employee)
#(employee 104659 "Tornkvist Torbjorn" 2 M 99514 #(222 22))
#(employee 117716 "Fedoriw Anna" 1 F 99143 #(221 31))
#(employee 115018 "Mattsson Hakan" 3 M 99251 #(203 348))
#(employee 104531 "Nilsson Hans" 3 M 99495 #(222 26))
#(employee 114872 "Dacker Bjarne" 3 M 99415 #(221 35))
#(employee 104732 "Wikstrom Claes" 2 M 99586 #(221 15))
#(employee 107912 "Carlsson Tuula" 2 F 94556 #(242 56))
#(employee 104465 "Johnson Torbjorn" 1 M 99184 #(242 38))
#(atomic ok)
> (querying:show-data 'in-project)
#(in-project 104659 otp)
#(in-project 104659 wolf)
#(in-project 117716 documentation)
#(in-project 117716 otp)
#(in-project 115018 mnesia)
#(in-project 115018 otp)
#(in-project 104531 mnesia)
#(in-project 104531 otp)
#(in-project 114872 otp)
#(in-project 104732 erlang)
#(in-project 104732 mnesia)
#(in-project 104732 otp)
#(in-project 107912 otp)
#(in-project 104465 otp)
#(atomic ok)
> (querying:show-data 'in-department)
#(in-department 104659 B/SFR)
#(in-department 117716 B/SFP)
#(in-department 115018 B/SFP)
#(in-department 104531 B/SFR)
#(in-department 114872 B/SFR)
#(in-department 104732 B/SFR)
#(in-department 107912 B/SF)
#(in-department 104465 B/SF)
#(atomic ok)
>

Looks like everything is there. What's left?

4.3 Adding the Missing Data

There are a last few bits of data we can now add, given that we've got our employee data fully inserted: manager data.

We've got a couple employees who are managers. Let's get them managing:

> (insert-manager 104465 'B/SF)
#(atomic ok)
> (insert-manager 104465 'B/SFP)
#(atomic ok)
> (insert-manager 114872 'B/SFR)
#(atomic ok)

Check that:

> (querying:show-data 'manager)
#(manager 114872 B/SFR)
#(manager 104465 B/SF)
#(manager 104465 B/SFP)
#(atomic ok)
>

And we're good!

Next up: we're going to learn how to query our Mnesia tables...