OLD | NEW |
| (Empty) |
1 # -*- test-case-name: twisted.news.test.test_news -*- | |
2 # Copyright (c) 2001-2004 Twisted Matrix Laboratories. | |
3 # See LICENSE for details. | |
4 | |
5 | |
6 """ | |
7 News server backend implementations | |
8 | |
9 Maintainer: U{Jp Calderone<mailto:exarkun@twistedmatrix.com>} | |
10 | |
11 Future Plans: A PyFramer-based backend and a new backend interface that is | |
12 less NNTP specific | |
13 """ | |
14 | |
15 | |
16 from __future__ import nested_scopes | |
17 | |
18 from twisted.news.nntp import NNTPError | |
19 from twisted.mail import smtp | |
20 from twisted.internet import defer | |
21 from twisted.enterprise import adbapi | |
22 from twisted.persisted import dirdbm | |
23 | |
24 import getpass, pickle, time, socket, md5 | |
25 import os | |
26 import StringIO | |
27 from zope.interface import implements, Interface | |
28 | |
29 | |
30 ERR_NOGROUP, ERR_NOARTICLE = range(2, 4) # XXX - put NNTP values here (I guess?
) | |
31 | |
32 OVERVIEW_FMT = [ | |
33 'Subject', 'From', 'Date', 'Message-ID', 'References', | |
34 'Bytes', 'Lines', 'Xref' | |
35 ] | |
36 | |
37 def hexdigest(md5): #XXX: argh. 1.5.2 doesn't have this. | |
38 return ''.join(map(lambda x: hex(ord(x))[2:], md5.digest())) | |
39 | |
40 class Article: | |
41 def __init__(self, head, body): | |
42 self.body = body | |
43 self.headers = {} | |
44 header = None | |
45 for line in head.split('\r\n'): | |
46 if line[0] in ' \t': | |
47 i = list(self.headers[header]) | |
48 i[1] += '\r\n' + line | |
49 else: | |
50 i = line.split(': ', 1) | |
51 header = i[0].lower() | |
52 self.headers[header] = tuple(i) | |
53 | |
54 if not self.getHeader('Message-ID'): | |
55 s = str(time.time()) + self.body | |
56 id = hexdigest(md5.md5(s)) + '@' + socket.gethostname() | |
57 self.putHeader('Message-ID', '<%s>' % id) | |
58 | |
59 if not self.getHeader('Bytes'): | |
60 self.putHeader('Bytes', str(len(self.body))) | |
61 | |
62 if not self.getHeader('Lines'): | |
63 self.putHeader('Lines', str(self.body.count('\n'))) | |
64 | |
65 if not self.getHeader('Date'): | |
66 self.putHeader('Date', time.ctime(time.time())) | |
67 | |
68 | |
69 def getHeader(self, header): | |
70 h = header.lower() | |
71 if self.headers.has_key(h): | |
72 return self.headers[h][1] | |
73 else: | |
74 return '' | |
75 | |
76 | |
77 def putHeader(self, header, value): | |
78 self.headers[header.lower()] = (header, value) | |
79 | |
80 | |
81 def textHeaders(self): | |
82 headers = [] | |
83 for i in self.headers.values(): | |
84 headers.append('%s: %s' % i) | |
85 return '\r\n'.join(headers) + '\r\n' | |
86 | |
87 def overview(self): | |
88 xover = [] | |
89 for i in OVERVIEW_FMT: | |
90 xover.append(self.getHeader(i)) | |
91 return xover | |
92 | |
93 | |
94 class NewsServerError(Exception): | |
95 pass | |
96 | |
97 | |
98 class INewsStorage(Interface): | |
99 """ | |
100 An interface for storing and requesting news articles | |
101 """ | |
102 | |
103 def listRequest(): | |
104 """ | |
105 Returns a deferred whose callback will be passed a list of 4-tuples | |
106 containing (name, max index, min index, flags) for each news group | |
107 """ | |
108 | |
109 | |
110 def subscriptionRequest(): | |
111 """ | |
112 Returns a deferred whose callback will be passed the list of | |
113 recommended subscription groups for new server users | |
114 """ | |
115 | |
116 | |
117 def postRequest(message): | |
118 """ | |
119 Returns a deferred whose callback will be invoked if 'message' | |
120 is successfully posted to one or more specified groups and | |
121 whose errback will be invoked otherwise. | |
122 """ | |
123 | |
124 | |
125 def overviewRequest(): | |
126 """ | |
127 Returns a deferred whose callback will be passed the a list of | |
128 headers describing this server's overview format. | |
129 """ | |
130 | |
131 | |
132 def xoverRequest(group, low, high): | |
133 """ | |
134 Returns a deferred whose callback will be passed a list of xover | |
135 headers for the given group over the given range. If low is None, | |
136 the range starts at the first article. If high is None, the range | |
137 ends at the last article. | |
138 """ | |
139 | |
140 | |
141 def xhdrRequest(group, low, high, header): | |
142 """ | |
143 Returns a deferred whose callback will be passed a list of XHDR data | |
144 for the given group over the given range. If low is None, | |
145 the range starts at the first article. If high is None, the range | |
146 ends at the last article. | |
147 """ | |
148 | |
149 | |
150 def listGroupRequest(group): | |
151 """ | |
152 Returns a deferred whose callback will be passed a two-tuple of | |
153 (group name, [article indices]) | |
154 """ | |
155 | |
156 | |
157 def groupRequest(group): | |
158 """ | |
159 Returns a deferred whose callback will be passed a five-tuple of | |
160 (group name, article count, highest index, lowest index, group flags) | |
161 """ | |
162 | |
163 | |
164 def articleExistsRequest(id): | |
165 """ | |
166 Returns a deferred whose callback will be passed with a true value | |
167 if a message with the specified Message-ID exists in the database | |
168 and with a false value otherwise. | |
169 """ | |
170 | |
171 | |
172 def articleRequest(group, index, id = None): | |
173 """ | |
174 Returns a deferred whose callback will be passed a file-like object | |
175 containing the full article text (headers and body) for the article | |
176 of the specified index in the specified group, and whose errback | |
177 will be invoked if the article or group does not exist. If id is | |
178 not None, index is ignored and the article with the given Message-ID | |
179 will be returned instead, along with its index in the specified | |
180 group. | |
181 """ | |
182 | |
183 | |
184 def headRequest(group, index): | |
185 """ | |
186 Returns a deferred whose callback will be passed the header for | |
187 the article of the specified index in the specified group, and | |
188 whose errback will be invoked if the article or group does not | |
189 exist. | |
190 """ | |
191 | |
192 | |
193 def bodyRequest(group, index): | |
194 """ | |
195 Returns a deferred whose callback will be passed the body for | |
196 the article of the specified index in the specified group, and | |
197 whose errback will be invoked if the article or group does not | |
198 exist. | |
199 """ | |
200 | |
201 class NewsStorage: | |
202 """ | |
203 Backwards compatibility class -- There is no reason to inherit from this, | |
204 just implement INewsStorage instead. | |
205 """ | |
206 def listRequest(self): | |
207 raise NotImplementedError() | |
208 def subscriptionRequest(self): | |
209 raise NotImplementedError() | |
210 def postRequest(self, message): | |
211 raise NotImplementedError() | |
212 def overviewRequest(self): | |
213 return defer.succeed(OVERVIEW_FMT) | |
214 def xoverRequest(self, group, low, high): | |
215 raise NotImplementedError() | |
216 def xhdrRequest(self, group, low, high, header): | |
217 raise NotImplementedError() | |
218 def listGroupRequest(self, group): | |
219 raise NotImplementedError() | |
220 def groupRequest(self, group): | |
221 raise NotImplementedError() | |
222 def articleExistsRequest(self, id): | |
223 raise NotImplementedError() | |
224 def articleRequest(self, group, index, id = None): | |
225 raise NotImplementedError() | |
226 def headRequest(self, group, index): | |
227 raise NotImplementedError() | |
228 def bodyRequest(self, group, index): | |
229 raise NotImplementedError() | |
230 | |
231 | |
232 class PickleStorage: | |
233 """A trivial NewsStorage implementation using pickles | |
234 | |
235 Contains numerous flaws and is generally unsuitable for any | |
236 real applications. Consider yourself warned! | |
237 """ | |
238 | |
239 implements(INewsStorage) | |
240 | |
241 sharedDBs = {} | |
242 | |
243 def __init__(self, filename, groups = None, moderators = ()): | |
244 self.datafile = filename | |
245 self.load(filename, groups, moderators) | |
246 | |
247 | |
248 def getModerators(self, groups): | |
249 # first see if any groups are moderated. if so, nothing gets posted, | |
250 # but the whole messages gets forwarded to the moderator address | |
251 moderators = [] | |
252 for group in groups: | |
253 moderators.append(self.db['moderators'].get(group, None)) | |
254 return filter(None, moderators) | |
255 | |
256 | |
257 def notifyModerators(self, moderators, article): | |
258 # Moderated postings go through as long as they have an Approved | |
259 # header, regardless of what the value is | |
260 article.putHeader('To', ', '.join(moderators)) | |
261 return smtp.sendEmail( | |
262 'twisted@' + socket.gethostname(), | |
263 moderators, | |
264 article.body, | |
265 dict(article.headers.values()) | |
266 ) | |
267 | |
268 | |
269 def listRequest(self): | |
270 "Returns a list of 4-tuples: (name, max index, min index, flags)" | |
271 l = self.db['groups'] | |
272 r = [] | |
273 for i in l: | |
274 if len(self.db[i].keys()): | |
275 low = min(self.db[i].keys()) | |
276 high = max(self.db[i].keys()) + 1 | |
277 else: | |
278 low = high = 0 | |
279 if self.db['moderators'].has_key(i): | |
280 flags = 'm' | |
281 else: | |
282 flags = 'y' | |
283 r.append((i, high, low, flags)) | |
284 return defer.succeed(r) | |
285 | |
286 def subscriptionRequest(self): | |
287 return defer.succeed(['alt.test']) | |
288 | |
289 def postRequest(self, message): | |
290 cleave = message.find('\r\n\r\n') | |
291 headers, article = message[:cleave], message[cleave + 4:] | |
292 | |
293 a = Article(headers, article) | |
294 groups = a.getHeader('Newsgroups').split() | |
295 xref = [] | |
296 | |
297 # Check moderated status | |
298 moderators = self.getModerators(groups) | |
299 if moderators and not a.getHeader('Approved'): | |
300 return self.notifyModerators(moderators, a) | |
301 | |
302 for group in groups: | |
303 if self.db.has_key(group): | |
304 if len(self.db[group].keys()): | |
305 index = max(self.db[group].keys()) + 1 | |
306 else: | |
307 index = 1 | |
308 xref.append((group, str(index))) | |
309 self.db[group][index] = a | |
310 | |
311 if len(xref) == 0: | |
312 return defer.fail(None) | |
313 | |
314 a.putHeader('Xref', '%s %s' % ( | |
315 socket.gethostname().split()[0], | |
316 ''.join(map(lambda x: ':'.join(x), xref)) | |
317 )) | |
318 | |
319 self.flush() | |
320 return defer.succeed(None) | |
321 | |
322 | |
323 def overviewRequest(self): | |
324 return defer.succeed(OVERVIEW_FMT) | |
325 | |
326 | |
327 def xoverRequest(self, group, low, high): | |
328 if not self.db.has_key(group): | |
329 return defer.succeed([]) | |
330 r = [] | |
331 for i in self.db[group].keys(): | |
332 if (low is None or i >= low) and (high is None or i <= high): | |
333 r.append([str(i)] + self.db[group][i].overview()) | |
334 return defer.succeed(r) | |
335 | |
336 | |
337 def xhdrRequest(self, group, low, high, header): | |
338 if not self.db.has_key(group): | |
339 return defer.succeed([]) | |
340 r = [] | |
341 for i in self.db[group].keys(): | |
342 if low is None or i >= low and high is None or i <= high: | |
343 r.append((i, self.db[group][i].getHeader(header))) | |
344 return defer.succeed(r) | |
345 | |
346 | |
347 def listGroupRequest(self, group): | |
348 if self.db.has_key(group): | |
349 return defer.succeed((group, self.db[group].keys())) | |
350 else: | |
351 return defer.fail(None) | |
352 | |
353 def groupRequest(self, group): | |
354 if self.db.has_key(group): | |
355 if len(self.db[group].keys()): | |
356 num = len(self.db[group].keys()) | |
357 low = min(self.db[group].keys()) | |
358 high = max(self.db[group].keys()) | |
359 else: | |
360 num = low = high = 0 | |
361 flags = 'y' | |
362 return defer.succeed((group, num, high, low, flags)) | |
363 else: | |
364 return defer.fail(ERR_NOGROUP) | |
365 | |
366 | |
367 def articleExistsRequest(self, id): | |
368 for g in self.db.values(): | |
369 for a in g.values(): | |
370 if a.getHeader('Message-ID') == id: | |
371 return defer.succeed(1) | |
372 return defer.succeed(0) | |
373 | |
374 | |
375 def articleRequest(self, group, index, id = None): | |
376 if id is not None: | |
377 raise NotImplementedError | |
378 | |
379 if self.db.has_key(group): | |
380 if self.db[group].has_key(index): | |
381 a = self.db[group][index] | |
382 return defer.succeed(( | |
383 index, | |
384 a.getHeader('Message-ID'), | |
385 StringIO.StringIO(a.textHeaders() + '\r\n' + a.body) | |
386 )) | |
387 else: | |
388 return defer.fail(ERR_NOARTICLE) | |
389 else: | |
390 return defer.fail(ERR_NOGROUP) | |
391 | |
392 | |
393 def headRequest(self, group, index): | |
394 if self.db.has_key(group): | |
395 if self.db[group].has_key(index): | |
396 a = self.db[group][index] | |
397 return defer.succeed((index, a.getHeader('Message-ID'), a.textHe
aders())) | |
398 else: | |
399 return defer.fail(ERR_NOARTICLE) | |
400 else: | |
401 return defer.fail(ERR_NOGROUP) | |
402 | |
403 | |
404 def bodyRequest(self, group, index): | |
405 if self.db.has_key(group): | |
406 if self.db[group].has_key(index): | |
407 a = self.db[group][index] | |
408 return defer.succeed((index, a.getHeader('Message-ID'), StringIO
.StringIO(a.body))) | |
409 else: | |
410 return defer.fail(ERR_NOARTICLE) | |
411 else: | |
412 return defer.fail(ERR_NOGROUP) | |
413 | |
414 | |
415 def flush(self): | |
416 pickle.dump(self.db, open(self.datafile, 'w')) | |
417 | |
418 | |
419 def load(self, filename, groups = None, moderators = ()): | |
420 if PickleStorage.sharedDBs.has_key(filename): | |
421 self.db = PickleStorage.sharedDBs[filename] | |
422 else: | |
423 try: | |
424 self.db = pickle.load(open(filename)) | |
425 PickleStorage.sharedDBs[filename] = self.db | |
426 except IOError, e: | |
427 self.db = PickleStorage.sharedDBs[filename] = {} | |
428 self.db['groups'] = groups | |
429 if groups is not None: | |
430 for i in groups: | |
431 self.db[i] = {} | |
432 self.db['moderators'] = dict(moderators) | |
433 self.flush() | |
434 | |
435 | |
436 class Group: | |
437 name = None | |
438 flags = '' | |
439 minArticle = 1 | |
440 maxArticle = 0 | |
441 articles = None | |
442 | |
443 def __init__(self, name, flags = 'y'): | |
444 self.name = name | |
445 self.flags = flags | |
446 self.articles = {} | |
447 | |
448 | |
449 class NewsShelf: | |
450 """ | |
451 A NewStorage implementation using Twisted's dirdbm persistence module. | |
452 """ | |
453 | |
454 implements(INewsStorage) | |
455 | |
456 def __init__(self, mailhost, path): | |
457 self.path = path | |
458 self.mailhost = mailhost | |
459 | |
460 if not os.path.exists(path): | |
461 os.mkdir(path) | |
462 | |
463 self.dbm = dirdbm.Shelf(os.path.join(path, "newsshelf")) | |
464 if not len(self.dbm.keys()): | |
465 self.initialize() | |
466 | |
467 | |
468 def initialize(self): | |
469 # A dictionary of group name/Group instance items | |
470 self.dbm['groups'] = dirdbm.Shelf(os.path.join(self.path, 'groups')) | |
471 | |
472 # A dictionary of group name/email address | |
473 self.dbm['moderators'] = dirdbm.Shelf(os.path.join(self.path, 'moderator
s')) | |
474 | |
475 # A list of group names | |
476 self.dbm['subscriptions'] = [] | |
477 | |
478 # A dictionary of MessageID strings/xref lists | |
479 self.dbm['Message-IDs'] = dirdbm.Shelf(os.path.join(self.path, 'Message-
IDs')) | |
480 | |
481 | |
482 def addGroup(self, name, flags): | |
483 self.dbm['groups'][name] = Group(name, flags) | |
484 | |
485 | |
486 def addSubscription(self, name): | |
487 self.dbm['subscriptions'] = self.dbm['subscriptions'] + [name] | |
488 | |
489 | |
490 def addModerator(self, group, email): | |
491 self.dbm['moderators'][group] = email | |
492 | |
493 | |
494 def listRequest(self): | |
495 result = [] | |
496 for g in self.dbm['groups'].values(): | |
497 result.append((g.name, g.maxArticle, g.minArticle, g.flags)) | |
498 return defer.succeed(result) | |
499 | |
500 | |
501 def subscriptionRequest(self): | |
502 return defer.succeed(self.dbm['subscriptions']) | |
503 | |
504 | |
505 def getModerator(self, groups): | |
506 # first see if any groups are moderated. if so, nothing gets posted, | |
507 # but the whole messages gets forwarded to the moderator address | |
508 for group in groups: | |
509 try: | |
510 return self.dbm['moderators'][group] | |
511 except KeyError: | |
512 pass | |
513 return None | |
514 | |
515 | |
516 def notifyModerator(self, moderator, article): | |
517 # Moderated postings go through as long as they have an Approved | |
518 # header, regardless of what the value is | |
519 print 'To is ', moderator | |
520 article.putHeader('To', moderator) | |
521 return smtp.sendEmail( | |
522 self.mailhost, | |
523 'twisted-news@' + socket.gethostname(), | |
524 moderator, | |
525 article.body, | |
526 dict(article.headers.values()) | |
527 ) | |
528 | |
529 | |
530 def postRequest(self, message): | |
531 cleave = message.find('\r\n\r\n') | |
532 headers, article = message[:cleave], message[cleave + 4:] | |
533 | |
534 article = Article(headers, article) | |
535 groups = article.getHeader('Newsgroups').split() | |
536 xref = [] | |
537 | |
538 # Check for moderated status | |
539 moderator = self.getModerator(groups) | |
540 if moderator and not article.getHeader('Approved'): | |
541 return self.notifyModerator(moderator, article) | |
542 | |
543 | |
544 for group in groups: | |
545 try: | |
546 g = self.dbm['groups'][group] | |
547 except KeyError: | |
548 pass | |
549 else: | |
550 index = g.maxArticle + 1 | |
551 g.maxArticle += 1 | |
552 g.articles[index] = article | |
553 xref.append((group, str(index))) | |
554 self.dbm['groups'][group] = g | |
555 | |
556 if not xref: | |
557 return defer.fail(NewsServerError("No groups carried: " + ' '.join(g
roups))) | |
558 | |
559 article.putHeader('Xref', '%s %s' % (socket.gethostname().split()[0], '
'.join(map(lambda x: ':'.join(x), xref)))) | |
560 self.dbm['Message-IDs'][article.getHeader('Message-ID')] = xref | |
561 return defer.succeed(None) | |
562 | |
563 | |
564 def overviewRequest(self): | |
565 return defer.succeed(OVERVIEW_FMT) | |
566 | |
567 | |
568 def xoverRequest(self, group, low, high): | |
569 if not self.dbm['groups'].has_key(group): | |
570 return defer.succeed([]) | |
571 | |
572 if low is None: | |
573 low = 0 | |
574 if high is None: | |
575 high = self.dbm['groups'][group].maxArticle | |
576 r = [] | |
577 for i in range(low, high + 1): | |
578 if self.dbm['groups'][group].articles.has_key(i): | |
579 r.append([str(i)] + self.dbm['groups'][group].articles[i].overvi
ew()) | |
580 return defer.succeed(r) | |
581 | |
582 | |
583 def xhdrRequest(self, group, low, high, header): | |
584 if group not in self.dbm['groups']: | |
585 return defer.succeed([]) | |
586 | |
587 if low is None: | |
588 low = 0 | |
589 if high is None: | |
590 high = self.dbm['groups'][group].maxArticle | |
591 r = [] | |
592 for i in range(low, high + 1): | |
593 if self.dbm['groups'][group].articles.has_key(i): | |
594 r.append((i, self.dbm['groups'][group].articles[i].getHeader(hea
der))) | |
595 return defer.succeed(r) | |
596 | |
597 | |
598 def listGroupRequest(self, group): | |
599 if self.dbm['groups'].has_key(group): | |
600 return defer.succeed((group, self.dbm['groups'][group].articles.keys
())) | |
601 return defer.fail(NewsServerError("No such group: " + group)) | |
602 | |
603 | |
604 def groupRequest(self, group): | |
605 try: | |
606 g = self.dbm['groups'][group] | |
607 except KeyError: | |
608 return defer.fail(NewsServerError("No such group: " + group)) | |
609 else: | |
610 flags = g.flags | |
611 low = g.minArticle | |
612 high = g.maxArticle | |
613 num = high - low + 1 | |
614 return defer.succeed((group, num, high, low, flags)) | |
615 | |
616 | |
617 def articleExistsRequest(self, id): | |
618 return defer.succeed(id in self.dbm['Message-IDs']) | |
619 | |
620 | |
621 def articleRequest(self, group, index, id = None): | |
622 if id is not None: | |
623 try: | |
624 xref = self.dbm['Message-IDs'][id] | |
625 except KeyError: | |
626 return defer.fail(NewsServerError("No such article: " + id)) | |
627 else: | |
628 group, index = xref[0] | |
629 index = int(index) | |
630 | |
631 try: | |
632 a = self.dbm['groups'][group].articles[index] | |
633 except KeyError: | |
634 return defer.fail(NewsServerError("No such group: " + group)) | |
635 else: | |
636 return defer.succeed(( | |
637 index, | |
638 a.getHeader('Message-ID'), | |
639 StringIO.StringIO(a.textHeaders() + '\r\n' + a.body) | |
640 )) | |
641 | |
642 | |
643 def headRequest(self, group, index, id = None): | |
644 if id is not None: | |
645 try: | |
646 xref = self.dbm['Message-IDs'][id] | |
647 except KeyError: | |
648 return defer.fail(NewsServerError("No such article: " + id)) | |
649 else: | |
650 group, index = xref[0] | |
651 index = int(index) | |
652 | |
653 try: | |
654 a = self.dbm['groups'][group].articles[index] | |
655 except KeyError: | |
656 return defer.fail(NewsServerError("No such group: " + group)) | |
657 else: | |
658 return defer.succeed((index, a.getHeader('Message-ID'), a.textHeader
s())) | |
659 | |
660 | |
661 def bodyRequest(self, group, index, id = None): | |
662 if id is not None: | |
663 try: | |
664 xref = self.dbm['Message-IDs'][id] | |
665 except KeyError: | |
666 return defer.fail(NewsServerError("No such article: " + id)) | |
667 else: | |
668 group, index = xref[0] | |
669 index = int(index) | |
670 | |
671 try: | |
672 a = self.dbm['groups'][group].articles[index] | |
673 except KeyError: | |
674 return defer.fail(NewsServerError("No such group: " + group)) | |
675 else: | |
676 return defer.succeed((index, a.getHeader('Message-ID'), StringIO.Str
ingIO(a.body))) | |
677 | |
678 | |
679 class NewsStorageAugmentation: | |
680 """ | |
681 A NewsStorage implementation using Twisted's asynchronous DB-API | |
682 """ | |
683 | |
684 implements(INewsStorage) | |
685 | |
686 schema = """ | |
687 | |
688 CREATE TABLE groups ( | |
689 group_id SERIAL, | |
690 name VARCHAR(80) NOT NULL, | |
691 | |
692 flags INTEGER DEFAULT 0 NOT NULL | |
693 ); | |
694 | |
695 CREATE UNIQUE INDEX group_id_index ON groups (group_id); | |
696 CREATE UNIQUE INDEX name_id_index ON groups (name); | |
697 | |
698 CREATE TABLE articles ( | |
699 article_id SERIAL, | |
700 message_id TEXT, | |
701 | |
702 header TEXT, | |
703 body TEXT | |
704 ); | |
705 | |
706 CREATE UNIQUE INDEX article_id_index ON articles (article_id); | |
707 CREATE UNIQUE INDEX article_message_index ON articles (message_id); | |
708 | |
709 CREATE TABLE postings ( | |
710 group_id INTEGER, | |
711 article_id INTEGER, | |
712 article_index INTEGER NOT NULL | |
713 ); | |
714 | |
715 CREATE UNIQUE INDEX posting_article_index ON postings (article_id); | |
716 | |
717 CREATE TABLE subscriptions ( | |
718 group_id INTEGER | |
719 ); | |
720 | |
721 CREATE TABLE overview ( | |
722 header TEXT | |
723 ); | |
724 """ | |
725 | |
726 def __init__(self, info): | |
727 self.info = info | |
728 self.dbpool = adbapi.ConnectionPool(**self.info) | |
729 | |
730 | |
731 def __setstate__(self, state): | |
732 self.__dict__ = state | |
733 self.info['password'] = getpass.getpass('Database password for %s: ' % (
self.info['user'],)) | |
734 self.dbpool = adbapi.ConnectionPool(**self.info) | |
735 del self.info['password'] | |
736 | |
737 | |
738 def listRequest(self): | |
739 # COALESCE may not be totally portable | |
740 # it is shorthand for | |
741 # CASE WHEN (first parameter) IS NOT NULL then (first parameter) ELSE (s
econd parameter) END | |
742 sql = """ | |
743 SELECT groups.name, | |
744 COALESCE(MAX(postings.article_index), 0), | |
745 COALESCE(MIN(postings.article_index), 0), | |
746 groups.flags | |
747 FROM groups LEFT OUTER JOIN postings | |
748 ON postings.group_id = groups.group_id | |
749 GROUP BY groups.name, groups.flags | |
750 ORDER BY groups.name | |
751 """ | |
752 return self.dbpool.runQuery(sql) | |
753 | |
754 | |
755 def subscriptionRequest(self): | |
756 sql = """ | |
757 SELECT groups.name FROM groups,subscriptions WHERE groups.group_id =
subscriptions.group_id | |
758 """ | |
759 return self.dbpool.runQuery(sql) | |
760 | |
761 | |
762 def postRequest(self, message): | |
763 cleave = message.find('\r\n\r\n') | |
764 headers, article = message[:cleave], message[cleave + 4:] | |
765 article = Article(headers, article) | |
766 return self.dbpool.runInteraction(self._doPost, article) | |
767 | |
768 | |
769 def _doPost(self, transaction, article): | |
770 # Get the group ids | |
771 groups = article.getHeader('Newsgroups').split() | |
772 if not len(groups): | |
773 raise NNTPError('Missing Newsgroups header') | |
774 | |
775 sql = """ | |
776 SELECT name, group_id FROM groups | |
777 WHERE name IN (%s) | |
778 """ % (', '.join([("'%s'" % (adbapi.safe(group),)) for group in groups])
,) | |
779 | |
780 transaction.execute(sql) | |
781 result = transaction.fetchall() | |
782 | |
783 # No relevant groups, bye bye! | |
784 if not len(result): | |
785 raise NNTPError('None of groups in Newsgroup header carried') | |
786 | |
787 # Got some groups, now find the indices this article will have in each | |
788 sql = """ | |
789 SELECT groups.group_id, COALESCE(MAX(postings.article_index), 0) + 1 | |
790 FROM groups LEFT OUTER JOIN postings | |
791 ON postings.group_id = groups.group_id | |
792 WHERE groups.group_id IN (%s) | |
793 GROUP BY groups.group_id | |
794 """ % (', '.join([("%d" % (id,)) for (group, id) in result]),) | |
795 | |
796 transaction.execute(sql) | |
797 indices = transaction.fetchall() | |
798 | |
799 if not len(indices): | |
800 raise NNTPError('Internal server error - no indices found') | |
801 | |
802 # Associate indices with group names | |
803 gidToName = dict([(b, a) for (a, b) in result]) | |
804 gidToIndex = dict(indices) | |
805 | |
806 nameIndex = [] | |
807 for i in gidToName: | |
808 nameIndex.append((gidToName[i], gidToIndex[i])) | |
809 | |
810 # Build xrefs | |
811 xrefs = socket.gethostname().split()[0] | |
812 xrefs = xrefs + ' ' + ' '.join([('%s:%d' % (group, id)) for (group, id)
in nameIndex]) | |
813 article.putHeader('Xref', xrefs) | |
814 | |
815 # Hey! The article is ready to be posted! God damn f'in finally. | |
816 sql = """ | |
817 INSERT INTO articles (message_id, header, body) | |
818 VALUES ('%s', '%s', '%s') | |
819 """ % ( | |
820 adbapi.safe(article.getHeader('Message-ID')), | |
821 adbapi.safe(article.textHeaders()), | |
822 adbapi.safe(article.body) | |
823 ) | |
824 | |
825 transaction.execute(sql) | |
826 | |
827 # Now update the posting to reflect the groups to which this belongs | |
828 for gid in gidToName: | |
829 sql = """ | |
830 INSERT INTO postings (group_id, article_id, article_index) | |
831 VALUES (%d, (SELECT last_value FROM articles_article_id_seq), %d
) | |
832 """ % (gid, gidToIndex[gid]) | |
833 transaction.execute(sql) | |
834 | |
835 return len(nameIndex) | |
836 | |
837 | |
838 def overviewRequest(self): | |
839 sql = """ | |
840 SELECT header FROM overview | |
841 """ | |
842 return self.dbpool.runQuery(sql).addCallback(lambda result: [header[0] f
or header in result]) | |
843 | |
844 | |
845 def xoverRequest(self, group, low, high): | |
846 sql = """ | |
847 SELECT postings.article_index, articles.header | |
848 FROM articles,postings,groups | |
849 WHERE postings.group_id = groups.group_id | |
850 AND groups.name = '%s' | |
851 AND postings.article_id = articles.article_id | |
852 %s | |
853 %s | |
854 """ % ( | |
855 adbapi.safe(group), | |
856 low is not None and "AND postings.article_index >= %d" % (low,) or "
", | |
857 high is not None and "AND postings.article_index <= %d" % (high,) or
"" | |
858 ) | |
859 | |
860 return self.dbpool.runQuery(sql).addCallback( | |
861 lambda results: [ | |
862 [id] + Article(header, None).overview() for (id, header) in resu
lts | |
863 ] | |
864 ) | |
865 | |
866 | |
867 def xhdrRequest(self, group, low, high, header): | |
868 sql = """ | |
869 SELECT articles.header | |
870 FROM groups,postings,articles | |
871 WHERE groups.name = '%s' AND postings.group_id = groups.group_id | |
872 AND postings.article_index >= %d | |
873 AND postings.article_index <= %d | |
874 """ % (adbapi.safe(group), low, high) | |
875 | |
876 return self.dbpool.runQuery(sql).addCallback( | |
877 lambda results: [ | |
878 (i, Article(h, None).getHeader(h)) for (i, h) in results | |
879 ] | |
880 ) | |
881 | |
882 | |
883 def listGroupRequest(self, group): | |
884 sql = """ | |
885 SELECT postings.article_index FROM postings,groups | |
886 WHERE postings.group_id = groups.group_id | |
887 AND groups.name = '%s' | |
888 """ % (adbapi.safe(group),) | |
889 | |
890 return self.dbpool.runQuery(sql).addCallback( | |
891 lambda results, group = group: (group, [res[0] for res in results]) | |
892 ) | |
893 | |
894 | |
895 def groupRequest(self, group): | |
896 sql = """ | |
897 SELECT groups.name, | |
898 COUNT(postings.article_index), | |
899 COALESCE(MAX(postings.article_index), 0), | |
900 COALESCE(MIN(postings.article_index), 0), | |
901 groups.flags | |
902 FROM groups LEFT OUTER JOIN postings | |
903 ON postings.group_id = groups.group_id | |
904 WHERE groups.name = '%s' | |
905 GROUP BY groups.name, groups.flags | |
906 """ % (adbapi.safe(group),) | |
907 | |
908 return self.dbpool.runQuery(sql).addCallback( | |
909 lambda results: tuple(results[0]) | |
910 ) | |
911 | |
912 | |
913 def articleExistsRequest(self, id): | |
914 sql = """ | |
915 SELECT COUNT(message_id) FROM articles | |
916 WHERE message_id = '%s' | |
917 """ % (adbapi.safe(id),) | |
918 | |
919 return self.dbpool.runQuery(sql).addCallback( | |
920 lambda result: bool(result[0][0]) | |
921 ) | |
922 | |
923 | |
924 def articleRequest(self, group, index, id = None): | |
925 if id is not None: | |
926 sql = """ | |
927 SELECT postings.article_index, articles.message_id, articles.hea
der, articles.body | |
928 FROM groups,postings LEFT OUTER JOIN articles | |
929 ON articles.message_id = '%s' | |
930 WHERE groups.name = '%s' | |
931 AND groups.group_id = postings.group_id | |
932 """ % (adbapi.safe(id), adbapi.safe(group)) | |
933 else: | |
934 sql = """ | |
935 SELECT postings.article_index, articles.message_id, articles.hea
der, articles.body | |
936 FROM groups,articles LEFT OUTER JOIN postings | |
937 ON postings.article_id = articles.article_id | |
938 WHERE postings.article_index = %d | |
939 AND postings.group_id = groups.group_id | |
940 AND groups.name = '%s' | |
941 """ % (index, adbapi.safe(group)) | |
942 | |
943 return self.dbpool.runQuery(sql).addCallback( | |
944 lambda result: ( | |
945 result[0][0], | |
946 result[0][1], | |
947 StringIO.StringIO(result[0][2] + '\r\n' + result[0][3]) | |
948 ) | |
949 ) | |
950 | |
951 | |
952 def headRequest(self, group, index): | |
953 sql = """ | |
954 SELECT postings.article_index, articles.message_id, articles.header | |
955 FROM groups,articles LEFT OUTER JOIN postings | |
956 ON postings.article_id = articles.article_id | |
957 WHERE postings.article_index = %d | |
958 AND postings.group_id = groups.group_id | |
959 AND groups.name = '%s' | |
960 """ % (index, adbapi.safe(group)) | |
961 | |
962 return self.dbpool.runQuery(sql).addCallback(lambda result: result[0]) | |
963 | |
964 | |
965 def bodyRequest(self, group, index): | |
966 sql = """ | |
967 SELECT postings.article_index, articles.message_id, articles.body | |
968 FROM groups,articles LEFT OUTER JOIN postings | |
969 ON postings.article_id = articles.article_id | |
970 WHERE postings.article_index = %d | |
971 AND postings.group_id = groups.group_id | |
972 AND groups.name = '%s' | |
973 """ % (index, adbapi.safe(group)) | |
974 | |
975 return self.dbpool.runQuery(sql).addCallback( | |
976 lambda result: result[0] | |
977 ).addCallback( | |
978 lambda (index, id, body): (index, id, StringIO.StringIO(body)) | |
979 ) | |
980 | |
981 #### | |
982 #### XXX - make these static methods some day | |
983 #### | |
984 def makeGroupSQL(groups): | |
985 res = '' | |
986 for g in groups: | |
987 res = res + """\n INSERT INTO groups (name) VALUES ('%s');\n""" % (ad
bapi.safe(g),) | |
988 return res | |
989 | |
990 | |
991 def makeOverviewSQL(): | |
992 res = '' | |
993 for o in OVERVIEW_FMT: | |
994 res = res + """\n INSERT INTO overview (header) VALUES ('%s');\n""" %
(adbapi.safe(o),) | |
995 return res | |
OLD | NEW |