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) def srcpkg_collection_factory(cursor, row): d = {} rest = {} for idx, col in enumerate(cursor.description): if not d.has_key(col[0]): d[col[0]] = row[idx] else: rest[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), rest) 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 REPLACE INTO packages (id,name,format,loc,version,\ diffgz_name,diffgz_size,diffgz_md5sum)\ VALUES ((SELECT MAX(id)+1 FROM packages),?,?,?,?,?,?,?)" 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 OR REPLACE INTO suites (id,name) \ VALUES ((SELECT MAX(id)+1 FROM suites),?)" cursor = self.db.cursor() cursor.execute(q, (suite,)) def saveComponent(self, component): q = "INSERT OR REPLACE INTO components (id,name) \ VALUES ((SELECT MAX(id)+1 FROM components),?)" cursor = self.db.cursor() cursor.execute(q, (component,)) def findCollection(self, package="%", version=None): oldfactory = self.db.row_factory self.db.row_factory = srcpkg_collection_factory cursor = self.db.cursor() toc = SourceArchive.SourcePackageIndex() q = "SELECT * FROM packages AS p,package_rel_map AS m,suites AS s \ WHERE p.name LIKE ? AND p.id = m.package_id AND m.suite_id = s.id" qargs = (package,) if version: q += " AND p.version = ?" qargs += (version,) cursor.execute(q, qargs) # use srcpkg_factory to fetch sourcepackages, once per suite for srcpkg,rest in cursor.fetchall(): toc.ins(srcpkg, rest["name"]) self.db.row_factory = oldfactory return toc def findLetterToc(self, letter): return self.findCollection(package=letter+"%").getletter(letter) def findMasterIndex(self): return self.findCollection(package="%") def relateSourcePackage(self, name, version, suite, component): q = "INSERT OR REPLACE INTO package_rel_map \ (package_id,suite_id,component_id) \ VALUES (SELECT id FROM packages WHERE name=? AND version=?), \ (SELECT id FROM suites WHERE name=?), \ (SELECT id FROM components WHERE name=?))" cursor = self.db.cursor() cursor.execute(q, (name, version, 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()