On Mon, May 17, 2010 at 06:10:09PM +0100, Simon Slavin scratched on the wall:
>
> On 17 May 2010, at 5:48pm, Jay A. Kreibich wrote:
>
> > The other options is to just use fixed-point integer values (e.g. an
> > integer value that is 1 cent, or 1/100th of a cent, or whatever range
> > you need). You need to be careful about the math, but that would be
> > much simpler. 64-bit integers give you a fair amount of room to play
> > with.
>
> The tedium comes when you try to implement all the types of
> calculation. It's fast to do the four which are
>
> decimal + - * / decimal
>
> but you also have to implement
>
> decimal + - * / integer
> decimal + - * / real
> integer + - * / decimal
> real + - * / decimal
No, you don't. Once a DECIMAL is "tainted" by a REAL, you don't want
it going back unless the SQL programmer manually casts it back (which
usually isn't wise). So there is no additional operation. You
just convert to REAL before and do the standard math. So you have:
dec2real(decimal) + - * / real => real
real + - * / dec2real(decimal) => real
With fixed and/or BCD it is always safe to cast an INTEGER into a
DECIMAL (outside of overflows), so any INT/DEC interactions
become DEC/DEC:
decimal + - * / int2dec(integer) => decimal
int2dec(integer) + - * / decimal => decimal
Decimal to real is whatever it is. Decimal to integer is either
known to be safe or not.
> and that's before implementing all the functions which take more than
> two arguments (e.g. max(), sum()).
I never said it was simple. If you wanted to modify SQLite to do
this with built-in types, you'd have to do all that as well.
By making it a modified type (e.g. TEXT or BLOB) you remove yourself
from the standard numeric sort order. I'm not sure if that's good or
bad. Likely good.
And you can still do all that with an extension.
> You also have to decide whether 1.333333 is meant to equal 4/3, which
> means reading up on how much slip to allow and trying to implement
> one solution which suits everyone who will want to use SQLite.
Again, yes, but that's still no different with BCD or Fixed, or with
an extension or built-in types. Those concerns are tied to the
problem (and part of the reason to move to BCD or Fixed), not any
specific solution.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
