Hi, On Sonntag, 2. Februar 2014, 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); I wonder whether this should have a subrelease column too. > 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 /me likes very much. "end-of-life" is really not an urgency... > '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. I think it's also fine to change the schemas in steps. > 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. fine > "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. yeah, the code needs some refactoring. I've gotten to know it quite well by now, so that I'm getting more confident doing larger rewrites... > Any comments? Are there any other users of the SQL schema besides the > web application? I've just said on irc, but in a wrong channel: [08:45] < h01ger> | have improved the -backports code but there is a fundamental problem: data/CVE/lists tracks [wheezy] and [squeeze] manually, this isnt done for bpo, thus version/fixed tracking is kind of harder. the BTS gets mails about fixes in specific versions, the security-tracker doesnt [08:47] < h01ger> | i've pushed what i have to collab-maint branch h01ger, not sure how much time i will have on the weekend. but i want this done and working soon 8-) [08:48] < h01ger> | (feel free to ignore it and wait for "final" commits. eit if you're curious, i'm glad to hear feedback :) [08:49] < h01ger> | also the /tracker/status/releases/$NAME pages are redone in my branch, they now feature new shiny checkboxes [08:49] < h01ger> | but you'll need to recreate the .db to use/see all the changes properly As you'll see in the commit message some will have "WIP" (work in progress) in them... cheers, Holger
Attachment:
signature.asc
Description: This is a digitally signed message part.