[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;
use DBICTest::Schema;
use DBIC::SqlMakerTest;

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