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' }
26 use DBICTest ':DiffSQL';
28 use DBIx::Class::SQLMaker::LimitDialects;
29 my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
30 my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype;
32 for my $q ( '', '"' ) {
34 my $schema = DBICTest->init_schema(
35 storage_type => 'DBIx::Class::Storage::DBI::Oracle::Generic',
40 # select the whole tree
42 my $rs = $schema->resultset('Artist')->search({}, {
43 start_with => { name => 'root' },
44 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
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}
55 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
60 $rs->count_rs->as_query,
63 FROM ${q}artist${q} ${q}me${q}
64 START WITH ${q}name${q} = ?
65 CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
67 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
72 # use order siblings by statement
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' },
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
89 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
96 my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
97 start_with => { name => 'root' },
98 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
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}
110 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
115 # combine a connect by with a join
117 my $rs = $schema->resultset('Artist')->search(
118 {'cds.title' => { -like => '%cd'} },
121 start_with => { 'me.name' => 'root' },
122 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
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}
137 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
139 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
145 $rs->count_rs->as_query,
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}
155 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
157 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
163 # combine a connect by with order_by
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' ] },
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
181 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
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' } ],
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}
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
210 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
211 => 'root'], [ $ROWS => 2 ],
216 $rs->count_rs->as_query,
220 SELECT ${q}me${q}.${q}artistid${q}
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}
231 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
238 # combine a connect_by with group_by and having
239 # add some bindvals to make sure things still work
241 my $rs = $schema->resultset('Artist')->search({}, {
242 select => \[ 'COUNT(rank) + ?', [ __cbind => 3 ] ],
244 start_with => { name => 'root' },
245 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
246 group_by => \[ 'rank + ? ', [ __gbind => 1] ],
247 having => \[ 'count(rank) < ?', [ cnt => 2 ] ],
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}
258 HAVING count(rank) < ?
261 [ { dbic_colname => '__cbind' }
263 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
265 [ { dbic_colname => '__gbind' }
267 [ { dbic_colname => 'cnt' }
273 # select the whole cycle tree with nocylce
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' } } },
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}
291 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
297 $rs->count_rs->as_query,
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}
305 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }