[Wellington-pm] numeric types

[Daniel Pittman]

Richard Hector <richard at walnut.gen.nz> writes:
> On Mon, 2008-10-27 at 15:26 +1100, Daniel Pittman wrote:

[...]

>> Specifically, see bigrat(3perl) for the easy path, or Math::BigRat for
>> the library that uses if you want to do it the hard way.[1]
>
> That looks like it might be a nice way to store numbers, but I still
> have the issue of how to get them out of the DB and into one of those
> without converting to a float along the way.

bigrat overloads a whole bunch of stuff to magically use Math::BigRat
objects where appropriate -- since this is global, not limited to your
own module, this can transparently add rational number support outside
your namespace.

>> > I'm not even clear that that's what will happen; it seems difficult
>> > to work out how Perl has actually stored a given value; perhaps DBI
>> > returns it as a string, which I can match and stick into a more
>> > complex data structure that preserves the components?
>> 
>> You can use Data::Dumper, or one of the non-bundled methods, to probe
>> what data type it is -- but, generally, it is fairly mutable.
>
> This is the core of my question, I think. I've done a few tests, and
> Data::Dumper doesn't seem to display scalars any differently based on
> their storage, as far as I can see.  What are these 'non-bundled
> methods' you're talking about?

Devel::Peek, predominantly, which gives a lot of information about the
internals of the object.[1]  Possibly something like Devel::XRay to inspect
code flow, but I don't think that helps a lot.

Mostly, though, I would expect that you could find out most of what you
want (is this a rational or a float) using ref, which will return the
class of the rational, or '' for a normal float or int.

[...]

>> Hard work and enthusiasm.  You can probably derive appropriate black-box
>> testing from this: http://speleotrove.com/decimal/decifaq1.html#inexact
>
> Test by doing calculations and seeing what I get? I can see that that
> could work, but I suspect conversions could happen in multiple places,
> leading to ambiguity of my results.

Well, my expectation would be that you would inspect the DBD::Pg code,
and/or tested this, to determine what it returns.

The quick answer is: DBD::Pg, in the absence of any configuration,
returns values from a NUMERIC(10,2) column as strings, which you can
then convert to a Math::BigRat trivially, giving you the precision you
want.

[...]

> Andrew: whether I actually need this is a separate issue, of course
> ...  but that speleotrove link gives examples of these issues showing
> up in funny places.

*nod*  Personally, I think the cost of manually wrapping the column to a
Math::BigRat, or configuring your ORM to do so, is pretty low -- but
then you have to deal with getting that data back into PGSQL. :/

> I'm tempted to just store everything as pairs (numerator and
> denominator) of integers. Seems much more precise than what SQL's
> numeric type can offer.

The biggest advantage of using NUMERIC or DECIMAL is that you can use
the database safely to act on the data: SUM(numeric_column) works
correctly[2], where it wouldn't with something you wrote yourself.

If you treat the database as a storage system only, rather than doing
any calculation or comparison in it, then this might be fine...

> And if I can't get those numeric numbers in and out of perl easily,
> then perhaps less work as well.

They go in quite fine.  Back out is more difficult, because Math::BigRat
wants to output decimal and PGSql doesn't like that syntax by default
for numeric columns...

Regards,
        Daniel

Footnotes: 
[1]  Which, apparently, is a built-in module in 5.8.8, which says that
     I am getting forgetful in my old age.

[2]  As in, preserves accuracy.