[fpc-devel] Implementing TFmtBCDField - ftFmtBCD and SQLite

[Joost van der Sluis]

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.