Commit | Line | Data |
43426175 |
1 | |
2 | use strict; |
3 | use warnings; |
4 | use Test::More; |
5 | use Test::Exception; |
6 | use Data::Dumper; |
7 | use lib qw(t/lib); |
8 | use DBIC::SqlMakerTest; |
9 | use DBIx::Class::SQLAHacks::Oracle; |
10 | |
11 | |
12 | |
13 | # |
14 | # Offline test for connect_by |
15 | # ( without acitve database connection) |
16 | # |
17 | my @handle_tests = ( |
18 | { |
19 | connect_by => { 'parentid' => { '-prior' => \'artistid' } }, |
2df1857d |
20 | stmt => "parentid = PRIOR( artistid )", |
43426175 |
21 | bind => [], |
22 | msg => 'Simple: parentid = PRIOR artistid', |
23 | }, |
2df1857d |
24 | { |
25 | connect_by => { 'parentid' => { '!=' => { '-prior' => \'artistid' } } }, |
26 | stmt => "parentid != PRIOR( artistid )", |
27 | bind => [], |
28 | msg => 'Simple: parentid != PRIOR artistid', |
29 | }, |
30 | # Example from http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm |
43426175 |
31 | |
2df1857d |
32 | # CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ... |
43426175 |
33 | { |
2df1857d |
34 | connect_by => [ |
35 | last_name => { '!=' => 'King' }, |
36 | manager_id => { '-prior' => \'employee_id' }, |
43426175 |
37 | ], |
2df1857d |
38 | stmt => "( last_name != ? AND manager_id = PRIOR( employee_id ) )", |
39 | bind => ['King'], |
40 | msg => 'oracle.com excample #1', |
43426175 |
41 | }, |
2df1857d |
42 | # CONNECT BY PRIOR employee_id = manager_id and |
43 | # PRIOR account_mgr_id = customer_id ... |
43426175 |
44 | { |
2df1857d |
45 | connect_by => [ |
46 | manager_id => { '-prior' => \'employee_id' }, |
47 | customer_id => { '-prior' => \'account_mgr_id' }, |
43426175 |
48 | ], |
2df1857d |
49 | stmt => "( manager_id = PRIOR( employee_id ) AND customer_id = PRIOR( account_mgr_id ) )", |
50 | bind => [], |
51 | msg => 'oracle.com excample #2', |
43426175 |
52 | }, |
2df1857d |
53 | # CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4; |
54 | # TODO: NOCYCLE parameter doesn't work |
43426175 |
55 | ); |
56 | |
57 | my $sqla_oracle = DBIx::Class::SQLAHacks::Oracle->new(); |
58 | isa_ok($sqla_oracle, 'DBIx::Class::SQLAHacks::Oracle'); |
59 | |
60 | |
61 | my $test_count = ( @handle_tests * 2 ) + 1; |
62 | |
63 | for my $case (@handle_tests) { |
64 | local $Data::Dumper::Terse = 1; |
65 | my ( $stmt, @bind ); |
66 | my $msg = sprintf("Offline: %s", |
67 | $case->{msg} || substr($case->{stmt},0,25), |
68 | ); |
69 | lives_ok( |
70 | sub { |
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}; |
74 | } |
75 | ,sprintf("lives is ok from '%s'",$msg)); |
76 | } |
77 | |
78 | # |
79 | # Online Tests? |
80 | # |
81 | $test_count += 0; |
82 | |
83 | done_testing( $test_count ); |