[Mageia-dev] Mageia Advisories Database

Tue Jun 28 16:49:46 CEST 2011

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.
+
+