SQLite3 Decimal

SQLite3 Decimal
Login

SQLite3 Decimal

SQLite3 Decimal is an extension for SQLite3 implementing (exact) decimal arithmetic.

WARNING: this library is NOT ready for production. Use at your own risk!

There is no manual or official documentation yet. Please refer to the code documentation instead.

Quick Start

To build in macOS:

make

To build in other systems:

make SOEXT=so LDFLAGS=-shared

To build the documentation (requires Doxygen):

make doc

To load dynamically in sqlite3 shell:

cd build/release
sqlite3 --cmd '.load ./libsqlite3decimal sqlite3_decimal_init'

Sample Session

sqlite> select decVersion();
Decimal v0.0.1 (decNumber 3.68)

sqlite> select decStr(decMul(decAdd('1.23', '45.0967', '-678.00000000000000001'), '-0.7891'));
498.453401030000000007891


sqlite> create table T(n blob); -- Decimals are stored as blobs
sqlite> insert into T values (dec('.843')), (dec('3427.19')), (dec('-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 decStr(n) from T where n < dec(1);
0.843
-28383.89

sqlite> select decStr(decDiv(1,7));
0.142857142857142857142857142857142857143
sqlite> select * from decStatus; -- Was the result rounded?
Inexact result
Rounded result

sqlite> select * from decTraps; -- Which errors are trapped by default?
flag             
-----------------
Conversion syntax
Division by zero 
Division impossib
Division undefine
Out of memory    
Invalid context  
Invalid operation
Overflow         
Underflow        

sqlite> insert into decTraps values ('Inexact result');
sqlite> select decDiv(1,7);
Error: Inexact

sqlite> select dec('abc');
Error: Conversion syntax
sqlite> delete from decTraps where flag = 'Conversion syntax';
sqlite> select decStr(dec('abc'));
NaN               

sqlite> delete from decStatus; -- Clear the status flags

sqlite> select * from decContext;
prec        emax        emin        round        
----------  ----------  ----------  -------------
39          999999999   -999999999  ROUND_HALF_UP

sqlite> update decContext set prec = 6;
sqlite> select * from decContext;
prec        emax        emin        round        
----------  ----------  ----------  -------------
6           999999999   -999999999  ROUND_HALF_UP

sqlite> select decStr(dec('1.23456789'));
decStr(dec('1.23456789'))
-------------------------
1.23457