[fpc-devel] Implementing TFmtBCDField - ftFmtBCD and SQLite

[LacaK]

> Simply said: So Sqlite doesn't support real BCD values.
Yes, SQLite does not support them native

>  So we can't support it either for Sqlite. 
>   
It was my question. We can (if we want) partialy add work-arounds:
DECIMAL(x,y)
if y=0 then map to ftLargeInt (64bit integer)
elseif y<=4 then map to ftBCD (as it is now)
else map to ftFloat (IMHO better than raising exception)

> More difficult: Sqlite is not a 'normal' sql-database. It has all kind
> of strange issues, it's own definitions and options. It is impossible to
> support all these gimmicks in sqldb.
I agree, and most horrible is, that in one column you can have any 
datatype in different rows (in 1st row character data, in 2nd integer 
and in 3th real numbers and in 4th blob)

> Sqlite only has very few
> data-types (the 'affinities') but to be gentle to our users we do
> support more, only based on the field-type name.
Yes and this is IMO good. If user defines column datatype as numeric or 
integer or float it can be expected, that also data in that column will 
be in that format

>  So as long as users
> can't make tables with numeric fields that support more then 15 numbers,
> we don't have to support bcd-fields. So map decimal to BCD (not float,
> it will be invalid for values within the supported precision range). If
> users want to store numbers with an higher precision, that is simply
> impossible in sqlite.
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) ?

>  If they want to store their numbers in
> blob-fields, they can simply define a blob field and do so...
>   
Yes but BLOB is mapped to TBlobField and there are no methods like 
AsFloat, AsBCD, AsFMTBCD ... so user must handle all this in their code 
... retrieve value convert them etc. , use TField.OnGetText etc. ... it 
is doable, but not very confort.

> So where's the problem? (I see the problem of Sqlite not supporting
> bcd-fields,
Yes it is primary problem

>  but the sqldb-problem?
>   
What to do in case when user defines column  NUMERIC, DECIAML (x,y) 
where y>4 or x>15 ?
(I do not like raise exception if there is way how to handle this 
situation ... and also user IMHO does not expect exception)

As I wrote we should at least do:
DECIMAL(x,y)
if y=0 ---> ftLargeInt
elseif y<=4 ---> ftBCD
else ---> ftFloat (or ftString with Size=x+1 ?)

But we can go far (this is relative simple to implement and gives 
maximum transparency and easy to use):
if x > 15 or y>4 ---> ftFmtBCD (and here write as BLOB into SQLite3 
database in background)
elseif y=0 ---> ftLargeInt
else ---> ftBCD


Laco.