[Mageia-dev] Mageia Advisories Database

[Romain d'Alverny]

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

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?
 - what screens do you expect?
 - are there several CVE numbers for a single advisory?
 - is there a link from source packages and binary packages?

Romain