<p>keith <strong>submitted</strong> this change.</p><p><a href="https://gerrit.osmocom.org/c/osmo-hlr/+/19914">View Change</a></p><div style="white-space:pre-wrap">Approvals:
keith: Looks good to me, approved
Jenkins Builder: Verified
</div><pre style="font-family: monospace,monospace; white-space: pre-wrap;">osmo-hlr-db-tool: Make import from osmo-nitb less "lossy"<br><br>Include the IMEI and the last seen time in an import from<br>an osmo-nitb database.<br><br>Change-Id: Ic47e549be3551ae43ab6a84228d47ae03e9652a6<br>---<br>M src/hlr_db_tool.c<br>1 file changed, 98 insertions(+), 5 deletions(-)<br><br></pre><pre style="font-family: monospace,monospace; white-space: pre-wrap;"><span>diff --git a/src/hlr_db_tool.c b/src/hlr_db_tool.c</span><br><span>index 1212018..b2a26dc 100644</span><br><span>--- a/src/hlr_db_tool.c</span><br><span>+++ b/src/hlr_db_tool.c</span><br><span>@@ -25,6 +25,7 @@</span><br><span> #include <getopt.h></span><br><span> #include <inttypes.h></span><br><span> #include <string.h></span><br><span style="color: hsl(120, 100%, 40%);">+#include <errno.h></span><br><span> </span><br><span> #include <osmocom/core/logging.h></span><br><span> #include <osmocom/core/application.h></span><br><span>@@ -70,8 +71,9 @@</span><br><span> printf(" (All commands imply this if none exists yet.)\n");</span><br><span> printf("\n");</span><br><span> printf(" import-nitb-db <nitb.db> Add OsmoNITB db's subscribers to OsmoHLR db.\n");</span><br><span style="color: hsl(0, 100%, 40%);">- printf(" Be aware that the import is lossy, only the\n");</span><br><span style="color: hsl(0, 100%, 40%);">- printf(" IMSI, MSISDN, nam_cs/ps and 2G auth data are set.\n");</span><br><span style="color: hsl(120, 100%, 40%);">+ printf(" Be aware that the import is somewhat lossy, only the IMSI,\n");</span><br><span style="color: hsl(120, 100%, 40%);">+ printf(" MSISDN, IMEI, nam_cs/ps, 2G auth data and last seen LU are set.\n");</span><br><span style="color: hsl(120, 100%, 40%);">+ printf(" The most recently associated IMEI from the Equipment table is used.\n");</span><br><span> }</span><br><span> </span><br><span> static void print_version(int print_copyright)</span><br><span>@@ -212,9 +214,15 @@</span><br><span> </span><br><span> static const char *nitb_stmt_sql[] = {</span><br><span> [NITB_SELECT_SUBSCR] =</span><br><span style="color: hsl(0, 100%, 40%);">- "SELECT imsi, id, extension, authorized"</span><br><span style="color: hsl(0, 100%, 40%);">- " FROM Subscriber"</span><br><span style="color: hsl(0, 100%, 40%);">- " ORDER BY id",</span><br><span style="color: hsl(120, 100%, 40%);">+ "SELECT s.imsi, s.id, s.extension, s.authorized,"</span><br><span style="color: hsl(120, 100%, 40%);">+ " SUBSTR(e.imei,0,15), STRFTIME('%s', s.expire_lu)"</span><br><span style="color: hsl(120, 100%, 40%);">+ " FROM Subscriber s LEFT JOIN"</span><br><span style="color: hsl(120, 100%, 40%);">+ " (SELECT imei, subscriber_id, MAX(Equipment.updated) AS updated"</span><br><span style="color: hsl(120, 100%, 40%);">+ " FROM Equipment,EquipmentWatch"</span><br><span style="color: hsl(120, 100%, 40%);">+ " WHERE Equipment.id = EquipmentWatch.equipment_id"</span><br><span style="color: hsl(120, 100%, 40%);">+ " GROUP BY EquipmentWatch.subscriber_id) e"</span><br><span style="color: hsl(120, 100%, 40%);">+ " ON e.subscriber_id = s.id"</span><br><span style="color: hsl(120, 100%, 40%);">+ " ORDER by s.id",</span><br><span> [NITB_SELECT_AUTH_KEYS] =</span><br><span> "SELECT algorithm_id, a3a8_ki from authkeys"</span><br><span> " WHERE subscriber_id = $subscr_id",</span><br><span>@@ -222,8 +230,65 @@</span><br><span> </span><br><span> sqlite3_stmt *nitb_stmt[ARRAY_SIZE(nitb_stmt_sql)] = {};</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+enum hlr_db_stmt {</span><br><span style="color: hsl(120, 100%, 40%);">+ HLR_DB_STMT_SET_IMPLICIT_LU_BY_IMSI,</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%);">+static const char *hlr_db_stmt_sql[] = {</span><br><span style="color: hsl(120, 100%, 40%);">+ [HLR_DB_STMT_SET_IMPLICIT_LU_BY_IMSI] =</span><br><span style="color: hsl(120, 100%, 40%);">+ "UPDATE subscriber SET last_lu_seen = datetime($last_lu, 'unixepoch') WHERE imsi = $imsi",</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%);">+sqlite3_stmt *hlr_db_stmt[ARRAY_SIZE(hlr_db_stmt_sql)] = {};</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span> size_t _dbd_decode_binary(const unsigned char *in, unsigned char *out);</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+/*! Set a subscriber's LU timestamp in the HLR database.</span><br><span style="color: hsl(120, 100%, 40%);">+ * In normal operations there is never any need to explicitly</span><br><span style="color: hsl(120, 100%, 40%);">+ * update the value of last_lu_seen, so this function can live here.</span><br><span style="color: hsl(120, 100%, 40%);">+ *</span><br><span style="color: hsl(120, 100%, 40%);">+ * \param[in,out] dbc database context.</span><br><span style="color: hsl(120, 100%, 40%);">+ * \param[in] imsi ASCII string of IMSI digits</span><br><span style="color: hsl(120, 100%, 40%);">+ * \param[in] imei ASCII string of identifier digits, or NULL to remove the IMEI.</span><br><span style="color: hsl(120, 100%, 40%);">+ * \returns 0 on success, -ENOENT when the given subscriber does not exist,</span><br><span style="color: hsl(120, 100%, 40%);">+ * -EIO on database errors.</span><br><span style="color: hsl(120, 100%, 40%);">+ */</span><br><span style="color: hsl(120, 100%, 40%);">+int db_subscr_update_lu_by_imsi(struct db_context *dbc, const char* imsi, const int last_lu)</span><br><span style="color: hsl(120, 100%, 40%);">+{</span><br><span style="color: hsl(120, 100%, 40%);">+ int rc, ret = 0;</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ sqlite3_stmt *stmt = hlr_db_stmt[HLR_DB_STMT_SET_IMPLICIT_LU_BY_IMSI];</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ if (!db_bind_text(stmt, "$imsi", imsi))</span><br><span style="color: hsl(120, 100%, 40%);">+ return -EIO;</span><br><span style="color: hsl(120, 100%, 40%);">+ if (last_lu && !db_bind_int(stmt, "$last_lu", last_lu))</span><br><span style="color: hsl(120, 100%, 40%);">+ return -EIO;</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ /* execute the statement */</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_step(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (rc != SQLITE_DONE) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DAUC, LOGL_ERROR, "Update last_lu_seen for subscriber IMSI='%s': SQL Error: %s\n", imsi,</span><br><span style="color: hsl(120, 100%, 40%);">+ sqlite3_errmsg(dbc->db));</span><br><span style="color: hsl(120, 100%, 40%);">+ ret = -EIO;</span><br><span style="color: hsl(120, 100%, 40%);">+ goto out;</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%);">+ /* verify execution result */</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_changes(dbc->db);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (!rc) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DAUC, LOGL_ERROR, "Cannot update last_lu_seen for subscriber IMSI='%s': no such subscriber\n", imsi);</span><br><span style="color: hsl(120, 100%, 40%);">+ ret = -ENOENT;</span><br><span style="color: hsl(120, 100%, 40%);">+ } else if (rc != 1) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DAUC, LOGL_ERROR, "Update last_lu_seen for subscriber IMSI='%s': SQL modified %d rows (expected 1)\n",</span><br><span style="color: hsl(120, 100%, 40%);">+ imsi, rc);</span><br><span style="color: hsl(120, 100%, 40%);">+ ret = -EIO;</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%);">+out:</span><br><span style="color: hsl(120, 100%, 40%);">+ db_remove_reset(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ return ret;</span><br><span style="color: hsl(120, 100%, 40%);">+}</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span> void import_nitb_subscr_aud(sqlite3 *nitb_db, const char *imsi, int64_t nitb_id, int64_t hlr_id)</span><br><span> {</span><br><span> int rc;</span><br><span>@@ -297,6 +362,7 @@</span><br><span> int64_t imsi;</span><br><span> char imsi_str[32];</span><br><span> bool authorized;</span><br><span style="color: hsl(120, 100%, 40%);">+ int last_lu_int;</span><br><span> </span><br><span> imsi = sqlite3_column_int64(stmt, 0);</span><br><span> </span><br><span>@@ -315,8 +381,18 @@</span><br><span> nitb_id = sqlite3_column_int64(stmt, 1);</span><br><span> copy_sqlite3_text_to_buf(subscr.msisdn, stmt, 2);</span><br><span> authorized = sqlite3_column_int(stmt, 3) ? true : false;</span><br><span style="color: hsl(120, 100%, 40%);">+ copy_sqlite3_text_to_buf(subscr.imei, stmt, 4);</span><br><span style="color: hsl(120, 100%, 40%);">+ /* Default periodic LU was 30 mins and the expire_lu</span><br><span style="color: hsl(120, 100%, 40%);">+ * was twice that + 1 min</span><br><span style="color: hsl(120, 100%, 40%);">+ */</span><br><span style="color: hsl(120, 100%, 40%);">+ last_lu_int = sqlite3_column_int(stmt, 5) - 3660;</span><br><span> </span><br><span> db_subscr_update_msisdn_by_imsi(dbc, imsi_str, subscr.msisdn);</span><br><span style="color: hsl(120, 100%, 40%);">+ /* In case the subscriber was somehow never seen, invent an IMEI */</span><br><span style="color: hsl(120, 100%, 40%);">+ if (strlen(subscr.imei) == 14)</span><br><span style="color: hsl(120, 100%, 40%);">+ db_subscr_update_imei_by_imsi(dbc, imsi_str, subscr.imei);</span><br><span style="color: hsl(120, 100%, 40%);">+ db_subscr_update_lu_by_imsi(dbc, imsi_str, last_lu_int);</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span> db_subscr_nam(dbc, imsi_str, authorized, true);</span><br><span> db_subscr_nam(dbc, imsi_str, authorized, false);</span><br><span> </span><br><span>@@ -361,6 +437,17 @@</span><br><span> }</span><br><span> }</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+ for (i = 0; i < ARRAY_SIZE(hlr_db_stmt_sql); i++) {</span><br><span style="color: hsl(120, 100%, 40%);">+ sql = hlr_db_stmt_sql[i];</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_prepare_v2(g_hlr_db_tool_ctx->dbc->db, hlr_db_stmt_sql[i], -1,</span><br><span style="color: hsl(120, 100%, 40%);">+ &hlr_db_stmt[i], NULL);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (rc != SQLITE_OK) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DDB, LOGL_ERROR, "OsmoHLR DB: Unable to prepare SQL statement '%s'\n", sql);</span><br><span style="color: hsl(120, 100%, 40%);">+ ret = -1;</span><br><span style="color: hsl(120, 100%, 40%);">+ goto out_free;</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%);">+</span><br><span> stmt = nitb_stmt[NITB_SELECT_SUBSCR];</span><br><span> </span><br><span> while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {</span><br><span>@@ -387,6 +474,7 @@</span><br><span> {</span><br><span> int rc;</span><br><span> int (*main_action)(void);</span><br><span style="color: hsl(120, 100%, 40%);">+ int i;</span><br><span> main_action = NULL;</span><br><span> </span><br><span> g_hlr_db_tool_ctx = talloc_zero(NULL, struct hlr_db_tool_ctx);</span><br><span>@@ -430,6 +518,11 @@</span><br><span> if (main_action)</span><br><span> rc = (*main_action)();</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+ /* db_close will only finalize statments in g_hlr_db_tool_ctx->dbc->stmt</span><br><span style="color: hsl(120, 100%, 40%);">+ * it is ok to call finalize on NULL */</span><br><span style="color: hsl(120, 100%, 40%);">+ for (i = 0; i < ARRAY_SIZE(hlr_db_stmt); i++) {</span><br><span style="color: hsl(120, 100%, 40%);">+ sqlite3_finalize(hlr_db_stmt[i]);</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span> db_close(g_hlr_db_tool_ctx->dbc);</span><br><span> log_fini();</span><br><span> exit(rc ? EXIT_FAILURE : EXIT_SUCCESS);</span><br><span></span><br></pre><p>To view, visit <a href="https://gerrit.osmocom.org/c/osmo-hlr/+/19914">change 19914</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/+/19914"/><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: Ic47e549be3551ae43ab6a84228d47ae03e9652a6 </div>
<div style="display:none"> Gerrit-Change-Number: 19914 </div>
<div style="display:none"> Gerrit-PatchSet: 7 </div>
<div style="display:none"> Gerrit-Owner: keith <keith@rhizomatica.org> </div>
<div style="display:none"> Gerrit-Assignee: neels <nhofmeyr@sysmocom.de> </div>
<div style="display:none"> Gerrit-Reviewer: Jenkins Builder </div>
<div style="display:none"> Gerrit-Reviewer: keith <keith@rhizomatica.org> </div>
<div style="display:none"> Gerrit-Reviewer: neels <nhofmeyr@sysmocom.de> </div>
<div style="display:none"> Gerrit-MessageType: merged </div>