Harald Welte | b3487b1 | 2012-11-15 00:12:56 +0100 | [diff] [blame] | 1 | /* Routines for storing measurement reports in SQLite3 database */ |
| 2 | |
| 3 | /* (C) 2012 by Harald Welte <laforge@gnumonks.org> |
| 4 | * |
| 5 | * All Rights Reserved |
| 6 | * |
| 7 | * This program is free software; you can redistribute it and/or modify |
| 8 | * it under the terms of the GNU Affero General Public License as published by |
| 9 | * the Free Software Foundation; either version 3 of the License, or |
| 10 | * (at your option) any later version. |
| 11 | * |
| 12 | * This program is distributed in the hope that it will be useful, |
| 13 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
| 14 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
| 15 | * GNU Affero General Public License for more details. |
| 16 | * |
| 17 | * You should have received a copy of the GNU Affero General Public License |
| 18 | * along with this program. If not, see <http://www.gnu.org/licenses/>. |
| 19 | * |
| 20 | */ |
| 21 | |
| 22 | #include <stdint.h> |
| 23 | #include <errno.h> |
| 24 | #include <string.h> |
| 25 | |
| 26 | #include <sqlite3.h> |
| 27 | |
| 28 | #include <osmocom/core/talloc.h> |
| 29 | #include <osmocom/core/utils.h> |
Harald Welte | 687d6b4 | 2015-11-20 10:28:42 +0100 | [diff] [blame] | 30 | #include <osmocom/gsm/gsm_utils.h> |
Harald Welte | b3487b1 | 2012-11-15 00:12:56 +0100 | [diff] [blame] | 31 | #include <openbsc/meas_rep.h> |
| 32 | |
| 33 | #include "meas_db.h" |
| 34 | |
| 35 | #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 (?,?,?,?,?,?,?,?,?,?)" |
| 36 | #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 (?,?,?,?,?,?,?)" |
| 37 | #define UPD_MR "UPDATE meas_rep SET ul_unidir=?, dl_unidir=? WHERE id=?" |
| 38 | |
| 39 | struct meas_db_state { |
| 40 | sqlite3 *db; |
| 41 | sqlite3_stmt *stmt_ins_ud; |
| 42 | sqlite3_stmt *stmt_ins_mr; |
| 43 | sqlite3_stmt *stmt_upd_mr; |
| 44 | }; |
| 45 | |
| 46 | /* macros to check for SQLite3 result codes */ |
| 47 | #define _SCK_OK(db, call, exp) \ |
| 48 | do { \ |
| 49 | int rc = call; \ |
| 50 | if (rc != exp) { \ |
| 51 | fprintf(stderr,"SQL Error in line %u: %s\n", \ |
| 52 | __LINE__, sqlite3_errmsg(db)); \ |
| 53 | goto err_io; \ |
| 54 | } \ |
| 55 | } while (0) |
| 56 | #define SCK_OK(db, call) _SCK_OK(db, call, SQLITE_OK) |
| 57 | #define SCK_DONE(db, call) _SCK_OK(db, call, SQLITE_DONE) |
| 58 | |
| 59 | static int _insert_ud(struct meas_db_state *st, unsigned long meas_id, int dtx, |
| 60 | int uplink, const struct gsm_meas_rep_unidir *ud) |
| 61 | { |
| 62 | unsigned long rowid; |
| 63 | |
| 64 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 1, meas_id)); |
| 65 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 2, |
| 66 | rxlev2dbm(ud->full.rx_lev))); |
| 67 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 3, |
| 68 | rxlev2dbm(ud->sub.rx_lev))); |
| 69 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 4, ud->full.rx_qual)); |
| 70 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 5, ud->sub.rx_qual)); |
| 71 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 6, dtx)); |
| 72 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_ud, 7, uplink)); |
| 73 | |
| 74 | SCK_DONE(st->db, sqlite3_step(st->stmt_ins_ud)); |
| 75 | |
| 76 | SCK_OK(st->db, sqlite3_reset(st->stmt_ins_ud)); |
| 77 | |
| 78 | return sqlite3_last_insert_rowid(st->db); |
| 79 | err_io: |
| 80 | exit(1); |
| 81 | } |
| 82 | |
| 83 | /* insert a measurement report into the database */ |
| 84 | int meas_db_insert(struct meas_db_state *st, const char *imsi, |
| 85 | const char *name, unsigned long timestamp, |
| 86 | const char *scenario, |
| 87 | const struct gsm_meas_rep *mr) |
| 88 | { |
| 89 | int rc; |
| 90 | sqlite3_int64 rowid, ul_rowid, dl_rowid; |
| 91 | |
| 92 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 1, timestamp)); |
| 93 | |
| 94 | if (imsi) |
| 95 | SCK_OK(st->db, sqlite3_bind_text(st->stmt_ins_mr, 2, |
| 96 | imsi, -1, SQLITE_STATIC)); |
| 97 | else |
| 98 | SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 2)); |
| 99 | |
| 100 | if (name) |
| 101 | SCK_OK(st->db, sqlite3_bind_text(st->stmt_ins_mr, 3, |
| 102 | name, -1, SQLITE_STATIC)); |
| 103 | else |
| 104 | SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 3)); |
| 105 | |
| 106 | if (scenario) |
| 107 | SCK_OK(st->db, sqlite3_bind_text(st->stmt_ins_mr, 4, |
| 108 | scenario, -1, SQLITE_STATIC)); |
| 109 | else |
| 110 | SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 4)); |
| 111 | |
| 112 | |
| 113 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 5, mr->nr)); |
| 114 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 6, mr->bs_power)); |
| 115 | |
| 116 | if (mr->flags & MEAS_REP_F_MS_TO) |
| 117 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 7, |
| 118 | mr->ms_timing_offset)); |
| 119 | else |
| 120 | SCK_OK(st->db, sqlite3_bind_null(st->stmt_ins_mr, 7)); |
| 121 | |
| 122 | if (mr->flags & MEAS_REP_F_FPC) |
| 123 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 8, 1)); |
| 124 | else |
| 125 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 8, 0)); |
| 126 | |
| 127 | if (mr->flags & MEAS_REP_F_MS_L1) { |
| 128 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 9, |
| 129 | mr->ms_l1.pwr)); |
| 130 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_ins_mr, 10, |
| 131 | mr->ms_l1.ta)); |
| 132 | } |
| 133 | |
| 134 | SCK_DONE(st->db, sqlite3_step(st->stmt_ins_mr)); |
| 135 | SCK_OK(st->db, sqlite3_reset(st->stmt_ins_mr)); |
| 136 | |
| 137 | rowid = sqlite3_last_insert_rowid(st->db); |
| 138 | |
| 139 | /* insert uplink measurement */ |
| 140 | ul_rowid = _insert_ud(st, rowid, mr->flags & MEAS_REP_F_UL_DTX, |
| 141 | 1, &mr->ul); |
| 142 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_upd_mr, 1, ul_rowid)); |
| 143 | |
| 144 | /* insert downlink measurement, if present */ |
| 145 | if (mr->flags & MEAS_REP_F_DL_VALID) { |
| 146 | dl_rowid = _insert_ud(st, rowid, mr->flags & MEAS_REP_F_DL_DTX, |
| 147 | 0, &mr->dl); |
| 148 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_upd_mr, 2, dl_rowid)); |
| 149 | } else |
| 150 | SCK_OK(st->db, sqlite3_bind_null(st->stmt_upd_mr, 2)); |
| 151 | |
| 152 | /* update meas_rep with the id's of the unidirectional |
| 153 | * measurements */ |
| 154 | SCK_OK(st->db, sqlite3_bind_int(st->stmt_upd_mr, 3, rowid)); |
| 155 | SCK_DONE(st->db, sqlite3_step(st->stmt_upd_mr)); |
| 156 | SCK_OK(st->db, sqlite3_reset(st->stmt_upd_mr)); |
| 157 | |
| 158 | return 0; |
| 159 | |
| 160 | err_io: |
| 161 | return -EIO; |
| 162 | } |
| 163 | |
| 164 | int meas_db_begin(struct meas_db_state *st) |
| 165 | { |
| 166 | SCK_OK(st->db, sqlite3_exec(st->db, "BEGIN", NULL, NULL, NULL)); |
| 167 | |
| 168 | return 0; |
| 169 | |
| 170 | err_io: |
| 171 | return -EIO; |
| 172 | } |
| 173 | |
| 174 | int meas_db_commit(struct meas_db_state *st) |
| 175 | { |
| 176 | SCK_OK(st->db, sqlite3_exec(st->db, "COMMIT", NULL, NULL, NULL)); |
| 177 | |
| 178 | return 0; |
| 179 | |
| 180 | err_io: |
| 181 | return -EIO; |
| 182 | } |
| 183 | |
| 184 | static const char *create_stmts[] = { |
| 185 | "CREATE TABLE IF NOT EXISTS meas_rep (" |
| 186 | "id INTEGER PRIMARY KEY AUTOINCREMENT," |
| 187 | "time TIMESTAMP," |
| 188 | "imsi TEXT," |
| 189 | "name TEXT," |
| 190 | "scenario TEXT," |
| 191 | "nr INTEGER," |
| 192 | "bs_power INTEGER NOT NULL," |
| 193 | "ms_timing_offset INTEGER," |
| 194 | "fpc INTEGER NOT NULL DEFAULT 0," |
| 195 | "ul_unidir INTEGER REFERENCES meas_rep_unidir(id)," |
| 196 | "dl_unidir INTEGER REFERENCES meas_rep_unidir(id)," |
| 197 | "ms_l1_pwr INTEGER," |
| 198 | "ms_l1_ta INTEGER" |
| 199 | ")", |
| 200 | "CREATE TABLE IF NOT EXISTS meas_rep_unidir (" |
| 201 | "id INTEGER PRIMARY KEY AUTOINCREMENT," |
| 202 | "meas_id INTEGER NOT NULL REFERENCES meas_rep(id)," |
| 203 | "rx_lev_full INTEGER NOT NULL," |
| 204 | "rx_lev_sub INTEGER NOT NULL," |
| 205 | "rx_qual_full INTEGER NOT NULL," |
| 206 | "rx_qual_sub INTEGER NOT NULL," |
| 207 | "dtx BOOLEAN NOT NULL DEFAULT 0," |
| 208 | "uplink BOOLEAN NOT NULL" |
| 209 | ")", |
| 210 | "CREATE VIEW IF NOT EXISTS path_loss AS " |
| 211 | "SELECT " |
| 212 | "meas_rep.id, " |
| 213 | "datetime(time,'unixepoch') AS timestamp, " |
| 214 | "imsi, " |
| 215 | "name, " |
| 216 | "scenario, " |
| 217 | "ms_timing_offset, " |
| 218 | "ms_l1_ta, " |
| 219 | "fpc, " |
| 220 | "ms_l1_pwr, " |
| 221 | "ud_ul.rx_lev_full AS ul_rx_lev_full, " |
| 222 | "ms_l1_pwr-ud_ul.rx_lev_full AS ul_path_loss_full, " |
| 223 | "ud_ul.rx_lev_sub ul_rx_lev_sub, " |
| 224 | "ms_l1_pwr-ud_ul.rx_lev_sub AS ul_path_loss_sub, " |
| 225 | "ud_ul.rx_qual_full AS ul_rx_qual_full, " |
| 226 | "ud_ul.rx_qual_sub AS ul_rx_qual_sub, " |
| 227 | "bs_power, " |
| 228 | "ud_dl.rx_lev_full AS dl_rx_lev_full, " |
| 229 | "bs_power-ud_dl.rx_lev_full AS dl_path_loss_full, " |
| 230 | "ud_dl.rx_lev_sub AS dl_rx_lev_sub, " |
| 231 | "bs_power-ud_dl.rx_lev_sub AS dl_path_loss_sub, " |
| 232 | "ud_dl.rx_qual_full AS dl_rx_qual_full, " |
| 233 | "ud_dl.rx_qual_sub AS dl_rx_qual_sub " |
| 234 | "FROM " |
| 235 | "meas_rep, " |
| 236 | "meas_rep_unidir AS ud_dl, " |
| 237 | "meas_rep_unidir AS ud_ul " |
| 238 | "WHERE " |
| 239 | "ud_ul.id = meas_rep.ul_unidir AND " |
| 240 | "ud_dl.id = meas_rep.dl_unidir", |
| 241 | "CREATE VIEW IF NOT EXISTS overview AS " |
| 242 | "SELECT " |
| 243 | "id," |
| 244 | "timestamp," |
| 245 | "imsi," |
| 246 | "name," |
| 247 | "scenario," |
| 248 | "ms_l1_pwr," |
| 249 | "ul_rx_lev_full," |
| 250 | "ul_path_loss_full," |
| 251 | "ul_rx_qual_full," |
| 252 | "bs_power," |
| 253 | "dl_rx_lev_full," |
| 254 | "dl_path_loss_full," |
| 255 | "dl_rx_qual_full " |
| 256 | "FROM path_loss", |
| 257 | }; |
| 258 | |
| 259 | static int check_create_tbl(struct meas_db_state *st) |
| 260 | { |
| 261 | int i, rc; |
| 262 | |
| 263 | for (i = 0; i < ARRAY_SIZE(create_stmts); i++) { |
| 264 | SCK_OK(st->db, sqlite3_exec(st->db, create_stmts[i], |
| 265 | NULL, NULL, NULL)); |
| 266 | } |
| 267 | |
| 268 | return 0; |
| 269 | err_io: |
| 270 | return -EIO; |
| 271 | } |
| 272 | |
| 273 | |
| 274 | #define PREP_CHK(db, stmt, ptr) \ |
| 275 | do { \ |
| 276 | int rc; \ |
| 277 | rc = sqlite3_prepare_v2(db, stmt, strlen(stmt)+1, \ |
| 278 | ptr, NULL); \ |
| 279 | if (rc != SQLITE_OK) { \ |
| 280 | fprintf(stderr, "Error during prepare of '%s': %s\n", \ |
| 281 | stmt, sqlite3_errmsg(db)); \ |
| 282 | goto err_io; \ |
| 283 | } \ |
| 284 | } while (0) |
| 285 | |
| 286 | struct meas_db_state *meas_db_open(void *ctx, const char *fname) |
| 287 | { |
| 288 | int rc; |
| 289 | struct meas_db_state *st = talloc_zero(ctx, struct meas_db_state); |
| 290 | |
| 291 | if (!st) |
| 292 | return NULL; |
| 293 | |
| 294 | rc = sqlite3_open_v2(fname, &st->db, |
| 295 | SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, |
| 296 | NULL); |
| 297 | if (rc != SQLITE_OK) { |
| 298 | fprintf(stderr, "Unable to open DB: %s\n", |
| 299 | sqlite3_errmsg(st->db)); |
| 300 | goto err_io; |
| 301 | } |
| 302 | |
| 303 | rc = check_create_tbl(st); |
| 304 | |
| 305 | PREP_CHK(st->db, INS_MR, &st->stmt_ins_mr); |
| 306 | PREP_CHK(st->db, INS_UD, &st->stmt_ins_ud); |
| 307 | PREP_CHK(st->db, UPD_MR, &st->stmt_upd_mr); |
| 308 | |
| 309 | return st; |
| 310 | err_io: |
| 311 | talloc_free(st); |
| 312 | return NULL; |
| 313 | } |
| 314 | |
| 315 | void meas_db_close(struct meas_db_state *st) |
| 316 | { |
Neels Hofmeyr | b67632b | 2016-03-01 13:05:27 +0100 | [diff] [blame] | 317 | if (sqlite3_finalize(st->stmt_ins_mr) != SQLITE_OK) |
| 318 | fprintf(stderr, "DB insert measurement report finalize error: %s\n", |
| 319 | sqlite3_errmsg(st->db)); |
| 320 | if (sqlite3_finalize(st->stmt_ins_ud) != SQLITE_OK) |
| 321 | fprintf(stderr, "DB insert unidir finalize error: %s\n", |
| 322 | sqlite3_errmsg(st->db)); |
| 323 | if (sqlite3_finalize(st->stmt_upd_mr) != SQLITE_OK) |
| 324 | fprintf(stderr, "DB update measurement report finalize error: %s\n", |
| 325 | sqlite3_errmsg(st->db)); |
Holger Hans Peter Freyther | 9d29545 | 2016-03-15 14:21:49 +0100 | [diff] [blame] | 326 | if (sqlite3_close(st->db) != SQLITE_OK) |
| 327 | fprintf(stderr, "Unable to close DB, abandoning.\n"); |
Harald Welte | b3487b1 | 2012-11-15 00:12:56 +0100 | [diff] [blame] | 328 | |
| 329 | talloc_free(st); |
| 330 | |
| 331 | } |