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@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@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe