+BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
+use DBIx::Class::Optional::Dependencies -skip_all_without => 'test_rdbms_oracle';
+
use strict;
use warnings;
use Test::Exception;
use Test::More;
-use DBIx::Class::Optional::Dependencies ();
-use lib qw(t/lib);
-use DBICTest::RunMode;
-$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');
+# I *strongly* suspect Oracle has an implicit stable output order when
+# dealing with HQs. So just punt on the entire shuffle thing.
+BEGIN { $ENV{DBIC_SHUFFLE_UNORDERED_RESULTSETS} = 0 }
use DBICTest::Schema::Artist;
BEGIN {
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,
+$ENV{NLS_SORT} = "BINARY";
+$ENV{NLS_COMP} = "BINARY";
+$ENV{NLS_LANG} = "AMERICAN";
+my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/};
my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
note "Oracle Version: " . $schema->storage->_server_info->{dbms_version};
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' );
}
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/ ],
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' ],
}
);
- 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' );
}
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
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' );
}
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],
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 (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' );
}
}