Schema reorganization for package_notes table

[Florian Weimer <fw@deneb.enyo.de>]

The package_notes table currently looks like this:

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,
         package_kind TEXT NOT NULL DEFAULT 'unknown',
         urgency TEXT NOT NULL,
         bug_origin TEXT NOT NULL DEFAULT '');

I plan to change it to something like this:

CREATE TABLE package_notes
        (id INTEGER NOT NULL PRIMARY KEY,
         bug_name TEXT NOT NULL,
         tag TEXT NOT NULL,
         freetext TEXT,
         package TEXT NOT NULL,
         fixed_version TEXT,
         release TEXT,
         urgency TEXT,
         bug_filed INTEGER NOT NULL DEFAULT 0);

fixed_version_id is an implementation detail of the approach to
version ordering; I may have to keep it until other reorganizations
are done.  Same for bug_origin.

The significant change is the addition of the "tag" column.  I think
we'll need the following values:

      plain
      itp
      removed
      undetermined
      no-dsa
      not-affected
      end-of-life

'plain' combines fixed and unfixed package annotations, depending on
the fixed_version column.  The other tags correspond to the
pseudo-versions inside <...>.

I'm not yet sure if the 'no-dsa' tag makes the existing
package_notes_nodsa table superfluous, but it looks like it is
possible to replace it with a view.

fixed_version can now be empty, replacing the artifical '0' marker
which is used for encoding <not-affected> at present.

"freetext" records the free-form text in the (...) part, something
that we currently emit as NOTEs.

"bug_filed" indicates whether there was a "bug filed" marker in the
(...) part.  (It's actually a boolean column, but SQLite doesn't
support that directly.)  The actual bug numbers are still supplied by
the package_notes_bug table.

On the Python side, much the subclassing around the PackageNote class
in the bugs module will go, and only a PackageNote and a StringNode
will remain, probably as named tuples.  Data parsed from the file and
loaded from the database will be represented identically.

My hope is that with the explicitly encoded tags, it will be possible
to make the per-bug/package vulnerability decision logic more
explicit.  We currently have three or four different implementations,
both in SQLite statements and in Python, and this keeps causing
problems.  If the new implementation is fast enough, it will be
possible to avoid pre-computing all the vulnerability information,
which should speed up database updates from Subversion.

Any comments?  Are there any other users of the SQL schema besides the
web application?