Re: Schema reorganization for package_notes table

[Moritz Mühlenhoff <jmm@inutil.org>]

On Sun, Feb 02, 2014 at 10:15:42PM +0100, Florian Weimer wrote:
> 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?  

Looks good to me from a high level view.

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

Helmut was planning to integrate the security tracker data into UDD, adding
him to CC.

Cheers,
        Moritz