forgot to send to the list...
---------- Forwarded message ---------- From: Roland Bouman <[EMAIL PROTECTED]> Date: Mon, Oct 13, 2008 at 7:53 PM Subject: Re: [Drizzle-discuss] VARBINARY To: [EMAIL PROTECTED] Hi Andrey, First, allow me to congratulate you with your work on virtual columns! I think virtual columns are a cool and useful feature (I prefer the term 'computed' or 'derived' columns though). I agree that virtual columns could help to solve the problem with phone numbers. However, the drawback is that the application developer has to be aware of the implementation to deal with it: they need to understand that they need to use different columns for displaying and comparing. Although that may not be a big problem, it would be totally absent when the data type deals with this. In addition, the user-defined type would allow solving other domain specific problems () So, my opinion is that both are useful features, largely with their own scope and application, although that in practice you may benefit from either feature to emulate aspects of the other one. kind regards, Roland. On Mon, Oct 13, 2008 at 7:38 PM, Andrey Zhakov <[EMAIL PROTECTED]> wrote: > Hi Roland, > > Here is where I could try to sell out my virtual columns ;) > > A server-based solution to your specific example with phone numbers might be > having a column with the formatting you want to retain, and having an > indexed virtual column defined like this: > "<2nd_col_name> VIRTUAL INT AS (RIGHT(REPLACE(<1st_col_name>,' ',''),9)) > STORED" > > Both the columns would be physically stored. When inserting you would need > to provide values for only the 1st column - those for the 2nd one would be > generated automatically. When querying phone numbers you would need to > retrieve the 1st column ordering by the 2nd one. > > Regards, > Andrey > > --- Sun, 12.10.08, Roland Bouman <[EMAIL PROTECTED]> wrote: > > Hi Monty, > >>>> If we move parsing out to the client, then we could implement > pluggable >>>> types as a client-side addition, leaving the database server lean > and >>>> mean, but still giving compile-time errors and data validation > hooks. >>> >>> Ouch....so that means essentially you cannot index columns using user >>> defined types (at least not in a way that the index reflects >>> type-specific semantics. > > [...] > >> I don't think it has to be necessary for the index to _understand_ the >> type, as long as the type has a sensible byte encoding. Take the new >> decimal type, for instance. One of the nice things about it was that it >> was a compact binary representation that was still sortable without >> needing to be expanded. > > That is interesting, I didn't consider the idea to make the type > responsible for yielding a binary > representation that always sorts > right. > > That said, it does scare me a bit that this would introduce a pretty > tight coupling between the type's value and its binary representation. > I mean, I can imagine that there are cases where the minimal binary > representation to reproduce the value maybe smaller than a > representation that also encodes order information, and perhaps in > these cases it would be more efficient to store a minimal binary > representation and allow ordering information to be dealt with at > runtime with code. > > An example I'm thinking about is phone numbers. Maybe you think the > example is whack in which case I'll try and think of another example > but the idea is this. Phone numbers are usually formatted in a way > that makes sense to the person who'se number it is, and there is no > generally accepted format. Some of the 'formatting' has some > 'real' > meaning (that is, is parsed by phones) and > other formatting is > personal, and allows the person to read their phone number better. > > Consider: > > 0031 71 5145678 > +31 71 514 56 78 > (0031) (0)71 5 145 678 > > All these address the same phone, so if I were to index this I'd like > this to all sort at the same position (and allow a unique constraint > to accept only one of these). At the same time I do not want to > perform some canonicalization before storing it and lose the > formatting - to me this is valuable information that I don't want to > lose. > > Without a customer type it is hard to deal with this. You can of > course use 2 columns, one storing the formatted number and one storing > the phone number in canonical format. A perhaps slightly better > solution maybe to to use a VARCHAR and add a special collation for > this phonenumber purpose. > > But if we want none of those hacks and create a custom type, then I > think it will be pretty hard to come up > with a binary representation > that correctly stores all information (that is, the phone number + > formatting) that still sorts correctly (that is, sort all example > phone numbers at the same position) > > What do you think? Does this make sense at all? > > kind regards, > > Roland > >> >> Same thing with IP addresses, right? If you throw binary version of one >> into a column, it'll sort fine and respond to equality fine. Yet you >> don't want to have to wrap all of your calls with inet_aton() or > something. >> >> The server can then do things like find ranges or equalities of values, >> and the UDTs can take care of encoding or decoding those things into a >> form that makes sense for the user. No? >> >> but yes... query execution certainly has to happen on the server... else >> I'm not entirely sure what the server does. :) >> >> > Monty >> >> >> > > > > -- > Roland Bouman > http://rpbouman.blogspot.com/ > > _______________________________________________ > Mailing list: https://launchpad.net/~drizzle-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~drizzle-discuss > More help : https://help.launchpad.net/ListHelp > > ________________________________ > Вы уже с Yahoo!? Испытайте обновленную и улучшенную. Yahoo! Почту! -- Roland Bouman http://rpbouman.blogspot.com/ -- Roland Bouman http://rpbouman.blogspot.com/ _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
