more pg test cleanup
[dbsrgits/DBIx-Class-Historic.git] / t / 71mysql.t
CommitLineData
70350518 1use strict;
34a9e8a0 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
28$dbh->do("CREATE TABLE cd (cdid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, artist INTEGER, title TEXT, year INTEGER, 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
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
3ff5b740 48# This is in Core now, but it's here just to test that it doesn't break
49$schema->class('Artist')->load_components('PK::Auto');
0567538f 50
51# test primary key handling
3ff5b740 52my $new = $schema->resultset('Artist')->create({ name => 'foo' });
0567538f 53ok($new->artistid, "Auto-PK worked");
54
55# test LIMIT support
56for (1..6) {
3ff5b740 57 $schema->resultset('Artist')->create({ name => 'Artist ' . $_ });
0567538f 58}
3ff5b740 59my $it = $schema->resultset('Artist')->search( {},
0567538f 60 { rows => 3,
61 offset => 2,
62 order_by => 'artistid' }
63);
fb4b58e8 64is( $it->count, 3, "LIMIT count ok" ); # ask for 3 rows out of 7 artists
0567538f 65is( $it->next->name, "Artist 2", "iterator->next ok" );
66$it->next;
67$it->next;
68is( $it->next, undef, "next past end of resultset ok" );
69
a953d8d9 70my $test_type_info = {
71 'artistid' => {
103e3e03 72 'data_type' => 'INT',
73 'is_nullable' => 0,
fc22fbac 74 'size' => 11,
75 'default_value' => undef,
a953d8d9 76 },
77 'name' => {
103e3e03 78 'data_type' => 'VARCHAR',
a953d8d9 79 'is_nullable' => 1,
a0dd8679 80 'size' => 100,
fc22fbac 81 'default_value' => undef,
103e3e03 82 },
39da2a2b 83 'rank' => {
84 'data_type' => 'INT',
85 'is_nullable' => 0,
86 'size' => 11,
87 'default_value' => 13,
88 },
103e3e03 89 'charfield' => {
637219ab 90 'data_type' => 'CHAR',
103e3e03 91 'is_nullable' => 1,
fc22fbac 92 'size' => 10,
93 'default_value' => undef,
103e3e03 94 },
a953d8d9 95};
96
bed3a173 97$schema->populate ('Owners', [
98 [qw/id name /],
99 [qw/1 wiggle/],
100 [qw/2 woggle/],
101 [qw/3 boggle/],
102]);
103
104$schema->populate ('BooksInLibrary', [
105 [qw/source owner title /],
13a2f031 106 [qw/Library 1 secrets1/],
107 [qw/Eatery 1 secrets2/],
108 [qw/Library 2 secrets3/],
bed3a173 109]);
110
13a2f031 111#
112# try a distinct + prefetch on tables with identically named columns
113# (mysql doesn't seem to like subqueries with equally named columns)
114#
bed3a173 115
d758ec36 116{
13a2f031 117 # try a ->has_many direction (due to a 'multi' accessor the select/group_by group is collapsed)
118 my $owners = $schema->resultset ('Owners')->search (
119 { 'books.id' => { '!=', undef }},
120 { prefetch => 'books', distinct => 1 }
121 );
122 my $owners2 = $schema->resultset ('Owners')->search ({ id => { -in => $owners->get_column ('me.id')->as_query }});
123 for ($owners, $owners2) {
b5963465 124 is ($_->all, 2, 'Prefetched grouped search returns correct number of rows');
125 is ($_->count, 2, 'Prefetched grouped search returns correct count');
13a2f031 126 }
127
d758ec36 128 # try a ->belongs_to direction (no select collapse)
b5963465 129 my $books = $schema->resultset ('BooksInLibrary')->search (
130 { 'owner.name' => 'wiggle' },
131 { prefetch => 'owner', distinct => 1 }
132 );
133 my $books2 = $schema->resultset ('BooksInLibrary')->search ({ id => { -in => $books->get_column ('me.id')->as_query }});
134 for ($books, $books2) {
135 is ($_->all, 1, 'Prefetched grouped search returns correct number of rows');
136 is ($_->count, 1, 'Prefetched grouped search returns correct count');
13a2f031 137 }
138}
bed3a173 139
5db49b9a 140SKIP: {
141 my $mysql_version = $dbh->get_info( $GetInfoType{SQL_DBMS_VER} );
5db49b9a 142 skip "Cannot determine MySQL server version", 1 if !$mysql_version;
a953d8d9 143
f750163c 144 my ($v1, $v2, $v3) = $mysql_version =~ /^(\d+)\.(\d+)(?:\.(\d+))?/;
145 skip "Cannot determine MySQL server version", 1 if !$v1 || !defined($v2);
146
147 $v3 ||= 0;
148
5db49b9a 149 if( ($v1 < 5) || ($v1 == 5 && $v2 == 0 && $v3 <= 3) ) {
150 $test_type_info->{charfield}->{data_type} = 'VARCHAR';
151 }
a953d8d9 152
3ff5b740 153 my $type_info = $schema->storage->columns_info_for('artist');
5db49b9a 154 is_deeply($type_info, $test_type_info, 'columns_info_for - column data types');
aa82ce29 155
156
5db49b9a 157}
a953d8d9 158
6a999241 159my $cd = $schema->resultset ('CD')->create ({});
160my $producer = $schema->resultset ('Producer')->create ({});
161lives_ok { $cd->set_producers ([ $producer ]) } 'set_relationship doesnt die';
162
aa82ce29 163{
164 my $artist = $schema->resultset('Artist')->next;
165 my $cd = $schema->resultset('CD')->next;
166 $cd->set_from_related ('artist', $artist);
167 $cd->update;
168
169 my $rs = $schema->resultset('CD')->search ({}, { prefetch => 'artist' });
170
171 lives_ok sub {
172 my $cd = $rs->next;
173 is ($cd->artist->name, $artist->name, 'Prefetched artist');
174 }, 'join does not throw (mysql 3 test)';
175
176 # induce a jointype override, make sure it works even if we don't have mysql3
de5f71ef 177 local $schema->storage->sql_maker->{_default_jointype} = 'inner';
aa82ce29 178 is_same_sql_bind (
179 $rs->as_query,
180 '(
181 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
182 artist.artistid, artist.name, artist.rank, artist.charfield
183 FROM cd me
184 INNER JOIN artist artist ON artist.artistid = me.artist
185 )',
186 [],
187 'overriden default join type works',
188 );
189}
6a999241 190
55cfbb70 191## Can we properly deal with the null search problem?
a7e65bb5 192##
193## Only way is to do a SET SQL_AUTO_IS_NULL = 0; on connect
194## But I'm not sure if we should do this or not (Ash, 2008/06/03)
0fde80d9 195#
196# There is now a built-in function to do this, test that everything works
197# with it (ribasushi, 2009/07/03)
55cfbb70 198
199NULLINSEARCH: {
97a0a148 200 my $ansi_schema = DBICTest::Schema->connect ($dsn, $user, $pass, { on_connect_call => 'set_strict_mode' });
8de9298c 201
202 $ansi_schema->resultset('Artist')->create ({ name => 'last created artist' });
6a999241 203
204 ok my $artist1_rs = $ansi_schema->resultset('Artist')->search({artistid=>6666})
205 => 'Created an artist resultset of 6666';
206
55cfbb70 207 is $artist1_rs->count, 0
6a999241 208 => 'Got no returned rows';
374dd926 209
6a999241 210 ok my $artist2_rs = $ansi_schema->resultset('Artist')->search({artistid=>undef})
211 => 'Created an artist resultset of undef';
74de7c2c 212
6a999241 213 is $artist2_rs->count, 0
214 => 'got no rows';
74de7c2c 215
6a999241 216 my $artist = $artist2_rs->single;
55cfbb70 217
6a999241 218 is $artist => undef
219 => 'Nothing Found!';
74de7c2c 220}
aa82ce29 221
222done_testing;