utils: store more fields from meas-feed in db

More fields need to be captured from meas-feed
to perform meaningful analysis of data from
multi-bts and multi-trx systems.

This patch adds expands the existing db utils
to record nearly all available fields from
meas-feed.

Change-Id: I509c939524b11a4ee455bcfc3ebee6c5c35b9fba
---
M src/utils/meas_db.c
M src/utils/meas_db.h
M src/utils/meas_pcap2db.c
M src/utils/meas_udp2db.c
4 files changed, 80 insertions(+), 56 deletions(-)

diff --git a/src/utils/meas_db.c b/src/utils/meas_db.c
index bf3a093..1b4f1f3 100644
--- a/src/utils/meas_db.c
+++ b/src/utils/meas_db.c
@@ -29,10 +29,11 @@
 #include <osmocom/core/utils.h>
 #include <osmocom/gsm/gsm_utils.h>
 #include <osmocom/bsc/meas_rep.h>
+#include <osmocom/bsc/meas_feed.h>
 
 #include "meas_db.h"
 
-#define INS_MR "INSERT INTO meas_rep (time, imsi, name, scenario, nr, bs_power, ms_timing_offset, fpc, ms_l1_pwr, ms_l1_ta) VALUES (?,?,?,?,?,?,?,?,?,?)"
+#define INS_MR "INSERT INTO meas_rep (time, bts_nr, trx_nr, ts_nr, ss_nr, lchan_type, pchan_type, imsi, name, scenario, nr, bs_power, ms_timing_offset, fpc, ms_l1_pwr, ms_l1_ta) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
 #define INS_UD "INSERT INTO meas_rep_unidir (meas_id, rx_lev_full, rx_lev_sub, rx_qual_full, rx_qual_sub, dtx, uplink) VALUES (?,?,?,?,?,?,?)"
 #define UPD_MR "UPDATE meas_rep SET ul_unidir=?, dl_unidir=? WHERE id=?"
 
@@ -79,52 +80,57 @@
 }
 
 /* insert a measurement report into the database */
-int meas_db_insert(struct meas_db_state *st, const char *imsi,
-		   const char *name, unsigned long timestamp,
-		   const char *scenario,
-		   const struct gsm_meas_rep *mr)
+int meas_db_insert(struct meas_db_state *st, unsigned long timestamp,
+		   const struct meas_feed_meas *mfm)
 {
 	sqlite3_int64 rowid, ul_rowid, dl_rowid;
 
 	SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 1, timestamp));
 
-	if (imsi)
-		SCK_OK(st->db, sqlite3_bind_text(st->stmt_ins_mr, 2,
-						 imsi, -1, SQLITE_STATIC));
+	SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 2, mfm->bts_nr));
+	SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 3, mfm->trx_nr));
+	SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 4, mfm->ts_nr));
+	SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 5, mfm->ss_nr));
+	SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 6, mfm->lchan_type));
+	SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 7, mfm->pchan_type));
+
+	if (mfm->imsi)
+		SCK_OK(st->db, sqlite3_bind_text(st->stmt_ins_mr, 8,
+						 mfm->imsi, -1, SQLITE_STATIC));
 	else
-		SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 2));
+		SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 8));
 
-	if (name)
-		SCK_OK(st->db, sqlite3_bind_text(st->stmt_ins_mr, 3,
-						 name, -1, SQLITE_STATIC));
+	if (mfm->name)
+		SCK_OK(st->db, sqlite3_bind_text(st->stmt_ins_mr, 9,
+						 mfm->name, -1, SQLITE_STATIC));
 	else
-		SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 3));
+		SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 9));
 
-	if (scenario)
-		SCK_OK(st->db, sqlite3_bind_text(st->stmt_ins_mr, 4,
-						 scenario, -1, SQLITE_STATIC));
+	if (mfm->scenario)
+		SCK_OK(st->db, sqlite3_bind_text(st->stmt_ins_mr, 10,
+						 mfm->scenario, -1, SQLITE_STATIC));
 	else
-		SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 4));
+		SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 10));
 
 
-	SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 5, mr->nr));
-	SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 6, mr->bs_power_db / 2));
+	SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 11, mfm->mr.nr));
+	SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 12, mfm->mr.bs_power_db / 2));
 
-	if (mr->flags & MEAS_REP_F_MS_TO)
-		SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 7, mr->ms_timing_offset));
+	if (mfm->mr.flags & MEAS_REP_F_MS_TO)
+		SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 13, mfm->mr.ms_timing_offset));
 	else
-		SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 7));
+		SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 13));
 
-	if (mr->flags & MEAS_REP_F_FPC)
-		SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 8, 1));
+	if (mfm->mr.flags & MEAS_REP_F_FPC)
+		SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 14, 1));
 	else
-		SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 8, 0));
+		SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 14, 0));
 
-	if (mr->flags & MEAS_REP_F_MS_L1) {
-		SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 9,
-						mr->ms_l1.pwr));
-		SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 10,
-						mr->ms_l1.ta));
+	if (mfm->mr.flags & MEAS_REP_F_MS_L1) {
+		SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 15,
+						mfm->mr.ms_l1.pwr));
+		SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 16,
+						mfm->mr.ms_l1.ta));
 	}
 
 	SCK_DONE(st->db, sqlite3_step(st->stmt_ins_mr));
