[dbsrgits/DBIx-Class.git] / t / 73oracle_hq.t

use strict;
use warnings;

use Test::Exception;
use Test::More;
use DBIx::Class::Optional::Dependencies ();
use lib qw(t/lib);
use DBICTest::RunMode;
use DBIC::SqlMakerTest;

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

$ENV{NLS_SORT} = "BINARY";
$ENV{NLS_COMP} = "BINARY";
$ENV{NLS_LANG} = "AMERICAN";

my ($dsn,  $user,  $pass)  = @ENV{map { "DBICTEST_ORA_${_}" }  qw/DSN USER PASS/};

plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test.'
 unless ($dsn && $user && $pass);

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

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;

my $schema = DBICTest::Schema->connect($dsn, $user, $pass);

note "Oracle Version: " . $schema->storage->_server_info->{dbms_version};

my $dbh = $schema->storage->dbh;
do_creates($dbh);

### test hierarchical queries
{
  $schema->resultset('Artist')->create ({
    name => 'root',
    rank => 1,
    cds => [],
    children => [
      {
        name => 'child1',
        rank => 2,
        children => [
          {
            name => 'grandchild',
            rank => 3,
            cds => [
              {
                title => "grandchilds's cd" ,
                year => '2008',
                tracks => [
                  {
                    position => 1,
                    title => 'Track 1 grandchild',
                  }
                ],
              }
            ],
            children => [
              {
                name => 'greatgrandchild',
                rank => 3,
              }
            ],
          }
        ],
      },
      {
        name => 'child2',
        rank => 3,
      },
    ],
  });

  $schema->resultset('Artist')->create({
    name => 'cycle-root',
    children => [
      {
        name => 'cycle-child1',
        children => [ { name => 'cycle-grandchild' } ],
      },
      {
        name => 'cycle-child2'
      },
    ],
  });

  $schema->resultset('Artist')->find({ name => 'cycle-root' })
    ->update({ parentid => { -ident => 'artistid' } });

  # 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 me.artistid, me.name, me.rank, me.charfield, me.parentid
          FROM artist me
        START WITH name = ?
        CONNECT BY parentid = PRIOR artistid
      )',
      [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'root'] ],
    );
    is_deeply (
      [ $rs->get_column ('name')->all ],
      [ qw/root child1 grandchild greatgrandchild child2/ ],
      'got artist tree',
    );

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

    is( $rs->count, 5, 'Connect By count ok' );
  }

  # use order siblings by statement
  SKIP: {
    # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2066123
    skip q{Oracle8i doesn't support ORDER SIBLINGS BY}, 1
      if $schema->storage->_server_info->{normalized_dbms_version} < 9;

    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 me.artistid, me.name, me.rank, me.charfield, me.parentid
          FROM artist me
        START WITH name = ?
        CONNECT BY parentid = PRIOR artistid
        ORDER SIBLINGS BY name DESC
      )',
      [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'root'] ],
    );

    is_deeply (
      [ $rs->get_column ('name')->all ],
      [ qw/root child2 child1 grandchild greatgrandchild/ ],
      'Order Siblings By ok',
    );
  }

  # 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 me.artistid, me.name, me.rank, me.charfield, me.parentid
          FROM artist me
        WHERE ( parentid IS NULL )
        START WITH name = ?
        CONNECT BY parentid = PRIOR artistid
      )',
      [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'root'] ],
    );

    is_deeply(
      [ $rs->get_column('name')->all ],
      [ 'root' ],
      'found root node',
    );
  }

  # combine a connect by with a join
  SKIP: {
    # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2066123
    skip q{Oracle8i doesn't support connect by with join}, 1
      if $schema->storage->_server_info->{normalized_dbms_version} < 9;

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

    is_deeply(
      [ $rs->get_column('name')->all ],
      [ 'grandchild' ],
      'Connect By with a join result name ok'
    );

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

    is( $rs->count, 1, 'Connect By with a join; count ok' );
  }

  # 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 me.artistid, me.name, me.rank, me.charfield, me.parentid
          FROM artist me
        START WITH name = ?
        CONNECT BY parentid = PRIOR artistid
        ORDER BY LEVEL ASC, name ASC
      )',
      [
        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'root'],
      ],
    );


    # Don't use "$rs->get_column ('name')->all" they build a query arround the $rs.
    #   If $rs has a order by, the order by is in the subquery and this doesn't work with Oracle 8i.
    # TODO: write extra test and fix order by handling on Oracle 8i
    is_deeply (
      [ map { $_->[1] } $rs->cursor->all ],
      [ qw/root child1 child2 grandchild greatgrandchild/ ],
      'Connect By with a order_by - result name ok (without get_column)'
    );

    SKIP: {
      skip q{Connect By with a order_by - result name ok (with get_column), Oracle8i doesn't support order by in a subquery},1
        if $schema->storage->_server_info->{normalized_dbms_version} < 9;
      is_deeply (
        [  $rs->get_column ('name')->all ],
        [ qw/root child1 child2 grandchild greatgrandchild/ ],
        'Connect By with a order_by - result name ok (with get_column)'
      );
    }
  }


  # limit a connect by
  SKIP: {
    skip q{Oracle8i doesn't support order by in a subquery}, 1
      if $schema->storage->_server_info->{normalized_dbms_version} < 9;

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

    is_deeply (
      [ $rs->get_column ('name')->all ],
      [qw/child1 child2/],
      'LIMIT a Connect By query - correct names'
    );

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

    is( $rs->count, 2, 'Connect By; LIMIT count ok' );
  }

  # 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 artist me
        START WITH name = ?
        CONNECT BY parentid = PRIOR artistid
        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 ],
      ],
    );

    is_deeply (
      [ $rs->get_column ('cnt')->all ],
      [4, 4],
      'Group By a Connect By query - correct values'
    );
  }

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

    # ORA-01436:  CONNECT BY loop in user data
    throws_ok { $rs->get_column ('name')->all } qr/ORA-01436/,
      "connect by initify loop detection without nocycle";
  }

  # select the whole cycle tree with nocylce
  SKIP: {
    # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1023748
    skip q{Oracle8i doesn't support connect by nocycle}, 1
      if $schema->storage->_server_info->{normalized_dbms_version} < 9;

    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 me.artistid, me.name, me.rank, me.charfield, me.parentid, CONNECT_BY_ISCYCLE
          FROM artist me
        START WITH name = ?
        CONNECT BY NOCYCLE parentid = PRIOR artistid
      )',
      [
        [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
            => 'cycle-root'],
      ],
    );
    is_deeply (
      [ $rs->get_column ('name')->all ],
      [ qw/cycle-root cycle-child1 cycle-grandchild cycle-child2/ ],
      'got artist tree with nocycle (name)',
    );
    is_deeply (
      [ $rs->get_column ('connector')->all ],
      [ qw/1 0 0 0/ ],
      'got artist tree with nocycle (CONNECT_BY_ISCYCLE)',
    );

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

    is( $rs->count, 4, 'Connect By Nocycle count ok' );
  }
}

