Move find_co_root into DBICTest::Util
[dbsrgits/DBIx-Class-Historic.git] / t / sqlmaker / mysql.t
CommitLineData
45150bc4 1use strict;
2use warnings;
3
4use Test::More;
5
6use lib qw(t/lib);
a5a7bb73 7use DBICTest ':DiffSQL';
45150bc4 8
c7e85630 9my $schema = DBICTest::Schema->connect (DBICTest->_database, { quote_char => '`' });
45150bc4 10# cheat
11require DBIx::Class::Storage::DBI::mysql;
b6a469f7 12*DBIx::Class::Storage::DBI::mysql::_get_server_version = sub { 5 };
45150bc4 13bless ( $schema->storage, 'DBIx::Class::Storage::DBI::mysql' );
14
15# check that double-subqueries are properly wrapped
16{
45150bc4 17 # the expected SQL may seem wastefully nonsensical - this is due to
18 # CD's tablename being \'cd', which triggers the "this can be anything"
19 # mode, and forces a subquery. This in turn forces *another* subquery
20 # because mysql is being mysql
21 # Also we know it will fail - never deployed. All we care about is the
2cfc22dd 22 # SQL to compare, hence the eval
23 $schema->is_executed_sql_bind( sub {
24 eval { $schema->resultset ('CD')->update({ genreid => undef }) }
25 },[[
45150bc4 26 'UPDATE cd SET `genreid` = ? WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )',
2cfc22dd 27 [ { dbic_colname => "genreid", sqlt_datatype => "integer" } => undef ],
28 ]], 'Correct update-SQL with double-wrapped subquery' );
45150bc4 29
30 # same comment as above
2cfc22dd 31 $schema->is_executed_sql_bind( sub {
32 eval { $schema->resultset ('CD')->delete }
33 }, [[
45150bc4 34 'DELETE FROM cd WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )',
2cfc22dd 35 ]], 'Correct delete-SQL with double-wrapped subquery' );
45150bc4 36
f4fdfd69 37 # and a couple of really contrived examples (we test them live in t/71mysql.t)
45150bc4 38 my $rs = $schema->resultset('Artist')->search({ name => { -like => 'baby_%' } });
39 my ($count_sql, @count_bind) = @${$rs->count_rs->as_query};
2cfc22dd 40 $schema->is_executed_sql_bind( sub {
41 eval {
42 $schema->resultset('Artist')->search(
43 { artistid => {
44 -in => $rs->get_column('artistid')
45 ->as_query
46 } },
47 )->update({ name => \[ "CONCAT( `name`, '_bell_out_of_', $count_sql )", @count_bind ] });
48 }
49 }, [[
45150bc4 50 q(
51 UPDATE `artist`
52 SET `name` = CONCAT(`name`, '_bell_out_of_', (
53 SELECT *
54 FROM (
55 SELECT COUNT( * )
56 FROM `artist` `me`
57 WHERE `name` LIKE ?
58 ) `_forced_double_subquery`
59 ))
60 WHERE
61 `artistid` IN (
62 SELECT *
63 FROM (
64 SELECT `me`.`artistid`
65 FROM `artist` `me`
66 WHERE `name` LIKE ?
67 ) `_forced_double_subquery` )
68 ),
2cfc22dd 69 ( [ { dbic_colname => "name", sqlt_datatype => "varchar", sqlt_size => 100 }
70 => 'baby_%' ]
71 ) x 2
72 ]]);
f4fdfd69 73
2cfc22dd 74 $schema->is_executed_sql_bind( sub {
75 eval {
76 $schema->resultset('CD')->search_related('artist',
77 { 'artist.name' => { -like => 'baby_with_%' } }
78 )->delete
79 }
80 }, [[
f4fdfd69 81 q(
82 DELETE FROM `artist`
83 WHERE `artistid` IN (
84 SELECT *
85 FROM (
86 SELECT `artist`.`artistid`
87 FROM cd `me`
b6a469f7 88 JOIN `artist` `artist`
f4fdfd69 89 ON `artist`.`artistid` = `me`.`artist`
90 WHERE `artist`.`name` LIKE ?
91 ) `_forced_double_subquery`
92 )
93 ),
2cfc22dd 94 [ { dbic_colname => "artist.name", sqlt_datatype => "varchar", sqlt_size => 100 }
95 => 'baby_with_%' ],
96 ]] );
45150bc4 97}
98
ac0c0825 99# Test support for straight joins
100{
101 my $cdsrc = $schema->source('CD');
102 my $artrel_info = $cdsrc->relationship_info ('artist');
103 $cdsrc->add_relationship(
104 'straight_artist',
105 $artrel_info->{class},
106 $artrel_info->{cond},
107 { %{$artrel_info->{attrs}}, join_type => 'straight' },
108 );
109 is_same_sql_bind (
110 $cdsrc->resultset->search({}, { prefetch => 'straight_artist' })->as_query,
111 '(
112 SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year`, `me`.`genreid`, `me`.`single_track`,
113 `straight_artist`.`artistid`, `straight_artist`.`name`, `straight_artist`.`rank`, `straight_artist`.`charfield`
114 FROM cd `me`
115 STRAIGHT_JOIN `artist` `straight_artist` ON `straight_artist`.`artistid` = `me`.`artist`
116 )',
117 [],
118 'straight joins correctly supported for mysql'
119 );
120}
121
b6a469f7 122# Test support for inner joins on mysql v3
123for (
124 [ 3 => 'INNER JOIN' ],
125 [ 4 => 'JOIN' ],
126) {
127 my ($ver, $join_op) = @$_;
128
b6a469f7 129 # we do not care at this point if data is available, just do a reconnect cycle
26aea721 130 # to clear the server version cache and then get a new maker
131 {
132 $schema->storage->disconnect;
133 $schema->storage->_sql_maker(undef);
134
135 no warnings 'redefine';
136 local *DBIx::Class::Storage::DBI::mysql::_get_server_version = sub { $ver };
137
138 $schema->storage->ensure_connected;
139 $schema->storage->sql_maker;
140 }
b6a469f7 141
142 is_same_sql_bind (
143 $schema->resultset('CD')->search ({}, { prefetch => 'artist' })->as_query,
144 "(
145 SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year`, `me`.`genreid`, `me`.`single_track`,
146 `artist`.`artistid`, `artist`.`name`, `artist`.`rank`, `artist`.`charfield`
147 FROM cd `me`
148 $join_op `artist` `artist` ON `artist`.`artistid` = `me`.`artist`
149 )",
150 [],
151 "default join type works for version $ver",
152 );
153}
154
45150bc4 155done_testing;