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