Jan Luebbe | 2697289 | 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() |
| 30 | |
| 31 | # index by subscr id |
| 32 | hlr_subscrs_by_id = {} |
| 33 | hlr_tokens_by_subscr_id = {} |
| 34 | for x in hlr_subscrs: |
| 35 | hlr_subscrs_by_id[x['id']] = x |
| 36 | del hlr_subscrs |
| 37 | for x in hlr_tokens: |
| 38 | hlr_tokens_by_subscr_id[x['subscriber_id']] = x |
| 39 | del hlr_tokens |
| 40 | |
| 41 | web_tokens_by_subscr_id = {} |
| 42 | for x in web_tokens: |
| 43 | web_tokens_by_subscr_id[x['subscriber_id']] = x |
| 44 | del web_tokens |
| 45 | |
| 46 | # remove leftover web_tokens and correct inconsistent fields |
| 47 | with web: |
| 48 | for x in web_tokens_by_subscr_id.values(): |
| 49 | subscr = hlr_subscrs_by_id.get(x['subscriber_id'], None) |
| 50 | if subscr is None: |
| 51 | web.execute(""" |
| 52 | DELETE FROM reg_tokens WHERE subscriber_id = ? |
| 53 | """, (x['subscriber_id'],)) |
| 54 | del web_tokens_by_subscr_id[x['subscriber_id']] |
| 55 | continue |
| 56 | if str(x['imsi']) != str(subscr['imsi']) or \ |
| 57 | x['extension'] != subscr['extension'] or \ |
| 58 | x['tmsi'] != subscr['tmsi'] or \ |
| 59 | x['lac'] != subscr['lac']: |
| 60 | web.execute(""" |
| 61 | UPDATE reg_tokens |
| 62 | SET imsi = ?, extension = ?, tmsi = ?, lac = ? |
| 63 | WHERE subscriber_id = ? |
Jan Luebbe | 74a7a22 | 2009-08-13 20:52:55 +0200 | [diff] [blame] | 64 | """, (str(subscr['imsi']), subscr['extension'], |
| 65 | subscr['tmsi'], subscr['lac'], x['subscriber_id'])) |
Jan Luebbe | 2697289 | 2009-08-13 00:58:34 +0200 | [diff] [blame] | 66 | |
| 67 | # add missing web_tokens |
| 68 | with web: |
| 69 | for x in hlr_tokens_by_subscr_id.values(): |
| 70 | subscr = hlr_subscrs_by_id.get(x['subscriber_id'], None) |
| 71 | if subscr is None: |
| 72 | hlr.execute(""" |
| 73 | DELETE FROM AuthToken WHERE subscriber_id = ? |
| 74 | """, (x['subscriber_id'],)) |
| 75 | del hlr_tokens_by_subscr_id[x['subscriber_id']] |
| 76 | continue |
| 77 | webtoken = web_tokens_by_subscr_id.get(x['subscriber_id'], None) |
| 78 | if webtoken is None: |
| 79 | web.execute(""" |
| 80 | INSERT INTO reg_tokens |
| 81 | (subscriber_id, extension, reg_completed, name, email, lac, imsi, token, tmsi) |
| 82 | VALUES |
| 83 | (?, ?, 0, ?, '', ?, ?, ?, ?) |
| 84 | """, (x['subscriber_id'], subscr['extension'], subscr['name'], |
| 85 | subscr['lac'], str(subscr['imsi']), x['token'], subscr['tmsi'])) |
| 86 | |
| 87 | # authorize subscribers |
| 88 | with hlr: |
| 89 | for x in web_tokens_by_subscr_id.values(): |
| 90 | subscr = hlr_subscrs_by_id.get(x['subscriber_id'], None) |
| 91 | if x['reg_completed'] and not subscr['authorized']: |
| 92 | hlr.execute(""" |
| 93 | UPDATE Subscriber |
| 94 | SET authorized = 1 |
| 95 | WHERE id = ? |
| 96 | """, (x['subscriber_id'],)) |
| 97 | |
| 98 | hlr.close() |
| 99 | web.close() |
| 100 | |