Change in osmo-hlr[master]: add db_upgrade test

This is merely a historical archive of years 2008-2021, before the migration to mailman3.

A maintained and still updated list archive can be found at https://lists.osmocom.org/hyperkitty/list/gerrit-log@lists.osmocom.org/.

neels gerrit-no-reply at lists.osmocom.org
Thu Oct 31 03:49:59 UTC 2019 

neels has uploaded this change for review. ( https://gerrit.osmocom.org/c/osmo-hlr/+/15913 )


Change subject: add db_upgrade test
......................................................................

add db_upgrade test

We have a database schema upgrade path, but so far nothing that verifies that
we don't break it. It almost seems like the user data weren't important to us!?

Add a db upgrade test:
- Create a db with an .sql dump taken from a db created with an old osmo-hlr,
  producing DB schema version 0.
- Run osmo-hlr --db-upgrade --db-check
- Verify that the upgrade exited successfully.
- Verify that restarting with the upgraded DB works.

If python tests are enabled, also:
- create a new database using the new osmo-hlr (just built).
- replay a VTY transcript to create subscribers as in the old snapshot.
- replay some sql modifications as done in the old snapshot.
- Get a list of sorted table names,
- a list of their sorted columns with all their properties,
- and dump the table contents in a column- and value-sorted way.
- Compare the resulting dumps and error if there are any diffs.
(This is how I found the difference in the imei column that was fixed in
I68a00014a3d603fcba8781470bc5285f78b538d0)

Change-Id: I0961bab0e17cfde5b030576c5bc243c2b51d9dc4
---
M configure.ac
M src/hlr.c
M tests/Makefile.am
A tests/db_upgrade/Makefile.am
A tests/db_upgrade/create_subscribers.vty
A tests/db_upgrade/create_subscribers_step2.sql
A tests/db_upgrade/db_upgrade_test.err
A tests/db_upgrade/db_upgrade_test.ok
A tests/db_upgrade/db_upgrade_test.sh
A tests/db_upgrade/hlr_db_v0.sql
A tests/db_upgrade/osmo-hlr.cfg
M tests/testsuite.at
12 files changed, 434 insertions(+), 6 deletions(-)



  git pull ssh://gerrit.osmocom.org:29418/osmo-hlr refs/changes/13/15913/1

diff --git a/configure.ac b/configure.ac
index 993d4d5..ca78f38 100644
--- a/configure.ac
+++ b/configure.ac
@@ -186,4 +186,5 @@
 	tests/gsup_server/Makefile
 	tests/gsup/Makefile
 	tests/db/Makefile
+	tests/db_upgrade/Makefile
 	)
diff --git a/src/hlr.c b/src/hlr.c
index f9cc2f5..6bfc141 100644
--- a/src/hlr.c
+++ b/src/hlr.c
@@ -862,12 +862,6 @@
 		return rc;
 	}
 
-	/* start telnet after reading config for vty_get_bind_addr() */
-	rc = telnet_init_dynif(hlr_ctx, NULL, vty_get_bind_addr(),
-			       OSMO_VTY_PORT_HLR);
-	if (rc < 0)
-		return rc;
-
 	LOGP(DMAIN, LOGL_NOTICE, "hlr starting\n");
 
 	rc = rand_init();
@@ -895,6 +889,13 @@
 		exit(0);
 	}
 
