[Mageia-dev] Mageia Advisories Database

[nicolas vigier]

On Tue, 28 Jun 2011, Romain d'Alverny wrote:

> Hi,
> 
> On Tue, Jun 28, 2011 at 15:34, Samuel Verschelde <stormi at laposte.net> wrote:
> > Le mardi 28 juin 2011 15:20:33, nicolas vigier a écrit :
> >> In order to send updates advisories, and have a web page listing all
> >> previous advisories, we need to create a database to store them.
> >>
> >> So I think it should have the following info for each advisory :
> >>
> >>  - advisory ID: something like MGA-[NUMBER] ?
> >>  - advisory date
> >>  - affected source packages
> >>  - affected distribution versions
> >>  - CVE numbers
> >>  - list of binary packages with sha1sum
> >>  - Mageia Bug #
> >>  - Reference URLs
> >>  - advisory text
> >>
> >> Anything else ?
> 
> If using SQL, make sure to normalize the db schema a bit (that is, for
> instance, an advisory table, with a distributions table, and a
> relationship). MDV security advisory web app had a single table, with
> new columns added each time a new release was published and that was
> really not good, neither safe to maintain.
> 
> In this perspective, there could be the following tables:
>  - advisories (id, date, text, list of URLs, list of bug #)
>  - distributions (id, name)
>  - source packages (id, name, version)
>  - CVE numbers

I am thinking about the following tables :

 - advisories : id, published, publish-date, update-date, text, severity
 - source-packages : packagename, filename, sha1, distribution, repository, version, advisory-id
 - binary-packages : packagename, filename, sha1, source-package-id
 - cve-numbers : cve-number, advisory-id
 - bugzilla-numbers : bugzilla-number, advisory-id
 - reference-urls : url, advisory-id

> 
> Not sure about the rest; depends on the data details and what type of
> queries would be expected:
>  - do we only query after the advisory id or do we plan to have stats
> per distribution, source package?

We can query by advisory id, source package, cve number, bugzilla
number. And we can do stats.

>  - what screens do you expect?
>  - are there several CVE numbers for a single advisory?

Yes. We can have several CVE numbers, source packages, bugzilla numbers,
URLs, distributions, for one advisory.

>  - is there a link from source packages and binary packages?

Yes.