database tables for VuXML

[Jacques A. Vidrine]

[Added freebsd-vuxml@]

On Sat, Aug 21, 2004 at 07:11:21PM -0400, Dan Langille wrote:
> Hi folks,
>
> I'm in the early stage of incorporating VuXML information into
> FreshPorts.

Alright!  That'll be cool.

> 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?

I have an old prototype application which uses SQLite.  FWIW, here is
the schema I used then. (the formatting is a little funny because this
was embedded in some other code)

        CREATE TABLE vuxml (
              vid          VARCHAR PRIMARY KEY UNIQUE
           ,  topic        VARCHAR
           ,  description  VARCHAR
           ,  discovery    DATE
           ,  entry        DATE
           ,  modified     DATE
	   -- computed max(entry, modified) for convenience in
	   -- other expressions
           ,  last         DATE
           );

        CREATE TABLE refs (
             vid           VARCHAR
           , type          VARCHAR
           , text          VARCHAR
           );

        CREATE TABLE affected (
              vid          VARCHAR
           ,  type         VARCHAR
           );

        CREATE TABLE names (
              affected     INTEGER
           ,  name         VARCHAR
           );

        CREATE TABLE ranges (
              affected     INTEGER
           ,  lowop        VARCHAR
           ,  low          VARCHAR
           ,  glow0        INTEGER
           ,  glow1        INTEGER
           ,  glow2        INTEGER
           ,  glow3        INTEGER
           ,  highop       VARCHAR
           ,  high         VARCHAR
           ,  ghigh0       INTEGER
           ,  ghigh1       INTEGER
           ,  ghigh2       INTEGER
           ,  ghigh3       INTEGER
           );

The `ranges' table perhaps looks erm interesting.  In this case,
I encoded each package version number into four 32-bit integers.
This allowed one to lookup an affected package completely in SQL.
However, the encoding I used may now be incorrect in some ways, since
pkg_install et al were recently changed to treat version numbers
differently.  Also, SQLite could not really optimize the complex SQL
statement that resulted.  So I think it is interesting for further
study, but I doubt I'd do it that way again unless I had a very
particular need to use pure SQL or the database was huge.

> 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_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)

Seem reasonable.  For what its worth, storing the range operators as
`>', `=>', etc lets you get something human readable as the output of a
SELECT, e.g.

   SELECT range_version_start, range_operator_start, port_name,
          range_operator_end, range_version_end FROM ... WHERE ...;

      ('20040810', '<', 'tnftpd', '', '')

I found that handy for manual inspection of the database.


You may notice from my example that I used separate `name', `ranges',
and `affected' tables.  I found this easier when filling the database,
since the <package>/<system> elements are kind of a cross-product
operator.  For example, when processing something like

      <package>
	<name>mutt</name>
	<name>ja-mutt</name>
	<range><ge>1.4</ge><lt>1.4.2</lt></range>
      </package>

I first created an `affected' entry to contain the other references.
Then, as I hit each `name' I just associated it with the `affected'
entry, and as I hit each `range' I did the same.  (Otherwise one
needs to construct a few lists, and then make all entries once
the </package> end tag is seen.)  I think this is also easier to
modify if we get new children of <package>, e.g. the <category> and
<architecture> tags that are currently in the VuXML pre-1.2 DTD.

Have fun, and please let me know if I can assist!

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