<p>Stefan Sperling has uploaded this change for <strong>review</strong>.</p><p><a href="https://gerrit.osmocom.org/12121">View Change</a></p><pre style="font-family: monospace,monospace; white-space: pre-wrap;">store timestamp of last location update seen from subscriber<br><br>Store a timestamp of the last location update seen from a subscriber<br>in the HLR DB. Timestamps are stored in a new 'last_lu_seen' column<br>in the 'subscriber' table, in granularity of seconds.<br><br>At present, osmo-hlr only records these timestamps but otherwise<br>makes no use of them. Because the timestamps are stored in a<br>human-readable formt, they may already provide value to external<br>processes which need this information. For example:<br><br> sqlite> select imsi,last_lu_seen from subscriber;<br> 901990000000001|2018-12-04 14:17:12<br><br>I didn't bother adding additional tests because the code added<br>with this commit is already being exercised by several calls<br>to db_subscr_lu() in db_test.c.<br><br>This change requires a HLR DB schema update. Existing databases<br>won't be upgraded automatically. However, osmo-hlr will refuse<br>to operate with databases which are not upgraded.<br><br>Change-Id: Ibeb49d45aec18451a260a6654b8c51b8fc3bec50<br>Related: OS#2838<br>---<br>M sql/hlr.sql<br>M src/db.c<br>M src/db.h<br>M src/db_hlr.c<br>4 files changed, 104 insertions(+), 5 deletions(-)<br><br></pre><pre style="font-family: monospace,monospace; white-space: pre-wrap;">git pull ssh://gerrit.osmocom.org:29418/osmo-hlr refs/changes/21/12121/1</pre><pre style="font-family: monospace,monospace; white-space: pre-wrap;"><span>diff --git a/sql/hlr.sql b/sql/hlr.sql</span><br><span>index 3499109..d1db256 100644</span><br><span>--- a/sql/hlr.sql</span><br><span>+++ b/sql/hlr.sql</span><br><span>@@ -36,7 +36,10 @@</span><br><span> -- Chapter 2.7.5</span><br><span> ms_purged_cs BOOLEAN NOT NULL DEFAULT 0,</span><br><span> -- Chapter 2.7.6</span><br><span style="color: hsl(0, 100%, 40%);">- ms_purged_ps BOOLEAN NOT NULL DEFAULT 0</span><br><span style="color: hsl(120, 100%, 40%);">+ ms_purged_ps BOOLEAN NOT NULL DEFAULT 0,</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ -- Timestamp of last location update seen from subscriber</span><br><span style="color: hsl(120, 100%, 40%);">+ last_lu_seen TIMESTAMP default NULL</span><br><span> );</span><br><span> </span><br><span> CREATE TABLE subscriber_apn (</span><br><span>@@ -69,4 +72,4 @@</span><br><span> CREATE UNIQUE INDEX idx_subscr_imsi ON subscriber (imsi);</span><br><span> </span><br><span> -- Set HLR database schema version number</span><br><span style="color: hsl(0, 100%, 40%);">-PRAGMA user_version = 0;</span><br><span style="color: hsl(120, 100%, 40%);">+PRAGMA user_version = 1;</span><br><span>diff --git a/src/db.c b/src/db.c</span><br><span>index df52f9b..70b5a26 100644</span><br><span>--- a/src/db.c</span><br><span>+++ b/src/db.c</span><br><span>@@ -27,7 +27,7 @@</span><br><span> #include "db.h"</span><br><span> #include "db_bootstrap.h"</span><br><span> </span><br><span style="color: hsl(0, 100%, 40%);">-#define CURRENT_SCHEMA_VERSION 0</span><br><span style="color: hsl(120, 100%, 40%);">+#define CURRENT_SCHEMA_VERSION 1</span><br><span> </span><br><span> #define SEL_COLUMNS \</span><br><span> "id," \</span><br><span>@@ -42,7 +42,8 @@</span><br><span> "nam_ps," \</span><br><span> "lmsi," \</span><br><span> "ms_purged_cs," \</span><br><span style="color: hsl(0, 100%, 40%);">- "ms_purged_ps"</span><br><span style="color: hsl(120, 100%, 40%);">+ "ms_purged_ps," \</span><br><span style="color: hsl(120, 100%, 40%);">+ "last_lu_seen"</span><br><span> </span><br><span> static const char *stmt_sql[] = {</span><br><span> [DB_STMT_SEL_BY_IMSI] = "SELECT " SEL_COLUMNS " FROM subscriber WHERE imsi = ?",</span><br><span>@@ -73,6 +74,7 @@</span><br><span> "INSERT INTO auc_3g (subscriber_id, algo_id_3g, k, op, opc, ind_bitlen)"</span><br><span> " VALUES($subscriber_id, $algo_id_3g, $k, $op, $opc, $ind_bitlen)",</span><br><span> [DB_STMT_AUC_3G_DELETE] = "DELETE FROM auc_3g WHERE subscriber_id = $subscriber_id",</span><br><span style="color: hsl(120, 100%, 40%);">+ [DB_STMT_SET_LAST_LU_SEEN] = "UPDATE subscriber SET last_lu_seen = datetime($val, 'unixepoch') WHERE id = $subscriber_id",</span><br><span> };</span><br><span> </span><br><span> static void sql3_error_log_cb(void *arg, int err_code, const char *msg)</span><br><span>@@ -252,6 +254,41 @@</span><br><span> return true;</span><br><span> }</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+static int</span><br><span style="color: hsl(120, 100%, 40%);">+db_upgrade_v1(struct db_context *dbc)</span><br><span style="color: hsl(120, 100%, 40%);">+{</span><br><span style="color: hsl(120, 100%, 40%);">+ sqlite3_stmt *stmt;</span><br><span style="color: hsl(120, 100%, 40%);">+ int rc;</span><br><span style="color: hsl(120, 100%, 40%);">+ const char *update_stmt_sql = "ALTER TABLE subscriber ADD COLUMN last_lu_seen TIMESTAMP default NULL";</span><br><span style="color: hsl(120, 100%, 40%);">+ const char *set_schema_version_sql = "PRAGMA user_version = 1";</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_prepare_v2(dbc->db, update_stmt_sql, -1, &stmt, NULL);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (rc != SQLITE_OK) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DDB, LOGL_ERROR, "Unable to prepare SQL statement '%s'\n", update_stmt_sql);</span><br><span style="color: hsl(120, 100%, 40%);">+ return rc;</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_step(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ db_remove_reset(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ sqlite3_finalize(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (rc != SQLITE_DONE) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DDB, LOGL_ERROR, "Unable to update HLR database schema to version %d\n", 1);</span><br><span style="color: hsl(120, 100%, 40%);">+ return rc;</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_prepare_v2(dbc->db, set_schema_version_sql, -1, &stmt, NULL);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (rc != SQLITE_OK) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DDB, LOGL_ERROR, "Unable to prepare SQL statement '%s'\n", set_schema_version_sql);</span><br><span style="color: hsl(120, 100%, 40%);">+ return rc;</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_step(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (rc != SQLITE_DONE)</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DDB, LOGL_ERROR, "Unable to update HLR database schema to version %d\n", 1);</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ db_remove_reset(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ sqlite3_finalize(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ return rc;</span><br><span style="color: hsl(120, 100%, 40%);">+}</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span> static int db_get_user_version(struct db_context *dbc)</span><br><span> {</span><br><span> const char *user_version_sql = "PRAGMA user_version";</span><br><span>@@ -346,12 +383,28 @@</span><br><span> rc, sqlite3_errmsg(dbc->db));</span><br><span> goto out_free;</span><br><span> }</span><br><span style="color: hsl(120, 100%, 40%);">+ version = CURRENT_SCHEMA_VERSION;</span><br><span> }</span><br><span> </span><br><span> LOGP(DDB, LOGL_NOTICE, "Database '%s' has HLR DB schema version %d\n", dbc->fname, version);</span><br><span> </span><br><span> if (version < CURRENT_SCHEMA_VERSION && allow_upgrade) {</span><br><span style="color: hsl(0, 100%, 40%);">- /* Future version upgrades will happen here. */</span><br><span style="color: hsl(120, 100%, 40%);">+ switch (version) {</span><br><span style="color: hsl(120, 100%, 40%);">+ case 0:</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = db_upgrade_v1(dbc);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (rc != SQLITE_DONE) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DDB, LOGL_ERROR, "Failed to upgrade HLR DB schema to version 1: (rc=%d) %s\n",</span><br><span style="color: hsl(120, 100%, 40%);">+ rc, sqlite3_errmsg(dbc->db));</span><br><span style="color: hsl(120, 100%, 40%);">+ goto out_free;</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span style="color: hsl(120, 100%, 40%);">+ version = 1;</span><br><span style="color: hsl(120, 100%, 40%);">+ /* fall through */</span><br><span style="color: hsl(120, 100%, 40%);">+ /* case N: ... */</span><br><span style="color: hsl(120, 100%, 40%);">+ default:</span><br><span style="color: hsl(120, 100%, 40%);">+ break;</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DDB, LOGL_NOTICE, "Database '%s' has been upgraded to HLR DB schema version %d\n",</span><br><span style="color: hsl(120, 100%, 40%);">+ dbc->fname, version);</span><br><span> }</span><br><span> </span><br><span> if (version != CURRENT_SCHEMA_VERSION) {</span><br><span>diff --git a/src/db.h b/src/db.h</span><br><span>index 66dfe57..5129b8d 100644</span><br><span>--- a/src/db.h</span><br><span>+++ b/src/db.h</span><br><span>@@ -25,6 +25,7 @@</span><br><span> DB_STMT_AUC_2G_DELETE,</span><br><span> DB_STMT_AUC_3G_INSERT,</span><br><span> DB_STMT_AUC_3G_DELETE,</span><br><span style="color: hsl(120, 100%, 40%);">+ DB_STMT_SET_LAST_LU_SEEN,</span><br><span> _NUM_DB_STMT</span><br><span> };</span><br><span> </span><br><span>diff --git a/src/db_hlr.c b/src/db_hlr.c</span><br><span>index 2bccc38..81897a8 100644</span><br><span>--- a/src/db_hlr.c</span><br><span>+++ b/src/db_hlr.c</span><br><span>@@ -20,6 +20,7 @@</span><br><span> #include <string.h></span><br><span> #include <errno.h></span><br><span> #include <inttypes.h></span><br><span style="color: hsl(120, 100%, 40%);">+#include <time.h></span><br><span> </span><br><span> #include <osmocom/core/utils.h></span><br><span> #include <osmocom/crypt/auth.h></span><br><span>@@ -577,6 +578,7 @@</span><br><span> {</span><br><span> sqlite3_stmt *stmt;</span><br><span> int rc, ret = 0;</span><br><span style="color: hsl(120, 100%, 40%);">+ struct timespec t;</span><br><span> </span><br><span> stmt = dbc->stmt[is_ps ? DB_STMT_UPD_SGSN_BY_ID</span><br><span> : DB_STMT_UPD_VLR_BY_ID];</span><br><span>@@ -603,13 +605,53 @@</span><br><span> ": no such subscriber\n",</span><br><span> is_ps? "SGSN" : "VLR", subscr_id);</span><br><span> ret = -ENOENT;</span><br><span style="color: hsl(120, 100%, 40%);">+ goto out;</span><br><span> } else if (rc != 1) {</span><br><span> LOGP(DAUC, LOGL_ERROR, "Update %s number for subscriber ID=%"PRId64</span><br><span> ": SQL modified %d rows (expected 1)\n",</span><br><span> is_ps? "SGSN" : "VLR", subscr_id, rc);</span><br><span> ret = -EIO;</span><br><span style="color: hsl(120, 100%, 40%);">+ goto out;</span><br><span> }</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+ db_remove_reset(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ if (clock_gettime(CLOCK_MONOTONIC, &t) != 0) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DAUC, LOGL_ERROR, "Cannot get the current time: (%d) %s\n", errno, strerror(errno));</span><br><span style="color: hsl(120, 100%, 40%);">+ ret = -errno;</span><br><span style="color: hsl(120, 100%, 40%);">+ goto out;</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ stmt = dbc->stmt[DB_STMT_SET_LAST_LU_SEEN];</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ if (!db_bind_int64(stmt, "$subscriber_id", subscr_id))</span><br><span style="color: hsl(120, 100%, 40%);">+ return -EIO;</span><br><span style="color: hsl(120, 100%, 40%);">+ if (!db_bind_int64(stmt, "$val", (int64_t)t.tv_sec)) {</span><br><span style="color: hsl(120, 100%, 40%);">+ ret = -EIO;</span><br><span style="color: hsl(120, 100%, 40%);">+ goto out;</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_step(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (rc != SQLITE_DONE) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DAUC, LOGL_ERROR,</span><br><span style="color: hsl(120, 100%, 40%);">+ "Cannot update LU timestamp for subscriber ID=%"PRId64": SQL error: (%d) %s\n",</span><br><span style="color: hsl(120, 100%, 40%);">+ subscr_id, rc, sqlite3_errmsg(dbc->db));</span><br><span style="color: hsl(120, 100%, 40%);">+ ret = -EIO;</span><br><span style="color: hsl(120, 100%, 40%);">+ goto out;</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ /* verify execution result */</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_changes(dbc->db);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (!rc) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DAUC, LOGL_ERROR, "Cannot update LU timestamp for subscriber ID=%"PRId64</span><br><span style="color: hsl(120, 100%, 40%);">+ ": no such subscriber\n", subscr_id);</span><br><span style="color: hsl(120, 100%, 40%);">+ ret = -ENOENT;</span><br><span style="color: hsl(120, 100%, 40%);">+ goto out;</span><br><span style="color: hsl(120, 100%, 40%);">+ } else if (rc != 1) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DAUC, LOGL_ERROR, "Update LU timestamp for subscriber ID=%"PRId64</span><br><span style="color: hsl(120, 100%, 40%);">+ ": SQL modified %d rows (expected 1)\n", subscr_id, rc);</span><br><span style="color: hsl(120, 100%, 40%);">+ ret = -EIO;</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span> out:</span><br><span> db_remove_reset(stmt);</span><br><span> return ret;</span><br><span></span><br></pre><p>To view, visit <a href="https://gerrit.osmocom.org/12121">change 12121</a>. To unsubscribe, or for help writing mail filters, visit <a href="https://gerrit.osmocom.org/settings">settings</a>.</p><div itemscope itemtype="http://schema.org/EmailMessage"><div itemscope itemprop="action" itemtype="http://schema.org/ViewAction"><link itemprop="url" href="https://gerrit.osmocom.org/12121"/><meta itemprop="name" content="View Change"/></div></div>
<div style="display:none"> Gerrit-Project: osmo-hlr </div>
<div style="display:none"> Gerrit-Branch: master </div>
<div style="display:none"> Gerrit-MessageType: newchange </div>
<div style="display:none"> Gerrit-Change-Id: Ibeb49d45aec18451a260a6654b8c51b8fc3bec50 </div>
<div style="display:none"> Gerrit-Change-Number: 12121 </div>
<div style="display:none"> Gerrit-PatchSet: 1 </div>
<div style="display:none"> Gerrit-Owner: Stefan Sperling <stsp@stsp.name> </div>