summaryrefslogtreecommitdiffstats
path: root/hlrsync/hlrsync.py
blob: e4a495555d74366111f2f824caf5bc6a0baa429d (plain)
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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
#!/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()
	web_sms = web.execute("""
		SELECT * FROM sms_queue
	""").fetchall()

# index by subscr id
hlr_subscrs_by_id = {}
hlr_subscrs_by_ext = {}
hlr_tokens_by_subscr_id = {}
for x in hlr_subscrs:
	hlr_subscrs_by_id[x['id']] = x
	hlr_subscrs_by_ext[x['extension']] = 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'],
				   subscr['tmsi'], subscr['lac'], x['subscriber_id']))

# 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'],))

# Sync SMS from web to hlr
with hlr:
	for sms in web_sms:
		subscr = hlr_subscrs_by_ext.get(sms['receiver_ext'])
		if subscr is None:
			print '%s not found' % sms['receiver_ext']
			continue
		hlr.execute("""
				      INSERT INTO SMS
				      (created, sender_id, receiver_id, reply_path_req, status_rep_req, protocol_id, data_coding_scheme, ud_hdr_ind, text)
				      VALUES
				      (?, 1, ?, 0, 0, 0, 0, 0, ?)
				   """, (sms['created'], subscr['id'], sms['text']))
with web:
	for sms in web_sms:
		web.execute("""
				      DELETE FROM sms_queue WHERE id = ?
				   """, (sms['id'],))


hlr.close()
web.close()