<p>Harald Welte <strong>merged</strong> this change.</p><p><a href="https://gerrit.osmocom.org/13439">View Change</a></p><div style="white-space:pre-wrap">Approvals:
Daniel Willmann: Verified
Harald Welte: Looks good to me, approved
</div><pre style="font-family: monospace,monospace; white-space: pre-wrap;">osmo-cn-latest: Add script to populate HLR<br><br>Change-Id: I3ba73347a87e82422fcd3ec3da9acc4b70411003<br>---<br>A osmo-cn-latest/create_hlr.py<br>1 file changed, 147 insertions(+), 0 deletions(-)<br><br></pre><pre style="font-family: monospace,monospace; white-space: pre-wrap;"><span>diff --git a/osmo-cn-latest/create_hlr.py b/osmo-cn-latest/create_hlr.py</span><br><span>new file mode 100755</span><br><span>index 0000000..4285ea6</span><br><span>--- /dev/null</span><br><span>+++ b/osmo-cn-latest/create_hlr.py</span><br><span>@@ -0,0 +1,147 @@</span><br><span style="color: hsl(120, 100%, 40%);">+#!/usr/bin/env python</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+import csv</span><br><span style="color: hsl(120, 100%, 40%);">+import sys</span><br><span style="color: hsl(120, 100%, 40%);">+import sqlite3</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+# 3G</span><br><span style="color: hsl(120, 100%, 40%);">+def create_hlr_3g(db):</span><br><span style="color: hsl(120, 100%, 40%);">+ conn = sqlite3.connect(db)</span><br><span style="color: hsl(120, 100%, 40%);">+ c = conn.execute(</span><br><span style="color: hsl(120, 100%, 40%);">+ """CREATE TABLE IF NOT EXISTS subscriber (</span><br><span style="color: hsl(120, 100%, 40%);">+ id INTEGER PRIMARY KEY AUTOINCREMENT,</span><br><span style="color: hsl(120, 100%, 40%);">+ imsi VARCHAR(15) UNIQUE NOT NULL,</span><br><span style="color: hsl(120, 100%, 40%);">+ msisdn VARCHAR(15) UNIQUE,</span><br><span style="color: hsl(120, 100%, 40%);">+ imeisv VARCHAR,</span><br><span style="color: hsl(120, 100%, 40%);">+ vlr_number VARCHAR(15),</span><br><span style="color: hsl(120, 100%, 40%);">+ hlr_number VARCHAR(15),</span><br><span style="color: hsl(120, 100%, 40%);">+ sgsn_number VARCHAR(15),</span><br><span style="color: hsl(120, 100%, 40%);">+ sgsn_address VARCHAR,</span><br><span style="color: hsl(120, 100%, 40%);">+ ggsn_number VARCHAR(15),</span><br><span style="color: hsl(120, 100%, 40%);">+ gmlc_number VARCHAR(15),</span><br><span style="color: hsl(120, 100%, 40%);">+ smsc_number VARCHAR(15),</span><br><span style="color: hsl(120, 100%, 40%);">+ periodic_lu_tmr INTEGER,</span><br><span style="color: hsl(120, 100%, 40%);">+ periodic_rau_tau_tmr INTEGER,</span><br><span style="color: hsl(120, 100%, 40%);">+ nam_cs BOOLEAN NOT NULL DEFAULT 1,</span><br><span style="color: hsl(120, 100%, 40%);">+ nam_ps BOOLEAN NOT NULL DEFAULT 1,</span><br><span style="color: hsl(120, 100%, 40%);">+ lmsi INTEGER,</span><br><span style="color: hsl(120, 100%, 40%);">+ ms_purged_cs BOOLEAN NOT NULL DEFAULT 0,</span><br><span style="color: hsl(120, 100%, 40%);">+ ms_purged_ps BOOLEAN NOT NULL DEFAULT 0</span><br><span style="color: hsl(120, 100%, 40%);">+ );"""</span><br><span style="color: hsl(120, 100%, 40%);">+ )</span><br><span style="color: hsl(120, 100%, 40%);">+ c.close()</span><br><span style="color: hsl(120, 100%, 40%);">+ c = conn.execute(</span><br><span style="color: hsl(120, 100%, 40%);">+ """CREATE TABLE IF NOT EXISTS subscriber_apn (</span><br><span style="color: hsl(120, 100%, 40%);">+ subscriber_id INTEGER,</span><br><span style="color: hsl(120, 100%, 40%);">+ apn VARCHAR(256) NOT NULL</span><br><span style="color: hsl(120, 100%, 40%);">+ );"""</span><br><span style="color: hsl(120, 100%, 40%);">+ )</span><br><span style="color: hsl(120, 100%, 40%);">+ c.close()</span><br><span style="color: hsl(120, 100%, 40%);">+ c = conn.execute(</span><br><span style="color: hsl(120, 100%, 40%);">+ """CREATE TABLE IF NOT EXISTS subscriber_multi_msisdn (</span><br><span style="color: hsl(120, 100%, 40%);">+ subscriber_id INTEGER,</span><br><span style="color: hsl(120, 100%, 40%);">+ msisdn VARCHAR(15) NOT NULL</span><br><span style="color: hsl(120, 100%, 40%);">+ );"""</span><br><span style="color: hsl(120, 100%, 40%);">+ )</span><br><span style="color: hsl(120, 100%, 40%);">+ c.close()</span><br><span style="color: hsl(120, 100%, 40%);">+ c = conn.execute(</span><br><span style="color: hsl(120, 100%, 40%);">+ """CREATE TABLE IF NOT EXISTS auc_2g (</span><br><span style="color: hsl(120, 100%, 40%);">+ subscriber_id INTEGER PRIMARY KEY,</span><br><span style="color: hsl(120, 100%, 40%);">+ algo_id_2g INTEGER NOT NULL,</span><br><span style="color: hsl(120, 100%, 40%);">+ ki VARCHAR(32) NOT NULL</span><br><span style="color: hsl(120, 100%, 40%);">+ );"""</span><br><span style="color: hsl(120, 100%, 40%);">+ )</span><br><span style="color: hsl(120, 100%, 40%);">+ c.close()</span><br><span style="color: hsl(120, 100%, 40%);">+ c = conn.execute(</span><br><span style="color: hsl(120, 100%, 40%);">+ """CREATE TABLE IF NOT EXISTS auc_3g (</span><br><span style="color: hsl(120, 100%, 40%);">+ subscriber_id INTEGER PRIMARY KEY,</span><br><span style="color: hsl(120, 100%, 40%);">+ algo_id_3g INTEGER NOT NULL,</span><br><span style="color: hsl(120, 100%, 40%);">+ k VARCHAR(32) NOT NULL,</span><br><span style="color: hsl(120, 100%, 40%);">+ op VARCHAR(32),</span><br><span style="color: hsl(120, 100%, 40%);">+ opc VARCHAR(32),</span><br><span style="color: hsl(120, 100%, 40%);">+ sqn INTEGER NOT NULL DEFAULT 0,</span><br><span style="color: hsl(120, 100%, 40%);">+ ind_bitlen INTEGER NOT NULL DEFAULT 5</span><br><span style="color: hsl(120, 100%, 40%);">+ );"""</span><br><span style="color: hsl(120, 100%, 40%);">+ )</span><br><span style="color: hsl(120, 100%, 40%);">+ c.close()</span><br><span style="color: hsl(120, 100%, 40%);">+ c = conn.execute(</span><br><span style="color: hsl(120, 100%, 40%);">+ """CREATE UNIQUE INDEX idx_subscr_imsi ON subscriber (imsi);"""</span><br><span style="color: hsl(120, 100%, 40%);">+ )</span><br><span style="color: hsl(120, 100%, 40%);">+ conn.commit()</span><br><span style="color: hsl(120, 100%, 40%);">+ conn.close()</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+def write_hlr_3g(db, data):</span><br><span style="color: hsl(120, 100%, 40%);">+ conn = sqlite3.connect(db)</span><br><span style="color: hsl(120, 100%, 40%);">+ c = conn.execute(</span><br><span style="color: hsl(120, 100%, 40%);">+ 'INSERT INTO subscriber ' +</span><br><span style="color: hsl(120, 100%, 40%);">+ '(imsi, msisdn) ' +</span><br><span style="color: hsl(120, 100%, 40%);">+ 'VALUES ' +</span><br><span style="color: hsl(120, 100%, 40%);">+ '(?,?);',</span><br><span style="color: hsl(120, 100%, 40%);">+ [</span><br><span style="color: hsl(120, 100%, 40%);">+ data['imsi'],</span><br><span style="color: hsl(120, 100%, 40%);">+ data['extension']</span><br><span style="color: hsl(120, 100%, 40%);">+ ],</span><br><span style="color: hsl(120, 100%, 40%);">+ )</span><br><span style="color: hsl(120, 100%, 40%);">+ sub_id= c.lastrowid</span><br><span style="color: hsl(120, 100%, 40%);">+ c.close()</span><br><span style="color: hsl(120, 100%, 40%);">+ c = conn.execute(</span><br><span style="color: hsl(120, 100%, 40%);">+ 'INSERT INTO auc_2g ' +</span><br><span style="color: hsl(120, 100%, 40%);">+ '(subscriber_id, algo_id_2g, ki)' +</span><br><span style="color: hsl(120, 100%, 40%);">+ 'VALUES ' +</span><br><span style="color: hsl(120, 100%, 40%);">+ '(?,?,?);',</span><br><span style="color: hsl(120, 100%, 40%);">+ [</span><br><span style="color: hsl(120, 100%, 40%);">+ sub_id,</span><br><span style="color: hsl(120, 100%, 40%);">+ 1,</span><br><span style="color: hsl(120, 100%, 40%);">+ data['ki']</span><br><span style="color: hsl(120, 100%, 40%);">+ ],</span><br><span style="color: hsl(120, 100%, 40%);">+ )</span><br><span style="color: hsl(120, 100%, 40%);">+ c.close()</span><br><span style="color: hsl(120, 100%, 40%);">+ c = conn.execute(</span><br><span style="color: hsl(120, 100%, 40%);">+ 'INSERT INTO auc_3g ' +</span><br><span style="color: hsl(120, 100%, 40%);">+ '(subscriber_id, algo_id_3g, k, opc, sqn)' +</span><br><span style="color: hsl(120, 100%, 40%);">+ 'VALUES ' +</span><br><span style="color: hsl(120, 100%, 40%);">+ '(?, ?, ?, ?, ?);',</span><br><span style="color: hsl(120, 100%, 40%);">+ [</span><br><span style="color: hsl(120, 100%, 40%);">+ sub_id,</span><br><span style="color: hsl(120, 100%, 40%);">+ 5,</span><br><span style="color: hsl(120, 100%, 40%);">+ data['ki'],</span><br><span style="color: hsl(120, 100%, 40%);">+ data['opc'],</span><br><span style="color: hsl(120, 100%, 40%);">+ 0</span><br><span style="color: hsl(120, 100%, 40%);">+ ],</span><br><span style="color: hsl(120, 100%, 40%);">+ )</span><br><span style="color: hsl(120, 100%, 40%);">+ conn.commit()</span><br><span style="color: hsl(120, 100%, 40%);">+ conn.close()</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+def main(infilename):</span><br><span style="color: hsl(120, 100%, 40%);">+ csvfields = ['name', 'iccid', 'mcc', 'mnc', 'imsi', 'extension', 'smsp', 'ki', 'opc', 'adm1']</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ create_hlr_3g("hlr.db")</span><br><span style="color: hsl(120, 100%, 40%);">+ inf = open(infilename, "r")</span><br><span style="color: hsl(120, 100%, 40%);">+ outf = open("simcards.csv", "w")</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ cr = csv.DictReader(inf)</span><br><span style="color: hsl(120, 100%, 40%);">+ cw = csv.DictWriter(outf, csvfields)</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+ cw.writeheader()</span><br><span style="color: hsl(120, 100%, 40%);">+ for row in cr:</span><br><span style="color: hsl(120, 100%, 40%);">+ data = {}</span><br><span style="color: hsl(120, 100%, 40%);">+ data['name'] = "Subscriber " + row['iccid'][-6:-1]</span><br><span style="color: hsl(120, 100%, 40%);">+ data['iccid'] = row['iccid']</span><br><span style="color: hsl(120, 100%, 40%);">+ data['mcc'] = row['imsi'][0:3]</span><br><span style="color: hsl(120, 100%, 40%);">+ data['mnc'] = row['imsi'][3:5]</span><br><span style="color: hsl(120, 100%, 40%);">+ data['imsi'] = row['imsi']</span><br><span style="color: hsl(120, 100%, 40%);">+ data['ki'] = row['ki']</span><br><span style="color: hsl(120, 100%, 40%);">+ data['opc'] = row['opc']</span><br><span style="color: hsl(120, 100%, 40%);">+ data['extension'] = row['iccid'][-6:-1]</span><br><span style="color: hsl(120, 100%, 40%);">+ data['smsp'] = '00495555'</span><br><span style="color: hsl(120, 100%, 40%);">+ if "adm1" in row:</span><br><span style="color: hsl(120, 100%, 40%);">+ data['adm1'] = row['adm1']</span><br><span style="color: hsl(120, 100%, 40%);">+ cw.writerow(data)</span><br><span style="color: hsl(120, 100%, 40%);">+ write_hlr_3g("hlr.db", data)</span><br><span style="color: hsl(120, 100%, 40%);">+ inf.close()</span><br><span style="color: hsl(120, 100%, 40%);">+ outf.close()</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span style="color: hsl(120, 100%, 40%);">+if __name__ == '__main__':</span><br><span style="color: hsl(120, 100%, 40%);">+ main(sys.argv[1])</span><br><span style="color: hsl(120, 100%, 40%);">+</span><br><span></span><br></pre><p>To view, visit <a href="https://gerrit.osmocom.org/13439">change 13439</a>. To unsubscribe, or for help writing mail filters, visit <a href="https://gerrit.osmocom.org/settings">settings</a>.</p><div itemscope itemtype="http://schema.org/EmailMessage"><div itemscope itemprop="action" itemtype="http://schema.org/ViewAction"><link itemprop="url" href="https://gerrit.osmocom.org/13439"/><meta itemprop="name" content="View Change"/></div></div>
<div style="display:none"> Gerrit-Project: docker-playground </div>
<div style="display:none"> Gerrit-Branch: master </div>
<div style="display:none"> Gerrit-MessageType: merged </div>
<div style="display:none"> Gerrit-Change-Id: I3ba73347a87e82422fcd3ec3da9acc4b70411003 </div>
<div style="display:none"> Gerrit-Change-Number: 13439 </div>
<div style="display:none"> Gerrit-PatchSet: 1 </div>
<div style="display:none"> Gerrit-Owner: Daniel Willmann <dwillmann@sysmocom.de> </div>
<div style="display:none"> Gerrit-Reviewer: Daniel Willmann <dwillmann@sysmocom.de> </div>
<div style="display:none"> Gerrit-Reviewer: Harald Welte <laforge@gnumonks.org> </div>