<p>osmith <strong>submitted</strong> this change.</p><p><a href="https://gerrit.osmocom.org/c/osmo-hlr/+/16201">View Change</a></p><div style="white-space:pre-wrap">Approvals:
pespin: Looks good to me, but someone else must approve
osmith: Looks good to me, approved
Jenkins Builder: Verified
</div><pre style="font-family: monospace,monospace; white-space: pre-wrap;">db v4: add column last_lu_seen_ps<br><br>Location Updating procedures from both CS and PS overwrite the same<br>last_lu_seen field of a subscriber. For upcoming D-GSM it will be important to<br>distinguish those, because only CS attaches qualify for MSISDN lookup.<br><br>Add column last_lu_seen_ps, and upon PS LU, do not overwrite last_lu_seen, so<br>that last_lu_seen now only reflects CS LU.<br><br>In the VTY, dump both LU dates distinctively.<br><br>Change-Id: Id7fc50567211a0870ac0524f6dee94d4513781ba<br>---<br>M include/osmocom/hlr/db.h<br>M sql/hlr.sql<br>M src/db.c<br>M src/db_hlr.c<br>M src/hlr_vty_subscr.c<br>M tests/db_upgrade/db_upgrade_test.ok<br>6 files changed, 76 insertions(+), 32 deletions(-)<br><br></pre><pre style="font-family: monospace,monospace; white-space: pre-wrap;"><span>diff --git a/include/osmocom/hlr/db.h b/include/osmocom/hlr/db.h</span><br><span>index 15d83de..eacc78e 100644</span><br><span>--- a/include/osmocom/hlr/db.h</span><br><span>+++ b/include/osmocom/hlr/db.h</span><br><span>@@ -28,6 +28,7 @@</span><br><span> DB_STMT_AUC_3G_INSERT,</span><br><span> DB_STMT_AUC_3G_DELETE,</span><br><span> DB_STMT_SET_LAST_LU_SEEN,</span><br><span style="color: hsl(120, 100%, 40%);">+ DB_STMT_SET_LAST_LU_SEEN_PS,</span><br><span> DB_STMT_EXISTS_BY_IMSI,</span><br><span> DB_STMT_EXISTS_BY_MSISDN,</span><br><span> _NUM_DB_STMT</span><br><span>@@ -95,6 +96,7 @@</span><br><span> bool ms_purged_cs;</span><br><span> bool ms_purged_ps;</span><br><span> time_t last_lu_seen;</span><br><span style="color: hsl(120, 100%, 40%);">+ time_t last_lu_seen_ps;</span><br><span> };</span><br><span> </span><br><span> /* A format string for use with strptime(3). This format string is</span><br><span>diff --git a/sql/hlr.sql b/sql/hlr.sql</span><br><span>index f8fdc0b..ad57661 100644</span><br><span>--- a/sql/hlr.sql</span><br><span>+++ b/sql/hlr.sql</span><br><span>@@ -42,7 +42,8 @@</span><br><span> </span><br><span> -- Timestamp of last location update seen from subscriber</span><br><span> -- The value is a string which encodes a UTC timestamp in granularity of seconds.</span><br><span style="color: hsl(0, 100%, 40%);">- last_lu_seen TIMESTAMP default NULL</span><br><span style="color: hsl(120, 100%, 40%);">+ last_lu_seen TIMESTAMP default NULL,</span><br><span style="color: hsl(120, 100%, 40%);">+ last_lu_seen_ps TIMESTAMP default NULL</span><br><span> );</span><br><span> </span><br><span> CREATE TABLE subscriber_apn (</span><br><span>@@ -77,4 +78,4 @@</span><br><span> </span><br><span> -- Set HLR database schema version number</span><br><span> -- Note: This constant is currently duplicated in src/db.c and must be kept in sync!</span><br><span style="color: hsl(0, 100%, 40%);">-PRAGMA user_version = 3;</span><br><span style="color: hsl(120, 100%, 40%);">+PRAGMA user_version = 4;</span><br><span>diff --git a/src/db.c b/src/db.c</span><br><span>index 992dbad..5e5ad35 100644</span><br><span>--- a/src/db.c</span><br><span>+++ b/src/db.c</span><br><span>@@ -28,7 +28,7 @@</span><br><span> #include "db_bootstrap.h"</span><br><span> </span><br><span> /* This constant is currently duplicated in sql/hlr.sql and must be kept in sync! */</span><br><span style="color: hsl(0, 100%, 40%);">-#define CURRENT_SCHEMA_VERSION 3</span><br><span style="color: hsl(120, 100%, 40%);">+#define CURRENT_SCHEMA_VERSION 4</span><br><span> </span><br><span> #define SEL_COLUMNS \</span><br><span> "id," \</span><br><span>@@ -45,7 +45,8 @@</span><br><span> "lmsi," \</span><br><span> "ms_purged_cs," \</span><br><span> "ms_purged_ps," \</span><br><span style="color: hsl(0, 100%, 40%);">- "last_lu_seen"</span><br><span style="color: hsl(120, 100%, 40%);">+ "last_lu_seen," \</span><br><span style="color: hsl(120, 100%, 40%);">+ "last_lu_seen_ps" \</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>@@ -79,6 +80,7 @@</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> [DB_STMT_SET_LAST_LU_SEEN] = "UPDATE subscriber SET last_lu_seen = datetime($val, 'unixepoch') WHERE id = $subscriber_id",</span><br><span style="color: hsl(120, 100%, 40%);">+ [DB_STMT_SET_LAST_LU_SEEN_PS] = "UPDATE subscriber SET last_lu_seen_ps = datetime($val, 'unixepoch') WHERE id = $subscriber_id",</span><br><span> [DB_STMT_EXISTS_BY_IMSI] = "SELECT 1 FROM subscriber WHERE imsi = $imsi",</span><br><span> [DB_STMT_EXISTS_BY_MSISDN] = "SELECT 1 FROM subscriber WHERE msisdn = $msisdn",</span><br><span> };</span><br><span>@@ -423,11 +425,28 @@</span><br><span> return rc;</span><br><span> }</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+static int db_upgrade_v4(struct db_context *dbc)</span><br><span style="color: hsl(120, 100%, 40%);">+{</span><br><span style="color: hsl(120, 100%, 40%);">+ int rc;</span><br><span style="color: hsl(120, 100%, 40%);">+ const char *statements[] = {</span><br><span style="color: hsl(120, 100%, 40%);">+ "ALTER TABLE subscriber ADD COLUMN last_lu_seen_ps TIMESTAMP default NULL",</span><br><span style="color: hsl(120, 100%, 40%);">+ "PRAGMA user_version = 4",</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 = db_run_statements(dbc, statements, ARRAY_SIZE(statements));</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 4\n");</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%);">+ return rc;</span><br><span style="color: hsl(120, 100%, 40%);">+}</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span> typedef int (*db_upgrade_func_t)(struct db_context *dbc);</span><br><span> static db_upgrade_func_t db_upgrade_path[] = {</span><br><span> db_upgrade_v1,</span><br><span> db_upgrade_v2,</span><br><span> db_upgrade_v3,</span><br><span style="color: hsl(120, 100%, 40%);">+ db_upgrade_v4,</span><br><span> };</span><br><span> </span><br><span> static int db_get_user_version(struct db_context *dbc)</span><br><span>diff --git a/src/db_hlr.c b/src/db_hlr.c</span><br><span>index e52b5ed..b3e3887 100644</span><br><span>--- a/src/db_hlr.c</span><br><span>+++ b/src/db_hlr.c</span><br><span>@@ -438,14 +438,36 @@</span><br><span> return ret;</span><br><span> }</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+static void parse_last_lu_seen(time_t *dst, const char *last_lu_seen_str, const char *imsi, const char *label)</span><br><span style="color: hsl(120, 100%, 40%);">+{</span><br><span style="color: hsl(120, 100%, 40%);">+ struct tm tm = {0};</span><br><span style="color: hsl(120, 100%, 40%);">+ time_t val;</span><br><span style="color: hsl(120, 100%, 40%);">+ if (!last_lu_seen_str || last_lu_seen_str[0] == '\0')</span><br><span style="color: hsl(120, 100%, 40%);">+ return;</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ if (strptime(last_lu_seen_str, DB_LAST_LU_SEEN_FMT, &tm) == NULL) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DAUC, LOGL_ERROR, "IMSI-%s: Last LU Seen %s: Cannot parse timestamp '%s'\n",</span><br><span style="color: hsl(120, 100%, 40%);">+ imsi, label, last_lu_seen_str);</span><br><span style="color: hsl(120, 100%, 40%);">+ return;</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%);">+ errno = 0;</span><br><span style="color: hsl(120, 100%, 40%);">+ val = mktime(&tm);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (val == -1) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DAUC, LOGL_ERROR, "IMSI-%s: Last LU Seen %s: Cannot convert timestamp '%s' to time_t: %s\n",</span><br><span style="color: hsl(120, 100%, 40%);">+ imsi, label, last_lu_seen_str, strerror(errno));</span><br><span style="color: hsl(120, 100%, 40%);">+ val = 0;</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%);">+ *dst = val;</span><br><span style="color: hsl(120, 100%, 40%);">+}</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span> /* Common code for db_subscr_get_by_*() functions. */</span><br><span> static int db_sel(struct db_context *dbc, sqlite3_stmt *stmt, struct hlr_subscriber *subscr,</span><br><span> const char **err)</span><br><span> {</span><br><span> int rc;</span><br><span> int ret = 0;</span><br><span style="color: hsl(0, 100%, 40%);">- const char *last_lu_seen_str;</span><br><span style="color: hsl(0, 100%, 40%);">- struct tm tm = {0};</span><br><span> </span><br><span> /* execute the statement */</span><br><span> rc = sqlite3_step(stmt);</span><br><span>@@ -479,20 +501,10 @@</span><br><span> subscr->lmsi = sqlite3_column_int(stmt, 11);</span><br><span> subscr->ms_purged_cs = sqlite3_column_int(stmt, 12);</span><br><span> subscr->ms_purged_ps = sqlite3_column_int(stmt, 13);</span><br><span style="color: hsl(0, 100%, 40%);">- last_lu_seen_str = (const char *)sqlite3_column_text(stmt, 14);</span><br><span style="color: hsl(0, 100%, 40%);">- if (last_lu_seen_str && last_lu_seen_str[0] != '\0') {</span><br><span style="color: hsl(0, 100%, 40%);">- if (strptime(last_lu_seen_str, DB_LAST_LU_SEEN_FMT, &tm) == NULL) {</span><br><span style="color: hsl(0, 100%, 40%);">- LOGP(DAUC, LOGL_ERROR, "Cannot parse last LU timestamp '%s' of subscriber with IMSI='%s': %s\n",</span><br><span style="color: hsl(0, 100%, 40%);">- last_lu_seen_str, subscr->imsi, strerror(errno));</span><br><span style="color: hsl(0, 100%, 40%);">- } else {</span><br><span style="color: hsl(0, 100%, 40%);">- subscr->last_lu_seen = mktime(&tm);</span><br><span style="color: hsl(0, 100%, 40%);">- if (subscr->last_lu_seen == -1) {</span><br><span style="color: hsl(0, 100%, 40%);">- LOGP(DAUC, LOGL_ERROR, "Cannot convert LU timestamp '%s' to time_t: %s\n",</span><br><span style="color: hsl(0, 100%, 40%);">- last_lu_seen_str, strerror(errno));</span><br><span style="color: hsl(0, 100%, 40%);">- subscr->last_lu_seen = 0;</span><br><span style="color: hsl(0, 100%, 40%);">- }</span><br><span style="color: hsl(0, 100%, 40%);">- }</span><br><span style="color: hsl(0, 100%, 40%);">- }</span><br><span style="color: hsl(120, 100%, 40%);">+ parse_last_lu_seen(&subscr->last_lu_seen, (const char *)sqlite3_column_text(stmt, 14),</span><br><span style="color: hsl(120, 100%, 40%);">+ subscr->imsi, "CS");</span><br><span style="color: hsl(120, 100%, 40%);">+ parse_last_lu_seen(&subscr->last_lu_seen_ps, (const char *)sqlite3_column_text(stmt, 15),</span><br><span style="color: hsl(120, 100%, 40%);">+ subscr->imsi, "PS");</span><br><span> </span><br><span> out:</span><br><span> db_remove_reset(stmt);</span><br><span>@@ -770,7 +782,7 @@</span><br><span> goto out;</span><br><span> }</span><br><span> </span><br><span style="color: hsl(0, 100%, 40%);">- stmt = dbc->stmt[DB_STMT_SET_LAST_LU_SEEN];</span><br><span style="color: hsl(120, 100%, 40%);">+ stmt = dbc->stmt[is_ps? DB_STMT_SET_LAST_LU_SEEN_PS : DB_STMT_SET_LAST_LU_SEEN];</span><br><span> </span><br><span> if (!db_bind_int64(stmt, "$subscriber_id", subscr_id))</span><br><span> return -EIO;</span><br><span>diff --git a/src/hlr_vty_subscr.c b/src/hlr_vty_subscr.c</span><br><span>index d2c4c81..b561636 100644</span><br><span>--- a/src/hlr_vty_subscr.c</span><br><span>+++ b/src/hlr_vty_subscr.c</span><br><span>@@ -47,12 +47,20 @@</span><br><span> return s;</span><br><span> }</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+static void dump_last_lu_seen(struct vty *vty, const char *domain_label, time_t last_lu_seen)</span><br><span style="color: hsl(120, 100%, 40%);">+{</span><br><span style="color: hsl(120, 100%, 40%);">+ char datebuf[26]; /* for ctime_r(3) */</span><br><span style="color: hsl(120, 100%, 40%);">+ if (!last_lu_seen)</span><br><span style="color: hsl(120, 100%, 40%);">+ return;</span><br><span style="color: hsl(120, 100%, 40%);">+ vty_out(vty, " last LU seen on %s: %s UTC%s", domain_label, get_datestr(&last_lu_seen, datebuf),</span><br><span style="color: hsl(120, 100%, 40%);">+ VTY_NEWLINE);</span><br><span style="color: hsl(120, 100%, 40%);">+}</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span> static void subscr_dump_full_vty(struct vty *vty, struct hlr_subscriber *subscr)</span><br><span> {</span><br><span> int rc;</span><br><span> struct osmo_sub_auth_data aud2g;</span><br><span> struct osmo_sub_auth_data aud3g;</span><br><span style="color: hsl(0, 100%, 40%);">- char datebuf[26]; /* for ctime_r(3) */</span><br><span> </span><br><span> vty_out(vty, " ID: %"PRIu64"%s", subscr->id, VTY_NEWLINE);</span><br><span> </span><br><span>@@ -87,8 +95,8 @@</span><br><span> vty_out(vty, " PS disabled%s", VTY_NEWLINE);</span><br><span> if (subscr->ms_purged_ps)</span><br><span> vty_out(vty, " PS purged%s", VTY_NEWLINE);</span><br><span style="color: hsl(0, 100%, 40%);">- if (subscr->last_lu_seen)</span><br><span style="color: hsl(0, 100%, 40%);">- vty_out(vty, " last LU seen: %s UTC%s", get_datestr(&subscr->last_lu_seen, datebuf), VTY_NEWLINE);</span><br><span style="color: hsl(120, 100%, 40%);">+ dump_last_lu_seen(vty, "CS", subscr->last_lu_seen);</span><br><span style="color: hsl(120, 100%, 40%);">+ dump_last_lu_seen(vty, "PS", subscr->last_lu_seen_ps);</span><br><span> </span><br><span> if (!*subscr->imsi)</span><br><span> return;</span><br><span>diff --git a/tests/db_upgrade/db_upgrade_test.ok b/tests/db_upgrade/db_upgrade_test.ok</span><br><span>index 49e7151..67d0006 100644</span><br><span>--- a/tests/db_upgrade/db_upgrade_test.ok</span><br><span>+++ b/tests/db_upgrade/db_upgrade_test.ok</span><br><span>@@ -83,6 +83,7 @@</span><br><span> DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 1</span><br><span> DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 2</span><br><span> DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 3</span><br><span style="color: hsl(120, 100%, 40%);">+DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 4</span><br><span> DMAIN Cmdline option --db-check: Database was opened successfully, quitting.</span><br><span> </span><br><span> Resulting db:</span><br><span>@@ -124,6 +125,7 @@</span><br><span> imeisv|VARCHAR|0||0</span><br><span> imsi|VARCHAR(15)|1||0</span><br><span> last_lu_seen|TIMESTAMP|0|NULL|0</span><br><span style="color: hsl(120, 100%, 40%);">+last_lu_seen_ps|TIMESTAMP|0|NULL|0</span><br><span> lmsi|INTEGER|0||0</span><br><span> ms_purged_cs|BOOLEAN|1|0|0</span><br><span> ms_purged_ps|BOOLEAN|1|0|0</span><br><span>@@ -139,13 +141,13 @@</span><br><span> vlr_number|VARCHAR(15)|0||0</span><br><span> </span><br><span> Table subscriber contents:</span><br><span style="color: hsl(0, 100%, 40%);">-ggsn_number|gmlc_number|id|imei|imeisv|imsi|last_lu_seen|lmsi|ms_purged_cs|ms_purged_ps|msc_number|msisdn|nam_cs|nam_ps|periodic_lu_tmr|periodic_rau_tau_tmr|sgsn_address|sgsn_number|smsc_number|vlr_number</span><br><span style="color: hsl(0, 100%, 40%);">-||1|||123456789012345|||0|0||098765432109876|1|1||||||MSC-1</span><br><span style="color: hsl(0, 100%, 40%);">-||2|||111111111|||1|0|||1|1||||||</span><br><span style="color: hsl(0, 100%, 40%);">-||3|||222222222|||0|1||22222|1|1||||||</span><br><span style="color: hsl(0, 100%, 40%);">-||4|||333333|||0|0||3|0|1||||||</span><br><span style="color: hsl(0, 100%, 40%);">-||5|||444444444444444|||0|0||4444|1|0||||||</span><br><span style="color: hsl(0, 100%, 40%);">-||6|||5555555|||0|0||55555555555555|0|0||||||</span><br><span style="color: hsl(120, 100%, 40%);">+ggsn_number|gmlc_number|id|imei|imeisv|imsi|last_lu_seen|last_lu_seen_ps|lmsi|ms_purged_cs|ms_purged_ps|msc_number|msisdn|nam_cs|nam_ps|periodic_lu_tmr|periodic_rau_tau_tmr|sgsn_address|sgsn_number|smsc_number|vlr_number</span><br><span style="color: hsl(120, 100%, 40%);">+||1|||123456789012345||||0|0||098765432109876|1|1||||||MSC-1</span><br><span style="color: hsl(120, 100%, 40%);">+||2|||111111111||||1|0|||1|1||||||</span><br><span style="color: hsl(120, 100%, 40%);">+||3|||222222222||||0|1||22222|1|1||||||</span><br><span style="color: hsl(120, 100%, 40%);">+||4|||333333||||0|0||3|0|1||||||</span><br><span style="color: hsl(120, 100%, 40%);">+||5|||444444444444444||||0|0||4444|1|0||||||</span><br><span style="color: hsl(120, 100%, 40%);">+||6|||5555555||||0|0||55555555555555|0|0||||||</span><br><span> </span><br><span> Table: subscriber_apn</span><br><span> name|type|notnull|dflt_value|pk</span><br><span>@@ -166,5 +168,5 @@</span><br><span> rc = 0</span><br><span> DMAIN hlr starting</span><br><span> DDB using database: <PATH>test.db</span><br><span style="color: hsl(0, 100%, 40%);">-DDB Database <PATH>test.db' has HLR DB schema version 3</span><br><span style="color: hsl(120, 100%, 40%);">+DDB Database <PATH>test.db' has HLR DB schema version 4</span><br><span> DMAIN Cmdline option --db-check: Database was opened successfully, quitting.</span><br><span></span><br></pre><p>To view, visit <a href="https://gerrit.osmocom.org/c/osmo-hlr/+/16201">change 16201</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/c/osmo-hlr/+/16201"/><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-Change-Id: Id7fc50567211a0870ac0524f6dee94d4513781ba </div>
<div style="display:none"> Gerrit-Change-Number: 16201 </div>
<div style="display:none"> Gerrit-PatchSet: 6 </div>
<div style="display:none"> Gerrit-Owner: neels <nhofmeyr@sysmocom.de> </div>
<div style="display:none"> Gerrit-Reviewer: Jenkins Builder </div>
<div style="display:none"> Gerrit-Reviewer: neels <nhofmeyr@sysmocom.de> </div>
<div style="display:none"> Gerrit-Reviewer: osmith <osmith@sysmocom.de> </div>
<div style="display:none"> Gerrit-Reviewer: pespin <pespin@sysmocom.de> </div>
<div style="display:none"> Gerrit-MessageType: merged </div>