#!/usr/bin/python
#Written by Adam Robinson (adarobin@umflint.edu)
#This program is free software: you can redistribute it and/or modify
#it under the terms of the GNU General Public License as published by
#the Free Software Foundation, either version 2 of the License, or
#(at your option) any later version.
#
#This program is distributed in the hope that it will be useful,
#but WITHOUT ANY WARRANTY; without even the implied warranty of
#MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#GNU General Public License for more details.
#
#You should have received a copy of the GNU General Public License
#along with this program.  If not, see <http://www.gnu.org/licenses/>.

import re, collections, csv, sqlite3, sys, requests
from dateutil import parser
from lxml import etree
sys.path.append("/usr/share/rhn/")
from up2date_client import rpcServer, up2dateAuth

#make sure this machine is on ULN
sid = up2dateAuth.getSystemId()
if sid is None:
   print "This machine is not registered with ULN, aborting"
   sys.exit(-1)

#connect to ULN
server = rpcServer.getServer()

#create a database in memory to store all of the information pulled and parsed
db = sqlite3.connect(":memory:")
cursor = db.cursor()
cursor.execute("""CREATE TABLE errataList (ID text, ULNChannel text, Name text, Summary text, Version text, Release text, Type text, Description text)""")
cursor.execute("""CREATE TABLE errataBug (ID text, VendorTag text, BugID text)""")
cursor.execute("""CREATE TABLE errataSec (ID text, CVE text)""")
cursor.execute("""CREATE TABLE errataDetail (ID text, Date text, Severity text)""")
cursor.execute("""CREATE TABLE channelList (Name text, Label text, Arch text)""") 
#db.commit()

#store every channel the server is registered to in ULN
for channel in server.up2date.listChannels(sid):
   cursor.execute("""INSERT INTO channelList VALUES (?,?,?)""", (channel['NAME'], channel['LABEL'], channel['ARCH']))
   #db.commit()

#fetch every errata from every channel and store it in the database
cursor.execute("SELECT Label FROM channelList")
for channel in cursor.fetchall():
   for errata in server.errata.getErrataByChannel(channel[0]):
      cursor.execute("""INSERT INTO errataList VALUES (?,?,?,?,?,?,?,?)""", (errata['id'].strip(), channel[0].lower(), errata['name'].strip(), errata['summary'].strip(), errata['version'].strip(), errata['release'].strip(), errata['type'].strip(), errata['description'].strip()))
      #db.commit()

#iterate through every unique selection of errata and description
#to parse out other information we want
cursor.execute("SELECT DISTINCT ID,Description FROM errataList")
for errata in cursor.fetchall():
   #try to parse out bug numbers (not 100% foolproof)
   VendorTags=["rhbz","orabug","orabz"]
   for VendorTag in VendorTags:
      bugID=re.findall('(?i)'+VendorTag+'[#  s]*[\d #,\n]+', errata[1])
      if bugID != []:
         for bug in bugID:
            bugnumbers=re.findall("\d+", bug)
            for bugnumber in bugnumbers:
               if bugnumber != "":
                  cursor.execute("""INSERT INTO errataBug VALUES(?,?,?)""", (errata[0], VendorTag, bugnumber))
                  #db.commit
  
   #parse out all CVE numbers contained in the errata 
   cveID=re.findall("CVE-\d\d\d\d-\d\d\d\d", errata[1])
   if cveID != []:
      for cve in cveID:
         cursor.execute("""INSERT INTO errataSec VALUES(?,?)""", (errata[0], cve))
         #db.commit

   #my crazy hacks to get the errata date and severity info
   #Oracle has this info but doesn't expose it through the api for some reason
   #have an Enhancement SR to get this info added to the api
   #I used to dump the mailing list archives and parse those, but once this
   #came out it seemed better
   errataRequest = requests.get("http://linux.oracle.com/errata/" + errata[0] + ".html")
   errataSeverity = re.findall(">[A-Za-z0-9]+<", re.findall("Severity.*", errataRequest.text)[0])
   errataSeverity = re.sub(">|<","",errataSeverity[0])
   errataDate = re.findall("\d\d\d\d-\d\d-\d\d",re.findall("Date.*", errataRequest.text)[0])[0]
   cursor.execute("""INSERT INTO errataDetail VALUES(?,?,?)""", (errata[0], errataDate, errataSeverity))
   #db.commit

#create an xml structure to store the errata
root = etree.Element("erratas")

