[dbsrgits/DBIx-Class.git] / t / sqlmaker / hierarchical / oracle.t

use strict;
use warnings;

use Test::More;
use lib qw(t/lib);

use DBIx::Class::Optional::Dependencies;
plan skip_all => 'Test needs ' . DBIx::Class::Optional::Dependencies->req_missing_for ('id_shortener')
  unless DBIx::Class::Optional::Dependencies->req_ok_for ('id_shortener');

use DBICTest::Schema::Artist;
BEGIN {
  DBICTest::Schema::Artist->add_column('parentid');

  DBICTest::Schema::Artist->has_many(
    children => 'DBICTest::Schema::Artist',
    { 'foreign.parentid' => 'self.artistid' }
  );

  DBICTest::Schema::Artist->belongs_to(
    parent => 'DBICTest::Schema::Artist',
    { 'foreign.artistid' => 'self.parentid' }
  );
}

use DBICTest ':DiffSQL';

use DBIx::Class::SQLMaker::LimitDialects;
my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype;

for my $q ( '', '"' ) {

  my $schema = DBICTest->init_schema(
    storage_type => 'DBIx::Class::Storage::DBI::Oracle::Generic',
    no_deploy => 1,
    quote_char => $q,
  );

  # select the whole tree
  {
    my $rs = $schema->resultset('Artist')->search({}, {
      start_with => { name => 'root' },
      connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
    });

    is_same_sql_bind (
      $rs->as_query,
      "(
        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}
          FROM ${q}artist${q} ${q}me${q}
        START WITH ${q}name${q} = ?
        CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
      )",
      [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'root'] ],
    );

    is_same_sql_bind (
      $rs->count_rs->as_query,
      "(
        SELECT COUNT( * )
          FROM ${q}artist${q} ${q}me${q}
        START WITH ${q}name${q} = ?
        CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
      )",
      [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'root'] ],
    );
  }

  # use order siblings by statement
  {
    my $rs = $schema->resultset('Artist')->search({}, {
      start_with => { name => 'root' },
      connect_by => { parentid => { -prior => { -ident =>  'artistid' } } },
      order_siblings_by => { -desc => 'name' },
    });

    is_same_sql_bind (
      $rs->as_query,
      "(
        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}
          FROM ${q}artist${q} ${q}me${q}
        START WITH ${q}name${q} = ?
        CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
        ORDER SIBLINGS BY ${q}name${q} DESC
      )",
      [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'root'] ],
    );
  }

  # get the root node
  {
    my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
      start_with => { name => 'root' },
      connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
    });

    is_same_sql_bind (
      $rs->as_query,
      "(
        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}
          FROM ${q}artist${q} ${q}me${q}
        WHERE ( ${q}parentid${q} IS NULL )
        START WITH ${q}name${q} = ?
        CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
      )",
      [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'root'] ],
    );
  }

  # combine a connect by with a join
  {
    my $rs = $schema->resultset('Artist')->search(
      {'cds.title' => { -like => '%cd'} },
      {
        join => 'cds',
        start_with => { 'me.name' => 'root' },
        connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
      }
    );

    is_same_sql_bind (
      $rs->as_query,
      "(
        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}
          FROM ${q}artist${q} ${q}me${q}
          LEFT JOIN cd ${q}cds${q} ON ${q}cds${q}.${q}artist${q} = ${q}me${q}.${q}artistid${q}
        WHERE ( ${q}cds${q}.${q}title${q} LIKE ? )
        START WITH ${q}me${q}.${q}name${q} = ?
        CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
      )",
      [
        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
            => '%cd'],
        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
            => 'root'],
      ],
    );

    is_same_sql_bind (
      $rs->count_rs->as_query,
      "(
        SELECT COUNT( * )
          FROM ${q}artist${q} ${q}me${q}
          LEFT JOIN cd ${q}cds${q} ON ${q}cds${q}.${q}artist${q} = ${q}me${q}.${q}artistid${q}
        WHERE ( ${q}cds${q}.${q}title${q} LIKE ? )
        START WITH ${q}me${q}.${q}name${q} = ?
        CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
      )",
      [
        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
            => '%cd'],
        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
              => 'root'],
      ],
    );
  }

  # combine a connect by with order_by
  {
    my $rs = $schema->resultset('Artist')->search({}, {
      start_with => { name => 'root' },
      connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
      order_by => { -asc => [ 'LEVEL', 'name' ] },
    });

    is_same_sql_bind (
      $rs->as_query,
      "(
        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}
          FROM ${q}artist${q} ${q}me${q}
        START WITH ${q}name${q} = ?
        CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
        ORDER BY ${q}LEVEL${q} ASC, ${q}name${q} ASC
      )",
      [
        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'root'],
      ],
    );
  }

  # limit a connect by
  {
    my $rs = $schema->resultset('Artist')->search({}, {
      start_with => { name => 'root' },
      connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
      order_by => [ { -asc => 'name' }, {  -desc => 'artistid' } ],
      rows => 2,
    });

    is_same_sql_bind (
      $rs->as_query,
      "(
        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}
          FROM (
            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}
              FROM ${q}artist${q} ${q}me${q}
            START WITH ${q}name${q} = ?
            CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
            ORDER BY ${q}name${q} ASC, ${q}artistid${q} DESC
          ) ${q}me${q}
        WHERE ROWNUM <= ?
      )",
      [
        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'root'], [ $ROWS => 2 ],
      ],
    );

    is_same_sql_bind (
      $rs->count_rs->as_query,
      "(
        SELECT COUNT( * )
          FROM (
            SELECT ${q}me${q}.${q}artistid${q}
              FROM (
                SELECT ${q}me${q}.${q}artistid${q}
                  FROM ${q}artist${q} ${q}me${q}
                START WITH ${q}name${q} = ?
                CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
              ) ${q}me${q}
            WHERE ROWNUM <= ?
          ) ${q}me${q}
      )",
      [
        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'root'],
        [ $ROWS => 2 ],
      ],
    );
  }

  # combine a connect_by with group_by and having
  # add some bindvals to make sure things still work
  {
    my $rs = $schema->resultset('Artist')->search({}, {
      select => \[ 'COUNT(rank) + ?', [ __cbind => 3 ] ],
      as => 'cnt',
      start_with => { name => 'root' },
      connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
      group_by => \[ 'rank + ? ', [ __gbind =>  1] ],
      having => \[ 'count(rank) < ?', [ cnt => 2 ] ],
    });

    is_same_sql_bind (
      $rs->as_query,
      "(
        SELECT COUNT(rank) + ?
          FROM ${q}artist${q} ${q}me${q}
        START WITH ${q}name${q} = ?
        CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
        GROUP BY( rank + ? )
        HAVING count(rank) < ?
      )",
      [
        [ { dbic_colname => '__cbind' }
            => 3 ],
        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'root'],
        [ { dbic_colname => '__gbind' }
            => 1 ],
        [ { dbic_colname => 'cnt' }
            => 2 ],
      ],
    );
  }

  # select the whole cycle tree with nocylce
  {
    my $rs = $schema->resultset('Artist')->search({}, {
      start_with => { name => 'cycle-root' },
      '+select'  => \ 'CONNECT_BY_ISCYCLE',
      '+as'      => [ 'connector' ],
      connect_by_nocycle => { parentid => { -prior => { -ident => 'artistid' } } },
    });

    is_same_sql_bind (
      $rs->as_query,
      "(
        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
          FROM ${q}artist${q} ${q}me${q}
        START WITH ${q}name${q} = ?
        CONNECT BY NOCYCLE ${q}parentid${q} = PRIOR ${q}artistid${q}
      )",
      [
        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'cycle-root'],
      ],
    );

    is_same_sql_bind (
      $rs->count_rs->as_query,
      "(
        SELECT COUNT( * )
          FROM ${q}artist${q} ${q}me${q}
        START WITH ${q}name${q} = ?
        CONNECT BY NOCYCLE ${q}parentid${q} = PRIOR ${q}artistid${q}
      )",
      [
        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'cycle-root'],
      ],
    );
  }
}

