Add a VTY command which deletes all expired SMS.

We already delete SMS which have been sent successfully. However, there
are plans to accept SMS for any subscriber in order to fix the problem
described in https://osmocom.org/issues/2354 ("SMSC: Store&Forward not
working for subscribed but unregistered MS").

This means we may end up storing SMS which never get sent, e.g. because
the B subscriber doesn't actually exist. This could lead to a higher
degree of SMS database growth over time, and therefore we need a way
to keep database size under control.

As a first step, introduce a DB function which removes an expired SMS,
and add a VTY command which removes all expired SMS from the DB.

Later commits will build upon this to remove expired SMS automatically.

The SMS expiry time period is currently hard-coded to 2 weeks.
We could make this configurable in the future if desired.

Change-Id: Icd6093b7b5d8db84b19a0aa47c68182566113ee2
Related: OS#2354

1 files changed, 37 insertions, 0 deletions

diff --git a/src/libmsc/db.c b/src/libmsc/db.c
index b48d137c5..c21aa7026 100644
--- a/src/libmsc/db.c
+++ b/src/libmsc/db.c
@@ -989,6 +989,43 @@ int db_sms_delete_sent_message_by_id(unsigned long long sms_id)
 	return 0;
 }
 
+int db_sms_delete_expired_message_by_id(unsigned long long sms_id)
+{
+	dbi_result result;
+	time_t created, validity_timestamp, now, min_created;
+
+	result = dbi_conn_queryf(conn, "SELECT created,valid_until FROM SMS WHERE id = %llu", sms_id);
+	if (!result)
+		return -1;
+	if (!next_row(result)) {
+		dbi_result_free(result);
+		return -1;
+	}
+
+	created = dbi_result_get_datetime(result, "created");
+	validity_timestamp = dbi_result_get_datetime(result, "valid_until");
+	dbi_result_free(result);
+
+	now = time(NULL);
+	if (validity_timestamp > now)
+		return -1;
+
+	/* Our SMS expiry threshold is hard-coded to roughly 2 weeks at the moment. */
+	min_created = now - (time_t)(60 * 60 * 24 * 7 * 2);
+	if (min_created < 0) /* bogus system clock? */
+		return -1;
+	if (created >= min_created) /* not yet expired */
+		return -1;
+
+	result = dbi_conn_queryf(conn, "DELETE FROM SMS WHERE id = %llu", sms_id);
+	if (!result) {
+		LOGP(DDB, LOGL_ERROR, "Failed to delete SMS %llu.\n", sms_id);
+		return -1;
+	}
+	dbi_result_free(result);
+	return 0;
+}
+
 int db_store_counter(struct osmo_counter *ctr)
 {
 	dbi_result result;