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

[Michael Snoyman]

On Jan 27, 2013 8:46 AM, <alexander.vershilov at gmail.com> wrote:
>
> 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.

Let me clarify a bit:

1. Persistent will currently allow you to create a `Money` datatype which
internally stores as an integer.

2. What Persistent currently lacks is a PersistValue constructor for
arbitrary-precision values. As a result, during marshaling, some data will
be lost when converting from NUMERIC to Double.

3. The upcoming change we're discussing for Persistent would just be to add
such a constructor. We could theoretically provide some extra PersistField
instances as well, but that's not really what's being discussed.

HTH,

Michael
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.haskell.org/pipermail/haskell-cafe/attachments/20130127/f6771150/attachment.htm>