Andrey Zhakov wrote: > Hi Roland, > > Here is where I could try to sell out my virtual columns ;)
Which, btw, have just now been merged into the trunk. Give em a good playing with folks... > 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! Почту > <http://ru.mail.yahoo.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
