Commit | Line | Data |
3a55f069 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
3a55f069 |
5 | |
6 | use lib qw(t/lib); |
a5a7bb73 |
7 | use DBICTest ':DiffSQL'; |
3a55f069 |
8 | |
9 | my $schema = DBICTest->init_schema(); |
10 | |
11 | my @chain = ( |
12 | { |
13 | columns => [ 'cdid' ], |
37aafa2e |
14 | '+columns' => [ { title_lc => { lower => 'title', -as => 'lctitle' } } ], |
442363e5 |
15 | '+select' => [ 'genreid' ], |
16 | '+as' => [ 'genreid' ], |
37aafa2e |
17 | } => 'SELECT me.cdid, LOWER( title ) AS lctitle, me.genreid FROM cd me', |
3a55f069 |
18 | |
19 | { |
37aafa2e |
20 | '+columns' => [ { max_year => { max => 'me.year', -as => 'last_y' }}, ], |
3a55f069 |
21 | '+select' => [ { count => 'me.cdid' }, ], |
22 | '+as' => [ 'cnt' ], |
f7f53a89 |
23 | } => 'SELECT me.cdid, LOWER( title ) AS lctitle, MAX( me.year ) AS last_y, me.genreid, COUNT( me.cdid ) FROM cd me', |
3a55f069 |
24 | |
25 | { |
26 | select => [ { min => 'me.cdid' }, ], |
27 | as => [ 'min_id' ], |
28 | } => 'SELECT MIN( me.cdid ) FROM cd me', |
29 | |
30 | { |
37aafa2e |
31 | '+columns' => [ { cnt => { count => 'cdid', -as => 'cnt' } } ], |
f7f53a89 |
32 | } => 'SELECT COUNT ( cdid ) AS cnt, MIN( me.cdid ) FROM cd me', |
3a55f069 |
33 | |
34 | { |
37aafa2e |
35 | columns => [ { foo => { coalesce => [qw/a b c/], -as => 'firstfound' } } ], |
36 | } => 'SELECT COALESCE( a, b, c ) AS firstfound FROM cd me', |
37 | |
38 | { |
39 | '+columns' => [ 'me.year' ], |
40 | '+select' => [ { max => 'me.year', -as => 'last_y' } ], |
41 | '+as' => [ 'ly' ], |
42 | } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y FROM cd me', |
43 | |
44 | { |
45 | '+select' => [ { count => 'me.cdid', -as => 'cnt' } ], |
46 | '+as' => [ 'cnt' ], |
47 | } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y, COUNT( me.cdid ) AS cnt FROM cd me', |
48 | |
49 | # adding existing stuff should not alter selector |
50 | { |
51 | '+select' => [ 'me.year' ], |
52 | '+as' => [ 'year' ], |
f7f53a89 |
53 | } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y, COUNT( me.cdid ) AS cnt, me.year FROM cd me', |
37aafa2e |
54 | |
55 | { |
56 | '+columns' => [ 'me.year' ], |
f7f53a89 |
57 | } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y, COUNT( me.cdid ) AS cnt, me.year FROM cd me', |
37aafa2e |
58 | |
59 | { |
60 | '+columns' => 'me.year', |
f7f53a89 |
61 | } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y, COUNT( me.cdid ) AS cnt, me.year FROM cd me', |
62 | |
63 | # naked selector at the end should just work |
64 | { |
65 | '+select' => 'me.moar_stuff', |
66 | } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y, COUNT( me.cdid ) AS cnt, me.year, me.moar_stuff FROM cd me', |
67 | |
68 | { |
69 | '+select' => [ { MOAR => 'f', -as => 'func' } ], |
70 | } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y, COUNT( me.cdid ) AS cnt, me.year, me.moar_stuff, MOAR(f) AS func FROM cd me', |
37aafa2e |
71 | |
3a55f069 |
72 | ); |
73 | |
74 | my $rs = $schema->resultset('CD'); |
75 | |
76 | my $testno = 1; |
77 | while (@chain) { |
78 | my $attrs = shift @chain; |
79 | my $sql = shift @chain; |
80 | |
81 | $rs = $rs->search ({}, $attrs); |
82 | |
83 | is_same_sql_bind ( |
84 | $rs->as_query, |
14af4aa0 |
85 | "($sql)", |
3a55f069 |
86 | [], |
87 | "Test $testno of SELECT assembly ok", |
88 | ); |
89 | |
90 | $testno++; |
91 | } |
92 | |
37aafa2e |
93 | # Make sure we don't lose bits even with weird selector specs |
eb37b376 |
94 | # also check that the default selector list is lazy |
f7f53a89 |
95 | # and make sure that unaliased +select does not go crazy |
eb37b376 |
96 | $rs = $schema->resultset('CD'); |
97 | for my $attr ( |
98 | { '+columns' => [ 'me.title' ] }, # this one should be de-duplicated but not the select's |
99 | |
100 | { '+select' => \'me.year AS foo' }, # duplication of identical select expected (FIXME ?) |
101 | { '+select' => \['me.year AS foo'] }, |
102 | |
103 | { '+select' => [ \'me.artistid AS bar' ] }, |
104 | { '+select' => { count => 'artistid', -as => 'baz' } }, |
105 | ) { |
106 | for (qw/columns select as/) { |
107 | ok (! exists $rs->{attrs}{$_}, "No eager '$_' attr on fresh resultset" ); |
108 | } |
109 | |
110 | $rs = $rs->search({}, $attr); |
111 | } |
37aafa2e |
112 | |
113 | is_same_sql_bind ( |
114 | $rs->as_query, |
115 | '( SELECT |
eb37b376 |
116 | me.cdid, |
117 | me.artist, |
37aafa2e |
118 | me.title, |
eb37b376 |
119 | me.year, |
120 | me.genreid, |
121 | me.single_track, |
37aafa2e |
122 | me.year AS foo, |
eb37b376 |
123 | me.year AS foo, |
f7f53a89 |
124 | me.artistid AS bar, |
125 | COUNT( artistid ) AS baz |
37aafa2e |
126 | FROM cd me |
127 | )', |
128 | [], |
129 | 'Correct chaining before attr resolution' |
130 | ); |
131 | |
dc81dba3 |
132 | # Test the order of columns |
133 | $rs = $schema->resultset('CD')->search ({}, { |
134 | 'select' => [ 'me.cdid', 'me.title' ], |
135 | }); |
136 | |
137 | is_same_sql_bind ( |
138 | $rs->as_query, |
139 | '( SELECT |
140 | me.cdid, |
141 | me.title |
142 | FROM cd me |
143 | )', |
144 | [], |
145 | 'Correct order of selected columns' |
146 | ); |
147 | |
f7f53a89 |
148 | # Test bare +select with as from root of resultset |
149 | $rs = $schema->resultset('CD')->search ({}, { |
150 | '+select' => [ |
151 | \ 'foo', |
152 | { MOAR => 'f', -as => 'func' }, |
153 | ], |
154 | }); |
155 | |
156 | is_same_sql_bind ( |
157 | $rs->as_query, |
158 | '( SELECT |
159 | me.cdid, |
160 | me.artist, |
161 | me.title, |
162 | me.year, |
163 | me.genreid, |
164 | me.single_track, |
165 | foo, |
166 | MOAR( f ) AS func |
167 | FROM cd me |
168 | )', |
169 | [], |
170 | 'Correct order of selected columns' |
171 | ); |
172 | |
b773093f |
173 | done_testing; |