add column 'last_lu_rat', show last RAN type
Change-Id: I5d73b1d928b61175d3198326706b7f49ba50e16f
diff --git a/sql/hlr.sql b/sql/hlr.sql
index 6ff561b..65c20c6 100644
--- a/sql/hlr.sql
+++ b/sql/hlr.sql
@@ -42,7 +42,12 @@
-- 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 TIMESTAMP default NULL,
+
+ -- Last Radio Access Type list as sent during Location Updating Request.
+ -- This is usually just one RAT name, but can be a comma separated list of strings
+ -- of all the RAT types sent during Location Updating Request.
+ last_lu_rat TEXT default NULL
);
CREATE TABLE subscriber_apn (
@@ -85,4 +90,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 = 3;
+PRAGMA user_version = 4;
diff --git a/src/db.c b/src/db.c
index 939cdbf..185a3df 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 3
+#define CURRENT_SCHEMA_VERSION 4
#define SEL_COLUMNS \
"id," \
@@ -45,7 +45,8 @@
"lmsi," \
"ms_purged_cs," \
"ms_purged_ps," \
- "last_lu_seen"
+ "last_lu_seen," \
+ "last_lu_rat"
static const char *stmt_sql[] = {
[DB_STMT_SEL_BY_IMSI] = "SELECT " SEL_COLUMNS " FROM subscriber WHERE imsi = ?",
@@ -78,7 +79,8 @@
"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_SET_LAST_LU_SEEN] = "UPDATE subscriber SET last_lu_seen = datetime($val, 'unixepoch') WHERE id = $subscriber_id",
+ [DB_STMT_SET_LAST_LU_SEEN] = "UPDATE subscriber SET last_lu_seen = datetime($val, 'unixepoch'), last_lu_rat = $rat"
+ " WHERE id = $subscriber_id",
[DB_STMT_EXISTS_BY_IMSI] = "SELECT 1 FROM subscriber WHERE imsi = $imsi",
[DB_STMT_EXISTS_BY_MSISDN] = "SELECT 1 FROM subscriber WHERE msisdn = $msisdn",
[DB_STMT_UPD_RAT_FLAG] = "INSERT OR REPLACE INTO subscriber_rat (subscriber_id, rat, allowed)"
@@ -380,6 +382,40 @@
return SQLITE_DONE;
}
+static int db_upgrade_v4(struct db_context *dbc)
+{
+ sqlite3_stmt *stmt;
+ int rc;
+ const char *update_stmt_sql = "ALTER TABLE subscriber ADD COLUMN last_lu_rat TEXT default NULL";
+ const char *set_schema_version_sql = "PRAGMA user_version = 4";
+
+ 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 %d\n", 1);
+ return rc;
+ }
+
+ rc = sqlite3_prepare_v2(dbc->db, set_schema_version_sql, -1, &stmt, NULL);
+ if (rc != SQLITE_OK) {
+ LOGP(DDB, LOGL_ERROR, "Unable to prepare SQL statement '%s'\n", set_schema_version_sql);
+ return rc;
+ }
+ rc = sqlite3_step(stmt);
+ if (rc != SQLITE_DONE)
+ LOGP(DDB, LOGL_ERROR, "Unable to update HLR database schema to version 4\n");
+
+ db_remove_reset(stmt);
+ sqlite3_finalize(stmt);
+ return rc;
+}
+
static int db_get_user_version(struct db_context *dbc)
{
const char *user_version_sql = "PRAGMA user_version";
@@ -521,6 +557,15 @@
}
version = 3;
/* fall through */
+ case 3:
+ rc = db_upgrade_v4(dbc);
+ if (rc != SQLITE_DONE) {
+ LOGP(DDB, LOGL_ERROR, "Failed to upgrade HLR DB schema to version 4: (rc=%d) %s\n",
+ rc, sqlite3_errmsg(dbc->db));
+ goto out_free;
+ }
+ version = 4;
+ /* fall through */
/* case N: ... */
default:
break;
diff --git a/src/db.h b/src/db.h
index 462a3fa..0f1d40d 100644
--- a/src/db.h
+++ b/src/db.h
@@ -99,6 +99,7 @@
bool ms_purged_cs;
bool ms_purged_ps;
time_t last_lu_seen;
+ char last_lu_rat[128];
bool rat_types[OSMO_RAT_COUNT];
};
@@ -158,7 +159,8 @@
int db_subscr_get_by_imei(struct db_context *dbc, const char *imei, struct hlr_subscriber *subscr);
int db_subscr_nam(struct db_context *dbc, const char *imsi, bool nam_val, bool is_ps);
int db_subscr_lu(struct db_context *dbc, int64_t subscr_id,
- const char *vlr_or_sgsn_number, bool is_ps);
+ const char *vlr_or_sgsn_number, bool is_ps,
+ const enum osmo_rat_type rat_types[], size_t rat_types_len);
int db_subscr_purge(struct db_context *dbc, const char *by_imsi,
bool purge_val, bool is_ps);
diff --git a/src/db_hlr.c b/src/db_hlr.c
index 91452e5..3364647 100644
--- a/src/db_hlr.c
+++ b/src/db_hlr.c
@@ -493,6 +493,7 @@
}
}
}
+ copy_sqlite3_text_to_buf(subscr->last_lu_rat, stmt, 15);
out:
db_remove_reset(stmt);
@@ -725,11 +726,14 @@
* -EIO on database errors.
*/
int db_subscr_lu(struct db_context *dbc, int64_t subscr_id,
- const char *vlr_or_sgsn_number, bool is_ps)
+ const char *vlr_or_sgsn_number, bool is_ps,
+ const enum osmo_rat_type rat_types[], size_t rat_types_len)
{
sqlite3_stmt *stmt;
int rc, ret = 0;
struct timespec localtime;
+ char rat_types_str[128] = "";
+ int i;
stmt = dbc->stmt[is_ps ? DB_STMT_UPD_SGSN_BY_ID
: DB_STMT_UPD_VLR_BY_ID];
@@ -783,6 +787,21 @@
goto out;
}
+ for (i = 0; i < rat_types_len; i++) {
+ char *pos = rat_types_str + strnlen(rat_types_str, sizeof(rat_types_str));
+ int len = pos - rat_types_str;
+ rc = snprintf(pos, len, "%s%s", pos == rat_types_str ? "" : ",", osmo_rat_type_name(rat_types[i]));
+ if (rc > len) {
+ osmo_strlcpy(rat_types_str + sizeof(rat_types_str) - 4, "...", 4);
+ break;
+ }
+ }
+
+ if (!db_bind_text(stmt, "$rat", rat_types_str)) {
+ ret = -EIO;
+ goto out;
+ }
+
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
LOGP(DAUC, LOGL_ERROR,
diff --git a/src/hlr.c b/src/hlr.c
index c7881ea..40f2f9f 100644
--- a/src/hlr.c
+++ b/src/hlr.c
@@ -437,7 +437,8 @@
LOGP(DAUC, LOGL_DEBUG, "IMSI='%s': storing %s = %s\n",
subscr->imsi, luop->is_ps ? "SGSN number" : "VLR number",
osmo_quote_str((const char*)luop->peer, -1));
- if (db_subscr_lu(g_hlr->dbc, subscr->id, (const char *)luop->peer, luop->is_ps))
+ if (db_subscr_lu(g_hlr->dbc, subscr->id, (const char *)luop->peer, luop->is_ps,
+ gsup->rat_types, gsup->rat_types_len))
LOGP(DAUC, LOGL_ERROR, "IMSI='%s': Cannot update %s in the database\n",
subscr->imsi, luop->is_ps ? "SGSN number" : "VLR number");
diff --git a/src/hlr_vty_subscr.c b/src/hlr_vty_subscr.c
index de78ef9..8c3ef2b 100644
--- a/src/hlr_vty_subscr.c
+++ b/src/hlr_vty_subscr.c
@@ -89,6 +89,8 @@
vty_out(vty, " PS purged%s", VTY_NEWLINE);
if (subscr->last_lu_seen)
vty_out(vty, " last LU seen: %s%s", get_datestr(&subscr->last_lu_seen), VTY_NEWLINE);
+ if (subscr->last_lu_rat[0])
+ vty_out(vty, " last LU RAT: %s%s", subscr->last_lu_rat, VTY_NEWLINE);
for (i = OSMO_RAT_UNKNOWN + 1; i < ARRAY_SIZE(subscr->rat_types); i++) {
vty_out(vty, " %s: %s%s", osmo_rat_type_name(i), subscr->rat_types[i] ? "allowed" : "forbidden",
VTY_NEWLINE);
diff --git a/tests/db/db_test.c b/tests/db/db_test.c
index 1da2752..bc1ba5a 100644
--- a/tests/db/db_test.c
+++ b/tests/db/db_test.c
@@ -170,6 +170,7 @@
Pfo(lmsi, "0x%x", subscr);
Pb(true, ms_purged_cs);
Pb(true, ms_purged_ps);
+ Ps(last_lu_rat);
fprintf(stderr, "}\n");
#undef Ps
#undef Pd
@@ -234,6 +235,7 @@
static const char *imsi2 = "123456789000002";
static const char *short_imsi = "123456";
static const char *unknown_imsi = "999999999";
+static const enum osmo_rat_type rat_types[2] = { OSMO_RAT_GERAN_A, OSMO_RAT_UTRAN_IU, };
static void test_subscr_create_update_sel_delete()
{
@@ -386,39 +388,44 @@
comment("Record LU for PS and CS (SGSN and VLR names)");
- ASSERT_RC(db_subscr_lu(dbc, id0, "5952", true), 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, "5952", true, NULL, 0), 0);
ASSERT_SEL(id, id0, 0);
- ASSERT_RC(db_subscr_lu(dbc, id0, "712", false), 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, "712", false, NULL, 0), 0);
ASSERT_SEL(id, id0, 0);
comment("Record LU for PS and CS (SGSN and VLR names) *again*");
- ASSERT_RC(db_subscr_lu(dbc, id0, "111", true), 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, "111", true, NULL, 0), 0);
ASSERT_SEL(id, id0, 0);
- ASSERT_RC(db_subscr_lu(dbc, id0, "111", true), 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, "111", true, NULL, 0), 0);
ASSERT_SEL(id, id0, 0);
- ASSERT_RC(db_subscr_lu(dbc, id0, "222", false), 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, "222", false, NULL, 0), 0);
ASSERT_SEL(id, id0, 0);
- ASSERT_RC(db_subscr_lu(dbc, id0, "222", false), 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, "222", false, NULL, 0), 0);
+ ASSERT_SEL(id, id0, 0);
+
+ ASSERT_RC(db_subscr_lu(dbc, id0, "333", false, rat_types, 1), 0);
+ ASSERT_SEL(id, id0, 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, "333", false, rat_types, 1), 0);
ASSERT_SEL(id, id0, 0);
comment("Unset LU info for PS and CS (SGSN and VLR names)");
- ASSERT_RC(db_subscr_lu(dbc, id0, "", true), 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, "", true, NULL, 0), 0);
ASSERT_SEL(id, id0, 0);
- ASSERT_RC(db_subscr_lu(dbc, id0, "", false), 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, "", false, NULL, 0), 0);
ASSERT_SEL(id, id0, 0);
- ASSERT_RC(db_subscr_lu(dbc, id0, "111", true), 0);
- ASSERT_RC(db_subscr_lu(dbc, id0, "222", false), 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, "111", true, NULL, 0), 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, "222", false, NULL, 0), 0);
ASSERT_SEL(id, id0, 0);
- ASSERT_RC(db_subscr_lu(dbc, id0, NULL, true), 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, NULL, true, NULL, 0), 0);
ASSERT_SEL(id, id0, 0);
- ASSERT_RC(db_subscr_lu(dbc, id0, NULL, false), 0);
+ ASSERT_RC(db_subscr_lu(dbc, id0, NULL, false, NULL, 0), 0);
ASSERT_SEL(id, id0, 0);
comment("Record LU for non-existent ID");
- ASSERT_RC(db_subscr_lu(dbc, 99999, "5952", true), -ENOENT);
- ASSERT_RC(db_subscr_lu(dbc, 99999, "712", false), -ENOENT);
+ ASSERT_RC(db_subscr_lu(dbc, 99999, "5952", true, NULL, 0), -ENOENT);
+ ASSERT_RC(db_subscr_lu(dbc, 99999, "712", false, NULL, 0), -ENOENT);
ASSERT_SEL(id, 99999, -ENOENT);
comment("Purge and un-purge PS and CS");
diff --git a/tests/db/db_test.err b/tests/db/db_test.err
index a3e4d58..95270fa 100644
--- a/tests/db/db_test.err
+++ b/tests/db/db_test.err
@@ -435,7 +435,7 @@
--- Record LU for PS and CS (SGSN and VLR names)
-db_subscr_lu(dbc, id0, "5952", true) --> 0
+db_subscr_lu(dbc, id0, "5952", true, NULL, 0) --> 0
db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
struct hlr_subscriber {
@@ -445,7 +445,7 @@
.sgsn_number = '5952',
}
-db_subscr_lu(dbc, id0, "712", false) --> 0
+db_subscr_lu(dbc, id0, "712", false, NULL, 0) --> 0
db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
struct hlr_subscriber {
@@ -459,7 +459,7 @@
--- Record LU for PS and CS (SGSN and VLR names) *again*
-db_subscr_lu(dbc, id0, "111", true) --> 0
+db_subscr_lu(dbc, id0, "111", true, NULL, 0) --> 0
db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
struct hlr_subscriber {
@@ -470,7 +470,7 @@
.sgsn_number = '111',
}
-db_subscr_lu(dbc, id0, "111", true) --> 0
+db_subscr_lu(dbc, id0, "111", true, NULL, 0) --> 0
db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
struct hlr_subscriber {
@@ -481,7 +481,7 @@
.sgsn_number = '111',
}
-db_subscr_lu(dbc, id0, "222", false) --> 0
+db_subscr_lu(dbc, id0, "222", false, NULL, 0) --> 0
db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
struct hlr_subscriber {
@@ -492,7 +492,7 @@
.sgsn_number = '111',
}
-db_subscr_lu(dbc, id0, "222", false) --> 0
+db_subscr_lu(dbc, id0, "222", false, NULL, 0) --> 0
db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
struct hlr_subscriber {
@@ -503,20 +503,44 @@
.sgsn_number = '111',
}
+db_subscr_lu(dbc, id0, "333", false, rat_types, 1) --> 0
+
+db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
+struct hlr_subscriber {
+ .id = 1,
+ .imsi = '123456789000000',
+ .msisdn = '543210123456789',
+ .vlr_number = '333',
+ .sgsn_number = '111',
+ .last_lu_rat = 'GERAN-A',
+}
+
+db_subscr_lu(dbc, id0, "333", false, rat_types, 1) --> 0
+
+db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
+struct hlr_subscriber {
+ .id = 1,
+ .imsi = '123456789000000',
+ .msisdn = '543210123456789',
+ .vlr_number = '333',
+ .sgsn_number = '111',
+ .last_lu_rat = 'GERAN-A',
+}
+
--- Unset LU info for PS and CS (SGSN and VLR names)
-db_subscr_lu(dbc, id0, "", true) --> 0
+db_subscr_lu(dbc, id0, "", true, NULL, 0) --> 0
db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
struct hlr_subscriber {
.id = 1,
.imsi = '123456789000000',
.msisdn = '543210123456789',
- .vlr_number = '222',
+ .vlr_number = '333',
}
-db_subscr_lu(dbc, id0, "", false) --> 0
+db_subscr_lu(dbc, id0, "", false, NULL, 0) --> 0
db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
struct hlr_subscriber {
@@ -525,9 +549,9 @@
.msisdn = '543210123456789',
}
-db_subscr_lu(dbc, id0, "111", true) --> 0
+db_subscr_lu(dbc, id0, "111", true, NULL, 0) --> 0
-db_subscr_lu(dbc, id0, "222", false) --> 0
+db_subscr_lu(dbc, id0, "222", false, NULL, 0) --> 0
db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
struct hlr_subscriber {
@@ -538,7 +562,7 @@
.sgsn_number = '111',
}
-db_subscr_lu(dbc, id0, NULL, true) --> 0
+db_subscr_lu(dbc, id0, NULL, true, NULL, 0) --> 0
db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
struct hlr_subscriber {
@@ -548,7 +572,7 @@
.vlr_number = '222',
}
-db_subscr_lu(dbc, id0, NULL, false) --> 0
+db_subscr_lu(dbc, id0, NULL, false, NULL, 0) --> 0
db_subscr_get_by_id(dbc, id0, &g_subscr) --> 0
struct hlr_subscriber {
@@ -560,10 +584,10 @@
--- Record LU for non-existent ID
-db_subscr_lu(dbc, 99999, "5952", true) --> -ENOENT
+db_subscr_lu(dbc, 99999, "5952", true, NULL, 0) --> -ENOENT
DAUC Cannot update SGSN number for subscriber ID=99999: no such subscriber
-db_subscr_lu(dbc, 99999, "712", false) --> -ENOENT
+db_subscr_lu(dbc, 99999, "712", false, NULL, 0) --> -ENOENT
DAUC Cannot update VLR number for subscriber ID=99999: no such subscriber
db_subscr_get_by_id(dbc, 99999, &g_subscr) --> -ENOENT