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