aboutsummaryrefslogtreecommitdiffstats
path: root/hlrsync
diff options
context:
space:
mode:
authorJan Luebbe <jluebbe@debian.org>2009-08-13 00:58:34 +0200
committerHarald Welte (local) <laflocal@hanuman.gnumonks.org>2009-08-13 13:55:33 +0200
commit6f656fcf1f239448797609e0fa10cbceae66f19a (patch)
treedc9ff1673a56df0aae3d11f443fcd3be92abfead /hlrsync
parent50d127199d62d1273ebacadc11554f9ee34b89e8 (diff)
add simple HLR DB sync script
Diffstat (limited to 'hlrsync')
-rwxr-xr-xhlrsync/hlrsync.py103
1 files changed, 103 insertions, 0 deletions
diff --git a/hlrsync/hlrsync.py b/hlrsync/hlrsync.py
new file mode 100755
index 000000000..b2a632b6f
--- /dev/null
+++ b/hlrsync/hlrsync.py
@@ -0,0 +1,103 @@
+#!/usr/bin/python2.5
+
+from __future__ import with_statement
+
+from pysqlite2 import dbapi2 as sqlite3
+import sys
+
+hlr = sqlite3.connect(sys.argv[1])
+web = sqlite3.connect(sys.argv[2])
+
+# switch to autocommit
+hlr.isolation_level = None
+web.isolation_level = None
+
+hlr.row_factory = sqlite3.Row
+web.row_factory = sqlite3.Row
+
+with hlr:
+ hlr_subscrs = hlr.execute("""
+ SELECT * FROM Subscriber
+ """).fetchall()
+ hlr_tokens = hlr.execute("""
+ SELECT * FROM AuthToken
+ """).fetchall()
+
+with web:
+ web_tokens = web.execute("""
+ SELECT * FROM reg_tokens
+ """).fetchall()
+
+# index by subscr id
+hlr_subscrs_by_id = {}
+hlr_tokens_by_subscr_id = {}
+for x in hlr_subscrs:
+ hlr_subscrs_by_id[x['id']] = x
+del hlr_subscrs
+for x in hlr_tokens:
+ hlr_tokens_by_subscr_id[x['subscriber_id']] = x
+del hlr_tokens
+
+web_tokens_by_subscr_id = {}
+for x in web_tokens:
+ web_tokens_by_subscr_id[x['subscriber_id']] = x
+del web_tokens
+
+# remove leftover web_tokens and correct inconsistent fields
+with web:
+ for x in web_tokens_by_subscr_id.values():
+ subscr = hlr_subscrs_by_id.get(x['subscriber_id'], None)
+ if subscr is None:
+ web.execute("""
+ DELETE FROM reg_tokens WHERE subscriber_id = ?
+ """, (x['subscriber_id'],))
+ del web_tokens_by_subscr_id[x['subscriber_id']]
+ continue
+ if str(x['imsi']) != str(subscr['imsi']) or \
+ x['extension'] != subscr['extension'] or \
+ x['tmsi'] != subscr['tmsi'] or \
+ x['lac'] != subscr['lac']:
+ web.execute("""
+ UPDATE reg_tokens
+ SET imsi = ?, extension = ?, tmsi = ?, lac = ?
+ WHERE subscriber_id = ?
+ """, (str(subscr['imsi']), subscr['extension'], x['subscriber_id']))
+ x['imsi'] = str(subscr['imsi'])
+ x['extension'] = subscr['extension']
+ x['tmsi'] = subscr['tmsi']
+ x['lac'] = subscr['lac']
+
+# add missing web_tokens
+with web:
+ for x in hlr_tokens_by_subscr_id.values():
+ subscr = hlr_subscrs_by_id.get(x['subscriber_id'], None)
+ if subscr is None:
+ hlr.execute("""
+ DELETE FROM AuthToken WHERE subscriber_id = ?
+ """, (x['subscriber_id'],))
+ del hlr_tokens_by_subscr_id[x['subscriber_id']]
+ continue
+ webtoken = web_tokens_by_subscr_id.get(x['subscriber_id'], None)
+ if webtoken is None:
+ web.execute("""
+ INSERT INTO reg_tokens
+ (subscriber_id, extension, reg_completed, name, email, lac, imsi, token, tmsi)
+ VALUES
+ (?, ?, 0, ?, '', ?, ?, ?, ?)
+ """, (x['subscriber_id'], subscr['extension'], subscr['name'],
+ subscr['lac'], str(subscr['imsi']), x['token'], subscr['tmsi']))
+
+# authorize subscribers
+with hlr:
+ for x in web_tokens_by_subscr_id.values():
+ subscr = hlr_subscrs_by_id.get(x['subscriber_id'], None)
+ if x['reg_completed'] and not subscr['authorized']:
+ hlr.execute("""
+ UPDATE Subscriber
+ SET authorized = 1
+ WHERE id = ?
+ """, (x['subscriber_id'],))
+
+hlr.close()
+web.close()
+