Commit | Line | Data |
70350518 |
1 | use strict; |
34a9e8a0 |
2 | use warnings; |
70350518 |
3 | |
4 | use Test::More; |
74de7c2c |
5 | use Test::Exception; |
70350518 |
6 | use lib qw(t/lib); |
7 | use DBICTest; |
5db49b9a |
8 | use DBI::Const::GetInfoType; |
aa82ce29 |
9 | use DBIC::SqlMakerTest; |
5c160298 |
10 | use Try::Tiny; |
11 | |
12 | DBICTest::Schema->load_classes('BitField'); |
0567538f |
13 | |
14 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MYSQL_${_}" } qw/DSN USER PASS/}; |
15 | |
16 | #warn "$dsn $user $pass"; |
17 | |
70350518 |
18 | plan skip_all => 'Set $ENV{DBICTEST_MYSQL_DSN}, _USER and _PASS to run this test' |
0567538f |
19 | unless ($dsn && $user); |
20 | |
3ff5b740 |
21 | my $schema = DBICTest::Schema->connect($dsn, $user, $pass); |
0567538f |
22 | |
3ff5b740 |
23 | my $dbh = $schema->storage->dbh; |
0567538f |
24 | |
25 | $dbh->do("DROP TABLE IF EXISTS artist;"); |
26 | |
a0dd8679 |
27 | $dbh->do("CREATE TABLE artist (artistid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), rank INTEGER NOT NULL DEFAULT '13', charfield CHAR(10));"); |
0567538f |
28 | |
74de7c2c |
29 | $dbh->do("DROP TABLE IF EXISTS cd;"); |
30 | |
e0d56e26 |
31 | $dbh->do("CREATE TABLE cd (cdid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, artist INTEGER, title TEXT, year DATE, genreid INTEGER, single_track INTEGER);"); |
74de7c2c |
32 | |
33 | $dbh->do("DROP TABLE IF EXISTS producer;"); |
34 | |
35 | $dbh->do("CREATE TABLE producer (producerid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, name TEXT);"); |
36 | |
37 | $dbh->do("DROP TABLE IF EXISTS cd_to_producer;"); |
38 | |
39 | $dbh->do("CREATE TABLE cd_to_producer (cd INTEGER,producer INTEGER);"); |
40 | |
bed3a173 |
41 | $dbh->do("DROP TABLE IF EXISTS owners;"); |
42 | |
43 | $dbh->do("CREATE TABLE owners (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL);"); |
44 | |
45 | $dbh->do("DROP TABLE IF EXISTS books;"); |
46 | |
47 | $dbh->do("CREATE TABLE books (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, source VARCHAR(100) NOT NULL, owner integer NOT NULL, title varchar(100) NOT NULL, price integer);"); |
48 | |
5c160298 |
49 | $dbh->do("DROP TABLE IF EXISTS bitfield_test;"); |
50 | |
51 | my $have_binary_bit = |
52 | $schema->storage->_server_info->{normalized_dbms_version} > 5.000002; |
53 | |
54 | if ($have_binary_bit) { |
55 | $dbh->do("CREATE TABLE bitfield_test (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, bitfield_1 BIT(1), bitfield_32 bit(32), bitfield_64 bit(64));"); |
56 | } |
57 | else { |
58 | $dbh->do("CREATE TABLE bitfield_test (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, bitfield_1 BIT(1));"); |
59 | } |
60 | |
0567538f |
61 | #'dbi:mysql:host=localhost;database=dbic_test', 'dbic_test', ''); |
62 | |
adbf0c89 |
63 | # make sure sqlt_type overrides work (::Storage::DBI::mysql does this) |
64 | { |
65 | my $schema = DBICTest::Schema->connect($dsn, $user, $pass); |
66 | |
67 | ok (!$schema->storage->_dbh, 'definitely not connected'); |
68 | is ($schema->storage->sqlt_type, 'MySQL', 'sqlt_type correct pre-connection'); |
69 | } |
70 | |
3ff5b740 |
71 | # This is in Core now, but it's here just to test that it doesn't break |
72 | $schema->class('Artist')->load_components('PK::Auto'); |
0567538f |
73 | |
74 | # test primary key handling |
3ff5b740 |
75 | my $new = $schema->resultset('Artist')->create({ name => 'foo' }); |
0567538f |
76 | ok($new->artistid, "Auto-PK worked"); |
77 | |
78 | # test LIMIT support |
79 | for (1..6) { |
3ff5b740 |
80 | $schema->resultset('Artist')->create({ name => 'Artist ' . $_ }); |
0567538f |
81 | } |
3ff5b740 |
82 | my $it = $schema->resultset('Artist')->search( {}, |
0567538f |
83 | { rows => 3, |
84 | offset => 2, |
85 | order_by => 'artistid' } |
86 | ); |
fb4b58e8 |
87 | is( $it->count, 3, "LIMIT count ok" ); # ask for 3 rows out of 7 artists |
0567538f |
88 | is( $it->next->name, "Artist 2", "iterator->next ok" ); |
89 | $it->next; |
90 | $it->next; |
91 | is( $it->next, undef, "next past end of resultset ok" ); |
92 | |
a953d8d9 |
93 | my $test_type_info = { |
94 | 'artistid' => { |
103e3e03 |
95 | 'data_type' => 'INT', |
96 | 'is_nullable' => 0, |
fc22fbac |
97 | 'size' => 11, |
98 | 'default_value' => undef, |
a953d8d9 |
99 | }, |
100 | 'name' => { |
103e3e03 |
101 | 'data_type' => 'VARCHAR', |
a953d8d9 |
102 | 'is_nullable' => 1, |
a0dd8679 |
103 | 'size' => 100, |
fc22fbac |
104 | 'default_value' => undef, |
103e3e03 |
105 | }, |
39da2a2b |
106 | 'rank' => { |
107 | 'data_type' => 'INT', |
108 | 'is_nullable' => 0, |
109 | 'size' => 11, |
110 | 'default_value' => 13, |
111 | }, |
103e3e03 |
112 | 'charfield' => { |
637219ab |
113 | 'data_type' => 'CHAR', |
103e3e03 |
114 | 'is_nullable' => 1, |
fc22fbac |
115 | 'size' => 10, |
116 | 'default_value' => undef, |
103e3e03 |
117 | }, |
a953d8d9 |
118 | }; |
119 | |
bed3a173 |
120 | $schema->populate ('Owners', [ |
121 | [qw/id name /], |
122 | [qw/1 wiggle/], |
123 | [qw/2 woggle/], |
124 | [qw/3 boggle/], |
125 | ]); |
126 | |
127 | $schema->populate ('BooksInLibrary', [ |
128 | [qw/source owner title /], |
13a2f031 |
129 | [qw/Library 1 secrets1/], |
130 | [qw/Eatery 1 secrets2/], |
131 | [qw/Library 2 secrets3/], |
bed3a173 |
132 | ]); |
133 | |
13a2f031 |
134 | # |
135 | # try a distinct + prefetch on tables with identically named columns |
136 | # (mysql doesn't seem to like subqueries with equally named columns) |
137 | # |
bed3a173 |
138 | |
d758ec36 |
139 | { |
13a2f031 |
140 | # try a ->has_many direction (due to a 'multi' accessor the select/group_by group is collapsed) |
141 | my $owners = $schema->resultset ('Owners')->search ( |
142 | { 'books.id' => { '!=', undef }}, |
143 | { prefetch => 'books', distinct => 1 } |
144 | ); |
145 | my $owners2 = $schema->resultset ('Owners')->search ({ id => { -in => $owners->get_column ('me.id')->as_query }}); |
146 | for ($owners, $owners2) { |
b5963465 |
147 | is ($_->all, 2, 'Prefetched grouped search returns correct number of rows'); |
148 | is ($_->count, 2, 'Prefetched grouped search returns correct count'); |
13a2f031 |
149 | } |
150 | |
d758ec36 |
151 | # try a ->belongs_to direction (no select collapse) |
b5963465 |
152 | my $books = $schema->resultset ('BooksInLibrary')->search ( |
153 | { 'owner.name' => 'wiggle' }, |
154 | { prefetch => 'owner', distinct => 1 } |
155 | ); |
156 | my $books2 = $schema->resultset ('BooksInLibrary')->search ({ id => { -in => $books->get_column ('me.id')->as_query }}); |
157 | for ($books, $books2) { |
158 | is ($_->all, 1, 'Prefetched grouped search returns correct number of rows'); |
159 | is ($_->count, 1, 'Prefetched grouped search returns correct count'); |
13a2f031 |
160 | } |
161 | } |
bed3a173 |
162 | |
5db49b9a |
163 | SKIP: { |
164 | my $mysql_version = $dbh->get_info( $GetInfoType{SQL_DBMS_VER} ); |
5db49b9a |
165 | skip "Cannot determine MySQL server version", 1 if !$mysql_version; |
a953d8d9 |
166 | |
f750163c |
167 | my ($v1, $v2, $v3) = $mysql_version =~ /^(\d+)\.(\d+)(?:\.(\d+))?/; |
168 | skip "Cannot determine MySQL server version", 1 if !$v1 || !defined($v2); |
169 | |
170 | $v3 ||= 0; |
171 | |
5db49b9a |
172 | if( ($v1 < 5) || ($v1 == 5 && $v2 == 0 && $v3 <= 3) ) { |
173 | $test_type_info->{charfield}->{data_type} = 'VARCHAR'; |
174 | } |
a953d8d9 |
175 | |
3ff5b740 |
176 | my $type_info = $schema->storage->columns_info_for('artist'); |
5db49b9a |
177 | is_deeply($type_info, $test_type_info, 'columns_info_for - column data types'); |
178 | } |
a953d8d9 |
179 | |
6a999241 |
180 | my $cd = $schema->resultset ('CD')->create ({}); |
181 | my $producer = $schema->resultset ('Producer')->create ({}); |
182 | lives_ok { $cd->set_producers ([ $producer ]) } 'set_relationship doesnt die'; |
183 | |
aa82ce29 |
184 | { |
185 | my $artist = $schema->resultset('Artist')->next; |
186 | my $cd = $schema->resultset('CD')->next; |
187 | $cd->set_from_related ('artist', $artist); |
188 | $cd->update; |
189 | |
190 | my $rs = $schema->resultset('CD')->search ({}, { prefetch => 'artist' }); |
191 | |
192 | lives_ok sub { |
193 | my $cd = $rs->next; |
194 | is ($cd->artist->name, $artist->name, 'Prefetched artist'); |
195 | }, 'join does not throw (mysql 3 test)'; |
196 | |
197 | # induce a jointype override, make sure it works even if we don't have mysql3 |
de5f71ef |
198 | local $schema->storage->sql_maker->{_default_jointype} = 'inner'; |
aa82ce29 |
199 | is_same_sql_bind ( |
200 | $rs->as_query, |
201 | '( |
202 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
203 | artist.artistid, artist.name, artist.rank, artist.charfield |
204 | FROM cd me |
205 | INNER JOIN artist artist ON artist.artistid = me.artist |
206 | )', |
207 | [], |
208 | 'overriden default join type works', |
209 | ); |
210 | } |
6a999241 |
211 | |
b8391c87 |
212 | { |
213 | # Test support for straight joins |
214 | my $cdsrc = $schema->source('CD'); |
215 | my $artrel_info = $cdsrc->relationship_info ('artist'); |
216 | $cdsrc->add_relationship( |
217 | 'straight_artist', |
218 | $artrel_info->{class}, |
219 | $artrel_info->{cond}, |
220 | { %{$artrel_info->{attrs}}, join_type => 'straight' }, |
221 | ); |
222 | is_same_sql_bind ( |
223 | $cdsrc->resultset->search({}, { prefetch => 'straight_artist' })->as_query, |
224 | '( |
225 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
226 | straight_artist.artistid, straight_artist.name, straight_artist.rank, straight_artist.charfield |
227 | FROM cd me |
228 | STRAIGHT_JOIN artist straight_artist ON straight_artist.artistid = me.artist |
229 | )', |
230 | [], |
231 | 'straight joins correctly supported for mysql' |
232 | ); |
233 | } |
234 | |
55cfbb70 |
235 | ## Can we properly deal with the null search problem? |
a7e65bb5 |
236 | ## |
237 | ## Only way is to do a SET SQL_AUTO_IS_NULL = 0; on connect |
238 | ## But I'm not sure if we should do this or not (Ash, 2008/06/03) |
0fde80d9 |
239 | # |
240 | # There is now a built-in function to do this, test that everything works |
241 | # with it (ribasushi, 2009/07/03) |
55cfbb70 |
242 | |
243 | NULLINSEARCH: { |
97a0a148 |
244 | my $ansi_schema = DBICTest::Schema->connect ($dsn, $user, $pass, { on_connect_call => 'set_strict_mode' }); |
8de9298c |
245 | |
246 | $ansi_schema->resultset('Artist')->create ({ name => 'last created artist' }); |
6a999241 |
247 | |
248 | ok my $artist1_rs = $ansi_schema->resultset('Artist')->search({artistid=>6666}) |
249 | => 'Created an artist resultset of 6666'; |
250 | |
55cfbb70 |
251 | is $artist1_rs->count, 0 |
6a999241 |
252 | => 'Got no returned rows'; |
374dd926 |
253 | |
6a999241 |
254 | ok my $artist2_rs = $ansi_schema->resultset('Artist')->search({artistid=>undef}) |
255 | => 'Created an artist resultset of undef'; |
74de7c2c |
256 | |
6a999241 |
257 | is $artist2_rs->count, 0 |
258 | => 'got no rows'; |
74de7c2c |
259 | |
6a999241 |
260 | my $artist = $artist2_rs->single; |
55cfbb70 |
261 | |
6a999241 |
262 | is $artist => undef |
263 | => 'Nothing Found!'; |
74de7c2c |
264 | } |
aa82ce29 |
265 | |
0ce4ab92 |
266 | # check for proper grouped counts |
267 | { |
9f775126 |
268 | my $ansi_schema = DBICTest::Schema->connect ($dsn, $user, $pass, { |
269 | on_connect_call => 'set_strict_mode', |
270 | quote_char => '`', |
271 | name_sep => '.' |
272 | }); |
0ce4ab92 |
273 | my $rs = $ansi_schema->resultset('CD'); |
274 | |
275 | my $years; |
276 | $years->{$_->year|| scalar keys %$years}++ for $rs->all; # NULL != NULL, thus the keys eval |
277 | |
278 | lives_ok ( sub { |
279 | is ( |
280 | $rs->search ({}, { group_by => 'year'})->count, |
281 | scalar keys %$years, |
282 | 'grouped count correct', |
283 | ); |
284 | }, 'Grouped count does not throw'); |
9f775126 |
285 | |
286 | lives_ok( sub { |
287 | $ansi_schema->resultset('Owners')->search({}, { |
288 | join => 'books', group_by => [ 'me.id', 'books.id' ] |
289 | })->count(); |
290 | }, 'count on grouped columns with the same name does not throw'); |
291 | |
292 | |
0ce4ab92 |
293 | } |
294 | |
e0d56e26 |
295 | ZEROINSEARCH: { |
296 | my $cds_per_year = { |
297 | 2001 => 2, |
298 | 2002 => 1, |
299 | 2005 => 3, |
300 | }; |
301 | |
302 | my $rs = $schema->resultset ('CD'); |
303 | $rs->delete; |
304 | for my $y (keys %$cds_per_year) { |
305 | for my $c (1 .. $cds_per_year->{$y} ) { |
306 | $rs->create ({ title => "CD $y-$c", artist => 1, year => "$y-01-01" }); |
307 | } |
308 | } |
309 | |
310 | is ($rs->count, 6, 'CDs created successfully'); |
311 | |
312 | $rs = $rs->search ({}, { |
ca458871 |
313 | select => [ \ 'YEAR(year)' ], as => ['y'], distinct => 1, |
e0d56e26 |
314 | }); |
315 | |
316 | is_deeply ( |
ca458871 |
317 | [ sort ($rs->get_column ('y')->all) ], |
e0d56e26 |
318 | [ sort keys %$cds_per_year ], |
319 | 'Years group successfully', |
320 | ); |
321 | |
322 | $rs->create ({ artist => 1, year => '0-1-1', title => 'Jesus Rap' }); |
323 | |
324 | is_deeply ( |
ca458871 |
325 | [ sort $rs->get_column ('y')->all ], |
e0d56e26 |
326 | [ 0, sort keys %$cds_per_year ], |
327 | 'Zero-year groups successfully', |
328 | ); |
329 | |
330 | # convoluted search taken verbatim from list |
331 | my $restrict_rs = $rs->search({ -and => [ |
332 | year => { '!=', 0 }, |
333 | year => { '!=', undef } |
334 | ]}); |
335 | |
336 | is_deeply ( |
337 | [ $restrict_rs->get_column('y')->all ], |
338 | [ $rs->get_column ('y')->all ], |
339 | 'Zero year was correctly excluded from resultset', |
340 | ); |
341 | } |
10176e1f |
342 | |
5c160298 |
343 | # test bit fields as binary |
344 | SKIP: { |
345 | skip 'no binary BIT(X) support in your MySQL', 4 unless $have_binary_bit; |
346 | |
347 | my $rs = $schema->resultset('BitField'); |
348 | |
349 | my $b1_bit = "\01"; |
350 | my $b32_bits = "\0xFFFFFFFF"; |
351 | my $b64_bits = "\0xFFFFFFFFFFFFFFFF"; |
352 | |
353 | ok((my $row1 = $rs->create({ |
354 | bitfield_1 => $b1_bit, bitfield_32 => $b32_bits, bitfield_64 => $b64_bits |
355 | })), 'created a row with bit columns'); |
356 | |
357 | is $row1->bitfield_1, $b1_bit, 'bit(1) field set correctly'; |
358 | is $row1->bitfield_32, $b32_bits, 'bit(32) field set correctly'; |
359 | is $row1->bitfield_64, $b64_bits, 'bit(64) field set correctly'; |
360 | |
361 | $row1->delete; |
362 | } |
363 | |
364 | # test bit fields with bit_as_unsigned |
365 | { |
366 | my $bit_schema = DBICTest::Schema->connect ($dsn, $user, $pass, { |
367 | on_connect_call => 'bit_as_unsigned' |
368 | }); |
369 | |
370 | my $rs = $bit_schema->resultset('BitField'); |
371 | |
372 | require Math::BigInt; |
373 | |
374 | my $i32_bits = Math::BigInt->new('0xFFFFFFFF'); |
375 | my $i64_bits = Math::BigInt->new('0xFFFFFFFFFFFFFFFF'); |
376 | |
377 | ok((my $row1 = $rs->create({ |
378 | bitfield_1 => 1, |
379 | ($have_binary_bit ? |
380 | (bitfield_32 => $i32_bits, bitfield_64 => $i64_bits) : |
381 | () |
382 | ), |
383 | })), 'created a row with bit columns'); |
384 | |
385 | $row1->discard_changes; |
386 | |
387 | is $row1->bitfield_1, 1, 'bit(1) field set to 1 correctly'; |
388 | |
389 | SKIP: { |
390 | skip 'no binary BIT(X) support in your MySQL', 2 unless $have_binary_bit; |
391 | |
392 | is $row1->bitfield_32, $i32_bits, 'bit(32) field set correctly'; |
393 | is $row1->bitfield_64, $i64_bits, 'bit(64) field set correctly'; |
394 | }; |
395 | |
396 | ok((my $row2 = $rs->create({ bitfield_1 => 0 })), |
397 | 'created a row with bit columns'); |
398 | |
399 | $row2->discard_changes; |
400 | |
401 | is $row2->bitfield_1, 0, 'bit(1) field set to 0 correctly'; |
402 | |
403 | is $rs->search({ bitfield_1 => 0 })->count, 1, |
404 | 'correct count of rows with bit field set to 0'; |
405 | |
406 | is $rs->search({ bitfield_1 => 1 })->count, 1, |
407 | 'correct count of rows with bit field set to 1'; |
408 | } |
409 | |
10176e1f |
410 | ## If find() is the first query after connect() |
411 | ## DBI::Storage::sql_maker() will be called before |
412 | ## _determine_driver() and so the ::SQLHacks class for MySQL |
413 | ## will not be used |
414 | |
415 | my $schema2 = DBICTest::Schema->connect($dsn, $user, $pass); |
416 | $schema2->resultset("Artist")->find(4); |
417 | isa_ok($schema2->storage->sql_maker, 'DBIx::Class::SQLAHacks::MySQL'); |
418 | |
aa82ce29 |
419 | done_testing; |
5c160298 |
420 | |
421 | END { |
422 | if (my $dbh = try { $schema->storage->dbh }) { |
423 | foreach my $table (qw/artist cd producer cd_to_producer owners books |
424 | bitfield_test/) { |
425 | $dbh->do("DROP TABLE $table"); |
426 | } |
427 | } |
428 | } |