title: Managing incremental counters in mnesia DBMS?
body: <p>I have realised that mnesia doesnot support auto-increment feature as does MySQL or other RDBMS do.The counters talked about in mnesia documentation are not really well explained. forexample i have found sofar one function in the entire documentation which manipulates counters<br></p>
  
  <pre><code>mnesia:dirty_update_counter({Tab::atom(),Key::any()}, Val::positive_integer())</code></pre>
  
  <p>So, this has disturbed me for a time coz it works with records of type
  <pre>{TabName, Key, Integer}</pre>This is also unclear and possibly because no erlang book or mnesia documentation provides an example to explain it.This has forced me to implement my own counter manipulation APIs.Since i wanted to be able to access and manage my records using counters, i had to include a field called 'counter' in my records, then at the point of adding the record in the table which is intended to have counters, i do it like this:</p>
  
  <pre>#recordname{field1 = Val1,...,counter = auto_increment(?THIS_TABLE)}</pre>
  
  <p>The position of the counter fields doesnot matter. The API is implemented like this below:</p>
  
  <pre>%% @doc this function is called whenever u are writing a new record in the table<br>%% by giving its result to the counter field in your record.<br>%% @end<br>%%<br>%% @spec auto_increment(TableName::atom()) -> integer() | exit(Reason)<br><b>
  auto_increment(TableName)-&gt;
      case lists:member(counter,table_info(TableName,attributes)) of
          false -&gt; erlang:exit({counter,field,not_found,in_table,TableName});
          true -&gt; table_info(TableName,size) + 1
      end.<br>
  table_info(Tab,Item)-&gt;
      F = fun({X,Y}) -&gt; mnesia:table_info(X,Y) end,
      mnesia:activity(transaction,F,[{Tab,Item}],mnesia_frag).</b>
  </pre>
  
  <p>To explain this, if the counter field isnot an attribute of the table i forcibly let the process that is trying to execute this code to exit with a reason, so if the programmers call this within a try ...catch, or a case (catch ...) of body , they would easily see what is wrong. Alternatively, i could ask whether this code fragment is being executed within a transaction by using <code>mnesia:is_transaction()</code> and if this returns true, i call <code>mnesia:abort/1</code>, if false, i could just exit with reason.Also, i use mnesia_frag in the mnesia activity function because this implementation will work regardless of the fragmentation properties of a table.If i use the <code>mnesia:transaction(Fun)</code>, fragmented tables will become inconsistent because this call will only access the initial fragment (the base table).
  <br>
  Now, when a record is deleted from the table with counters, we need to re-arrange the order in the table. This operation is expensive as it requires iteration over the whole table.Because if they delete a record whose counter = 5, the one with counter = 6 must become counter = 5 and so on following the pattern. All records whose counters were greater than the one deleted, must be decremented. So by passing the counter value deleted and the TableName, its possible to iterate over the table using <br><code>mnesia:foldl/3 or mnesia:foldr/3 , the difference between these two comes in only with ordered table types</code><br>Here is the function which takes care of this:</p>
  
  <pre><b>
  auto_decrement(Counter_deleted,TableName)-&gt;
      Attrs = table_info(TableName,attributes),
      case lists:member(counter,Attrs) of
          false -&gt; erlang:exit({counter,field,not_found,in_table,TableName});
          true -&gt; 
              Counter_position = position(counter,Attrs) + 1,         
              Iterator =  fun(Rec,_) when element(Counter_position,Rec) &gt; Counter_deleted -&gt; 
                              Count = element(Counter_position,Rec),
                              New_rec = erlang:setelement(Counter_position,Rec,Count - 1),
                              mnesia:write(TableName,New_rec,read),
                              [];
                          (_,_) -&gt; []
                          end,
              Find = fun({Fun,Table}) -&gt; mnesia:foldl(Fun, [],Table) end,
              mnesia:activity(transaction,Find,[{Iterator,TableName}],mnesia_frag)
      end.</b>
  </pre>
  
  <p><br>
  You notice that i have code which helps me find the position of the counter field dynamically from the record. The code which helps me do this is shown below:</p>
  
  <pre>
  <b>
  position(_,[]) -&gt; -1;
  position(Value,List)-&gt;
      find(lists:member(Value,List),Value,List,1).
  
  find(false,_,_,_) -&gt; -1;
  find(true,V,[V|_],N)-&gt; N;
  find(true,V,[_|X],N)-&gt;
      find(V,X,N + 1).
  
  find(V,[V|_],N)-&gt; N;
  find(V,[_|X],N) -&gt; find(V,X,N + 1).
  </b>
  </pre>
  
  <p><br> This is so because this module must not know any of the programmers' records to help him with counters.Hence in order to access the value of the counter from the record using tuple manipulation functions such as <code>element(N::integer(),Tuple::tuple())</code>, i have to calculate its position in the tuple representation of the record, dynamically.
  <br><pre>These two functions have worked for me and are still working till auto_increment<br>is implemented in mnesia. </pre>
  <br>
  For example, using qlc (query list comprehension) to query tables with dynamic constraints, consider these pieces of code below:</p>
  
  <pre>
  <b>
  select(Q)-&gt;
      F = fun(QH) -&gt; qlc:e(QH) end,
      mnesia:activity(transaction,F,[Q],mnesia_frag).
  
  read_by_custom_validation(Validation_fun,From_table)-&gt;
      select(qlc:q([X || X &lt;- mnesia:table(From_table),Validation_fun(X) == true])).
  
  %% Applying the two functions....
  find_records_with_counter(From_this,To_that) when <br>       is_integer(From_this),is_integer(To_that),To_that &gt; From_this -&gt;
      F = fun(#recordName{counter = N}) when N &gt;= From_this,N =&lt; To_That -&gt; true;
          (_) -&gt; false
      end,
      read_by_custom_validation(F,TableName).
  </b>
  </pre>
  
  <p>In a stock management system, this is working...</p>
  
  <pre><b>
  
  (stock_project@127.0.0.1)6> stock:get_items_in_range(1,4).
  [#item{item_id = "D694",name = "cement",
         time_stamp = {"30/12/2010","11:29:10 am"},
         min_stock = 500,units = "bags",unit_cost = 20000,
         state = available,last_modified = undefined,
         category = "building material",counter = 1},
   #item{item_id = "131B",name = "nails",
         time_stamp = {"30/12/2010","11:29:10 am"},
         min_stock = 20000,units = "kgs",unit_cost = 1000,
         state = available,last_modified = undefined,
         category = "building material",counter = 2},
   #item{item_id = "FDD9",name = "iron sheets",
         time_stamp = {"30/12/2010","11:29:10 am"},
         min_stock = 20,units = "bars",unit_cost = 50000,
         state = available,last_modified = undefined,
         category = "building material",counter = 3},
   #item{item_id = "09D4",name = "paint",
         time_stamp = {"30/12/2010","11:29:10 am"},
         min_stock = 30000,units = "tins",unit_cost = 5000,
         state = available,last_modified = undefined,
         category = "building material",counter = 4}]
  (stock_project@127.0.0.1)7>
  </b></pre>
  
  <p>This is working for me. Please advise me on how else i should take care of counters.Or you could tell me how u r handling them over that side.</p>