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