Date: Wed, 25 Aug 2004 13:58:51 -0500 From: Jacques Vidrine <nectar@FreeBSD.org> To: "Dan Langille" <dan@langille.org> Cc: freebsd-vuxml@freebsd.org Subject: Re: database tables for VuXML Message-ID: <CDBB0430-F6C8-11D8-9236-000A95BC6FAE@FreeBSD.org> In-Reply-To: <412B7D29.31547.1472F80@localhost> References: <41279E59.13631.76AD2AB9@localhost> <412B7D29.31547.1472F80@localhost>
next in thread | previous in thread | raw e-mail | index | archive | help
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@celabo.org / jvidrine@verio.net / nectar@freebsd.org
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?CDBB0430-F6C8-11D8-9236-000A95BC6FAE>