Author: fw
Date: 2005-09-17 13:02:49 +0000 (Sat, 17 Sep 2005)
New Revision: 2030
Modified:
lib/python/bugs.py
lib/python/security_db.py
Log:
lib/python/bugs.py (BugFromDB.getDebianBugs):
Optimize SELECT statement.
(BugFromDB.getSourcePackages, BugFromDB.getBinaryPackages):
Move to security_db.DB.
lib/python/security_db.py (DB._initFunctions):
Register user-defined SQLite functions.
(DB.getSourcePackages, DB.getBinaryPackages):
Renamed from bugs.BugFromDB. Implement aggregation.
Modified: lib/python/bugs.py
==================================================================---
lib/python/bugs.py 2005-09-17 01:44:26 UTC (rev 2029)
+++ lib/python/bugs.py 2005-09-17 13:02:49 UTC (rev 2030)
@@ -358,60 +358,11 @@
def getDebianBugs(self, cursor):
"""Returns a list of Debian bugs to which the bug report
refers."""
return map(lambda (x,): x, cursor.execute(
- """SELECT DISTINCT bug FROM debian_bugs,
package_notes
+ """SELECT DISTINCT bug FROM package_notes,
debian_bugs
WHERE package_notes.bug_name = ?
AND debian_bugs.note = package_notes.id
ORDER BY bug""", (self.name,)))
- def getSourcePackages(self, cursor):
- # FIXME: Should perform aggregation.
-
- result = []
- for (package, release, subrelease, version, vulnerable) \
- in cursor.execute(
- """SELECT p.name, p.release, p.subrelease,
p.version,
- s.vulnerable
- FROM source_packages AS p, source_package_status AS s,
- package_notes AS n
- WHERE n.bug_name = ? AND s.note = n.id AND p.rowid = s.package
- ORDER BY p.name, p.version_id""",
- (self.name,)):
- result.append((package, release, subrelease, version, vulnerable))
-
- return result
-
- def getBinaryPackages(self, cursor):
- # FIXME: This should aggregated, possibly using group_by.
-
- result = []
-
- def group_by(column, source):
- result = {}
- for t in source:
- key = t[column]
- value = t[:column] + t[column + 1:]
- if result.has_key(key):
- result[key].append(value)
- else:
- result[key] = [value]
- return result
-
-
- for (package, release, subrelease, version, archs, vulnerable) \
- in cursor.execute(
- """SELECT p.name, p.release, p.subrelease,
p.version, p.archs,
- s.vulnerable
- FROM binary_packages AS p, binary_package_status AS s,
- package_notes AS n
- WHERE n.bug_name = ? AND s.note = n.id AND p.rowid = s.package
- ORDER BY p.name, p.version_id""",
- (self.name,)):
- for arch in archs.split('',''):
- result.append((package, release, subrelease, version, arch,
- vulnerable))
-
- return result
-
def getStatus(self, cursor):
"""Calculate bug status.
Modified: lib/python/security_db.py
==================================================================---
lib/python/security_db.py 2005-09-17 01:44:26 UTC (rev 2029)
+++ lib/python/security_db.py 2005-09-17 13:02:49 UTC (rev 2030)
@@ -107,6 +107,8 @@
print "DB: schema version mismatch: expected %d, got
%d" \
% (self.schema_version, v)
raise SchemaMismatch, `v`
+
+ self._initFunctions()
return
assert False
@@ -271,6 +273,55 @@
cursor.execute("PRAGMA user_version = %d" %
self.schema_version)
+ def _initFunctions(self):
+ """Registers user-defined SQLite
functions."""
+
+ def string_list_add(lst, *args):
+ for arg in args:
+ lst.append(arg)
+ def string_list_to_string(lst):
+ return '', ''.join(lst)
+ def string_list_factory():
+ return ([], string_list_add, string_list_to_string)
+ self.db.createaggregatefunction("string_list",
string_list_factory)
+
+ def string_set_add(lst, *args):
+ for arg in args:
+ for arch in arg.split('',''):
+ lst[arch] = True
+ def string_set_to_archs(lst):
+ l = lst.keys()
+ l.sort()
+ return '',''.join(l)
+ def string_set_factory():
+ return ({}, string_set_add, string_set_to_archs)
+ self.db.createaggregatefunction("string_set",
string_set_factory)
+
+ urgencies = [''high'', ''medium'',
''low'', ''unimportant'']
+ def urgency_to_number(u):
+ try:
+ return urgencies.index(u)
+ except ValueError:
+ return 999
+ self.db.createscalarfunction("urgency_to_number",
urgency_to_number, 1)
+
+ releases = [''potato'', ''woody'',
''sarge'', ''etch'', ''sid'']
+ def release_to_number(u):
+ try:
+ return releases.index(u)
+ except ValueError:
+ return -1
+ self.db.createscalarfunction("release_to_number",
release_to_number, 1)
+
+ def release_name(release, subrelease, archive):
+ if archive <> ''main'':
+ release = release + ''/'' + archive
+ if subrelease:
+ return "%s (%s)" % (release, subrelease)
+ else:
+ return release
+ self.db.createscalarfunction("release_name", release_name, 3)
+
def filePrint(self, filename):
"""Returns a fingerprint string for
filename."""
@@ -1091,6 +1142,52 @@
return result
+ def getSourcePackages(self, cursor, bug):
+ """A generator which returns tuples (SOURCE-PACKAGE,
+ RELEASE-LIST, VERSION, VULNERABLE-FLAG) of source packages
+ which are related to the given bug."""
+
+ for (package, releases, version, vulnerable) in cursor.execute(
+ """SELECT package, string_list(release), version,
vulnerable
+ FROM (SELECT p.name AS package,
+ release_name(p.release, p.subrelease, p.archive) AS release,
+ p.version AS version, s.vulnerable AS vulnerable
+ FROM package_notes AS n, source_package_status AS s,
+ source_packages AS p
+ WHERE n.bug_name = ? AND s.note = n.id AND p.rowid = s.package
+ ORDER BY release_to_number(p.release), p.subrelease)
+ GROUP BY package, version, vulnerable
+ ORDER BY package, version""",
+ (bug,)):
+ yield package, releases.split('', ''), version,
vulnerable
+
+ def getBinaryPackages(self, cursor, bug):
+ """A generator which returns tuples
(BINARY-PACKAGE-LIST,
+ RELEASE-LIST, VERSION, ARCH-LIST, VULNERABLE-FLAG) of binary
+ packages which are related to the given bug."""
+
+ for (packages, releases, version, archs, vulnerable) in cursor.execute(
+ """SELECT string_list(package) AS packages,
releases,
+ version, archs, vulnerable
+ FROM (SELECT package, string_set(release) AS releases,
+ version, archs, vulnerable
+ FROM (SELECT p.name AS package,
+ release_name(p.release, p.subrelease, p.archive) AS release,
+ p.version AS version, string_set(archs) AS archs,
+ s.vulnerable AS vulnerable
+ FROM package_notes AS n, binary_package_status AS s,
+ binary_packages AS p
+ WHERE n.bug_name = ? AND s.note = n.id AND p.rowid = s.package
+ GROUP BY p.name, p.release, p.subrelease, p.archive,
+ p.version, vulnerable
+ ORDER BY release_to_number(p.release), p.subrelease)
+ GROUP BY package, version, vulnerable, archs
+ ORDER BY package, version)
+ GROUP BY releases, version, vulnerable, archs
+ ORDER BY packages, version""",
+ (bug,)):
+ yield (packages.split('', ''),
releases.split('',''), version,
+ archs.split('',''), vulnerable)
def check(self, cursor=None):
"""Runs a simple consistency check and prints the
results."""