Base 10 multiplication is needed.  Although money is the main reason for doing 
BCD due to cumulative errors it's not the only reason.  So I'd recommend adding 
decimal*decimal -> decimal just to be complete.  It's easy enough to implement 
using the + function so could just be noted as "slow".

There are many cases where people are doing calculations or using numbers 
expecting them to retain all digits.  This would allow the BCD type to be used 
for that if they really need it.

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate



________________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Sunday, March 27, 2011 5:49 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Lack of "decimal" support

For those who are scared by this, there really isn't that much to do.  Let us, 
for the same of argument, say we're going to allow decimal values to an 
arbitrary length: any number of digits, possibly with a decimal point somewhere 
along the string, possibly starting with a minus sign.

The major choice is how the numbers are going to be stored.  The two 
conventional ways are with one digit per byte (either using ASCII 0 == 0x30 or 
with 0 == 0x00), or with two digits per byte, often known as Binary-Coded 
Decimal.  The first option makes for faster input, output, easier reading of 
file formats, and simpler programming and debugging.  The second makes for a 
smaller database file and speeds up some calculations using tables.  You 
generally turn the minus sign into a bit flag.

Once you have decided that you'll need conversion routines.  These will either 
be to-and-from REAL or to-and-from TEXT.

The other things to be implemented are the maths routines.  There are really 
only five of these to worry about since, as someone pointed out upthread, one 
does not multiply two amounts of money together.  There are other things you 
don't do either: you don't add a decimal to a real, nor raise decimals to 
powers (the log of a decimal value doesn't mean much).  So you need

decimal + decimal --> decimal
decimal - decimal --> decimal
decimal * real --> decimal
decimal / real --> decimal
decimal / decimal --> real

The first four are slower than adding reals together.  The last one is tricky 
and usually dealt with by turning the decimals into reals before division 
(since the answer will be a real anyhow, this doesn't lose much precision).  
The same tactic can be used if the programmer asks for anything weird (e.g. 
decimal + real) though those normally indicate that the programmer made a 
programming error and some compilers flag them with warnings.

That's pretty-much all there is to it.  It doesn't represent huge growth to the 
SQLite codebase, or even of the SQLite compiled code, though it will stop 
database files which use the format from being backward-compatible, and it will 
add a lot of tests to the test suite.  I don't know if it could be added as a 
compilation option or not: you need someone who understands how SQLite is built.

Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to