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;