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_HEAD(self, group_name, id):
table_name = self.get_table_name(group_name)
stmt = """
SELECT
id,
author,
email,
subject,
DATE_PART('epoch', 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].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[5] != 0:
headers.append("References: <%s@%s>" % (result[5], group_name))
return "\r\n".join(headers)
def get_HEAD(self, group_name, id):
table_name = self.get_table_name(group_name)
stmt = """
SELECT
id,
author,
subject,
UNIX_TIMESTAMP(datestamp) AS datestamp,
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[4] != 0:
headers.append("References: <%s@%s>" % (result[4], group_name))
return "\r\n".join(headers)
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):
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_XOVER(self, group_name, start_id, end_id="ggg"):
self.update_newsgroup_meta()
stmt = self.get_article_sql()
stmt += " AND article_number >= %s" % (start_id,)
if end_id != "ggg":
stmt += " AND article_number <= %s" % (end_id,)
self.cursor.execute(stmt)
result = list(self.cursor.fetchall())
overviews = []
for row in result:
if html2text:
body = html2text.html2text(row[6].encode("utf-8")).encode("utf-8")
else:
body = strutil.format_body(row[6].encode("utf-8"))
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 = row[9]
line_count = body.count("\n")
xref = "Xref: %s %s:%s" % (settings.nntp_hostname, group_name, row[0])
parent = []
if row[7] != 0:
parent.append(self.get_message_id(row[7], group_name, "posts"))
if row[8] != 0:
parent.append(self.get_message_id(row[8], group_name, "comments"))
reference = ", ".join(parent)
# 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(body), line_count, xref)
)
return "\r\n".join(overviews)
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_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 A.post_subject = '' THEN CONCAT('Re: ', E.topic_title) ELSE A.post_subject END,
A.post_time,
A.topic_id,
A.post_username,
MIN(D.post_id)
FROM
%sposts A
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 OR A.forum_id=0) AND
A.post_id=%s
GROUP BY
D.topic_id""" % (prefix, prefix, prefix, prefix, forum_id, id)
num_rows = self.query(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])
author = result[1]
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]))
# because topics are all related in forums we can only reference the first topic
if result[7] != result[0]:
headers.append("References: <%s@%s>" % (result[7], group_name))
headers.append("In-Reply-To: <%s@%s>" % (result[7], group_name))
return "\r\n".join(headers)
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,
DATE_PART('epoch', datestamp) AS datestamp,
B.body
FROM
%s A,
%s_bodies B
WHERE
A.approved='Y' AND
%s LIKE '%s' AND
A.id = B.id AND
A.id >= %s""" % (table_name, table_name, header,
strutil.format_wildcards_sql(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].strip()))
elif header.upper() == 'FROM':
# XXX: totally broken with empty values for the email address
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='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_XOVER(self, group_name, start_id, end_id='ggg'):
forum_id = self.get_forum(group_name)
prefix = settings.phpbb_table_prefix
#print "xover startid=%s endid=%s\r\n" % (start_id, end_id)
stmt = """
SELECT
A.post_id,
A.topic_id,
C.username,
C.user_email,
CASE WHEN A.post_subject = '' THEN CONCAT('Re: ', D.topic_title) ELSE A.post_subject END,
A.post_time,
A.post_text,
A.post_username,
E.MinPostID
FROM
%sposts A
LEFT JOIN
(select topic_id, MIN(post_id) as MinPostID from %sposts group by topic_id) E
ON
E.topic_id=A.topic_id
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 OR A.forum_id=0) 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.query(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])
author = row[2]
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[8] != row[0]:
reference = "<%s@%s>" % (row[8], 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_XPAT(self, group_name, header, pattern, start_id, end_id="ggg"):
return None # TODO: really broken
# 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,
post_parent,
display_name,
user_email,
post_title,
UNIX_TIMESTAMP(post_date_gmt) AS datestamp,
post_content
FROM
%s A,
wp_users
WHERE
A.post_type='post' AND A.post_status='publish' AND
%s REGEXP '%s' AND
post_author = wp_users.ID AND
A.ID >= %s""" % (
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(row[0] + " " + self.get_message_id(row[0], group_name))
elif (header.upper() == "REFERENCES") and (row[1] != 0):
hdrs.append(row[0] + " " + self.message_id(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"):
# 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 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_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_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_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,
DATE_PART('epoch', datestamp) AS datestamp,
B.body
FROM
%s A,
%s_bodies B
WHERE
A.approved='Y' AND
%s LIKE '%s' AND
A.id = B.id AND
A.id >= %s""" % (table_name, table_name, header, strutil.format_wildcards_sql(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].strip()))
elif header.upper() == 'FROM':
# XXX: totally broken with empty values for the email address
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_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_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_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_ARTICLE(self, group_name, id, headers_only=False, body_only=False):
stmt = self.get_article_sql()
if str(id).count("<") > 0 or str(id).count("@") > 0:
id = self.quote_string(id)
stmt += " AND message_id='%s'" % (id,)
else:
id = int(id)
stmt += " AND article_number=%s" % (id,)
num_rows = self.cursor.execute(stmt)
if num_rows == 0:
return None
result = list(self.cursor.fetchone())
if not body_only:
if len(result[3]) == 0:
author = result[2]
else:
author = "%s <%s>" % (result[2], result[3])
formatted_time = strutil.get_formatted_time(time.localtime(result[5]))
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[4]))
headers.append("Message-ID: %s" % (result[9]))
headers.append("Xref: %s %s:%s" % (settings.nntp_hostname, group_name, result[0]))
parent = []
if result[7] != 0:
parent.append(self.get_message_id(result[7], group_name, "posts"))
if result[8] != 0:
parent.append(self.get_message_id(result[8], group_name, "comments"))
if len(parent) > 0:
headers.append("References: " + ", ".join(parent))
headers.append("In-Reply-To: " + parent.pop())
headers.append("Content-Type: text/plain; charset=utf-8")
if headers_only:
return "\r\n".join(headers)
if html2text:
body = html2text.html2text(
result[6].encode("utf-8").replace("\r\n", "\n").replace("\r", "\n").replace("\n\n", "</p><p>")
).encode("utf-8")
else:
body = strutil.format_body(result[6].encode("utf-8"))
if body_only:
return body
return ("\r\n".join(headers).encode("utf-8"), body)
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,
DATE_PART('epoch', 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].strip()
else:
author = "%s <%s>" % (row[2].strip(), row[3].strip())
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].strip(), 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,
UNIX_TIMESTAMP(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]
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[6] != 0:
headers.append("References: <%s@%s>" % (result[6], group_name))
return ("\r\n".join(headers), strutil.format_body(result[5]))
def get_XHDR(self, group_name, header, style, range):
self.update_newsgroup_meta()
stmt = self.get_article_sql()
if style == "range":
stmt += " AND article_number >= %s" % (range[0],)
if len(range) == 2:
stmt += " AND article_number <= %s" % (range[1])
else:
stmt += " AND article_number = %s" % (range[0],)
if self.cursor.execute(stmt) == 0:
return None
result = self.cursor.fetchall()
hdrs = []
for row in result:
parent = []
if row[7] != 0:
parent.append(self.get_message_id(row[7], group_name, "posts"))
if row[8] != 0:
parent.append(self.get_message_id(row[8], group_name, "comments"))
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(row[0] + " " + row[9])
elif (header.upper() == "REFERENCES") and len(parent) > 0:
hdrs.append("%s %s" % (row[0], ", ".join(parent)))
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_ARTICLE(self, group_name, id):
table_name = self.get_table_name(group_name)
stmt = """
SELECT
A.id,
author,
email,
subject,
UNIX_TIMESTAMP(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]
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[6] != 0:
headers.append("References: <%s@%s>" % (result[6], group_name))
return ("\r\n".join(headers), strutil.format_body(result[5]))
def get_HEAD(self, group_name, id):
table_name = self.get_table_name(group_name)
stmt = """
SELECT
id,
author,
email,
subject,
DATE_PART('epoch', 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].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[5] != 0:
headers.append("References: <%s@%s>" % (result[5], group_name))
return "\r\n".join(headers)
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'):
# 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
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.forum_id=%s AND
%s REGEXP '%s' AND
A.post_id = B.post_id AND
A.post_id >= %s""" % (
prefix, prefix, nuke_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_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_ARTICLE(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 A.post_subject = '' THEN CONCAT('Re: ', E.topic_title) ELSE A.post_subject END,
A.post_time,
A.post_text,
A.topic_id,
A.post_username,
MIN(D.post_id),
A.poster_ip
FROM
%sposts A
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 OR A.forum_id=0) AND
A.post_id=%s
GROUP BY
D.topic_id""" % (prefix, prefix, prefix, prefix, forum_id,
id)
num_rows = self.query(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])
author = result[1]
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[9].strip() != '':
headers.append("NNTP-Posting-Host: %s" % (result[9].strip()))
if result[8] != result[0]:
headers.append("References: <%s@%s>" % (result[8], group_name))
headers.append("In-Reply-To: <%s@%s>" % (result[8], group_name))
return ("\r\n".join(headers), strutil.format_body(result[5]))
