WARNING: this library is NOT ready for production yet. Use at your own risk!
SQLite3 Decimal
SQLite3 Decimal is an extension for SQLite implementing exact decimal arithmetic (within a bounded range). Numbers can be stored as strings or as 16-byte blobs conforming to IEEE 754 decimal128 format. Note that this representation is different from the one of SQLite's ieee754.c extension, which deals with a binary format encoding, not decimal. This extension is comparable to SQLite's decimal.c extension, but it provides more operations and uses a different internal representation for calculations.
The library provides 34 digits of decimal precision and an exponent range in [-6143,6144]. See also How much precision and range is needed for decimal arithmetic?
There is no manual yet. Please refer to the code documentation instead (read at the end of this document).
Quick Start
./configure
make
sqlite3 --cmd '.load ./libdecimal'
Sample Session
General Information
sqlite> select decVersion();
Decimal v0.1.2 (decNumber 3.68-p2)
sqlite> select * from decContext;
prec emax emin round
---- ---- ----- ---------------
34 6144 -6143 ROUND_HALF_EVEN
Creating decimals
Decimals can be created from strings, integers, or other decimals. The internal representation is based on IEEE 754 decimal128 format.
sqlite> select decBytes(dec('1.89'));
cf000000 00000000 00000000 00800722
sqlite> select decStr(dec('1.89'));
1.89
sqlite> select decBytes(dec(42));
42000000 00000000 00000000 00000822
sqlite> select decStr(dec(42));
42
Note: internally, SQLite3Decimal always manipulates numbers in IEEE's decimal128 format regardless of the input format. So, the output of a calculation is always a decimal128-formatted blob. To translate that into a string you have to use decStr().
Mathematical operations
sqlite> select decStr(decMul(decAdd('1.23', '45.0967', '-678.00000000000000001'), '-0.7891'));
498.453401030000000007891
sqlite> select decStr(decDivInt(9,4));
2
sqlite> select decStr(decSqrt(16.000));
4.00
sqlite> select decStr(decExp(-1)); -- e⁻¹
0.3678794411714423215955237701614609
sqlite> select decStr(decQuantize(decExp(-1), '.00')); -- Two fractional digits, please
0.37
Rounding
sqlite> select decStr(decDiv(1,7));
0.1428571428571428571428571428571429
sqlite> select * from decStatus; -- Was the result rounded?
Inexact result
Clear the status flags:
sqlite> delete from decStatus;
The rounding method can be adjusted at runtime by updating the context:
sqlite> select decStr(decDiv(1,3));
0.3333333333333333333333333333333333
sqlite> update decContext set round = 'ROUND_UP';
sqlite> select decStr(decDiv(1,3));
0.3333333333333333333333333333333334
Decimals stored as blobs
sqlite> create table T(n blob);
sqlite> insert into T values (dec('.843')), (dec('3427.19')), (dec('-28383.89'));
sqlite> select decStr(n) from T;
0.843
3427.19
-28383.89
sqlite> select decStr(n) from T where decLt(n, 10); -- Numbers less than 10
0.843
-28383.89
sqlite> select decStr(decMin(n)), decStr(decMax(n)), decStr(decSum(n)), decStr(decAvg(n)) from T;
-28383.89|3427.19|-24955.857|-8318.619
sqlite> select hex(n) from T;
4D010000000000000000000000400722
990B0700000000000000000000800722
CFF924000000000000000000008007A2
sqlite> select decBytes(n) from T;
4d010000 00000000 00000000 00400722
990b0700 00000000 00000000 00800722
cff92400 00000000 00000000 008007a2
Decimals stored as strings
Similarly to SQLite's own decimal.c extension, numbers stored as text can also be processed:
sqlite> create table U(n text);
sqlite> insert into U values ('0.123'), ('-34.20');
sqlite> select n from U;
0.123
-34.20
select decStr(decMul(X.n, Y.n)) from U X, U Y where decLt(X.n, Y.n);
-4.20660
(Almost) equivalent formulation with SQLite's decimal.c extension:
sqlite> select decimal_mul(X.n, Y.n) from U X, U Y where decimal_cmp(X.n, Y.n) < 0;
-4.2066
When numbers are stored as text, you can even mix the two extensions if you are careful enough to convert SQLite3Decimal results back to strings:
sqlite> select decimal_add(decStr(decMul(X.n, Y.n)), '1.5') from U X, U Y where decimal_cmp(X.n, Y.n) < 0;
-2.70660
Trapping error conditions
The extension can be configured at runtime to raise or not to raise errors upon certain conditions. The conditions that should raise an error are found inside the decTraps virtual table:
sqlite> select flag from decTraps;
Conversion syntax
Division by zero
Division impossible
Division undefined
Out of memory
Invalid context
Invalid operation
sqlite> insert into decTraps values ('Inexact result');
sqlite> select decStr(decDiv(1,7));
Runtime error: Inexact
sqlite> select dec('1..2');
Runtime error: Conversion syntax
sqlite> delete from decTraps where flag = 'Conversion syntax';
sqlite> select decStr(dec('1..2'));
NaN
Build the code documentation
Install Doxygen and Graphviz, then:
make doc