diff options
author | Stefan Sperling <ssperling@sysmocom.de> | 2018-12-04 15:07:29 +0100 |
---|---|---|
committer | Stefan Sperling <ssperling@sysmocom.de> | 2018-12-07 11:50:06 +0100 |
commit | 638ba8cc049b35c1a6a604fb058a92cff68198e1 (patch) | |
tree | b0df5bba90af21e8699173858b9aeefff47268d1 | |
parent | 55f5efa5681fe7456d0c7adbee324dbeff2a283e (diff) |
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
-rw-r--r-- | sql/hlr.sql | 9 | ||||
-rw-r--r-- | src/db.c | 60 | ||||
-rw-r--r-- | src/db.h | 1 | ||||
-rw-r--r-- | src/db_hlr.c | 43 |
4 files changed, 108 insertions, 5 deletions
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 @@ CREATE TABLE subscriber ( -- 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 TABLE auc_3g ( 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; @@ -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 @@ static const char *stmt_sql[] = { "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 @@ static bool db_is_bootstrapped_v0(struct db_context *dbc) 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 @@ struct db_context *db_open(void *ctx, const char *fname, bool enable_sqlite_logg 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) { @@ -25,6 +25,7 @@ enum stmt_idx { 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 @@ int db_subscr_lu(struct db_context *dbc, int64_t subscr_id, { 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 @@ int db_subscr_lu(struct db_context *dbc, int64_t subscr_id, ": 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; |