Change in osmo-hlr[master]: add database schema versioning to the HLR database

This is merely a historical archive of years 2008-2021, before the migration to mailman3.

A maintained and still updated list archive can be found at https://lists.osmocom.org/hyperkitty/list/gerrit-log@lists.osmocom.org/.

Stefan Sperling gerrit-no-reply at lists.osmocom.org
Thu Nov 22 18:15:04 UTC 2018


Stefan Sperling has uploaded this change for review. ( https://gerrit.osmocom.org/11898


Change subject: add database schema versioning to the HLR database
......................................................................

add database schema versioning to the HLR database

This introduces a new 'meta' table for meta-data, and adds the first
meta data item 'revision' which indicates the database schema version.
The present schema is now known as 'version 0' and automatically
upgraded to schema version 1 when a database is opened.

Furthermore, introduce a new column to the subscriber table which is
intended to track the timestamp of the last LU received from a given
subscriber.

Change-Id: I8aeaa9a404b622657cbc7138106f38aa6ad8d01b
Related: OS#2838
---
M sql/hlr.sql
M src/db.c
M src/db.h
3 files changed, 103 insertions(+), 1 deletion(-)



  git pull ssh://gerrit.osmocom.org:29418/osmo-hlr refs/changes/98/11898/1

diff --git a/sql/hlr.sql b/sql/hlr.sql
index 80eb3e5..6a08e6f 100644
--- a/sql/hlr.sql
+++ b/sql/hlr.sql
@@ -1,3 +1,9 @@
+CREATE TABLE IF NOT EXISTS meta (
+	id	INTEGER PRIMARY KEY AUTOINCREMENT,
+	key	TEXT UNIQUE NOT NULL,
+	value	TEXT NOT NULL
+);
+
 CREATE TABLE IF NOT EXISTS subscriber (
 -- OsmoHLR's DB scheme is modelled roughly after TS 23.008 version 13.3.0
 	id		INTEGER PRIMARY KEY,
@@ -37,6 +43,9 @@
 	ms_purged_cs	BOOLEAN NOT NULL DEFAULT 0,
 	-- Chapter 2.7.6
 	ms_purged_ps	BOOLEAN NOT NULL DEFAULT 0
+
+	-- Further fields added on the fly during DB upgrades:
+	-- last_lu_seen TIMESTAMP default NULL
 );
 
 CREATE TABLE IF NOT EXISTS subscriber_apn (
diff --git a/src/db.c b/src/db.c
index 4b0577f..4a328d4 100644
--- a/src/db.c
+++ b/src/db.c
@@ -27,6 +27,14 @@
 #include "db.h"
 #include "db_bootstrap.h"
 
+/*
+ * Database schema version history:
+ * Version 0: Did not have a 'meta' table.
+ * Version 1: Added the 'meta' table with schema version 1, and
+ *            added the 'last_lu_seen' column to table 'subscriber'.
+ */
+#define CURRENT_SCHEMA_VERSION 1
+
 #define SEL_COLUMNS \
 	"id," \
 	"imsi," \
@@ -40,9 +48,12 @@
 	"nam_ps," \
 	"lmsi," \
 	"ms_purged_cs," \
-	"ms_purged_ps"
+	"ms_purged_ps," \
+	"last_lu_seen"
 
 static const char *stmt_sql[] = {
+	[DB_STMT_ADD_SCHEMA_VERSION] = "INSERT INTO meta (key, value) VALUES ('revision', 1)",
+	[DB_STMT_SEL_SCHEMA_VERSION] = "SELECT value FROM meta WHERE key = 'revision'",
 	[DB_STMT_SEL_BY_IMSI] = "SELECT " SEL_COLUMNS " FROM subscriber WHERE imsi = ?",
 	[DB_STMT_SEL_BY_MSISDN] = "SELECT " SEL_COLUMNS " FROM subscriber WHERE msisdn = ?",
 	[DB_STMT_SEL_BY_ID] = "SELECT " SEL_COLUMNS " FROM subscriber WHERE id = ?",
@@ -70,6 +81,7 @@
 		"INSERT INTO auc_3g (subscriber_id, algo_id_3g, k, op, opc, ind_bitlen)"
 		" VALUES($subscriber_id, $algo_id_3g, $k, $op, $opc, $ind_bitlen)",
 	[DB_STMT_AUC_3G_DELETE] = "DELETE FROM auc_3g WHERE subscriber_id = $subscriber_id",
+	[DB_STMT_UPD_LAST_LU_SEEN] = "UPDATE subscriber SET last_lu_seen = $val WHERE imsi = $imsi",
 };
 
 static void sql3_error_log_cb(void *arg, int err_code, const char *msg)
@@ -220,6 +232,64 @@
 	return SQLITE_OK;
 }
 
+static int
+read_schema_version(struct db_context *dbc)
+{
+	sqlite3_stmt *stmt;
+	int rc;
+
+	rc = sqlite3_prepare_v2(dbc->db, stmt_sql[DB_STMT_SEL_SCHEMA_VERSION], -1, &stmt, NULL);
+	if (rc != SQLITE_OK) {
+		LOGP(DDB, LOGL_ERROR, "Unable to prepare SQL statement '%s'\n", stmt_sql[DB_STMT_SEL_SCHEMA_VERSION]);
+		return rc;
+	}
+
+	rc = sqlite3_step(stmt);
+	if (rc == SQLITE_ROW) {
+		dbc->schema_version = sqlite3_column_int(stmt, 0);
+		rc = sqlite3_step(stmt);
+	} else {
+		/* Assume 'meta' table does not exist, i.e. this is schema version zero. */
+		dbc->schema_version = 0;
+		rc = SQLITE_DONE;
+	}
+
+	db_remove_reset(stmt);
+	return rc;
+}
+
+static int
+db_update_v1(struct db_context *dbc)
+{
+	sqlite3_stmt *stmt;
+	int rc;
+	const char *update_stmt_sql = "ALTER TABLE subscriber ADD COLUMN last_lu_seen TIMESTAMP default NULL";
+
+	rc = sqlite3_prepare_v2(dbc->db, update_stmt_sql, -1, &stmt, NULL);
+	if (rc != SQLITE_OK) {
+		LOGP(DDB, LOGL_ERROR, "Unable to prepare SQL statement '%s'\n", update_stmt_sql);
+		return rc;
+	}
+	rc = sqlite3_step(stmt);
+	db_remove_reset(stmt);
+	if (rc != SQLITE_DONE) {
+		LOGP(DDB, LOGL_ERROR, "Unable to update HLR database schema to version %d\n", 1);
+		return rc;
+	}
+
+	rc = sqlite3_prepare_v2(dbc->db, stmt_sql[DB_STMT_ADD_SCHEMA_VERSION], -1, &stmt, NULL);
+	if (rc != SQLITE_OK) {
+		LOGP(DDB, LOGL_ERROR, "Unable to prepare SQL statement '%s'\n", stmt_sql[DB_STMT_ADD_SCHEMA_VERSION]);
+		return rc;
+	}
+	rc = sqlite3_step(stmt);
+	if (rc != SQLITE_DONE)
+		LOGP(DDB, LOGL_ERROR, "Unable to update HLR database schema to version %d\n", 1);
+
+	db_remove_reset(stmt);
+	return rc;
+}
+
 struct db_context *db_open(void *ctx, const char *fname, bool enable_sqlite_logging)
 {
 	struct db_context *dbc = talloc_zero(ctx, struct db_context);
@@ -281,6 +351,25 @@
 		goto out_free;
 	}
 
+	rc = read_schema_version(dbc);
+	if (rc != SQLITE_DONE)
+		goto out_free;
+	switch (dbc->schema_version) {
+	case 0:
+		if (db_update_v1(dbc) != SQLITE_DONE)
+			break;
+		/* fall through */
+	case 1:
+		/* fall through */
+	/* case N: */
+		/* fall through */
+	/* case N+1: */
+		break;
+	default:
+		LOGP(DDB, LOGL_ERROR, "Unknown HLR database schema version: '%d'\n", dbc->schema_version);
+		goto out_free;
+	}
+
 	/* prepare all SQL statements */
 	for (i = 0; i < ARRAY_SIZE(dbc->stmt); i++) {
 		rc = sqlite3_prepare_v2(dbc->db, stmt_sql[i], -1,
diff --git a/src/db.h b/src/db.h
index 956b5ed..c688249 100644
--- a/src/db.h
+++ b/src/db.h
@@ -6,6 +6,8 @@
 struct hlr;
 
 enum stmt_idx {
+	DB_STMT_ADD_SCHEMA_VERSION,
+	DB_STMT_SEL_SCHEMA_VERSION,
 	DB_STMT_SEL_BY_IMSI,
 	DB_STMT_SEL_BY_MSISDN,
 	DB_STMT_SEL_BY_ID,
@@ -24,6 +26,7 @@
 	DB_STMT_AUC_2G_DELETE,
 	DB_STMT_AUC_3G_INSERT,
 	DB_STMT_AUC_3G_DELETE,
+	DB_STMT_UPD_LAST_LU_SEEN,
 	_NUM_DB_STMT
 };
 
@@ -31,6 +34,7 @@
 	char *fname;
 	sqlite3 *db;
 	sqlite3_stmt *stmt[_NUM_DB_STMT];
+	int schema_version;
 };
 
 void db_remove_reset(sqlite3_stmt *stmt);

-- 
To view, visit https://gerrit.osmocom.org/11898
To unsubscribe, or for help writing mail filters, visit https://gerrit.osmocom.org/settings

Gerrit-Project: osmo-hlr
Gerrit-Branch: master
Gerrit-MessageType: newchange
Gerrit-Change-Id: I8aeaa9a404b622657cbc7138106f38aa6ad8d01b
Gerrit-Change-Number: 11898
Gerrit-PatchSet: 1
Gerrit-Owner: Stefan Sperling <ssperling at sysmocom.de>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osmocom.org/pipermail/gerrit-log/attachments/20181122/84f24587/attachment.htm>


More information about the gerrit-log mailing list