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

use DBIx::Class::Optional::Dependencies -skip_all_without => 'id_shortener';

use strict;
use warnings;

use Test::More;

use lib qw(t/lib);
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
cb551b07	1	use DBIx::Class::Optional::Dependencies -skip_all_without => 'id_shortener';
cb551b07	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;