<p>neels has uploaded this change for <strong>review</strong>.</p><p><a href="https://gerrit.osmocom.org/c/osmo-hlr/+/16207">View Change</a></p><pre style="font-family: monospace,monospace; white-space: pre-wrap;">db v5: prep for D-GSM: add vlr_via_proxy and sgsn_via_proxy<br><br>D-GSM will store in the HLR DB whether a locally connected MSC has attached the<br>subscriber (last_lu_seen[_ps]), or whether the attach happened via a GSUP proxy<br>from a different site.<br><br>Add columns for this separately in this patch.<br><br>Change-Id: I98c7b3870559ede84adf56e4bf111f53c7487745<br>---<br>M include/osmocom/hlr/db.h<br>M sql/hlr.sql<br>M src/db.c<br>M src/db_hlr.c<br>M tests/db_upgrade/db_upgrade_test.ok<br>5 files changed, 75 insertions(+), 14 deletions(-)<br><br></pre><pre style="font-family: monospace,monospace; white-space: pre-wrap;">git pull ssh://gerrit.osmocom.org:29418/osmo-hlr refs/changes/07/16207/1</pre><pre style="font-family: monospace,monospace; white-space: pre-wrap;"><span>diff --git a/include/osmocom/hlr/db.h b/include/osmocom/hlr/db.h</span><br><span>index 592a976..78dad3e 100644</span><br><span>--- a/include/osmocom/hlr/db.h</span><br><span>+++ b/include/osmocom/hlr/db.h</span><br><span>@@ -51,6 +51,7 @@</span><br><span> bool db_bind_text(sqlite3_stmt *stmt, const char *param_name, const char *text);</span><br><span> bool db_bind_int(sqlite3_stmt *stmt, const char *param_name, int nr);</span><br><span> bool db_bind_int64(sqlite3_stmt *stmt, const char *param_name, int64_t nr);</span><br><span style="color: hsl(120, 100%, 40%);">+bool db_bind_null(sqlite3_stmt *stmt, const char *param_name);</span><br><span> void db_close(struct db_context *dbc);</span><br><span> struct db_context *db_open(void *ctx, const char *fname, bool enable_sqlite3_logging, bool allow_upgrades);</span><br><span> </span><br><span>@@ -99,6 +100,9 @@</span><br><span> bool ms_purged_ps;</span><br><span> time_t last_lu_seen;</span><br><span> time_t last_lu_seen_ps;</span><br><span style="color: hsl(120, 100%, 40%);">+ /* talloc'd IPA unit name */</span><br><span style="color: hsl(120, 100%, 40%);">+ struct osmo_gt vlr_via_proxy;</span><br><span style="color: hsl(120, 100%, 40%);">+ struct osmo_gt sgsn_via_proxy;</span><br><span> };</span><br><span> </span><br><span> /* A format string for use with strptime(3). This format string is</span><br><span>diff --git a/sql/hlr.sql b/sql/hlr.sql</span><br><span>index ad57661..98e586d 100644</span><br><span>--- a/sql/hlr.sql</span><br><span>+++ b/sql/hlr.sql</span><br><span>@@ -43,7 +43,12 @@</span><br><span> -- Timestamp of last location update seen from subscriber</span><br><span> -- The value is a string which encodes a UTC timestamp in granularity of seconds.</span><br><span> last_lu_seen TIMESTAMP default NULL,</span><br><span style="color: hsl(0, 100%, 40%);">- last_lu_seen_ps TIMESTAMP default NULL</span><br><span style="color: hsl(120, 100%, 40%);">+ last_lu_seen_ps TIMESTAMP default NULL,</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ -- When a LU was received via a proxy, that proxy's hlr_number is stored here,</span><br><span style="color: hsl(120, 100%, 40%);">+ -- while vlr_number reflects the MSC on the far side of that proxy.</span><br><span style="color: hsl(120, 100%, 40%);">+ vlr_via_proxy VARCHAR,</span><br><span style="color: hsl(120, 100%, 40%);">+ sgsn_via_proxy VARCHAR</span><br><span> );</span><br><span> </span><br><span> CREATE TABLE subscriber_apn (</span><br><span>@@ -78,4 +83,4 @@</span><br><span> </span><br><span> -- Set HLR database schema version number</span><br><span> -- Note: This constant is currently duplicated in src/db.c and must be kept in sync!</span><br><span style="color: hsl(0, 100%, 40%);">-PRAGMA user_version = 4;</span><br><span style="color: hsl(120, 100%, 40%);">+PRAGMA user_version = 5;</span><br><span>diff --git a/src/db.c b/src/db.c</span><br><span>index e38bdaa..3cbd9c9 100644</span><br><span>--- a/src/db.c</span><br><span>+++ b/src/db.c</span><br><span>@@ -30,7 +30,7 @@</span><br><span> #include "db_bootstrap.h"</span><br><span> </span><br><span> /* This constant is currently duplicated in sql/hlr.sql and must be kept in sync! */</span><br><span style="color: hsl(0, 100%, 40%);">-#define CURRENT_SCHEMA_VERSION 4</span><br><span style="color: hsl(120, 100%, 40%);">+#define CURRENT_SCHEMA_VERSION 5</span><br><span> </span><br><span> #define SEL_COLUMNS \</span><br><span> "id," \</span><br><span>@@ -48,15 +48,17 @@</span><br><span> "ms_purged_cs," \</span><br><span> "ms_purged_ps," \</span><br><span> "last_lu_seen," \</span><br><span style="color: hsl(0, 100%, 40%);">- "last_lu_seen_ps" \</span><br><span style="color: hsl(120, 100%, 40%);">+ "last_lu_seen_ps," \</span><br><span style="color: hsl(120, 100%, 40%);">+ "vlr_via_proxy," \</span><br><span style="color: hsl(120, 100%, 40%);">+ "sgsn_via_proxy"</span><br><span> </span><br><span> static const char *stmt_sql[] = {</span><br><span> [DB_STMT_SEL_BY_IMSI] = "SELECT " SEL_COLUMNS " FROM subscriber WHERE imsi = ?",</span><br><span> [DB_STMT_SEL_BY_MSISDN] = "SELECT " SEL_COLUMNS " FROM subscriber WHERE msisdn = ?",</span><br><span> [DB_STMT_SEL_BY_ID] = "SELECT " SEL_COLUMNS " FROM subscriber WHERE id = ?",</span><br><span> [DB_STMT_SEL_BY_IMEI] = "SELECT " SEL_COLUMNS " FROM subscriber WHERE imei = ?",</span><br><span style="color: hsl(0, 100%, 40%);">- [DB_STMT_UPD_VLR_BY_ID] = "UPDATE subscriber SET vlr_number = $number WHERE id = $subscriber_id",</span><br><span style="color: hsl(0, 100%, 40%);">- [DB_STMT_UPD_SGSN_BY_ID] = "UPDATE subscriber SET sgsn_number = $number WHERE id = $subscriber_id",</span><br><span style="color: hsl(120, 100%, 40%);">+ [DB_STMT_UPD_VLR_BY_ID] = "UPDATE subscriber SET vlr_number = $number, vlr_via_proxy = $proxy WHERE id = $subscriber_id",</span><br><span style="color: hsl(120, 100%, 40%);">+ [DB_STMT_UPD_SGSN_BY_ID] = "UPDATE subscriber SET sgsn_number = $number, sgsn_via_proxy = $proxy WHERE id = $subscriber_id",</span><br><span> [DB_STMT_UPD_IMEI_BY_IMSI] = "UPDATE subscriber SET imei = $imei WHERE imsi = $imsi",</span><br><span> [DB_STMT_AUC_BY_IMSI] =</span><br><span> "SELECT id, algo_id_2g, ki, algo_id_3g, k, op, opc, sqn, ind_bitlen"</span><br><span>@@ -185,6 +187,25 @@</span><br><span> return true;</span><br><span> }</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+bool db_bind_null(sqlite3_stmt *stmt, const char *param_name)</span><br><span style="color: hsl(120, 100%, 40%);">+{</span><br><span style="color: hsl(120, 100%, 40%);">+ int rc;</span><br><span style="color: hsl(120, 100%, 40%);">+ int idx = param_name ? sqlite3_bind_parameter_index(stmt, param_name) : 1;</span><br><span style="color: hsl(120, 100%, 40%);">+ if (idx < 1) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DDB, LOGL_ERROR, "Error composing SQL, cannot bind parameter '%s'\n",</span><br><span style="color: hsl(120, 100%, 40%);">+ param_name);</span><br><span style="color: hsl(120, 100%, 40%);">+ return false;</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_bind_null(stmt, idx);</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, "Error binding NULL to SQL parameter %s: %d\n",</span><br><span style="color: hsl(120, 100%, 40%);">+ param_name ? param_name : "#1", rc);</span><br><span style="color: hsl(120, 100%, 40%);">+ db_remove_reset(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ return false;</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span style="color: hsl(120, 100%, 40%);">+ return true;</span><br><span style="color: hsl(120, 100%, 40%);">+}</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span> void db_close(struct db_context *dbc)</span><br><span> {</span><br><span> unsigned int i;</span><br><span>@@ -443,12 +464,30 @@</span><br><span> return rc;</span><br><span> }</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+static int db_upgrade_v5(struct db_context *dbc)</span><br><span style="color: hsl(120, 100%, 40%);">+{</span><br><span style="color: hsl(120, 100%, 40%);">+ int rc;</span><br><span style="color: hsl(120, 100%, 40%);">+ const char *statements[] = {</span><br><span style="color: hsl(120, 100%, 40%);">+ "ALTER TABLE subscriber ADD COLUMN vlr_via_proxy VARCHAR",</span><br><span style="color: hsl(120, 100%, 40%);">+ "ALTER TABLE subscriber ADD COLUMN sgsn_via_proxy VARCHAR",</span><br><span style="color: hsl(120, 100%, 40%);">+ "PRAGMA user_version = 5",</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%);">+ rc = db_run_statements(dbc, statements, ARRAY_SIZE(statements));</span><br><span style="color: hsl(120, 100%, 40%);">+ if (rc != SQLITE_DONE) {</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DDB, LOGL_ERROR, "Unable to update HLR database schema to version 5\n");</span><br><span style="color: hsl(120, 100%, 40%);">+ return rc;</span><br><span style="color: hsl(120, 100%, 40%);">+ }</span><br><span style="color: hsl(120, 100%, 40%);">+ return rc;</span><br><span style="color: hsl(120, 100%, 40%);">+}</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span> typedef int (*db_upgrade_func_t)(struct db_context *dbc);</span><br><span> static db_upgrade_func_t db_upgrade_path[] = {</span><br><span> db_upgrade_v1,</span><br><span> db_upgrade_v2,</span><br><span> db_upgrade_v3,</span><br><span> db_upgrade_v4,</span><br><span style="color: hsl(120, 100%, 40%);">+ db_upgrade_v5,</span><br><span> };</span><br><span> </span><br><span> static int db_get_user_version(struct db_context *dbc)</span><br><span>diff --git a/src/db_hlr.c b/src/db_hlr.c</span><br><span>index 0f9b367..3255dd9 100644</span><br><span>--- a/src/db_hlr.c</span><br><span>+++ b/src/db_hlr.c</span><br><span>@@ -505,6 +505,8 @@</span><br><span> subscr->imsi, "CS");</span><br><span> parse_last_lu_seen(&subscr->last_lu_seen_ps, (const char *)sqlite3_column_text(stmt, 15),</span><br><span> subscr->imsi, "PS");</span><br><span style="color: hsl(120, 100%, 40%);">+ copy_sqlite3_text_to_gt(&subscr->vlr_via_proxy, stmt, 16);</span><br><span style="color: hsl(120, 100%, 40%);">+ copy_sqlite3_text_to_gt(&subscr->sgsn_via_proxy, stmt, 17);</span><br><span> </span><br><span> out:</span><br><span> db_remove_reset(stmt);</span><br><span>@@ -750,6 +752,14 @@</span><br><span> if (!db_bind_text(stmt, "$number", (char*)vlr_name->val))</span><br><span> return -EIO;</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+ if (via_proxy && via_proxy->len) {</span><br><span style="color: hsl(120, 100%, 40%);">+ if (!db_bind_text(stmt, "$proxy", (char*)via_proxy->val))</span><br><span style="color: hsl(120, 100%, 40%);">+ return -EIO;</span><br><span style="color: hsl(120, 100%, 40%);">+ } else {</span><br><span style="color: hsl(120, 100%, 40%);">+ if (!db_bind_null(stmt, "$proxy"))</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%);">+</span><br><span> /* execute the statement */</span><br><span> rc = sqlite3_step(stmt);</span><br><span> if (rc != SQLITE_DONE) {</span><br><span>diff --git a/tests/db_upgrade/db_upgrade_test.ok b/tests/db_upgrade/db_upgrade_test.ok</span><br><span>index 67d0006..2bc6a39 100644</span><br><span>--- a/tests/db_upgrade/db_upgrade_test.ok</span><br><span>+++ b/tests/db_upgrade/db_upgrade_test.ok</span><br><span>@@ -84,6 +84,7 @@</span><br><span> DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 2</span><br><span> DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 3</span><br><span> DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 4</span><br><span style="color: hsl(120, 100%, 40%);">+DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 5</span><br><span> DMAIN Cmdline option --db-check: Database was opened successfully, quitting.</span><br><span> </span><br><span> Resulting db:</span><br><span>@@ -137,17 +138,19 @@</span><br><span> periodic_rau_tau_tmr|INTEGER|0||0</span><br><span> sgsn_address|VARCHAR|0||0</span><br><span> sgsn_number|VARCHAR(15)|0||0</span><br><span style="color: hsl(120, 100%, 40%);">+sgsn_via_proxy|VARCHAR|0||0</span><br><span> smsc_number|VARCHAR(15)|0||0</span><br><span> vlr_number|VARCHAR(15)|0||0</span><br><span style="color: hsl(120, 100%, 40%);">+vlr_via_proxy|VARCHAR|0||0</span><br><span> </span><br><span> Table subscriber contents:</span><br><span style="color: hsl(0, 100%, 40%);">-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</span><br><span style="color: hsl(0, 100%, 40%);">-||1|||123456789012345||||0|0||098765432109876|1|1||||||MSC-1</span><br><span style="color: hsl(0, 100%, 40%);">-||2|||111111111||||1|0|||1|1||||||</span><br><span style="color: hsl(0, 100%, 40%);">-||3|||222222222||||0|1||22222|1|1||||||</span><br><span style="color: hsl(0, 100%, 40%);">-||4|||333333||||0|0||3|0|1||||||</span><br><span style="color: hsl(0, 100%, 40%);">-||5|||444444444444444||||0|0||4444|1|0||||||</span><br><span style="color: hsl(0, 100%, 40%);">-||6|||5555555||||0|0||55555555555555|0|0||||||</span><br><span style="color: hsl(120, 100%, 40%);">+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|sgsn_via_proxy|smsc_number|vlr_number|vlr_via_proxy</span><br><span style="color: hsl(120, 100%, 40%);">+||1|||123456789012345||||0|0||098765432109876|1|1|||||||MSC-1|</span><br><span style="color: hsl(120, 100%, 40%);">+||2|||111111111||||1|0|||1|1||||||||</span><br><span style="color: hsl(120, 100%, 40%);">+||3|||222222222||||0|1||22222|1|1||||||||</span><br><span style="color: hsl(120, 100%, 40%);">+||4|||333333||||0|0||3|0|1||||||||</span><br><span style="color: hsl(120, 100%, 40%);">+||5|||444444444444444||||0|0||4444|1|0||||||||</span><br><span style="color: hsl(120, 100%, 40%);">+||6|||5555555||||0|0||55555555555555|0|0||||||||</span><br><span> </span><br><span> Table: subscriber_apn</span><br><span> name|type|notnull|dflt_value|pk</span><br><span>@@ -168,5 +171,5 @@</span><br><span> rc = 0</span><br><span> DMAIN hlr starting</span><br><span> DDB using database: <PATH>test.db</span><br><span style="color: hsl(0, 100%, 40%);">-DDB Database <PATH>test.db' has HLR DB schema version 4</span><br><span style="color: hsl(120, 100%, 40%);">+DDB Database <PATH>test.db' has HLR DB schema version 5</span><br><span> DMAIN Cmdline option --db-check: Database was opened successfully, quitting.</span><br><span></span><br></pre><p>To view, visit <a href="https://gerrit.osmocom.org/c/osmo-hlr/+/16207">change 16207</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/+/16207"/><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: I98c7b3870559ede84adf56e4bf111f53c7487745 </div>
<div style="display:none"> Gerrit-Change-Number: 16207 </div>
<div style="display:none"> Gerrit-PatchSet: 1 </div>
<div style="display:none"> Gerrit-Owner: neels <nhofmeyr@sysmocom.de> </div>
<div style="display:none"> Gerrit-MessageType: newchange </div>