done_testing;
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
a5a7bb73	26	use DBICTest ':DiffSQL';
ac0c0825	27
	28	use DBIx::Class::SQLMaker::LimitDialects;
	29	my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
	30	my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype;
	31
	32	for my $q ( '', '"' ) {
	33
	34	my $schema = DBICTest->init_schema(
	35	storage_type => 'DBIx::Class::Storage::DBI::Oracle::Generic',
	36	no_deploy => 1,
	37	quote_char => $q,
	38	);
	39
	40	# select the whole tree
	41	{
	42	my $rs = $schema->resultset('Artist')->search({}, {
	43	start_with => { name => 'root' },
	44	connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
	45	});
	46
	47	is_same_sql_bind (
	48	$rs->as_query,
	49	"(
	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}
	54	)",
	55	[ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
	56	=> 'root'] ],
	57	);
	58
	59	is_same_sql_bind (
	60	$rs->count_rs->as_query,
	61	"(
	62	SELECT COUNT( * )
	63	FROM ${q}artist${q} ${q}me${q}
	64	START WITH ${q}name${q} = ?
	65	CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
	66	)",
	67	[ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
	68	=> 'root'] ],
	69	);
	70	}
	71
	72	# use order siblings by statement
	73	{
	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' },
	78	});
	79
	80	is_same_sql_bind (
	81	$rs->as_query,
	82	"(
	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
	88	)",
	89	[ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
	90	=> 'root'] ],
