SQLite Forum

About the newly added decimal extension

About the newly added decimal extension

(1) By Lifepillar (lifepillar) on 2020-08-25 14:18:27 [source]

I am glad that a decimal extension has landed into SQLite!

From a cursory view of your code, it seems to me (correct me if I am wrong) that your extension operates directly on strings (i.e., char arrays) representing decimal numbers.

You are certainly aware of my sqlite3decimal project. Just as a quick summary, it uses a binary format for storage (using blobs), which can accommodate any number (with a finite number of digits, plus NaNs/Infs). Such format is called "decimalInfinite" (or decInfinite for short). It has the nice property that it preserves the total ordering among numbers (including special numbers such as +/-Inf), so that comparisons can be performed with memcmp() without any decoding. For arithmetic operations, numbers are decoded into the internal struct of an IEEE754-compliant library, which is currently decNumber (but could be anything else).

I am wondering whether you have considered to reuse (part of) my project. IMO, the binary encoding for storage is a very good choice for databases. DecimalInfinite is relatively simple, compact, order-preserving, and it can be encoded/decoded reasonably fast. Since the representation does not impose any limits, it is future-proof (you could implement decimals with up to 10 significant digits today, and extend it to 20 digits tomorrow, with no required change at the storage level). In principle, you could define arithmetic operations directly on the binary format, but no such algorithms have been published to date, to my best of my knowledge (that's why I use decNumber).

The implementation in sqlite3decimal is my own (see decInfinite.h and decInfinite.c), and it can be easily adapted for something other than decNumber. The implementation could be simplified if more reasonable limits were imposed to the size of the decimals (especially for exponents).

Of course I cannot release decNumber with a different license, but I would have no problem to put my code in the public domain.