Daniel Willmann has uploaded this change for review. (
https://gerrit.osmocom.org/13439
Change subject: osmo-cn-latest: Add script to populate HLR
......................................................................
osmo-cn-latest: Add script to populate HLR
Change-Id: I3ba73347a87e82422fcd3ec3da9acc4b70411003
---
A osmo-cn-latest/create_hlr.py
1 file changed, 147 insertions(+), 0 deletions(-)
git pull ssh://gerrit.osmocom.org:29418/docker-playground
refs/changes/39/13439/1
diff --git a/osmo-cn-latest/create_hlr.py b/osmo-cn-latest/create_hlr.py
new file mode 100755
index 0000000..4285ea6
--- /dev/null
+++ b/osmo-cn-latest/create_hlr.py
@@ -0,0 +1,147 @@
+#!/usr/bin/env python
+
+import csv
+import sys
+import sqlite3
+
+# 3G
+def create_hlr_3g(db):
+ conn = sqlite3.connect(db)
+ c = conn.execute(
+ """CREATE TABLE IF NOT EXISTS subscriber (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ imsi VARCHAR(15) UNIQUE NOT NULL,
+ msisdn VARCHAR(15) UNIQUE,
+ imeisv VARCHAR,
+ vlr_number VARCHAR(15),
+ hlr_number VARCHAR(15),
+ sgsn_number VARCHAR(15),
+ sgsn_address VARCHAR,
+ ggsn_number VARCHAR(15),
+ gmlc_number VARCHAR(15),
+ smsc_number VARCHAR(15),
+ periodic_lu_tmr INTEGER,
+ periodic_rau_tau_tmr INTEGER,
+ nam_cs BOOLEAN NOT NULL DEFAULT 1,
+ nam_ps BOOLEAN NOT NULL DEFAULT 1,
+ lmsi INTEGER,
+ ms_purged_cs BOOLEAN NOT NULL DEFAULT 0,
+ ms_purged_ps BOOLEAN NOT NULL DEFAULT 0
+ );"""
+ )
+ c.close()
+ c = conn.execute(
+ """CREATE TABLE IF NOT EXISTS subscriber_apn (
+ subscriber_id INTEGER,
+ apn VARCHAR(256) NOT NULL
+ );"""
+ )
+ c.close()
+ c = conn.execute(
+ """CREATE TABLE IF NOT EXISTS subscriber_multi_msisdn (
+ subscriber_id INTEGER,
+ msisdn VARCHAR(15) NOT NULL
+ );"""
+ )
+ c.close()
+ c = conn.execute(
+ """CREATE TABLE IF NOT EXISTS auc_2g (
+ subscriber_id INTEGER PRIMARY KEY,
+ algo_id_2g INTEGER NOT NULL,
+ ki VARCHAR(32) NOT NULL
+ );"""
+ )
+ c.close()
+ c = conn.execute(
+ """CREATE TABLE IF NOT EXISTS auc_3g (
+ subscriber_id INTEGER PRIMARY KEY,
+ algo_id_3g INTEGER NOT NULL,
+ k VARCHAR(32) NOT NULL,
+ op VARCHAR(32),
+ opc VARCHAR(32),
+ sqn INTEGER NOT NULL DEFAULT 0,
+ ind_bitlen INTEGER NOT NULL DEFAULT 5
+ );"""
+ )
+ c.close()
+ c = conn.execute(
+ """CREATE UNIQUE INDEX idx_subscr_imsi ON subscriber (imsi);"""
+ )
+ conn.commit()
+ conn.close()
+
+def write_hlr_3g(db, data):
+ conn = sqlite3.connect(db)
+ c = conn.execute(
+ 'INSERT INTO subscriber ' +
+ '(imsi, msisdn) ' +
+ 'VALUES ' +
+ '(?,?);',
+ [
+ data['imsi'],
+ data['extension']
+ ],
+ )
+ sub_id= c.lastrowid
+ c.close()
+ c = conn.execute(
+ 'INSERT INTO auc_2g ' +
+ '(subscriber_id, algo_id_2g, ki)' +
+ 'VALUES ' +
+ '(?,?,?);',
+ [
+ sub_id,
+ 1,
+ data['ki']
+ ],
+ )
+ c.close()
+ c = conn.execute(
+ 'INSERT INTO auc_3g ' +
+ '(subscriber_id, algo_id_3g, k, opc, sqn)' +
+ 'VALUES ' +
+ '(?, ?, ?, ?, ?);',
+ [
+ sub_id,
+ 5,
+ data['ki'],
+ data['opc'],
+ 0
+ ],
+ )
+ conn.commit()
+ conn.close()
+
+def main(infilename):
+ csvfields = ['name', 'iccid', 'mcc', 'mnc', 'imsi', 'extension',
'smsp', 'ki', 'opc', 'adm1']
+
+ create_hlr_3g("hlr.db")
+ inf = open(infilename, "r")
+ outf = open("simcards.csv", "w")
+
+ cr = csv.DictReader(inf)
+ cw = csv.DictWriter(outf, csvfields)
+
+ cw.writeheader()
+ for row in cr:
+ data = {}
+ data['name'] = "Subscriber " + row['iccid'][-6:-1]
+ data['iccid'] = row['iccid']
+ data['mcc'] = row['imsi'][0:3]
+ data['mnc'] = row['imsi'][3:5]
+ data['imsi'] = row['imsi']
+ data['ki'] = row['ki']
+ data['opc'] = row['opc']
+ data['extension'] = row['iccid'][-6:-1]
+ data['smsp'] = '00495555'
+ if "adm1" in row:
+ data['adm1'] = row['adm1']
+ cw.writerow(data)
+ write_hlr_3g("hlr.db", data)
+ inf.close()
+ outf.close()
+
+
+if __name__ == '__main__':
+ main(sys.argv[1])
+
--
To view, visit https://gerrit.osmocom.org/13439
To unsubscribe, or for help writing mail filters, visit
https://gerrit.osmocom.org/settings
Gerrit-Project: docker-playground
Gerrit-Branch: master
Gerrit-MessageType: newchange
Gerrit-Change-Id: I3ba73347a87e82422fcd3ec3da9acc4b70411003
Gerrit-Change-Number: 13439
Gerrit-PatchSet: 1
Gerrit-Owner: Daniel Willmann <dwillm...@sysmocom.de>
