1 use DBIx::Class::Optional::Dependencies -skip_all_without => 'id_shortener';
9 use DBICTest::Schema::Artist;
11 DBICTest::Schema::Artist->add_column('parentid');
13 DBICTest::Schema::Artist->has_many(
14 children => 'DBICTest::Schema::Artist',
15 { 'foreign.parentid' => 'self.artistid' }
18 DBICTest::Schema::Artist->belongs_to(
19 parent => 'DBICTest::Schema::Artist',
20 { 'foreign.artistid' => 'self.parentid' }
24 use DBICTest ':DiffSQL';
26 use DBIx::Class::SQLMaker::LimitDialects;
27 my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
28 my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype;
30 for my $q ( '', '"' ) {
32 my $schema = DBICTest->init_schema(
33 storage_type => 'DBIx::Class::Storage::DBI::Oracle::Generic',
38 # select the whole tree
40 my $rs = $schema->resultset('Artist')->search({}, {
41 start_with => { name => 'root' },
42 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
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}
53 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
58 $rs->count_rs->as_query,
61 FROM ${q}artist${q} ${q}me${q}
62 START WITH ${q}name${q} = ?
63 CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
65 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
70 # use order siblings by statement
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' },
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
87 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
94 my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
95 start_with => { name => 'root' },
96 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
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}
108 [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
113 # combine a connect by with a join
115 my $rs = $schema->resultset('Artist')->search(
116 {'cds.title' => { -like => '%cd'} },
119 start_with => { 'me.name' => 'root' },
120 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
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}
135 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
137 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
143 $rs->count_rs->as_query,
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}
153 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
155 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
161 # combine a connect by with order_by
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' ] },
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
179 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
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' } ],
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}
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
208 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
209 => 'root'], [ $ROWS => 2 ],
214 $rs->count_rs->as_query,
218 SELECT ${q}me${q}.${q}artistid${q}
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}
229 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
236 # combine a connect_by with group_by and having
237 # add some bindvals to make sure things still work
239 my $rs = $schema->resultset('Artist')->search({}, {
240 select => \[ 'COUNT(rank) + ?', [ __cbind => 3 ] ],
242 start_with => { name => 'root' },
243 connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
244 group_by => \[ 'rank + ? ', [ __gbind => 1] ],
245 having => \[ 'count(rank) < ?', [ cnt => 2 ] ],
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}
256 HAVING count(rank) < ?
259 [ { dbic_colname => '__cbind' }
261 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
263 [ { dbic_colname => '__gbind' }
265 [ { dbic_colname => 'cnt' }
271 # select the whole cycle tree with nocylce
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' } } },
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}
289 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
295 $rs->count_rs->as_query,
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}
303 [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }