[fpc-devel] Implementing TFmtBCDField - ftFmtBCD and SQLite

[LacaK]

>> Yes it is impossible in native way ... but we can help him and do 
>> necessary conversion in the background ... question is what user 
>> expect, when defines column like DECIMAL(15,7) ?
>
> He expects to get/put values of that type, i.e. TFmtBCDField if I 
> understand the discussion right.
>
> Question: is it assumed that the concrete DB is accessed only by FPC 
> apps?
No, as long as we can implement it ... so we must do it in way, that 
values are confortable readable also by others

>
> If not, the user is lost. So let's assume that the value can be stored 
> in any format.
Yes in general value can be stored in any format, but my goal is provide 
such mechanizm, which will be transparent also to other database 
connectors outside FPC (idea is based on fact, that SQLite is able to do 
internaly automatic conversions between BLOB -> TEXT -> INTEGER , REAL

> Now you're free to use either a BLOB
yes

> or an fixed-length string with the ASCII representation of the number 
> (ToString).
no, because SQLite will try ALWAYS convert such numbers into either 
INTEGER or REAL (if into string will be valid number i.e. integer like 
'123456789123456789' or floating point like '123456789123456789.123456') 
what can lead to lost of precision


I think, that there are 2 ways which are consistent and logical:

1.way is "support only datatypes which directly/natively supports SQLite"
--------------------------------------------------------------------------
SQLite support this numeric columns types (called type affinities):
1.1 INTEGER 1-8 bytes (corresponds to FPC ftSmallInt, ftInteger, 
ftLargeInt) ... declared as TINYINT, SMALLINT, INT, INTEGER, BIGINT
1.2 REAL 8bytes floating point (corresponds to FPC ftFloat) ... declared 
as REAL, FLOAT, DOUBLE
1.3 NUMERIC combination of 1.1 and 1.2 ... declared as NUMERIC, DECIAML

As we see SQLite does not support exact numeric datatypes (with scale>0)
So if we want be strictly consistent with this, then we should map 
NUMERIC, DECIMAL to either ftLargeInt (if scale=0) or ftFloat (if 
scale>0) ... so do the same thing as SQLite is doing (do not use FPC 
exact numeric datatypes ftBCD, ftFmtBCD because we can not save their 
values into SQLite database without lost of precision).

What we do now ? As example lets look at case when scale<=4 then we map 
to ftBCD.
1. When reading value from SQLite database (where is stored as 8byte 
IEEE floating point number) we use sqlite3_column_double ... so value is 
stored as double and we read it as double
2. then we convert them using FloatToCurr to currency (datatype used by 
ftBCD)
3. when writting back we write it AsFloat using sqlite3_bind_double

It seems to me strange, why we do all this conversions (we can not 
guarantee that value inserted into ftBCD will be saved without lost of 
precision) ? Why not simply map to ftFloat and be sure, that values are 
stored and read correctly (without conversions).

2. way is "support exact numeric at sqlDB level using some add-on logic 
and use SQLite REAL, INTEGER or BLOB base datatypes"
---------------------------------------------------------------------------------------------------------------------------------------
This way adds some extra logic into SQLiteConnector which can guarantee, 
that all exact numeric values will be written/read without lost of 
precision. Base idea is for DECIMAL(precision,scale)
1. if scale=0 and precision<19 then map to ftLargeInt
2. if scale<=4 and precision<15 then map to ftBCD
3. else map to ftFmtBCD

When writting ftFmtBCD values we must test current precision of value 
and if > 15 then write it as BLOB (sqlite3_bind_blob)
Value written as BLOB is still readable (with possible lost of 
precision) as TEXT (sqlite3_column_text) or INTEGER 
(sqlite3_column_int64) or REAL (sqlite3_column_double) ... so no problem 
for others .

Laco.