done_testing;

sub do_creates {
  my $dbh = shift;

  eval {
    $dbh->do("DROP SEQUENCE artist_autoinc_seq");
    $dbh->do("DROP SEQUENCE artist_pk_seq");
    $dbh->do("DROP SEQUENCE cd_seq");
    $dbh->do("DROP SEQUENCE track_seq");
    $dbh->do("DROP TABLE artist");
    $dbh->do("DROP TABLE track");
    $dbh->do("DROP TABLE cd");
  };

  $dbh->do("CREATE SEQUENCE artist_pk_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
  $dbh->do("CREATE SEQUENCE cd_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
  $dbh->do("CREATE SEQUENCE track_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");

  $dbh->do("CREATE TABLE artist (artistid NUMBER(12), parentid NUMBER(12), name VARCHAR(255), autoinc_col NUMBER(12), rank NUMBER(38), charfield VARCHAR2(10))");
  $dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))");

  $dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4), genreid NUMBER(12), single_track NUMBER(12))");
  $dbh->do("ALTER TABLE cd ADD (CONSTRAINT cd_pk PRIMARY KEY (cdid))");

  $dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12) REFERENCES cd(cdid) DEFERRABLE, position NUMBER(12), title VARCHAR(255), last_updated_on DATE, last_updated_at DATE, small_dt DATE)");
  $dbh->do("ALTER TABLE track ADD (CONSTRAINT track_pk PRIMARY KEY (trackid))");

  $dbh->do(qq{
    CREATE OR REPLACE TRIGGER artist_insert_trg_pk
    BEFORE INSERT ON artist
    FOR EACH ROW
      BEGIN
        IF :new.artistid IS NULL THEN
          SELECT artist_pk_seq.nextval
          INTO :new.artistid
          FROM DUAL;
        END IF;
      END;
  });
  $dbh->do(qq{
    CREATE OR REPLACE TRIGGER cd_insert_trg
    BEFORE INSERT OR UPDATE ON cd
    FOR EACH ROW

      DECLARE
      tmpVar NUMBER;

      BEGIN
        tmpVar := 0;

        IF :new.cdid IS NULL THEN
          SELECT cd_seq.nextval
          INTO tmpVar
          FROM dual;

          :new.cdid := tmpVar;
        END IF;
      END;
  });
  $dbh->do(qq{
    CREATE OR REPLACE TRIGGER track_insert_trg
    BEFORE INSERT ON track
    FOR EACH ROW
      BEGIN
        IF :new.trackid IS NULL THEN
          SELECT track_seq.nextval
          INTO :new.trackid
          FROM DUAL;
        END IF;
      END;
  });
}

