def get_HEAD(self, group_name, id): forum_id = self.get_forum(group_name) prefix = settings.phpbb_table_prefix stmt = """ SELECT A.post_id, C.username, C.user_email, CASE WHEN B.post_subject = '' THEN CONCAT('Re: ', E.topic_title) ELSE B.post_subject END, A.post_time, A.topic_id, A.post_username, MIN(D.post_id) FROM %sposts A, %sposts_text B INNER JOIN %stopics E ON A.topic_id = E.topic_id INNER JOIN %sposts D ON D.topic_id=A.topic_id LEFT JOIN %susers C ON A.poster_id=C.user_id WHERE A.forum_id=%s AND A.post_id=B.post_id AND A.post_id=%s GROUP BY D.topic_id""" % (prefix, prefix, prefix, prefix, prefix, forum_id, id) num_rows = self.cursor.execute(stmt) if num_rows == 0: return None result = list(self.cursor.fetchone()) # check if there is a registered user if len(result[6]) == 0 or result[6] == '': if len(result[2]) == 0: author = result[1] else: author = "%s <%s>" % (result[1], result[2]) else: author = result[6] formatted_time = strutil.get_formatted_time(time.localtime(result[4])) headers = [] headers.append("Path: %s" % (settings.nntp_hostname)) headers.append("From: %s" % (author)) headers.append("Newsgroups: %s" % (group_name)) headers.append("Date: %s" % (formatted_time)) headers.append("Subject: %s" % (result[3])) headers.append("Message-ID: <%s@%s>" % (result[0], group_name)) headers.append("Xref: %s %s:%s" % (settings.nntp_hostname, group_name, result[0])) if result[7] != result[0]: headers.append("References: <%s@%s>" % (result[7], group_name)) return "\r\n".join(headers)
def get_ARTICLE(self, group_name, id): table_name = self.get_table_name(group_name) stmt = """ SELECT id, author, subject, UNIX_TIMESTAMP(datestamp) AS datestamp, body, parent FROM %s WHERE id=%s""" % (table_name, id) num_rows = self.cursor.execute(stmt) if num_rows == 0: return None result = list(self.cursor.fetchone()) headers = [] headers.append("Path: %s" % (settings.nntp_hostname)) headers.append("From: %s" % (result[1])) headers.append("Newsgroups: %s" % (group_name)) headers.append("Date: %s" % (strutil.get_formatted_time(time.localtime(result[3])))) headers.append("Subject: %s" % (result[2])) headers.append("Message-ID: <%s@%s>" % (result[0], group_name)) headers.append("Xref: %s %s:%s" % (settings.nntp_hostname, group_name, result[0])) if result[5] != 0: headers.append("References: <%s@%s>" % (result[5], group_name)) return ("\r\n".join(headers), strutil.format_body(result[4]))
def get_HEAD(self, group_name, id): table_name = self.get_table_name(group_name) stmt = """ SELECT id, author, email, subject, UNIX_TIMESTAMP(datestamp) AS datestamp, parent FROM %s WHERE approved='Y' AND id=%s""" % (table_name, id) num_rows = self.cursor.execute(stmt) if num_rows == 0: return None result = list(self.cursor.fetchone()) if len(result[2]) == 0: author = result[1] else: author = "%s <%s>" % (result[1], result[2]) formatted_time = strutil.get_formatted_time(time.localtime(result[4])) headers = [] headers.append("Path: %s" % (settings.nntp_hostname)) headers.append("From: %s" % (author)) headers.append("Newsgroups: %s" % (group_name)) headers.append("Date: %s" % (formatted_time)) headers.append("Subject: %s" % (result[3])) headers.append("Message-ID: <%s@%s>" % (result[0], group_name)) headers.append("Xref: %s %s:%s" % (settings.nntp_hostname, group_name, result[0])) if result[5] != 0: headers.append("References: <%s@%s>" % (result[5], group_name)) return "\r\n".join(headers)
def get_XOVER(self, group_name, start_id, end_id=100): group = decut(group_name) if len(self.xn.forums[group]['posts']) == 0: return "" overviews = [] # message_number <tab> subject <tab> author <tab> date <tab> message_id <tab> reference <tab> bytes <tab> lines <tab> xref for index, post in enumerate( self.xn.forums[group]['posts'][int(start_id) - 1:int(end_id)]): if 'reference' in post: reference = post['reference'] else: reference = '' msg_num = index + 1 overviews.append( "%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s" % (msg_num, post['nntp_subject'], post['username'], strutil.get_formatted_time(time.localtime( post['post_date'])), post['nntp_message_id'], reference, 8192, 50, 'Xref: %s %s:%s' % (settings.nntp_hostname, group_name, msg_num))) return "\r\n".join(overviews)
def get_ARTICLE(self, group_name, id): forum_id = self.get_forum(group_name) prefix = settings.phpbb_table_prefix nuke_prefix = settings.nuke_table_prefix stmt = """ SELECT A.post_id, C.username, C.user_email, CASE WHEN B.post_subject = '' THEN CONCAT('Re: ', E.topic_title) ELSE B.post_subject END, A.post_time, B.post_text, A.topic_id, A.post_username, MIN(D.post_id) FROM %sposts A, %sposts_text B INNER JOIN %sposts D ON D.topic_id=A.topic_id INNER JOIN %stopics E ON A.topic_id = E.topic_id LEFT JOIN %susers C ON A.poster_id=C.user_id WHERE A.forum_id=%s AND A.post_id=B.post_id AND A.post_id=%s GROUP BY D.topic_id""" % (prefix, prefix, prefix, prefix, nuke_prefix, forum_id, id) num_rows = self.cursor.execute(stmt) if num_rows == 0: return None result = list(self.cursor.fetchone()) # check if there is a registered user if result[7] == '': if len(result[2]) == 0: author = result[1] else: author = "%s <%s>" % (result[1], result[2]) else: author = result[7] formatted_time = strutil.get_formatted_time(time.localtime(result[4])) headers = [] headers.append("Path: %s" % (settings.nntp_hostname)) headers.append("From: %s" % (author)) headers.append("Newsgroups: %s" % (group_name)) headers.append("Date: %s" % (formatted_time)) headers.append("Subject: %s" % (result[3])) headers.append("Message-ID: <%s@%s>" % (result[0], group_name)) headers.append("Xref: %s %s:%s" % (settings.nntp_hostname, group_name, result[0])) if result[8] != result[0]: headers.append("References: <%s@%s>" % (result[8], group_name)) return ("\r\n".join(headers), strutil.format_body(result[5]))
def get_XPAT(self, group_name, header, pattern, start_id, end_id='ggg'): # XXX: need to actually check for the header values being passed as # XXX: not all header names map to column names on the tables forum_id = self.get_forum(group_name) prefix = settings.phpbb_table_prefix stmt = """ SELECT A.post_id, A.topic_id, C.username, C.user_email, CASE WHEN B.post_subject = '' THEN CONCAT('Re: ', D.topic_title) ELSE B.post_subject END, A.post_time, B.post_text, A.post_username FROM %sposts A, %sposts_text B LEFT JOIN %susers C ON A.poster_id=C.user_id LEFT JOIN %stopics D ON A.topic_id = D.topic_id WHERE A.forum_id=%s AND %s REGEXP '%s' AND A.post_id = B.post_id AND A.post_id >= %s""" % (prefix, prefix, prefix, prefix, forum_id, header, strutil.format_wildcards(pattern), start_id) if end_id != 'ggg': stmt = "%s AND A.post_id <= %s" % (stmt, end_id) num_rows = self.cursor.execute(stmt) if num_rows == 0: return None result = list(self.cursor.fetchall()) hdrs = [] for row in result: if header.upper() == 'SUBJECT': hdrs.append('%s %s' % (row[0], row[4])) elif header.upper() == 'FROM': # XXX: totally broken with empty values for the email address hdrs.append('%s %s <%s>' % (row[0], row[2], row[3])) elif header.upper() == 'DATE': hdrs.append('%s %s' % (row[0], strutil.get_formatted_time(time.localtime(result[5])))) elif header.upper() == 'MESSAGE-ID': hdrs.append('%s <%s@%s>' % (row[0], row[0], group_name)) elif (header.upper() == 'REFERENCES') and (row[1] != 0): hdrs.append('%s <%s@%s>' % (row[0], row[1], group_name)) elif header.upper() == 'BYTES': hdrs.append('%s %s' % (row[0], len(row[6]))) elif header.upper() == 'LINES': hdrs.append('%s %s' % (row[0], len(row[6].split('\n')))) elif header.upper() == 'XREF': hdrs.append('%s %s %s:%s' % (row[0], settings.nntp_hostname, group_name, row[0])) if len(hdrs) == 0: return "" else: return "\r\n".join(hdrs)
def get_XHDR(self, group_name, header, style, range): forum_id = self.get_forum(group_name) prefix = settings.phpbb_table_prefix nuke_prefix = settings.nuke_table_prefix stmt = """ SELECT A.post_id, A.topic_id, D.username, D.user_email, CASE WHEN B.post_subject = '' THEN CONCAT('Re: ', C.topic_title) ELSE B.post_subject END, A.post_time, B.post_text, A.post_username FROM %sposts A, %sposts_text B LEFT JOIN %stopics C ON A.topic_id = C.topic_id LEFT JOIN %susers D ON A.poster_id=D.user_id WHERE A.forum_id=%s AND A.post_id = B.post_id AND """ % (prefix, prefix, prefix, nuke_prefix, forum_id) if style == 'range': stmt = '%s A.post_id >= %s' % (stmt, range[0]) if len(range) == 2: stmt = '%s AND A.post_id <= %s' % (stmt, range[1]) else: stmt = '%s A.post_id = %s' % (stmt, range[0]) if self.cursor.execute(stmt) == 0: return None result = self.cursor.fetchall() hdrs = [] for row in result: if header.upper() == 'SUBJECT': hdrs.append('%s %s' % (row[0], row[4])) elif header.upper() == 'FROM': hdrs.append('%s %s <%s>' % (row[0], row[2], row[3])) elif header.upper() == 'DATE': hdrs.append('%s %s' % (row[0], strutil.get_formatted_time(time.localtime(result[5])))) elif header.upper() == 'MESSAGE-ID': hdrs.append('%s <%s@%s>' % (row[0], row[0], group_name)) elif (header.upper() == 'REFERENCES') and (row[1] != 0): hdrs.append('%s <%s@%s>' % (row[0], row[1], group_name)) elif header.upper() == 'BYTES': hdrs.append('%s %s' % (row[0], len(row[6]))) elif header.upper() == 'LINES': hdrs.append('%s %s' % (row[0], len(row[6].split('\n')))) elif header.upper() == 'XREF': hdrs.append('%s %s %s:%s' % (row[0], settings.nntp_hostname, group_name, row[0])) if len(hdrs) == 0: return "" else: return "\r\n".join(hdrs)
def get_XPAT(self, group_name, header, pattern, start_id, end_id='ggg'): table_name = self.get_table_name(group_name) stmt = """ SELECT id, parent, author, subject, UNIX_TIMESTAMP(datestamp) AS datestamp, bytes, line_num FROM %s WHERE id >= %s AND""" % ( table_name, header, strutil.format_wildcards(pattern), start_id) if header.upper() == 'SUBJECT': stmt = "%s AND subject REGEXP '%s'" % ( stmt, strutil.format_wildcards(pattern)) elif header.upper() == 'FROM': stmt = "%s AND (author REGEXP '%s' OR email REGEXP '%s')" % ( stmt, strutil.format_wildcards(pattern), strutil.format_wildcards(pattern)) elif header.upper() == 'DATE': stmt = "%s AND %s" % (stmt, pattern) if end_id != 'ggg': stmt = "%s AND id <= %s" % (stmt, end_id) num_rows = self.cursor.execute(stmt) if num_rows == 0: return None result = list(self.cursor.fetchall()) hdrs = [] for row in result: if header.upper() == 'SUBJECT': hdrs.append('%s %s' % (row[0], row[3])) elif header.upper() == 'FROM': hdrs.append('%s %s' % (row[0], row[2])) elif header.upper() == 'DATE': hdrs.append( '%s %s' % (row[0], strutil.get_formatted_time(time.localtime(result[4])))) elif header.upper() == 'MESSAGE-ID': hdrs.append('%s <%s@%s>' % (row[0], row[0], group_name)) elif (header.upper() == 'REFERENCES') and (row[1] != 0): hdrs.append('%s <%s@%s>' % (row[0], row[1], group_name)) elif header.upper() == 'BYTES': hdrs.append('%s %s' % (row[0], row[5])) elif header.upper() == 'LINES': hdrs.append('%s %s' % (row[0], row[6])) elif header.upper() == 'XREF': hdrs.append( '%s %s %s:%s' % (row[0], settings.nntp_hostname, group_name, row[0])) if len(hdrs) == 0: return "" else: return "\r\n".join(hdrs)
def get_XOVER(self, group_name, start_id, end_id='ggg'): forum_id = self.get_forum(group_name) prefix = settings.phpbb_table_prefix stmt = """ SELECT A.post_id, A.topic_id, C.username, C.user_email, CASE WHEN B.post_subject = '' THEN CONCAT('Re: ', D.topic_title) ELSE B.post_subject END, A.post_time, B.post_text, A.post_username FROM %sposts A, %sposts_text B LEFT JOIN %susers C ON A.poster_id=C.user_id LEFT JOIN %stopics D ON A.topic_id = D.topic_id WHERE A.post_id=B.post_id AND A.forum_id=%s AND A.post_id >= %s""" % (prefix, prefix, prefix, prefix, forum_id, start_id) if end_id != 'ggg': stmt = "%s AND A.post_id <= %s" % (stmt, end_id) self.cursor.execute(stmt) result = list(self.cursor.fetchall()) overviews = [] for row in result: if row[7] == '': if row[3] == '': author = row[2] else: author = "%s <%s>" % (row[2], row[3]) else: author = row[7] formatted_time = strutil.get_formatted_time(time.localtime(row[5])) message_id = "<%s@%s>" % (row[0], group_name) line_count = len(row[6].split('\n')) xref = 'Xref: %s %s:%s' % (settings.nntp_hostname, group_name, row[0]) if row[1] != row[0]: reference = "<%s@%s>" % (row[1], group_name) else: reference = "" # message_number <tab> subject <tab> author <tab> date <tab> message_id <tab> reference <tab> bytes <tab> lines <tab> xref overviews.append( "%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s" % (row[0], row[4], author, formatted_time, message_id, reference, len(strutil.format_body(row[6])), line_count, xref)) return "\r\n".join(overviews)
def get_XOVER(self, group_name, start_id, end_id='ggg'): forum_id = self.get_forum(group_name) prefix = settings.phpbb_table_prefix nuke_prefix = settings.nuke_table_prefix stmt = """ SELECT A.post_id, A.topic_id, C.username, C.user_email, CASE WHEN B.post_subject = '' THEN CONCAT('Re: ', D.topic_title) ELSE B.post_subject END, A.post_time, B.post_text, A.post_username FROM %sposts A, %sposts_text B LEFT JOIN %susers C ON A.poster_id=C.user_id LEFT JOIN %stopics D ON A.topic_id = D.topic_id WHERE A.post_id=B.post_id AND A.forum_id=%s AND A.post_id >= %s""" % (prefix, prefix, nuke_prefix, prefix, forum_id, start_id) if end_id != 'ggg': stmt = "%s AND A.post_id <= %s" % (stmt, end_id) self.cursor.execute(stmt) result = list(self.cursor.fetchall()) overviews = [] for row in result: if row[7] == '': if row[3] == '': author = row[2] else: author = "%s <%s>" % (row[2], row[3]) else: author = row[7] formatted_time = strutil.get_formatted_time(time.localtime(row[5])) message_id = "<%s@%s>" % (row[0], group_name) line_count = len(row[6].split('\n')) xref = 'Xref: %s %s:%s' % (settings.nntp_hostname, group_name, row[0]) if row[1] != row[0]: reference = "<%s@%s>" % (row[1], group_name) else: reference = "" # message_number <tab> subject <tab> author <tab> date <tab> message_id <tab> reference <tab> bytes <tab> lines <tab> xref overviews.append("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s" % (row[0], row[4], author, formatted_time, message_id, reference, len(strutil.format_body(row[6])), line_count, xref)) return "\r\n".join(overviews)
def get_XHDR(self, group_name, header, style, range): table_name = self.get_table_name(group_name) stmt = """ SELECT id, parent, author, subject, UNIX_TIMESTAMP(datestamp) AS datestamp, bytes, line_num FROM %s WHERE """ % (table_name) if style == 'range': stmt = '%s id >= %s' % (stmt, range[0]) if len(range) == 2: stmt = '%s AND id <= %s' % (stmt, range[1]) else: stmt = '%s id = %s' % (stmt, range[0]) if self.cursor.execute(stmt) == 0: return None result = self.cursor.fetchall() hdrs = [] for row in result: if header.upper() == 'SUBJECT': hdrs.append('%s %s' % (row[0], row[3])) elif header.upper() == 'FROM': hdrs.append('%s %s' % (row[0], row[2])) elif header.upper() == 'DATE': hdrs.append( '%s %s' % (row[0], strutil.get_formatted_time(time.localtime(result[4])))) elif header.upper() == 'MESSAGE-ID': hdrs.append('%s <%s@%s>' % (row[0], row[0], group_name)) elif (header.upper() == 'REFERENCES') and (row[1] != 0): hdrs.append('%s <%s@%s>' % (row[0], row[1], group_name)) elif header.upper() == 'BYTES': hdrs.append('%s %s' % (row[0], row[6])) elif header.upper() == 'LINES': hdrs.append('%s %s' % (row[0], row[7])) elif header.upper() == 'XREF': hdrs.append( '%s %s %s:%s' % (row[0], settings.nntp_hostname, group_name, row[0])) if len(hdrs) == 0: return "" else: return "\r\n".join(hdrs)
def get_XPAT(self, group_name, header, pattern, start_id, end_id='ggg'): # XXX: need to actually check for the header values being passed as # XXX: not all header names map to column names on the tables table_name = self.get_table_name(group_name) stmt = """ SELECT A.id, parent, author, email, subject, UNIX_TIMESTAMP(datestamp) AS datestamp, B.body FROM %s A, %s_bodies B WHERE A.approved='Y' AND %s REGEXP '%s' AND A.id = B.id AND A.id >= %s""" % (table_name, table_name, header, strutil.format_wildcards(pattern), start_id) if end_id != 'ggg': stmt = "%s AND A.id <= %s" % (stmt, end_id) num_rows = self.cursor.execute(stmt) if num_rows == 0: return None result = list(self.cursor.fetchall()) hdrs = [] for row in result: if header.upper() == 'SUBJECT': hdrs.append('%s %s' % (row[0], row[4])) elif header.upper() == 'FROM': # XXX: totally broken with empty values for the email address hdrs.append('%s %s <%s>' % (row[0], row[2], row[3])) elif header.upper() == 'DATE': hdrs.append('%s %s' % (row[0], strutil.get_formatted_time(time.localtime(result[5])))) elif header.upper() == 'MESSAGE-ID': hdrs.append('%s <%s@%s>' % (row[0], row[0], group_name)) elif (header.upper() == 'REFERENCES') and (row[1] != 0): hdrs.append('%s <%s@%s>' % (row[0], row[1], group_name)) elif header.upper() == 'BYTES': hdrs.append('%s %s' % (row[0], len(row[6]))) elif header.upper() == 'LINES': hdrs.append('%s %s' % (row[0], len(row[6].split('\n')))) elif header.upper() == 'XREF': hdrs.append('%s %s %s:%s' % (row[0], settings.nntp_hostname, group_name, row[0])) if len(hdrs) == 0: return "" else: return "\r\n".join(hdrs)
def create_usenet_headers(self, post, id): headers = [] headers.append("Path: %s" % (settings.nntp_hostname)) headers.append("From: %s" % (post['username'])) headers.append("Newsgroups: %s" % (post['nntp_group_name'])) headers.append("Date: %s" % (strutil.get_formatted_time(time.localtime(post['post_date'])))) headers.append("Subject: %s" % (post['nntp_subject'])) headers.append("Message-ID: %s" % (post['nntp_message_id'])) # easiest. # headers.append("Xref: %s %s:%s" % (settings.nntp_hostname, post['nntp_group_name'], id)) if post['references']: headers.append("References: %s" % post['references']) return "\r\n".join(headers)
def get_XHDR(self, group_name, header, style, range): table_name = self.get_table_name(group_name) stmt = """ SELECT A.id, parent, author, email, subject, UNIX_TIMESTAMP(datestamp) AS datestamp, B.body FROM %s A, %s_bodies B WHERE A.approved='Y' AND A.id = B.id AND """ % (table_name, table_name) if style == 'range': stmt = '%s A.id >= %s' % (stmt, range[0]) if len(range) == 2: stmt = '%s AND A.id <= %s' % (stmt, range[1]) else: stmt = '%s A.id = %s' % (stmt, range[0]) if self.cursor.execute(stmt) == 0: return None result = self.cursor.fetchall() hdrs = [] for row in result: if header.upper() == 'SUBJECT': hdrs.append('%s %s' % (row[0], row[4])) elif header.upper() == 'FROM': hdrs.append('%s %s <%s>' % (row[0], row[2], row[3])) elif header.upper() == 'DATE': hdrs.append('%s %s' % (row[0], strutil.get_formatted_time(time.localtime(result[5])))) elif header.upper() == 'MESSAGE-ID': hdrs.append('%s <%s@%s>' % (row[0], row[0], group_name)) elif (header.upper() == 'REFERENCES') and (row[1] != 0): hdrs.append('%s <%s@%s>' % (row[0], row[1], group_name)) elif header.upper() == 'BYTES': hdrs.append('%s %s' % (row[0], len(row[6]))) elif header.upper() == 'LINES': hdrs.append('%s %s' % (row[0], len(row[6].split('\n')))) elif header.upper() == 'XREF': hdrs.append('%s %s %s:%s' % (row[0], settings.nntp_hostname, group_name, row[0])) if len(hdrs) == 0: return "" else: return "\r\n".join(hdrs)
def get_XHDR(self, group_name, header, style, range): table_name = self.get_table_name(group_name) stmt = """ SELECT A.id, parent, author, email, subject, DATE_PART('epoch', datestamp) AS datestamp, B.body FROM %s A, %s_bodies B WHERE A.approved='Y' AND A.id = B.id AND """ % (table_name, table_name) if style == 'range': stmt = '%s A.id >= %s' % (stmt, range[0]) if len(range) == 2: stmt = '%s AND A.id <= %s' % (stmt, range[1]) else: stmt = '%s A.id = %s' % (stmt, range[0]) if self.cursor.execute(stmt) == 0: return None result = self.cursor.fetchall() hdrs = [] for row in result: if header.upper() == 'SUBJECT': hdrs.append('%s %s' % (row[0], row[4].strip())) elif header.upper() == 'FROM': hdrs.append('%s %s <%s>' % (row[0], row[2].strip(), row[3].strip())) elif header.upper() == 'DATE': hdrs.append('%s %s' % (row[0], strutil.get_formatted_time(time.localtime(result[5])))) elif header.upper() == 'MESSAGE-ID': hdrs.append('%s <%s@%s>' % (row[0], row[0], group_name)) elif (header.upper() == 'REFERENCES') and (row[1] != 0): hdrs.append('%s <%s@%s>' % (row[0], row[1], group_name)) elif header.upper() == 'BYTES': hdrs.append('%s %s' % (row[0], len(row[6]))) elif header.upper() == 'LINES': hdrs.append('%s %s' % (row[0], len(row[6].split('\n')))) elif header.upper() == 'XREF': hdrs.append('%s %s %s:%s' % (row[0], settings.nntp_hostname, group_name, row[0])) if len(hdrs) == 0: return "" else: return "\r\n".join(hdrs)
def get_XOVER(self, group_name, start_id, end_id=100): if group_name != self.group_name: return None overviews = [] for index, attachment in enumerate(self.xn.attachments[int(start_id) - 1:int(end_id)]): print(attachment) msg_num = index + 1 overviews.append( "%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s" % (msg_num, attachment['filename'], 'N/A', strutil.get_formatted_time( time.localtime(attachment['attach_date'])), '<*****@*****.**>' % attachment['attachment_id'], '', 8192, 50, 'Xref: %s %s:%s' % (settings.nntp_hostname, group_name, msg_num))) return "\r\n".join(overviews)
def get_XOVER(self, group_name, start_id, end_id='ggg'): table_name = self.get_table_name(group_name) stmt = """ SELECT A.id, parent, author, email, subject, UNIX_TIMESTAMP(datestamp) AS datestamp, B.body FROM %s A, %s_bodies B WHERE A.approved='Y' AND A.id=B.id AND A.id >= %s""" % (table_name, table_name, start_id) if end_id != 'ggg': stmt = "%s AND A.id <= %s" % (stmt, end_id) self.cursor.execute(stmt) result = list(self.cursor.fetchall()) overviews = [] for row in result: if row[3] == '': author = row[2] else: author = "%s <%s>" % (row[2], row[3]) formatted_time = strutil.get_formatted_time(time.localtime(row[5])) message_id = "<%s@%s>" % (row[0], group_name) line_count = len(row[6].split('\n')) xref = 'Xref: %s %s:%s' % (settings.nntp_hostname, group_name, row[0]) if row[1] != 0: reference = "<%s@%s>" % (row[1], group_name) else: reference = "" # message_number <tab> subject <tab> author <tab> date <tab> message_id <tab> reference <tab> bytes <tab> lines <tab> xref overviews.append("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s" % (row[0], row[4], author, formatted_time, message_id, reference, len(strutil.format_body(row[6])), line_count, xref)) return "\r\n".join(overviews)
def get_ARTICLE(self, group_name, id): table_name = self.get_table_name(group_name) stmt = """ SELECT A.id, author, email, subject, DATE_PART('epoch', datestamp) AS datestamp, body, parent FROM %s A, %s_bodies B WHERE A.approved='Y' AND A.id=B.id AND A.id=%s""" % (table_name, table_name, id) num_rows = self.cursor.execute(stmt) if num_rows == 0: return None result = list(self.cursor.fetchone()) if len(result[2]) == 0: author = result[1].strip() else: author = "%s <%s>" % (result[1].strip(), result[2].strip()) formatted_time = strutil.get_formatted_time(time.localtime(result[4])) headers = [] headers.append("Path: %s" % (settings.nntp_hostname)) headers.append("From: %s" % (author)) headers.append("Newsgroups: %s" % (group_name)) headers.append("Date: %s" % (formatted_time)) headers.append("Subject: %s" % (result[3].strip())) headers.append("Message-ID: <%s@%s>" % (result[0], group_name)) headers.append("Xref: %s %s:%s" % (settings.nntp_hostname, group_name, result[0])) if result[6] != 0: headers.append("References: <%s@%s>" % (result[6], group_name)) return ("\r\n".join(headers), strutil.format_body(result[5]))
def get_XOVER(self, group_name, start_id, end_id='ggg'): table_name = self.get_table_name(group_name) stmt = """ SELECT id, parent, author, subject, UNIX_TIMESTAMP(datestamp) AS datestamp, body, line_num, bytes FROM %s WHERE id >= %s""" % (table_name, start_id) if end_id != 'ggg': stmt = "%s AND id <= %s" % (stmt, end_id) self.cursor.execute(stmt) result = list(self.cursor.fetchall()) overviews = [] for row in result: message_id = "<%s@%s>" % (row[0], group_name) xref = 'Xref: %s %s:%s' % (settings.nntp_hostname, group_name, row[0]) if row[1] != 0: reference = "<%s@%s>" % (row[1], group_name) else: reference = "" # message_number <tab> subject <tab> author <tab> date <tab> message_id <tab> reference <tab> bytes <tab> lines <tab> xref overviews.append( "%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s" % (row[0], row[3], row[2], strutil.get_formatted_time(time.localtime( row[4])), message_id, reference, row[7], row[6], xref)) return "\r\n".join(overviews)
def get_XPAT(self, group_name, header, pattern, start_id, end_id='ggg'): # XXX: need to actually check for the header values being passed as # XXX: not all header names map to column names on the tables forum_id = self.get_forum(group_name) prefix = settings.phpbb_table_prefix stmt = """ SELECT A.post_id, A.topic_id, C.username, C.user_email, CASE WHEN B.post_subject = '' THEN CONCAT('Re: ', D.topic_title) ELSE B.post_subject END, A.post_time, B.post_text, A.post_username FROM %sposts A, %sposts_text B LEFT JOIN %susers C ON A.poster_id=C.user_id LEFT JOIN %stopics D ON A.topic_id = D.topic_id WHERE A.forum_id=%s AND %s REGEXP '%s' AND A.post_id = B.post_id AND A.post_id >= %s""" % ( prefix, prefix, prefix, prefix, forum_id, header, strutil.format_wildcards(pattern), start_id) if end_id != 'ggg': stmt = "%s AND A.post_id <= %s" % (stmt, end_id) num_rows = self.cursor.execute(stmt) if num_rows == 0: return None result = list(self.cursor.fetchall()) hdrs = [] for row in result: if header.upper() == 'SUBJECT': hdrs.append('%s %s' % (row[0], row[4])) elif header.upper() == 'FROM': # XXX: totally broken with empty values for the email address hdrs.append('%s %s <%s>' % (row[0], row[2], row[3])) elif header.upper() == 'DATE': hdrs.append( '%s %s' % (row[0], strutil.get_formatted_time(time.localtime(result[5])))) elif header.upper() == 'MESSAGE-ID': hdrs.append('%s <%s@%s>' % (row[0], row[0], group_name)) elif (header.upper() == 'REFERENCES') and (row[1] != 0): hdrs.append('%s <%s@%s>' % (row[0], row[1], group_name)) elif header.upper() == 'BYTES': hdrs.append('%s %s' % (row[0], len(row[6]))) elif header.upper() == 'LINES': hdrs.append('%s %s' % (row[0], len(row[6].split('\n')))) elif header.upper() == 'XREF': hdrs.append( '%s %s %s:%s' % (row[0], settings.nntp_hostname, group_name, row[0])) if len(hdrs) == 0: return "" else: return "\r\n".join(hdrs)