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');
11 use DBICTest::Schema::Artist;
13 DBICTest::Schema::Artist->add_column('parentid');
15 DBICTest::Schema::Artist->has_many(
16 children => 'DBICTest::Schema::Artist',
17 { 'foreign.parentid' => 'self.artistid' }
20 DBICTest::Schema::Artist->belongs_to(
21 parent => 'DBICTest::Schema::Artist',
22 { 'foreign.artistid' => 'self.parentid' }
28 use DBIC::SqlMakerTest;
30 use DBIx::Class::SQLMaker::LimitDialects;
31 my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
32 my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype;
34 for my $q ( '', '"' ) {
36 my $schema = DBICTest->init_schema(
37 storage_type => 'DBIx::Class::Storage::DBI::Oracle::Generic',
42 # select the whole tree
44 my $rs = $schema->resultset('Artist')->search({}, {
45 start_with => { name => 'root' },
46 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
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}
57 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
62 $rs->count_rs->as_query,
65 FROM ${q}artist${q} ${q}me${q}
66 START WITH ${q}name${q} = ?
67 CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
69 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
74 # use order siblings by statement
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' },
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
91 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
98 my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
99 start_with => { name => 'root' },
100 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
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}
112 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
117 # combine a connect by with a join
119 my $rs = $schema->resultset('Artist')->search(
120 {'cds.title' => { -like => '%cd'} },
123 start_with => { 'me.name' => 'root' },
124 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
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}
139 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
141 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
147 $rs->count_rs->as_query,
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}
157 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
159 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
165 # combine a connect by with order_by
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' ] },
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
183 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
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' } ],
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}
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
212 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
213 => 'root'], [ $ROWS => 2 ],
218 $rs->count_rs->as_query,
222 SELECT ${q}me${q}.${q}artistid${q}
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}
233 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
240 # combine a connect_by with group_by and having
241 # add some bindvals to make sure things still work
243 my $rs = $schema->resultset('Artist')->search({}, {
244 select => \[ 'COUNT(rank) + ?', [ __cbind => 3 ] ],
246 start_with => { name => 'root' },
247 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
248 group_by => \[ 'rank + ? ', [ __gbind => 1] ],
249 having => \[ 'count(rank) < ?', [ cnt => 2 ] ],
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}
260 HAVING count(rank) < ?
263 [ { dbic_colname => '__cbind' }
265 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
267 [ { dbic_colname => '__gbind' }
269 [ { dbic_colname => 'cnt' }
275 # select the whole cycle tree with nocylce
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' } } },
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}
293 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
299 $rs->count_rs->as_query,
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}
307 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }