diff options
author | Neels Hofmeyr <neels@hofmeyr.de> | 2019-11-25 03:59:50 +0100 |
---|---|---|
committer | Neels Hofmeyr <neels@hofmeyr.de> | 2020-04-30 19:22:24 +0200 |
commit | 04c2375b385356c9d0fc973f01d2c8928b286cf3 (patch) | |
tree | 07c542e075952933c7cd3a689bd5044519a54ba9 | |
parent | 939f508f00ee56d6e347a4377d4007e7ffe51782 (diff) |
db v5: prep for D-GSM: add vlr_via_proxy and sgsn_via_proxy
D-GSM will store in the HLR DB whether a locally connected MSC has attached the
subscriber (last_lu_seen[_ps]), or whether the attach happened via a GSUP proxy
from a different site.
Add columns for this separately in this patch.
Change-Id: I98c7b3870559ede84adf56e4bf111f53c7487745
-rw-r--r-- | include/osmocom/hlr/db.h | 4 | ||||
-rw-r--r-- | sql/hlr.sql | 9 | ||||
-rw-r--r-- | src/db.c | 47 | ||||
-rw-r--r-- | src/db_hlr.c | 10 | ||||
-rw-r--r-- | tests/db_upgrade/db_upgrade_test.ok | 19 |
5 files changed, 75 insertions, 14 deletions
diff --git a/include/osmocom/hlr/db.h b/include/osmocom/hlr/db.h index 9be88d8..ca336a0 100644 --- a/include/osmocom/hlr/db.h +++ b/include/osmocom/hlr/db.h @@ -51,6 +51,7 @@ void db_remove_reset(sqlite3_stmt *stmt); bool db_bind_text(sqlite3_stmt *stmt, const char *param_name, const char *text); bool db_bind_int(sqlite3_stmt *stmt, const char *param_name, int nr); bool db_bind_int64(sqlite3_stmt *stmt, const char *param_name, int64_t nr); +bool db_bind_null(sqlite3_stmt *stmt, const char *param_name); void db_close(struct db_context *dbc); struct db_context *db_open(void *ctx, const char *fname, bool enable_sqlite3_logging, bool allow_upgrades); @@ -99,6 +100,9 @@ struct hlr_subscriber { bool ms_purged_ps; time_t last_lu_seen; time_t last_lu_seen_ps; + /* talloc'd IPA unit name */ + struct osmo_ipa_name vlr_via_proxy; + struct osmo_ipa_name sgsn_via_proxy; }; /* A format string for use with strptime(3). This format string is diff --git a/sql/hlr.sql b/sql/hlr.sql index ad57661..98e586d 100644 --- a/sql/hlr.sql +++ b/sql/hlr.sql @@ -43,7 +43,12 @@ CREATE TABLE subscriber ( -- Timestamp of last location update seen from subscriber -- The value is a string which encodes a UTC timestamp in granularity of seconds. last_lu_seen TIMESTAMP default NULL, - last_lu_seen_ps TIMESTAMP default NULL + last_lu_seen_ps TIMESTAMP default NULL, + + -- When a LU was received via a proxy, that proxy's hlr_number is stored here, + -- while vlr_number reflects the MSC on the far side of that proxy. + vlr_via_proxy VARCHAR, + sgsn_via_proxy VARCHAR ); CREATE TABLE subscriber_apn ( @@ -78,4 +83,4 @@ CREATE UNIQUE INDEX idx_subscr_imsi ON subscriber (imsi); -- 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 = 4; +PRAGMA user_version = 5; @@ -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 4 +#define CURRENT_SCHEMA_VERSION 5 #define SEL_COLUMNS \ "id," \ @@ -46,15 +46,17 @@ "ms_purged_cs," \ "ms_purged_ps," \ "last_lu_seen," \ - "last_lu_seen_ps" \ + "last_lu_seen_ps," \ + "vlr_via_proxy," \ + "sgsn_via_proxy" static const char *stmt_sql[] = { [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 = ?", [DB_STMT_SEL_BY_IMEI] = "SELECT " SEL_COLUMNS " FROM subscriber WHERE imei = ?", - [DB_STMT_UPD_VLR_BY_ID] = "UPDATE subscriber SET vlr_number = $number WHERE id = $subscriber_id", - [DB_STMT_UPD_SGSN_BY_ID] = "UPDATE subscriber SET sgsn_number = $number WHERE id = $subscriber_id", + [DB_STMT_UPD_VLR_BY_ID] = "UPDATE subscriber SET vlr_number = $number, vlr_via_proxy = $proxy WHERE id = $subscriber_id", + [DB_STMT_UPD_SGSN_BY_ID] = "UPDATE subscriber SET sgsn_number = $number, sgsn_via_proxy = $proxy WHERE id = $subscriber_id", [DB_STMT_UPD_IMEI_BY_IMSI] = "UPDATE subscriber SET imei = $imei WHERE imsi = $imsi", [DB_STMT_AUC_BY_IMSI] = "SELECT id, algo_id_2g, ki, algo_id_3g, k, op, opc, sqn, ind_bitlen" @@ -183,6 +185,25 @@ bool db_bind_int64(sqlite3_stmt *stmt, const char *param_name, int64_t nr) return true; } +bool db_bind_null(sqlite3_stmt *stmt, const char *param_name) +{ + int rc; + int idx = param_name ? sqlite3_bind_parameter_index(stmt, param_name) : 1; + if (idx < 1) { + LOGP(DDB, LOGL_ERROR, "Error composing SQL, cannot bind parameter '%s'\n", + param_name); + return false; + } + rc = sqlite3_bind_null(stmt, idx); + if (rc != SQLITE_OK) { + LOGP(DDB, LOGL_ERROR, "Error binding NULL to SQL parameter %s: %d\n", + param_name ? param_name : "#1", rc); + db_remove_reset(stmt); + return false; + } + return true; +} + void db_close(struct db_context *dbc) { unsigned int i; @@ -441,12 +462,30 @@ static int db_upgrade_v4(struct db_context *dbc) return rc; } +static int db_upgrade_v5(struct db_context *dbc) +{ + int rc; + const char *statements[] = { + "ALTER TABLE subscriber ADD COLUMN vlr_via_proxy VARCHAR", + "ALTER TABLE subscriber ADD COLUMN sgsn_via_proxy VARCHAR", + "PRAGMA user_version = 5", + }; + + rc = db_run_statements(dbc, statements, ARRAY_SIZE(statements)); + if (rc != SQLITE_DONE) { + LOGP(DDB, LOGL_ERROR, "Unable to update HLR database schema to version 5\n"); + return rc; + } + return rc; +} + typedef int (*db_upgrade_func_t)(struct db_context *dbc); static db_upgrade_func_t db_upgrade_path[] = { db_upgrade_v1, db_upgrade_v2, db_upgrade_v3, db_upgrade_v4, + db_upgrade_v5, }; static int db_get_user_version(struct db_context *dbc) diff --git a/src/db_hlr.c b/src/db_hlr.c index ec8f896..9073926 100644 --- a/src/db_hlr.c +++ b/src/db_hlr.c @@ -505,6 +505,8 @@ static int db_sel(struct db_context *dbc, sqlite3_stmt *stmt, struct hlr_subscri subscr->imsi, "CS"); parse_last_lu_seen(&subscr->last_lu_seen_ps, (const char *)sqlite3_column_text(stmt, 15), subscr->imsi, "PS"); + copy_sqlite3_text_to_ipa_name(&subscr->vlr_via_proxy, stmt, 16); + copy_sqlite3_text_to_ipa_name(&subscr->sgsn_via_proxy, stmt, 17); out: db_remove_reset(stmt); @@ -750,6 +752,14 @@ int db_subscr_lu(struct db_context *dbc, int64_t subscr_id, if (!db_bind_text(stmt, "$number", (char*)vlr_name->val)) return -EIO; + if (via_proxy && via_proxy->len) { + if (!db_bind_text(stmt, "$proxy", (char*)via_proxy->val)) + return -EIO; + } else { + if (!db_bind_null(stmt, "$proxy")) + return -EIO; + } + /* execute the statement */ rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { diff --git a/tests/db_upgrade/db_upgrade_test.ok b/tests/db_upgrade/db_upgrade_test.ok index 67d0006..2bc6a39 100644 --- a/tests/db_upgrade/db_upgrade_test.ok +++ b/tests/db_upgrade/db_upgrade_test.ok @@ -84,6 +84,7 @@ DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 1 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 DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 4 +DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 5 DMAIN Cmdline option --db-check: Database was opened successfully, quitting. Resulting db: @@ -137,17 +138,19 @@ periodic_lu_tmr|INTEGER|0||0 periodic_rau_tau_tmr|INTEGER|0||0 sgsn_address|VARCHAR|0||0 sgsn_number|VARCHAR(15)|0||0 +sgsn_via_proxy|VARCHAR|0||0 smsc_number|VARCHAR(15)|0||0 vlr_number|VARCHAR(15)|0||0 +vlr_via_proxy|VARCHAR|0||0 Table subscriber contents: -ggsn_number|gmlc_number|id|imei|imeisv|imsi|last_lu_seen|last_lu_seen_ps|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|||||| +ggsn_number|gmlc_number|id|imei|imeisv|imsi|last_lu_seen|last_lu_seen_ps|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|sgsn_via_proxy|smsc_number|vlr_number|vlr_via_proxy +||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 @@ -168,5 +171,5 @@ osmo-hlr --database $db --db-check --config-file $srcdir/osmo-hlr.cfg rc = 0 DMAIN hlr starting DDB using database: <PATH>test.db -DDB Database <PATH>test.db' has HLR DB schema version 4 +DDB Database <PATH>test.db' has HLR DB schema version 5 DMAIN Cmdline option --db-check: Database was opened successfully, quitting. |