8 use DBIC::SqlMakerTest;
9 use DBIx::Class::SQLAHacks::Oracle;
14 # Offline test for connect_by
15 # ( without acitve database connection)
19 connect_by => { 'parentid' => { '-prior' => \'artistid' } },
20 stmt => "parentid = PRIOR( artistid )",
22 msg => 'Simple: parentid = PRIOR artistid',
25 connect_by => { 'parentid' => { '!=' => { '-prior' => \'artistid' } } },
26 stmt => "parentid != PRIOR( artistid )",
28 msg => 'Simple: parentid != PRIOR artistid',
30 # Example from http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm
32 # CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
35 last_name => { '!=' => 'King' },
36 manager_id => { '-prior' => \'employee_id' },
38 stmt => "( last_name != ? AND manager_id = PRIOR( employee_id ) )",
40 msg => 'oracle.com excample #1',
42 # CONNECT BY PRIOR employee_id = manager_id and
43 # PRIOR account_mgr_id = customer_id ...
46 manager_id => { '-prior' => \'employee_id' },
47 customer_id => { '-prior' => \'account_mgr_id' },
49 stmt => "( manager_id = PRIOR( employee_id ) AND customer_id = PRIOR( account_mgr_id ) )",
51 msg => 'oracle.com excample #2',
53 # CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;
54 # TODO: NOCYCLE parameter doesn't work
57 my $sqla_oracle = DBIx::Class::SQLAHacks::Oracle->new();
58 isa_ok($sqla_oracle, 'DBIx::Class::SQLAHacks::Oracle');
61 my $test_count = ( @handle_tests * 2 ) + 1;
63 for my $case (@handle_tests) {
64 local $Data::Dumper::Terse = 1;
66 my $msg = sprintf("Offline: %s",
67 $case->{msg} || substr($case->{stmt},0,25),
71 ( $stmt, @bind ) = $sqla_oracle->_recurse_where( $case->{connect_by}, 'and' );
72 is_same_sql_bind( $stmt, \@bind, $case->{stmt}, $case->{bind},$msg )
73 || diag "Search term:\n" . Dumper $case->{connect_by};
75 ,sprintf("lives is ok from '%s'",$msg));
83 done_testing( $test_count );