From de50b201167b717e69053c57c42ea26c35cfe5db Mon Sep 17 00:00:00 2001 From: Keith Date: Mon, 31 Aug 2020 16:38:32 +0200 Subject: 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 --- src/hlr_db_tool.c | 103 +++++++++++++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 98 insertions(+), 5 deletions(-) diff --git a/src/hlr_db_tool.c b/src/hlr_db_tool.c index 1212018..b2a26dc 100644 --- a/src/hlr_db_tool.c +++ b/src/hlr_db_tool.c @@ -25,6 +25,7 @@ #include #include #include +#include #include #include @@ -70,8 +71,9 @@ static void print_help() printf(" (All commands imply this if none exists yet.)\n"); printf("\n"); printf(" import-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 @@ enum nitb_stmt { 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 LEFT 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", @@ -222,8 +230,65 @@ static const char *nitb_stmt_sql[] = { sqlite3_stmt *nitb_stmt[ARRAY_SIZE(nitb_stmt_sql)] = {}; +enum hlr_db_stmt { + HLR_DB_STMT_SET_IMPLICIT_LU_BY_IMSI, +}; + +static const char *hlr_db_stmt_sql[] = { + [HLR_DB_STMT_SET_IMPLICIT_LU_BY_IMSI] = + "UPDATE subscriber SET last_lu_seen = datetime($last_lu, 'unixepoch') WHERE imsi = $imsi", +}; + +sqlite3_stmt *hlr_db_stmt[ARRAY_SIZE(hlr_db_stmt_sql)] = {}; + 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 = hlr_db_stmt[HLR_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 +362,7 @@ void import_nitb_subscr(sqlite3 *nitb_db, sqlite3_stmt *stmt) int64_t imsi; char imsi_str[32]; bool authorized; + int last_lu_int; imsi = sqlite3_column_int64(stmt, 0); @@ -315,8 +381,18 @@ void import_nitb_subscr(sqlite3 *nitb_db, sqlite3_stmt *stmt) 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); + /* In case the subscriber was somehow never seen, invent an IMEI */ + if (strlen(subscr.imei) == 14) + 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); @@ -361,6 +437,17 @@ int import_nitb_db(void) } } + for (i = 0; i < ARRAY_SIZE(hlr_db_stmt_sql); i++) { + sql = hlr_db_stmt_sql[i]; + rc = sqlite3_prepare_v2(g_hlr_db_tool_ctx->dbc->db, hlr_db_stmt_sql[i], -1, + &hlr_db_stmt[i], NULL); + if (rc != SQLITE_OK) { + LOGP(DDB, LOGL_ERROR, "OsmoHLR DB: Unable to prepare SQL statement '%s'\n", sql); + ret = -1; + goto out_free; + } + } + stmt = nitb_stmt[NITB_SELECT_SUBSCR]; while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) { @@ -387,6 +474,7 @@ int main(int argc, char **argv) { int rc; int (*main_action)(void); + int i; main_action = NULL; g_hlr_db_tool_ctx = talloc_zero(NULL, struct hlr_db_tool_ctx); @@ -430,6 +518,11 @@ int main(int argc, char **argv) if (main_action) rc = (*main_action)(); + /* db_close will only finalize statments in g_hlr_db_tool_ctx->dbc->stmt + * it is ok to call finalize on NULL */ + for (i = 0; i < ARRAY_SIZE(hlr_db_stmt); i++) { + sqlite3_finalize(hlr_db_stmt[i]); + } db_close(g_hlr_db_tool_ctx->dbc); log_fini(); exit(rc ? EXIT_FAILURE : EXIT_SUCCESS); -- cgit v1.2.3