database tables for VuXML

[Jacques Vidrine]

On Aug 24, 2004, at 4:38 PM, Dan Langille wrote:
[...]
> What is this type field?  I didn't follow what the affected table
> contains.

Type is either `package' or `system'.  You are probably concerned only 
with the former.
[...]
> FreshPorts stores PORTVERSION and PORTREVISION as separate text
> fields.  I take it that the ranges equations should compare only
> PORTVERSION and ignore PORTREVISION.

One can think a version number as a tuple as follows (most significant 
first):  (PORTEPOCH, PORTVERSION, PORTREVISION).  Comparisons are done 
on these tuples.

> SQLite is a great little database.  I like what I've seen of it
> (mostly via my work on Bacula).  I'll be using PostgreSQL for this.

Yeah, SQLite is great for single-user things.  I especially like the 
way it is so easy to add SQL functions from C.
>
[...]
> My plan is to populate empty the vuxml_* tables each time there is a
> commit to the data file.  This keeps the vuxml system totally
> separate from FreshPorts.
>
> Each row under Commit History (e.g.
> http://beta.freshports.org/sysutils/bacula/) relates to a row from
> the commit_log_ports table.  That looks something like this:
>
> create table commit_log_ports
> (
>     commit_log_id      integer               not null,
>     port_id            integer               not null,
>     needs_refresh      smallint              not null,
>     port_version       text                          ,
>     port_revision      text                          ,
>     primary key (commit_log_id, port_id)
> );

You'll need a `port_epoch' here as well.  So these get created as 
commits "come in"?

> I'll probably create another table commit_log_ports_vuxml:
>
> create table commit_log_ports
> (
>     commit_log_id      integer               not null,
>     port_id            integer               not null,
>     vuxml_id         integer     not null                 ,
>     primary key (commit_log_id, port_id)
> );
>
> Looking at the current data, there's about 140 affected ports, but I
> haven't broken that with respect to ranges, which is what the above
> will do.
>
> I'll either do that, or add the vuxml_id column to the
> commit_log_ports table, but I'd rather keep it separate.  Time will
> tell.

Seems like it'll work to me :-)

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