#create a unique errata for each channel and ID combo (different channels may have different descriptions of the same errata)
cursor.execute("SELECT DISTINCT ID, ULNChannel FROM errataList")
for errata in cursor.fetchall():
   child = etree.SubElement(root, "errata")

   #read in the synopsis and prepend the severity to it if there is one 
   cursor.execute("SELECT Summary from errataList WHERE ID=?",[errata[0]])
   synopsis = etree.SubElement(child, "synopsis")
   result = cursor.fetchone()[0]
   cursor.execute("SELECT Severity from errataDetail WHERE ID=?", [errata[0]])
   severity=cursor.fetchone()[0]
   if severity is not None:
      if severity == "NA":
         synopsis.text = result
      else:
         synopsis.text = severity + ": " + result
   else:
      synopsis.text = result

   topic = etree.SubElement(child, "topic")
   topic.text  = result

   #name the advisory after the id and the channel that contains it
   advisory_name = etree.SubElement(child, "advisory_name")
   advisory_name.text = errata[0] + "-"+ errata[1]

   advisory_release = etree.SubElement(child, "advisory_release")
   advisory_release.text = "1"

   advisory_type = etree.SubElement(child, "advisory_type")
   if errata[0].find("SA") >= 0:
      advisory_type.text = "Security Advisory"
   if errata[0].find("BA") >= 0:
      advisory_type.text = "Bug Fix Advisory"
   if errata[0].find("EA") >= 0:
      advisory_type.text = "Product Enhancement Advisory"
   
   product = etree.SubElement(child, "product")
   product.text = errata[1]

   errataFrom = etree.SubElement(child, "errataFrom")
   errataFrom.text = "Oracle Inc."

   description = etree.SubElement(child, "description")
   description.text = ""
   cursor.execute("SELECT DISTINCT Description from errataList WHERE ID=? AND ULNChannel=?", (errata[0],errata[1]))
   result = cursor.fetchone()
   if result is not None:
      description.text = result[0]

   references = etree.SubElement(child, "references") 
   references.text = "http://linux.oracle.com/errata/" + errata[0] + ".html"

   notes = etree.SubElement(child, "notes")
   
   solution = etree.SubElement(child, "solution")
   solution.text = "Apply the following package updates:\n"
   cursor.execute("SELECT DISTINCT Name, Version, Release from errataList WHERE ID=?", [errata[0]])
   for row in cursor.fetchall():
      solution.text = solution.text + row[0] + "-" + row[1] + "-" + row[2] + "\n"

   bugs = etree.SubElement(child, "bugs")
   cursor.execute("SELECT DISTINCT VendorTag, BugID FROM errataBug WHERE ID=?", [errata[0]])
   for row in cursor.fetchall():
      bug = etree.SubElement(bugs, "bug")
      bugid = etree.SubElement(bug, "id")
      bugid.text = row[1]
      bugsummary = etree.SubElement(bug, "summary")
      bugsummary.text = row[0] + row[1]
      bugurl = etree.SubElement(bug, "url")
      if row[0] == "rhbz":
         bugurl.text = "https://bugzilla.redhat.com/show_bug.cgi?id=" + row[1]
      else:
         bugurl.text = ""

   cves = etree.SubElement(child, "CVEs")
   cursor.execute("SELECT DISTINCT CVE FROM errataSec WHERE ID=?", [errata[0]])
   for row in cursor.fetchall():
      cve = etree.SubElement(cves, "cve")
      cve.text = row[0]

   packages = etree.SubElement(child, "packages")
   cursor.execute("SELECT Name, Version, Release from errataList WHERE ID=? AND ULNChannel=?",(errata[0], errata[1]))
   for row in cursor.fetchall():
      package = etree.SubElement(packages, "package")
      name = etree.SubElement(package, "name")
      name.text = row[0]
      version = etree.SubElement(package, "version")
      version.text = row[1]
      release = etree.SubElement(package, "release")
      release.text = row[2]

   date = etree.SubElement(child, "date")
   cursor.execute("SELECT Date from errataDetail WHERE ID=?",[errata[0]])
   result = cursor.fetchone()
   if result is not None:
      date.text = result[0]
   else:
      date.text = ""

#write the xml to a file (obviously change the path to whatever you want)
file = open("/var/www/html/OracleLinux/uln-errata.xml","w")
file.write(etree.tostring(root, pretty_print=True))
file.close()

#write the xml to the screen for testing purposes
#str = etree.tostring(root, pretty_print=True)
#print str