from pysqlite2 import dbapi2 as sqlite
import Conf
import os
import errno

import patchtracker.SourceArchive as SourceArchive

def srcpkg_factory(cursor, row):
  d = {}
  for idx, col in enumerate(cursor.description):
    d[col[0]] = row[idx]

  info = {}
  info['Files'] = []
  if d['diffgz_name']:
    diffgz = {'name':d['diffgz_name'], 'size':d['diffgz_size'], 
              'md5sum':d['diffgz_md5sum'] }
    info['Files'].append(diffgz)

  colmap = {'name':'Package','version':'Version','format':'Format',
            'loc':'Directory'}
            
  for col,field in colmap.iteritems():
    info[field] = d[col]
  return SourceArchive.SourcePackage(info)


class PatchTrackerDB:
  def __init__(self, dbname=Conf.database):
    self.db = sqlite.connect(dbname)
    cursor = self.db.cursor()
    cursor.execute("SELECT * FROM sqlite_master WHERE name='packages'")
    if not cursor.fetchone():
      print "repopulating empty database..."
      nextcmd = ""
      for l in file(Conf.sqlschema).readlines():
        if len(l.strip()):
          nextcmd += " " + l.strip()
        else:
          print "exec: %s"%(nextcmd)
          cursor.execute(nextcmd)
          nextcmd = ""

  def setFactory(self, factory):
    self.db.row_factory = factory

  def findSourcePackage(self, srcpkg):
    q = "SELECT * FROM packages WHERE name=? AND version=?"
    cursor = self.db.cursor()
    cursor.execute(q, (srcpkg.name, srcpkg.version))
    s = cursor.fetchone()
    return s

  def saveSourcePackage(self, srcpkg):
    cursor = self.db.cursor()
    s = self.findSourcePackage(srcpkg)

    queryargs = (srcpkg.name,srcpkg.format,srcpkg.loc,
                 srcpkg.version,srcpkg.diffgz_name,srcpkg.diffgz_size,
                 srcpkg.diffgz_md5sum)
    if not s:
      #print "creating new record for",srcpkg
      q = "INSERT INTO packages (id,name,format,loc,version,\
                                 diffgz_name,diffgz_size,diffgz_md5sum)\
             VALUES ((SELECT MAX(id)+1 FROM packages),?,?,?,?,?,?,?)"
    else:
      #print "updating record for",srcpkg
      q = "UPDATE packages SET name=?,format=?,loc=?,version=?,\
                               diffgz_name=?,diffgz_size=?,diffgz_md5sum=?\
             WHERE name=? AND version=?"
      queryargs += (srcpkg.name, srcpkg.version)
    cursor.execute(q, queryargs)

  def findSuite(self, suite):
    q = "SELECT * FROM suites WHERE name = ?"
    cursor = self.db.cursor()
    cursor.execute(q, (suite,))
    return cursor.fetchone()

  def saveSuite(self, suite):
    s = self.findSuite(suite)
    if not s:
      q = "INSERT INTO suites (id,name) \
             VALUES ((SELECT MAX(id)+1 FROM suites),?)"
      cursor = self.db.cursor()
      cursor.execute(q, (suite,))

  def findComponent(self, component):
    q = "SELECT * FROM components WHERE name = ?"
    cursor = self.db.cursor()
    cursor.execute(q, (component,))
    return cursor.fetchone()

  def saveComponent(self, component):
    c = self.findComponent(component)
    if not c:
      q = "INSERT INTO components (id,name) \
             VALUES ((SELECT MAX(id)+1 FROM components),?)"
      cursor = self.db.cursor()
      cursor.execute(q, (component,))

  def findLetterToc(self, letter):
    oldfactory = self.db.row_factory
    dcursor = self.db.cursor()
    dq = "SELECT id,name FROM suites" 
    dcursor.execute(dq)
    suites = {}
    for id,suite in dcursor.fetchall():
      suites[id] = suite

    self.db.row_factory = srcpkg_factory
    cursor = self.db.cursor()
    toc = SourceArchive.SourcePackageIndex()
    for s,v in suites.iteritems():
      q = "SELECT * FROM packages AS p,package_rel_map AS m \
             WHERE p.name like ? AND p.id = m.package_id and m.suite_id = ?"
      cursor.execute(q, (letter+"%", s))
      # use srcpkg_factory to fetch sourcepackages, once per suite
      for srcpkg in cursor.fetchall():
        toc.ins(srcpkg, v)
    self.db.row_factory = oldfactory
    return toc.getletter(letter)

  def findMasterIndex(self):
    cursor = self.db.cursor()
    toc = SourceArchive.MasterIndex()
    q = "SELECT name FROM packages WHERE id > 0"
    cursor.execute(q)
    for name in cursor.fetchall():
      # XXX find a better way to do this..., probably just store metainfo
      # XXX in the db and update it via triggers
      toc.add(name[0])
    return toc
     
  def findSourcePackageRelation(self, srcpkg, suite):
    q = "SELECT * FROM packages AS p,suites AS s,package_rel_map AS m \
           WHERE p.name=? AND s.name=? AND m.package_id=p.id \
                 AND m.suite_id=s.id"
    cursor = self.db.cursor()
    cursor.execute(q, (srcpkg.name, suite))
    return cursor.fetchone()

  def relateSourcePackage(self, srcpkg, suite, component):
    s = self.findSourcePackageRelation(srcpkg, suite)
    if not s:
      s = self.findSourcePackage(srcpkg)
      q = "INSERT INTO package_rel_map (package_id,suite_id,component_id) \
             VALUES (?, \
                    (SELECT id FROM suites WHERE name=?), \
                    (SELECT id FROM components WHERE name=?))"
    else:
      q = "UPDATE package_rel_map SET package_id=? \
               WHERE suite_id=(SELECT id FROM suites WHERE name=?) \
                 AND component_id=(SELECT id FROM components WHERE name=?)"
    cursor = self.db.cursor()
    cursor.execute(q, (s[0], suite, component))

  def finalize(self):
    self.db.commit()

  def __del__(self):
    self.finalize()
    

if __name__ == "__main__":
  print "patch tracker db testing"
  d = PatchTrackerDB(dbname="foo.db")
  sp = SourceArchive.SourcePackage({'Package':'foo','Format':'blah','Directory':'/foo','Version':'1.2.3.4','Files':[]})
  d.saveSourcePackage(sp)
  d.finalize()