<p>neels has uploaded this change for <strong>review</strong>.</p><p><a href="https://gerrit.osmocom.org/c/osmo-hlr/+/15914">View Change</a></p><pre style="font-family: monospace,monospace; white-space: pre-wrap;">hlr db schema 3: hlr_number -> msc_number<br><br>The osmo-hlr DB schema indicates a hlr_number column and references it as 3GPP<br>TS 23.008 chapter 2.4.6. However, chapter 2.4.6 refers to the "MSC number",<br>while the "HLR number" is chapter 2.4.7.<br><br>Taking a closer look, 2.4.6 says "The MSC number is [...] stored in the HLR",<br>while 2.4.7 says "The HLR number may be stored in the VLR". As quite obvious,<br>the HLR does not store the HLR number. This was a typo from the start.<br><br>The osmo-hlr code base so far does not use the hlr_number column at all, so we<br>get away with renaming the column without any effects on the code base.<br>However, let's rather make this a new schema version to be safe.<br><br>Change-Id: I527e8627b24b79f3e9eec32675c7f5a3a6d25440<br>---<br>M doc/manuals/chapters/subscribers.adoc<br>M sql/hlr.sql<br>M src/db.c<br>M tests/db_upgrade/db_upgrade_test.ok<br>4 files changed, 50 insertions(+), 7 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/14/15914/1</pre><pre style="font-family: monospace,monospace; white-space: pre-wrap;"><span>diff --git a/doc/manuals/chapters/subscribers.adoc b/doc/manuals/chapters/subscribers.adoc</span><br><span>index e09e99a..ab41b0f 100644</span><br><span>--- a/doc/manuals/chapters/subscribers.adoc</span><br><span>+++ b/doc/manuals/chapters/subscribers.adoc</span><br><span>@@ -52,7 +52,7 @@</span><br><span> |aud3g.ind_bitlen|5|Nr of index bits at lower SQN end</span><br><span> |apn||</span><br><span> |vlr_number||3GPP TS 23.008 chapter 2.4.5</span><br><span style="color: hsl(0, 100%, 40%);">-|hlr_number||3GPP TS 23.008 chapter 2.4.6</span><br><span style="color: hsl(120, 100%, 40%);">+|msc_number||3GPP TS 23.008 chapter 2.4.6</span><br><span> |sgsn_number||3GPP TS 23.008 chapter 2.4.8.1</span><br><span> |sgsn_address||3GPP TS 23.008 chapter 2.13.10</span><br><span> |ggsn_number||3GPP TS 23.008 chapter 2.4.8.2</span><br><span>diff --git a/sql/hlr.sql b/sql/hlr.sql</span><br><span>index 10838f2..a627f0c 100644</span><br><span>--- a/sql/hlr.sql</span><br><span>+++ b/sql/hlr.sql</span><br><span>@@ -12,7 +12,7 @@</span><br><span> -- Chapter 2.4.5</span><br><span> vlr_number VARCHAR(15),</span><br><span> -- Chapter 2.4.6</span><br><span style="color: hsl(0, 100%, 40%);">- hlr_number VARCHAR(15),</span><br><span style="color: hsl(120, 100%, 40%);">+ msc_number VARCHAR(15),</span><br><span> -- Chapter 2.4.8.1</span><br><span> sgsn_number VARCHAR(15),</span><br><span> -- Chapter 2.13.10</span><br><span>@@ -76,4 +76,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 = 2;</span><br><span style="color: hsl(120, 100%, 40%);">+PRAGMA user_version = 3;</span><br><span>diff --git a/src/db.c b/src/db.c</span><br><span>index 31c4ba5..4ae491e 100644</span><br><span>--- a/src/db.c</span><br><span>+++ b/src/db.c</span><br><span>@@ -28,7 +28,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 2</span><br><span style="color: hsl(120, 100%, 40%);">+#define CURRENT_SCHEMA_VERSION 3</span><br><span> </span><br><span> #define SEL_COLUMNS \</span><br><span> "id," \</span><br><span>@@ -329,6 +329,40 @@</span><br><span> return rc;</span><br><span> }</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+static int db_upgrade_v3(struct db_context *dbc)</span><br><span style="color: hsl(120, 100%, 40%);">+{</span><br><span style="color: hsl(120, 100%, 40%);">+ sqlite3_stmt *stmt;</span><br><span style="color: hsl(120, 100%, 40%);">+ int rc;</span><br><span style="color: hsl(120, 100%, 40%);">+ const char *update_stmt_sql = "ALTER TABLE subscriber RENAME COLUMN hlr_number TO msc_number";</span><br><span style="color: hsl(120, 100%, 40%);">+ const char *set_schema_version_sql = "PRAGMA user_version = 3";</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_prepare_v2(dbc->db, update_stmt_sql, -1, &stmt, 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, "Unable to prepare SQL statement '%s'\n", update_stmt_sql);</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%);">+ rc = sqlite3_step(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ db_remove_reset(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ sqlite3_finalize(stmt);</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 3\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%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_prepare_v2(dbc->db, set_schema_version_sql, -1, &stmt, 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, "Unable to prepare SQL statement '%s'\n", set_schema_version_sql);</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%);">+ 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(DDB, LOGL_ERROR, "Unable to update HLR database schema to version 3\n");</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ db_remove_reset(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ sqlite3_finalize(stmt);</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> static int db_get_user_version(struct db_context *dbc)</span><br><span> {</span><br><span> const char *user_version_sql = "PRAGMA user_version";</span><br><span>@@ -459,6 +493,15 @@</span><br><span> }</span><br><span> version = 2;</span><br><span> /* fall through */</span><br><span style="color: hsl(120, 100%, 40%);">+ case 2:</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = db_upgrade_v3(dbc);</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, "Failed to upgrade HLR DB schema to version 3: (rc=%d) %s\n",</span><br><span style="color: hsl(120, 100%, 40%);">+ rc, sqlite3_errmsg(dbc->db));</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%);">+ version = 3;</span><br><span style="color: hsl(120, 100%, 40%);">+ /* fall through */</span><br><span> /* case N: ... */</span><br><span> default:</span><br><span> break;</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 7fa1258..1a1d1b8 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>@@ -87,7 +87,7 @@</span><br><span> DMAIN hlr starting</span><br><span> DDB using database: <PATH>test.db</span><br><span> DDB Database <PATH>test.db' has HLR DB schema version 0</span><br><span style="color: hsl(0, 100%, 40%);">-DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 2</span><br><span style="color: hsl(120, 100%, 40%);">+DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 3</span><br><span> DMAIN Cmdline option --db-check: Database was opened successfully, quitting.</span><br><span> </span><br><span> Resulting db:</span><br><span>@@ -105,7 +105,7 @@</span><br><span> -- Chapter 2.4.5</span><br><span> vlr_number VARCHAR(15),</span><br><span> -- Chapter 2.4.6</span><br><span style="color: hsl(0, 100%, 40%);">- hlr_number VARCHAR(15),</span><br><span style="color: hsl(120, 100%, 40%);">+ msc_number VARCHAR(15),</span><br><span> -- Chapter 2.4.8.1</span><br><span> sgsn_number VARCHAR(15),</span><br><span> -- Chapter 2.13.10</span><br><span>@@ -176,5 +176,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 2</span><br><span style="color: hsl(120, 100%, 40%);">+DDB Database <PATH>test.db' has HLR DB schema version 3</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/+/15914">change 15914</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/+/15914"/><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: I527e8627b24b79f3e9eec32675c7f5a3a6d25440 </div>
<div style="display:none"> Gerrit-Change-Number: 15914 </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>