Re: enums

On Thu, Oct 27, 2005 at 09:45:05PM -0400, Andrew Dunstan wrote:
> 
> 
> Jim C. Nasby wrote:
> 
> >Andrew, you mentioned that if you want to change the ordering you should
> >just create a new type. What about if you need to change the values that
> >are in the enum? MySQL does (or at least did, it's been some time since
> >I've messed with this) a horrible job at that. There's no way to rename
> >anything; you have to add the new names you want, then do a bulk update,
> >then delete the (now old) names. IMO this is broken.
> > 
> >
> 
> 
> It would just be a standard "ALTER TABLE foo ALTER COLUMN bar TYPE 
> newtype USING expression" operation. You would write a function that 
> took a value of the old type and returned a value of the new type and 
> use a cll to that function in the expression. Since these would be named 
> types, unlike the case in mysql where they are anonymously defined 
> inline, this would present no difficulties at all.

But why force a re-write of the entire table just to change the name of
something?

Or is ALTER COLUMN TYPE smart enough to not touch anything if the
mapping function is equality?

> >Also, if we are going to maintain ordering and mapping (presumably via
> >the internal number that we're storing), then I think we should expose
> >that, at least optionally. So for example, you should be able to define
> >what a specific enum value means. Not everyone will want a linear
> >numbering starting at 0 afterall.
> > 
> >
> 
> What on earth for? Users should not care in the slightest what the 
> internal representation is . Users who want a map where the values are 
> exposed should create a lookup table.
> 
> You keep saying that we are using the internal representation as the 
> ordering. This is simply the wrong way to look at it. The internal 
> representation REFLECTS the ordering; it doesn't impose it. The user has 
> imposed the ordering when defining the type. In my enumkit I did provide 
> a function that gave back the internal representation, but I am not by 
> any means certain that that's a good idea.

Well, someone was arguing that enum should be used as a convenient way
to map human labels on a set of values. To me, that means you should be
able to define exactly what that set of values is.

Personally, I don't see why enum can't just be syntactic sugar on top of
a side-table of values and a foreign key. And I guess a view to hide the
internals from normal viewing. That would certainly allow the most
flexibility, although it probably wouldn't perform as well as what you
wrote.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461