9 use DBIC::SqlMakerTest;
12 my $schema = DBICTest::Schema->connect (DBICTest->_database, { quote_char => '`' });
14 require DBIx::Class::Storage::DBI::mysql;
15 *DBIx::Class::Storage::DBI::mysql::_get_server_version = sub { 5 };
16 bless ( $schema->storage, 'DBIx::Class::Storage::DBI::mysql' );
18 # check that double-subqueries are properly wrapped
21 my $debugobj = DBIC::DebugObj->new (\$sql, \@bind);
22 my $orig_debugobj = $schema->storage->debugobj;
23 my $orig_debug = $schema->storage->debug;
25 $schema->storage->debugobj ($debugobj);
26 $schema->storage->debug (1);
28 # the expected SQL may seem wastefully nonsensical - this is due to
29 # CD's tablename being \'cd', which triggers the "this can be anything"
30 # mode, and forces a subquery. This in turn forces *another* subquery
31 # because mysql is being mysql
32 # Also we know it will fail - never deployed. All we care about is the
34 eval { $schema->resultset ('CD')->update({ genreid => undef }) };
38 'UPDATE cd SET `genreid` = ? WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )',
40 'Correct update-SQL with double-wrapped subquery',
43 # same comment as above
44 eval { $schema->resultset ('CD')->delete };
48 'DELETE FROM cd WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )',
50 'Correct delete-SQL with double-wrapped subquery',
53 # and a couple of really contrived examples (we test them live in t/71mysql.t)
54 my $rs = $schema->resultset('Artist')->search({ name => { -like => 'baby_%' } });
55 my ($count_sql, @count_bind) = @${$rs->count_rs->as_query};
57 $schema->resultset('Artist')->search(
59 -in => $rs->get_column('artistid')
62 )->update({ name => \[ "CONCAT( `name`, '_bell_out_of_', $count_sql )", @count_bind ] });
70 SET `name` = CONCAT(`name`, '_bell_out_of_', (
76 ) `_forced_double_subquery`
82 SELECT `me`.`artistid`
85 ) `_forced_double_subquery` )
91 $schema->resultset('CD')->search_related('artist',
92 { 'artist.name' => { -like => 'baby_with_%' } }
101 WHERE `artistid` IN (
104 SELECT `artist`.`artistid`
106 JOIN `artist` `artist`
107 ON `artist`.`artistid` = `me`.`artist`
108 WHERE `artist`.`name` LIKE ?
109 ) `_forced_double_subquery`
115 $schema->storage->debugobj ($orig_debugobj);
116 $schema->storage->debug ($orig_debug);
119 # Test support for straight joins
121 my $cdsrc = $schema->source('CD');
122 my $artrel_info = $cdsrc->relationship_info ('artist');
123 $cdsrc->add_relationship(
125 $artrel_info->{class},
126 $artrel_info->{cond},
127 { %{$artrel_info->{attrs}}, join_type => 'straight' },
130 $cdsrc->resultset->search({}, { prefetch => 'straight_artist' })->as_query,
132 SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year`, `me`.`genreid`, `me`.`single_track`,
133 `straight_artist`.`artistid`, `straight_artist`.`name`, `straight_artist`.`rank`, `straight_artist`.`charfield`
135 STRAIGHT_JOIN `artist` `straight_artist` ON `straight_artist`.`artistid` = `me`.`artist`
138 'straight joins correctly supported for mysql'
142 # Test support for inner joins on mysql v3
144 [ 3 => 'INNER JOIN' ],
147 my ($ver, $join_op) = @$_;
149 no warnings 'redefine';
150 local *DBIx::Class::Storage::DBI::mysql::_get_server_version = sub { $ver };
152 # we do not care at this point if data is available, just do a reconnect cycle
153 # to clear all caches
154 $schema->storage->disconnect;
155 $schema->storage->ensure_connected;
158 $schema->resultset('CD')->search ({}, { prefetch => 'artist' })->as_query,
160 SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year`, `me`.`genreid`, `me`.`single_track`,
161 `artist`.`artistid`, `artist`.`name`, `artist`.`rank`, `artist`.`charfield`
163 $join_op `artist` `artist` ON `artist`.`artistid` = `me`.`artist`
166 "default join type works for version $ver",