use lib qw(t/lib);
use DBICTest;
use DBI::Const::GetInfoType;
+use DBIC::SqlMakerTest;
my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MYSQL_${_}" } qw/DSN USER PASS/};
plan skip_all => 'Set $ENV{DBICTEST_MYSQL_DSN}, _USER and _PASS to run this test'
unless ($dsn && $user);
-plan tests => 23;
-
my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
my $dbh = $schema->storage->dbh;
$dbh->do("DROP TABLE IF EXISTS cd;");
-$dbh->do("CREATE TABLE cd (cdid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, artist INTEGER, title TEXT, year INTEGER, genreid INTEGER, single_track INTEGER);");
+$dbh->do("CREATE TABLE cd (cdid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, artist INTEGER, title TEXT, year DATE, genreid INTEGER, single_track INTEGER);");
$dbh->do("DROP TABLE IF EXISTS producer;");
#'dbi:mysql:host=localhost;database=dbic_test', 'dbic_test', '');
+# make sure sqlt_type overrides work (::Storage::DBI::mysql does this)
+{
+ my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
+
+ ok (!$schema->storage->_dbh, 'definitely not connected');
+ is ($schema->storage->sqlt_type, 'MySQL', 'sqlt_type correct pre-connection');
+}
+
# This is in Core now, but it's here just to test that it doesn't break
$schema->class('Artist')->load_components('PK::Auto');
# (mysql doesn't seem to like subqueries with equally named columns)
#
-SKIP: {
+{
# try a ->has_many direction (due to a 'multi' accessor the select/group_by group is collapsed)
my $owners = $schema->resultset ('Owners')->search (
{ 'books.id' => { '!=', undef }},
);
my $owners2 = $schema->resultset ('Owners')->search ({ id => { -in => $owners->get_column ('me.id')->as_query }});
for ($owners, $owners2) {
- lives_ok { is ($_->all, 2, 'Prefetched grouped search returns correct number of rows') }
- || skip ('No test due to exception', 1);
- lives_ok { is ($_->count, 2, 'Prefetched grouped search returns correct count') }
- || skip ('No test due to exception', 1);
+ is ($_->all, 2, 'Prefetched grouped search returns correct number of rows');
+ is ($_->count, 2, 'Prefetched grouped search returns correct count');
}
- TODO: {
- # try a ->prefetch direction (no select collapse)
- my $books = $schema->resultset ('BooksInLibrary')->search (
- { 'owner.name' => 'wiggle' },
- { prefetch => 'owner', distinct => 1 }
- );
-
- local $TODO = 'MySQL is crazy - there seems to be no way to make this work';
- # error thrown is:
- # Duplicate column name 'id' [for Statement "
- # SELECT COUNT( * )
- # FROM (
- # SELECT me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name
- # FROM books me
- # JOIN owners owner ON owner.id = me.owner
- # WHERE ( ( owner.name = ? AND source = ? ) )
- # GROUP BY me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name
- # ) count_subq
- # " with ParamValues: 0='wiggle', 1='Library']
- #
- # go fucking figure
-
- my $books2 = $schema->resultset ('BooksInLibrary')->search ({ id => { -in => $books->get_column ('me.id')->as_query }});
- for ($books, $books2) {
- lives_ok { is ($_->all, 1, 'Prefetched grouped search returns correct number of rows') }
- || skip ('No test due to exception', 1);
- lives_ok { is ($_->count, 1, 'Prefetched grouped search returns correct count') }
- || skip ('No test due to exception', 1);
- }
+ # try a ->belongs_to direction (no select collapse)
+ my $books = $schema->resultset ('BooksInLibrary')->search (
+ { 'owner.name' => 'wiggle' },
+ { prefetch => 'owner', distinct => 1 }
+ );
+ my $books2 = $schema->resultset ('BooksInLibrary')->search ({ id => { -in => $books->get_column ('me.id')->as_query }});
+ for ($books, $books2) {
+ is ($_->all, 1, 'Prefetched grouped search returns correct number of rows');
+ is ($_->count, 1, 'Prefetched grouped search returns correct count');
}
}
is_deeply($type_info, $test_type_info, 'columns_info_for - column data types');
}
+my $cd = $schema->resultset ('CD')->create ({});
+my $producer = $schema->resultset ('Producer')->create ({});
+lives_ok { $cd->set_producers ([ $producer ]) } 'set_relationship doesnt die';
+
+{
+ my $artist = $schema->resultset('Artist')->next;
+ my $cd = $schema->resultset('CD')->next;
+ $cd->set_from_related ('artist', $artist);
+ $cd->update;
+
+ my $rs = $schema->resultset('CD')->search ({}, { prefetch => 'artist' });
+
+ lives_ok sub {
+ my $cd = $rs->next;
+ is ($cd->artist->name, $artist->name, 'Prefetched artist');
+ }, 'join does not throw (mysql 3 test)';
+
+ # induce a jointype override, make sure it works even if we don't have mysql3
+ local $schema->storage->sql_maker->{_default_jointype} = 'inner';
+ is_same_sql_bind (
+ $rs->as_query,
+ '(
+ SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
+ artist.artistid, artist.name, artist.rank, artist.charfield
+ FROM cd me
+ INNER JOIN artist artist ON artist.artistid = me.artist
+ )',
+ [],
+ 'overriden default join type works',
+ );
+}
+
## Can we properly deal with the null search problem?
##
## Only way is to do a SET SQL_AUTO_IS_NULL = 0; on connect
## But I'm not sure if we should do this or not (Ash, 2008/06/03)
+#
+# There is now a built-in function to do this, test that everything works
+# with it (ribasushi, 2009/07/03)
NULLINSEARCH: {
-
- ok my $artist1_rs = $schema->resultset('Artist')->search({artistid=>6666})
- => 'Created an artist resultset of 6666';
-
+ my $ansi_schema = DBICTest::Schema->connect ($dsn, $user, $pass, { on_connect_call => 'set_strict_mode' });
+
+ $ansi_schema->resultset('Artist')->create ({ name => 'last created artist' });
+
+ ok my $artist1_rs = $ansi_schema->resultset('Artist')->search({artistid=>6666})
+ => 'Created an artist resultset of 6666';
+
is $artist1_rs->count, 0
- => 'Got no returned rows';
-
- ok my $artist2_rs = $schema->resultset('Artist')->search({artistid=>undef})
- => 'Created an artist resultset of undef';
-
- TODO: {
- local $TODO = "need to fix the row count =1 when select * from table where pk IS NULL problem";
- is $artist2_rs->count, 0
- => 'got no rows';
- }
+ => 'Got no returned rows';
+
+ ok my $artist2_rs = $ansi_schema->resultset('Artist')->search({artistid=>undef})
+ => 'Created an artist resultset of undef';
+
+ is $artist2_rs->count, 0
+ => 'got no rows';
my $artist = $artist2_rs->single;
-
+
is $artist => undef
- => 'Nothing Found!';
+ => 'Nothing Found!';
}
-
-my $cd = $schema->resultset ('CD')->create ({});
-my $producer = $schema->resultset ('Producer')->create ({});
+# check for proper grouped counts
+{
+ my $ansi_schema = DBICTest::Schema->connect ($dsn, $user, $pass, { on_connect_call => 'set_strict_mode' });
+ my $rs = $ansi_schema->resultset('CD');
-lives_ok { $cd->set_producers ([ $producer ]) } 'set_relationship doesnt die';
+ my $years;
+ $years->{$_->year|| scalar keys %$years}++ for $rs->all; # NULL != NULL, thus the keys eval
+
+ lives_ok ( sub {
+ is (
+ $rs->search ({}, { group_by => 'year'})->count,
+ scalar keys %$years,
+ 'grouped count correct',
+ );
+ }, 'Grouped count does not throw');
+}
+
+ZEROINSEARCH: {
+ my $cds_per_year = {
+ 2001 => 2,
+ 2002 => 1,
+ 2005 => 3,
+ };
+
+ my $rs = $schema->resultset ('CD');
+ $rs->delete;
+ for my $y (keys %$cds_per_year) {
+ for my $c (1 .. $cds_per_year->{$y} ) {
+ $rs->create ({ title => "CD $y-$c", artist => 1, year => "$y-01-01" });
+ }
+ }
+
+ is ($rs->count, 6, 'CDs created successfully');
-# clean up our mess
-END {
- #$dbh->do("DROP TABLE artist") if $dbh;
+ $rs = $rs->search ({}, {
+ select => [ \ 'YEAR(year)' ], as => ['y'], distinct => 1,
+ });
+
+ is_deeply (
+ [ sort ($rs->get_column ('y')->all) ],
+ [ sort keys %$cds_per_year ],
+ 'Years group successfully',
+ );
+
+ $rs->create ({ artist => 1, year => '0-1-1', title => 'Jesus Rap' });
+
+ is_deeply (
+ [ sort $rs->get_column ('y')->all ],
+ [ 0, sort keys %$cds_per_year ],
+ 'Zero-year groups successfully',
+ );
+
+ # convoluted search taken verbatim from list
+ my $restrict_rs = $rs->search({ -and => [
+ year => { '!=', 0 },
+ year => { '!=', undef }
+ ]});
+
+ is_deeply (
+ [ $restrict_rs->get_column('y')->all ],
+ [ $rs->get_column ('y')->all ],
+ 'Zero year was correctly excluded from resultset',
+ );
}
+
+## If find() is the first query after connect()
+## DBI::Storage::sql_maker() will be called before
+## _determine_driver() and so the ::SQLHacks class for MySQL
+## will not be used
+
+my $schema2 = DBICTest::Schema->connect($dsn, $user, $pass);
+$schema2->resultset("Artist")->find(4);
+isa_ok($schema2->storage->sql_maker, 'DBIx::Class::SQLAHacks::MySQL');
+
+done_testing;
projects / dbsrgits/DBIx-Class.git / blobdiff