FreshPorts - VuXML tables - DDL + data

[Jacques A. Vidrine]

On Fri, Aug 27, 2004 at 07:07:07AM -0400, Dan Langille wrote:
> Here is the DDL I created this morning.  Note that some slight changes
> have been made relative to the diagram I posted last night.  The diagram
> is now out of date.
> 
> The rest of this message contains a sample vuxml entry followed by the DDL
> to create the tables and populate it with a single VuXML entry.  It also
> shows how I mark which packages are affected by a given vid (i.e. how I
> populate the commit_log_ports_vuxml table).
> 
> I have created the code to display VuXML information, but at present, the
> only data loaded is for http://beta.freshports.org/ftp/tnftpd/

Good work!

> NOTE: I'm assuming there can be multiple VuXML entries for a given version
> of a package/port.  However, there will be only one VuXML icon shown per
> row of Commit History.  Look at the last commit on the page for a commit
> which is affected by two VuXML entries (this is achieved with a fake VuXML
> entry).

Yep, that's a correct assumption.

> freshports.org=# select id, vid, topic from vuxml;
>  id |                 vid                  |                    topic
> ----+--------------------------------------+----------------------------------------------
>   1 | c4b025bb-f05d-11d8-9837-000c41e2cdad | tnftpd -- remotely exploitable vulnerability
> (1 row)
[...snip...]

This is all looking pretty good to me.  When the beta is up, I'll be
happy to keep an eye on the results for anything unusual.

By the way-- a bit off-topic from the DBA stuff-- when processing the
FreeBSD VuXML document, you may want to insert a validation step between
fetching it and loading it into the database.  This will catch problems
like bad commits or truncated transfers-- cases in which you probably do
not want to use the results for updating the database.

See ports/security/vuxml/files/validate.sh for a simple example of using
xmllint or onsgmls for validation.

Cheers,
-- 
Jacques Vidrine / nectar at celabo.org / jvidrine at verio.net / nectar at freebsd.org