From e72cf55347542fccd9dbaee6dbff342ebc81dc6c Mon Sep 17 00:00:00 2001
From: Harald Welte <laforge@gnumonks.org>
Date: Thu, 28 Apr 2016 07:18:49 +0200
Subject: initial import of osmo-gsup-hlr code so far

---
 sql/hlr.sql      | 67 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 sql/hlr_data.sql | 13 +++++++++++
 2 files changed, 80 insertions(+)
 create mode 100644 sql/hlr.sql
 create mode 100644 sql/hlr_data.sql

(limited to 'sql')

diff --git a/sql/hlr.sql b/sql/hlr.sql
new file mode 100644
index 0000000..b95d260
--- /dev/null
+++ b/sql/hlr.sql
@@ -0,0 +1,67 @@
+--modelled roughly after TS 23.008 version 13.3.0
+
+CREATE TABLE subscriber (
+	id		INTEGER PRIMARY KEY,
+	-- Chapter 2.1.1.1
+	imsi		VARCHAR(15) NOT NULL,
+	-- Chapter 2.1.2
+	msisdn		VARCHAR(15),
+	-- 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 TRUE,
+	nam_ps		BOOLEAN NOT NULL DEFAULT TRUE,
+	-- Chapter 2.1.8
+	lmsi		INTEGER,
+
+	-- Chapter 2.7.5
+	ms_purged_cs	BOOLEAN NOT NULL DEFAULT FALSE,
+	-- Chapter 2.7.6
+	ms_purged_ps	BOOLEAN NOT NULL DEFAULT FALSE
+);
+
+CREATE TABLE subscriber_apn (
+	subscriber_id	INTEGER,		-- subscriber.id
+	apn		VARCHAR(256) NOT NULL
+);
+
+-- Chapter 2.1.3
+CREATE TABLE subscriber_multi_msisdn (
+	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,
+	ki		VARCHAR NOT NULL
+);
+
+CREATE TABLE auc_3g (
+	subscriber_id	INTEGER PRIMARY KEY,	-- subscrbier.id
+	algo_id_3g	INTEGER NOT NULL,
+	k		INTEGER NOT NULL,
+	op		VARCHAR,
+	opc		VARCHAR,
+	sqn		INTEGER
+);
+
+-- SELECT algo_id_2g, ki, algo_id_3g, k, op, opc, sqn FROM subscriber LEFT JOIN auc_2g ON auc_2g.subscriber_id = subscriber.id LEFT JOIN auc_3g ON auc_3g.subscriber_id = subscriber.id WHERE imsi = ?
diff --git a/sql/hlr_data.sql b/sql/hlr_data.sql
new file mode 100644
index 0000000..939e4c9
--- /dev/null
+++ b/sql/hlr_data.sql
@@ -0,0 +1,13 @@
+
+-- 2G only subscriber
+INSERT INTO subscriber (id, imsi) VALUES (1, '901990000000001');
+INSERT INTO auc_2g (subscriber_id, algo_id_2g, ki) VALUES (1, 1, '000102030405060708090a0b0c0d0e0f');
+
+-- 3G only subscriber
+INSERT INTO subscriber (id, imsi) VALUES (2, '901990000000002');
+INSERT INTO auc_3g (subscriber_id, algo_id_3g, k, op, sqn) VALUES (2, 5, '000102030405060708090a0b0c0d0e0f', '101112131415161718191a1b1c1d1e1f', 0);
+
+-- 2G + 3G subscriber
+INSERT INTO subscriber (id, imsi) VALUES (3, '901990000000003');
+INSERT INTO auc_2g (subscriber_id, algo_id_2g, ki) VALUES (3, 1, '000102030405060708090a0b0c0d0e0f');
+INSERT INTO auc_3g (subscriber_id, algo_id_3g, k, op, sqn) VALUES (3, 5, '000102030405060708090a0b0c0d0e0f', '101112131415161718191a1b1c1d1e1f', 0);
-- 
cgit v1.2.3