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/.
keith gerrit-no-reply at lists.osmocom.orgkeith has uploaded this change for review. ( https://gerrit.osmocom.org/c/osmo-hlr/+/19914 ) Change subject: osmo-hlr-db-tool: Make import from osmo-nitb less "lossy" ...................................................................... osmo-hlr-db-tool: Make import from osmo-nitb less "lossy" Include the IMEI and the last seen time in an import from an osmo-nitb database. Change-Id: Ic47e549be3551ae43ab6a84228d47ae03e9652a6 --- M include/osmocom/hlr/db.h M src/db.c M src/hlr_db_tool.c 3 files changed, 70 insertions(+), 5 deletions(-) git pull ssh://gerrit.osmocom.org:29418/osmo-hlr refs/changes/14/19914/1 diff --git a/include/osmocom/hlr/db.h b/include/osmocom/hlr/db.h index ca336a0..176a097 100644 --- a/include/osmocom/hlr/db.h +++ b/include/osmocom/hlr/db.h @@ -33,6 +33,7 @@ DB_STMT_SET_LAST_LU_SEEN_PS, DB_STMT_EXISTS_BY_IMSI, DB_STMT_EXISTS_BY_MSISDN, + DB_STMT_SET_IMPLICIT_LU_BY_IMSI, _NUM_DB_STMT }; diff --git a/src/db.c b/src/db.c index 5ec20e2..fb7c2d5 100644 --- a/src/db.c +++ b/src/db.c @@ -85,6 +85,7 @@ [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", + [DB_STMT_SET_IMPLICIT_LU_BY_IMSI] = "UPDATE subscriber SET last_lu_seen = datetime($last_lu, 'unixepoch') WHERE imsi = $imsi", }; static void sql3_error_log_cb(void *arg, int err_code, const char *msg) diff --git a/src/hlr_db_tool.c b/src/hlr_db_tool.c index 1212018..e9733a0 100644 --- a/src/hlr_db_tool.c +++ b/src/hlr_db_tool.c @@ -25,6 +25,7 @@ #include <getopt.h> #include <inttypes.h> #include <string.h> +#include <errno.h> #include <osmocom/core/logging.h> #include <osmocom/core/application.h> @@ -70,8 +71,9 @@ printf(" (All commands imply this if none exists yet.)\n"); printf("\n"); printf(" import-nitb-db <nitb.db> Add OsmoNITB db's subscribers to OsmoHLR db.\n"); - printf(" Be aware that the import is lossy, only the\n"); - printf(" IMSI, MSISDN, nam_cs/ps and 2G auth data are set.\n"); + printf(" Be aware that the import is somewhat lossy, only the IMSI,\n"); + printf(" MSISDN, IMEI, nam_cs/ps, 2G auth data and last seen LU are set.\n"); + printf(" The most recently associated IMEI from the Equipment table is used.\n"); } static void print_version(int print_copyright) @@ -212,9 +214,15 @@ static const char *nitb_stmt_sql[] = { [NITB_SELECT_SUBSCR] = - "SELECT imsi, id, extension, authorized" - " FROM Subscriber" - " ORDER BY id", + "SELECT s.imsi, s.id, s.extension, s.authorized," + " SUBSTR(e.imei,0,15), STRFTIME('%s', s.expire_lu)" + " FROM Subscriber s INNER JOIN" + " (SELECT imei, subscriber_id, MAX(Equipment.updated) AS updated" + " FROM Equipment,EquipmentWatch" + " WHERE Equipment.id = EquipmentWatch.equipment_id" + " GROUP BY EquipmentWatch.subscriber_id) e" + " ON e.subscriber_id = s.id" + " ORDER by s.id", [NITB_SELECT_AUTH_KEYS] = "SELECT algorithm_id, a3a8_ki from authkeys" " WHERE subscriber_id = $subscr_id", @@ -224,6 +232,52 @@ size_t _dbd_decode_binary(const unsigned char *in, unsigned char *out); +/*! Set a subscriber's LU timestamp in the HLR database. + * In normal operations there is never any need to explicitly + * update the value of last_lu_seen, so this function can live here. + * + * \param[in,out] dbc database context. + * \param[in] imsi ASCII string of IMSI digits + * \param[in] imei ASCII string of identifier digits, or NULL to remove the IMEI. + * \returns 0 on success, -ENOENT when the given subscriber does not exist, + * -EIO on database errors. + */ +int db_subscr_update_lu_by_imsi(struct db_context *dbc, const char* imsi, const int last_lu) +{ + int rc, ret = 0; + + sqlite3_stmt *stmt = dbc->stmt[DB_STMT_SET_IMPLICIT_LU_BY_IMSI]; + + if (!db_bind_text(stmt, "$imsi", imsi)) + return -EIO; + if (last_lu && !db_bind_int(stmt, "$last_lu", last_lu)) + return -EIO; + + /* execute the statement */ + rc = sqlite3_step(stmt); + if (rc != SQLITE_DONE) { + LOGP(DAUC, LOGL_ERROR, "Update last_lu_seen for subscriber IMSI='%s': SQL Error: %s\n", imsi, + sqlite3_errmsg(dbc->db)); + ret = -EIO; + goto out; + } + + /* verify execution result */ + rc = sqlite3_changes(dbc->db); + if (!rc) { + LOGP(DAUC, LOGL_ERROR, "Cannot update last_lu_seen for subscriber IMSI='%s': no such subscriber\n", imsi); + ret = -ENOENT; + } else if (rc != 1) { + LOGP(DAUC, LOGL_ERROR, "Update last_lu_seen for subscriber IMSI='%s': SQL modified %d rows (expected 1)\n", + imsi, rc); + ret = -EIO; + } + +out: + db_remove_reset(stmt); + return ret; +} + void import_nitb_subscr_aud(sqlite3 *nitb_db, const char *imsi, int64_t nitb_id, int64_t hlr_id) { int rc; @@ -297,6 +351,7 @@ int64_t imsi; char imsi_str[32]; bool authorized; + int last_lu_int; imsi = sqlite3_column_int64(stmt, 0); @@ -315,8 +370,16 @@ nitb_id = sqlite3_column_int64(stmt, 1); copy_sqlite3_text_to_buf(subscr.msisdn, stmt, 2); authorized = sqlite3_column_int(stmt, 3) ? true : false; + copy_sqlite3_text_to_buf(subscr.imei, stmt, 4); + /* Default periodic LU was 30 mins and the expire_lu + * was twice that + 1 min + */ + last_lu_int = sqlite3_column_int(stmt, 5) - 3660; db_subscr_update_msisdn_by_imsi(dbc, imsi_str, subscr.msisdn); + db_subscr_update_imei_by_imsi(dbc, imsi_str, subscr.imei); + db_subscr_update_lu_by_imsi(dbc, imsi_str, last_lu_int); + db_subscr_nam(dbc, imsi_str, authorized, true); db_subscr_nam(dbc, imsi_str, authorized, false); -- To view, visit https://gerrit.osmocom.org/c/osmo-hlr/+/19914 To unsubscribe, or for help writing mail filters, visit https://gerrit.osmocom.org/settings Gerrit-Project: osmo-hlr Gerrit-Branch: master Gerrit-Change-Id: Ic47e549be3551ae43ab6a84228d47ae03e9652a6 Gerrit-Change-Number: 19914 Gerrit-PatchSet: 1 Gerrit-Owner: keith <keith at rhizomatica.org> Gerrit-MessageType: newchange -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://lists.osmocom.org/pipermail/gerrit-log/attachments/20200831/fba1930e/attachment.htm>