Harald Welte | e72cf55 | 2016-04-28 07:18:49 +0200 | [diff] [blame] | 1 | --modelled roughly after TS 23.008 version 13.3.0 |
| 2 | |
| 3 | CREATE TABLE subscriber ( |
| 4 | id INTEGER PRIMARY KEY, |
| 5 | -- Chapter 2.1.1.1 |
Neels Hofmeyr | cc785f0 | 2017-02-01 17:08:56 +0100 | [diff] [blame] | 6 | imsi VARCHAR(15) UNIQUE NOT NULL, |
Harald Welte | e72cf55 | 2016-04-28 07:18:49 +0200 | [diff] [blame] | 7 | -- Chapter 2.1.2 |
Neels Hofmeyr | cc785f0 | 2017-02-01 17:08:56 +0100 | [diff] [blame] | 8 | msisdn VARCHAR(15) UNIQUE, |
Harald Welte | e72cf55 | 2016-04-28 07:18:49 +0200 | [diff] [blame] | 9 | -- Chapter 2.2.3: Most recent / current IMEI |
| 10 | imeisv VARCHAR, |
| 11 | -- Chapter 2.4.5 |
| 12 | vlr_number VARCHAR(15), |
| 13 | -- Chapter 2.4.6 |
| 14 | hlr_number VARCHAR(15), |
| 15 | -- Chapter 2.4.8.1 |
| 16 | sgsn_number VARCHAR(15), |
| 17 | -- Chapter 2.13.10 |
| 18 | sgsn_address VARCHAR, |
| 19 | -- Chapter 2.4.8.2 |
| 20 | ggsn_number VARCHAR(15), |
| 21 | -- Chapter 2.4.9.2 |
| 22 | gmlc_number VARCHAR(15), |
| 23 | -- Chapter 2.4.23 |
| 24 | smsc_number VARCHAR(15), |
| 25 | -- Chapter 2.4.24 |
| 26 | periodic_lu_tmr INTEGER, |
| 27 | -- Chapter 2.13.115 |
| 28 | periodic_rau_tau_tmr INTEGER, |
| 29 | -- Chapter 2.1.1.2: network access mode |
Harald Welte | 6122915 | 2016-05-03 18:47:08 +0200 | [diff] [blame] | 30 | nam_cs BOOLEAN NOT NULL DEFAULT 1, |
| 31 | nam_ps BOOLEAN NOT NULL DEFAULT 1, |
Harald Welte | e72cf55 | 2016-04-28 07:18:49 +0200 | [diff] [blame] | 32 | -- Chapter 2.1.8 |
| 33 | lmsi INTEGER, |
| 34 | |
Harald Welte | b18f0e0 | 2016-05-05 21:03:03 +0200 | [diff] [blame] | 35 | -- The below purged flags might not even be stored non-volatile, |
| 36 | -- refer to TS 23.012 Chapter 3.6.1.4 |
Harald Welte | e72cf55 | 2016-04-28 07:18:49 +0200 | [diff] [blame] | 37 | -- Chapter 2.7.5 |
Harald Welte | 6122915 | 2016-05-03 18:47:08 +0200 | [diff] [blame] | 38 | ms_purged_cs BOOLEAN NOT NULL DEFAULT 0, |
Harald Welte | e72cf55 | 2016-04-28 07:18:49 +0200 | [diff] [blame] | 39 | -- Chapter 2.7.6 |
Harald Welte | 6122915 | 2016-05-03 18:47:08 +0200 | [diff] [blame] | 40 | ms_purged_ps BOOLEAN NOT NULL DEFAULT 0 |
Harald Welte | e72cf55 | 2016-04-28 07:18:49 +0200 | [diff] [blame] | 41 | ); |
| 42 | |
| 43 | CREATE TABLE subscriber_apn ( |
| 44 | subscriber_id INTEGER, -- subscriber.id |
| 45 | apn VARCHAR(256) NOT NULL |
| 46 | ); |
| 47 | |
| 48 | -- Chapter 2.1.3 |
| 49 | CREATE TABLE subscriber_multi_msisdn ( |
| 50 | subscriber_id INTEGER, -- subscriber.id |
| 51 | msisdn VARCHAR(15) NOT NULL |
| 52 | ); |
| 53 | |
| 54 | CREATE TABLE auc_2g ( |
| 55 | subscriber_id INTEGER PRIMARY KEY, -- subscriber.id |
Neels Hofmeyr | 8089d51 | 2017-01-30 13:49:49 +0100 | [diff] [blame] | 56 | algo_id_2g INTEGER NOT NULL, -- enum osmo_auth_algo value |
| 57 | ki VARCHAR(32) NOT NULL -- hex string: subscriber's secret key (128bit) |
Harald Welte | e72cf55 | 2016-04-28 07:18:49 +0200 | [diff] [blame] | 58 | ); |
| 59 | |
| 60 | CREATE TABLE auc_3g ( |
Neels Hofmeyr | 2116ce2 | 2017-01-19 15:50:59 +0100 | [diff] [blame] | 61 | subscriber_id INTEGER PRIMARY KEY, -- subscriber.id |
Neels Hofmeyr | 8089d51 | 2017-01-30 13:49:49 +0100 | [diff] [blame] | 62 | algo_id_3g INTEGER NOT NULL, -- enum osmo_auth_algo value |
| 63 | k VARCHAR(32) NOT NULL, -- hex string: subscriber's secret key (128bit) |
| 64 | op VARCHAR(32), -- hex string: operator's secret key (128bit) |
| 65 | opc VARCHAR(32), -- hex string: derived from OP and K (128bit) |
Neels Hofmeyr | cab2fcd | 2017-03-15 00:07:43 +0100 | [diff] [blame] | 66 | sqn INTEGER NOT NULL DEFAULT 0, -- sequence number of key usage |
| 67 | ind_bitlen INTEGER NOT NULL DEFAULT 5 -- nr of index bits at lower SQN end |
Harald Welte | e72cf55 | 2016-04-28 07:18:49 +0200 | [diff] [blame] | 68 | ); |
| 69 | |
Harald Welte | 6122915 | 2016-05-03 18:47:08 +0200 | [diff] [blame] | 70 | CREATE UNIQUE INDEX IF NOT EXISTS idx_subscr_imsi ON subscriber (imsi); |
Harald Welte | e72cf55 | 2016-04-28 07:18:49 +0200 | [diff] [blame] | 71 | -- 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 = ? |