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