Discussion:
Table name length constraint?
(too old to reply)
Greg Coates
2015-02-24 00:36:48 UTC
Permalink
Is there code somewhere in DBIx::Class that limits the length of a table
name?

I have a schema package with the following table name:

__PACKAGE__->table('***@FRONT1');

When I try to access data from this table, I get an error saying the
table does not exist, but if I look at the generated SQL in the error
message, the table name has changed to
RDSPRD.RcClssMtgInstrctrDt_D412VD8KCP.

If I change the table name to REC_CLASS_MTG_INSTRUCTOR_DTL, the table
name doesn't get changed in the SQL.

Does anyone have any idea what is going on here?

Thanks,
Greg Coates


_______________________________________________
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-***@lists.scsys.co.uk
Greg Coates
2015-02-24 00:45:30 UTC
Permalink
One note: The ' at ' in the table name should have been an actual at sign.

Greg
Post by Greg Coates
Is there code somewhere in DBIx::Class that limits the length of a
table name?
When I try to access data from this table, I get an error saying the
table does not exist, but if I look at the generated SQL in the error
message, the table name has changed to
RDSPRD.RcClssMtgInstrctrDt_D412VD8KCP.
If I change the table name to REC_CLASS_MTG_INSTRUCTOR_DTL, the table
name doesn't get changed in the SQL.
Does anyone have any idea what is going on here?
Thanks,
Greg Coates
_______________________________________________
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-***@lists.scsys.co.uk
Ben Tilly
2015-02-24 00:49:24 UTC
Permalink
Random guess, DBIx::Class is working around a database limitation.
For example see
http://search.cpan.org/~ribasushi/DBIx-Class-0.082810/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm#relname_to_table_alias
for how your situation could have happened.
Post by Greg Coates
Is there code somewhere in DBIx::Class that limits the length of a table
name?
When I try to access data from this table, I get an error saying the table
does not exist, but if I look at the generated SQL in the error message, the
table name has changed to RDSPRD.RcClssMtgInstrctrDt_D412VD8KCP.
If I change the table name to REC_CLASS_MTG_INSTRUCTOR_DTL, the table name
doesn't get changed in the SQL.
Does anyone have any idea what is going on here?
Thanks,
Greg Coates
_______________________________________________
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/
_______________________________________________
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-***@lists.scsys.co.uk
Greg Coates
2015-02-24 01:00:23 UTC
Permalink
That looks to be what is happening. Is there any way to turn this
behavior off? This is a legitimate table name with a dblink and schema
appended. It works in a direct query.

Thanks,
Greg
Post by Ben Tilly
Random guess, DBIx::Class is working around a database limitation.
For example see
http://search.cpan.org/~ribasushi/DBIx-Class-0.082810/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm#relname_to_table_alias
for how your situation could have happened.
Post by Greg Coates
Is there code somewhere in DBIx::Class that limits the length of a table
name?
When I try to access data from this table, I get an error saying the table
does not exist, but if I look at the generated SQL in the error message, the
table name has changed to RDSPRD.RcClssMtgInstrctrDt_D412VD8KCP.
If I change the table name to REC_CLASS_MTG_INSTRUCTOR_DTL, the table name
doesn't get changed in the SQL.
Does anyone have any idea what is going on here?
Thanks,
Greg Coates
_______________________________________________
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/
_______________________________________________
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/
_______________________________________________
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-***@lists.scsys.co.uk
Hartmaier Alexander
2015-02-24 08:57:37 UTC
Permalink
So the correct way to handle that case is to limit only the tablename
portion, right?
Post by Greg Coates
That looks to be what is happening. Is there any way to turn this
behavior off? This is a legitimate table name with a dblink and
schema appended. It works in a direct query.
Thanks,
Greg
Post by Ben Tilly
Random guess, DBIx::Class is working around a database limitation.
For example see
http://search.cpan.org/~ribasushi/DBIx-Class-0.082810/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm#relname_to_table_alias
for how your situation could have happened.
Post by Greg Coates
Is there code somewhere in DBIx::Class that limits the length of a table
name?
When I try to access data from this table, I get an error saying the table
does not exist, but if I look at the generated SQL in the error message, the
table name has changed to RDSPRD.RcClssMtgInstrctrDt_D412VD8KCP.
If I change the table name to REC_CLASS_MTG_INSTRUCTOR_DTL, the table name
doesn't get changed in the SQL.
Does anyone have any idea what is going on here?
Thanks,
Greg Coates
_______________________________________________
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/
_______________________________________________
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/
_______________________________________________
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/
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*

