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)
