RE: Sqlbox performance patch

Index: ChangeLog
===================================================================
--- ChangeLog	(revision 52)
+++ ChangeLog	(working copy)
@@ -1,3 +1,9 @@
+2013-08-11  Rene Kluwen <rene dot kluwen at chimit dot nl>
+    implemented configuration directives: limit-per-cycle (thanks to Alex
+    Guerrieri for the original patch), save-dlr, save-mo, save-mt.
+    Speed improvements should be significant because we process batches of
+    records both when selecting and inserting as well as deleting.
+
 2011-05-08  Rene Kluwen <rene dot kluwen at chimit dot nl>
     * gw/sqlbox.c
       fixed memory leak sending messages through the send_sms table
Index: doc/userguide.xml
===================================================================
--- doc/userguide.xml	(revision 52)
+++ doc/userguide.xml	(working copy)
@@ -531,37 +531,69 @@
                 <entry>host-name</entry>
                 <entry valign="bottom">This is the host where bearerbox is running.</entry>
               </row>
-                            <row>
-                                <entry>
-                                    <literal>bearerbox-port (m)</literal>
-                                </entry>
-                                <entry>port-number</entry>
-                                <entry valign="bottom">This is the port number used to connect to bearerbox.</entry>
-                            </row>
-                            <row>
-                                <entry>
-                                    <literal>smsbox-port (c)</literal>
-                                </entry>
-                                <entry>port-number</entry>
-                                <entry valign="bottom"> This is the port number to which the
-                                    smsboxes, if any, connect. This can be
-                                    anything you want. Must be set if you want to handle any SMS
-                                    traffic. </entry>
-                            </row>
-                            <row>
-                                <entry>
-                                    <literal>smsbox-port-ssl (o)</literal>
-                                </entry>
-                                <entry>bool</entry>
-                                <entry valign="bottom"> If set to true, the smsbox connection
-                                    module will be SSL-enabled. Your smsboxes will have to connect
-                                    using SSL to sqlbox then. This is used to secure
-                                    communication between sqlbox and smsboxes in case they are
-                                    in separate networks operated and the TCP communication is not
-                                    secured on a lower network layer. Defaults to "no". </entry>
-                            </row>
               <row>
+                  <entry>
+                      <literal>bearerbox-port (m)</literal>
+                  </entry>
+                  <entry>port-number</entry>
+                  <entry valign="bottom">This is the port number used to connect to bearerbox.</entry>
+              </row>
+              <row>
+                  <entry>
+                      <literal>smsbox-port (c)</literal>
+                  </entry>
+                  <entry>port-number</entry>
+                  <entry valign="bottom"> This is the port number to which the
+                      smsboxes, if any, connect. This can be
+                      anything you want. Must be set if you want to handle any SMS
+                      traffic. </entry>
+              </row>
+              <row>
+                  <entry>
+                      <literal>smsbox-port-ssl (o)</literal>
+                  </entry>
+                  <entry>bool</entry>
+                  <entry valign="bottom"> If set to true, the smsbox connection
+                      module will be SSL-enabled. Your smsboxes will have to connect
+                      using SSL to sqlbox then. This is used to secure
+                      communication between sqlbox and smsboxes in case they are
+                      in separate networks operated and the TCP communication is not
+                      secured on a lower network layer. Defaults to "no". </entry>
+              </row>
+              <row>
                 <entry>
+                  <literal>limit-per-cycle</literal>
+                </entry>
+                <entry>number</entry>
+                <entry valign="bottom">Sqlbox processes this number of messages at a time. Note:
+                    Currently only the MySQL driver supports this configuration directive. Defaults to 10.</entry>
+              </row>
+              <row>
+                <entry>
+                  <literal>save-dlr (o)</literal>
+                </entry>
+                <entry>bool</entry>
+                <entry valign="bottom">Indicates whether to save dlr messages in the sql-log-table. Defaults to "yes".</entry>
+              </row>
+              <row>
+                <entry>
+                  <literal>save-mo (o)</literal>
+                </entry>
+                <entry>bool</entry>
+                <entry valign="bottom">Indicates whether to save mobile originated (MO) messages in the sql-log-table.
+                    Defaults to "yes".</entry>
+              </row>
+              <row>
+                <entry>
+                  <literal>save-mt (o)</literal>
+                </entry>
+                <entry>bool</entry>
+                <entry valign="bottom">Indicates whether to save mobile terminated (MT) messages in the sql-log-table.
+                    This applies to messages that are sent via smsbox as well as messages inserted in the sql-insert-table.
+                    Defaults to "yes".</entry>
+              </row>
+              <row>
+                <entry>
                   <literal>sql-log-table</literal>
                 </entry>
                 <entry>table-name</entry>
