How to save extra fields in archive table using Ejabberd mod_mam (Message Archive Management, XEP-0313)?

Question

I am using Ejabberd server for chatting application. It works to save messages in arhieve table, but I want to save additional field in the table when message is sent. This field will be per message.

Answer 1

There are some ways to achieve this. The first and the simplest way (but it only affects 'xml' field in the 'archive' table) is an implementation of 'store_mam_message' hook in your custom module. You can modify Packet inside that hook and return new packet which should be saved in the DB. This hook is available since v16.09

If you modified 'archive' table (a new column has been added to it), then the second way helps:

1. Create your custom module and call it "mod_mam_sql2"

2. Copy the content of "src/mod_mam_sql.erl" into your custom module

3. Find the function store/7.

       store(Pkt, LServer, {LUser, LHost}, Type, Peer, Nick, _Dir) ->
       TSinteger = p1_time_compat:system_time(micro_seconds),
       ID = jlib:integer_to_binary(TSinteger),
       SUser = case Type of
           chat -> LUser;
           groupchat -> jid:to_string({LUser, LHost, >})
           end,
       BarePeer = jid:to_string(
            jid:tolower(
              jid:remove_resource(Peer))),
       LPeer = jid:to_string(
             jid:tolower(Peer)),
       XML = fxml:element_to_binary(Pkt),
       Body = fxml:get_subtag_cdata(Pkt, >),
       SType = jlib:atom_to_binary(Type),
       case ejabberd_sql:sql_query(
              LServer,
              ?SQL("insert into archive (username, timestamp,"
                   " peer, bare_peer, xml, txt, kind, nick) values ("
           "%(SUser)s, "
           "%(TSinteger)d, "
           "%(LPeer)s, "
           "%(BarePeer)s, "
           "%(XML)s, "
           "%(Body)s, "
           "%(SType)s, "
           "%(Nick)s)")) of
       {updated, _} ->
           {ok, ID};
       Err ->
           Err
       end.
   

4. Change the SQL-query as you need

5. Compile your custom module: ejabberdctl module_install mod_mam_sql2

6. Update ejabberd.yml config file

       mod_mam:
          db_type: sql2
   

7. Restart ejabberd server: ejabberdctl restart

I hope it will help you to solve your issue.

Answer 2

Igor, Thanks for your answer, looks promising, but I did something else which I want to share here. I have installed ejabberd from source code with help of this link http://www.blikoon.com/networking/how-to-install-ejabberd-on-linux-ubuntu then I modified mod_mam_sql.erl file in src folder. Code I modified is as follow. I recompiled module and it worked.

Body = fxml:get_subtag_cdata(Pkt, <<"body">>),
Resid = fxml:get_subtag_cdata(Pkt, <<"resid">>),
Ownuid = fxml:get_subtag_cdata(Pkt, <<"ownuid">>),
SType = jlib:atom_to_binary(Type),
case ejabberd_sql:sql_query(
     LServer,
     ?SQL("insert into archive (username, timestamp,"
                " peer, bare_peer, xml, txt, kind, nick,resid,Ownuid) values ("
 "%(SUser)s, "
 "%(TSinteger)d, "
 "%(LPeer)s, "
 "%(BarePeer)s, "
 "%(XML)s, "
 "%(Body)s, "
 "%(SType)s, "
 "%(Nick)s, "
 "%(Resid)s, "
 "%(Ownuid)s)")) of
{updated, _} ->
 {ok, ID};