Commit | Line | Data |
ac0c0825 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | use lib qw(t/lib); |
6 | |
7 | use DBIx::Class::Optional::Dependencies; |
8 | plan skip_all => 'Test needs ' . DBIx::Class::Optional::Dependencies->req_missing_for ('id_shortener') |
9 | unless DBIx::Class::Optional::Dependencies->req_ok_for ('id_shortener'); |
10 | |
11 | use DBICTest::Schema::Artist; |
12 | BEGIN { |
13 | DBICTest::Schema::Artist->add_column('parentid'); |
14 | |
15 | DBICTest::Schema::Artist->has_many( |
16 | children => 'DBICTest::Schema::Artist', |
17 | { 'foreign.parentid' => 'self.artistid' } |
18 | ); |
19 | |
20 | DBICTest::Schema::Artist->belongs_to( |
21 | parent => 'DBICTest::Schema::Artist', |
22 | { 'foreign.artistid' => 'self.parentid' } |
23 | ); |
24 | } |
25 | |
a5a7bb73 |
26 | use DBICTest ':DiffSQL'; |
ac0c0825 |
27 | |
28 | use DBIx::Class::SQLMaker::LimitDialects; |
29 | my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype; |
30 | my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype; |
31 | |
32 | for my $q ( '', '"' ) { |
33 | |
34 | my $schema = DBICTest->init_schema( |
35 | storage_type => 'DBIx::Class::Storage::DBI::Oracle::Generic', |
36 | no_deploy => 1, |
37 | quote_char => $q, |
38 | ); |
39 | |
40 | # select the whole tree |
41 | { |
42 | my $rs = $schema->resultset('Artist')->search({}, { |
43 | start_with => { name => 'root' }, |
44 | connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, |
45 | }); |
46 | |
47 | is_same_sql_bind ( |
48 | $rs->as_query, |
49 | "( |
50 | SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} |
51 | FROM ${q}artist${q} ${q}me${q} |
52 | START WITH ${q}name${q} = ? |
53 | CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} |
54 | )", |
55 | [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } |
56 | => 'root'] ], |
57 | ); |
58 | |
59 | is_same_sql_bind ( |
60 | $rs->count_rs->as_query, |
61 | "( |
62 | SELECT COUNT( * ) |
63 | FROM ${q}artist${q} ${q}me${q} |
64 | START WITH ${q}name${q} = ? |
65 | CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} |
66 | )", |
67 | [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } |
68 | => 'root'] ], |
69 | ); |
70 | } |
71 | |
72 | # use order siblings by statement |
73 | { |
74 | my $rs = $schema->resultset('Artist')->search({}, { |
75 | start_with => { name => 'root' }, |
76 | connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, |
77 | order_siblings_by => { -desc => 'name' }, |
78 | }); |
79 | |
80 | is_same_sql_bind ( |
81 | $rs->as_query, |
82 | "( |
83 | SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} |
84 | FROM ${q}artist${q} ${q}me${q} |
85 | START WITH ${q}name${q} = ? |
86 | CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} |
87 | ORDER SIBLINGS BY ${q}name${q} DESC |
88 | )", |
89 | [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } |
90 | => 'root'] ], |
91 | ); |
92 | } |
93 | |
94 | # get the root node |
95 | { |
96 | my $rs = $schema->resultset('Artist')->search({ parentid => undef }, { |
97 | start_with => { name => 'root' }, |
98 | connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, |
99 | }); |
100 | |
101 | is_same_sql_bind ( |
102 | $rs->as_query, |
103 | "( |
104 | SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} |
105 | FROM ${q}artist${q} ${q}me${q} |
106 | WHERE ( ${q}parentid${q} IS NULL ) |
107 | START WITH ${q}name${q} = ? |
108 | CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} |
109 | )", |
110 | [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } |
111 | => 'root'] ], |
112 | ); |
113 | } |
114 | |
115 | # combine a connect by with a join |
116 | { |
117 | my $rs = $schema->resultset('Artist')->search( |
118 | {'cds.title' => { -like => '%cd'} }, |
119 | { |
120 | join => 'cds', |
121 | start_with => { 'me.name' => 'root' }, |
122 | connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, |
123 | } |
124 | ); |
125 | |
126 | is_same_sql_bind ( |
127 | $rs->as_query, |
128 | "( |
129 | SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} |
130 | FROM ${q}artist${q} ${q}me${q} |
131 | LEFT JOIN cd ${q}cds${q} ON ${q}cds${q}.${q}artist${q} = ${q}me${q}.${q}artistid${q} |
132 | WHERE ( ${q}cds${q}.${q}title${q} LIKE ? ) |
133 | START WITH ${q}me${q}.${q}name${q} = ? |
134 | CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} |
135 | )", |
136 | [ |
137 | [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 } |
138 | => '%cd'], |
139 | [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 } |
140 | => 'root'], |
141 | ], |
142 | ); |
143 | |
144 | is_same_sql_bind ( |
145 | $rs->count_rs->as_query, |
146 | "( |
147 | SELECT COUNT( * ) |
148 | FROM ${q}artist${q} ${q}me${q} |
149 | LEFT JOIN cd ${q}cds${q} ON ${q}cds${q}.${q}artist${q} = ${q}me${q}.${q}artistid${q} |
150 | WHERE ( ${q}cds${q}.${q}title${q} LIKE ? ) |
151 | START WITH ${q}me${q}.${q}name${q} = ? |
152 | CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} |
153 | )", |
154 | [ |
155 | [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 } |
156 | => '%cd'], |
157 | [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 } |
158 | => 'root'], |
159 | ], |
160 | ); |
161 | } |
162 | |
163 | # combine a connect by with order_by |
164 | { |
165 | my $rs = $schema->resultset('Artist')->search({}, { |
166 | start_with => { name => 'root' }, |
167 | connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, |
168 | order_by => { -asc => [ 'LEVEL', 'name' ] }, |
169 | }); |
170 | |
171 | is_same_sql_bind ( |
172 | $rs->as_query, |
173 | "( |
174 | SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} |
175 | FROM ${q}artist${q} ${q}me${q} |
176 | START WITH ${q}name${q} = ? |
177 | CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} |
178 | ORDER BY ${q}LEVEL${q} ASC, ${q}name${q} ASC |
179 | )", |
180 | [ |
181 | [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } |
182 | => 'root'], |
183 | ], |
184 | ); |
185 | } |
186 | |
187 | # limit a connect by |
188 | { |
189 | my $rs = $schema->resultset('Artist')->search({}, { |
190 | start_with => { name => 'root' }, |
191 | connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, |
192 | order_by => [ { -asc => 'name' }, { -desc => 'artistid' } ], |
193 | rows => 2, |
194 | }); |
195 | |
196 | is_same_sql_bind ( |
197 | $rs->as_query, |
198 | "( |
199 | SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} |
200 | FROM ( |
201 | SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q} |
202 | FROM ${q}artist${q} ${q}me${q} |
203 | START WITH ${q}name${q} = ? |
204 | CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} |
205 | ORDER BY ${q}name${q} ASC, ${q}artistid${q} DESC |
206 | ) ${q}me${q} |
207 | WHERE ROWNUM <= ? |
208 | )", |
209 | [ |
210 | [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } |
211 | => 'root'], [ $ROWS => 2 ], |
212 | ], |
213 | ); |
214 | |
215 | is_same_sql_bind ( |
216 | $rs->count_rs->as_query, |
217 | "( |
218 | SELECT COUNT( * ) |
219 | FROM ( |
220 | SELECT ${q}me${q}.${q}artistid${q} |
221 | FROM ( |
222 | SELECT ${q}me${q}.${q}artistid${q} |
223 | FROM ${q}artist${q} ${q}me${q} |
224 | START WITH ${q}name${q} = ? |
225 | CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} |
226 | ) ${q}me${q} |
227 | WHERE ROWNUM <= ? |
228 | ) ${q}me${q} |
229 | )", |
230 | [ |
231 | [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } |
232 | => 'root'], |
233 | [ $ROWS => 2 ], |
234 | ], |
235 | ); |
236 | } |
237 | |
238 | # combine a connect_by with group_by and having |
239 | # add some bindvals to make sure things still work |
240 | { |
241 | my $rs = $schema->resultset('Artist')->search({}, { |
242 | select => \[ 'COUNT(rank) + ?', [ __cbind => 3 ] ], |
243 | as => 'cnt', |
244 | start_with => { name => 'root' }, |
245 | connect_by => { parentid => { -prior => { -ident => 'artistid' } } }, |
246 | group_by => \[ 'rank + ? ', [ __gbind => 1] ], |
247 | having => \[ 'count(rank) < ?', [ cnt => 2 ] ], |
248 | }); |
249 | |
250 | is_same_sql_bind ( |
251 | $rs->as_query, |
252 | "( |
253 | SELECT COUNT(rank) + ? |
254 | FROM ${q}artist${q} ${q}me${q} |
255 | START WITH ${q}name${q} = ? |
256 | CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q} |
257 | GROUP BY( rank + ? ) |
258 | HAVING count(rank) < ? |
259 | )", |
260 | [ |
261 | [ { dbic_colname => '__cbind' } |
262 | => 3 ], |
263 | [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } |
264 | => 'root'], |
265 | [ { dbic_colname => '__gbind' } |
266 | => 1 ], |
267 | [ { dbic_colname => 'cnt' } |
268 | => 2 ], |
269 | ], |
270 | ); |
271 | } |
272 | |
273 | # select the whole cycle tree with nocylce |
274 | { |
275 | my $rs = $schema->resultset('Artist')->search({}, { |
276 | start_with => { name => 'cycle-root' }, |
277 | '+select' => \ 'CONNECT_BY_ISCYCLE', |
278 | '+as' => [ 'connector' ], |
279 | connect_by_nocycle => { parentid => { -prior => { -ident => 'artistid' } } }, |
280 | }); |
281 | |
282 | is_same_sql_bind ( |
283 | $rs->as_query, |
284 | "( |
285 | SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q}, CONNECT_BY_ISCYCLE |
286 | FROM ${q}artist${q} ${q}me${q} |
287 | START WITH ${q}name${q} = ? |
288 | CONNECT BY NOCYCLE ${q}parentid${q} = PRIOR ${q}artistid${q} |
289 | )", |
290 | [ |
291 | [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } |
292 | => 'cycle-root'], |
293 | ], |
294 | ); |
295 | |
296 | is_same_sql_bind ( |
297 | $rs->count_rs->as_query, |
298 | "( |
299 | SELECT COUNT( * ) |
300 | FROM ${q}artist${q} ${q}me${q} |
301 | START WITH ${q}name${q} = ? |
302 | CONNECT BY NOCYCLE ${q}parentid${q} = PRIOR ${q}artistid${q} |
303 | )", |
304 | [ |
305 | [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 } |
306 | => 'cycle-root'], |
307 | ], |
308 | ); |
309 | } |
310 | } |
311 | |
312 | done_testing; |