+	/* start telnet after reading config for vty_get_bind_addr() */
+	rc = telnet_init_dynif(hlr_ctx, NULL, vty_get_bind_addr(),
+			       OSMO_VTY_PORT_HLR);
+	if (rc < 0)
+		return rc;
+
+
 	g_hlr->gs = osmo_gsup_server_create(hlr_ctx, g_hlr->gsup_bind_addr, OSMO_GSUP_PORT,
 					    read_cb, &g_lu_ops, g_hlr);
 	if (!g_hlr->gs) {
diff --git a/tests/Makefile.am b/tests/Makefile.am
index 4da8ab1..357fbac 100644
--- a/tests/Makefile.am
+++ b/tests/Makefile.am
@@ -3,6 +3,7 @@
 	gsup_server \
 	db \
 	gsup \
+	db_upgrade \
 	$(NULL)
 
 # The `:;' works around a Bash 3.2 bug when the output is not writeable.
@@ -44,6 +45,7 @@
 # don't run vty and ctrl tests concurrently so that the ports don't conflict
 	$(MAKE) vty-test
 	$(MAKE) ctrl-test
+	$(MAKE) db-upgrade-equivalence-test
 else
 python-tests:
 	echo "Not running python-based external tests (determined at configure-time)"
@@ -81,6 +83,9 @@
 	-rm -f $(CTRL_TEST_DB)
 	-rm $(CTRL_TEST_DB)-*
 
+db-upgrade-equivalence-test:
+	$(MAKE) -C db_upgrade upgrade-equivalence-test
+
 check-local: atconfig $(TESTSUITE)
 	$(SHELL) '$(TESTSUITE)' $(TESTSUITEFLAGS)
 	$(MAKE) $(AM_MAKEFLAGS) python-tests
diff --git a/tests/db_upgrade/Makefile.am b/tests/db_upgrade/Makefile.am
new file mode 100644
index 0000000..79136c9
--- /dev/null
+++ b/tests/db_upgrade/Makefile.am
@@ -0,0 +1,14 @@
+EXTRA_DIST = \
+	db_upgrade_test.sh \
+	db_upgrade_test.err \
+	db_upgrade_test.ok \
+	hlr_db_v0.sql \
+	osmo-hlr.cfg \
+	create_subscribers.vty \
+	$(NULL)
+
+update_exp:
+	$(srcdir)/db_upgrade_test.sh $(srcdir) $(builddir) >"$(srcdir)/db_upgrade_test.ok" 2>"$(srcdir)/db_upgrade_test.err"
+
+upgrade-equivalence-test:
+	$(srcdir)/db_upgrade_test.sh $(srcdir) $(builddir) do-equivalence-test
diff --git a/tests/db_upgrade/create_subscribers.vty b/tests/db_upgrade/create_subscribers.vty
new file mode 100644
index 0000000..30eeba6
--- /dev/null
+++ b/tests/db_upgrade/create_subscribers.vty
@@ -0,0 +1,47 @@
+OsmoHLR> enable
+OsmoHLR# subscriber imsi 123456789012345 create
+% Created subscriber 123456789012345
+    ID: 1
+    IMSI: 123456789012345
+    MSISDN: none
+OsmoHLR# subscriber imsi 123456789012345 update msisdn 098765432109876
+% Updated subscriber IMSI='123456789012345' to MSISDN='098765432109876'
+OsmoHLR# subscriber imsi 123456789012345 update aud2g comp128v1 ki BeefedCafeFaceAcedAddedDecadeFee
+OsmoHLR# subscriber imsi 123456789012345 update aud3g milenage k C01ffedC1cadaeAc1d1f1edAcac1aB0a opc CededEffacedAceFacedBadFadedBeef
+OsmoHLR# subscriber imsi 111111111 create
+% Created subscriber 111111111
+    ID: 2
+    IMSI: 111111111
+    MSISDN: none
+OsmoHLR# subscriber imsi 222222222 create
+% Created subscriber 222222222
+    ID: 3
+    IMSI: 222222222
+    MSISDN: none
+OsmoHLR# subscriber imsi 222222222 update msisdn 22222
+% Updated subscriber IMSI='222222222' to MSISDN='22222'
+OsmoHLR# subscriber imsi 333333 create
+% Created subscriber 333333
+    ID: 4
+    IMSI: 333333
+    MSISDN: none
+OsmoHLR# subscriber imsi 333333 update msisdn 3
+% Updated subscriber IMSI='333333' to MSISDN='3'
+OsmoHLR# subscriber imsi 333333 update aud2g comp128v2 ki 33333333333333333333333333333333
+OsmoHLR# subscriber imsi 444444444444444 create
+% Created subscriber 444444444444444
+    ID: 5
+    IMSI: 444444444444444
+    MSISDN: none
+OsmoHLR# subscriber imsi 444444444444444 update msisdn 4444
+% Updated subscriber IMSI='444444444444444' to MSISDN='4444'
+OsmoHLR# subscriber imsi 444444444444444 update aud3g milenage k 44444444444444444444444444444444 op 44444444444444444444444444444444
+OsmoHLR# subscriber imsi 5555555 create
+% Created subscriber 5555555
+    ID: 6
+    IMSI: 5555555
+    MSISDN: none
+OsmoHLR# subscriber imsi 5555555 update msisdn 55555555555555
+% Updated subscriber IMSI='5555555' to MSISDN='55555555555555'
+OsmoHLR# subscriber imsi 5555555 update aud2g xor ki 55555555555555555555555555555555
+OsmoHLR# subscriber imsi 5555555 update aud3g milenage k 55555555555555555555555555555555 opc 55555555555555555555555555555555
diff --git a/tests/db_upgrade/create_subscribers_step2.sql b/tests/db_upgrade/create_subscribers_step2.sql
new file mode 100644
index 0000000..7933e25
--- /dev/null
+++ b/tests/db_upgrade/create_subscribers_step2.sql
@@ -0,0 +1,6 @@
+update subscriber set vlr_number = 'MSC-1' where id = 1;
+update subscriber set ms_purged_cs = 1 where id = 2;
+update subscriber set ms_purged_ps = 1 where id = 3;
+update subscriber set nam_cs = 0 where id = 4;
+update subscriber set nam_ps = 0 where id = 5;
+update subscriber set nam_cs = 0, nam_ps = 0 where id = 6;
diff --git a/tests/db_upgrade/db_upgrade_test.err b/tests/db_upgrade/db_upgrade_test.err
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/tests/db_upgrade/db_upgrade_test.err
diff --git a/tests/db_upgrade/db_upgrade_test.ok b/tests/db_upgrade/db_upgrade_test.ok
new file mode 100644
index 0000000..7fa1258
--- /dev/null
+++ b/tests/db_upgrade/db_upgrade_test.ok
@@ -0,0 +1,180 @@
+Creating db in schema version 0
+
+Version 0 db:
+PRAGMA foreign_keys=OFF;
+BEGIN TRANSACTION;
+CREATE TABLE subscriber (
+-- OsmoHLR's DB scheme is modelled roughly after TS 23.008 version 13.3.0
+	id		INTEGER PRIMARY KEY,
+	-- Chapter 2.1.1.1
+	imsi		VARCHAR(15) UNIQUE NOT NULL,
+	-- Chapter 2.1.2
+	msisdn		VARCHAR(15) UNIQUE,
+	-- Chapter 2.2.3: Most recent / current IMEI
+	imeisv		VARCHAR,
+	-- Chapter 2.4.5
+	vlr_number	VARCHAR(15),
+	-- Chapter 2.4.6
+	hlr_number	VARCHAR(15),
+	-- Chapter 2.4.8.1
+	sgsn_number	VARCHAR(15),
+	-- Chapter 2.13.10
+	sgsn_address	VARCHAR,
+	-- Chapter 2.4.8.2
+	ggsn_number	VARCHAR(15),
+	-- Chapter 2.4.9.2
+	gmlc_number	VARCHAR(15),
+	-- Chapter 2.4.23
+	smsc_number	VARCHAR(15),
+	-- Chapter 2.4.24
+	periodic_lu_tmr	INTEGER,
+	-- Chapter 2.13.115
+	periodic_rau_tau_tmr INTEGER,
+	-- Chapter 2.1.1.2: network access mode
+	nam_cs		BOOLEAN NOT NULL DEFAULT 1,
+	nam_ps		BOOLEAN NOT NULL DEFAULT 1,
+	-- Chapter 2.1.8
+	lmsi		INTEGER,
+	-- The below purged flags might not even be stored non-volatile,
+	-- refer to TS 23.012 Chapter 3.6.1.4
+	-- Chapter 2.7.5
+	ms_purged_cs	BOOLEAN NOT NULL DEFAULT 0,
+	-- Chapter 2.7.6
+	ms_purged_ps	BOOLEAN NOT NULL DEFAULT 0
+);
+INSERT INTO subscriber VALUES(1,'123456789012345','098765432109876',NULL,'MSC-1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,NULL,0,0);
+INSERT INTO subscriber VALUES(2,'111111111',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,NULL,1,0);
+INSERT INTO subscriber VALUES(3,'222222222','22222',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,NULL,0,1);
+INSERT INTO subscriber VALUES(4,'333333','3',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,1,NULL,0,0);
+INSERT INTO subscriber VALUES(5,'444444444444444','4444',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,NULL,0,0);
+INSERT INTO subscriber VALUES(6,'5555555','55555555555555',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,0,0);
+CREATE TABLE subscriber_apn (
+	subscriber_id	INTEGER,		-- subscriber.id
+	apn		VARCHAR(256) NOT NULL
+);
+CREATE TABLE subscriber_multi_msisdn (
+-- Chapter 2.1.3
+	subscriber_id	INTEGER,		-- subscriber.id
+	msisdn		VARCHAR(15) NOT NULL
+);
+CREATE TABLE auc_2g (
+	subscriber_id	INTEGER PRIMARY KEY,	-- subscriber.id
+	algo_id_2g	INTEGER NOT NULL,	-- enum osmo_auth_algo value
+	ki		VARCHAR(32) NOT NULL	-- hex string: subscriber's secret key (128bit)
+);
+INSERT INTO auc_2g VALUES(1,1,'BeefedCafeFaceAcedAddedDecadeFee');
+INSERT INTO auc_2g VALUES(4,2,'33333333333333333333333333333333');
+INSERT INTO auc_2g VALUES(6,4,'55555555555555555555555555555555');
+CREATE TABLE auc_3g (
+	subscriber_id	INTEGER PRIMARY KEY,	-- subscriber.id
+	algo_id_3g	INTEGER NOT NULL,	-- enum osmo_auth_algo value
+	k		VARCHAR(32) NOT NULL,	-- hex string: subscriber's secret key (128bit)
+	op		VARCHAR(32),		-- hex string: operator's secret key (128bit)
+	opc		VARCHAR(32),		-- hex string: derived from OP and K (128bit)
+	sqn		INTEGER NOT NULL DEFAULT 0,	-- sequence number of key usage
+	ind_bitlen	INTEGER NOT NULL DEFAULT 5	-- nr of index bits at lower SQN end
+);
+INSERT INTO auc_3g VALUES(1,5,'C01ffedC1cadaeAc1d1f1edAcac1aB0a',NULL,'CededEffacedAceFacedBadFadedBeef',0,5);
+INSERT INTO auc_3g VALUES(5,5,'44444444444444444444444444444444','44444444444444444444444444444444',NULL,0,5);
+INSERT INTO auc_3g VALUES(6,5,'55555555555555555555555555555555',NULL,'55555555555555555555555555555555',0,5);
+CREATE UNIQUE INDEX idx_subscr_imsi ON subscriber (imsi)
+;
+COMMIT;
+
+Launching osmo-hlr to upgrade db:
+osmo-hlr --database $db --db-upgrade --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 0
+DDB Database <PATH>test.db' has been upgraded to HLR DB schema version 2
+DMAIN Cmdline option --db-check: Database was opened successfully, quitting.
+
+Resulting db:
+PRAGMA foreign_keys=OFF;
+BEGIN TRANSACTION;
+CREATE TABLE subscriber (
+-- OsmoHLR's DB scheme is modelled roughly after TS 23.008 version 13.3.0
+	id		INTEGER PRIMARY KEY,
+	-- Chapter 2.1.1.1
+	imsi		VARCHAR(15) UNIQUE NOT NULL,
+	-- Chapter 2.1.2
+	msisdn		VARCHAR(15) UNIQUE,
+	-- Chapter 2.2.3: Most recent / current IMEI
+	imeisv		VARCHAR,
+	-- Chapter 2.4.5
+	vlr_number	VARCHAR(15),
+	-- Chapter 2.4.6
+	hlr_number	VARCHAR(15),
+	-- Chapter 2.4.8.1
+	sgsn_number	VARCHAR(15),
+	-- Chapter 2.13.10
+	sgsn_address	VARCHAR,
+	-- Chapter 2.4.8.2
+	ggsn_number	VARCHAR(15),
+	-- Chapter 2.4.9.2
+	gmlc_number	VARCHAR(15),
+	-- Chapter 2.4.23
+	smsc_number	VARCHAR(15),
+	-- Chapter 2.4.24
+	periodic_lu_tmr	INTEGER,
+	-- Chapter 2.13.115
+	periodic_rau_tau_tmr INTEGER,
+	-- Chapter 2.1.1.2: network access mode
+	nam_cs		BOOLEAN NOT NULL DEFAULT 1,
+	nam_ps		BOOLEAN NOT NULL DEFAULT 1,
+	-- Chapter 2.1.8
+	lmsi		INTEGER,
+	-- The below purged flags might not even be stored non-volatile,
+	-- refer to TS 23.012 Chapter 3.6.1.4
+	-- Chapter 2.7.5
+	ms_purged_cs	BOOLEAN NOT NULL DEFAULT 0,
+	-- Chapter 2.7.6
+	ms_purged_ps	BOOLEAN NOT NULL DEFAULT 0
+, last_lu_seen TIMESTAMP default NULL, imei VARCHAR(14));
+INSERT INTO subscriber VALUES(1,'123456789012345','098765432109876',NULL,'MSC-1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,NULL,0,0,NULL,NULL);
+INSERT INTO subscriber VALUES(2,'111111111',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,NULL,1,0,NULL,NULL);
+INSERT INTO subscriber VALUES(3,'222222222','22222',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,NULL,0,1,NULL,NULL);
+INSERT INTO subscriber VALUES(4,'333333','3',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,1,NULL,0,0,NULL,NULL);
+INSERT INTO subscriber VALUES(5,'444444444444444','4444',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,NULL,0,0,NULL,NULL);
+INSERT INTO subscriber VALUES(6,'5555555','55555555555555',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,0,0,NULL,NULL);
+CREATE TABLE subscriber_apn (
+	subscriber_id	INTEGER,		-- subscriber.id
+	apn		VARCHAR(256) NOT NULL
+);
+CREATE TABLE subscriber_multi_msisdn (
+-- Chapter 2.1.3
+	subscriber_id	INTEGER,		-- subscriber.id
+	msisdn		VARCHAR(15) NOT NULL
+);
+CREATE TABLE auc_2g (
+	subscriber_id	INTEGER PRIMARY KEY,	-- subscriber.id
+	algo_id_2g	INTEGER NOT NULL,	-- enum osmo_auth_algo value
+	ki		VARCHAR(32) NOT NULL	-- hex string: subscriber's secret key (128bit)
+);
+INSERT INTO auc_2g VALUES(1,1,'BeefedCafeFaceAcedAddedDecadeFee');
+INSERT INTO auc_2g VALUES(4,2,'33333333333333333333333333333333');
+INSERT INTO auc_2g VALUES(6,4,'55555555555555555555555555555555');
+CREATE TABLE auc_3g (
+	subscriber_id	INTEGER PRIMARY KEY,	-- subscriber.id
+	algo_id_3g	INTEGER NOT NULL,	-- enum osmo_auth_algo value
+	k		VARCHAR(32) NOT NULL,	-- hex string: subscriber's secret key (128bit)
+	op		VARCHAR(32),		-- hex string: operator's secret key (128bit)
+	opc		VARCHAR(32),		-- hex string: derived from OP and K (128bit)
+	sqn		INTEGER NOT NULL DEFAULT 0,	-- sequence number of key usage
+	ind_bitlen	INTEGER NOT NULL DEFAULT 5	-- nr of index bits at lower SQN end
+);
+INSERT INTO auc_3g VALUES(1,5,'C01ffedC1cadaeAc1d1f1edAcac1aB0a',NULL,'CededEffacedAceFacedBadFadedBeef',0,5);
+INSERT INTO auc_3g VALUES(5,5,'44444444444444444444444444444444','44444444444444444444444444444444',NULL,0,5);
+INSERT INTO auc_3g VALUES(6,5,'55555555555555555555555555555555',NULL,'55555555555555555555555555555555',0,5);
+CREATE UNIQUE INDEX idx_subscr_imsi ON subscriber (imsi)
+;
+COMMIT;
+
+Verify that osmo-hlr can open it:
+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 2
+DMAIN Cmdline option --db-check: Database was opened successfully, quitting.
diff --git a/tests/db_upgrade/db_upgrade_test.sh b/tests/db_upgrade/db_upgrade_test.sh
new file mode 100755
index 0000000..189ebf3
--- /dev/null
+++ b/tests/db_upgrade/db_upgrade_test.sh
@@ -0,0 +1,82 @@
+#!/bin/sh
+srcdir="${1:-.}"
+builddir="${2:-.}"
+do_equivalence_test="$3"
+
+set -e
+
+db="$builddir/test.db"
+osmo_hlr="$builddir/../../src/osmo-hlr"
+cfg="$srcdir/osmo-hlr.cfg"
+
+rm -f "$db"
+echo "Creating db in schema version 0"
+sqlite3 "$db" < "$srcdir/hlr_db_v0.sql"
+
+echo
+echo "Version 0 db:"
+sqlite3 "$db" .dump
+
+set +e
+
+echo
+echo "Launching osmo-hlr to upgrade db:"
+echo osmo-hlr --database '$db' --db-upgrade --db-check --config-file '$srcdir/osmo-hlr.cfg'
+"$osmo_hlr" --database "$db" --db-upgrade --db-check --config-file "$cfg" >log 2>&1
+echo "rc = $?"
+cat log | sed 's@[^ "]*/@<PATH>@g'
+
+echo
+echo "Resulting db:"
+sqlite3 "$db" .dump
+
+echo
+echo "Verify that osmo-hlr can open it:"
+echo osmo-hlr --database '$db' --db-check --config-file '$srcdir/osmo-hlr.cfg'
+"$osmo_hlr" --database "$db" --db-check --config-file "$cfg" >log 2>&1
+echo "rc = $?"
+cat log | sed 's@[^ "]*/@<PATH>@g'
+
+dump_sorted_schema(){
+	db_file="$1"
+	tables="$(sqlite3 "$db_file" "SELECT name FROM sqlite_master WHERE type = 'table' order by name")"
+	for table in $tables; do
+		echo
+		echo "Table: $table"
+		sqlite3 -header "$db_file" "SELECT name,type,\"notnull\",dflt_value,pk FROM PRAGMA_TABLE_INFO('$table') order by name;"
+		echo
+		echo "Table $table contents:"
+		columns="$(sqlite3 "$db_file" "SELECT name FROM PRAGMA_TABLE_INFO('$table') order by name;")"
+		sqlite3 -header "$db_file" "SELECT $(echo $columns | sed 's/ /,/g') from $table;"
+	done
+}
+
+if [ -n "$do_equivalence_test" ]; then
+	# this part requires osmo_interact_vty.py, so this test is not part of the normal run
+	set -e -x
+	mint_db="$builddir/mint.db"
+	rm -f "$mint_db"
+
+	osmo_verify_transcript_vty.py -v \
+		-n OsmoHLR -p 4258 \
+		-r "$osmo_hlr -c $cfg -l $mint_db" \
+		"$srcdir/create_subscribers.vty"
+	sqlite3 "$mint_db" < "$srcdir/create_subscribers_step2.sql"
+
+	set +x
+	test_dump="$builddir/test.dump"
+	mint_dump="$builddir/mint.dump"
+
+	dump_sorted_schema "$db" > "$test_dump"
+	dump_sorted_schema "$mint_db" > "$mint_dump"
+
+	echo
+	echo "Newly created sorted schema is:"
+	cat "$mint_dump"
+	echo
+	echo "Diff to upgraded schema:"
+	diff -u "$mint_dump" "$test_dump"
+	echo "rc=$?"
+fi
+
+rm -f "$db"
diff --git a/tests/db_upgrade/hlr_db_v0.sql b/tests/db_upgrade/hlr_db_v0.sql
new file mode 100644
index 0000000..87fcfd3
--- /dev/null
+++ b/tests/db_upgrade/hlr_db_v0.sql
@@ -0,0 +1,79 @@
+PRAGMA foreign_keys=OFF;
+BEGIN TRANSACTION;
+CREATE TABLE subscriber (
+-- OsmoHLR's DB scheme is modelled roughly after TS 23.008 version 13.3.0
+	id		INTEGER PRIMARY KEY,
+	-- Chapter 2.1.1.1
+	imsi		VARCHAR(15) UNIQUE NOT NULL,
+	-- Chapter 2.1.2
+	msisdn		VARCHAR(15) UNIQUE,
+	-- Chapter 2.2.3: Most recent / current IMEI
+	imeisv		VARCHAR,
+	-- Chapter 2.4.5
+	vlr_number	VARCHAR(15),
+	-- Chapter 2.4.6
+	hlr_number	VARCHAR(15),
+	-- Chapter 2.4.8.1
+	sgsn_number	VARCHAR(15),
+	-- Chapter 2.13.10
+	sgsn_address	VARCHAR,
+	-- Chapter 2.4.8.2
+	ggsn_number	VARCHAR(15),
+	-- Chapter 2.4.9.2
+	gmlc_number	VARCHAR(15),
+	-- Chapter 2.4.23
+	smsc_number	VARCHAR(15),
+	-- Chapter 2.4.24
+	periodic_lu_tmr	INTEGER,
+	-- Chapter 2.13.115
+	periodic_rau_tau_tmr INTEGER,
+	-- Chapter 2.1.1.2: network access mode
+	nam_cs		BOOLEAN NOT NULL DEFAULT 1,
+	nam_ps		BOOLEAN NOT NULL DEFAULT 1,
+	-- Chapter 2.1.8
+	lmsi		INTEGER,
+	-- The below purged flags might not even be stored non-volatile,
+	-- refer to TS 23.012 Chapter 3.6.1.4
+	-- Chapter 2.7.5
+	ms_purged_cs	BOOLEAN NOT NULL DEFAULT 0,
+	-- Chapter 2.7.6
+	ms_purged_ps	BOOLEAN NOT NULL DEFAULT 0
+);
+INSERT INTO subscriber VALUES(1,'123456789012345','098765432109876',NULL,'MSC-1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,NULL,0,0);
+INSERT INTO subscriber VALUES(2,'111111111',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,NULL,1,0);
+INSERT INTO subscriber VALUES(3,'222222222','22222',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,NULL,0,1);
+INSERT INTO subscriber VALUES(4,'333333','3',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,1,NULL,0,0);
+INSERT INTO subscriber VALUES(5,'444444444444444','4444',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,NULL,0,0);
+INSERT INTO subscriber VALUES(6,'5555555','55555555555555',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,0,0);
+CREATE TABLE subscriber_apn (
+	subscriber_id	INTEGER,		-- subscriber.id
+	apn		VARCHAR(256) NOT NULL
+);
+CREATE TABLE subscriber_multi_msisdn (
+-- Chapter 2.1.3
+	subscriber_id	INTEGER,		-- subscriber.id
+	msisdn		VARCHAR(15) NOT NULL
+);
+CREATE TABLE auc_2g (
+	subscriber_id	INTEGER PRIMARY KEY,	-- subscriber.id
+	algo_id_2g	INTEGER NOT NULL,	-- enum osmo_auth_algo value
+	ki		VARCHAR(32) NOT NULL	-- hex string: subscriber's secret key (128bit)
+);
+INSERT INTO auc_2g VALUES(1,1,'BeefedCafeFaceAcedAddedDecadeFee');
+INSERT INTO auc_2g VALUES(4,2,'33333333333333333333333333333333');
+INSERT INTO auc_2g VALUES(6,4,'55555555555555555555555555555555');
+CREATE TABLE auc_3g (
+	subscriber_id	INTEGER PRIMARY KEY,	-- subscriber.id
+	algo_id_3g	INTEGER NOT NULL,	-- enum osmo_auth_algo value
+	k		VARCHAR(32) NOT NULL,	-- hex string: subscriber's secret key (128bit)
+	op		VARCHAR(32),		-- hex string: operator's secret key (128bit)
+	opc		VARCHAR(32),		-- hex string: derived from OP and K (128bit)
+	sqn		INTEGER NOT NULL DEFAULT 0,	-- sequence number of key usage
+	ind_bitlen	INTEGER NOT NULL DEFAULT 5	-- nr of index bits at lower SQN end
+);
+INSERT INTO auc_3g VALUES(1,5,'C01ffedC1cadaeAc1d1f1edAcac1aB0a',NULL,'CededEffacedAceFacedBadFadedBeef',0,5);
+INSERT INTO auc_3g VALUES(5,5,'44444444444444444444444444444444','44444444444444444444444444444444',NULL,0,5);
+INSERT INTO auc_3g VALUES(6,5,'55555555555555555555555555555555',NULL,'55555555555555555555555555555555',0,5);
+CREATE UNIQUE INDEX idx_subscr_imsi ON subscriber (imsi)
+;
+COMMIT;
diff --git a/tests/db_upgrade/osmo-hlr.cfg b/tests/db_upgrade/osmo-hlr.cfg
new file mode 100644
index 0000000..7fb12c1
--- /dev/null
+++ b/tests/db_upgrade/osmo-hlr.cfg
@@ -0,0 +1,6 @@
+log stderr
+ logging level db notice
+ logging print category-hex 0
+ logging print file 0
+ logging print category 1
+ logging color 0
diff --git a/tests/testsuite.at b/tests/testsuite.at
index 70ae7ae..a8efe42 100644
--- a/tests/testsuite.at
+++ b/tests/testsuite.at
@@ -36,3 +36,10 @@
 sqlite3 db_test.db < $abs_top_srcdir/sql/hlr.sql
 AT_CHECK([$abs_top_builddir/tests/db/db_test], [], [expout], [experr])
 AT_CLEANUP
+
+AT_SETUP([db_upgrade])
+AT_KEYWORDS([db_upgrade])
+cat $abs_srcdir/db_upgrade/db_upgrade_test.ok > expout
+cat $abs_srcdir/db_upgrade/db_upgrade_test.err > experr
+AT_CHECK([$abs_srcdir/db_upgrade/db_upgrade_test.sh $abs_srcdir/db_upgrade $abs_builddir/db_upgrade], [], [expout], [experr])
+AT_CLEANUP

-- 
To view, visit https://gerrit.osmocom.org/c/osmo-hlr/+/15913
To unsubscribe, or for help writing mail filters, visit https://gerrit.osmocom.org/settings

Gerrit-Project: osmo-hlr
Gerrit-Branch: master
Gerrit-Change-Id: I0961bab0e17cfde5b030576c5bc243c2b51d9dc4
Gerrit-Change-Number: 15913
Gerrit-PatchSet: 1
Gerrit-Owner: neels <nhofmeyr at sysmocom.de>
Gerrit-MessageType: newchange
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osmocom.org/pipermail/gerrit-log/attachments/20191031/a4082306/attachment.htm>

More information about the gerrit-log mailing list