import sys,re
import MySQLdb
class Database:
conn = None
cursor = None
def __init__(self):
pass
#self.connect()
def connect(self):
try:
self.conn = MySQLdb.connect (host = "localhost",
user = "root",
passwd = "chen",
db = "usenet")
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)
def fetchone(self,sql):
self.connect()
self.cursor = self.conn.cursor ()
self.cursor.execute (sql)
one = self.cursor.fetchone ()
self.close()
return one
def fetchall(self,sql):
self.connect()
self.cursor = self.conn.cursor ()
self.cursor.execute (sql)
all = self.cursor.fetchall ()
self.close()
return all
def execute(self,sql):
self.connect()
self.cursor = self.conn.cursor ()
self.cursor.execute (sql)
self.conn.commit()
self.close()
return self.cursor.rowcount
def close(self):
self.cursor.close ()
self.conn.close()
"""
class abstract:
subject = None
mail_from = None
rcpt_to = None
data = None
group = ""
grouplist = (
("cn.comp.linux", 5, 2, "y"),
("cn.comp.freebsd", 3, 2, "y"),
("cn.comp.dos", 10, 4, "y"),
("cn.test", 5, 2, "y"),
("comp.lang.python", 5, 2, "y")
)
def welcome(self):
return 'Welcome'
def list (self):
lists = []
for name, last, first, mode in self.grouplist:
lists.append(name+ " " + str(last) + " " + str(first) +" "+ mode)
return lists
def group(self,groupname):
group_rang = ""
for name, last, first, mode in self.grouplist:
if name == groupname:
group_rang = str(last - first)+" "+ str(first)+" "+str(last)
break
return group_rang
def xover(self,first,last):
xover_tmp = []
xover_tmp.append("2 Mozilla programmer needed for children's learning program "John Fodor, PhD" <fodor@ilt-inc.com> Mon, 23 Jan 2006 12:01:09 -0500 <mailman.349.1138141614.8933.jobs@lists.mozilla.org> 10532 276 Xref: number1.nntp.dca.giganews.com mozilla.jobs:2")
xover_tmp.append("3 mozilla expert needed "Paul Sponagl" <sponagl@7val.com> Tue, 7 Mar 2006 20:00:52 +0100 <mailman.1692.1141758057.18700.jobs@lists.mozilla.org> 2913 41 Xref: number1.nntp.dca.giganews.com mozilla.jobs:10")
return xover_tmp
def head(self):
self.xover(first,last)
def xhdr(self,first,last):
xover_tmp = []
xover_tmp.append('1 HI "NNTP.HK" <admin@nntp.hk> 14 Jun 2006 14:43:05 +0800 <448faff9$1@news.nntp.hk> 1151 31 Xref: news.nntp.hk vip.cicefans:1\r\n')
return xover_tmp
def newgroups(self,data,time,gmt):
lists = []
lists.append("cn.test.os")
lists.append("cn.test.qa")
return lists
(abstract)
"""
class Messages:
banner = '200 \"Welcome to Netkiler News server\"\r\n'
db= None
def __init__(self):
self.db = Database()
#print self.db.fetchone(sql)
def list (self):
sql = """SELECT id,`group`,(select max(Number) from article) as last, (select min(Number) from article) as first, p FROM list"""
#print sql
result = self.db.fetchall(sql)
group = []
for id, name, last, first, mode, in result:
group.append(name+ " " + str(last) + " " + str(first) +" "+ mode)
return group
def listgroup(self,group):
sql = "select Number from article where Newsgroups = '%s'" % (group)
#print sql
result = self.db.fetchall(sql)
number = []
for num in result:
number.append(str(num[0]))
return number
def group(self,newsgroup):
sql = "SELECT `group`,(select count(Number) from article) as number, (select max(Number) from article) as last, (select min(Number) from article) as first FROM list where `group` = '%s' limit 1" % (newsgroup)
#print sql
result=self.db.fetchone(sql)
name, number, last, first = result
return (str(number),str(first),str(last),name)
def xover(self,group,first,last):
sql = "select * from article where Newsgroups='%s' and Number BETWEEN %s AND %s" % (group,first,last)
#print sql
result=self.db.fetchall(sql)
xovers = []
for Number, MessageID, Newsgroups, Body, Date in result:
parse = self.parseArticle('head',Body)
if 'Xref' in set(parse):
xref = '\t'+parse['Xref']
else:
xref = ''
if 'Lines' in set(parse):
lines = '\t'+parse['Lines']
else:
lines = ''
xovers.append(str(Number)+'\t'+parse['Subject']+'\t'+parse['From']+'\t'+parse['Date']+'\t<3dc0af03@netkiller.hikz.com>\t'+str(len(Body))+'\t'+lines+xref)
return xovers
def post(self, Newsgroups, MessageID, text):
sql = "insert into article( MessageID, Newsgroups, body, `date`) values('%s','%s','%s',now())" % (MessageID,Newsgroups,text)
#print sql
rowcount = self.db.execute(sql)
return rowcount
def article(self,group,id):
sql = "select body from article where Newsgroups='%s' and Number='%s'" % (group,id)
#print sql
result=self.db.fetchone(sql)
if result:
result = result[0]
return result
"""
def head(self,id):
def body(self,id):
"""
def parseArticle(self,position,data):
#[a-zA-z0-9]+@[a-zA-z0-9]+.[a-zA-z]+
head_matchs = (
('From', r'From: (.+ <.+>)\r\n'),
('Newsgroups', r'Newsgroups: (.+)\r\n'),
('Subject', r'Subject: (.+)\r\n'),
('Date', r'Date: (.+)\r\n'),
('Lines', r'Lines: (.+)\r\n'),
('Message-ID', r'Message-ID: (.+)\r\n'),
('MIME-Version', r'MIME-Version: (.+)\r\n'),
('User-Agent', r'User-Agent: (.+)\r\n'),
('Content-Type', r'Content-Type: (.+)\r\n'),
('Content-Transfer-Encoding', r'Content-Transfer-Encoding: (.+)\r\n'),
('Xref', r'Xref: (.+)\r\n')
)
"""
('X-MSMail-Priority', r'X-MSMail-Priority: (.+)\r\n'),
('X-Newsreader', r'X-Newsreader: (.+)\r\n'),
('X-MimeOLE', r'X-MimeOLE: (.+)\r\n'),
('X-RFC2646', r'X-RFC2646: (.+)\r\n'),
"""
body_matchs = (
('Body', r'\r\n\r\n(.+)\r\n')
)
article_matchs = (
('From', r'From: (.+ <.+>)\r\n'),
('Newsgroups', r'Newsgroups: (.+)\r\n'),
('Subject', r'Subject: (.+)\r\n'),
('Date', r'Date: (.+)\r\n'),
('Lines', r'Lines: (.+)\r\n'),
('X-Priority', r'X-Priority: (.+)\r\n'),
('X-MSMail-Priority', r'X-MSMail-Priority: (.+)\r\n'),
('X-Newsreader', r'X-Newsreader: (.+)\r\n'),
('X-MimeOLE', r'X-MimeOLE: (.+)\r\n'),
('X-RFC2646', r'X-RFC2646: (.+)\r\n'),
('MIME-Version', r'MIME-Version: (.+)\r\n'),
('User-Agent', r'User-Agent: (.+)\r\n'),
('Content-Type', r'Content-Type: (.+)\r\n'),
('Content-Transfer-Encoding', r'Content-Transfer-Encoding: (.+)\r\n'),
('Xref', r'Xref: (.+)\r\n'),
('Body', r'\r\n\r\n(.+)\r\n')
)
parse = {}
if position == 'head':
matchs = head_matchs
elif position == 'body':
matchs = body_matchs
else:
matchs = article_matchs
for head, value in matchs:
digs = re.compile(value,re.IGNORECASE)
for test in digs.findall(data):
parse[head] = test
return parse
def main():
test = Messages()
#print msg.banner
print test.list()
print '---------------------'
print test.listgroup('cn.test')
print '---------------------'
print test.group('cn.test')
print '---------------------'
print test.xover('cn.test','50', '100')
print '---------------------'
#nntp.post('test')
#delete from article;
text = test.article('cn.test','70')
#print repr(text)
print text
print '---------------------'
#from email.MIMEText import MIMEText
#print MIMEText(text)['Form']
#import email
#msg = email.message_from_string(myString).__dict__
#print msg['_headers']
#print msg['_payload']
#print test.parseArticle(text)
sql = 'select * from list'
db = Database()
print db.fetchone(sql)
print db.fetchall(sql)
if __name__ == '__main__':
main()