database tables for VuXML

[Dan Langille]

I am now finalizing my tables.  I have some comments below and some 
questions about your tables.

On 24 Aug 2004 at 17:38, Dan Langille wrote:

> On 22 Aug 2004 at 14:29, Jacques A. Vidrine wrote:
> 
> > [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
> >            );

I'm not going to have a last column.  And I am using TEXT, not DATE, 
as the values are not always dates.  I recall reading someone 
suggesting allowing unknown.  If the date is unknown, I suggest 
leaving this field empty.

If the suggestion above is accepted, I will reconsider my DATE versus 
TEXT decision as I could then use a NULL value.

> This is pretty much identical to what I have.
> 
> > 
> >         CREATE TABLE refs (
> >              vid           VARCHAR
> >            , type          VARCHAR
> >            , text          VARCHAR
> >            );

What is this type?  I fear I have asked this already.  I have added 
the table but without the type field.

> Ahh, yes, that repeating group.  I'm not storing that.  I should be
> 
> >         CREATE TABLE affected (
> >               vid          VARCHAR
> >            ,  type         VARCHAR
> >            );
> 
> What is this type field?  I didn't follow what the affected table 
> contains.

Ahh, type is either package or system.

e.g.

      <package>
        <name>tnftpd</name>
        <range><lt>20040810</lt></range>
      </package>
      <package>
        <name>lukemftpd</name>
        <range><ge>0</ge></range>
      </package>
      <system>
        <name>FreeBSD</name>
        <range><ge>4.7</ge></range>
      </system>

> >         CREATE TABLE names (
> >               affected     INTEGER
> >            ,  name         VARCHAR
> >            );

This I now understand. It's a list of the affected packages, or 
systems.  For packages, it appears to be PORTNAME.  Is that true?

I see now why you have names and affect separate:

      <package>
        <name>gaim</name>
        <name>ja-gaim</name>
        <range><lt>0.81_1</lt></range>
      </package>

A given package may have more than one name (e.g. slave ports).

> >From your example below, the names table would have entries for mutt 
> and ja-mutt.  I think I'll do that as well.  See the end of my 
> message for how I plan to relate VuXML entries to specific ports.
> 
> >         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.  

Each package has a list of 1 or more ranges which are affected.  The 
ranges affect all names listed in the package.

e.g.:

      <package>
        <name>samba</name>
        <range><ge>3</ge><lt>3.0.5,1</lt></range>
        <range><lt>2.2.10</lt></range>
      </package>

Thanks.  This is coming together and I'm getting closer to having 
tables.
-- 
Dan Langille : http://www.langille.org/