database tables for VuXML

[Dan Langille]

Hi folks,

I'm in the early stage of incorporating VuXML information into 
FreshPorts.  I have a preliminary database table design which appears 
below.  If you think I've missed any essential information out of 
this design, please let me know.

First: has anyone already worked on creating database tables for the 
VuXML data?

create table vuxml
(
    id              serial                not null,
-- internal FreshPorts ID
    vid             text                  not null,
-- the vuln vid. e.g. c4b025bb-f05d-11d8-9837-000c41e2cdad
    topic           text                  not null,
    description     text                  not null,
    date_discovery  date                          ,
    date_entry      date                          ,
    date_modified   date                          ,
    status          date                  not null,
    primary key (id)
);

create table vuxml
(
    id                    serial                not null,
    vid                   text                  not null,
    topic                 text                  not null,
    description           text                  not null,
    date_discovery        date                          ,
    date_entry            date                          ,
    date_modified         date                          ,
    status                date                  not null,
    primary key (id)
);

create table vuxml_port_xref
(
    id                    serial                not null,
    vuxml_id              integer               not null,
    port_id               integer               not null,
    primary key (id)
);

create table vuxml_port_xref_range
(
    id                    serial                not null,
    vuxml_port_xref_id    text                          ,
    range_operator_start  text                          ,
    range_operator_end    text                          ,
    range_version_start   text                          ,
    range_version_end     text                          ,
    primary key (id)
);

If you consider the output from: vxquery -t vuxml ~/VuXML/vuln.xml 
tnftpd, the entry in vuxml_port_xref_range for tnftpd might be:

(1, 'lt', 20040810, NULL, NULL)

alter table vuxml_port_xref
    add foreign key  (vuxml_id)
       references vuxml (id) on update cascade on delete cascade;

alter table vuxml_port_xref_range
    add foreign key  (vuxml_port_xref_id)
  references vuxml_port_xref (id) on update cascade on delete 
cascade;


-- 
Dan Langille : http://www.langille.org/