# clean up our mess
END {
  if ($schema and my $dbh = $schema->storage->dbh) {
    eval { $dbh->do($_) } for (
      'DROP SEQUENCE artist_pk_seq',
      'DROP SEQUENCE cd_seq',
      'DROP SEQUENCE track_seq',
      'DROP TABLE artist',
      'DROP TABLE track',
      'DROP TABLE cd',
    );
  };
  undef $schema;
}
Commit	Line	Data
12e05c15	1	use strict;
	2	use warnings;
	3
	4	use Test::Exception;
	5	use Test::More;
199fbc45	6	use DBIx::Class::Optional::Dependencies ();
12e05c15	7	use lib qw(t/lib);
8d6b1478	8	use DBICTest::RunMode;
12e05c15	9	use DBIC::SqlMakerTest;
12e05c15	10
fcb7fcbb	11	use DBIx::Class::SQLMaker::LimitDialects;
	12	my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
	13	my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype,
	14
994dc91b	15	$ENV{NLS_SORT} = "BINARY";
	16	$ENV{NLS_COMP} = "BINARY";
	17	$ENV{NLS_LANG} = "AMERICAN";
	18
12e05c15	19	my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/};
	20
	21	plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test.'
	22	unless ($dsn && $user && $pass);
	23
e6983054	24	plan skip_all => 'Test needs ' . DBIx::Class::Optional::Dependencies->req_missing_for ('rdbms_oracle')
	25	unless DBIx::Class::Optional::Dependencies->req_ok_for ('rdbms_oracle');
	26
12e05c15	27	use DBICTest::Schema::Artist;
	28	BEGIN {
	29	DBICTest::Schema::Artist->add_column('parentid');
	30
	31	DBICTest::Schema::Artist->has_many(
	32	children => 'DBICTest::Schema::Artist',
	33	{ 'foreign.parentid' => 'self.artistid' }
	34	);
	35
	36	DBICTest::Schema::Artist->belongs_to(
	37	parent => 'DBICTest::Schema::Artist',
	38	{ 'foreign.artistid' => 'self.parentid' }
	39	);
	40	}
	41
