mORMot Open Source

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Floating point and Currency fields

For standard floating-point values, the framework only handle the double and currency kind of variables.

In fact, double is the native type handled by SQLite3 and most database providers - it is also the native to the SSE set of opcodes of newer CPUs. Lack of extended should not be problematic (if it is mandatory, a dedicated set of mathematical classes should be prefered to a database), and could be implemented with the expected precision via a TEXT field (or a BLOB mapped by a dynamic array).

The currency type is the standard Delphi type to be used when storing and handling monetary values. It will avoid any rounding problems, with 4 decimals precision. It is able to safely store numbers in the range -922337203685477.5808 .. 922337203685477.5807. Should be enough for your pocket change.

Currency is a fixed-point data type that minimizes rounding errors in monetary calculations. On the Win32 platform, it is stored as a scaled 64-bit integer with the four least significant digits implicitly representing decimal places. When mixed with other real types in assignments and expressions, Currency values are automatically divided or multiplied by 10000.

In fact, this type matches the corresponding OLE and .Net implementation of currency. It is still implemented the same in the Win64 platform (since XE 2). The Int64 binary representation of the currency type (i.e. value*10000 as accessible via PInt64(aCurrencyValue)^) is a safe and fast implementation pattern.

In our framework, we tried to avoid any unnecessary conversion to float values when dealing with currency values. Some dedicated functions have been implemented for fast and secure access to currency published properties via RTTI, especially when converting values to or from JSON text. Using the Int64 binary representation can be not only faster, but only safer: you will avoid any rounding problem which may be introduced by the conversion to a float type. Rounding issues are a nightmare to track - it sounds safe to have a framework handling natively a currency type from the ground up.
Faster and safer way of comparing two currency values is certainly to map the variables to their internal Int64 binary representation, as such:

function CompCurrency(var A,B: currency): Int64;
var A64: Int64 absolute A;
    B64: Int64 absolute B;
begin
  result := A64-B64;
end;

This will avoid any rounding error during comparison (working with *10000 integer values), and will be faster than the default implementation, which uses the FPU (or SSE2 under x64 architecture) instructions.

You some direct currency handling in the SynCommons.pas unit. It will by-pass the FPU use, and is therefore very fast.

There are some functions using the Int64 binary representation (accessible either as PInt64(aCurrencyVar)^ or the absolute syntax):
- function Curr64ToString(Value: Int64): string;
- function StrToCurr64(P: PUTF8Char): Int64;
- function Curr64ToStr(Value: Int64): RawUTF8;
- function Curr64ToPChar(Value: Int64; Dest: PUTF8Char): PtrInt;
- function StrCurr64(P: PAnsiChar; const Value: Int64): PAnsiChar;

Using those functions can be much faster for textual conversion than using the standard FloatToText() implementation. They are validated with provided regression tests.

Of course, in normal code, it is certainly not worth using the Int64 binary representation of currency, but rely on the default compiler/RTL implementation. In all cases, having optimized functions was a need for both speed and accuracy of our ORM data processing.