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.
_______________________________________________
fpc-devel maillist - fpc-devel@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-devel