8d6b1478	42	use DBICTest;
12e05c15	43	use DBICTest::Schema;
	44
	45	my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
	46
	47	note "Oracle Version: " . $schema->storage->_server_info->{dbms_version};
	48
	49	my $dbh = $schema->storage->dbh;
	50	do_creates($dbh);
	51
	52	### test hierarchical queries
	53	{
	54	$schema->resultset('Artist')->create ({
	55	name => 'root',
	56	rank => 1,
	57	cds => [],
	58	children => [
	59	{
	60	name => 'child1',
	61	rank => 2,
	62	children => [
	63	{
	64	name => 'grandchild',
	65	rank => 3,
	66	cds => [
	67	{
	68	title => "grandchilds's cd" ,
	69	year => '2008',
	70	tracks => [
	71	{
	72	position => 1,
	73	title => 'Track 1 grandchild',
	74	}
	75	],
	76	}
	77	],
	78	children => [
	79	{
	80	name => 'greatgrandchild',
	81	rank => 3,
	82	}
	83	],
	84	}
	85	],
	86	},
	87	{
	88	name => 'child2',
	89	rank => 3,
	90	},
	91	],
	92	});
	93
	94	$schema->resultset('Artist')->create({
	95	name => 'cycle-root',
	96	children => [
	97	{
	98	name => 'cycle-child1',
	99	children => [ { name => 'cycle-grandchild' } ],
	100	},
	101	{
	102	name => 'cycle-child2'
	103	},
	104	],
	105	});
	106
107	$schema->resultset('Artist')->find({ name => 'cycle-root' })
108	->update({ parentid => { -ident => 'artistid' } });
109
110	# select the whole tree
111	{
112	my $rs = $schema->resultset('Artist')->search({}, {
113	start_with => { name => 'root' },
114	connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
115	});
116
117	is_same_sql_bind (
118	$rs->as_query,
119	'(
120	SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
121	FROM artist me
122	START WITH name = ?
fcb7fcbb	123	CONNECT BY parentid = PRIOR artistid
12e05c15	124	)',
0e773352	125	[ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
0e773352	126	=> 'root'] ],
12e05c15	127	);
	128	is_deeply (
	129	[ $rs->get_column ('name')->all ],
	130	[ qw/root child1 grandchild greatgrandchild child2/ ],
	131	'got artist tree',
	132	);
	133
	134	is_same_sql_bind (
	135	$rs->count_rs->as_query,
	136	'(
	137	SELECT COUNT( * )
	138	FROM artist me
	139	START WITH name = ?
fcb7fcbb	140	CONNECT BY parentid = PRIOR artistid
12e05c15	141	)',
0e773352	142	[ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
0e773352	143	=> 'root'] ],
12e05c15	144	);
	145
	146	is( $rs->count, 5, 'Connect By count ok' );
	147	}
	148
	149	# use order siblings by statement
	150	SKIP: {
	151	# http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2066123
	152	skip q{Oracle8i doesn't support ORDER SIBLINGS BY}, 1
	153	if $schema->storage->_server_info->{normalized_dbms_version} < 9;
	154
	155	my $rs = $schema->resultset('Artist')->search({}, {
	156	start_with => { name => 'root' },
	157	connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
	158	order_siblings_by => { -desc => 'name' },
	159	});
	160
	161	is_same_sql_bind (
	162	$rs->as_query,
	163	'(
	164	SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
	165	FROM artist me
	166	START WITH name = ?
fcb7fcbb	167	CONNECT BY parentid = PRIOR artistid
12e05c15	168	ORDER SIBLINGS BY name DESC
12e05c15	169	)',
0e773352	170	[ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
0e773352	171	=> 'root'] ],
12e05c15	172	);
	173
	174	is_deeply (
	175	[ $rs->get_column ('name')->all ],
	176	[ qw/root child2 child1 grandchild greatgrandchild/ ],
	177	'Order Siblings By ok',
	178	);
	179	}
	180
	181	# get the root node
	182	{
	183	my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
	184	start_with => { name => 'root' },
	185	connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
	186	});
	187
	188	is_same_sql_bind (
	189	$rs->as_query,
	190	'(
	191	SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
	192	FROM artist me
	193	WHERE ( parentid IS NULL )
	194	START WITH name = ?
fcb7fcbb	195	CONNECT BY parentid = PRIOR artistid
12e05c15	196	)',
0e773352	197	[ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
0e773352	198	=> 'root'] ],
12e05c15	199	);
	200
	201	is_deeply(
	202	[ $rs->get_column('name')->all ],
	203	[ 'root' ],
	204	'found root node',
	205	);
	206	}
	207
	208	# combine a connect by with a join
	209	SKIP: {
	210	# http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2066123
	211	skip q{Oracle8i doesn't support connect by with join}, 1
	212	if $schema->storage->_server_info->{normalized_dbms_version} < 9;
	213
	214	my $rs = $schema->resultset('Artist')->search(
	215	{'cds.title' => { -like => '%cd'} },
	216	{
	217	join => 'cds',
	218	start_with => { 'me.name' => 'root' },
	219	connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
	220	}
	221	);
	222
	223	is_same_sql_bind (
	224	$rs->as_query,
	225	'(
	226	SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
	227	FROM artist me
	228	LEFT JOIN cd cds ON cds.artist = me.artistid
	229	WHERE ( cds.title LIKE ? )
	230	START WITH me.name = ?
fcb7fcbb	231	CONNECT BY parentid = PRIOR artistid
12e05c15	232	)',
0e773352	233	[
	234	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
	235	=> '%cd'],
	236	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
	237	=> 'root'],
	238	],
