lib/python/security_db.py

# security_db.py -- simple, CVE-driven Debian security bugs database
# Copyright (C) 2005 Florian Weimer <fw@deneb.enyo.de>
# 
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

"""This module implements a small database for tracking security bugs.

Note that the database is always secondary to the text files.  The
database is only an implementation tool, and not used for maintaining
the data.

The data is kept in a SQLite 3 database.

FIXME: Document the database schema once it is finished.
"""

import apsw
import bugs
import cPickle
import cStringIO
import debian_support
import glob
import os
import re
import sys
import types

class InsertError(Exception):
    """Class for capturing insert errors.

    The 'errors' member collects all error messages.
    """
    
    def __init__(self, errors):
        assert len(errors) > 0, errors
        assert type(errors) == types.ListType, errors
        assert type(errors[0])== types.StringType, errors
        self.errors = errors
        
    def __str__(self):
        return self.errors[0] + ' [more...]'

def mergeLists(a, b):
    """Merges two lists."""
    if type(a) == types.StringType:
        if a == "":
            a = []
        else:
            a = a.split(',')
    if type(b) == types.StringType:
        if b == "":
            b = []
        else:
            b = b.split(',')
    result = {}
    for x in a:
        result[x] = 1
    for x in b:
        result[x] = 1
    result = result.keys()
    result.sort()
    return result

class SchemaMismatch(Exception):
    """Raised to indicate a schema mismatch.

    The caller is expected to remove and regenerate the database."""