Index: gw/sqlbox_sdb.c
===================================================================
--- gw/sqlbox_sdb.c	(revision 52)
+++ gw/sqlbox_sdb.c	(working copy)
@@ -409,6 +409,8 @@
     res->sql_leave = sdb_leave;
     res->sql_fetch_msg = sdb_fetch_msg;
     res->sql_save_msg = sdb_save_msg;
+    res->sql_fetch_msg_list = NULL;
+    res->sql_save_list = NULL;
     return res;
 }
 #endif
Index: gw/sqlbox_mysql.h
===================================================================
--- gw/sqlbox_mysql.h	(revision 52)
+++ gw/sqlbox_mysql.h	(working copy)
@@ -31,6 +31,11 @@
 compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, \
 charset, boxc_id, binfo, meta_data FROM %S LIMIT 0,1"
 
+#define SQLBOX_MYSQL_SELECT_LIST_QUERY "SELECT sql_id, momt, sender, receiver, udhdata, \
+msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, \
+compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, \
+charset, boxc_id, binfo, meta_data FROM %S LIMIT 0,%ld"
+
 #define SQLBOX_MYSQL_INSERT_QUERY "INSERT INTO %S ( sql_id, momt, sender, \
 receiver, udhdata, msgdata, time, smsc_id, service, account, sms_type, \
 mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, \
@@ -38,7 +43,13 @@
 NULL, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, \
 %S, %S, %S, %S, %S, %S, %S, %S, %S)"
 
+#define SQLBOX_MYSQL_INSERT_LIST_QUERY "INSERT INTO %S ( sql_id, momt, sender, \
+receiver, udhdata, msgdata, time, smsc_id, service, account, sms_type, \
+mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, \
+pid, alt_dcs, rpi, charset, boxc_id, binfo, meta_data ) VALUES %S"
+
 #define SQLBOX_MYSQL_DELETE_QUERY "DELETE FROM %S WHERE sql_id = %S"
+#define SQLBOX_MYSQL_DELETE_LIST_QUERY "DELETE FROM %S WHERE sql_id in (%S)"
 
 #endif /* HAVE_MYSQL || HAVE_SDB */
 
Index: gw/sqlbox.c
===================================================================
--- gw/sqlbox.c	(revision 52)
+++ gw/sqlbox.c	(working copy)
@@ -85,6 +85,8 @@
 static Octstr *bearerbox_host;
 static int bearerbox_port_ssl = 0;
 static Octstr *global_sender;
+static long limit_per_cycle;
+static int save_mo, save_mt, save_dlr;
 
 #ifndef HAVE_MSSQL
 #ifndef HAVE_MYSQL
@@ -103,7 +105,8 @@
 #endif
 Octstr *sqlbox_id;
 
