1use strict; 2use warnings; 3 4use Test::More; 5use Test::Warn; 6use Test::Exception; 7use lib qw(t/lib); 8use DBICTest; 9use DBIC::SqlMakerTest; 10 11my $schema = DBICTest->init_schema(); 12 13my $rs = $schema->resultset("CD"); 14 15cmp_ok ( 16 $rs->count, 17 '!=', 18 $rs->search ({}, {columns => ['year'], distinct => 1})->count, 19 'At least one year is the same in rs' 20); 21 22my $rs_title = $rs->get_column('title'); 23my $rs_year = $rs->get_column('year'); 24my $max_year = $rs->get_column(\'MAX (year)'); 25 26is($rs_title->next, 'Spoonful of bees', "next okay"); 27is_deeply( [ sort $rs_year->func('DISTINCT') ], [ 1997, 1998, 1999, 2001 ], "wantarray context okay"); 28ok ($max_year->next == $rs_year->max, q/get_column (\'FUNC') ok/); 29 30my @all = $rs_title->all; 31cmp_ok(scalar @all, '==', 5, "five titles returned"); 32 33cmp_ok($rs_year->max, '==', 2001, "max okay for year"); 34is($rs_title->min, 'Caterwaulin\' Blues', "min okay for title"); 35 36cmp_ok($rs_year->sum, '==', 9996, "three artists returned"); 37 38$rs_year->reset; 39is($rs_year->next, 1999, "reset okay"); 40 41is($rs_year->first, 1999, "first okay"); 42 43warnings_exist (sub { 44 is($rs_year->single, 1999, "single okay"); 45}, qr/Query returned more than one row/, 'single warned'); 46 47 48# test distinct propagation 49is_deeply ( 50 [$rs->search ({}, { distinct => 1 })->get_column ('year')->all], 51 [$rs_year->func('distinct')], 52 'distinct => 1 is passed through properly', 53); 54 55# test +select/+as for single column 56my $psrs = $schema->resultset('CD')->search({}, 57 { 58 '+select' => \'MAX(year)', 59 '+as' => 'last_year' 60 } 61); 62lives_ok(sub { $psrs->get_column('last_year')->next }, '+select/+as additional column "last_year" present (scalar)'); 63dies_ok(sub { $psrs->get_column('noSuchColumn')->next }, '+select/+as nonexistent column throws exception'); 64 65# test +select/+as for overriding a column 66$psrs = $schema->resultset('CD')->search({}, 67 { 68 'select' => \"'The Final Countdown'", 69 'as' => 'title' 70 } 71); 72is($psrs->get_column('title')->next, 'The Final Countdown', '+select/+as overridden column "title"'); 73 74 75# test +select/+as for multiple columns 76$psrs = $schema->resultset('CD')->search({}, 77 { 78 '+select' => [ \'LENGTH(title) AS title_length', 'title' ], 79 '+as' => [ 'tlength', 'addedtitle' ] 80 } 81); 82lives_ok(sub { $psrs->get_column('tlength')->next }, '+select/+as multiple additional columns, "tlength" column present'); 83lives_ok(sub { $psrs->get_column('addedtitle')->next }, '+select/+as multiple additional columns, "addedtitle" column present'); 84 85# test that +select/+as specs do not leak 86is_same_sql_bind ( 87 $psrs->get_column('year')->as_query, 88 '(SELECT me.year FROM cd me)', 89 [], 90 'Correct SQL for get_column/as' 91); 92 93is_same_sql_bind ( 94 $psrs->get_column('addedtitle')->as_query, 95 '(SELECT me.title FROM cd me)', 96 [], 97 'Correct SQL for get_column/+as col' 98); 99 100is_same_sql_bind ( 101 $psrs->get_column('tlength')->as_query, 102 '(SELECT LENGTH(title) AS title_length FROM cd me)', 103 [], 104 'Correct SQL for get_column/+as func' 105); 106 107# test that order_by over a function forces a subquery 108lives_ok ( sub { 109 is_deeply ( 110 [ $psrs->search ({}, { order_by => { -desc => 'title_length' } })->get_column ('title')->all ], 111 [ 112 "Generic Manufactured Singles", 113 "Come Be Depressed With Us", 114 "Caterwaulin' Blues", 115 "Spoonful of bees", 116 "Forkful of bees", 117 ], 118 'Subquery count induced by aliased ordering function', 119 ); 120}); 121 122# test for prefetch not leaking 123{ 124 my $rs = $schema->resultset("CD")->search({}, { prefetch => 'artist' }); 125 my $rsc = $rs->get_column('year'); 126 is( $rsc->{_parent_resultset}->{attrs}->{prefetch}, undef, 'prefetch wiped' ); 127} 128 129# test sum() 130is ($schema->resultset('BooksInLibrary')->get_column ('price')->sum, 125, 'Sum of a resultset works correctly'); 131 132# test sum over search_related 133my $owner = $schema->resultset('Owners')->find ({ name => 'Newton' }); 134ok ($owner->books->count > 1, 'Owner Newton has multiple books'); 135is ($owner->search_related ('books')->get_column ('price')->sum, 60, 'Correctly calculated price of all owned books'); 136 137 138# make sure joined/prefetched get_column of a PK dtrt 139 140$rs->reset; 141my $j_rs = $rs->search ({}, { join => 'tracks' })->get_column ('cdid'); 142is_deeply ( 143 [ $j_rs->all ], 144 [ map { my $c = $rs->next; ( ($c->id) x $c->tracks->count ) } (1 .. $rs->count) ], 145 'join properly explodes amount of rows from get_column', 146); 147 148$rs->reset; 149my $p_rs = $rs->search ({}, { prefetch => 'tracks' })->get_column ('cdid'); 150is_deeply ( 151 [ $p_rs->all ], 152 [ $rs->get_column ('cdid')->all ], 153 'prefetch properly collapses amount of rows from get_column', 154); 155 156done_testing; 157