12e05c15	239	);
	240
	241	is_deeply(
	242	[ $rs->get_column('name')->all ],
	243	[ 'grandchild' ],
	244	'Connect By with a join result name ok'
	245	);
	246
	247	is_same_sql_bind (
	248	$rs->count_rs->as_query,
	249	'(
	250	SELECT COUNT( * )
	251	FROM artist me
	252	LEFT JOIN cd cds ON cds.artist = me.artistid
	253	WHERE ( cds.title LIKE ? )
	254	START WITH me.name = ?
fcb7fcbb	255	CONNECT BY parentid = PRIOR artistid
12e05c15	256	)',
0e773352	257	[
	258	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
	259	=> '%cd'],
	260	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
	261	=> 'root'],
	262	],
12e05c15	263	);
	264
	265	is( $rs->count, 1, 'Connect By with a join; count ok' );
	266	}
	267
	268	# combine a connect by with order_by
	269	{
	270	my $rs = $schema->resultset('Artist')->search({}, {
	271	start_with => { name => 'root' },
	272	connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
	273	order_by => { -asc => [ 'LEVEL', 'name' ] },
	274	});
	275
	276	is_same_sql_bind (
	277	$rs->as_query,
	278	'(
	279	SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
	280	FROM artist me
	281	START WITH name = ?
fcb7fcbb	282	CONNECT BY parentid = PRIOR artistid
12e05c15	283	ORDER BY LEVEL ASC, name ASC
12e05c15	284	)',
0e773352	285	[
	286	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
	287	=> 'root'],
	288	],
12e05c15	289	);
	290
	291
	292	# Don't use "$rs->get_column ('name')->all" they build a query arround the $rs.
	293	# If $rs has a order by, the order by is in the subquery and this doesn't work with Oracle 8i.
	294	# TODO: write extra test and fix order by handling on Oracle 8i
	295	is_deeply (
	296	[ map { $_->[1] } $rs->cursor->all ],
	297	[ qw/root child1 child2 grandchild greatgrandchild/ ],
	298	'Connect By with a order_by - result name ok (without get_column)'
	299	);
	300
	301	SKIP: {
	302	skip q{Connect By with a order_by - result name ok (with get_column), Oracle8i doesn't support order by in a subquery},1
	303	if $schema->storage->_server_info->{normalized_dbms_version} < 9;
	304	is_deeply (
	305	[ $rs->get_column ('name')->all ],
	306	[ qw/root child1 child2 grandchild greatgrandchild/ ],
	307	'Connect By with a order_by - result name ok (with get_column)'
	308	);
	309	}
	310	}
	311
	312
	313	# limit a connect by
	314	SKIP: {
	315	skip q{Oracle8i doesn't support order by in a subquery}, 1
	316	if $schema->storage->_server_info->{normalized_dbms_version} < 9;
	317
	318	my $rs = $schema->resultset('Artist')->search({}, {
	319	start_with => { name => 'root' },
	320	connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
6a6394f1	321	order_by => [ { -asc => 'name' }, { -desc => 'artistid' } ],
12e05c15	322	rows => 2,
	323	});
	324
	325	is_same_sql_bind (
	326	$rs->as_query,
	327	'(
90ed89cb	328	SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
12e05c15	329	FROM (
	330	SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
	331	FROM artist me
	332	START WITH name = ?
	333	CONNECT BY parentid = PRIOR artistid
6a6394f1	334	ORDER BY name ASC, artistid DESC
12e05c15	335	) me
fcb7fcbb	336	WHERE ROWNUM <= ?
12e05c15	337	)',
0e773352	338	[
0e773352	339	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
fcb7fcbb	340	=> 'root'], [ $ROWS => 2 ],
0e773352	341	],
12e05c15	342	);
	343
	344	is_deeply (
	345	[ $rs->get_column ('name')->all ],
	346	[qw/child1 child2/],
	347	'LIMIT a Connect By query - correct names'
	348	);
	349
	350	is_same_sql_bind (
	351	$rs->count_rs->as_query,
	352	'(
	353	SELECT COUNT( * )
	354	FROM (
90ed89cb	355	SELECT me.artistid
12e05c15	356	FROM (
cccd1876	357	SELECT me.artistid
	358	FROM artist me
	359	START WITH name = ?
	360	CONNECT BY parentid = PRIOR artistid
12e05c15	361	) me
cccd1876	362	WHERE ROWNUM <= ?
12e05c15	363	) me
12e05c15	364	)',
0e773352	365	[
0e773352	366	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
6a6394f1	367	=> 'root'],
cccd1876	368	[ $ROWS => 2 ],
0e773352	369	],
12e05c15	370	);
	371
	372	is( $rs->count, 2, 'Connect By; LIMIT count ok' );
	373	}
	374
	375	# combine a connect_by with group_by and having