-#define SLEEP_BETWEEN_SELECTS 1.0
+#define SLEEP_BETWEEN_EMPTY_SELECTS 1.0
+#define DEFAULT_LIMIT_PER_CYCLE 10
 
 typedef struct _boxc {
     Connection    *smsbox_connection;
@@ -285,14 +288,16 @@
 
         if (msg_type(msg) == sms) {
             debug("sqlbox", 0, "smsbox_to_bearerbox: sms received");
-            msg_escaped = msg_duplicate(msg);
-            /* convert validity & deferred to minutes */
-            if (msg_escaped->sms.validity != SMS_PARAM_UNDEFINED)
-                msg_escaped->sms.validity = (msg_escaped->sms.validity - time(NULL))/60;
-            if (msg_escaped->sms.deferred != SMS_PARAM_UNDEFINED)
-                msg_escaped->sms.deferred = (msg_escaped->sms.deferred - time(NULL))/60;
-            gw_sql_save_msg(msg_escaped, octstr_imm("MT"));
-            msg_destroy(msg_escaped);
+            if (save_mt) {
+                msg_escaped = msg_duplicate(msg);
+                /* convert validity & deferred to minutes */
+                if (msg_escaped->sms.validity != SMS_PARAM_UNDEFINED)
+                    msg_escaped->sms.validity = (msg_escaped->sms.validity - time(NULL))/60;
+                if (msg_escaped->sms.deferred != SMS_PARAM_UNDEFINED)
+                    msg_escaped->sms.deferred = (msg_escaped->sms.deferred - time(NULL))/60;
+                gw_sql_save_msg(msg_escaped, octstr_imm("MT"));
+                msg_destroy(msg_escaped);
+            }
         }
 
         send_msg(conn->bearerbox_connection, conn, msg);
@@ -434,9 +439,9 @@
         if (msg_type(msg) == sms) {
             msg_escaped = msg_duplicate(msg);
             if (msg->sms.sms_type != report_mo)
-                gw_sql_save_msg(msg_escaped, octstr_imm("MO"));
+                if (save_mo) gw_sql_save_msg(msg_escaped, octstr_imm("MO"));
             else
-                gw_sql_save_msg(msg_escaped, octstr_imm("DLR"));
+                if (save_dlr) gw_sql_save_msg(msg_escaped, octstr_imm("DLR"));
             msg_destroy(msg_escaped);
         }
         send_msg(conn->smsbox_connection, conn, msg);
@@ -549,9 +554,9 @@
         }
         if (msg_type(msg) == sms) {
             if (msg->sms.sms_type != report_mo)
-                gw_sql_save_msg(msg, octstr_imm("MO"));
+                if (save_mo) gw_sql_save_msg(msg, octstr_imm("MO"));
             else
-                gw_sql_save_msg(msg, octstr_imm("DLR"));
+                if (save_dlr) gw_sql_save_msg(msg, octstr_imm("DLR"));
 
 	    /* create ack message */
 	    mack = msg_create(ack);
@@ -567,27 +572,10 @@
     }
 }
 
