Date: Sat, 21 Aug 2004 19:11:21 -0400 From: "Dan Langille" <dan@langille.org> To: ports@freebsd.org Cc: nectar@FreeBSD.org Subject: database tables for VuXML Message-ID: <41279E59.13631.76AD2AB9@localhost>
next in thread | raw e-mail | index | archive | help
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/
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?41279E59.13631.76AD2AB9>