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