Date: Thu, 26 Aug 2004 18:59:05 -0400 From: "Dan Langille" <dan@langille.org> To: Jacques Vidrine <nectar@FreeBSD.org> Cc: freebsd-vuxml@freebsd.org Subject: Re: database tables for VuXML Message-ID: <412E32F9.17168.57CEB1D@localhost> In-Reply-To: <412B7D29.31547.1472F80@localhost> References: <20040822192915.GA17478@madman.celabo.org>
next in thread | previous in thread | raw e-mail | index | archive | help
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/
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?412E32F9.17168.57CEB1D>