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

2013-01-25 06:19:39 GMT

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