Re: [fpc-devel] Implementing TFmtBCDField - ftFmtBCD and SQLite
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 y4 or x15 ? (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 y4 --- 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
Re: [fpc-devel] Implementing TFmtBCDField - ftFmtBCD and SQLite
On Fri, 2011-04-01 at 11:13 +0200, LacaK wrote: Simply said: So Sqlite doesn't support real BCD values. Yes, SQLite does not support them native Then it's up to the 'user' (in our case the user is a programmer) to implement workarounds. If we add some work-around, nobody will know that that work-around exist. And other sqlite tools will not be able to handle it, and the 'user' will be puzzled. 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) No, map to ftfmtbcd, as it should. That will work fine as long as the values are within the sqlite-range. 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 What we could do is add a possibility for users to override the field-type definition. Add an event or something which is called to determine the field-type. But make this generic, so it works for all datasets. That could be a very strong feature. 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) ? What other db-tools do when you use sqlite: work as long as you don't really require the specified precision. 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. Yes, but it's a sqlite limitation. If users choose to use a tool that is not suited for their goal, it's not the task of sqldb to mask that, imho. 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 y4 or x15 ? (I do not like raise exception if there is way how to handle this situation ... and also user IMHO does not expect exception) (Well, show me the bug: as it is now, he won't get an exception? Just map to ftmbcd) the user will only get an invalid value when he stores a value outside the sqlite-precision scope. Exactly what he would expect when he uses sqlite. 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 y4 --- ftFmtBCD (and here write as BLOB into SQLite3 database in background) elseif y=0 --- ftLargeInt else --- ftBCD No, but if you need whit work-around in your own projects, implement a general event to override this behavior. I think that's best. Joost. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] Implementing TFmtBCDField - ftFmtBCD and SQLite
No, map to ftfmtbcd, as it should. That will work fine as long as the values are within the sqlite-range. ok. in reading phase no problem (ATM we read using sqlite3_column_text (so SQLite converts all storage classes (integer,real, blob) to string) and then converting to TBCD ... ok (Well, show me the bug: as it is now, he won't get an exception? Just map to ftmbcd) yes now no exception it is ok as is the user will only get an invalid value when he stores a value outside the sqlite-precision scope. Exactly what he would expect when he uses sqlite. Yes. I can live with this, but do not forget, that ATM there is not implemented writting of ftFmtBCD parameters in Procedure TSQLite3Cursor.bindparams(AParams : TParams); So writting never occurs! So there we must add at least ftFmtBCD case, (behind ftbcd) But If we add this code (excuse me, I must offer it) ;-): //it is only pseudo code ftFmtBCD: begin if P.AsFMTBCD.Precision 15 then //we are out-of-range begin str1:=BCDTOStr(P.AsFMTBCD, SQLFormatSettings); checkerror(sqlite3_bind_blob(fstatement,I,pcharstr(str1), length(str1),@freebindstring)); end else begin do1:=P.AsFloat; checkerror(sqlite3_bind_double(fstatement,I,do1)); end; end; // all. Then all cases will be covered and no additional work around are needed. Laco. P.S.: P.AsFMTBCD: http://bugs.freepascal.org/view.php?id=18809 ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] Implementing TFmtBCDField - ftFmtBCD and SQLite
LacaK schrieb: 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) ? 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? If not, the user is lost. So let's assume that the value can be stored in any format. Now you're free to use either a BLOB or an fixed-length string with the ASCII representation of the number (ToString). The string representation has the advantage that the format can be determined from the stored data, making the whole thing somewhat type-safe. The size overhead of such an encoding should be compared to the overhead of an blob, in order to find out whether the verbose storage is acceptable. If it wastes too much space, the digits may be encoded in some way, that e.g. maps 2 digits into 100 distinct character codes. The sign can be encoded in the first character, either as its ASCII value, or like a digit with '0' for positive and everything else for negative values. Such an encoding can be used for every DB, that doesn't support BCD numbers natively. DoDi ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] Implementing TFmtBCDField - ftFmtBCD and SQLite
On Thu, 2011-03-24 at 09:43 +0100, LacaK wrote:
after doing some test with new implementation of TFmtBCDField for
TSQLite3Connection connector I encounter this problem:
When you declare in SQLite some column as NUMERIC or DECIMAL then this
column will have NUMERIC affinity.
CREATE TABLE t (d DECIMAL(30,7));
If you insert in such column any numeric value (quoted or unquoted):
INSERT INTO t VALUES('123456789123456789.123456');
SQLite converts such numeric value to 8-byte floating point
representation (REAL) or 8-byte integer (INTEGER), so:
(lost of precision occurs)
SELECT * FROM t;
returns 123456789123456784
Simply said: So Sqlite doesn't support real BCD values. So we can't
support it either for Sqlite.
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. This holds also in a lesser degree
for the other databse-engines, btw.
Practical: (the only important answer) Show me the problem, a bug, or
some code that doesn't work. I think we don't have a problem. Sqldb is
used to read data from existing tables. 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. 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. If they want to store their numbers in
blob-fields, they can simply define a blob field and do so...
So where's the problem? (I see the problem of Sqlite not supporting
bcd-fields, but the sqldb-problem?)
Joost.
___
fpc-devel maillist - fpc-devel@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-devel
[fpc-devel] Implementing TFmtBCDField - ftFmtBCD and SQLite
Hi,
after doing some test with new implementation of TFmtBCDField for
TSQLite3Connection connector I encounter this problem:
When you declare in SQLite some column as NUMERIC or DECIMAL then this
column will have NUMERIC affinity.
CREATE TABLE t (d DECIMAL(30,7));
If you insert in such column any numeric value (quoted or unquoted):
INSERT INTO t VALUES('123456789123456789.123456');
SQLite converts such numeric value to 8-byte floating point
representation (REAL) or 8-byte integer (INTEGER), so:
(lost of precision occurs)
SELECT * FROM t;
returns 123456789123456784
See: http://www.sqlite.org/datatype3.html#affinity
A column with NUMERIC affinity may contain values using all five
storage classes. When text data is inserted into a NUMERIC column, the
storage class of the text is converted to INTEGER or REAL (in order of
preference) if such conversion is lossless and reversible. For
conversions between TEXT and REAL storage classes, SQLite considers the
conversion to be lossless and reversible if the first 15 significant
decimal digits of the number are preserved. If the lossless conversion
of TEXT to INTEGER or REAL is not possible then the value is stored
using the TEXT storage class. No attempt is made to convert NULL or BLOB
values.
My original understanding was, that when I store big numbers as TEXT and
lossless conversion to REAL can not be done, then value will be stored
as TEXT, but in meaning in SQLite lossless is: if the first 15
significant decimal digits of the number are preserved (not all digits
only first 15!!!)
I have asked about this in sqlite-users mailing list (topic storing big
numbers into NUMERIC, DECIMAL columns) and results are:
(I attach some of comments from sqlite-users mailing list)
A.) if we want preserve precision of BCD values we must store them as
BLOBs (using sqlite3_bind_blob()) ... see (1) and (2) in attachment
(with optimalization like : if BCDPrecision(...) 15 then use
sqlite3_bind_blob(...) else sqlite3_bind_double(...) )
Here my test shows, that if I store value like BLOB I still can do
select like:
SELECT d+100 FROM t;
SELECT sum(d) FROM t;
SELECT * FROM t WHERE d12345678912345678;
(it seems, that SQLite converts BLOB-TEXT-REAL ...of course precision
is lost)
B.) does not support numbers (big exact numbers), which are not
supported by SQLite (SQLite supports only REAL and INTEGER). ... see (3)
So DECIMAL, NUMERIC map to ftFloat if decimal places4 (out of range
ftBCD) and map to ftLargeInt if decimal places=0
What do you think, which way to go ?
-Laco.
(1)
Here are two options which will let you get the contents back to the original
precision:
A) Store the values as BLOBs.
B) Store the value as TEXT, but add a non-digit to the beginning of each number
value, for example
X24395734857634756.92384729847239842398423964294298473927
Both methods will prevent SQLite from trying to see the value as a number. Oh
and since nobody seems to have pointed it out yet, SQLite doesn't have a
NUMERIC or a DECIMAL column type. The types can be found here:
http://www.sqlite.org/datatype3.html
Putting INTEGER and REAL together gives you NUMERIC, but there's no way to
declare a column of that type, just a value. The page actually rehearses your
problem, showing when strings containing numeric values can be converted to a
number.
--
(2)
You can store any big-number representation you like as TEXT or BLOB
values. The main issue is that you'll lose syntactic sugar: SQLite3
won't be able to treat those as numeric values, therefore it won't be
able to compare numerically nor use arithmetic with such values.
--
(3)
If your goal is to work with any user database created outside of your
FreePascal connector then chances are that user will use the same
database outside of your FreePascal connector too. And if use declared
column as NUMERIC then he probably will expect numbers to be inserted
into that column (especially if the data type inside FreePascal is
also some sort of number). User also will probably execute some
queries that do some arithmetic operations on values in that column.
And they will be really surprised to see that not all the data is
numbers there.
So you better insert everything as numbers, let SQLite transform
everything to REAL and lose precision. And tell your users that it's
limitation of SQLite and you cannot do anything with it, only suggest
to insert big numbers as text.___
fpc-devel maillist - fpc-devel@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-devel
