On Tue, 28 Jun 2011, Romain d'Alverny wrote: > Hi, > > On Tue, Jun 28, 2011 at 15:34, Samuel Verschelde <sto...@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.