<p>Stefan Sperling has uploaded this change for <strong>review</strong>.</p><p><a href="https://gerrit.osmocom.org/11898">View Change</a></p><pre style="font-family: monospace,monospace; white-space: pre-wrap;">add database schema versioning to the HLR database<br><br>This introduces a new 'meta' table for meta-data, and adds the first<br>meta data item 'revision' which indicates the database schema version.<br>The present schema is now known as 'version 0' and automatically<br>upgraded to schema version 1 when a database is opened.<br><br>Furthermore, introduce a new column to the subscriber table which is<br>intended to track the timestamp of the last LU received from a given<br>subscriber.<br><br>Change-Id: I8aeaa9a404b622657cbc7138106f38aa6ad8d01b<br>Related: OS#2838<br>---<br>M sql/hlr.sql<br>M src/db.c<br>M src/db.h<br>3 files changed, 103 insertions(+), 1 deletion(-)<br><br></pre><pre style="font-family: monospace,monospace; white-space: pre-wrap;">git pull ssh://gerrit.osmocom.org:29418/osmo-hlr refs/changes/98/11898/1</pre><pre style="font-family: monospace,monospace; white-space: pre-wrap;"><span>diff --git a/sql/hlr.sql b/sql/hlr.sql</span><br><span>index 80eb3e5..6a08e6f 100644</span><br><span>--- a/sql/hlr.sql</span><br><span>+++ b/sql/hlr.sql</span><br><span>@@ -1,3 +1,9 @@</span><br><span style="color: hsl(120, 100%, 40%);">+CREATE TABLE IF NOT EXISTS meta (</span><br><span style="color: hsl(120, 100%, 40%);">+ id INTEGER PRIMARY KEY AUTOINCREMENT,</span><br><span style="color: hsl(120, 100%, 40%);">+ key TEXT UNIQUE NOT NULL,</span><br><span style="color: hsl(120, 100%, 40%);">+ value TEXT NOT NULL</span><br><span style="color: hsl(120, 100%, 40%);">+);</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span> CREATE TABLE IF NOT EXISTS subscriber (</span><br><span> -- OsmoHLR's DB scheme is modelled roughly after TS 23.008 version 13.3.0</span><br><span> id INTEGER PRIMARY KEY,</span><br><span>@@ -37,6 +43,9 @@</span><br><span> ms_purged_cs BOOLEAN NOT NULL DEFAULT 0,</span><br><span> -- Chapter 2.7.6</span><br><span> ms_purged_ps BOOLEAN NOT NULL DEFAULT 0</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ -- Further fields added on the fly during DB upgrades:</span><br><span style="color: hsl(120, 100%, 40%);">+ -- last_lu_seen TIMESTAMP default NULL</span><br><span> );</span><br><span> </span><br><span> CREATE TABLE IF NOT EXISTS subscriber_apn (</span><br><span>diff --git a/src/db.c b/src/db.c</span><br><span>index 4b0577f..4a328d4 100644</span><br><span>--- a/src/db.c</span><br><span>+++ b/src/db.c</span><br><span>@@ -27,6 +27,14 @@</span><br><span> #include "db.h"</span><br><span> #include "db_bootstrap.h"</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+/*</span><br><span style="color: hsl(120, 100%, 40%);">+ * Database schema version history:</span><br><span style="color: hsl(120, 100%, 40%);">+ * Version 0: Did not have a 'meta' table.</span><br><span style="color: hsl(120, 100%, 40%);">+ * Version 1: Added the 'meta' table with schema version 1, and</span><br><span style="color: hsl(120, 100%, 40%);">+ * added the 'last_lu_seen' column to table 'subscriber'.</span><br><span style="color: hsl(120, 100%, 40%);">+ */</span><br><span style="color: hsl(120, 100%, 40%);">+#define CURRENT_SCHEMA_VERSION 1</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span> #define SEL_COLUMNS \</span><br><span> "id," \</span><br><span> "imsi," \</span><br><span>@@ -40,9 +48,12 @@</span><br><span> "nam_ps," \</span><br><span> "lmsi," \</span><br><span> "ms_purged_cs," \</span><br><span style="color: hsl(0, 100%, 40%);">- "ms_purged_ps"</span><br><span style="color: hsl(120, 100%, 40%);">+ "ms_purged_ps," \</span><br><span style="color: hsl(120, 100%, 40%);">+ "last_lu_seen"</span><br><span> </span><br><span> static const char *stmt_sql[] = {</span><br><span style="color: hsl(120, 100%, 40%);">+ [DB_STMT_ADD_SCHEMA_VERSION] = "INSERT INTO meta (key, value) VALUES ('revision', 1)",</span><br><span style="color: hsl(120, 100%, 40%);">+ [DB_STMT_SEL_SCHEMA_VERSION] = "SELECT value FROM meta WHERE key = 'revision'",</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>@@ -70,6 +81,7 @@</span><br><span> "INSERT INTO auc_3g (subscriber_id, algo_id_3g, k, op, opc, ind_bitlen)"</span><br><span> " VALUES($subscriber_id, $algo_id_3g, $k, $op, $opc, $ind_bitlen)",</span><br><span> [DB_STMT_AUC_3G_DELETE] = "DELETE FROM auc_3g WHERE subscriber_id = $subscriber_id",</span><br><span style="color: hsl(120, 100%, 40%);">+ [DB_STMT_UPD_LAST_LU_SEEN] = "UPDATE subscriber SET last_lu_seen = $val WHERE imsi = $imsi",</span><br><span> };</span><br><span> </span><br><span> static void sql3_error_log_cb(void *arg, int err_code, const char *msg)</span><br><span>@@ -220,6 +232,64 @@</span><br><span> return SQLITE_OK;</span><br><span> }</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+static int</span><br><span style="color: hsl(120, 100%, 40%);">+read_schema_version(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%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_prepare_v2(dbc->db, stmt_sql[DB_STMT_SEL_SCHEMA_VERSION], -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", stmt_sql[DB_STMT_SEL_SCHEMA_VERSION]);</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_step(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (rc == SQLITE_ROW) {</span><br><span style="color: hsl(120, 100%, 40%);">+ dbc->schema_version = sqlite3_column_int(stmt, 0);</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = sqlite3_step(stmt);</span><br><span style="color: hsl(120, 100%, 40%);">+ } else {</span><br><span style="color: hsl(120, 100%, 40%);">+ /* Assume 'meta' table does not exist, i.e. this is schema version zero. */</span><br><span style="color: hsl(120, 100%, 40%);">+ dbc->schema_version = 0;</span><br><span style="color: hsl(120, 100%, 40%);">+ rc = SQLITE_DONE;</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%);">+ db_remove_reset(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 style="color: hsl(120, 100%, 40%);">+static int</span><br><span style="color: hsl(120, 100%, 40%);">+db_update_v1(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 ADD COLUMN last_lu_seen TIMESTAMP default NULL";</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%);">+ 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 %d\n", 1);</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, stmt_sql[DB_STMT_ADD_SCHEMA_VERSION], -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", stmt_sql[DB_STMT_ADD_SCHEMA_VERSION]);</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 %d\n", 1);</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%);">+ return rc;</span><br><span style="color: hsl(120, 100%, 40%);">+}</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span> struct db_context *db_open(void *ctx, const char *fname, bool enable_sqlite_logging)</span><br><span> {</span><br><span> struct db_context *dbc = talloc_zero(ctx, struct db_context);</span><br><span>@@ -281,6 +351,25 @@</span><br><span> goto out_free;</span><br><span> }</span><br><span> </span><br><span style="color: hsl(120, 100%, 40%);">+ rc = read_schema_version(dbc);</span><br><span style="color: hsl(120, 100%, 40%);">+ if (rc != SQLITE_DONE)</span><br><span style="color: hsl(120, 100%, 40%);">+ goto out_free;</span><br><span style="color: hsl(120, 100%, 40%);">+ switch (dbc->schema_version) {</span><br><span style="color: hsl(120, 100%, 40%);">+ case 0:</span><br><span style="color: hsl(120, 100%, 40%);">+ if (db_update_v1(dbc) != SQLITE_DONE)</span><br><span style="color: hsl(120, 100%, 40%);">+ break;</span><br><span style="color: hsl(120, 100%, 40%);">+ /* fall through */</span><br><span style="color: hsl(120, 100%, 40%);">+ case 1:</span><br><span style="color: hsl(120, 100%, 40%);">+ /* fall through */</span><br><span style="color: hsl(120, 100%, 40%);">+ /* case N: */</span><br><span style="color: hsl(120, 100%, 40%);">+ /* fall through */</span><br><span style="color: hsl(120, 100%, 40%);">+ /* case N+1: */</span><br><span style="color: hsl(120, 100%, 40%);">+ break;</span><br><span style="color: hsl(120, 100%, 40%);">+ default:</span><br><span style="color: hsl(120, 100%, 40%);">+ LOGP(DDB, LOGL_ERROR, "Unknown HLR database schema version: '%d'\n", dbc->schema_version);</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> /* prepare all SQL statements */</span><br><span> for (i = 0; i < ARRAY_SIZE(dbc->stmt); i++) {</span><br><span> rc = sqlite3_prepare_v2(dbc->db, stmt_sql[i], -1,</span><br><span>diff --git a/src/db.h b/src/db.h</span><br><span>index 956b5ed..c688249 100644</span><br><span>--- a/src/db.h</span><br><span>+++ b/src/db.h</span><br><span>@@ -6,6 +6,8 @@</span><br><span> struct hlr;</span><br><span> </span><br><span> enum stmt_idx {</span><br><span style="color: hsl(120, 100%, 40%);">+ DB_STMT_ADD_SCHEMA_VERSION,</span><br><span style="color: hsl(120, 100%, 40%);">+ DB_STMT_SEL_SCHEMA_VERSION,</span><br><span> DB_STMT_SEL_BY_IMSI,</span><br><span> DB_STMT_SEL_BY_MSISDN,</span><br><span> DB_STMT_SEL_BY_ID,</span><br><span>@@ -24,6 +26,7 @@</span><br><span> DB_STMT_AUC_2G_DELETE,</span><br><span> DB_STMT_AUC_3G_INSERT,</span><br><span> DB_STMT_AUC_3G_DELETE,</span><br><span style="color: hsl(120, 100%, 40%);">+ DB_STMT_UPD_LAST_LU_SEEN,</span><br><span> _NUM_DB_STMT</span><br><span> };</span><br><span> </span><br><span>@@ -31,6 +34,7 @@</span><br><span> char *fname;</span><br><span> sqlite3 *db;</span><br><span> sqlite3_stmt *stmt[_NUM_DB_STMT];</span><br><span style="color: hsl(120, 100%, 40%);">+ int schema_version;</span><br><span> };</span><br><span> </span><br><span> void db_remove_reset(sqlite3_stmt *stmt);</span><br><span></span><br></pre><p>To view, visit <a href="https://gerrit.osmocom.org/11898">change 11898</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/11898"/><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-MessageType: newchange </div>
<div style="display:none"> Gerrit-Change-Id: I8aeaa9a404b622657cbc7138106f38aa6ad8d01b </div>
<div style="display:none"> Gerrit-Change-Number: 11898 </div>
<div style="display:none"> Gerrit-PatchSet: 1 </div>
<div style="display:none"> Gerrit-Owner: Stefan Sperling <ssperling@sysmocom.de> </div>