9 use DBIC::SqlMakerTest;
12 my $schema = DBICTest::Schema->connect (DBICTest->_database, { quote_char => '`' });
14 require DBIx::Class::Storage::DBI::mysql;
15 bless ( $schema->storage, 'DBIx::Class::Storage::DBI::mysql' );
17 # check that double-subqueries are properly wrapped
20 my $debugobj = DBIC::DebugObj->new (\$sql, \@bind);
21 my $orig_debugobj = $schema->storage->debugobj;
22 my $orig_debug = $schema->storage->debug;
24 $schema->storage->debugobj ($debugobj);
25 $schema->storage->debug (1);
27 # the expected SQL may seem wastefully nonsensical - this is due to
28 # CD's tablename being \'cd', which triggers the "this can be anything"
29 # mode, and forces a subquery. This in turn forces *another* subquery
30 # because mysql is being mysql
31 # Also we know it will fail - never deployed. All we care about is the
33 eval { $schema->resultset ('CD')->update({ genreid => undef }) };
37 'UPDATE cd SET `genreid` = ? WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )',
39 'Correct update-SQL with double-wrapped subquery',
42 # same comment as above
43 eval { $schema->resultset ('CD')->delete };
47 'DELETE FROM cd WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )',
49 'Correct delete-SQL with double-wrapped subquery',
52 # and a really contrived example (we test it live in t/71mysql.t)
53 my $rs = $schema->resultset('Artist')->search({ name => { -like => 'baby_%' } });
54 my ($count_sql, @count_bind) = @${$rs->count_rs->as_query};
56 $schema->resultset('Artist')->search(
58 -in => $rs->get_column('artistid')
61 )->update({ name => \[ "CONCAT( `name`, '_bell_out_of_', $count_sql )", @count_bind ] });
69 SET `name` = CONCAT(`name`, '_bell_out_of_', (
75 ) `_forced_double_subquery`
81 SELECT `me`.`artistid`
84 ) `_forced_double_subquery` )
89 $schema->storage->debugobj ($orig_debugobj);
90 $schema->storage->debug ($orig_debug);