Re: Proposed schema for the backend database for Nessus

On Tuesday 18 February 2003 13:36, Javier Fernandez-Sanguino wrote:
<Lots of stuff in response to lots of stuff>
> 	Have I made my point? Are you convinced now? :-)
>
> 	Javi
Convinced indeed.  Hope I didn't burn too many of peoples' cycles on this 
debate.

I've taken your original proposal and added most of the elements from the 
TODO.txt in CVS NESSUS_SQL.  I've also added a lot of NOT NULL statements 
where they seem appropriate (to me at least).  It should hopefully be 
segfault-safe for everyone:  I know some people had problems opening it up on 
their systems due to wierd fonts.  I've changed all the fonts to Courier, so 
hopefully that fixes that.

The dia diagram, pgsql and mysql output from dia2sqlpy and change notes are 
all here:
http://mywebpages.comcast.net/earnoth/proposed_nessus_db_schema.v4.tgz
***Please note that if you generate the mysql output with dia2sqlpy yourself, 
this will FAIL.  I used boolean datatypes, and as yet, dia2sqlpy does not yet 
handle the datatype nuances between the two DBMSes.  I hope to add such 
support in this weekend (it'll be easy but tedious) but until then, the MySQL 
output from my script won't work until you substutited BOOL where BOOLEAN is 
(vi command - :%s/BOOLEAN/BOOL/  ;-)  After doing that, it will work.

One question I want to put up for public deba^H^H^H^Hdiscussion ;-)  is the 
question of ACID compliance:  foreign key relationships, transactions, etc.  
Being a postgresql fanatic, I feel very strongly that these concepts should 
be implemented in the Nessus code.  Indeed, the database design that's being 
hammered out is very ACIDlike  

For MySQL, however, there are some issues.  With InnoDB, MySQL can have all of 
this (according to the website [1]).  However, I expect that most people have 
not installed thier production databases with InnoDB compiled in.  As well, 
the MySQL philosophy seems to speak against foriegn keys (and thus 
transactions)[2] 

While it would be possible for the C code that supports PostgreSQL to support 
ACID while keeping the MySQL nonACID, it might make this development effort 
rather complicated.  Not to mention the other vendor databases which Javier 
eluded to (Oracle, Sybase) with his email about ./configure flags, all of 
which are very ACID.  

Please note that I am *NOT* trying to start a flame war about database 
religion or any such thing.  I have complete respect for MySQL's way of doing 
things and the reasons for their choices.  However, this does not change the 
engineering issues presented by supporting two differing philosophies (ACID & 
nonACID compliance).  My intention is only to head off a conundrum which, I 
expect, will be easier to solve at this early stage, while the C code is 
still very nascent and the SQL schema is not yet bronzed.  


[1]http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#InnoDB
[2]http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html#example-Foreign_keys

-- 
Eric I. Arnoth    CISSP (http://www.isc2.org)        
[email protected]                
http://mywebpages.comcast.net/earnoth
¤ø,¸¸,ø¤º°*°º¤ø,¸¸,ø¤ø,¸¸,ø¤º°*°º¤ø,¸¸,ø¤ø,¸¸,ø¤º°*°º¤ø,¸¸,ø¤ø,¸¸,ø¤º°*°º¤ø,¸¸,ø