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

[Michael Snoyman]

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/Database/Persist/Postgresql.hs#L271
[2] https://github.com/yesodweb/yesod/issues/493


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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.haskell.org/pipermail/haskell-cafe/attachments/20130125/b86e9aa0/attachment.htm>