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

import patchtracker.SourceArchive as SourceArchive

def srcpkg_collection_factory(cursor, row):
  d = {}
  rest = {}
  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 = {'packagename':'Package','version':'Version','format':'Format',
            'loc':'Directory','emails':'Maintainer'}
            
  for col,field in colmap.iteritems():
    info[field] = d[col]
  return (SourceArchive.SourcePackage(info), d)


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:
          cursor.execute(nextcmd)
          nextcmd = ""

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

  def saveSourcePackage(self, srcpkg):
    cursor = self.db.cursor()
    #print "creating new record for",srcpkg
    q = "INSERT OR IGNORE INTO packages \
           (name,format,loc,version,diffgz_name,diffgz_size,diffgz_md5sum) \
           VALUES (?,?,?,?,?,?,?)"
    cursor.execute(q, (srcpkg.name,srcpkg.format,srcpkg.loc,
                       srcpkg.version,srcpkg.diffgz_name,srcpkg.diffgz_size,
                       srcpkg.diffgz_md5sum,))

  def saveSuite(self, suite):
    q = "INSERT INTO suites (name) VALUES (?)"
    cursor = self.db.cursor()
    cursor.execute(q, (suite,))

  def saveComponent(self, component):
    q = "INSERT INTO components (name) VALUES (?)"
    cursor = self.db.cursor()
    cursor.execute(q, (component,))

  def saveMaintainers(self, maintainers):
    q = "INSERT INTO maintainers (email) VALUES (?)"
    cursor = self.db.cursor()
    cursor.executemany(q, [(m,) for m in maintainers])

  def findCollection(self, package=None, version=None, email=None):
    oldfactory = self.db.row_factory
    self.db.row_factory = srcpkg_collection_factory
    cursor = self.db.cursor()
    toc = SourceArchive.SourcePackageIndex()
    # zomgroflcoptersql!!1!one!
    q = "SELECT p.name as packagename, version, format, loc, diffgz_name, \
                diffgz_size, diffgz_md5sum, s.name as suitename, \
                (SELECT GROUP_CONCAT(email) FROM maintainers AS m, \
                        maint_rel_map AS mm \
                   WHERE mm.package_id = p.id \
                     AND mm.maintainer_id = m.id) AS emails \
           FROM packages AS p, package_rel_map AS pm, suites AS s \
             WHERE pm.package_id = p.id AND pm.suite_id = s.id"

    qargs = ()
    if package:
      q += " AND packagename LIKE ?"
      qargs += (package,)
    if version:
      q += " AND p.version = ?"
      qargs += (version,)
    if email:
      q += " AND emails LIKE ?"
      qargs += ("%%<%s>%%"%(email),)

    cursor.execute(q, qargs)
    # use srcpkg_factory to fetch sourcepackages, once per suite
    for srcpkg,rest in cursor.fetchall():
      toc.ins(srcpkg, rest['suitename']) 
    self.db.row_factory = oldfactory
    return toc

  def findLetterToc(self, letter):
    return self.findCollection(package=letter+"%").getletter(letter)

  def findIndices(self):
    indices = []
    cursor = self.db.cursor()
    q1 = "SELECT DISTINCT SUBSTR(name,1,1) FROM packages \
            WHERE name NOT LIKE 'lib%'"
    q2 = "SELECT DISTINCT SUBSTR(name,1,4) FROM packages \
            WHERE name LIKE 'lib%'"

    for q in [q1, q2]:
      cursor.execute(q)
      for idx in cursor.fetchall():
        indices.append(idx[0])
    
    indices.sort()
    return indices
     
  def relateSourcePackage(self, name, version, suite, component, maintainers):
    q = "INSERT INTO package_rel_map \
           (package_id,suite_id,component_id,marked) \
           VALUES ((SELECT id FROM packages WHERE name=? AND version=?), \
                  (SELECT id FROM suites WHERE name=?), \
                  (SELECT id FROM components WHERE name=?), 1)"
    cursor = self.db.cursor()
    cursor.execute(q, (name, version, suite, component))
    q2 = "INSERT INTO maint_rel_map \
           (package_id,maintainer_id) \
           VALUES ((SELECT id FROM packages WHERE name=? AND version=?), \
                  (SELECT id FROM maintainers WHERE email=?))"
    cursor.executemany(q2, [(name, version, m) for m in maintainers])

  def findDiffGz(self, pkgname, version):
    q = "SELECT diffgz_name,loc FROM packages WHERE name=? AND version=?"
    cursor = self.db.cursor()
    cursor.execute(q, (pkgname, version))
    try:
      diffgz,loc = cursor.fetchone()
      return os.sep.join([Conf.archive_root, loc, diffgz])
    except:
      return None

  def prune(self):
    q = "DELETE FROM package_rel_map WHERE marked != 1"
    cursor = self.db.cursor()
    cursor.execute(q)

  def unmark(self):
    q = "UPDATE package_rel_map SET marked=0"
    cursor = self.db.cursor()
    cursor.execute(q)

  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()