diff options
author | Neels Hofmeyr <neels@hofmeyr.de> | 2019-11-20 02:36:35 +0100 |
---|---|---|
committer | Neels Hofmeyr <neels@hofmeyr.de> | 2019-11-27 03:19:06 +0100 |
commit | 07e1602d2dd13d0884ccd623d9f7955b921fa702 (patch) | |
tree | 02a08050da1f6a69bbae0b1571abc8cc6a7c205e | |
parent | abdfdb8a4a58d36e8ac8e54736589df753225da7 (diff) |
db v4: add column last_lu_seen_ps
Location Updating procedures from both CS and PS overwrite the same
last_lu_seen field of a subscriber. For upcoming D-GSM it will be important to
distinguish those, because only CS attaches qualify for MSISDN lookup.
Add column last_lu_seen_ps, and upon PS LU, do not overwrite last_lu_seen, so
that last_lu_seen now only reflects CS LU.
In the VTY, dump both LU dates distinctively.
Change-Id: Id7fc50567211a0870ac0524f6dee94d4513781ba
-rw-r--r-- | include/osmocom/hlr/db.h | 2 | ||||
-rw-r--r-- | sql/hlr.sql | 5 | ||||
-rw-r--r-- | src/db.c | 23 | ||||
-rw-r--r-- | src/db_hlr.c | 46 | ||||
-rw-r--r-- | src/hlr_vty_subscr.c | 14 | ||||
-rw-r--r-- | tests/db_upgrade/db_upgrade_test.ok | 18 |
6 files changed, 76 insertions, 32 deletions
diff --git a/include/osmocom/hlr/db.h b/include/osmocom/hlr/db.h index 15d83de..eacc78e 100644 --- a/include/osmocom/hlr/db.h +++ b/include/osmocom/hlr/db.h @@ -28,6 +28,7 @@ enum stmt_idx { DB_STMT_AUC_3G_INSERT, DB_STMT_AUC_3G_DELETE, DB_STMT_SET_LAST_LU_SEEN, + DB_STMT_SET_LAST_LU_SEEN_PS, DB_STMT_EXISTS_BY_IMSI, DB_STMT_EXISTS_BY_MSISDN, _NUM_DB_STMT @@ -95,6 +96,7 @@ struct hlr_subscriber { bool ms_purged_cs; bool ms_purged_ps; time_t last_lu_seen; + time_t last_lu_seen_ps; }; /* A format string for use with strptime(3). This format string is diff --git a/sql/hlr.sql b/sql/hlr.sql index f8fdc0b..ad57661 100644 --- a/sql/hlr.sql +++ b/sql/hlr.sql @@ -42,7 +42,8 @@ CREATE TABLE subscriber ( -- 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 + last_lu_seen TIMESTAMP default NULL, + last_lu_seen_ps TIMESTAMP default NULL ); CREATE TABLE subscriber_apn ( @@ -77,4 +78,4 @@ CREATE UNIQUE INDEX idx_subscr_imsi ON subscriber (imsi); -- Set HLR database schema version number -- Note: This constant is currently duplicated in src/db.c and must be kept in sync! -PRAGMA user_version = 3; +PRAGMA user_version = 4; @@ -28,7 +28,7 @@ #include "db_bootstrap.h" /* This constant is currently duplicated in sql/hlr.sql and must be kept in sync! */ -#define CURRENT_SCHEMA_VERSION 3 +#define CURRENT_SCHEMA_VERSION 4 #define SEL_COLUMNS \ "id," \ @@ -45,7 +45,8 @@ "lmsi," \ "ms_purged_cs," \ "ms_purged_ps," \ - "last_lu_seen" + "last_lu_seen," \ + "last_lu_seen_ps" \ static const char *stmt_sql[] = { [DB_STMT_SEL_BY_IMSI] = "SELECT " SEL_COLUMNS " FROM subscriber WHERE imsi = ?", @@ -79,6 +80,7 @@ static const char *stmt_sql[] = { " 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", + [DB_STMT_SET_LAST_LU_SEEN_PS] = "UPDATE subscriber SET last_lu_seen_ps = datetime($val, 'unixepoch') WHERE id = $subscriber_id", [DB_STMT_EXISTS_BY_IMSI] = "SELECT 1 FROM subscriber WHERE imsi = $imsi", [DB_STMT_EXISTS_BY_MSISDN] = "SELECT 1 FROM subscriber WHERE msisdn = $msisdn", }; @@ -423,11 +425,28 @@ static int db_upgrade_v3(struct db_context *dbc) return rc; } +static int db_upgrade_v4(struct db_context *dbc) +{ + int rc; + const char *statements[] = { + "ALTER TABLE subscriber ADD COLUMN last_lu_seen_ps TIMESTAMP default NULL", + "PRAGMA user_version = 4", + }; + + rc = db_run_statements(dbc, statements, ARRAY_SIZE(statements)); + if (rc != SQLITE_DONE) { + LOGP(DDB, LOGL_ERROR, "Unable to update HLR database schema to version 4\n"); + return rc; + } + return rc; +} + typedef int (*db_upgrade_func_t)(struct db_context *dbc); static db_upgrade_func_t db_upgrade_path[] = { db_upgrade_v1, db_upgrade_v2, db_upgrade_v3, + db_upgrade_v4, }; static int db_get_user_version(struct db_context *dbc) diff --git a/src/db_hlr.c b/src/db_hlr.c index e52b5ed..b3e3887 100644 --- a/src/db_hlr.c +++ b/src/db_hlr.c @@ -438,14 +438,36 @@ out: return ret; } +static void parse_last_lu_seen(time_t *dst, const char *last_lu_seen_str, const char *imsi, const char *label) +{ + struct tm tm = {0}; + time_t val; + if (!last_lu_seen_str || last_lu_seen_str[0] == '\0') + return; + + if (strptime(last_lu_seen_str, DB_LAST_LU_SEEN_FMT, &tm) == NULL) { + LOGP(DAUC, LOGL_ERROR, "IMSI-%s: Last LU Seen %s: Cannot parse timestamp '%s'\n", + imsi, label, last_lu_seen_str); + return; + } + + errno = 0; + val = mktime(&tm); + if (val == -1) { + LOGP(DAUC, LOGL_ERROR, "IMSI-%s: Last LU Seen %s: Cannot convert timestamp '%s' to time_t: %s\n", + imsi, label, last_lu_seen_str, strerror(errno)); + val = 0; + } + + *dst = val; +} + /* Common code for db_subscr_get_by_*() functions. */ static int db_sel(struct db_context *dbc, sqlite3_stmt *stmt, struct hlr_subscriber *subscr, const char **err) { int rc; int ret = 0; - const char *last_lu_seen_str; - struct tm tm = {0}; /* execute the statement */ rc = sqlite3_step(stmt); @@ -479,20 +501,10 @@ static int db_sel(struct db_context *dbc, sqlite3_stmt *stmt, struct hlr_subscri subscr->lmsi = sqlite3_column_int(stmt, 11); subscr->ms_purged_cs = sqlite3_column_int(stmt, 12); subscr->ms_purged_ps = sqlite3_column_int(stmt, 13); - last_lu_seen_str = (const char *)sqlite3_column_text(stmt, 14); - if (last_lu_seen_str && last_lu_seen_str[0] != '\0') { - if (strptime(last_lu_seen_str, DB_LAST_LU_SEEN_FMT, &tm) == NULL) { - LOGP(DAUC, LOGL_ERROR, "Cannot parse last LU timestamp '%s' of subscriber with IMSI='%s': %s\n", - last_lu_seen_str, subscr->imsi, strerror(errno)); - } else { - subscr->last_lu_seen = mktime(&tm); - if (subscr->last_lu_seen == -1) { - LOGP(DAUC, LOGL_ERROR, "Cannot convert LU timestamp '%s' to time_t: %s\n", - last_lu_seen_str, strerror(errno)); - subscr->last_lu_seen = 0; - } - } - } + parse_last_lu_seen(&subscr->last_lu_seen, (const char *)sqlite3_column_text(stmt, 14), + subscr->imsi, "CS"); + parse_last_lu_seen(&subscr->last_lu_seen_ps, (const char *)sqlite3_column_text(stmt, 15), + subscr->imsi, "PS"); out: db_remove_reset(stmt); @@ -770,7 +782,7 @@ int db_subscr_lu(struct db_context *dbc, int64_t subscr_id, goto out; } - stmt = dbc->stmt[DB_STMT_SET_LAST_LU_SEEN]; + stmt = dbc->stmt[is_ps? DB_STMT_SET_LAST_LU_SEEN_PS : DB_STMT_SET_LAST_LU_SEEN]; if (!db_bind_int64(stmt, "$subscriber_id", subscr_id)) return -EIO; diff --git a/src/hlr_vty_subscr.c b/src/hlr_vty_subscr.c index d2c4c81..b561636 100644 --- a/src/hlr_vty_subscr.c +++ b/src/hlr_vty_subscr.c @@ -47,12 +47,20 @@ get_datestr(const time_t *t, char *datebuf) return s; } +static void dump_last_lu_seen(struct vty *vty, const char *domain_label, time_t last_lu_seen) +{ + char datebuf[26]; /* for ctime_r(3) */ + if (!last_lu_seen) + return; + vty_out(vty, " last LU seen on %s: %s UTC%s", domain_label, get_datestr(&last_lu_seen, datebuf), + VTY_NEWLINE); +} + static void subscr_dump_full_vty(struct vty *vty, struct hlr_subscriber *subscr) { int rc; struct osmo_sub_auth_data aud2g; struct osmo_sub_auth_data aud3g; - char datebuf[26]; /* for ctime_r(3) */ vty_out(vty, " ID: %"PRIu64"%s", subscr->id, VTY_NEWLINE); @@ -87,8 +95,8 @@ static void subscr_dump_full_vty(struct vty *vty, struct hlr_subscriber *subscr) vty_out(vty, " PS disabled%s", VTY_NEWLINE); if (subscr->ms_purged_ps) vty_out(vty, " PS purged%s", VTY_NEWLINE); - if (subscr->last_lu_seen) - vty_out(vty, " last LU seen: %s UTC%s", get_datestr(&subscr->last_lu_seen, datebuf), VTY_NEWLINE); + dump_last_lu_seen(vty, "CS", subscr->last_lu_seen); + dump_last_lu_seen(vty, "PS", subscr->last_lu_seen_ps); if (!*subscr->imsi) return; diff --git a/tests/db_upgrade/db_upgrade_test.ok b/tests/db_upgrade/db_upgrade_test.ok index 49e7151..67d0006 100644 --- a/tests/db_upgrade/db_upgrade_test.ok +++ b/tests/db_upgrade/db_upgrade_test.ok @@ -83,6 +83,7 @@ DDB Database <PATH>test.db' has HLR DB schema version 0 DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 1 DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 2 DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 3 +DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 4 DMAIN Cmdline option --db-check: Database was opened successfully, quitting. Resulting db: @@ -124,6 +125,7 @@ imei|VARCHAR(14)|0||0 imeisv|VARCHAR|0||0 imsi|VARCHAR(15)|1||0 last_lu_seen|TIMESTAMP|0|NULL|0 +last_lu_seen_ps|TIMESTAMP|0|NULL|0 lmsi|INTEGER|0||0 ms_purged_cs|BOOLEAN|1|0|0 ms_purged_ps|BOOLEAN|1|0|0 @@ -139,13 +141,13 @@ smsc_number|VARCHAR(15)|0||0 vlr_number|VARCHAR(15)|0||0 Table subscriber contents: -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 -||1|||123456789012345|||0|0||098765432109876|1|1||||||MSC-1 -||2|||111111111|||1|0|||1|1|||||| -||3|||222222222|||0|1||22222|1|1|||||| -||4|||333333|||0|0||3|0|1|||||| -||5|||444444444444444|||0|0||4444|1|0|||||| -||6|||5555555|||0|0||55555555555555|0|0|||||| +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 +||1|||123456789012345||||0|0||098765432109876|1|1||||||MSC-1 +||2|||111111111||||1|0|||1|1|||||| +||3|||222222222||||0|1||22222|1|1|||||| +||4|||333333||||0|0||3|0|1|||||| +||5|||444444444444444||||0|0||4444|1|0|||||| +||6|||5555555||||0|0||55555555555555|0|0|||||| Table: subscriber_apn name|type|notnull|dflt_value|pk @@ -166,5 +168,5 @@ osmo-hlr --database $db --db-check --config-file $srcdir/osmo-hlr.cfg rc = 0 DMAIN hlr starting DDB using database: <PATH>test.db -DDB Database <PATH>test.db' has HLR DB schema version 3 +DDB Database <PATH>test.db' has HLR DB schema version 4 DMAIN Cmdline option --db-check: Database was opened successfully, quitting. |