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