Stefan Sperling has submitted this change and it was merged. (
https://gerrit.osmocom.org/12121 )
Change subject: store a timestamp of the last location update seen from a
subscriber
......................................................................
store a timestamp of the last location update seen from a subscriber
Timestamps are stored in the HLR DB in the new 'last_lu_seen' column
of the 'subscriber' table, in UTC and in granularity of seconds.
At present, osmo-hlr only records these timestamps but otherwise
makes no use of them. Because the timestamps are stored in a
human-readable form, they may already provide value to external
processes which need this information. For example:
sqlite> select imsi,last_lu_seen from subscriber;
901990000000001|2018-12-04 14:17:12
I didn't bother adding additional tests because the code added
with this commit is already being exercised by several calls
to db_subscr_lu() in db_test.c.
This change requires a HLR DB schema update. Existing databases
won't be upgraded automatically. However, osmo-hlr will refuse
to operate with databases which are not upgraded.
Change-Id: Ibeb49d45aec18451a260a6654b8c51b8fc3bec50
Related: OS#2838
---
M sql/hlr.sql
M src/db.c
M src/db.h
M src/db_hlr.c
4 files changed, 108 insertions(+), 5 deletions(-)
Approvals:
Jenkins Builder: Verified
Pau Espin Pedrol: Looks good to me, approved
diff --git a/sql/hlr.sql b/sql/hlr.sql
index 3499109..9ff9867 100644
--- a/sql/hlr.sql
+++ b/sql/hlr.sql
@@ -36,7 +36,11 @@
-- Chapter 2.7.5
ms_purged_cs BOOLEAN NOT NULL DEFAULT 0,
-- Chapter 2.7.6
- ms_purged_ps BOOLEAN NOT NULL DEFAULT 0
+ ms_purged_ps BOOLEAN NOT NULL DEFAULT 0,
+
+ -- Timestamp of last location update seen from subscriber
+ -- The value is a string which encodes a UTC timestamp in granularity
of seconds.
+ last_lu_seen TIMESTAMP default NULL
);
CREATE TABLE subscriber_apn (
@@ -69,4 +73,5 @@
CREATE UNIQUE INDEX idx_subscr_imsi ON subscriber (imsi);
-- Set HLR database schema version number
-PRAGMA user_version = 0;
+-- Note: This constant is currently duplicated in src/db.c and must be kept in
sync!
+PRAGMA user_version = 1;
diff --git a/src/db.c b/src/db.c
index df52f9b..f7cbace 100644
--- a/src/db.c
+++ b/src/db.c
@@ -27,7 +27,8 @@
#include "db.h"
#include "db_bootstrap.h"
-#define CURRENT_SCHEMA_VERSION 0
+/* This constant is currently duplicated in sql/hlr.sql and must be kept in
sync! */
+#define CURRENT_SCHEMA_VERSION 1
#define SEL_COLUMNS \
"id," \
@@ -42,7 +43,8 @@
"nam_ps," \
"lmsi," \
"ms_purged_cs," \
- "ms_purged_ps"
+ "ms_purged_ps," \
+ "last_lu_seen"
static const char *stmt_sql[] = {
[DB_STMT_SEL_BY_IMSI] = "SELECT " SEL_COLUMNS " FROM subscriber WHERE
imsi = ?",
@@ -73,6 +75,7 @@
"INSERT INTO auc_3g (subscriber_id, algo_id_3g, k, op, opc,
ind_bitlen)"
" VALUES($subscriber_id, $algo_id_3g, $k, $op, $opc,
$ind_bitlen)",
[DB_STMT_AUC_3G_DELETE] = "DELETE FROM auc_3g WHERE subscriber_id =
$subscriber_id",
+ [DB_STMT_SET_LAST_LU_SEEN] = "UPDATE subscriber SET last_lu_seen =
datetime($val, 'unixepoch') WHERE id = $subscriber_id",
};
static void sql3_error_log_cb(void *arg, int err_code, const char *msg)
@@ -252,6 +255,41 @@
return true;
}
+static int
+db_upgrade_v1(struct db_context *dbc)
+{
+ sqlite3_stmt *stmt;
+ int rc;
+ const char *update_stmt_sql = "ALTER TABLE subscriber ADD COLUMN
last_lu_seen TIMESTAMP default NULL";
+ const char *set_schema_version_sql = "PRAGMA user_version = 1";
+
+ rc = sqlite3_prepare_v2(dbc->db, update_stmt_sql, -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ LOGP(DDB, LOGL_ERROR, "Unable to prepare SQL statement '%s'\n",
update_stmt_sql);
+ return rc;
+ }
+ rc = sqlite3_step(stmt);
+ db_remove_reset(stmt);
+ sqlite3_finalize(stmt);
+ if (rc != SQLITE_DONE) {
+ LOGP(DDB, LOGL_ERROR, "Unable to update HLR database schema to
version %d\n", 1);
+ return rc;
+ }
+
+ rc = sqlite3_prepare_v2(dbc->db, set_schema_version_sql, -1, &stmt,
NULL);
+ if (rc != SQLITE_OK) {
+ LOGP(DDB, LOGL_ERROR, "Unable to prepare SQL statement '%s'\n",
set_schema_version_sql);
+ return rc;
+ }
+ rc = sqlite3_step(stmt);
+ if (rc != SQLITE_DONE)
+ LOGP(DDB, LOGL_ERROR, "Unable to update HLR database schema to
version %d\n", 1);
+
+ db_remove_reset(stmt);
+ sqlite3_finalize(stmt);
+ return rc;
+}
+
static int db_get_user_version(struct db_context *dbc)
{
const char *user_version_sql = "PRAGMA user_version";
@@ -346,12 +384,28 @@
rc, sqlite3_errmsg(dbc->db));
goto out_free;
}
+ version = CURRENT_SCHEMA_VERSION;
}
LOGP(DDB, LOGL_NOTICE, "Database '%s' has HLR DB schema version %d\n",
dbc->fname, version);
if (version < CURRENT_SCHEMA_VERSION && allow_upgrade) {
- /* Future version upgrades will happen here. */
+ switch (version) {
+ case 0:
+ rc = db_upgrade_v1(dbc);
+ if (rc != SQLITE_DONE) {
+ LOGP(DDB, LOGL_ERROR, "Failed to upgrade HLR DB
schema to version 1: (rc=%d) %s\n",
+ rc, sqlite3_errmsg(dbc->db));
+ goto out_free;
+ }
+ version = 1;
+ /* fall through */
+ /* case N: ... */
+ default:
+ break;
+ }
+ LOGP(DDB, LOGL_NOTICE, "Database '%s' has been upgraded to HLR
DB schema version %d\n",
+ dbc->fname, version);
}
if (version != CURRENT_SCHEMA_VERSION) {
diff --git a/src/db.h b/src/db.h
index 66dfe57..5129b8d 100644
--- a/src/db.h
+++ b/src/db.h
@@ -25,6 +25,7 @@
DB_STMT_AUC_2G_DELETE,
DB_STMT_AUC_3G_INSERT,
DB_STMT_AUC_3G_DELETE,
+ DB_STMT_SET_LAST_LU_SEEN,
_NUM_DB_STMT
};
diff --git a/src/db_hlr.c b/src/db_hlr.c
index 2bccc38..342698e 100644
--- a/src/db_hlr.c
+++ b/src/db_hlr.c
@@ -20,6 +20,7 @@
#include <string.h>
#include <errno.h>
#include <inttypes.h>
+#include <time.h>
#include <osmocom/core/utils.h>
#include <osmocom/crypt/auth.h>
@@ -577,6 +578,7 @@
{
sqlite3_stmt *stmt;
int rc, ret = 0;
+ struct timespec localtime;
stmt = dbc->stmt[is_ps ? DB_STMT_UPD_SGSN_BY_ID
: DB_STMT_UPD_VLR_BY_ID];
@@ -603,13 +605,54 @@
": no such subscriber\n",
is_ps? "SGSN" : "VLR", subscr_id);
ret = -ENOENT;
+ goto out;
} else if (rc != 1) {
LOGP(DAUC, LOGL_ERROR, "Update %s number for subscriber
ID=%"PRId64
": SQL modified %d rows (expected 1)\n",
is_ps? "SGSN" : "VLR", subscr_id, rc);
ret = -EIO;
+ goto out;
}
+ db_remove_reset(stmt);
+
+ if (osmo_clock_gettime(CLOCK_REALTIME, &localtime) != 0) {
+ LOGP(DAUC, LOGL_ERROR, "Cannot get the current time: (%d)
%s\n", errno, strerror(errno));
+ ret = -errno;
+ goto out;
+ }
+
+ stmt = dbc->stmt[DB_STMT_SET_LAST_LU_SEEN];
+
+ if (!db_bind_int64(stmt, "$subscriber_id", subscr_id))
+ return -EIO;
+ /* The timestamp will be converted to UTC by SQLite. */
+ if (!db_bind_int64(stmt, "$val", (int64_t)localtime.tv_sec)) {
+ ret = -EIO;
+ goto out;
+ }
+
+ rc = sqlite3_step(stmt);
+ if (rc != SQLITE_DONE) {
+ LOGP(DAUC, LOGL_ERROR,
+ "Cannot update LU timestamp for subscriber ID=%"PRId64":
SQL error: (%d) %s\n",
+ subscr_id, rc, sqlite3_errmsg(dbc->db));
+ ret = -EIO;
+ goto out;
+ }
+
+ /* verify execution result */
+ rc = sqlite3_changes(dbc->db);
+ if (!rc) {
+ LOGP(DAUC, LOGL_ERROR, "Cannot update LU timestamp for
subscriber ID=%"PRId64
+ ": no such subscriber\n", subscr_id);
+ ret = -ENOENT;
+ goto out;
+ } else if (rc != 1) {
+ LOGP(DAUC, LOGL_ERROR, "Update LU timestamp for subscriber
ID=%"PRId64
+ ": SQL modified %d rows (expected 1)\n", subscr_id, rc);
+ ret = -EIO;
+ }
out:
db_remove_reset(stmt);
return ret;
--
To view, visit https://gerrit.osmocom.org/12121
To unsubscribe, or for help writing mail filters, visit
https://gerrit.osmocom.org/settings
Gerrit-Project: osmo-hlr
Gerrit-Branch: master
Gerrit-MessageType: merged
Gerrit-Change-Id: Ibeb49d45aec18451a260a6654b8c51b8fc3bec50
Gerrit-Change-Number: 12121
Gerrit-PatchSet: 6
Gerrit-Owner: Stefan Sperling <[email protected]>
Gerrit-Reviewer: Jenkins Builder (1000002)
Gerrit-Reviewer: Pau Espin Pedrol <[email protected]>
Gerrit-Reviewer: Stefan Sperling <[email protected]>
Gerrit-CC: Neels Hofmeyr <[email protected]>
