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_XOVER(self, group_name, start_id, end_id='ggg'):
group = self._groupname2group(group_name)
start_id = int(start_id)
if end_id == 'ggg':
end_id = self.get_group_article_count(group)
else:
end_id = int(end_id)
overviews = []
for id in range(start_id, end_id + 1):
msg = self.get_message(group_name, id)
if msg is None:
break
author = msg.get('from')
formatted_time = msg.get('date')
message_id = self.get_message_id(id, group_name)
line_count = len(msg.fp.read().split('\n'))
xref = 'Xref: %s %s:%d' % (settings.nntp_hostname, group_name, id)
if msg.get('references') is not None:
reference = msg.get('references')
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" % \
(id, msg.get('subject'), author,
formatted_time, message_id, reference,
len(strutil.format_body(msg.fp.read())),
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
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_XOVER(self, group_name, start_id, end_id='ggg'):
mbox = self.get_mailbox(group_name.replace('papercut.mbox.', ''))
# don't count the first message
mbox.next()
i = 1
overviews = []
while 1:
msg = mbox.next()
if msg is None:
break
author = msg.get('from')
formatted_time = msg.get('date')
message_id = msg.get('message-id')
line_count = len(msg.fp.read().split('\n'))
xref = 'Xref: %s %s:%s' % (settings.nntp_hostname, group_name, i)
if msg.get('in-reply-to') is not None:
reference = msg.get('in-reply-to')
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" %
(i, msg.get('subject'), author, formatted_time, message_id,
reference, len(strutil.format_body(
msg.fp.read())), line_count, xref))
i = i + 1
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_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_BODY(self, group_name, id):
table_name = self.get_table_name(group_name)
stmt = """
SELECT
body
FROM
%s
WHERE
id=%s""" % (table_name, id)
num_rows = self.cursor.execute(stmt)
if num_rows == 0:
return None
else:
return strutil.format_body(self.cursor.fetchone()[0])
def get_BODY(self, group_name, id):
forum_id = self.get_forum(group_name)
prefix = settings.phpbb_table_prefix
stmt = """
SELECT
A.post_text
FROM
%sposts A
WHERE
(A.forum_id=%s OR A.forum_id=0) AND
A.post_id=%s""" % (prefix, forum_id, id)
num_rows = self.query(stmt)
if num_rows == 0:
return None
else:
return strutil.format_body(self.cursor.fetchone()[0])
def get_BODY(self, group_name, id):
forum_id = self.get_forum(group_name)
prefix = settings.phpbb_table_prefix
stmt = """
SELECT
A.post_text
FROM
%sposts A
WHERE
(A.forum_id=%s OR A.forum_id=0) AND
A.post_id=%s""" % (prefix, forum_id, id)
num_rows = self.query(stmt)
if num_rows == 0:
return None
else:
return strutil.format_body(self.cursor.fetchone()[0])
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_BODY(self, group_name, id):
table_name = self.get_table_name(group_name)
stmt = """
SELECT
B.body
FROM
%s A,
%s_bodies B
WHERE
A.id=B.id AND
A.approved='Y' AND
B.id=%s""" % (table_name, table_name, id)
num_rows = self.cursor.execute(stmt)
if num_rows == 0:
return None
else:
return strutil.format_body(self.cursor.fetchone()[0])
def get_BODY(self, group_name, id):
table_name = self.get_table_name(group_name)
stmt = """
SELECT
B.body
FROM
%s A,
%s_bodies B
WHERE
A.id=B.id AND
A.approved='Y' AND
B.id=%s""" % (table_name, table_name, id)
num_rows = self.cursor.execute(stmt)
if num_rows == 0:
return None
else:
return strutil.format_body(self.cursor.fetchone()[0])
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,
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_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_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'):
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'):
mbox = self.get_mailbox(group_name.replace('papercut.mbox.', ''))
# don't count the first message
mbox.next()
i = 1
overviews = []
while 1:
msg = mbox.next()
if msg is None:
break
author = msg.get('from')
formatted_time = msg.get('date')
message_id = msg.get('message-id')
line_count = len(msg.fp.read().split('\n'))
xref = 'Xref: %s %s:%s' % (settings.nntp_hostname, group_name, i)
if msg.get('in-reply-to') is not None:
reference = msg.get('in-reply-to')
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" % (i, msg.get('subject'), author, formatted_time, message_id, reference, len(strutil.format_body(msg.fp.read())), line_count, xref))
i = i + 1
return "\r\n".join(overviews)
def get_BODY(self, group_name, id):
msg = self.get_message(group_name, id)
if msg is None:
return None
else:
return strutil.format_body(msg.fp.read())
def get_BODY(self, group_name, id):
msg = self.get_message(group_name, id)
if msg is None:
return None
else:
return strutil.format_body(msg.fp.read())
