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.orgStefan 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>