diff options
author | Harald Welte <laforge@gnumonks.org> | 2016-04-28 07:18:49 +0200 |
---|---|---|
committer | Harald Welte <laforge@gnumonks.org> | 2016-04-28 07:18:49 +0200 |
commit | e72cf55347542fccd9dbaee6dbff342ebc81dc6c (patch) | |
tree | 727e316fa342fd2d103ef2627f23c9598d0102ce /sql |
initial import of osmo-gsup-hlr code so far
Diffstat (limited to 'sql')
-rw-r--r-- | sql/hlr.sql | 67 | ||||
-rw-r--r-- | sql/hlr_data.sql | 13 |
2 files changed, 80 insertions, 0 deletions
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); |