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 ?
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
[1]
https://github.com/yesodweb/persistent/blob/master/persistent-postgresql/Dat...
[2] https://github.com/yesodweb/yesod/issues/493
On Fri, Jan 25, 2013 at 8:19 AM,
**
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@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
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.
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.
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@haskell.org javascript:; http://www.haskell.org/mailman/listinfo/haskell-cafe
Sat, Jan 26, 2013 at 12:21:02PM +0600, s9gf4ult@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
On Jan 27, 2013 8:46 AM,
Sat, Jan 26, 2013 at 12:21:02PM +0600, s9gf4ult@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
participants (4)
-
alexander.vershilov＠gmail.com -
Michael Snoyman -
ok＠cs.otago.ac.nz -
s9gf4ult＠gmail.com