-static void sql_to_bearerbox(void *arg)
+static void sql_single(Boxc *boxc)
 {
-    Boxc *boxc;
     Msg *msg;
 
-    boxc = gw_malloc(sizeof(Boxc));
-    boxc->bearerbox_connection = connect_to_bearerbox_real(bearerbox_host, bearerbox_port, bearerbox_port_ssl, NULL /* bb_our_host */);
-    boxc->smsbox_connection = NULL;
-    boxc->client_ip = NULL;
-    boxc->alive = 1;
-    boxc->connect_time = time(NULL);
-    boxc->boxc_id = octstr_duplicate(sqlbox_id);
-    if (boxc->bearerbox_connection == NULL) {
-        boxc_destroy(boxc);
-        return;
-    }
-
-    gwthread_create(bearerbox_to_sql, boxc);
-
-    identify_to_bearerbox(boxc);
-
     while (sqlbox_status == SQL_RUNNING && boxc->alive) {
         if ((msg = gw_sql_fetch_msg()) != NULL) {
             if (charset_processing(msg) == -1) {
@@ -605,21 +593,99 @@
                 msg->sms.deferred = time(NULL) + msg->sms.deferred * 60;
             send_msg(boxc->bearerbox_connection, boxc, msg);
 
-            /* convert validity & deferred back to minutes
-             * TODO clarify why we fetched message from DB and then insert it back here???
-             */
-            if (msg->sms.validity != SMS_PARAM_UNDEFINED)
-                msg->sms.validity = (msg->sms.validity - time(NULL))/60;
-            if (msg->sms.deferred != SMS_PARAM_UNDEFINED)
-                msg->sms.deferred = (msg->sms.deferred - time(NULL))/60;
-            gw_sql_save_msg(msg, octstr_imm("MT"));
+            if (save_mt) {
+                /* convert validity & deferred back to minutes
+                 * TODO clarify why we fetched message from DB and then insert it back here???
+                 */
+                if (msg->sms.validity != SMS_PARAM_UNDEFINED)
+                    msg->sms.validity = (msg->sms.validity - time(NULL))/60;
+                if (msg->sms.deferred != SMS_PARAM_UNDEFINED)
+                    msg->sms.deferred = (msg->sms.deferred - time(NULL))/60;
+                gw_sql_save_msg(msg, octstr_imm("MT"));
+            }
         }
         else {
-            gwthread_sleep(SLEEP_BETWEEN_SELECTS);
+            gwthread_sleep(SLEEP_BETWEEN_EMPTY_SELECTS);
         }
         msg_destroy(msg);
     }
+}
 
+static void sql_list(Boxc *boxc)
+{
+    Msg *msg;
+    List *qlist, *save_list;
+
+    qlist = gwlist_create();
+    gwlist_add_producer(qlist);
+    save_list = gwlist_create();
+    gwlist_add_producer(save_list);
+
+    while (sqlbox_status == SQL_RUNNING && boxc->alive) {
+	if ( gw_sql_fetch_msg_list(qlist, limit_per_cycle) > 0 ) {
+	    while((gwlist_len(qlist)>0) && ((msg = gwlist_consume(qlist)) != NULL )) {
+                if (charset_processing(msg) == -1) {
+                    error(0, "Could not charset process message, dropping it!");
+                    msg_destroy(msg);
+                    continue;
+                }
+                if (global_sender != NULL && (msg->sms.sender == NULL || octstr_len(msg->sms.sender) == 0)) {
+                    msg->sms.sender = octstr_duplicate(global_sender);
+                }
+                /* convert validity and deferred to unix timestamp */
+                if (msg->sms.validity != SMS_PARAM_UNDEFINED)
+                    msg->sms.validity = time(NULL) + msg->sms.validity * 60;
+                if (msg->sms.deferred != SMS_PARAM_UNDEFINED)
+                    msg->sms.deferred = time(NULL) + msg->sms.deferred * 60;
+                send_msg(boxc->bearerbox_connection, boxc, msg);
+    
+                /* convert validity & deferred back to minutes */
+                if (msg->sms.validity != SMS_PARAM_UNDEFINED)
+                    msg->sms.validity = (msg->sms.validity - time(NULL))/60;
+                if (msg->sms.deferred != SMS_PARAM_UNDEFINED)
+                    msg->sms.deferred = (msg->sms.deferred - time(NULL))/60;
+		gwlist_produce(save_list, msg);
+            }
+            /* save_list also deletes and destroys messages */
+	    gw_sql_save_list(save_list, octstr_imm("MT"), save_mt);
+        }
+        else {
+            gwthread_sleep(SLEEP_BETWEEN_EMPTY_SELECTS);
+        }
+    }
+
+    gwlist_remove_producer(qlist);
+    gwlist_remove_producer(save_list);
+    gwlist_destroy(qlist, msg_destroy_item);
+    gwlist_destroy(save_list, msg_destroy_item);
+}
+
+static void sql_to_bearerbox(void *arg)
+{
+    Boxc *boxc;
+
+    boxc = gw_malloc(sizeof(Boxc));
+    boxc->bearerbox_connection = connect_to_bearerbox_real(bearerbox_host, bearerbox_port, bearerbox_port_ssl, NULL /* bb_our_host */);
+    boxc->smsbox_connection = NULL;
+    boxc->client_ip = NULL;
+    boxc->alive = 1;
+    boxc->connect_time = time(NULL);
+    boxc->boxc_id = octstr_duplicate(sqlbox_id);
+    if (boxc->bearerbox_connection == NULL) {
+        boxc_destroy(boxc);
+        return;
+    }
+
+    gwthread_create(bearerbox_to_sql, boxc);
+    identify_to_bearerbox(boxc);
+
+    if (gw_sql_fetch_msg_list == NULL || gw_sql_save_list == NULL || limit_per_cycle <= 1) {
+        sql_single(boxc);
+    }
+    else {
+        sql_list(boxc);
+    }
+
     boxc_destroy(boxc);
 }
 
@@ -745,6 +811,21 @@
 
     if (cfg_get_integer(&sqlbox_port, grp, octstr_imm("smsbox-port")) == -1)
         sqlbox_port = 13005;
+
+    /* setup limit per cycle */
+    if (cfg_get_integer(&limit_per_cycle, grp, octstr_imm("limit-per-cycle")) == -1)
+        limit_per_cycle = DEFAULT_LIMIT_PER_CYCLE;
+
+    /* set up save parameters */
+    if (cfg_get_bool(&save_mo, grp, octstr_imm("save-mo")) == -1)
+        save_mo = 1;
+
+    if (cfg_get_bool(&save_mt, grp, octstr_imm("save-mt")) == -1)
+        save_mt = 1;
+
+    if (cfg_get_bool(&save_dlr, grp, octstr_imm("save-dlr")) == -1)
+        save_dlr = 1;
+
     /* setup logfile stuff */
     logfile = cfg_get(grp, octstr_imm("log-file"));
 
Index: gw/sqlbox_pgsql.c
===================================================================
--- gw/sqlbox_pgsql.c	(revision 52)
+++ gw/sqlbox_pgsql.c	(working copy)
@@ -324,6 +324,8 @@
     res->sql_leave = pgsql_leave;
     res->sql_fetch_msg = pgsql_fetch_msg;
     res->sql_save_msg = pgsql_save_msg;
+    res->sql_fetch_msg_list = NULL;
+    res->sql_save_list = NULL;
     return res;
 }
 
