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>