[Dbix-class] DBIx::Class::ResultSet::RecursiveUpdate - announcement and RFC

[Zbigniew Lukasiak]

On Sun, Sep 28, 2008 at 10:00 PM, Daniel Westermann-Clark <dwc at pobox.com> wrote:
> On 2008-09-28 17:17:33 +0200, Zbigniew Lukasiak wrote:
>> update_or_create relies on find - if you don't pass the PK - then
>> find will issue an unrestricted search and return a random row (and
>> issue the warning), then that random row will be updated, create
>> will never be called.  This is assuming you add { key => primary} -
>> if you don't do that then find will fall back to the old behaviour
>> and also return a random row.
>
> That's also assuming you don't have any other unique constraints
> defined. At least, that's how it was designed.


That is a bit more complicated - you need to also have all columns of
that constraint in the query.   So update_or_create works on tables
with auto_increment keys only if you have an additional unique
constraint in that table and use it in the query.


Here is an illustration:
zby at zby:~/progs/DBIC$ cat update_or_create_illustration.pl
use strict;
use warnings;

use lib qw(t/lib);
use DBICTest;

my $schema = DBICTest->init_schema();

my $cd_rs = $schema->resultset("CD");
$schema->storage->debug(1);
my $art1 = $cd_rs->search( { year => '1999' } )->next;
my $art2 = $cd_rs->update_or_create( { year => '1999' } );
print "equals\n" if $art1->id == $art2->id;

zby at zby:~/progs/DBIC$ perl -Ilib update_or_create_illustration.pl
SELECT me.cdid, me.artist, me.title, me.year, me.genreid FROM cd me
WHERE ( year = ? ): '1999'
SELECT me.cdid, me.artist, me.title, me.year, me.genreid FROM cd me
WHERE ( me.year = ? ): '1999'
equals
zby at zby:~/progs/DBIC$


I run it from a DBIC directory tree just updated from svn - so it was
using the standard test library from DBIC ( the CD table has an
additional constraint: __PACKAGE__->add_unique_constraint([ qw/artist
title/ ]); ).


By the way when I put there
my $art2 = $cd_rs->update_or_create( { year => '2000' } );

Then it fails with:

zby at zby:~/progs/DBIC$ perl -Ilib update_or_create_illustration.pl
SELECT me.cdid, me.artist, me.title, me.year, me.genreid FROM cd me
WHERE ( year = ? ): '1999'
SELECT me.cdid, me.artist, me.title, me.year, me.genreid FROM cd me
WHERE ( me.year = ? ): '2000'
INSERT INTO cd (year) VALUES (?): '2000'
DBIx::Class::ResultSet::update_or_create(): DBI Exception:
DBD::SQLite::st execute failed: cd.artist may not be NULL(19) at
dbdimp.c line 403 [for Statement "INSERT INTO cd (year) VALUES (?)"]
at update_or_create_illustration.pl line 12
zby at zby:~/progs/DBIC$

I have not yet analyzed what causes that.

-- 
Zbigniew Lukasiak
http://brudnopis.blogspot.com/
http://perlalchemy.blogspot.com/