Index: gw/sqlbox-cfg.def
===================================================================
--- gw/sqlbox-cfg.def	(revision 52)
+++ gw/sqlbox-cfg.def	(working copy)
@@ -17,6 +17,10 @@
     OCTSTR(log-file)
     OCTSTR(log-level)
     OCTSTR(bearerbox-port)
+    OCTSTR(limit-per-cycle)
+    OCTSTR(save-mo)
+    OCTSTR(save-mt)
+    OCTSTR(save-dlr)
     OCTSTR(ssl-client-certkey-file)
     OCTSTR(ssl-server-cert-file)
     OCTSTR(ssl-server-key-file)
Index: gw/sqlbox_mssql.c
===================================================================
--- gw/sqlbox_mssql.c	(revision 52)
+++ gw/sqlbox_mssql.c	(working copy)
@@ -291,6 +291,8 @@
     res->sql_leave = mssql_leave;
     res->sql_fetch_msg = mssql_fetch_msg;
     res->sql_save_msg = mssql_save_msg;
+    res->sql_fetch_msg_list = NULL;
+    res->sql_save_list = NULL;
     return res;
 }
 #endif
Index: gw/sqlbox_sqlite.c
===================================================================
--- gw/sqlbox_sqlite.c	(revision 52)
+++ gw/sqlbox_sqlite.c	(working copy)
@@ -319,6 +319,8 @@
     res->sql_leave = sqlite_leave;
     res->sql_fetch_msg = sqlite_fetch_msg;
     res->sql_save_msg = sqlite_save_msg;
+    res->sql_fetch_msg_list = NULL;
+    res->sql_save_list = NULL;
     return res;
 }
 #endif
Index: gw/sqlbox_sql.h
===================================================================
--- gw/sqlbox_sql.h	(revision 52)
+++ gw/sqlbox_sql.h	(working copy)
@@ -16,6 +16,8 @@
     void (*sql_leave) ();
     Msg *(*sql_fetch_msg) ();
     void (*sql_save_msg) (Msg *, Octstr *);
+    int  (*sql_fetch_msg_list) (List *, long);
+    void (*sql_save_list) (List *, Octstr *, int);
 };
 
 struct sqlbox_db_queries {
@@ -38,6 +40,8 @@
 struct server_type *sql_type;
 
 #define gw_sql_fetch_msg sql_type->sql_fetch_msg
+#define gw_sql_fetch_msg_list sql_type->sql_fetch_msg_list
+#define gw_sql_save_list sql_type->sql_save_list
 #define gw_sql_save_msg(message, table) \
     do { \
         octstr_url_encode(message->sms.msgdata); \
@@ -55,6 +59,6 @@
     octstr_destroy(sql); \
 }
 
-//#define SQLBOX_TRACE
+#undef SQLBOX_TRACE
 
 #endif
Index: gw/sqlbox_oracle.c
===================================================================
--- gw/sqlbox_oracle.c	(revision 52)
+++ gw/sqlbox_oracle.c	(working copy)
@@ -326,6 +326,8 @@
     res->sql_leave = oracle_leave;
     res->sql_fetch_msg = oracle_fetch_msg;
     res->sql_save_msg = oracle_save_msg;
+    res->sql_fetch_msg_list = NULL;
+    res->sql_save_list = NULL;
     return res;
 }
 #endif
