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