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