[Mageia-dev] Mageia Advisories Database

Tue Jun 28 15:50:05 CEST 2011

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
+