Commit | Line | Data |
c2f2a718 |
1 | use strict; |
2 | use warnings; |
3 | use Test::More; |
4 | use Test::Exception; |
a84fe712 |
5 | use Data::Dumper::Concise; |
c2f2a718 |
6 | use lib qw(t/lib); |
7 | use DBIC::SqlMakerTest; |
d5dedbd6 |
8 | use DBIx::Class::SQLMaker::Oracle; |
c2f2a718 |
9 | |
bf51641f |
10 | # |
c2f2a718 |
11 | # Offline test for connect_by |
a6646e1b |
12 | # ( without active database connection) |
bf51641f |
13 | # |
c2f2a718 |
14 | my @handle_tests = ( |
15 | { |
16 | connect_by => { 'parentid' => { '-prior' => \'artistid' } }, |
17 | stmt => '"parentid" = PRIOR artistid', |
18 | bind => [], |
19 | msg => 'Simple: "parentid" = PRIOR artistid', |
20 | }, |
21 | { |
e6600283 |
22 | connect_by => { 'parentid' => { '!=' => { '-prior' => { -ident => 'artistid' } } } }, |
23 | stmt => '"parentid" != ( PRIOR "artistid" )', |
c2f2a718 |
24 | bind => [], |
e6600283 |
25 | msg => 'Simple: "parentid" != ( PRIOR "artistid" )', |
c2f2a718 |
26 | }, |
27 | # Examples from http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm |
28 | |
29 | # CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ... |
30 | { |
31 | connect_by => [ |
32 | last_name => { '!=' => 'King' }, |
e6600283 |
33 | manager_id => { '-prior' => { -ident => 'employee_id' } }, |
c2f2a718 |
34 | ], |
e6600283 |
35 | stmt => '( "last_name" != ? OR "manager_id" = PRIOR "employee_id" )', |
c2f2a718 |
36 | bind => ['King'], |
37 | msg => 'oracle.com example #1', |
38 | }, |
39 | # CONNECT BY PRIOR employee_id = manager_id and |
40 | # PRIOR account_mgr_id = customer_id ... |
41 | { |
42 | connect_by => { |
e6600283 |
43 | manager_id => { '-prior' => { -ident => 'employee_id' } }, |
c2f2a718 |
44 | customer_id => { '>', { '-prior' => \'account_mgr_id' } }, |
45 | }, |
e6600283 |
46 | stmt => '( "customer_id" > ( PRIOR account_mgr_id ) AND "manager_id" = PRIOR "employee_id" )', |
c2f2a718 |
47 | bind => [], |
48 | msg => 'oracle.com example #2', |
49 | }, |
50 | # CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4; |
51 | # TODO: NOCYCLE parameter doesn't work |
52 | ); |
53 | |
d5dedbd6 |
54 | my $sqla_oracle = DBIx::Class::SQLMaker::Oracle->new( quote_char => '"', name_sep => '.' ); |
55 | isa_ok($sqla_oracle, 'DBIx::Class::SQLMaker::Oracle'); |
c2f2a718 |
56 | |
57 | |
c2f2a718 |
58 | for my $case (@handle_tests) { |
c2f2a718 |
59 | my ( $stmt, @bind ); |
60 | my $msg = sprintf("Offline: %s", |
61 | $case->{msg} || substr($case->{stmt},0,25), |
62 | ); |
63 | lives_ok( |
64 | sub { |
65 | ( $stmt, @bind ) = $sqla_oracle->_recurse_where( $case->{connect_by} ); |
66 | is_same_sql_bind( $stmt, \@bind, $case->{stmt}, $case->{bind},$msg ) |
67 | || diag "Search term:\n" . Dumper $case->{connect_by}; |
68 | } |
69 | ,sprintf("lives is ok from '%s'",$msg)); |
70 | } |
71 | |
63ca94e1 |
72 | is ( |
73 | $sqla_oracle->_shorten_identifier('short_id'), |
74 | 'short_id', |
75 | '_shorten_identifier for short id without keywords ok' |
76 | ); |
77 | |
78 | is ( |
79 | $sqla_oracle->_shorten_identifier('short_id', [qw/ foo /]), |
80 | 'short_id', |
81 | '_shorten_identifier for short id with one keyword ok' |
82 | ); |
83 | |
84 | is ( |
85 | $sqla_oracle->_shorten_identifier('short_id', [qw/ foo bar baz /]), |
86 | 'short_id', |
87 | '_shorten_identifier for short id with keywords ok' |
88 | ); |
89 | |
90 | is ( |
91 | $sqla_oracle->_shorten_identifier('very_long_identifier_which_exceeds_the_30char_limit'), |
92 | 'VryLngIdntfrWhchExc_72M8CIDTM7', |
93 | '_shorten_identifier without keywords ok', |
94 | ); |
95 | |
96 | is ( |
97 | $sqla_oracle->_shorten_identifier('very_long_identifier_which_exceeds_the_30char_limit',[qw/ foo /]), |
98 | 'Foo_72M8CIDTM7KBAUPXG48B22P4E', |
99 | '_shorten_identifier with one keyword ok', |
100 | ); |
101 | is ( |
102 | $sqla_oracle->_shorten_identifier('very_long_identifier_which_exceeds_the_30char_limit',[qw/ foo bar baz /]), |
103 | 'FooBarBaz_72M8CIDTM7KBAUPXG48B', |
104 | '_shorten_identifier with keywords ok', |
105 | ); |
c2f2a718 |
106 | |
bf51641f |
107 | # test SQL generation for INSERT ... RETURNING |
108 | |
109 | sub UREF { \do { my $x } }; |
110 | |
111 | $sqla_oracle->{bindtype} = 'columns'; |
112 | |
113 | for my $q ('', '"') { |
114 | local $sqla_oracle->{quote_char} = $q; |
115 | |
116 | my ($sql, @bind) = $sqla_oracle->insert( |
117 | 'artist', |
118 | { |
119 | 'name' => 'Testartist', |
120 | }, |
121 | { |
122 | 'returning' => 'artistid', |
123 | 'returning_container' => [], |
124 | }, |
125 | ); |
126 | |
127 | is_same_sql_bind( |
128 | $sql, \@bind, |
129 | "INSERT INTO ${q}artist${q} (${q}name${q}) VALUES (?) RETURNING ${q}artistid${q} INTO ?", |
130 | [ [ name => 'Testartist' ], [ artistid => UREF ] ], |
131 | 'sql_maker generates insert returning for one column' |
132 | ); |
133 | |
134 | ($sql, @bind) = $sqla_oracle->insert( |
135 | 'artist', |
136 | { |
137 | 'name' => 'Testartist', |
138 | }, |
139 | { |
140 | 'returning' => \'artistid', |
141 | 'returning_container' => [], |
142 | }, |
143 | ); |
144 | |
145 | is_same_sql_bind( |
146 | $sql, \@bind, |
147 | "INSERT INTO ${q}artist${q} (${q}name${q}) VALUES (?) RETURNING artistid INTO ?", |
148 | [ [ name => 'Testartist' ], [ artistid => UREF ] ], |
149 | 'sql_maker generates insert returning for one column' |
150 | ); |
151 | |
152 | |
153 | ($sql, @bind) = $sqla_oracle->insert( |
154 | 'computed_column_test', |
155 | { |
156 | 'a_timestamp' => '2010-05-26 18:22:00', |
157 | }, |
158 | { |
159 | 'returning' => [ 'id', 'a_computed_column', 'charfield' ], |
160 | 'returning_container' => [], |
161 | }, |
162 | ); |
163 | |
164 | is_same_sql_bind( |
165 | $sql, \@bind, |
166 | "INSERT INTO ${q}computed_column_test${q} (${q}a_timestamp${q}) VALUES (?) RETURNING ${q}id${q}, ${q}a_computed_column${q}, ${q}charfield${q} INTO ?, ?, ?", |
167 | [ [ a_timestamp => '2010-05-26 18:22:00' ], [ id => UREF ], [ a_computed_column => UREF ], [ charfield => UREF ] ], |
168 | 'sql_maker generates insert returning for multiple columns' |
169 | ); |
170 | } |
171 | |
63ca94e1 |
172 | done_testing; |