_______________________________________________
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-***@lists.scsys.co.uk
Greg Coates
2015-02-24 16:29:29 UTC
Permalink
Yes, that is correct.
Post by Hartmaier Alexander
So the correct way to handle that case is to limit only the tablename
portion, right?
Post by Greg Coates
That looks to be what is happening. Is there any way to turn this
behavior off? This is a legitimate table name with a dblink and
schema appended. It works in a direct query.
Thanks,
Greg
Post by Ben Tilly
Random guess, DBIx::Class is working around a database limitation.
For example see
http://search.cpan.org/~ribasushi/DBIx-Class-0.082810/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm#relname_to_table_alias
for how your situation could have happened.
Post by Greg Coates
Is there code somewhere in DBIx::Class that limits the length of a table
name?
When I try to access data from this table, I get an error saying the table
does not exist, but if I look at the generated SQL in the error message, the
table name has changed to RDSPRD.RcClssMtgInstrctrDt_D412VD8KCP.
If I change the table name to REC_CLASS_MTG_INSTRUCTOR_DTL, the table name
doesn't get changed in the SQL.
Does anyone have any idea what is going on here?
Thanks,
Greg Coates
_______________________________________________
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/
_______________________________________________
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/
_______________________________________________
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/
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
_______________________________________________
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/
_______________________________________________
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-***@lists.scsys.co.uk
Augustus Saunders
2015-02-25 21:15:12 UTC
Permalink
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-***@lists.scsys.co.uk
Darren Duncan
2015-02-25 21:59:27 UTC
Permalink
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
Post by Augustus Saunders
--- 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 {
$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-***@lists.scsys.co.uk
Lasse Makholm
2015-02-25 22:43:00 UTC
Permalink
Post by Darren Duncan
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
Post by Darren Duncan
Post by Augustus Saunders
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
--- 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 {
$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/
Ben Tilly
2015-02-25 22:49:42 UTC
Permalink
It gets a lot worse if the JSON is being consumed by a typed language
like ObjectiveC. Data type issues can easily crash the client. I
remember working with a mobile app backed by JSON data being emitted
from Moose, and it was a constant nightmare that a trivial change on
the Perl side would stringify data. The resulting JSON would work
fine for us in AJAX calls, and the mobile client would crash.

You would think that declaring something to be numeric would be enough
of a hint for Moose to be able to emit JSON with numeric data. But
you would think wrong...
Post by Lasse Makholm
Post by Darren Duncan
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
Post by Darren Duncan
Post by Augustus Saunders
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
--- 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 {
$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/
_______________________________________________
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/
_______________________________________________
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-***@lists.scsys.co.uk
Dmitry L.
2015-02-25 22:53:20 UTC
Permalink
Use DBIx::Class::Helper::Row::NumifyGet Luke
Post by Lasse Makholm
Post by Darren Duncan
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
Post by Darren Duncan
Post by Augustus Saunders
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
--- 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 {
$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/
_______________________________________________
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/
--
//wbr, Dmitry L.

_______________________________________________
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-***@lists.scsys.co.uk
Augustus Saunders
2015-02-25 23:30:37 UTC
Permalink
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
Post by Dmitry L.
Use DBIx::Class::Helper::Row::NumifyGet Luke
Post by Lasse Makholm
Post by Darren Duncan
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
Post by Darren Duncan
Post by Augustus Saunders
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
--- 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 {
$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/
_______________________________________________
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/
--
//wbr, Dmitry L.
_______________________________________________
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/
_______________________________________________
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-***@lists.scsys.co.uk
Darren Duncan
2015-02-26 06:15:38 UTC
Permalink
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
Post by Augustus Saunders
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
Post by Dmitry L.
Use DBIx::Class::Helper::Row::NumifyGet Luke
Post by Lasse Makholm
Post by Darren Duncan
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
Post by Darren Duncan
Post by Augustus Saunders
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
--- 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 {
$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-***@lists.scsys.co.uk
Augustus Saunders
2015-02-26 18:51:43 UTC
Permalink
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
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
Post by Augustus Saunders
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
Post by Dmitry L.
Use DBIx::Class::Helper::Row::NumifyGet Luke
Post by Lasse Makholm
Post by Darren Duncan
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
Post by Darren Duncan
Post by Augustus Saunders
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
--- 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 {
$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/
_______________________________________________
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-***@lists.scsys.co.uk
Peter Rabbitson
2015-02-26 13:32:45 UTC
Permalink
Post by Augustus Saunders
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.
You are correct in your overall diagnosis, you are incorrect wrt the
detail and the solution.

The problem is that DBIC "touches" some of the values returned from the
DBD (even something as innocuous as $something->{$column_value} is
enough to break the assumptions of a naive serializer).

The solution is to eliminate all these instances, and definitively not
introducing expensive operations on every single value retrieval. Some
work has been planned for this, but an audit of all code paths is
decidedly not an easy task (and having an incomplete fix in this case is
worse than not having any at all).

For the time being if you need proper *dependable* numeric semantics you
should use the ::Row::NumifyGet helper as suggested, until its
documentation is updated with 'DBIC now does this automatically'.

Cheers


_______________________________________________
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-***@lists.scsys.co.uk
Augustus Saunders
2015-02-26 18:04:09 UTC
Permalink
The problem originates in DBI drivers, so I don't think DBIC can fix it in the general case. You could potentially try and add smarts so that for drivers like Oracle and ODBC you avoid stringifying the values unnecessarily, and I suppose you could force numeric for other databases when you read the values rather than when the user asks for them. But as you said, you'd have to evaluate a lot of code paths. In the interim, numifying when the user asks for the value seems simple. If there is some other way of accessing values that needs to be trapped so that they are numifyed, I'll update those as well, but nobody has indicated that I missed anything.
As for performance, my understanding is that extracting the numeric part of a scalar is essentially free. If checking to see whether a column is numeric is expensive, then that should be cached when the column is created/configured, and not recalculated when extracting values. If it's really important, this can be reduced to one hash lookup, one comparison, and one conditional jump. This is admittedly not free, but it's pretty damn cheap. In all fairness, anybody who cares about this probably isn't going to use a heavy ORM tool in the first place. Hell, witness the fact that most of the database drivers haven't fixed this problem; nobody seems to care about performance *that much*.

Thanks-
Augustus
Post by Augustus Saunders
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.
You are correct in your overall diagnosis, you are incorrect wrt the detail and the solution.
The problem is that DBIC "touches" some of the values returned from the DBD (even something as innocuous as $something->{$column_value} is enough to break the assumptions of a naive serializer).
The solution is to eliminate all these instances, and definitively not introducing expensive operations on every single value retrieval. Some work has been planned for this, but an audit of all code paths is decidedly not an easy task (and having an incomplete fix in this case is worse than not having any at all).
For the time being if you need proper *dependable* numeric semantics you should use the ::Row::NumifyGet helper as suggested, until its documentation is updated with 'DBIC now does this automatically'.
Cheers
_______________________________________________
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/
_______________________________________________
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-***@lists.scsys.co.uk
Peter Rabbitson
2015-02-26 19:05:55 UTC
Permalink
Post by Augustus Saunders
The problem originates in DBI drivers
Not true for most DBDs
Post by Augustus Saunders
In the interim, numifying when the user asks for the value seems simple.
And architecturally incorrect. Just like another 40-odd features I am
holding because they are not good enough for general consumption of the
*extremely wide* userbase of DBIC.
Post by Augustus Saunders
As for performance, my understanding is that extracting the numeric part of a scalar is essentially free.
Nothing is "essentially free".

If checking to see whether a column is numeric is expensive, then that
should be cached when the column is created/configured, and not
recalculated when extracting values.

This has already been the case for years.
Post by Augustus Saunders
In all fairness, anybody who cares about this probably isn't going to use a heavy ORM tool in the first place. Hell, witness the fact that most of the database drivers haven't fixed this problem; nobody seems to care about performance *that much*.
Let's start with "I care" and... leave it at that? :)

TL;DR: Your proposed fix is not going to be merged, besides you were
already given a non-invasive and reliable solution.

Cheers

_______________________________________________
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-***@lists.scsys.co.uk
Martin J. Evans
2015-02-27 09:10:57 UTC
Permalink
Post by Peter Rabbitson
Post by Augustus Saunders
The problem originates in DBI drivers
Not true for most DBDs
Post by Augustus Saunders
In the interim, numifying when the user asks for the value seems simple.
And architecturally incorrect. Just like another 40-odd features I am holding because they are not good enough for general consumption of the *extremely wide* userbase of DBIC.
Post by Augustus Saunders
As for performance, my understanding is that extracting the numeric part of a scalar is essentially free.
Nothing is "essentially free".
If checking to see whether a column is numeric is expensive, then that should be cached when the column is created/configured, and not recalculated when extracting values.
This has already been the case for years.
Post by Augustus Saunders
In all fairness, anybody who cares about this probably isn't going to use a heavy ORM tool in the first place. Hell, witness the fact that most of the database drivers haven't fixed this problem; nobody seems to care about performance *that much*.
Let's start with "I care" and... leave it at that? :)
TL;DR: Your proposed fix is not going to be merged, besides you were already given a non-invasive and reliable solution.
Cheers
Just as a reminder but I'm sure Peter knows this already.

When I came across this problem (not using DBIC) we started adding 0 to every numeric column when it was fetched but that became a pain. Then, with Tim's help I added

http://search.cpan.org/~timb/DBI-1.633/DBI.pm#sql_type_cast

http://search.cpan.org/~timb/DBI-1.633/DBI.pm#bind_col
StrictlyTyped and DiscardString

So when I fetch data with DBD::Oracle or DBD::ODBC which I know are numbers and want to encode as JSON I do something like:

my $s = prepare(q/select a_number from table/);
$s->bind_col(1, \my $dest, {DiscardString => 1, TYPE => SQL_INTEGER});
$s->fetch;
encode_json($dest);

and $dest is a number JSON::XS recognises as such and encodes as NN instead of "NN".

Not that I'm suggesting this is relevant to DBIC, just adding the info of a solution in another arena.

Martin


_______________________________________________
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-***@lists.scsys.co.uk
Peter Rabbitson
2015-02-27 09:19:04 UTC
Permalink
Post by Martin J. Evans
When I came across this problem (not using DBIC) we started adding 0 to
every numeric column when it was fetched but that became a pain. Then,
with Tim's help I added
http://search.cpan.org/~timb/DBI-1.633/DBI.pm#sql_type_cast
http://search.cpan.org/~timb/DBI-1.633/DBI.pm#bind_col
StrictlyTyped and DiscardString
Hi Martin,

Even though I've seen them when scrolling through changelogs, I've
completely forgotten about these options, thanks for reminding me!

I'll investigate in the future if this can be better incorporated into
how DBIC does things. Thanks!


_______________________________________________
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-***@lists.scsys.co.uk
Martin J. Evans
2015-02-27 09:27:35 UTC
Permalink
Post by Peter Rabbitson
Post by Martin J. Evans
When I came across this problem (not using DBIC) we started adding 0 to
every numeric column when it was fetched but that became a pain. Then,
with Tim's help I added
http://search.cpan.org/~timb/DBI-1.633/DBI.pm#sql_type_cast
http://search.cpan.org/~timb/DBI-1.633/DBI.pm#bind_col
StrictlyTyped and DiscardString
Hi Martin,
Even though I've seen them when scrolling through changelogs, I've completely forgotten about these options, thanks for reminding me!
I'll investigate in the future if this can be better incorporated into how DBIC does things. Thanks!
Unfortunately for you, AFAIK, only DBD::ODBC and DBD::Oracle ever added support for them (i.e., support for attributes in bind_col and actually calling sql_type_cast internally) - principally, because those were the only drivers I needed it in.

http://search.cpan.org/~mjevans/DBD-ODBC-1.50/ODBC.pm#Additional_bind_col_attributes
http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#bind_col

although DBI's sql_type_cast might be useful.

Martin

_______________________________________________
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-***@lists.scsys.co.uk
Dagfinn Ilmari Mannsåker
2015-04-26 22:04:45 UTC
Permalink
Post by Martin J. Evans
Post by Peter Rabbitson
Post by Martin J. Evans
When I came across this problem (not using DBIC) we started adding 0 to
every numeric column when it was fetched but that became a pain. Then,
with Tim's help I added
http://search.cpan.org/~timb/DBI-1.633/DBI.pm#sql_type_cast
http://search.cpan.org/~timb/DBI-1.633/DBI.pm#bind_col
StrictlyTyped and DiscardString
Hi Martin,
Even though I've seen them when scrolling through changelogs, I've
completely forgotten about these options, thanks for reminding me!
I'll investigate in the future if this can be better incorporated
into how DBIC does things. Thanks!
Unfortunately for you, AFAIK, only DBD::ODBC and DBD::Oracle ever
added support for them (i.e., support for attributes in bind_col and
actually calling sql_type_cast internally) - principally, because
those were the only drivers I needed it in.
Just FYI: DBD::Pg will automatically return integers as plain IVs
without a string component, and as of the next version it will do that
with bigints as well if your IV and long types are big enough.

https://github.com/bucardo/dbdpg/blob/master/dbdimp.c#L3717-3722
--
"A disappointingly low fraction of the human race is,
at any given time, on fire." - Stig Sandbeck Mathisen


_______________________________________________
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-***@lists.scsys.co.uk
Tim Bunce
2015-02-26 08:57:27 UTC
Permalink
Post by Augustus Saunders
For reasons unknown to us, DBIx is stringifying numeric values somewhere along the way.
In addition to the other points made on this thread...

When working on WebAPI::DBIC I found that Cpanel::JSON::XS was the only
JSON serializer to handle dual-vars (eg stringified numbers) correctly.

Per https://metacpan.org/pod/Cpanel::JSON::XS#cPanel-fork

"fixed encode of numbers for dual-vars. Different string
representations are preserved, but numbers with temporary strings
which represent the same number are here treated as numbers,
not strings. Cpanel::JSON::XS is a bit slower, but preserves
numeric types better."

So WebAPI::DBIC now requires Cpanel::JSON::XS.

Tim.

_______________________________________________
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-***@lists.scsys.co.uk
Peter Rabbitson
2015-02-26 13:35:22 UTC
Permalink
Post by Tim Bunce
Post by Augustus Saunders
For reasons unknown to us, DBIx is stringifying numeric values somewhere along the way.
In addition to the other points made on this thread...
When working on WebAPI::DBIC I found that Cpanel::JSON::XS was the only
JSON serializer to handle dual-vars (eg stringified numbers) correctly.
Per https://metacpan.org/pod/Cpanel::JSON::XS#cPanel-fork
"fixed encode of numbers for dual-vars. Different string
representations are preserved, but numbers with temporary strings
which represent the same number are here treated as numbers,
not strings. Cpanel::JSON::XS is a bit slower, but preserves
numeric types better."
Mojo::JSON does it as well. Fundamentally this is yet again a
performance tradeoff. The logic as seen from PP-space can be seen here:
https://github.com/kraih/mojo/blob/v5.82/lib/Mojo/JSON.pm#L255-L259

_______________________________________________
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-***@lists.scsys.co.uk
Peter Rabbitson
2015-02-24 07:12:33 UTC
Permalink
Post by Greg Coates
Is there code somewhere in DBIx::Class that limits the length of a table
name?
Please try with:

__PACKAGE__->table(\'***@FRONT1');

to indicate to DBIC "I know what I am doing"

_______________________________________________
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-***@lists.scsys.co.uk
Greg Coates
2015-02-24 16:29:21 UTC
Permalink
Yes, that worked. Thanks!
Post by Peter Rabbitson
Post by Greg Coates
Is there code somewhere in DBIx::Class that limits the length of a table
name?
to indicate to DBIC "I know what I am doing"
_______________________________________________
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/
_______________________________________________
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-***@lists.scsys.co.uk
Loading...