Package: security-tracker Severity: wishlist The attached patch implements a first pass at a per-maintainer page of security issues. It involves some database schema changes to it will require a full reimport of all the data. My SQL knowledge isn't great, so there are some deficiencies: I'm not sure if the adding another table is the right way to go, nor if I used the right table name. I'm not sure if the getBugsForMaintainer is correct, especially wrt version numbers/releases/etc. I am not sure how to implement a getDSAsForMaintainer function to add DSAs related to the maintainer at the bottom of the page. -- bye, pabs http://wiki.debian.org/PaulWise
Index: lib/python/security_db.py
===================================================================
--- lib/python/security_db.py (revision 18462)
+++ lib/python/security_db.py (working copy)
@@ -38,6 +38,7 @@
import sys
import types
import zlib
+import email.utils
import debian_support
import dist_config
@@ -123,6 +124,9 @@
# Enable WAL. This means that updates will not block readers.
c.execute("PRAGMA journal_mode = WAL")
+ # Enable foreign keys
+ c.execute("PRAGMA foreign_keys=ON")
+
self.schema_version = 22
self._initFunctions()
@@ -198,15 +202,23 @@
cursor.execute(
"""CREATE TABLE source_packages
- (name TEXT NOT NULL,
+ (id INTEGER,
+ name TEXT NOT NULL,
release TEXT NOT NULL,
subrelease TEXT NOT NULL,
archive TEXT NOT NULL,
version TEXT NOT NULL,
version_id INTEGER NOT NULL DEFAULT 0,
- PRIMARY KEY (name, release, subrelease, archive))""")
+ UNIQUE (name, release, subrelease, archive),
+ PRIMARY KEY(id ASC))""")
cursor.execute(
+ """CREATE TABLE source_package_maintainers
+ (source_package_id INTEGER NOT NULL,
+ maintainer TEXT NOT NULL,
+ FOREIGN KEY(source_package_id) REFERENCES source_packages(id) ON DELETE CASCADE)""")
+
+ cursor.execute(
"""CREATE TABLE binary_packages
(name TEXT NOT NULL,
release TEXT NOT NULL,
@@ -348,14 +360,14 @@
AND sidp.release = 'sid' AND sidp.subrelease = ''
AND sidp.archive = sp.archive
AND sidst.bug_name = st.bug_name
- AND sidst.package = sidp.rowid) AS unstable_vulnerable,
+ AND sidst.package = sidp.id) AS unstable_vulnerable,
COALESCE((SELECT NOT vulnerable
FROM source_packages AS tsecp, source_package_status AS tsecst
WHERE tsecp.name = sp.name
AND tsecp.release = 'wheezy' AND tsecp.subrelease = 'security'
AND tsecp.archive = sp.archive
AND tsecst.bug_name = st.bug_name
- AND tsecst.package = tsecp.rowid), 0) AS testing_security_fixed,
+ AND tsecst.package = tsecp.id), 0) AS testing_security_fixed,
(SELECT range_remote FROM nvd_data
WHERE cve_name = st.bug_name) AS remote,
(EXISTS (SELECT * FROM package_notes_nodsa AS pnd
@@ -363,7 +375,7 @@
AND pnd.package = sp.name
AND pnd.release = 'wheezy')) AS no_dsa
FROM source_package_status AS st, source_packages AS sp
- WHERE st.vulnerable > 0 AND sp.rowid = st.package
+ WHERE st.vulnerable > 0 AND sp.id = st.package
AND sp.release = 'wheezy' AND sp.subrelease = ''
ORDER BY sp.name, st.urgency, st.bug_name""")
@@ -380,7 +392,7 @@
AND pnd.package = sp.name
AND pnd.release = '%s')) AS no_dsa
FROM source_package_status AS st, source_packages AS sp
- WHERE st.vulnerable > 0 AND sp.rowid = st.package
+ WHERE st.vulnerable > 0 AND sp.id = st.package
AND sp.release = '%s' AND sp.subrelease = ''
AND NOT COALESCE((SELECT NOT vulnerable
FROM source_packages AS secp, source_package_status AS secst
@@ -388,7 +400,7 @@
AND secp.release = '%s' AND secp.subrelease = 'security'
AND secp.archive = sp.archive
AND secst.bug_name = st.bug_name
- AND secst.package = secp.rowid), 0)
+ AND secst.package = secp.id), 0)
ORDER BY sp.name, urgency_to_number(urgency), st.bug_name"""
% (name, nickname, nickname, nickname))
@@ -465,6 +477,7 @@
for pkg in packages:
pkg_name = None
pkg_version = None
+ pkg_maintainers = []
pkg_arch = None
pkg_source = None
pkg_source_version = None
@@ -473,6 +486,8 @@
pkg_name = contents
elif name == "Version":
pkg_version = contents
+ elif name in ("Maintainer", "Uploaders"):
+ pkg_maintainers += [mail for (name, mail) in email.utils.getaddresses([contents])]
elif name == "Source":
match = re_source.match(contents)
if match is None:
@@ -482,6 +497,7 @@
(pkg_source, pkg_source_version) = match.groups()
elif name == "Architecture":
pkg_arch = contents
+ pkg_maintainers = list(set(pkg_maintainers))
if pkg_name is None:
raise SyntaxError\
("package record does not contain package name")
@@ -497,10 +513,10 @@
oversion = debian_support.Version(data[pkg_name][0])
if oversion < debian_support.Version(pkg_version):
data[pkg_name] = (pkg_version, pkg_arch,
- pkg_source, pkg_source_version)
+ pkg_source, pkg_source_version, pkg_maintainers)
else:
data[pkg_name] = (pkg_version, pkg_arch,
- pkg_source, pkg_source_version)
+ pkg_source, pkg_source_version, pkg_maintainers)
return data
@@ -546,6 +562,7 @@
print " reading source packages"
for filename in glob.glob(directory + '/*_Sources'):
+ print filename
match = re_sources.match(filename)
if match is None:
raise ValueError, "invalid file name: " + `filename`
@@ -561,18 +578,26 @@
(release, subrelease, archive))
self._clearVersions(cursor)
- def gen():
- for name in parsed.keys():
- (version, archs, source, source_version) = parsed[name]
- assert source is None
- assert source_version is None
- yield name, release, subrelease, archive, version
- cursor.executemany(
- """INSERT INTO source_packages
- (name, release, subrelease, archive, version)
- VALUES (?, ?, ?, ?, ?)""",
- gen())
-
+ for name in parsed.keys():
+ (version, archs, source, source_version, maintainers) = parsed[name]
+ assert source is None
+ assert source_version is None
+ cursor.execute(
+ """INSERT INTO source_packages
+ (name, release, subrelease, archive, version)
+ VALUES (?, ?, ?, ?, ?)""",
+ (name, release, subrelease, archive, version))
+ last_id = self.db.last_insert_rowid()
+ def gen():
+ for maintainer in maintainers:
+ yield last_id, maintainer
+ cursor.executemany(
+ """INSERT INTO source_package_maintainers
+ (source_package_id, maintainer)
+ VALUES (?, ?)""",
+ gen())
+ print 'done importing'
+
def _readBinaryPackages(self, cursor, directory):
"""Reads from a directory with binary package files."""
@@ -618,7 +643,7 @@
(unch, parsed) = self._parseFile(cursor, filename)
unchanged = unchanged and unch
for name in parsed.keys():
- (version, arch, source, source_version) = parsed[name]
+ (version, arch, source, source_version, maintainer) = parsed[name]
if source is None:
source = name
if source_version is None:
@@ -1001,7 +1026,7 @@
cursor.execute(
"""INSERT INTO source_package_status
- SELECT n.bug_name, p.rowid,
+ SELECT n.bug_name, p.id,
CASE WHEN n.fixed_version == 'undetermined' THEN 2
ELSE CASE WHEN n.fixed_version IS NULL THEN 1
ELSE CASE WHEN p.version_id < n.fixed_version_id THEN 1
@@ -1017,7 +1042,7 @@
print " source packages (qualified)"
cursor.execute(
"""INSERT OR REPLACE INTO source_package_status
- SELECT n.bug_name, p.rowid,
+ SELECT n.bug_name, p.id,
CASE WHEN n.fixed_version == 'undetermined' THEN 2
ELSE CASE WHEN n.fixed_version IS NULL THEN 1
ELSE CASE WHEN p.version_id < n.fixed_version_id THEN 1
@@ -1081,7 +1106,7 @@
"""SELECT DISTINCT sp.name, st.vulnerable, n.urgency
FROM source_package_status AS st,
source_packages AS sp, package_notes AS n
- WHERE st.bug_name = ? AND sp.rowid = st.package
+ WHERE st.bug_name = ? AND sp.id = st.package
AND sp.release = 'sid'
AND n.bug_name = st.bug_name AND n.package = sp.name
ORDER BY sp.name""",
@@ -1145,7 +1170,7 @@
st.vulnerable, n.urgency
FROM source_package_status AS st,
source_packages AS sp, package_notes AS n
- WHERE st.bug_name = ? AND sp.rowid = st.package
+ WHERE st.bug_name = ? AND sp.id = st.package
AND sp.release = ? AND sp.subrelease IN ('', 'security')
AND n.bug_name = st.bug_name AND n.package = sp.name
ORDER BY sp.name""",
@@ -1279,7 +1304,7 @@
AND p.release = ?
AND p.subrelease IN ('', 'security')
AND st.bug_name = ?
- AND st.package = p.rowid
+ AND st.package = p.id
ORDER BY p.version COLLATE version DESC"""
else:
fix_available_sql = ''
@@ -1615,7 +1640,7 @@
release_name(p.release, p.subrelease, p.archive) AS release,
p.version AS version, s.vulnerable AS vulnerable
FROM source_package_status AS s, source_packages AS p
- WHERE s.bug_name = ? AND p.rowid = s.package
+ WHERE s.bug_name = ? AND p.id = s.package
AND release in ('lenny', 'squeeze', 'wheezy', 'sid', 'experimental')
ORDER BY release_to_number(p.release), p.subrelease)
GROUP BY package, version, vulnerable
@@ -1661,14 +1686,14 @@
source_package_status AS st2
WHERE sp2.name = sp.name AND sp2.release = sp.release
AND sp2.subrelease = 'security' AND sp2.archive = sp.archive
- AND st2.package = sp2.rowid AND st2.bug_name = st.bug_name
+ AND st2.package = sp2.id AND st2.bug_name = st.bug_name
ORDER BY st2.vulnerable DESC), 1)) AS vulnerable,
st.urgency = 'unimportant' OR NOT vulnerable AS unimportant
FROM source_packages AS sp, source_package_status AS st, bugs
WHERE sp.name = ?
AND sp.release IN ('lenny', 'squeeze', 'wheezy', 'sid')
AND sp.subrelease <> 'security'
- AND st.package = sp.rowid
+ AND st.package = sp.id
AND bugs.name = st.bug_name
AND bugs.name NOT LIKE 'DSA-%'
GROUP BY bugs.name, bugs.description, sp.name)
@@ -1683,6 +1708,33 @@
AND bugs.name LIKE 'DSA-%'
AND p.package = ?""", (package,))
+ def getBugsForMaintainer(self, cursor, maintainer, vulnerable, unimportant):
+ """Returns a generator for a list of (BUG, DESCRIPTION) pairs
+ which have the requested status. Only bugs affecting supported
+ releases are returned."""
+ return cursor.execute(
+ """SELECT DISTINCT pkg, name, description
+ FROM (SELECT sp.name AS pkg, bugs.name AS name, bugs.description AS description,
+ MAX(st.vulnerable
+ AND COALESCE((SELECT st2.vulnerable FROM source_packages AS sp2,
+ source_package_status AS st2
+ WHERE sp2.name = sp.name AND sp2.release = sp.release
+ AND sp2.subrelease = 'security' AND sp2.archive = sp.archive
+ AND st2.package = sp2.id AND st2.bug_name = st.bug_name
+ ORDER BY st2.vulnerable DESC), 1)) AS vulnerable,
+ st.urgency = 'unimportant' OR NOT vulnerable AS unimportant
+ FROM source_packages AS sp, source_package_maintainers AS m, source_package_status AS st, bugs
+ WHERE m.maintainer = ?
+ AND m.source_package_id = sp.id
+ AND sp.release IN ('lenny', 'squeeze', 'wheezy', 'sid')
+ AND sp.subrelease <> 'security'
+ AND st.package = sp.id
+ AND bugs.name = st.bug_name
+ AND bugs.name NOT LIKE 'DSA-%'
+ GROUP BY bugs.name, bugs.description, sp.name)
+ WHERE vulnerable = ? AND unimportant = ?
+ ORDER BY name""", (maintainer, vulnerable, unimportant))
+
def getTODOs(self, cursor=None, hide_check=False):
"""Returns a list of pairs (BUG-NAME, DESCRIPTION)."""
if cursor is None:
Index: bin/tracker_service.py
===================================================================
--- bin/tracker_service.py (revision 18462)
+++ bin/tracker_service.py (working copy)
@@ -132,6 +132,7 @@
self.register('*', self.page_object)
self.register('redirect/*', self.page_redirect)
self.register('source-package/*', self.page_source_package)
+ self.register('maintainer/*', self.page_maintainer)
self.register('status/release/oldstable',
self.page_status_release_oldstable)
self.register('status/release/stable', self.page_status_release_stable)
@@ -278,6 +279,9 @@
if self.db.isSourcePackage(c, obj):
return RedirectResult(self.url_source_package(url, obj, full=True))
+ if '@' in obj:
+ return RedirectResult(self.url_maintainer(url, obj, full=True))
+
return self.page_not_found(url, obj)
def page_bug(self, url, name, redirect):
@@ -576,6 +580,49 @@
replacement='No known security announcements.')
])
+ def page_maintainer(self, path, params, url):
+ maintainer = path[0]
+
+ def gen_versions():
+ for (releases, version) in self.db.getSourcePackageVersions(
+ self.db.cursor(), maintainer):
+ yield ', '.join(releases), version
+ def gen_bug_list(lst):
+ for (pkg, bug, description) in lst:
+ yield self.make_xref(url, pkg), self.make_xref(url, bug), description
+
+ caption=('Package','Bug', 'Description')
+ return self.create_page(
+ url, 'Information on maintainer ' + maintainer,
+ [make_menu(lambda x: x,
+ (self.url_ddpo(url, maintainer),
+ maintainer + ' in the Debian Developer\'s Packages Overview'),
+ (self.url_debian_bug_maintainer(url, maintainer),
+ maintainer + ' in the Bug Tracking System'),
+ (self.url_buildd(url, maintainer),
+ maintainer + ' in the Buildd status page'),
+ (self.url_portfolio(url, maintainer),
+ maintainer + ' in the Debian Member Portfolio Service')),
+
+ H2('Open issues'),
+ make_table(gen_bug_list(self.db.getBugsForMaintainer
+ (self.db.cursor(), maintainer, True, False)),
+ caption=caption,
+ replacement='No known open issues.'),
+
+ H2('Open unimportant issues'),
+ make_table(gen_bug_list(self.db.getBugsForMaintainer
+ (self.db.cursor(), maintainer, True, True)),
+ caption=caption,
+ replacement='No known unimportant issues.'),
+
+ H2('Resolved issues'),
+ make_table(gen_bug_list(self.db.getBugsForMaintainer
+ (self.db.cursor(), maintainer, False, True)),
+ caption=caption,
+ replacement='No known resolved issues.')
+ ])
+
def page_status_release_stable_oldstable(self, release, params, url):
assert release in ('stable', 'oldstable')
@@ -1181,7 +1228,7 @@
status=status)
def make_search_button(self, url):
- return FORM("Search for package or bug name: ",
+ return FORM("Search for maintainer email or package or bug name: ",
INPUT(type='text', name='query',
onkeyup="onSearch(this.value)",
onmousemove="onSearch(this.value)"),
@@ -1223,17 +1270,34 @@
def url_debian_bug_pkg(self, url, debian):
return url.absolute("http://bugs.debian.org/cgi-bin/pkgreport.cgi";,
pkg=debian)
+ def url_debian_bug_maintainer(self, url, maintainer):
+ return url.absolute("http://bugs.debian.org/cgi-bin/pkgreport.cgi";,
+ maint=maintainer)
def url_pts(self, url, package):
return url.absolute("http://packages.qa.debian.org/common/index.html";,
src=package)
def url_testing_status(self, url, package):
return url.absolute("http://release.debian.org/migration/testing.pl";,
package=package)
+ def url_portfolio(self, url, maintainer):
+ return url.absolute("http://portfolio.debian.net/result";,
+ email=maintainer)
+ def url_ddpo(self, url, maintainer):
+ return url.absolute("http://qa.debian.org/developer.php";,
+ email=maintainer)
+ def url_buildd(self, url, maintainer):
+ return url.absolute("https://buildd.debian.org/status/package.php";,
+ p=maintainer, compact='compact', comaint='yes')
def url_source_package(self, url, package, full=False):
if full:
return url.scriptRelativeFull("source-package/" + package)
else:
return url.scriptRelative("source-package/" + package)
+ def url_maintainer(self, url, maintainer, full=False):
+ if full:
+ return url.scriptRelativeFull("maintainer/" + maintainer)
+ else:
+ return url.scriptRelative("maintainer/" + maintainer)
def make_xref(self, url, name):
return A(url.scriptRelative(name), name)
Attachment:
signature.asc
Description: This is a digitally signed message part