91	);
92	}
93
94	# get the root node
95	{
96	my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
97	start_with => { name => 'root' },
98	connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
99	});
100
101	is_same_sql_bind (
102	$rs->as_query,
103	"(
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}
109	)",
110	[ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
111	=> 'root'] ],
112	);
113	}
114
115	# combine a connect by with a join
116	{
117	my $rs = $schema->resultset('Artist')->search(
118	{'cds.title' => { -like => '%cd'} },
119	{
120	join => 'cds',
121	start_with => { 'me.name' => 'root' },
122	connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
123	}
124	);
125
126	is_same_sql_bind (
127	$rs->as_query,
128	"(
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}
135	)",
136	[
137	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
138	=> '%cd'],
139	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
140	=> 'root'],
141	],
142	);
143
144	is_same_sql_bind (
145	$rs->count_rs->as_query,
146	"(
147	SELECT COUNT( * )
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}
153	)",
154	[
155	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
156	=> '%cd'],
157	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
158	=> 'root'],
159	],
160	);
161	}
162
163	# combine a connect by with order_by
164	{
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' ] },
169	});
170
171	is_same_sql_bind (
172	$rs->as_query,
173	"(
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
179	)",
180	[
181	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
182	=> 'root'],
183	],
184	);
185	}
186
187	# limit a connect by
188	{
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' } ],
193	rows => 2,
194	});
195
196	is_same_sql_bind (
197	$rs->as_query,
198	"(
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 (
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
206	) ${q}me${q}
207	WHERE ROWNUM <= ?
208	)",
209	[
210	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
211	=> 'root'], [ $ROWS => 2 ],
212	],
213	);
214
215	is_same_sql_bind (
216	$rs->count_rs->as_query,
217	"(
218	SELECT COUNT( * )
219	FROM (
220	SELECT ${q}me${q}.${q}artistid${q}
221	FROM (
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}
226	) ${q}me${q}
227	WHERE ROWNUM <= ?
228	) ${q}me${q}
229	)",
230	[
231	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
232	=> 'root'],
233	[ $ROWS => 2 ],
234	],
235	);
236	}
237
238	# combine a connect_by with group_by and having
239	# add some bindvals to make sure things still work
240	{
241	my $rs = $schema->resultset('Artist')->search({}, {
242	select => \[ 'COUNT(rank) + ?', [ __cbind => 3 ] ],
243	as => 'cnt',
244	start_with => { name => 'root' },
245	connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
246	group_by => \[ 'rank + ? ', [ __gbind => 1] ],
247	having => \[ 'count(rank) < ?', [ cnt => 2 ] ],
248	});
249
250	is_same_sql_bind (
251	$rs->as_query,
252	"(
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}
257	GROUP BY( rank + ? )
258	HAVING count(rank) < ?
259	)",
260	[
261	[ { dbic_colname => '__cbind' }
262	=> 3 ],
263	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
264	=> 'root'],
265	[ { dbic_colname => '__gbind' }
266	=> 1 ],
267	[ { dbic_colname => 'cnt' }
268	=> 2 ],
269	],
270	);
271	}
272
273	# select the whole cycle tree with nocylce
274	{
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' } } },
280	});
281
282	is_same_sql_bind (
283	$rs->as_query,
284	"(
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}
289	)",
290	[
291	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
292	=> 'cycle-root'],
293	],
294	);
295
296	is_same_sql_bind (
297	$rs->count_rs->as_query,
298	"(
299	SELECT COUNT( * )
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}
303	)",
304	[
305	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
306	=> 'cycle-root'],
307	],
308	);
309	}
310	}
311
312	done_testing;