From owner-freebsd-vuxml@FreeBSD.ORG Wed Aug 25 18:59:13 2004 Return-Path: Delivered-To: freebsd-vuxml@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id D215A16A4DA; Wed, 25 Aug 2004 18:59:13 +0000 (GMT) Received: from gw.celabo.org (gw.celabo.org [208.42.49.153]) by mx1.FreeBSD.org (Postfix) with ESMTP id 7D64F43D66; Wed, 25 Aug 2004 18:59:13 +0000 (GMT) (envelope-from nectar@FreeBSD.org) Received: from localhost (localhost [127.0.0.1]) by gw.celabo.org (Postfix) with ESMTP id C8ED654861; Wed, 25 Aug 2004 13:59:11 -0500 (CDT) Received: from gw.celabo.org ([127.0.0.1]) by localhost (hellblazer.celabo.org [127.0.0.1]) (amavisd-new, port 10024) with SMTP id 48900-10; Wed, 25 Aug 2004 13:59:01 -0500 (CDT) Received: from [10.0.1.107] (lum.celabo.org [10.0.1.107]) (using TLSv1 with cipher RC4-SHA (128/128 bits)) (Client did not present a certificate) by gw.celabo.org (Postfix) with ESMTP id 13EF45485D; Wed, 25 Aug 2004 13:59:01 -0500 (CDT) In-Reply-To: <412B7D29.31547.1472F80@localhost> References: <41279E59.13631.76AD2AB9@localhost> <412B7D29.31547.1472F80@localhost> Mime-Version: 1.0 (Apple Message framework v619) Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: Content-Transfer-Encoding: 7bit From: Jacques Vidrine Date: Wed, 25 Aug 2004 13:58:51 -0500 To: "Dan Langille" X-Mailer: Apple Mail (2.619) cc: ports@freebsd.org cc: freebsd-vuxml@freebsd.org Subject: Re: database tables for VuXML X-BeenThere: freebsd-vuxml@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Documenting security issues in VuXML List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 25 Aug 2004 18:59:14 -0000 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