s9gf4ult | 25 Jan 07:19 2013
Picon

How to store Fixed data type in the database with persistent ?

All modern databases has field type NUMERIC(x, y) with arbitrary precision.

 

I need to store financial data with absolute accuracy, and I decided to use Fixed.

How can I store Fixed data type as NUMERIC ? I decided to use Snoyman's persistent, bit persistent can not use it from the box and there is a problem with custom field declaration.

 

Here is the instance of PersistField for Fixed I wrote

 

instance (HasResolution a) => PersistField (Fixed a) where

toPersistValue a = PersistText $ T.pack $ show a

-- fromPersistValue (PersistDouble d) = Right $ fromRational $ toRational d

fromPersistValue (PersistText d) = case reads dpt of

[(a, "")] -> Right a

_ -> Left $ T.pack $ "Could not read value " ++ dpt ++ " as fixed value"

where dpt = T.unpack d

 

fromPersistValue a = Left $ T.append "Unexpected data value can not be converted to Fixed: " $ T.pack $ show a

 

sqlType a = SqlOther $ T.pack $ "NUMERIC(" ++ (show l) ++ "," ++ (show p) ++ ")"

where

p = round $ (log $ fromIntegral $ resolution a) / (log 10)

l = p + 15 -- FIXME: this is maybe not very good

isNullable _ = False

 

I did not found any proper PersistValue to convert into Fixed from. As well as converting Fixed to PersistValue is just a converting to string. Anyway the saving works properly, but thre reading does not - it just reads Doubles with rounding error.

 

If you uncomment the commented string in instance you will see, that accuracy is not absolute.

 

Here is test project to demonstrate the problem.

 

https://github.com/s9gf4ult/xres

 

If you launch main you will see that precission is not very good because of converting database value to Double and then converting to Fixed.

 

How can i solve this with persistent or what other framework works well with NUMERIC database field type ?

 

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
Michael Snoyman | 25 Jan 11:32 2013

Re: How to store Fixed data type in the database with persistent ?

I can point you to the line of code causing you trouble[1].

The problem is, as you already pointed out, that we don't have a PersistValue constructor that fits this case correctly. I think the right solution is to go ahead and add such a constructor for the next release. I've opened a ticket on Github[2] to track this.

By the way, not all databases supported by Persistent have the ability to represent NUMERIC with perfect precision. I'm fairly certain the SQLite will just cast to 8-byte reals, though it's possible that it will keep the data as strings in some circumstances.

In the short term, you can probably get this to work today by turning your Fixed values into Integers (by multiplying by some power of 10) to marshaling to the database, and do the reverse when coming from the database. I haven't used this technique myself, but I think it should work.

Michael



On Fri, Jan 25, 2013 at 8:19 AM, <s9gf4ult <at> gmail.com> wrote:

All modern databases has field type NUMERIC(x, y) with arbitrary precision.

 

I need to store financial data with absolute accuracy, and I decided to use Fixed.

How can I store Fixed data type as NUMERIC ? I decided to use Snoyman's persistent, bit persistent can not use it from the box and there is a problem with custom field declaration.

 

Here is the instance of PersistField for Fixed I wrote

 

instance (HasResolution a) => PersistField (Fixed a) where

toPersistValue a = PersistText $ T.pack $ show a

-- fromPersistValue (PersistDouble d) = Right $ fromRational $ toRational d

fromPersistValue (PersistText d) = case reads dpt of

[(a, "")] -> Right a

_ -> Left $ T.pack $ "Could not read value " ++ dpt ++ " as fixed value"

where dpt = T.unpack d

 

fromPersistValue a = Left $ T.append "Unexpected data value can not be converted to Fixed: " $ T.pack $ show a

 

sqlType a = SqlOther $ T.pack $ "NUMERIC(" ++ (show l) ++ "," ++ (show p) ++ ")"

where

p = round $ (log $ fromIntegral $ resolution a) / (log 10)

l = p + 15 -- FIXME: this is maybe not very good

isNullable _ = False

 

I did not found any proper PersistValue to convert into Fixed from. As well as converting Fixed to PersistValue is just a converting to string. Anyway the saving works properly, but thre reading does not - it just reads Doubles with rounding error.

 

If you uncomment the commented string in instance you will see, that accuracy is not absolute.

 

Here is test project to demonstrate the problem.

 

https://github.com/s9gf4ult/xres

 

If you launch main you will see that precission is not very good because of converting database value to Double and then converting to Fixed.

 

How can i solve this with persistent or what other framework works well with NUMERIC database field type ?

 


_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe


_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
s9gf4ult | 25 Jan 21:01 2013
Picon

Re: How to store Fixed data type in the database with persistent ?

>>turning your Fixed values into Integers

 

Thanks for that idea.

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
ok | 26 Jan 03:01 2013
Picon

Re: How to store Fixed data type in the database with persistent ?


> By the way, not all databases supported by Persistent have the ability to
> represent NUMERIC with perfect precision. I'm fairly certain the SQLite
> will just cast to 8-byte reals, though it's possible that it will keep the
> data as strings in some circumstances.

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.
s9gf4ult | 26 Jan 07:21 2013
Picon

Re: How to store Fixed data type in the database with persistent ?

> 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.
Michael Snoyman | 26 Jan 18:16 2013

Re: How to store Fixed data type in the database with persistent ?

Very nice to see, I'm happy to stand corrected here. We'll definitely get some support for fixed into the next major release.

On Saturday, January 26, 2013, 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.

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
alexander.vershilov | 27 Jan 07:55 2013
Picon

Re: How to store Fixed data type in the database with persistent ?

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

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
Michael Snoyman | 27 Jan 16:20 2013

Re: How to store Fixed data type in the database with persistent ?


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

_______________________________________________
Haskell-Cafe mailing list
Haskell-Cafe <at> haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

Gmane