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/.
laforge gerrit-no-reply at lists.osmocom.orglaforge has submitted this change. ( https://gerrit.osmocom.org/c/osmo-hlr/+/15914 ) Change subject: hlr db schema 3: hlr_number -> msc_number ...................................................................... hlr db schema 3: hlr_number -> msc_number The osmo-hlr DB schema indicates a hlr_number column and references it as 3GPP TS 23.008 chapter 2.4.6. However, chapter 2.4.6 refers to the "MSC number", while the "HLR number" is chapter 2.4.7. Taking a closer look, 2.4.6 says "The MSC number is [...] stored in the HLR", while 2.4.7 says "The HLR number may be stored in the VLR". As quite obvious, the HLR does not store the HLR number. This was a typo from the start. The osmo-hlr code base so far does not use the hlr_number column at all, so we get away with renaming the column without any effects on the code base. However, let's rather make this a new schema version to be safe. Change-Id: I527e8627b24b79f3e9eec32675c7f5a3a6d25440 --- M doc/manuals/chapters/subscribers.adoc M sql/hlr.sql M src/db.c M tests/db_upgrade/db_upgrade_test.ok 4 files changed, 157 insertions(+), 14 deletions(-) Approvals: laforge: Looks good to me, but someone else must approve pespin: Looks good to me, approved Jenkins Builder: Verified diff --git a/doc/manuals/chapters/subscribers.adoc b/doc/manuals/chapters/subscribers.adoc index e09e99a..ab41b0f 100644 --- a/doc/manuals/chapters/subscribers.adoc +++ b/doc/manuals/chapters/subscribers.adoc @@ -52,7 +52,7 @@ |aud3g.ind_bitlen|5|Nr of index bits at lower SQN end |apn|| |vlr_number||3GPP TS 23.008 chapter 2.4.5 -|hlr_number||3GPP TS 23.008 chapter 2.4.6 +|msc_number||3GPP TS 23.008 chapter 2.4.6 |sgsn_number||3GPP TS 23.008 chapter 2.4.8.1 |sgsn_address||3GPP TS 23.008 chapter 2.13.10 |ggsn_number||3GPP TS 23.008 chapter 2.4.8.2 diff --git a/sql/hlr.sql b/sql/hlr.sql index c1b0f1a..f8fdc0b 100644 --- a/sql/hlr.sql +++ b/sql/hlr.sql @@ -12,7 +12,7 @@ -- Chapter 2.4.5 vlr_number VARCHAR(15), -- Chapter 2.4.6 - hlr_number VARCHAR(15), + msc_number VARCHAR(15), -- Chapter 2.4.8.1 sgsn_number VARCHAR(15), -- Chapter 2.13.10 @@ -77,4 +77,4 @@ -- Set HLR database schema version number -- Note: This constant is currently duplicated in src/db.c and must be kept in sync! -PRAGMA user_version = 2; +PRAGMA user_version = 3; diff --git a/src/db.c b/src/db.c index 31c4ba5..d2564e6 100644 --- a/src/db.c +++ b/src/db.c @@ -28,7 +28,7 @@ #include "db_bootstrap.h" /* This constant is currently duplicated in sql/hlr.sql and must be kept in sync! */ -#define CURRENT_SCHEMA_VERSION 2 +#define CURRENT_SCHEMA_VERSION 3 #define SEL_COLUMNS \ "id," \ @@ -329,6 +329,140 @@ return rc; } +static int db_upgrade_v3(struct db_context *dbc) +{ + sqlite3_stmt *stmt; + int rc; + + /* A newer SQLite version would allow simply 'ATLER TABLE subscriber RENAME COLUMN hlr_number TO msc_number'. + * This is a really expensive workaround for that in order to cover earlier SQLite versions as well: + * Create a new table with the new column name and copy the data over (https://www.sqlite.org/faq.html#q11). + */ +#define SUBSCR_V3_CREATE \ +"(\n" \ +"-- OsmoHLR's DB scheme is modelled roughly after TS 23.008 version 13.3.0\n" \ +" id INTEGER PRIMARY KEY,\n" \ +" -- Chapter 2.1.1.1\n" \ +" imsi VARCHAR(15) UNIQUE NOT NULL,\n" \ +" -- Chapter 2.1.2\n" \ +" msisdn VARCHAR(15) UNIQUE,\n" \ +" -- Chapter 2.2.3: Most recent / current IMEISV\n" \ +" imeisv VARCHAR,\n" \ +" -- Chapter 2.1.9: Most recent / current IMEI\n" \ +" imei VARCHAR(14),\n" \ +" -- Chapter 2.4.5\n" \ +" vlr_number VARCHAR(15),\n" \ +" -- Chapter 2.4.6\n" \ +" msc_number VARCHAR(15),\n" \ +" -- Chapter 2.4.8.1\n" \ +" sgsn_number VARCHAR(15),\n" \ +" -- Chapter 2.13.10\n" \ +" sgsn_address VARCHAR,\n" \ +" -- Chapter 2.4.8.2\n" \ +" ggsn_number VARCHAR(15),\n" \ +" -- Chapter 2.4.9.2\n" \ +" gmlc_number VARCHAR(15),\n" \ +" -- Chapter 2.4.23\n" \ +" smsc_number VARCHAR(15),\n" \ +" -- Chapter 2.4.24\n" \ +" periodic_lu_tmr INTEGER,\n" \ +" -- Chapter 2.13.115\n" \ +" periodic_rau_tau_tmr INTEGER,\n" \ +" -- Chapter 2.1.1.2: network access mode\n" \ +" nam_cs BOOLEAN NOT NULL DEFAULT 1,\n" \ +" nam_ps BOOLEAN NOT NULL DEFAULT 1,\n" \ +" -- Chapter 2.1.8\n" \ +" lmsi INTEGER,\n" \ + \ +" -- The below purged flags might not even be stored non-volatile,\n" \ +" -- refer to TS 23.012 Chapter 3.6.1.4\n" \ +" -- Chapter 2.7.5\n" \ +" ms_purged_cs BOOLEAN NOT NULL DEFAULT 0,\n" \ +" -- Chapter 2.7.6\n" \ +" ms_purged_ps BOOLEAN NOT NULL DEFAULT 0,\n" \ + \ +" -- Timestamp of last location update seen from subscriber\n" \ +" -- The value is a string which encodes a UTC timestamp in granularity of seconds.\n" \ +" last_lu_seen TIMESTAMP default NULL\n" \ +")\n" + +#define SUBSCR_V2_COLUMN_NAMES \ + "id," \ + "imsi," \ + "msisdn," \ + "imeisv," \ + "imei," \ + "vlr_number," \ + "hlr_number," \ + "sgsn_number," \ + "sgsn_address," \ + "ggsn_number," \ + "gmlc_number," \ + "smsc_number," \ + "periodic_lu_tmr," \ + "periodic_rau_tau_tmr," \ + "nam_cs," \ + "nam_ps," \ + "lmsi," \ + "ms_purged_cs," \ + "ms_purged_ps," \ + "last_lu_seen" + +#define SUBSCR_V3_COLUMN_NAMES \ + "id," \ + "imsi," \ + "msisdn," \ + "imeisv," \ + "imei," \ + "vlr_number," \ + "msc_number," \ + "sgsn_number," \ + "sgsn_address," \ + "ggsn_number," \ + "gmlc_number," \ + "smsc_number," \ + "periodic_lu_tmr," \ + "periodic_rau_tau_tmr," \ + "nam_cs," \ + "nam_ps," \ + "lmsi," \ + "ms_purged_cs," \ + "ms_purged_ps," \ + "last_lu_seen" + + const char *statements[] = { + "BEGIN TRANSACTION", + "CREATE TEMPORARY TABLE subscriber_backup" SUBSCR_V3_CREATE, + "INSERT INTO subscriber_backup SELECT " SUBSCR_V2_COLUMN_NAMES " FROM subscriber", + "DROP TABLE subscriber", + "CREATE TABLE subscriber" SUBSCR_V3_CREATE, + "INSERT INTO subscriber SELECT " SUBSCR_V3_COLUMN_NAMES " FROM subscriber_backup", + "DROP TABLE subscriber_backup", + "COMMIT", + "PRAGMA user_version = 3", + }; + + int i; + for (i = 0; i < ARRAY_SIZE(statements); i++) { + const char *update_stmt_sql = statements[i]; + + 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); + sqlite3_finalize(stmt); + if (rc != SQLITE_DONE) { + LOGP(DDB, LOGL_ERROR, "Unable to update HLR database schema to version 3\n"); + return rc; + } + + } + return rc; +} + static int db_get_user_version(struct db_context *dbc) { const char *user_version_sql = "PRAGMA user_version"; @@ -459,6 +593,15 @@ } version = 2; /* fall through */ + case 2: + rc = db_upgrade_v3(dbc); + if (rc != SQLITE_DONE) { + LOGP(DDB, LOGL_ERROR, "Failed to upgrade HLR DB schema to version 3: (rc=%d) %s\n", + rc, sqlite3_errmsg(dbc->db)); + goto out_free; + } + version = 3; + /* fall through */ /* case N: ... */ default: break; diff --git a/tests/db_upgrade/db_upgrade_test.ok b/tests/db_upgrade/db_upgrade_test.ok index bce3eb4..c1f0f9d 100644 --- a/tests/db_upgrade/db_upgrade_test.ok +++ b/tests/db_upgrade/db_upgrade_test.ok @@ -80,7 +80,7 @@ DMAIN hlr starting DDB using database: <PATH>test.db DDB Database <PATH>test.db' has HLR DB schema version 0 -DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 2 +DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 3 DMAIN Cmdline option --db-check: Database was opened successfully, quitting. Resulting db: @@ -117,7 +117,6 @@ name|type|notnull|dflt_value|pk ggsn_number|VARCHAR(15)|0||0 gmlc_number|VARCHAR(15)|0||0 -hlr_number|VARCHAR(15)|0||0 id|INTEGER|0||1 imei|VARCHAR(14)|0||0 imeisv|VARCHAR|0||0 @@ -126,6 +125,7 @@ lmsi|INTEGER|0||0 ms_purged_cs|BOOLEAN|1|0|0 ms_purged_ps|BOOLEAN|1|0|0 +msc_number|VARCHAR(15)|0||0 msisdn|VARCHAR(15)|0||0 nam_cs|BOOLEAN|1|1|0 nam_ps|BOOLEAN|1|1|0 @@ -137,13 +137,13 @@ vlr_number|VARCHAR(15)|0||0 Table subscriber contents: -ggsn_number|gmlc_number|hlr_number|id|imei|imeisv|imsi|last_lu_seen|lmsi|ms_purged_cs|ms_purged_ps|msisdn|nam_cs|nam_ps|periodic_lu_tmr|periodic_rau_tau_tmr|sgsn_address|sgsn_number|smsc_number|vlr_number -|||1|||123456789012345|||0|0|098765432109876|1|1||||||MSC-1 -|||2|||111111111|||1|0||1|1|||||| -|||3|||222222222|||0|1|22222|1|1|||||| -|||4|||333333|||0|0|3|0|1|||||| -|||5|||444444444444444|||0|0|4444|1|0|||||| -|||6|||5555555|||0|0|55555555555555|0|0|||||| +ggsn_number|gmlc_number|id|imei|imeisv|imsi|last_lu_seen|lmsi|ms_purged_cs|ms_purged_ps|msc_number|msisdn|nam_cs|nam_ps|periodic_lu_tmr|periodic_rau_tau_tmr|sgsn_address|sgsn_number|smsc_number|vlr_number +||1|||123456789012345|||0|0||098765432109876|1|1||||||MSC-1 +||2|||111111111|||1|0|||1|1|||||| +||3|||222222222|||0|1||22222|1|1|||||| +||4|||333333|||0|0||3|0|1|||||| +||5|||444444444444444|||0|0||4444|1|0|||||| +||6|||5555555|||0|0||55555555555555|0|0|||||| Table: subscriber_apn name|type|notnull|dflt_value|pk @@ -164,5 +164,5 @@ rc = 0 DMAIN hlr starting DDB using database: <PATH>test.db -DDB Database <PATH>test.db' has HLR DB schema version 2 +DDB Database <PATH>test.db' has HLR DB schema version 3 DMAIN Cmdline option --db-check: Database was opened successfully, quitting. -- To view, visit https://gerrit.osmocom.org/c/osmo-hlr/+/15914 To unsubscribe, or for help writing mail filters, visit https://gerrit.osmocom.org/settings Gerrit-Project: osmo-hlr Gerrit-Branch: master Gerrit-Change-Id: I527e8627b24b79f3e9eec32675c7f5a3a6d25440 Gerrit-Change-Number: 15914 Gerrit-PatchSet: 7 Gerrit-Owner: neels <nhofmeyr at sysmocom.de> Gerrit-Reviewer: Jenkins Builder Gerrit-Reviewer: fixeria <axilirator at gmail.com> Gerrit-Reviewer: laforge <laforge at osmocom.org> Gerrit-Reviewer: neels <nhofmeyr at sysmocom.de> Gerrit-Reviewer: pespin <pespin at sysmocom.de> Gerrit-MessageType: merged -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://lists.osmocom.org/pipermail/gerrit-log/attachments/20191112/2a893065/attachment.htm>