1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
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,6,'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
-- nr of index bits at lower SQN end
ind_bitlen INTEGER NOT NULL DEFAULT 5
);
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;
|