55d02972	376	# add some bindvals to make sure things still work
12e05c15	377	{
12e05c15	378	my $rs = $schema->resultset('Artist')->search({}, {
55d02972	379	select => \[ 'COUNT(rank) + ?', [ __cbind => 3 ] ],
55d02972	380	as => 'cnt',
12e05c15	381	start_with => { name => 'root' },
12e05c15	382	connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
55d02972	383	group_by => \[ 'rank + ? ', [ __gbind => 1] ],
12e05c15	384	having => \[ 'count(rank) < ?', [ cnt => 2 ] ],
	385	});
	386
	387	is_same_sql_bind (
	388	$rs->as_query,
	389	'(
55d02972	390	SELECT COUNT(rank) + ?
12e05c15	391	FROM artist me
	392	START WITH name = ?
	393	CONNECT BY parentid = PRIOR artistid
55d02972	394	GROUP BY( rank + ? ) HAVING count(rank) < ?
12e05c15	395	)',
55d02972	396	[
0e773352	397	[ { dbic_colname => '__cbind' }
	398	=> 3 ],
	399	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
	400	=> 'root'],
	401	[ { dbic_colname => '__gbind' }
	402	=> 1 ],
	403	[ { dbic_colname => 'cnt' }
	404	=> 2 ],
55d02972	405	],
12e05c15	406	);
	407
	408	is_deeply (
	409	[ $rs->get_column ('cnt')->all ],
55d02972	410	[4, 4],
12e05c15	411	'Group By a Connect By query - correct values'
	412	);
	413	}
	414
	415	# select the whole cycle tree without nocylce
	416	{
	417	my $rs = $schema->resultset('Artist')->search({}, {
	418	start_with => { name => 'cycle-root' },
	419	connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
	420	});
	421
	422	# ORA-01436: CONNECT BY loop in user data
	423	throws_ok { $rs->get_column ('name')->all } qr/ORA-01436/,
	424	"connect by initify loop detection without nocycle";
	425	}
	426
	427	# select the whole cycle tree with nocylce
	428	SKIP: {
	429	# http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1023748
	430	skip q{Oracle8i doesn't support connect by nocycle}, 1
	431	if $schema->storage->_server_info->{normalized_dbms_version} < 9;
	432
	433	my $rs = $schema->resultset('Artist')->search({}, {
	434	start_with => { name => 'cycle-root' },
	435	'+select' => \ 'CONNECT_BY_ISCYCLE',
	436	'+as' => [ 'connector' ],
	437	connect_by_nocycle => { parentid => { -prior => { -ident => 'artistid' } } },
	438	});
	439
	440	is_same_sql_bind (
	441	$rs->as_query,
	442	'(
	443	SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid, CONNECT_BY_ISCYCLE
	444	FROM artist me
	445	START WITH name = ?
fcb7fcbb	446	CONNECT BY NOCYCLE parentid = PRIOR artistid
12e05c15	447	)',
0e773352	448	[
	449	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
	450	=> 'cycle-root'],
	451	],
12e05c15	452	);
	453	is_deeply (
	454	[ $rs->get_column ('name')->all ],
	455	[ qw/cycle-root cycle-child1 cycle-grandchild cycle-child2/ ],
	456	'got artist tree with nocycle (name)',
	457	);
	458	is_deeply (
	459	[ $rs->get_column ('connector')->all ],
	460	[ qw/1 0 0 0/ ],
	461	'got artist tree with nocycle (CONNECT_BY_ISCYCLE)',
	462	);
	463
	464	is_same_sql_bind (
	465	$rs->count_rs->as_query,
	466	'(
	467	SELECT COUNT( * )
	468	FROM artist me
	469	START WITH name = ?
fcb7fcbb	470	CONNECT BY NOCYCLE parentid = PRIOR artistid
12e05c15	471	)',
0e773352	472	[
	473	[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
	474	=> 'cycle-root'],
	475	],
