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