> This is arguably a bug in DBD::mysql, but as far as RDBO is concerned,
For what it is worth, I dont think it is strictly DBD::mysql's fault, it is Mysql itself. http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html It might be fixed with mysql 5.0.2, but I do not have that version around to test with. Unfortunately I ran into this problem this weekend, and hunted around until I found the doc above. -Cory On Wednesday 17 January 2007 7:01 am, John Siracusa wrote: > 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 ------------------------------------------------------------------------- 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
