Jan Luebbe | 6f656fc | 2009-08-13 00:58:34 +0200 | [diff] [blame] | 1 | #!/usr/bin/python2.5 |
| 2 | |
| 3 | from __future__ import with_statement |
| 4 | |
| 5 | from pysqlite2 import dbapi2 as sqlite3 |
| 6 | import sys |
| 7 | |
| 8 | hlr = sqlite3.connect(sys.argv[1]) |
| 9 | web = sqlite3.connect(sys.argv[2]) |
| 10 | |
| 11 | # switch to autocommit |
| 12 | hlr.isolation_level = None |
| 13 | web.isolation_level = None |
| 14 | |
| 15 | hlr.row_factory = sqlite3.Row |
| 16 | web.row_factory = sqlite3.Row |
| 17 | |
| 18 | with hlr: |
| 19 | hlr_subscrs = hlr.execute(""" |
| 20 | SELECT * FROM Subscriber |
| 21 | """).fetchall() |
| 22 | hlr_tokens = hlr.execute(""" |
| 23 | SELECT * FROM AuthToken |
| 24 | """).fetchall() |
| 25 | |
| 26 | with web: |
| 27 | web_tokens = web.execute(""" |
| 28 | SELECT * FROM reg_tokens |
| 29 | """).fetchall() |
Stefan Schmidt | 5f8a78b | 2009-08-14 21:33:34 +0200 | [diff] [blame] | 30 | web_sms = web.execute(""" |
| 31 | SELECT * FROM sms_queue |
| 32 | """).fetchall() |
Jan Luebbe | 6f656fc | 2009-08-13 00:58:34 +0200 | [diff] [blame] | 33 | |
| 34 | # index by subscr id |
| 35 | hlr_subscrs_by_id = {} |
Stefan Schmidt | 5f8a78b | 2009-08-14 21:33:34 +0200 | [diff] [blame] | 36 | hlr_subscrs_by_ext = {} |
Jan Luebbe | 6f656fc | 2009-08-13 00:58:34 +0200 | [diff] [blame] | 37 | hlr_tokens_by_subscr_id = {} |
| 38 | for x in hlr_subscrs: |
| 39 | hlr_subscrs_by_id[x['id']] = x |
Stefan Schmidt | 5f8a78b | 2009-08-14 21:33:34 +0200 | [diff] [blame] | 40 | hlr_subscrs_by_ext[x['extension']] = x |
Jan Luebbe | 6f656fc | 2009-08-13 00:58:34 +0200 | [diff] [blame] | 41 | del hlr_subscrs |
| 42 | for x in hlr_tokens: |
| 43 | hlr_tokens_by_subscr_id[x['subscriber_id']] = x |
| 44 | del hlr_tokens |
| 45 | |
| 46 | web_tokens_by_subscr_id = {} |
| 47 | for x in web_tokens: |
| 48 | web_tokens_by_subscr_id[x['subscriber_id']] = x |
| 49 | del web_tokens |
| 50 | |
| 51 | # remove leftover web_tokens and correct inconsistent fields |
| 52 | with web: |
| 53 | for x in web_tokens_by_subscr_id.values(): |
| 54 | subscr = hlr_subscrs_by_id.get(x['subscriber_id'], None) |
| 55 | if subscr is None: |
| 56 | web.execute(""" |
| 57 | DELETE FROM reg_tokens WHERE subscriber_id = ? |
| 58 | """, (x['subscriber_id'],)) |
| 59 | del web_tokens_by_subscr_id[x['subscriber_id']] |
| 60 | continue |
| 61 | if str(x['imsi']) != str(subscr['imsi']) or \ |
| 62 | x['extension'] != subscr['extension'] or \ |
| 63 | x['tmsi'] != subscr['tmsi'] or \ |
| 64 | x['lac'] != subscr['lac']: |
| 65 | web.execute(""" |
| 66 | UPDATE reg_tokens |
| 67 | SET imsi = ?, extension = ?, tmsi = ?, lac = ? |
| 68 | WHERE subscriber_id = ? |
Jan Luebbe | bf0d5bd | 2009-08-13 20:52:55 +0200 | [diff] [blame] | 69 | """, (str(subscr['imsi']), subscr['extension'], |
| 70 | subscr['tmsi'], subscr['lac'], x['subscriber_id'])) |
Jan Luebbe | 6f656fc | 2009-08-13 00:58:34 +0200 | [diff] [blame] | 71 | |
| 72 | # add missing web_tokens |
| 73 | with web: |
| 74 | for x in hlr_tokens_by_subscr_id.values(): |
| 75 | subscr = hlr_subscrs_by_id.get(x['subscriber_id'], None) |
| 76 | if subscr is None: |
| 77 | hlr.execute(""" |
| 78 | DELETE FROM AuthToken WHERE subscriber_id = ? |
| 79 | """, (x['subscriber_id'],)) |
| 80 | del hlr_tokens_by_subscr_id[x['subscriber_id']] |
| 81 | continue |
| 82 | webtoken = web_tokens_by_subscr_id.get(x['subscriber_id'], None) |
| 83 | if webtoken is None: |
| 84 | web.execute(""" |
| 85 | INSERT INTO reg_tokens |
| 86 | (subscriber_id, extension, reg_completed, name, email, lac, imsi, token, tmsi) |
| 87 | VALUES |
| 88 | (?, ?, 0, ?, '', ?, ?, ?, ?) |
| 89 | """, (x['subscriber_id'], subscr['extension'], subscr['name'], |
| 90 | subscr['lac'], str(subscr['imsi']), x['token'], subscr['tmsi'])) |
| 91 | |
| 92 | # authorize subscribers |
| 93 | with hlr: |
| 94 | for x in web_tokens_by_subscr_id.values(): |
| 95 | subscr = hlr_subscrs_by_id.get(x['subscriber_id'], None) |
| 96 | if x['reg_completed'] and not subscr['authorized']: |
| 97 | hlr.execute(""" |
| 98 | UPDATE Subscriber |
| 99 | SET authorized = 1 |
| 100 | WHERE id = ? |
| 101 | """, (x['subscriber_id'],)) |
| 102 | |
Stefan Schmidt | 5f8a78b | 2009-08-14 21:33:34 +0200 | [diff] [blame] | 103 | # Sync SMS from web to hlr |
| 104 | with hlr: |
| 105 | for sms in web_sms: |
| 106 | subscr = hlr_subscrs_by_ext.get(sms['receiver_ext']) |
| 107 | if subscr is None: |
| 108 | print '%s not found' % sms['receiver_ext'] |
| 109 | continue |
| 110 | hlr.execute(""" |
| 111 | INSERT INTO SMS |
| 112 | (created, sender_id, receiver_id, reply_path_req, status_rep_req, protocol_id, data_coding_scheme, ud_hdr_ind, text) |
| 113 | VALUES |
| 114 | (?, 1, ?, 0, 0, 0, 0, 0, ?) |
| 115 | """, (sms['created'], subscr['id'], sms['text'])) |
| 116 | with web: |
| 117 | for sms in web_sms: |
| 118 | web.execute(""" |
| 119 | DELETE FROM sms_queue WHERE id = ? |
| 120 | """, (sms['id'],)) |
| 121 | |
| 122 | |
Jan Luebbe | 6f656fc | 2009-08-13 00:58:34 +0200 | [diff] [blame] | 123 | hlr.close() |
| 124 | web.close() |
| 125 | |