Author: fw
Date: 2005-09-22 10:04:07 +0000 (Thu, 22 Sep 2005)
New Revision: 2079
Modified:
lib/python/security_db.py
Log:
Remove cruft from the database schema. Make status tracking
explicitly per-bug and not per-package note.
lib/python/security_db.py (DB.__init__):
Bump schema version.
(DB.initSchema):
In tables source_package_status and binary_package_status,
make (bug_name, package) the primary key. Remove the note
and release columns.
(DB.calculateVulnerabilities):
Update accordingly. Remove code which sets the dead variables
package_by_release and binary_package_status.
(DB._calcUnstable, DB._calcTesting):
Update.
(DB.getSourcePackages, DB.getBinaryPackages, DB.getBugsForBinaryPackage,
DB.getBugsForSourcePackage):
Update to new database schema.
(DB.getBugXrefs):
New method, which works with xrefs in both directions.
Modified: lib/python/security_db.py
==================================================================---
lib/python/security_db.py 2005-09-22 09:47:53 UTC (rev 2078)
+++ lib/python/security_db.py 2005-09-22 10:04:07 UTC (rev 2079)
@@ -93,7 +93,7 @@
self.db = apsw.Connection(name)
self.verbose = verbose
- self.schema_version = 10
+ self.schema_version = 11
c = self.cursor()
for (v,) in c.execute("PRAGMA user_version"):
@@ -243,29 +243,24 @@
PRIMARY KEY (bug_name, release))""")
cursor.execute("""CREATE TABLE source_package_status
- (note INTEGER NOT NULL,
+ (bug_name TEXT NOT NULL,
package INTEGER NOT NULL,
vulnerable INTEGER NOT NULL,
- bug_name TEXT NOT NULL,
- release TEXT NOT NULL,
- PRIMARY KEY (note, package))""")
+ urgency TEXT NOT NULL,
+ PRIMARY KEY (bug_name, package))""")
cursor.execute(
"""CREATE INDEX source_package_status_package
ON source_package_status(package)""")
cursor.execute("""CREATE TABLE binary_package_status
- (note INTEGER NOT NULL,
+ (bug_name TEXT NOT NULL,
package INTEGER NOT NULL,
vulnerable INTEGER NOT NULL,
- bug_name TEXT NOT NULL,
- release TEXT NOT NULL,
- PRIMARY KEY (note, package))""")
+ urgency TEXT NOT NULL,
+ PRIMARY KEY (bug_name, package))""")
cursor.execute(
"""CREATE INDEX binary_package_status_package
ON binary_package_status(package)""")
- cursor.execute(
- """CREATE INDEX binary_package_status_bug_name
- ON binary_package_status(bug_name)""")
cursor.execute("PRAGMA user_version = %d" %
self.schema_version)
@@ -972,9 +967,9 @@
cursor.execute(
"""INSERT INTO source_package_status
- SELECT n.id, p.rowid,
+ SELECT n.bug_name, p.rowid,
n.fixed_version IS NULL OR p.version_id < n.fixed_version_id,
- n.bug_name, p.release
+ n.urgency
FROM package_notes AS n, source_packages AS p
WHERE n.release = '''' AND p.name =
n.package""")
@@ -985,9 +980,9 @@
print " source packages (qualified)"
cursor.execute(
"""INSERT OR REPLACE INTO source_package_status
- SELECT n.id, p.rowid,
+ SELECT n.bug_name, p.rowid,
n.fixed_version IS NULL OR p.version_id < n.fixed_version_id,
- n.bug_name, p.release
+ n.urgency
FROM package_notes AS n, source_packages AS p
WHERE p.name = n.package
AND p.release = n.release""")
@@ -996,23 +991,23 @@
print " binary packages (from source packages)"
cursor.execute(
"""INSERT INTO binary_package_status
- SELECT n.id, p.rowid,
+ SELECT n.bug_name, p.rowid,
n.fixed_version IS NULL
OR p.source_version_id < n.fixed_version_id,
- n.bug_name, p.release
+ n.urgency
FROM package_notes AS n, binary_packages AS p
WHERE n.release = '''' AND p.source =
n.package""")
cursor.execute(
"""INSERT OR REPLACE INTO binary_package_status
- SELECT n.id, p.rowid,
+ SELECT n.bug_name, p.rowid,
n.fixed_version IS NULL
OR p.source_version_id < n.fixed_version_id,
- n.bug_name, p.release
+ n.urgency
FROM package_notes AS n, binary_packages AS p
WHERE p.source = n.package AND p.release =
n.release""")
- # Almost the samefor binary packages. We prefer interpreting
+ # Almost the same for binary packages. We prefer interpreting
# package names as source packages, so we only process the
# notes which refer to binary packages. (Of course, we do not
# have to add status information for binary package
@@ -1022,9 +1017,9 @@
print " binary packages (unqualified)"
cursor.execute(
"""INSERT INTO binary_package_status
- SELECT n.id, p.rowid,
+ SELECT n.bug_name, p.rowid,
n.fixed_version IS NULL OR p.version_id < n.fixed_version_id,
- n.bug_name, p.release
+ n.urgency
FROM package_notes AS n, binary_packages AS p
WHERE n.release = '''' AND p.name = n.package
AND n.package_kind = ''binary''""")
@@ -1033,9 +1028,9 @@
print " binary packages (qualified)"
cursor.execute(
"""INSERT OR REPLACE INTO binary_package_status
- SELECT n.id, p.rowid,
+ SELECT n.bug_name, p.rowid,
n.fixed_version IS NULL OR p.version_id < n.fixed_version_id,
- n.bug_name, p.release
+ n.urgency
FROM package_notes AS n, binary_packages AS p
WHERE p.name = n.package AND p.release = n.release
AND n.package_kind = ''binary''""")
@@ -1044,20 +1039,21 @@
print " source packages (from binary packages)"
cursor.execute(
"""INSERT INTO source_package_status
- SELECT n.id, s.rowid,
+ SELECT n.bug_name, s.rowid,
MAX(n.fixed_version IS NULL
OR b.version_id < n.fixed_version_id),
- n.bug_name, s.release
+ MAX(n.urgency)
FROM package_notes AS n, binary_packages AS b,
source_packages AS s
WHERE n.package_kind = ''binary'' AND b.name =
n.package
AND s.name = b.source
AND s.release = b.release AND s.subrelease = b.subrelease
AND s.archive = b.archive
- GROUP BY n.id, s.rowid, n.bug_name, s.release""")
+ GROUP BY n.bug_name, s.rowid, s.release""")
# The "GROUP BY" is needed because we we might have multiple
# rows in the binary_packages table for different
# architectures.
+ # FIXME: MAX(n.urgency) is wrong.
# Calculate the release-specific bug status.
@@ -1066,20 +1062,6 @@
c = self.cursor()
-
- # Packages relevant for testing. This includes the packages
- # from unstable.
-
- package_by_release = {}
- binary_packages_in_testing = {}
- for x in (''etch'', ''sid''):
- package_by_release[x] = {}
- for (pkg_name, release) in cursor.execute(
- """SELECT name, release FROM binary_packages
- WHERE release IN (''etch'',
''sid'')"""):
- package_by_release[release][pkg_name] = True
- binary_packages_in_testing[pkg_name] = True
-
for (bug_name,) in cursor.execute(
"SELECT name FROM bugs WHERE NOT not_for_us"):
@@ -1095,12 +1077,12 @@
have_something = False
for (package, vulnerable) in cursor.execute(
"""SELECT DISTINCT sp.name, st.vulnerable
- FROM package_notes AS n,
- source_package_status AS st, source_packages AS sp
- WHERE n.bug_name = ? AND n.urgency <>
''unimportant''
- AND n.release = ''''
- AND st.note = n.id
- AND sp.rowid = st.package AND sp.release = ''sid''
+ FROM source_package_status AS st,
+ source_packages AS sp, package_notes AS n
+ WHERE st.bug_name = ? AND sp.rowid = st.package
+ AND sp.release = ''sid''
+ AND n.bug_name = st.bug_name AND n.package = sp.name
+ AND n.urgency <> ''unimportant''
ORDER BY sp.name""",
(bug_name,)):
have_something = True
@@ -1132,18 +1114,18 @@
# Note that there is at most one source package per
# note/release/subrelease triple, but we should check that
- # here. (A separate test is needed.)
+ # here.
status = {'''' : {}, ''security'' : {}}
for (package, note, subrelease, vulnerable) in cursor.execute(
"""SELECT DISTINCT sp.name, n.id, sp.subrelease,
st.vulnerable
- FROM package_notes AS n,
- source_package_status AS st, source_packages AS sp
- WHERE n.bug_name = ? AND n.urgency <>
''unimportant''
- AND st.note = n.id
- AND sp.rowid = st.package AND sp.release = ''etch''
- AND sp.subrelease IN ('''',
''security'')
+ FROM source_package_status AS st,
+ source_packages AS sp, package_notes AS n
+ WHERE st.bug_name = ? AND sp.rowid = st.package
+ AND sp.release = ''etch'' AND sp.subrelease IN
('''', ''security'')
+ AND n.bug_name = st.bug_name AND n.package = sp.name
+ AND n.urgency <> ''unimportant''
ORDER BY sp.name""",
(bug_name,)):
status[subrelease][(package, note)] = vulnerable
@@ -1242,9 +1224,8 @@
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
+ FROM source_package_status AS s, source_packages AS p
+ WHERE s.bug_name = ? AND p.rowid = s.package
ORDER BY release_to_number(p.release), p.subrelease)
GROUP BY package, version, vulnerable
ORDER BY package, version COLLATE version""",
@@ -1265,9 +1246,8 @@
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
+ FROM binary_package_status AS s, binary_packages AS p
+ WHERE s.bug_name = ? 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)
@@ -1312,12 +1292,12 @@
"""SELECT name, description
FROM (SELECT bugs.name AS name, bugs.description AS description,
MAX(st.vulnerable) AS vulnerable
- FROM source_packages AS sp, source_package_status AS st,
- package_notes AS n, bugs
- WHERE sp.name = ? AND st.package = sp.rowid AND n.id = st.note
- AND bugs.name = n.bug_name
+ FROM source_packages AS sp, source_package_status AS st, bugs
+ WHERE sp.name = ? AND st.package = sp.rowid
+ AND bugs.name = st.bug_name
GROUP BY bugs.name, bugs.description)
- WHERE vulnerable = ?""", (pkg, vulnerable))
+ WHERE vulnerable = ?
+ ORDER BY name""", (pkg, vulnerable))
def getBugsForBinaryPackage(self, cursor, pkg, vulnerable):
"""Returns a generator for a list of (BUG, DESCRIPTION)
pairs
@@ -1326,12 +1306,12 @@
"""SELECT name, description
FROM (SELECT bugs.name AS name, bugs.description AS description,
MAX(st.vulnerable) AS vulnerable
- FROM binary_packages AS bp, binary_package_status AS st,
- package_notes AS n, bugs
- WHERE bp.name = ? AND st.package = bp.rowid AND n.id = st.note
- AND bugs.name = n.bug_name
+ FROM binary_packages AS bp, binary_package_status AS st, bugs
+ WHERE bp.name = ? AND st.package = bp.rowid
+ AND bugs.name = st.bug_name
GROUP BY bugs.name, bugs.description)
- WHERE vulnerable = ?""", (pkg, vulnerable))
+ WHERE vulnerable = ?
+ ORDER BY name""", (pkg, vulnerable))
def getTODOs(self, cursor=None):
"""Returns a list of pairs (BUG-NAME,
DESCRIPTION)."""
@@ -1343,6 +1323,22 @@
WHERE bugs_notes.typ = ''TODO'' AND bugs.name =
bugs_notes.bug_name
ORDER BY name """)
+ def getBugXrefs(self, cursor, bug):
+ """Returns a generator for a list of bug names. The
listed
+ bugs refer to the given bug, or the bug refers to
them."""
+
+ for (bug_name,) in cursor.execute(
+ """SELECT DISTINCT bug
+ FROM (SELECT normalized_target AS bug
+ FROM bugs_xref WHERE source = ?
+ UNION ALL SELECT source AS bug
+ FROM bugs_xref WHERE normalized_target = ?
+ UNION ALL SELECT bug_origin AS bug FROM package_notes
+ WHERE bug_name = ? AND bug_origin <> '''')
+ WHERE bug <> ?
+ ORDER BY bug""", (bug, bug, bug, bug)):
+ yield bug_name
+
def check(self, cursor=None):
"""Runs a simple consistency check and prints the
results."""