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; |
15 | bless ( $schema->storage, 'DBIx::Class::Storage::DBI::mysql' ); |
16 | |
17 | # check that double-subqueries are properly wrapped |
18 | { |
19 | my ($sql, @bind); |
20 | my $debugobj = DBIC::DebugObj->new (\$sql, \@bind); |
21 | my $orig_debugobj = $schema->storage->debugobj; |
22 | my $orig_debug = $schema->storage->debug; |
23 | |
24 | $schema->storage->debugobj ($debugobj); |
25 | $schema->storage->debug (1); |
26 | |
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 |
32 | # SQL to compare |
33 | eval { $schema->resultset ('CD')->update({ genreid => undef }) }; |
34 | is_same_sql_bind ( |
35 | $sql, |
36 | \@bind, |
37 | 'UPDATE cd SET `genreid` = ? WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )', |
38 | [ 'NULL' ], |
39 | 'Correct update-SQL with double-wrapped subquery', |
40 | ); |
41 | |
42 | # same comment as above |
43 | eval { $schema->resultset ('CD')->delete }; |
44 | is_same_sql_bind ( |
45 | $sql, |
46 | \@bind, |
47 | 'DELETE FROM cd WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )', |
48 | [], |
49 | 'Correct delete-SQL with double-wrapped subquery', |
50 | ); |
51 | |
f4fdfd69 |
52 | # and a couple of really contrived examples (we test them live in t/71mysql.t) |
45150bc4 |
53 | my $rs = $schema->resultset('Artist')->search({ name => { -like => 'baby_%' } }); |
54 | my ($count_sql, @count_bind) = @${$rs->count_rs->as_query}; |
55 | eval { |
56 | $schema->resultset('Artist')->search( |
57 | { artistid => { |
58 | -in => $rs->get_column('artistid') |
59 | ->as_query |
60 | } }, |
61 | )->update({ name => \[ "CONCAT( `name`, '_bell_out_of_', $count_sql )", @count_bind ] }); |
62 | }; |
63 | |
64 | is_same_sql_bind ( |
65 | $sql, |
66 | \@bind, |
67 | q( |
68 | UPDATE `artist` |
69 | SET `name` = CONCAT(`name`, '_bell_out_of_', ( |
70 | SELECT * |
71 | FROM ( |
72 | SELECT COUNT( * ) |
73 | FROM `artist` `me` |
74 | WHERE `name` LIKE ? |
75 | ) `_forced_double_subquery` |
76 | )) |
77 | WHERE |
78 | `artistid` IN ( |
79 | SELECT * |
80 | FROM ( |
81 | SELECT `me`.`artistid` |
82 | FROM `artist` `me` |
83 | WHERE `name` LIKE ? |
84 | ) `_forced_double_subquery` ) |
85 | ), |
86 | [ ("'baby_%'") x 2 ], |
87 | ); |
88 | |
f4fdfd69 |
89 | eval { |
90 | $schema->resultset('CD')->search_related('artist', |
91 | { 'artist.name' => { -like => 'baby_with_%' } } |
92 | )->delete |
93 | }; |
94 | |
95 | is_same_sql_bind ( |
96 | $sql, |
97 | \@bind, |
98 | q( |
99 | DELETE FROM `artist` |
100 | WHERE `artistid` IN ( |
101 | SELECT * |
102 | FROM ( |
103 | SELECT `artist`.`artistid` |
104 | FROM cd `me` |
105 | INNER JOIN `artist` `artist` |
106 | ON `artist`.`artistid` = `me`.`artist` |
107 | WHERE `artist`.`name` LIKE ? |
108 | ) `_forced_double_subquery` |
109 | ) |
110 | ), |
111 | [ "'baby_with_%'" ], |
112 | ); |
113 | |
45150bc4 |
114 | $schema->storage->debugobj ($orig_debugobj); |
115 | $schema->storage->debug ($orig_debug); |
116 | } |
117 | |
118 | done_testing; |