Add IMEI column to subscriber table
Extend the database scheme, add imei to the hlr_subscriber struct and
create db_subscr_update_imei_by_imsi() and db_subscr_get_by_imei(). The
new functions are used in db_test, and in follow-up commits [1], [2].
Upgrade DB schema to version 2. SQLite can only insert new columns at
the end of the table, so this happens when upgrading the database. In
new databases, the column is placed after the IMEISV column (where it
makes more sense in my opinion). This should not have any effect, as
we never rely on the order of the columns in the tables.
Follow-up commit [1] will make use of this column to save the IMEI as
received from the MSC/VLR with the Check-IMEI Procedure. It was
decided to use Check-IMEI instead of the recent Automatic Device
Detection Procedure (which would send the IMEISV) in OS#3733, because
with Check-IMEI we don't need to rely on very recent releases of the
specification.
[1] change-id I09274ecbed64224f7ae305e09ede773931da2a57
"Optionally store IMEI in subscriber table"
[2] change-id I1af7b573ca2a1cb22497052665012d9c1acf3b30
"VTY: integrate IMEI"
Depends: Id2d2a3a93b033bafc74c62e15297034bf4aafe61 (libosmocore)
Related: OS#2541
Change-Id: If232c80bea35d5c6864b889ae92d477eeaa3f45d
diff --git a/src/db.c b/src/db.c
index f7cbace..09e1776 100644
--- a/src/db.c
+++ b/src/db.c
@@ -28,12 +28,13 @@
#include "db_bootstrap.h"
/* This constant is currently duplicated in sql/hlr.sql and must be kept in sync! */
-#define CURRENT_SCHEMA_VERSION 1
+#define CURRENT_SCHEMA_VERSION 2
#define SEL_COLUMNS \
"id," \
"imsi," \
"msisdn," \
+ "imei," \
"vlr_number," \
"sgsn_number," \
"sgsn_address," \
@@ -50,8 +51,10 @@
[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_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"
" FROM subscriber"
@@ -290,6 +293,40 @@
return rc;
}
+static int db_upgrade_v2(struct db_context *dbc)
+{
+ sqlite3_stmt *stmt;
+ int rc;
+ const char *update_stmt_sql = "ALTER TABLE subscriber ADD COLUMN imei VARCHAR(14) default NULL";
+ const char *set_schema_version_sql = "PRAGMA user_version = 2";
+
+ 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 %d\n", 1);
+
+ 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";
@@ -400,6 +437,15 @@
}
version = 1;
/* fall through */
+ case 1:
+ rc = db_upgrade_v2(dbc);
+ if (rc != SQLITE_DONE) {
+ LOGP(DDB, LOGL_ERROR, "Failed to upgrade HLR DB schema to version 2: (rc=%d) %s\n",
+ rc, sqlite3_errmsg(dbc->db));
+ goto out_free;
+ }
+ version = 2;
+ /* fall through */
/* case N: ... */
default:
break;