Commit | Line | Data |
45150bc4 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | |
232b9aa8 |
6 | # Fixed properly in mainline |
7 | BEGIN { delete $ENV{DBICTEST_SQLITE_USE_FILE} } |
8 | |
45150bc4 |
9 | use lib qw(t/lib); |
10 | use DBICTest; |
11 | use DBICTest::Schema; |
12 | use DBIC::SqlMakerTest; |
13 | use DBIC::DebugObj; |
14 | |
15 | my $schema = DBICTest::Schema->connect (DBICTest->_database, { quote_char => '`' }); |
16 | # cheat |
17 | require DBIx::Class::Storage::DBI::mysql; |
18 | bless ( $schema->storage, 'DBIx::Class::Storage::DBI::mysql' ); |
19 | |
20 | # check that double-subqueries are properly wrapped |
21 | { |
22 | my ($sql, @bind); |
23 | my $debugobj = DBIC::DebugObj->new (\$sql, \@bind); |
24 | my $orig_debugobj = $schema->storage->debugobj; |
25 | my $orig_debug = $schema->storage->debug; |
26 | |
27 | $schema->storage->debugobj ($debugobj); |
28 | $schema->storage->debug (1); |
29 | |
30 | # the expected SQL may seem wastefully nonsensical - this is due to |
31 | # CD's tablename being \'cd', which triggers the "this can be anything" |
32 | # mode, and forces a subquery. This in turn forces *another* subquery |
33 | # because mysql is being mysql |
34 | # Also we know it will fail - never deployed. All we care about is the |
35 | # SQL to compare |
36 | eval { $schema->resultset ('CD')->update({ genreid => undef }) }; |
37 | is_same_sql_bind ( |
38 | $sql, |
39 | \@bind, |
40 | 'UPDATE cd SET `genreid` = ? WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )', |
41 | [ 'NULL' ], |
42 | 'Correct update-SQL with double-wrapped subquery', |
43 | ); |
44 | |
45 | # same comment as above |
46 | eval { $schema->resultset ('CD')->delete }; |
47 | is_same_sql_bind ( |
48 | $sql, |
49 | \@bind, |
50 | 'DELETE FROM cd WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )', |
51 | [], |
52 | 'Correct delete-SQL with double-wrapped subquery', |
53 | ); |
54 | |
f4fdfd69 |
55 | # and a couple of really contrived examples (we test them live in t/71mysql.t) |
45150bc4 |
56 | my $rs = $schema->resultset('Artist')->search({ name => { -like => 'baby_%' } }); |
57 | my ($count_sql, @count_bind) = @${$rs->count_rs->as_query}; |
58 | eval { |
59 | $schema->resultset('Artist')->search( |
60 | { artistid => { |
61 | -in => $rs->get_column('artistid') |
62 | ->as_query |
63 | } }, |
64 | )->update({ name => \[ "CONCAT( `name`, '_bell_out_of_', $count_sql )", @count_bind ] }); |
65 | }; |
66 | |
67 | is_same_sql_bind ( |
68 | $sql, |
69 | \@bind, |
70 | q( |
71 | UPDATE `artist` |
72 | SET `name` = CONCAT(`name`, '_bell_out_of_', ( |
73 | SELECT * |
74 | FROM ( |
75 | SELECT COUNT( * ) |
76 | FROM `artist` `me` |
77 | WHERE `name` LIKE ? |
78 | ) `_forced_double_subquery` |
79 | )) |
80 | WHERE |
81 | `artistid` IN ( |
82 | SELECT * |
83 | FROM ( |
84 | SELECT `me`.`artistid` |
85 | FROM `artist` `me` |
86 | WHERE `name` LIKE ? |
87 | ) `_forced_double_subquery` ) |
88 | ), |
89 | [ ("'baby_%'") x 2 ], |
90 | ); |
91 | |
f4fdfd69 |
92 | eval { |
93 | $schema->resultset('CD')->search_related('artist', |
94 | { 'artist.name' => { -like => 'baby_with_%' } } |
95 | )->delete |
96 | }; |
97 | |
98 | is_same_sql_bind ( |
99 | $sql, |
100 | \@bind, |
101 | q( |
102 | DELETE FROM `artist` |
103 | WHERE `artistid` IN ( |
104 | SELECT * |
105 | FROM ( |
106 | SELECT `artist`.`artistid` |
107 | FROM cd `me` |
108 | INNER JOIN `artist` `artist` |
109 | ON `artist`.`artistid` = `me`.`artist` |
110 | WHERE `artist`.`name` LIKE ? |
111 | ) `_forced_double_subquery` |
112 | ) |
113 | ), |
114 | [ "'baby_with_%'" ], |
115 | ); |
116 | |
45150bc4 |
117 | $schema->storage->debugobj ($orig_debugobj); |
118 | $schema->storage->debug ($orig_debug); |
119 | } |
120 | |
ac0c0825 |
121 | # Test support for straight joins |
122 | { |
123 | my $cdsrc = $schema->source('CD'); |
124 | my $artrel_info = $cdsrc->relationship_info ('artist'); |
125 | $cdsrc->add_relationship( |
126 | 'straight_artist', |
127 | $artrel_info->{class}, |
128 | $artrel_info->{cond}, |
129 | { %{$artrel_info->{attrs}}, join_type => 'straight' }, |
130 | ); |
131 | is_same_sql_bind ( |
132 | $cdsrc->resultset->search({}, { prefetch => 'straight_artist' })->as_query, |
133 | '( |
134 | SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year`, `me`.`genreid`, `me`.`single_track`, |
135 | `straight_artist`.`artistid`, `straight_artist`.`name`, `straight_artist`.`rank`, `straight_artist`.`charfield` |
136 | FROM cd `me` |
137 | STRAIGHT_JOIN `artist` `straight_artist` ON `straight_artist`.`artistid` = `me`.`artist` |
138 | )', |
139 | [], |
140 | 'straight joins correctly supported for mysql' |
141 | ); |
142 | } |
143 | |
45150bc4 |
144 | done_testing; |