@@ -133,14 +139,14 @@
 	rowid = sqlite3_last_insert_rowid(st->db);
 
 	/* insert uplink measurement */
-	ul_rowid = _insert_ud(st, rowid, mr->flags & MEAS_REP_F_UL_DTX,
-				1, &mr->ul);
+	ul_rowid = _insert_ud(st, rowid, mfm->mr.flags & MEAS_REP_F_UL_DTX,
+				1, &mfm->mr.ul);
 	SCK_OK(st->db, sqlite3_bind_int(st->stmt_upd_mr, 1, ul_rowid));
 
 	/* insert downlink measurement, if present */
-	if (mr->flags & MEAS_REP_F_DL_VALID) {
-		dl_rowid = _insert_ud(st, rowid, mr->flags & MEAS_REP_F_DL_DTX,
-			       	      0, &mr->dl);
+	if (mfm->mr.flags & MEAS_REP_F_DL_VALID) {
+		dl_rowid = _insert_ud(st, rowid, mfm->mr.flags & MEAS_REP_F_DL_DTX,
+					0, &mfm->mr.dl);
 		SCK_OK(st->db, sqlite3_bind_int(st->stmt_upd_mr, 2, dl_rowid));
 	} else
 		SCK_OK(st->db, sqlite3_bind_null(st->stmt_upd_mr, 2));
@@ -181,6 +187,12 @@
 	"CREATE TABLE IF NOT EXISTS meas_rep ("
 		"id INTEGER PRIMARY KEY AUTOINCREMENT,"
 		"time TIMESTAMP,"
+		"bts_nr INTEGER,"
+		"trx_nr INTEGER,"
+		"ts_nr INTEGER,"
+		"ss_nr INTEGER,"
+		"lchan_type INTEGER,"
+		"pchan_type INTEGER,"
 		"imsi TEXT,"
 		"name TEXT,"
 		"scenario TEXT,"
@@ -207,6 +219,12 @@
 		"SELECT "
 			"meas_rep.id, "
 			"datetime(time,'unixepoch') AS timestamp, "
+			"bts_nr,"
+			"trx_nr,"
+			"ts_nr,"
+			"ss_nr,"
+			"lchan_type,"
+			"pchan_type,"
 			"imsi, "
 			"name, "
 			"scenario, "
@@ -238,6 +256,12 @@
 		"SELECT "
 			"id,"
 			"timestamp,"
+			"bts_nr,"
+			"trx_nr,"
+			"ts_nr,"
+			"ss_nr,"
+			"lchan_type,"
+			"pchan_type,"
 			"imsi,"
 			"name,"
 			"scenario,"
diff --git a/src/utils/meas_db.h b/src/utils/meas_db.h
index 889e902..8f8a8c6 100644
--- a/src/utils/meas_db.h
+++ b/src/utils/meas_db.h
@@ -9,9 +9,7 @@
 int meas_db_begin(struct meas_db_state *st);
 int meas_db_commit(struct meas_db_state *st);
 
-int meas_db_insert(struct meas_db_state *st, const char *imsi,
-		   const char *name, unsigned long timestamp,
-		   const char *scenario,
-		   const struct gsm_meas_rep *mr);
+int meas_db_insert(struct meas_db_state *st, unsigned long timestamp,
+		   const struct meas_feed_meas *mfm);
 
 #endif
diff --git a/src/utils/meas_pcap2db.c b/src/utils/meas_pcap2db.c
index d784ca7..eb69d50 100644
--- a/src/utils/meas_pcap2db.c
+++ b/src/utils/meas_pcap2db.c
@@ -47,15 +47,7 @@
 static void handle_mfm(const struct pcap_pkthdr *h,
 		       const struct meas_feed_meas *mfm)
 {
-	const char *scenario;
-
-	if (strlen(mfm->scenario))
-		scenario = mfm->scenario;
-	else
-		scenario = NULL;
-
-	meas_db_insert(db, mfm->imsi, mfm->name, h->ts.tv_sec,
-			scenario, &mfm->mr);
+	meas_db_insert(db, h->ts.tv_sec, mfm);
 }
 
 static void pcap_cb(u_char *user, const struct pcap_pkthdr *h,
diff --git a/src/utils/meas_udp2db.c b/src/utils/meas_udp2db.c
index 07023c3..f40f137 100644
--- a/src/utils/meas_udp2db.c
+++ b/src/utils/meas_udp2db.c
@@ -47,7 +47,6 @@
 {
 	struct meas_feed_hdr *mfh = (struct meas_feed_hdr *) msgb_data(msg);
 	struct meas_feed_meas *mfm = (struct meas_feed_meas *) msgb_data(msg);
-	const char *scenario;
 	time_t now = time(NULL);
 
 	if (mfh->version != MEAS_FEED_VERSION)
@@ -56,13 +55,7 @@
 	if (mfh->msg_type != MEAS_FEED_MEAS)
 		return -EINVAL;
 
-	if (strlen(mfm->scenario))
-		scenario = mfm->scenario;
-	else
-		scenario = NULL;
-
-	meas_db_insert(db, mfm->imsi, mfm->name, now,
-			scenario, &mfm->mr);
+	meas_db_insert(db, now, mfm);
 
 	return 0;
 }

