From owner-freebsd-vuxml@FreeBSD.ORG Fri Aug 27 14:04:26 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 0E5B716A4CE for ; Fri, 27 Aug 2004 14:04:26 +0000 (GMT) Received: from gw.celabo.org (gw.celabo.org [208.42.49.153]) by mx1.FreeBSD.org (Postfix) with ESMTP id 8153C43D76 for ; Fri, 27 Aug 2004 14:04:25 +0000 (GMT) (envelope-from nectar@celabo.org) Received: from localhost (localhost [127.0.0.1]) by gw.celabo.org (Postfix) with ESMTP id 0845654861; Fri, 27 Aug 2004 09:04:25 -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 70510-04; Fri, 27 Aug 2004 09:04:13 -0500 (CDT) Received: from madman.celabo.org (madman.celabo.org [10.0.1.111]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "madman.celabo.org", Issuer "celabo.org CA" (not verified)) by gw.celabo.org (Postfix) with ESMTP id D2E425485D; Fri, 27 Aug 2004 09:04:13 -0500 (CDT) Received: by madman.celabo.org (Postfix, from userid 1001) id 8262B6D468; Fri, 27 Aug 2004 09:04:04 -0500 (CDT) Date: Fri, 27 Aug 2004 09:04:04 -0500 From: "Jacques A. Vidrine" To: Dan Langille Message-ID: <20040827140404.GE54027@madman.celabo.org> Mail-Followup-To: "Jacques A. Vidrine" , Dan Langille , freebsd-vuxml@freebsd.org References: <20040822192915.GA17478@madman.celabo.org> <412E32F9.17168.57CEB1D@localhost> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <412E32F9.17168.57CEB1D@localhost> X-Url: http://www.celabo.org/ User-Agent: Mutt/1.5.6i 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: Fri, 27 Aug 2004 14:04:26 -0000 On Thu, Aug 26, 2004 at 06:59:05PM -0400, Dan Langille wrote: > I am now finalizing my tables. I have some comments below and some > questions about your tables. [...] > 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. Actually, with SQLite, all columns are really strings even if one specifies other SQL types :-) Oliver requested that `unknown' be a valid value. The only other valid format is YYYY-MM-DD. > If the suggestion above is accepted, I will reconsider my DATE versus > TEXT decision as I could then use a NULL value. Yes, you should expect that `unknown' dates are generated by Oliver or his perl script. They should all be transient values: even though such entries will technically be acceptable, they are morally bugs. :-) > > > > > > 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. This is a different type than the other :-) References are (type, text) tuples, where the type can be "url", "cvename", "uscertta", "mlist", and so forth. The meaning of the text of course depends upon the type. The list of types is constrained by the VuXML DTD, but VuXML processing tools should handle any unrecognized type generically. > > > 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. > > > tnftpd > 20040810 > > > lukemftpd > 0 > > > FreeBSD > 4.7 > Yes, the type column in *this* table is "package" or "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? Almost. In FreeBSD ports collection terms, the element contains the PKGNAME minus the version, i.e. ${PKGNAMEPREFIX}${PORTNAME}${PKGNAMESUFFIX}. > I see now why you have names and affect separate: > > > gaim > ja-gaim > 0.81_1 > > > A given package may have more than one name (e.g. slave ports). Right. Another example is compile time options, e.g. "vim", "vim-lite". [...] > Each package has a list of 1 or more ranges which are affected. The > ranges affect all names listed in the package. > > e.g.: > > > samba > 33.0.5,1 > 2.2.10 > You got it. > Thanks. This is coming together and I'm getting closer to having > tables. Cheers, -- Jacques Vidrine / nectar@celabo.org / jvidrine@verio.net / nectar@freebsd.org