In a production system we should not store messages longer than needed, as it will quickly bloat our DB. In case one wants to store messages for debug purposes, we could add one of the following capabilities later: - hexdump to a log file - send to an SMPP entry on delivery - send to Wireshark
-- Regards, Alexander Chemeris. CEO, Fairwaves, Inc. / ООО УмРадио https://fairwaves.co
From df2bb8e1f6b8b4c290b5659fcf2e86845df3812f Mon Sep 17 00:00:00 2001 From: Alexander Chemeris <[email protected]> Date: Sat, 8 Mar 2014 21:57:07 +0100 Subject: [PATCH 2/2] sms,db: Do not store delivered messages in the DB. In a production system we should not store messages longer than needed, as it will quickly bloat our DB. In case one wants to store messages for debug purposes, we could add one of the following capabilities later: - hexdump to a log file - send to an SMPP entry on delivery - send to Wireshark --- openbsc/src/libmsc/db.c | 13 +++++-------- openbsc/tests/db/db_test.c | 8 ++++++++ 2 files changed, 13 insertions(+), 8 deletions(-) diff --git a/openbsc/src/libmsc/db.c b/openbsc/src/libmsc/db.c index 2af39e5..4e879a0 100644 --- a/openbsc/src/libmsc/db.c +++ b/openbsc/src/libmsc/db.c @@ -115,7 +115,6 @@ static const char *create_stmts[] = { "id INTEGER PRIMARY KEY AUTOINCREMENT, " "created TIMESTAMP NOT NULL, " "received TIMESTAMP, " - "sent TIMESTAMP, " "deliver_attempts INTEGER NOT NULL DEFAULT 0, " /* data directly copied/derived from SMS */ "valid_until TIMESTAMP, " @@ -1409,7 +1408,7 @@ struct gsm_sms *db_sms_get_unsent(struct gsm_network *net, unsigned long long mi "SELECT SMS.* " "FROM SMS JOIN Subscriber ON " "SMS.dest_addr = Subscriber.extension " - "WHERE SMS.id >= %llu AND SMS.sent IS NULL " + "WHERE SMS.id >= %llu " "AND Subscriber.lac > 0 " "ORDER BY SMS.id LIMIT 1", min_id); @@ -1439,7 +1438,7 @@ struct gsm_sms *db_sms_get_unsent_by_subscr(struct gsm_network *net, "SELECT SMS.* " "FROM SMS JOIN Subscriber ON " "SMS.dest_addr = Subscriber.extension " - "WHERE Subscriber.id >= %llu AND SMS.sent IS NULL " + "WHERE Subscriber.id >= %llu " "AND Subscriber.lac > 0 AND SMS.deliver_attempts < %u " "ORDER BY Subscriber.id, SMS.id LIMIT 1", min_subscr_id, failed); @@ -1468,7 +1467,7 @@ struct gsm_sms *db_sms_get_unsent_for_subscr(struct gsm_subscriber *subscr) "SELECT SMS.* " "FROM SMS JOIN Subscriber ON " "SMS.dest_addr = Subscriber.extension " - "WHERE Subscriber.id = %llu AND SMS.sent IS NULL " + "WHERE Subscriber.id = %llu " "AND Subscriber.lac > 0 " "ORDER BY SMS.id LIMIT 1", subscr->id); @@ -1493,11 +1492,9 @@ int db_sms_mark_delivered(struct gsm_sms *sms) dbi_result result; result = dbi_conn_queryf(conn, - "UPDATE SMS " - "SET sent = datetime('now') " - "WHERE id = %llu", sms->id); + "DELETE FROM SMS WHERE SMS.id = %llu", sms->id); if (!result) { - LOGP(DDB, LOGL_ERROR, "Failed to mark SMS %llu as sent.\n", sms->id); + LOGP(DDB, LOGL_ERROR, "Failed to remove SMS %llu from DB.\n", sms->id); return 1; } diff --git a/openbsc/tests/db/db_test.c b/openbsc/tests/db/db_test.c index ea6f2dc..3693cda 100644 --- a/openbsc/tests/db/db_test.c +++ b/openbsc/tests/db/db_test.c @@ -120,6 +120,14 @@ static void test_sms(void) OSMO_ASSERT(sms->user_data_len == strlen("UserData123")); OSMO_ASSERT(strcmp((char *) sms->user_data, "UserData123") == 0); + /* mark SMS as delivered in the DB */ + db_sms_mark_delivered(sms); + sms_free(sms); + + /* now query - we should not get anything */ + sms = db_sms_get_unsent_for_subscr(subscr); + OSMO_ASSERT(!sms); + subscr_put(subscr); } -- 1.7.9.5
