sql: add table subscriber_imsi_pseudo

Related: OS#4476
Change-Id: I5d66ae11102dd616d1ce468034241493719f9db2

3 files changed, 46 insertions, 3 deletions

diff --git a/sql/hlr.sql b/sql/hlr.sql
index 98e586d..49c40e1 100644
--- a/sql/hlr.sql
+++ b/sql/hlr.sql
@@ -62,6 +62,16 @@ CREATE TABLE subscriber_multi_msisdn (
 	msisdn		VARCHAR(15) NOT NULL
 );
 
+CREATE TABLE subscriber_imsi_pseudo (
+-- https://osmocom.org/projects/imsi-pseudo/wiki
+	id		INTEGER PRIMARY KEY,
+	subscriber_id	INTEGER NOT NULL,	-- subscriber.id
+	imsi_pseudo	VARCHAR(15) UNIQUE NOT NULL,
+	imsi_pseudo_i	INTEGER default 0 NOT NULL
+);
+
+/* FIXME: index for imsi_pseudo and subscriber_id for better performance */
+
 CREATE TABLE auc_2g (
 	subscriber_id	INTEGER PRIMARY KEY,	-- subscriber.id
 	algo_id_2g	INTEGER NOT NULL,	-- enum osmo_auth_algo value
@@ -83,4 +93,4 @@ CREATE UNIQUE INDEX idx_subscr_imsi ON subscriber (imsi);
 
 -- 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 = 5;
+PRAGMA user_version = 6;
diff --git a/src/db.c b/src/db.c
index 5ec20e2..325b63a 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	5
+#define CURRENT_SCHEMA_VERSION	6
 
 #define SEL_COLUMNS \
 	"id," \
@@ -479,6 +479,28 @@ static int db_upgrade_v5(struct db_context *dbc)
 	return rc;
 }
 
+static int db_upgrade_v6(struct db_context *dbc)
+{
+	int rc;
+	const char *statements[] = {
+		"CREATE TABLE subscriber_imsi_pseudo (\n"
+		"-- https://osmocom.org/projects/imsi-pseudo/wiki\n"
+			"id		INTEGER PRIMARY KEY,\n"
+			"subscriber_id	INTEGER NOT NULL,	-- subscriber.id\n"
+			"imsi_pseudo	VARCHAR(15) UNIQUE NOT NULL,\n"
+			"imsi_pseudo_i	INTEGER default 0 NOT NULL\n"
+		")",
+		"PRAGMA user_version = 6"
+	};
+
+	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 6\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,
@@ -486,6 +508,7 @@ static db_upgrade_func_t db_upgrade_path[] = {
 	db_upgrade_v3,
 	db_upgrade_v4,
 	db_upgrade_v5,
+	db_upgrade_v6,
 };
 
 static int db_get_user_version(struct db_context *dbc)
diff --git a/tests/db_upgrade/db_upgrade_test.ok b/tests/db_upgrade/db_upgrade_test.ok
index 2bc6a39..352bb4b 100644
--- a/tests/db_upgrade/db_upgrade_test.ok
+++ b/tests/db_upgrade/db_upgrade_test.ok
@@ -85,6 +85,7 @@ DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 2
 DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 3
 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
 DMAIN Cmdline option --db-check: Database was opened successfully, quitting.
 
 Resulting db:
@@ -159,6 +160,15 @@ subscriber_id|INTEGER|0||0
 
 Table subscriber_apn contents:
 
+Table: subscriber_imsi_pseudo
+name|type|notnull|dflt_value|pk
+id|INTEGER|0||1
+imsi_pseudo|VARCHAR(15)|1||0
+imsi_pseudo_i|INTEGER|1|0|0
+subscriber_id|INTEGER|1||0
+
+Table subscriber_imsi_pseudo contents:
+
 Table: subscriber_multi_msisdn
 name|type|notnull|dflt_value|pk
 msisdn|VARCHAR(15)|1||0
@@ -171,5 +181,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 5
+DDB Database <PATH>test.db' has HLR DB schema version 6
 DMAIN Cmdline option --db-check: Database was opened successfully, quitting.