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