add column 'last_lu_rat', show last RAN type

Change-Id: I5d73b1d928b61175d3198326706b7f49ba50e16f

8 files changed, 85 insertions, 23 deletions

diff --git a/include/osmocom/hlr/db.h b/include/osmocom/hlr/db.h
index 5fbc846..d0c1195 100644
--- a/include/osmocom/hlr/db.h
+++ b/include/osmocom/hlr/db.h
@@ -107,6 +107,8 @@ struct hlr_subscriber {
 	bool		ms_purged_ps;
 	time_t		last_lu_seen;
 	time_t		last_lu_seen_ps;
+	char		last_lu_rat_cs[128];
+	char		last_lu_rat_ps[128];
 	/* talloc'd IPA unit name */
 	struct osmo_ipa_name	vlr_via_proxy;
 	struct osmo_ipa_name	sgsn_via_proxy;
@@ -170,7 +172,8 @@ int db_subscr_get_by_imei(struct db_context *dbc, const char *imei, struct hlr_s
 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 struct osmo_ipa_name *vlr_name, bool is_ps,
-		 const struct osmo_ipa_name *via_proxy);
+		 const struct osmo_ipa_name *via_proxy,
+		 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/sql/hlr.sql b/sql/hlr.sql
index fd71aef..5548afb 100644
--- a/sql/hlr.sql
+++ b/sql/hlr.sql
@@ -45,6 +45,12 @@ CREATE TABLE subscriber (
 	last_lu_seen TIMESTAMP default NULL,
 	last_lu_seen_ps 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_cs	TEXT default NULL,
+	last_lu_rat_ps	TEXT 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,
@@ -101,4 +107,4 @@ CREATE UNIQUE INDEX idx_subscr_rat_flag ON subscriber_rat (subscriber_id, rat);
 
 -- 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 = 7;
+PRAGMA user_version = 8;
diff --git a/src/db.c b/src/db.c
index fa868ea..9cb6b4c 100644
--- a/src/db.c
+++ b/src/db.c
@@ -30,7 +30,7 @@
 #include "db_bootstrap.h"
 
 /* This constant is currently duplicated in sql/hlr.sql and must be kept in sync! */
-#define CURRENT_SCHEMA_VERSION	7
+#define CURRENT_SCHEMA_VERSION	8
 
 #define SEL_COLUMNS \
 	"id," \
@@ -49,6 +49,8 @@
 	"ms_purged_ps," \
 	"last_lu_seen," \
 	"last_lu_seen_ps," \
+	"last_lu_rat_cs," \
+	"last_lu_rat_ps," \
 	"vlr_via_proxy," \
 	"sgsn_via_proxy"
 
@@ -83,8 +85,12 @@ static const char *stmt_sql[] = {
 		"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_PS] = "UPDATE subscriber SET last_lu_seen_ps = 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_cs = $rat"
+		" WHERE id = $subscriber_id",
+	[DB_STMT_SET_LAST_LU_SEEN_PS] = "UPDATE subscriber SET last_lu_seen_ps = datetime($val, 'unixepoch'),"
+		" last_lu_rat_ps = $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_IND_ADD] = "INSERT INTO ind (vlr) VALUES ($vlr)",
@@ -537,6 +543,23 @@ static int db_upgrade_v7(struct db_context *dbc)
 	return rc;
 }
 
+static int db_upgrade_v8(struct db_context *dbc)
+{
+	int rc;
+	const char *statements[] = {
+		"ALTER TABLE subscriber ADD COLUMN last_lu_rat_cs TEXT default NULL",
+		"ALTER TABLE subscriber ADD COLUMN last_lu_rat_ps TEXT default NULL",
+		"PRAGMA user_version = 8",
+	};
+
+	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 7\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,
@@ -546,6 +569,7 @@ static db_upgrade_func_t db_upgrade_path[] = {
 	db_upgrade_v5,
 	db_upgrade_v6,
 	db_upgrade_v7,
+	db_upgrade_v8,
 };
 
 static int db_get_user_version(struct db_context *dbc)
diff --git a/src/db_hlr.c b/src/db_hlr.c
index ee53a39..f797f2e 100644
--- a/src/db_hlr.c
+++ b/src/db_hlr.c
@@ -505,8 +505,10 @@ 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);
+	copy_sqlite3_text_to_buf(subscr->last_lu_rat_cs, stmt, 16);
+	copy_sqlite3_text_to_buf(subscr->last_lu_rat_ps, stmt, 17);
+	copy_sqlite3_text_to_ipa_name(&subscr->vlr_via_proxy, stmt, 18);
+	copy_sqlite3_text_to_ipa_name(&subscr->sgsn_via_proxy, stmt, 19);
 
 out:
 	db_remove_reset(stmt);
@@ -740,11 +742,14 @@ out:
  */
 int db_subscr_lu(struct db_context *dbc, int64_t subscr_id,
 		 const struct osmo_ipa_name *vlr_name, bool is_ps,
-		 const struct osmo_ipa_name *via_proxy)
+		 const struct osmo_ipa_name *via_proxy,
+		 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];
@@ -806,6 +811,21 @@ int db_subscr_lu(struct db_context *dbc, int64_t subscr_id,
 		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_vty_subscr.c b/src/hlr_vty_subscr.c
index 15e26ad..d3aa4fd 100644
--- a/src/hlr_vty_subscr.c
+++ b/src/hlr_vty_subscr.c
@@ -49,7 +49,7 @@ static char *get_datestr(const time_t *t, char *buf, size_t bufsize)
 	return buf;
 }
 
-static void dump_last_lu_seen(struct vty *vty, const char *domain_label, time_t last_lu_seen)
+static void dump_last_lu_seen(struct vty *vty, const char *domain_label, time_t last_lu_seen, const char *last_lu_rat)
 {
 	uint32_t age;
 	char datebuf[32];
@@ -57,7 +57,7 @@ static void dump_last_lu_seen(struct vty *vty, const char *domain_label, time_t
 		return;
 	vty_out(vty, "    last LU seen on %s: %s", domain_label, get_datestr(&last_lu_seen, datebuf, sizeof(datebuf)));
 	if (!timestamp_age(&last_lu_seen, &age))
-		vty_out(vty, " (invalid timestamp)%s", VTY_NEWLINE);
+		vty_out(vty, " (invalid timestamp)");
 	else {
 		vty_out(vty, " (");
 #define UNIT_AGO(UNITNAME, UNITVAL) \
@@ -69,9 +69,12 @@ static void dump_last_lu_seen(struct vty *vty, const char *domain_label, time_t
 		UNIT_AGO("h", 60*60);
 		UNIT_AGO("m", 60);
 		UNIT_AGO("s", 1);
-		vty_out(vty, " ago)%s", VTY_NEWLINE);
+		vty_out(vty, " ago)");
 #undef UNIT_AGO
 	}
+	if (last_lu_rat && *last_lu_rat != '\0')
+		vty_out(vty, " on %s", last_lu_rat);
+	vty_out(vty, "%s", VTY_NEWLINE);
 }
 
 static void subscr_dump_full_vty(struct vty *vty, struct hlr_subscriber *subscr)
@@ -114,8 +117,8 @@ static void subscr_dump_full_vty(struct vty *vty, struct hlr_subscriber *subscr)
 		vty_out(vty, "    PS disabled%s", VTY_NEWLINE);
 	if (subscr->ms_purged_ps)
 		vty_out(vty, "    PS purged%s", VTY_NEWLINE);
-	dump_last_lu_seen(vty, "CS", subscr->last_lu_seen);
-	dump_last_lu_seen(vty, "PS", subscr->last_lu_seen_ps);
+	dump_last_lu_seen(vty, "CS", subscr->last_lu_seen, subscr->last_lu_rat_cs);
+	dump_last_lu_seen(vty, "PS", subscr->last_lu_seen_ps, subscr->last_lu_rat_ps);
 	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/src/lu_fsm.c b/src/lu_fsm.c
index f5154c2..0b771a5 100644
--- a/src/lu_fsm.c
+++ b/src/lu_fsm.c
@@ -210,7 +210,8 @@ static void lu_start(struct osmo_gsup_req *update_location_req)
 		return;
 	}
 	if (db_subscr_lu(g_hlr->dbc, lu->subscr.id, &lu->vlr_name.ipa_name, lu->is_ps,
-			 osmo_gsup_peer_id_is_empty(&lu->via_proxy)? NULL : &lu->via_proxy.ipa_name)) {
+			 osmo_gsup_peer_id_is_empty(&lu->via_proxy)? NULL : &lu->via_proxy.ipa_name,
+			 update_location_req->gsup.supported_rat_types, update_location_req->gsup.supported_rat_types_len)) {
 		lu_failure(lu, GMM_CAUSE_NET_FAIL, "Cannot update %s in the database",
 			   lu->is_ps ? "SGSN number" : "VLR number");
 		return;
diff --git a/tests/db/db_test.c b/tests/db/db_test.c
index bbc728e..8acbba7 100644
--- a/tests/db/db_test.c
+++ b/tests/db/db_test.c
@@ -173,6 +173,8 @@ void dump_subscr(struct hlr_subscriber *subscr)
 		Pfo(lmsi, "0x%x", subscr);
 	Pb(true, ms_purged_cs);
 	Pb(true, ms_purged_ps);
+	Ps(last_lu_rat_cs);
+	Ps(last_lu_rat_ps);
 	fprintf(stderr, "}\n");
 #undef Ps
 #undef Pd
@@ -243,7 +245,7 @@ static int db_subscr_lu_str(struct db_context *dbc, int64_t subscr_id,
 {
 	struct osmo_ipa_name vlr_nr;
 	osmo_ipa_name_set_str(&vlr_nr, vlr_or_sgsn_number);
-	return db_subscr_lu(dbc, subscr_id, &vlr_nr, is_ps, NULL);
+	return db_subscr_lu(dbc, subscr_id, &vlr_nr, is_ps, NULL, NULL, 0);
 }
 
 static void test_subscr_create_update_sel_delete()
diff --git a/tests/db_upgrade/db_upgrade_test.ok b/tests/db_upgrade/db_upgrade_test.ok
index a366b3d..ff83442 100644
--- a/tests/db_upgrade/db_upgrade_test.ok
+++ b/tests/db_upgrade/db_upgrade_test.ok
@@ -87,6 +87,7 @@ 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
 DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 6
 DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 7
+DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 8
 DMAIN Cmdline option --db-check: Database was opened successfully, quitting.
 
 Resulting db:
@@ -134,6 +135,8 @@ id|INTEGER|0||1
 imei|VARCHAR(14)|0||0
 imeisv|VARCHAR|0||0
 imsi|VARCHAR(15)|1||0
+last_lu_rat_cs|TEXT|0|NULL|0
+last_lu_rat_ps|TEXT|0|NULL|0
 last_lu_seen|TIMESTAMP|0|NULL|0
 last_lu_seen_ps|TIMESTAMP|0|NULL|0
 lmsi|INTEGER|0||0
@@ -153,13 +156,13 @@ 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|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||||||||
+ggsn_number|gmlc_number|id|imei|imeisv|imsi|last_lu_rat_cs|last_lu_rat_ps|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
@@ -188,5 +191,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 7
+DDB Database <PATH>test.db' has HLR DB schema version 8
 DMAIN Cmdline option --db-check: Database was opened successfully, quitting.