WARNING: this library comes with no warranty whatsoever. 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:
This is a quick comparison between this extension and SQLite's decimal.c to help you decide which library best suits your needs (but note that the two libraries can be used together!):
| Feature | This library | SQLite's decimal.c |
|---|---|---|
| Storage | 16-byte blobs or text format | Text format only |
| Time performance | More efficient | Less efficient |
| Operations | Almost all IEEE 754 operations | Few operations |
| Precision | 34 decimal digits | Arbitrary |
| Rounding | Possible (and detectable) | Only exact operations are implemented (e.g., no division) |
| Special numbers (±Inf, NaNs, -0) | Supported | Not supported |
| Trailing zeroes | Follows IEEE 754 standard rules | Custom rules |
| Collating | Not possible for blobs | Decimal collate |
The difference about trailing zeroes means that 1.00 x 1.000 is 1.00000 according to this library, but it's 1.00 according to SQLite's decimal.c.
Lack of collating sequences for blobs (a limitation of SQLite) means that it is not possible in general to compare numbers stored as blobs with operators such as = or >: you have to use functions such as decEq() and decGt() instead, or normalize the numbers with decReduce() (for equality and inequality only). For numbers stored as text you can use SQLite's decimal collation.
Although SQLite's decimal.c only implements exact operations on decimals, SQLite automatically casts a numerical string into a binary float if necessary. So, operations such as divisions can be performed by falling back to the usual binary floating point arithmetic (which kind of defeats the purpose of having a decimal arithmetic library, though).
Quick Start
./configure
make
sqlite3 --cmd '.load ./libdecimal'
To build a static library instead of a dynamic library, use:
make clean && ./configure --static && make
Sample Session
General Information
sqlite> select decVersion();
Decimal v0.1.3 (decNumber 3.68-p3)
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'));
22078000 00000000 00000000 000000cf
sqlite> select decStr(dec('1.89'));
1.89
sqlite> select decBytes(dec(42));
22080000 00000000 00000000 00000042
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;
22074000 00000000 00000000 0000014d
22078000 00000000 00000000 00070b99
a2078000 00000000 00000000 0024f9cf
Note that blobs are stored in little-endian order, but decBytes() displays the numbers in big-endian order (so, the first bit—that is, leftmost bit—is the sign).
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