Roland Bouman 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?
This is an interesting example. So I think what we may be talking about here is not only pluggable types but also making sure that sort methods are at least partially pluggable. If we look at the STL organization here, you've got sort algorithms defined which then take a predicate function that they use to compare items. If we can get our sorting to be able to do this, then part of your custom data type may also provide a sort predicate function, so that in your data type's case, the pure-bytes comparison method isn't tried. (I'm, of course, ignoring the part where I tell you that you don't really need to store the differences between those phone numbers and that a canonical phone number would be a more efficient thing to deal with... it is an example use case after all) So I'd say it's the type's responsibility to: 1) piggyback on a general storage that makes sense (string, byte[], int, etc) 2) provide a binary encoding for itself 3) optionally, provide a sort method if its binary encoding can't be directly sorted. As with all things, hopefully people will realize that likely 3 will be less efficient if used. On the other hand, perhaps pluggable sort functions would be something that people can do something clever with. Additionally, again if we have access to this client-side, then it would also be easy to tell the server to just return the rows unsorted and let us do it client side. Doesn't help in the sort then limit 10 environments ... but could be useful in spreading some of that load. Monty > 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 >> >> >> > > > _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
