Commit | Line | Data |
c2f2a718 |
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' } }, |
20 | stmt => '"parentid" = PRIOR artistid', |
21 | bind => [], |
22 | msg => 'Simple: "parentid" = PRIOR artistid', |
23 | }, |
24 | { |
25 | connect_by => { 'parentid' => { '!=' => { '-prior' => \'artistid' } } }, |
26 | stmt => '"parentid" != ( PRIOR artistid )', |
27 | bind => [], |
28 | msg => 'Simple: "parentid" != ( PRIOR artistid )', |
29 | }, |
30 | # Examples from http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm |
31 | |
32 | # CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ... |
33 | { |
34 | connect_by => [ |
35 | last_name => { '!=' => 'King' }, |
36 | manager_id => { '-prior' => \'employee_id' }, |
37 | ], |
38 | stmt => '( "last_name" != ? OR "manager_id" = PRIOR employee_id )', |
39 | bind => ['King'], |
40 | msg => 'oracle.com example #1', |
41 | }, |
42 | # CONNECT BY PRIOR employee_id = manager_id and |
43 | # PRIOR account_mgr_id = customer_id ... |
44 | { |
45 | connect_by => { |
46 | manager_id => { '-prior' => \'employee_id' }, |
47 | customer_id => { '>', { '-prior' => \'account_mgr_id' } }, |
48 | }, |
49 | stmt => '( "customer_id" > ( PRIOR account_mgr_id ) AND "manager_id" = PRIOR employee_id )', |
50 | bind => [], |
51 | msg => 'oracle.com example #2', |
52 | }, |
53 | # CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4; |
54 | # TODO: NOCYCLE parameter doesn't work |
55 | ); |
56 | |
57 | my $sqla_oracle = DBIx::Class::SQLAHacks::Oracle->new( quote_char => '"', name_sep => '.' ); |
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} ); |
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 ); |