forked from csev/py4e
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathgmodel.py
More file actions
262 lines (223 loc) · 7.7 KB
/
gmodel.py
File metadata and controls
262 lines (223 loc) · 7.7 KB
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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
import sqlite3
import time
import urllib
import re
import zlib
from datetime import datetime, timedelta
# Not all systems have this
try:
import dateutil.parser as parser
except:
pass
dnsmapping = dict()
mapping = dict()
def fixsender(sender,allsenders=None) :
global dnsmapping
global mapping
if sender is None : return None
sender = sender.strip().lower()
sender = sender.replace('<','').replace('>','')
# Check if we have a hacked gmane.org from address
if allsenders is not None and sender.endswith('gmane.org') :
pieces = sender.split('-')
realsender = None
for s in allsenders:
if s.startswith(pieces[0]) :
realsender = sender
sender = s
# print realsender, sender
break
if realsender is None :
for s in mapping:
if s.startswith(pieces[0]) :
realsender = sender
sender = mapping[s]
# print realsender, sender
break
if realsender is None : sender = pieces[0]
mpieces = sender.split("@")
if len(mpieces) != 2 : return sender
dns = mpieces[1]
x = dns
pieces = dns.split(".")
if dns.endswith(".edu") or dns.endswith(".com") or dns.endswith(".org") or dns.endswith(".net") :
dns = ".".join(pieces[-2:])
else:
dns = ".".join(pieces[-3:])
# if dns != x : print x,dns
# if dns != dnsmapping.get(dns,dns) : print dns,dnsmapping.get(dns,dns)
dns = dnsmapping.get(dns,dns)
return mpieces[0] + '@' + dns
def parsemaildate(md) :
# See if we have dateutil
try:
pdate = parser.parse(tdate)
test_at = pdate.isoformat()
return test_at
except:
pass
# Non-dateutil version - we try our best
pieces = md.split()
notz = " ".join(pieces[:4]).strip()
# Try a bunch of format variations - strptime() is *lame*
dnotz = None
for form in [ '%d %b %Y %H:%M:%S', '%d %b %Y %H:%M:%S',
'%d %b %Y %H:%M', '%d %b %Y %H:%M', '%d %b %y %H:%M:%S',
'%d %b %y %H:%M:%S', '%d %b %y %H:%M', '%d %b %y %H:%M' ] :
try:
dnotz = datetime.strptime(notz, form)
break
except:
continue
if dnotz is None :
# print 'Bad Date:',md
return None
iso = dnotz.isoformat()
tz = "+0000"
try:
tz = pieces[4]
ival = int(tz) # Only want numeric timezone values
if tz == '-0000' : tz = '+0000'
tzh = tz[:3]
tzm = tz[3:]
tz = tzh+":"+tzm
except:
pass
return iso+tz
# Parse out the info...
def parseheader(hdr, allsenders=None):
if hdr is None or len(hdr) < 1 : return None
sender = None
x = re.findall('\nFrom: .* <(\S+@\S+)>\n', hdr)
if len(x) >= 1 :
sender = x[0]
else:
x = re.findall('\nFrom: (\S+@\S+)\n', hdr)
if len(x) >= 1 :
sender = x[0]
# normalize the domain name of Email addresses
sender = fixsender(sender, allsenders)
date = None
y = re.findall('\nDate: .*, (.*)\n', hdr)
sent_at = None
if len(y) >= 1 :
tdate = y[0]
tdate = tdate[:26]
try:
sent_at = parsemaildate(tdate)
except Exception, e:
# print 'Date ignored ',tdate, e
return None
subject = None
z = re.findall('\nSubject: (.*)\n', hdr)
if len(z) >= 1 : subject = z[0].strip().lower()
guid = None
z = re.findall('\nMessage-ID: (.*)\n', hdr)
if len(z) >= 1 : guid = z[0].strip().lower()
if sender is None or sent_at is None or subject is None or guid is None :
return None
return (guid, sender, subject, sent_at)
# Open the output database and create empty tables
conn = sqlite3.connect('index.sqlite')
conn.text_factory = str
cur = conn.cursor()
cur.execute('''DROP TABLE IF EXISTS Messages ''')
cur.execute('''DROP TABLE IF EXISTS Senders ''')
cur.execute('''DROP TABLE IF EXISTS Subjects ''')
cur.execute('''DROP TABLE IF EXISTS Replies ''')
cur.execute('''CREATE TABLE IF NOT EXISTS Messages
(id INTEGER PRIMARY KEY, guid TEXT UNIQUE, sent_at INTEGER,
sender_id INTEGER, subject_id INTEGER,
headers BLOB, body BLOB)''')
cur.execute('''CREATE TABLE IF NOT EXISTS Senders
(id INTEGER PRIMARY KEY, sender TEXT UNIQUE)''')
cur.execute('''CREATE TABLE IF NOT EXISTS Subjects
(id INTEGER PRIMARY KEY, subject TEXT UNIQUE)''')
cur.execute('''CREATE TABLE IF NOT EXISTS Replies
(from_id INTEGER, to_id INTEGER)''')
# Open the mapping information
conn_1 = sqlite3.connect('mapping.sqlite')
conn_1.text_factory = str
cur_1 = conn_1.cursor()
# Load up the mapping information into memory structures
cur_1.execute('''SELECT old,new FROM DNSMapping''')
for message_row in cur_1 :
dnsmapping[message_row[0].strip().lower()] = message_row[1].strip().lower()
mapping = dict()
cur_1.execute('''SELECT old,new FROM Mapping''')
for message_row in cur_1 :
old = fixsender(message_row[0])
new = fixsender(message_row[1])
mapping[old] = fixsender(new)
cur_1.close()
# Open the raw data retrieved from the network
conn_2 = sqlite3.connect('content.sqlite')
conn_2.text_factory = str
cur_2 = conn_2.cursor()
allsenders = list()
cur_2.execute('''SELECT email FROM Messages''')
for message_row in cur_2 :
sender = fixsender(message_row[0])
if sender is None : continue
if 'gmane.org' in sender : continue
if sender in allsenders: continue
allsenders.append(sender)
print "Loaded allsenders",len(allsenders),"and mapping",len(mapping),"dns mapping",len(dnsmapping)
cur_2.execute('''SELECT headers, body, sent_at
FROM Messages ORDER BY sent_at''')
senders = dict()
subjects = dict()
guids = dict()
count = 0
for message_row in cur_2 :
hdr = message_row[0]
parsed = parseheader(hdr, allsenders)
if parsed is None: continue
(guid, sender, subject, sent_at) = parsed
# Apply the sender mapping
sender = mapping.get(sender,sender)
count = count + 1
if count % 250 == 1 : print count,sent_at, sender
# print guid, sender, subject, sent_at
if 'gmane.org' in sender:
print "Error in sender ===", sender
sender_id = senders.get(sender,None)
subject_id = subjects.get(subject,None)
guid_id = guids.get(guid,None)
if sender_id is None :
cur.execute('INSERT OR IGNORE INTO Senders (sender) VALUES ( ? )', ( sender, ) )
conn.commit()
cur.execute('SELECT id FROM Senders WHERE sender=? LIMIT 1', ( sender, ))
try:
row = cur.fetchone()
sender_id = row[0]
senders[sender] = sender_id
except:
print 'Could not retrieve sender id',sender
break
if subject_id is None :
cur.execute('INSERT OR IGNORE INTO Subjects (subject) VALUES ( ? )', ( subject, ) )
conn.commit()
cur.execute('SELECT id FROM Subjects WHERE subject=? LIMIT 1', ( subject, ))
try:
row = cur.fetchone()
subject_id = row[0]
subjects[subject] = subject_id
except:
print 'Could not retrieve subject id',subject
break
# print sender_id, subject_id
cur.execute('INSERT OR IGNORE INTO Messages (guid,sender_id,subject_id,sent_at,headers,body) VALUES ( ?,?,?,datetime(?),?,? )',
( guid, sender_id, subject_id, sent_at, zlib.compress(message_row[0]), zlib.compress(message_row[1])) )
conn.commit()
cur.execute('SELECT id FROM Messages WHERE guid=? LIMIT 1', ( guid, ))
try:
row = cur.fetchone()
message_id = row[0]
guids[guid] = message_id
except:
print 'Could not retrieve guid id',guid
break
# Close the connections
cur.close()
cur_2.close()