Index: gw/sqlbox_mysql.c
===================================================================
--- gw/sqlbox_mysql.c	(revision 52)
+++ gw/sqlbox_mysql.c	(working copy)
@@ -104,9 +104,6 @@
     MYSQL_ROW row;
 
     sql = octstr_format(SQLBOX_MYSQL_SELECT_QUERY, sqlbox_insert_table);
-#if defined(SQLBOX_TRACE)
-     debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
-#endif
     res = mysql_select(sql);
     if (res == NULL) {
         debug("sqlbox", 0, "SQL statement failed: %s", octstr_get_cstr(sql));
@@ -117,6 +114,7 @@
             id = octstr_null_create(row[0]);
             /* save fields in this row as msg struct */
             msg = msg_create(sms);
+            msg->sms.foreign_id = octstr_null_create(row[0]);
             msg->sms.sender     = octstr_null_create(row[2]);
             msg->sms.receiver   = octstr_null_create(row[3]);
             msg->sms.udhdata    = octstr_null_create(row[4]);
@@ -125,7 +123,6 @@
             msg->sms.smsc_id    = octstr_null_create(row[7]);
             msg->sms.service    = octstr_null_create(row[8]);
             msg->sms.account    = octstr_null_create(row[9]);
-            /* msg->sms.id      = atol_null(row[10]); */
             msg->sms.sms_type   = atol_null(row[11]);
             msg->sms.mclass     = atol_null(row[12]);
             msg->sms.mwi        = atol_null(row[13]);
@@ -162,6 +159,64 @@
     return msg;
 }
 
