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_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_XGTITLE(self, pattern=None):
stmt = """
SELECT
nntp_group_name,
description
FROM
forums
WHERE
LENGTH(nntp_group_name) > 0"""
if pattern != None:
stmt = stmt + """ AND
nntp_group_name REGEXP '%s'""" % (strutil.format_wildcards(pattern))
stmt = stmt + """
ORDER BY
nntp_group_name ASC"""
self.cursor.execute(stmt)
result = list(self.cursor.fetchall())
return "\r\n".join(["%s %s" % (k, v) for k, v in result])
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_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)