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! Почту! 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