class DB:
    """Access to the security database.

    This is a wrapper around an SQLite database object (which is
    accessible as the "db" member.

    Most operations need a special cursor object, which can be created
    with a cursor object.  The name "cursor" is somewhat of a
    misnomer because these objects are quite versatile.
    """

    def __init__(self, name, verbose=False):
        self.db = apsw.Connection(name)
        self.verbose = verbose
        self.nicknames = {'etch': 'testing',
                          'sarge' : 'stable',
                          'woody': 'oldstable'}

        c = self.cursor()
        for (v,) in c.execute("PRAGMA user_version"):
            if v == 0:
                self.initSchema()
            if v <> 1:
                raise SchemaMismatch, `v`
            return
        assert False

    def cursor(self):
        """Creates a new database cursor.

        Also see the writeTxn method."""
        return self.db.cursor()

    def writeTxn(self):
        """Creates a cursor for an exclusive transaction.

        No other process may modify the database at the same time.
        After finishing the work, you should invoke the commit or
        rollback methods below.
        """
        c = self.cursor()
        c.execute("BEGIN TRANSACTION EXCLUSIVE")
        return c

    def commit(self, cursor):
        """Makes the changes in the transaction permanent."""
        cursor.execute("COMMIT")

    def rollback(self, cursor):
        """Undos the changes in the transaction."""
        cursor.execute("ROLLBACK")
    
    def initSchema(self):
        """Creates the database schema."""
        cursor = self.cursor()

        cursor.execute("""CREATE TABLE inodeprints
        (file TEXT NOT NULL PRIMARY KEY,
         inodeprint TEXT NOT NULL,
         parsed BLOB)""")

        cursor.execute(
            """CREATE TABLE nicknames
            (realname TEXT NOT NULL,
            nickname TEXT NOT NULL)""")
        cursor.executemany("INSERT INTO nicknames VALUES (?, ?)",
                           self.nicknames.items())

        cursor.execute("""CREATE TABLE version_linear_order
        (id INTEGER NOT NULL PRIMARY KEY,
         version TEXT NOT NULL UNIQUE)""")

        cursor.execute(
            """CREATE TABLE source_packages
            (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))""")

        cursor.execute(
            """CREATE TABLE binary_packages
            (name TEXT NOT NULL,
            release TEXT NOT NULL,
            subrelease TEXT NOT NULL,
            archive TEXT NOT NULL,
            version TEXT NOT NULL,
            source TEXT NOT NULL,
            source_version TEXT NOT NULL,
            archs TEXT NOT NULL,
            version_id INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (name, release, subrelease, archive, version, source,
            source_version))""")
        cursor.execute(
            """CREATE INDEX binary_packages_source
            ON binary_packages(source)""")

        cursor.execute("""CREATE TABLE package_notes
        (id INTEGER NOT NULL PRIMARY KEY,
         bug_name TEXT NOT NULL,
         package TEXT NOT NULL,
         fixed_version TEXT
             CHECK (fixed_version IS NULL OR fixed_version <> ''),
         fixed_version_id INTEGER NOT NULL DEFAULT 0,
         release TEXT NOT NULL,
         urgency TEXT NOT NULL)""")
        cursor.execute(
            "CREATE INDEX package_notes_bug ON package_notes(bug_name)")

        cursor.execute("""CREATE TABLE debian_bugs
        (bug INTEGER NOT NULL,
         note INTEGER NOT NULL,
         PRIMARY KEY (bug, note))""")

        cursor.execute("""CREATE TABLE bugs
        (name TEXT NOT NULL PRIMARY KEY,
         cve_status TEXT NOT NULL
             CHECK (cve_status IN
                    ('', 'CANDIDATE', 'ASSIGNED', 'RESERVED', 'REJECTED')),
         not_for_us INTEGER NOT NULL CHECK (not_for_us IN (0, 1)),
         description TEXT NOT NULL,
         source_file TEXT NOT NULL,
         source_line INTEGER NOT NULL)""")

        cursor.execute("""CREATE TABLE bugs_notes
        (bug_name TEXT NOT NULL CHECK (typ <> ''),
         typ TEXT NOT NULL CHECK (typ IN ('TODO', 'NOTE')),
         release TEXT NOT NULL DEFAULT '',
         comment TEXT NOT NULL CHECK (comment <> ''))""")

        cursor.execute("""CREATE TABLE bugs_xref
        (source TEXT NOT NULL,
         target TEXT NOT NULL,
         normalized_target TEXT NOT NULL DEFAULT '',
         PRIMARY KEY (source, target))""")

        cursor.execute("""CREATE TABLE bugs_status
        (bug_name TEXT NOT NULL,
         release TEXT NOT NULL,
         note INTEGER NOT NULL,
         reason TEXT NOT NULL,
         PRIMARY KEY (bug_name, release, note))""")
         
        cursor.execute("""CREATE TABLE source_package_status
        (note INTEGER NOT NULL,
         package INTEGER NOT NULL,
         vulnerable INTEGER NOT NULL,
         PRIMARY KEY (note, 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,
         package INTEGER NOT NULL,
         vulnerable INTEGER NOT NULL,
         PRIMARY KEY (note, package))""")
        cursor.execute(
            """CREATE INDEX binary_package_status_package
            ON binary_package_status(package)""")

        # Put this at the end.  Any exception will leave the schema
        # version at 0, so we automatically recreate the schema once
        # the application is started after the underlying error has
        # been fixed.

        cursor.execute("PRAGMA user_version = 1")

    def filePrint(self, filename):
        """Returns a fingerprint string for filename."""

        st = os.stat(filename)
        # The "1" is a version number which can be used to trigger a
        # re-read if the code has changed in an incompatible way.
        return `(st.st_size, st.st_ino, st.st_mtime, 1)`

    def _parseFile(self, cursor, filename):
        current_print = self.filePrint(filename)

        def do_parse(packages):
            if self.verbose:
                print "    parseFile: reading " + `filename`

            re_source = re.compile\
                (r'^([a-zA-Z0-9.+-]+)(?:\s+\(([a-zA-Z0-9.+:-]+)\))?$')

            data = []
            for pkg in packages:
                pkg_name = None
                pkg_version = None
                pkg_source = None
                pkg_source_version = None
                for (name, contents) in pkg:
                    if name == "Package":
                        pkg_name = contents
                    elif name == "Version":
                        pkg_version = contents
                    elif name == "Source":
                        match = re_source.match(contents)
                        if match is None:
                            raise SyntaxError(('package %s references '
                                               + 'invalid source package %s') %
                                              (pkg_name, `contents`))
                        (pkg_source, pkg_source_version) = match.groups()
                if pkg_name is None:
                    raise SyntaxError\
                          ("package record does not contain package name")
                if pkg_version is None:
                    raise SyntaxError\
                          ("package record for %s does not contain version"
                           % pkg_name)
                data.append((pkg_name, pkg_version,
                             pkg_source, pkg_source_version))

            return data

        def toString(data):
            result = cStringIO.StringIO()
            cPickle.dump(data, result)
            return result.getvalue()
            
        for (old_print, contents) in cursor.execute(
            "SELECT inodeprint, parsed FROM inodeprints WHERE file = ?",
            (filename,)):
            if old_print == current_print:
                return (True, cPickle.load(cStringIO.StringIO(contents)))
            result = do_parse(debian_support.PackageFile(filename))
            cursor.execute("""UPDATE inodeprints SET inodeprint = ?, parsed = ?
            WHERE file = ?""", (current_print, toString(result), filename))
            return (False, result)

        # No inodeprints entry, load file and add one.
        result = do_parse(debian_support.PackageFile(filename))
        cursor.execute("""INSERT INTO inodeprints (file, inodeprint, parsed)
        VALUES (?, ?, ?)""", (filename, current_print, toString(result)))
        return (False, result)

    def readPackages(self, cursor, directory):
        """Reads a directory of package files."""

        if self.verbose:
            print "readPackages:"

        self._readSourcePackages(cursor, directory)
        self._readBinaryPackages(cursor, directory)

        if self.verbose:
            print "  finished"
            
    def _readSourcePackages(self, cursor, directory):
        """Reads from directory with source package files."""

        re_sources = re.compile(r'.*/([a-z-]+)_([a-z-]*)_([a-z-]+)_Sources$')


        if self.verbose:
            print "  reading source packages"

        for filename in glob.glob(directory + '/*_Sources'):
            match = re_sources.match(filename)
            if match is None:
                raise ValueError, "invalid file name: " + `filename`

            (release, subrelease, archive) = match.groups()
            (unchanged, parsed) = self._parseFile(cursor, filename)
            if unchanged:
                continue

            cursor.execute(
                """DELETE FROM source_packages
                WHERE release = ? AND subrelease = ? AND archive = ?""",
                (release, subrelease, archive))
            self._clearVersions(cursor)

            def gen():
                for (name, version, source, source_version) in parsed:
                    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())
        
    def _readBinaryPackages(self, cursor, directory):
        """Reads from a directory with binary package files."""

        re_packages \
            = re.compile(
            r'.*/([a-z-]+)_([a-z-]*)_([a-z-]+)_([a-z0-9]+)_Packages$')
        
        if self.verbose:
            print "  reading binary packages"

        packages = {}
        unchanged = True
        for filename in glob.glob(directory + '/*_Packages'):
            match = re_packages.match(filename)
            if match is None:
                raise ValueError, "invalid file name: " + `filename`

            (release, subrelease, archive, architecture) = match.groups()
            (unch, parsed) = self._parseFile(cursor, filename)
            unchanged = unchanged and unch
            for (name, version, source, source_version) in parsed:
                if source is None:
                    source = name
                if source_version is None:
                    source_version = version
        
                key = (name, release, subrelease, archive, version,
                       source, source_version)
                if packages.has_key(key):
                    packages[key][architecture] = 1
                else:
                    packages[key] = {architecture : 1}

        if unchanged:
            if self.verbose:
                print "    finished (no changes)"
            return

        if self.verbose:
            print "    deleting old data"
        cursor.execute("DELETE FROM binary_packages")
        self._clearVersions(cursor)

        l = packages.keys()

        if len(l) == 0:
            raise ValueError, "no binary packages found"
        
        l.sort()
        def gen():
            for key in l:
                archs = packages[key].keys()
                archs.sort()
                archs = ','.join(archs)
                yield key + (archs,)

        if self.verbose:
            print "  storing binary package data"

        cursor.executemany(
            """INSERT INTO binary_packages
            (name, release, subrelease, archive, version,
            source, source_version, archs)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
            gen())
            
    def readBugs(self, cursor, path):
        if self.verbose:
            print "readBugs:"

        def clear_db(filename):
            cursor.execute(
                """CREATE TEMPORARY TABLE bugs_to_delete
                (tbd TEXT NOT NULL PRIMARY KEY)""")
            cursor.execute(
                """INSERT INTO bugs_to_delete
                SELECT name FROM bugs WHERE source_file = ?""",
                (filename,))

            cursor.execute(
                """DELETE FROM debian_bugs
                WHERE EXISTS (SELECT 1
                              FROM package_notes AS p, bugs_to_delete AS b
                              WHERE p.id = debian_bugs.note
                              AND p.bug_name = b.tbd)""")

            cursor.execute("""DELETE FROM bugs
                WHERE EXISTS (SELECT * FROM bugs_to_delete
                              WHERE tbd = name)""")
            cursor.execute("""DELETE FROM package_notes
                WHERE EXISTS (SELECT * FROM bugs_to_delete
                              WHERE tbd = bug_name)""")
            cursor.execute("""DELETE FROM bugs_notes
                WHERE EXISTS (SELECT * FROM bugs_to_delete
                              WHERE tbd = bug_name)""")
            cursor.execute("""DELETE FROM bugs_xref
                WHERE EXISTS (SELECT * FROM bugs_to_delete
                              WHERE tbd = source)""")

            # The *_status tables are regenerated anyway, no need to
            # delete them here.
            
            cursor.execute("""DROP TABLE bugs_to_delete""")
            
            self._clearVersions(cursor)
            
        def do_parse(source):
            errors = []
          
            if self.verbose:
                print "  reading " + `source.name`

            clear_db(source.name)
            
            for bug in source:
                try:
                    bug.writeDB(cursor)
                except ValueError, e:
                    errors.append("%s: %d: error: %s"
                                  % (bug.source_file, bug.source_line, e))
            if errors:
                raise InsertError(errors)

        def read_one(source):
            filename = source.name
            current_print = self.filePrint(filename)

            for (old_print,) in cursor.execute(
                "SELECT inodeprint FROM inodeprints WHERE file = ?",
                (filename,)):
                if old_print == current_print:
                    return False
                do_parse(source)
                cursor.execute(
                    "UPDATE inodeprints SET inodeprint = ? WHERE file = ?",
                    (current_print, filename))
                return True

            # No inodeprints entry, load file and add one.
            do_parse(source)
            cursor.execute(
                "INSERT INTO inodeprints (file, inodeprint) VALUES (?, ?)",
                (filename, current_print))
            return True

        unchanged = True
        if read_one(bugs.CANFile(path + '/CAN/list')):
            unchanged = False
        if read_one(bugs.CVEFile(path + '/CVE/list')):
            unchanged = False
        if read_one(bugs.DSAFile(path + '/DSA/list')):
            unchanged = False
        if read_one(bugs.DTSAFile(path + '/DTSA/list')):
            unchanged = False

        if unchanged:
            if self.verbose:
                print "  finished (no changes)"
            return

        errors = []
        
        if self.verbose:
            print "  checking CAN/CVE collisions"
        
        for b1, b2 in list(cursor.execute\
            ("""SELECT b1.name, b2.name FROM bugs AS b1, bugs AS b2
             WHERE b1.name LIKE 'CVE-%'
             AND b2.name = 'CAN-' || substr(b1.name, 5, 9)""")):
            b1 = bugs.BugFromDB(cursor, b1)
            b2 = bugs.BugFromDB(cursor, b2)

            errors.append("%s:%d: duplicate CVE entries %s and %s"
                          % (b1.source_file, b1.source_line,
                             b1.name, b2.name))
            errors.append("%s:%d: location of %s"
                          % (b1.source_file, b1.source_line, b1.name))
            errors.append("%s:%d: location of %s"
                          % (b2.source_file, b2.source_line, b2.name))

        # Normalize the CAN/CVE references to the entry which is
        # actually in the database.  After the CAN -> CVE transition,
        # this can go away (but we should check that the
        # cross-references are valid).

        if self.verbose:
            print "  normalize CAN/CVE references"
        
        for source, target in list(cursor.execute\
            ("""SELECT source, target FROM bugs_xref
            WHERE normalized_target = ''""")):
            if bugs.BugBase.re_cve_name.match(target):
                can_target = 'CAN-' + target[4:]
                cve_target = 'CVE-' + target[4:]

                found = False
                for (t,) in list(cursor.execute("""SELECT name FROM bugs
                WHERE name IN (?, ?)""", (can_target, cve_target))):
                    cursor.execute("""UPDATE bugs_xref
                    SET normalized_target = ?
                    WHERE source = ? AND target = ?""",
                                   (t, source, target))
                    found = True
                    break
                if not found:
                    b = bugs.BugFromDB(cursor, source)
                    errors.append\
                        ("%s: %d: reference to unknwown CVE entry %s"
                         % (b.source_file, b.source_line, target))

        if self.verbose:
            print "  check DSA/DTSA references"

        for source, target in list(cursor.execute
            ("""SELECT source, target FROM bugs_xref
            WHERE target LIKE 'DSA%' OR target LIKE 'DTSA%'""")):
            found = False
            for (b,) in cursor.execute("SELECT name FROM bugs WHERE name = ?",
                                       (target,)):
                found = True
            if not found:
                b = bugs.BugFromDB(cursor, source)
                errors.append\
                    ("%s: %d: reference to unknwown advisory %s"
                     % (b.source_file, b.source_line, target))

        if errors:
            raise InsertErrors(errors)

        if self.verbose:
            print "  finished"

    def availableReleases(self, cursor=None):
        """Returns a list of tuples (RELEASE, ARCHIVE,
        SOURCES-PRESENT, ARCHITECTURE-LIST)."""
        if cursor is None:
            cursor = self.cursor()

        releases = {}
        for r in cursor.execute(
            """SELECT DISTINCT release, subrelease, archive
            FROM source_packages"""):
            releases[r] = (True, [])

        for (rel, subrel, archive, archs) in cursor.execute(
            """SELECT DISTINCT release, subrelease, archive, archs
            FROM binary_packages"""):
            key = (rel, subrel, archive)
            if not releases.has_key(key):
                releases[key] = (False, [])
            releases[key][1][:] = mergeLists(releases[key][1], archs)

        result = []
        for ((rel, subrel, archive), (sources, archs)) in releases.items():
            result.append((rel, subrel, archive, sources, archs))
        result.sort()

        return result

    def getFunnyPackageVersions(self):
        """Returns a list of (PACKAGE, RELEASE, ARCHIVE, VERSION,
        SOURCE-VERSION) tuples such that PACKAGE is both a source and
        binary package, but the associated version numbers are
        different."""

        return list(self.db.cursor().execute(
            """SELECT DISTINCT name, release, archive, version, source_version
            FROM binary_packages
            WHERE name = source AND version <> source_version
            ORDER BY name, release, archive"""))

    def _clearVersions(self, cursor):
        cursor.execute("DELETE FROM version_linear_order")

    def _updateVersions(self, cursor):
        """Updates the linear version table."""

        if self.verbose:
            print "updateVersions:"

        for x in cursor.execute("SELECT * FROM version_linear_order LIMIT 1"):
            if self.verbose:
                print "  finished (no changes)"
            return

        if self.verbose:
            print "  reading"

        versions = []
        for (v,) in cursor.execute(
            """SELECT DISTINCT *
            FROM (SELECT fixed_version FROM package_notes
                WHERE fixed_version IS NOT NULL
            UNION ALL SELECT version FROM source_packages
            UNION ALL SELECT version FROM binary_packages)"""):
            versions.append(debian_support.Version(v))

        if self.verbose:
            print "  calculating linear order"
        versions.sort()

        if self.verbose:
            print "  storing linear order"
        for v in versions:
            cursor.execute(
                "INSERT INTO version_linear_order (version) VALUES (?)",
                (str(v),))

        if self.verbose:
            print "  updating package notes"
        cursor.execute(
            """UPDATE package_notes
            SET fixed_version_id = (SELECT id FROM version_linear_order
            WHERE version = package_notes.fixed_version)
            WHERE fixed_version IS NOT NULL""")

        if self.verbose:
            print "  updating source packages"
        cursor.execute(
            """UPDATE source_packages
            SET version_id = (SELECT id FROM version_linear_order
            WHERE version = source_packages.version)""")

        if self.verbose:
            print "  updating binary packages"
        cursor.execute(
            """UPDATE binary_packages
            SET version_id = (SELECT id FROM version_linear_order
            WHERE version = binary_packages.version)""")

        if self.verbose:
            print "  finished"
        
    def calculateVulnerabilities(self, cursor):
        """Calculate vulnerable packages.

        To each package note, a release-specific vulnerability status
        is attached.  Currently, only etch/testing is processed.

        Returns a list strings describing inconsistencies.
        """

        result = []

        self._updateVersions(cursor)
        # self._synthesizeReleases(cursor)

        if self.verbose:
            print "calculateVulnerabilities:"
            print "  check for version consistency in package notes"
        for (bug_name, pkg_name, rel, unstable_ver, rel_ver) \
                in list(cursor.execute(
        """SELECT a.bug_name, a.package, a.release,
        a.fixed_version, b.fixed_version
        FROM package_notes a, package_notes b
        WHERE a.bug_name = b.bug_name AND a.package = b.package
        AND a.release = '' AND b.release <> ''
        AND a.fixed_version_id < b.fixed_version_id""")):
            b = bugs.BugFromDB(cursor, bug_name)
            result.append("%s:%d: inconsistent versions for package %s"
                          % (b.source_file, b.source_line, pkg_name))
            result.append("%s:%d: unstable: %s"
                          % (b.source_file, b.source_line, rel_ver))
            result.append("%s:%d: release %s: %s"
                          % (b.source_file, b.source_line, `rel`, rel_ver))

        if self.verbose:
            print "  create temporary tables"
        cursor.execute(
            """CREATE TEMPORARY TABLE tmp_bug_releases
            (bug_name TEXT NOT NULL,
            release TEXT NOT NULL,
            PRIMARY KEY (bug_name, release))""")
        for (bug_name, release) in list(cursor.execute(
            """SELECT DISTINCT bug_name, release FROM package_notes
            WHERE release <> ''""")):
            data = [(bug_name, release),
                    (bug_name, release + '-security')]
            try:
                data.append((bug_name, self.nicknames[release]))
            except KeyError:
                pass
            cursor.executemany("INSERT INTO tmp_bug_releases VALUES (?, ?)",
                               data)

        if self.verbose:
            print "  remove old status"
        cursor.execute("DELETE FROM source_package_status")
        cursor.execute("DELETE FROM binary_package_status")
        if self.verbose:
            print "  calculate package status"
            print "    source packages (unqualified)"

        # If there is a single package note qualified with a specific
        # release, ignore all the unqualified annotations (even for
        # other packages).  This is implemented by looking at the
        # tmp_bug_releases table.
        
        cursor.execute(
            """INSERT INTO source_package_status
            SELECT n.id, p.rowid,
            n.fixed_version IS NULL OR p.version_id < n.fixed_version_id
            FROM package_notes AS n, source_packages AS p
            WHERE n.release = '' AND p.name = n.package
            AND NOT EXISTS (SELECT * FROM tmp_bug_releases AS t
                            WHERE t.bug_name = n.bug_name
                            AND t.release = p.release)""")
        if self.verbose:
            print "    source packages (qualified)"
        cursor.execute(
            """INSERT INTO source_package_status
            SELECT n.id, p.rowid,
            n.fixed_version IS NULL OR p.version_id < n.fixed_version_id
            FROM package_notes AS n, source_packages AS p
            WHERE p.name = n.package
            AND (p.release = n.release
                 OR p.release = (n.release || '-security')
                 OR p.release = (SELECT nickname FROM nicknames
                                 WHERE realname = n.release))""")

        # Same story for binary packages.  We prefer source packages,
        # so we skip all notes which have already source packages
        # attached.
            
        if self.verbose:
            print "    binary packages (unqualified)"
        cursor.execute(
            """INSERT INTO binary_package_status
            SELECT n.id, p.rowid,
            n.fixed_version IS NULL OR p.version_id < n.fixed_version_id
            FROM package_notes AS n, binary_packages AS p
            WHERE n.release = '' AND p.name = n.package
            AND (NOT EXISTS (SELECT * FROM tmp_bug_releases AS t
                            WHERE t.bug_name = n.bug_name
                            AND t.release = p.release))
            AND (NOT EXISTS (SELECT * FROM source_package_status AS s
                             WHERE s.package = p.rowid))""")
            
        if self.verbose:
            print "    binary packages (qualified)"
        cursor.execute(
            """INSERT INTO binary_package_status
            SELECT n.id, p.rowid,
            n.fixed_version IS NULL OR p.version_id < n.fixed_version_id
            FROM package_notes AS n, binary_packages AS p
            WHERE p.name = n.package
            AND (p.release = n.release
                 OR p.release = n.release || '-security'
                 OR p.release = (SELECT nickname FROM nicknames
                                 WHERE realname = n.release))
            AND (NOT EXISTS (SELECT * FROM source_package_status AS s
                             WHERE s.package = p.rowid))""")

        return
        

        if self.verbose:
            print "  clearing old data"
        cursor.execute("DELETE FROM bugs_status")

        def markVulnerable(bug, release, note, reason):
            cursor.execute("""INSERT INTO bugs_status
            (bug_name, release, note, reason) VALUES (?, ?, ?, ?)""",
                           (bug.name, release, note, reason))

        def calcVuln(bug):
            vulnerable = False
            note_found = False

            for n in bug.notes:
                # ignore all notes conditioned on releases.
                if n.release is not None: # assumes 'etch'
                    continue
                note_found = True
                v = self.getVersion(cursor, 'etch', n.package)
                if v is None:
                    # Package is not in testing, go on.
                    continue
                if n.affects(v):
                    vulnerable = True
                    markVulnerable(b, 'etch', n.id,
                                   "%s (%s) is vulnerable, %s"
                                   % (n.package, v, n.fixedVersion()))

            if bug.hasTODO():
                vulnerable = True
                markVulnerable(b, 'etch', 0, 'TODO items present')
            elif not note_found:
                # We found no matching note.  Maybe all packages have
                # been removed?
                if bug.notes:
                    for n in bug.notes:
                        if self.releaseContainsPackage \
                               (cursor, 'etch', n.package):
                            markVulnerable(b, 'etch', 0,
                                'applicable package note for %s missing'
                                           % n.package)
                            vulnerable = True
                else:
                    vulnerable = True
                    markVulnerable(b, 'etch', 0, 'status is unclear')

            return vulnerable

        # First handle the DSAs.  Cache results in DSA_status (used
        # for CAN/CVE below).

        if self.verbose:
            print "  reading DSAs"
        bug_names = list(cursor.execute(
            """SELECT name FROM bugs
            WHERE name LIKE 'DSA-%' AND NOT not_for_us"""))
        DSA_status = {}
        if self.verbose:
            print "  rating DSAs"
        for (bug_name,) in bug_names:
            b = bugs.BugFromDB(cursor, bug_name)
            DSA_status[bug_name] = calcVuln(b)
            
        # Process the CAN/CVE/FAKE entries.  If an entry has no
        # package annotations, but it references a non-vulnerable DSA,
        # we assume that the current is not affect either.

        if self.verbose:
            print "  reading other entries"
        bug_names = list(cursor.execute(
            """SELECT name FROM bugs
            WHERE (NOT not_for_us)
            AND NOT (name LIKE 'DSA-%' OR name LIKE 'DTSA-%')"""))
        if self.verbose:
            print "  rating other entries"
        for (bug_name,) in bug_names:
            b = bugs.BugFromDB(cursor, bug_name)
            if b.notes:
                calcVuln(b)
                continue

            if b.hasTODO():
                markVulnerable(b, 'etch', 0, 'TODO items present')
                continue
                
            dsa_found = False
            for x in b.xref:
                if x[0:4] == 'DSA-':
                    dsa_found = True
                    if DSA_status[x]:
                        markVulnerable(b, 'etch', 0,
                                       'vulnerability %s referenced' % x)
                        break
            if not dsa_found:
                markVulnerable(b, 'etch', 0, 'status is unclear')
                
        if self.verbose:
            print "  finished"

        return result

    def check(self, cursor=None):
        """Runs a simple consistency check and prints the results."""

        if cursor is None:
            cursor = self.cursor()

        for (package, release, archive, architecture, source) in\
            cursor.execute(
            """SELECT package, release, archive, architecture, source
            FROM binary_packages
            WHERE NOT EXISTS
            (SELECT *
                    FROM source_packages AS sp
                    WHERE sp.package = binary_packages.source
                    AND sp.release = binary_packages.release
                    AND sp.archive = binary_packages.archive)
            """):
            print "error: binary package without source package"
            print "  binary package:", package
            print "  release:", release
            if archive:
                print "  archive:", archive
            print "  architecture:", architecture
            print "  missing source package:", source

        for (package, release, archive, architecture, version,
             source, source_version) \
            in cursor.execute("""SELECT binary_packages.package,
            binary_packages.release, binary_packages.archive,
            binary_packages.architecture,binary_packages.version,
            sp.package, sp.version
            FROM binary_packages, source_packages AS sp
            WHERE sp.package = binary_packages.source
            AND sp.release = binary_packages.release
            AND sp.archive = binary_packages.archive
            AND sp.version <> binary_packages.source_version"""):
            relation = cmp(debian_support.Version(version),
                           debian_support.Version(source_version))
            assert relation <> 0
            if relation <= 0:
                print "error: binary package is older than source package"
            else:
                print "warning: binary package is newer than source package"
            print "  binary package: %s (%s)" % (package, version)
            print "  source package: %s (%s)" % (source, source_version)
            print "  release:", release
            if archive:
                print "  archive:", archive
            print "  architecture:", architecture
            
def test():
    assert mergeLists('', '') == [], mergeLists('', '')
    assert mergeLists('', []) == []
    assert mergeLists('a', 'a') == ['a']
    assert mergeLists('a', 'b') == ['a', 'b']
    assert mergeLists('a,c', 'b') == ['a', 'b', 'c']
    assert mergeLists('a,c', ['b', 'de']) == ['a', 'b', 'c', 'de']

    import os
    db_file = 'test_security.db'
    try:
        db = DB(db_file)
    except SchemaMismatch:
        os.unlink(db_file)
        db = DB(db_file)

    cursor = db.writeTxn()
    db.readBugs(cursor, '../../data')
    db.commit(cursor)

    b = bugs.BugFromDB(cursor, 'CAN-2005-2491')
    assert b.name == 'CAN-2005-2491', b.name
    assert b.description == 'Integer overflow in pcre_compile.c in Perl Compatible Regular ...', b.description
    assert len(b.xref) == 2, b.xref
    assert not b.not_for_us
    assert 'DSA-800-1' in b.xref, b.xref
    assert 'DTSA-10-1' in b.xref, b.xref
    assert tuple(b.comments) == (('NOTE', 'gnumeric/goffice includes one as well; according to upstream not exploitable in gnumeric,'),
                                 ('NOTE', 'new copy will be included any way')),\
                                 b.comments

    assert len(b.notes) == 4, len(b.notes)
    
    for n in b.notes:
        assert n.release is None
        if n.package == 'pcre3':
            assert n.fixed_version == debian_support.Version('6.3-0.1etch1')
            assert tuple(n.bugs) == (324531,), n.bugs
            assert n.urgency == bugs.internUrgency('medium')
        elif n.package == 'python2.1':
            assert n.fixed_version == debian_support.Version('2.1.3dfsg-3')
            assert len(n.bugs) == 0, n.bugs
            assert n.urgency == bugs.internUrgency('medium')
        elif n.package == 'python2.2':
            assert n.fixed_version == debian_support.Version('2.2.3dfsg-4')
            assert len(n.bugs) == 0, n.bugs
            assert n.urgency == bugs.internUrgency('medium')
        elif n.package == 'python2.3':
            assert n.fixed_version == debian_support.Version('2.3.5-8')
            assert len(n.bugs) == 0, n.bugs
            assert n.urgency == bugs.internUrgency('medium')
        else:
            assert False

    assert bugs.BugFromDB(cursor, 'DSA-311').isKernelOnly()

if __name__ == "__main__":
    test()