Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 51559
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: Dave [...] WolfeWorks.net
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 0.08115



Subject: update_or_create CURRENT_DATE() problem
Date: Sun, 15 Nov 2009 16:00:41 -0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Dave Wolfe <Dave [...] WolfeWorks.net>
Download (untitled) / with headers
text/plain 1.6k
Ubuntu 9.10 generic with latest updates Perl 1.10.0 Catalyst 5.80002 DBIx::Class 0.08112 Updating a possibly non-existant table entry using the MySQL CURRENT_DATE() function fails in DateTime/Format/MySQL.pm. When I attempt to use the following request: c->model('DB::Hitcount')->update_or_create( { hitdate => {'=' => \'CURRENT_DATE()'}, contentid => $id, hitcount => \'hitcount + 1' }); it throws the error: [debug] Hit count transaction failed for content 51: DBIx::Class::Schema::txn_do(): Can't call method "ymd" on unblessed reference at .../DateTime/Format/MySQL.pm line 84. Because 'hitdate' is a "DATE" data_type, it expects the ref to 'CURRENT_DATE()' to be a DateTime object and doesn't accept a DB function in create/update although it does for search/find. -------------------- Begin Hitcount.pm -------------------- package STM::Schema::Result::Hitcount; use strict; use warnings; use base 'DBIx::Class'; __PACKAGE__->load_components("InflateColumn::DateTime", "Core"); __PACKAGE__->table("HitCount"); __PACKAGE__->add_columns( "hitdate", { data_type => "DATE", default_value => undef, is_nullable => 0, size => 10 }, "contentid", { data_type => "INT", default_value => undef, is_nullable => 0, size => 10 }, "hitcount", { data_type => "INT", default_value => undef, is_nullable => 0, size => 10 }, ); __PACKAGE__->set_primary_key("hitdate", "contentid"); # Created by DBIx::Class::Schema::Loader v0.04005 @ 2009-06-22 15:50:14 # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:BN+t/vl86e7atzM1AqFa7g __PACKAGE__->belongs_to(hitpage => 'STM::Schema::Result::Content', 'contentid'); 1; -------------------- End Contactmap.pm -------------------- -- Dave Wolfe
Download (untitled) / with headers
text/plain 933b
On Sun Nov 15 17:01:05 2009, Dave@WolfeWorks.net wrote: Show quoted text
> Ubuntu 9.10 generic with latest updates > Perl 1.10.0 > Catalyst 5.80002 > DBIx::Class 0.08112 > > Updating a possibly non-existant table entry using the MySQL > CURRENT_DATE() function fails in DateTime/Format/MySQL.pm. > > When I attempt to use the following request: > > c->model('DB::Hitcount')->update_or_create( > { hitdate => {'=' => \'CURRENT_DATE()'}, > contentid => $id, > hitcount => \'hitcount + 1' }); >
In my eye this is not a viable create() statement. Say the update() part would work, and wouldn't find anything. Then the create() would be called with hitcount => \'hitcount + 1' as one of the column arguments, which is a clear case of GIGO. IMHO this is a situation in which update_or_create is not suitable, and instead a separate if (my $row = $rs->find ... ) { $rs->update (...) } else { $rs->create (...) } is in order
Subject: Re: [rt.cpan.org #51559] update_or_create CURRENT_DATE() problem
Date: Sun, 15 Nov 2009 22:01:57 -0600
To: Peter Rabbitson via RT <bug-DBIx-Class [...] rt.cpan.org>
From: Dave Wolfe <Dave [...] WolfeWorks.net>
Download (untitled) / with headers
text/plain 1.7k
Peter, Thanks for the reply. However, if I change the 'hitdate' value to an appropriate DateTime object to keep inflate_column happy: my $dt = DateTime->today(); $c->model('DB::Hitcount')->update_or_create( { hitdate => $dt, contentid => $id, hitcount => \'hitcount + 1' }); it works just fine for both create and update. Since hitcount is specified as "NOT NULL" in the schema, it defaults to zero so adding one to it is quite feasible, no? (At least for MySQL.) Normally I'd agree completely with your Humble Opinion, but DBIx is supposed to make all this simpler so I don't have to worry about such internal details, right? [ Peter Rabbitson via RT writes: ] Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=51559 > > > On Sun Nov 15 17:01:05 2009, Dave@WolfeWorks.net wrote:
> > Ubuntu 9.10 generic with latest updates > > Perl 1.10.0 > > Catalyst 5.80002 > > DBIx::Class 0.08112 > > > > Updating a possibly non-existant table entry using the MySQL > > CURRENT_DATE() function fails in DateTime/Format/MySQL.pm. > > > > When I attempt to use the following request: > > > > c->model('DB::Hitcount')->update_or_create( > > { hitdate => {'=' => \'CURRENT_DATE()'}, > > contentid => $id, > > hitcount => \'hitcount + 1' }); > >
> > In my eye this is not a viable create() statement. Say the update() part > would work, and wouldn't find anything. Then the create() would be > called with hitcount => \'hitcount + 1' as one of the column arguments, > which is a clear case of GIGO. > > IMHO this is a situation in which update_or_create is not suitable, and > instead a separate > > if (my $row = $rs->find ... ) { > $rs->update (...) > } > else { > $rs->create (...) > } > > is in order
-- Dave Wolfe
Download (untitled) / with headers
text/plain 2.2k
On Sun Nov 15 23:03:02 2009, Dave@WolfeWorks.net wrote: Show quoted text
> Peter, > > Thanks for the reply. However, if I change the 'hitdate' value to an > appropriate DateTime object to keep inflate_column happy: > > my $dt = DateTime->today(); > $c->model('DB::Hitcount')->update_or_create( > { hitdate => $dt, > contentid => $id, > hitcount => \'hitcount + 1' }); > > it works just fine for both create and update. Since hitcount is > specified as "NOT NULL" in the schema, it defaults to zero so adding one > to it is quite feasible, no? (At least for MySQL.) Normally I'd agree > completely with your Humble Opinion, but DBIx is supposed to make all > this simpler so I don't have to worry about such internal details, > right? > > [ Peter Rabbitson via RT writes: ]
> > <URL: https://rt.cpan.org/Ticket/Display.html?id=51559 > > > > > On Sun Nov 15 17:01:05 2009, Dave@WolfeWorks.net wrote:
> > > Ubuntu 9.10 generic with latest updates > > > Perl 1.10.0 > > > Catalyst 5.80002 > > > DBIx::Class 0.08112 > > > > > > Updating a possibly non-existant table entry using the MySQL > > > CURRENT_DATE() function fails in DateTime/Format/MySQL.pm. > > > > > > When I attempt to use the following request: > > > > > > c->model('DB::Hitcount')->update_or_create( > > > { hitdate => {'=' => \'CURRENT_DATE()'}, > > > contentid => $id, > > > hitcount => \'hitcount + 1' }); > > >
> > > > In my eye this is not a viable create() statement. Say the update() part > > would work, and wouldn't find anything. Then the create() would be > > called with hitcount => \'hitcount + 1' as one of the column arguments, > > which is a clear case of GIGO. > > > > IMHO this is a situation in which update_or_create is not suitable, and > > instead a separate > > > > if (my $row = $rs->find ... ) { > > $rs->update (...) > > } > > else { > > $rs->create (...) > > } > > > > is in order
>
Yes you have a point. However I was not able to reproduce your exact problem. I fixed[1] a similar issue however, please test this branch[2] and see if this solves your problem as well. Cheers [1] http://dev.catalyst.perl.org/svnweb/bast/revision?rev=7953 [2] http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/branches/create_scalarref_rt51559/
Subject: Re: [rt.cpan.org #51559] update_or_create CURRENT_DATE() problem
Date: Sat, 01 Jan 2011 16:05:39 -0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Dave Wolfe <Dave [...] WolfeWorks.net>
Download (untitled) / with headers
text/plain 2.7k
FWIW, I was finally able to get back to this project after a long stretch of h/w failures and updating to Ubuntu 10.10 and the latest CPAN versions. I reverted the code to the original way I did it and I still get an error: "Can't call method "ymd" on unblessed reference at .../lib/perl5/DateTime/Format/MySQL.pm". Now it's back to the way that uses a pre-computed variable instead of the MySQL function since that works. Peter Rabbitson via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=51559> > > On Sun Nov 15 23:03:02 2009, Dave@WolfeWorks.net wrote:
>> Peter, >> >> Thanks for the reply. However, if I change the 'hitdate' value to an >> appropriate DateTime object to keep inflate_column happy: >> >> my $dt = DateTime->today(); >> $c->model('DB::Hitcount')->update_or_create( >> { hitdate => $dt, >> contentid => $id, >> hitcount => \'hitcount + 1' }); >> >> it works just fine for both create and update. Since hitcount is >> specified as "NOT NULL" in the schema, it defaults to zero so adding one >> to it is quite feasible, no? (At least for MySQL.) Normally I'd agree >> completely with your Humble Opinion, but DBIx is supposed to make all >> this simpler so I don't have to worry about such internal details, >> right? >> >> [ Peter Rabbitson via RT writes: ]
>>> <URL: https://rt.cpan.org/Ticket/Display.html?id=51559> >>> >>> On Sun Nov 15 17:01:05 2009, Dave@WolfeWorks.net wrote:
>>>> Ubuntu 9.10 generic with latest updates >>>> Perl 1.10.0 >>>> Catalyst 5.80002 >>>> DBIx::Class 0.08112 >>>> >>>> Updating a possibly non-existant table entry using the MySQL >>>> CURRENT_DATE() function fails in DateTime/Format/MySQL.pm. >>>> >>>> When I attempt to use the following request: >>>> >>>> c->model('DB::Hitcount')->update_or_create( >>>> { hitdate => {'=' => \'CURRENT_DATE()'}, >>>> contentid => $id, >>>> hitcount => \'hitcount + 1' }); >>>>
>>> In my eye this is not a viable create() statement. Say the update() part >>> would work, and wouldn't find anything. Then the create() would be >>> called with hitcount => \'hitcount + 1' as one of the column arguments, >>> which is a clear case of GIGO. >>> >>> IMHO this is a situation in which update_or_create is not suitable, and >>> instead a separate >>> >>> if (my $row = $rs->find ... ) { >>> $rs->update (...) >>> } >>> else { >>> $rs->create (...) >>> } >>> >>> is in order
> Yes you have a point. However I was not able to reproduce your exact > problem. I fixed[1] a similar issue however, please test this branch[2] > and see if this solves your problem as well. > > Cheers > > [1] http://dev.catalyst.perl.org/svnweb/bast/revision?rev=7953 > [2] > http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/branches/create_scalarref_rt51559/ >
-- /*Dave Wolfe Wolfe Works LLC*/
Download (untitled) / with headers
text/plain 750b
On Sat Jan 01 17:05:58 2011, Dave@WolfeWorks.net wrote: Show quoted text
> FWIW, I was finally able to get back to this project after a long > stretch of h/w failures and updating to Ubuntu 10.10 and the latest > CPAN > versions. I reverted the code to the original way I did it and I still > get an error: "Can't call method "ymd" on unblessed reference at > .../lib/perl5/DateTime/Format/MySQL.pm". Now it's back to the way that > uses a pre-computed variable instead of the MySQL function since that > works. >
Could you please turn this into a dbic-based test case, using [1] as a starting point? Thanks! [1] http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=t/inflate/datetime.t;h=061037a335aead2e204c7cc8e07262ed959f7b26;hb=HEAD
Subject: Re: [rt.cpan.org #51559] update_or_create CURRENT_DATE() problem
Date: Wed, 05 Jan 2011 10:45:37 -0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Dave Wolfe <Dave [...] WolfeWorks.net>
Download (untitled) / with headers
text/plain 1.3k
Peter, I'll try, but my window for working on that project seems to have closed again so please be patient. After updating I have another failure that worked in the older version where a mysql date_format function now gets "me." prepended and the SQL fails since the DATE_FORMAT column is unknown. The SQL if fine without the "me.", which is how it used to be generated. I haven't had a chance to track it down to specific code and how the code changed. Peter Rabbitson via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=51559> > > On Sat Jan 01 17:05:58 2011, Dave@WolfeWorks.net wrote:
>> FWIW, I was finally able to get back to this project after a long >> stretch of h/w failures and updating to Ubuntu 10.10 and the latest >> CPAN >> versions. I reverted the code to the original way I did it and I still >> get an error: "Can't call method "ymd" on unblessed reference at >> .../lib/perl5/DateTime/Format/MySQL.pm". Now it's back to the way that >> uses a pre-computed variable instead of the MySQL function since that >> works. >>
> Could you please turn this into a dbic-based test case, using [1] as a > starting point? Thanks! > > [1] > http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=t/inflate/datetime.t;h=061037a335aead2e204c7cc8e07262ed959f7b26;hb=HEAD
-- /*Dave Wolfe Wolfe Works LLC*/
Download (untitled) / with headers
text/plain 743b
Am Mi 05. Jan 2011, 11:46:02, Dave@WolfeWorks.net schrieb: Show quoted text
> Peter, > > I'll try, but my window for working on that project seems to have > closed > again so please be patient. > > After updating I have another failure that worked in the older version > where a mysql date_format function now gets "me." prepended and the > SQL > fails since the DATE_FORMAT column is unknown. The SQL if fine without > the "me.", which is how it used to be generated. I haven't had a > chance > to track it down to specific code and how the code changed.
I guess because you pass literal sql but don't tell DBIC so. You have to pass it as scalarref like \'your literal sql' if you want that. The older version had a bug that allowed literal sql to slip in.
Subject: Re: [rt.cpan.org #51559] update_or_create CURRENT_DATE() problem
Date: Wed, 05 Jan 2011 15:07:35 -0600
To: bug-DBIx-Class [...] rt.cpan.org
From: Dave Wolfe <Dave [...] WolfeWorks.net>
Download (untitled) / with headers
text/plain 1.3k
Thanks for the reply. I tried putting the select arguments in literal form and still get SQL errors, just different ones. However, I think I misunderstood what Peter originally said because I coded this before InflateColumn::DateTime was available (or I just missed it). I'd much rather use that than all the literal SQL functions that seem to cause so many problems. Fine with me if you close this whole issue as "problem source between keyboard and chair". Alexander Hartmaier via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=51559> > > Am Mi 05. Jan 2011, 11:46:02, Dave@WolfeWorks.net schrieb:
>> Peter, >> >> I'll try, but my window for working on that project seems to have >> closed >> again so please be patient. >> >> After updating I have another failure that worked in the older version >> where a mysql date_format function now gets "me." prepended and the >> SQL >> fails since the DATE_FORMAT column is unknown. The SQL if fine without >> the "me.", which is how it used to be generated. I haven't had a >> chance >> to track it down to specific code and how the code changed.
> I guess because you pass literal sql but don't tell DBIC so. > You have to pass it as scalarref like \'your literal sql' if you want that. > The older version had a bug that allowed literal sql to slip in.
-- /*Dave Wolfe Wolfe Works LLC*/
On Wed Jan 05 16:07:45 2011, Dave@WolfeWorks.net wrote: Show quoted text
> Thanks for the reply. I tried putting the select arguments in literal > form and still get SQL errors, just different ones. However, I think I > misunderstood what Peter originally said because I coded this before > InflateColumn::DateTime was available (or I just missed it).
You did miss it - it's been around forever. Also take a look at http://search.cpan.org/~flora/DBIx-Class-DynamicDefault-0.03/lib/DBIx/Class/DynamicDefault.pm, might do even more of what you want. Show quoted text
> Fine with me if you close this whole issue as "problem > source between keyboard and chair".
Yes and no. The real problem is that the update_or_create interface was created wit a half-assed design back in the day. It combines two *incompatible* APIs in one call, and as such fails on anything non-trivial. If during your time exploring DBIC you get an idea how to make it behave more intuitively - please feel free to reopen the ticket and suggest an improvement. In the meantime closing as per your request.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.