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
