[Dbix-class] Getting numeric columns as numeric values

[Augustus Saunders]

I've heard this "you might lose precision so let's use a string" argument before, and I lend it no credence. I understand this mentality may be common, but there's just no need. In JDBC, you can ask for a value in whichever format you want, and it's up to the programmer to deal with relative precision of the DB vs the language. Since raw DBI doesn't offer this capability, a more sensible approach would be to throw an error on unsafe conversions, and let the user apply casts in SQL to control stringification vs losing precision. Anyway, that's out of DBIC's hands, but obviously DBIC lets you specify what type of a thing a column is, so the user can control what they want it to come out as when you call get_column etc. Just like with JDBC, it will still be up to the programmer to ask for the right thing. 

It might make sense to have DBIC's column configuration routines take a parameter that says, "hey, I don't know the best representation, you choose!" Whether that's default true or default false, I don't know. If the user explicitly asks for a loss of precision the reasonable options are to just do it or throw an exception/return an error and force the user to do ask for something sensible. I would tend to throw the exception and make the user apply casts in SQL if they really want to lose precision. Anyway, it would be sensible for tools that automatically generate scaffolding from your DB to automatically detect the need for BigInt etc.

In the meantime, if more sophisticated behavior is going to take a while to develop, I believe the correct behavior is to either lose precision or throw an error, and let the user ask for a string if that's what they want.

Thanks-
Augustus

On Feb 25, 2015, at 10:15 PM, Darren Duncan <darren at darrenduncan.net> wrote:

> I think the core issue is that Perl is not capable of losslessly representing all numbers that could come from a SQL database without using the string representation (or BigInt/BigRat/etc), and so using strings is a safe solution.
> 
> That being said, I think the best solution is for DBIx::Class to conditionally return as numbers.  Return a result $x as a Perl number if and only if both "(0+$x) eq $x" is true and $x is logically a number to the database.
> 
> Optionally auto-promote $x to a Math::BigInt/Rat/etc only when "(0+$x) ne $x".
> 
> Under this system, you get the most accurate semantics and performance without loss of precision.  (I assume the JSON modules deal with Math::BigInt/etc correctly.)
> 
> How does that work for people?
> 
> -- Darren Duncan
> 
> On 2015-02-25 3:30 PM, Augustus Saunders wrote:
>> Seems a little backwards to create a helper to work around a core defect. This is something that should work transparently, and is very simple to do so.
>> 
>> Augustus
>> 
>> 
>> On Feb 25, 2015, at 2:53 PM, Dmitry L. <dim0xff at gmail.com> wrote:
>> 
>>> Use DBIx::Class::Helper::Row::NumifyGet Luke
>>> 
>>> On 26 February 2015 at 01:43, Lasse Makholm <lasse at unity3d.com> wrote:
>>>> 
>>>> 
>>>> On Wed, Feb 25, 2015 at 10:59 PM, Darren Duncan <darren at darrenduncan.net>
>>>> wrote:
>>>>> 
>>>>> Augustus, what is the problem with DBIC stringifying numeric values?  That
>>>>> ensures full precision and in particular when you want to use them as
>>>>> numbers you can just do so, Perl does that automatically. -- Darren Duncan
>>>> 
>>>> 
>>>> One example of it being a problem is when converting row objects to JSON.
>>>> Javascripts === operator, for example, evaluates to false for for 42 ===
>>>> "42".
>>>> 
>>>> /L
>>>> 
>>>> 
>>>>> 
>>>>> 
>>>>> On 2015-02-25 1:15 PM, Augustus Saunders wrote:
>>>>>> 
>>>>>> For reasons unknown to us, DBIx is stringifying numeric values somewhere
>>>>>> along the way. In order to ensure they come out numeric, we made this small
>>>>>> patch:
>>>>>> 
>>>>>> --- a/lib/perl5/DBIx/Class/Row.pm
>>>>>> +++ b/lib/perl5/DBIx/Class/Row.pm
>>>>>> @@ -661,10 +661,15 @@ To retrieve all loaded column values as a hash, use
>>>>>> L</get_columns>.
>>>>>>  sub get_column {
>>>>>>    my ($self, $column) = @_;
>>>>>>    $self->throw_exception( "Can't fetch data as class method" ) unless
>>>>>> ref $self;
>>>>>> -  return $self->{_column_data}{$column} if exists
>>>>>> $self->{_column_data}{$column};
>>>>>> +  if (exists $self->{_column_data}{$column}) {
>>>>>> +    return 0 + $self->{_column_data}{$column} if
>>>>>> $self->_is_column_numeric($column);
>>>>>> +    return $self->{_column_data}{$column};
>>>>>> +  }
>>>>>>    if (exists $self->{_inflated_column}{$column}) {
>>>>>> -    return $self->store_column($column,
>>>>>> +    my $ret = $self->store_column($column,
>>>>>>        $self->_deflated_column($column,
>>>>>> $self->{_inflated_column}{$column}));
>>>>>> +    return 0 + $ret if $self->_is_column_numeric($column);
>>>>>> +    return $ret;
>>>>>>    }
>>>>>>    $self->throw_exception( "No such column '${column}'" ) unless
>>>>>> $self->has_column($column);
>>>>>>    return undef;
>>>>>> 
>>>>>> If there's a better way to do this, or some way to prevent DBIx from
>>>>>> stringifying the values in the first place, that would be nice to know too.
>>>>>> Thanks-
>>>>>> 
>>>>>> Augustus
> 
> 
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk