This is merely a historical archive of years 2008-2021, before the migration to mailman3.
A maintained and still updated list archive can be found at https://lists.osmocom.org/hyperkitty/list/gerrit-log@lists.osmocom.org/.
Stefan Sperling gerrit-no-reply at lists.osmocom.orgStefan Sperling has uploaded this change for review. ( https://gerrit.osmocom.org/12121 Change subject: store timestamp of last location update seen from subscriber ...................................................................... store timestamp of last location update seen from subscriber Store a timestamp of the last location update seen from a subscriber in the HLR DB. Timestamps are stored in a new 'last_lu_seen' column in the 'subscriber' table, 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 formt, 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, 104 insertions(+), 5 deletions(-) git pull ssh://gerrit.osmocom.org:29418/osmo-hlr refs/changes/21/12121/1 diff --git a/sql/hlr.sql b/sql/hlr.sql index 3499109..d1db256 100644 --- a/sql/hlr.sql +++ b/sql/hlr.sql @@ -36,7 +36,10 @@ -- 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 + last_lu_seen TIMESTAMP default NULL ); CREATE TABLE subscriber_apn ( @@ -69,4 +72,4 @@ CREATE UNIQUE INDEX idx_subscr_imsi ON subscriber (imsi); -- Set HLR database schema version number -PRAGMA user_version = 0; +PRAGMA user_version = 1; diff --git a/src/db.c b/src/db.c index df52f9b..70b5a26 100644 --- a/src/db.c +++ b/src/db.c @@ -27,7 +27,7 @@ #include "db.h" #include "db_bootstrap.h" -#define CURRENT_SCHEMA_VERSION 0 +#define CURRENT_SCHEMA_VERSION 1 #define SEL_COLUMNS \ "id," \ @@ -42,7 +42,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 +74,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 +254,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 +383,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..81897a8 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 t; stmt = dbc->stmt[is_ps ? DB_STMT_UPD_SGSN_BY_ID : DB_STMT_UPD_VLR_BY_ID]; @@ -603,13 +605,53 @@ ": 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 (clock_gettime(CLOCK_MONOTONIC, &t) != 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; + if (!db_bind_int64(stmt, "$val", (int64_t)t.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: newchange Gerrit-Change-Id: Ibeb49d45aec18451a260a6654b8c51b8fc3bec50 Gerrit-Change-Number: 12121 Gerrit-PatchSet: 1 Gerrit-Owner: Stefan Sperling <stsp at stsp.name> -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://lists.osmocom.org/pipermail/gerrit-log/attachments/20181204/b24badd8/attachment.htm>