+int mysql_fetch_msg_list(List *qlist, long limit)
+{
+    Msg *msg = NULL;
+    Octstr *sql, *delet, *id;
+    MYSQL_RES *res;
+    MYSQL_ROW row;
+    int ret = 0;
+
+    sql = octstr_format(SQLBOX_MYSQL_SELECT_LIST_QUERY, sqlbox_insert_table, limit);
+    res = mysql_select(sql);
+    if (res == NULL) {
+        debug("sqlbox", 0, "SQL statement failed: %s", octstr_get_cstr(sql));
+    }
+    else {
+	ret = mysql_num_rows(res);
+        if (ret >= 1) {
+            while (row = mysql_fetch_row(res)) {
+                /* save fields in this row as msg struct */
+                msg = msg_create(sms);
+                msg->sms.foreign_id = octstr_null_create(row[0]);
+                msg->sms.sender     = octstr_null_create(row[2]);
+                msg->sms.receiver   = octstr_null_create(row[3]);
+                msg->sms.udhdata    = octstr_null_create(row[4]);
+                msg->sms.msgdata    = octstr_null_create(row[5]);
+                msg->sms.time       = atol_null(row[6]);
+                msg->sms.smsc_id    = octstr_null_create(row[7]);
+                msg->sms.service    = octstr_null_create(row[8]);
+                msg->sms.account    = octstr_null_create(row[9]);
+                msg->sms.sms_type   = atol_null(row[11]);
+                msg->sms.mclass     = atol_null(row[12]);
+                msg->sms.mwi        = atol_null(row[13]);
+                msg->sms.coding     = atol_null(row[14]);
+                msg->sms.compress   = atol_null(row[15]);
+                msg->sms.validity   = atol_null(row[16]);
+                msg->sms.deferred   = atol_null(row[17]);
+                msg->sms.dlr_mask   = atol_null(row[18]);
+                msg->sms.dlr_url    = octstr_null_create(row[19]);
+                msg->sms.pid        = atol_null(row[20]);
+                msg->sms.alt_dcs    = atol_null(row[21]);
+                msg->sms.rpi        = atol_null(row[22]);
+                msg->sms.charset    = octstr_null_create(row[23]);
+                msg->sms.binfo      = octstr_null_create(row[25]);
+                msg->sms.meta_data  = octstr_null_create(row[26]);
+                if (row[24] == NULL) {
+                    msg->sms.boxc_id= octstr_duplicate(sqlbox_id);
+                }
+                else {
+                    msg->sms.boxc_id= octstr_null_create(row[24]);
+                }
+                gwlist_produce(qlist, msg);
+            }
+        }
+        mysql_free_result(res);
+    }
+    octstr_destroy(sql);
+    return ret;
+}
+
 static Octstr *get_numeric_value_or_return_null(long int num)
 {
     if (num == -1) {
@@ -178,6 +233,7 @@
     if (octstr_compare(str, octstr_imm("")) == 0) {
         return octstr_create("NULL");
     }
+    /* todo: create a new string instead of inline replacing */
     octstr_replace(str, octstr_imm("\\"), octstr_imm("\\\\"));
     octstr_replace(str, octstr_imm("\'"), octstr_imm("\\\'"));
     return octstr_format("\'%S\'", str);
@@ -186,7 +242,7 @@
 #define st_num(x) (stuffer[stuffcount++] = get_numeric_value_or_return_null(x))
 #define st_str(x) (stuffer[stuffcount++] = get_string_value_or_return_null(x))
 
-void mysql_save_msg(Msg *msg, Octstr *momt /*, Octstr smsbox_id */)
+void mysql_save_msg(Msg *msg, Octstr *momt)
 {
     Octstr *sql;
     Octstr *stuffer[30];
@@ -206,6 +262,53 @@
     octstr_destroy(sql);
 }
 
+/* save a list of messages and delete them from the insert table */
+void mysql_save_list(List *qlist, Octstr *momt, int save_mt)
+{
+    Octstr *sql, *values, *ids, *sep;
+    Octstr *stuffer[30];
+    int stuffcount = 0, first = 1;
+    Msg *msg;
+
+    values = octstr_create("");
+    ids = octstr_create("");
+    sep = octstr_imm("");
+    while (gwlist_len(qlist) > 0 && (msg = gwlist_consume(qlist)) != NULL) {
+        if (save_mt) {
+            /* convert into urlencoded tekst first */
+            octstr_url_encode(msg->sms.msgdata);
+            octstr_url_encode(msg->sms.udhdata);
+            octstr_format_append(values, "%S (NULL, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S)",
+                sep, st_str(momt), st_str(msg->sms.sender),
+                st_str(msg->sms.receiver), st_str(msg->sms.udhdata), st_str(msg->sms.msgdata), st_num(msg->sms.time),
+                st_str(msg->sms.smsc_id), st_str(msg->sms.service), st_str(msg->sms.account), st_num(msg->sms.sms_type),
+                st_num(msg->sms.mclass), st_num(msg->sms.mwi), st_num(msg->sms.coding), st_num(msg->sms.compress),
+                st_num(msg->sms.validity), st_num(msg->sms.deferred), st_num(msg->sms.dlr_mask), st_str(msg->sms.dlr_url),
+                st_num(msg->sms.pid), st_num(msg->sms.alt_dcs), st_num(msg->sms.rpi), st_str(msg->sms.charset),
+                st_str(msg->sms.boxc_id), st_str(msg->sms.binfo), st_str(msg->sms.meta_data));
+        }
+        octstr_format_append(ids, "%S %S", sep, msg->sms.foreign_id);
+        msg_destroy(msg);
+        if (first) {
+            first = 0;
+            sep = octstr_imm(",");
+        }
+        while (stuffcount > 0) {
+            octstr_destroy(stuffer[--stuffcount]);
+        }
+    }
+    if (save_mt) {
+        sql = octstr_format(SQLBOX_MYSQL_INSERT_LIST_QUERY, sqlbox_logtable, values);
+        sql_update(sql);
+        octstr_destroy(sql);
+    }
+    octstr_destroy(values);
+    sql = octstr_format(SQLBOX_MYSQL_DELETE_LIST_QUERY, sqlbox_insert_table, ids);
+    octstr_destroy(ids);
+    sql_update(sql);
+    octstr_destroy(sql);
+}
+
 void mysql_leave()
 {
     dbpool_destroy(pool);
@@ -304,6 +407,8 @@
     res->sql_leave = mysql_leave;
     res->sql_fetch_msg = mysql_fetch_msg;
     res->sql_save_msg = mysql_save_msg;
+    res->sql_fetch_msg_list = mysql_fetch_msg_list;
+    res->sql_save_list = mysql_save_list;
     return res;
 }
 #endif