[Haskell-cafe] How to store Fixed data type in the database with persistent ?

[alexander.vershilov at gmail.com]

Sat, Jan 26, 2013 at 12:21:02PM +0600, s9gf4ult at gmail.com wrote
> > According to the documentation, SQLite stores whatever you give it,
> > paying very little heed to the declared type.  If you get SQLite to
> > *compare* two numbers, it will at that point *convert* them to doubles
> > in order to carry out the comparison.  This is quite separate from the
> > question of what it can store.
> 
> CREATE TABLE t1(val);
> sqlite> insert into t1 values ('24.24242424')
>    ...> ;
> sqlite> insert into t1 values ('24.24242423')
>    ...> ;
> sqlite> select * from t1 order by val;
> 24.24242423
> 24.24242424
> sqlite> select * from t1 order by val desc;
> 24.24242424
> 24.24242423
> sqlite> select sum(val) from t1;
> 48.48484847
> 
> it seems Sqlite can work with arbitrary percission data, very good ! 
> Persistent must have ability to store Fixed.
> 

It's not correct. SQLlite stores any value, but it will use arithmetic 
operations only with double presicion:

sqlite> select val from t1;
1
0.000001
0.00000001
0.0000000001
0.000000000001
0.00000000000001
0.0000000000000001
0.000000000000000001
0.00000000000000000001
0.0000000000000000000001

sqlite> select sum(val) from t1;
1.00000101010101

as you see it has 14 degree. 

Let's check another well known floating point problem:

sqlilte> create table t2 ('val')
sqlite> insert into t2 values ('0.7');
sqlite> update t2 set val = 11*val-7;

t2 should remain a const
sqlite> update t2 set val = 11*val-7; -- 4 times
sqlite> select val from t2;
0.699999999989597
sqlite> update t2 set val = 11*val-7; -- 10 times mote
sqlite> select val from t2;
0.430171514341321

As you see you have errors. So SQLlite doesn't support arbitrary
presision values.

As for me Persistent should at least support a Money type and use
correct backend-specific type for them, either a native for big integer.

--
Best regards
  Alexander Vershilov

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 490 bytes
Desc: Digital signature
URL: <http://www.haskell.org/pipermail/haskell-cafe/attachments/20130127/d3a252f0/attachment.pgp>