store a timestamp of the last location update seen from a subscriber

Timestamps are stored in the HLR DB in the new 'last_lu_seen' column
of the 'subscriber' table, in UTC and in granularity of seconds.

At present, osmo-hlr only records these timestamps but otherwise
makes no use of them. Because the timestamps are stored in a
human-readable form, they may already provide value to external
processes which need this information. For example:

  sqlite> select imsi,last_lu_seen from subscriber;
  901990000000001|2018-12-04 14:17:12

I didn't bother adding additional tests because the code added
with this commit is already being exercised by several calls
to db_subscr_lu() in db_test.c.

This change requires a HLR DB schema update. Existing databases
won't be upgraded automatically. However, osmo-hlr will refuse
to operate with databases which are not upgraded.

Change-Id: Ibeb49d45aec18451a260a6654b8c51b8fc3bec50
Related: OS#2838

1 files changed, 7 insertions, 2 deletions

diff --git a/sql/hlr.sql b/sql/hlr.sql
index 3499109..9ff9867 100644
--- a/sql/hlr.sql
+++ b/sql/hlr.sql
@@ -36,7 +36,11 @@ CREATE TABLE subscriber (
 	-- Chapter 2.7.5
 	ms_purged_cs	BOOLEAN NOT NULL DEFAULT 0,
 	-- Chapter 2.7.6
-	ms_purged_ps	BOOLEAN NOT NULL DEFAULT 0
+	ms_purged_ps	BOOLEAN NOT NULL DEFAULT 0,
+
+	-- Timestamp of last location update seen from subscriber
+	-- The value is a string which encodes a UTC timestamp in granularity of seconds.
+	last_lu_seen TIMESTAMP default NULL
 );
 
 CREATE TABLE subscriber_apn (
@@ -69,4 +73,5 @@ CREATE TABLE auc_3g (
 CREATE UNIQUE INDEX idx_subscr_imsi ON subscriber (imsi);
 
 -- Set HLR database schema version number
-PRAGMA user_version = 0;
+-- Note: This constant is currently duplicated in src/db.c and must be kept in sync!
+PRAGMA user_version = 1;