Fetching and updating data in mnesia

Question

I have multiple mnesia tuples like (GroupID is the primary key)

{GroupID, GroupName, GroupType, GroupDescription, GroupTag, CreatorID, AdminID, MemberList, Counter}.

MemberList = "memberone@xyz,membertwo@xyz,memberthree@xyz".
GroupName = "Any String Value". % e.g.: "basketball"
GroupTag = "comma separated values". % e.g.: "Sports,Cricket,Fifa,Ronaldo"

I will pass a character or word to a function. This function will search the character in GroupName and GroupTag.

If successful then it will return comma separated tuples of GroupID, GroupName, GroupDescription; And Counter should be incremented for the corresponding row.

Suppose in my mnesia database tuples are

{"A", "Cricket", "0", "A group for cricket fans", "Sports, Cricket, Sachin tendulkar", "Xyz", "XYZ", "XYZ", 1},
{"B", "Sports", "0", "A group for Sport fans", "Sports,Cricket,Fifa,Ronaldo,Sachin tendulkar", "Xyz", "XYZ", "XYZ", 0}.

So if I search for "sac", it should give the output

[{"A", "Cricket", "A group for cricket fans"},
 {"B", "Sports", "A group for Sport fans"}] 

Counter value for group A should be 2 (it was 1, check the last element of the tuple) and for group B should be 1 (it was 0, check the last element of the tuple).

Any pointers?

Answer 1

As far as I know, you cannot build a guard with a call to get the substring from a string, so instead of using a Erlang Match Specification you would have to iterate over all the records and filter the ones that you need.

-module(tuples).
-compile(export_all).

-record(group, {group_id,
        group_name,
        group_type,
        group_description,
        group_tag,
        creator_id,
        admin_id,
        member_list,
        counter}).

start() ->
    mnesia:create_schema([node()]),
    mnesia:start().

stop() ->
    mnesia:stop(),
    mnesia:delete_schema([node()]).

load_data() ->
    mnesia:create_table(group,
            [{attributes, record_info(fields, group)}]),
    Record1 = #group{group_id = "A", 
             group_name = "Cricket",
             group_type = "0",
             group_description = "A group for cricket fans",
             group_tag = "Spots,Cricket,Sachin tendulkar",
             creator_id = "Xyz",
             admin_id = "XYZ",
             member_list = "XYZ",
             counter = 1},
    Record2 = #group{group_id = "B",
             group_name = "Sports",
             group_type = "0",
             group_description = "A group for Sport fans",
             group_tag = "Sports,Cricket,Fifa,Ronaldo,Sachin tendulkar",
             creator_id = "Xyz",
             admin_id = "XYZ",
             member_list = "XYZ",
             counter = 0},
    Insert = fun() -> lists:map(fun(X) -> mnesia:write(X) end, [Record1, Record2]) end,
    mnesia:transaction(Insert).

show_data() ->
    CatchAll = [{'_', [], ['$_']}],
    mnesia:dirty_select(group, CatchAll).

query(Substring) ->
    Update = fun(Record) ->
             NewRecord = Record#group{counter = Record#group.counter + 1},
             mnesia:write(NewRecord),
             NewRecord
         end,

    RequiredFields = fun(Record) -> {Record#group.group_id, Record#group.group_name, Record#group.group_description} end,

    Constraint = 
    fun(Group, Acc) ->
        case string:str(string:to_lower(Group#group.group_name), 
                string:to_lower(Substring)) of
            0 ->
                case string:str(string:to_lower(Group#group.group_tag),
                    string:to_lower(Substring)) of
                0 ->
                    Acc;
                _ ->
                    NewRecord = Update(Group),
                    [RequiredFields(Group) | NewRecord]
                end;
            _->
                NewRecord = Update(Group),
                [RequiredFields(Group) | NewRecord]
        end
    end,
    Find = fun() -> mnesia:foldl(Constraint, [], group) end,
    {_, Data} = mnesia:transaction(Find),
    Data.

and to try the code:

Eshell V6.4  (abort with ^G)
1> c("tuples.erl").
{ok,tuples}
2> tuples:start().
ok
3> tuples:load_data().
{atomic,[ok,ok]}
4> tuples:show_data().
[{group,"A","Cricket","0","A group for cricket fans",
        "Spots,Cricket,Sachin tendulkar","Xyz","XYZ","XYZ",1},
 {group,"B","Sports","0","A group for Sport fans",
        "Sports,Cricket,Fifa,Ronaldo,Sachin tendulkar","Xyz","XYZ",
        "XYZ",0}]
5> tuples:query("sac").
[{group,"A","Cricket","0","A group for cricket fans",
  "Spots,Cricket,Sachin tendulkar","Xyz","XYZ","XYZ",1}|
 {group,"A","Cricket","0","A group for cricket fans",
  "Spots,Cricket,Sachin tendulkar","Xyz","XYZ","XYZ",2}]
6> tuples:stop().

=INFO REPORT==== 14-Jun-2015::22:14:42 ===
    application: mnesia
    exited: stopped
    type: temporary
ok
7> q().
ok
8>