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