I'm forwarding this private email thread to the list with the
permission of the (now anonymized) sender because it's about an
unexpected situation that many RDBO users may face. Yes, it revolves
around yet another fun feature of MySQL/DBD::mysql. How did you
guess? :)
Anyway, here it is, for posterity, the list archives, and ultimately, google.
-John
---cut---
Subject: Rose::DB::Object::load question [ Version 0.758 ]
Date: Mon, 15 Jan 2007 23:24:32 +0100
From: XXXX XXXXX <[EMAIL PROTECTED]>
Dear John,
I was not very happy with "DBIx::Class" and "Class::DBI", so because I
was already enjoying your module "Rose::HTML::Objects", I've decided to
try "Rose::DB::Object". (By the way, why HTML::Object*_s_* (with s) and
DB::Object (without s) ? )
My first impression is very good, congratulation! But I came on the
following issue:
I have a table with 2 _unique_ keys having each 2 fields [[/x/, /y/],
[/x/, /z/]] plus a primary key (/id/). Everything is fine If I'm loading
an object with (/x,//y)/, but if I'm loading with (/x,z/), then the
"load" method seems to still try to use /x/ and /y/ ( y = '' ) and thus
does not find the object.
I had a look at the code and the problem seems to be that the accessor
(used at line 173) never returns "undef"! I've made a small test and if
a field is not defined, the accessor returned the empty string, but not
"undef". So the first unique key always matches.
164:# Prefer unique keys where we have defined values for all
165:# key calumns, but fall back to teh first unique key found
166:# where we have at least one defined value.
167:foreach my $cols ($meta->unique_keys_column_names)
168:{
169: my $defined = 0;
170: @key_columns = @$cols;
171: @key_methods = map { $meta->column_accessor_method_name($_) }
@key_columns;
172: @key_values = map { $defined++ if(defined $_); $_ }
173: map { $self->$_() } @key_methods;
174:
175: if($defined == @key_columns)
176: {
177: $found_key = 1;
178: last;
189: }
(By the way, note the 2 spelling errors at line 165).
Even if the field is explicitly set to undef : "$o->y(undef)", $o->y()"
still returns the empty string.
/
(my code:)/
$pferd->Name($o{Pferdename});
$pferd->PassNummer(undef);
print "Value is ", (defined $pferd->PassNummer ? "Defined" : "UNDEFINED"), "\n";
print "and is '" . $pferd->PassNummer . "'\n";
gives:
Value is Defined
and is ''
What should I do to make the accessor returning "undef" if the value of
the field is not defined, and so make the "load" method work correctly?
Is there a bug or did I do something wrong?
Thank you in advance for your support.
Regards,
-- XXXX XXXXX
---cut---
Subject: Re: Rose::DB::Object::load question [ Version 0.758 ]
To: "XXXX XXXXX" <[EMAIL PROTECTED]>
From: "John Siracusa" <[EMAIL PROTECTED]>
Date: Tue, 16 Jan 2007 10:08:31 -0500
On 1/15/07, XXXX XXXXX <[EMAIL PROTECTED]> wrote:
> $pferd->PassNummer(undef);
> print "Value is ", (defined $pferd->PassNummer ? "Defined" : "UNDEFINED"),
> "\n";
> print "and is '" . $pferd->PassNummer . "'\n";
> gives:
> Value is Defined and is ''
What kind of column is PassNummer? Can you show me the database
definition for that column as well as the definition in your
RDBO-derived class?
(I suspect that you're using MySQL, which tends to make the default
value for non-null (var)char columns an empty string. This default
value would be picked up by the Loader/auto_initalize() and would
cause the column to exhibit the behavior you describe.)
-John
---cut---
Subject: Re: Rose::DB::Object::load question [ Version 0.758 ]
Date: Tue, 16 Jan 2007 17:28:22 +0100
To: [EMAIL PROTECTED]
From: XXXX XXXXX <[EMAIL PROTECTED]>
Hi John,
Thank you very much for your answer.
So here is the definition of my table:
CREATE TABLE `live_pferd` (
`PferdId` int(11) NOT NULL auto_increment,
`VnummerSVPS` varchar(10) NOT NULL,
`PassNummer` decimal(10,0) NOT NULL,
`Name` varchar(26) NOT NULL,
...
PRIMARY KEY (`PferdId`),
UNIQUE KEY `idx0` (`VnummerSVPS`,`PassNummer`),
UNIQUE KEY `idx1` (`VnummerSVPS`,`Name`)
)
...and I'm using auto_initialize:
package Hippique::DB::Live::Pferd;
use strict;
use base qw(Hippique::DB::Live::Object);
__PACKAGE__->meta->setup(
table => 'live_pferd',
auto_initialize => [],
);
... and yes, I'm using MySQL!
This is what I'm trying to do
my $pferd = Hippique::DB::Live::Pferd->new(
VnummerSVPS => "1234",
);
$pferd->Name("blabla");
$pferd->load; # Ooops!
and it fails to load the object "$pferd->PassNummer" is empty and not
"undef"; so it selects the wrong unique index (idx0 instead of idx1).
But actually MySQL has not yet be involved at this point; it's the
accessor of the "in-memory" object $pferd that returns "" instead of undef.
I could explain the phenomenon by the "NOT NULL" in the column
definition of the database, but actually the columns are "NOT NULL".
It's just that I'm trying to load the object either by (VnummerSVP,
PassNummer) or by (VnummerSVPS, Name) depending on which field is
defined. In the database, the 3 fields are defined and are not-null:
my $pferd = Hippique::DB::Live::Pferd->new(
VnummerSVPS => "1234",
);
if (defined $horse_name) {
$pferd->Name($horse_name);
} else {
$pferd->PassNummer($horse_nr);
}
$pferd->load;
So maybe I'm using the "load" method in a wrong way and I should write
my own "SELECT" instead... But using the "load" methods seems much simpler!
I also have another issue: I have a case where the database is a SQLite
file and the file location is not known as compile time. Actually the
SQLite file name is given as an argument, so it is only known during the
run time. Is it still possible to use Rose::DB in this configuration?
Thank you very much for your support and for the great modules that you
are writing.
-- XXXX XXXXX
---cut---
Subject: Re: Rose::DB::Object::load question [ Version 0.758 ]
To: "XXXX XXXXX" <[EMAIL PROTECTED]>
From: "John Siracusa" <[EMAIL PROTECTED]>
Date: Tue, 16 Jan 2007 11:55:24 -0500
On 1/16/07, XXXX XXXXX <[EMAIL PROTECTED]> wrote:
> CREATE TABLE `live_pferd` (
> `PferdId` int(11) NOT NULL auto_increment,
> `VnummerSVPS` varchar(10) NOT NULL,
> `PassNummer` decimal(10,0) NOT NULL,
> `Name` varchar(26) NOT NULL,
> ...
> PRIMARY KEY (`PferdId`),
> UNIQUE KEY `idx0` (`VnummerSVPS`,`PassNummer`),
> UNIQUE KEY `idx1` (`VnummerSVPS`,`Name`)
> )
>
> ...and I'm using auto_initialize:
> ... and yes, I'm using MySQL!
Do this:
print Hippique::DB::Live::Pferd->meta->perl_class_definition;
and I think you'll see that the VnummerSVPS column has a default value
of empty string:
VnummerSVPS => { type => 'varchar', default => '', length => 10,
not_null => 1 },
That's why the VnummerSVPS() column accessor method always returns an
empty string, even when you set it to undef. This is happening
because DBI's column_info() method is returning an empty string as the
COLUMN_DEF value:
'COLUMN_DEF' => ''
http://search.cpan.org/dist/DBI/DBI.pm#column_info
This is arguably a bug in DBD::mysql, but as far as RDBO is concerned,
the VnummerSVPS column definition is a correct reflection of what's in
the database as determined by DBI.
To work around this, you can set the default value to undef in a
pre_init_hook subroutine:
__PACKAGE__->meta->setup(
table => 'live_pferd',
pre_init_hook => \&fixup_column_defaults,
auto_initialize => [],
);
sub fixup_column_defaults
{
my($meta) = shift;
foreach my $column ($meta->columns)
{
$column->default(undef);
}
}
If you then do this again:
print Hippique::DB::Live::Pferd->meta->perl_class_definition;
I think you'll see that the default => '' column attribute is gone.
> So maybe I'm using the "load" method in a wrong way and I should write
> my own "SELECT" instead... But using the "load" methods seems much simpler!
You're doing it right. MySQL and DBD::mysql just tend to do weird
things sometimes...
> I also have another issue: I have a case where the database is a SQLite
> file and the file location is not known as compile time. Actually the
> SQLite file name is given as an argument, so it is only known during the
> run time. Is it still possible to use Rose::DB in this configuration?
Sure, just call Rose::DB's modify_db() method to set the correct
information in your registered data source as soon as you know it.
http://search.cpan.org/dist/Rose-DB/lib/Rose/DB.pm#modify_db
-John
---cut---
Subject: Re: Rose::DB::Object::load question [ Version 0.758 ]
To: John Siracusa <[EMAIL PROTECTED]>
From: XXXX XXXXX <[EMAIL PROTECTED]>
Date: Tue, 16 Jan 2007 23:05:01 +0100
Thank you for your *great* support.
actually, I've just changed the default for the columns in the unique keys:
sub fixup_column_defaults {
my $meta = shift;
$meta->column("PassNummer")->default(undef);
$meta->column("Name")->default(undef);
}
... and YES! everything works as expected now.
Actually it was OK (and even convenient) for me to leave "" as a default
for the other fields.
So once again, a huge "Thank You" and congratulation for you job! Your
support is so efficient and this is your better selling argument :-)
It's decided, I'm dropping Class::DBI and DBIx::Class and I'm adopting
Rose::DB::Object! And after having read R. Schwartz's Column 86 (Oct
2006) of the Linux Magazine, I'm even more convinced that this is the
right decision!
Rose Rulez!
Cheers,
-- XXXX XXXXX
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
