From owner-freebsd-ports@FreeBSD.ORG  Sat Aug 21 23:11:22 2004
Return-Path: <owner-freebsd-ports@FreeBSD.ORG>
Delivered-To: freebsd-ports@freebsd.org
Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125])
	by hub.freebsd.org (Postfix) with ESMTP
	id D912C16A4D9; Sat, 21 Aug 2004 23:11:22 +0000 (GMT)
Received: from bast.unixathome.org (bast.unixathome.org [66.11.174.150])
	by mx1.FreeBSD.org (Postfix) with ESMTP
	id A427743D49; Sat, 21 Aug 2004 23:11:22 +0000 (GMT)
	(envelope-from dan@langille.org)
Received: from wocker (wocker.unixathome.org [192.168.0.99])
	by bast.unixathome.org (Postfix) with ESMTP
	id B7A6E3D3D; Sat, 21 Aug 2004 19:11:21 -0400 (EDT)
From: "Dan Langille" <dan@langille.org>
To: ports@freebsd.org
Date: Sat, 21 Aug 2004 19:11:21 -0400
MIME-Version: 1.0
Message-ID: <41279E59.13631.76AD2AB9@localhost>
Priority: normal
X-mailer: Pegasus Mail for Windows (v4.12a)
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
cc: nectar@FreeBSD.org
Subject: database tables for VuXML
X-BeenThere: freebsd-ports@freebsd.org
X-Mailman-Version: 2.1.1
Precedence: list
List-Id: Porting software to FreeBSD <freebsd-ports.freebsd.org>
List-Unsubscribe: <http://lists.freebsd.org/mailman/listinfo/freebsd-ports>,
	<mailto:freebsd-ports-request@freebsd.org?subject=unsubscribe>
List-Archive: <http://lists.freebsd.org/pipermail/freebsd-ports>
List-Post: <mailto:freebsd-ports@freebsd.org>
List-Help: <mailto:freebsd-ports-request@freebsd.org?subject=help>
List-Subscribe: <http://lists.freebsd.org/mailman/listinfo/freebsd-ports>,
	<mailto:freebsd-ports-request@freebsd.org?subject=subscribe>
X-List-Received-Date: Sat, 21 Aug 2004 23:11:23 -0000

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/