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