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
