| Subject: | duplicate inserts with multicreate causing error |
Please see attached test case, which should run fine as a stand alone.
Basically if you try to multi-create along a 'belongs_to' to something
that has a 'has_many' set of the starting table, unless you specify an
empty arrayref for the has_many, you get a duplicate insert into the
starting table, which generates a constrain error.
If you run the test case test file attached, with DBIC_TRACE, you'll see
the duplicate attempts to insert, followed by the exception message. If
you uncomment the "relation_rs" bit at the end of the test, you'll see
it runs fine.
I put a while into this, but can't really trace it down. From what I
can tell, if you don't have the 'relation_rs' bit, find_or_create tries
to create a new result, but if you specify it as '[]', everything is good.
If you can give me some pointers I will be happy to try again, but right
now the multicreate stuff is a bit fuzzy for me. Thank you!
| Subject: | multicreateextra.t |
use Test::More; {
use strict;
use warnings;
use_ok 'DBIx::Class','0.08108';
use_ok 'SQL::Translator', '0.09003';
package MyApp::Schema::Entry; {
use base 'DBIx::Class';
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('entry');
__PACKAGE__->add_columns(
entry_id => {
data_type=>'integer',
is_auto_increment=>1,
},
title => {
data_type=>'varchar',
},
);
__PACKAGE__->set_primary_key('entry_id');
__PACKAGE__->might_have(
object => 'MyApp::Schema::Object',
{'foreign.object_id'=>'self.entry_id'},
);
__PACKAGE__->might_have(
subject => 'MyApp::Schema::Subject',
{'foreign.subject_id'=>'self.entry_id'},
);
}
package MyApp::Schema::Object; {
use base 'DBIx::Class';
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('object');
__PACKAGE__->add_columns(
object_id => {data_type=>'integer'},
);
__PACKAGE__->set_primary_key('object_id');
__PACKAGE__->belongs_to(
entry => 'MyApp::Schema::Entry',
{'foreign.entry_id' => 'self.object_id'},
);
__PACKAGE__->has_many(
relation_rs => 'MyApp::Schema::Relation',
{'foreign.fk_object_id' => 'self.object_id'},
);
}
package MyApp::Schema::Subject; {
use base 'DBIx::Class';
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('subject');
__PACKAGE__->add_columns(
subject_id => {data_type=>'integer'},
);
__PACKAGE__->set_primary_key('subject_id');
__PACKAGE__->belongs_to(
entry => 'MyApp::Schema::Entry',
{'foreign.entry_id' => 'self.subject_id'},
);
__PACKAGE__->has_many(
relation_rs => 'MyApp::Schema::Relation',
{'foreign.fk_subject_id' => 'self.subject_id'},
);
}
package MyApp::Schema::Relation; {
use base 'DBIx::Class';
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('relation');
__PACKAGE__->add_columns(
fk_object_id => {
data_type=>'integer'
},
fk_subject_id => {
data_type=>'integer'
},
);
__PACKAGE__->belongs_to(
object => 'MyApp::Schema::Object',
{'foreign.object_id' => 'self.fk_object_id'},
);
__PACKAGE__->belongs_to(
subject => 'MyApp::Schema::Subject',
{'foreign.subject_id' => 'self.fk_subject_id'},
);
__PACKAGE__->set_primary_key(qw/
fk_object_id
fk_subject_id
/);
}
package MyApp::Schema; {
use base 'DBIx::Class::Schema';
MyApp::Schema->load_classes(qw/
Entry
Object
Subject
Relation
/);
}
Test::More::ok my $schema = MyApp::Schema->connect('dbi:SQLite:dbname=:memory:','','')
=> 'Connected to sqlite in memory database';
$schema->deploy();
## Create some top level entries.
Test::More::ok my $obama = $schema->resultset('Entry')->create({title=>'obama'})
=> 'new entry "obama"';
Test::More::ok my $president = $schema->resultset('Entry')->create({title=>'president'})
=> 'new entry "president"';
Test::More::ok my $benedict = $schema->resultset('Entry')->create({title=>'benedict'})
=> 'new entry "benedict"';
Test::More::ok my $pope = $schema->resultset('Entry')->create({title=>'pope'})
=> 'new entry "pope"';
## Totally fine
Test::More::ok my $object_benedict = $schema->resultset('Object')->create({
entry => $benedict,
}), 'created object benedict';
Test::More::ok my $subject_pope = $schema->resultset('Subject')->create({
entry => $pope,
}), 'created subject pope';
## Create a Relation. If this works, I imagine the next one should work as well
Test::More::ok my $relation_benedict_pope = $schema->resultset('Relation')->create({
subject => $subject_pope,
object => $object_benedict,
}), 'multicreate via objects';
## Create a Relation. This dies without the 'relation_rs => []' thing.
Test::More::ok my $relation_obama_president = $schema->resultset('Relation')->create({
subject => {
entry => $obama,
### relation_rs => [],
},
object => {
entry => $president,
### relation_rs => [],
},
}), "multicreate m2m bug";
Test::More::done_testing();
}
__END__
So this dies with the following (error message isolated for clarity)
BEGIN WORK
SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2'
SELECT me.entry_id, me.title FROM entry me WHERE ( me.entry_id = ? ): '2'
INSERT INTO object ( object_id) VALUES ( ? ): '2'
SELECT me.fk_object_id, me.fk_subject_id FROM relation me WHERE ( ( me.fk_object_id = ? AND me.fk_subject_id = ? ) ): '2', '1'
SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2'
SELECT me.subject_id FROM subject me WHERE ( me.subject_id = ? ): '1'
SELECT me.entry_id, me.title FROM entry me WHERE ( me.entry_id = ? ): '1'
INSERT INTO subject ( subject_id) VALUES ( ? ): '1'
SELECT me.fk_object_id, me.fk_subject_id FROM relation me WHERE ( ( me.fk_object_id = ? AND me.fk_subject_id = ? ) ): '2', '1'
SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2'
SELECT me.subject_id FROM subject me WHERE ( me.subject_id = ? ): '1'
INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? ): '2', '1'
INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? ): '2', '1'
DBI Exception: DBD::SQLite::st execute failed: columns fk_object_id, fk_subject_id are not unique [for Statement "INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? )"] at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Schema.pm line 1025
Which is due to the double insert.
However, if we change the multicreate to:
$schema->resultset('Relation')->create({
subject => {
entry => $obama,
relation_rs => [],
},
object => {
entry => $president,
relation_rs => [],
},
});
Everything works correctly. Here's the sql that it generates:
SELECT me.object_id FROM object me WHERE ( 1 = 0 ):
SELECT me.subject_id FROM subject me WHERE ( 1 = 0 ):
BEGIN WORK
SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2'
INSERT INTO object ( object_id) VALUES ( ? ): '2'
SELECT me.subject_id FROM subject me WHERE ( me.subject_id = ? ): '1'
INSERT INTO subject ( subject_id) VALUES ( ? ): '1'
INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? ): '2', '1'
COMMIT
As you can see, the SQL is significantly better.
For More assistance, here's what the debug with DBIC_MULTICREATE_DEBUG=1
MC MyApp::Schema::Entry=HASH(0x19f7184) fetching missing PKs entry_id
MC MyApp::Schema::Relation=HASH(0x19f7178) constructing object via find_or_new_related at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 122.
SELECT me.object_id FROM object me WHERE ( 1 = 0 ):
MC MyApp::Schema::Object=HASH(0x1a003e0) uninserted relation_rs MyApp::Schema::Relation=HASH(0x19f7178) (1 of 1)
MC MyApp::Schema::Relation=HASH(0x19f7178) uninserted object MyApp::Schema::Object=HASH(0x1a003e0)
MC MyApp::Schema::Relation=HASH(0x19f7178) constructing subject via find_or_new_related at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 122.
SELECT me.subject_id FROM subject me WHERE ( 1 = 0 ):
MC MyApp::Schema::Subject=HASH(0x1a004dc) uninserted relation_rs MyApp::Schema::Relation=HASH(0x19f7178) (1 of 1)
MC MyApp::Schema::Relation=HASH(0x19f7178) uninserted subject MyApp::Schema::Subject=HASH(0x1a004dc)
BEGIN WORK
MC MyApp::Schema::Relation=HASH(0x19f7178) pre-reconstructing object MyApp::Schema::Object=HASH(0x1a003e0)
SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2'
MC MyApp::Schema::Object=HASH(0x1a30890) uninserted relation_rs MyApp::Schema::Relation=HASH(0x19f7178) (1 of 1)
MC MyApp::Schema::Object=HASH(0x1a30890) pre-reconstructing entry MyApp::Schema::Entry=HASH(0x19f7184)
SELECT me.entry_id, me.title FROM entry me WHERE ( me.entry_id = ? ): '2'
MC MyApp::Schema::Object=HASH(0x1a30890) inserting (object_id, 2)
INSERT INTO object ( object_id) VALUES ( ? ): '2'
MC MyApp::Schema::Object=HASH(0x1a30890) re-creating relation_rs MyApp::Schema::Relation=HASH(0x19f7178) at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 376.
SELECT me.fk_object_id, me.fk_subject_id FROM relation me WHERE ( ( me.fk_object_id = ? AND me.fk_subject_id = ? ) ): '2', '1'
MC MyApp::Schema::Relation=HASH(0x1a318b4) uninserted object MyApp::Schema::Object=HASH(0x1a003e0)
MC MyApp::Schema::Relation=HASH(0x1a318b4) uninserted subject MyApp::Schema::Subject=HASH(0x1a004dc)
MC MyApp::Schema::Relation=HASH(0x1a318b4) pre-reconstructing object MyApp::Schema::Object=HASH(0x1a003e0)
SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2'
MC MyApp::Schema::Relation=HASH(0x1a318b4) pre-reconstructing subject MyApp::Schema::Subject=HASH(0x1a004dc)
SELECT me.subject_id FROM subject me WHERE ( me.subject_id = ? ): '1'
MC MyApp::Schema::Subject=HASH(0x1a37658) uninserted relation_rs MyApp::Schema::Relation=HASH(0x19f7178) (1 of 1)
MC MyApp::Schema::Subject=HASH(0x1a37658) pre-reconstructing entry MyApp::Schema::Entry=HASH(0x19f7100)
SELECT me.entry_id, me.title FROM entry me WHERE ( me.entry_id = ? ): '1'
MC MyApp::Schema::Subject=HASH(0x1a37658) inserting (subject_id, 1)
INSERT INTO subject ( subject_id) VALUES ( ? ): '1'
MC MyApp::Schema::Subject=HASH(0x1a37658) re-creating relation_rs MyApp::Schema::Relation=HASH(0x19f7178) at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 376.
SELECT me.fk_object_id, me.fk_subject_id FROM relation me WHERE ( ( me.fk_object_id = ? AND me.fk_subject_id = ? ) ): '2', '1'
MC MyApp::Schema::Relation=HASH(0x1a38300) uninserted subject MyApp::Schema::Subject=HASH(0x1a004dc)
MC MyApp::Schema::Relation=HASH(0x1a38300) pre-reconstructing object MyApp::Schema::Object=HASH(0x1a003e0)
SELECT me.object_id FROM object me WHERE ( me.object_id = ? ): '2'
MC MyApp::Schema::Relation=HASH(0x1a38300) pre-reconstructing subject MyApp::Schema::Subject=HASH(0x1a004dc)
SELECT me.subject_id FROM subject me WHERE ( me.subject_id = ? ): '1'
MC MyApp::Schema::Relation=HASH(0x1a38300) inserting (fk_object_id, 2, fk_subject_id, 1)
INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? ): '2', '1'
MC MyApp::Schema::Subject=HASH(0x1a37658) new relation_rs MyApp::Schema::Relation=HASH(0x19f7178) at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Row.pm line 382.
MC MyApp::Schema::Relation=HASH(0x1a318b4) inserting (fk_object_id, 2, fk_subject_id, 1)
INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? ): '2', '1'
DBI Exception: DBD::SQLite::st execute failed: columns fk_object_id, fk_subject_id are not unique [for Statement "INSERT INTO relation ( fk_object_id, fk_subject_id) VALUES ( ?, ? )"] at /opt/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Schema.pm line 1025