9 use DBIC::SqlMakerTest;
11 my $schema = DBICTest->init_schema;
14 my $debugobj = DBIC::DebugObj->new (\$sql, \@bind);
15 my $orig_debugobj = $schema->storage->debugobj;
16 my $orig_debug = $schema->storage->debug;
18 my $tkfks = $schema->resultset('FourKeys_to_TwoKeys');
20 my ($fa, $fb) = $tkfks->related_resultset ('fourkeys')->populate ([
21 [qw/foo bar hello goodbye sensors read_count/],
26 # This is already provided by DBICTest
27 #my ($ta, $tb) = $tkfk->related_resultset ('twokeys')->populate ([
32 my ($ta, $tb) = $schema->resultset ('TwoKeys')
33 ->search ( [ { artist => 1, cd => 1 }, { artist => 2, cd => 2 } ])
36 my $tkfk_cnt = $tkfks->count;
38 my $non_void_ctx = $tkfks->populate ([
39 { autopilot => 'a', fourkeys => $fa, twokeys => $ta, pilot_sequence => 10 },
40 { autopilot => 'b', fourkeys => $fb, twokeys => $tb, pilot_sequence => 20 },
41 { autopilot => 'x', fourkeys => $fa, twokeys => $tb, pilot_sequence => 30 },
42 { autopilot => 'y', fourkeys => $fb, twokeys => $ta, pilot_sequence => 40 },
44 is ($tkfks->count, $tkfk_cnt += 4, 'FourKeys_to_TwoKeys populated succesfully');
47 # Make sure the forced group by works (i.e. the joining does not cause double-updates)
50 # create a resultset matching $fa and $fb only
51 my $fks = $schema->resultset ('FourKeys')
52 ->search ({ map { $_ => [1, 2] } qw/foo bar hello goodbye/}, { join => 'fourkeys_to_twokeys' });
54 is ($fks->count, 4, 'Joined FourKey count correct (2x2)');
56 $schema->storage->debugobj ($debugobj);
57 $schema->storage->debug (1);
58 $fks->update ({ read_count => \ 'read_count + 1' });
59 $schema->storage->debugobj ($orig_debugobj);
60 $schema->storage->debug ($orig_debug);
66 SET read_count = read_count + 1
67 WHERE ( bar = ? AND foo = ? AND goodbye = ? AND hello = ? ) OR ( bar = ? AND foo = ? AND goodbye = ? AND hello = ? )',
68 [ map { "'$_'" } ( (1) x 4, (2) x 4 ) ],
69 'Correct update-SQL without multicolumn in support',
72 is ($fa->discard_changes->read_count, 11, 'Update ran only once on joined resultset');
73 is ($fb->discard_changes->read_count, 21, 'Update ran only once on joined resultset');
75 # try the same sql with forced multicolumn in
76 $schema->storage->_use_multicolumn_in (1);
77 $schema->storage->debugobj ($debugobj);
78 $schema->storage->debug (1);
79 eval { $fks->update ({ read_count => \ 'read_count + 1' }) }; # this can't actually execute, we just need the "as_query"
80 $schema->storage->_use_multicolumn_in (undef);
81 $schema->storage->debugobj ($orig_debugobj);
82 $schema->storage->debug ($orig_debug);
88 SET read_count = read_count + 1
90 (foo, bar, hello, goodbye) IN (
91 SELECT me.foo, me.bar, me.hello, me.goodbye
93 WHERE ( bar = ? OR bar = ? ) AND ( foo = ? OR foo = ? ) AND ( goodbye = ? OR goodbye = ? ) AND ( hello = ? OR hello = ? )
97 [ map { "'$_'" } ( (1, 2) x 4 ) ],
98 'Correct update-SQL with multicolumn in support',
102 # Make sure multicolumn in or the equivalent functions correctly
105 my $sub_rs = $tkfks->search (
107 { map { $_ => 1 } qw/artist.artistid cd.cdid fourkeys.foo fourkeys.bar fourkeys.hello fourkeys.goodbye/ },
108 { map { $_ => 2 } qw/artist.artistid cd.cdid fourkeys.foo fourkeys.bar fourkeys.hello fourkeys.goodbye/ },
111 join => [ 'fourkeys', { twokeys => [qw/artist cd/] } ],
115 is ($sub_rs->count, 2, 'Only two rows from fourkeys match');
117 # attempts to delete a grouped rs should fail miserably
119 sub { $sub_rs->search ({}, { distinct => 1 })->delete },
120 qr/attempted a delete operation on a resultset which does group_by/,
121 'Grouped rs update/delete not allowed',
124 # grouping on PKs only should pass
128 group_by => [ reverse $sub_rs->result_source->primary_columns ], # reverse to make sure the PK-list comparison works
130 )->update ({ pilot_sequence => \ 'pilot_sequence + 1' });
133 [ $tkfks->search ({ autopilot => [qw/a b x y/]}, { order_by => 'autopilot' })
134 ->get_column ('pilot_sequence')->all
137 'Only two rows incremented',
140 # also make sure weird scalarref usage works (RT#51409)
142 \ 'pilot_sequence BETWEEN 11 AND 21',
143 )->update ({ pilot_sequence => \ 'pilot_sequence + 1' });
146 [ $tkfks->search ({ autopilot => [qw/a b x y/]}, { order_by => 'autopilot' })
147 ->get_column ('pilot_sequence')->all
150 'Only two rows incremented (where => scalarref works)',
154 my $rs = $schema->resultset('FourKeys_to_TwoKeys')->search (
157 { 'me.pilot_sequence' => 12 },
158 { 'me.autopilot' => 'b' },
162 lives_ok { $rs->update({ autopilot => 'z' }) }
163 'Update with table name qualifier in -or conditions lives';
165 [ $tkfks->search ({ pilot_sequence => [12, 22]})
166 ->get_column ('autopilot')->all
169 '... and yields the right data',
175 is ($tkfks->count, $tkfk_cnt -= 2, 'Only two rows deleted');
177 # make sure limit-only deletion works
178 cmp_ok ($tkfk_cnt, '>', 1, 'More than 1 row left');
179 $tkfks->search ({}, { rows => 1 })->delete;
180 is ($tkfks->count, $tkfk_cnt -= 1, 'Only one row deleted');
183 # Make sure prefetch is properly stripped too
184 # check with sql-equality, as sqlite will accept bad sql just fine
185 $schema->storage->debugobj ($debugobj);
186 $schema->storage->debug (1);
187 $schema->resultset('CD')->search(
188 { year => { '!=' => 2010 } },
189 { prefetch => 'liner_notes' },
192 $schema->storage->debugobj ($orig_debugobj);
193 $schema->storage->debug ($orig_debug);
198 'DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM cd me WHERE ( year != ? ) ) )',
200 'Update on prefetching resultset strips prefetch correctly'