1use strict; 2use warnings; 3 4use Test::More; 5use Test::Exception; 6use lib qw(t/lib); 7use DBICTest; 8use DBI::Const::GetInfoType; 9use DBIC::SqlMakerTest; 10 11my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MYSQL_${_}" } qw/DSN USER PASS/}; 12 13#warn "$dsn $user $pass"; 14 15plan skip_all => 'Set $ENV{DBICTEST_MYSQL_DSN}, _USER and _PASS to run this test' 16 unless ($dsn && $user); 17 18my $schema = DBICTest::Schema->connect($dsn, $user, $pass); 19 20my $dbh = $schema->storage->dbh; 21 22$dbh->do("DROP TABLE IF EXISTS artist;"); 23 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));"); 25 26$dbh->do("DROP TABLE IF EXISTS cd;"); 27 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);"); 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 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 46#'dbi:mysql:host=localhost;database=dbic_test', 'dbic_test', ''); 47 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 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'); 58 59# test primary key handling 60my $new = $schema->resultset('Artist')->create({ name => 'foo' }); 61ok($new->artistid, "Auto-PK worked"); 62 63# test LIMIT support 64for (1..6) { 65 $schema->resultset('Artist')->create({ name => 'Artist ' . $_ }); 66} 67my $it = $schema->resultset('Artist')->search( {}, 68 { rows => 3, 69 offset => 2, 70 order_by => 'artistid' } 71); 72is( $it->count, 3, "LIMIT count ok" ); # ask for 3 rows out of 7 artists 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 78my $test_type_info = { 79 'artistid' => { 80 'data_type' => 'INT', 81 'is_nullable' => 0, 82 'size' => 11, 83 'default_value' => undef, 84 }, 85 'name' => { 86 'data_type' => 'VARCHAR', 87 'is_nullable' => 1, 88 'size' => 100, 89 'default_value' => undef, 90 }, 91 'rank' => { 92 'data_type' => 'INT', 93 'is_nullable' => 0, 94 'size' => 11, 95 'default_value' => 13, 96 }, 97 'charfield' => { 98 'data_type' => 'CHAR', 99 'is_nullable' => 1, 100 'size' => 10, 101 'default_value' => undef, 102 }, 103}; 104 105$schema->populate ('Owners', [ 106 [qw/id name /], 107 [qw/1 wiggle/], 108 [qw/2 woggle/], 109 [qw/3 boggle/], 110]); 111 112$schema->populate ('BooksInLibrary', [ 113 [qw/source owner title /], 114 [qw/Library 1 secrets1/], 115 [qw/Eatery 1 secrets2/], 116 [qw/Library 2 secrets3/], 117]); 118 119# 120# try a distinct + prefetch on tables with identically named columns 121# (mysql doesn't seem to like subqueries with equally named columns) 122# 123 124{ 125 # try a ->has_many direction (due to a 'multi' accessor the select/group_by group is collapsed) 126 my $owners = $schema->resultset ('Owners')->search ( 127 { 'books.id' => { '!=', undef }}, 128 { prefetch => 'books', distinct => 1 } 129 ); 130 my $owners2 = $schema->resultset ('Owners')->search ({ id => { -in => $owners->get_column ('me.id')->as_query }}); 131 for ($owners, $owners2) { 132 is ($_->all, 2, 'Prefetched grouped search returns correct number of rows'); 133 is ($_->count, 2, 'Prefetched grouped search returns correct count'); 134 } 135 136 # try a ->belongs_to direction (no select collapse) 137 my $books = $schema->resultset ('BooksInLibrary')->search ( 138 { 'owner.name' => 'wiggle' }, 139 { prefetch => 'owner', distinct => 1 } 140 ); 141 my $books2 = $schema->resultset ('BooksInLibrary')->search ({ id => { -in => $books->get_column ('me.id')->as_query }}); 142 for ($books, $books2) { 143 is ($_->all, 1, 'Prefetched grouped search returns correct number of rows'); 144 is ($_->count, 1, 'Prefetched grouped search returns correct count'); 145 } 146} 147 148SKIP: { 149 my $mysql_version = $dbh->get_info( $GetInfoType{SQL_DBMS_VER} ); 150 skip "Cannot determine MySQL server version", 1 if !$mysql_version; 151 152 my ($v1, $v2, $v3) = $mysql_version =~ /^(\d+)\.(\d+)(?:\.(\d+))?/; 153 skip "Cannot determine MySQL server version", 1 if !$v1 || !defined($v2); 154 155 $v3 ||= 0; 156 157 if( ($v1 < 5) || ($v1 == 5 && $v2 == 0 && $v3 <= 3) ) { 158 $test_type_info->{charfield}->{data_type} = 'VARCHAR'; 159 } 160 161 my $type_info = $schema->storage->columns_info_for('artist'); 162 is_deeply($type_info, $test_type_info, 'columns_info_for - column data types'); 163} 164 165my $cd = $schema->resultset ('CD')->create ({}); 166my $producer = $schema->resultset ('Producer')->create ({}); 167lives_ok { $cd->set_producers ([ $producer ]) } 'set_relationship doesnt die'; 168 169{ 170 my $artist = $schema->resultset('Artist')->next; 171 my $cd = $schema->resultset('CD')->next; 172 $cd->set_from_related ('artist', $artist); 173 $cd->update; 174 175 my $rs = $schema->resultset('CD')->search ({}, { prefetch => 'artist' }); 176 177 lives_ok sub { 178 my $cd = $rs->next; 179 is ($cd->artist->name, $artist->name, 'Prefetched artist'); 180 }, 'join does not throw (mysql 3 test)'; 181 182 # induce a jointype override, make sure it works even if we don't have mysql3 183 local $schema->storage->sql_maker->{_default_jointype} = 'inner'; 184 is_same_sql_bind ( 185 $rs->as_query, 186 '( 187 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, 188 artist.artistid, artist.name, artist.rank, artist.charfield 189 FROM cd me 190 INNER JOIN artist artist ON artist.artistid = me.artist 191 )', 192 [], 193 'overriden default join type works', 194 ); 195} 196 197## Can we properly deal with the null search problem? 198## 199## Only way is to do a SET SQL_AUTO_IS_NULL = 0; on connect 200## But I'm not sure if we should do this or not (Ash, 2008/06/03) 201# 202# There is now a built-in function to do this, test that everything works 203# with it (ribasushi, 2009/07/03) 204 205NULLINSEARCH: { 206 my $ansi_schema = DBICTest::Schema->connect ($dsn, $user, $pass, { on_connect_call => 'set_strict_mode' }); 207 208 $ansi_schema->resultset('Artist')->create ({ name => 'last created artist' }); 209 210 ok my $artist1_rs = $ansi_schema->resultset('Artist')->search({artistid=>6666}) 211 => 'Created an artist resultset of 6666'; 212 213 is $artist1_rs->count, 0 214 => 'Got no returned rows'; 215 216 ok my $artist2_rs = $ansi_schema->resultset('Artist')->search({artistid=>undef}) 217 => 'Created an artist resultset of undef'; 218 219 is $artist2_rs->count, 0 220 => 'got no rows'; 221 222 my $artist = $artist2_rs->single; 223 224 is $artist => undef 225 => 'Nothing Found!'; 226} 227 228# check for proper grouped counts 229{ 230 my $ansi_schema = DBICTest::Schema->connect ($dsn, $user, $pass, { on_connect_call => 'set_strict_mode' }); 231 my $rs = $ansi_schema->resultset('CD'); 232 233 my $years; 234 $years->{$_->year|| scalar keys %$years}++ for $rs->all; # NULL != NULL, thus the keys eval 235 236 lives_ok ( sub { 237 is ( 238 $rs->search ({}, { group_by => 'year'})->count, 239 scalar keys %$years, 240 'grouped count correct', 241 ); 242 }, 'Grouped count does not throw'); 243} 244 245ZEROINSEARCH: { 246 my $cds_per_year = { 247 2001 => 2, 248 2002 => 1, 249 2005 => 3, 250 }; 251 252 my $rs = $schema->resultset ('CD'); 253 $rs->delete; 254 for my $y (keys %$cds_per_year) { 255 for my $c (1 .. $cds_per_year->{$y} ) { 256 $rs->create ({ title => "CD $y-$c", artist => 1, year => "$y-01-01" }); 257 } 258 } 259 260 is ($rs->count, 6, 'CDs created successfully'); 261 262 $rs = $rs->search ({}, { 263 select => [ \ 'YEAR(year)' ], as => ['y'], distinct => 1, 264 }); 265 266 is_deeply ( 267 [ sort ($rs->get_column ('y')->all) ], 268 [ sort keys %$cds_per_year ], 269 'Years group successfully', 270 ); 271 272 $rs->create ({ artist => 1, year => '0-1-1', title => 'Jesus Rap' }); 273 274 is_deeply ( 275 [ sort $rs->get_column ('y')->all ], 276 [ 0, sort keys %$cds_per_year ], 277 'Zero-year groups successfully', 278 ); 279 280 # convoluted search taken verbatim from list 281 my $restrict_rs = $rs->search({ -and => [ 282 year => { '!=', 0 }, 283 year => { '!=', undef } 284 ]}); 285 286 is_deeply ( 287 [ $restrict_rs->get_column('y')->all ], 288 [ $rs->get_column ('y')->all ], 289 'Zero year was correctly excluded from resultset', 290 ); 291} 292 293## If find() is the first query after connect() 294## DBI::Storage::sql_maker() will be called before 295## _determine_driver() and so the ::SQLHacks class for MySQL 296## will not be used 297 298my $schema2 = DBICTest::Schema->connect($dsn, $user, $pass); 299$schema2->resultset("Artist")->find(4); 300isa_ok($schema2->storage->sql_maker, 'DBIx::Class::SQLAHacks::MySQL'); 301 302done_testing; 303