12e05c15	476	);
	477
	478	is( $rs->count, 4, 'Connect By Nocycle count ok' );
	479	}
	480	}
	481
	482	done_testing;
	483
	484	sub do_creates {
	485	my $dbh = shift;
	486
	487	eval {
	488	$dbh->do("DROP SEQUENCE artist_autoinc_seq");
	489	$dbh->do("DROP SEQUENCE artist_pk_seq");
	490	$dbh->do("DROP SEQUENCE cd_seq");
	491	$dbh->do("DROP SEQUENCE track_seq");
	492	$dbh->do("DROP TABLE artist");
	493	$dbh->do("DROP TABLE track");
	494	$dbh->do("DROP TABLE cd");
	495	};
	496
	497	$dbh->do("CREATE SEQUENCE artist_pk_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
	498	$dbh->do("CREATE SEQUENCE cd_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
	499	$dbh->do("CREATE SEQUENCE track_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
	500
	501	$dbh->do("CREATE TABLE artist (artistid NUMBER(12), parentid NUMBER(12), name VARCHAR(255), autoinc_col NUMBER(12), rank NUMBER(38), charfield VARCHAR2(10))");
	502	$dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))");
	503
	504	$dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4), genreid NUMBER(12), single_track NUMBER(12))");
	505	$dbh->do("ALTER TABLE cd ADD (CONSTRAINT cd_pk PRIMARY KEY (cdid))");
	506
	507	$dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12) REFERENCES cd(cdid) DEFERRABLE, position NUMBER(12), title VARCHAR(255), last_updated_on DATE, last_updated_at DATE, small_dt DATE)");
	508	$dbh->do("ALTER TABLE track ADD (CONSTRAINT track_pk PRIMARY KEY (trackid))");
	509
	510	$dbh->do(qq{
	511	CREATE OR REPLACE TRIGGER artist_insert_trg_pk
	512	BEFORE INSERT ON artist
	513	FOR EACH ROW
	514	BEGIN
	515	IF :new.artistid IS NULL THEN
	516	SELECT artist_pk_seq.nextval
	517	INTO :new.artistid
	518	FROM DUAL;
	519	END IF;
	520	END;
	521	});
	522	$dbh->do(qq{
	523	CREATE OR REPLACE TRIGGER cd_insert_trg
	524	BEFORE INSERT OR UPDATE ON cd
	525	FOR EACH ROW
	526
	527	DECLARE
	528	tmpVar NUMBER;
	529
	530	BEGIN
	531	tmpVar := 0;
	532
	533	IF :new.cdid IS NULL THEN
	534	SELECT cd_seq.nextval
	535	INTO tmpVar
	536	FROM dual;
	537
	538	:new.cdid := tmpVar;
	539	END IF;
540	END;
541	});
542	$dbh->do(qq{
543	CREATE OR REPLACE TRIGGER track_insert_trg
544	BEFORE INSERT ON track
545	FOR EACH ROW
546	BEGIN
547	IF :new.trackid IS NULL THEN
548	SELECT track_seq.nextval
549	INTO :new.trackid
550	FROM DUAL;
551	END IF;
552	END;
553	});
554	}
555
556	# clean up our mess
557	END {
65d35121	558	if ($schema and my $dbh = $schema->storage->dbh) {
	559	eval { $dbh->do($_) } for (
	560	'DROP SEQUENCE artist_pk_seq',
	561	'DROP SEQUENCE cd_seq',
	562	'DROP SEQUENCE track_seq',
	563	'DROP TABLE artist',
	564	'DROP TABLE track',
	565	'DROP TABLE cd',
	566	);
12e05c15	567	};
65d35121	568	undef $schema;
12e05c15	569	}