Erlang Mnesia

Carrying out my Erlang learning plan, I embarked on the porting exercise of my Address DAO package into Erlang Mnesia. In fact, it is more than porting of the data access functions. It is also a porting of the Address database itself from RDBMS into Mnesia.

Creating the Mnesia Schema

The Entity Relationship Diagram (ERD) of the database schema is shown below.

To create the tables in Mnesia, I defined the following records, each corresponding to a table.

-record(address_field_type, {id,
 name,
 locale_country,
 default_value,
 hierarchy_order,
 display_order,
 validation_rules,
 suffix} ).
 
-record(address_field, {id,
 value,
 address_field_type_id,
 location_code}).
 
-record(address, {id,
 status,
 location_code}).
 
-record(address_addressField, {
 address_id,
 address_field_id
 }).
-record(oids, {
 name,
 id
 }).

Note that there is an extra record oids. This record/table is required because Mnesia does not support auto-incrementing fields usually used as primary key generation. Therefore this extra table is used to store the current key value - similar to Oracle's sequence.

Creating the tables are shown below. The oids table is also populated with seed values.

create_db() ->
 mnesia:create_table(address_field_type,
  [
   {ram_copies,[nonode@nohost]},
   {attributes, record_info(fields, address_field_type)}
  ]),
 mnesia:create_table(address_field,
  [
   {ram_copies,[nonode@nohost]},
   {index, [location_code]},
   {attributes, record_info(fields, address_field)}
  ]),
 mnesia:create_table(address,
  [
   {ram_copies,[nonode@nohost]},
   {index, [location_code]},
   {attributes, record_info(fields, address)}
  ]),
 mnesia:create_table(address_addressField,
  [
   {type, bag},
   {ram_copies,[nonode@nohost]},
   {index, [address_field_id]},
   {attributes, record_info(fields, address_addressField)}
  ]),
 mnesia:create_table(oids,
  [
   {attributes, record_info(fields, oids)}
  ]),
 Fun = fun() ->
  mnesia:write(#oids{ name=address, id=0 }),
  mnesia:write(#oids{ name=address_field_type, id=0 }),
  mnesia:write(#oids{ name=address_field, id=0})
 end,
 mnesia:transaction(Fun).

Simple CRUD

The address_field_type is a table for storing reference data, which does not rely on other tables. So the C and R of the CRUD is very simple. From address_db.erl file:

%% returns {atomic, Oid} or {aborted, Reason}
generate_oid(TableName) when is_atom(TableName) ->
 Fun = fun() ->
  [Oid] = mnesia:read(oids, TableName, write),
  %% because Erlang only supports single assignment
  %% I have to create new variables every time the value changes.
  NewId=Oid#oids.id+1,
  New = Oid#oids{ id=NewId },
  mnesia:write(New),
  NewId
 end,
 mnesia:transaction(Fun).
  
insert_aft(Aft) when is_record(Aft, address_field_type) ->
 Fun = fun() ->
  {atomic, Id}=generate_oid(address_field_type),
  New = Aft#address_field_type{ id=Id },
  mnesia:write(New),
  New
 end,
 mnesia:transaction(Fun).

find_aft(Id) when is_integer(Id) ->
 {atomic, [Aft]} = mnesia:transaction(fun()-> mnesia:read({address_field_type, Id}) end),
 Aft.

There are a few things worth noting:

1. Although the mnesia:read and mnesia:select functions can be read-only, they still need to be wrapped in a function and called by mnesia:transaction/1. This does not make sense to me.
2. The mnesia:transaction(Fun) returns {aborted, Reason} or {atomic, Result} where Result is whatever the return result of the Fun is. Therefore, the insert_aft/1 above returns New - the new address_field_type record with the id field populated.
3. Erlang is single assignment language - i.e. a variable can only be assigned value once. Therefore, if I want to assign the Aft#address_field_type.id, I have to create a whole new address_field_type record as in the insert_aft/1 above. I have to say, I am not a fan of this single assignment - it defeats the half of the purpose of having variables.

A Slightly More Complicated CRUD

The address entity relies on address_field, which in turn depends on address_field_type. Also, if address records share the same address_field values, then the address_field record should be shared and not be duplicated in the database. Therefore, the management of the address records are more complicated than those of address_field_type.

The creation of address records is shown below.

insert_address(A, Afs) when is_record(A, address) and is_list(Afs) ->
 {A2, Afs2} = address:generate_location_code(A, Afs),
 Fun = fun() ->
  % populate the address_field records' id field
  NewAfs=lists:foldl( fun(Af, NewAfs) ->
    % check if the same Af is already in database
    Result = mnesia:select(address_field, [{
     #address_field{ id='$1',
      location_code=Af#address_field.location_code, 
      _='_' },
     [], ['$1'] }] 
    ),
    if length(Result)==0 -> % Af not in DB, so insert it
     {atomic, Id}=generate_oid(address_field);
    true -> % Af already exists in database
     Id=lists:nth(1, Result)
    end,
    NewAf = Af#address_field{ id=Id },
    lists:append(NewAfs, [NewAf])
   end,
   [],
   Afs2),
  
  % create the new address record
  {atomic, AddressId} = generate_oid(address),
  NewA = A2#address{ id = AddressId },
  mnesia:write(NewA),
  
  % now insert/update into address_field 
  % and insert into address_addressField table
  lists:foreach( fun(Af) ->
    mnesia:write(Af),
    A_Af=#address_addressField{ 
     address_id=AddressId,
     address_field_id=Af#address_field.id
    },
    mnesia:write(A_Af)
   end,
   NewAfs),
  {NewA, NewAfs}
 end,
 mnesia:transaction(Fun).

From the address.erl file:

%% applying quick sort to AddressField list.
sort_afs([]) -> [];
sort_afs([Pivot|Tail]) when is_record(Pivot, address_field) ->
 sort_afs([Af || Af <- Tail, 
  compare_aft(address_db:find_aft(Af#address_field.address_field_type_id),
   address_db:find_aft(Pivot#address_field.address_field_type_id)) < 0])
  ++ [Pivot] ++
  sort_afs([Af || Af <- Tail, 
  compare_aft(address_db:find_aft(Af#address_field.address_field_type_id),
   address_db:find_aft(Pivot#address_field.address_field_type_id)) >= 0]).

compare_aft(Aft1, Aft2) when 
 is_record(Aft1, address_field_type) and is_record(Aft2, address_field_type) ->
 Aft1#address_field_type.hierarchy_order - Aft2#address_field_type.hierarchy_order.

generate_location_code(A, AddressFields) when is_record(A, address) 
 and is_list(AddressFields) and (length(AddressFields)>0) ->
 Afs=generate_location_code(AddressFields),
 FirstAf=lists:nth(1, Afs),
 { 
  A#address{ location_code=FirstAf#address_field.location_code },
  Afs
 }.

generate_location_code(Afs) ->
 ReverseSortedAfs=lists:reverse(sort_afs(Afs)),
 generate_location_code(ReverseSortedAfs, [], []).
 
generate_location_code([Head|Tail], String, NewAfs) when is_record(Head, address_field) ->
 StringValue = Head#address_field.value ++ String,
 Code=erlang:phash2(StringValue),
 generate_location_code(Tail, 
  StringValue,
  [ Head#address_field{ location_code=Code } ] ++ NewAfs);  
generate_location_code([], _String, NewAfs) ->
 NewAfs.   

The following functions are queries from the address_db.erl file:

find_address(Id) when is_integer(Id) ->
 {atomic, [A]} = mnesia:transaction(fun()-> mnesia:read({address, Id}) end),
 A.
 
find_address_field(Id) when is_integer(Id) ->
 {atomic, [Af]} = mnesia:transaction(fun()-> mnesia:read({address_field, Id}) end),
 Af.
 
find_address_field_ids(A_id) ->
 mnesia:transaction(fun()-> mnesia:read({address_addressField, A_id}) end).
 
find_address_ids(Af_id) ->
 F = fun() ->
  mnesia:select(address_addressField, [{
   #address_addressField{ address_id='$1',
    address_field_id=Af_id, 
    _='_' },
    [], ['$1'] }] 
  )
 end,
 mnesia:transaction(F).
 
find_address_ids2(Af_id) ->
 F = fun() ->
  Q = qlc:q([Aaf#address_addressField.address_id 
   || Aaf <- mnesia:table(address_addressField),
   Aaf#address_addressField.address_field_id==Af_id]),
  qlc:e(Q)
 end,
 mnesia:transaction(F).

Things that I want to highlight from the above are:

1. Unlike Java or C#, Erlang does not have a sortable list out-of-the-box. Instead, I had to write functions that sort my list of address_field records - these are: sort_afs, compare_aft
2. When searching using the primary key field, mnesia:read is used; when searching using other fields, either use mnesia:select or QLC. In the above code, the functions find_address_ids/1 and find_address_ids2/1 do the same thing. However, the result (a list) given by find_address_ids2/1 are reversed.
3. If a record with the same primary key already exists in the table, the mnesia:write either creates (if table type if set) or updates (if table type is bag) the record in the table.
4. Again, due to the single assignment rule, the lists:append(List, AnotherList) does not change the List; instead, the new list is the return value of lists:append/2. This is just counter-intuitive.

Related Posts:

• Erlang Mnesia - Take 2