Commit | Line | Data |
45150bc4 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | |
6 | use lib qw(t/lib); |
7 | use DBICTest; |
8 | use DBICTest::Schema; |
9 | use DBIC::SqlMakerTest; |
10 | use DBIC::DebugObj; |
11 | |
12 | my $schema = DBICTest::Schema->connect (DBICTest->_database, { quote_char => '`' }); |
13 | # cheat |
14 | require DBIx::Class::Storage::DBI::mysql; |
b6a469f7 |
15 | *DBIx::Class::Storage::DBI::mysql::_get_server_version = sub { 5 }; |
45150bc4 |
16 | bless ( $schema->storage, 'DBIx::Class::Storage::DBI::mysql' ); |
17 | |
18 | # check that double-subqueries are properly wrapped |
19 | { |
20 | my ($sql, @bind); |
21 | my $debugobj = DBIC::DebugObj->new (\$sql, \@bind); |
22 | my $orig_debugobj = $schema->storage->debugobj; |
23 | my $orig_debug = $schema->storage->debug; |
24 | |
25 | $schema->storage->debugobj ($debugobj); |
26 | $schema->storage->debug (1); |
27 | |
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 |
33 | # SQL to compare |
34 | eval { $schema->resultset ('CD')->update({ genreid => undef }) }; |
35 | is_same_sql_bind ( |
36 | $sql, |
37 | \@bind, |
38 | 'UPDATE cd SET `genreid` = ? WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )', |
39 | [ 'NULL' ], |
40 | 'Correct update-SQL with double-wrapped subquery', |
41 | ); |
42 | |
43 | # same comment as above |
44 | eval { $schema->resultset ('CD')->delete }; |
45 | is_same_sql_bind ( |
46 | $sql, |
47 | \@bind, |
48 | 'DELETE FROM cd WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )', |
49 | [], |
50 | 'Correct delete-SQL with double-wrapped subquery', |
51 | ); |
52 | |
f4fdfd69 |
53 | # and a couple of really contrived examples (we test them live in t/71mysql.t) |
45150bc4 |
54 | my $rs = $schema->resultset('Artist')->search({ name => { -like => 'baby_%' } }); |
55 | my ($count_sql, @count_bind) = @${$rs->count_rs->as_query}; |
56 | eval { |
57 | $schema->resultset('Artist')->search( |
58 | { artistid => { |
59 | -in => $rs->get_column('artistid') |
60 | ->as_query |
61 | } }, |
62 | )->update({ name => \[ "CONCAT( `name`, '_bell_out_of_', $count_sql )", @count_bind ] }); |
63 | }; |
64 | |
65 | is_same_sql_bind ( |
66 | $sql, |
67 | \@bind, |
68 | q( |
69 | UPDATE `artist` |
70 | SET `name` = CONCAT(`name`, '_bell_out_of_', ( |
71 | SELECT * |
72 | FROM ( |
73 | SELECT COUNT( * ) |
74 | FROM `artist` `me` |
75 | WHERE `name` LIKE ? |
76 | ) `_forced_double_subquery` |
77 | )) |
78 | WHERE |
79 | `artistid` IN ( |
80 | SELECT * |
81 | FROM ( |
82 | SELECT `me`.`artistid` |
83 | FROM `artist` `me` |
84 | WHERE `name` LIKE ? |
85 | ) `_forced_double_subquery` ) |
86 | ), |
87 | [ ("'baby_%'") x 2 ], |
88 | ); |
89 | |
f4fdfd69 |
90 | eval { |
91 | $schema->resultset('CD')->search_related('artist', |
92 | { 'artist.name' => { -like => 'baby_with_%' } } |
93 | )->delete |
94 | }; |
95 | |
96 | is_same_sql_bind ( |
97 | $sql, |
98 | \@bind, |
99 | q( |
100 | DELETE FROM `artist` |
101 | WHERE `artistid` IN ( |
102 | SELECT * |
103 | FROM ( |
104 | SELECT `artist`.`artistid` |
105 | FROM cd `me` |
b6a469f7 |
106 | JOIN `artist` `artist` |
f4fdfd69 |
107 | ON `artist`.`artistid` = `me`.`artist` |
108 | WHERE `artist`.`name` LIKE ? |
109 | ) `_forced_double_subquery` |
110 | ) |
111 | ), |
112 | [ "'baby_with_%'" ], |
113 | ); |
114 | |
45150bc4 |
115 | $schema->storage->debugobj ($orig_debugobj); |
116 | $schema->storage->debug ($orig_debug); |
117 | } |
118 | |
ac0c0825 |
119 | # Test support for straight joins |
120 | { |
121 | my $cdsrc = $schema->source('CD'); |
122 | my $artrel_info = $cdsrc->relationship_info ('artist'); |
123 | $cdsrc->add_relationship( |
124 | 'straight_artist', |
125 | $artrel_info->{class}, |
126 | $artrel_info->{cond}, |
127 | { %{$artrel_info->{attrs}}, join_type => 'straight' }, |
128 | ); |
129 | is_same_sql_bind ( |
130 | $cdsrc->resultset->search({}, { prefetch => 'straight_artist' })->as_query, |
131 | '( |
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` |
134 | FROM cd `me` |
135 | STRAIGHT_JOIN `artist` `straight_artist` ON `straight_artist`.`artistid` = `me`.`artist` |
136 | )', |
137 | [], |
138 | 'straight joins correctly supported for mysql' |
139 | ); |
140 | } |
141 | |
b6a469f7 |
142 | # Test support for inner joins on mysql v3 |
143 | for ( |
144 | [ 3 => 'INNER JOIN' ], |
145 | [ 4 => 'JOIN' ], |
146 | ) { |
147 | my ($ver, $join_op) = @$_; |
148 | |
149 | no warnings 'redefine'; |
150 | local *DBIx::Class::Storage::DBI::mysql::_get_server_version = sub { $ver }; |
151 | |
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; |
156 | |
157 | is_same_sql_bind ( |
158 | $schema->resultset('CD')->search ({}, { prefetch => 'artist' })->as_query, |
159 | "( |
160 | SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year`, `me`.`genreid`, `me`.`single_track`, |
161 | `artist`.`artistid`, `artist`.`name`, `artist`.`rank`, `artist`.`charfield` |
162 | FROM cd `me` |
163 | $join_op `artist` `artist` ON `artist`.`artistid` = `me`.`artist` |
164 | )", |
165 | [], |
166 | "default join type works for version $ver", |
167 | ); |
168 | } |
169 | |
45150bc4 |
170 | done_testing; |