Re: [fpc-devel] Implementing TFmtBCDField - ftFmtBCD and 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? 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
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
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
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 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) (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 y>4 ---> 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
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
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
