| Subject: | find_or_create converts subselects to NULLs |
I think I have found a bug in find_or_create(), although maybe it's
more a case of "it should throw an error if you try this", instead of
silenting doing the Wrong Thing.
Viz:
Assume you have these tables:
table1, with columns "luser" and "role".
The primary key is on "luser, role".
"role" is a foreign key into table2..
table2 has columns "id" and "name".
my $rs = $schema->resultset('table1');
$rs->find_or_create(
{
luser => 'john',
role => { name => 'Admin' },
}
);
In this case, the SELECT that dbix class does to try and find the user
will be WHERE luser='john' AND role=NULL.
When it then creates the row, it will correctly fill it in with the ID
from the role table.
Eg. like:
INSERT INTO mytable (luser,role)
VALUES ('john', (SELECT id FROM role WHERE name='Admin'));
See attached for a failing test demonstrating the issue. (Provided as
both patch, and raw t file)
-Toby
| Subject: | update_or_create_single.t |
use strict;
use warnings;
use Test::More;
use lib qw(t/lib);
use DBICTest;
my $schema = DBICTest->init_schema();
plan tests => 20;
my $artist = $schema->resultset ('Artist')->first;
my $genre = $schema->resultset ('Genre')
->create ({ name => 'par excellence' });
is ($genre->search_related( 'model_cd' )->count, 0, 'No cds yet');
# expect a create
$genre->update_or_create_related ('model_cd', {
artist => $artist,
year => 2009,
title => 'the best thing since sliced bread',
});
# verify cd was inserted ok
is ($genre->search_related( 'model_cd' )->count, 1, 'One cd');
my $cd = $genre->find_related ('model_cd', {});
is_deeply (
{ map { $_, $cd->get_column ($_) } qw/artist year title/ },
{
artist => $artist->id,
year => 2009,
title => 'the best thing since sliced bread',
},
'CD created correctly',
);
# expect a year update on the only related row
# (non-qunique column + unique column as disambiguator)
$genre->update_or_create_related ('model_cd', {
year => 2010,
title => 'the best thing since sliced bread',
});
# re-fetch the cd, verify update
is ($genre->search_related( 'model_cd' )->count, 1, 'Still one cd');
$cd = $genre->find_related ('model_cd', {});
is_deeply (
{ map { $_, $cd->get_column ($_) } qw/artist year title/ },
{
artist => $artist->id,
year => 2010,
title => 'the best thing since sliced bread',
},
'CD year column updated correctly',
);
# expect an update of the only related row
# (update a unique column)
$genre->update_or_create_related ('model_cd', {
title => 'the best thing since vertical toasters',
});
# re-fetch the cd, verify update
is ($genre->search_related( 'model_cd' )->count, 1, 'Still one cd');
$cd = $genre->find_related ('model_cd', {});
is_deeply (
{ map { $_, $cd->get_column ($_) } qw/artist year title/ },
{
artist => $artist->id,
year => 2010,
title => 'the best thing since vertical toasters',
},
'CD title column updated correctly',
);
# expect a year update on the only related row
# (non-unique column only)
$genre->update_or_create_related ('model_cd', {
year => 2011,
});
# re-fetch the cd, verify update
is ($genre->search_related( 'model_cd' )->count, 1, 'Still one cd');
$cd = $genre->find_related ('model_cd', {});
is_deeply (
{ map { $_, $cd->get_column ($_) } qw/artist year title/ },
{
artist => $artist->id,
year => 2011,
title => 'the best thing since vertical toasters',
},
'CD year column updated correctly without a disambiguator',
);
# Test multi-level find-or-create functionality.
# We should be able to find-or-create this twice, with the second time
# returning the same item and genre as the first..
# This first test has the sub-level query on a non-unique key, ie. it
# isn't checked as part of the "find" half of the method.
my $genre_name = 'Highlander';
my %cd_details = (
year => '2010',
title => 'Tasty Treats',
genre => { name => $genre_name }
);
my $genre2 = $schema->resultset ('Genre')
->create ({ name => $genre_name });
my $found1 = $artist->find_or_create_related('cds', { %cd_details });
ok($found1->id, "Found (actually created) album in first iteration");
is($found1->genre->name, $genre_name, ".. with correct genre");
my $found2 = $artist->find_or_create_related('cds', { %cd_details });
ok($found2->id, "Found album in second iteration");
is($found2->id, $found1->id, "..and the IDs are the same.");
is($found2->genre->name, $genre_name, ".. with correct genre");
# Now we repeat the tests, using a sub-level query on one of the critical
# keys that IS used in the "find" part.
# Unfortunately DBIC's generated SQL looks for "artist=NULL" here.
my $artist_name = 'Peanut and Cashew Mix';
my %new_cd = (
year => '2011',
title => 'Various Failures',
artist => { name => $artist_name },
);
my $found3 = $genre2->find_or_create_related('cds', { %new_cd });
ok($found3->id, "Found (actually created) album in first iteration");
is($found3->artist->name, $artist_name, "..with correct artist name");
my $found4 = $genre2->find_or_create_related('cds', { %new_cd });
ok($found4->id, "Found album in second iteration");
is($found4->id, $found3->id, "..and the IDs are the same.");
is($found4->artist->name, $artist_name, ".. with correct artist name");
is($found4->artist->id, $found3->artist->id, "..matching artist ids");
| Subject: | 0001-Add-test-for-sub-levels-in-find_or_create.patch |
From 7649e2e7726fe4a39aec9b2eb1883451c8e9a9ec Mon Sep 17 00:00:00 2001
From: Toby Corkindale <toby@dryft.net>
Date: Mon, 20 Dec 2010 13:27:09 +1100
Subject: [PATCH] Add test for sub-levels in find_or_create.
Currently failing.
---
t/relationship/update_or_create_single.t | 48 +++++++++++++++++++++++++++++-
1 files changed, 47 insertions(+), 1 deletions(-)
diff --git a/t/relationship/update_or_create_single.t b/t/relationship/update_or_create_single.t
index a0e31fb..416a130 100644
--- a/t/relationship/update_or_create_single.t
+++ b/t/relationship/update_or_create_single.t
@@ -7,7 +7,7 @@ use DBICTest;
my $schema = DBICTest->init_schema();
-plan tests => 9;
+plan tests => 20;
my $artist = $schema->resultset ('Artist')->first;
@@ -95,3 +95,49 @@ is_deeply (
},
'CD year column updated correctly without a disambiguator',
);
+
+
+# Test multi-level find-or-create functionality.
+# We should be able to find-or-create this twice, with the second time
+# returning the same item and genre as the first..
+# This first test has the sub-level query on a non-unique key, ie. it
+# isn't checked as part of the "find" half of the method.
+
+my $genre_name = 'Highlander';
+my %cd_details = (
+ year => '2010',
+ title => 'Tasty Treats',
+ genre => { name => $genre_name }
+);
+my $genre2 = $schema->resultset ('Genre')
+ ->create ({ name => $genre_name });
+
+my $found1 = $artist->find_or_create_related('cds', { %cd_details });
+ok($found1->id, "Found (actually created) album in first iteration");
+is($found1->genre->name, $genre_name, ".. with correct genre");
+
+my $found2 = $artist->find_or_create_related('cds', { %cd_details });
+ok($found2->id, "Found album in second iteration");
+is($found2->id, $found1->id, "..and the IDs are the same.");
+is($found2->genre->name, $genre_name, ".. with correct genre");
+
+
+# Now we repeat the tests, using a sub-level query on one of the critical
+# keys that IS used in the "find" part.
+# Unfortunately DBIC's generated SQL looks for "artist=NULL" here.
+my $artist_name = 'Peanut and Cashew Mix';
+my %new_cd = (
+ year => '2011',
+ title => 'Various Failures',
+ artist => { name => $artist_name },
+);
+my $found3 = $genre2->find_or_create_related('cds', { %new_cd });
+ok($found3->id, "Found (actually created) album in first iteration");
+is($found3->artist->name, $artist_name, "..with correct artist name");
+
+my $found4 = $genre2->find_or_create_related('cds', { %new_cd });
+ok($found4->id, "Found album in second iteration");
+is($found4->id, $found3->id, "..and the IDs are the same.");
+is($found4->artist->name, $artist_name, ".. with correct artist name");
+is($found4->artist->id, $found3->artist->id, "..matching artist ids");
+
--
1.7.3.4