#!/usr/bin/env python

# Note, this script needs database connectivity.  You need a table
# named patches and table as follows:
#
#  create table app(id int not null auto_increment primary key, \
#  	name varchar(255) not null);

me = "user@host.com"
mysmtp = "mail.myisp.com"

import MySQLdb, email, smtplib
from ftplib import FTP
from email.Message import Message
from email.MIMEBase import MIMEBase
from email.MIMEText import MIMEText
from email.MIMEMessage import MIMEMessage

# Setup mysql connection
db = MySQLdb.connect(user="myuser", passwd="mypasswd", db="slackpatches")
c = db.cursor()

filelist = []

# Download the file
ftp = FTP('ftp.wwc.edu')
ftp.login(user = "anonymous", passwd = me)
ftp.cwd('/pub/mirrors/ftp.slackware.com/pub/slackware/slackware-9.0/patches/packages')
ftp.retrlines('LIST', filelist.append)
ftp.quit()

filenames = []
newfiles = []
# Get the filenames
for file in filelist:
	fields = file.split()
	filenames.append(fields[8])

for file in filenames:
	sql = "select name from app where name=\"%s\"" % file
	c.execute(sql)
	result = c.fetchall()
	
	if len(result) == 0:
		newfiles.append(file)
		# Insert it
		sql = "insert into app(name) values(\"%s\")" % file
		c.execute(sql)


if len(newfiles) > 0:
	message = "From: <patcher@myhost.com>\nTo:", me, "\nSubject: New Slackware Patches\n\nThese New Files showed up:\n"

	for f in newfiles:
		message = message + f + "\n"

	message = message, "\n\nThanks, \n\n\tSlackPatch.py"
	smtpserver = smtplib.SMTP(mysmtp)
	smtpserver.sendmail(me, me, message[0])
	smtpserver.close()